<?php 
/*
 * @Author: Jino Lacson
 * @Date: 2018-10-26 14:40:50
 * @Last Modified by: jlacson@wylog.com
 * @Last Modified time: 2018-11-07 15:53:44
 */

if (! defined('BASEPATH')) {
    exit('No direct script access allowed');
}

use app\core\utils\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Noshow_import_controller extends MY_Controller
{
    /**
     * Holds list of valid excel extensions
     *
     * @var array
     */
    private $extensions = ['.xlsx','.csv','.xls','.ods'];

    /**
     * Holds author variable
     *
     * @var int
     */
    private $author;

    /**
     * Holds file name
     *
     * @var string
     */
    private $fileName;

    /**
     * Holds file type
     *
     * @var string
     */
    private $fileType;
    
    /**
    * Holds object variable
    *
    * @var string
    */
    private $reader;

    /**
    * Holds spreadsheet variable
    *
    * @var string
    */
    private $sheets;

    /**
    * Initialize active sheet index
    *
    * @var string
    */
    private $activeSheetIndex = 0;

    /**
     * Holds readed cells values
     *
     * @var array
     */
    private $readedData = [];

    /**
     * Start reader at row 6
     *
     * @var integer
     */
    private $startRow = 6;

    /**
     * Instantiate validation as false
     *
     * @var boolean
     */
    private $errors = [false];

    /**
     * Holds Booking or Confirmed list(s)
     *
     * @var array
     */
    private $bookingData = [];

    /**
     * Holds Waiting or Cancelled list(s)
     *
     * @var array
     */
    private $waitingData = [];

    /**
     * Holds Extra people(s)
     *
     * @var array
     */
    private $extraData = [];

    /**
     * No show upload folder location
     *
     * @var string
     */
    private $folder = './resources/images/backoffice/noshow_imports/';
    
    /**
     * Added status, booking = 1
     *
     * @var integer
     */
    private const Addedbooking = 1;

    /**
     * Added status, waiting = 0
     *
     * @var integer
     */
    private const AddedWaiting = 0;

    /**
     * Save type 1 meaning imported thru excel file
     *
     * @var integer
     */
    private const saveTypeExcel = 1;

    public function __construct()
    {
        $this->my_parent_controller();
        Response::handleSessionTimeout("bo");

        $this->load->model('nshow_model');
        $this->lang->load('backoffice/attendance', 'fr');

        $config['upload_path'] =  $this->folder;
        $config['allowed_types'] = '*';
        $config['max_size']     = 0;

        $this->load->library('upload', $config);
    }

    /**
     * Get uploader Id
     *
     * @return string
     * access private member
     */
    private function getAuthor() : string
    {
        if (is_null($this->author)) {
            return $this->data["logged_in"]["user_id"] ?? 0;
        }
    }

    /**
     * Read uploaded filename
     *
     * @return string
     * access private member
     */
    private function readFileName(string $fileName): string
    {
        $this->fileName = $fileName;
        
        if (is_null($this->fileName)) {
            return false;
        }

        return $this->fileName;
    }
    
    private function initializeObjectReader(string $fileName) : array
    {
        //Instantiate filename
        $this->fileName = $this->readFileName($fileName);

        //Identify the file type
        $this->fileType = IOFactory::identify($this->fileName);

        //Instantiate reader Object
        $this->reader = IOFactory::createReader($this->fileType);

        //Load proper reader for different file type(s)
        $this->sheets = $this->reader->load($this->fileName);

        $activeSheet = $this->sheets->getSheet($this->activeSheetIndex);
        $highestRow = $activeSheet->getHighestRow();
        $highestColumn = $activeSheet->getHighestColumn();

        //return configurations
        return array(
            'activeSheet' => $activeSheet,
            'highestRow' => $highestRow,
            'highestCol'  => $highestColumn
        );
    }

    /**
     * Read excell rows and columns
     *
     * @return array
     * access private member
     */
    private function readExcelRowsAndColums(string $fileName) : array
    {
        //Call object reader
        $excelConfig = $this->initializeObjectReader($fileName);

        //try read excell row and columns starting A:6 downwards
        for ($rows = $this->startRow; $rows <= $excelConfig['highestRow']; $rows++) {
            $cells = $excelConfig['activeSheet']->rangeToArray(
                'A' . $rows . ':' . $excelConfig['highestCol']. $rows,
                null,
                true,
                false
            );

            //read cell by cell
            foreach ($cells as $key => $readedValues) {
                array_push($this->readedData, $readedValues);
            }
        }

        //return readed values
        return $this->readedData;
    }

    /**
     * Read Range A2:A3 which the "Event ID" and "Event Schedule ID" stored
     *
     * @param string $fileName
     * @return array
     * access private member
     */
    private function readExcelHeaders(string $fileName) : array
    {
        //Call object reader
        $excelConfig = $this->initializeObjectReader($fileName);

        $cellRangeIDs = $excelConfig['activeSheet']->rangeToArray(
           'A2:A4',
           null,
           true,
           false
        );

        //Store event ID , Event Schedule ID
        return [
           'eventId' => $cellRangeIDs[0],
           'eventScheduleId' => $cellRangeIDs[1],
           'totalSubscribers' => $cellRangeIDs[2]
       ];
    }

    /**
     * List of ID'S Waitlist, Booking, Extra people
     *
     * @return integer
     * access private member
     */
    private function primaryKeys(int $primaryKey) : int
    {
        return trim($primaryKey) ?? 0;
    }

    /**
     * Determine if what type booking/waiting or Extra people
     *
     * @param integer $type
     * @return string
     * access private member
     */
    private function type(string $type) : string
    {
        return trim(ucfirst($type)) ?? '';
    }

    /**
     * Return booking/waiting
     *
     * @param int $lists
     * @return integer
     * access private member
     */
    private function bookingAndWaiting(int $lists) : int
    {
        return trim($lists) ?? 0;
    }

    /**
     * Return civility, Last name, First name, Email
     *
     * @param string $fields
     * @return string
     * acccess private member
     */
    private function fields(string $fields) : string
    {
        return $fields ?? '';
    }

    /**
     * Comments
     *
     * @param string $comments
     * @return string
     * access private member
     */
    private function comments($comments = null) : string
    {
        return $comments ?? '';
    }
    
    /**
    * Check if type is "extra people"
    *
    * @param string $type
    * @return boolean
    * access private member
    */
    private function isExtra(string $type)
    {
        $other_people = ["walk in", "Walk in", "extra", "Extra"];

        $checkType = trim(strtolower($type)) ?? false;

        if (is_null($type)) {
            return false;
        }

        
        return (in_array($checkType, $other_people));
    }

    /**
     * Filter, seperate and store in array the lists of booking, waiting, extra peoples
     *
     * @param string $search
     * @param array $excelValues
     * @return void
     * access private member
     */
    private function storeAndFilterExcelDataValues(string $search, array $excelValues)
    {
        //Holds return values
        $holder = [];

        /**
         * filter strings if type exactly the same
         * Type === Search
         * Where: Type and Search = Booking, Waiting, Extra
         */
        $search = (strtolower($excelValues['Type']) === strtolower($search)) ;

        //filter extra people for insert: Note only extra people are allowed for insert
        $isExtra = $this->isExtra($excelValues['Type']);

        //Store primary keys (This purpose is for "update" query)
        $keys = ($excelValues['PrimaryKeys'] > 0);

        //Filter values booking/waiting/extra for "UPDATE" and add to holder
        if ($keys && $search) {
            $holder[] = [
            'Action'            => 'update',
            'EventId'           => $excelValues['EventId'],
            'EventScheduleId'   => $excelValues['EventScheduleId'],
            'PrimaryKeys'       => $excelValues['PrimaryKeys'],
            'Type'              => $excelValues['Type'],
            'Gender'            => $excelValues['Gender'],
            'LastName'          => $excelValues['LastName'],
            'FirstName'         => $excelValues['FirstName'],
            'Email'             => $excelValues['Email'],
            'Booking'           => $excelValues['Booking'],
            'Waiting'           => $excelValues['Waiting'],
            'TotalPresent'      => $excelValues['TotalPresent'],
            'Comments'          => $excelValues['Comments'],
            'UserIds'           => $excelValues['UserIds'],
           ];
        }

        //Filter extra peoples for "INSERT" and add to holder
        if (empty($keys) && $search && $isExtra) {
            $holder[] = [
            'Action'            => 'insert',
            'EventId'           => $excelValues['EventId'],
            'EventScheduleId'   => $excelValues['EventScheduleId'],
            'PrimaryKeys'       => $excelValues['PrimaryKeys'],
            'Type'              => $excelValues['Type'],
            'Gender'            => $excelValues['Gender'],
            'LastName'          => $excelValues['LastName'],
            'FirstName'         => $excelValues['FirstName'],
            'Email'             => $excelValues['Email'],
            'Booking'           => $excelValues['Booking'],
            'Waiting'           => $excelValues['Waiting'],
            'TotalPresent'      => $excelValues['TotalPresent'],
            'Comments'          => $excelValues['Comments'],
            'UserIds'           => $excelValues['UserIds'],
           ];
        }

        //return filtered values
        return array_shift($holder);
    }
    /**
     * Compare total present entered by the users related to private member "validate()"
     *
     * @param integer $compare
     * @param integer $actual
     * @return boolean
     * access private members
     */
    private function validateTotalPresent(int $compare, int $actual) : bool
    {
        return $compare > $actual ;
    }

    /**
     * Validate numeric values only for no show
     *
     * @param integer $noShow
     * @return integer
     * acces private member
     */
    private function isNumericNoShow(int $noShow) : int
    {
        return !is_numeric($noShow) ? 0 : $noShow;
    }

    /**
     * Check empty user IDS
     *
     * @param integer $userIds = user ID
     * @return integer
     * access private member
     */
    private function checkUserId(int $userIds) : int
    {
        return $userIds ?? 0;
    }

    /**
     * Holds validation(s)
     * Validate first booking or Waiting list if total present are greater than the actual numbers,
     * and push errors in array
     *
     * @param array $booking
     * @param array $waiting
     * @param array $extra
     * @return boolean
     * access private member
     */
    private function validate(array $booking, array $waiting, array $extra) : array
    {
        foreach ($booking as $k => $booking) {
            if ($this->validateTotalPresent($booking['TotalPresent'], $booking['Booking'])) {
                array_push($this->errors, $booking);
            }
        }

        foreach ($waiting as $k => $waiting) {
            if ($this->validateTotalPresent($waiting['TotalPresent'], $waiting['Waiting'])) {
                array_push($this->errors, $waiting);
            }
        }

        //return boolean response
        return $this->errors;
    }

    /**
     * Main function of upload
     * - Check first if extensions are valid for upload
     * - Check if Upload helper are properly set
     * - Assign $fileName + folder location
     * - Diplay upload system error
     *
     * @return void
     * access public member
     */
    public function upload()
    {
        
        //If not upload no show display system error about upload
        if (!$this->upload->do_upload('AttendanceUpload')) {
            output_to_json($this, array(
                "mtype" => "error",
                "message" => $this->upload->display_errors()
            )) ;
            exit();
        }

        //initialize upload helper
        $files = $this->upload->data();

        //uploaded excel File Name
        $fileName = $this->folder.$files['file_name'];

        //if uploaded file in not a valid excel file(s) display error messages
        if (!in_array($files['file_ext'], $this->extensions)) {
            output_to_json($this, array(
                    "mtype" => "error" ,
                    "message" => $this->lang->line('uploadExtensionsLike')
           ));
        }

        
        
        //Get Event ID and Event schedule id in excel
        $cells = $this->readExcelHeaders($fileName);

        //Import excel file
        $this->import(
            $cells['eventId'][0],
            $cells['eventScheduleId'][0],
            $fileName,
            $files['file_name']
        );
        

        //End of import :)
        exit();
    }

    /**
     * Function to delete uploaded excel file after values inserted to database
     * @param  string|null $file = uploaded excel file
     * @return void
     * access private member
     */
    private function unlinkExcelFile($file)
    {
        if (file_exists($file)) {
            unlink($file);
        }
    }

    /**
     *  Import/Read and save to database no show data excel values
     *
     * @param integer $eventId = event ID
     * @param integer $eventScheduleId = Event schedule ID
     * @param string $fileName  = filename of excel file
     * @return void
     * access private member
     */
    private function import(int $eventId, int $eventScheduleId, string $fileName, ?string $ExcelfileLocation)
    {
        //Check if event ID, Event schedule id and filename are not set
        if (is_null($eventId) && is_null($eventScheduleId) && is_null($fileName)) {
            return false;
        }

        //Iterates list of datas
        foreach ($this->readExcelRowsAndColums($fileName) as $index => $cellValues) {
            $PrimaryKeys = $this->primaryKeys((int)$cellValues[0]);
            $Email = $this->fields((string)$cellValues[1]);
            $Type = $this->type((string)$cellValues[2]);
            $Gender = $this->fields((string)$cellValues[3]);
            $LastName = $this->fields((string)$cellValues[4]);
            $FirstName = $this->fields((string)$cellValues[5]);
            $Booking = $this->bookingAndWaiting((int)$cellValues[6]);
            $Waiting = $this->bookingAndWaiting((int)$cellValues[7]);
            $TotalPresent = $this->isNumericNoShow((int)$cellValues[8]);
            $Comments = $this->comments((string)$cellValues[9]);
            $UserIds = $this->checkUserId((int)$cellValues[12]);

            //Store list of fields for filtering and search keys
            $lists = [
                'EventId'         => $eventId,
                'EventScheduleId' => $eventScheduleId,
                'PrimaryKeys'     => $PrimaryKeys,
                'Type'            => $Type,
                'Gender'          => $Gender,
                'LastName'        => $LastName,
                'FirstName'       => $FirstName,
                'Email'           => $Email,
                'Booking'         => $Booking,
                'Waiting'         => $Waiting,
                'TotalPresent'    => $TotalPresent,
                'Comments'        => $Comments,
                'UserIds'         => $UserIds,
            ];

            $this->bookingData[] = $this->storeAndFilterExcelDataValues('Réservation', $lists);
            $this->waitingData[] = $this->storeAndFilterExcelDataValues('Attente', $lists);
            $this->extraData[]   = $this->storeAndFilterExcelDataValues('Extra', $lists);
        }
        
        //validate first datasets for possible errors
        $response = $this->validate(
            array_filter($this->bookingData),
            array_filter($this->waitingData),
            array_filter($this->extraData)
        );

        //Set error variable handler
        $hasError = count(array_filter($response)) > 0 ? true : false;

        if (!$hasError) {

            //For secure insert we need to delete first the records if exist for table
            //"event_attendance_walk_in" and "event_attendance_noshow"
            $this->nshow_model->deleteRecordIfExist($eventId, $eventScheduleId);
            
            //Then save Data to database if "No error found"
            $this->save(
                array_filter($this->bookingData),
                array_filter($this->waitingData),
                array_filter($this->extraData)
            );

             //delete uploaded exel file
            $this->unlinkExcelFile($this->folder.$ExcelfileLocation);

            //Display success message
            output_to_json($this, array(
                "mtype" => "success",
                "message" => $this->lang->line("importSuccessMessage"),
                "filename" => $fileName
            ));
        } else {

            //Error display which subscriber failed.
            output_to_json($this, [
                "mtype" => "error",
                "subscriber" =>  array_filter($response)
            ]);
        }
    }

    /**
     * Store and sum "Total Present" for extra peoples
     *
     * @param array $extra
     * @return void
     * access private member
     */
    private function storeExtraToEventAttendanceNoShowTable(array $extra) : void
    {
        //Holds grand total of extra peoples
        $TotalExtraCount = 0;

        //Holds event ID
        $EventId = 0;

        //Holds Event schedule ID
        $EventScheduleId = 0;
        
        foreach ($extra as $k => $extraPeoples) {
            $EventId = $extraPeoples['EventId'];
            $EventScheduleId = $extraPeoples['EventScheduleId'];
            $TotalExtraCount += $extraPeoples['TotalPresent'];
        }

        $this->nshow_model->saveEventAttendanceNoShow([
            'event_id'          => $EventId,
            'event_schedule_id' => $EventScheduleId,
            'user_id'           => null,
            'booking_expected'  => null,
            'waiting_expected'  => null,
            'attendance'        => $TotalExtraCount,
            'comment'           => null,
        ]);
    }

    /**
     *  Store all booking details in "event_attendance_noshow"
     *
     * @param array $booking
     * @return void
     * access private member
     */
    private function storeBookingToEventAttendanceNoShowTable(array $booking) : void
    {
        foreach ($booking as $k => $bookingLists) {
            $this->nshow_model->saveEventAttendanceNoShow([
                  'event_id' =>  $bookingLists['EventId'],
                  'event_schedule_id' => $bookingLists['EventScheduleId'],
                  'user_id' => $bookingLists['UserIds'],
                  'booking_expected' => $bookingLists['Booking'],
                  'waiting_expected' => null,
                  'attendance' => $bookingLists['TotalPresent'],
                  'comment' => $bookingLists['Comments'],
           ]);
        }
    }

    /**
     * Store all waiting details in "event_attendance_noshow"
     *
     * @param array $booking
     * @return void
     * access private member
     */
    private function storeWaitingToEventAttendanceNoShowTable(array $waiting) : void
    {
        foreach ($waiting as $k => $waitingLists) {
            $this->nshow_model->saveEventAttendanceNoShow([
                  'event_id' =>  $waitingLists['EventId'],
                  'event_schedule_id' => $waitingLists['EventScheduleId'],
                  'user_id' => $waitingLists['UserIds'],
                  'booking_expected' => null,
                  'waiting_expected' => $waitingLists['Waiting'],
                  'attendance' => $waitingLists['TotalPresent'],
                  'comment' => $waitingLists['Comments'],
           ]);
        }
    }
    
    /**
     * Save booking , Waiting, Extra peoples list(s)
     * Booking List(s) = 1
     * Waitign list(s) = 0
     *
     * @param array $booking = All booking lists
     * @param array $waiting = All waiting lists
     * @param array $extra   = All new extra peoples
     * @return void
     * access private member
     */
    private function save(array $booking, array $waiting, array $extra)
    {

        //insert to "event_attendance_noshow" booking details
        $this->storeBookingToEventAttendanceNoShowTable($booking);

        //insert to "event_attendance_noshow" waiting details
        $this->storeWaitingToEventAttendanceNoShowTable($waiting);

        //insert to "event_attendance_noshow" the total extra people count
        $this->storeExtraToEventAttendanceNoShowTable($extra);
        
        //Get the author or uploader
        $author = $this->getAuthor();

        //Add or Update Confirmed/Booking = 1
        foreach ($booking as $k => $bookingLists) {
            $this->nshow_model->saveBookAndWait(
                self::Addedbooking,
                $author,
                $bookingLists['PrimaryKeys'],
                $bookingLists['TotalPresent'],
                $bookingLists['Comments'],
                self::saveTypeExcel
            );
        }

        //Update or insert Waitlists = 0
        foreach ($waiting as $k => $waitingLists) {
            $this->nshow_model->saveBookAndWait(
                self::AddedWaiting,
                $author,
                $waitingLists['PrimaryKeys'],
                $waitingLists['TotalPresent'],
                $waitingLists['Comments'],
                self::saveTypeExcel
            );
        }

        //Insert Extra people(s)
        foreach ($extra as $k => $extraPeoples) {
            $this->nshow_model->AddNewExtraPeople(
                $extraPeoples['EventId'],
                $extraPeoples['EventScheduleId'],
                $extraPeoples['Gender'],
                $extraPeoples['FirstName'],
                $extraPeoples['LastName'],
                $extraPeoples['Email'],
                $extraPeoples['Comments'],
                $extraPeoples['TotalPresent']
            );
        }
    }
}