<?php
namespace App\Modules\HR\Reports\Exports;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Files\LocalTemporaryFile;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class EpfEvemcCreatorExport implements WithEvents, WithColumnFormatting
{
    protected $data;

    public function __construct($data)
    {
        $this->data = $data['employees'];
    }

    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function(BeforeWriting $event) {
                $templateFile = new LocalTemporaryFile(public_path('Epf Evemc Creator Sample.xlsm'));
                $event->writer->reopen($templateFile, \Maatwebsite\Excel\Excel::XLSX);
                $sheet = $event->writer->getSheetByIndex(0);

                $this->populateSheet($sheet);

                $event->writer->getSheetByIndex(0)->export($event->getConcernable());

                return $event->getWriter()->getSheetByIndex(0);
            },
        ];
    }

    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_NUMBER,
            'B' => NumberFormat::FORMAT_NUMBER,
            'C' => NumberFormat::FORMAT_NUMBER,
            'D' => NumberFormat::FORMAT_NUMBER,
            'E' => NumberFormat::FORMAT_TEXT,
            'F' => NumberFormat::FORMAT_NUMBER,
            'G' => NumberFormat::FORMAT_NUMBER,
            'H' => NumberFormat::FORMAT_NUMBER,
            'I' => NumberFormat::FORMAT_NUMBER,
            'J' => NumberFormat::FORMAT_NUMBER,
            'K' => NumberFormat::FORMAT_TEXT,
            'L' => NumberFormat::FORMAT_NUMBER,
            'M' => NumberFormat::FORMAT_NUMBER,
            'N' => NumberFormat::FORMAT_NUMBER,
            'O' => NumberFormat::FORMAT_TEXT,
        ];
    }

    private function populateSheet($sheet)
    {
        // Data starts from row 7 (as per EPF EVEMC template)
        foreach ($this->data as $key => $d) {
            $i = $key + 7;

            $sheet->setCellValue('A' . $i, $d['empIDCardNo']);
            $sheet->setCellValue('B' . $i, $d['empName']);
            $sheet->setCellValue('C' . $i, $d['empNameWithInit']);
            $sheet->setCellValue('D' . $i, $d['empEPFNo']);
            $sheet->setCellValue('E' . $i, $d['EPF20']);
            $sheet->setCellValue('F' . $i, $d['EPF12']);
            $sheet->setCellValue('G' . $i, $d['EPF8']);
            $sheet->setCellValue('H' . $i, $d['totForEPF']);
            $sheet->setCellValue('I' . $i, $d['memberStatus']);
            $sheet->setCellValue('J' . $i, $d['zoneCode']);
            $sheet->setCellValue('K' . $i, $d['compEPFRegNo']);
            $sheet->setCellValue('L' . $i, $d['contYearMonth']);
            $sheet->setCellValue('M' . $i, $d['dataSubNo']);
            $sheet->setCellValue('N' . $i, $d['workedDays']);
            $sheet->setCellValue('O' . $i, $d['ocgCode']);
        }
    }
}
