<?php
namespace App\Models;
use CodeIgniter\Model;
Class TransactionModel extends Model {
    protected $db;

	 public function __construct()
    {
        $this->db = db_connect();

        
    }
	
	public function create($carcode,$userid) {
		$code=str_replace('CAR','TRA',$carcode);
		//var_dump($code);exit;
        $this->db->query("INSERT INTO `transaction` (`trans_code`, `car_code`, `seller_id`, `buyer_id`, `trans_status`) 
		select '$code', '$carcode', client_id, $userid, '1' from cars where code='$carcode'");
        
		$this->db->query("INSERT INTO `transaction_history` ( `transaction_code`, `completed`, `completed_at`, `step`) VALUES ('$code',1,NOW(),1)");
		
		$this->db->query("INSERT INTO `transaction_history` ( `transaction_code`,`step`) VALUES ('$code',2)");
		
		$this->db->query("update car set status=11 where code='$carcode'");
		
		return 1;
    }
	
	public function getAllBuyer($usr)
	{
		 
		 $query = $this->db->query("SELECT * FROM transaction where buyer_id=$usr");
         return $query->getResult('array');
		
	}
	
	public function getAllSeller($usr)
	{
		 $query = $this->db->query("SELECT * FROM transaction where seller_id=$usr");
         return $query->getResult('array');
		
	}
	
	public function getAll()
	{
		 $query = $this->db->query(
		 "SELECT transaction.*,transaction_steps.step_name,
		 sellers.companyname as seller,buyers.companyname as buyer		 
		 FROM transaction 
		 inner join transaction_steps on transaction.trans_step=transaction_steps.step_ord
         inner join users as sellers on transaction.seller_id=sellers.id
		 inner join users as buyers on transaction.buyer_id=buyers.id
		 ");
         
		 return $query->getResult('array');
		
	}
	
	
	public function getTransaction($code)
	{
		 
		 $query = $this->db->query("SELECT transaction.id,trans_code, transaction.created_at,  
		 transaction.car_code,transaction.seller_id,transaction.buyer_id, 
		 sellers.sell_name, buyers.buy_name,step_name FROM transaction inner join (select id as sell_id, companyname as sell_name from users) sellers 
		 on transaction.seller_id=sellers.sell_id 
		 inner join (select id as buy_id, companyname as buy_name from users) buyers on transaction.buyer_id=buyers.buy_id
		 inner join transaction_steps
		 on transaction.trans_step=transaction_steps.step_ord
		 where trans_code='$code'");
         $res=$query->getResult('array');
		
		 if(count($res))
			return $res[0];
		else
			return null;
	}
	
	public function setStep()
	{
		 $query = $this->db->query("SELECT * FROM transaction");
         return $query->getResult('array');
		
	}
	
	public function getHistoryById($id)
	{
		$sqlstr="SELECT * from transaction_history where id=".$id;
		$query = $this->db->query($sqlstr);
		$res=$query->getResult('array');
		if(count($res))
			return $res[0];
		
	}
	public function getHist($code)
	{
		$sqlstr="SELECT hist.id as history_id, `transaction_code`, `created_at`,completed, `chk1`, `chk2`, `chk_notes`,
		`chk2_notes`, `chk3`, `chk3_notes`, `attach_1`, `attach_2`, `attach_3`, `completed`,`completed_at`,
		`step_ord`, `step_name`, `step_desc`, `chk1_label`, `chk2_label`, `chk3_label`, 
		`attach1_label`, `attach2_label`, `attach3_label`, `show_to_seller`, `seller_msg`,
		`show_to_buyer`, `buyer_msg` FROM `transaction_steps` 
		LEFT JOIN (SELECT * FROM `transaction_history` WHERE transaction_code='$code') hist 
		ON transaction_steps.step_ord=hist.step order by step_ord";
		
		//var_dump($sqlstr);exit;
		$query = $this->db->query($sqlstr);
		$res=$query->getResult('array');
		return $res;
		
	}
	public function filter_aprroved_cars($filter) {
		
		$searchstr="";
		if (isset($filter['brand']) && $filter['brand'])
		{
			$searchstr=" and brand='".$filter['brand']."'";
		}
		if(isset($filter['model']) && $filter['model'])
		{
			$searchstr=" and (model_dec='".$filter['model']."' or model='".$filter['model']."') ";
		}
		
		if(isset($filter['searchstr']) && $filter['searchstr'])
		{
			$srcstr="%".strtolower($filter['searchstr'])."%";
			$searchstr=" and ( LOWER(brand) like '".$srcstr."'".
					   " or LOWER(model) like '".$srcstr."'".
					   " or LOWER(model_dec) like '".$srcstr."'".
					   " or LOWER(code) like '".$srcstr."'".
					   " or LOWER(price_bgn) like '".$srcstr."'".
					   " or LOWER(body_type) like '".$srcstr."'".
					   " or LOWER(cubic_capacity) like '".$srcstr."'".
					   " or LOWER(engine_power_hp) like '".$srcstr."'".
					   " or LOWER(emission_standard) like '".$srcstr."'".
					   " or LOWER(date_manufacture) like '".$srcstr."'".
					   " or LOWER(gear_box) like '".$srcstr."'".
					   " or LOWER(drivetrain) like '".$srcstr."'".
					   " or LOWER(km) like '".$srcstr."'".
					   " or LOWER(vin) like '".$srcstr."'".
					   " or LOWER(technical_status) like '".$srcstr."'".
					   " or LOWER(country_of_origin) like '".$srcstr."'".
					   " or LOWER(notes) like '".$srcstr."'".
					   " or LOWER(fuel) like '".$srcstr."'".
					   " or LOWER(additionals) like '".$srcstr."')";
					   
					   
		}
		
		
			
        $query = $this->db->query("SELECT * FROM cars where status=3  ".$searchstr." order by id desc");
        return $query->getResult('array');
    }

	public function get_aprroved_brands() {
        $query = $this->db->query("SELECT distinct brand as brand_name FROM cars where status=3 order by brand");
        return $query->getResult('array');
    }

	public function get_aprroved_models() {
        $query = $this->db->query("SELECT distinct model FROM cars where status=3 order by model");
        return $query->getResult('array');
    }

	public function get_all_cars() {
        $query = $this->db->query("SELECT * FROM cars");
        return $query->getResult('array');
    }
	
	 public function get_approved_by_code($code) {
        $query = $this->db->query("SELECT * FROM cars where code='$code' and status=3");
        $arr=$query->getResult('array');
		if(count($arr))
			
		return $arr[0];
    }
	
    public function get_all($usr) {
        $query = $this->db->query("SELECT * FROM cars where client_id=$usr");
        return $query->getResult('array');
    }
    
	public function save_data($insert_array)
	{
		$updt_str = '';
		$insr_str='INSERT INTO cars (';
		$insr_val='';
		
		foreach ($insert_array as $k => $v)
		{
			$updt_str = $updt_str.' '.$k.' = \''.$v.'\',';
			$insr_str = $insr_str.' '.$k.',';
			$insr_val = $insr_val.' \''.$v.'\',';
		}
		$updt_str = substr_replace($updt_str,"", -1);
		$insr_str =  substr_replace($insr_str,"", -1).')';
		$insr_val = ' VALUES (' .substr_replace($insr_val,"", -1).')';
		$sqlstr=$insr_str . $insr_val. ' ON DUPLICATE KEY UPDATE '.$updt_str;
		//var_dump($sqlstr);exit;
		
		$this->db->query($insr_str . $insr_val. ' ON DUPLICATE KEY UPDATE '.$updt_str);
	}	
   
  
    public function get_by_id($id,$usr) {
        $query = $this->db->query("SELECT * FROM cars where id=$id and client_id=$usr");
        return $query->getResult('array');
    }
	
	 public function get_by_code($code,$usr) {
        $query = $this->db->query("SELECT * FROM cars where code='$code' and client_id=$usr");
        return $query->getResult('array');
    }


	 public function get_imgs($code) {
        $query = $this->db->query("SELECT * FROM `car_images` where car_code='$code' order by url");
        return $query->getResult('array');
    }


	public function get_brands() {
        $query = $this->db->query("SELECT distinct id, brand_name FROM car_brand  where active=1 order by brand_name");
        return $query->getResult('array');
    }
	

	public function get_client_brands($usr) {
        $query = $this->db->query("SELECT distinct brand as brand_name FROM cars where client_id=$usr order by brand_name" );
        return $query->getResult('array');
    }
	
	
	public function get_country() {
        $query = $this->db->query("SELECT distinct id, country_name FROM country order by country_name");
        return $query->getResult('array');
    }
	
	public function get_car_status() {
        $query = $this->db->query("SELECT distinct id, status_name FROM car_status where active=1 order by status_name ");
        return $query->getResult('array');
    }
	public function get_active_transactions_count_admin(){
		$query = $this->db->query("SELECT count(*) as cnt FROM transaction where trans_step!=19");
		return $query->getResult('array')[0];
	}
	
	public function get_concluded_transactions_count_admin(){
		$query = $this->db->query("SELECT count(*) as cnt FROM transaction where trans_step=19");
		return $query->getResult('array')[0];
	}
	
    public function set_values($id = 1) {
        $data = array(
            'quantity_defect' => $this->input->post('quantity_defect')
        );
		
        if ($id !=0) {        
           $obs=$this->input->post('obs');
			$quantity_defect=$this->input->post('quantity_defect');
			
			$sqlstr="update cars set quantity_defect=quantity_defect+".$quantity_defect." where id=" . $id;
			$this->db->query($sqlstr);
			
			$sqlstr="insert into defect_log(product_id,quantity_defect,obs) values(".$id.",".$quantity_defect.",'".$obs."')";
			return $this->db->query($sqlstr);
			
		
        } 
    }
    
    public function search(){
        $this->db->or_like('description', $this->input->post('search'), 'both');
        $this->db->or_like('barcode', $this->input->post('search'), 'both');
        $this->db->or_like('customer', $this->input->post('search'), 'both');
		$this->db->where('quantity_defect>0');
        $query = $this->db->get('products');
        return $query->getResult('array');
    }
    
    
    
}
