<?php 

    $arrayData = $data;        
 
    //echo "<pre>";var_dump($arrayData);echo "</pre>";exit;
    
    $colTitulos[] = $this->_getTranslation('Sucursal');

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

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

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

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

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

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

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

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

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

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

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

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



    $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", "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("A2:".$lastCol."2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

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



    $row=3;

    $firstRow = $row;
   
    foreach ($arrayData['result'] as $key => $data) {       
        // Sucursal
        $sheet->setCellValue("A$row", $data['sucursal']);
        
        // Ventas
        $totalVenta = 0;
        foreach ($data['ventas'] as $venta) {
            $totalVenta += isset($venta['total_venta']) ? (float)$venta['total_venta'] : 0;
        }
        $sheet->setCellValue("B$row", $totalVenta);
    
        // Rentas
        $totalRentas = 0;
     
        foreach ($data['rentas'] as $renta) {
            $totalRentas += isset($renta['total_valor']) ? (float)$renta['total_valor'] : 0;
            
        }
        $sheet->setCellValue("C$row", $totalRentas);

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

        $totalTelefono = 0;
     
        foreach ($data['telefono'] as $telefono) {
            $totalTelefono += isset($telefono['total_valor']) ? (float)$telefono['total_valor'] : 0;
            
        }
        $sheet->setCellValue("E$row", $totalTelefono);

        
        $totalInternet = 0;
     
        foreach ($data['internet'] as $internet) {
            $totalInternet += isset($internet['total_valor']) ? (float)$internet['total_valor'] : 0;
            
        }
        $sheet->setCellValue("F$row", $totalInternet);
    
        // Horas
       
        $salarioHora = 0;
        $salariofijo_social = 0;
        foreach ($data['horas'] as $hora) {
           
            $salarioHora += isset($hora['salario_Hora']) ? (float)$hora['salario_Hora'] : 0;
            $salariofijo_social += isset($hora['salario_fijo_Social']) ? (float)$hora['salario_fijo_Social'] : 0;


        }
        $salarioSocialSumado  = round($salarioHora + $salariofijo_social,2);
        $sheet->setCellValue("G$row", $salarioSocialSumado);

    
        // Fijo
        $totalFijo = 0;
        $totalHoraContratista = 0;

        foreach ($data['fijo'] as $fijo) {
            $totalFijo += isset($fijo['salario_fijo']) ? (float)$fijo['salario_fijo'] : 0;
            $totalHoraContratista += isset($fijo['salario_Hora_Contratista']) ? (float)$fijo['salario_Hora_Contratista'] : 0;

        }
        $salarioContratistaSumado  = round($totalFijo + $totalHoraContratista,2);
        $sheet->setCellValue("H$row", $salarioContratistaSumado);
    
        // Producción
        $totalProduccion = 0;
        foreach ($data['produccion'] as $produccion) {
            $totalProduccion += isset($produccion['total_sum']) ? (float)$produccion['total_sum'] : 0;
        }
        $sheet->setCellValue("I$row", $totalProduccion);
    
        // Gastos
        $totalGastos = 0;
        foreach ($data['gastos'] as $gasto) {
            $totalGastos += isset($gasto['total_sum']) ? (float)$gasto['total_sum'] : 0;
        }
        $sheet->setCellValue("J$row", $totalGastos);


          // Gastos Caja
          $totalGastosCaja = 0;
          foreach ($data['gastosCaja'] as $gasto) {
              $totalGastos += isset($gasto['total_sum']) ? (float)$gasto['total_sum'] : 0;
          }
          $sheet->setCellValue("K$row", $totalGastosCaja);
    
        // Compras
        $totalCompras = 0;
        foreach ($data['compras'] as $compra) {
            $totalCompras += isset($compra['total_compras']) ? (float)$compra['total_compras'] : 0;
        }
        $sheet->setCellValue("L$row", $totalCompras);

    
    // Ganancia
    //$totalGastado = ($totalRentas + $totalLuz + $totalTelefono + $totalInternet + $totalHoras  + $totalFijo + $totalProduccion + $totalGastos + $totalCompras );

    //CAMBIO DE totalHoras X salarioSocialSumado no se porque estaba asi
    $totalGastado = ($totalRentas + $totalLuz + $totalTelefono + $totalInternet + $salarioSocialSumado  + $totalFijo + $totalProduccion + $totalGastos + $totalGastosCaja + $totalCompras );

    $totalGanancias = ($totalVenta);
    
    $sheet->setCellValue("M$row", "=B$row-SUM(C$row:L$row)");
    
        $row++;
    }
    

    $sheet->getStyle("A$firstRow:E$row")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $sheet->getStyle("A$firstRow:E$row")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);

    $sheet->getStyle("B$firstRow:L$row")->getNumberFormat()->setFormatCode('$#,##0.00_ ;[Red]-#,##0.00');

    $sheet->getStyle("H$firstRow:H$row")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    

    $lastRow = $row - 1;

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

    $sheet->setCellValue("B$row", "=SUM(B$firstRow:B$lastRow)"); 

    $sheet->setCellValue("C$row", "=SUM(C$firstRow:C$lastRow)"); 

    $sheet->setCellValue("D$row", "=SUM(D$firstRow:D$lastRow)"); 

    $sheet->setCellValue("E$row", "=SUM(E$firstRow:E$lastRow)"); 

    $sheet->setCellValue("F$row", "=SUM(F$firstRow:F$lastRow)"); 

    $sheet->setCellValue("G$row", "=SUM(G$firstRow:G$lastRow)");

    $sheet->setCellValue("H$row", "=SUM(H$firstRow:H$lastRow)");

    $sheet->setCellValue("I$row", "=SUM(I$firstRow:I$lastRow)");

    $sheet->setCellValue("J$row", "=SUM(J$firstRow:J$lastRow)");

    $sheet->setCellValue("K$row", "=SUM(K$firstRow:K$lastRow)");

    $sheet->setCellValue("L$row", "=SUM(L$firstRow:L$lastRow)");
    
    $sheet->setCellValue("M$row", "=SUM(M$firstRow:M$lastRow)");
    
    $sheet->getStyle("A$row:M$row")->getFont()->setBold(true);

    $sheet->getStyle("B$row:M$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);