<?php
session_start();
require("conexion.php");
    $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();
	}
    $nom = $_SESSION['nom_admin'];
    $iduser = $_SESSION['id_user'];
    $suc = $_SESSION['IdSuc'];

    $consulta = "SELECT
    I.IdInsumo,
    I.Descripcion,
    I.Codigo,
    I.IdProveedor,
    P.Nombre,
    P.RazonSocial,
    I.PrecioCompra,
    I.PrecioVarios,
    CASE WHEN I.Moneda = 1 THEN 'PESOS' WHEN I.Moneda = 2 THEN 'DOLARES' END AS 'Moneda',
    I.UnidPaq,
    CASE WHEN I.IdUnidadMed = 1 THEN 'Metro' WHEN I.IdUnidadMed = 2 THEN 'Area' WHEN I.IdUnidadMed = 3 THEN 'Pieza' END AS 'Umed',
    I.Desperdicio,
    I.TotalPaq,
    I.CostoUnit,
    I.TotalUnit,
    I.PorcDesp
FROM
    Insumos I
JOIN Proveedor P ON
    P.IdProveedor = I.IdProveedor
WHERE
    I.idtipo = 0 AND I.Activo = 1 AND I.Eliminado = 0
ORDER BY
    I.Descripcion 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 insumos")
                             ->setSubject("Reporte de insumos")
                             ->setDescription("Reporte de insumos")
                             ->setKeywords("reporte insumos 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';
          }
        $usuarios = mysqli_query($conexion,"SELECT * FROM Usuarios WHERE Activado = 1 AND Eliminado = 0 AND IdUsuario = '$iduser'");
        $us = mysqli_fetch_array($usuarios);
        $nombreu = $us['Nombre'].' '.$us['Apellidos'];
        $nombreu = strtoupper($nombreu);
        $tituloReporte = "REPORTE DE INSUMOS ".$nombreu;
        $titulosColumnas = array('Id', 'Descripcion','Codigo','IdProveedor','Nombre proveedor', 'Razon Social proveedor', 'Precio compra','Precio varios','Moneda','Unid. por paq','U. med','Desperdicio','Total paq.','Costo unit.','Total unit.','Porc. desperdicio');
        
        $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A2:P2');

                  /* 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])
                    ->setCellValue('N3',  $titulosColumnas[13])
                    ->setCellValue('O3',  $titulosColumnas[14])
                    ->setCellValue('P3',  $titulosColumnas[15]);

        //Se agregan los datos de los alumnos
        $i = 4;
        while ($fila = $resultado->fetch_array()) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fila['IdInsumo'])
                    ->setCellValue('B'.$i,  $fila['Descripcion'])
                    ->setCellValue('C'.$i,  $fila['Codigo'])
                    ->setCellValue('D'.$i,  $fila['IdProveedor'])
                    ->setCellValue('E'.$i,  $fila['Nombre'])
                    ->setCellValue('F'.$i,  $fila['RazonSocial'])
                    ->setCellValue('G'.$i,  $fila['PrecioCompra'])
                    ->setCellValue('H'.$i,  $fila['PrecioVarios'])
                    ->setCellValue('I'.$i,  $fila['Moneda'])
                    ->setCellValue('J'.$i,  $fila['UnidPaq'])
                    ->setCellValue('K'.$i,  $fila['Umed'])
                    ->setCellValue('L'.$i,  $fila['Desperdicio'])
                    ->setCellValue('M'.$i,  $fila['TotalPaq'])
                    ->setCellValue('N'.$i,  $fila['CostoUnit'])
                    ->setCellValue('O'.$i,  $fila['TotalUnit'])
                    ->setCellValue('P'.$i,  $fila['PorcDesp']);
                    $i++;


 }
        $i ++;
        /* $objPHPExcel->setActiveSheetIndex(0)
                    
                    ->setCellValue('E'.$i,  $tv)
                    ->setCellValue('F'.$i,  $tc)
                    ->setCellValue('G'.$i,  $tu)
                    ->setCellValue('H'.$i,  $tco)
                    ->setCellValue('I'.$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:P2')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:P".($i-1));
        $objPHPExcel->getActiveSheet()->getStyle('O4:O'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('N4:N'.($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('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 insumos');

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