<?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));
        // var_dump($thank_you_list_query); 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;
    }
?>