<?php 
/*
 * @Author: Jino Lacson
 * @Date: 2018-10-25 10:20:42
 * @Last Modified by: jlacson@wylog.com
 * @Last Modified time: 2019-02-14 10:29:12
 */

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

use app\core\utils\Response;

//require phpoffice library(Spreadsheet and Writer)
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

//require excel styles
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

//require excel protection for lock/unlock
use PhpOffice\PhpSpreadsheet\Style\Protection;

//require excel contitions
use PhpOffice\PhpSpreadsheet\Style\Conditional;

//require excel datavalidations
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

class Noshow_export_controller extends MY_Controller
{
    /**
     * php excel object instance variables
     *
     * @var string
     */
    private $spreadsheet;

    /**
     * no show excel file tabs
     *
     * @var string
     */
    private $reports;

    /**
     * instance variable for PhpOffice writer class
     *
     * @var string
     */
    private $writer;

    /**
     * variable that set iteration to row 2 which is the datasets not headers
     *
     * @var string
     */
    private $dataset;

    /**
     * instance variable for activesheet
     *
     * @var string
     */
    private $sheets;

    /**
     * variable default font size for excel exports
     *
     * @var string
     */
    private $default_font;

    /**
     * variable for default background color
     *
     * @var string
     */
    private $default_background_color;

    /**
     * Tab title
     *
     * @var string
     */
    private $sheetTitle;

    /**
     * Holds default maximum row
     *
     * @var int
     */
    private $defaultMaximumRow;

    /**
     * Holds gray color variable
     *
     * @var string
     */
    private $setGrayColor;
    
    /**
     * EXCEL PASSWORD PROTECTION THIS IS CONFIDENTIAL
     */
    private const PASSWORD = '1@#]00YHkBlackHatzZ';

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

        //load nshow_model
        $this->load->model('nshow_model');

        // Create new PHPExcel object
        $this->spreadsheet = new Spreadsheet();

        //get active sheet
        $this->sheets = $this->spreadsheet->getActiveSheet();

        //create writer object
        $this->writer = new Xlsx($this->spreadsheet);

        //tab title
        $this->sheetTitle = "No_Show";
        
        //no show excel tabs pagination
        $this->reports = "data_fields";

        //start iteration in row 6 dataset exclude row 1 - 5(which is the excel main pane headers)
        $this->dataset = 6;

        //set default excel font size which is 8
        $this->default_font = 8;

        //Default maximum row
        $this->defaultMaximumRow = 1000;
        
        //Default maximum row
        $this->booklist_end_row = 0;

        //set default background color
        $this->default_background_color = '428bcb';

