<?php 

$arrayData = $data;        

$colTitulos[] = $this->_getTranslation('Sucursal');
$colTitulos[] = $this->_getTranslation('Ventas');
$colTitulos[] = $this->_getTranslation('Renta');
$colTitulos[] = $this->_getTranslation('Luz');
$colTitulos[] = $this->_getTranslation('Internet');
$colTitulos[] = $this->_getTranslation('Social');
$colTitulos[] = $this->_getTranslation('Contratista');
$colTitulos[] = $this->_getTranslation('Produccion');
$colTitulos[] = $this->_getTranslation('Gastos');
$colTitulos[] = $this->_getTranslation('Compras');
$colTitulos[] = $this->_getTranslation('Total Nomina');
$colTitulos[] = $this->_getTranslation('Total Fijo');
$colTitulos[] = $this->_getTranslation('Total Compras');
$colTitulos[] = $this->_getTranslation('Ganancia');

$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle('Resumen Sucursal');

$sheet->freezePane('A3');

$col = count($colTitulos);
$lastCol = $this->getColLetter($col - 1);

// Título principal
$sheet->mergeCells("A1:".$lastCol."1");
$sheet->getRowDimension('1')->setRowHeight(58);
$sheet->getStyle("A1")->getFont()->setBold(true)->setSize(14);
$sheet->setCellValue(
    "A1",
    "Resumen por sucursal" . "\n" .
    "Fecha " . $arrayData['filtros']['date'] . "\n" .
    "Periodo: " . $arrayData['filtros']['month'] . " - " . $arrayData['filtros']['year']
);
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);
$sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("A1")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheet->getStyle("A1:".$lastCol."1")->applyFromArray([
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => 'D9E2F3']
    ],
    'borders' => [
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => ['rgb' => 'A6A6A6']
        ]
    ]
]);

// Encabezados
$sheet->fromArray($colTitulos, NULL, 'A2');
$sheet->getRowDimension('2')->setRowHeight(24);
$sheet->getStyle("A2:".$lastCol."2")->getFont()->setBold(true)->getColor()->setRGB('FFFFFF');
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setWrapText(true);
$sheet->getStyle("A2:".$lastCol."2")->applyFromArray([
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => '1F4E78']
    ],
    'borders' => [
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => ['rgb' => 'D9D9D9']
        ]
    ]
]);

$row = 3;
$firstRow = $row;

foreach ($arrayData['result'] as $key => $data) {

    // A - Sucursal
    $sheet->setCellValue("A$row", $data['sucursal']);

    // B - Ventas
    $totalVenta = 0;
    foreach ($data['ventas'] as $venta) {
        $totalVenta += (float)($venta['total_venta'] ?? 0);
    }
    $sheet->setCellValue("B$row", $totalVenta);

    // C - Renta
    $totalRentas = 0;
    foreach ($data['rentas'] as $renta) {
        $totalRentas += (float)($renta['total_valor'] ?? 0);
    }
    $sheet->setCellValue("C$row", $totalRentas);

    // D - Luz
    $totalLuz = 0;
    foreach ($data['luz'] as $luz) {
        $totalLuz += (float)($luz['total_valor'] ?? 0);
    }
    $sheet->setCellValue("D$row", $totalLuz);

    // E - Internet
    $totalInternet = 0;
    foreach ($data['internet'] as $internet) {
        $totalInternet += (float)($internet['total_valor'] ?? 0);
    }
    $sheet->setCellValue("E$row", $totalInternet);

    // F - Social
    $salarioHora = 0;
    $salariofijo_social = 0;
    foreach ($data['horas'] as $hora) {
        $salarioHora += (float)($hora['salario_Hora'] ?? 0);
        $salariofijo_social += (float)($hora['salario_fijo_Social'] ?? 0);
    }
    $salarioSocialSumado = round($salarioHora + $salariofijo_social, 2);
    $sheet->setCellValue("F$row", $salarioSocialSumado);

    // G - Contratista
    $totalFijo = 0;
    $totalHoraContratista = 0;
    foreach ($data['fijo'] as $fijo) {
        $totalFijo += (float)($fijo['salario_fijo'] ?? 0);
        $totalHoraContratista += (float)($fijo['salario_Hora_Contratista'] ?? 0);
    }
    $salarioContratistaSumado = round($totalFijo + $totalHoraContratista, 2);
    $sheet->setCellValue("G$row", $salarioContratistaSumado);

    // H - Producción
    $totalProduccion = 0;
    foreach ($data['produccion'] as $produccion) {
        $totalProduccion += (float)($produccion['total_sum'] ?? 0);
    }
    $sheet->setCellValue("H$row", $totalProduccion);

    // I - Gastos
    $totalGastos = 0;
    foreach ($data['gastos'] as $gasto) {
        $totalGastos += (float)($gasto['total_sum'] ?? 0);
    }
    $sheet->setCellValue("I$row", $totalGastos);

    // J - Compras
    $totalCompras = 0;
    foreach ($data['compras'] as $compra) {
        $totalCompras += (float)($compra['total_compras'] ?? 0);
    }
    $sheet->setCellValue("J$row", $totalCompras);

    // K - Total Nómina
    $totalNomina = round($salarioSocialSumado + $salarioContratistaSumado, 2);
    $sheet->setCellValue("K$row", $totalNomina);

    // L - Total Fijo
    $totalFijoGeneral = round($totalRentas + $totalLuz + $totalInternet, 2);
    $sheet->setCellValue("L$row", $totalFijoGeneral);

    // M - Total Compras
    $totalComprasGeneral = round($totalProduccion + $totalGastos + $totalCompras, 2);
    $sheet->setCellValue("M$row", $totalComprasGeneral);

    // N - Ganancia
    $sheet->setCellValue("N$row", "=B$row-SUM(K$row:M$row)");

    $row++;
}

