<?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 ");
          $registro = "SELECT V.IdMovimiento AS 'Consecutivo',V.Fecha AS 'Fecha', V.Serie AS 'Serie', V.FolioVenta AS 'Folio' ,V.IdSucursal AS 'Sucursal',V.Total AS 'Total',V.Anticipo AS 'Anticipo',V.Saldo AS 'Saldo',V.IdUsuario,V.Descripcion,V.TipoMovimiento,V.FechaB,D.TipoFolio FROM Movimientos V 
      
              WHERE V.Activo = 1 AND V.FechaB BETWEEN '$desde' AND '$hasta' AND V.TipoMovimiento != '4' AND D.TipoFolio = '$tipop'  ORDER BY V.Fecha ASC";
        }else if($tipop == 0){
          $registro = "SELECT V.IdMovimiento AS 'Consecutivo',V.Fecha AS 'Fecha', V.Serie AS 'Serie', V.FolioVenta AS 'Folio' ,V.IdSucursal AS 'Sucursal',V.Total AS 'Total',V.Anticipo AS 'Anticipo',V.Saldo AS 'Saldo',V.IdUsuario,V.Descripcion,V.TipoMovimiento,V.FechaB FROM Movimientos V 
      
      
              WHERE V.Activo = 1 AND V.FechaB BETWEEN '$desde' AND '$hasta' AND V.TipoMovimiento != '4'   ORDER BY V.Fecha ASC";
        }
      }else if($sucbus != 0){
        if($tipop != 0){
          //$registro = mysqli_query($conexion,"SELECT * FROM factGDL JOIN  ORDER BY IdCliente ASC ");
          $registro = "SELECT V.IdMovimiento AS 'Consecutivo',V.Fecha AS 'Fecha', V.Serie AS 'Serie', V.FolioVenta AS 'Folio' ,V.IdSucursal AS 'Sucursal',V.Total AS 'Total',V.Anticipo AS 'Anticipo',V.Saldo AS 'Saldo',V.IdUsuario,V.Descripcion,V.TipoMovimiento,V.FechaB,D.TipoFolio FROM Movimientos V 
      
            JOIN Ventas D ON D.Serie = V.Serie AND D.FolioVenta = V.FolioVenta
      
              WHERE V.Activo = 1 AND V.FechaB BETWEEN '$desde' AND '$hasta' AND V.TipoMovimiento != '4' AND D.TipoFolio = '$tipop' AND V.IdSucursal = '$sucbus' AND D.Activo = 1  ORDER BY V.Fecha ASC";
        }else if($tipop == 0){
          $registro ="SELECT V.IdMovimiento AS 'Consecutivo',V.Fecha AS 'Fecha', V.Serie AS 'Serie', V.FolioVenta AS 'Folio' ,V.IdSucursal AS 'Sucursal',V.Total AS 'Total',V.Anticipo AS 'Anticipo',V.Saldo AS 'Saldo',V.IdUsuario,V.Descripcion,V.TipoMovimiento,V.FechaB FROM Movimientos V 
      
      
              WHERE V.Activo = 1 AND V.FechaB BETWEEN '$desde' AND '$hasta' AND V.TipoMovimiento != '4' AND V.IdSucursal = '$sucbus'  ORDER BY V.Fecha ASC";
        }
      }

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


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

        /* if($sucbus == 0){
            if($tipop == 0){
            $j = mysqli_query($conexion,"SELECT * FROM Movimientos WHERE FechaB BETWEEN '$desde' AND '$hasta' AND Activo = 1 AND TipoMovimiento != '4' ");
            }else if($tipop != 0){
              $j = mysqli_query($conexion,"SELECT V.TipoMovimiento,V.Total,V.Anticipo,V.MetodoPago,V.FechaB,D.TipoFolio FROM Movimientos V
                JOIN Ventas D ON D.Serie = V.Serie AND D.FolioVenta = V.FolioVenta
                WHERE V.FechaB BETWEEN '$desde' AND '$hasta' AND V.Activo = 1 AND V.TipoMovimiento != '4' AND D.TipoFolio = '$tipop'");
            }
          }else if($sucbus != 0){
            if($tipop == 0){
            $j = mysqli_query($conexion,"SELECT * FROM Movimientos WHERE FechaB BETWEEN '$desde' AND '$hasta' AND Activo = 1 AND TipoMovimiento != '4' AND IdSucursal = '$sucbus' ");
            }else if($tipop != 0){
              $j = mysqli_query($conexion,"SELECT V.TipoMovimiento,V.Total,V.Anticipo,V.MetodoPago,V.FechaB,D.TipoFolio FROM Movimientos V
                JOIN Ventas D ON D.Serie = V.Serie AND D.FolioVenta = V.FolioVenta
                WHERE V.FechaB BETWEEN '$desde' AND '$hasta' AND V.Activo = 1 AND V.TipoMovimiento != '4' AND D.TipoFolio = '$tipop' AND V.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;
                  }
              }
          
          } */
          $totalf = ($totali + $tefe) - $salida;
          $dif = $totalf - $total;
          $tf = $tefe + $tch + $ttar;
          $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,'.',',');
          $tf = number_format($tf,2,'.',',');
          
          
          $dif = number_format($dif,2,'.',',');
        $tituloReporte = "REPORTE DE MOVIMIENTOS".$ti;
        $titulosColumnas = array('FECHA', 'DESCRIPCION','VENTA','ENTRADA','SALDO','SALIDA');
        
        $objPHPExcel->setActiveSheetIndex(0)
                    ->mergeCells('A2:F2');

                  /* 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]);

        //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; */
            $ea = 0;
            $total = 0;
            $saldo = 0;
            $anticipos = 0;
            $fecha= $fila['Fecha'];
            $des = $fila['Descripcion'];
            $anticipos = $fila['Anticipo'];
            
            $tipo = $d['TipoMovimiento'];
            if($tipo == 2){
                $ea = $fila['Total'];
            }else{
                $total = $fila['Total'];
                $saldo = $fila['Saldo'];
            }
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A'.$i,  $fila['Fecha'])
                    ->setCellValue('B'.$i,  $fila['Descripcion'])
                    ->setCellValue('C'.$i,  $total)
                    ->setCellValue('D'.$i,  $anticipos)
                    ->setCellValue('E'.$i,  $fila['Saldo'])
                    ->setCellValue('F'.$i,  $ea);
                    $i++;
                    
                    


        }/* 
        $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:F2')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->applyFromArray($estiloTituloColumnas);       
        $objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:F".($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('C4:C'.($i-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('D4:D'.($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($registro);
        print_r('No hay resultados para mostrar');
        print_r($registro);
    }
?>