        //set gray color
        $this->setGrayColor = 'D3D3D3';
    }

    /**
     * Set execution and memory limit
     *
     * @return void
     * access public
     */
    public function setMemoryLimit()
    {
        ini_set('memory_limit', '1024M');
        ini_set('max_execution_time', (60*3));
    }

    /**
     * Set document properties
     *
     * @return void
     * access public
     */
    public function setDocumentProperties()
    {
        return $this->spreadsheet->getProperties()->setCreator("Company For Madagascar Event Management System")
         ->setLastModifiedBy("Company For Madagascar Event Management System")
         ->setTitle("Office 2007 XLSX Document")
         ->setSubject("Office 2007 XLSX  Document")
         ->setDescription("No show export import file")
         ->setKeywords("office 2007 openxml php")
         ->setCategory("Export file");
    }

    /**
     * Set headers(pragma, cache control , cookies etc..)
     *
     * @param string $filename
     * @return void
     */
    public function setHeaders(string $filename)
    {
        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.ms-excel; charset=utf-8');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
        header('Cache-Control: max-age=0');

        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $this->writer->save('php://output');
        exit();
    }

    /**
     * Auto size columns for each worksheet
     *
     * @return void
     * access public
     */
    public function setExcelAutoSize()
    {
        foreach ($this->spreadsheet->getWorksheetIterator() as $worksheet) {
            $this->spreadsheet->setActiveSheetIndex($this->spreadsheet->getIndex($worksheet));

            $sheet = $this->sheets;
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);
            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
            }
        }
    }
    /**
     * set excel main headers (first rows 1 - 5 with freeze pane)
     *
     * @param integer $eventID
     * @param integer $eventScheduleID
     * @param string $event_title
     * @return void
     * access private member
     */
    private function setExcelHeaders(int $eventID, int $eventScheduleID, string $event_title)
    {
        /**
         * Merge some header cells
         */
        $this->sheets->mergeCells('B1:E1');
        $this->sheets->mergeCells('B2:D2');
        $this->sheets->mergeCells('B3:D3');
        $this->sheets->mergeCells('B4:E4');
        
        //Set event_id in A3 cell
        $this->sheets->setCellValue('A2', $eventID);

        //Set event_schedule_id in A3 cell
        $this->sheets->setCellValue('A3', $eventScheduleID);

        //Count total subscribers
        $this->sheets->setCellValue('A4', '=COUNT(A6:A'.$this->defaultMaximumRow.')');

        $this->sheets->setCellValueByColumnAndRow(2, 2, "Nombre Total de places offertes");
        $this->sheets->setCellValue('E2', $this->nshow_model->getTotalAvailableAndRemainingPlaces($eventID, $eventScheduleID)['total_available_seat']);
       
        $this->sheets->setCellValueByColumnAndRow(2, 3, "Nombre de place restantes");
        $this->sheets->setCellValue('E3', "=E2-(SUM(I{$this->dataset}:I{$this->defaultMaximumRow}))");

        $this->sheets->setCellValueByColumnAndRow(2, 4, $event_title);
        
        $this->sheets->setCellValue('F2', "Nombre total d'abonnés");
        $this->sheets->setCellValue('F3', "Nombre total d'invités");
        $this->sheets->setCellValue('F4', "Nombre total de personnes");

        $this->sheets->setCellValue('G1', 'Attendus');
        //$sheet->setCellValueExplicit('F2', '=sum(F3:F4)',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA);
        $this->sheets->setCellValue('G2', '=COUNT(G6:G'.$this->defaultMaximumRow.')');
        $this->sheets->setCellValue('G3', '=G4-G2');
        $this->sheets->setCellValue('G4', '=SUM(G6:G'.$this->defaultMaximumRow.')');

        $this->sheets->setCellValue('H1', 'Liste d\'attente');
        $this->sheets->setCellValue('H2', '=COUNT(H6:H'.$this->defaultMaximumRow.')');
        $this->sheets->setCellValue('H3', '=H4-H2');
        $this->sheets->setCellValue('H4', '=SUM(H6:H'.$this->defaultMaximumRow.')');

        $this->sheets->setCellValue('I1', 'Présents');
        $this->sheets->setCellValue('I2', '=(COUNTIF(I6:I'.$this->booklist_end_row.',">0"))');
        $this->sheets->setCellValue('I3', '=SUM(N6:N'.$this->booklist_end_row.')');
        $this->sheets->setCellValue('I4', '=I2+I3');

        $this->sheets->setCellValue('J1', 'Absents');
        $this->sheets->setCellValue('J2', '=G2-I2');
        $this->sheets->setCellValue('J3', '=G3-I3');
        $this->sheets->setCellValue('J4', '=J3+J2');

        $this->sheets->setCellValue('K1', 'Nbre de No Show Complet :');
        $this->sheets->setCellValue('L1', '=SUM(K6:K'.$this->booklist_end_row.')');

        $this->sheets->setCellValue('K2', 'Nbre de No Show Partiel :');
        $this->sheets->setCellValue('L2', '=SUM(L6:L'.$this->booklist_end_row.')');

        $this->sheets->setCellValue('K4', 'Noshow :');
        $this->sheets->setCellValue('L4', '=J3+J2&"/"&SUM(G6:G'.$this->defaultMaximumRow.')');

        //set second headers for dataset title
        $this->sheets->setCellValue('A5', 'ID unique');
        $this->sheets->setCellValue('B5', 'Adresse e-mail');
        $this->sheets->setCellValue('C5', 'Type');
        $this->sheets->setCellValue('D5', 'Civilité');
        $this->sheets->setCellValue('E5', 'Nom de famille');
        $this->sheets->setCellValue('F5', 'Prénom');
        $this->sheets->setCellValue('G5', 'Nombre de places réservées');
        $this->sheets->mergeCells('G5:H5');
        $this->sheets->setCellValue('I5', 'Présence Total');
        $this->sheets->setCellValue('J5', 'Commentaires');
        $this->sheets->setCellValue('K5', 'No Show');
        $this->sheets->setCellValue('L5', 'No Show Partiel');
        $this->sheets->setCellValue('M5', 'User Id');
        $this->sheets->setCellValue('N5', 'Présence Invité(s)');
        

        //set font styles for header totals
        $this->setTotalsStyle();

        //set cell alignments
        $this->setCellAlignMent(
            ["A1","A4","D4","A5","B5","C5","D5","E5","F5","G5","H5","I5","J5","K5","L5","M5","N5"],
            ['alignment' => [
                                'horizontal' => Alignment::HORIZONTAL_CENTER
            ],
            'fill' => [
                'fillType' => Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startColor' => [
                    'argb' => $this->default_background_color,
                ],
                'endColor' => [
                    'argb' => $this->default_background_color,
                ],
            ],
            'font' => [
                'size' => $this->default_font,
                'bold' => false
            ]
            ]
        );

        //set color for headers then freeze
        $this->fillHeaderColorsAndFreeze();
    }
    /**
     * add text alignments for headers
     *
     * @return void
     * access private member
     */
    private function setCellAlignMent(array $ranges_alignment, array $property)
    {
        if (count($ranges_alignment) == 0 && count($property) == 0) {
            return false;
        }

        foreach ($ranges_alignment as $index => $rows) {
            $this->sheets->getStyle($rows)->applyFromArray($property)
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN);
        }
    }
    /**
     * Set font styles(bold or without bold formatting) for its header totals
     * - Nombre Total de places offertes
     * - Nombre de place restantes
     * - Nombre total d'abonnés
     * - Nombre total d'invités
     * - Nombre total de personnes
     * - Attendus
     * - Waiting List
     * - Présents
     * - Absents
     * - Nbre de No Show Complet :
     * - Nbre de No Show Partiel :
     *
     * @return void
     * access private
     */
    private function setTotalsStyle()
    {
        //These ranges should be in bold format
        $ranges_bold = ["E2","E3","F2","F3","F4","G4","H4","I4","J4","K1","K2","L1","L2"];

        //list of ranges to apply font and or bold formating
        $ranges_list = ["E2","E3","F2","F3","F4","G2","G3","G4","H2","H3","H4","I2","I3","I4","J1","J2","J3","J4","K1","K2","L1","L2"];

        $with_bold = array(
                'font' => [
                    'size' => $this->default_font,
                    'bold' => true
                ]
        );

        $without_bold = array(
            'font' => [
                'size' => $this->default_font,
                'bold' => false
            ]
        );

        foreach ($ranges_list as $index => $rows) {
            $this->sheets->getStyle($rows)->applyFromArray((in_array($rows, $ranges_bold) ? $with_bold : $without_bold))
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN);
        }
    }
    /**
     * Fill main header colors and freeze pane
     *
     * @return void
     * access private member
     */
    private function fillHeaderColorsAndFreeze()
    {
        //freeze pane from A5 to L5
        $this->sheets->freezePane('L6');

        //set of cells to be filled with colors, fonts and other attributes
        $ranges_color = ['A1:J1','A2:B2','A3:B3','A4:B4','C2','C3','C4','E4', "F2", "F3", "F4", "K1" ,"K2", "K3" , "K4",
        "L3" , "L4" ,'M1','M2','M3','M4','N1','N2','N3','N4'];

        $color = array(
            'fill' => [
                'fillType' => Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startColor' => [
                    'argb' => $this->default_background_color,
                ],
                'endColor' => [
                    'argb' => $this->default_background_color,
                ],
            ],
            'font' => [
                'size' => $this->default_font,
                'bold' => true
            ]
            );
            
        //add color to main header
        foreach ($ranges_color as $range_cells) {
            $this->sheets->getStyle($range_cells)->applyFromArray($color)
                      ->getBorders()
                      ->getAllBorders()
                      ->setBorderStyle(Border::BORDER_THIN);
        }

        //create freeze pane header for 5 columns
        for ($row_collapse = 1; $row_collapse <= 4; ++$row_collapse) {
            $this->sheets->getRowDimension($row_collapse)
                        ->setOutlineLevel(1)
                        ->setVisible(true)
                        ->setCollapsed(true);
        }
    }
    /**
     * Add protections to cell
     *
     * @return void
     * access private member
     */
    private function setUnProtectedCells($ranges)
    {
        if (is_null($ranges)) {
            return false;
        }

        $this->sheets
            ->getStyle($ranges)
            ->getProtection()
            ->setLocked(Protection::PROTECTION_UNPROTECTED);
        
        $this->sheets
            ->getProtection()
            ->setSheet(true);

        //disable sorting
        $this->sheets->getProtection()->setSort(true);

        //disable inserting new rows
        $this->sheets->getProtection()->setInsertRows(true);

        //hide format cells
        $this->sheets->getProtection()->setFormatCells(true);

        //This line will add password protection for no show excel file
        $this->sheets->getProtection()->setPassword(self::PASSWORD);
    }

    /**
     * Set row or coloumn visibility
     *
     * @param string $range = cell range(s)
     * @param string $type = row or col
     * @param boolean $visibility = true or false
     * @return void
     * access private member
     */
    private function rowColumnVisibility(string $range, string $type, bool $visibility)
    {
        if (is_null($range) && is_null($type) && is_null($visibility)) {
            return false;
        }

        if ($type == 'row') {
            $this->sheets->getRowDimension($range)->setVisible($visibility);
        }

        if ($type == 'col') {
            $this->sheets->getColumnDimension($range)->setVisible($visibility);
        }
    }

    /**
     * apply styles for report data main row(s)
     *
     * @param string $cellStylesRangeIndex
     * @param array  $styles
     * @return void
     * access private member
     */
    private function setReportDataCellsStyles($cellStylesRangeIndex, array $styles)
    {
        $this->sheets
        ->getStyle($cellStylesRangeIndex)
        ->applyFromArray($styles)
        ->getBorders()
        ->getAllBorders()
        ->setBorderStyle(Border::BORDER_THIN);
    }

    /**
     * Add grey color to cells
     *
     * @return void
     * access private member
     */
    private function setGrayColor()
    {

        //Fill light gray color in cells
        for ($datasets=6; $datasets < $this->defaultMaximumRow ; $datasets++) {
            $this->grayColorRanges('A'.$datasets.':H'.$datasets, false);
            $this->grayColorRanges('K'.$datasets.':N'.$datasets, false);
        }

        //Fill light gray color in header
        $this->grayColorRanges([
            "E2:E3",
            "G2:J2",
            "G3:J3",
            "G2:J2",
            "G4:J4",
            "L1:L2"
        ], true);
    }

    /**
     *  Gray color properties
     *
     * @param [type] $ranges_cells = range cells
     * @param boolean $isArray = true or false
     * @return void
     * access private member
     */
    private function grayColorRanges($ranges_cells = null, bool $isArray)
    {
        $grayColor = array(
            'fill' => [
                'fillType' => Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startColor' => [
                    'argb' => $this->setGrayColor,
                ],
                'endColor' => [
                    'argb' => $this->setGrayColor,
                ],
            ]
        );

        if ($isArray) {
            foreach ($ranges_cells as $ranges) {
                $this->sheets->getStyle($ranges)->applyFromArray($grayColor)
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(Border::BORDER_THIN);
            }
        }

        if (!$isArray) {
            $this->sheets->getStyle($ranges_cells)->applyFromArray($grayColor)
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN);
        }
    }

    /**
     * Add style for 1000 rows from cell range A5:N5 down to A1000:N1000
     *
     * @return void
     * access private
     */
    private function setDefaultStyleRows($rowSets = 5)
    {
        //add gray color to cells
        $this->setGrayColor();

        for ($datasets=$rowSets; $datasets < $this->defaultMaximumRow ; $datasets++) {

            //Add styles for A - N
            $this->setReportDataCellsStyles('A'.$datasets.':N'.$datasets, ['font' => ['size' => $this->default_font]]);

            //Add validation colors for cells K and L
            $this->addCellByColorConditions('K'.($datasets + 1).'', 'FF0000');
            $this->addCellByColorConditions('L'.($datasets + 1).'', 'FFC000');
        }
    }

    /**
     * Create drop down "Walk in" and drop down for "Monsieur/Madame"
     *
     * @param [type] $datasets = range cells
     * @return void
     * access private member
     */
    private function setDropDowns($datasets)
    {
        //loop cell range by 20 persons start at row 6
        for ($i=$datasets; $i < $this->defaultMaximumRow; $i++) {

         //Pick only "Extra" or "extra" from dropdown list(s)
            $this->setCellValidationDropDown('C'.$i, 'Seule la valeur "extra" est autorisée', '"Extra"');

            //Pick only "Extra" or "extra" from dropdown list(s)
            $this->setCellValidationDropDown('D'.$i, 'Seules les valeurs "Monsieur" ou "Madame" sont autorisées', '"Monsieur,Madame"');
        }
    }
    /**
     * Add cell color by its value green if 1 or red if zero
     *
     * @param string $cell
     * @param string $color
     * @return void
     * access private member
     */
    private function addCellByColorConditions(string $cell, string $color)
    {
        if (is_null($cell)) {
            return false;
        }

        $conditions = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
        $conditions->setConditionType(Conditional::CONDITION_CELLIS);
        $conditions->setOperatorType(Conditional::OPERATOR_GREATERTHANOREQUAL);
        $conditions->addCondition('1');
        $conditions->getStyle()->applyFromArray(['fill' => [
            'fillType' => Fill::FILL_GRADIENT_LINEAR,
            'rotation' => 90,
            'startColor' => [
                'argb' => $color,
            ],
            'endColor' => [
                'argb' => $color,
            ],
        ]])->getFont()->getColor()->setARGB($color);

        $conditionalStyles = $this->sheets->getStyle($cell)->getConditionalStyles();
        $conditionalStyles[] = $conditions;
        $this->sheets->getStyle($cell)->setConditionalStyles($conditionalStyles);
    }
    /**
     * Add validations for "No Show and No show Partial"
     *
     * @param string $cell
     * @param integer $min
     * @param integer $max
     * @return void
     * access private member
     */
    private function setCellValidationsWithPrompt(string $cell, int $min, int $max)
    {
        $validation = $this->sheets->getCell($cell)->getDataValidation();
        $validation->setType(DataValidation::TYPE_WHOLE);
        $validation->setErrorStyle(DataValidation::STYLE_STOP);
        $validation->setAllowBlank(true);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setErrorTitle('Erreur de saisie.');
        $validation->setError('Valeur incorrect!');
        $validation->setPromptTitle('Rappel');
        $validation->setPrompt('Seules les valeurs numériques sont autorisées.');
        $validation->setFormula1($min);
        $validation->setFormula2($max);
    }
    /**
     * Allow only to pick from list "Extra" or "extra" people
     * Allow only to pick from list "Monseur" or "Madam" gender
     *
     * @param string $cell = cell range
     * @param string $prompt = Validation prompt message
     * @param string $formula = dropdown list(s)
     * @return void
     * access private member
     */
    private function setCellValidationDropDown(string $cell, string $prompt, string $formula)
    {
        $validation = $this->sheets->getCell($cell)->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_STOP);
        $validation->setAllowBlank(true);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Erreur de saisie.');
        $validation->setError('Valeur inexistante dans la liste.');
        $validation->setPromptTitle('Sélectionnez une valeur dans la liste.');
        $validation->setPrompt($prompt);
        $validation->setFormula1($formula);
    }

    /**
     * get data for export = confirmed,waitlist,extra_people
     *
     * @param int $event_schedule_id
     * @return array
     * access public
     */
    private function getExportData($event_schedule_id) : array
    {
        $confirmed = $this->nshow_model->bookList($event_schedule_id);
        $this->booklist_end_row = (count($confirmed)+($this->dataset)-1);
        $waitlist = $this->nshow_model->waitList($event_schedule_id);
        $extra = $this->nshow_model->extraPeople($event_schedule_id);

        return array_merge($confirmed, $waitlist, $extra);
    }

    /**
     *  set No show "filename title" or "Tab" for download
     *
     * @param integer $event_schedule_id
     * @param string $titleOrTab
     * @return string
     * access private member
     */
    private function setNoShowFileNameTitle(int $event_schedule_id, string $titleOrTab) : string
    {
        $data  = "";
        $title = "";
        $event_id = "";
    
        $event = $this->nshow_model->getEventScheduleDetails($event_schedule_id);
        $event_id = $event["event_id"] ?? "";
        $date = $event["date_month"] ?? "";
        $title = $event["title"] ?? "";
        $start_date_time_hour = $event["start_date_time_hour"] ?? "";
        
        if ($titleOrTab == 'title') {
            return $title.$start_date_time_hour;
        }

        if ($titleOrTab == 'tab') {
            return $this->sheetTitle."_".$event_id."_".$event_schedule_id;
        }
    }

    /**
     * Check if type is "booking or withdrawn"
     *
     * @param string $type
     * @return boolean
     * access private member
     */
    private function isBooking(string $type) : bool
    {
        if (is_null($type)) {
            return false;
        }

        return ((trim(strtolower($type)) == 'booking' || trim(strtolower($type)) == 'withdrawn'));
    }

    /**
     * Check if type is "waiting or cancelled"
     *
     * @param string $type
     * @return boolean
     * access private member
     */
    private function isWaiting(string $type) : bool
    {
        if (is_null($type)) {
            return false;
        }

        return ((trim(strtolower($type)) == 'waiting' || trim(strtolower($type)) == 'cancelled'));
    }

    /**
     * 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));
    }

    /**
     * Hide cell range by col or rows
     *
     * @param array $range
     * @param string $axis
     * @param boolean $isShow
     * @return void
     * access private member
     */
    private function hideCells(array $range, string $axis, bool $isShow) : void
    {
        foreach ($range as $key => $cells) {
            $this->rowColumnVisibility($cells, strtolower($axis), $isShow);
        }
    }

    /**
     * Set default formated cells by 20 rows for extra peoples
     * If no datasets for Booking,Waiting are empty
     *
     * @param integer $persons = number of rows to iterate
     * @return void
     * access private member
     */
    private function setDefaultCellRange(int $persons) : void
    {
        //loop cell range by 20 persons start at row 6
        for ($i=$this->dataset; $i <= $persons; $i++) {
            
            //Unlock cells for new extra people(s) = start unlock at cell (6)
            $this->setUnProtectedCells('B'.($i).':F'.$this->defaultMaximumRow);
            $this->setUnProtectedCells('I'.($i).':J'.$this->defaultMaximumRow);
            //End unlock extra people(s)

            //set drop downs for 20 extra peoples
            $this->setDropDowns(($i));

            /**
             * 'Presence Total' and 'Commentaries' editable
             *  Set Excel file Password protection
             */
            $this->setUnProtectedCells('I'.$i.':J'.$i);

            //Basic datasets
            $this->sheets->setCellValue('A'.$i, '');
            $this->sheets->setCellValue('B'.$i, '');
            $this->sheets->setCellValue('C'.$i, '');
            $this->sheets->setCellValue('D'.$i, '');
            $this->sheets->setCellValue('E'.$i, '');
            $this->sheets->setCellValue('F'.$i, '');

            //Booking/withdrawn
            $this->sheets->setCellValue('G'.$i, '');

            //Waiting/cancelled
            $this->sheets->setCellValue('H'.$i, '');

            //Total present(s)
            $this->sheets->setCellValue('I'.$i, '');

            //Comment
            $this->sheets->setCellValue('J'.$i, '');

            //No show/No Show partial
            //$this->sheets->setCellValue('K'.$i, '=IF(I'.$i.'=0,1,"")');
            //$this->sheets->setCellValue('L'.$i, '=IF(I'.$i.'>0,IF(I'.$i.'<G'.$i.',1,""),"")');

            //User IDS
            $this->sheets->setCellValue('M'.$i, '');

            //Invitees
            $this->sheets->setCellValue('N'.$i, '=IF(I'.$i.'<>0,I'.$i.'-1,0)');
        }
    }
    /**
     * Main no show excel data values
     *
     * @param array $options
     * @return void
     * access private member
     */
    private function reportData(array $datasets)
    {
        //start lock(s) at row 6
        $registrationIndexStartAt = $this->dataset;

        //Count total subscribers
        $totalData = count($datasets);
         
        /**
         * Hide coloumn A(Registration ID)
         * Hide column M(User IDs)
         * Hide column N(Invitees)
         *
         */
        $this->hideCells(["A","M","N"], "col", false);

        //if $datasets is empty then set default cells to input 20 extra peoples
        if (count($datasets) == 0) {
            $this->setDefaultCellRange(20);
        }

        //Display list of booking,waiting, extra, data rows and columns
        foreach ($datasets as $index => $data) {

            //Unlock cells for new extra people(s) = start unlock at cell (6 + total subscribers)
            $this->setUnProtectedCells('B'.($registrationIndexStartAt + $totalData).':F'.$this->defaultMaximumRow);
            $this->setUnProtectedCells('I'.($registrationIndexStartAt + $totalData).':J'.$this->defaultMaximumRow);
            //End unlock extra people(s)

            //set drop downs
            $this->setDropDowns(($registrationIndexStartAt + $totalData));
            
            //set unprotected ('Presence Total' and 'Commentaries' only are both editable other cells are disabled)
            //Also set Excel file Password protection
            $this->setUnProtectedCells('I'.$registrationIndexStartAt++.':J'.$registrationIndexStartAt++);

            //Basic datasets
            $this->sheets->setCellValue('A'.$this->dataset, $data->registration_id);
            $this->sheets->setCellValue('D'.$this->dataset, $data->civility);
            $this->sheets->setCellValue('E'.$this->dataset, strtoupper($data->last_name));
            $this->sheets->setCellValue('F'.$this->dataset, $data->first_name);
            $this->sheets->setCellValue('B'.$this->dataset, 'N/A');
            // $this->sheets->setCellValue('B'.$this->dataset, ($data->email_address == "Unknown") ? 'N/A':$data->email_address);

            //Booking/withdrawn
            if ($this->isBooking($data->type)) {
                $this->sheets->setCellValue('C'.$this->dataset, "Réservation");
                $this->sheets->setCellValue('G'.$this->dataset, (($data->no_of_place == 0) ? "" : $data->no_of_place));

                //Add validations for cells "Total present"
                $this->setCellValidationsWithPrompt('I'.$this->dataset, 0, (int)$data->no_of_place);
            }

            //Waiting/cancelled
            if ($this->isWaiting($data->type)) {
                $this->sheets->setCellValue('C'.$this->dataset, "Attente");
                $this->sheets->setCellValue('H'.$this->dataset, (($data->no_of_place == 0) ? "" : $data->no_of_place));

                //Add validations for cells "Total present"
                $this->setCellValidationsWithPrompt('I'.$this->dataset, 0, (int)$data->no_of_place);
            }

            //Walk in/Extra
            if ($this->isExtra($data->type)) {
                $this->sheets->setCellValue('C'.$this->dataset, 'Extra');
            }

            //Total present(s)
            $this->sheets->setCellValue('I'.$this->dataset, (($data->attendance == 0) ? 0 : $data->attendance));

            //Comment
            $this->sheets->setCellValue('J'.$this->dataset, (($data->comment == "") ? "" : $data->comment));

            //No show/No Show partial
            $this->sheets->setCellValue('K'.$this->dataset, '=IF(I'.$this->dataset.'=0,1,0)');
            $this->sheets->setCellValue('L'.$this->dataset, '=IF(I'.$this->dataset.'>0,IF(I'.$this->dataset.'<G'.$this->dataset.',1,0),0)');
            
            //User IDS
            $this->sheets->setCellValue('M'.$this->dataset, $data->user_id);

            //Invitees
            $this->sheets->setCellValue('N'.$this->dataset, '=IF(I'.$this->dataset.'<>0,I'.$this->dataset.'-1,0)');

            //append datasets starting from row 6 downwards
            $this->dataset++;
        }

        //Set default styles for A5:L5 down to A1000:L1000
        $this->setDefaultStyleRows(5);
    }
    /**
     * Export no show file with title+date
     *
     * @param integer $eventID
     * @param integer $eventScheduleID
     * @return void
     * access private member
     */
    public function export(int $eventID, int $eventScheduleID)
    {
        //This should be match for general export validation
        if (is_null($eventID) && is_null($eventScheduleID)) {
            return false;
        }

        //call memory limit
        $this->setMemoryLimit();

        //call document properties
        $this->setDocumentProperties();

        //get data for populate
        $options = $this->getExportData($eventScheduleID);

        //set php excel first 5 row(s) with freeze header
        $this->setExcelHeaders($eventID, $eventScheduleID, $this->setNoShowFileNameTitle($eventScheduleID, "title"));

        //Add no show excel data
        $this->reportData($options);
       
        //Call auto size function columns for active worksheet
        $this->setExcelAutoSize();

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $this->spreadsheet->setActiveSheetIndex(0)->setTitle($this->setNoShowFileNameTitle($eventScheduleID, "tab"));

        //call headers and download file
        $this->setHeaders($this->setNoShowFileNameTitle($eventScheduleID, "title"));
    }
}