<?php
include_once("../../cfg/db.php"); 
include_once("../../api/controllers/utility.php");
 
$utility = new Utility(); 
$my_customer_record_id = $_SESSION['user']['id'];
 $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ?  $_SESSION['user']['company_permissions_implode']  : '-1' ;


if(isset($_REQUEST['search'])){
    $search =  $db -> escape(trim($_REQUEST['search']));
    $search_clean =  $utility->alphaNumericOnly($search); //remove special char and space
} else {
    $search = "";
    $search_clean = "";
}

if(isset($_REQUEST['offset'])){
    $offset =   $db -> escape(trim($_REQUEST['offset']));
} else {
    $offset =   "";
}

 if(isset($_REQUEST['my_records'])){
     $my_record_bool = filter_var($_REQUEST['my_records'], FILTER_VALIDATE_BOOLEAN);
     if($my_record_bool){
         $my_record=" AND (v.upload_by='$my_customer_record_id' OR v.modified_by='$my_customer_record_id') ";
     }else{
        $my_record="";
     } 
}else{
    $my_record="";
} 

if(isset($_REQUEST['limit'])){
    $limit =   $db -> escape(trim($_REQUEST['limit']));
    $offset_limit = " LIMIT ". $offset.",".$limit;
} else {
    $offset_limit = "";
}

$customer_name = "";
	 
if(isset($_REQUEST['customer_id'])){ //if from customer profile
    $customer_record_id =   $db -> escape(trim($_REQUEST['customer_id'])); 
    $customer_id = " AND v.customer_record_id = '$customer_record_id' ";
} else {
    $customer_id = "";
}

$vehicle_actions = "" ;
if(isset($_REQUEST['vehicle_actions']) && $_REQUEST['vehicle_actions'] != '0'){
    switch($_REQUEST['vehicle_actions']){
        case "all": 
            $vehicle_actions =  "";
            break;
        case "new": 
            $vehicle_actions =  " AND  v.date_uploaded > DATE_SUB(NOW(), INTERVAL 1 WEEK) ";
            break;
         case "updated": 
            $vehicle_actions =  " AND v.date_modified > DATE_SUB(NOW(), INTERVAL 1 WEEK)  ";
            break;
         case "added": 
            $vehicle_actions =  " AND v.date_uploaded > DATE_SUB(NOW(), INTERVAL 1 DAY) ";
            break;
    }
}

    
     $dateRange='';
    // if($_REQUEST['start_date'] && $_REQUEST['end_date']){
    //     $date_from = $_REQUEST['start_date'];
    //     $date_to = $_REQUEST['end_date'];
    //     $dateRange = " AND DATE(v.date_created) >='$date_from' AND DATE(v.date_created) <= '$date_to' ";
    // }


