<?php

/*
 * *************************************************
 * Copyright (C) 2021 Earworks.                    *
 * Codeigniter Project Template Modification       *
 * Please contact our email for more information : *
 * contact@earworks.co.id                          *
 * *************************************************
 */

defined('BASEPATH') OR exit('No direct script access allowed');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// style
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Shared\Date;

/**
 * Description of Spreadsheet
 *
 * @author Andhy Taslin <andhy@earworks.co.id>
 * @since 1.0
 */
class Ci_spreadsheet extends Spreadsheet {

    public function generate($name = 'spreadsheet') {
        $writer = new Xlsx($this);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $name . '.xlsx"');
        $writer->save('php://output');
    }

    public function read($file, $config = []) {
        $identifier = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file);
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($identifier);
        if (isset($config['column_read'])) {
            $reader->setReadFilter(new SheetFilter($config['column_read']));
        }
        if (isset($config['sheet'])) {
            $reader->setLoadSheetsOnly($config['sheet']);
        }
        $spreadsheet = $reader->load($file);
        $rows = $this->read_data($spreadsheet, $config);
        return $rows;
    }

    private function read_data($spreadsheet, $config) {
        $sheet_list = isset($config['sheet']) ? $config['sheet'] : null;
        $get_calculate = isset($config['calculate']) && $config['calculate'] === true;
        $rows = [];
        $available_sheets = $spreadsheet->getSheetNames();
        if (is_array($sheet_list)) {
            foreach ($sheet_list as $target) {
                if (!in_array($target, $available_sheets)) {
                    continue;
                }
                $spreadsheet->setActiveSheetIndexByName($target);
                $worksheet = $spreadsheet->getActiveSheet();
                foreach ($worksheet->getRowIterator() AS $row) {
                    $rows[$target][] = $this->read_row($row, $get_calculate);
                }
            }
        } elseif (!is_array($sheet_list) && !empty($sheet_list)) {
            if (in_array($sheet_list, $available_sheets)) {
                $spreadsheet->setActiveSheetIndexByName($sheet_list);
                $worksheet = $spreadsheet->getActiveSheet();

                foreach ($worksheet->getRowIterator() AS $row) {
                    $rows[] = $this->read_row($row, $get_calculate);
                }
            }
        } else {
            $worksheet = $spreadsheet->getActiveSheet();
            foreach ($worksheet->getRowIterator() AS $row) {
                $rows[] = $this->read_row($row, $get_calculate);
            }
        }
        return $rows;
    }

    private function read_row($rowIterator, $get_calculate) {
        $cellIterator = $rowIterator->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
        $cells = [];
        foreach ($cellIterator as $cell) {
            if ($get_calculate) {
                $cells[$cell->getColumn()] = $cell->getCalculatedValue();
            } else {
                $cells[$cell->getColumn()] = $cell->getValue();
            }
        }
        return $cells;
    }

    public function get_time($value) {
        return Date::excelToTimestamp($value);
    }

    public function excel_time($date = null) {
        $time = null;
        if ($date) {
            $time = @strtotime($date);
        }
        return $time ? Date::PHPToExcel($time) : null;
    }

