<?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('PHP', NumberFormatter::CURRENCY);
    $search = "";
    $offset = "";

    if(isset($_GET['search'])){
        $search =  $db -> escape(trim($_GET['search']));
    }
    
    if(isset($_GET['offset'])){
        $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 sales 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 = "";
    }
    
    $json_arr['rows'] = array();
    $sales_query = "SELECT %s FROM sales 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 v.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 
    LEFT OUTER JOIN sales_consultant sc
    ON s.sales_person = sc.id
WHERE (concat(
    v.conduction_sticker,' ',
    IF(
        s.order_number IS NOT NULL, s.order_number, ''
    ),
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  
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.id, sb.name as brand, scm.name as model, s.brand_id, s.sale_price,v.model_id, v.plate_number , v.conduction_sticker,s.order_number, s.po_number, DATE_FORMAT(s.activity_date, '%Y %M %d') AS activity_date, CONCAT(sc.first_name,' ',sc.last_name) as sales_person, v.customer_record_id, 
    IF(
		c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name
	) as name  ";

    // echo (sprintf($sales_query, $fields, $search, $search,$search, $customer_id)." $offset_limit");exit;
    $sales_list_query = $db -> sql_query(sprintf($sales_query, $fields, $search, $search,$search, $customer_id)." $offset_limit");
    // $sales_list_query_count = $db -> select(sprintf($sales_query, $count,  $search,$search,$search, $customer_id));
    $sales_list_query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($sales_query, $count,  $search,$search,$search, $customer_id)." ) as total_count");
 
    //echo sprintf($sales_query, $fields, $search, $customer_id); return;
    
//     $get_brand_model = "SELECT 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 = $sales_list_query->fetch_assoc()) {
       
        // $car['brand'] = "";
        // $car['model'] = "";
        // 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'];
        //       }
        //   } 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'];
        //       }
        //  } 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'];
        //      } 
        //  }


        $json_arr['rows'] = array_merge($json_arr['rows'] ,array( array(
            'id'=>$row['id'], 
            'order_number'=>$row['order_number'], 
            'brand'=>   $row['brand'],
            'model'=> $row['model'],
            'po_number'=>$row['po_number'], 
            'activity_date'=>$row['activity_date'],
            'sale_price'=>($row['sale_price'] <> '' )  ? $fmt->formatCurrency($row['sale_price'], "Php")  : "",
            'sales_person'=>trim($row['sales_person']),
            'customer_id'=>$row['customer_record_id'],
            'vehicle_id'=>$row['vehicle_id'],
            'conduction_sticker'=>$row['conduction_sticker'],
            '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>")
            )));
    }


    $json_arr['total'] = $sales_list_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);
}

?>