<?php

namespace App\Controllers;

use App\Core\Controller;
use App\Core\Sms;
use App\Utilities\Session;
use App\Utilities\Uuid;
use App\Utilities\Utility;

class LtoController extends Controller
{
    private $lto;

    function __construct()
    {
        $this->lto = new \App\Models\Lto;
    }
    public function ltoIndex()
    {
        response()->redirect(url('lto.orcr.plate'));
    }
    public function OrcrPlateIndex()
    {
        // if (!(isset($global_action_permissions['7']['33']) &&
        //     in_array("1", $global_action_permissions['7']['33']))) {
        //     header('location:signin.php');
        // }


        // print_r(Session::get('action_permissions'));
        if (!in_array("1", Session::get('action_permissions')['10'])) {
            response()->redirect("/index.php");
        }

        $page_title = "OR/CR & Plate Update";
        include('resources/views/lto/orcr_plate_update/list.php');
    }

    public function orcrPlateList()
    {
        $array_data['search_keyword'] = Utility::removeNotAlphaNumeric(input('search'));
        // $array_data['search_keyword_name'] = input('search');
        $array_data['search_type'] = input('search_type');
        $array_data['search_type'] = json_decode($array_data['search_type'], true)[0];
        $array_data['offset'] = input('offset');
        $array_data['limit'] = input('limit');
        $array_data['sort'] = input('sort');
        $array_data['user_id'] = Session::get('user')['id'];

        switch ($array_data['sort']) {
            case "date":
                $array_data['sort'] = " ORDER BY vops.date_added " . input('order');
                break;
            case "date_received":
                $array_data['sort'] = " ORDER BY vops.date_received " . input('order');
                break;
            case "type":
                $array_data['sort'] = " ORDER BY vops.type " . input('order');
                break;
            case "status":
                $array_data['sort'] = " ORDER BY vops.sms_response " . input('order') . " ,vops.sms_date " . input('order') . "";
                break;
            default:
                $array_data['sort'] = " ORDER BY vops.date_added DESC";
        }

        //  start search using keywords
        switch ($array_data['search_type']) {
            case "Customer":
                $array_data['search'] = " AND CONCAT(c.first_name,' ',c.last_name,' ',c.corporation_name)  LIKE ? ";
                $array_data['search_keyword'] = "%" . input('search') . "%";
                break;
            case "Conduction Sticker":
                $array_data['search'] = " AND v.conduction_sticker = ? ";
                break;
            case "Plate Number":
                $array_data['search'] = " AND v.plate_number = ? ";
                break;
            case "MV File":
                $array_data['search'] = " AND v.mv_file = ? ";
                break;
            case "Engine Number":
                $array_data['search'] = " AND v.engine_number = ? ";
                break;
            case "Chassis Number":
                $array_data['search'] = " AND v.chassis_number = ? ";
                break;
        }

        if (empty($array_data['search_keyword'])) {
            $array_data['search_keyword'] = "1";
            $array_data['search'] = " AND  ? ";
        }
        //end search using keywords

        // start date filter
        if (empty(input('start_date_add'))) {
            $array_data['date_added'] = "";
        } else {
            $array_data['date_added'] = " AND DATE_FORMAT(vops.date_added,'%Y-%m-%d') BETWEEN '" . input('start_date_add') . "' AND '" . input('end_date_add') . "' ";
        }

        if (empty(input('start_date_receive'))) {
            $array_data['date_received'] = "";
        } else {
            $array_data['date_received'] = " AND DATE_FORMAT(vops.date_received,'%Y-%m-%d') BETWEEN '" . input('start_date_receive') . "' AND '" . input('end_date_receive') . "' ";
        }

        //sms status filter
        switch (input('sms_status')) {
            case "Sent":
                $array_data['sms_status'] = " AND (vops.sms_date IS NOT NULL AND vops.sms_response IS NOT NULL)";
                break;
            case "Pending":
                $array_data['sms_status'] = " AND (vops.sms_date IS NULL AND vops.sms_response IS NULL)";
                break;
            case "Error":
                $array_data['sms_status'] = " AND (vops.sms_date IS NULL AND vops.sms_response IS NOT NULL)";
                break;
            default:
                $array_data['sms_status'] = "";
        }


        $array_data['company_access']  = input('company_id') ? input('company_id') : implode(",", Session::get('company_permissions'));
        $array_data['dealer_access']  = input('dealer_id');

        $result =  $this->lto->getOrcrPlateList($array_data);
        $response['rows'] = array();
        $response['total']  = $this->lto->getOrcrPlateListCount($array_data);


        foreach ($result as $row) {


            if ($row['sms_date'] && $row['sms_response'] != NULL) {
                $status = '<span class="right text-sm badge font-weight-normal badge-success">SMS Sent</span>';
            } else if (!$row['sms_date'] &&  $row['sms_response'] != NULL) {
                // $status = '<span class="right text-sm badge font-weight-normal badge-danger">Error: ' . $row['sms_response'] . '</span>';
                 $smsResponse = json_decode($row['sms_response'], true);
                if(!$smsResponse['Error'] && $smsResponse['Failed'] == 0 && $smsResponse['Accepted']==1 )
                {
                    $status = '<span class="right text-sm badge font-weight-normal badge-success">SMS Sent</span>';
                    
                }
                else
                {
                $status = '<span class="right text-sm badge font-weight-normal badge-danger">Error: ' . $row['sms_response'] . '</span>
                ';
                }
            } else if (!$row['sms_date'] &&  $row['sms_response'] == NULL) {
                $status = '<span class="right text-sm badge font-weight-normal badge-warning">Pending</span>';
            }

            if ($row['vops_type'] == "orcr_plate") {
                $type =  "ORCR & Plate";
            } else if ($row['vops_type'] == "orcr") {
                $type = "ORCR";
            } else if ($row['vops_type'] == "plate") {
                $type = "Plate";
            }

            if ($row['pickup_date']) {
                $pickupdate = date('F d, Y', strtotime($row['pickup_date']));
            } else {
                $pickupdate = '<span data-action-pickup class=" right text-sm badge font-weight-normal badge-primary">Pick up</span>';
            }

            $response['rows'] = array_merge(
                $response['rows'],
                array(array(
                    'vops_id' => $row['vops_id'],
                    'date' =>  date('F d, Y', strtotime($row['date_added'])),
                    'customer' => $row['type'] == "1" ? $row['first_name'] . ' ' .  $row['last_name']   :  $row['corporation_name'],
                    // 'released_date' => '',
                    'vehicle' => $row['model_variant'],
                    'conduction_sticker' => $row['conduction_sticker'],
                    'plate' => $row['plate_number'],
                    'mv_file' => $row['mv_file'],
                    'engine_number' => $row['engine_number'],
                    'chassis_number' => $row['chassis_number'],
                    'type' => $type,
                    'date_received' => date('F d, Y', strtotime($row['date_received'])),
                    'status' => $status,
                    'pickup_date' => $pickupdate,
                    'notes' => $row['notes'],
                    'company_dealer' => $row['company_code'] . ' - ' . $row['dealer_code'],
                ))
            );
        }

        response()->json($response);
    }

