<?php
session_start();
require("conexion.php");
    $conexion = new mysqli('localhost', 'reasap_admin_cuad', '!Cuadropolis2022!','reasap_Cuadropolis');
    
    //$conexion = new mysqli('localhost','root','root','SistemaMP');
	if (mysqli_connect_errno()) {
    	printf("La conexión con el servidor de base de datos falló: %s\n", mysqli_connect_error());
    	exit();
	}
	$desde = $_GET['desde'];
	$hasta = $_GET['hasta'];
    $nom = $_SESSION['nom_admin'];
    $iduser = $_SESSION['id_user'];
    $suc = $_SESSION['IdSuc'];
    $sucbus = $_GET['sucbus'];
//JOIN Ventas VT ON VT.Serie = V.Serie AND VT.FolioVenta = V.IdVenta
    if($sucbus == 0){
        $consulta = "SELECT V.Serie,V.IdVenta AS 'Folio',IF(V.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', V.Largo,V.Ancho,IF(V.Umed =1, 'CM','IN') AS 'Unidad', V.CodProd AS 'Producto',V.PrecioMold AS 'Moldura', V.cmold AS 'Costo Moldura', V.PrecioCrisN As 'Cristal', V.ccn AS 'Costo Cristal', V.PrecioCrisA AS 'Cristal Anti', V.cca As 'Costo Cristal Anti', V.PrecioMl AS 'Maria Luisa', V.cml As 'Costo ML', V.PrecioRes AS 'Respaldo', V.cr As 'Costo Respaldo', V.PrecioPapel AS 'Papel Fotografico', V.cpap AS 'Costo Papel F.', V.PrecioText AS 'Textura', V.ctext AS 'Costo Textura', V.PrecioMaq AS 'Maquila', V.PrecioCanva As 'Canva', V.ccan AS 'Costo Canva', V.PrecioAcrilico AS 'Acrilico', V.cacr AS 'Costo Acrilico', V.PrecioDobleCara AS 'Doble cara', V.cdc AS 'Costo Doble cara',V.PrecioTotal AS 'Precio total', V.ctotal AS 'Costo Total',V.CrisN,V.CrisA,V.Ml,V.Res,V.PapF,V.Text,V.Canva,V.Acrilico,V.DobleCara,V.TiraC,V.PTiraC,V.CTiraC,V.Fecha,V.Cantidad FROM VentasDetalle V
        JOIN Ventas VT ON VT.Serie = V.Serie AND VT.FolioVenta = V.IdVenta
            Where VT.Activo = 1 AND V.Activo = 1 AND V.Serie NOT IN ('COT-','','PRE-MAZ-','PRE-HER-','PRE-CUL-','NULL') AND VT.TipoOrden IN ('1','3')  AND VT.FechaBusq BETWEEN '$desde' AND '$hasta' ORDER BY VT.FechaBusq,VT.Serie,VT.IdVenta ASC";
    }else if($sucbus != 0){
        $consulta = "SELECT V.Serie,V.IdVenta AS 'Folio',IF(V.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', V.Largo,V.Ancho,IF(V.Umed =1, 'CM','IN') AS 'Unidad', V.CodProd AS 'Producto',V.PrecioMold AS 'Moldura', V.cmold AS 'Costo Moldura', V.PrecioCrisN As 'Cristal', V.ccn AS 'Costo Cristal', V.PrecioCrisA AS 'Cristal Anti', V.cca As 'Costo Cristal Anti', V.PrecioMl AS 'Maria Luisa', V.cml As 'Costo ML', V.PrecioRes AS 'Respaldo', V.cr As 'Costo Respaldo', V.PrecioPapel AS 'Papel Fotografico', V.cpap AS 'Costo Papel F.', V.PrecioText AS 'Textura', V.ctext AS 'Costo Textura', V.PrecioMaq AS 'Maquila', V.PrecioCanva As 'Canva', V.ccan AS 'Costo Canva', V.PrecioAcrilico AS 'Acrilico', V.cacr AS 'Costo Acrilico', V.PrecioDobleCara AS 'Doble cara', V.cdc AS 'Costo Doble cara',V.PrecioTotal AS 'Precio total', V.ctotal AS 'Costo Total',V.CrisN,V.CrisA,V.Ml,V.Res,V.PapF,V.Text,V.Canva,V.Acrilico,V.DobleCara,V.TiraC,V.PTiraC,V.CTiraC,V.Fecha,V.Cantidad FROM VentasDetalle V
        JOIN Ventas VT ON VT.Serie = V.Serie AND VT.FolioVenta = V.IdVenta
        JOIN Clientes C ON VT.IdCliente = C.IdCliente
            Where VT.Activo = 1 AND V.Activo = 1 AND V.Serie NOT IN ('COT-','','PRE-MAZ-','PRE-HER-','PRE-CUL-','NULL') AND VT.TipoOrden IN ('1','3')  AND VT.FechaBusq BETWEEN '$desde' AND '$hasta' AND VT.IdSucursal = '$sucbus' ORDER BY VT.FechaBusq,VT.Serie,VT.IdVenta ASC";
    }
        $resultado = $conexion->query($consulta);

        if($sucbus == 0){
                $consultatc = "SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'TipoCliente',SUM(V.Total) AS 'Venta',SUM(V.CostoTotal) AS 'Costo',SUM(V.Utilidad) AS 'Utilidad',SUM(V.Anticipo) AS 'Cobrado',SUM(V.Saldo) AS 'Saldo',V.FechaBusq FROM Ventas V
                JOIN Clientes C ON V.IdCliente = C.IdCliente
                WHERE V.Activo = 1 AND V.Serie NOT IN ('PRE-MAZ-','PRE-HER-','PRE-CUL-','NULL','')  AND V.TipoOrden IN ('1','3') AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY C.TipoCliente ORDER BY V.FechaBusq,C.TipoCliente ASC";
            
        }else if($sucbus != 0){
            
                $consultatc = "SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'TipoCliente',SUM(V.Total) AS 'Venta',SUM(V.CostoTotal) AS 'Costo',SUM(V.Utilidad) AS 'Utilidad',SUM(V.Anticipo) AS 'Cobrado',SUM(V.Saldo) AS 'Saldo',V.FechaBusq FROM Ventas V
                JOIN Clientes C ON V.IdCliente = C.IdCliente
                WHERE V.Activo = 1 AND V.Serie NOT IN ('PRE-MAZ-','PRE-HER-','PRE-CUL-','NULL','')  AND V.TipoOrden IN ('1','3') AND V.IdSucursal = '$sucbus' AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY C.TipoCliente ORDER BY V.FechaBusq,C.TipoCliente ASC";
            
        }
    
            


        $resultadotc = $conexion->query($consultatc);
	if($resultado->num_rows > 0 )
	{
						
		date_default_timezone_set('America/Mazatlan');

        if (PHP_SAPI == 'cli')
            die('Este archivo solo se puede ver desde un navegador web');

        /** Se agrega la libreria PHPExcel */
        require_once 'lib/PHPExcel/PHPExcel.php';
        // Se crea el objeto PHPExcel
        $objPHPExcel = new PHPExcel();

        // Se asignan las propiedades del libro
        $objPHPExcel->getProperties()->setCreator("SINTECH") //Autor
                             ->setLastModifiedBy("SINTECH") //Ultimo usuario que lo modificó
                             ->setTitle("Reporte de ordenes")
                             ->setSubject("Reporte de ordenes")
                             ->setDescription("Reporte de ordenes")
                             ->setKeywords("reporte ordenes reasa")
                             ->setCategory("Reporte excel");

        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

        if($sucbus == 0){
            $sn = 'GLOBAL';
          }else if($sucbus == 1){
            $sn = 'CULIACAN';
          }else if($sucbus == 2){
            $sn = 'MAZATLAN';
          }else if($sucbus == 3){
            $sn = 'HERMOSILLO';
          }

        $tituloReporte = "REPORTE DE EJECUTIVO ".$sn.' '.$desde.' AL '.$hasta;
        //$titulosColumnas = array($sn,'Valores Brutos','Utilidad',' ','Valores Netos','Utilidad',' ','Costo Fotos','Caballetes','Costo Caballetes','Fotomosaicos','Costo Fotomosaicos','Maquila');
        
        

                  /* ADD LOGO */
        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setName('Logo');
        $objDrawing->setDescription('Logo');
        $objDrawing->setPath('cuad.png');
        $objDrawing->setCoordinates('A1');
        // set resize to false first
        $objDrawing->setResizeProportional(true);
        // set width later
        $objDrawing->setWidth(250);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(125);
        /* END LOGO */

        // set resize to false first
        $objDrawing->setResizeProportional(true); 
        $objPHPExcel->setActiveSheetIndex(0)
        ->mergeCells('A2:P2')
        ->mergeCells('B3:D3')
        ->mergeCells('G3:I3')
        ->mergeCells('J3:K3')
        ->mergeCells('M3:P3')
        ->mergeCells('J13:K13')
        ->mergeCells('M13:P13');
        // Se agregan los titulos del reporte
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A2',$tituloReporte)
                    ->setCellValue('A3', $sn)
                    ->setCellValue('B3',  'VALORES BRUTOS')
                    ->setCellValue('E3',  'UTILIDAD')
                    ->setCellValue('G3',  'VALORES NETOS')
                    ->setCellValue('J3',  'UTILIDAD')
                    ->setCellValue('M3',  'PORCENTAJE SOBRE VALORES TOTALES')
                    ->setCellValue('A4',  'CATEGORIA')
                    ->setCellValue('B4',  'VENTA')
                    ->setCellValue('C4',  'INGRESO')
                    ->setCellValue('D4',  'COSTO')
                    ->setCellValue('E4',  'PESOS')
                    ->setCellValue('G4',  'VENTA')
                    ->setCellValue('H4',  'INGRESO')
                    ->setCellValue('I4',  'COSTO')
                    ->setCellValue('J4',  'PESOS')
                    ->setCellValue('K4',  '%')
                    ->setCellValue('M4',  'VENTA')
                    ->setCellValue('N4',  'INGRESO')
                    ->setCellValue('O4',  'COSTO')
                    ->setCellValue('P4',  'UTILIDAD')
                    
                    
                    ->setCellValue('E13',  'UTILIDAD')
                    ->setCellValue('J13',  'UTILIDAD')
                    ->setCellValue('M13',  'PORCENTAJE SOBRE VALORES TOTALES')
                    
                    ->setCellValue('B14',  'VENTA')
                    ->setCellValue('C14',  'INGRESO')
                    ->setCellValue('D14',  'COSTO')
                    ->setCellValue('E14',  'PESOS')
                    ->setCellValue('G14',  'VENTA')
                    ->setCellValue('H14',  'INGRESO')
                    ->setCellValue('I14',  'COSTO')
                    ->setCellValue('J14',  'PESOS')
                    ->setCellValue('K14',  '%')

                    ->setCellValue('M14',  'VENTA')
                    ->setCellValue('N14',  'INGRESO')
                    ->setCellValue('O14',  'COSTO')
                    ->setCellValue('P14',  'UTILIDAD');
                    

        //Se agregan los datos de los alumnos
        $i = 11;
        $fila = $resultado->fetch_array();
        
        $filatc = $resultadotc->fetch_array();
        $fechai = $fila['Fecha'];
        $pcatfl = 0;
        $ccatfl = 0;
        $pcatmold = 0;
        $ccatmold = 0;
        $catfotos = 0;
        $ccatfotos = 0;
        $catcab = 0;
        $ccatcab = 0;
        $catfm = 0;
        $ccatfm = 0;
        $catmaq = 0;
        $ventamostr = 0;
        $costomostr= 0;
        $utilmostr = 0;
        $ventafot = 0;
        $costofot = 0;
        $utilfot= 0;
//LLENADO VARIABLES

    do{
                $fechaf = $fila['Fecha'];
                $tipcli = $fila['Tipo de precio'];
                

                $producto = $fila['Producto'];
                $p = mysqli_query($conexion,"SELECT * FROM Productos WHERE Codigo = '$producto'");
                $pf = mysqli_fetch_array($p);
                $cat = $pf['IdCategoria'];
                $cant = $fila['Cantidad'];
/*
SELECT V.Serie,V.IdVenta AS 'Folio',IF(V.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', V.Largo,V.Ancho,IF(V.Umed =1, 'CM','IN') AS 'Unidad', V.CodProd AS 'Producto',V.PrecioMold AS 'Moldura', V.cmold AS 'Costo Moldura', V.PrecioCrisN As 'Cristal', V.ccn AS 'Costo Cristal', V.PrecioCrisA AS 'Cristal Anti', V.cca As 'Costo Cristal Anti', V.PrecioMl AS 'Maria Luisa', V.cml As 'Costo ML', V.PrecioRes AS 'Respaldo', V.cr As 'Costo Respaldo', V.PrecioPapel AS 'Papel Fotografico', V.cpap AS 'Costo Papel F.', V.PrecioText AS 'Textura', V.ctext AS 'Costo Textura', V.PrecioMaq AS 'Maquila', V.PrecioCanva As 'Canva', V.ccan AS 'Costo Canva', V.PrecioAcrilico AS 'Acrilico', V.cacr AS 'Costo Acrilico', V.PrecioDobleCara AS 'Doble cara', V.cdc AS 'Costo Doble cara',V.PrecioTotal AS 'Precio total', V.ctotal AS 'Costo Total',V.CrisN,V.CrisA,V.Ml,V.Res,V.PapF,V.Text,V.Canva,V.Acrilico,V.DobleCara,V.TiraC,V.PTiraC,V.CTiraC,V.Fecha,V.Cantidad,V.TiraC,V.PTiraC,V.CTiraC FROM VentasDetalle V
        
        JOIN Ventas VT ON VT.Serie = V.Serie AND VT.FolioVenta = V.IdVenta
            Where V.Activo = 1 AND V.Serie != 'COT-' AND V.Fecha BETWEEN '$desde' AND '$hasta' AND V.IdSucursal = '$sucbus' ORDER BY V.Fecha,V.Serie,V.IdVenta ASC
*/
                $pprod = $fila['Moldura'];
                    $cprod = $fila['Costo Moldura'];
                    $pcrisn = $fila['Cristal'];
                    $ccrisn = $fila['Costo Cristal'];
                    $pcrisa = $fila['Cristal Anti'];
                    $ccrisa = $fila['Costo Crisal Anti'];
                    $pml = $fila['Maria Luisa'];
                    $cml = $fila['Costo ML'];
                    $pres = $fila['Respaldo'];
                    $cres = $fila['Costo Respaldo'];
                    $ppap = $fila['Papel Fotografico'];
                    $cpap = $fila['Costo Papel F.'];
                    $ptext = $fila['Textura'];
                    $ctext = $fila['Costo Textura'];
                    $maq = $fila['Maquila'];
                    $pcanva = $fila['Canva'];
                    $ccanva = $fila['Costo Canva'];
                    $pacr = $fila['Acrilico'];
                    $cacr = $fila['Costo Acrilico'];
                    $pdobca = $fila['Doble cara'];
                    $cdc = $fila['Costo Doble cara'];
                    $ptot = $fila['Precio total'];
                    $ctot = $fila['Costo Total'];
                    $tirac = $fila['TiraC'];
                    $ptirac = $fila['PTiraC'];
                    $ctirac = $fila['CTiraC'];

                    if($cat == 2){
                        $pcatfl += $pprod * $cant;
                        $ccatfl += $cprod * $cant;
                    }else if($cat == 3){
                        $pcatmold += $pprod * $cant;
                        $ccatmold += $cprod * $cant;
                    }else if($cat == 4){
                        $catfotos += $pprod * $cant;
                        $ccatfotos += $cprod * $cant;
                    }else if($cat == 5){
                        $catcab += $pprod * $cant;
                        $ccatcab += $cprod * $cant;
                    }else if($cat == 6){
                        $catfm += $pprod * $cant;
                        $ccatfm += $cprod * $cant;
                    }else{
                        $extra = $pprod * $cant;
                        $extrac = $cprod * $cant;
                    }
                    if($tirac > 0 ){
                        $pcatmold += $ptirac * $cant;
                        $ccatmold += $ctirac * $cant;
                    }
                    
                    if($pcrisn > 0 && $ccrisn > 0){
                        $catfotos += $pcrisn * $cant;
                        $ccatfotos += $ccrisn * $cant;
                    }
                    
                    if($pcrisa > 0 && $ccrisa > 0){
                        $catfotos += $pcrisa * $cant;
                        $ccatfotos += $ccrisa * $cant;
                    }
                    if($pml > 0 && $cml > 0){
                        $catfotos += $pml * $cant;
                        $ccatfotos += $cml * $cant;
                    }
                    if($pres > 0 && $cres > 0){
                        $catfotos += $pres * $cant;
                        $ccatfotos += $cres * $cant;
                    }
                    if($ppap > 0 && $cpap > 0){
                        $catfotos += $ppap * $cant;
                        $ccatfotos += $cpap * $cant;
                    }
                    if($ptext > 0 && $ctext > 0){
                        $catfotos += $ptext * $cant;
                        $ccatfotos += $ctext * $cant;
                    }
                    if($maq > 0){
                        $catmaq += $maq  * $cant;
                    }
                    if($pcanva >0 && $ccanva >0 ){
                        $catfotos += $pcanva * $cant;
                        $ccatfotos += $ccanva * $cant;
                    }
                    if($pacr >0 && $cacr >0 ){
                        $catfotos += $pacr * $cant;
                        $ccatfotos += $cacr * $cant;
                    }
                    if($pdobca >0 && $cdc >0 ){
                        $catfotos += $pdobca * $cant;
                        $ccatfotos += $cdc * $cant;
                    }


                    $ptprod += $pprod;
                    $ctprod += $cprod;
                    $ptcrisn += $pcrisn;
                    $ctcrisn += $ccrisn;
                    $ptcrisa += $pcrisa;
                    $ctcrisa += $ccrisa;
                    $ptml += $pml;
                    $ctml += $cml;
                    $ptres += $pres;
                    $ctres += $cres;
                    $ptpap += $ppap;
                    $ctpap += $cpap;
                    $pttext += $ptext;
                    $cttext += $ctext;
                    $ptmaq += $maq;
                    $ptcanva += $pcanva;
                    $ctcanva += $ccanva;
                    $ptacr += $pacr;
                    $ctacr += $cacr;
                    $ptdobca += $pdobca;
                    $ctdc += $cdc;
                    $pttot += $ptot;
                    $cttot += $ctot;
                    if($tipcli == 'Mostrador'){
                        $ventamostr += $ptot;
                        $costomostr += $ctot;
                        $utilmostr += ($ptot - $ctot);
                    }else if($tipcli == 'Fotografo'){
                        $ventafot += $ptot;
                        $costofot += $ctot;
                        $utilfot += ($ptot - $ctot);
                    }

    }while($fila = $resultado->fetch_array());

    do{
        
        $tipocliente = $filatc['TipoCliente'];
        if($tipocliente == 'Mostrador'){
            $tcventam = $filatc['Venta'];
            $tccostom = $filatc['Costo'];
            $tcutilm = $filatc['Utilidad'];
            $tccobradom = $filatc['Cobrado'];
            $tcsaldom = $filatc['Saldo'];
        }
        if($tipocliente == 'Fotografo'){
            $tcventaf = $filatc['Venta'];
            $tccostof = $filatc['Costo'];
            $tcutilf = $filatc['Utilidad'];
            $tccobradof = $filatc['Cobrado'];
            $tcsaldof = $filatc['Saldo'];
        }   

    }while($filatc = $resultadotc->fetch_array());
//LLENADO VARIABLES
        $ttcventa = $tcventam + $tcventaf;
        $ttccosto = $tccostom + $tccostof;
        $ttcutil  = $tcutilm + $tcutilf;
        $ttccobrado = $tccobradom + $tccobradof;
        $ttcsaldo = $tcsaldom + $tcsaldof;

        
        $tpfl += $pcatfl;
        $tcfl += $ccatfl;
        $tpmold += $pcatmold;
        $tcmold += $ccatmold;
        $tpfotos += $catfotos;
        $tcfotos += $ccatfotos;
        $tcatcab += $catcab;
        $tccatcab += $ccatcab;
        $tcatfm += $catfm;
        $tccatfm += $ccatfm;
        $tmaq += $catmaq;
        $utilidadtotal = ($tpfotos + $tpmold + $tpfl + $tmaq + $tcatfm) - ($tcfotos + $tcmold + $tcfl + $tccatfm);
        $ventatotal = ($tpfotos + $tpmold + $tpfl + $tmaq + $tcatfm);
        $costototal = $tcfotos + $tcmold + $tcfl + $tccatfm;
        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A5','MOLDURA')
        ->setCellValue('A6', 'FOTOS')
        ->setCellValue('A7',  'LIBROS')
        ->setCellValue('A8',  'FOTOMOSAICOS')
        ->setCellValue('A9',  'MOLDURAS')
        ->setCellValue('A10',  'MAQUILA')
        ->setCellValue('A11',  'TOTAL')
        
        ->setCellValue('A14',  'Tipo de cliente')
        ->setCellValue('A15',  'Fotografo')
        ->setCellValue('A16',  'Mostrador')
        ->setCellValue('A17',  'TOTAL');
//molduras
        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('B5',  $pcatmold)
        ->setCellValue('C5',  ' ')
        ->setCellValue('D5',  $ccatmold)
        ->setCellValue('E5',  $pcatmold - $ccatmold)

        ->setCellValue('G5',  $pcatmold)
        ->setCellValue('H5',  ' ')
        ->setCellValue('I5',  $ccatmold)
        ->setCellValue('J5',  $pcatmold - $ccatmold)
        ->setCellValue('K5',  ($pcatmold - $ccatmold)/$pcatmold)
        ->setCellValue('M5',  $pcatmold / $ventatotal)
        ->setCellValue('O5',  $ccatmold / $costototal)
        ->setCellValue('P5',  ($pcatmold - $ccatmold)/$utilidadtotal);
//fotos
        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('B6',  $catfotos)
        ->setCellValue('C6',  ' ')
        ->setCellValue('D6',  $ccatfotos)
        ->setCellValue('E6',  $catfotos - $ccatfotos)

        ->setCellValue('G6',  $catfotos)
        ->setCellValue('H6',  ' ')
        ->setCellValue('I6',  $ccatfotos)
        ->setCellValue('J6',  $catfotos - $ccatfotos)
        ->setCellValue('K6',  ($catfotos - $ccatfotos)/$catfotos)

        ->setCellValue('M6',  $catfotos / $ventatotal)
        ->setCellValue('O6',  $ccatfotos / $costototal)
        ->setCellValue('P6',  ($catfotos - $ccatfotos)/$utilidadtotal);
//fotomosaicos
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B8',  $catfm)
->setCellValue('C8',  ' ')
->setCellValue('D8',  $ccatfm)
->setCellValue('E8',  $catfm - $ccatfm)

->setCellValue('G8',  $catfm)
->setCellValue('H8',  ' ')
->setCellValue('I8',  $ccatfm)
->setCellValue('J8',  $catfm - $ccatfm)
->setCellValue('K8',  ($catfm - $ccatfm)/$catfm)

->setCellValue('M8',  $catfm / $ventatotal)
->setCellValue('O8',  $ccatfm / $costototal)
->setCellValue('P8',  ($catfm - $ccatfm)/$utilidadtotal);

//libros
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B7',  $pcatfl)
->setCellValue('C7',  ' ')
->setCellValue('D7',  $ccatfl)
->setCellValue('E7',  $pcatfl - $ccatfl)

->setCellValue('G7',  $pcatfl)
->setCellValue('H7',  ' ')
->setCellValue('I7',  $ccatfl)
->setCellValue('J7',  $pcatfl - $ccatfl)
->setCellValue('K7',  ($pcatfl - $ccatfl)/$pcatfl)

->setCellValue('M7',  $pcatfl / $ventatotal)
->setCellValue('O7',  $ccatfl / $costototal)
->setCellValue('P7',  ($pcatfl - $ccatfl)/$utilidadtotal);

//MAQUILA
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B10',  $catmaq)
->setCellValue('C10',  ' ')
->setCellValue('D10',  '0')
->setCellValue('E10',  $catmaq - 0)

->setCellValue('G10',  $catmaq)
->setCellValue('H10',  ' ')
->setCellValue('I10',  '0')
->setCellValue('J10',  $catmaq - 0)
->setCellValue('K10',  ($catmaq - 0)/$catmaq)

->setCellValue('M10',  $catmaq / $ventatotal)
->setCellValue('O10',  0 / $costototal)
->setCellValue('P10',  ($catmaq - 0)/$utilidadtotal);

//TOTAL
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B11',  $ventatotal)
->setCellValue('C11',  ' ')
->setCellValue('D11',  $costototal)
->setCellValue('E11',  $utilidadtotal)

->setCellValue('G11',  $ventatotal)
->setCellValue('H11',  ' ')
->setCellValue('I11',  $costototal)
->setCellValue('J11',  $utilidadtotal)
->setCellValue('K11',  ($ventatotal - $costototal)/$ventatotal);

//fotografo
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B15',  $tcventaf)
->setCellValue('C15',  ' ')
->setCellValue('D15',  $tccostof)
->setCellValue('E15',  $tcutilf)

->setCellValue('G15',  $tcventaf)
->setCellValue('H15',  ' ')
->setCellValue('I15',  $tccostof)
->setCellValue('J15',  $tcutilf)
->setCellValue('K15',  ($tcventaf - $tccostof)/$ttcventa)
   
->setCellValue('M15',  $tcventaf / $ttcventa)
->setCellValue('O15',  $tccostof / $ttccosto)
->setCellValue('P15',  ($tcventaf - $tccostof)/$ttcutil);
//mostrador
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B16',  $tcventam)
->setCellValue('C16',  ' ')
->setCellValue('D16',  $tccostom)
->setCellValue('E16',  $tcutilm)

->setCellValue('G16',  $tcventam)
->setCellValue('H16',  ' ')
->setCellValue('I16',  $tccostom)
->setCellValue('J16',  $tcutilm)
->setCellValue('K16',  ($tcventam - $tccostom)/$ttcventa)

->setCellValue('M16',  $tcventam / $ttcventa)
->setCellValue('O16',  $tccostom / $ttccosto)
->setCellValue('P16',  ($tcventam - $tccostom)/$ttcutil);
//totalclientes
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B17',  $ttcventa)
->setCellValue('C17',  ' ')
->setCellValue('D17',  $ttccosto)
->setCellValue('E17',  $ttcutil)

->setCellValue('G17',  $ttcventa)
->setCellValue('H17',  ' ')
->setCellValue('I17',  $ttccosto)
->setCellValue('J17',  $ttcutil)
->setCellValue('K17',  ($ttcventa - $ttccosto)/$ttcventa);
    
 //totalclientes
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B19',  $ventafot)
->setCellValue('B20',  $ventamostr)
->setCellValue('D19',  $costofot)
->setCellValue('D20',  $costomostr)
->setCellValue('E19',  ($ventafot- $costofot))
->setCellValue('E20',  ($ventamostr -  $costomostr))


->setCellValue('B21',  $ventafot + $ventamostr)
->setCellValue('D21',  $costomostr + $costofot)
->setCellValue('E21',  ($ventamostr + $ventafot) -  ($costomostr + $costofot));

           
$i = 21;
$i++;

        $estiloTituloReporte = array(
            'font' => array(
                'name'      => 'Verdana',
                'bold'      => true,
                'italic'    => false,
                'strike'    => false,
                'size' =>16,
                    'color'     => array(
                        'rgb' => 'FFFFFF'
                    )
            ),
            'fill' => array(
                'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('argb' => 'FF2b5e28')
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_NONE                    
                )
            ), 
            'alignment' =>  array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'rotation'   => 0,
                    'wrap'          => TRUE
            )
        );

        $estiloTituloColumnas = array(
            'font' => array(
                'name'      => 'Arial',
                'bold'      => true,                          
                'color'     => array(
                    'rgb' => 'FFFFFF'
                )
            ),
            'fill'  => array(
                'type'      => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 90,
                'startcolor' => array(
                    'rgb' => '57BB52'
                ),
                'endcolor'   => array(
                    'argb' => 'FF57BB52'
                )
            ),
            'borders' => array(
                'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '143860'
                    )
                ),
                'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '143860'
                    )
                )
            ),
            'alignment' =>  array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'wrap'          => TRUE
            ));
            
        $estiloInformacion = new PHPExcel_Style();
        $estiloInformacion->applyFromArray(
            array(
                'font' => array(
                'name'      => 'Arial',               
                'color'     => array(
                    'rgb' => '000000'
                )
            ),
            'fill'  => array(
                'type'      => PHPExcel_Style_Fill::FILL_SOLID,
                'color'     => array('argb' => 'FFFFFFFF')
            ),
            'borders' => array(
                
                'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '000000'
                    )
                ),
                'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
                    'color' => array(
                        'rgb' => '000000'
                    )
                )

            ),
            'alignment' =>  array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER
                    
            )
        ));
         
        $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:P".($i-1));
        $objPHPExcel->getActiveSheet()->getStyle('B4:B'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('C4:C'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('D4:D'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('E4:E'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('F4:F'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('G4:G'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('H4:H'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('I4:I'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('J4:J'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');/* 
        $objPHPExcel->getActiveSheet()->getStyle("K5:P11")->getNumberFormat()->applyFromArray(["code" => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE]); */

            $objPHPExcel->getActiveSheet()->getStyle('K5:P11')
        ->getNumberFormat()->applyFromArray( 
            array( 
                'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
            )
        );
        $objPHPExcel->getActiveSheet()->getStyle('K15:P17')
        ->getNumberFormat()->applyFromArray( 
            array( 
                'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
            )
        );
        /*for($i = 1; $i <= 11; $i++){
                $objPHPExcel->getActiveSheet()->getStyle('K'.($i))
            ->getNumberFormat()->applyFromArray( 
                array( 
                    'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
                )
            );
        } 
        for($i = 1; $i <= 11; $i++){
            $objPHPExcel->getActiveSheet()->getStyle('M'.($i))
        ->getNumberFormat()->applyFromArray( 
            array( 
                'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
            )
        );
    }
    for($i = 1; $i <= 11; $i++){
        $objPHPExcel->getActiveSheet()->getStyle('O'.($i))
    ->getNumberFormat()->applyFromArray( 
        array( 
            'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
        )
    );
}
for($i = 1; $i <= 11; $i++){
    $objPHPExcel->getActiveSheet()->getStyle('P'.($i))
->getNumberFormat()->applyFromArray( 
    array( 
        'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
    )
);
} */

       // In my case this line didn't make much of a difference
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
// Iterating all the sheets
/** @var PHPExcel_Worksheet $sheet */
foreach ($objPHPExcel->getAllSheets() as $sheet) {
    // Iterating through all the columns
    // The after Z column problem is solved by using numeric columns; thanks to the columnIndexFromString method
    for ($col = 0; $col <= PHPExcel_Cell::columnIndexFromString($sheet->getHighestDataColumn()); $col++) {
        $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
    }
}

        
        // Se asigna el nombre a la hoja
        $objPHPExcel->getActiveSheet()->setTitle('Reporte de productos');

        // Se activa la hoja para que sea la que se muestre cuando el archivo se abre
        $objPHPExcel->setActiveSheetIndex(0);
        // Inmovilizar paneles 
        //$objPHPExcel->getActiveSheet(0)->freezePane('A4');
        $objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0,4);

        // Se manda el archivo al navegador web, con el nombre que se indica (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="ReportedeCategorias.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
        
    }
    else{
        print_r('No hay resultados para mostrar');
    }
?>