<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 1);

session_cache_limiter('private');
session_start();
$com = "I";
include("DMMYSQLG.php");
include("DSA3000.php");
include('DF80000.php');
 require_once 'excel/PHPExcel.php';

	date_default_timezone_set('America/Mexico_City');

// Crea un nuevo objeto PHPExcel
$objPHPExcel = new PHPExcel();

// Establecer propiedades
$objPHPExcel->getProperties()
        ->setCreator("Rodo")
        ->setLastModifiedBy("Rodo")
        ->setTitle("Reporte de Propinas")
        ->setSubject("Reporte")
        ->setDescription("Reporte de Propinas")
        ->setKeywords("")
        ->setCategory("");

// Propiedades Celda
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);  
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(0)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(1)->setWidth(150);;
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(2)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(3)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(4)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension('1')->setRowHeight(20);  

 $style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

    $objPHPExcel->getDefaultStyle()->applyFromArray($style);
 


 //Título
		if($_POST['setipvent']!='T'){
			$dt_tipven = mysqli_fetch_array(mysqli_query($con ,"select * from ctmeser where nparte1='".$_POST['setipvent']."'"));
			$tipoventa=$dt_tipven["np_desc1"];
		}else {
			$tipoventa="TODAS";
		}	
	if($_POST['setipven']!='T'){
			$dt_tipven = mysqli_fetch_array(mysqli_query($con ,"select * from ptventa where nparte1='".$_POST['setipven']."'"));
			$pventa=$dt_tipven["np_desc1"];
		}else {
			$pventa="TODOS";
		}	
		
		
$f=5;
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValueByColumnAndRow(0, 1, "PROPINAS PAGADAS")
    ->mergeCells('A1:E1')
    ->setCellValueByColumnAndRow(0, 2,'PERIODO DEL '.$_POST['fecini'].' AL '.$_POST['fecfin'].'')
    ->mergeCells('A2:E2')
 ->setCellValueByColumnAndRow(0, 3,'PUNTO DE VENTA:'.$pventa.                        '')
    ->mergeCells('A3:E3')
