<?php
include_once("../../cfg/db.php"); 
include_once("../../api/controllers/utility.php");
 
if(!isset($_SESSION['user']['id'])){
	echo "Session Expired.";
    exit;
}

$utility = new Utility(); 
$my_customer_record_id = $_SESSION['user']['id'];
$user_dealer_permissions  = (!empty($_SESSION['user']['dealer_permissions']) ? $_SESSION['user']['dealer_permissions'] : $_SESSION['user']['dealer']);
 $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ?  $_SESSION['user']['company_permissions_implode']  : '-1' ; 
//  $fields_permissions = $utility->isNotEmpty($_SESSION['user']['fields_permissions']) ?  $_SESSION['user']['fields_permissions']  : '-1' ;

if(isset($_REQUEST['search'])){
    $search =  $db -> escape(trim($_REQUEST['search']));
}else{
    $search="";
}

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 (t1.upload_by='$my_customer_record_id' OR t1.modified_by='$my_customer_record_id') ";
     }else{
        $my_record="";
     } 
}else{
    $my_record="";
} 
$customer_corporation = (isset($_REQUEST['customer_corporation']) && $_REQUEST['customer_corporation']) ? " AND t1.corporation_id = '".$_REQUEST['customer_id']."' " : "" ;
 

if(isset($_REQUEST['limit'])){
    $limit =   $db -> escape(trim($_REQUEST['limit']));

    if(isset($_REQUEST['grid'])){   
        $page = $_REQUEST['offset']; 
        if($page){ 
        $start = ($page - 1) * $limit; //first item to display on this page
        $offset = ($page - 1) * $limit;
        }else{
        $start = 0;
        }
    }

    $offset_limit = " LIMIT ". $offset.",".$limit;
    // echo $offset_limit;exit;
} else {
    $offset_limit = "";
}

 
$where_age = " TIMESTAMPDIFF(YEAR,t1.date_of_birth, CURDATE() ) ";
  
//FILTERS
$type = (isset($_REQUEST['type']) && $_REQUEST['type'] != '0') ? " AND t1.type = '".$_REQUEST['type']."' " : "" ;
$gender = (isset($_REQUEST['gender']) && $_REQUEST['gender'] != 'all') ? " AND t1.gender_id = '".$_REQUEST['gender']."' " : "" ;
$dms = (isset($_REQUEST['dms']) && $_REQUEST['dms'] != '0') ? " AND sd.id = '".$_REQUEST['dms']."' " : "" ;
$company = (isset($_REQUEST['company']) && $_REQUEST['company'] != '0') ? " AND scd.company_id = '".$_REQUEST['company']."' " : "" ;

$age = "" ;
if(isset($_REQUEST['filter_age']) && $_REQUEST['filter_age'] != '0'){
    switch($_REQUEST['filter_age']){
        case "below": 
            $age =  "AND $where_age  <= 30  ";
            break;
        case "between": 
            $age =  "AND $where_age   <= 50 AND $where_age >= 31  ";
            break;
         case "above": 
            $age =  "AND $where_age   >= 51   ";
            break;
    }
}

$customer_actions = "" ;
if(isset($_REQUEST['customer_actions']) && $_REQUEST['customer_actions'] != '0'){
    switch($_REQUEST['customer_actions']){
        case "all": 
            $customer_actions =  "";
            break;
        case "new": 
            $customer_actions =  " AND t1.date_uploaded > DATE_SUB(NOW(), INTERVAL 1 WEEK) ";
            break;
         case "updated": 
            $customer_actions =  " AND t1.date_modified > DATE_SUB(NOW(), INTERVAL 1 WEEK) ";
            break;
         case "added": 
            $customer_actions =  " AND t1.date_uploaded > DATE_SUB(NOW(), INTERVAL 1 DAY) ";
            break;
    }
}


$complete_data = "";
$incomplete_data = "";  
$mobile_phone_1 = "";  
$email_1 = "";
$address_1 = "";
$date_of_birth ="";

