File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/app/sms_service/edit_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(); } $send_sms = new SMS(); //send message $utility = new Utility(); $requestor_name = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name']; $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_id = $db->escape(($_POST['request_id'])); $sort_by = $db->escape(($_POST['sort_by'])); $order_by = $db->escape(($_POST['order_by'])); $reg_from = $db->escape(($_POST['reg_from'])); $reg_to = $db->escape(($_POST['reg_to'])); $company = $db->escape(($_POST['company_id'])); $dealer = $db->escape(($_POST['dealer_id'])); $type = $db->escape(($_POST['type'])); $customer_category = $db->escape(($_POST['category'])); $customer_status = $db->escape(($_POST['customer_status'])); $customer_data = $db->escape(($_POST['customer_data'])); $gender = $db->escape(($_POST['gender'])); $date_of_birth = $db->escape(($_POST['date_of_birth'])); $age_from = $db->escape(($_POST['age_from'])); $age_to = $db->escape(($_POST['age_to'])); $brand_id = $db->escape(($_POST['brand'])); $model_id = $db->escape(($_POST['model'])); $variant_id = $db->escape($_POST['variant']); $insurance_company = $db->escape(($_POST['insurance_company'])); $insurance_type = $db->escape(($_POST['insurance_type'])); $finance_company = $db->escape(($_POST['finance_company'])); $state = $db->escape(($_POST['state'])); $city = $db->escape(($_POST['city'])); $description = $db->escape(($_POST['description'])); $message_content = $db->escape(($_POST['message_content'])); $update_query = "UPDATE `text_blast` SET `sort_by`='$sort_by', `order_by`='$order_by', `activity_date_from`='$reg_from', `activity_date_to`='$reg_to', `company_id`='$company', `dealer_id`='$dealer', `customer_type`='$type', `customer_category`='$customer_category', `customer_status`='$customer_status', `customer_data_type`='$customer_data', `gender_id`='$gender', `date_of_birth`='$date_of_birth', `age_from`='$age_from', `age_to`='$age_to', `brand_id`='$brand_id', `model_id`='$model_id', `variant_id`='$variant_id', `insurance_company_id`='$insurance_company', `insurance_type_id`='$insurance_type', `finance_company_id`='$finance_company', `state_id`='$state', `city_id`='$city', `description`='$description', `message_content`='$message_content' WHERE `id` = '$request_id' "; $updated = $db->sql_query_num_inserted($update_query); $notify = "SELECT rs.employee_id, ec.contact_number FROM `request_status` rs INNER JOIN `employee` e ON e.id = rs.employee_id INNER JOIN `employee_contact` ec ON ec.employee_id = e.employee_id WHERE rs.`request_id` = '$request_id' AND rs.`status` = 4"; // echo $notify; exit; $notify_approvers = $db->sql_query($notify); foreach($notify_approvers AS $row){ $number = "0" . $row['contact_number']; $employee_id_approver = $row['employee_id']; $employee_id = $_SESSION['user']['employee_id']; //send message $send_sms->sendSMS($number, $requestor_name . " has re-submitted request", $variable); // create notification $notification = new Notification(); $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>"); $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']. " has re-submitted a request", /Notification Content/ $db ); //send email // $send_mail->sendMail( // $employee_id_approver, // $notification_email_subject, // $employee_name_approver.$notification_mail.$variable['site_link'].'request_approval.php'.$_GLOBALS['email_footer'], // $variable // ); } $update_request_status = "UPDATE `request_status` SET `status` = 0 WHERE `status` = 4 AND `request_id` = '$request_id'"; $db->sql_query($update_request_status); $arr['status'] = 1; //success update // if($updated > 0){ // $arr['status'] = 1; //success update // } // else { // $arr['status'] = 0; //failed update // } echo json_encode($arr); // $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`, // `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', // '$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')"); // writeToDB($request_id, $db); // } else { // $db -> sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`) VALUES ('$request_id','$employee_id_approver', NOW())"); // 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 // // ); // // // echo ($employee_email_approver);exit(); // // $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 // // $db -> sql_query_id("INSERT INTO `request_status_overall` (`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,$fmt,$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, $fmt, $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 ?>

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