$dms = (isset($_REQUEST['dms']) && $_REQUEST['dms'] != '0') ? " AND sc.dms_id = '".$_REQUEST['dms']."' " : "" ;
$company = (isset($_REQUEST['company']) && $_REQUEST['company'] != '0') ? " AND scd.company_id = '".$_REQUEST['company']."' " : "" ;

    $json_arr['rows'] = array();

    $vehicle_query = "SELECT  %s
    FROM vehicle v 
    INNER JOIN source_brand sb ON v.brand_id=sb.id 
        INNER JOIN vehicle_color vc ON v.color_id=vc.id 
        INNER JOIN source_car_model scm ON v.model_id=scm.id  
        LEFT OUTER JOIN customer c
        ON v.customer_record_id = c.id 
        LEFT OUTER JOIN customer_contact cc
        ON c.id = cc.customer_id 
        INNER JOIN source_company_dealer scd 
        ON v.company_dealer_id = scd.id
        INNER JOIN source_company sc
        ON scd.company_id = sc.id
        WHERE (
        (CONCAT(IFNULL(v.plate_number,''), IFNULL(v.conduction_sticker,''), v.customer_id, 
          IF(c.id IS NOT NULL,
            CONCAT(
                c.corporation_name,
                ' ',
                c.first_name,
                ' ',
                c.last_name ,
                ' ',
                c.first_name,
                ' ',
                c.middle_name,
                ' ',
                c.last_name,
                CONCAT('0',SUBSTRING(cc.mobile_phone_1, 4)),
                      ' ',cc.mobile_phone_1
            )
            ,'')

        ) LIKE '%%%s%%' OR IFNULL(v.plate_number,'') = '%s' OR IFNULL(v.conduction_sticker,'') = '%s' OR IFNULL(v.vin_number,'') = '%s' OR v.id='%s') %s  %s %s %s AND v.status = 1)   AND scd.company_id IN (".$granted_company_ids.") %s %s GROUP BY v.id ORDER BY v.id DESC";

    $count = " count(v.id) ";
    $fields = " v.customer_record_id,v.id, scm.name AS model, v.plate_number, v.conduction_sticker, sb.name AS brand, vc.name AS color, v.customer_id,v.date_created,sb.logo, 
	IF(
		c.status = 1,
        IF(
		    c.type = 1, CONCAT(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name
	    ),
    '') as name ";

    // echo (sprintf($vehicle_query, $fields, $search,$search,$customer_id,$dms,$company,$vehicle_actions)." $offset_limit");exit;
// echo sprintf($vehicle_query, $fields, $search, $search_clean, $search_clean,$search,$customer_id,$dms,$company,$vehicle_actions,$my_record)." $offset_limit";exit;
    $vehicle_list_query =  $db -> sql_query(sprintf($vehicle_query, $fields, $search, $search_clean, $search_clean, $search,$search,$customer_id,$dms,$company,$vehicle_actions,$my_record,$dateRange)." $offset_limit");
    // $vehicle_list_query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($vehicle_query,$count, $search, $search_clean,$search_clean,$search,$customer_id,$dms,$company,$vehicle_actions,$my_record)." ) as total_count");

    //echo sprintf($vehicle_query, $fields, $search,$customer_id); return;


    if(isset($_REQUEST['grid'])){ 
        $json_arr['vehicle_list'] = "";
        $json_arr['vehicle_list_paging'] = "";
       $vehicle_list_query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($vehicle_query,$count, $search, $search_clean,$search_clean,$search,$search,$customer_id,$dms,$company,$vehicle_actions,$my_record)." ) as total_count");
$json_arr['total'] = $vehicle_list_query_count; //total number of result  
        // echo $json_arr['total'] % $limit;
        $goto_offset = 0;
        for($i = 0; $i < $json_arr['total'] / $limit; $i++){ 
            if($offset ==  $goto_offset){
                $grid_status = "active";
                $grid_offset = "";
            }else{
                $grid_status = "";
                $grid_offset = " onclick='gotoOffset(".$goto_offset.")' ";
            }
            $json_arr['vehicle_list_paging'] =  $json_arr['vehicle_list_paging']."<li style='cursor:pointer' class='page-item ".$grid_status."'><a class='page-link' ".$grid_offset.">".($i+1)."</a></li>";
            $goto_offset = $goto_offset+$limit;
        }
        if($goto_offset < 1){
            $json_arr['vehicle_list_paging']  = "No Record";
        }
        $json_arr['grid_offset'] =  $goto_offset;
        
        while($row = $vehicle_list_query->fetch_assoc()) {

            $grid_customer_id = $row['customer_id']; 
            $grid_customer_record_id = $row['customer_record_id'];
            $grid_date = ($row['date_created']) <> "" ? $row['date_created'] : "<span style='color:red;'>(Not provided)</span>";
            $grid_brand = ($row['brand']) <> "" ? $row['brand'] : "<span style='color:red;'>(Not provided)</span>";
            $grid_model = ($row['model']) <> "" ? $row['model'] : "<span style='color:red;'>(Not provided)</span>";// $row['model'];
            $grid_plate = ($row['plate_number']) <> "" ? $row['plate_number'] : "<span style='color:red;'>(Not provided)</span>";
            $grid_cs = ($row['conduction_sticker']) <> "" ? $row['conduction_sticker'] : "<span style='color:red;'>(Not provided)</span>";
            $grid_vehicle_id = $row['id'];
            $grid_color = ($row['color']) <> "" ? $row['color'] : "<span style='color:red;'>(Not provided)</span>";
            $grid_brand_logo = $row['logo'];  
            $grid_cs_data = $row['conduction_sticker'];
            $grid_plate_data =  $row['plate_number'];
            $json_arr['vehicle_list'] = $json_arr['vehicle_list']."<div class='col-12 col-sm-6 col-md-4 d-flex align-items-stretch'><div class='card bg-light'> <!--<div class='card-header text-muted border-bottom-0'>".$grid_date."</div> --><div class='card-body mt-2 pt-0'><div class='row'><div class='col-7'><h2 class='lead'><b>".$grid_brand."</b></h2><p class='text-muted text-sm'><b>Model: </b> ".$grid_model." </p><ul class='ml-4 mb-0 fa-ul text-muted'><li class='small'><span class='fa-li'><i class='fa fa-id-card'></i></span> Plate #: ".$grid_plate."</li><li class='small'><span class='fa-li'><i class='fa fa-barcode'></i></span> CS #: ".$grid_cs."</li></ul></div><div class='col-5 text-center'><img src='dist/img/brand/".$grid_brand_logo."' alt='logo' class='img-circle img-fluid'></div></div></div><div class='card-footer'  style='padding: .25rem;'><div class='text-right'><a onclick='customerVehicleInfo(\"".$grid_vehicle_id."\")' class='btn btn-sm btn-primary'><i class='fas fa-info'></i> View</a></div></div></div></div> ";
        }
    }else{
        $data_counter = 0;
    while($row = $vehicle_list_query->fetch_assoc()) {
        $json_arr['rows'] = array_merge($json_arr['rows'],array(array('model'=>$row['model'], 
            'plate_number'=>$row['plate_number'], 
            'brand'=>$row['brand'], 
            'customer_record_id'=>$row['customer_record_id'], 
            'color'=>$row['color'], 
            'conduction_sticker'=>$row['conduction_sticker'], 
            'id'=>$row['id'], 
            'customer_id'=>$row['customer_id'], 
            'customer_name_id'=>(($row['name']) <> "" ? $row['name'] : "<span style='color:red;'>(Not provided)</span>")
            // 'customer_name_id'=>(($row['customer_id']) <> "" ? $row['customer_id'] : "<span style='color: red;'></span>") . " / " . 
            //     (($row['name']) <> "" ? $row['name'] : "<span style='color:red;'>(Not provided)</span>")
            
            )));   
            $data_counter++;
    }
      $json_arr['total'] = ($data_counter < $limit) ? $data_counter : 100; //total number of resu 100;
}
 

    // $json_arr['total'] = $vehicle_list_query_count; //total number of result  
  

    echo json_encode($json_arr);
    //print_r($json_arr);
 
    function isNotEmpty($data){
        return preg_match('/\S/', $data);
    }

    function quote($str) {
        return sprintf("'%s'", $str);
    }
?>