<?php

use Phalcon\Mvc\Model\Transaction\Failed as TxFailed;
use Carbon\Carbon;

class ExcelService
{
    private $options;
    private $document;
    private $spreadsheet;
    private $type;
    private $errorMessage;

    public function __construct($document, $type)
    {
        $this->document = $document;
        $this->type = $type;
        $this->errorMessage = [];

        $this->GetTypeOption();
        $this->InitializeDocument();
    }

    #region Setup Method
    private function InitializeDocument()
    {
        if (count($this->options) > 0) {
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($this->document->getTempName());
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(false);

            if (array_key_exists('active_sheet', $this->options)) {
                if (count($this->options['active_sheet']) > 0) {
                    $reader->setLoadSheetsOnly($this->options['active_sheet']);
                }
            }

            $this->spreadsheet = $reader->load($this->document->getTempName());
        }
    }
    #endregion

    #region Action Method
    public function RetrieveExcelData()
    {
        $result = [];

        if (count($this->options) > 0) {
            foreach ($this->options['active_sheet'] as $sheetName) {
                $this->spreadsheet->setActiveSheetIndexByName($sheetName);
                $worksheet = $this->spreadsheet->getActiveSheet();

                //Additional Variable
                $countHeader = 0;
                $passedHeader = [];
                $countRow = 1;

                foreach ($worksheet->getRowIterator() as $row) {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    // echo('Row' . $countRow);

                    $rowValue = [];
                    $countColumn = 1;
                    foreach ($cellIterator as $cell) {
                        // echo('Column' . $countColumn);
                        if ($countRow < $this->options['data_start']) {
                            //Access Header
                            if (array_key_exists($cell->getCoordinate(), $this->options['expected_header'])) {
                                //Register Header if not empty
                                if (!empty(trim($cell->getFormattedValue(), " "))) {
                                    $passedHeader[$cell->getCoordinate()] = trim($cell->getFormattedValue(), " ");

                                    //Validate Header
                                    if (trim($cell->getFormattedValue(), " ") == $this->options['expected_header'][$cell->getCoordinate()]) {
                                        $countHeader++;
                                    } else {
                                        $this->errorMessage[] = 'Header not valid at [' . $cell->getCoordinate() . '] (Expected : [' . $this->options['expected_header'][$cell->getCoordinate()] . '] Current : [' . trim($cell->getFormattedValue(), " ") . '])';
                                    }
                                }
                            }
                        } else {
                            //Access Data
                            if ($countColumn <= $this->options['max_column']) {
                                $rowValue['column_' . $countColumn] = $cell->getCalculatedValue();
                            }
                        }

                        $countColumn++;
                    }

                    $countRow++;
                    //Validate for next Row
                    if ($countRow == $this->options['data_start']) {
                        // Check for Missing Header
                        if (count($this->options['expected_header']) != count($passedHeader)) {
                            $missingHeader = array_diff_key($this->options['expected_header'], $passedHeader);
                            foreach ($missingHeader as $coor => $value) {
                                $this->errorMessage[] = 'Expecting Header at [' . $coor . '] with value [' . $value . ']';
                            }
                        }

                        // Break Row Loop in Current Sheet if Header Invalid
                        if (count($this->errorMessage) > 0) {
                            break;
                        }
                    }

                    if (count($rowValue) > 0) {
                        $result[] = $rowValue;
                    }
                }
            }


            if (count($this->errorMessage) > 0) {
                $result = [];
            }
        }

        return $result;
    }

    public function GetErrorMessage()
    {
        return $this->errorMessage;
    }
    #endregion

    #region Options Method
    // For New Type, must add new constant and add new case
    // Option List :
    // - Expected Headers (Coord => Value) -> expected_header
    // - Index Data Start (int) -> data_start
    // - Maximum Column Usage (int) -> max_column
    // - Sheets to Load (arr-string) -> active_sheet

    //Constant List
    public const EXCEL_ASET_SERTIFIKASI = 'ASET_SERTIFIKASI';
    public const EXCEL_ASET_PENERTIBAN = 'ASET_PENERTIBAN';
    public const EXCEL_ASET_PENERTIBAN_SARPRAS = 'ASET_PENERTIBAN_SARPRAS';
    public const EXCEL_PAJAK_CAPAIAN = 'ASET_PAJAK_CAPAIAN';
    public const EXCEL_PAJAK_PENAGIHAN = 'ASET_PAJAK_PENAGIHAN';
    public const EXCEL_PAJAK_TUNGGAKAN = 'ASET_PAJAK_TUNGGAKAN';

