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

    $utility = new Utility();

    $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){ // refresh table
        $json_arr['rows'] = array();
        $company = ""; $dealer = ""; $date_q = ""; $filter_box_q = "";
        $filter_box = "";

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

        $dealer = $db->escape($_GET['dealer']);
        if($dealer != 0 && $dealer != ""){
            $dealer = " AND fp.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(1) ";
        $fields = " fp.id, fp.plate_cs_number1, fp.plate_cs_number2, fsb.name AS brand, DATE_FORMAT(fp.check_date, '%b %d, %Y') AS check_date, fp.pdc_check_number, bd.abbreviation AS bank, fp.branch, sc.code AS company, sd.name AS dealer, fp.policy_number ";

        $query = "SELECT %s 
            FROM finance_pdc fp 
            INNER JOIN source_company sc 
                ON sc.id = fp.company_id 
            INNER JOIN source_dealer sd 
                ON sd.id = fp.dealer_id 
            INNER JOIN bank_db bd 
                ON bd.id = fp.bank_id 
            INNER JOIN finance_source_brand fsb 
                ON fsb.id = fp.brand_id 
            WHERE concat(fp.plate_cs_number1, fp.plate_cs_number2, fp.pdc_check_number, fp.policy_number) LIKE '%%%s%%'" . 
                $company . 
                $dealer . "
                AND fp.status = 1 
            GROUP BY fp.plate_cs_number1, fp.plate_cs_number2 
            ORDER BY fp.date_added DESC";

        // echo sprintf($query, $fields, $search); return;

        $pdc_query_list = $db->sql_query(sprintf($query, $fields, $search) . $offset_limit);
        $pdc_query_count = $db->select("SELECT COUNT(1) FROM (".sprintf($query, $fields, $search).") AS count");

        while($row = $pdc_query_list->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'], 
                'brand'=>$row['brand'], 
                'check_date'=>$row['check_date'], 
                'check_number'=>$row['pdc_check_number'], 
                'bank_branch'=>$row['bank'] . " / " . $row['branch'], 
                'company_dealer'=>$row['company'] . " / " . $row['dealer']
            )));
        }

        $json_arr['total'] = $pdc_query_count;

        echo json_encode($json_arr);
    } 
    else if($type == 2){ // pdc information
        $json_arr['rows'] = array();
        $pdc_plate_cs_number1 = $db->escape($_GET['pdc_plate_cs_number']);

        $count = " COUNT(1) ";
        $fields = " fp.id AS record_id, fp.plate_cs_number1, fp.plate_cs_number2, fsb.name AS brand, sc.code AS company, sd.name AS dealer, fp.pdc_check_number, DATE_FORMAT(fp.check_date, '%b %d, %Y') AS check_date, 
                    fp.policy_number, fip.name AS insurance_company ";

        $query = "SELECT %s 
                    FROM finance_pdc fp 
                    INNER JOIN source_company sc 
                        ON sc.id = fp.company_id 
                    INNER JOIN source_dealer sd 
                        ON sd.id = fp.dealer_id 
                    INNER JOIN finance_source_brand fsb 
                        ON fsb.id = fp.brand_id 
                    INNER JOIN finance_ins_provider fip 
                        ON fip.id = fp.insurance_company_id 
                    WHERE fp.plate_cs_number1 = '$pdc_plate_cs_number1' AND fp.status = 1
                    "; // echo sprintf($query, $fields); return;
        
        $pdc_information_list = $db->sql_query(sprintf($query, $fields) . $offset_limit);
        $pdc_information_count = $db->select(sprintf($query, $count));

        $ctr = 1;

        while($row = $pdc_information_list->fetch_assoc()){
            $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                'no'=>$ctr,
                'record_id'=>$row['record_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'], 
                'brand'=>$row['brand'], 
                'check_date'=>$row['check_date'], 
                'check_number'=>$row['pdc_check_number'], 
                'insurance_company'=>$row['insurance_company'], 
                'policy_number'=>$row['policy_number'] != "" ? $row['policy_number'] : "<span style='color: red;'>(Not provided)</span>", 
                'company_dealer'=>$row['company'] . " / " . $row['dealer']
            )));
            $ctr++;
        }

        $json_arr['total'] = $pdc_information_count;

        echo json_encode($json_arr);
    } 
    else if($type == 3){ // ---------------- pdc remaining days table (resources/views/common/scripts/finance_pdc/pdc_information.js) [queryParamsPDCInformationTable function]
        $pdc_id = $db->escape($_GET['record_id']);
        // $plate_cs_number = $db->escape($_GET['plate_cs_number']);
        // $policy_issue_date = $db->escape($_GET['policy_issue_date']);
        // echo $pdc_id; exit;

        $rem_day = array(); $sms_status = "";
        $json_arr['rows'] = array();
        
        $remaining_days = $db->select("SELECT remaining_days FROM auto_txt_blast WHERE id = 4");
        $rem_day = explode(",", $remaining_days);

        $check_date = $db->select("SELECT check_date FROM finance_pdc WHERE id = '$pdc_id'");
        $current_date = $db->select("SELECT CURDATE()");

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

            $count = " COUNT(*) ";
            $fields = " status ";
            
            $sms_query = "SELECT %s 
                FROM txtblast_pdc_reminder_record 
                WHERE pdc_id = '$pdc_id' AND check_date = '$check_date' AND reminder_type = '$remaining'"; 
            
            $get_record_count = $db->select(sprintf($sms_query, $count));

            if($get_record_count > 0){
                $sms_status = $db->select(sprintf($sms_query, $fields));
            } 
            else {
                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){ // --------------------------------------- pdc activity log : resources/views/common/scripts/finance_pdc/pdc_information.js [function: queryParamsPDCActivityLogTable]-->>
        $plate_cs_number1 = $db->escape($_GET['plate_cs_number1']);
        $json_arr['rows'] = array();

        $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 = 2 
                    ORDER BY ac.datetime DESC";

        // echo $query; return;
        $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);
    }
?>