<?php

$arrayData = $data;
 //echo "<pre>";var_dump($arrayData);echo "</pre>";exit;

 $colTitulos[] = $this->_getTranslation('Sucursal');

// Añadimos las horas al array de títulos de columnas
for ($hour = 7; $hour <= 22; $hour++) {
    $colTitulos[] = $this->_getTranslation($hour);
}

$colTitulos[] = $this->_getTranslation('Total');



$objPHPExcel = new PHPExcel();

$sheet = $objPHPExcel->getActiveSheet();

$sheet->freezePane('A3');

$col = count($colTitulos);

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


$sheet->mergeCells("A1:" . $lastCol . "1");

$sheet->getRowDimension('1')->setRowHeight(50);

$sheet->getStyle("A1")->getFont()->setBold(true);

$sheet->setCellValue("A1", "Ventas por Hora" . "\n" . "Fecha " . $arrayData['filtros']['date'] . "\n" . "Periodo: " . $arrayData['filtros']['De'] . " - " . $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("A2:" . $lastCol . "2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$sheet->fromArray($colTitulos, NULL, 'A2');



$row = 3;

$firstRow = $row;

foreach ($arrayData['result']['sucursales']['id_sucursal'] as $index => $idSucursal) {
    $sheet->setCellValue("A$row", $arrayData['result']['sucursales']['sucursales'][$index]);

    // Añadimos las ventas por cada hora
    $totalVenta = 0;
    $colIndex = 'B'; // Empieza en la columna B (la primera después de "Sucursal")

    for ($hour = 7; $hour <= 22; $hour++) {
        $ventaHora = isset($arrayData['result']['dataByHour'][$index][$hour]) ? $arrayData['result']['dataByHour'][$index][$hour] : 0.00;
        $sheet->setCellValue($colIndex . $row, $ventaHora);
        $totalVenta += $ventaHora;
        $colIndex++;
    }

    // Añadimos la columna de Total
    $sheet->setCellValue($colIndex . $row, $totalVenta);

    $row++;
}

$sheet->getStyle("A$firstRow:".$lastCol."$row")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("A$firstRow:".$lastCol."$row")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheet->getStyle("B$firstRow:".$lastCol."$row")->getNumberFormat()->setFormatCode('$#,##0.00_ ;[Red]-#,##0.00');

$lastRow = $row - 1;
$sheet->setCellValue("A$row", "TOTAL");

// Calculamos los totales por hora y el total general
$colIndex = 'B';
for ($hour = 7; $hour <= 22; $hour++) {
    $sheet->setCellValue($colIndex . $row, "=SUM(" . $colIndex . "$firstRow:" . $colIndex . "$lastRow)");
    $colIndex++;
}


// Calculamos el total general de todas las sucursales
$sheet->setCellValue($colIndex . $row, "=SUM(" . $colIndex . "$firstRow:" . $colIndex . "$lastRow)");


$sheet->getStyle("A$row:".$lastCol."$row")->getFont()->setBold(true);
$sheet->getStyle("B$row:".$lastCol."$row")->getNumberFormat()->setFormatCode('$#,##0.00_ ;[Red]-#,##0.00');

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



$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);
