File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/app/sms_service/insert_request.php

Back
<?php include_once("../../cfg/db.php"); include_once("../notification/notification.php"); include_once("../sms/send_sms.php"); include_once("../mail/send_mail.php"); include_once("../language/en-US.php"); include_once("../../api/controllers/utility.php"); if(!isset($_SESSION['user'])){ exit(); } $utility = new Utility(); $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ? $_SESSION['user']['company_permissions_implode'] : '-1' ; // if(isset($_POST['checkboxArray'])){ // $request_type_arr = $_POST['checkboxArray']; // } else { // $return_arr["status"]=0; // $return_arr["message"]=" Select request type."; // echo json_encode($return_arr); // exit; // } $request_type_arr = array(1); $upload_employee_id = $db->escape(($_POST['uploaded-by'])); $request_type = $db->escape($_POST['level']); $reg_from = $db->escape(($_POST['reg-from'])); $reg_to = $db->escape(($_POST['reg-to'])); // $dms = $db->escape(($_POST['dms'])); $dms = 0; $company = $db->escape(($_POST['company'])); $dealer = $db->escape(($_POST['dealer'])); $state = $db->escape(($_POST['state'])); $city = $db->escape(($_POST['city'])); // $upload_from = $db->escape(($_POST['upload-from'])); // $upload_to = $db->escape(($_POST['upload-to'])); $message_content = $db->escape(($_POST['message-content'])); $character_count = $db->escape($_POST['char-count-hidden']); // $message_content = str_replace('\n', '\\\n', $message); $description = $db->escape(($_POST['description'])); $type = $db->escape(($_POST['type'])); $brand_id = $db->escape(($_POST['brand'])); $model_id = $db->escape(($_POST['model'])); $variant_id = $db->escape($_POST['variant']); $filter_data = $db->escape(($_POST['filter-data'])); $gender = $db->escape(($_POST['gender'])); $age_from = $db->escape(($_POST['age-from'])); $age_to = $db->escape(($_POST['age-to'])); $sort_by = $db->escape(($_POST['sort-by'])); $order_by = $db->escape(($_POST['order'])); // $downloadable = $db->escape(($_POST['downloadable'])); $downloadable = 0; $date_of_birth = $db->escape(($_POST['date-of-birth'])); $customer_category = $db->escape(($_POST['category'])); $customer_status = $db->escape(($_POST['active-status'])); $finance_company = $db->escape(($_POST['finance-company'])); $insurance_company = $db->escape(($_POST['insurance-company'])); $insurance_type = $db->escape(($_POST['insurance-type'])); $employee_id = $_SESSION['user']['id']; // echo $filter_data // exit; // foreach($request_type_arr as $request_type){ $employee_id_approver_list = $db->sql_query("SELECT `employee_id` FROM `sms_service_approver` WHERE `request_type_id` = '$request_type' "); if($employee_id_approver_list -> num_rows > 0){ $control_number = date("Y").'-'.uniqid(); $query = "INSERT INTO `text_blast` ( `control_number`, `employee_id`, `request_type_id`, `message_content`, `char_count`, `description`, `company_id`, `dealer_id`, `state_id`, `city_id`, `request_date`, `request_time`, `upload_by_employee_id`, `customer_type`, `age_from`, `age_to`, `gender_id`, `customer_data_type`, `dms_id`, `activity_date_from`, `activity_date_to`, `expire`, `brand_id`, `model_id`, `variant_id`, `sort_by`, `order_by`, `date_of_birth`, `customer_category`, `customer_status`, `insurance_company_id`, `insurance_type_id`, `finance_company_id`, `is_downloadable`, `company_list` ) VALUES ( (select concat(YEAR(NOW()),'-', conv( concat( substring(uid,16,3), substring(uid,10,4), substring(uid,1,8)) ,16,10) div 10000 - (141427 * 24 * 60 * 60 * 1000)) as current_mills from ( select uuid() uid) as alias), '$employee_id', '$request_type', '$message_content', '$character_count', '$description', '$company', '$dealer', '$state', '$city', NOW(), NOW(), '$upload_employee_id', '$type', '$age_from', '$age_to', '$gender', '$filter_data', '$dms', '$reg_from', '$reg_to', DATE_ADD(NOW(), INTERVAL 30 DAY), '$brand_id', '$model_id', '$variant_id', '$sort_by', '$order_by', '$date_of_birth', '$customer_category', '$customer_status', '$insurance_company', '$insurance_type', '$finance_company', '$downloadable', '$granted_company_ids' )"; //echo $query;exit(); $request_id = $db->sql_query_id($query); while($row = $employee_id_approver_list->fetch_assoc()){ $employee_id_approver = $row['employee_id']; if($employee_id_approver == $employee_id){ $db->sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`,`status`) VALUES ('$request_id', '$employee_id_approver', NOW(),'1')"); } else { $db -> sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`) VALUES ('$request_id','$employee_id_approver', NOW())"); $notification_email_subject = sprintf($_GLOBALS['request_mail_subject'], 'Customer'); $notification_system = sprintf($_GLOBALS['request_sms_service'],'customer'); $notification_sms = sprintf($_GLOBALS['request_data'],'customer',"\r\n"); $notification_mail = sprintf($_GLOBALS['request_data'],'customer',"<br>"); switch($request_type){ case "1": $notification_email_subject = sprintf($_GLOBALS['request_mail_subject'], 'Customer'); $notification_system = sprintf($_GLOBALS['request_sms_service'],'customer'); $notification_sms = sprintf($_GLOBALS['request_data'],'customer',"\r\n"); $notification_mail = sprintf($_GLOBALS['request_data'],'customer',"<br>"); break; case "2": // $notification_email_subject = sprintf($_GLOBALS['report_mail_subject'],'Vehicle'); // $notification_system = sprintf($_GLOBALS['request_sms_system'],'vehicle'); // $notification_sms = sprintf($_GLOBALS['request_sms_service'],'vehicle',"\r\n"); // $notification_mail =sprintf($_GLOBALS['request_sms_service'],'vehicle',"<br>"); break; case "3": // $notification_email_subject = sprintf($_GLOBALS['report_mail_subject'],'Service'); // $notification_system = sprintf($_GLOBALS['request_sms_system'],'service'); // $notification_sms = sprintf($_GLOBALS['request_sms_service'],'service',"\r\n"); // $notification_mail =sprintf($_GLOBALS['request_sms_service'],'service',"<br>"); break; case "4": // $notification_email_subject = sprintf($_GLOBALS['report_mail_subject'],'Sales'); // $notification_system = sprintf($_GLOBALS['request_sms_system'],'sales'); // $notification_sms = sprintf($_GLOBALS['request_sms_service'],'sales',"\r\n"); // $notification_mail =sprintf($_GLOBALS['request_sms_service'],'sales',"<br>"); break; } //-----------------------------------------------------------notify employee $suffix = ""; if($_SESSION['user']['suffix_name'] != 'None'){ $suffix = $_SESSION['user']['suffix_name']; } $employee_name_approver = $_SESSION['user']['first_name'].' '.$_SESSION['user']['middle_name'].' '.$_SESSION['user']['last_name'].' '.$suffix; // $notification = new Notification(); // $notification->insertNotification('3', // $request_id/record id of specific record / , // $employee_id_approver, /employee_id of person who receive the notif/ // $employee_id, // $_SESSION['user']['employee_fullname'].$notification_system, /Notification Content/ // $db // ); $employee_contact_approver = ""; $employee_email_approver = ""; $employee_contact = $db -> sql_query("SELECT ec.`email_address`, ec.`contact_number` FROM `employee_contact` ec INNER JOIN employee e ON ec.employee_id=e.employee_id WHERE e.id = '$employee_id_approver'"); while($row1 = $employee_contact->fetch_assoc()) { $employee_contact_approver = $row1['contact_number']; $employee_email_approver = $row1['email_address']; } $send_sms = new SMS(); $send_sms->sendSMS( $employee_contact_approver, $employee_name_approver.$notification_sms.$variable['site_link'].'request_approval.php'. $_GLOBALS['sms_footer'],$variable ); $send_mail = new Mail(); $send_mail->sendMail( $employee_email_approver, $notification_email_subject, $employee_name_approver.$notification_mail.$variable['site_link'].'request_approval.php'.$_GLOBALS['email_footer'], $variable ); }//end of if } //end of while $approver_count = "SELECT * FROM request_status WHERE request_id = '$request_id'"; //total $approved_count = "SELECT COUNT(id) FROM request_status WHERE request_id = '$request_id' AND status = 1"; $total_approver = $db->sql_num_rows($approver_count); $count_approved = $db->select($approved_count); if($total_approver == $count_approved){ writeToDB($request_id, $db); } else { } // $db -> sql_query_id("INSERT INTO `request_status_overall` (`request_id`, `status`) VALUES ('$request_id', '0')"); $bilang = $db->select("SELECT COUNT() FROM `request_status` WHERE request_id = '$request_id'"); if($bilang == 1){ $db->sql_query("INSERT INTO request_status_summary (`request_id`, `status`) VALUES ('$request_id', 1)"); } else { $db->sql_query("INSERT INTO request_status_summary (`request_id`, `status`) VALUES ('$request_id', 0)"); } $return_arr["status"] = 1; $return_arr["message"]= "Request Successfully Requested."; } else { $return_arr["status"] = 0; $return_arr["message"]= "No available approver for this request."; } // }//end of foreach echo json_encode($return_arr); function clean($s){ return preg_replace( "/\r\|\n/", "", $s ); } 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; } } // =================================================================================================================================================writeToDB function writeToDB($request_id, $db){ // $fmt = new NumberFormatter('PHP', NumberFormatter::CURRENCY); $parameter_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."' "; //WHERE tb.id = '".$request_session_data['request_id']."' " $request_data = $db->sql_query($parameter_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'] : '<strong>N/A</strong>' ; $request_session_data['request_type_id'] = isNotEmpty($data['request_type_id']) ? $data['request_type_id'] : '<strong>N/A</strong>' ; $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' ; $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($request_id, $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,$db); } } //writeToDB function getFilteredCustomerData($request_id, $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, $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 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 // 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 // ) $fields = " c.id, c.photo, c.first_name, c.last_name, asx.suffix_name AS suffix, c.corporation_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, 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,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus); return; $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)); $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"); $record_counter = 0; if( $query_count > 0){ $record_counter++; 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(); $ins = ""; if(intval($data['type']) === 1){ //individual $suffix = ""; $id = $data['id']; $first_name = $data['first_name']; $last_name = $data['last_name']; if($data['suffix'] == "None"){ $suffix = ""; } else { $suffix = $data['suffix']; } $mob_num = $data['mobile_phone_1']; $ins = "INSERT INTO `txtblast_sms_service_approved` (`cust_id`, `request_id`, `first_name`, `last_name`, `suffix`, `mobile_number`, `customer_type`) VALUES('$id', '$request_id', '$first_name', '$last_name', '$suffix', '$mob_num', 1)"; // echo $ins; exit; $db->sql_query($ins); } else { $id = $data['id']; $corp_name = $data['corporation_name']; $mob_num = $data['mobile_phone_1']; $ins = "INSERT INTO `txtblast_sms_service_approved` (`cust_id`, `request_id`, `company_name`, `mobile_number`, `customer_type`) VALUES('$id', '$request_id', '$corp_name', '$mob_num', 2)"; // echo $ins; exit; $db->sql_query($ins); } } } } //getFilteredCustomerData function checkStatusFirst($request_id, $db){ } ?>

| ver. 1.4 | . | PHP 7.3.33 | Generation time: 0.02 | proxy | phpinfo | Settings