<?php
	include_once("../../cfg/db.php");

    $filter_date = "";
    $type = $db->escape($_POST['type']);

    if(isset($_POST['filter_date']) && $_POST['filter_date'] != ""){
        $date = $db -> escape(trim($_POST['filter_date']));
        $filter_date = substr($date, 6, 4) . "-" . substr($date, 0, 2) . "-" . substr($date, 3, 2); //2021-08-18
    } 
    else {
        $filter_date = "";
    }

    if(isset($_POST['filter_status']) && $_POST['filter_status'] != ""){
        $get_status = $db -> escape(trim($_POST['filter_status'])); 
        
        if($get_status > 0){
            $filter_status = " AND status > 0 "; 
        } 
        else {
            $filter_status = " AND status = '$get_status' "; 
        }
    } 
    else {
        $filter_status = ""; 
    }

    //counting purpose
    if($type == 1){ // birthday celebrant
        $fetch_today = $db->sql_query("SELECT NOW() AS today"); 
        $current_date = ""; 

        foreach($fetch_today AS $data){ 
            $current_date = substr($data['today'], 0, 10); 
        }

        $query_total = "SELECT COUNT(*) FROM `text_blast_birthday_record` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '$filter_date'"; // total
        $query_sent = "SELECT COUNT(*) FROM `text_blast_birthday_record` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '$filter_date' AND (`sms_response` = 0 OR `sms_response` = -2)"; // sent
        $query_pending = "SELECT COUNT(*) FROM `text_blast_birthday_record` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` = -1"; // pending
        $query_unsent = "SELECT COUNT(*) FROM `text_blast_birthday_record` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` > 0"; // unsent
        // echo $query_sent; return;

        $arr['count_total'] = $db->select($query_total); 
        $arr['count_sent'] = $db->select($query_sent); 
        $arr['count_pending'] = $db->select($query_pending); 
        $arr['count_unsent'] = $db->select($query_unsent); 

        echo json_encode($arr); 
    } 
    else if($type == 2){ // insurance expiration 
        $query_total = "SELECT COUNT(*) FROM `txtblast_insurance_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` != -3"; // total
        $query_sent = "SELECT COUNT(*) FROM `txtblast_insurance_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND (`sms_response` = 0 OR `sms_response` = -2)"; // sent
        $query_pending = "SELECT COUNT(*) FROM `txtblast_insurance_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` = -1"; // pending
        $query_unsent = "SELECT COUNT(*) FROM `txtblast_insurance_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` > 0"; // unsent

        // echo $query_total; return;
        $arr['count_total'] = $db->select($query_total);
        $arr['count_sent'] = $db->select($query_sent);
        $arr['count_pending'] = $db->select($query_pending);
        $arr['count_unsent'] = $db->select($query_unsent);

        echo json_encode($arr);
    } 
    else if($type == 3){ // newly imported
        $yesterday = $db->select("SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)"); 

        $query_total = "SELECT COUNT(*) FROM `text_blast_new_import_record` WHERE DATE_FORMAT(date_uploaded, '%Y-%m-%d') = '$filter_date'" ; //total
        $query_sent = "SELECT COUNT(*) FROM `text_blast_new_import_record` WHERE DATE_FORMAT(date_uploaded, '%Y-%m-%d') = '$filter_date' AND (`status` = 0 OR `status` = -2)"; //sent
        $query_pending = "SELECT COUNT(*) FROM `text_blast_new_import_record` WHERE DATE_FORMAT(date_uploaded, '%Y-%m-%d') = '$filter_date' AND `status` = -1"; //pending
        $query_unsent = "SELECT COUNT(*) FROM `text_blast_new_import_record` WHERE DATE_FORMAT(date_uploaded, '%Y-%m-%d') = '$filter_date' AND `status` > 0"; //unsent
        // echo $query_total; return;

        $arr['count_total'] = $db->select($query_total);
        $arr['count_sent'] = $db->select($query_sent);
        $arr['count_pending'] = $db->select($query_pending);
        $arr['count_unsent'] = $db->select($query_unsent);

        echo json_encode($arr);
    } 
    else if($type == 4){ // pdc expiration 
        $query_total = "SELECT COUNT(*) FROM `txtblast_pdc_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date'"; // total
        $query_sent = "SELECT COUNT(*) FROM `txtblast_pdc_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND (`sms_response` = 0 OR `sms_response` = -2)"; // sent
        $query_pending = "SELECT COUNT(*) FROM `txtblast_pdc_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` = -1"; // pending
        $query_unsent = "SELECT COUNT(*) FROM `txtblast_pdc_reminder_record` WHERE DATE_FORMAT(`action_date`, '%Y-%m-%d') = '$filter_date' AND `sms_response` > 0"; // unsent

        // echo $query_total; return;
        $arr['count_total'] = $db->select($query_total);
        $arr['count_sent'] = $db->select($query_sent);
        $arr['count_pending'] = $db->select($query_pending);
        $arr['count_unsent'] = $db->select($query_unsent);

        echo json_encode($arr);
    } 
    else if($type == 10){ // NEW IMPORT TOTAL HEADER MODAL
        $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')"); $mask_exclude_corp = ""; $corp = "";

        if(isset($_POST['mask_exclude_corp']) && $_POST['mask_exclude_corp'] != ""){
            $mask_exclude_corp = $db->escape($_POST['mask_exclude_corp']);

            if($mask_exclude_corp == 0){
                $corp = "";
            } 
            else if($mask_exclude_corp == 1){
                $corp = " AND c.type = 1 ";
            }
        }

        $new_import_query = "SELECT COUNT(c.id) 
            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 DATE_FORMAT(s.date_uploaded,'%Y-%m-%d') = '" . $current_date . "' " . $corp . "GROUP BY c.id ORDER BY c.last_name ASC";

        $arr['new_import_count'] = $db->select("SELECT COUNT(1) FROM (" . $new_import_query . ") AS count");

        echo json_encode($arr);
    } 
    else if($type == 410){ // BG REPORT
        $company = ""; $dealer = ""; $status = ""; $date_to = ""; $date_from = "";

        if(isset($_POST['start_date']) && $_POST['start_date'] != ""){
            $date_from = $db -> escape(trim($_POST['start_date']));
            // $date_from = substr($date_from, 6, 4) . "-" . substr($date_from, 0, 2) . "-" . substr($date_from, 3, 2); // 2021-08-18
        }

        if(isset($_POST['end_date']) && $_POST['end_date'] != ""){
            $date_to = $db -> escape(trim($_POST['end_date']));
            // $date_to = substr($date_to, 6, 4) . "-" . substr($date_to, 0, 2) . "-" . substr($date_to, 3, 2); // 2021-08-18
        }

        if(isset($_POST['company']) && $_POST['company'] != ""){
            $company = $db->escape($_POST['company']);
            $company = " AND bday.`company_id` = '$company' ";
        } 

        if(isset($_POST['dealer']) && $_POST['dealer'] != ""){
            $dealer = $db->escape($_POST['dealer']);
            $dealer = " AND bday.`dealer_id` = '$dealer' ";
        }

        if(isset($_POST['status']) && $_POST['status'] != ""){
            $get_status = $db->escape($_POST['status']);
            if($get_status == -3){
                $status = "";
            } 
            else if($get_status > 0){
                $status = $db->escape($_POST['status']);
                $status = " AND bday.`status` > 0 ";
            }
            else {
                $status = $db->escape($_POST['status']);
                $status = " AND bday.`status` = '$status' ";
            }
        }

        $celebrant_query = "SELECT COUNT(*) 
            FROM `text_blast_birthday_record` bday 
            INNER JOIN customer_dms cdms 
            ON bday.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 bday.date BETWEEN '$date_from' AND '$date_to' %s %s %s GROUP BY bday.id ORDER BY bday.date ASC";
        // echo "SELECT COUNT(*) FROM (".sprintf($celebrant_query, $status, $company, $dealer).") AS count"; return;
        
        $arr['count_total'] = $db->select("SELECT COUNT(*) FROM (".sprintf($celebrant_query, $status, $company, $dealer).") AS count");
        echo json_encode($arr);
    } 
    else if($type == 411){ // NEW IMPORT REPORT
        $company = ""; $dealer = ""; $status = "";

        if(isset($_POST['company']) && $_POST['company'] != ""){
            $company = $db->escape($_POST['company']);
            $company = " AND `company_id` = '$company' ";
        } 

        if(isset($_POST['dealer']) && $_POST['dealer'] != ""){
            $dealer = $db->escape($_POST['dealer']);
            $dealer = " AND `dealer_id` = '$dealer' ";
        }

        if(isset($_POST['status']) && $_POST['status'] != ""){
            $get_status = $db->escape($_POST['status']);
            if($get_status == -3){
                $status = "";
            } 
            else {
                $status = $db->escape($_POST['status']);
                $status = " AND `status` = '$status' ";
            }
        }

        $start_date = $db->escape($_POST['start_date']);
        $end_date = $db->escape($_POST['end_date']);

        $query_total = "SELECT COUNT(*) FROM `text_blast_new_import_record` WHERE (DATE_FORMAT(date_uploaded, '%Y-%m-%d') BETWEEN '$start_date' AND '$end_date')" . $status . $company . $dealer . " GROUP BY id"; //total
        // echo "SELECT COUNT(1) FROM (".$query_total.") AS count"; return;

        $arr['count_total'] = $db->select("SELECT COUNT(1) FROM (".$query_total.") AS count");
        echo json_encode($arr);
    }
?>