<?php

namespace CrmSpreadsheet;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Conditional;

class CrmSpreadsheet extends Spreadsheet
{
    public $meses = array(
        '01'  => 'Janeiro',
        '02'  => 'Fevereiro',
        '03'  => 'Março',
        '04'  => 'Abril',
        '05'  => 'Maio',
        '06'  => 'Junho',
        '07'  => 'Julho',
        '08'  => 'Agosto',
        '09'  => 'Setembro',
        '10' => 'Outubro',
        '11' => 'Novembro',
        '12' => 'Dezembro'
    );

    public function setDefaults()
    {
        $this->getDefaultStyle()->getFont()->setSize(10);

        $this->getProperties()
                    ->setCreator("Windel CRM " . VERSAO)
                    ->setLastModifiedBy("Windel CRM " . VERSAO)
                    ->setCreated(time())
                    ->setCompany("WINDEL SISTEMAS LTDA - EPP");
    }

    public function createHeader($values, $filter = false)
    {
        $sheet = $this->getActiveSheet();

        $columnLetter = "A";
        foreach ($values as $key => $value) {
            $columnLetter = Coordinate::stringFromColumnIndex($key+1);
            $coordinate = $columnLetter . "1";

            $sheet->setCellValue($coordinate, $value['name']);

            if (isset($value['width'])) {
                $columnDimension = $sheet->getColumnDimension($columnLetter);
                if ($value['width'] == 'auto') {
                    $columnDimension->setAutoSize(true);
                } elseif (is_numeric($value['width'])) {
                    $columnDimension->setWidth($value['width']);
                }
            }

            $style = $sheet->getStyle($columnLetter);
            if (isset($value['wrap']) && $value['wrap'] === true) {
                $style->getAlignment()->setWrapText(true);
            }
            $style
                  ->getAlignment()
                  ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        }

        $range = "A1:{$columnLetter}1";

        if ($filter) {
            $sheet->setAutoFilter($range);
        }

        $styleArray = [
            'font' => [
                'bold' => true,
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
                    'color' => ['rgb' => '000000'],
                ],
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'rotation' => 90,
                'startColor' => [
                    'rgb' => 'BBDBEE',
                ],
            ],
        ];
        $sheet->getStyle($range)->applyFromArray($styleArray);
        $sheet->freezePane('A2');
    }

    public function createFooter($values, $linha)
    {
        $sheet = $this->getActiveSheet();

        $columnLetter = "A";
        foreach ($values as $key => $value) {
            $columnLetter = Coordinate::stringFromColumnIndex($key+1);
            $coordinate = $columnLetter . $linha;

            if (is_array($value)) {
                if (isset($value['format'])) {
                    $sheet
                        ->getStyle($coordinate)
                        ->getNumberFormat()
                        ->setFormatCode($value['format']);
                }
                $value = $value['value'];
            }

            $sheet->setCellValue($coordinate, $value);
        }

        $styleArray = [
            'font' => [
                'bold' => false,
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
                    'color' => ['rgb' => '000000'],
                ],
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'rotation' => 90,
                'startColor' => [
                    'rgb' => 'BBDBEE',
                ],
            ],
        ];

        $range = "A{$linha}:{$columnLetter}{$linha}";
        $sheet->getStyle($range)->applyFromArray($styleArray);
    }

    public function abasSintetico($values)
    {
        foreach ($values as $title => $valores) {
            ksort($valores);
            $values[$title] = $valores;
        }

        foreach ($values as $title => $valores) {
            $currency = $valores['currency'];
            unset($valores['currency']);

            if (isset($valores['name'])) {
                $title = $valores['name'];
                unset($valores['name']);
            }

            $header = array();
            $header[] = array(
                'name' => $title,
                'width' => 'auto',
                'wrap' => false
            );

            $hasHeader2 = false;
            if (isset($valores['header2'])) {
                $header[] = array(
                    'name' => $valores['header2'],
                    'width' => 'auto',
                    'wrap' => false
                );
                unset($valores['header2']);
                $hasHeader2 = true;
            }

            $sheet = $this->createSheet();
            $sheet->setTitle($title);

            $this->setActiveSheetIndexByName($title);
            $aba = $this->getActiveSheet();

            $header[] = array(
                'name' => 'Total',
                'width' => 'auto',
                'wrap' => false
            );

            $this->createHeader($header);

            $linha = 2;
            foreach ($valores as $key => $value) {
                $coluna = 1;
                if (!is_array($value['valor'])) {
                    $aba->setCellValueByColumnAndRow($coluna, $linha, $value['valor']);
                    $coluna++;
                } else {
                    foreach ($value['valor'] as $val) {
                        $aba->setCellValueByColumnAndRow($coluna, $linha, $val);
                        $coluna++;
                    }
                }

                $aba->setCellValueByColumnAndRow($coluna, $linha, $value['count']);
                if ($currency) {
                    $aba
                        ->getStyleByColumnAndRow($coluna, $linha)
                        ->getNumberFormat()
                        ->setFormatCode('"R$ "#,##0.00_-');
                }

                $linha++;
            }

            if (empty($valores)) {
                $value = "0";
            } elseif (count($value) == 1) {
                $keys = array_keys($valores);
                $primeira = reset($keys);
                $value = $valores[$primeira]['count'];
            } elseif (count($value) > 1) {
                $linhaFinal = $linha - 1;
                $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                $value = "=SUM({$columnLetter}2:{$columnLetter}{$linhaFinal})";
            }

            $footer = array();

            $footer[] = 'Total';
            if ($hasHeader2) {
                $footer[] = "";
            }
            if (!$currency) {
                $footer[] = $value;
            } else {
                $footer[] = array('value' => $value, 'format' => '"R$ "#,##0.00_-');
            }
            $this->createFooter($footer, $linha);
        }
    }

    public function setConditionalStyle($range, $style)
    {
        $sheet = $this->getActiveSheet();

        $conditionalStyles = $sheet->getStyle($range)->getConditionalStyles();
        if (is_array($style)) {
            $conditionalStyles += $style;
        } else {
            $conditionalStyles[] = $style;
        }

        $sheet->getStyle($range)->setConditionalStyles($conditionalStyles);
    }
}
