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

    $utility = new Utility();
    $json_arr['rows'] = array();

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

    $type = $db->escape($_GET['type']);
    $search = ""; $offset = ""; $offset_limit = "";

    if(isset($_GET['search'])){
        $search = $db->escape(trim($_GET['search']));
    }

    if(isset($_GET['offset'])){
        $offset = $db->escape(trim($_GET['offset']));
    }

    if(isset($_GET['limit'])){
        $limit = $db->escape(trim($_GET['limit']));
        $offset_limit = " LIMIT ". $offset.",".$limit;
    }

    if($type == 1){ // finance table list
        $start_date = ""; $end_date = ""; $company = ""; $dealer = ""; $date_q = ""; $filter_box_q = "";
        $start_date = $db->escape($_GET['start_date']);
        $end_date = $db->escape($_GET['end_date']);
        $filter_box = "";

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

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

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

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

        if(isset($_GET['filter_box']) && $_GET['filter_box'] != 0){
            $filter_box = $db->escape($_GET['filter_box']);
            $current_date = $db->select("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')");

            if($filter_box == 1){ // categorized
                $filter_box_q = " GROUP BY f.plate_cs_number1, f.plate_cs_number2";
            } 
            else if($filter_box == 2){ // individual insurance
                $filter_box_q = "";
            } 
            else if($filter_box == 3){ // expiring this month
                $fdate = date('Y-m-01');
                $ldate = date('Y-m-t');

                $filter_box_q = " AND f.policy_end_date BETWEEN '$fdate' AND '$ldate' GROUP BY f.plate_cs_number1, f.plate_cs_number2";
            }
            else if($filter_box == 4){ // added today
                $filter_box_q = " AND DATE_FORMAT(f.date_uploaded, '%%Y-%%m-%%d') = '$current_date'";
            } 
        } 
        else {
            $filter_box_q = " GROUP BY f.plate_cs_number1, f.plate_cs_number2";
        }

        $count = " COUNT(*) ";
        $fields = "";
        if($filter_box == 2){
            $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer ";
        } 
        else if($filter_box == 4){
            $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer ";
        }
        else {
            $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(MAX(f.policy_end_date), '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer ";
        }

        $finance_table_query = "SELECT %s 
            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 (concat(f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname) LIKE '%%%s%%') AND f.status = 1 " . $company . $dealer . $date_q . $filter_box_q . " ORDER BY f.date_uploaded DESC";
        
        // echo sprintf($finance_table_query, $fields, $search); return;
        $finance_list_query = $db->sql_query(sprintf($finance_table_query, $fields, $search) . $offset_limit);
        // $finance_list_count = $db->select("SELECT COUNT(*) FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT");
        $finance_list_count = 0;
        if($filter_box == 2){
            $finance_list_count = $db->select(sprintf($finance_table_query, $count, $search));
        } 
        else if($filter_box == 4){
            $finance_list_count = $db->select(sprintf($finance_table_query, $count, $search));
        }
        else {
            $finance_list_count = $db->select("SELECT COUNT(*) FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT");
        }

        while($row = $finance_list_query->fetch_assoc()) {
            $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                'plate_cs_number1'=>$row['plate_cs_number1'], 
                'plate_cs_number'=>$row['plate_cs_number2'] != "" ? $row['plate_cs_number1'] . " / " . $row['plate_cs_number2'] : $row['plate_cs_number1'], 
                'policy_number'=>$row['policy_number'],
                'customer_fullname'=>$row['customer_fullname'], 
                'inception_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'], 
                'company'=>$row['company'], 
                'dealer'=>$row['dealer']
            )));   
        }

        $json_arr['total'] = $finance_list_count;
            
        echo json_encode($json_arr);
    } 
    else if($type == 2){ // ---------------------------------------------- finance_information.js -->>
        $plate_cs_number_1 = ""; $plate_cs_number_2 = ""; $where_clause_query = "";
        $plate_cs_number = $db->escape($_GET['plate_cs_number']);

        if(strpos($plate_cs_number, '/') > 0){
            $multi_data = array();
            $multi_data = explode("/", $plate_cs_number);

            $plate_cs_number_1 = $multi_data[0];
            $plate_cs_number_2 = $multi_data[1];

            $where_clause_query = "(f.plate_cs_number1 = '$plate_cs_number_1' OR f.plate_cs_number1 = '$plate_cs_number_2' OR f.plate_cs_number2 = '$plate_cs_number_1' OR f.plate_cs_number2 = '$plate_cs_number_2') ";
        } 
        else {
            $plate_cs_number_1 = $plate_cs_number;
            $where_clause_query = "(f.plate_cs_number1 = '$plate_cs_number_1' OR f.plate_cs_number2 = '$plate_cs_number_1') ";
        }

        $count = " COUNT(*) ";
        $fields = " f.id AS record_id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname, f.customer_email, fsb.name AS brand, fibt.name AS insurance_type, fip.name AS insurance_provider, f.customer_mobile_number, DATE_FORMAT(f.policy_issue_date, '%b %d, %Y') AS policy_issue_date, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, f.sales_consultant ";

        $finance_information_query = "SELECT %s 
            FROM finance f 
            INNER JOIN finance_ins_business_type fibt ON fibt.id = f.insurance_business_type 
            INNER JOIN finance_ins_provider fip ON fip.id = f.insurance_provider 
            INNER JOIN finance_source_brand fsb ON fsb.id = f.brand_id 
            WHERE f.status = 1 AND " . $where_clause_query . " ORDER BY f.policy_issue_date ASC";

        // echo sprintf($finance_information_query, $fields); return;
        $finance_information_query_list = $db->sql_query(sprintf($finance_information_query, $fields) . $offset_limit);
        $finance_information_count = $db->select(sprintf($finance_information_query, $count));

        $ctr = 1;

        while($row = $finance_information_query_list->fetch_assoc()) {
            $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                'no'=>$ctr, 
                'finance_id'=>$row['record_id'], 
                'policy_number'=>$row['policy_number'], 
                'customer_fullname'=>$row['customer_fullname'], 
                'customer_mobile_number'=>$row['customer_mobile_number'], 
                'customer_email'=>$row['customer_email'], 
                'brand'=>$row['brand'], 
                'insurance_type'=>$row['insurance_type'], 
                'insurance_provider'=>$row['insurance_provider'], 
                'policy_issue_date'=>$row['policy_issue_date'], 
                'policy_start_end_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date']
            )));
            $ctr++; 
        }

        $json_arr['total'] = $finance_information_count;
            
        echo json_encode($json_arr);
    } 
    else if($type == 3){ // -------------------------------------------- finance_remaining_days.js -->>
        $finance_id = $db->escape($_GET['fin_id']);
        // $plate_cs_number = $db->escape($_GET['plate_cs_number']);
        // $policy_issue_date = $db->escape($_GET['policy_issue_date']);

        $rem_day = array(); $sms_status = "";
        
        $remaining_days = $db->select("SELECT remaining_days FROM auto_txt_blast WHERE id = 2");
        $rem_day = explode(",", $remaining_days);

        $policy_end_date = $db->select("SELECT policy_end_date FROM finance WHERE id = '$finance_id'");
        $date_diff_now = $db->select("SELECT DATEDIFF(NOW(), '2021-11-23')");

        for($i=0; $i < count($rem_day); $i++){
            $remaining = $rem_day[$i]; 
            $var_date = $db->select("SELECT DATE_FORMAT(DATE_SUB('" . $policy_end_date . "', INTERVAL " . $remaining . " DAY), '%b %d, %Y')");
            $var_date_db = $db->select("SELECT DATE_FORMAT(DATE_SUB('" . $policy_end_date . "', INTERVAL " . $remaining . " DAY), '%Y-%m-%d')");
            $var_date_now = $db->select("SELECT DATEDIFF(NOW(), '$var_date_db')");
            $current_date = $db->select("SELECT CURDATE()");

            $count = " COUNT(*) ";
            $fields = " tbirr.sms_response ";
            
            $sms_query = "SELECT %s 
                FROM `txtblast_insurance_reminder_record` tbirr 
                INNER JOIN `finance` f ON f.id = tbirr.finance_id 
                WHERE tbirr.finance_id = '$finance_id' AND f.policy_end_date = '$policy_end_date' AND tbirr.reminder_type = '$remaining'"; // echo sprintf($sms_query, $fields); exit;
            
            $get_record_count = $db->select(sprintf($sms_query, $count));

            if($get_record_count > 0){
                $sms_status = $db->select(sprintf($sms_query, $fields));
            } 
            else { // echo "".$var_date_db." > ".$current_date.""; return;
                if($var_date_db < $current_date){
                    $sms_status = 200; // passed
                } 
                else {
                    $sms_status = 100; // waiting
                }
            }

            $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                'reminder_type'=>$rem_day[$i] . "-day reminder", 
                'reminder_date'=>$var_date, 
                'sms_status'=>$sms_status == 0 ? "<span class='badge bg-success'>SMS SENT</span>" : 
                    ($sms_status == 1 ? "<span class='badge bg-danger'>INVALID NUMBER</span>" : 
                    ($sms_status == 2 ? "<span class='badge bg-danger'>UNSUPPORTED #</span>" : 
                    ($sms_status == 30 ? "<span class='badge bg-info'>UNSUBSCRIBED</span>" :
                    ($sms_status == -2 ? "<span class='badge bg-info'>SMS send feature is not yet activated</span>" : 
                    ($sms_status == -1 ? "<span class='badge bg-warning'>PENDING</span>" : 
                    ($sms_status == 200 ? "<span class='badge bg-secondary'>PASSED</span>" : 
                    ($sms_status == -3 ? "<span class='badge bg-dark'>SKIPPED</span>" : 
                    "<span class='badge bg-primary'>WAITING</span>")))))))
            )));
            $sms_status = "";
            
        }
            
        echo json_encode($json_arr);
    } 
    else if($type == 4){ // --------------------------------------- FINANCE REPORT : dist/js/finance/finance_reports.js -->>
        $policy_end_date_start = ""; $policy_end_date_end = ""; $filter_date = "";
        $company = ""; $dealer = ""; 
        $brand = ""; $model = ""; $variant = "";
        $remaining_days = ""; $inactive = "";
        $fields = "";
        $set = 0; // 1-rem days, 2-expiration date, 3-inactive
        $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year");
        $previous_year = $current_year - 1;
        $current_date = $db->select("SELECT CURDATE()");

        if(isset($_GET['start_date']) && $_GET['start_date'] != ""){
            $policy_end_date_start = $db->escape($_GET['start_date']);
        }

        if(isset($_GET['end_date']) && $_GET['end_date'] != ""){
            $policy_end_date_end = $db->escape($_GET['end_date']);
        }

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

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

        if(isset($_GET['brand']) && $_GET['brand'] != 0){
            $brand = $db->escape($_GET['brand']);
            $brand = " AND t1.brand_id = '$brand' ";
        }

        if(isset($_GET['model']) && $_GET['model'] != 0){
            $model = $db->escape($_GET['model']);
            $model = " AND t1.model_id = '$model' ";
        }

        if(isset($_GET['variant']) && $_GET['variant'] != 0){
            $variant = $db->escape($_GET['variant']);
            $variant = " AND t1.variant_id = '$variant'";
        }

        if(isset($_GET['remaining_days']) && $_GET['remaining_days'] != 0){
            $remaining_days = $db->escape($_GET['remaining_days']);
            $rem_day = array();
            $rem_day = explode(",", $remaining_days);
            $remaining_start_date = $db->select("SELECT DATE_ADD(CURDATE(), INTERVAL ".$rem_day[0]." DAY)");
            $remaining_end_date = $db->select("SELECT DATE_ADD(CURDATE(), INTERVAL ".$rem_day[1]." DAY)");
            $remaining_days = " AND f.policy_end_date BETWEEN '$remaining_start_date' AND '$remaining_end_date' ";
        } 
        else {
            $remaining_days = "";
        }

        if($policy_end_date_start != ""){
            $filter_date = " AND DATE_FORMAT(policy_end_date, '%%Y-%%m-%%d') BETWEEN '$policy_end_date_start' AND '$policy_end_date_end' ";
        }

        if(isset($_GET['inactive']) && $_GET['inactive'] != 0){
            $set = 3;
            $inactive = $db->escape($_GET['inactive']);
            $current_date = $db->select("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS today");
            $sub_date = $db->select("SELECT DATE_SUB(CURDATE(), INTERVAL " . $inactive . " DAY) AS day;");

            if($inactive == 1){ // All inactive
                $inactive = " HAVING max_year <= '$current_date'";
            } 
            else {
                $inactive = " HAVING max_year <= '$sub_date'";
                // $filter_date = " AND f.policy_end_date < '$sub_date' ";
            }
        }

        $count = " COUNT(*) ";

        if($set == 3){ // inactive filter is selected
            $fields = " MAX(f.policy_end_date) as max_year, f.id, f.customer_fullname, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date ";
        } 
        else {
            $fields = " f.id, f.customer_fullname, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date ";
        }

        $query = "SELECT %s 
            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 AND concat(f.customer_fullname, f.policy_number, f.plate_cs_number1, f.plate_cs_number2) LIKE '%%%s%%' " . $filter_date . $company . $dealer . $variant . $remaining_days . " GROUP BY f.plate_cs_number1, f.plate_cs_number2 " . $inactive . " ORDER BY f.policy_end_date DESC";

        // echo "SELECT COUNT(1) FROM (".sprintf($query, $fields, $search) . ") AS COUNT"; return;
        $finance_report_query_list = $db->sql_query(sprintf($query, $fields, $search) ." $offset_limit");
        // $var = "SELECT COUNT(1) FROM (" . sprintf($query, $fields, $search) . ") AS COUNT";
        // echo $var; return;
        // $finance_report_query_count = $db->select(sprintf($query, $count, $search));
        $finance_report_query_count = $db->select("SELECT COUNT(1) FROM (" . sprintf($query, $fields, $search) . ") AS COUNT");

        while($row = $finance_report_query_list->fetch_assoc()) {
            $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                'id'=>$row['id'],
                'policy_number'=>$row['policy_number'],
                'plate_cs_number'=>$row['plate_cs_number1'] != "" && $row['plate_cs_number2'] == "" ? $row['plate_cs_number1'] : 
                    ($row['plate_cs_number1'] == "" && $row['plate_cs_number2'] != "" ? $row['plate_cs_number2'] : 
                    $row['plate_cs_number1'] . "/" . $row['plate_cs_number2']),
                'customer_fullname'=>$row['customer_fullname'], 
                'company_dealer'=>$row['company'] . " / " . $row['dealer'], 
                // 'model_year' => $row['vehicle_release_year'],
                'policy_end_date'=>$row['policy_end_date']
            )));   
        }

        $json_arr['total'] = $finance_report_query_count;
            
        echo json_encode($json_arr);
    } 
    else if($type == 5){ // finance dashboard 
        $box = $db->escape($_GET['box']);
        $company = ""; $dealer = "";
        if(isset($_GET['company']) && $_GET['company'] != 0){
            $company = $db->escape($_GET['company']);
            $company = " AND f.company_id = $company ";
        } 
        else {
            $company = " AND f.company_id IN(" . $granted_company_ids . ") ";
        }

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

        if($box == 1){ // BOX 1
            $count = " COUNT(1) ";
            $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, DATE_FORMAT(MAX(f.policy_start_date), '%b %d, %Y') AS policy_start_date, DATE_FORMAT(MAX(f.policy_end_date), '%b %d, %Y') AS policy_end_date, sc.code AS company, sd.name AS dealer ";

            $query = "SELECT %s 
                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 concat(f.plate_cs_number1, '', f.plate_cs_number2) LIKE '%%%s%%' AND f.status = 1 ".$company.$dealer." GROUP BY f.plate_cs_number1, f.plate_cs_number2 ORDER BY f.policy_end_date DESC";

            // echo sprintf($query, $fields, $search); return;
            $query_list = $db->sql_query(sprintf($query, $fields, $search) . $offset_limit);
            $query_count = $db->select("SELECT COUNT(1) FROM(".sprintf($query, $count, $search).") AS count");

            while($row = $query_list->fetch_assoc()) {
                $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                    'id'=>$row['id'],
                    'plate_cs_number'=>$row['plate_cs_number2'] != "" ? $row['plate_cs_number1'] . " / " . $row['plate_cs_number2'] : $row['plate_cs_number1'], 
                    'plate_cs_number1'=>$row['plate_cs_number1'],
                    'policy_number'=>$row['policy_number'],
                    'policy_start_end_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'], 
                    'company_dealer'=>$row['company'] . " / " . $row['dealer']
                )));
            }

            $json_arr['total'] = $query_count;

            echo json_encode($json_arr);
        } 
        else if($box == 2){ // BOX 2
            $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year");
            $previous_year = $current_year - 1;
            $current_date = $db->select("SELECT CURDATE()");

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

            if(isset($_GET['dealer']) && $_GET['dealer'] != 0){
                $dealer_b2 = $db->escape($_GET['dealer']);
                $dealer_b2 = " AND f1.dealer_id = $dealer_b2 ";
            } 
            
            $fields = " MAX(YEAR(f1.policy_end_date)) as max_year, f1.id, DATE_FORMAT(MAX(f1.policy_start_date), '%b %d, %Y') AS policy_start_date, DATE_FORMAT(MAX(f1.policy_end_date), '%b %d, %Y') AS policy_end_date, f1.policy_number, f1.plate_cs_number1, f1.plate_cs_number2, sc.code AS company, sd.name AS dealer ";
            $count = " COUNT(1) ";

            $inactive_dormant = "SELECT %s 
                FROM finance f1 
                INNER JOIN source_company sc 
                ON sc.id = f1.company_id 
                INNER JOIN source_dealer sd 
                ON sd.id = f1.dealer_id 
                WHERE 1 
                    AND f1.status = 1 ".$company_b2.$dealer_b2."
                    AND (concat(f1.policy_end_date, f1.policy_number, f1.plate_cs_number1) LIKE '%%%s%%' OR f1.plate_cs_number2 LIKE '%%$search%%')
                GROUP BY f1.plate_cs_number1, f1.plate_cs_number2 
                HAVING max_year = '$previous_year'";
            // echo sprintf($inactive_dormant, $fields, $search); return;

            // echo sprintf($inactive_dormant, $fields, $search); return;
            $inactive_dormant_list = $db->sql_query(sprintf($inactive_dormant, $fields, $search) . $offset_limit);
            $inactive_dormant_count = $db->select("SELECT COUNT(1) FROM(".sprintf($inactive_dormant, $fields, $search).") AS count");
            // $inactive_dormant_count = $db->select(sprintf($inactive_dormant, $search, $search));

            while($data = $inactive_dormant_list->fetch_assoc()) {
                $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                    'id'=>$data['id'],
                    'plate_cs_number'=>$data['plate_cs_number2'] != "" ? $data['plate_cs_number1'] . " / " . $data['plate_cs_number2'] : $data['plate_cs_number1'], 
                    'plate_cs_number1'=>$data['plate_cs_number1'],
                    'policy_number'=>$data['policy_number'], 
                    'policy_start_end_date'=>$data['policy_start_date'] . " - " . $data['policy_end_date'], 
                    'company_dealer'=>$data['company'] . " / " . $data['dealer']
                )));
            }

            $json_arr['total'] = $inactive_dormant_count;

            echo json_encode($json_arr);
        } 
        else if($box == 3){ // BOX 3
            $current_year = $db->select("SELECT YEAR(CURDATE()) AS current_year");
            $fdate = $current_year . "-01-01";
            $ldate = $current_year . "-12-31";
            $date_between = " AND YEAR(f.policy_start_date) = '$current_year'";

            $fields = " MAX(YEAR(f.policy_start_date)) as max_year, f.id, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, sc.code AS company, sd.name AS dealer ";
            $count = " COUNT(1) ";

            $newly_added_query = "SELECT %s 
                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."
                    AND concat(f.policy_end_date, f.plate_cs_number1, f.plate_cs_number2, f.policy_number) LIKE '%%%s%%' 
                    GROUP BY f.plate_cs_number1 
                    HAVING COUNT(f.plate_cs_number1) = 1 AND max_year = '$current_year' ORDER BY f.policy_start_date DESC";
            
            // echo sprintf($newly_added_query, $fields, $search); return;
            $newly_added_list = $db->sql_query(sprintf($newly_added_query, $fields, $search) . $offset_limit);
            $newly_added_count = $db->select("SELECT COUNT(1) FROM(".sprintf($newly_added_query, $fields, $search).") AS count");

            while($data = $newly_added_list->fetch_assoc()) {
                $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                    'id'=>$data['id'],
                    'plate_cs_number'=>$data['plate_cs_number2'] != "" ? $data['plate_cs_number1'] . " / " . $data['plate_cs_number2'] : $data['plate_cs_number1'], 
                    'plate_cs_number1'=>$data['plate_cs_number1'],
                    'policy_number'=>$data['policy_number'], 
                    'policy_start_end_date'=>$data['policy_start_date'] . " - " . $data['policy_end_date'], 
                    'company_dealer'=>$data['company'] . " / " . $data['dealer']
                )));
            }

            $json_arr['total'] = $newly_added_count;

            echo json_encode($json_arr);
        } 
        else if($box == 4){ // BOX 4
            $company = ""; $dealer = "";
            if(isset($_GET['company']) && $_GET['company'] != 0){
                $company = $db->escape($_GET['company']);
                $company = " AND t1.company_id = $company ";
            } 
            else {
                $company = " AND t1.company_id IN(" . $granted_company_ids . ") ";
            }
    
            if(isset($_GET['dealer']) && $_GET['dealer'] != 0){
                $dealer = $db->escape($_GET['dealer']);
                $dealer = " AND t1.dealer_id = $dealer ";
            } 

            $fields = " t1.id, t1.customer_fullname, t1.plate_number, t1.conduction_sticker, t1.policy_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(t1.policy_start_date, '%b %d, %Y') AS policy_start_date, DATE_FORMAT(t1.policy_end_date, '%b %d, %Y') AS policy_end_date, sb.name AS brand, scm.name AS model ";
            $count = " COUNT(1) ";

            $plate_query1 = "SELECT f.id, f.customer_fullname, f.policy_number, v.plate_number, '' AS conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status 
                FROM finance f 
                INNER JOIN vehicle v 
                ON v.plate_number = f.plate_cs_number1
                INNER JOIN source_company sc 
                ON f.company_id = sc.id
                INNER JOIN source_dealer sd 
                ON f.dealer_id = sd.id
                INNER JOIN source_brand sb 
                ON sb.id = v.brand_id
                INNER JOIN source_car_model scm 
                ON scm.id = v.model_id
                INNER JOIN source_car_model_variant scmv 
                ON scmv.id = v.model_variant_description";

            $plate_query2 = "SELECT f.id, f.customer_fullname, f.policy_number, v.plate_number, '' AS conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status 
                FROM finance f 
                INNER JOIN vehicle v 
                ON v.plate_number = f.plate_cs_number2
                INNER JOIN source_company sc 
                ON f.company_id = sc.id
                INNER JOIN source_dealer sd 
                ON f.dealer_id = sd.id
                INNER JOIN source_brand sb 
                ON sb.id = v.brand_id
                INNER JOIN source_car_model scm 
                ON scm.id = v.model_id
                INNER JOIN source_car_model_variant scmv 
                ON scmv.id = v.model_variant_description";

            $cs_query1 = "SELECT f.id, f.customer_fullname, f.policy_number, '' AS plate_number, v.conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status
                FROM finance f 
                INNER JOIN vehicle v 
                ON v.conduction_sticker = f.plate_cs_number1
                INNER JOIN source_company sc 
                ON f.company_id = sc.id
                INNER JOIN source_dealer sd 
                ON f.dealer_id = sd.id
                INNER JOIN source_brand sb 
                ON sb.id = v.brand_id
                INNER JOIN source_car_model scm 
                ON scm.id = v.model_id
                INNER JOIN source_car_model_variant scmv 
                ON scmv.id = v.model_variant_description";

            $cs_query2 = "SELECT f.id, f.customer_fullname, f.policy_number, '' AS plate_number, v.conduction_sticker, f.company_id, f.dealer_id, v.brand_id AS brand_id, v.model_id AS model_id, v.model_variant_description AS variant_id, f.policy_start_date, f.policy_end_date, f.status
                FROM finance f 
                INNER JOIN vehicle v 
                ON v.conduction_sticker = f.plate_cs_number2
                INNER JOIN source_company sc 
                ON f.company_id = sc.id
                INNER JOIN source_dealer sd 
                ON f.dealer_id = sd.id
                INNER JOIN source_brand sb 
                ON sb.id = v.brand_id
                INNER JOIN source_car_model scm 
                ON scm.id = v.model_id
                INNER JOIN source_car_model_variant scmv 
                ON scmv.id = v.model_variant_description";

            $with_vehicle_query = "SELECT %s 
                FROM ($plate_query1 UNION $cs_query1 UNION $plate_query2 UNION $cs_query2) t1 
                INNER JOIN source_company sc 
                ON sc.id = t1.company_id 
                INNER JOIN source_dealer sd 
                ON sd.id = t1.dealer_id 
                INNER JOIN source_brand sb 
                ON sb.id = t1.brand_id 
                INNER JOIN source_car_model scm 
                ON scm.id = t1.model_id 
                WHERE 1 AND t1.status = 1 AND concat(customer_fullname, policy_number, plate_number, conduction_sticker) LIKE '%%%s%%'".$company.$dealer." GROUP BY t1.plate_number, t1.conduction_sticker ORDER BY policy_end_date DESC";
            // echo sprintf($query, $fields, $search); return;

            $with_vehicle_list = $db->sql_query(sprintf($with_vehicle_query, $fields, $search) . $offset_limit);
            $with_vehicle_count = $db->select("SELECT COUNT(1) FROM(".sprintf($with_vehicle_query, $count, $search).") AS count");

            while($data = $with_vehicle_list->fetch_assoc()) {
                $plate_cs_number = "";
                $plate_number = $data['plate_number'];
                $conduction_sticker = $data['conduction_sticker'];

                if($plate_number != ""){
                    if($conduction_sticker != ""){
                        $plate_cs_number = $plate_number . " / " . $conduction_sticker;
                    } 
                    else {
                        $plate_cs_number = $plate_number;
                    }
                } 
                else {
                    $plate_cs_number = $conduction_sticker;
                }

                $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                    'id'=>$data['id'],
                    'plate_cs_number'=>$plate_cs_number, 
                    'plate_cs_number1'=>$plate_cs_number, 
                    'policy_number'=>$data['policy_number'], 
                    'policy_start_end_date'=>$data['policy_start_date'] . " - " . $data['policy_end_date'], 
                    'company_dealer'=>$data['company'] . " / " . $data['dealer']
                )));
            }

            $json_arr['total'] = $with_vehicle_count;

            echo json_encode($json_arr);
        }
    } 
    else if($type == 6){ // --------------------------------------- fni activity log : resources/views/common/scripts/finance/finance_information.js [function: queryParamsFinanceActivityLogTable]-->>
        $plate_cs_number1 = $db->escape($_GET['plate_cs_number1']);

        $query = "SELECT DATE_FORMAT(ac.datetime, '%b %d, %Y / %h:%i:%s %p') AS datetime, ac.description, concat(e.first_name, ' ', e.last_name, ' (', e.employee_id, ')') AS done_by 
                    FROM all_activity_log_fni_n_pdc ac 
                    INNER JOIN employee e ON e.id = ac.done_by 
                    WHERE plate_cs_number1 = '$plate_cs_number1' 
                        AND type = 1 
                    ORDER BY ac.datetime DESC";

        $log_list = $db->sql_query($query);

        while($data = $log_list->fetch_assoc()) {
            $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                'datetime'=>$data['datetime'],
                'description'=>$data['description'],
                'done_by'=>$data['done_by']
            )));
        } 

        $json_arr['total'] = $db->select("SELECT COUNT(1) FROM (".$query.") AS count");

        echo json_encode($json_arr);
    } 
    else if($type == 7){ // customer information insurance
        $arr_found = $db->escape($_GET['plate_cs_number']); // echo $arr_found; exit; 
        $json = "";

        if(!$arr_found == 0){ 
            $json = implode("\', \'", $arr_found); 

            $count = " COUNT(*) ";
            $fields = ""; 
            $fields = " f.id, f.plate_cs_number1, f.plate_cs_number2, f.policy_number, DATE_FORMAT(f.policy_issue_date, '%b %d, %Y') AS policy_issue_date, DATE_FORMAT(f.policy_start_date, '%b %d, %Y') AS policy_start_date, 
                            DATE_FORMAT(f.policy_end_date, '%b %d, %Y') AS policy_end_date, fip.name AS insurance_company, fibt.name AS insurance_type ";

            $finance_table_query = "SELECT %s 
                FROM finance f 
                INNER JOIN finance_ins_business_type fibt 
                ON fibt.id = f.insurance_business_type 
                INNER JOIN finance_ins_provider fip 
                ON fip.id = f.insurance_provider 
                WHERE (concat(f.plate_cs_number1, f.plate_cs_number2, f.policy_number, f.customer_fullname) LIKE '%%%s%%') AND f.status = 1 
                    AND `plate_cs_number1` IN('".$json."') OR `plate_cs_number2` IN('".$json."') ORDER BY f.date_uploaded DESC"; 
            
            // echo sprintf($finance_table_query, $fields, $search); return;
            $finance_list_query = $db->sql_query(sprintf($finance_table_query, $fields, $search) . $offset_limit);
            // $finance_list_count = $db->select("SELECT COUNT(*) FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT");
            $finance_list_count = 0;
            $finance_list_count = $db->select("SELECT COUNT(*) FROM (" . sprintf($finance_table_query, $count, $search) . ") AS COUNT"); 

            while($row = $finance_list_query->fetch_assoc()) {
                $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                    'id'=>$row['id'], 
                    'plate_cs_number1'=>$row['plate_cs_number1'], 
                    'plate_cs_number'=>$row['plate_cs_number2'] != "" ? $row['plate_cs_number1'] . " / " . $row['plate_cs_number2'] : $row['plate_cs_number1'], 
                    'policy_number'=>$row['policy_number'], 
                    'policy_start_end_date'=>$row['policy_start_date'] . " - " . $row['policy_end_date'], 
                    'policy_issue_date'=>$row['policy_issue_date'], 
                    'insurance_type'=>$row['insurance_type'], 
                    'insurance_provider'=>$row['insurance_company']
                )));   
            }

            $json_arr['total'] = $finance_list_count;
        } 

        echo json_encode($json_arr); 
    }
?>