    private function GetTypeOption()
    {
        switch ($this->type) {
            case self::EXCEL_ASET_SERTIFIKASI:
                $this->options = $this->GetOptionAsetSertifikasi();
                break;
            case self::EXCEL_ASET_PENERTIBAN:
                $this->options = $this->GetOptionAsetPenertiban();
                break;
            case self::EXCEL_ASET_PENERTIBAN_SARPRAS:
                $this->options = $this->GetOptionAsetPenertibanSarpras();
                break;
            case self::EXCEL_PAJAK_CAPAIAN:
                $this->options = $this->GetOptionPajakCapaian();
                break;
            case self::EXCEL_PAJAK_PENAGIHAN:
                $this->options = $this->GetOptionPajakPenagihan();
                break;
            case self::EXCEL_PAJAK_TUNGGAKAN:
                $this->options = $this->GetOptionPajakTunggakan();
                break;
            default:
                $this->options = [];
                break;
        }
    }

    private function GetOptionAsetSertifikasi()
    {
        $result['expected_header'] = [
            'A1' => 'Instansi', 'B1' => 'Tahun', 'C1' => 'Periode(Triwulan I-IV)',
            'D1' => 'Keterangan', 'E1' => 'Belum Bersertifikat', 'H1' => 'Bersertifikat',
            'K1' => 'Nilai (IDR)', 'E2' => 'Jumlah', 'F2' => 'Bidang',
            'G2' => 'Luas (m2)', 'H2' => 'Jumlah', 'I2' => 'Bidang',
            'J2' => 'Luas'
        ];
        $result['data_start'] = 3;
        $result['max_column'] = 11;
        $result['active_sheet'] = ['Sheet1'];

        return $result;
    }

    private function GetOptionAsetPenertiban()
    {
        $result['expected_header'] = [
            'A1' => 'Instansi', 'B1' => 'Tahun', 'C1' => 'Periode(Triwulan I-IV)',
            'D1' => 'Periode (Bulan)', 'E1' => 'Uraian', 'F1' => 'Tanah',
            'I1' => 'Kendaraan Dinas', 'K1' => 'Bangunan', 'F2' => 'Unit',
            'G2' => 'Luas (m2)', 'H2' => 'Nilai(IDR)', 'I2' => 'Unit',
            'J2' => 'Nilai(IDR)', 'K2' => 'Unit', 'L2' => 'Luas (m2)',
            'M2' => 'Nilai'
        ];
        $result['data_start'] = 3;
        $result['max_column'] = 13;
        $result['active_sheet'] = ['Sheet1'];

        return $result;
    }

    private function GetOptionAsetPenertibanSarpras()
    {
        $result['expected_header'] = [
            'A1' => 'Instansi', 'B1' => 'Tahun', 'C1' => 'Periode(Triwulan I-IV)',
            'D1' => 'Periode Bulan', 'E1' => 'Keterangan', 'F1' => 'Jumlah Lokasi',
            'G1' => 'Luas (m2)', 'H1' => 'Nilai (IDR)'
        ];
        $result['data_start'] = 2;
        $result['max_column'] = 8;
        $result['active_sheet'] = ['Sheet1'];

        return $result;
    }

    private function GetOptionPajakCapaian()
    {
        $result['expected_header'] = [
            'A1' => 'Instansi', 'B1' => 'Tahun', 'C1' => 'Bulan (Januari, Februari, dst)',
            'D1' => 'Jenis Pajak (Cek Tab Jenis Pajak)', 'E1' => 'Pajak Tahun Sebelumnya', 'F1' => 'Target Sekarang',
            'G1' => 'Relisasi'
        ];
        $result['data_start'] = 2;
        $result['max_column'] = 7;
        $result['active_sheet'] = ['Sheet1'];

        return $result;
    }

    private function GetOptionPajakPenagihan()
    {
        $result['expected_header'] = [
            'A1' => 'Instansi', 'B1' => 'Tahun', 'C1' => 'Bulan (Januari, Februari, dst)',
            'D1' => 'Capain Penagihan'
        ];
        $result['data_start'] = 2;
        $result['max_column'] = 4;
        $result['active_sheet'] = ['Sheet1'];

        return $result;
    }

    private function GetOptionPajakTunggakan()
    {
        $result['expected_header'] = [
            'A1' => 'Instansi', 'B1' => 'Tahun', 'C1' => 'Nilai Tunggakan'
        ];
        $result['data_start'] = 2;
        $result['max_column'] = 3;
        $result['active_sheet'] = ['Sheet1'];

        return $result;
    }
    #endregion
}