File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/app/table/reload_table.php

Back
<?php include_once("../../cfg/db.php"); include_once("../../api/controllers/utility.php"); $utility = new Utility(); $now = $db->sql_query("SELECT NOW() AS today"); $today = ""; foreach($now AS $data){ $today = substr($data['today'], 0, 10); } $yesterday_date = $db->select("SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)"); $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')"); $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ? $_SESSION['user']['company_permissions_implode'] : '-1' ; $type = $db->escape(trim($_GET['type'])); $filter_date = ""; $filter_status = ""; $search = ""; $offset = ""; $limit = ""; // REPORTING $date_from = ""; $date_to = ""; $company = ""; $dealer = ""; $status = ""; $mask = 1; if(isset($_GET['search'])){ $search = $db->escape(trim($_GET['search'])); } else { $search = ""; } if(isset($_GET['offset'])){ $offset = $db -> escape(trim($_GET['offset'])); } else { $offset = ""; } if(isset($_GET['limit'])){ $limit = $db -> escape(trim($_GET['limit'])); $offset_limit = " LIMIT ". $offset.",".$limit; } else { $offset_limit = ""; } if(isset($_GET['filter_date']) && $_GET['filter_date'] != ""){ $date = $db -> escape(trim($_GET['filter_date'])); $today = substr($date, 6, 4) . "-" . substr($date, 0, 2) . "-" . substr($date, 3, 2); //2021-08-18 } else { $filter_date = ""; } if($type == 1){ // ----------------------------------------------------------------------------------- BIRTHDAY CELEBRANT ----------->> if(isset($_GET['filter_status']) && $_GET['filter_status'] != ""){ $get_status = $db -> escape(trim($_GET['filter_status'])); if($get_status > 0){ $filter_status = " AND tbbr.sms_response > 0 "; } else { $filter_status = " AND tbbr.sms_response = '$get_status' "; } } else { $filter_status = ""; } $mask_mobile = $db->escape($_GET['mask_main']); $json_arr['rows'] = array(); $count = " count() "; $fields = " tbbr.id, tbbr.customer_id, concat(c.first_name, ' ', c.last_name) AS name, cc.mobile_phone_1, tbbr.sms_response, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbbr.date, '%Y-%m-%d') AS date, DATE_FORMAT(c.date_of_birth, '%M %d, %Y') AS birthdate "; $celebrant_query = "SELECT %s FROM `text_blast_birthday_record` tbbr INNER JOIN customer c ON c.id = tbbr.customer_id INNER JOIN customer_contact cc ON cc.customer_id = tbbr.customer_id INNER JOIN customer_dms cdms ON cdms.customer_record_id = tbbr.customer_id INNER JOIN source_company_dealer scd ON scd.id = cdms.company_dealer_id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dealer sd ON sd.id = scd.dealer_id WHERE 1 AND (concat(c.first_name, ' ', c.last_name) LIKE '%%%s%%') AND DATE_FORMAT(tbbr.date, '%%Y-%%m-%%d') = '$today' %s GROUP BY tbbr.id ORDER BY tbbr.id ASC"; // echo sprintf($celebrant_query, $fields, $search, $filter_status); return; $celebrant_list_query = $db -> sql_query(sprintf($celebrant_query, $fields, $search, $filter_status)." $offset_limit"); $celebrant_list_query_count = $db->select("SELECT COUNT() FROM `text_blast_birthday_record` tbbr WHERE tbbr.date = '$today' " . $filter_status); while($row = $celebrant_list_query->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'customer_id'=>$row['customer_id'], 'name'=>$row['name'], 'number'=>$mask_mobile == 1 ? (substr($row['mobile_phone_1'], 0, 4) == '+639' ? "+" . $utility->mask($row['mobile_phone_1'], 4, 1) : $utility->mask($row['mobile_phone_1'], 3, 1)) : $row['mobile_phone_1'], 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'date'=>$row['date'], 'birthdate'=>$row['birthdate'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>"))))) ))); } $json_arr['total'] = $celebrant_list_query_count; echo json_encode($json_arr); } else if($type == 2){ // ----------------------------------------------------------------------- INSURANCE EXPIRATION ------->> if(isset($_GET['filter_status']) && $_GET['filter_status'] != ""){ $get_status = $db->escape(trim($_GET['filter_status'])); if($get_status > 0){ $filter_status = " AND tbirr.sms_response > 0 "; } else { if($get_status == -2){ $filter_status = " AND tbirr.sms_response = -2 OR tbirr.sms_response = 0"; } else { $filter_status = " AND tbirr.sms_response = '$get_status' "; } } } else { $filter_status = ""; } $json_arr['rows'] = array(); $mask_mobile = $db->escape($_GET['mask_main']); $count = " count() "; $fields = " tbirr.finance_id, f.plate_cs_number1, f.policy_number, f.customer_fullname, f.customer_mobile_number, sc.code AS company, sd.name AS dealer, tbirr.reminder_type, tbirr.sms_response "; $insurance_reminder_query = "SELECT %s FROM `txtblast_insurance_reminder_record` tbirr INNER JOIN `finance` f ON f.id = tbirr.finance_id INNER JOIN source_company sc ON f.company_id = sc.id INNER JOIN source_dealer sd ON f.dealer_id = sd.id WHERE 1 AND tbirr.sms_response != 'skipped' AND concat(f.plate_cs_number1, f.policy_number, f.customer_fullname, f.customer_mobile_number) LIKE '%%%s%%' AND DATE_FORMAT(tbirr.action_date, '%%Y-%%m-%%d') = '" . $today . "' " . $filter_status . " GROUP BY tbirr.id ORDER BY tbirr.id ASC"; // echo sprintf($insurance_reminder_query, $fields, $search); exit; // echo $new_import_query . $offset_limit; return; $insurance_reminder_list_query = $db->sql_query(sprintf($insurance_reminder_query, $fields, $search) . $offset_limit); $insurance_reminder_list_count = $db->select("SELECT COUNT(1) FROM (".sprintf($insurance_reminder_query, $count, $search).") AS count"); while($row = $insurance_reminder_list_query->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( $sms_response = $row['sms_response'], 'finance_id'=>$row['finance_id'], 'plate_cs_number'=>$row['plate_cs_number1'], 'customer_fullname'=>$row['customer_fullname'], 'mobile_number'=>$mask_mobile == 1 ? (substr($row['customer_mobile_number'], 0, 4) == '+639' ? "+" . $utility->mask($row['customer_mobile_number'], 4, 1) : $utility->mask($row['customer_mobile_number'], 3, 1)) : $row['customer_mobile_number'], 'policy_number'=>$row['policy_number'], 'company_dealer'=>$row['company'] . "/" . $row['dealer'], 'reminder_type'=>$row['reminder_type'] . "-day", 'status'=>$sms_response == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($sms_response == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($sms_response == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($sms_response == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($sms_response == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($sms_response == -1 ? "<span class='badge bg-warning'>PENDING</span>" : ($sms_response == -3 ? "<span class='badge bg-dark'>SKIPPED</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>")))))) ))); } $json_arr['total'] = $insurance_reminder_list_count; echo json_encode($json_arr); } else if($type == 3){ // ----------------------------------------------------------------------- NEWLY IMPORTED ------------->> if(isset($_GET['filter_status']) && $_GET['filter_status'] != ""){ $get_status = $db -> escape(trim($_GET['filter_status'])); if($get_status > 0){ // $filter_status = " AND (tbnir.status > 0 "; $filter_status = " AND (tbnir.status > 0 OR concat('', tbnir.status 1) != tbnir.status) "; } else { $filter_status = " AND tbnir.status = '$get_status' "; } } else { $filter_status = ""; } $json_arr['rows'] = array(); $mask_mobile = $db->escape($_GET['mask_main']); $count = " count() "; $fields = " tbnir.id, tbnir.customer_id, concat(tbnir.first_name, ' ', tbnir.last_name) AS name, tbnir.mobile_number, tbnir.status, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbnir.released_date, '%M %d, %Y') AS released_date, DATE_FORMAT(tbnir.date_uploaded, '%h:%i:%s %p') AS time_uploaded, tbnir.uploaded_by "; $new_import_query = "SELECT " . $fields . " FROM `text_blast_new_import_record` tbnir INNER JOIN customer_dms cdms ON tbnir.customer_id = cdms.customer_record_id INNER JOIN source_company_dealer scd ON cdms.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 WHERE (concat(tbnir.first_name, ' ', tbnir.last_name) LIKE '%%" . $search . "%%') AND DATE_FORMAT(tbnir.date_uploaded, '%Y-%m-%d') = '" . $today . "' " . $filter_status . " GROUP BY tbnir.id ORDER BY tbnir.id ASC"; // echo sprintf($new_import_query, $fields, $search, $filter_status); return; // echo $new_import_query . $offset_limit; return; // $new_import_list_query = $db -> sql_query(sprintf($new_import_query, $fields, $search, $filter_status)." $offset_limit"); $new_import_list_query = $db->sql_query($new_import_query . $offset_limit); $new_import_list_query_count = $db->select("SELECT COUNT() FROM `text_blast_new_import_record` tbnir WHERE DATE_FORMAT(tbnir.date_uploaded, '%Y-%m-%d') = '$today' " . $filter_status); while($row = $new_import_list_query->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'customer_id'=>$row['customer_id'], 'name'=>$row['name'], 'number'=>$mask_mobile == 1 ? (substr($row['mobile_number'], 0, 4) == '+639' ? "+" . $utility->mask($row['mobile_number'], 4, 1) : $utility->mask($row['mobile_number'], 3, 1)) : $row['mobile_number'], 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'released_date'=>$row['released_date'], 'time_uploaded'=>$row['time_uploaded'], 'uploaded_by'=>$row['uploaded_by'], 'status'=>$row['status'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['status'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['status'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['status'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : (row['status'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['status'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>".$row['status']." </span>"))))) ))); } $json_arr['total'] = $new_import_list_query_count; echo json_encode($json_arr); } else if($type == 4){ // --------------------------------------------------------------------------- PDC REMINDER if(isset($_GET['filter_status']) && $_GET['filter_status'] != ""){ $get_status = $db->escape(trim($_GET['filter_status'])); if($get_status > 0){ $filter_status = " AND tbprr.sms_response > 0 "; } else { if($get_status == 0){ $filter_status = " AND tbprr.sms_response = -2 OR tbprr.sms_response = 0"; } else { $filter_status = " AND tbprr.sms_response = '$get_status' "; } } } else { $filter_status = ""; } $json_arr['rows'] = array(); $mask_mobile = $db->escape($_GET['mask_main']); $count = " count() "; $fields = " tbprr.pdc_id, fp.plate_cs_number1, fp.pdc_check_number, fp.client_name, fp.client_mobile, sc.code AS company, sd.name AS dealer, tbprr.reminder_type, tbprr.sms_response "; $pdc_reminder_query = "SELECT %s FROM `txtblast_pdc_reminder_record` tbprr INNER JOIN `finance_pdc` fp ON fp.id = tbprr.pdc_id INNER JOIN source_company sc ON sc.id = fp.company_id INNER JOIN source_dealer sd ON sd.id = fp.dealer_id WHERE concat(fp.plate_cs_number1, fp.plate_cs_number2, fp.pdc_check_number, fp.client_name, fp.client_mobile) LIKE '%%%s%%' AND DATE_FORMAT(tbprr.action_date, '%%Y-%%m-%%d') = '" . $today . "' " . $filter_status . " GROUP BY tbprr.id ORDER BY tbprr.id ASC"; // echo sprintf($pdc_reminder_query, $fields, $search); return; // echo $new_import_query . $offset_limit; return; $pdc_reminder_list_query = $db->sql_query(sprintf($pdc_reminder_query, $fields, $search) . $offset_limit); $pdc_reminder_list_count = $db->select("SELECT COUNT(1) FROM (".sprintf($pdc_reminder_query, $count, $search).") AS count"); while($row = $pdc_reminder_list_query->fetch_assoc()) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'pdc_id'=>$row['pdc_id'], 'plate_cs_number'=>$row['plate_cs_number1'], 'client_name'=>$row['client_name'], 'client_mobile'=>$mask_mobile == 1 ? (substr($row['client_mobile'], 0, 4) == '+639' ? "+" . $utility->mask($row['client_mobile'], 4, 1) : $utility->mask($row['client_mobile'], 3, 1)) : $row['client_mobile'], 'check_number'=>$row['pdc_check_number'], 'company_dealer'=>$row['company'] . "/" . $row['dealer'], 'reminder_type'=>$row['reminder_type'] . "-day", 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>"))))) ))); } $json_arr['total'] = $pdc_reminder_list_count; echo json_encode($json_arr); } else if($type == 10){ // ---------------------------------------------------------------- BIRTHDAY REPORT ----->> if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ $date_from = $db -> escape(trim($_GET['start_date'])); // $date_from = substr($date_from, 6, 4) . "-" . substr($date_from, 0, 2) . "-" . substr($date_from, 3, 2); // 2021-08-18 } else { $date_from = ""; } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ $date_to = $db->escape(trim($_GET['end_date'])); // $date_to = substr($date_to, 6, 4) . "-" . substr($date_to, 0, 2) . "-" . substr($date_to, 3, 2); // 2021-08-18 } else { $date_to = ""; } if(isset($_GET['company']) && $_GET['company'] != ""){ $company = $db->escape(trim($_GET['company'])); $company = " AND sc.id = '$company' "; } else { $company = " AND sc.id IN(" . $granted_company_ids . ")"; } if(isset($_GET['dealer']) && $_GET['dealer'] != ""){ $dealer = $db->escape(trim($_GET['dealer'])); $dealer = " AND sd.id = '$dealer' "; } else { $dealer = ""; } if(isset($_GET['status']) && $_GET['status'] != ""){ $get_status = $db -> escape(trim($_GET['status'])); if($get_status > 0){ $filter_status = " AND tbbr.sms_response > 0 "; } else { $filter_status = " AND tbbr.sms_response = '$get_status' "; if($get_status == -3){$filter_status = "";} } } else { $filter_status = ""; } $mask = $_GET['mask']; $json_arr['rows'] = array(); $count = " COUNT() "; $fields = " tbbr.id, concat(c.first_name, ' ', c.last_name) AS name, cc.mobile_phone_1, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbbr.date, '%M %d') AS date, DATE_FORMAT(c.date_of_birth, '%M %d, %Y') AS birthdate, tbbr.sms_response "; $celebrant_query = "SELECT %s FROM `text_blast_birthday_record` tbbr INNER JOIN customer c ON c.id = tbbr.customer_id INNER JOIN customer_contact cc ON cc.customer_id = tbbr.customer_id INNER JOIN source_company sc ON sc.id = tbbr.company_id INNER JOIN source_dealer sd ON sd.id = tbbr.dealer_id WHERE (concat(c.first_name, ' ', c.last_name, cc.mobile_phone_1, sc.code) LIKE '%%%s%%') AND tbbr.date BETWEEN '$date_from' AND '$date_to' %s %s %s GROUP BY c.id ORDER BY tbbr.date ASC"; // echo sprintf($celebrant_query, $fields, $search, $filter_status, $company, $dealer); return; $celebrant_list_query = $db->sql_query(sprintf($celebrant_query, $fields, $search, $filter_status, $company, $dealer)." $offset_limit"); // $celebrant_list_query_count = $db->select(sprintf($celebrant_query, $count, $search, $filter_status, $company, $dealer)." $offset_limit"); $celebrant_list_query_count = $db->select("SELECT COUNT() FROM (".sprintf($celebrant_query, $count, $search, $filter_status, $company, $dealer).") AS count"); while($row = $celebrant_list_query->fetch_assoc()) { if($mask == 1){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number'=>substr($row['mobile_phone_1'], 0, 4) == '+639' ? '+' . $utility->mask($row['mobile_phone_1'], 4, 1) : $utility->mask($row['mobile_phone_1'], 3, 1), 'name'=>$row['name'], 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'date'=>$row['date'], 'birthdate'=>$row['birthdate'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>"))))) ))); } else if($mask == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number'=>$row['mobile_phone_1'], 'name'=>$row['name'], 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'date'=>$row['date'], 'birthdate'=>$row['birthdate'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>"))))) ))); } } $json_arr['total'] = $celebrant_list_query_count; echo json_encode($json_arr); } else if($type == 11){ // ----------------------------------------------------Reports > SMS Reports > Insurance Expiration (sms table) --> if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ $date_from = $db -> escape(trim($_GET['start_date'])); } else { $date_from = ""; } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ $date_to = $db -> escape(trim($_GET['end_date'])); } else { $date_to = ""; } if(isset($_GET['company']) && $_GET['company'] != ""){ $company = $db -> escape(trim($_GET['company'])); $company = " AND sc.id = '$company' "; } else { $company = " AND sc.id IN(" . $granted_company_ids . ")"; } if(isset($_GET['dealer']) && $_GET['dealer'] != ""){ $dealer = $db -> escape(trim($_GET['dealer'])); $dealer = " AND sd.id = '$dealer' "; } else { $dealer = ""; } if(isset($_GET['status']) && $_GET['status'] != ""){ $get_status = $db -> escape(trim($_GET['status'])); if($get_status > 0){ $filter_status = " AND tbirr.sms_response > 0 "; } else { $filter_status = " AND tbirr.sms_response = '$get_status' "; if($get_status == -10){$filter_status = "";} } } else { $filter_status = ""; } $mask = $_GET['mask']; $json_arr['rows'] = array(); $count = " COUNT() "; $fields = " tbirr.id, f.customer_fullname, f.customer_mobile_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbirr.action_date, '%b %d, %Y') AS action_date, tbirr.sms_response "; $ins_exp_query = "SELECT %s FROM `txtblast_insurance_reminder_record` tbirr INNER JOIN `finance` f ON f.id = tbirr.finance_id INNER JOIN source_company sc ON sc.id = f.company_id INNER JOIN source_dealer sd ON sd.id = f.dealer_id WHERE (concat(f.customer_fullname, f.customer_mobile_number, sc.code) LIKE '%%%s%%') AND tbirr.action_date BETWEEN '$date_from' AND '$date_to' %s %s %s ORDER BY tbirr.action_date ASC"; // echo sprintf($ins_exp_query, $fields, $search, $filter_status, $company, $dealer); return; $ins_exp_list_query = $db->sql_query(sprintf($ins_exp_query, $fields, $search, $filter_status, $company, $dealer)." $offset_limit"); $ins_exp_list_query_count = $db->select(sprintf($ins_exp_query, $count, $search, $filter_status, $company, $dealer)." $offset_limit"); // $ins_exp_list_query_count = $db->select("SELECT COUNT() FROM (".sprintf($ins_exp_query, $count, $search, $filter_status, $company, $dealer).") AS count"); while($row = $ins_exp_list_query->fetch_assoc()) { if($mask == 1){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number'=>substr($row['customer_mobile_number'], 0, 4) == '+639' ? '+' . $utility->mask($row['customer_mobile_number'], 4, 1) : $utility->mask($row['customer_mobile_number'], 3, 1), 'customer_fullname'=>$row['customer_fullname'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'action_date'=>$row['action_date'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : ($row['sms_response'] == -3 ? "<span class='badge bg-dark'>SKIPPED</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>")))))) ))); } else if($mask == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number'=>$row['customer_mobile_number'], 'customer_fullname'=>$row['customer_fullname'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'action_date'=>$row['action_date'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : ($row['sms_response'] == -3 ? "<span class='badge bg-dark'>SKIPPED</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>")))))) ))); } } $json_arr['total'] = $ins_exp_list_query_count; echo json_encode($json_arr); } else if($type == 12){ // ---------------------------------------------------------------- THANK YOU REPORT ----->> if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ $date_from = $db -> escape(trim($_GET['start_date'])); } else { $date_from = ""; } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ $date_to = $db -> escape(trim($_GET['end_date'])); } else { $date_to = ""; } if(isset($_GET['company']) && $_GET['company'] != ""){ $company = $db -> escape(trim($_GET['company'])); $company = " AND tbnir.company_id = '$company' "; } else { $company = " AND tbnir.company_id IN(" . $granted_company_ids . ")"; } if(isset($_GET['dealer']) && $_GET['dealer'] != ""){ $dealer = $db -> escape(trim($_GET['dealer'])); $dealer = " AND tbnir.dealer_id = '$dealer' "; } else { $dealer = ""; } if(isset($_GET['status']) && $_GET['status'] != ""){ $get_status = $db -> escape(trim($_GET['status'])); if($get_status > 0){ $filter_status = " AND (tbnir.status > 0 OR concat('', tbnir.status 1) != tbnir.status) "; } else { $filter_status = " AND tbnir.status = '$get_status' "; if($get_status == -3){$filter_status = "";} } } else { $filter_status = ""; } $mask = $_GET['mask']; $json_arr['rows'] = array(); $count = " count() "; $fields = " concat(tbnir.first_name, ' ', tbnir.last_name) AS name, tbnir.mobile_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbnir.date_uploaded, '%M %d, %Y / %h:%i:%s %p') AS date_uploaded, tbnir.uploaded_by, tbnir.status "; $thank_you_query = "SELECT %s FROM `text_blast_new_import_record` tbnir INNER JOIN source_company sc ON tbnir.company_id = sc.id INNER JOIN source_dealer sd ON tbnir.dealer_id = sd.id WHERE (concat(tbnir.first_name, ' ', tbnir.last_name, tbnir.mobile_number) LIKE '%%%s%%') AND DATE_FORMAT(tbnir.date_uploaded, '%%Y-%%m-%%d') BETWEEN '$date_from' AND '$date_to' %s %s %s ORDER BY tbnir.date_uploaded ASC"; // echo sprintf($thank_you_query, $fields, $search, $filter_status, $company, $dealer); return; // echo sprintf($thank_you_query, $count, $search, $filter_status, $company, $dealer); return; $thank_you_list_query = $db->sql_query(sprintf($thank_you_query, $fields, $search, $filter_status, $company, $dealer)." $offset_limit"); $thank_you_query_count = $db->select("SELECT COUNT(1) FROM (".sprintf($thank_you_query, $count, $search, $filter_status, $company, $dealer).") AS count"); $thank_you_query_count = $db->select(sprintf($thank_you_query, $count, $search, $filter_status, $company, $dealer)); // echo $thank_you_query_count; return; // $thank_you_query_count = $db->select("SELECT COUNT(1) FROM (".sprintf($thank_you_query, $count, $search, $filter_status, $company, $dealer).") AS count"); // $company_arr = array(); // while($row = $thank_you_list_query->fetch_assoc()){ // $company_item = $row['company']; // if(!(in_array($company_item, $company_arr))){ // array_push($company_arr, $company_item); // } // echo $company_item . " "; // } // print_r($company_arr); return; while($row = $thank_you_list_query->fetch_assoc()) { $stats = strip_tags($row['status']); $stats = (string) $db->escape(doctypeIssue($stats)); $statusArray = json_decode($row['status'], true); // if($mask == 1){ // $json_arr['rows'] = array_merge($json_arr['rows'], array(array( // 'number'=>substr($row['mobile_number'], 0, 4) == '+639' ? '+' . $utility->mask($row['mobile_number'], 4, 1) : $utility->mask($row['mobile_number'], 3, 1), // 'name'=>$row['name'], // 'company'=>$row['company'], // 'dealer'=>$row['dealer'], // 'date_uploaded'=>$row['date_uploaded'], // 'uploaded_by'=>$row['uploaded_by'], // 'status'=>!is_numeric($stats) ? "<span class='badge bg-danger'>= ".$stats."</span>" : // ($stats == "" ? "<span class='badge bg-danger'>ERROR DOCTYPE RESULT</span>" : // ($stats == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : // ($stats == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : // ($stats == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : // ($stats == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : // ($stats == -1 ? "<span class='badge bg-warning'>PENDING</span>" : // ($stats == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : // "<span class='badge bg-danger'>Something went wrong. SMS Status: ".$stats." </span>"))))))) // ))); // } // else if($mask == 0){ // $json_arr['rows'] = array_merge($json_arr['rows'], array(array( // 'number'=>$row['mobile_number'], // 'name'=>$row['name'], // 'company'=>$row['company'], // 'dealer'=>$row['dealer'], // 'date_uploaded'=>$row['date_uploaded'], // 'uploaded_by'=>$row['uploaded_by'], // 'status'=>!is_numeric($stats) ? "<span class='badge bg-danger'>= ".$stats."</span>" : // ($stats == "" ? "<span class='badge bg-danger'>ERROR DOCTYPE RESULT</span>" : // ($stats == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : // ($stats == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : // ($stats == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : // ($stats == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : // ($stats == -1 ? "<span class='badge bg-warning'>PENDING</span>" : // ($stats == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : // "<span class='badge bg-danger'>Something went wrong. SMS Status: ".$stats." </span>"))))))) // ))); // } if (json_last_error() === JSON_ERROR_NONE && is_array($statusArray)) { // Successfully decoded JSON, now check for SMS sent status if (!$statusArray['Error'] && $statusArray['Accepted'] == 1 && $statusArray['TotalCreditUsed'] > 0) { $smsStatus = "<span class='badge bg-success'>SMS SENT</span>"; } else { // Handle case where JSON exists but SMS wasn't sent $smsStatus = "<span class='badge bg-danger'>Something went wrong. SMS Status: " . $row['status'] . "</span>"; } } else { // Fallback if $stats is not JSON or error in decoding $smsStatus = !is_numeric($stats) ? "<span class='badge bg-danger'>= " . $stats . "</span>" : ($stats == "" ? "<span class='badge bg-danger'>ERROR DOCTYPE RESULT</span>" : ($stats == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($stats == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($stats == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($stats == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($stats == -1 ? "<span class='badge bg-warning'>PENDING</span>" : ($stats == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Status: " . $stats . "</span>"))))))); } if ($mask == 1) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number' => substr($row['mobile_number'], 0, 4) == '+639' ? '+' . $utility->mask($row['mobile_number'], 4, 1) : $utility->mask($row['mobile_number'], 3, 1), 'name' => $row['name'], 'company' => $row['company'], 'dealer' => $row['dealer'], 'date_uploaded' => $row['date_uploaded'], 'uploaded_by' => $row['uploaded_by'], 'status' => $smsStatus ))); } else if ($mask == 0) { $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number' => $row['mobile_number'], 'name' => $row['name'], 'company' => $row['company'], 'dealer' => $row['dealer'], 'date_uploaded' => $row['date_uploaded'], 'uploaded_by' => $row['uploaded_by'], 'status' => $smsStatus ))); } } $json_arr['total'] = $thank_you_query_count; echo json_encode($json_arr); } else if($type == 13){ //------------------------------------------------ Reports > SMS Reports > Text Blast (requestors table) $request_id = ""; if(isset($_GET['search'])){ $search = $db -> escape(trim($_GET['search'])); } else { $search = ""; } if(isset($_GET['offset'])){ $offset = $db -> escape(trim($_GET['offset'])); } else { $offset = ""; } if(isset($_GET['company']) && $_GET['company'] != ""){ $company = $db -> escape(trim($_GET['company'])); $company = " AND sc.id = '$company' "; } else { $company = ""; } if(isset($_GET['dealer']) && $_GET['dealer'] != ""){ $dealer = $db -> escape(trim($_GET['dealer'])); $dealer = " AND sd.id = '$dealer' "; } else { $dealer = ""; } if(isset($_GET['status']) && $_GET['status'] != ""){ $status = $db->escape(trim($_GET['status'])); $status = " AND rss.status = '$status' "; } else { $status = ""; } if(isset($_GET['limit'])){ $limit = $db -> escape(trim($_GET['limit'])); $offset_limit = " LIMIT ". $offset.",".$limit; } else { $offset_limit = ""; } $json_arr['rows'] = array(); $count = " count(rs.request_id) "; $fields = " rs.request_id, tb.control_number, tb.description, concat(e.first_name, ' ', e.last_name) AS name, DATE_FORMAT(rs.date, '%M %d, %Y') AS date, IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 2) > 0, 2 , IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 0) > 0, 0 , IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 1) > 0, 1 , 3 ) ) ) AS status, sc.code AS company, sd.name AS dealer"; $company_query = "SELECT %s FROM `request_status` rs INNER JOIN `text_blast` tb ON rs.request_id = tb.id INNER JOIN `request_status_summary` rss ON rs.request_id = rss.request_id INNER JOIN employee e ON tb.employee_id = e.id INNER JOIN source_company_dealer scd ON e.company_dealer_id = scd.id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dealer sd ON sd.id = scd.dealer_id WHERE 1 AND concat(tb.control_number, concat(e.first_name, ' ', e.last_name), sc.code, sd.name) LIKE '%%%s%%' " . $company . $dealer . $status . "GROUP BY rs.request_id ORDER BY rs.date DESC "; // echo sprintf($company_query, $fields, $search); return; $company_list_query = $db->sql_query(sprintf($company_query, $fields, $search)." $offset_limit"); $company_list_query_count = $db->select("SELECT COUNT(1) FROM (".sprintf($company_query, $count, $search).") AS count"); //get status $query_request = "SELECT id FROM `request_status` WHERE `status` = 0"; $count_pending = $db->select($query_request); $overall_status = -1; while($row = $company_list_query->fetch_assoc()) { if($count_pending > 0){ $overall_status = 0; //has pending } else { $overall_status = $row['status']; } $json_arr['rows'] = array_merge($json_arr['rows'],array(array( 'id'=>$row['request_id'] , 'control_number'=>$row['control_number'], 'name'=>$row['name'], 'date'=>$row['date'], 'description'=>$row['description'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'status_id'=>$row['status'], 'status'=>$row['status'] == 0 ? "<span style='color:orange; font-weight:bold'>PENDING</span>" : ($row['status'] == 1 ? "<span style='color:green; font-weight:bold'>APPROVED / ON GOING</span>" : ($row['status'] == 3 ? "<span style='color:blue; font-weight:bold'>CLOSED</span>" : "<span style='color:red; font-weight:bold'>DECLINED</span>")) ))); } $json_arr['total'] = $company_list_query_count; //total number of result echo json_encode($json_arr); } else if($type == 14){ // ------------------------------------------------ Reports > SMS Reports > Text Blast (sms table) if(isset($_GET['search'])){ $search = $db -> escape(trim($_GET['search'])); } else { $search = ""; } if(isset($_GET['offset'])){ $offset = $db -> escape(trim($_GET['offset'])); } else { $offset = ""; } if(isset($_GET['limit'])){ $limit = $db -> escape(trim($_GET['limit'])); $offset_limit = " LIMIT ". $offset.",".$limit; } else { $offset_limit = ""; } if(isset($_GET['status']) && $_GET['status'] != ""){ $get_status = $db->escape(trim($_GET['status'])); if($get_status > 0){ $status = " AND tbssa.status > 0 "; } else { $status = " AND tbssa.status = $get_status "; } } else { $status = ""; $get_status = -3; } $mask = $db->escape($_GET['mask']); $request_id = $db->escape(trim($_GET['request_id'])); $json_arr['rows'] = array(); $count = " count(tbssa.id) "; $fields = " tbssa.cust_id, tbssa.first_name, tbssa.last_name, tbssa.suffix, tbssa.company_name, tbssa.mobile_number, tbssa.status, tbssa.customer_type "; $company_query = "SELECT %s FROM `txtblast_sms_service_approved` tbssa INNER JOIN `text_blast` tb ON tbssa.request_id = tb.id WHERE tbssa.request_id = '$request_id' " . $status . " AND concat(tbssa.cust_id, tbssa.first_name, tbssa.last_name) LIKE '%%%s%%'"; // echo sprintf($company_query, $fields, $search)." $offset_limit"; return; $company_list_query = $db->sql_query(sprintf($company_query, $fields, $search)." $offset_limit"); // echo sprintf($company_query, $fields, $search); return; $company_list_query_count = $db->select(sprintf($company_query,$count,$search)); while($row = $company_list_query->fetch_assoc()) { $name = ""; $suffix = ""; //suffix if($row['suffix'] == "None"){ //do nothing } else { $suffix = $row['suffix']; } //check customer type if($row['customer_type'] == 1){ $name = $row['first_name'] . " " . $row['last_name'] . " " . $suffix; } else { $name = $row['company_name']; } $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'cust_id'=>$row['cust_id'], 'name'=>$name, 'mobile_number'=>$mask == 1 ? "+" . $utility->mask($row['mobile_number'], 4, 1) : $row['mobile_number'], 'status'=>$row['status'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : ($row['status'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['status'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['status'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED NUMBER PREFIX</span>" : ($row['status'] == -2 ? "<span class='badge bg-primary'>SMS not yet activated</span>" : "<span class='badge bg-danger'>Send error: Itexmo status: ".$row['status']."</span>")))) ))); } $json_arr['total'] = $company_list_query_count; //total number of result echo json_encode($json_arr); } else if($type == 15){ // ---------------------------------------------------------------- IMPORTED TODAY ----->> $mask_exclude_corp = 1; $corp = ""; if(isset($_GET['filter_status']) && $_GET['filter_status'] != ""){ $get_status = $db -> escape(trim($_GET['filter_status'])); if($get_status > 0){ $filter_status = " AND tbnir.status > 0 "; } else { $filter_status = " AND tbnir.status = '$get_status' "; } } else { $filter_status = ""; } if(isset($_GET['mask']) && $_GET['mask'] != ""){ $mask = $db->escape(trim($_GET['mask'])); } if(isset($_GET['mask_exclude_corp']) && $_GET['mask_exclude_corp'] != ""){ $mask_exclude_corp = $db->escape(trim($_GET['mask_exclude_corp'])); if($mask_exclude_corp == 0){ $corp = ""; } else if($mask_exclude_corp == 1){ $corp = " AND c.type = 1 "; } } $json_arr['rows'] = array(); $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')"); $count = " count(c.id) "; $fields = " c.id, DATE_FORMAT(s.date_uploaded, '%h:%i:%s %p') AS time_uploaded, DATE_FORMAT(s.activity_date, '%M %d, %Y') AS activity_date, c.first_name, c.last_name, c.corporation_name, cc.mobile_phone_1, sc.name AS company, sd.name AS dealer, e.first_name AS employee_fname, e.last_name AS employee_lname, c.type "; $new_import_query = "SELECT %s FROM sales s INNER JOIN vehicle v ON v.id = s.vehicle_id INNER JOIN customer c ON c.id = v.customer_record_id INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN customer_dms cdms ON c.id = cdms.customer_record_id INNER JOIN source_company_dealer scd ON cdms.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 employee e ON e.id = s.upload_by WHERE concat(c.first_name, ' ', c.last_name, ' ', e.first_name, ' ', e.last_name) LIKE '%%%s%%' AND DATE_FORMAT(s.date_uploaded,'%%Y-%%m-%%d') = '" . $current_date . "' " . $corp . " AND c.status != 0 AND v.status != 0 AND s.status != 0 AND c.upload_source = 0 GROUP BY c.id ORDER BY c.last_name ASC"; // echo sprintf($new_import_query, $fields, $search); return; // echo $new_import_query . $offset_limit; return; $new_import_list_query = $db->sql_query(sprintf($new_import_query, $fields, $search). $offset_limit); // $new_import_list_query = $db->sql_query($new_import_query . $offset_limit); $new_import_list_query_count = $db->select("SELECT COUNT(1) FROM (".sprintf($new_import_query, $count, $search).") AS count"); while($row = $new_import_list_query->fetch_assoc()) { $customer_type = $row["type"]; if($customer_type == 1){ // individual customer if($mask == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'id'=>$row['id'], 'name'=>$row['first_name'] . " " . $row['last_name'], 'number'=>$row['mobile_phone_1'], 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'released_date'=>$row['activity_date'], 'time_uploaded'=>$row['time_uploaded'], 'uploaded_by'=>$row['employee_fname'] . " " . $row['employee_lname'] ))); } else if($mask == 1){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'name'=>$row['first_name'] . " " . $row['last_name'], 'number'=>"+" . $utility->mask($row['mobile_phone_1'], 5, 1), 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'released_date'=>$row['activity_date'], 'time_uploaded'=>$row['time_uploaded'], 'uploaded_by'=>$row['employee_fname'] . " " . $row['employee_lname'] ))); } } else { // corporation customer if($mask == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'name'=>"" . $row['corporation_name'], 'number'=>$row['mobile_phone_1'], 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'released_date'=>$row['activity_date'], 'time_uploaded'=>$row['time_uploaded'], 'uploaded_by'=>$row['employee_fname'] . " " . $row['employee_lname'] ))); } else if($mask == 1){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'name'=>"" . $row['corporation_name'], 'number'=>"+" . $utility->mask($row['mobile_phone_1'], 5, 1), 'company'=>$row['company'], 'dealer'=>$row['dealer'], 'released_date'=>$row['activity_date'], 'time_uploaded'=>$row['time_uploaded'], 'uploaded_by'=>$row['employee_fname'] . " " . $row['employee_lname'] ))); } } } $json_arr['total'] = $new_import_list_query_count; echo json_encode($json_arr); } else if($type == 16){ // ----------------------------------------------------Reports > SMS Reports > PDC Expiration (sms table) --> if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ $date_from = $db -> escape(trim($_GET['start_date'])); } else { $date_from = ""; } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ $date_to = $db -> escape(trim($_GET['end_date'])); } else { $date_to = ""; } if(isset($_GET['company']) && $_GET['company'] != ""){ $company = $db -> escape(trim($_GET['company'])); $company = " AND sc.id = '$company' "; } else { $company = " AND sc.id IN(" . $granted_company_ids . ")"; } if(isset($_GET['dealer']) && $_GET['dealer'] != ""){ $dealer = $db -> escape(trim($_GET['dealer'])); $dealer = " AND sd.id = '$dealer' "; } else { $dealer = ""; } if(isset($_GET['status']) && $_GET['status'] != ""){ $get_status = $db -> escape(trim($_GET['status'])); if($get_status > 0){ $filter_status = " AND tbprr.sms_response > 0 "; } else { $filter_status = " AND tbprr.sms_response = '$get_status' "; if($get_status == -3){$filter_status = "";} } } else { $filter_status = ""; } $mask = $_GET['mask']; $json_arr['rows'] = array(); $count = " COUNT() "; $fields = " tbprr.id, fp.client_name, fp.client_mobile, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbprr.action_date, '%b %d, %Y') AS action_date, tbprr.sms_response "; $pdc_exp_query = "SELECT %s FROM `txtblast_pdc_reminder_record` tbprr INNER JOIN `finance_pdc` fp ON fp.id = tbprr.pdc_id INNER JOIN source_company sc ON sc.id = fp.company_id INNER JOIN source_dealer sd ON sd.id = fp.dealer_id WHERE (concat(fp.client_name, fp.client_mobile, sc.code) LIKE '%%%s%%') AND tbprr.action_date BETWEEN '$date_from' AND '$date_to' %s %s %s ORDER BY tbprr.action_date ASC"; // echo sprintf($ins_exp_query, $fields, $search, $filter_status, $company, $dealer); return; $pdc_exp_list_query = $db->sql_query(sprintf($pdc_exp_query, $fields, $search, $filter_status, $company, $dealer)." $offset_limit"); $pdc_exp_list_query_count = $db->select(sprintf($pdc_exp_query, $count, $search, $filter_status, $company, $dealer)." $offset_limit"); // $pdc_exp_list_query_count = $db->select("SELECT COUNT() FROM (".sprintf($pdc_exp_query, $count, $search, $filter_status, $company, $dealer).") AS count"); while($row = $pdc_exp_list_query->fetch_assoc()) { if($mask == 1){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number'=>substr($row['client_mobile'], 0, 4) == '+639' ? '+' . $utility->mask($row['client_mobile'], 4, 1) : $utility->mask($row['client_mobile'], 3, 1), 'client_name'=>$row['client_name'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'action_date'=>$row['action_date'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>"))))) ))); } else if($mask == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'number'=>$row['client_mobile'], 'client_name'=>$row['client_name'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'action_date'=>$row['action_date'], 'status'=>$row['sms_response'] == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : ($row['sms_response'] == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : ($row['sms_response'] == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : ($row['sms_response'] == 30 ? "<span class='badge bg-primary'>UNSUBSCRIBED</span>" : ($row['sms_response'] == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : ($row['sms_response'] == -1 ? "<span class='badge bg-warning'>PENDING</span>" : "<span class='badge bg-danger'>Something went wrong. SMS Response: ".$row['sms_response']." </span>"))))) ))); } } $json_arr['total'] = $pdc_exp_list_query_count; echo json_encode($json_arr); } else if($type == 17){ // follow-up report $where_clause = ""; if(isset($_GET['start_date']) && $_GET['start_date'] != ""){ $date_from = $db->escape(trim($_GET['start_date'])); } else { $date_from = ""; } if(isset($_GET['end_date']) && $_GET['end_date'] != ""){ $date_to = $db->escape(trim($_GET['end_date'])); } else { $date_to = ""; } $type_status = $_GET['status']; $action_status = $_GET['action_status']; if(isset($_GET['status']) && $_GET['status'] == 0){ if($action_status == 0){ // both done and undone $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`sms`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND (DATE_FORMAT(sfu.`called`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to')"; } else if($action_status == 1){ // done only $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`sms`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND (DATE_FORMAT(sfu.`called`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND sfu.`sms` != NULL AND sfu.`called` != NULL "; } else if($action_status == 2){ // undone only $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`sms`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND (DATE_FORMAT(sfu.`called`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND sfu.`sms` == NULL AND sfu.`called` == NULL "; } } else if($_GET['status'] == 1) { // sms only if($action_status == 0){ // both done and undone $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`sms`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to')"; } else if($action_status == 1){ // done only $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`sms`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND sfu.`sms` != NULL"; } else if($action_status == 2){ // undone only $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`sms`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to') AND sfu.`sms` == NULL"; } } else if($_GET['status'] == 2) { // called if($action_status == 0){ } else if($action_status == 1){ } else if($action_status == 2){ } $where_clause = $where_clause . " AND (DATE_FORMAT(sfu.`called`, '%Y-%m-%d') BETWEEN '$date_from' AND '$date_to')"; } if($_GET['company'] != ""){ $company = $db->escape(trim($_GET['company'])); $where_clause = $where_clause . " AND sc.id = '$company' "; } else { $where_clause = $where_clause . " AND sc.id IN(" . $granted_company_ids . ")"; } if($_GET['dealer'] != ""){ $dealer = $db->escape(trim($_GET['dealer'])); $where_clause = $where_clause . " AND sd.id = '$dealer' "; } else { $dealer = ""; } if($search != ""){ $search = " AND concat(customer_individual, customer_corporation, mobile) LIKE '%$search%'"; } $mask = $_GET['mask']; $json_arr['rows'] = array(); $count = " COUNT() "; $fields = " IF(ISNULL(sfu.`sms`), 0 , 1 ) AS sms_status, IF(ISNULL(sfu.`called`), 0 , 1 ) AS call_status, DATE_FORMAT(s.activity_date, '%b %d, %Y') AS sales_date, sc.code AS company, sd.code AS dealer, IF(c.corporation_name = '', concat(c.first_name, ' ', c.last_name) , c.corporation_name ) AS customer_name, cc.mobile_phone_1 AS mobile "; $follow_up_query = "SELECT %s FROM `source_follow_ups` sfu INNER JOIN `sales` s ON s.id = sfu.sales_id INNER JOIN `vehicle` v ON v.id = s.vehicle_id INNER JOIN `customer` c ON c.id = v.customer_record_id INNER JOIN `customer_contact` cc ON cc.customer_id = c.id INNER JOIN `customer_dms` cd ON cd.customer_record_id = c.id INNER JOIN `source_company_dealer` scd ON scd.id = cd.company_dealer_id INNER JOIN `source_company` sc ON sc.id = scd.company_id INNER JOIN `source_dealer` sd ON sd.id = scd.dealer_id WHERE 1 %s %s"; // echo sprintf($follow_up_query, $fields, $search, $where_clause); return; $fup_list_query = $db->sql_query(sprintf($follow_up_query, $fields, $search, $where_clause)." $offset_limit"); $fup_list_query_count = $db->select(sprintf($follow_up_query, $count, $search, $where_clause)." $offset_limit"); while($row = $fup_list_query->fetch_assoc()) { if($mask == 1){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'sales_date'=>$row['sales_date'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'customer_name'=>$row['customer_name'], 'number'=>substr($row['mobile'], 0, 4) == '+639' ? '+' . $utility->mask($row['mobile'], 4, 1) : $utility->mask($row['mobile'], 3, 1), 'sms_status'=>$row['sms_status'] == 0 ? "<span><i class='fas fa-times-circle' style='color: red;'></i></span>" : "<span><i class='fas fa-check-circle' style='color: green;'></i></span>", 'call_status'=>$row['call_status'] == 0 ? "<span><i class='fas fa-times-circle' style='color: red;'></i></span>" : "<span><i class='fas fa-check-circle' style='color: green;'></i></span>" ))); } else if($mask == 0){ $json_arr['rows'] = array_merge($json_arr['rows'], array(array( 'sales_date'=>$row['sales_date'], 'company_dealer'=>$row['company'] . " / " . $row['dealer'], 'customer_name'=>$row['customer_name'], 'number'=>$row['mobile'], 'sms_status'=>$row['sms_status'] == 0 ? "<span><i class='fas fa-times-circle' style='color: red;'></i></span>" : "<span><i class='fas fa-check-circle' style='color: green;'></i></span>", 'call_status'=>$row['call_status'] == 0 ? "<span><i class='fas fa-times-circle' style='color: red;'></i></span>" : "<span><i class='fas fa-check-circle' style='color: green;'></i></span>" ))); } } $json_arr['total'] = $fup_list_query_count; echo json_encode($json_arr); } function doctypeIssue($str) { // find '<>' in a string $return_str = ""; if(strpos($str, "<!") !== false \|\| strpos($str, ">") !== false){ $return_str = str_replace("<!", "", $str); $return_str = str_replace(">", "", $str); $return_str = str_replace("\"", "", $str); } else { $return_str = $str; } // echo "hays " . $return_str; exit; return $return_str; } ?>

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