<?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']['14']) && 
    in_array("2", $global_action_permissions['5']['38']['14']))){
        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;

    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 = $db->select("SELECT DATE_FORMAT('".$start_date."', '%b %d, %Y')");
        } 
        else {
            $date_title =  $db->select("SELECT DATE_FORMAT('".$start_date."', '%b %d, %Y')") . " to " .  $db->select("SELECT DATE_FORMAT('".$end_date."', '%b %d, %Y')");
        }
    }

    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 tbbr.sms_response > 0 ";
            $status_title = "_UNSENT";
        } 
        else {
            $filter_status = " AND tbbr.sms_response = $get_status ";
            if($get_status == 0){$status_title = "_SENT";}
            if($get_status == -3){$filter_status = "";}
        }
    }

    $mask = $db->escape($_REQUEST['mask']);
    //echo "Mask: " . $mask; return;

    $count = 0;
    $count_query = " COUNT(*) ";
    $fields = " c.id, concat(c.first_name, ' ', c.last_name) AS name, cc.mobile_phone_1, sc.code AS company, sd.name AS dealer, DATE_FORMAT(tbbr.date, '%M %d, %Y') AS date, tbbr.sms_response ";

    $query_print = "SELECT %s 
        FROM `text_blast_birthday_record` tbbr 
        INNER JOIN customer c ON c.id = tbbr.customer_id 
        INNER JOIN customer_contact cc ON cc.customer_id = tbbr.customer_id 
        INNER JOIN source_company sc ON sc.id = tbbr.company_id 
        INNER JOIN source_dealer sd ON sd.id = tbbr.dealer_id 
        WHERE c.type = 1 AND tbbr.date BETWEEN '$start_date' AND '$end_date' " . $filter_status . $company_q . $dealer_q . " GROUP BY c.id ";

    // echo sprintf($query_print, $fields); return;
    $fetch_print = $db->sql_query(sprintf($query_print, $fields));
    $customer_id_array = array();
    foreach($fetch_print AS $data){
        $date = $data['date'];
    }

    $data_to_insert = array(); $company_arr = array(); $filename_arr = array(); $ctr = 1; $per_company_ctr = 1;
    foreach($fetch_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);
        }
    }

    $count = $db->select("SELECT COUNT(*) FROM text_blast_birthday_record WHERE date = '$filter_date'");
    $pdf_file_name = strtr($db->cipher($filter_date . "" . $filter_status, true), "+/=", "-_~" ).'.'.'pdf';

    // $filename = 'sms/report_generation/bg_service/data/'.strtr($db->cipher($filter_date . "" . $filter_status, true),  "+/", "-_" ).'.'.'php';
    // $fp = fopen($filename, 'w');
    $col_width = array(9, 53, 41, 55, 42);
    $col_header = array('No.', 'Name','Mobile', '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/bg_service/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 text_blast_birthday_record tbbr 
                INNER JOIN customer c ON c.id = tbbr.customer_id 
                INNER JOIN customer_contact cc ON cc.customer_id = tbbr.customer_id 
                INNER JOIN source_company sc ON sc.id = tbbr.company_id 
                INNER JOIN source_dealer sd ON sd.id = tbbr.dealer_id 
                WHERE tbbr.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_query).") 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 {
                    $status_str = "Send error. SMS response: " . $status;
                }

                $data_to_insert = array(
                    $ctr, 
                    $row['name'],
                    $row['mobile_phone_1'],
                    $row['dealer'],
                    $status_str
                );

                $ctr++;

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

                // if(in_array($customer_id, $customer_id_array)){
                //     $data_to_insert = array(
                //         $ctr, 
                //         $row['name'],
                //         $row['mobile_phone_1'],
                //         $row['dealer'],
                //         $status_str
                //     );
    
                //     $ctr++;
                //     $per_company_ctr++;

                //     if(($key = array_search($customer_id, $customer_id_array)) !== false){
                //         unset($customer_id_array[$key]);
                //     }

                    
                // }
            }
        } 
        else {
            $company_q = " AND sc.id = '$company_id'";
            $query_print = "SELECT %s 
                FROM text_blast_birthday_record tbbr 
                INNER JOIN customer c ON c.id = tbbr.customer_id 
                INNER JOIN customer_contact cc ON cc.customer_id = tbbr.customer_id 
                INNER JOIN source_company sc ON sc.id = tbbr.company_id 
                INNER JOIN source_dealer sd ON sd.id = tbbr.dealer_id 
                WHERE tbbr.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_query).") AS count");
            array_push($company_count, $comp_count);

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

                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 {
                    $status_str = "Send error. SMS response: " . $status;
                }

                $data_to_insert = array(
                    $ctr, 
                    $row['name'],
                    $row['mobile_phone_1'],
                    $row['dealer'],
                    $status_str
                );

                $ctr++;

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

                // if(in_array($customer_id, $customer_id_array)){
                //     $data_to_insert = array(
                //         $ctr, 
                //         $row['name'],
                //         $row['mobile_phone_1'],
                //         $row['dealer'],
                //         $status_str
                //     );
    
                //     $ctr++;
                //     $per_company_ctr++;

                //     if(($key = array_search($customer_id, $customer_id_array)) !== false){
                //         unset($customer_id_array[$key]);
                //     }

                //     $string = implode(';', array_map('replaceExistingSemiColon', $data_to_insert));
                //     fwrite($fp, $string.PHP_EOL); 
                // }
            }
            // array_push($company_count, $per_company_ctr-1);
            // $per_company_ctr = 1;
        }
    }

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

    if($get_status == -3){$stat = "All status (Sent & Unsent)";}else if($get_status == -2 || $get_status == 0){$stat = "Sent";}else if($get_status > 0){$stat = "Unsent";}
    $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_bg.php'); 

    $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 of Birth',  ': ', $date_title);
    $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/bg_service/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/bg_service/data/'.$file_name.'.'.'php');
    }

    $filename = "BG_SMS_REPORT.pdf";
    $pdf->Output($filename, 'D');

    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);
    }
?>