->setCellValueByColumnAndRow(0, 4,'MESERO:'.       $tipoventa.                           '')
    ->mergeCells('A4:E4');	
    
    
	   $objPHPExcel->setActiveSheetIndex(0)
	    ->setCellValueByColumnAndRow(0,$f, "")
    ->setCellValueByColumnAndRow(1,$f, "Mesero")
    ->setCellValueByColumnAndRow(2,$f, "Subtotal Cta.")
  ->setCellValueByColumnAndRow(3,$f, "Subtotal Propina.")
    ->setCellValueByColumnAndRow(4,$f, "Propina")
    ->setCellValueByColumnAndRow(5,$f, "Servicio");
    



		
	//Pie de Página

	$fecIni=chfecha($_POST['fecini'],'d/m/Y','Y-m-d');
	$fecFin=chfecha($_POST['fecfin'],'d/m/Y','Y-m-d');
	$arrBus[]="(dgpemema.fec_ven between '$fecIni' and '$fecFin') AND dgpemema.cancelado!=1 and dgpemema.cancela=0 and dgpemema.pagado='S' ";	
	
	if($_POST['secodven']!='T') {
		 $arrBus[]="dgger_cajerosj.vendedor='".$_POST['secodven']."'";		
	}
	
	if($_POST['setipven']!='T') 
	{
	 	$arrBus[]=' ptventa.nparte1='.$_POST['setipven'].' ';
	}	
	
	if($_POST['setipvent']!='T') {
  		$arrBus[]=' dgpemema.cod_ven='.$_POST['setipvent'].' ';
	}else {
		$arrBus[]=' dgpemema.cod_ven!=0 ';
    }
	if($_POST['setippag']!='T') {
  		$arrBus[]=' dgger_cajerosd.tip_pag='.$_POST['setippag'].' ';
	}else {
		$arrBus[]=' dgger_cajerosd.tip_pag!=0 ';
    }
	
 	$WHERE="WHERE ".implode(" AND ",$arrBus);

 $sqlMes=mysqli_query($con ,"SELECT distinct(cod_ven) FROM dgpemema
	LEFT JOIN ctmeser ON ctmeser.nparte1=dgpemema.cod_ven
	 where fec_ven between '$fecIni' and '$fecFin'
	 AND dgpemema.cancelado!=1 and dgpemema.cancela=0 and dgpemema.pagado='S'
	  order by np_desc1");
	while($mesero=mysqli_fetch_array($sqlMes)){
$f++;
//CONCAT(dgcatven.nom_ven,' ',dgcatven.ap_pat_ven,' ',dgcatven.ap_mat_ven) as nomVen,			
	@$result = mysqli_query($con ,"
		SELECT 
			dgpemema.fol_ven,
			dgpemema.con_ven,
			dgpemema.tip_ven,
			dgpemema.fec_ven,
			dgpemema.cod_ven,
			dgpemema.iva_ven,
			ctmeser.np_desc1 as nomVen,
			dgpemema.tot_ven,
			dgpemema.imp_pag,
			dgpemema.sub_ven,
			dgpemema.por_des as descuento,
			fpos.mesa as mesa,
			ctmesas.np_desc1 as dmesa,
			ctarea.np_desc1 as  darea,
			ptventa.tip_ven as dptvta,
			dgger_cajerosj.acc_hor as hora,
			fpropina.tip as propina,
			dgpemema.servicio as servicio,
			dgger_cajerosd.tip_pag as tipag,
			dgtippag.ini_pag as descpag,
			dgpemema.cancela as cancela,
				dgtippag.desc_pag as pagotip
		FROM 
			dgpemema
			LEFT JOIN fpos ON fpos.fol_ven=dgpemema.fol_ven
			LEFT JOIN ctmesas ON ctmesas.nparte1=fpos.mesa
			LEFT JOIN ctarea ON ctarea.nparte1=ctmesas.area
			LEFT JOIN ctmeser  ON ctmeser.nparte1=dgpemema.cod_ven
			LEFT JOIN ptventa ON ptventa.nparte1=ctmesas.descripcion
			LEFT JOIN dgger_cajerosj ON dgger_cajerosj.pedido=dgpemema.fol_ven		
			LEFT JOIN  fpropina ON fpropina.fol_ven=dgpemema.fol_ven
			LEFT JOIN dgger_cajerosd ON dgger_cajerosd.n_rec=dgger_cajerosj.n_rec	
			LEFT JOIN  dgtippag ON dgtippag.tip_pag=dgger_cajerosd.tip_pag
			LEFT JOIN dgcatven ON dgpemema.cod_ven=dgcatven.cod_ven
		
		$WHERE and dgger_cajerosj.fol_unp=0 and dgpemema.cod_ven='".$mesero['cod_ven']."' 
		group by dgpemema.fol_ven
		ORDER BY 
			dgpemema.con_ven");
			
			$mesEr0=mysqli_fetch_array(mysqli_query($con ,"SELECT np_desc1 FROM ctmeser WHERE nparte1='".$mesero['cod_ven']."'"));
			
	
    
	$totp=0;
	$tots=0;
	$totc=0;
	$mesero=$mesEr0['np_desc1'];
	
	$totcprop=0;
	while($reg = mysqli_fetch_array($result))
	{
	//------suma de productos
	@$s = mysqli_query($con ,"SELECT SUM(dgpememd.pre_bas*dgpememd.can_ref) as sumat, sum(dgpememd.imp_des) as desCuento  FROM dgpememd, dgger_cajerosj, dgpemema WHERE dgpememd.cons_ven='".$reg['fol_ven']."' and dgger_cajerosj.pedido='".$reg['fol_ven']."' and dgpememd.cons_ven=dgpemema.fol_ven and dgpemema.cancelado!=1 and dgpemema.cancela=0 and dgger_cajerosj.cancelado=0 and dgpememd.cancela=0");
		echo mysqli_error($con);
		while($r = mysqli_fetch_array($s)){
		$suma=$r["sumat"];
$desCuento=$r['desCuento'];
		}
////---------------------------------	

////---------------------------------	
	$tip = $reg["fol_ven"];
	$iop = mysqli_query($con ,"select * from fpropina where fol_ven = '$tip'");
	$qwe = mysqli_fetch_array($iop); 



	//if ($qwe['tip']>0 || $reg["servicio"]>0){
		$codigo = $reg["cod_ven"];
		$rsd = mysqli_query($con ,"select * from ctmeser where nparte1 = '$codigo'");
		$djk = mysqli_fetch_array($rsd);
		$serv = $reg["servicio"];
$reg['descuento'];  //porcentaje descuento
	$desc=$suma*($reg['descuento']/100);  //cantidad descuento descuento			
	$sumtot=(($suma-$desCuento))/1.16;			//saldo	
	
if ($qwe['tip']>0 || $reg["servicio"]>0)
{
$totcprop+=(($suma-$desCuento))/1.16;


}
	
	$totcprop+=$propventot;
	
	$totv+=$sumtot;
		$totp+=$qwe['tip'];
		$tots+=$serv;
		$totc+=$sumtot;
		$porce = $sumtot;
		$taje = $qwe["tip"];
		$p = ($taje * 100);
		if ($porce==0)
		$o=0;
		else
		$o =$p / $porce;
	
	     $totsiva=$sumtot*1.16;
	      $totprop=$qwe['tip']+$reg["servicio"];
		  if ($totsiva==0)
		  {
			$por=0;
		  }else 
		  $por=($totprop*100)/$totsiva;
	
	
	// }	
	}
	
	
		 $objPHPExcel->setActiveSheetIndex(0)
	    ->setCellValueByColumnAndRow(0,$f, "")
    ->setCellValueByColumnAndRow(1,$f, $mesero)
    ->setCellValueByColumnAndRow(2,$f, '$'.number_format($totc,2,'.',','))
    ->setCellValueByColumnAndRow(3,$f, '$'.number_format($totcprop,2,'.',','))
    ->setCellValueByColumnAndRow(4,$f, '$'.number_format($totp,2,'.',','))
    ->setCellValueByColumnAndRow(5,$f, '$'.number_format($tots,2,'.',','));
    
		
		
		$totpr+=$totp;
						$totse+=$tots;
						$totve+=$totc;
$totveprop+=$totcprop;
	
	}
	$f++;
	$f++;
 $objPHPExcel->setActiveSheetIndex(0)
	    ->setCellValueByColumnAndRow(0,$f, "TOTALES")
    ->setCellValueByColumnAndRow(1,$f, " ")
    ->setCellValueByColumnAndRow(2,$f, '$'.number_format($totve,2,'.',','))
    ->setCellValueByColumnAndRow(3,$f, '$'.number_format($totveprop,2,'.',','))
    ->setCellValueByColumnAndRow(4,$f, '$'.number_format($totpr,2,'.',','))
    ->setCellValueByColumnAndRow(5,$f, '$'.number_format($totse,2,'.',','));
    
		
	
	$objPHPExcel->setActiveSheetIndex(0)->setTitle('propinas meseros');

// Establecer la hoja activa, para que cuando se abra el documento se muestre primero.
$objPHPExcel->setActiveSheetIndex(0);

// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Reporte.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

				
		

?>