    public function resendAll()
    {
        $not_sent = 0;
        $sent = 0;

        $sms_model  = new \App\Models\Sms;
        $sms_module_id = 1; //orcr_plate
        if ($sms_model->getSmsModuleStatus($sms_module_id)) {
            $response = $this->lto->getAllError();
            foreach ($response as $row) {
                $lto_dealer_contact = $this->lto->getLtoDealerContacts($row['dealer_id']);
                if (count($lto_dealer_contact) <= 0) {
                    $not_sent++;
                    continue;
                }

                if (empty($lto_dealer_contact[0]['contact_person']) || empty($lto_dealer_contact[0]['contact_phone'])) {
                    $not_sent++;
                    continue;
                }

                $vehicle = new \App\Models\Vehicle;
                $vehicle_data = $vehicle->getVehicleDataByCS($row['conduction_sticker']);

                //get message template
                if ($row['type'] == "orcr_plate") {
                    $sms_data['message'] = $sms_model->getSmsTemplate(3);
                } else if ($row['type'] == "orcr") {
                    $sms_data['message'] = $sms_model->getSmsTemplate(1);
                } else if ($row['type'] == "plate") {
                    $sms_data['message'] = $sms_model->getSmsTemplate(2);
                }

                $replace_parameter = array(
                    '[plate]' => $vehicle_data[0]['plate_number'],
                    '[cs]' => $vehicle_data[0]['conduction_sticker'],
                    '[brand]' => $vehicle_data[0]['brand'],
                    '[model]' => $vehicle_data[0]['model'],
                    '[variant]' => $vehicle_data[0]['model_variant'],
                    '[company]' => $vehicle_data[0]['company'],
                    '[dealer]' => $vehicle_data[0]['dealer'],
                    '[contact_person]' => $lto_dealer_contact[0]['contact_person'],
                    '[contact_email]' => $lto_dealer_contact[0]['contact_email'],
                    '[contact_phone]' => $lto_dealer_contact[0]['contact_phone'],
                );

                $sms_data['message']  = strtr($sms_data['message'], $replace_parameter);
                $sms_data['mobile'] = $vehicle_data[0]['mobile_phone_1'];

                $array_data['record_id'] = $row["id"];

                //send sms to customer
                $excluded_year_below = 2021;
                if (date('Y', strtotime($row['date_received'])) <= $excluded_year_below) {
                    $array_data['sms_date'] =  date("Y-m-d h:i:s");
                    $array_data['sms_response'] = $excluded_year_below;
                    $not_sent++;
                } else {
                    $sms_response = Sms::sendSms($sms_data);
                    $array_data['sms_date'] = $sms_response == SMS_SUCCESS ? date("Y-m-d h:i:s") : NULL;
                    $array_data['sms_response'] = $sms_response;

                    if ($sms_response == SMS_SUCCESS) {
                        $sent++;
                    } else {
                        $not_sent++;
                    }
                }

                $this->lto->updateOrcrPlateSms($array_data);
            }
        } else {
            response()->json(array("status" => 0, "message" => "SMS API Disabled"));
        }
        response()->json(array("sent" => $sent, "not_sent" => $not_sent, "status" => 1, "message" => "Sent"));
    }

