<?php
    ini_set('max_execution_time', '0'); 
    set_time_limit(0);
    //same code above
    ini_set('memory_limit','-1');
    date_default_timezone_set('Asia/Manila');
    include('cfg/db.php');
    include_once("api/controllers/utility.php");

    if(!isset($_SESSION['user']['id'])){
        header('location: home.php');
        return;
    }

    $utility = new Utility();

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

    if(!(isset($global_action_permissions['5']['38']['15']) && 
    in_array("2", $global_action_permissions['5']['38']['15']))){
        echo "You are not allowed to do this action.";
        exit;
    }

    $generated_by = "";
    $user_role = $db->select("SELECT employee_role_id FROM employee_account WHERE id = '$current_user'");
    if($user_role == 45 || $user_role == 1){
        $generated_by = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'];
    } 
    else {
        $generated_by = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'] . " (" . $_SESSION['user']['company_code'] . ")";
    }

    $filter_date = ""; $filter_status = ""; $date_title = ""; $status_title = "_ALL"; $get_status = -3; $total_count = $_REQUEST['count']; $company = ""; $dealer = ""; $interval = 0;
    $date = ""; 
    $start_date = ""; $end_date = ""; $company_q = ""; $dealer_q = ""; $mask = -1;
    $filter_date_eng = "";

    $uuid = $db->select("SELECT UUID()");

    if(isset($_REQUEST['filter_date']) && $_REQUEST['filter_date'] != ""){ // 2021-10-01@2021-10-08
        $date = $db -> escape(trim($_REQUEST['filter_date']));
        $start_date = substr($date, 0, 10);
        $end_date = substr($date, 11, 10);

        $diff = date_diff(date_create($start_date), date_create($end_date)); // compute date interval
        $interval = (int)$diff->format("%a");

        if($interval == 0){
            $date_title = substr($utility->remove_non_numeric($start_date), 2, 6);
            $filter_date_eng = $db->select("SELECT DATE_FORMAT('$start_date', '%b %d, %Y') AS start_date");
            // $date_title = $db->select("SELECT DATE_FORMAT('".$start_date."', '%b %d, %Y')");
        } 
        else {
            $date_title = substr($utility->remove_non_numeric($start_date), 2, 6) . "-" . substr($utility->remove_non_numeric($end_date), 2, 6);
            $filter_date_eng = $db->select("SELECT DATE_FORMAT('$start_date', '%b %d, %Y') AS start_date") . " to " . $db->select("SELECT DATE_FORMAT('$end_date', '%b %d, %Y') AS end_date");
        }
    }

    if(isset($_REQUEST['company']) && $_REQUEST['company'] != ""){
        $company = $db->escape($_REQUEST['company']);
        $company_q = " AND sc.id = '$company' ";
    } 
    else {
        $company_q = " AND sc.id IN(" . $granted_company_ids . ")";
    }

    if(isset($_REQUEST['dealer']) && $_REQUEST['dealer'] != ""){
        $dealer = $db->escape($_REQUEST['dealer']);
        $dealer_q = " AND sd.id = '$dealer' ";

        if($dealer == 0){$dealer_q = "";}
    }

    if(isset($_REQUEST['filter_status']) && $_REQUEST['filter_status'] != ""){
        $get_status = $db -> escape(trim($_REQUEST['filter_status']));
        if($get_status > 0){
            $filter_status = " AND tbirr.sms_response > 0 ";
            $status_title = "_UNSENT";
        } 
        else {
            $filter_status = " AND tbirr.sms_response = $get_status ";
            if($get_status == 0){$status_title = "_SENT";}
            if($get_status == -10){$filter_status = "";}
        }
    }

    $mask = $db->escape($_REQUEST['mask']);

    $fields = " f.customer_fullname, f.customer_mobile_number, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbirr.action_date, '%M %d, %Y') AS action_date, tbirr.sms_response ";
    $count_query = " COUNT(*) ";

    $query_print = "SELECT %s 
        FROM txtblast_insurance_reminder_record tbirr
        INNER JOIN `finance` f ON f.id = tbirr.finance_id 
        INNER JOIN source_company sc ON sc.id = f.company_id 
        INNER JOIN source_dealer sd ON sd.id = f.dealer_id 
        WHERE tbirr.action_date BETWEEN '$start_date' AND '$end_date' " . $filter_status . $company_q . $dealer_q;

    // echo sprintf($query_print, $fields); return;
    $fetch_query_print = $db->sql_query(sprintf($query_print, $fields));

    $data_to_insert = array(); $company_arr = array(); $filename_arr = array(); $ctr = 1;
    foreach($fetch_query_print AS $row){
        $company_item = $row['company'];

        if(!(in_array($company_item, $company_arr))){
            $uuid = $db->select("SELECT UUID()");

            array_push($company_arr, $company_item);
            array_push($filename_arr, $uuid);
        }
    }

    $pdf_file_name = strtr($db->cipher($uuid, true), "+/=", "-_~" ).'.'.'pdf';

    // $filename = 'sms/report_generation/insurance_expiration/data/'.strtr($db->cipher($uuid, true),  "+/", "-_" ).'.'.'php';
    // $fp = fopen($filename, 'w');
    $col_width = array(12, 62, 30, 54, 41);   
    $col_header = array('No.', 'Customer Name','Mobile','Company / Dealer', 'Status'); 
    // $string = implode(';',array_map('replaceExistingSemiColon', $col_header)); 
    // fwrite($fp, $string.PHP_EOL);  

    $company_arr_length = count($company_arr);
    $company_code_list_arr = array(); $company_name_list_arr = array(); $company_count = array();

    for($i=0; $i<$company_arr_length; $i++){
        $company_code = $company_arr[$i];
        $file_name = $filename_arr[$i];
        $company_id = $db->select("SELECT id FROM source_company WHERE code = '$company_code'");
        $company_name = $db->select("SELECT name FROM source_company WHERE code = '$company_code'");

        array_push($company_code_list_arr, $company_code);
        array_push($company_name_list_arr, $company_name);

        $filename = 'sms/report_generation/insurance_expiration/data/'.$file_name.'.'.'php';
        $fp = fopen($filename, 'w');
        $string = implode(';',array_map('replaceExistingSemiColon', $col_header)); 
        fwrite($fp, $string.PHP_EOL);

        if($mask == 0){
            $company_q = " AND sc.id = '$company_id'";
            $query_print = "SELECT %s 
                FROM txtblast_insurance_reminder_record tbirr
                INNER JOIN `finance` f ON f.id = tbirr.finance_id 
                INNER JOIN source_company sc ON sc.id = f.company_id 
                INNER JOIN source_dealer sd ON sd.id = f.dealer_id 
                WHERE tbirr.action_date BETWEEN '$start_date' AND '$end_date' " . $filter_status . $company_q . $dealer_q;

            $new = $db->sql_query(sprintf($query_print, $fields));
            $comp_count = $db->select(sprintf($query_print, $count_query));
            // $comp_count = $db->select("SELECT COUNT(*) FROM (".sprintf($query_print, $count).") AS count");
            array_push($company_count, $comp_count);

            foreach($new AS $row){
                $status = $row['sms_response'];

                if($status == -2 || $status == 0){ //success
                    $status_str = "SMS sent";
                }
                else if($status == -1){ //pending
                    $status_str = "Pending";
                }
                else if($status == 1){ //invalid
                    $status_str = "Invalid number";
                }
                else if($status == 2){ //unsupp #
                    $status_str = "Unsupported #";
                } 
                else if($status == -3){
                    $status_str = "Skipped";
                } 
                else {
                    $status_str = "Send error. Itexmo status: " . $status;
                }

                $data_to_insert = array(
                    $ctr, 
                    $row['customer_fullname'],
                    $row['customer_mobile_number'],
                    $row['dealer'], 
                    $status_str
                );

                $ctr++;

                $string = implode(';', array_map('replaceExistingSemiColon', $data_to_insert));
                fwrite($fp, $string.PHP_EOL); 
            }
        } 
        else {
            $company_q = " AND sc.id = '$company_id'";
            $query_print = "SELECT %s
                FROM txtblast_insurance_reminder_record tbirr
                INNER JOIN `finance` f ON f.id = tbirr.finance_id 
                INNER JOIN source_company sc ON sc.id = f.company_id 
                INNER JOIN source_dealer sd ON sd.id = f.dealer_id 
                WHERE tbirr.action_date BETWEEN '$start_date' AND '$end_date' " . $filter_status . $company_q . $dealer_q;
            // echo sprintf($query_print, $fields); return;
            $new = $db->sql_query(sprintf($query_print, $fields));
            $comp_count = $db->select(sprintf($query_print, $count_query));
            // $comp_count = $db->select("SELECT COUNT(*) FROM (".sprintf($query_print, $count).") AS count");
            array_push($company_count, $comp_count);

            foreach($new AS $row){
                $status = $row['sms_response'];

                if($status == -2 || $status == 0){ //success
                    $status_str = "SMS sent";
                }
                else if($status == -1){ //pending
                    $status_str = "Pending";
                }
                else if($status == 1){ //invalid
                    $status_str = "Invalid number";
                }
                else if($status == 2){ //unsupp #
                    $status_str = "Unsupported #";
                }
                else if($status == -3){
                    $status_str = "Skipped";
                } 
                else {
                    $status_str = "Send error. Itexmo status: " . $status;
                }

                $data_to_insert = array(
                    $ctr, 
                    $row['customer_fullname'],
                    "+" . mask($row['customer_mobile_number'], 5, 1),
                    $row['dealer'], 
                    $status_str
                );

                $ctr++;

                $string = implode(';', array_map('replaceExistingSemiColon', $data_to_insert));
                fwrite($fp, $string.PHP_EOL); 
            }
        }
    }

    fclose($fp);
    
    $stat = ""; $company_header = ""; $dealer_header = ""; $company_filename = ""; $dealer_filename = ""; $mask_header = ""; $mask_filename = "";

    if($get_status == -10){$stat = "All status (Sent & Unsent)";} else if($get_status == -2 || $get_status == 0){$stat = "Sent";}else if($get_status > 0){$stat = "Unsent";} else if($get_status == -3){$stat = "Skipped";}
    $company == "" ? $company_header = "ALL" : $company_header = $db->select("SELECT code FROM source_company WHERE id = '$company'");
    $dealer == "" ? $dealer_header = "ALL" : $dealer_header = $db->select("SELECT code FROM source_dealer WHERE id = '$dealer'");
    $mask == 0 ? $mask_header = " / Unmasked" : $mask_header = " / Masked";

    // GENERATE PDF
    include('include/fpdf183/mc_table_ins_exp.php'); 
    // $col_width = array(12, 62, 30, 54, 41);

    $interval == 0 ? $date = $start_date : $date = $start_date . " to " . $end_date;
    $company_header == "ALL" ? $company_filename = "" : $company_filename = "_" . $company_header;
    $dealer_header == "ALL" ? $dealer_filename = "" : $dealer_filename = "_" . $dealer_header;
    $mask == 0 ? $mask_filename = "_UNMASKED" : $mask_filename = "_MASKED";

    $pdf = new PDF_MC_Table('P','mm','Legal'); 
    $title = 'AutoHub-' . $date;
    $pdf->SetTitle($title);
    $pdf->AddPage();
    $pdf->SetFont('Arial', '', 10);
    $pdf_request_data[] = array('Date Range',  ': ', $filter_date_eng);
    $pdf_request_data[] = array('Company',  ': ', $company_header);
    $pdf_request_data[] = array('Dealer',  ': ', $dealer_header);
    $pdf_request_data[] = array('Status',  ': ', $stat . $mask_header);
    $pdf_request_data[] = array('Total Data Count', ': ', number_format($total_count));
    $pdf_request_data[] = array('Generated by',  ': ', $generated_by);
    $request_header_widths = array(30, 10, 92, 25, 35);

    $pdf->SetWidths($request_header_widths);
    foreach($pdf_request_data as $row){
        $pdf->Row($row, false);  
    }  
    $pdf->Ln(2);
    // Arial 12
    $pdf->SetFont('Arial', '', 11);
    // Background color
    $pdf->SetFillColor(255,255,255);

    $pdf->Ln(5);

    $pdf->SetFont('Arial', '', 8); 

    for($i=0; $i<count($filename_arr); $i++){
        $file_name = $filename_arr[$i];
        $record_data = $pdf->LoadData('sms/report_generation/insurance_expiration/data/'.$file_name.'.'.'php');
        $pdf->Ln(3);
        $pdf->Write(7, $company_code_list_arr[$i] . ": " . $company_count[$i]);
        $pdf->Ln(7);
        $pdf->FancyTable($record_data, $col_width);
        $record_data = [];
        unlink('sms/report_generation/insurance_expiration/data/'.$file_name.'.'.'php');
    }

    // $pdf_request_data = $pdf->LoadData('sms/report_generation/insurance_expiration/data/'.strtr($db -> cipher($uuid, true),  "+/", "-_" ).'.'.'php');
    // print_r($pdf_request_data); exit;

    // $pdf->FancyTable($pdf_request_data, $col_width);
    $filename = "INS_EXP_REPORT.pdf";
    // $filename = "INS_EXP_REPORT_" . $date_title . $status_title . $company_filename . $dealer_filename . $mask_filename . ".pdf";
    $pdf->Output($filename, 'D');

    //echo "\n" . $count . " end\n\n";

    if($count > 0){
        $return_array['status'] = 1;
        $return_array['message'] = "Request Generated";  
        $return_array['file'] = str_replace(".pdf", "",  $pdf_file_name);
        $file = str_replace(".pdf", "",  $pdf_file_name);

    } else {
        $return_array['status'] = 0;
        $return_array['message'] = "Request Generation Failed"; 
    }

    echo json_encode($return_array);

    function replaceExistingSemiColon($s){
        return str_replace(";", " ", $s);
    }

    function removeHyphen($s){
        return str_replace("-", "", $s);
    }

    function cleanStr($string) {
        $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.
     
        return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
    }
    
    function mask($str, $first, $last) { 
        $str = cleanStr($str);
        $len = strlen($str);
        $toShow = $first + $last;
        return substr($str, 0, $len <= $toShow ? 0 : $first).str_repeat("*", $len - ($len <= $toShow ? 0 : $toShow)).substr($str, $len - $last, $len <= $toShow ? 0 : $last);
    }
?>