<?php
include_once("../../cfg/db.php"); 

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

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

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

// if(isset($_GET['plate_number']) && isNotEmpty($_GET['plate_number']) ){
//     $plate_number =   $db -> escape(trim($_GET['plate_number']));
//     $plate_number_query = " s.plate_number = '".$plate_number."' ";
//     $where_clause = " AND v.plate_number='".$plate_number."'";
// } else {
//     $where_clause = "";
// }
 
// if(isset($_GET['cs_number']) && isNotEmpty($_GET['cs_number'])  ){
//     $cs_number = $db -> escape(trim($_GET['cs_number']));
//     $cs_number_query = " s.conduction_sticker = '".$cs_number."' ";
//     $where_clause = "v.conduction_sticker='".$cs_number."'";
// } else {
//     $where_clause = "";
// }

// if(isNotEmpty($plate_number) && isNotEmpty($cs_number)){
//  $plate_cs = " AND ".$plate_number_query. " OR ".$cs_number_query;
//  $where_clause = " AND v.plate_number='".$plate_number."' OR v.conduction_sticker='".$cs_number."'";
// } else if(isNotEmpty($plate_number)){
//     $plate_cs = " AND ".$plate_number_query;
// } else if(isNotEmpty($cs_number)){
//     $plate_cs = " AND ".$cs_number_query;
// } else {
//     $plate_cs = "";
// }

// if(isset($_GET['customer_id'])){
//     $customer_id = " AND s.customer_id = '" . $db -> escape(trim($_GET['customer_id'])) . "'";
// } else {
   // $customer_id = "";
// }
	 
// if(isset($_GET['customer_id'])){
//     $customer_record_id =   $db -> escape(trim($_GET['customer_id']));
//     $customer_dms_ids = array();
//     $customer_dms_id_list = $db -> sql_query("SELECT customer_dms_id FROM `customer_dms` WHERE `customer_record_id` = '$customer_record_id'");
//     while($row = $customer_dms_id_list->fetch_assoc()) { 
//         $customer_dms_ids = array_merge($customer_dms_ids,array($row['customer_dms_id']));
//     }
    
//     $customer_id = " AND v.customer_id IN (".implode(",",array_map('quote', $customer_dms_ids)).") ";
// } else {
//     $customer_id = "";
// }

if(isNotEmpty($_GET['cs_number']) && isNotEmpty($_GET['plate_number'])){ 
    $cs_number = $db-> escape(trim($_GET['cs_number']));
    $plate_number = $db-> escape(trim($_GET['plate_number']));
    $where_clause = " AND s.plate_number='".$plate_number."' OR s.conduction_sticker='".$cs_number."'";
   } else if(isNotEmpty($_GET['plate_number'])){ 
    $plate_number = $db-> escape(trim($_GET['plate_number']));
    $where_clause = " AND s.plate_number='".$plate_number."' ";
   } else if(isNotEmpty($_GET['cs_number'])){
    $cs_number = $db-> escape(trim($_GET['cs_number']));
    $where_clause = " AND s.conduction_sticker='".$cs_number."' ";
   } else {
    $where_clause = "";
   }  


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

    $dates_query = "SELECT %s FROM service s 
	INNER JOIN source_brand sb ON s.brand_id = sb.id
	INNER JOIN source_car_model scm ON s.model_id = scm.id
        WHERE concat(s.customer_id, s.start_date_of_service, s.ro_number, s.plate_number, s.conduction_sticker) LIKE '%%%s%%' %s GROUP BY s.id ORDER BY s.start_date_of_service DESC";

    $count = " count(s.id) ";
    $fields = " sb.name as brand ,scm.name as model, s.brand_id, s.model_id, s.customer_id, DATE_FORMAT(s.start_date_of_service, '%M %d, %Y') AS start_date_of_service, s.ro_number, s.plate_number, s.conduction_sticker ";

    $service_dates_query =  $db->sql_query(sprintf($dates_query, $fields, $search,$where_clause)." $offset_limit");
    // $service_dates_query_count = $db->select(sprintf($dates_query,$count,$search,$where_clause));
    $service_dates_query_count = $db->select("SELECT COUNT(*) FROM ( ".sprintf($dates_query,$count,$search,$where_clause)." ) as total_count");