    public function orcrPlateCreate()
    {
        // echo (input('chk_send_sms'));
        // exit;
        // print_r(input());
        $is_orcr = input('chk_orcr');
        $is_plate = input('chk_plate');
        $is_add_anyway = input('add_anyway');
        $cs = strtoupper(input('txt_cs'));
        $plate = strtoupper(input('txt_plate'));
        $or = strtoupper(input('txt_or'));
        $cr = strtoupper(input('txt_cr'));
        $mv = strtoupper(input('txt_mv'));
        $date_received = strtoupper(input('txt_received_date'));
        $engine_no = strtoupper(input('txt_en'));
        $chassis_no = strtoupper(input('txt_cn'));
        $notes = strtoupper(input('txt_notes'));

        $vehicle = new \App\Models\Vehicle;
        //check if cs is exist
        $vehicle_data = $vehicle->getVehicleDataByCS($cs);
        if (count($vehicle_data) <= 0) {
            $response['message'] = "Conduction sticker is not exist.";
            $response['status'] = 0;
            response()->json($response);
        }

        //VALIDATE LTO CONTACT PERSON
        $lto_dealer_contact = $this->lto->getLtoDealerContacts($vehicle_data[0]['dealer_id']);
        if (count($lto_dealer_contact) <= 0) {
            $response['message'] = "No LTO dealer contact information. contact system admin.";
            $response['status'] = 0;
            response()->json($response);
        }

        if (empty($lto_dealer_contact[0]['contact_person']) || empty($lto_dealer_contact[0]['contact_phone'])) {
            $response['message'] = "Incomplete LTO dealer contact information. contact system admin.";
            $response['status'] = 0;
            response()->json($response);
        }

        $array_data['vehicle_id'] = $vehicle_data[0]['id'];
        $array_data['user_id'] = Session::get('user')['id'];
        $array_data['plate'] = Utility::removeNotAlphaNumeric($plate);
        $array_data['or'] = Utility::removeNotAlphaNumeric($or);
        $array_data['cr'] = Utility::removeNotAlphaNumeric($cr);
        $array_data['mv'] = Utility::removeNotAlphaNumeric($mv);
        $array_data['engine_no'] = Utility::removeNotAlphaNumeric($engine_no);
        $array_data['chassis_no'] = Utility::removeNotAlphaNumeric($chassis_no);
        $array_data['notes'] = $notes;
        $array_data['date_received'] = date("Y-m-d", strtotime($date_received));
        $array_data['status'] = "1";

        if ($is_orcr && $is_plate) {
            $array_data['type']  = "orcr_plate";
        } else if ($is_orcr) {
            $array_data['type'] = "orcr";
        } else if ($is_plate) {
            $array_data['type'] = "plate";
        }

        //check if data recently added

        if (!input('record_id')) {
            if ($is_add_anyway == '0') {
                $existing_data = $this->lto->checkorcrPlateExisting($array_data);
                if (count($existing_data) > 0) {
                    $response['status'] = 2;
                    response()->json($response);
                }
            }
        }

        //update vehicle or cr plate
        if ($plate) {
            //check plate exist in other vehicle 
            $vehicle->updateVehiclePlate($array_data);
        }
        if ($or) {
            $vehicle->updateVehicleOr($array_data);
        }
        if ($cr) {
            $vehicle->updateVehicleCr($array_data);
        }
        if ($mv) {
            $vehicle->updateVehicleMv($array_data);
        }
        if ($engine_no) {
            $vehicle->updateVehicleEngineNo($array_data);
        }
        if ($chassis_no) {
            $vehicle->updateVehicleChassisNo($array_data);
        }

        if (!input('record_id') || input('chk_send_sms')) {
            $sms_model  = new \App\Models\Sms;
            //get message template
            if ($array_data['type'] == "orcr_plate") {
                $sms_data['message'] = $sms_model->getSmsTemplate(3);
            } else if ($array_data['type'] == "orcr") {
                $sms_data['message'] = $sms_model->getSmsTemplate(1);
            } else if ($array_data['type'] == "plate") {
                $sms_data['message'] = $sms_model->getSmsTemplate(2);
            }

            $replace_parameter = array(
                '[plate]' => $vehicle_data[0]['plate_number'],
                '[cs]' => $vehicle_data[0]['conduction_sticker'],
                '[brand]' => $vehicle_data[0]['brand'],
                '[model]' => $vehicle_data[0]['model'],
                '[variant]' => $vehicle_data[0]['model_variant'],
                '[company]' => $vehicle_data[0]['company'],
                '[dealer]' => $vehicle_data[0]['dealer'],
                '[contact_person]' => $lto_dealer_contact[0]['contact_person'],
                '[contact_email]' => $lto_dealer_contact[0]['contact_email'],
                '[contact_phone]' => $lto_dealer_contact[0]['contact_phone'],
            );

            $sms_data['message']  = strtr($sms_data['message'], $replace_parameter);
            $sms_data['mobile'] = $vehicle_data[0]['mobile_phone_1'];


            // if ($sms_data['mobile'] == '+639399064816') {
            //     $sms_data['mobile'] = "+639176331398"; //debug
            // }


            //check if sms module is enabled
            $sms_module_id = 1; //orcr_plate
            if ($sms_model->getSmsModuleStatus($sms_module_id)) {
                //send sms to customer

                $excluded_year_below = 2021;
                if (!input('record_id') && date('Y', strtotime($date_received)) <= $excluded_year_below) {
                    $array_data['sms_date'] =  date("Y-m-d h:i:s");
                    $array_data['sms_response'] = $excluded_year_below;
                } else {
                    $sms_response = Sms::sendSms($sms_data);

                    if (empty($sms_response)) {
                        $sms_response =  SMS_SUCCESS;
                    }

                    $array_data['sms_date'] = $sms_response == SMS_SUCCESS ? date("Y-m-d h:i:s") : NULL;
                    $array_data['sms_response'] = $sms_response;
                }
            } else {
                $array_data['sms_date'] = NULL;
                $array_data['sms_response'] = NULL;
            }
        }
        //update data to database
        if (input('record_id')) {
            $array_data['record_id'] = input('record_id');
            if (input('chk_send_sms')) {
                $this->lto->updateOrcrPlateSms($array_data);
            }
            $this->lto->updateOrcrPlate($array_data);
        } else {
            $this->lto->createOrcrPlateWithSMS($array_data);
        }

        //insert data to database

        $response['message'] = "Data successfully created.";
        $response['status'] = 1;
        response()->json($response);
    }