if(isset($_REQUEST['filter_data']) && $_REQUEST['filter_data'] != '0'){
    switch($_REQUEST['filter_data']){
        case "Complete Data": 
            $complete_data  = " AND ((IFNULL(DATE_FORMAT(t1.`date_of_birth`,'%%Y-%%m-%%d'),'') <> '' and t1.gender_id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '')  AND t1.type = 1) OR
            (cc.address_1 <> '' AND cc.email_1 <> ''  AND t1.type = 2) ";
             break;
       case "Incomplete Data": 
           $incomplete_data = " AND ((IFNULL(DATE_FORMAT(t1.`date_of_birth`,'%%Y-%%m-%%d'),'') = '' or 
           t1.gender_id = 0 or 
           cc.address_1 = '' or cc.email_1 = '' ) AND t1.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND  t1.type = 2)  ";
           break;
         case "No Mobile": 
            $mobile_phone_1 =  " AND cc.mobile_phone_1 = '' "; 
            break;
        case "No Email":  
            $email_1 =  " AND cc.email_1 = '' "; 
            break;
        case "No Address": 
            $address_1 =  " AND cc.address_1 = '' "; 
            break;
        case "No Gender": 
            $gender =   " AND t1.gender_id = '0' AND t1.type = '1' ";
            break;
        case "No Date of birth": 
            $date_of_birth = " AND IFNULL(DATE_FORMAT(t1.`date_of_birth`,'%%Y-%%m-%%d'),'') = ''  AND t1.type = '1'  "; 
            break;
    }
}


 
if(isNotEmpty($search)){
    $filter_email = " OR cc.email_1 = '".$search."' ";
}else{
    $filter_email = "";
}

if(isNotEmpty($dms) || isNotEmpty($company)){
    $group_by = " GROUP BY t1.id ";
}else{
    $group_by = " GROUP BY t1.id ";
}

$data_counter = 0;
    $json_arr['rows'] = array();
 
 
    $query = " SELECT  %s
    FROM customer t1
   INNER JOIN customer_contact cc 
   ON t1.id = cc.customer_id 
   INNER JOIN customer_dms cd 
   ON t1.id = cd.customer_record_id
   INNER JOIN source_company_dealer scd 
   ON cd.company_dealer_id = scd.id
   INNER JOIN source_company sc 
   ON scd.company_id  = sc.id
   INNER JOIN source_dms sd
   ON sc.dms_id = sd.id
   LEFT OUTER JOIN all_suffix ass
   ON t1.suffix_id = ass.id
         WHERE (
            (CONCAT('',
                CONCAT(
                    t1.corporation_name,
                    ' ',
                    t1.first_name,
                    ' ',
                    t1.last_name ,
                    ' ',
                    t1.first_name,
                    ' ',
                    t1.middle_name,
                    ' ',
                    t1.last_name
                ),' ',
                    CONCAT('0',SUBSTRING(cc.mobile_phone_1, 4)
            ),
            ' ',
            cc.mobile_phone_1,' ',cd.customer_dms_id) LIKE 
         '%%%s%%' /* 1 search */
         )
         %s /* email */
         %s /* 3 gender */
         %s /* 4 dms */
         %s /* 5 age */
         %s /* 6 complete data */
         %s /* 7 incomplete data */
         %s /* 8 mobile phone */
         %s /* 9 email */
         %s /* 10 address */
         %s /* 11 dateofbirth */ 
         %s /* 12 company */ 
         %s /* 13 corporation_customer */ 
         %s /* 14 customer_actions */ 
         )
         AND scd.company_id IN (".$granted_company_ids.")
         AND scd.dealer_id IN (".$user_dealer_permissions.")
         AND t1.status = 1
         %s /* myrec */
         %s /* 2 type */
         %s /* 13 group */ 
    ORDER BY t1.id  DESC "; 
 
 