//    public function read_import() {
//        if (!isset($_FILES['file']) || empty($_FILES['file']['tmp_name'])) {
//            return [];
//        }
//        return $time ? Date::PHPToExcel($time) : null;
//    }

    public function prepare_user($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(6);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(16);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);
        $sheet->getColumnDimension('G')->setWidth(20);
        $sheet->getColumnDimension('H')->setWidth(30);
        $sheet->getColumnDimension('I')->setWidth(20);
        $sheet->getColumnDimension('J')->setWidth(20);
        $sheet->getColumnDimension('K')->setWidth(20);
        $sheet->getColumnDimension('L')->setWidth(20);
        $sheet->getColumnDimension('M')->setWidth(20);
        $sheet->getColumnDimension('N')->setWidth(10);
        $sheet->getColumnDimension('O')->setWidth(30);
        $sheet->getColumnDimension('P')->setWidth(5);
        $sheet->getColumnDimension('Q')->setWidth(20);
        $sheet->getColumnDimension('R')->setWidth(20);
        $sheet->getColumnDimension('S')->setWidth(20);
        $sheet->getColumnDimension('T')->setWidth(20);
        $sheet->getColumnDimension('U')->setWidth(20);
        $sheet->getColumnDimension('V')->setWidth(20);
        $sheet->getColumnDimension('W')->setWidth(20);
        $sheet->getColumnDimension('X')->setWidth(20);
        $sheet->getColumnDimension('Y')->setWidth(7);
        $sheet->getColumnDimension('Z')->setWidth(7);
        $sheet->getColumnDimension('AA')->setWidth(12);
        $sheet->getColumnDimension('AB')->setWidth(9);
        $sheet->freezePane('A2');
        $sheet->mergeCells('AA1:AB1');
        $sheet->getStyle('A1:AB1')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A1:AB1')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A1:AB1')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('B2:B' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_TEXT);
        $sheet->getStyle('AA2:AA' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
        $sheet->getStyle('AB2:AB' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
        return $sheet;
    }

    public function prepare_user_data($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(6);
        $sheet->getColumnDimension('B')->setWidth(12);
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->getColumnDimension('D')->setWidth(10);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);
        $sheet->getColumnDimension('G')->setWidth(30);
        $sheet->getColumnDimension('H')->setWidth(30);
        $sheet->getColumnDimension('I')->setWidth(20);
        $sheet->getColumnDimension('J')->setWidth(20);
        $sheet->getColumnDimension('K')->setWidth(20);
        $sheet->getColumnDimension('L')->setWidth(20);
        $sheet->getColumnDimension('M')->setWidth(10);
        $sheet->getColumnDimension('N')->setWidth(50);
        $sheet->getColumnDimension('O')->setWidth(5);
        $sheet->getColumnDimension('P')->setWidth(20);
        $sheet->getColumnDimension('Q')->setWidth(20);
        $sheet->getColumnDimension('R')->setWidth(20);
        $sheet->freezePane('A2');
        $sheet->getStyle('A1:R1')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A1:R1')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A1:R1')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('B2:R' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_TEXT);
        return $sheet;
    }

    public function prepare_absen_gate($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(35);
        $sheet->getColumnDimension('C')->setWidth(35);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(6);
        $sheet->freezePane('A5');
        $sheet->mergeCells('D4:E4');
        
        $sheet->getStyle('A1:E2')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A4:E4')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A5:E' . $config['num_row'])->applyFromArray([
            'alignment' => [
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A1:E2')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A4:E4')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('A1:E2')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A4:E4')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('A2:E' . $config['num_row'])->getAlignment()->setWrapText(true);
         $sheet->getStyle('D2:D' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
        $sheet->getStyle('E2:E' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
        return $sheet;
    }

    public function prepare_absen($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(16);
        $sheet->getColumnDimension('C')->setWidth(61);
        $sheet->freezePane('A2');
        $sheet->getStyle('A1:C1')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A2:C' . $config['num_row'])->applyFromArray([
            'alignment' => [
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A1:C1')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A1:C1')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('A2:C' . $config['num_row'])->getAlignment()->setWrapText(true);
        return $sheet;
    }

    public function prepare_absen_detail($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(16);
        $sheet->getColumnDimension('C')->setWidth(16);
        $sheet->getColumnDimension('D')->setWidth(16);
        $sheet->getColumnDimension('E')->setWidth(10);
        $sheet->freezePane('A2');
        $sheet->mergeCells('D1:E1');
        $sheet->getStyle('A1:E1')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A1:E1')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A1:E1')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');

        $sheet->getStyle('D2:D' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
        $sheet->getStyle('E2:E' . $config['num_row'])
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
        return $sheet;
    }

    public function prepare_doorprize($sheet, $config = []) {
        return $this->prepare_absen($sheet, $config);
    }

    public function prepare_doorprize_detail($sheet, $config = []) {
        return $this->prepare_absen_detail($sheet, $config);
    }

    public function prepare_form($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(62);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->freezePane('A5');
        $sheet->mergeCells('A2:F2');
        $sheet->mergeCells('B4:F4');
        $sheet->getStyle('A4:F4')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A4:F4')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A4:F4')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('A2:F2')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A2:F2')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A2:F2')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
//        $sheet->getStyle('F2:F' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_TEXT);
//        $sheet->getStyle('G2:G' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_TEXT);
//        $sheet->getStyle('D5:D' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
//        $sheet->getStyle('E5:E' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
        return $sheet;
    }

    public function prepare_respon_hasil($sheet, $config = []) {
        if (isset($config['title'])) {
            $sheet->setTitle($config['title']);
        }
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(22);
        $sheet->getColumnDimension('D')->setWidth(22);
        $sheet->getColumnDimension('E')->setWidth(22);
        $sheet->getColumnDimension('F')->setWidth(22);
        $sheet->getColumnDimension('G')->setWidth(22);
        $sheet->getColumnDimension('H')->setWidth(22);
        $sheet->getColumnDimension('I')->setWidth(22);
        $sheet->getColumnDimension('J')->setWidth(22);
        $sheet->freezePane('A5');
        $sheet->mergeCells('A2:I2');
        $sheet->getRowDimension(4)->setRowHeight(40);
        $sheet->getStyle('A4:I4')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => array(
                'horizontal' => PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
                'vertical' => PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrap' => true,
                'wrapText' => true
            )
        ]);
        $sheet->getStyle('A4:I4')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A4:I4')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('A2:I2')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $sheet->getStyle('A2:I2')
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('cccccccc'));
        $sheet->getStyle('A2:I2')
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('fff8f8f8');
        $sheet->getStyle('C5:G' . $config['num_row'])->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
//        $sheet->getStyle('D4:D' . $config['num_row'])
//            ->getNumberFormat()
//            ->setFormatCode(NumberFormat::FORMAT_TEXT);
//        $sheet->getStyle('G2:G' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_TEXT);
//        $sheet->getStyle('D5:D' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
//        $sheet->getStyle('E5:I' . $config['num_row'])
//                ->getNumberFormat()
//                ->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
        return $sheet;
    }

}

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class SheetFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public $column;
    public $row;

    public function __construct($column = null, $row = 0) {
        $this->row = $row;
        $this->column = $column;
    }

    public function readCell($columnAddress, $row, $worksheetName = '') {
        if (in_array($columnAddress, $this->column)) {
            return true;
        } else {
            return false;
        }
    }

}
