<?php

class EstadoResultadoAnualExcel
{
    protected $query;

    public function __construct($query)
    {
        $this->query = $query;
    }

    public function create()
    {
        $datos = $this->query['detalle'];

        if (count($datos) == 0) {
            exit;
        }

        $phpExcelPath = Yii::getPathOfAlias('ext.Excel.PHPExcel.Classes');
        $phpExcelPath2 = Yii::getPathOfAlias('application.extensions.Excel.PHPExcel.Classes.PHPExcel');
        spl_autoload_unregister(['YiiBase', 'autoload']);
        include $phpExcelPath.DIRECTORY_SEPARATOR.'PHPExcel.php';
        include $phpExcelPath.DIRECTORY_SEPARATOR.'PHPExcel'.DIRECTORY_SEPARATOR.'Reader'.DIRECTORY_SEPARATOR.'Excel2007.php';
        include $phpExcelPath2.DIRECTORY_SEPARATOR.'IOFactory.php';
        spl_autoload_register(['YiiBase', 'autoload']);
        $excel = new PHPExcel();

        $excel->getActiveSheet()->freezePaneByColumnAndRow(0, 2);

        foreach (range('A', 'C') as $columnID) {
            $excel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
        }

        $columna = 0;
        foreach (['Código', 'Cuenta', 'Año actual'] as $titulo) {
            $excel->getActiveSheet()->getStyleByColumnAndRow($columna, 1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyleByColumnAndRow($columna, 1)->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyleByColumnAndRow($columna, 1)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
            $excel->getActiveSheet()->getStyleByColumnAndRow($columna, 1)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $excel->getActiveSheet()->getStyleByColumnAndRow($columna, 1)->getFill()->getStartColor()->setARGB('1C336E');
            $excel->getActiveSheet()->setCellValueByColumnAndRow($columna, 1, $titulo);
            $columna++;
        }

        $fila = 2;
        foreach ($datos as $campos) {
            $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, $campos['cuenta']);
            $excel->getActiveSheet()->getStyleByColumnAndRow(0, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

            $excel->getActiveSheet()->setCellValueByColumnAndRow(1, $fila, $campos['descripcion']);

            $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $campos['actual']);
            $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            if ($campos['cuenta_titulo']) {
                $excel->getActiveSheet()->getStyle("A{$fila}:F{$fila}")->getFont()->setBold(true);
            }

            $fila++;
        }

        $totales = new Warp($this->query['totales']);

        $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, 'TOTAL INGRESOS');

        $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $totales->get('ingresos.variacion'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $fila++;

        $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, 'TOTAL EGRESOS');

        $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $totales->get('egresos.variacion'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $fila++;

        $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, 'RESULTADO DEL EJERCICIO');

        $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $totales->get('utilidad.variacion'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $excel->getActiveSheet()->getStyle("A{$fila}:E{$fila}")->getFont()->setBold(true);
        $excel->getActiveSheet()->getStyle("A{$fila}:E{$fila}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $excel->getActiveSheet()->getStyle("A{$fila}:E{$fila}")->getFill()->getStartColor()->setARGB('cccccc');

        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="Estado resultado.xlsx"');

        $objWriter = new PHPExcel_Writer_Excel2007($excel);
        $objWriter->setOffice2003Compatibility(true);
        $objWriter->save('php://output');
    }
}