<?php

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Reporte_Facturacion.xls");

 include ("../conexion.php");	
 
   
    
       $parametros = "SELECT * FROM web_0000 ";
       $result_parametros = $mysqli->query($parametros);
       $row_para = $result_parametros->fetch_array();
     
	   $NombreComercial =  $row_para["NOMCOM"];
	   $razon         =  $row_para["RAZSOC"];
	   $Direccion       =  $row_para["DIREMP"];
	   $Cedula          =  $row_para["CEDJUR"];
	   $Telefono        =  $row_para["NUMTEL"];
	   
	   
				$exento     = "0"  ;
				$gravado    = "0"  ;
				$subtotal   = "0"  ;
				$descuento  = "0"  ;
				$impuesto   = "0"  ;
				$total      = "0"  ;
				
				
	 	 /////////////////        where para facturas ********************
		 
	   
 	 $fecini    = $_GET["fechainicio"];
	 $fecfin    = $_GET["fechafin"];
	 $cdgtipven = $_GET["combotipoventa"];
	 $cdgcli    = $_GET["cdgcli"];	 
	 
$fechainicio = implode("-", array_reverse(explode("-", $fecini))); 
$fechafin    = implode("-", array_reverse(explode("-", $fecfin))); 

	$where="1=1";
if ($cdgtipven <> "") { $where.=" AND  facturas.cdgtipven='$cdgtipven'"; }
if ($cdgcli <> "") { $where.=" AND  facturas.cdgcli='$cdgcli'"; }

if (($fechainicio<>"") and ($fechafin<>"")) {
	$where.=" AND facturas.fecha between '".$fechainicio."' AND '".$fechafin."'";
} else {
	if ($fechainicio<>"") {
		$where.=" and  facturas.fecha >='".$fechainicio."'";
	} else {
		if ($fechafin<>"") {
			$where.=" and  facturas.fecha <='".$fechafin."'";
		}
	}
}

 $fechainicio= implode("-", array_reverse(explode("-", $fecini))); 
 $fechafin= implode("-", array_reverse(explode("-", $fecfin))); 
	 
	 
	 /////////////////        where para notas de credito ********************
	 



if ($cdgcli <> "") { $where_notas.=" AND  facturas_notas.cdgcli='$cdgcli'"; }


