<?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: auto_txt_blast.php');
        return;
    }

    $utility = new Utility();

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

    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);
        } 
        else {
            $date_title = substr($utility->remove_non_numeric($start_date), 2, 6) . "-" . substr($utility->remove_non_numeric($end_date), 2, 6);
        }
    }

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

    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['status']) && $_REQUEST['status'] != ""){
        $get_status = $db -> escape(trim($_REQUEST['status']));
        if($get_status > 0){
            $filter_status = " AND rss.status > 0 ";
            $status_title = "_UNSENT";
        } 
        else {
            $filter_status = " AND rss.status = $get_status ";
            if($get_status == 0){$status_title = "_SENT";}
            if($get_status == -3){$filter_status = "";}
        }
    }

    $date = ""; $count = 0;
    $pdf_file_name = ""; $pdf_id = "";
    // if(strlen($get_status) == 1){
    //     $pdf_id = removeHyphen($filter_date) . "" . substr($get_status, 0, 1);
    // }
    // else if(strlen($get_status) == 2){
    //     $pdf_id = removeHyphen($filter_date) . "" . substr($get_status, 0, 2);
    // }
    
    // $cipher_date_status = strtr($db->cipher($filter_date . "" . $filter_status, true), "+/=", "-_~");
    
    $count = " count(rs.request_id) ";
    $fields = " rs.request_id, tb.control_number, concat(e.first_name, ' ', e.last_name) AS name, DATE_FORMAT(rs.date, '%M %d, %Y') AS date, 
    IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 2) > 0, 
        2
    ,
        IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 0) > 0,
            0
        ,
            IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 1) > 0, 
                1
            ,
                3
            )
        )
    ) AS status, sc.code AS company, sd.name AS dealer";

    $company_query = "SELECT %s 
        FROM `request_status` rs 
        INNER JOIN `text_blast` tb 
        ON rs.request_id = tb.id 
        INNER JOIN `request_status_summary` rss 
        ON rs.request_id = rss.request_id 
        INNER JOIN employee e 
        ON tb.employee_id = e.id 
        INNER JOIN source_company_dealer scd 
        ON e.company_dealer_id = scd.id 
        INNER JOIN source_company sc 
        ON sc.id = scd.company_id 
        INNER JOIN source_dealer sd 
        ON sd.id = scd.dealer_id 
        WHERE 1 " . $company_q . $dealer_q . $filter_status . " GROUP BY rs.request_id ORDER BY rs.date DESC 
        ";
    
    // echo sprintf($company_query, $fields); return;
    $company_list_query = $db->sql_query(sprintf($company_query, $fields)); 
    $count = $db->select("SELECT COUNT(1) FROM (".sprintf($company_query, $count).") AS count");

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

    $filename = 'sms/report_generation/text_blast_service/data/'.strtr($db->cipher($filter_date . "" . $filter_status, true),  "+/", "-_" ).'.'.'php';
    $fp = fopen($filename, 'w');
    $col_width = array(32, 31, 23, 38, 43, 33);   
    $col_header = array('Control No.', 'Requested By','Emp. Company','Emp. Dealer', 'Date Requested', 'Status'); 
    $string = implode(';',array_map('replaceExistingSemiColon', $col_header)); 
    fwrite($fp, $string.PHP_EOL);  

    $data_to_insert = array();
    $ctr = 1;

    foreach($company_list_query AS $row){
        $data_to_insert = array(
            $row['control_number'], 
            $row['name'],
            $row['company'],
            $row['dealer'],
            $row['date'], 
            $row['status'] == 0 ? "Pending" : 
                ($row['status'] == 1 ? "Approved" : 
                ($row['status'] == 3 ? "Closed" : 
                "Declined"))
        );

        $ctr++;

        $string = implode(';', array_map('replaceExistingSemiColon', $data_to_insert));
        fwrite($fp, $string.PHP_EOL); 
    }
    // $cipher_date_status = strtr($cipher_date_status, "+/=", "-_~");

    // $insert = "INSERT INTO `txtblast_pdf_birthday` (`pdf_id`, `field_date`, `field_status`, `pdf_file_name`) 
        // VALUES('$pdf_id', '$filter_date', '$get_status', '$cipher_date_status')";
    
    // echo $insert; return;
    // $db->sql_query($insert);

    fclose($fp);
    
    $stat = ""; $company_header = ""; $dealer_header = ""; $company_filename = ""; $dealer_filename = ""; $mask_header = ""; $mask_filename = "";
    
    if($get_status == ""){$stat = "All status";}else if($get_status == 0){$stat = "All Sent SMS only";}else if($get_status == 1){$stat = "All Unsent SMS only";}
    $company == "" ? $company_header = "ALL COMPANIES" : $company_header = $db->select("SELECT code FROM source_company WHERE id = '$company'");
    $dealer == "" ? $dealer_header = "ALL DEALERS" : $dealer_header = $db->select("SELECT code FROM source_dealer WHERE id = '$dealer'");

    // GENERATE PDF
    include('include/fpdf183/mc_table_tb_request.php'); 
    $col_width = array(32, 31, 23, 38, 43, 33);

    $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;

    $pdf = new PDF_MC_Table('P','mm','Legal'); 
    $title = 'AutoHub-' . $date;
    $pdf->SetTitle($title);
    $pdf->AddPage();
    $pdf->SetFont('Arial', '', 10);
    $pdf_request_data[] = array('Company',  ': ', $company_header);
    $pdf_request_data[] = array('Dealer',  ': ', $dealer_header);
    $pdf_request_data[] = array('Status',  ': ', $stat);
    $pdf_request_data[] = array('Total Data Count', ': ', number_format($total_count));
    $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(1);
    // $pdf->SetWidths(array(36,4,160));
    // $pdf->SetFont('','');
    // $pdf->Row(array('Message Content',  ': ', $request_session_data['message_content']), false);  
    $pdf->Ln(2);
    // Arial 12
    $pdf->SetFont('Arial', '', 11);
    // Background color
    $pdf->SetFillColor(255,255,255);
    // Title
    // $pdf->Cell(0,6,"Approve by: ", 0, 1, 'L', true);

    // $pdf->SetWidths(array(10, 35, 55,100));
    // $pdf->SetFont('Arial','',10);
    // foreach($request_session_data['approver_list'] as $row){
    //     $pdf->Row($row,false);  
    // }

    $pdf->Ln(5);

    $pdf->SetFont('Arial', '', 8); 
    $pdf_request_data = $pdf->LoadData('sms/report_generation/text_blast_service/data/'.strtr($db -> cipher($filter_date . "" . $filter_status, true),  "+/", "-_" ).'.'.'php');

    $pdf->FancyTable($pdf_request_data, $col_width);
    $filename = "REQUEST_REPORT_TEXTBLAST" . $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);
    }
?>