<?php

class SituacionFinancieraFechaExcel
{
    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();

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

        $columna = 0;
        $attributes = new Warp($this->query['attributes']);
        foreach ([
            'Código', 
            'Cuenta',
            "Anterior a {$attributes->get('fecha_desde')}",
            "{$attributes->get('fecha_desde')} {$attributes->get('fecha_hasta')}",
            'Variación'
        ] 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++;
        }

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

        $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['anterior']);
            $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

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

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

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

            $fila++;
        }

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

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

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

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

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

        $excel->getActiveSheet()->getStyle("A{$fila}:E{$fila}")->getFont()->setBold(true);
        $fila++;

        $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, 'TOTAL PASIVO');
        $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $totales->get('pasivo.anterior'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

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

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

        $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, 'TOTAL PATRIMONIO');
        $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $totales->get('patrimonio.anterior'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

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

        $excel->getActiveSheet()->setCellValueByColumnAndRow(4, $fila, $totales->get('patrimonio.variacion'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(4, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(4, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $fila++;
        
        $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $fila, 'TOTAL PASIVO + PATRIMONIO');
        $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $fila, $totales->get('pasivo_patrimonio.anterior'));
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getNumberFormat()->setFormatCode('#,##0.00');
        $excel->getActiveSheet()->getStyleByColumnAndRow(2, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

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

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

        $excel->getActiveSheet()->getStyle("A{$fila}:E{$fila}")->getFont()->setBold(true);

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

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