// echo "SELECT COUNT(*) FROM ( ".sprintf($dates_query,$count,$search,$where_clause)." ) as total_count";exit;
    // //echo $plate_cs; return;
    // //echo sprintf($dates_query, $fields, $search,$plate_cs); return;

    // //code block: vehicle get vehicle_id and customer_id ------------------------->
    // $vehicle_id = "";
    // $vehicle_customer_id = "";

    // $query_vehicle_and_customer_id = "SELECT v.id, v.customer_id FROM vehicle v WHERE 1 " .$where_clause;
    // // echo $query_vehicle_and_customer_id;exit;
    // $vehicle_and_customer_id = $db->return_result($query_vehicle_and_customer_id);

    // foreach($vehicle_and_customer_id as $data){
    //     $vehicle_id = $data['id'];
    //     $vehicle_customer_id = $data['customer_id'];
    // }
    //code block: vehicle get vehicle_id and customer_id -------------------------------->

    $get_brand_model = "SELECT  v.id, v.customer_id, sb.name as brand, scm.name as model FROM `vehicle` v
    INNER JOIN source_brand sb
    ON v.`brand_id` = sb.id
    INNER JOIN source_car_model scm
    ON v.`model_id` = scm.id
    WHERE 1 ";


    while($row = $service_dates_query->fetch_assoc()){

        $car['brand'] = "";
        $car['model'] = "";
        $car['vehicle_id'] = "";
        $car['customer_no'] = "";
        if(isNotEmpty($row['plate_number']) && isNotEmpty($row['conduction_sticker'])){ 
          $get_brand_model = $get_brand_model." AND v.plate_number='".$row['plate_number']."' OR v.conduction_sticker='".$row['conduction_sticker']."'"; 

              $car_info = $db -> sql_query($get_brand_model);
              foreach($car_info as $car_data){
                  $car['brand'] = $car_data['brand'];
                  $car['model'] = $car_data['model'];
                  $car['vehicle_id'] = $car_data['id'];
                  $car['customer_no'] = $car_data['customer_id'];
              }
          } else if(isNotEmpty($row['plate_number'])){
              $get_brand_model = $get_brand_model." AND  v.plate_number= '".$row['plate_number']."'";

              $car_info = $db -> sql_query($get_brand_model);
              foreach($car_info as $car_data){
                  $car['brand'] = $car_data['brand'];
                  $car['model'] = $car_data['model'];
                  $car['vehicle_id'] = $car_data['id'];
                  $car['customer_no'] = $car_data['customer_id'];
              }
         } else if(isNotEmpty($row['conduction_sticker'])){
          $get_brand_model = $get_brand_model." AND v.conduction_sticker= '".$row['conduction_sticker']."'";

             $car_info = $db -> sql_query($get_brand_model);
             foreach($car_info as $car_data){
              $car['brand'] = $car_data['brand'];
              $car['model'] = $car_data['model'];
              $car['vehicle_id'] = $car_data['id'];
              $car['customer_no'] = $car_data['customer_id'];
             } 
         }

        $json_arr['rows'] = array_merge($json_arr['rows'],array(array(
            'table_data'=>(($row['start_date_of_service']) <> "" ? $row['start_date_of_service'] : "<span style='color: red;'>(Not provided)</span>") . " / " . 
            (($row['ro_number']) <> "" ? $row['ro_number'] : "<span style='color:red;'>(Not provided)</span>"), 
            'service_date'=>(($row['start_date_of_service']) <> "" ? $row['start_date_of_service'] : "<span style='color: red;'>(Not provided)</span>") ,
            'ro_number'=> (($row['ro_number']) <> "" ? $row['ro_number'] : "<span style='color:red;'>(Not provided)</span>"), 
            'customer_id'=>$row['customer_id'],  
            'vehicle_id'=>$car['vehicle_id'],
            'vehicle_customer_id'=>$car['customer_no'],
            'plate_number'=> (($row['plate_number']) <> "") ? $row['plate_number'] :  "<span style='color:red;'>(Not provided)</span>",
            'cs_number'=> (($row['conduction_sticker']) <> "") ? $row['conduction_sticker'] :  "<span style='color:red;'>(Not provided)</span>"

            // , 
            // 'plate_number'=>$row['plate_number'], 
            // 'brand'=>$row['brand'], 
            // '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>")
            
            )));
    }
 

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

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

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