<?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();
	}
	$desde = $_GET['desde'];
    $nom = $_SESSION['nom_admin'];
    $iduser = $_SESSION['id_user'];
    $suc = $_SESSION['IdSuc'];
    $sucbus = $_GET['sucbus'];

    $consulta = "SELECT SUM(M.Total) as 'TotalV' FROM Movimientos M WHERE M.FechaB = '$desde'  AND M.Activo = 1 AND M.TipoMovimiento not in (4,2) AND M.IdSucursal = '$sucbus' ";

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


	
	if($resultado->num_rows > 0 )
	{
 $date = DateTime::createFromFormat("Y-m-d", $desde);
 $dia = $date->format("d");

 $fechaant = new DateTime($desde);
 $fechaant->modify('-1 day');
 $fechaAnterior = $fechaant->format('Y-m-d');

		$pagadodia = "
    SELECT
        SUM(M.Anticipo) as 'Pagado'
    FROM
        Movimientos M
    JOIN Ventas V ON M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
    WHERE V.FechaBusq = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN (4, 2) AND V.EstadoPago = 2 AND V.TipoFolio = 1";
    $pagadodia .= " AND M.IdSucursal =".$sucbus;
    $respagdia = $conexion->query($pagadodia);
    $rpd = $respagdia->fetch_array();

    $anticipodia = "
    SELECT
        SUM(M.Anticipo) as 'Anticipo'
    FROM
        Movimientos M
    JOIN Ventas V ON M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
    WHERE V.FechaBusq = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN (4, 2) AND V.EstadoPago = 1 AND V.TipoFolio = 1";
    $anticipodia .= " AND M.IdSucursal =".$sucbus;
    $resantdia = $conexion->query($anticipodia);
    $rad = $resantdia->fetch_array();

    $saldoscobrados = "
    SELECT
    SUM(M.Anticipo) AS 'Saldos'
FROM
    Movimientos M
JOIN Ventas V ON
    M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
WHERE
    M.FechaB = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN(4, 2)   AND M.Anticipo > 0 AND M.TipoMovimiento = 3 AND V.FechaBusq != '$desde' AND V.TipoFolio = 1";
    $saldoscobrados .= " AND M.IdSucursal =".$sucbus;
    $ressaldoscob = $conexion->query($saldoscobrados);
    $rsc = $ressaldoscob->fetch_array();

    $cobtotal = "
    SELECT
    SUM(M.Anticipo) AS 'Cobranza'
FROM
    Movimientos M
JOIN Ventas V ON
    M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
WHERE
    M.FechaB = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN(4, 2)   AND M.Anticipo > 0  AND V.TipoFolio = 1";
    $cobtotal .= " AND M.IdSucursal =".$sucbus;
    $rescobtotal = $conexion->query($cobtotal);
    $rct = $rescobtotal->fetch_array();

    $ventamostrador = "
    SELECT
    SUM(M.Anticipo) AS 'VentaM'
FROM
    Movimientos M
JOIN Ventas V ON
    M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
WHERE
    M.FechaB = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN(4, 2)  AND M.Anticipo > 0  AND V.TipoFolio = 2";
    $ventamostrador .= " AND M.IdSucursal =".$sucbus;
    $resventamostrador = $conexion->query($ventamostrador);
    $rvm = $resventamostrador->fetch_array();

    $saldopendiente = "
    SELECT
    SUM(M.Saldo) AS 'SaldoPen'
FROM
    Movimientos M
JOIN Ventas V ON
    M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
WHERE
    M.FechaB = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN(4, 2)  AND M.Saldo > 0 AND V.TipoFolio = 1";
    $saldopendiente .= " AND M.IdSucursal =".$sucbus;
    $ressdopendiente = $conexion->query($saldopendiente);
    $rsp = $ressdopendiente->fetch_array();

    $facturasemitidas = "
    SELECT
    IFNULL(SUM(M.Anticipo),0) as 'Facturas'
FROM
    Movimientos M
JOIN Ventas V ON M.Serie = V.Serie AND M.FolioVenta = V.FolioVenta
WHERE
    V.FechaBusq = '$desde' AND M.Activo = 1 AND M.TipoMovimiento NOT IN (4, 2) AND V.EstadoPago = 2 AND V.TipoFolio = 1 AND V.EdoFact = 1";
    $facturasemitidas .= " AND M.IdSucursal =".$sucbus;
    $resfactemitidas = $conexion->query($facturasemitidas);
    $rfe = $resfactemitidas->fetch_array();

    $egresosq = "
    SELECT 
    SUM(CASE WHEN TipoEgreso = 1 THEN Total ELSE 0 END) AS 'Retiro',
    SUM(CASE WHEN TipoEgreso = 2 THEN Total ELSE 0 END) AS 'Depositos',
    SUM(CASE WHEN TipoEgreso = 3 THEN Total ELSE 0 END) AS 'Paqueteria',
    SUM(CASE WHEN TipoEgreso = 4 THEN Total ELSE 0 END) AS 'Propinas',
    SUM(CASE WHEN TipoEgreso = 5 THEN Total ELSE 0 END) AS 'Insumos',
    SUM(CASE WHEN TipoEgreso = 6 THEN Total ELSE 0 END) AS 'Agua',
    SUM(CASE WHEN TipoEgreso = 7 THEN Total ELSE 0 END) AS 'Varios',
    SUM(CASE WHEN TipoEgreso = 8 THEN Total ELSE 0 END) AS 'Basura',
    SUM(CASE WHEN TipoEgreso = 9 THEN Total ELSE 0 END) AS 'Otros',
    SUM(Total) AS 'Egresos'
    FROM `Movimientos` WHERE TipoMovimiento = 2 and FechaB = '$desde' AND Activo = 1 ";
    $egresosq .= " AND IdSucursal =".$sucbus;
    $resegresos = $conexion->query($egresosq);
    $reg = $resegresos->fetch_array();
    
    $arqueo = "
    SELECT * FROM Arqueo WHERE Fecha = '$desde' AND IdSucursal = '".$sucbus."' order by IdArqueo desc limit 1";
    $resarqueo = $conexion->query($arqueo);
    $rar = $resarqueo->fetch_array();


///////////////// ARQUEO FECHA ACTUAL DEL REPORTE ///////////////////////////
    $j = mysqli_query($conexion,"SELECT * FROM Movimientos WHERE FechaB = '$desde' AND Activo = 1 AND IdSucursal = '$sucbus'");
    $totale = 0;
    while($f = mysqli_fetch_array($j)){
        $anti = $f['Anticipo'];
        $totale = $anti + $totale;
        $tipomov = $f['TipoMovimiento'];
        $mp = $f['MetodoPago'];
        if($tipomov == 2){
            $salida += $f['Total'];
        }else{
            if($mp == 1){
                $tefe = $tefe + $anti;
            }else if($mp== 2){
                $tch = $tch + $anti;
            }else if($mp == 3){
                $ttar = $ttar + $anti;
            }
        }
    }
    $total = $rar['Saldo'];
    $totali = $rar['SaldoInicial'];
    $totalf = ($totali + $tefe) - $salida;
    $dif = $total - $totalf;
    /* $totalf = number_format($totalf,2,'.',',');
    $totale = number_format($totale,2,'.',',');
    $salidas = number_format($salida,2,'.',',');
    $efectivo = number_format($tefe,2,'.',',');
    $cheques = number_format($tch,2,'.',',');
    $tarj = number_format($ttar,2,'.',',');
    $total = number_format($total,2,'.',',');
    $dif = number_format($dif,2,'.',','); */
//////////////////////////////////////////////////////////////////////////////



		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("SISTEMA CUADROPOLIS") //Autor
                             ->setLastModifiedBy("SISTEMA CUADROPOLIS"); //Ultimo usuario que lo modificó
                             

        $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($suc == 0){
            $sn = 'GLOBAL';
          }else if($suc == 1){
            $sn = 'CULIACAN';
          }else if($suc == 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;
        } */
        $usuarios = mysqli_query($conexion,"SELECT * FROM Usuarios WHERE Activado = 1 AND Eliminado = 0 AND IdUsuario = '$tipop'");
        $us = mysqli_fetch_array($usuarios);
        $nombreu = $us['Nombre'].' '.$us['Apellidos'];
        $nombreu = strtoupper($nombreu);
        $tituloReporte = "REPORTE F.C.E ".$nombreu;
        $titulosColumnas = array('Fecha');
        
      /*   $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A2:I2'); */

                  /* 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); 
        $fila = $resultado->fetch_array();     
        $cobtot = $tefe + $ttar + $tch;

        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A2','DÍA')
                    ->setCellValue('A3','Efectivo disponible al inicio del día')

                    ->setCellValue('A6','Ventas')
                    ->setCellValue('A7','Ingresos en efectivo')
                    ->setCellValue('A8','cobranza en efectivo')
                    ->setCellValue('A9','Cobranza tarjeta')
                    ->setCellValue('A10','cobranza deposito a cta/cheque')
                    ->setCellValue('A11','cobranza total')

                    ->setCellValue('A13','notas pagadas')
                    ->setCellValue('A14','notas con anticipo')
                    ->setCellValue('A15','Saldos cobrados')
                    ->setCellValue('A16','Cobranza total')
                    ->setCellValue('A17','Venta  mostrador')
                    ->setCellValue('A18','Cobranza total')

                    ->setCellValue('A20','Notas con saldo pendiente')

                    ->setCellValue('A22','facturas emitidas pagada')
                    ->setCellValue('A23', 'Total factura venta mostrador')
                    ->setCellValue('A24','Total a depositar')
                    
                    ->setCellValue('A26','Egresos en efectivo')
                    ->setCellValue('A27','Retiro Efectivo')
                    ->setCellValue('A28','Depositos Clientes')
                    ->setCellValue('A29','Paqueteria')
                    ->setCellValue('A30','Propinas')
                    ->setCellValue('A31','Insumos')
                    ->setCellValue('A32','Agua / Hielo')
                    ->setCellValue('A33','Varios')
                    ->setCellValue('A34','Basura')
                    ->setCellValue('A36','OTROS EGRESOS EN EFECTIVO')
                    ->setCellValue('A37','Total Egresos en efectivo')
                    
                    ->setCellValue('A39','Posición de Efectivo')
                    ->setCellValue('A40','Diferencia');
        // Se agregan los titulos del reporte
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('B2',$dia)
                    ->setCellValue('B3',$totali)

                    ->setCellValue('B6',$fila['TotalV'])

                    ->setCellValue('B8',$tefe)
                    ->setCellValue('B9',$ttar)
                    ->setCellValue('B10',$tch)
                    ->setCellValue('B11',$cobtot)

                    ->setCellValue('B13',$rpd['Pagado'])
                    ->setCellValue('B14',$rad['Anticipo'])
                    ->setCellValue('B15',$rsc['Saldos'])
                    ->setCellValue('B16',$rct['Cobranza'])
                    ->setCellValue('B17',$rvm['VentaM'])
                    ->setCellValue('B18',($rct['Cobranza'] + $rvm['VentaM']))

                    ->setCellValue('B20',$rsp['SaldoPen'])

                    ->setCellValue('B22',$rfe['Facturas'])
                    ->setCellValue('B23', ($rct['Cobranza'] - $rfe['Facturas'] ) )
                    ->setCellValue('B24',($rct['Cobranza'] - $ttar - $tch))
                    
                    ->setCellValue('B27',$reg['Retiro'])
                    ->setCellValue('B28',$reg['Depositos'])
                    ->setCellValue('B29',$reg['Paqueteria'])
                    ->setCellValue('B30',$reg['Propinas'])
                    ->setCellValue('B31',$reg['Insumos'])
                    ->setCellValue('B32',$reg['Agua'])
                    ->setCellValue('B33',$reg['Varios'])
                    ->setCellValue('B34',$reg['Basura'])
                    ->setCellValue('B36',$reg['Otros'])
                    ->setCellValue('B37',$reg['Egresos'])
                    
                    ->setCellValue('B39',$rar['Saldo'])
                    ->setCellValue('B40',$dif);

        

        $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:B40')->applyFromArray($estiloTituloReporte);      
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A3:B40");
        $objPHPExcel->getActiveSheet()->getStyle('B3:B40')->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 FEC');

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