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

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

        
    }
	
	public function get_all_cars() {
        $query = $this->db->query("SELECT cars.*, users.companyname FROM cars inner join users on cars.client_id=users.id");
        return $query->getResult('array');
    }
	
    public function get_all($usr) {
        $query = $this->db->query("SELECT * FROM cars where client_id=$usr");
        return $query->getResult('array');
    }
    
	
   
  
    public function get_by_id($id) {
        $query = $this->db->query("SELECT * FROM cars where id=$id and client_id=$usr");
        return $query->getResult('array');
    }
	
	 public function get_by_code($code) {
        $query = $this->db->query("SELECT * FROM cars where code='$code'");
        $res=$query->getResult('array');
		if(count($res)) 
			return $res[0];
		else
			return null;
    }


	 public function get_all_by_code($code) {
        $query = $this->db->query("SELECT cars.*,companyname FROM cars inner join users on cars.client_id=users.id where cars.code='$code'");
        return $query->getResult('array');
    }
	

	 public function get_imgs($code) {
        $query = $this->db->query("SELECT * FROM `car_images` where car_code='$code' order by `sort`" );
        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 getCarsByStatus($status) {
        $query = $this->db->query("SELECT cars.*, users.companyname FROM cars inner join users on cars.client_id=users.id where cars.status=$status");
        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');
    }
	
	// Valid statuses:
    // 0: Cars published all time
    // 1: Cars published now
    // 2: Cars in transaction
    // 3: Cars to receive payment from Buyers
    // 4: Cars to receive from Sellers
    // 5: Cars to be paid to Sellers
    // 6: Total sold cars
    public function get_cars_count_admin($usr, $status){
        switch($status){
            case 0:
                $query = $this->db->query("SELECT count(*) as cnt FROM cars where status in (3,11,12)");
                break;
            case 1:
                $query = $this->db->query("SELECT count(*) as cnt FROM cars where status=3");
                break;
            case 2:
                $query = $this->db->query("SELECT count(*) as cnt FROM cars where status=11");
                break;
            case 3:
                // TODO after transaction steps are added
                // $query = $this->db->query("SELECT COUNT(DISTINCT c.code) AS cnt
				// FROM cars c
				// JOIN transaction t ON c.code = t.car_code
				// WHERE c.client_id = $usr
				// AND t.trans_step = 13;");
				// break;
            case 4:
                // TODO after transaction steps are added
                // $query = $this->db->query("SELECT COUNT(DISTINCT c.code) AS cnt
                // FROM cars c
                // JOIN transaction t ON c.code = t.car_code
                // WHERE c.client_id = $usr
                // AND t.trans_step = 14;");
                // break;
            case 5:
                // TODO after transaction steps are added
                // $query = $this->db->query("SELECT COUNT(DISTINCT c.code) AS cnt
                // FROM cars c
                // JOIN transaction t ON c.code = t.car_code
                // WHERE c.client_id = $usr
                // AND t.trans_step = 15;");
                // break;
            case 6:
                $query = $this->db->query("SELECT count(*) as cnt FROM cars where status=12");
                break;
            default:
                return [];
        }

        return $query->getResult('array')[0];

    }
    
	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.' = \''.addslashes($v).'\',';
			$insr_str = $insr_str.' '.$k.',';
			$insr_val = $insr_val.' \''.addslashes($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);
	}	
	
   
    
    
    
}