$fields = "  
t1.id,
t1.photo, 
IF(t1.type = 1,
   concat(t1.first_name,' ',t1.middle_name,' ',t1.last_name)
   ,t1.corporation_name
) 
as name, 
t1.gender_id,
(SELECT gender_name FROM all_gender WHERE id=t1.gender_id) as gender_name,
t1.date_of_birth,
t1.date_uploaded,
t1.date_created,
TIMESTAMPDIFF(YEAR,t1.date_of_birth, CURDATE()) as age_number, 
cc.mobile_phone_1,
cc.landline,
cc.email_1,
cc.address_1,
t1.type ";
$count = " 1  ";  
// echo (sprintf($query,$fields,$search,$search,$filter_email,$gender,$dms,$age ,$complete_data, $incomplete_data,$mobile_phone_1,$email_1,$address_1,$date_of_birth,$company,$customer_corporation,$customer_actions,$my_record,$type,$group_by)." $offset_limit");exit;
$final_query = sprintf($query,$fields,$search,$filter_email,$gender,$dms,$age ,$complete_data, $incomplete_data,$mobile_phone_1,$email_1,$address_1,$date_of_birth,$company,$customer_corporation,$customer_actions,$my_record,$type,$group_by)." $offset_limit";
// echo $final_query;exit;
$exec_query =  $db -> sql_query($final_query);  
// $query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($query,$count,$search,$filter_email,$gender,$dms,$age,$complete_data,$incomplete_data,$mobile_phone_1,$email_1,$address_1,$date_of_birth,$company,$customer_corporation,$customer_actions,$my_record,$type,$group_by)." ) as total_count"); 
$query_count = 100;


    // $json_arr['total'] = $query_count; //total number of result  

    
    if(isset($_REQUEST['grid'])){ 
        $json_arr['customer_list'] = "";
        $json_arr['customer_list_paging'] = "";
          
        $total = 100;//$json_arr['total'];
        /* Setup page vars for display. */
        if ($page == 0) $page = 1; //if no page var is given, default to 1.
        $prev = $page - 1; //previous page is current page - 1
        $next = $page + 1; //next page is current page + 1
        $lastpage = ceil($total/$limit); //lastpage.
        $lpm1 = $lastpage - 1; //last page minus 1
        $counter=0; 
        $adjacents = 1; 
        $lpm1 = $lastpage - 1; //last page minus 1
 
        if($lastpage > 1)
        {
            $json_arr['customer_list_paging'] .= "";
        if ($page > $counter+1) {
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$prev.")' hidden>Prev</a></li>"; 
        }

        if ($lastpage < 7 + ($adjacents * 2)){ 
        for ($counter = 1; $counter <= $lastpage; $counter++){
        if ($counter == $page)
              $json_arr['customer_list_paging'].= " <li class='page-item active' ><a class='page-link '  onclick='gotoOffsetCustomer(".$counter.")' >$counter</a></li>";
        else
             $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$counter.")'>$counter</a></li>"; 
        }
        }
        elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
        {
        //close to beginning; only hide later pages
        if($page < 1 + ($adjacents * 2)){
            for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)  {
                if ($counter == $page)
                    $json_arr['customer_list_paging'].= " <li class='page-item active' ><a   class='page-link '  >$counter</a></li>";
                else
                    $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$counter.")'>$counter</a></li>"; 
            }
                $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' >...</li>";
                $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$lpm1.")'>$lpm1</a></li>";
                $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link'  onclick='gotoOffsetCustomer(".$lastpage.")'>$lastpage</a></li>"; 
        }
        //in middle; hide some front and some back
        elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(1)'>1</a></li>";
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(2)'>2</a></li>";
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' >...</li>";
        for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++) {
            if ($counter == $page)
                $json_arr['customer_list_paging'].= " <li class='page-item active' ><a  class='page-link'  >$counter</a></li>";
            else
                $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$counter.")' >$counter</a></li>"; 
        }
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' >...</li>";
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$lpm1.")' >$lpm1</a></li>";
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$lastpage.")' >$lastpage</a></li>"; 
        }
        //close to end; only hide early pages
        else {
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(1)' >1</a></li>";
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(2)' >2</a></li>";
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' >...</li>";
        for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; 
             $counter++)
        {
            if ($counter == $page)
                $json_arr['customer_list_paging'].= " <li class='page-item active'  ><a  class='page-link '  >$counter</a></li>";
            else
                $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$counter.")' >$counter</a></li>"; 
        }
        }
        }
        
        //next button
        if ($page < $counter - 1) 
            $json_arr['customer_list_paging'].= " <li class='page-item' style='cursor:pointer' ><a class='page-link' onclick='gotoOffsetCustomer(".$next.")' hidden>Next</a></li>";
        else
            $json_arr['customer_list_paging'].= ""; 
        }

        
        
        // $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='gotoOffsetCustomer(".$goto_offset.")' ";
        //     }
        //     // $json_arr['customer_list_paging'] =  $json_arr['customer_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($total < 1){
            $json_arr['customer_list_paging']  = "No Record";
        }
        // $json_arr['grid_offset'] =  $goto_offset;
        
        while($row = $exec_query->fetch_assoc()) {

            $get_image_query = "SELECT vi.name FROM vehicle v
                        INNER JOIN vehicle_images vi
                        ON v.id = vi.vehicle_id 
                        INNER JOIN source_company_dealer scd 
                        ON v.company_dealer_id = scd.id 
                        WHERE 1 
                        AND v.customer_record_id ='".$row['id']."'
                        AND scd.company_id IN (".$granted_company_ids.")
                        AND vi.name <> 'default.png' 
                        AND vi.status <> 0
                        ORDER BY v.date_uploaded DESC LIMIT 1";
                        // echo  $get_image_query;
        $get_image_name= $db -> select($get_image_query);
         if($get_image_name <> ''){     
                $row['photo'] = $get_image_name;
         }
         


            $mask_email =  $utility->isNotEmpty($row['email_1']) ? $utility->mask_email($row['email_1']) : '<span style="color: red;">(Not Provided)</span>';
         if(isset($_SESSION['user']['fields_permissions']['customer_fields']['fields_email_1'])){
            if($_SESSION['user']['fields_permissions']['customer_fields']['fields_email_1']){
                $mask_email = $row['email_1'];
            }
         }

         $mask_mobile = $utility->isNotEmpty($row['mobile_phone_1']) ? $utility->mask($row['mobile_phone_1'],4,1) : '';
         if(isset($_SESSION['user']['fields_permissions']['customer_fields']['fields_mobile_1'])){
            if($_SESSION['user']['fields_permissions']['customer_fields']['fields_mobile_1']){
                $mask_mobile = $row['mobile_phone_1'];
            }
         }

         $mask_landline = $utility->isNotEmpty($row['landline']) ? $utility->mask($row['landline'],2,1) : '';
         if(isset($_SESSION['user']['fields_permissions']['customer_fields']['fields_landline'])){
            if($_SESSION['user']['fields_permissions']['customer_fields']['fields_landline']){
                $mask_landline = $row['landline'];
            }
         }

 $utility->make_thumb('../../dist/img/customer/'.$row['photo'], '../../dist/img/thumbnail/'.$row['photo'], 150);
 
            $grid_customer_master_id = $row['id']; 
            $grid_date_created = $row['date_created']; 
            $grid_name = $row['name']; 
            $grid_type =(intval($row['type']) === 1) ? 'Individual' : 'Corporation';
            $grid_photo = $row['photo']; 
            $grid_age_number = $row['age_number']; 
            $grid_email_1 = $mask_email;
            $grid_mobile_phone_1 =  intval($row['type']) === 2 ? ($utility->isNotEmpty($mask_landline) ? $mask_landline : $mask_mobile ) : $mask_mobile;

            $json_arr['customer_list'] = $json_arr['customer_list']."<div class='col-md-4 d-flex align-items-stretch'><div class='card bg-light' style='width:100%'>"./*$grid_date_created.*/"<div class='card-body mt-2 pt-0' style='padding: 1.00rem'><div class='row'><div class='col-8'><h2 class='lead mb-1'>     <p class=' text-md mb-1'><b>$grid_name</b>  </p>   </h2><p class='text-muted text-sm mb-1'><b>Type: </b> ".$grid_type." </p>   <!-- <p class='text-muted text-sm mb-1'><b>Age: </b> ".$grid_age_number." </p> --> <ul class='ml-4 mb-0 fa-ul text-muted'><li class='small'><span class='fa-li'><i class='fas fa-envelope'></i></span> Email: <a href='mailto:".$grid_email_1."' >".$grid_email_1."</a></li><li class='small'><span class='fa-li'><i class='fas fa-lg fa-phone'></i></span> Mobile: <a href='tel:".$grid_mobile_phone_1."' >".$grid_mobile_phone_1."</a></li></ul></div><div class='col-4 text-center'><img src='dist/img/customer/".$grid_photo."' alt='logo' class='img-circle img-fluid img' style='width:90px; height:90px;'>  </div></div></div><div class='card-footer' style='padding: .25rem;' ><div class='text-right'><a onclick='customerInfo(\"".$grid_customer_master_id."\",\"".$grid_type."\")' class='btn btn-sm btn-primary'><i class='fas fa-info'></i> View </a></div></div></div></div>";
            $data_counter++;
        }
    }else{

    while($row = $exec_query->fetch_assoc()) {

            $get_image_query = "SELECT vi.name FROM vehicle v
                        INNER JOIN vehicle_images vi
                        ON v.id = vi.vehicle_id 
                        INNER JOIN source_company_dealer scd 
                        ON v.company_dealer_id = scd.id 
                        WHERE 1 
                        AND v.customer_record_id ='".$row['id']."'
                        AND scd.company_id IN (".$granted_company_ids.")
                        AND vi.name <> 'default.png'  
                        AND vi.status <> 0
                        ORDER BY v.id DESC LIMIT 1";
                        // echo  $get_image_query;
        $get_image_name= $db -> select($get_image_query);
         if($get_image_name <> ''){     
                $row['photo'] = $get_image_name;
         }

         $mask_email =  $utility->isNotEmpty($row['email_1']) ? $utility->mask_email($row['email_1']) : '';
         if(isset($_SESSION['user']['fields_permissions']['customer_fields']['fields_email_1'])){
            if($_SESSION['user']['fields_permissions']['customer_fields']['fields_email_1']){
                $mask_email = $row['email_1'];
            }
         }

         $mask_mobile = $utility->isNotEmpty($row['mobile_phone_1']) ? $utility->mask($row['mobile_phone_1'],4,1) : '';
         if(isset($_SESSION['user']['fields_permissions']['customer_fields']['fields_mobile_1'])){
            if($_SESSION['user']['fields_permissions']['customer_fields']['fields_mobile_1']){
                $mask_mobile = $row['mobile_phone_1'];
            }
         }

     $mask_landline = $utility->isNotEmpty($row['landline']) ? $utility->mask($row['landline'],2,1) : '';
         if(isset($_SESSION['user']['fields_permissions']['customer_fields']['fields_landline'])){
            if($_SESSION['user']['fields_permissions']['customer_fields']['fields_landline']){
                $mask_landline = $row['landline'];
            }
         }


        
         $utility->make_thumb('../../dist/img/customer/'.$row['photo'], '../../dist/img/thumbnail/'.$row['photo'], 150);
        
        $json_arr['rows'] = array_merge(
            $json_arr['rows'],
            array( array('photo'=>$row['photo'],
            'id'=>$row['id'],
            'name'=>$row['name'],
            'age_number'=>$row['age_number'],
            'gender_name'=>$row['gender_name'],
            'email_1'=> $mask_email ,
            'mobile_phone_1'=> intval($row['type']) === 2 ? ($utility->isNotEmpty($mask_landline) ? $mask_landline : $mask_mobile ) : $mask_mobile,
            'type'=>(intval($row['type']) === 1) ? 'Individual' : 'Corporation' ))) ;
            $data_counter++;
    }
}
 
if($data_counter > 0  ){
    $json_arr['total'] = ($data_counter < $limit) ? $data_counter : 100; //total number of result  
}else{
    $json_arr['total'] = 0;
}

echo json_encode($json_arr);
 
function isNotEmpty($data){
	return preg_match('/\S/', $data);
}

?>