<?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 V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,IF(D.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio',V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
        JOIN Clientes C ON V.IdCliente = C.IdCliente
        JOIN VentasDetalle D ON V.Serie = D.Serie AND V.FolioVenta = D.IdVenta
        WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.TipoFolio = '$tipop' AND V.EstadoPago != 2 AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY V.Serie, V.FolioVenta ORDER BY V.IdSucursal,V.Serie,V.FolioVenta";
        }else if($tipop == 0){
            $consulta = "SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,IF(D.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio',V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            JOIN VentasDetalle D ON V.Serie = D.Serie AND V.FolioVenta = D.IdVenta
        WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.EstadoPago != 2 AND V.FechaBusq BETWEEN '$desde' AND '$hasta' GROUP BY V.Serie, V.FolioVenta ORDER BY V.IdSucursal,V.Serie,V.FolioVenta";
        }
    }else if($sucbus != 0){
        if($tipop != 0){
        //$registro = mysqli_query($conexion,"SELECT * FROM factGDL JOIN  ORDER BY IdCliente ASC ");
        $consulta = "SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,IF(D.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio',V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
        JOIN Clientes C ON V.IdCliente = C.IdCliente
        JOIN VentasDetalle D ON V.Serie = D.Serie AND V.FolioVenta = D.IdVenta
        WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.TipoFolio = '$tipop' AND V.EstadoPago != 2 AND V.FechaBusq BETWEEN '$desde' AND '$hasta' AND V.IdSucursal = '$sucbus' GROUP BY V.Serie, V.FolioVenta ORDER BY V.IdSucursal,V.Serie,V.FolioVenta";
        }else if($tipop == 0){
            $consulta = "SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,IF(D.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio',V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            JOIN VentasDetalle D ON V.Serie = D.Serie AND V.FolioVenta = D.IdVenta
        WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.EstadoPago != 2 AND V.FechaBusq BETWEEN '$desde' AND '$hasta' AND V.IdSucursal = '$sucbus' GROUP BY V.Serie, V.FolioVenta ORDER BY V.IdSucursal,V.Serie,V.FolioVenta";
        }
    }

        $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 DE VENTAS NO PAGADAS".$ti;
        $titulosColumnas = array('FECHA', 'FOLIO','CLIENTE','TIPO CLIENTE','SUBTOTAL VENTA','IVA VENTA','TOTAL VENTA','SUBTOTAL COSTO', 'IVA COSTO', 'TOTAL COSTO', 'UTILIDAD','COBRADO','SALDO');
        
        $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A2:M2');

                  /* 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])
         ->setCellValue('I3',  $titulosColumnas[8])
         ->setCellValue('J3',  $titulosColumnas[9])
         ->setCellValue('K3',  $titulosColumnas[10])
         ->setCellValue('L3',  $titulosColumnas[11])
         ->setCellValue('M3',  $titulosColumnas[12]);

        //Se agregan los datos de los alumnos
        $i = 7;
        while ($fila = $resultado->fetch_array()) {
            $totalv = $fila['Venta'];
            $subv = $totalv / 1.16;
            $ivav = $subv * 0.16;
            $totalc = $fila['Costo'];
            $subc = $totalc / 1.16;
            $ivac = $subc * 0.16;
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fila['FechaVenta'])
                    ->setCellValue('B'.$i,  $fila['Serie'].$fila['FolioVenta'])
                    ->setCellValue('C'.$i,  $fila['Nombre'])
                    ->setCellValue('D'.$i,  $fila['Tipo de precio'])
                    ->setCellValue('E'.$i,  $subv)
                    ->setCellValue('F'.$i,  $ivav)
                    ->setCellValue('G'.$i,  $totalv)
                    ->setCellValue('H'.$i,  $subc)
                    ->setCellValue('I'.$i,  $ivac)
                    ->setCellValue('J'.$i,  $totalc)
                    ->setCellValue('K'.$i,  $fila['Utilidad'])
                    ->setCellValue('L'.$i,  $fila['Cobrado'])
                    ->setCellValue('M'.$i,  $fila['Saldo']);
                    $i++;
                    
                    
                    $u = $fila['Utilidad'];
                    $co = $fila['Cobrado'];
                    $s = $fila['Saldo'];

                    $sv += $subv;
                    $iv += $ivav;
                    $tv += $totalv;
                    $sc += $subc;
                    $ic += $ivac;
                    $tc += $totalc;
                    $tu += $u;
                    $tco += $co;
                    $ts += $s;

 }
        $j=5;
        $objPHPExcel->setActiveSheetIndex(0)
                    
                    ->setCellValue('E'.$j,  $sv)
                    ->setCellValue('F'.$j,  $iv)
                    ->setCellValue('G'.$j,  $tv)
                    ->setCellValue('H'.$j,  $sc)
                    ->setCellValue('I'.$j,  $ic)
                    ->setCellValue('J'.$j,  $tc)
                    ->setCellValue('K'.$j,  $tu)
                    ->setCellValue('L'.$j,  $tco)
                    ->setCellValue('M'.$j,  $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:M2')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:M".($i-1));
        $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('K4:K'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('L4:L'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('M4:M'.($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 de ventas');

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