<?php
session_start();
    $conexion = new mysqli('localhost', 'reasap_admin_cuad', '!Cuadropolis2022!','reasap_PruebasCuadropolis');
    
    //$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'];
    $tipop = $_GET['tipo'];
    $nom = $_SESSION['nom_admin'];
    $iduser = $_SESSION['id_user'];
    $suc = $_SESSION['IdSuc'];
    $sucbus = $_GET['sucbus'];

    if($sucbus == 0){
        if($tipop != 0){
        //$registro = mysqli_query($conexion,"SELECT * FROM factGDL JOIN  ORDER BY IdCliente ASC ");
        $consulta = "SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',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,CASE WHEN V.IdSucursal = 1 THEN 'CULIACAN' WHEN V.IdSucursal=2 THEN 'MAZATLAN' WHEN V.IdSucursal = 3 THEN 'HERMOSILLO' END AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.TipoFolio = '$tipop'  AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY V.IdSucursal,C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq,V.IdSucursal,C.TipoCliente ASC";
        }else if($tipop == 0){
            $consulta = "SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',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,CASE WHEN V.IdSucursal = 1 THEN 'CULIACAN' WHEN V.IdSucursal=2 THEN 'MAZATLAN' WHEN V.IdSucursal = 3 THEN 'HERMOSILLO' END AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY V.IdSucursal,C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq,V.IdSucursal,C.TipoCliente ASC";
        }
    }else if($sucbus != 0){
        if($tipop != 0){
        //$registro = mysqli_query($conexion,"SELECT * FROM factGDL JOIN  ORDER BY IdCliente ASC ");
        $consulta = "SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',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,CASE WHEN V.IdSucursal = 1 THEN 'CULIACAN' WHEN V.IdSucursal=2 THEN 'MAZATLAN' WHEN V.IdSucursal = 3 THEN 'HERMOSILLO' END AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.TipoFolio = '$tipop' AND V.IdSucursal = '$sucbus' AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY V.IdSucursal,C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq,V.IdSucursal,C.TipoCliente ASC";
        }else if($tipop == 0){
            $consulta = "SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',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,CASE WHEN V.IdSucursal = 1 THEN 'CULIACAN' WHEN V.IdSucursal=2 THEN 'MAZATLAN' WHEN V.IdSucursal = 3 THEN 'HERMOSILLO' END AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.IdSucursal = '$sucbus' AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY V.IdSucursal,C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq,V.IdSucursal,C.TipoCliente ASC";
        }
    }

        $resultado = $conexion->query($consulta);
    


	
    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 ventas")
                             ->setSubject("Reporte de ventas")
                             ->setDescription("Reporte de ventas")
                             ->setKeywords("reporte ventas cuadropolis")
                             ->setCategory("Reporte excel");

        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $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';
          }
        if($tipop == 1){
            $ti = 'FISCALES '.$sn;
        }else if($tipop == 2){
            $ti = 'NO FISCALES '.$sn;
        }else if($tipop == 0){
            $ti = 'GENERALES '.$sn;
        }
        $tituloReporte = "REPORTE TIPOS DE CLIENTES ".$ti;
        $titulosColumnas = array('FECHA', 'SUCURSAL','TIPO DE PRECIO','VENTA', 'COSTO', 'UTILIDAD','COBRADO','SALDO');
        
        $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A2:H2');

                  /* 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); 
                        
        // Se agregan los titulos del reporte
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A2',$tituloReporte)
                    ->setCellValue('A3',  $titulosColumnas[0])
                    ->setCellValue('B3',  $titulosColumnas[1])
                    ->setCellValue('C3',  $titulosColumnas[2])
                    ->setCellValue('D3',  $titulosColumnas[3])
                    ->setCellValue('E3',  $titulosColumnas[4])
                    ->setCellValue('F3',  $titulosColumnas[5])
                    ->setCellValue('G3',  $titulosColumnas[6])
                    ->setCellValue('H3',  $titulosColumnas[7]);

        //Se agregan los datos de los alumnos
        $i = 7;
        while ($fila = $resultado->fetch_array()) {

            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fila['FechaBusq'])
                    ->setCellValue('B'.$i,  $fila['SUCURSAL'])
                    ->setCellValue('C'.$i,  $fila['Tipo de precio'])
                    ->setCellValue('D'.$i,  $fila['Venta'])
                    ->setCellValue('E'.$i,  $fila['Costo'])
                    ->setCellValue('F'.$i,  $fila['Utilidad'])
                    ->setCellValue('G'.$i,  $fila['Cobrado'])
                    ->setCellValue('H'.$i,  $fila['Saldo']);
                    $i++;

                    $v = $fila['Venta'];
                    $c = $fila['Costo'];
                    $u = $fila['Utilidad'];
                    $co = $fila['Cobrado'];
                    $s = $fila['Saldo'];

                    $tv += $v;
                    $tc += $c;
                    $tu += $u;
                    $tco += $co;
                    $ts += $s;

 }
        $i ++;

        $objPHPExcel->setActiveSheetIndex(0)
                    
                    ->setCellValue('D5',  $tv)
                    ->setCellValue('E5',  $tc)
                    ->setCellValue('F5',  $tu)
                    ->setCellValue('G5',  $tco)
                    ->setCellValue('H5',  $ts);

        $objPHPExcel->setActiveSheetIndex(0)
                    
                    ->setCellValue('D'.$i,  $tv)
                    ->setCellValue('E'.$i,  $tc)
                    ->setCellValue('F'.$i,  $tu)
                    ->setCellValue('G'.$i,  $tco)
                    ->setCellValue('H'.$i,  $ts);


        $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:H2')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:H".($i-1));
        $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');
       



       // 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 FM');

        // 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="RPTFOM'.$sn.'.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');
    }
?>