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

    $utility = new Utility();

    $type = $db->escape($_POST['type']);
    $json_arr['rows'] = array();
    $arr = array();
    $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ? $_SESSION['user']['company_permissions_implode']  : '-1' ;

    if($type == 1){ // ----------------------------------------------- FINANCE TABLE LIST COUNT BOXES-->
        $company = ""; $dealer = "";
        $start_date = ""; $end_date = ""; $date_q = "";
        $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')");

        if(isset($_POST['company']) && $_POST['company'] != 0){
            $company = $db->escape($_POST['company']);
            $company = " AND f.company_id = $company ";
        } 
        else {
            $company = " AND f.company_id IN(" . $granted_company_ids . ") ";
        }

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

        if(isset($_POST['start_date']) && $_POST['start_date'] != ""){ //policy start date starting range
            $start_date = $db->escape($_POST['start_date']);
        }

        if(isset($_POST['end_date']) && $_POST['end_date'] != ""){ //policy start date ending range
            $end_date = $db->escape($_POST['end_date']);
            $date_q = " AND f.policy_start_date BETWEEN '$start_date' AND '$end_date' ";
        } 

        // ------------------------------------------------------------------ CATEOGRIZED BY VEHICLE -->>
        $total_count_query = "SELECT COUNT(*) FROM finance f 
            INNER JOIN source_company sc 
            ON sc.id = f.company_id 
            INNER JOIN source_dealer sd 
            ON sd.id = f.dealer_id 
            WHERE 1 " . $company . $dealer . $date_q . " AND f.status = 1 GROUP BY f.plate_cs_number1, f.plate_cs_number2";

        // ------------------------------------------------------------------ INDIVIDUAL INSURANCE: LAST RECORD 7 DAYS -->>
        // $last_seven = $db->select("SELECT DATE_FORMAT(NOW()-INTERVAL 7 DAY, '%Y-%m-%d');");
        // $between_seven_days = " AND DATE_FORMAT(f.date_uploaded, '%Y-%m-%d') BETWEEN '" . $last_seven . "' AND '" . $current_date . "'";

        $individual_insurance = "SELECT COUNT(*) FROM finance f 
            INNER JOIN source_company sc 
            ON sc.id = f.company_id 
            INNER JOIN source_dealer sd 
            ON sd.id = f.dealer_id 
            WHERE 1 AND f.status = 1 " . $company . $dealer . $date_q; // echo $individual_insurance; exit;

        // ------------------------------------------------------------------ THIS MONTH EXPIRATION -->>
        $fdate = date('Y-m-01');
        $ldate = date('Y-m-t');
        $date_between = " AND f.policy_end_date BETWEEN '" . $fdate . "' AND '" . $ldate . "'";

        $this_month_expiration_query = "SELECT COUNT(*) FROM finance f 
            INNER JOIN source_company sc 
            ON sc.id = f.company_id 
            INNER JOIN source_dealer sd 
            ON sd.id = f.dealer_id 
            WHERE 1 AND f.status = 1 " . $company . $dealer . $date_between . " GROUP BY f.plate_cs_number1, f.plate_cs_number2";

        // ------------------------------------------------------------------ ADDED TODAY -->>
        $date_added_today = " AND DATE_FORMAT(f.date_uploaded, '%Y-%m-%d') = '$current_date' ";
        $added_today_query = "SELECT COUNT(*) FROM finance f 
            INNER JOIN source_company sc 
            ON sc.id = f.company_id 
            INNER JOIN source_dealer sd 
            ON sd.id = f.dealer_id 
            WHERE 1 AND f.status = 1" . $company . $dealer . $date_added_today;

        $arr['total_count'] = number_format($db->select("SELECT COUNT(*) FROM (". $total_count_query . ") AS count"));
        $arr['exp_this_month'] = number_format($db->select("SELECT COUNT(*) FROM (". $this_month_expiration_query . ") AS count"));
        $arr['individual_insurance'] = number_format($db->select($individual_insurance));
        $arr['added_today'] = number_format($db->select($added_today_query));

        echo json_encode($arr);
    }
?>