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


$fmt = new NumberFormatter('ph_PH', NumberFormatter::CURRENCY);
$search =  $db -> escape(trim($_GET['search']));
$offset =   $db -> escape(trim($_GET['offset']));

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

$customer_name = "";

   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'");
        $customer_dms_id_list = $db->sql_query("SELECT v.id FROM customer c 
INNER JOIN vehicle v
ON c.id = v.customer_record_id 
INNER JOIN service s 
ON v.id = s.vehicle_id
WHERE c.id = '$customer_record_id'");

        while($row = $customer_dms_id_list->fetch_assoc()){
            $customer_dms_ids = array_merge($customer_dms_ids,array($row['id']));
        }
        if(count($customer_dms_ids) > 0){
            $customer_id = " AND s.vehicle_id IN (".implode(",",array_map('quote', $customer_dms_ids)).") ";
        }else{ 
          $customer_id = "-1"; //no service records
        }
    } else {
        $customer_id = "";
    }

    
   if(isset($_GET['vehicle_id'])){
        $vehicle_record_id = $db->escape(trim($_GET['vehicle_id'])); 
        $vehicle_id = " AND s.vehicle_id IN (".$vehicle_record_id.") ";
    } else {
        $vehicle_id = "";
    }

    //echo "customer id: " . $customer_id; return;

    $json_arr['rows'] = array();
    $service_query = "SELECT  %s
    FROM service s 
    INNER JOIN vehicle v ON s.vehicle_id = v.id
    INNER JOIN source_brand sb ON v.brand_id=sb.id
    INNER JOIN source_car_model scm ON v.model_id=scm.id
    INNER JOIN source_company_dealer scd ON s.company_dealer_id=scd.id
    INNER JOIN source_dealer sd ON scd.dealer_id=sd.id 
       LEFT OUTER JOIN customer c
       ON v.customer_record_id = c.id 
WHERE (concat( IFNULL(s.ro_number,''), IFNULL(v.plate_number,''), IFNULL(v.conduction_sticker,'') , s.service_advisor, 
                    IF(
                         concat(c.first_name,c.last_name,c.corporation_name) <> '', concat(c.first_name,c.last_name,c.corporation_name), ''
                     )
     
    ) LIKE '%%%s%%' OR s.id= '%s' OR v.id= '%s') %s  %s 
    AND s.status = 1
AND v.status = 1
    AND scd.company_id IN (".$granted_company_ids.") 
    GROUP BY s.id ORDER BY s.date_uploaded DESC";

    $count = " count(s.id) ";
    $fields = "v.id as vehicle_id, sd.code as dealer, s.ro_number, DATE_FORMAT(s.ro_date, '%M %d, %Y') AS date, s.ro_total,v.plate_number, s.service_advisor, s.id, v.customer_record_id, v.conduction_sticker,v.brand_id,v.model_id, sb.name as brand,scm.name as model,
    IF(
		c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name
	) as name ";


//    echo (sprintf($service_query, $fields, $search, $search, $search,$customer_id,$vehicle_id)." $offset_limit");exit;
    $service_list_query = $db->sql_query(sprintf($service_query, $fields, $search, $search, $search,$customer_id,$vehicle_id)." $offset_limit");
    // $service_list_query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($service_query,$count,$search, $search, $search,$customer_id,$vehicle_id)." ) as total_count");
 
$data_counter = 0;
    while($row = $service_list_query->fetch_assoc()) {
        
    
        $json_arr['rows'] = array_merge($json_arr['rows'],array(array(
            'vehicle_id'=>$row['vehicle_id'],
            'id'=>$row['id'],
            'ro_number'=>$row['ro_number'], 
            'plate_number'=>$row['plate_number'], 
            // 'serial_number'=>$row['serial_number'], 
            'conduction_sticker'=>$row['conduction_sticker'],  
            'ro_total'=>($row['ro_total'] <> '' )  ? $fmt->format($row['ro_total'])  : "",
            'brand'=>   $row['brand'] ,
            'model'=> $row['model'],
            'service_advisor'=>$row['service_advisor'], 
            'date'=> $row['date'], 
            'id'=>$row['id'], 
            'customer_id'=>$row['customer_record_id'], 
            'dealer'=>$row['dealer'], 
            '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;'>(Not provided)</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;

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

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

?>