<?php
    include_once("../../cfg/db.php");
    include_once("../notification/notification.php");
    include_once("../sms/send_sms.php");
    include_once("../mail/send_mail.php");
    include_once("../language/en-US.php");
    include_once("../../api/controllers/utility.php");

    if(!isset($_SESSION['user'])){
        exit();
    }

    $send_sms = new SMS(); //send message

    $utility = new Utility(); 
    $requestor_name = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'];
    $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ?  $_SESSION['user']['company_permissions_implode']  : '-1' ;

    // if(isset($_POST['checkboxArray'])){
    //     $request_type_arr =  $_POST['checkboxArray'];

        
    // } else {
    //     $return_arr["status"]=0;
    //     $return_arr["message"]=" Select request type.";  
    //     echo json_encode($return_arr);
    //     exit;
    // }

    $request_type_arr = array(1);

    // $upload_employee_id = $db->escape(($_POST['uploaded-by']));
    
    $request_id = $db->escape(($_POST['request_id']));
    $sort_by = $db->escape(($_POST['sort_by']));
    $order_by = $db->escape(($_POST['order_by']));
    $reg_from = $db->escape(($_POST['reg_from']));
    $reg_to = $db->escape(($_POST['reg_to']));
    $company = $db->escape(($_POST['company_id']));
    $dealer = $db->escape(($_POST['dealer_id']));
    $type = $db->escape(($_POST['type']));
    $customer_category = $db->escape(($_POST['category']));
    $customer_status = $db->escape(($_POST['customer_status']));
    $customer_data = $db->escape(($_POST['customer_data']));
    $gender = $db->escape(($_POST['gender']));
    $date_of_birth = $db->escape(($_POST['date_of_birth']));
    $age_from = $db->escape(($_POST['age_from']));
    $age_to = $db->escape(($_POST['age_to']));
    $brand_id = $db->escape(($_POST['brand'])); 
    $model_id = $db->escape(($_POST['model']));
    $variant_id = $db->escape($_POST['variant']); 
    $insurance_company = $db->escape(($_POST['insurance_company']));
    $insurance_type = $db->escape(($_POST['insurance_type']));
    $finance_company = $db->escape(($_POST['finance_company']));
    $state = $db->escape(($_POST['state']));
    $city = $db->escape(($_POST['city']));
    $description = $db->escape(($_POST['description']));
    $message_content = $db->escape(($_POST['message_content']));

    $update_query = "UPDATE `text_blast` SET 
        `sort_by`='$sort_by', 
        `order_by`='$order_by', 
        `activity_date_from`='$reg_from', 
        `activity_date_to`='$reg_to', 
        `company_id`='$company', 
        `dealer_id`='$dealer', 
        `customer_type`='$type', 
        `customer_category`='$customer_category', 
        `customer_status`='$customer_status', 
        `customer_data_type`='$customer_data', 
        `gender_id`='$gender', 
        `date_of_birth`='$date_of_birth', 
        `age_from`='$age_from', 
        `age_to`='$age_to', 
        `brand_id`='$brand_id', 
        `model_id`='$model_id', 
        `variant_id`='$variant_id', 
        `insurance_company_id`='$insurance_company', 
        `insurance_type_id`='$insurance_type', 
        `finance_company_id`='$finance_company', 
        `state_id`='$state', 
        `city_id`='$city', 
        `description`='$description', 
        `message_content`='$message_content' 
        WHERE `id` = '$request_id'
        ";
    $updated = $db->sql_query_num_inserted($update_query); 
    
    $notify = "SELECT rs.employee_id, ec.contact_number  
        FROM `request_status` rs 
        INNER JOIN `employee` e
        ON e.id = rs.employee_id 
        INNER JOIN `employee_contact` ec 
        ON ec.employee_id = e.employee_id 
        WHERE rs.`request_id` = '$request_id' AND rs.`status` = 4";

    // echo $notify; exit;
    $notify_approvers = $db->sql_query($notify);

    foreach($notify_approvers AS $row){
        $number = "0" . $row['contact_number'];
        $employee_id_approver = $row['employee_id'];
        $employee_id = $_SESSION['user']['employee_id'];

        //send message
        $send_sms->sendSMS($number, $requestor_name . " has re-submitted request", $variable);

        // create notification
        $notification = new Notification();
        $notification_email_subject = sprintf($_GLOBALS['request_mail_subject'], 'Customer');
                            $notification_system =  sprintf($_GLOBALS['request_sms_service'],'customer');
                            $notification_sms = sprintf($_GLOBALS['request_data'],'customer',"\r\n");
                            $notification_mail = sprintf($_GLOBALS['request_data'],'customer',"<br>");
        $notification->insertNotification('3',
            $request_id/*record id of specific record */ ,
            $employee_id_approver,  /*employee_id of person who receive the notif*/ 
            $employee_id,
            $_SESSION['user']['employee_fullname']. " has re-submitted a request", /*Notification Content*/ 
            $db
        );
        //send email
        // $send_mail->sendMail(
        //     $employee_id_approver,
        //     $notification_email_subject,
        //     $employee_name_approver.$notification_mail.$variable['site_link'].'request_approval.php'.$_GLOBALS['email_footer'],
        //     $variable
        // );
    }

    $update_request_status = "UPDATE `request_status` SET `status` = 0 WHERE `status` = 4 AND `request_id` = '$request_id'";
    $db->sql_query($update_request_status);

    $arr['status'] = 1; //success update

    // if($updated > 0){
    //     $arr['status'] = 1; //success update
    // }
    // else {
    //     $arr['status'] = 0; //failed update
    // }

    echo json_encode($arr);
    
    // $employee_id = $_SESSION['user']['id'];

    // // echo $filter_data
    // // exit;

    // foreach($request_type_arr as $request_type){
    //     $employee_id_approver_list = $db -> sql_query("SELECT `employee_id` FROM `sms_service_approver` WHERE `request_type_id` = '$request_type' ");
        
    //     if($employee_id_approver_list -> num_rows > 0){  
    //         $control_number = date("Y").'-'.uniqid();

    //         $query = "INSERT INTO `text_blast`(
    //             `control_number`, 
    //             `employee_id`, 
    //             `request_type_id`, 
    //             `message_content`, 
    //             `description`, 
    //             `company_id`, 
    //             `dealer_id`, 
    //             `state_id`, 
    //             `city_id`, 
    //             `request_date`, 
    //             `request_time`, 
    //             `upload_by_employee_id`, 
    //             `customer_type`, 
    //             `age_from`, 
    //             `age_to`, 
    //             `gender_id`, 
    //             `customer_data_type`, 
    //             `dms_id`, 
    //             `activity_date_from`, 
    //             `activity_date_to`, 
    //             `expire`,
    //             `brand_id`,
    //             `model_id`,
    //             `variant_id`, 
    //             `sort_by`,
    //             `order_by`,
    //             `date_of_birth`,
    //             `customer_category`,
    //             `customer_status`,
    //             `insurance_company_id`,
    //             `insurance_type_id`,
    //             `finance_company_id`,
    //             `is_downloadable`,
    //             `company_list`
    //         )
    //         VALUES (
    //             (select concat(YEAR(NOW()),'-', conv(
    //                 concat(
    //                     substring(uid,16,3), 
    //                     substring(uid,10,4), 
    //                     substring(uid,1,8))
    //             ,16,10) 
    //             div 10000 - (141427 * 24 * 60 * 60 * 1000)) as current_mills
    //             from (
    //             select uuid() uid) as alias),
    //             '$employee_id',
    //             '$request_type',
    //             '$message_content',
    //             '$description',
    //             '$company',
    //             '$dealer',
    //             '$state',
    //             '$city',
    //             NOW(),
    //             NOW(),
    //             '$upload_employee_id',
    //             '$type',
    //             '$age_from',
    //             '$age_to',
    //             '$gender',
    //             '$filter_data',
    //             '$dms',
    //             '$reg_from',
    //             '$reg_to',
    //             DATE_ADD(NOW(), INTERVAL 30 DAY), 
    //             '$brand_id',
    //             '$model_id',
    //             '$variant_id',
    //             '$sort_by',
    //             '$order_by',
    //             '$date_of_birth',
    //             '$customer_category',
    //             '$customer_status',
    //             '$insurance_company',
    //             '$insurance_type',
    //             '$finance_company',
    //             '$downloadable',
    //             '$granted_company_ids'
    //         )";

    //         //echo $query;exit();
    //         $request_id = $db -> sql_query_id($query); 

    //         while($row = $employee_id_approver_list->fetch_assoc()){
    //             $employee_id_approver = $row['employee_id'];

    //             if($employee_id_approver == $employee_id){
    //                 $db -> sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`,`status`) VALUES ('$request_id', '$employee_id_approver', NOW(),'1')");

    //                 writeToDB($request_id, $db);
    //             } else {
    //                 $db -> sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`) VALUES ('$request_id','$employee_id_approver', NOW())");

    //                 switch($request_type){
    //                     case "1":
    //                         $notification_email_subject = sprintf($_GLOBALS['request_mail_subject'], 'Customer');
    //                         $notification_system =  sprintf($_GLOBALS['request_sms_service'],'customer');
    //                         $notification_sms = sprintf($_GLOBALS['request_data'],'customer',"\r\n");
    //                         $notification_mail = sprintf($_GLOBALS['request_data'],'customer',"<br>");
    //                     break; 
    //                     case "2": 
    //                         // $notification_email_subject = sprintf($_GLOBALS['report_mail_subject'],'Vehicle');
    //                         // $notification_system =  sprintf($_GLOBALS['request_sms_system'],'vehicle');
    //                         // $notification_sms = sprintf($_GLOBALS['request_sms_service'],'vehicle',"\r\n");
    //                         // $notification_mail =sprintf($_GLOBALS['request_sms_service'],'vehicle',"<br>");
    //                     break; 
    //                     case "3":
    //                         // $notification_email_subject = sprintf($_GLOBALS['report_mail_subject'],'Service');
    //                         // $notification_system =  sprintf($_GLOBALS['request_sms_system'],'service');
    //                         // $notification_sms = sprintf($_GLOBALS['request_sms_service'],'service',"\r\n");
    //                         // $notification_mail =sprintf($_GLOBALS['request_sms_service'],'service',"<br>");
    //                     break; 
    //                     case "4":
    //                         // $notification_email_subject = sprintf($_GLOBALS['report_mail_subject'],'Sales');
    //                         // $notification_system =  sprintf($_GLOBALS['request_sms_system'],'sales');
    //                         // $notification_sms = sprintf($_GLOBALS['request_sms_service'],'sales',"\r\n");
    //                         // $notification_mail =sprintf($_GLOBALS['request_sms_service'],'sales',"<br>");
    //                     break; 
    //                 }

    //                 //-----------------------------------------------------------notify employee
    //                 // $suffix = ""; if($_SESSION['user']['suffix_name'] != 'None'){ $suffix = $_SESSION['user']['suffix_name']; } 
    //                 // $employee_name_approver =  $_SESSION['user']['first_name'].' '.$_SESSION['user']['middle_name'].' '.$_SESSION['user']['last_name'].' '.$suffix; 
                    
    //                 // $notification = new Notification();
    //                 // $notification->insertNotification('3',
    //                 //     $request_id/*record id of specific record */ ,
    //                 //     $employee_id_approver,  /*employee_id of person who receive the notif*/ 
    //                 //     $employee_id,
    //                 //     $_SESSION['user']['employee_fullname'].$notification_system, /*Notification Content*/ 
    //                 //     $db
    //                 // );

    //                 // $employee_contact_approver = "";
    //                 // $employee_email_approver = "";
    //                 // $employee_contact = $db -> sql_query("SELECT ec.`email_address`, ec.`contact_number` FROM `employee_contact` ec
    //                 // INNER JOIN employee e 
    //                 // ON ec.employee_id=e.employee_id WHERE e.id = '$employee_id_approver'");
                
    //                 // while($row1 = $employee_contact->fetch_assoc()) {
    //                 //     $employee_contact_approver = $row1['contact_number'];
    //                 //     $employee_email_approver = $row1['email_address'];
    //                 // }

    //                 // $send_sms = new SMS();
    //                 // $send_sms->sendSMS(
    //                 //     $employee_contact_approver,
    //                 //     $employee_name_approver.$notification_sms.$variable['site_link'].'request_approval.php'.
    //                 //     $_GLOBALS['sms_footer'],$variable
    //                 // );
    //                 // // echo ($employee_email_approver);exit();
    //                 // $send_mail = new Mail();
    //                 // $send_mail->sendMail(
    //                 //     $employee_email_approver,
    //                 //     $notification_email_subject,
    //                 //     $employee_name_approver.$notification_mail.$variable['site_link'].'request_approval.php'.$_GLOBALS['email_footer'],
    //                 //     $variable
    //                 // );

    //             }//end of if
    //         } //end of while

    //         // $db -> sql_query_id("INSERT INTO `request_status_overall` (`request_id`, `status`) VALUES ('$request_id', '0')");

    //         $return_arr["status"] = 1;
    //         $return_arr["message"]= "Request Successfully Requested.";
    //     } 
    //     else {
    //         $return_arr["status"] = 0;
    //         $return_arr["message"]= "No available approver for this request.";  
    //     }
    // }//end of foreach
        
    // echo json_encode($return_arr);

    // function clean($s){
    //     return preg_replace( "/\r|\n/", "", $s );
    // }

    // function isNotEmpty($data){
    //     return preg_match('/\S/', $data);
    // }

    // function fix_date($data,$format){ 
    //     if(!isNotEmpty($data)){
    //         return "";
    //     }
    //     // 01/23/2020 false
    //     // 23/01/2020 true
    //     $array_date = explode("/",explode(" ",$data)[0]);
    //     if($format){ //if format == true swap index 1 and 2 
    //             $return_date = $array_date[2].'-'.$array_date[1].'-'.$array_date[0];
    //             return $return_date;
    //     } 
    //     else {
    //         $return_date = $array_date[2].'-'.$array_date[0].'-'.$array_date[1];
    //             return $return_date;
    //     } 
    // } 


    // // =================================================================================================================================================writeToDB
    // function writeToDB($request_id, $db){
    //     $fmt = new NumberFormatter('PHP', NumberFormatter::CURRENCY);

    //     $parameter_query = "SELECT tb.`id`, tb.`control_number`, CONCAT(e.first_name,' ',e.middle_name,' ',e.last_name) as request_by, CONCAT(DATE_FORMAT(tb.`request_date`, '%M %d, %Y'), ' - ' ,TIME_FORMAT(tb.`request_time`, '%h:%i %p')) as request_date_time,  rt.name as request_type, tb.request_type_id, tb.`dms_id`, 
    //         IF(
    //             tb.`dms_id` = '0','All', (SELECT name FROM source_dms WHERE id = tb.dms_id)
    //         ) AS dms_name,
    //         tb.`company_id`,
    //         IF(
    //             tb.`company_id` = '0','All', (SELECT code FROM source_company WHERE id = tb.company_id)
    //         ) AS company_name, 
    //         tb.`dealer_id`,
    //         IF(
    //             tb.`dealer_id` = '0','All',   (SELECT code FROM source_dealer WHERE id = tb.dealer_id)
    //         ) AS dealer_name,
    //         tb.`customer_type` as customer_type_id,
    //         IF(`customer_type` = '0',
    //             'All'
    //         ,
    //             IF(
    //             `customer_type` = '1', 'Individual', 'Corporation'
    //             )
    //         ) AS customer_type,

    //         tb.`customer_category` as customer_category_id, 
    //         IF(
    //             `customer_category` = '0','All', (SELECT name FROM customer_category WHERE id = tb.customer_category)
    //         ) as customer_category,
    //         tb.`customer_status` as customer_status_id, 
    //         IF(
    //             `customer_status` = '0','All',
    //             IF(
    //             `customer_status` = '1', 'Active', 'Inactive'
    //             )
    //         ) as customer_status,
    //         tb.`activity_date_from`, tb.`activity_date_to`, 
            
    //             IF(
    //             tb.`activity_date_from` <> '' AND  tb.`activity_date_to` <> ''  ,CONCAT(tb.`activity_date_from`, ' - ', tb.`activity_date_to`), ''
    //         ) as activity_between,
            
    //         tb.`gender_id`,
    //         IF(
    //             tb.`gender_id` = '0','All',
    //             (SELECT gender_name FROM all_gender WHERE id = tb.gender_id)
    //             ) as gender_name , tb.`age_from`, tb.`age_to`,
    //         IF(
    //             tb.`age_from` <> '' AND  tb.`age_to` <> ''  ,CONCAT(tb.`age_from`, ' - ',tb.`age_to`), ''
    //         ) as age_between, tb.state_id,
    //             IF(
    //             tb.`state_id` = '0','All',
    //             (SELECT `provDesc` FROM `all_state` WHERE `provCode` = tb.`state_id` LIMIT 1 )
    //             ) as state_name , tb.city_id,
    //             IF(
    //             tb.`city_id` = '0','All',
    //             (SELECT `citymunDesc` FROM `all_city` WHERE `id` = tb.`city_id`)
    //             ) as city_name, tb.`uploaded_date_from`, tb.`uploaded_date_to`, 
    //             IF(
    //             tb.`uploaded_date_from` <> '' AND  tb.`uploaded_date_to` <> ''  , CONCAT(tb.`uploaded_date_from`, ' - ', tb.`uploaded_date_to`), ''
    //         ) as upload_between,
    //         tb.`upload_by_employee_id`,
    //             IF(
    //             tb.`upload_by_employee_id` = '','',
    //             (SELECT CONCAT(first_name,' ',middle_name,' ',last_name) FROM `employee` WHERE `id` = tb.`upload_by_employee_id`)
    //             ) as upload_by , tb.`customer_data_type`,
    //             IF(
    //             tb.`customer_data_type` = '0','',
    //             tb.`customer_data_type`
    //             ) as data_type,
    //             tb.message_content,
    //             tb.`brand_id`,
    //             IF(
    //             tb.`brand_id` = '0','All',
    //             (SELECT name FROM source_brand WHERE id = tb.brand_id)
    //             ) as brand_name, 
    //             tb.`model_id`,
    //             IF(
    //             tb.`model_id` = '0','All',
    //             (SELECT name FROM source_car_model WHERE id = tb.model_id)
    //             ) as model_name, 
    //             tb.`variant_id`,
    //             IF(
    //             tb.`variant_id` = '0','All',
    //             (SELECT name FROM source_car_model_variant WHERE id = tb.variant_id)
    //             ) as variant_name,
                
    //             tb.sort_by, tb.order_by, tb.date_of_birth,

    //             tb.insurance_company_id, 
    //             tb.insurance_type_id,
    //             tb.finance_company_id,
    //             IF(
    //             tb.`insurance_company_id` = '0','All',
    //             (SELECT name FROM source_insurance_company WHERE id = tb.insurance_company_id)
    //             ) as insurance_company, 
    //             IF(
    //             tb.`insurance_type_id` = '0','All',
    //             (SELECT name FROM source_insurance_type WHERE id = tb.insurance_type_id)
    //             ) as insurance_type, 
    //             IF(
    //             tb.`finance_company_id` = '0','All',
    //             (SELECT name FROM source_finance_company WHERE id = tb.finance_company_id)
    //             ) as finance_company,
    //             tb.record_count,
    //             tb.company_list 

    //             FROM `text_blast` tb 

    //             INNER JOIN employee e
    //             ON tb.employee_id=e.id
    //             INNER JOIN request_type rt 
    //             ON tb.`request_type_id`=rt.id
    //             WHERE tb.id = '".$request_id."' ";

    //             //WHERE tb.id = '".$request_session_data['request_id']."' "

    //             $request_data = $db->sql_query($parameter_query);

    //             foreach($request_data as $data){
    //                 $request_session_data['control_number'] =  isNotEmpty($data['control_number']) ? $data['control_number'] : 'N/A' ;
    //                 $request_session_data['request_by'] = isNotEmpty($data['request_by']) ? $data['request_by'] : 'N/A' ;
    //                 $request_session_data['request_date_time'] = isNotEmpty($data['request_date_time']) ? $data['request_date_time'] : 'N/A' ;
    //                 $request_session_data['request_type'] = isNotEmpty($data['request_type']) ? $data['request_type'] : '<strong>N/A</strong>' ;
    //                 $request_session_data['request_type_id'] = isNotEmpty($data['request_type_id']) ? $data['request_type_id'] : '<strong>N/A</strong>' ;

    //                 $request_session_data['request_between'] =  isNotEmpty($data['activity_between']) ? $data['activity_between'] : 'N/A' ;

    //                 $request_session_data['dms_name'] = isNotEmpty($data['dms_name']) ? $data['dms_name'] : 'N/A' ; 
    //                 $request_session_data['company_name'] = isNotEmpty($data['company_name']) ? $data['company_name'] : 'N/A' ;
    //                 $request_session_data['dealer_name'] = isNotEmpty($data['dealer_name']) ? $data['dealer_name'] : 'N/A' ;
    //                 $request_session_data['customer_type'] = isNotEmpty($data['customer_type']) ? $data['customer_type'] : 'N/A' ;
    //                 $request_session_data['customer_status'] = isNotEmpty($data['customer_status']) ? $data['customer_status'] : 'N/A' ;
    //                 $request_session_data['customer_category'] = isNotEmpty($data['customer_category']) ? $data['customer_category'] : 'N/A' ;
    //                 $request_session_data['gender_name'] = isNotEmpty($data['gender_name']) ? $data['gender_name'] : 'N/A' ;
    //                 // $report_session_data['brand_name'] = isNotEmpty($data['brand_name']) ? $data['brand_name'] : 'N/A' ;
    //                 // $report_session_data['model_name'] = isNotEmpty($data['model_name']) ? $data['model_name'] : 'N/A' ;
    //                 $request_session_data['brand_name'] = ($data['brand_name'] <> '0' ) ? $data['brand_name'] : 'All' ;
    //                 $request_session_data['model_name'] = ($data['model_name'] <> '0') ? $data['model_name'] : 'All' ;
    //                 $request_session_data['variant_name'] = ($data['variant_name'] <> '0') ? $data['variant_name'] : 'All' ;
    //                 $request_session_data['age_between'] = isNotEmpty($data['age_between']) ? $data['age_between'] : 'N/A' ;
    //                 $request_session_data['state_name'] = isNotEmpty($data['state_name']) ? $data['state_name'] : 'N/A' ;
    //                 $request_session_data['city_name'] = isNotEmpty($data['city_name']) ? $data['city_name'] : 'N/A' ;
    //                 $request_session_data['upload_between'] = isNotEmpty($data['upload_between']) ? $data['upload_between'] : 'N/A' ;
    //                 $request_session_data['upload_by'] = isNotEmpty($data['upload_by']) ? $data['upload_by'] : 'N/A' ;
    //                 $request_session_data['data_type'] = isNotEmpty($data['data_type']) ? $data['data_type'] : 'N/A' ;
    //                 $request_session_data['date_of_birth'] = isNotEmpty($data['date_of_birth']) ? $data['date_of_birth'] : 'N/A' ;
    //                 $request_session_data['message_content'] = isNotEmpty($data['message_content']) ? $data['message_content'] : 'N/A' ;
    //                 $request_session_data['insurance_company'] = isNotEmpty($data['insurance_company']) ? $data['insurance_company'] : 'N/A' ;
    //                 $request_session_data['insurance_type'] = isNotEmpty($data['insurance_type']) ? $data['insurance_type'] : 'N/A' ;
    //                 $request_session_data['finance_company'] = isNotEmpty($data['finance_company']) ? $data['finance_company'] : 'N/A' ;
    //                 $request_session_data['record_count'] = isNotEmpty($data['record_count']) ? $data['record_count'] : '0' ;
                
    //                 $dms_id = $data['dms_id'];
    //                 $company_id = $data['company_id'];
    //                 $dealer_id = $data['dealer_id'];
    //                 $age_from = $data['age_from'];
    //                 $age_to = $data['age_to'];
    //                 $gender_id = $data['gender_id'];
    //                 $state_id = $data['state_id'];
    //                 $city_id = $data['city_id'];
    //                 $brand_id = $data['brand_id'];
    //                 $model_id = $data['model_id'];
    //                 $variant_id = $data['variant_id'];
    //                 $insurance_company_id = $data['insurance_company_id'];
    //                 $insurance_type_id = $data['insurance_type_id'];
    //                 $finance_company_id = $data['finance_company_id'];
                    
    //                 $upload_by_id = $data['upload_by_employee_id'];
    //                 $customer_data_type = $data['customer_data_type'];
    //                 $customer_type = $data['customer_type_id']; 
    //                 $customer_category = $data['customer_category_id']; 
    //                 $customer_status = $data['customer_status_id']; 
    //                 $sort_by = $data['sort_by'];
    //                 $order_by = $data['order_by'];
    //                 $company_list = $data['company_list']; 

    //                 //dates format
    //                 $activity_date_from = fix_date($data['activity_date_from'], false);
    //                 $activity_date_to = fix_date($data['activity_date_to'],false);
    //                 $upload_date_from = fix_date($data['uploaded_date_from'], false);
    //                 $upload_date_to = fix_date($data['uploaded_date_to'],false);
    //                 $filter_date_of_birth = fix_date($data['date_of_birth'],false);

    //                 $request_count_data = getFilteredCustomerData($request_id, $company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$request_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$variant_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); 
    //             }   
    // } //writeToDB

    // function getFilteredCustomerData($request_id, $company_list, $insurance_company_id, $insurance_type_id, $finance_company_id, $control_number, $customer_category, $customer_status, $fmt, $filter_date_of_birth, $order_by, $sort_by, $brand_id, $model_id, $variant_id, $customer_type, $customer_data_type, $upload_by_id, $state_id, $city_id, $gender_id, $age_from, $age_to, $dms_id, $company_id, $dealer_id, $activity_date_from, $activity_date_to, $upload_date_from, $upload_date_to, $db){
    //     if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){
    //         $filter_date_upload = "  AND c.date_uploaded BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') ";
    //     } else {
    //         $filter_date_upload = "";
    //     } 
    
    //     if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){
    //         $filter_date_created = "  AND c.date_created BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; 
    //     } else {
    //         $filter_date_created = "";
    //     } 
        
    //     if(isNotEmpty($age_from) && isNotEmpty($age_to)){
    //         $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND  '$age_to' ";
    //     }else{
    //         $filter_age = "";
    //     }
    
    //     if(isNotEmpty($upload_by_id)){
    //         $filter_upload_by = " AND c.upload_by = '$upload_by_id' ";
    //     }else{
    //         $filter_upload_by = "";
    //     } 
        
    //     $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ;
    //     $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; 
        
    //     $gender =($gender_id != '0') ? " AND c.gender_id = '".$gender_id."' " : "" ;
    //     $dms =($dms_id != '0') ? " AND sc.dms_id = '".$dms_id."' " : "" ;
        
    //     $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ;
    //     $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ;
    //     $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ;
    
    //     $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ;
    //     $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ;
    
    //     //default
    //     $brand = ($brand_id != '0') ? " AND v.brand_id = '$brand_id' " : "" ;
    //     $model = ($model_id != '0') ? " AND v.model_id = '$model_id' " : "" ;
    //     $variant = ($variant_id != '0') ? " AND v.model_variant_description = '$variant_id' " : "";
    
    //     $complete_data = "";
    //     $incomplete_data = "";
    //     $mobile_phone_1 = "";  
    //     $email_1 = "";
    //     $address_1 = "";
    //     $date_of_birth ="";
    
    //     if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){
    //         switch($customer_data_type){
    //             case "Complete Data": 
    //                 $complete_data  = " AND ((c.date_of_birth <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '')  AND c.type = 1) 
    //                                             OR
    //                                         (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) ";
    //                 break;
    //             case "Incomplete Data": 
    //                 $incomplete_data = " AND ((c.date_of_birth = '' OR 
    //                 ag.id = 0 OR 
    //                 cc.address_1 = ''  OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = ''  or cc.email_1 = '' AND  c.type = 2)  "; 
    //                 break;
    //             case "No Mobile": 
    //                 $mobile_phone_1 =  " AND cc.mobile_phone_1 = '' "; 
    //                 break;
    //             case "No Email":  
    //                 $email_1 =  " AND cc.email_1 = '' "; 
    //                 break;
    //             case "No Address": 
    //                 $address_1 =  " AND cc.address_1 = '' "; 
    //                 break;
    //             case "No Gender": 
    //                 $gender =   " AND ag.gender_id = '0' AND c.type = '1' ";
    //                 break;
    //             case "No Date of birth": 
    //                 $date_of_birth = " AND c.date_of_birth = '' AND c.type = '1'  "; 
    //                 break;
    //         }
    //     }
    
    //     $insurance_company = ($insurance_company_id != '0') ? " AND (s.insurance_company_id = '$insurance_company_id') " : "" ;
    //     $insurance_type  = ($insurance_type_id != '0') ? " AND (s.insurance_type_id = '$insurance_type_id') " : "" ;
    //     $finance_company = ($finance_company_id != '0') ? " AND (s.finance_company_id = '$finance_company_id') " : "" ;
        
    
    //     if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){
    //         $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' ";
    //     }else{
    //         $filter_dob = "";
    //     } 
    
    //     switch(intval($sort_by)){
    //     case 1: //lastname
    //         $filter_sort_by = " c.last_name ";
    //         break;
    //         case 2: //firstname
    //         $filter_sort_by = " c.first_name ";
    //         break; 
    //         case 3: //corporation name
    //         $filter_sort_by = " c.corporation_name ";
    //         break;
    //     case 4: //date created
    //         $filter_sort_by = " c.date_created ";
    //         break;
    //     case 5: //date uploaded
    //         $filter_sort_by = " c.date_uploaded ";
    //         break;
    //     }
    
    //     $return_data = array(); 
        
    //     $query = " SELECT  %s
    //     FROM customer c
    //     INNER JOIN customer_category ccat 
    //     ON c.category_id = ccat.id
    //     INNER JOIN customer_contact cc 
    //     ON c.id = cc.customer_id 
    //     INNER JOIN employee e
    //     ON c.upload_by = e.id 
    //     INNER JOIN customer_dms cd 
    //     ON c.id = cd.customer_record_id
    //     INNER JOIN source_company_dealer scd 
    //     ON cd.company_dealer_id = scd.id 
    //     INNER JOIN source_company sc 
    //     ON scd.company_id = sc.id
    //     INNER JOIN all_gender ag 
    //     ON c.gender_id = ag.id
    //     INNER JOIN all_marital_status am
    //     ON c.marital_status_id = am.id
    //     INNER JOIN all_countries ac   
    //     ON c.nationality_id = ac.num_code
    //     INNER JOIN all_suffix asx   
    //     ON c.suffix_id = asx.id 
    //     LEFT OUTER JOIN vehicle v 
    //     ON c.id = v.customer_record_id
    //     LEFT OUTER JOIN sales s
    //     ON v.id = s.vehicle_id
    //     LEFT OUTER JOIN service se
    //     ON v.id = se.vehicle_id  
    //     WHERE 1
    //     %s /* upload_date */ 
    //     %s /* date_created */ 
    //     %s /* dms */
    //     %s /* company */
    //     %s /* dealer */ 
    //     %s /* age */ 
    //     %s /* gender */ 
    //     %s /* state */ 
    //     %s /* city */ 
    //     %s /* upload_by */ 
    //     %s /* complete_data */ 
    //     %s /* incomplete_id */ 
    //     %s /* no_address */ 
    //     %s /* no_dob */
    //     %s /* type */ 
    //     %s /* dob */  
    //     %s /* brand */ 
    //     %s /* model */  
    //     %s /* insurance_company */  
    //     %s /* insurance_type */  
    //     %s /* finance_company */  
    //     AND c.status = 1
    //     GROUP BY c.id 
    //     %s
    //     %s
    //     ORDER BY $filter_sort_by $order_by "; 
    
    //     // LEFT OUTER JOIN source_brand sbv 
    //     // ON v.brand_id=sbv.id 
    //     // LEFT OUTER JOIN source_car_model scmv 
    //     // ON v.model_id=scmv.id 
    //     // LEFT OUTER JOIN source_brand sbs
    //     // ON s.brand_id=sbs.id 
    //     // LEFT OUTER JOIN source_car_model scms
    //     // ON s.model_id=scms.id 
    //     // LEFT OUTER JOIN source_brand sbse
    //     // ON se.brand_id=sbse.id 
    //     // LEFT OUTER JOIN source_car_model scmse
    //     // ON se.model_id=scmse.id 

    //     // IF(c.type = 1,
    //     //     concat(c.first_name,' ',c.middle_name,' ',c.last_name, ' ', IF(c.suffix_id < 1, '',asx.suffix_name ))
    //     // ,   
    //     //     c.corporation_name
    //     // ) 
    
    //     $fields = " c.id,
    //     c.photo, 
    //     c.first_name, 
    //     c.last_name, 
    //     asx.suffix_name AS suffix, 
    //     c.corporation_name, 
    //     am.name as marital_status,
    //     ag.gender_name,
    //     c.date_of_birth,
    //     'Individual' as customer_type,
    //     c.date_uploaded,
    //     TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) as age_number,
    //     c.date_created,
    //     cc.mobile_phone_1,
    //     cc.mobile_phone_2,
    //     cc.email_1,
    //     cc.email_2,
    //     cc.business_phone,
    //     cc.fax_phone,
    //     cc.residential_phone,
    //     cc.address_1,
    //     c.contact_person,
    //     ac.nationality,
    //     c.occupation,
    //     c.no_of_children,
    //     concat(e.first_name,' ',e.last_name) as upload_by,
    //     c.type,
    
    //     (
    //                         IF( c.category_id <> 0,ccat.id,
    //                             IF( COUNT(se.id) > 0 && COUNT(s.id) < 1,
    //                             '1',
    //                                     IF( COUNT(se.id) < 1 && COUNT(s.id) > 0,
    //                                     '2',
    //                                             IF( COUNT(v.id) = 1 &&  COUNT(se.id) > 0 && COUNT(s.id) > 0,
    //                                             '3',
    //                                                     IF( COUNT(v.id) > 1 && COUNT(v.id) < 4,
    //                                                     '4',
    //                                                             IF( COUNT(v.id) > 3,
    //                                                                 '5','0'/*VVIP*/
    //                                                             ) 
    //                                                     ) 
    //                                             )   
    //                                     ) 
    //                             ) 
    //                             ) 
    //                     ) as customer_category,
    //     IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365)  || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) ,
    //     '1','2'
    //     ) 
    //     as isActive";
    
    //     $count = " 
    //     (
    //                         IF( c.category_id <> 0,ccat.id,
    //                             IF( COUNT(se.id) > 0 && COUNT(s.id) < 1,
    //                             '1',
    //                                     IF( COUNT(se.id) < 1 && COUNT(s.id) > 0,
    //                                     '2',
    //                                             IF( COUNT(v.id) = 1 &&  COUNT(se.id) > 0 && COUNT(s.id) > 0,
    //                                             '3',
    //                                                     IF( COUNT(v.id) > 1 && COUNT(v.id) < 4,
    //                                                     '4',
    //                                                             IF( COUNT(v.id) > 3,
    //                                                                 '5','0'/*VVIP*/
    //                                                             ) 
    //                                                     ) 
    //                                             )   
    //                                     ) 
    //                             ) 
    //                             ) 
    //                     ) as customer_category,
    //     IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365)  || (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) ,
    //     '1','2'
    //     )
    //     as isActive"; 
        
    //     // echo sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus); return;
    //     $data_list =  $db -> sql_query(sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$variant,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus));  
    //     $query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($query,$count,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus) ." ) as total_count");
        
    //     $record_counter = 0;
    //     if( $query_count > 0){
    //         $record_counter++;
    //         foreach ($data_list as $data){ 
    //             $involvements = "SELECT sd.name AS dms, sc.code AS company, sdr.code AS dealer 
    //             FROM `customer_dms` cd 
    //             INNER JOIN source_company_dealer scd 
    //             ON cd.company_dealer_id = scd.id 
    //             INNER JOIN source_company sc 
    //             ON sc.id = scd.company_id 
    //             INNER JOIN source_dms sd 
    //             ON sc.`dms_id` = sd.id 
    //             INNER JOIN source_dealer sdr 
    //             ON sdr.id = scd.dealer_id 
    //             WHERE `customer_record_id` IN ('".$data['id']."')";  

    //             //echo $involvements; exit;
    //             $in = $db->return_result($involvements);
    //             $dms = array();
    //             $company = array();
    //             $dealer = array();  

    //             foreach($in as $data1){
    //                 $dms = array_merge($dms, array($data1['dms']));
    //                 $company = array_merge($company, array($data1['company']));
    //                 $dealer = array_merge($dealer, array($data1['dealer']));
    //             } 

    //             $dms_new = array_unique($dms);
    //             $company_new = array_unique($company);
    //             $dealer_new = array_unique($dealer); 
    //             $list_dms = implode(', ', $dms_new);
    //             $list_company = implode(', ', $company_new);
    //             $list_dealer = implode(', ', $dealer_new);
            
    //             $data_to_insert = array(); $ins = "";
                                    
    //             if(intval($data['type']) === 1){ //individual
    //                 $suffix = "";

    //                 $id = $data['id'];
    //                 $first_name = $data['first_name'];
    //                 $last_name = $data['last_name'];
    //                 if($data['suffix'] == "None"){
    //                     $suffix = "";
    //                 } else {
    //                     $suffix = $data['suffix'];
    //                 }
    //                 $mob_num = $data['mobile_phone_1'];
                    
    //                 $ins = "INSERT INTO `txtblast_sms_service_approved` (`cust_id`, `request_id`, `first_name`, `last_name`, `suffix`, `mobile_number`, `customer_type`) 
    //                     VALUES('$id', '$request_id', '$first_name', '$last_name', '$suffix', '$mob_num', 1)";
    //                 // echo $ins; exit;
    //                 $db->sql_query($ins);
    //             } else {
    //                 $id = $data['id'];
    //                 $corp_name = $data['corporation_name'];
    //                 $mob_num = $data['mobile_phone_1'];
                    
    //                 $ins = "INSERT INTO `txtblast_sms_service_approved` (`cust_id`, `request_id`, `company_name`, `mobile_number`, `customer_type`) 
    //                     VALUES('$id', '$request_id', '$corp_name', '$mob_num', 2)";
    //                 // echo $ins; exit;
    //                 $db->sql_query($ins);
    //             }        
    //         }
    //     }
    // } //getFilteredCustomerData
?>