';print_r($_SESSION['user']);echo'';exit;
$user_dealer_id = (isset($_SESSION['user']['dealer']) ? $_SESSION['user']['dealer'] : 0);
$employee_id = $_SESSION['user']['employee_id'];
// $employee_role_id = $_SESSION['user']['employee_role_id'];
$my_customer_record_id = $_SESSION['user']['id'];
$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['apiKey']) && base64_decode($_REQUEST['apiKey']) == 'ZmtDl9SkyB') {
$company_id = $_REQUEST['cid'];
// echo"SELECT scd.company_id, sd.id as dealer_id, sd.name as dealer_name, sd.code as dealer_code
// FROM source_company_dealer scd
// INNER JOIN source_dealer sd ON sd.id = scd.dealer_id
// WHERE scd.company_id = '$company_id' AND sd.status = 1 AND sd.active = 1
// ORDER BY sd.name";exit;
// Adjusting query to retrieve dealers based on selected company ID
$dealer_query = $db->connect()->query("
SELECT scd.company_id, sd.id as dealer_id, sd.name as dealer_name, sd.code as dealer_code
FROM source_company_dealer scd
INNER JOIN source_dealer sd ON sd.id = scd.dealer_id
WHERE scd.company_id = '$company_id' AND sd.status = 1 AND sd.active = 1 group by sd.id
ORDER BY sd.name
");
$dealers_ = [];
if ($dealer_query->num_rows > 0) {
while ($row = $dealer_query->fetch_assoc()) {
$dealers_[] = [
'dealer_id' => $row['dealer_id'],
'dealer_name' => $row['dealer_name'],
'dealer_code' => $row['dealer_code']
];
}
}
// var_dump($dealers_);
// Return the dealers data as JSON
echo json_encode($dealers_);
exit;
}
if(isset($_REQUEST['search'])){
$search = $db -> escape(trim($_REQUEST['search']));
// $primary_search = $search_condition = (strlen($search) > 0 ? " AND s.conduction_sticker LIKE '%" . $search . "%' OR CONCAT(c.first_name,' ',c.last_name) LIKE '%".$search."' " : "");
$primary_search = $search_condition = (strlen($search) > 0
? " AND (s.conduction_sticker LIKE '%" . $search . "%'
OR CONCAT(c.first_name, ' ', c.last_name) LIKE '%" . $search . "%'
OR c.corporation_name LIKE '%" . $search . "%'
OR c.first_name LIKE '%" . $search . "%'
OR c.last_name LIKE '%" . $search . "%')"
: "");
}else{
$search="";
}
if(isset($_REQUEST['offset'])){
$offset = $db -> escape(trim($_REQUEST['offset']));
}else{
$offset="";
}
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 = "";
}
$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;
// }
// }
$date_filter = " AND s.activity_date >= '2024-09-01'";
// Check if start_date and end_date are provided in the request
if(isset($_REQUEST['start_date']) && isset($_REQUEST['end_date'])){
$start_date = $db->escape(trim($_REQUEST['start_date']));
$end_date = $db->escape(trim($_REQUEST['end_date']));
// Validate dates
if (!empty($start_date) && !empty($end_date)) {
// Add the date filter to the query
$date_filter = " AND s.activity_date BETWEEN '$start_date' AND '$end_date'";
}
}
$ltms_filter = "";
// Check if LTMS filter is provided in the request
if (isset($_REQUEST['ltms_status']) && $_REQUEST['ltms_status'] !== "") {
$ltms_status = $db->escape(trim($_REQUEST['ltms_status']));
// If LTMS status is 1 (Has LTMS)
if ($ltms_status == '1') {
$ltms_filter = " AND EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id)";
}
// If LTMS status is 0 (No LTMS)
elseif ($ltms_status == '0') {
$ltms_filter = " AND NOT EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id)";
}
}
$dealer_filter ="";
if (isset($_REQUEST['did']) && $_REQUEST['did'] !== "") {
$dealer_id = $db->escape(trim($_REQUEST['did']));
$dealer_filter = " AND scd.dealer_id ='$dealer_id' ";
}
$company_filter ="";
if (isset($_REQUEST['cid']) && $_REQUEST['cid'] !== "") {
$company_id = $db->escape(trim($_REQUEST['cid']));
$company_filter = " AND scd.company_id ='$company_id' ";
}
$data_counter = 0;
$json_arr['rows'] = array();
// $group_bys =" GROUP BY cua.user_id ORDER BY cua.id" ;
$query ="SELECT DISTINCT %s
FROM sales as s
LEFT JOIN vehicle AS v ON s.vehicle_id = v.id
LEFT JOIN customer AS c ON v.customer_record_id = c.id
LEFT JOIN customer_contact as cc ON c.id = cc.customer_id
LEFT JOIN source_company_dealer AS scd ON v.company_dealer_id = scd.id
LEFT JOIN source_company as sc ON scd.company_id = sc.id
LEFT JOIN source_dealer as sd ON scd.dealer_id = sd.id
WHERE 1 %s $date_filter $ltms_filter $company_filter $dealer_filter ORDER BY s.id DESC ";
$fields ="s.id,v.conduction_sticker as cs_number,s.activity_date,s.deal_date,s.sales_person,s.upload_by,s.vehicle_id,
v.id AS vid,v.customer_record_id,c.id as customer_id,sc.code as company_code, sc.name as company_name,sd.code as dealer_code,sd.name as delear_name,cc.mobile_phone_1,
c.corporation_id,c.corporation_name,c.first_name,c.middle_name,c.last_name,c.date_of_birth,c.type, IF(EXISTS (SELECT 1 FROM customer_ltms cl WHERE cl.customer_id = c.id), 1, 0) AS has_tag_ltms";
$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,$primary_search)." $offset_limit";
// echo($final_query);exit;
$exec_query = $db -> sql_query($final_query);
$query_count = $db->select("SELECT COUNT(DISTINCT s.id) as total_count
FROM sales as s
LEFT JOIN vehicle AS v ON s.vehicle_id = v.id
LEFT JOIN customer AS c ON v.customer_record_id = c.id
LEFT JOIN customer_contact as cc ON c.id = cc.customer_id
LEFT JOIN source_company_dealer AS scd ON v.company_dealer_id = scd.id
LEFT JOIN source_company as sc ON scd.company_id = sc.id
LEFT JOIN source_dealer as sd ON scd.dealer_id = sd.id
WHERE 1 $date_filter $ltms_filter $company_filter $dealer_filter");
// $query_count = $db -> select("SELECT COUNT(*) FROM ( ".sprintf($query,$fields,$primary_search)." ) as total_count");
$json_arr['total'] = $query_count; //total number of result
if(isset($_REQUEST['grid'])){
$json_arr['customer_list'] = "";
$json_arr['customer_list_paging'] = "";
$total = intval($query_count);//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'].= " Prev";
}
if ($lastpage < 7 + ($adjacents * 2)){
for ($counter = 1; $counter <= $lastpage; $counter++){
if ($counter == $page)
$json_arr['customer_list_paging'].= " $counter";
else
$json_arr['customer_list_paging'].= " $counter";
}
}
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'].= " $counter";
else
$json_arr['customer_list_paging'].= " $counter";
}
$json_arr['customer_list_paging'].= " ...";
$json_arr['customer_list_paging'].= " $lpm1";
$json_arr['customer_list_paging'].= " $lastpage";
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
$json_arr['customer_list_paging'].= " 1";
$json_arr['customer_list_paging'].= " 2";
$json_arr['customer_list_paging'].= " ...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++) {
if ($counter == $page)
$json_arr['customer_list_paging'].= " $counter";
else
$json_arr['customer_list_paging'].= " $counter";
}
$json_arr['customer_list_paging'].= " ...";
$json_arr['customer_list_paging'].= " $lpm1";
$json_arr['customer_list_paging'].= " $lastpage";
}
//close to end; only hide early pages
else {
$json_arr['customer_list_paging'].= " 1";
$json_arr['customer_list_paging'].= " 2";
$json_arr['customer_list_paging'].= " ...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage;
$counter++)
{
if ($counter == $page)
$json_arr['customer_list_paging'].= " $counter";
else
$json_arr['customer_list_paging'].= " $counter";
}
}
}
//next button
if ($page < $counter - 1)
$json_arr['customer_list_paging'].= " Next";
else
$json_arr['customer_list_paging'].= "";
}
if($total < 1){
$json_arr['customer_list_paging'] = "No Record";
}
}else{
$users_dealer='';
$allowed_access_ = (isset($global_action_permissions['12']['2']) && in_array("1", $global_action_permissions['12']['2']) ? : false);
while($row = $exec_query->fetch_assoc()) {
$json_arr['rows'] = array_merge(
$json_arr['rows'],
array( array(
'company_dealer' => $row['company_code'].' - '.$row['dealer_code'],
'company_code' =>$row['company_code'],
'company_name' => $row['company_name'],
'dealer_code' => $row['dealer_code'],
'dealer_name' => $row['delear_name'],
'mobile_phone_1' => ($allowed_access_ ? $row['mobile_phone_1'] : '***********'),
'cs_number' => $row['cs_number'],
'sales_customer_name' => ($row['type'] == 1 ? $row['first_name'].' '.$row['middle_name'].' '.$row['last_name'] : $row['corporation_name']),
'activity_date' => date('F d, Y', strtotime($row['activity_date'])),
'has_tag_ltms' => ($row['has_tag_ltms'] ==1 ? 'Uploaded' : 'No LTMS')
// // 'ref_code' => (isset($row['ref_code']) ? $row['ref_code'] : ''),
// 'customer_name' => $row['fullname'],
// 'file' => $row['filename'].'
date upload : '.date('F d ,Y', strtotime($row['created_at'])).'',
// 'date_uploaded' => $row['created_at'],
// 'iview' => 'View Image'
// 'iview' => 'View ',
)
)
);
unset($row);
$users_dealer ='';
$data_counter++;
}
unset($user_row);
unset($row);
}
// if($data_counter > 0 ){
// $json_arr['total'] = ($data_counter < $limit) ? $data_counter : $query_count;// $query_count;//100; //total number of result
// }else{
// $json_arr['total'] = 0;
// }
echo json_encode($json_arr);
function isNotEmpty($data){
return preg_match('/\S/', $data);
}
?>