    public function orcrPlatePickup()
    {
        // echo (input('chk_send_sms'));
        // exit;
        // print_r(input()); 
        $this->lto->updateOrcrPlatePickup(array(input('id')));

        $response['message'] = "Data successfully saved.";
        $response['status'] = 1;
        response()->json($response);
    }

    function orcrImportFields()
    {
        $import = new \App\Models\Import;
        $result = $import->getImportFields(2);
        $response = array();
        foreach ($result as $row) {
            $response[$row['id']] = $row;
        }
        response()->json($response);
    }

    public function orcrImport()
    {

        $disk_dir = APP_DISK . input('dir');
        if (!file_exists($disk_dir)) {
            mkdir($disk_dir, 0777, true);
        }

        $log_dir = APP_DISK . input('log_dir');
        if (!file_exists($log_dir)) {
            mkdir($log_dir, 0777, true);
        }

        $import = new \App\Models\Import;
        $import_fields = json_decode(input('import_fields'), true);

        foreach ($import_fields as $key => $value) {
            $import->updateImportFields(array(
                'key' => $key,
                'value' => $value,
            ));
        }


        // $destinationFilname = sprintf('%s.%s', uniqid(), $image->getExtension());
        // $image->move(sprintf('/uploads/%s', $destinationFilename));

        $object = input()->file('file', $defaultValue = null);
        if ($object->getSize() <= 0) {
            $response['message'] = "File is empty";
            $response['status'] = 0;
            response()->json($response);
        } else {
            //check extension
            $valid_ext =  array('xls', 'csv', 'xlsx');
            if (!in_array($object->getExtension(), $valid_ext)) {
                $response['message'] = "Invalid file extension";
                $response['status'] = 0;
                response()->json($response);
            }

            //gen filename
            $destinationFilename = sprintf('%s.%s', Uuid::long(), $object->getExtension());
            //move file
            $full_dir = $disk_dir . '/' . $destinationFilename;
            $object->move($full_dir);

            // start import process
            $GLOBALS['total_data_count'] = 0;
            $GLOBALS['not_inserted_data_count'] = 0;
            $GLOBALS['inserted_data_count'] = 0;
            $GLOBALS['not_inserted_list_v2'] = array();
            $GLOBALS['data_count_list_v2'] = array();

            $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($full_dir);
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type);
            try {

                $spreadsheet = $reader->load($full_dir);
                //remove excel after store to phpspreadsheet
                unlink($full_dir);

                $data = $spreadsheet->getActiveSheet()->toArray();
                $highestRow = $spreadsheet->getActiveSheet()->getHighestRow();
                $highestColumn =  $spreadsheet->getActiveSheet()->getHighestColumn();
                $ColumnNumber = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);


                $data = array_map(array(new Utility(), 'upperCaseNestedArray'), $data);

                $header_column_found = false;
                foreach ($data as $row) {
                    if (!empty(array_filter($row))) {
                        // echo 'Not empty row';
                    } else {
                        continue;
                    }
                    $row = array_filter($row);

                    if (!$header_column_found) {

                        // get matched field from database and excel globals
                        $issue_summary_index = $import->getSummaryIndex('SUMMARY', $ColumnNumber, $row);
                        $GLOBALS['conduction_sticker'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[11]));
                        $GLOBALS['plate_number'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[12]));
                        $GLOBALS['mv_file'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[13]));
                        $GLOBALS['date'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[14]));
                        $GLOBALS['notes'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[15]));
                        $GLOBALS['engine_number'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[16]));
                        $GLOBALS['chassis_number'] = $import->getFieldName($row, $ColumnNumber, array($import_fields[17]));


                        // check required fields
                        if (
                            !empty($GLOBALS['conduction_sticker']) &&
                            !empty($GLOBALS['plate_number']) &&
                            !empty($GLOBALS['mv_file']) &&
                            !empty($GLOBALS['date']) &&
                            !empty($GLOBALS['notes']) &&
                            !empty($GLOBALS['engine_number']) &&
                            !empty($GLOBALS['chassis_number'])
                        ) {
                            $header_column_found = true;
                            $GLOBALS['row_headers'] = $row;
                            continue;
                        }
                    } else {
                        $GLOBALS['total_data_count']++;

                        $data_conduction_sticker = $import->checkExist($row, $GLOBALS['conduction_sticker'], $GLOBALS['row_headers']);
                        $data_plate_number = $import->checkExist($row, $GLOBALS['plate_number'], $GLOBALS['row_headers']);
                        $data_plate_number = Utility::removeNotAlphaNumeric($data_plate_number);
                        $data_mv_file = $import->checkExist($row, $GLOBALS['mv_file'], $GLOBALS['row_headers']);
                        $data_mv_file =  Utility::removeNotAlphaNumeric($data_mv_file);
                        $data_date = $import->checkExist($row, $GLOBALS['date'], $GLOBALS['row_headers']);
                        $data_date = date('Y-m-d', strtotime($data_date));
                        $data_notes = $import->checkExist($row, $GLOBALS['notes'], $GLOBALS['row_headers']);
                        $data_engine_number = $import->checkExist($row, $GLOBALS['engine_number'], $GLOBALS['row_headers']);
                        $data_engine_number =  Utility::removeNotAlphaNumeric($data_engine_number);
                        $data_chassis_number = $import->checkExist($row, $GLOBALS['chassis_number'], $GLOBALS['row_headers']);
                        $data_chassis_number =  Utility::removeNotAlphaNumeric($data_chassis_number);


                        //Validations
                        if (empty($data_conduction_sticker)) {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("NO CONDUCTION STICKER"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        if ($data_date == "1970-01-01") {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("NO DATE"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        if (strtotime($data_date) > strtotime(date('Y-m-d'))) {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("INVALID DATE"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        if ($data_date == "1970-01-01") {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("NO DATE"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        $vehicle = new \App\Models\Vehicle;
                        $vehicle_data = $vehicle->getVehicleDataByCS($data_conduction_sticker);
                        if (count($vehicle_data) <= 0) {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("VEHICLE DATA NOT EXIST IN SYSTEM"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        $lto_dealer_contact = $this->lto->getLtoDealerContacts($vehicle_data[0]['dealer_id']);
                        if (count($lto_dealer_contact) <= 0) {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("NO LTO DEALER CONTACT INFO"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        if (empty($lto_dealer_contact[0]['contact_person']) || empty($lto_dealer_contact[0]['contact_phone'])) {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("INCOMPLETE LTO DEALER CONTACT INFO"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        $array_data['vehicle_id'] = $vehicle_data[0]['id'];
                        $array_data['user_id'] = Session::get('user')['id'];
                        $array_data['plate'] = $data_plate_number;
                        $array_data['mv'] = $data_mv_file;
                        $array_data['date_received']  = $data_date;
                        $array_data['notes'] = $data_notes;
                        $array_data['engine_no'] = $data_engine_number;
                        $array_data['chassis_no'] = $data_chassis_number;
                        $array_data['status'] = "1";


                        //identify the type
                        if ($data_mv_file && $data_plate_number) {
                            $array_data['type']  = "orcr_plate";
                            $vehicle->updateVehiclePlate($array_data);
                            $vehicle->updateVehicleMv($array_data);
                        } else if ($data_mv_file) {
                            $array_data['type'] = "orcr";
                            $vehicle->updateVehicleMv($array_data);
                        } else if ($data_plate_number) {
                            $array_data['type'] = "plate";
                            $vehicle->updateVehiclePlate($array_data);
                        } else {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("NO MV FILE OR PLATE"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        $existing_data = $this->lto->checkorcrPlateExisting($array_data);
                        if (count($existing_data) > 0) {
                            $GLOBALS['not_inserted_list_v2'][] = array_merge(array("ALREADY UPLOADED IN SYSTEM"), $row);
                            $GLOBALS['not_inserted_data_count']++;
                            continue;
                        }

                        if ($data_engine_number) {
                            $vehicle->updateVehicleEngineNo($array_data);
                        }
                        if ($data_chassis_number) {
                            $vehicle->updateVehicleChassisNo($array_data);
                        }

                        $array_data['record_id'] = $this->lto->createOrcrPlate($array_data);

                        //for 2021 records fill the sms
                        $excluded_year_below = 2021;
                        if (date('Y', strtotime($data_date)) <= $excluded_year_below) {
                            $array_data['sms_date'] =  date("Y-m-d h:i:s");
                            $array_data['sms_response'] = $excluded_year_below;
                            $this->lto->updateOrcrPlateSms($array_data);
                        }

                        $GLOBALS['not_inserted_list_v2'][] = array_merge(array("SUCCESS IMPORT"), $row);
                        $GLOBALS['inserted_data_count']++;
                    }
                }

                if (!$header_column_found) {
                    $return_arr["status"] = 2;
                    $return_arr["message"] = " Header not found.";
                    response()->json($return_arr);
                } else {
                    $return_arr["status"] = 1;
                    $return_arr["gen_file"] = $destinationFilename;
                    $return_arr["orig_file"] = $object->getFilename();
                    $return_arr["total"] = $GLOBALS['total_data_count'];
                    $return_arr["inserted"] = $GLOBALS['inserted_data_count'];
                    $return_arr["not_inserted"] = $GLOBALS['not_inserted_data_count'];
                    $return_arr["message"] = " File Uploaded.";


                    ///START GEN LOG
                    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

                    $GLOBALS['data_count_list_v2'][] = array("Total Data", (string) ($GLOBALS['total_data_count']));
                    $GLOBALS['data_count_list_v2'][] = array("Inserted Data", (string) ($GLOBALS['inserted_data_count']));
                    $GLOBALS['data_count_list_v2'][] = array("Not Inserted Data", (string) ($GLOBALS['not_inserted_data_count']));
                    $GLOBALS['data_count_list_v2'][] = array("Import By", (string) Session::get('user')['first_name'] . ' ' . Session::get('user')['last_name']);
                    $GLOBALS['data_count_list_v2'][] = array("Import Date", (string)  date('F d, Y'));


                    $data_count_list_v2_count = count($GLOBALS['data_count_list_v2']);

                    $spreadsheet
                        ->getActiveSheet()
                        ->getStyle('B' . ($data_count_list_v2_count + 1) . ':' . (string) $highestColumn . ($data_count_list_v2_count + 1))
                        ->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()
                        ->setARGB('FFFF00');
                    $spreadsheet
                        ->getActiveSheet()
                        ->getStyle('A' . ($data_count_list_v2_count + 1) . '')
                        ->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()
                        ->setARGB('FF0000');

                    $spreadsheet->getActiveSheet()->getStyle('A' . ($data_count_list_v2_count + 1))
                        ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

                    $sheet = $spreadsheet->getActiveSheet();
                    for ($i = 'A'; $i != $highestColumn; $i++) {
                        $sheet->getColumnDimension($i)->setAutoSize(true);
                    }

                    foreach ($GLOBALS['not_inserted_list_v2'] as $key => $subArr) {
                        unset($subArr[intval($issue_summary_index) + 1]);
                        $GLOBALS['not_inserted_list_v2'][$key] = $subArr;
                    }

                    unset($GLOBALS['row_headers'][intval($issue_summary_index)]);

                    usort($GLOBALS['not_inserted_list_v2'], function ($a, $b) {
                        return $a[0] <=> $b[0];
                    });

                    $GLOBALS['not_inserted_list_v2'] = array_merge(array(array_map("strtoupper", array_merge(array('SUMMARY'), $GLOBALS['row_headers']))),  $GLOBALS['not_inserted_list_v2']);

                    if ($data_count_list_v2_count > 0) {
                        $GLOBALS['not_inserted_list_v2'] = array_merge($GLOBALS['data_count_list_v2'], $GLOBALS['not_inserted_list_v2']);
                    }

                    $sheet->fromArray($GLOBALS['not_inserted_list_v2'], NULL, 'A1');
                    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
                    $writer->save($log_dir .  '/' . $destinationFilename);

                    //END GEN LOG
                    // echo json_encode($return_arr);
                    response()->json($return_arr);
                }
            } catch (\Exception $e) {
                $response['message'] = "Error has occured " . $e;
                $response['status'] = 0;
                response()->json($response);
            }
        }
    }

    public function orcrImportSummaryDownload()
    {
        $log_dir = APP_DISK . "import/orcr_plate/logs/";
        $file = $log_dir . input('file');

        $fp = fopen($file, 'rb');

        header("Content-Type: application/octet-stream");
        header("Content-Disposition: attachment; filename=" . escape(input('name')) . "");
        header("Content-Length: " . filesize($file));
        fpassthru($fp);
    }

    public function orcrPlateSmsUpdateCron()
    {
        // curl -H "App-Dev-Id: 626ca81708a1f" https://localhost/cron/lto/orcr_plate/sms --insecure
        // curl -H "App-Dev-Id: 626ca81708a1f" https://connect.autohub.ph/cron/lto/orcr_plate/sms

        $headers = apache_request_headers();
        if (!isset($headers['App-Dev-Id'])) {
            echo "Access Denied";
            exit;
        }
        if ($headers['App-Dev-Id'] != "626ca81708a1f") {
            echo "Invalid Key";
            exit;
        }
        $array_data['company_access'] = "";
        $array_data['search'] = "";
        $array_data['date_added'] = "";
        $array_data['date_received'] = "";
        $array_data['company_access'] = "";
        $array_data['dealer_access'] = "";
        $array_data['sort'] = "";
        $array_data['sms_status'] = " AND (vops.sms_date IS NULL AND vops.sms_response IS NULL)";

        $response = $this->lto->getNoSmsOrcrPlateList($array_data);

        $sms_model  = new \App\Models\Sms;

        //get templates
        $template['orcr_plate'] = $sms_model->getSmsTemplate(3);
        $template['plate'] = $sms_model->getSmsTemplate(2);
        $template['orcr'] = $sms_model->getSmsTemplate(1);

        foreach ($response as $row) {
            if ($row['vops_type'] == "orcr_plate") {
                $sms_data['message'] = $template['orcr_plate'];
            } else if ($row['vops_type'] == "orcr") {
                $sms_data['message'] = $template['orcr'];
            } else if ($row['vops_type'] == "plate") {
                $sms_data['message'] =   $template['plate'];
            }

            $array_data['record_id'] = $row['vops_id'];

            $replace_parameter = array(
                '[plate]' => $row['plate_number'],
                '[cs]' => $row['conduction_sticker'],
                '[brand]' => $row['brand'],
                '[model]' => $row['model'],
                '[variant]' => $row['model_variant'],
                '[company]' => $row['company'],
                '[dealer]' => $row['dealer'],
                '[contact_person]' => $row['contact_person'],
                '[contact_email]' => $row['contact_email'],
                '[contact_phone]' => $row['contact_phone'],
            );

            $sms_data['message']  = strtr($sms_data['message'], $replace_parameter);
            $sms_data['mobile'] = $row['mobile_phone_1'];


            // $sms_data['mobile'] = "+639389592854"; //debug

            //check if sms module is enabled
            $sms_module_id = 1; //orcr_plate
            if ($sms_model->getSmsModuleStatus($sms_module_id)) {
                if (empty($row['contact_person']) || empty($row['contact_phone'])) {
                    $array_data['sms_date'] = NULL;
                    $array_data['sms_response'] = "No LTO Dealer contact";
                } else {
                    //send sms to customer
                    $sms_response = Sms::sendSms($sms_data);
                    $array_data['sms_date'] = $sms_response == SMS_SUCCESS ? date("Y-m-d h:i:s") : NULL;
                    $array_data['sms_response'] = $sms_response;
                }
            } else {
                $array_data['sms_date'] = NULL;
                $array_data['sms_response'] = NULL;
            }

            $this->lto->updateOrcrPlateSms($array_data);
        }
    }

    public function orcrStatusByCSMobile()
    {
        if (!input('mobile') && !input('cs') && !input('email')) {
            $response['message'] = "Insufficient parameters.";
            $response['status'] = 0;
            response()->json($response);
        }


        $array_data['mobile'] = \App\Utilities\MobileFormatter::format(Utility::removeNotAlphaNumeric(input('mobile')));
        $array_data['cs'] = Utility::removeNotAlphaNumeric(input('cs'));
        $array_data['email'] = input('email');

        // if (!empty($array_data['mobile']) && empty($array_data['cs'])) {
        //     $array_data['search'] = " AND cc.mobile_phone_1  LIKE ? AND ?";
        //     $array_data['search_keyword'][0] = "%" . $array_data['mobile'] . "%";
        //     $array_data['search_keyword'][1] = "1";
        // } else  if (empty($array_data['mobile']) && !empty($array_data['cs'])) {
        //     $array_data['search'] = " AND v.conduction_sticker = ? AND ? ";
        //     $array_data['search_keyword'][0] = $array_data['cs'];
        //     $array_data['search_keyword'][1] = "1";
        // } else {
        //     $array_data['search'] = " AND (
        //                                     cc.mobile_phone_1  LIKE ? 
        //                                     OR v.conduction_sticker = ? 
        //                                 )
        //                             ";
        //     $array_data['search_keyword'][0] = "%" . $array_data['mobile'] . "%";
        //     $array_data['search_keyword'][1] = $array_data['cs'];
        // }

        if (!empty($array_data['mobile']) && !empty($array_data['cs'])) {
            $array_data['search'] = " AND (REPLACE(cc.mobile_phone_1, '+63', '' ) = ? OR v.conduction_sticker = ?) ";
            $array_data['search_keyword'][0] = $array_data['mobile'];
            $array_data['search_keyword'][1] = $array_data['cs'];
        } else if (!empty($array_data['mobile'])) {
            $array_data['search'] = " AND REPLACE(cc.mobile_phone_1, '+63', '' ) = ? AND ?";
            $array_data['search_keyword'][0] = $array_data['mobile'];
            $array_data['search_keyword'][1] = "1";
        } else if (!empty($array_data['cs'])) {
            $array_data['search'] = " AND v.conduction_sticker = ? AND ? ";
            $array_data['search_keyword'][0] = $array_data['cs'];
            $array_data['search_keyword'][1] = "1";
        } else if (!empty($array_data['email'])) {
            $array_data['search'] = " AND cc.email_1 = ? AND ? ";
            $array_data['search_keyword'][0] = $array_data['email'];
            $array_data['search_keyword'][1] = "1";
        }

        $result =  $this->lto->getOrcrPlateStatusList($array_data);
        $response['data'] = array();

        foreach ($result as $row) {

            if ($row['sms_date'] && $row['sms_response'] != NULL) {
                $status = 'SMS Sent'; //'<span class="right text-sm badge font-weight-normal badge-success">SMS Sent</span>';
            } else if (!$row['sms_date'] &&  $row['sms_response'] != NULL) {
                $status = 'Error'; //'<span class="right text-sm badge font-weight-normal badge-danger">Error: ' . $row['sms_response'] . '</span>';
            } else if (!$row['sms_date'] &&  $row['sms_response'] == NULL) {
                $status = 'Pending'; //'<span class="right text-sm badge font-weight-normal badge-warning">Pending</span>';
            }

            if ($row['vops_type'] == "orcr_plate") {
                $type =  "ORCR & Plate";
            } else if ($row['vops_type'] == "orcr") {
                $type = "ORCR";
            } else if ($row['vops_type'] == "plate") {
                $type = "Plate";
            }

            $response['data'] = array_merge(
                $response['data'],
                array(array(
                    'id' => $row['vehicle_id'],
                    'date_add' =>  date('F d, Y', strtotime($row['date_added'])),
                    'customer' => $row['type'] == "1" ? $row['first_name'] . ' ' .  $row['last_name']   :  $row['corporation_name'],
                    'firstname' => $row['type'] == "1" ? $row['first_name'] :  '',
                    'lastname' => $row['type'] == "1" ? $row['last_name'] :  '',
                    'mobile' =>  $row['mobile_phone_1'],
                    'brand' =>  $row['brand'],
                    'model' => $row['model'],
                    // 'released_date' => '',
                    'vehicle' => $row['model_variant'],
                    'conduction_sticker' => $row['conduction_sticker'],
                    'plate' => $row['plate_number'],
                    'mv_file' => $row['mv_file'],
                    'engine_number' => $row['engine_number'],
                    'chassis_number' => $row['chassis_number'],
                    'type' => $type,
                    'date_received' => date('F d, Y', strtotime($row['date_received'])),
                    'date_pickup' => $row['pickup_date'] ? date('F d, Y', strtotime($row['pickup_date'])) : '',
                    'sms_status' => $status,
                    'plate_status' =>  $row['pickup_date'] ? 'Picked-up' : ($this->lto->getPlateStatus(array($row['vehicle_id'])) ? 'Available' : 'Not Available'),
                    'orcr_status' =>  $row['pickup_date'] ? 'Picked-up' : ($this->lto->getORCRStatus(array($row['vehicle_id'])) ? 'Available' : 'Not Available'),
                    'notes' => $row['notes'],
                    'company_dealer' => $row['company_code'] . ' - ' . $row['dealer_code'],
                    'sales_person' => $row['sales_person'],
                    'sc_id' => $row['asa_sales_person'],
                ))
            );
        }

        $response['message'] = count($response['data']) ? 'Success' : 'No data fetched.';
        $response['status'] = count($response['data']) ? 1 : 0;
        response()->json($response);
    }
}