<?php
session_start();
    $conexion = new mysqli('localhost', 'reasapac_admin', 'PACKreasa123','reasapac_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'];

    
    $consulta = "SELECT Serie,IdVenta AS 'Folio',IF(TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', Largo,Ancho,IF(Umed =1, 'CM','IN') AS 'Unidad', CodProd AS 'Producto',PrecioMold AS 'Moldura', cmold AS 'Costo Moldura', PrecioCrisN As 'Cristal', ccn AS 'Costo Cristal', PrecioCrisA AS 'Cristal Anti', cca As 'Costo Cristal Anti', PrecioMl AS 'Maria Luisa', cml As 'Costo ML', PrecioRes AS 'Respaldo', cr As 'Costo Respaldo', PrecioPapel AS 'Papel Fotografico', cpap AS 'Costo Papel F.', PrecioText AS 'Textura', ctext AS 'Costo Textura', PrecioMaq AS 'Maquila', PrecioCanva As 'Canva', ccan AS 'Costo Canva', PrecioAcrilico AS 'Acrilico', cacr AS 'Costo Acrilico', PrecioDobleCara AS 'Doble cara', cdc AS 'Costo Doble cara',PrecioTotal AS 'Precio total', ctotal AS 'Costo Total' FROM VentasDetalle Where Activo = 1 AND Serie != 'COT-' AND Fecha BETWEEN '$desde' AND '$hasta' ORDER BY Serie,IdVenta";
        $resultado = $conexion->query($consulta);
    


	
	if($resultado->num_rows > 0 )
	{
						
		date_default_timezone_set('America/Mexico_City');

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

        $tituloReporte = "REPORTE DE PRODUCTOS";
        $titulosColumnas = array('FOLIO', 'TIPO PRECIO','LARGO','ANCHO', 'UNIDAD', 'PRODUCTO','MOLDURA','COSTO MOLDURA','CRISTAL',' COSTO CRISTAL ',' CRISTAL ANTI ',' COSTO CRISTAL ANTI ','MARIA LUISA', 'COSTO ML', 'RESPALDO', 'COSTO RESPALDO','PAPEL FOTOGRAFICO','COSTO PAPEL F.','TEXTURA',' COSTO TEXTURA ',' MAQUILA ',' CANVA','COSTO CANVA', 'ACRILICO', 'COSTO ACRILICO','DOBLE CARA','COSTO DOB. CARA','PRECIO TOTAL',' COSTO TOTAL ');
        
        $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A1:AC1');

                    /* ADD LOGO */
        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setName('Logo');
        $objDrawing->setDescription('Logo');
        $objDrawing->setPath('../imagenes/logo.jpg');
        $objDrawing->setCoordinates('A1');


        // set resize to false first
        $objDrawing->setResizeProportional(true);
        // set width later
        $objDrawing->setHeight(200);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(190);

        // set resize to false first
        $objDrawing->setResizeProportional(true); 
                        
        // Se agregan los titulos del reporte
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A1',$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])
                    ->setCellValue('N3',  $titulosColumnas[13])
                    ->setCellValue('O3',  $titulosColumnas[14])
                    ->setCellValue('P3',  $titulosColumnas[15])
                    ->setCellValue('Q3',  $titulosColumnas[16])
                    ->setCellValue('R3',  $titulosColumnas[17])
                    ->setCellValue('S3',  $titulosColumnas[18])
                    ->setCellValue('T3',  $titulosColumnas[19])
                    ->setCellValue('U3',  $titulosColumnas[20])
                    ->setCellValue('V3',  $titulosColumnas[21])
                    ->setCellValue('W3',  $titulosColumnas[22])
                    ->setCellValue('X3',  $titulosColumnas[23])
                    ->setCellValue('Y3',  $titulosColumnas[24])
                    ->setCellValue('Z3',  $titulosColumnas[25])
                    ->setCellValue('AA3',  $titulosColumnas[26])
                    ->setCellValue('AB3',  $titulosColumnas[27])
                    ->setCellValue('AC3',  $titulosColumnas[28]);

        //Se agregan los datos de los alumnos
        $i = 4;
        while ($fila = $resultado->fetch_array()) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fila['Serie'].$fila['Folio'])
                    ->setCellValue('B'.$i,  $fila['Tipo de precio'])
                    ->setCellValue('C'.$i,  $fila['Largo'])
                    ->setCellValue('D'.$i,  $fila['Ancho'])
                    ->setCellValue('E'.$i,  $fila['Unidad'])
                    ->setCellValue('F'.$i,  $fila['Producto'])
                    ->setCellValue('G'.$i,  $fila['Moldura'])
                    ->setCellValue('H'.$i,  $fila['Costo Moldura'])
                    ->setCellValue('I'.$i,  $fila['Cristal'])
                    ->setCellValue('J'.$i,  $fila['Costo Cristal'])
                    ->setCellValue('K'.$i,  $fila['Cristal Anti'])
                    ->setCellValue('L'.$i,  $fila['Costo Cristal Anti'])
                    ->setCellValue('M'.$i,  $fila['Maria Luisa'])
                    ->setCellValue('N'.$i,  $fila['Costo ML'])
                    ->setCellValue('O'.$i,  $fila['Respaldo'])
                    ->setCellValue('P'.$i,  $fila['Costo Respaldo'])
                    ->setCellValue('Q'.$i,  $fila['Papel Fotografico'])
                    ->setCellValue('R'.$i,  $fila['Costo Papel F.'])
                    ->setCellValue('S'.$i,  $fila['Textura'])
                    ->setCellValue('T'.$i,  $fila['Costo Textura'])
                    ->setCellValue('U'.$i,  $fila['Maquila'])
                    ->setCellValue('V'.$i,  $fila['Canva'])
                    ->setCellValue('W'.$i,  $fila['Costo Canva'])
                    ->setCellValue('X'.$i,  $fila['Acrilico'])
                    ->setCellValue('Y'.$i,  $fila['Costo Acrilico'])
                    ->setCellValue('Z'.$i,  $fila['Doble cara'])
                    ->setCellValue('AA'.$i,  $fila['Costo Doble cara'])
                    ->setCellValue('AB'.$i,  $fila['Precio total'])
                    ->setCellValue('AC'.$i,  $fila['Costo Total']);
                    $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' => 'FF0A24A4')
            ),
            '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' => '0A24A4'
                ),
                'endcolor'   => array(
                    'argb' => 'FF081B7C'
                )
            ),
            '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' => 'FF9AA5DE')
            ),
            '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('A1:AC1')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:AC3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:AC".($i-1));
        $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');
        $objPHPExcel->getActiveSheet()->getStyle('N4:N'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('O4:O'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('P4:P'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('Q4:Q'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('R4:R'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('S4:S'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('T4:T'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('U4:U'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('V4:V'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('W4:W'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('X4:X'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('Y4:Y'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('Z4:Z'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('AA4:AA'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('AB4:AB'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('AC4:AC'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');



        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

            $objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));

            $sheet = $objPHPExcel->getActiveSheet();
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);
            /** @var PHPExcel_Cell $cell */
            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->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="ReportedeProductos.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');
    }
?>