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

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

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

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

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

    $request_type_arr = array(1);

    $upload_employee_id = $db->escape(($_POST['uploaded-by']));
    $request_type = $db->escape($_POST['level']);

    $reg_from = $db->escape(($_POST['reg-from']));
    $reg_to = $db->escape(($_POST['reg-to']));
    // $dms = $db->escape(($_POST['dms']));
    $dms = 0;
    $company = $db->escape(($_POST['company']));
    $dealer = $db->escape(($_POST['dealer']));
    $state = $db->escape(($_POST['state']));
    $city = $db->escape(($_POST['city']));
    // $upload_from = $db->escape(($_POST['upload-from']));
    // $upload_to = $db->escape(($_POST['upload-to']));
    $message_content = $db->escape(($_POST['message-content']));
    $character_count = $db->escape($_POST['char-count-hidden']);
    // $message_content = str_replace('\n', '\\\n', $message);
    $description = $db->escape(($_POST['description']));
    $type = $db->escape(($_POST['type']));

    $brand_id = $db->escape(($_POST['brand'])); 
    $model_id = $db->escape(($_POST['model']));
    $variant_id = $db->escape($_POST['variant']); 

    $filter_data = $db->escape(($_POST['filter-data']));
    $gender = $db->escape(($_POST['gender']));
    $age_from = $db->escape(($_POST['age-from']));
    $age_to = $db->escape(($_POST['age-to']));
    $sort_by = $db->escape(($_POST['sort-by']));
    $order_by = $db->escape(($_POST['order']));
    // $downloadable = $db->escape(($_POST['downloadable']));
    $downloadable = 0;
    $date_of_birth = $db->escape(($_POST['date-of-birth']));
    $customer_category = $db->escape(($_POST['category']));
    $customer_status = $db->escape(($_POST['active-status']));

    $finance_company = $db->escape(($_POST['finance-company']));
    $insurance_company = $db->escape(($_POST['insurance-company']));
    $insurance_type = $db->escape(($_POST['insurance-type']));
    
    $employee_id = $_SESSION['user']['id'];

    // echo $filter_data
    // exit;

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

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

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

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

                if($employee_id_approver == $employee_id){
                    $db->sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`,`status`) VALUES ('$request_id', '$employee_id_approver', NOW(),'1')");
                } 
                else {
                    $db -> sql_query_id("INSERT INTO `request_status`(`request_id`, `employee_id`, `date`) VALUES ('$request_id','$employee_id_approver', NOW())");
                    
                    $notification_email_subject = sprintf($_GLOBALS['request_mail_subject'], 'Customer');
                    $notification_system = sprintf($_GLOBALS['request_sms_service'],'customer');
                    $notification_sms = sprintf($_GLOBALS['request_data'],'customer',"\r\n");
                    $notification_mail = sprintf($_GLOBALS['request_data'],'customer',"<br>");

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

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

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

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

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

            $approver_count = "SELECT * FROM request_status WHERE request_id = '$request_id'"; //total
            $approved_count = "SELECT COUNT(id) FROM request_status WHERE request_id = '$request_id' AND status = 1";

            $total_approver = $db->sql_num_rows($approver_count);
            $count_approved = $db->select($approved_count);
            
            if($total_approver == $count_approved){
                writeToDB($request_id, $db);
            } 
            else {
            }

            // $db -> sql_query_id("INSERT INTO `request_status_overall` (`request_id`, `status`) VALUES ('$request_id', '0')");
            $bilang = $db->select("SELECT COUNT(*) FROM `request_status` WHERE request_id = '$request_id'");
            if($bilang == 1){
                $db->sql_query("INSERT INTO request_status_summary (`request_id`, `status`) VALUES ('$request_id', 1)");
            } 
            else {
                $db->sql_query("INSERT INTO request_status_summary (`request_id`, `status`) VALUES ('$request_id', 0)");
            }

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

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

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

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


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

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

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

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

                FROM `text_blast` tb 

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

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

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

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

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

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

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

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

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

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

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

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

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

                    $id = $data['id'];
                    $first_name = $data['first_name'];
                    $last_name = $data['last_name'];

                    if($data['suffix'] == "None"){
                        $suffix = "";
                    } 
                    else {
                        $suffix = $data['suffix'];
                    }

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

    function checkStatusFirst($request_id, $db){
        
    }
?>