escape(trim($_GET['search']));
}else{
$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 = "";
}
$type = $_GET['type'];
if($type == 1){
$request_status = $db -> escape(trim($_GET['filter_data']));
$json_arr['rows'] = array();
switch(intval($request_status)){
case 0: //pendings
$status = " AND (
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) = 0 ,
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 0 ";
break;
case 1: //approves
$status = " AND rs.status = 1 /*AND (
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 1 */ ";
break;
case 2: //declined
$status = " AND (
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 2 ";
break;
case 3: //expired
$status = " AND (SELECT DATEDIFF(tb.expire,NOW()) < 0) ";
break;
default:
$status = "";
}
$owner_employee_id = $_SESSION['user']['id'];
$employee_query = "SELECT %s FROM `text_blast` tb
INNER JOIN employee e
ON tb.employee_id = e.id
INNER JOIN request_status rs
ON tb.id = rs.request_id
WHERE rs.`employee_id` = '$owner_employee_id'
AND concat(tb.id, tb.control_number) LIKE '%%%s%%'
%s /* status filter */
ORDER BY tb.request_date DESC, tb.request_time DESC";
$count = " count(tb.id) ";
$fields = " concat(e.first_name,' ',e.last_name) as requestor, tb.id, tb.control_number, tb.description, DATE_FORMAT(tb.request_date, '%M %d, %Y') as request_date, DATE_FORMAT(tb.expire, '%M %d, %Y') as expire, tb.`message_content`,
(
IF(rs.status <> 2,
IF(rs.status <> 0,
IF(rs.status <> 4,
IF(rs.status = 3,
'Closed'
,
'Approved'
)
,
'Has suggestions'
)
,
'Pending'
)
,
'Declined'
)
)
as status";
// $fields = " concat(e.first_name,' ',e.last_name) as requestor,r.id,r.control_number,DATE_FORMAT(r.report_date, '%M %d, %Y') as report_date,rt.name, DATE_FORMAT(r.expire, '%M %d, %Y') as expire,r.`description`,
// (
// IF(
// (SELECT DATEDIFF(r.expire,NOW()) < 0),'Expired',
// IF(
// (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 ,
// IF(
// (SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 ,
// 'Approved',
// 'Pending'
// ),
// 'Declined'
// )
// )
// )
// as status";
// echo (sprintf($employee_query, $fields, $search, $status)." $offset_limit"); return;
$empoloyee_list_query = $db -> sql_query(sprintf($employee_query, $fields, $search, $status)." $offset_limit"); // echo sprintf($employee_query, $fields, $search, $status); exit;
// $empoloyee_list_query_filter_count = $empoloyee_list_query -> num_rows;
$employee_list_query_count = $db -> select(sprintf($employee_query,$count,$search,$status));
while($row = $empoloyee_list_query->fetch_assoc()) {
$json_arr['rows'] = array_merge($json_arr['rows'], array( array(
'id'=>$row['id'],
'control_number'=>$row['control_number'],
'date'=>$row['request_date'],
'description'=>$row['description'],
'message_content'=>$row['message_content'],
'status'=>$row['status'],
'requestor'=>$row['requestor'],
))) ;
}
$json_arr['total'] = $employee_list_query_count; //total number of result
echo json_encode($json_arr);
}
else if($type == 2){
$request_id = $_GET['request_id'];
$request_parameters_query = "SELECT tb.`id`, tb.`control_number`, CONCAT(e.first_name,' ',e.middle_name,' ',e.last_name) as request_by, CONCAT(DATE_FORMAT(tb.`request_date`, '%M %d, %Y'), ' - ' ,TIME_FORMAT(tb.`request_time`, '%h:%i %p')) as request_date_time, rt.name as request_type, tb.request_type_id, tb.`dms_id`,
IF(
tb.`dms_id` = '0','All', (SELECT name FROM source_dms WHERE id = tb.dms_id)
) AS dms_name,
tb.`company_id`,
IF(
tb.`company_id` = '0','All', (SELECT code FROM source_company WHERE id = tb.company_id)
) AS company_name,
tb.`dealer_id`,
IF(
tb.`dealer_id` = '0','All', (SELECT code FROM source_dealer WHERE id = tb.dealer_id)
) AS dealer_name,
tb.`customer_type` as customer_type_id,
IF(`customer_type` = '0',
'All'
,
IF(
`customer_type` = '1', 'Individual', 'Corporation'
)
) AS customer_type,
tb.`customer_category` as customer_category_id,
IF(
`customer_category` = '0','All', (SELECT name FROM customer_category WHERE id = tb.customer_category)
) as customer_category,
tb.`customer_status` as customer_status_id,
IF(
`customer_status` = '0','All',
IF(
`customer_status` = '1', 'Active', 'Inactive'
)
) as customer_status,
tb.`activity_date_from`, tb.`activity_date_to`,
IF(
tb.`activity_date_from` <> '' AND tb.`activity_date_to` <> '' ,CONCAT(tb.`activity_date_from`, ' - ', tb.`activity_date_to`), ''
) as activity_between,
tb.`gender_id`,
IF(
tb.`gender_id` = '0','All',
(SELECT gender_name FROM all_gender WHERE id = tb.gender_id)
) as gender_name , tb.`age_from`, tb.`age_to`,
IF(
tb.`age_from` <> '' AND tb.`age_to` <> '' ,CONCAT(tb.`age_from`, ' - ',tb.`age_to`), ''
) as age_between, tb.state_id,
IF(
tb.`state_id` = '0','All',
(SELECT `provDesc` FROM `all_state` WHERE `provCode` = tb.`state_id` LIMIT 1 )
) as state_name , tb.city_id,
IF(
tb.`city_id` = '0','All',
(SELECT `citymunDesc` FROM `all_city` WHERE `id` = tb.`city_id`)
) as city_name, tb.`uploaded_date_from`, tb.`uploaded_date_to`,
IF(
tb.`uploaded_date_from` <> '' AND tb.`uploaded_date_to` <> '' , CONCAT(tb.`uploaded_date_from`, ' - ', tb.`uploaded_date_to`), ''
) as upload_between,
tb.`upload_by_employee_id`,
IF(
tb.`upload_by_employee_id` = '','',
(SELECT CONCAT(first_name,' ',middle_name,' ',last_name) FROM `employee` WHERE `id` = tb.`upload_by_employee_id`)
) as upload_by , tb.`customer_data_type`,
IF(
tb.`customer_data_type` = '0','',
tb.`customer_data_type`
) as data_type,
tb.message_content,
tb.`brand_id`,
IF(
tb.`brand_id` = '0','All',
(SELECT name FROM source_brand WHERE id = tb.brand_id)
) as brand_name,
tb.`model_id`,
IF(
tb.`model_id` = '0','All',
(SELECT name FROM source_car_model WHERE id = tb.model_id)
) as model_name,
tb.`variant_id`,
IF(
tb.`variant_id` = '0','All',
(SELECT name FROM source_car_model_variant WHERE id = tb.variant_id)
) as variant_name,
tb.sort_by, tb.order_by, tb.date_of_birth,
tb.insurance_company_id,
tb.insurance_type_id,
tb.finance_company_id,
IF(
tb.`insurance_company_id` = '0','All',
(SELECT name FROM source_insurance_company WHERE id = tb.insurance_company_id)
) as insurance_company,
IF(
tb.`insurance_type_id` = '0','All',
(SELECT name FROM source_insurance_type WHERE id = tb.insurance_type_id)
) as insurance_type,
IF(
tb.`finance_company_id` = '0','All',
(SELECT name FROM source_finance_company WHERE id = tb.finance_company_id)
) as finance_company,
tb.record_count,
tb.company_list
FROM `text_blast` tb
INNER JOIN employee e
ON tb.employee_id=e.id
INNER JOIN request_type rt
ON tb.`request_type_id`=rt.id
WHERE tb.id = '".$request_id."' ";
$request_data = $db->sql_query($request_parameters_query);
foreach($request_data AS $data){
$request_session_data['control_number'] = isNotEmpty($data['control_number']) ? $data['control_number'] : 'N/A' ;
$request_session_data['request_by'] = isNotEmpty($data['request_by']) ? $data['request_by'] : 'N/A' ;
$request_session_data['request_date_time'] = isNotEmpty($data['request_date_time']) ? $data['request_date_time'] : 'N/A' ;
$request_session_data['request_type'] = isNotEmpty($data['request_type']) ? $data['request_type'] : 'N/A' ;
$request_session_data['request_type_id'] = isNotEmpty($data['request_type_id']) ? $data['request_type_id'] : 'N/A' ;
$request_session_data['request_between'] = isNotEmpty($data['activity_between']) ? $data['activity_between'] : 'N/A' ;
$request_session_data['dms_name'] = isNotEmpty($data['dms_name']) ? $data['dms_name'] : 'N/A' ;
$request_session_data['company_name'] = isNotEmpty($data['company_name']) ? $data['company_name'] : 'N/A' ;
$request_session_data['dealer_name'] = isNotEmpty($data['dealer_name']) ? $data['dealer_name'] : 'N/A' ;
$request_session_data['customer_type'] = isNotEmpty($data['customer_type']) ? $data['customer_type'] : 'N/A' ;
$request_session_data['customer_status'] = isNotEmpty($data['customer_status']) ? $data['customer_status'] : 'N/A' ;
$request_session_data['customer_category'] = isNotEmpty($data['customer_category']) ? $data['customer_category'] : 'N/A' ;
$request_session_data['gender_name'] = isNotEmpty($data['gender_name']) ? $data['gender_name'] : 'N/A' ;
// $report_session_data['brand_name'] = isNotEmpty($data['brand_name']) ? $data['brand_name'] : 'N/A' ;
// $report_session_data['model_name'] = isNotEmpty($data['model_name']) ? $data['model_name'] : 'N/A' ;
$request_session_data['brand_name'] = ($data['brand_name'] <> '0' ) ? $data['brand_name'] : 'All' ;
$request_session_data['model_name'] = ($data['model_name'] <> '0') ? $data['model_name'] : 'All' ;
$request_session_data['variant_name'] = ($data['variant_name'] <> '0') ? $data['variant_name'] : 'All' ;
$request_session_data['age_between'] = isNotEmpty($data['age_between']) ? $data['age_between'] : 'N/A' ;
$request_session_data['state_name'] = isNotEmpty($data['state_name']) ? $data['state_name'] : 'N/A' ;
$request_session_data['city_name'] = isNotEmpty($data['city_name']) ? $data['city_name'] : 'N/A' ;
$request_session_data['upload_between'] = isNotEmpty($data['upload_between']) ? $data['upload_between'] : 'N/A' ;
$request_session_data['upload_by'] = isNotEmpty($data['upload_by']) ? $data['upload_by'] : 'N/A' ;
$request_session_data['data_type'] = isNotEmpty($data['data_type']) ? $data['data_type'] : 'N/A' ;
$request_session_data['date_of_birth'] = isNotEmpty($data['date_of_birth']) ? $data['date_of_birth'] : 'N/A' ;
$request_session_data['message_content'] = isNotEmpty($data['message_content']) ? $data['message_content'] : 'N/A' ;
$request_session_data['insurance_company'] = isNotEmpty($data['insurance_company']) ? $data['insurance_company'] : 'N/A' ;
$request_session_data['insurance_type'] = isNotEmpty($data['insurance_type']) ? $data['insurance_type'] : 'N/A' ;
$request_session_data['finance_company'] = isNotEmpty($data['finance_company']) ? $data['finance_company'] : 'N/A' ;
$request_session_data['record_count'] = isNotEmpty($data['record_count']) ? $data['record_count'] : '0' ;
// $request_session_data['customer_company_dealer'] = $data['company_name'] . "/" . $data['dealer_name'];
$dms_id = $data['dms_id'];
$company_id = $data['company_id'];
$dealer_id = $data['dealer_id'];
$age_from = $data['age_from'];
$age_to = $data['age_to'];
$gender_id = $data['gender_id'];
$state_id = $data['state_id'];
$city_id = $data['city_id'];
$brand_id = $data['brand_id'];
$model_id = $data['model_id'];
$variant_id = $data['variant_id'];
$insurance_company_id = $data['insurance_company_id'];
$insurance_type_id = $data['insurance_type_id'];
$finance_company_id = $data['finance_company_id'];
$upload_by_id = $data['upload_by_employee_id'];
$customer_data_type = $data['customer_data_type'];
$customer_type = $data['customer_type_id'];
$customer_category = $data['customer_category_id'];
$customer_status = $data['customer_status_id'];
$sort_by = $data['sort_by'];
$order_by = $data['order_by'];
$company_list = $data['company_list'];
//dates format
$activity_date_from = fix_date($data['activity_date_from'], false);
$activity_date_to = fix_date($data['activity_date_to'],false);
$upload_date_from = fix_date($data['uploaded_date_from'], false);
$upload_date_to = fix_date($data['uploaded_date_to'],false);
$filter_date_of_birth = fix_date($data['date_of_birth'],false);
$request_count_data = getFilteredCustomerData(
$company_list,
$insurance_company_id,
$insurance_type_id,
$finance_company_id,
$request_session_data['control_number'],
$customer_category,
$customer_status,
$filter_date_of_birth,
$order_by,
$sort_by,
$brand_id,
$model_id,
$variant_id,
$customer_type,
$customer_data_type,
$upload_by_id,
$state_id,
$city_id,
$gender_id,
$age_from,
$age_to,
$dms_id,
$company_id,
$dealer_id,
$activity_date_from,
$activity_date_to,
$upload_date_from,
$upload_date_to,
$offset_limit,
$db);
}
}
function isNotEmpty($data){
return preg_match('/\S/', $data);
}
function fix_date($data,$format){
if(!isNotEmpty($data)){
return "";
}
// 01/23/2020 false
// 23/01/2020 true
$array_date = explode("/", explode(" ", $data)[0]);
if($format){ //if format == true swap index 1 and 2
$return_date = $array_date[2].'-'.$array_date[1].'-'.$array_date[0];
return $return_date;
}else{
$return_date = $array_date[2].'-'.$array_date[0].'-'.$array_date[1];
return $return_date;
}
}
function getFilteredCustomerData($company_list, $insurance_company_id, $insurance_type_id, $finance_company_id, $control_number, $customer_category, $customer_status, $filter_date_of_birth, $order_by, $sort_by, $brand_id, $model_id, $variant_id, $customer_type, $customer_data_type, $upload_by_id, $state_id, $city_id, $gender_id, $age_from, $age_to, $dms_id, $company_id, $dealer_id, $activity_date_from, $activity_date_to, $upload_date_from, $upload_date_to, $offset_limit, $db){
if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){
$filter_date_upload = " AND c.date_uploaded BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') ";
} else {
$filter_date_upload = "";
}
if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){
$filter_date_created = " AND c.date_created BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') ";
} else {
$filter_date_created = "";
}
if(isNotEmpty($age_from) && isNotEmpty($age_to)){
$filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' ";
}else{
$filter_age = "";
}
if(isNotEmpty($upload_by_id)){
$filter_upload_by = " AND c.upload_by = '$upload_by_id' ";
}else{
$filter_upload_by = "";
}
$company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ;
$dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ;
$gender =($gender_id != '0') ? " AND c.gender_id = '".$gender_id."' " : "" ;
$dms =($dms_id != '0') ? " AND sc.dms_id = '".$dms_id."' " : "" ;
$state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ;
$city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ;
$customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ;
$customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ;
$customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ;
//default
$brand = ($brand_id != '0') ? " AND v.brand_id = '$brand_id' " : "" ;
$model = ($model_id != '0') ? " AND v.model_id = '$model_id' " : "" ;
$variant = ($variant_id != '0') ? " AND v.model_variant_description = '$variant_id' " : "";
$complete_data = "";
$incomplete_data = "";
$mobile_phone_1 = "";
$email_1 = "";
$address_1 = "";
$date_of_birth ="";
if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){
switch($customer_data_type){
case "Complete Data":
$complete_data = " AND ((c.date_of_birth <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1)
OR
(cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) ";
break;
case "Incomplete Data":
$incomplete_data = " AND ((c.date_of_birth = '' OR
ag.id = 0 OR
cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.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 ag.gender_id = '0' AND c.type = '1' ";
break;
case "No Date of birth":
$date_of_birth = " AND c.date_of_birth = '' AND c.type = '1' ";
break;
}
}
$insurance_company = ($insurance_company_id != '0') ? " AND (s.insurance_company_id = '$insurance_company_id') " : "" ;
$insurance_type = ($insurance_type_id != '0') ? " AND (s.insurance_type_id = '$insurance_type_id') " : "" ;
$finance_company = ($finance_company_id != '0') ? " AND (s.finance_company_id = '$finance_company_id') " : "" ;
if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){
$filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' ";
}else{
$filter_dob = "";
}
switch(intval($sort_by)){
case 1: //lastname
$filter_sort_by = " c.last_name ";
break;
case 2: //firstname
$filter_sort_by = " c.first_name ";
break;
case 3: //corporation name
$filter_sort_by = " c.corporation_name ";
break;
case 4: //date created
$filter_sort_by = " c.date_created ";
break;
case 5: //date uploaded
$filter_sort_by = " c.date_uploaded ";
break;
}
$return_data = array();
$query = " SELECT %s
FROM customer c
INNER JOIN customer_category ccat
ON c.category_id = ccat.id
INNER JOIN customer_contact cc
ON c.id = cc.customer_id
INNER JOIN employee e
ON c.upload_by = e.id
INNER JOIN customer_dms cd
ON c.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_dealer sd
ON scd.dealer_id = sd.id
INNER JOIN all_gender ag
ON c.gender_id = ag.id
INNER JOIN all_marital_status am
ON c.marital_status_id = am.id
INNER JOIN all_countries ac
ON c.nationality_id = ac.num_code
INNER JOIN all_suffix asx
ON c.suffix_id = asx.id
LEFT OUTER JOIN vehicle v
ON c.id = v.customer_record_id
LEFT OUTER JOIN sales s
ON v.id = s.vehicle_id
LEFT OUTER JOIN service se
ON v.id = se.vehicle_id
WHERE 1
%s /* upload_date */
%s /* date_created */
%s /* dms */
%s /* company */
%s /* dealer */
%s /* age */
%s /* gender */
%s /* state */
%s /* city */
%s /* upload_by */
%s /* complete_data */
%s /* incomplete_id */
%s /* no_address */
%s /* no_dob */
%s /* type */
%s /* dob */
%s /* brand */
%s /* model */
%s /* insurance_company */
%s /* insurance_type */
%s /* finance_company */
AND c.status = 1
GROUP BY c.id
%s
%s
ORDER BY $filter_sort_by $order_by ";
// LEFT OUTER JOIN source_brand sbv
// ON v.brand_id=sbv.id
// LEFT OUTER JOIN source_car_model scmv
// ON v.model_id=scmv.id
// LEFT OUTER JOIN source_brand sbs
// ON s.brand_id=sbs.id
// LEFT OUTER JOIN source_car_model scms
// ON s.model_id=scms.id
// LEFT OUTER JOIN source_brand sbse
// ON se.brand_id=sbse.id
// LEFT OUTER JOIN source_car_model scmse
// ON se.model_id=scmse.id
$fields = "
c.id,
c.photo,
IF(c.type = 1,
concat(c.first_name,' ',c.middle_name,' ',c.last_name, ' ', IF(c.suffix_id < 1, '',asx.suffix_name ))
,
c.corporation_name
) AS name,
am.name as marital_status,
ag.gender_name,
c.date_of_birth,
'Individual' as customer_type,
c.date_uploaded,
TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) as age_number,
c.date_created,
cc.mobile_phone_1,
cc.mobile_phone_2,
cc.email_1,
cc.email_2,
cc.business_phone,
cc.fax_phone,
cc.residential_phone,
cc.address_1,
c.contact_person,
ac.nationality,
c.occupation,
c.no_of_children,
sc.code AS company,
sd.name AS dealer,
concat(e.first_name,' ',e.last_name) as upload_by,
c.type,
(IF(c.category_id <> 0,
ccat.id
,
IF(COUNT(se.id) > 0 && COUNT(s.id) < 1,
'1'
,
IF(COUNT(se.id) < 1 && COUNT(s.id) > 0,
'2'
,
IF(COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0,
'3'
,
IF(COUNT(v.id) > 1 && COUNT(v.id) < 4,
'4'
,
IF(COUNT(v.id) > 3,
'5'
,
'0'/*VVIP*/
)
)
)
)
)
)) as customer_category,
IF((SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365),
'1'
,
'2'
)
AS isActive";
$count = "
(IF(c.category_id <> 0,
ccat.id
,
IF(COUNT(se.id) > 0 && COUNT(s.id) < 1,
'1'
,
IF(COUNT(se.id) < 1 && COUNT(s.id) > 0,
'2'
,
IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0,
'3'
,
IF( COUNT(v.id) > 1 && COUNT(v.id) < 4,
'4'
,
IF( COUNT(v.id) > 3,
'5'
,
'0'/*VVIP*/
)
)
)
)
)
)) AS customer_category,
IF((SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365),
'1'
,
'2'
) AS isActive";
// echo sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$variant,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus); return;
//echo $brand; exit;
$data_list = $db->sql_query(sprintf($query,
$fields,
$filter_date_upload,
$filter_date_created,
$dms,$company,
$dealer,
$filter_age,
$gender,
$state,
$city,
$filter_upload_by,
$complete_data,
$incomplete_data,
$address_1,
$date_of_birth,
$customertype,
$filter_dob,
$brand,
$model,
$variant,
$insurance_company,
$insurance_type,
$finance_company,
$customercategory,
$customerstatus
)." $offset_limit"
);
$query_count = $db->select("SELECT COUNT(1) FROM ( " . sprintf($query,$count,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus) ." ) as total_count");
// echo $query_count;
// $filename = 'sms_service/data/'.strtr($db -> cipher($control_number, true), "+/", "-_" ).'.'.'php';
// $fp = fopen($filename, 'w');
// $col_header = array('No.', 'Name', 'Type', 'Gender', 'Mobile');
// $string = implode(';',array_map('replaceExistingSemiColon', $col_header));
// fwrite($fp, $string.PHP_EOL);
$record_counter = 1;
$json_arr['rows'] = array();
$utility = new Utility();
if($query_count > 0){
while($row = $data_list->fetch_assoc()){
$json_arr['rows'] = array_merge($json_arr['rows'], array(array(
'record_number'=>$record_counter,
'cust_name'=>$row['name'],
'customer_company_dealer'=>$row['company'] . " / " . $row['dealer'],
'mobile_number'=>"+" . $utility->mask($row['mobile_phone_1'], 4, 1)
)));
$record_counter++;
}
$json_arr['total'] = $query_count; //total number of result
echo json_encode($json_arr);
// foreach ($data_list as $data){
// $involvements = "SELECT sd.name AS dms, sc.code AS company, sdr.code AS dealer
// FROM `customer_dms` cd
// INNER JOIN source_company_dealer scd
// ON cd.company_dealer_id = scd.id
// INNER JOIN source_company sc
// ON sc.id = scd.company_id
// INNER JOIN source_dms sd
// ON sc.`dms_id` = sd.id
// INNER JOIN source_dealer sdr
// ON sdr.id = scd.dealer_id
// WHERE `customer_record_id` IN ('".$data['id']."')";
// //echo $involvements; exit;
// $in = $db->return_result($involvements);
// $dms = array();
// $company = array();
// $dealer = array();
// foreach($in as $data1){
// $dms = array_merge($dms, array($data1['dms']));
// $company = array_merge($company, array($data1['company']));
// $dealer = array_merge($dealer, array($data1['dealer']));
// }
// $dms_new = array_unique($dms);
// $company_new = array_unique($company);
// $dealer_new = array_unique($dealer);
// $list_dms = implode(', ', $dms_new);
// $list_company = implode(', ', $company_new);
// $list_dealer = implode(', ', $dealer_new);
// $data_to_insert = array();
// // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Name'=>$data['name'],'Age'=>$data['age_number'],'Gender'=>$data['gender_name'],'Email'=>$data['email_1'],'Mobile'=>$data['mobile_phone_1'],'Type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation' )));
// if(intval($data['type']) === 1){
// $data_to_insert = array(
// $record_counter++,
// $data['name'],
// (intval($data['type']) === 1) ? 'Individual' : 'Corporation',
// $data['gender_name'],
// //$data['date_of_birth'],
// //$data['age_number'],
// // $data['email_1'],
// "+" . mask($data['mobile_phone_1'], 5, 1)
// // $list_dms,
// // $list_company,
// // $list_dealer,
// // $data['date_created'],
// );
// }
// else {
// $data_to_insert = array(
// $record_counter++,
// $data['name'],
// (intval($data['type']) === 1) ? 'Individual' : 'Corporation',
// '',
// // $data['date_of_birth'],
// // $data['age_number'],
// // $data['email_1'],
// "+" . mask($data['mobile_phone_1'], 5, 1)
// // $list_dms,
// // $list_company,
// // $list_dealer,
// // $data['date_created'],
// );
// }
// $string = implode(';',array_map('replaceExistingSemiColon', $data_to_insert));
//echo $data_to_insert; exit;
// fwrite($fp, $string.PHP_EOL);
// }
// fclose($fp);
}
// $db -> sql_query("UPDATE text_blast SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' ");
// return max(0,($record_counter-1));//array($return_data,array($record_counter));
}
?>