$lastRow = $row - 1;

// TOTAL
$sheet->setCellValue("A$row", "TOTAL");
foreach (range('B', 'N') as $colLetter) {
    $sheet->setCellValue($colLetter.$row, "=SUM(".$colLetter.$firstRow.":".$colLetter.$lastRow.")");
}
$totalRow = $row;

/* =========================
   ESTILOS GENERALES
========================= */

// Bordes finos para toda la tabla
$sheet->getStyle("A2:".$lastCol.$totalRow)->applyFromArray([
    'borders' => [
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => ['rgb' => 'D9D9D9']
        ]
    ]
]);

// Alineación
$sheet->getStyle("A3:A$totalRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$sheet->getStyle("A3:A$totalRow")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$sheet->getStyle("B3:N$totalRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle("B3:N$totalRow")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// Formato moneda
$sheet->getStyle("B3:N$totalRow")->getNumberFormat()->setFormatCode('$#,##0.00_ ;[Red]-$#,##0.00');

// Sombreado alterno ligero para filas de datos
for ($r = $firstRow; $r <= $lastRow; $r++) {
    if (($r % 2) == 0) {
        $sheet->getStyle("A$r:N$r")->applyFromArray([
            'fill' => [
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => ['rgb' => 'F8F9FB']
            ]
        ]);
    }
}

// Resaltar columnas ejecutivas K, L, M
$sheet->getStyle("K2:M$totalRow")->applyFromArray([
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => 'E2F0D9']
    ]
]);

// Resaltar encabezados de columnas ejecutivas
$sheet->getStyle("K2:M2")->applyFromArray([
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => '70AD47']
    ],
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF']
    ]
]);

// Resaltar columna de Ganancia
$sheet->getStyle("N2:N$totalRow")->applyFromArray([
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => 'FFF2CC']
    ]
]);
$sheet->getStyle("N2")->applyFromArray([
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => 'BF9000']
    ],
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF']
    ]
]);

// Fila total destacada
$sheet->getStyle("A$totalRow:N$totalRow")->applyFromArray([
    'font' => [
        'bold' => true,
        'color' => ['rgb' => '000000']
    ],
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => 'D9EAD3']
    ],
    'borders' => [
        'top' => [
            'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
            'color' => ['rgb' => '7F7F7F']
        ],
        'bottom' => [
            'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
            'color' => ['rgb' => '7F7F7F']
        ]
    ]
]);

// Autofiltro
//$sheet->setAutoFilter("A2:".$lastCol."2");

// Ajuste de ancho
foreach (range('A', $lastCol) as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

// Anchos mínimos para mejorar apariencia ejecutiva
$sheet->getColumnDimension('A')->setWidth(18);
foreach (range('B', 'N') as $colLetter) {
    if ($sheet->getColumnDimension($colLetter)->getWidth() < 14) {
        $sheet->getColumnDimension($colLetter)->setWidth(14);
    }
}

/* =========================
   FORMATO CONDICIONAL GANANCIA
========================= */

// Positivo = verde
$conditionalPositive = new PHPExcel_Style_Conditional();
$conditionalPositive->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$conditionalPositive->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL);
$conditionalPositive->addCondition('0');
$conditionalPositive->getStyle()->getFont()->getColor()->setRGB('006100');
$conditionalPositive->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$conditionalPositive->getStyle()->getFill()->getStartColor()->setRGB('C6EFCE');

// Negativo = rojo
$conditionalNegative = new PHPExcel_Style_Conditional();
$conditionalNegative->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$conditionalNegative->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
$conditionalNegative->addCondition('0');
$conditionalNegative->getStyle()->getFont()->getColor()->setRGB('9C0006');
$conditionalNegative->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$conditionalNegative->getStyle()->getFill()->getStartColor()->setRGB('FFC7CE');

$conditionalStyles = $sheet->getStyle("N3:N$totalRow")->getConditionalStyles();
$conditionalStyles[] = $conditionalPositive;
$conditionalStyles[] = $conditionalNegative;
$sheet->getStyle("N3:N$totalRow")->setConditionalStyles($conditionalStyles);

// Seleccionar hoja principal
$objPHPExcel->setActiveSheetIndex(0);   
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

if ($this->saveFile == true) {
    $objWriter->save($this->getTempFolder()."/".$this->getNombreArchivo().".xlsx");
} else {                
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment;filename=".$this->getNombreArchivo().".xlsx");
    header('Cache-Control: max-age=0');
    $objWriter->save('php://output');
    exit;
}

unset($objWriter);