////////////////////////////////////////////////////////////////////////////////////////


 if ($cdgcli=="") {
	   
	   	$dsccli="TODOS LOS CLIENTES";
	 
	     } else {

	 
	   $query_codcaj="SELECT * FROM web_0301 WHERE cdgcli=$cdgcli ";
       $resultado = $mysqli->query( $query_codcaj);
       $row = $resultado->fetch_array();
        
		 $dsccli    = $row["DSCCLI"];
	
     }
					
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Reporte Analitico de Ventas </title>
<style type="text/css">
<!--
.Estilo1 {color: #CCCCCC}
.Estilo4 {font-size: medium}
.Estilo6 {
	color: #000000;
	font-weight: bold;
}
.Estilo17 {color: #000000; font-weight: bold; font-size: 14px; }
-->
</style>
</head>
<body>
<table width="92%" border="0" cellspacing="0" cellpadding="0">
    
    <tr>
      <td><div align="center"></div></td>
      <td colspan="8"  ><div align="center"><font size=8 ><?php echo $NombreComercial?></font></div>
      <div align="center"></div><div align="center"></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td colspan="8" ><div align="center"><font size="3" ><?php echo $razon?></font></div></td>
    </tr>
    <tr>
      <td><div align="center"></div></td>
      <td colspan="8" ><div align="center"><font size="2" ><?php echo $Cedula; ?></font></div></td>
    </tr>
    <tr>
      <td><div align="center"></div></td>
      <td colspan="8" ><div align="center"><font size="2" ><?php echo $Direccion; ?></font></div></td>
    </tr>
    <tr>
      <td><div align="center"></div></td>
      <td colspan="8" ><div align="center"><font size="2" ><?php echo $Telefono; ?></font></div></td>
    </tr>
    <tr>
      <td><span class="Estilo1"></span></td>
      <td colspan="8" ><span class="Estilo1"></span></td>
    </tr>
  <tr>
     <td></td>
 <td colspan="8"  bgcolor="skyblue"><div align="center"><strong><font size="3" >Reporte Analitico de Ventas</font>  </div></td>
  </tr>
     
     <tr>
       <td></td>
       <td colspan="8"   border="1"><div align="center"><font size="3" >Fecha del <?php echo $fecini; ?> Al <?php echo $fecfin; ?> -  <?php echo $dsccli; ?></font></div></td>
     </tr>
     
  <tr>
      <td width="1%"></td>


    <td width="20%" align="center" bgcolor="skyblue" ><span class="Estilo4"><strong>Codigo</strong></font></span></td>
    <td width="30%" align="center" bgcolor="skyblue" ><span class="Estilo4"><strong>Cliente</strong></font></span></td>
    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Sub.Exento </strong></font></span></td> 
	    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Sub.Gravado </strong></font></span></td>
	    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Subtotal </strong></font></span></td>
        <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Descuento </strong></font></span></td>
		<td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Impuesto </strong></font></span></td>
        <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Total </strong></font></span></td>
  </tr>
  
<?PHP
		
        ////////////////////////////////////     Agrupa Clientes con codigo ///////////////////////////////////////////////////////////////////////
				
				
					$sel_resultado="SELECT  fecha, codfactura, numfactura, cdgcli, dsccli, pladia, sum(totalfactura) as totalfactura , sum(subtot) as subtot, sum(destot) as destot, sum(impven) as impven, sum(subexe) as subexe ,sum(subgra) as subgra   FROM facturas WHERE borrado=0 AND cdgcli <> 9999  AND ".$where. " GROUP BY cdgcli ORDER BY dsccli ASC "  ;
					
		if ($resultado = $mysqli->query($sel_resultado)) 		  
  		while ($dato = $resultado->fetch_assoc()) {     //  si encuentra datos
		
				$codfactura =$dato['codfactura'];
				$numfactura =$dato['numfactura'];
				$fecha = $dato['fecha'];
 				$fecha= implode("-", array_reverse(explode("-", $fecha))); 		
				$cdgcli = $dato['cdgcli'];
				$cdgcli=sprintf("%04d",$cdgcli); 
				$dsccli = $dato['dsccli'];
				//$dsccli = substr($dsccli, 0, 40);
				$pladia = $dato['pladia'];
				$totalfactura = number_format($dato['totalfactura'], 2);
				$subexe = number_format($dato['subexe'], 2);
				$subgra = number_format($dato['subgra'], 2);
				$subtot = number_format($dato['subtot'], 2);
				$impven = number_format($dato['impven'], 2);
				$destot = number_format($dato['destot'], 2);

			
			

?>  

 <tr>
   <td></td>
   <td align="center"><font size=1 ><?php echo $cdgcli?></font></td>
   <td align="left"><font size=1 ><?php echo $dsccli?></font></td>
   <td align="right"><font size=1 ><?php echo $subexe?></font></td>
   <td align="right"><font size=1 ><?php echo $subgra ?></font></td>
   <td align="right"><font size=1 ><?php echo  $subtot ?></font></td>
   <td align="right"><font size=1 ><?php echo $destot?></font></td>
   <td align="right"><font size=1 ><?php echo $impven ?></font></td>
   <td align="right"><font size=1 ><?php echo $totalfactura ?></font></td>
 </tr>
  
  <?php

  	        
		 } 
		 
		 
		 
	   ////////////////////////////////////     Agrupa Clientes con sin Codigo  (Tipo  9999  ) ///////////////////////////////////////////////////////////////////////
 

				
					$sel_resultado="SELECT  fecha, codfactura, numfactura, cdgcli, dsccli, pladia, sum(totalfactura) as totalfactura , sum(subtot) as subtot, sum(destot) as destot, sum(impven) as impven, sum(subexe) as subexe ,sum(subgra) as subgra   FROM facturas WHERE borrado=0 AND cdgcli = 9999  AND ".$where. " GROUP BY dsccli ORDER BY dsccli ASC "  ;
					
		if ($resultado = $mysqli->query($sel_resultado)) 		  
  		while ($dato = $resultado->fetch_assoc()) {     //  si encuentra datos
		
				$codfactura =$dato['codfactura'];
				$numfactura =$dato['numfactura'];
				$fecha = $dato['fecha'];
 				$fecha= implode("-", array_reverse(explode("-", $fecha))); 		
				$cdgcli = $dato['cdgcli'];
				$cdgcli=sprintf("%04d",$cdgcli); 
				$dsccli = $dato['dsccli'];
				//$dsccli = substr($dsccli, 0, 40);
				$pladia = $dato['pladia'];
				$totalfactura = number_format($dato['totalfactura'], 2);
				$subexe = number_format($dato['subexe'], 2);
				$subgra = number_format($dato['subgra'], 2);
				$subtot = number_format($dato['subtot'], 2);
				$impven = number_format($dato['impven'], 2);
				$destot = number_format($dato['destot'], 2);
			
			

?>  

 <tr>
   <td></td>
   <td align="center"><font size=1 ><?php echo $cdgcli?></font></td>
   <td align="left"><font size=1 ><?php echo $dsccli?></font></td>
   <td align="right"><font size=1 ><?php echo $subexe?></font></td>
   <td align="right"><font size=1 ><?php echo $subgra ?></font></td>
   <td align="right"><font size=1 ><?php echo  $subtot ?></font></td>
   <td align="right"><font size=1 ><?php echo $destot?></font></td>
   <td align="right"><font size=1 ><?php echo $impven ?></font></td>
   <td align="right"><font size=1 ><?php echo $totalfactura ?></font></td>
 </tr>
  
  <?php

		 } 
		 	 

				
					$sel_resultado2="SELECT SUM(totalfactura) AS total , SUM(subtot) AS subtotal,  SUM(subexe) AS exento, SUM(subgra) AS gravado, SUM(destot) AS descuento , SUM(impven) AS impuesto FROM facturas   WHERE borrado=0   AND ".$where  ;
					
		if ($resultado2 = $mysqli->query($sel_resultado2)) 		  
  		while ($dato2 = $resultado2->fetch_assoc()) {     //  si encuentra datos
		 

				$exento_fac    = $dato2['exento'];
				$gravado_fac   = $dato2['gravado'];
				$subtotal_fac  = $dato2['subtotal'];
				$descuento_fac = $dato2['descuento'];
				$impuesto_fac  = $dato2['impuesto'];
				$total_fac     = $dato2['total'];
  ?>
</table>

<table width="92%" border="0" cellspacing="0" cellpadding="0">

 <tr>
   <td></td>
   <td align="center">&nbsp;</td>
   <td align="left">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
 </tr>
 <tr>

     <td width="1%"></td>
      <td align="left"><strong><font size=1 >TOTAL FACTURAS</font></strong></td>
   <td align="center"><strong><font size=1 ></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo number_format($exento_fac , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo number_format($gravado_fac, 2); ?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo number_format($subtotal_fac , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo number_format($descuento_fac , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo number_format($impuesto_fac , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo number_format($total_fac , 2); ?></font></strong></td>
 </tr>
</table>



    <?php
	 } 
		 
  ?>



<table width="92%" border="0" cellspacing="0" cellpadding="0">
    
    
     
  <tr>
    <td></td>
    <td colspan="11"  align="center" >&nbsp;</td>
  </tr>
  <tr>
      <td width="1%"></td>


    <td width="20%" align="center" bgcolor="skyblue" ><span class="Estilo4"><strong>Codigo</strong></font></span></td>
    <td width="20%" align="center" bgcolor="skyblue" ><span class="Estilo4"><strong>Cliente</strong></font></span></td>

    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Sub.Exento </strong></font></span></td> 
	    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Sub.Gravado </strong></font></span></td>
	    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Subtotal </strong></font></span></td>
        <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Descuento </strong></font></span></td>
		<td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Impuesto </strong></font></span></td>
        <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>Total </strong></font></span></td>
  </tr>
  
<?PHP
		
		//////////////////////////////////////////////    Agrupa clientes con Codigo diferente  a 9999 /////////////////////////////////////////////////

				
					$consulta_notas="SELECT  fecha, codnota, cdgcli, dsccli,  sum(totalnota) as totalnota , sum(subtot) as subtot, sum(destot) as destot, sum(impven) as impven, sum(subexe) as subexe ,sum(subgra) as subgra   FROM facturas_notas WHERE  cdgcli <> 9999 AND borrado = 0 AND  fecha  between '$fechainicio' AND '$fechafin'  GROUP BY cdgcli ORDER BY dsccli ASC "  ;
					
		if ($resultado = $mysqli->query($consulta_notas)) 		  
  		while ($dato = $resultado->fetch_assoc()) {     //  si encuentra datos
		
				$codnota   = $dato['codnota'];
				$fecha     = $dato['fecha'];
 				$fecha= implode("-", array_reverse(explode("-", $fecha))); 		
				$cdgcli    = $dato['cdgcli'];
				$cdgcli    = sprintf("%04d",$cdgcli); 
				$dsccli    = $dato['dsccli'];
				//$dsccli = substr($dsccli, 0, 40);
				$totalnota = number_format($dato['totalnota'], 2);
				$subexe    = number_format($dato['subexe'], 2);
				$subgra    = number_format($dato['subgra'], 2);
				$subtot    = number_format($dato['subtot'], 2);
				$impven    = number_format($dato['impven'], 2);
				$destot    = number_format($dato['destot'], 2);

					
			

?>  

 <tr>
   <td></td>

   <td align="center"><font size=1 ><?php echo $cdgcli?></font></td>
   <td align="left"><font size=1 ><?php echo $dsccli?></font></td>
   <td align="right"><font size=1 ><?php echo $subexe?></font></td>
   <td align="right"><font size=1 ><?php echo $subgra ?></font></td>
   <td align="right"><font size=1 ><?php echo  $subtot ?></font></td>
   <td align="right"><font size=1 ><?php echo $destot?></font></td>
   <td align="right"><font size=1 ><?php echo $impven ?></font></td>
   <td align="right"><font size=1 ><?php echo $totalnota ?></font></td>
 </tr>
  
  <?php
	
  	        
		 } 
		 
		 
		 
		 


	//////////////////////////////////////////////    Agrupa clientes con Codigo IGUAL A  9999 /////////////////////////////////////////////////




	
				
					$consulta_notas="SELECT   fecha, codnota, cdgcli, dsccli,  sum(totalnota) as totalnota , sum(subtot) as subtot, sum(destot) as destot, sum(impven) as impven, sum(subexe) as subexe ,sum(subgra) as subgra   FROM facturas_notas WHERE  cdgcli = 9999 AND borrado = 0 AND  fecha  between '$fechainicio' AND '$fechafin'  GROUP BY dsccli ORDER BY dsccli ASC "  ;
					
		if ($resultado = $mysqli->query($consulta_notas)) 		  
  		while ($dato = $resultado->fetch_assoc()) {     //  si encuentra datos
		
				$codnota   = $dato['codnota'];
				$fecha     = $dato['fecha'];
 				$fecha= implode("-", array_reverse(explode("-", $fecha))); 		
				$cdgcli    = $dato['cdgcli'];
				$cdgcli    = sprintf("%04d",$cdgcli); 
				$dsccli    = $dato['dsccli'];
				//$dsccli = substr($dsccli, 0, 40);
				$totalnota = number_format($dato['totalnota'], 2);
				$subexe    = number_format($dato['subexe'], 2);
				$subgra    = number_format($dato['subgra'], 2);
				$subtot    = number_format($dato['subtot'], 2);
				$impven    = number_format($dato['impven'], 2);
				$destot    = number_format($dato['destot'], 2);
			
			

?>  

 <tr>
   <td></td>

   <td align="center"><font size=1 ><?php echo $cdgcli?></font></td>
   <td align="left"><font size=1 ><?php echo $dsccli?></font></td>
   <td align="right"><font size=1 ><?php echo $subexe?></font></td>
   <td align="right"><font size=1 ><?php echo $subgra ?></font></td>
   <td align="right"><font size=1 ><?php echo  $subtot ?></font></td>
   <td align="right"><font size=1 ><?php echo $destot?></font></td>
   <td align="right"><font size=1 ><?php echo $impven ?></font></td>
   <td align="right"><font size=1 ><?php echo $totalnota ?></font></td>
 </tr>
  
  <?php

  	        
		 } 
		 
		
  					  $consulta_notas="SELECT SUM(totalnota) AS total , SUM(subtot) AS subtotal, SUM(subexe) AS exento, SUM(subgra) AS gravado, SUM(destot) AS descuento , SUM(impven) AS impuesto FROM facturas_notas WHERE   borrado = 0 AND  fecha  between '$fechainicio' AND '$fechafin'  ";
		if ($resultado2 = $mysqli->query($consulta_notas)) 		  
  		while ($dato2 = $resultado2->fetch_assoc()) {     //  si encuentra datos
		 

				$exento_not    = $dato2['exento'];
				$gravado_not   = $dato2['gravado'];
				$subtotal_not  = $dato2['subtotal'];
				$descuento_not = $dato2['descuento'];
				$impuesto_not  = $dato2['impuesto'];
				$total_not     = $dato2['total'];
  ?>
</table>

<table width="92%" border="0" cellspacing="0" cellpadding="0">

 <tr>
   <td></td>

   <td align="center">&nbsp;</td>
   <td align="left">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
 </tr>
 <tr>

     <td width="1%"></td>
      <td align="left"><strong><font size=1 >TOTAL NOTAS</font></strong></td>
   <td align="center"><strong><font size=1 ></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo  number_format($exento_not , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo  number_format($gravado_not , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo  number_format( $subtotal_not , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo  number_format($descuento_not , 2);?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo  number_format($impuesto_not , 2); ?></font></strong></td>
   <td align="right"><strong><font size=1 ><?php echo  number_format($total_not , 2); ?></font></strong></td>
 </tr>
</table>

 



    <?php
	
	
	 } 
	
	
				$exento = $exento_fac - $exento_not  ;
				$gravado = $gravado_fac - $gravado_not  ;
				$subtotal =$subtotal_fac - $subtotal_not  ;
				$descuento  = $descuento_fac - $descuento_not   ;
				$impuesto =  $impuesto_fac - $impuesto_not ;
				$total = $total_fac - $total_not  ;
  ?>
  
  
  
  <table width="92%"  border="0" cellspacing="0" cellpadding="0">

 <tr>
   <td></td>

   <td align="center">&nbsp;</td>
   <td align="left">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
   <td align="right">&nbsp;</td>
 </tr>
 <tr>

     <td width="1%" class="Estilo17"></td>
      <td align="left" class="Estilo6"><span class="Estilo17">TOTALES</span></td>

   <td align="center" class="Estilo17">&nbsp;</td>
   <td align="right" class="Estilo6"><span class="Estilo17"><?php echo number_format($exento  , 2);?></span></td>
   <td align="right" class="Estilo6"><span class="Estilo17"><?php echo number_format($gravado  , 2);?></span></td>
   <td align="right" class="Estilo6"><span class="Estilo17"><?php echo number_format($subtotal , 2); ?></span></td>
   <td align="right" class="Estilo6"><span class="Estilo17"><?php echo number_format($descuento  , 2);?></span></td>
   <td align="right" class="Estilo6"><span class="Estilo17"><?php echo number_format($impuesto , 2); ?></span></td>
   <td align="right" class="Estilo6"><span class="Estilo17"><?php echo number_format($total  , 2); ?></span></td>
 </tr>
</table>


</body>
</html>