<?php

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Reporte_Clientes_sin_ventas.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"];
  



	 $cdgtipven = $_GET["combotipoventa"];
	 $cdgcli    = $_GET["cdgcli"];
	 $tipoven    = $_GET["tipovenc"];
	 $gesven    = $_GET["gesven"];
	 
	 
	 
	$where="";
$where2="1=1";
$where3="1=1";
if ($cdgcli <> "") { $where.=" AND cdgcli='$cdgcli'"; }
if ($gesven <> "") { $where.=" AND gesven='$gesven'"; }

if ($cdgtipven <> "") { $where2.=" AND cdgtipven='$cdgtipven'"; }
if ($cdgtipven <> "") { $where3.=" AND cdgtipven='$cdgtipven'"; }



if ($tipoven <> ""){


if ($tipoven == "8DIAS"){ $where3.="  AND fecha >= DATE(DATE_ADD(now(), INTERVAL - 8 DAY)) "; }
if ($tipoven == "15DIAS"){ $where3.=" AND fecha >= DATE(DATE_ADD(now(), INTERVAL - 15 DAY)) "; }
if ($tipoven == "30DIAS"){ $where3.=" AND fecha >= DATE(DATE_ADD(now(), INTERVAL - 30 DAY)) "; }
if ($tipoven == "45DIAS"){ $where3.=" AND fecha >= DATE(DATE_ADD(now(), INTERVAL - 45 DAY))"; }
if ($tipoven == "2MESES"){ $where3.=" AND fecha >= DATE(DATE_ADD(now(), INTERVAL - 2 MONTH)) "; }

//if ($tipoven == "VENCIDOS"){ $where2.=" AND feclot <= CURRENT_DATE() ";}
}



 if ($gesven=="") {
	 
	 $nomgesven="TODOS";
	 
	     } else {


	   $query_codcaj="SELECT * FROM usuarios  WHERE codusu=$gesven ";
       $resultado = $mysqli->query( $query_codcaj);
       $row = $resultado->fetch_array();
        
		$nomgesven    = $row["nomcomusu"];
     }
	 
	 
	 
	 
					
?>
<!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 Clientes No FACTURAN </title>
<style type="text/css">
<!--
.Estilo1 {color: #CCCCCC}
.Estilo4 {font-size: medium}
-->
</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 Clientes Sin Ventas </font>  </div></td>
  </tr>
     
     <tr>
       <td></td>
       <td colspan="8"   border="1"><div align="center"><font size="3" >Clientes Sin Ventas Ultimos <?php echo $tipoven?>  GESTOR <?php echo $nomgesven?>  </font></div></td>
     </tr>
        <tr>
       <td></td>
    
	<td colspan="9"   border="1"></td>
	
	

  <tr>
	 
  <tr>
      <td width="1%"></td>




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

    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>FECHA </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>TIPO </strong></font></span></td>
		<td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>DIAS </strong></font></span></td>
	    <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>VENDEDOR </strong></font></span></td>
        <td width="20%" align="right" bgcolor="skyblue"><span class="Estilo4"><strong>MONTO </strong></font></span></td>

		
		
   
  </tr>
  
<?PHP
	
  $consulta= "SELECT CDGCLI,DSCCLI
 FROM web_0301 WHERE ESTCLI='ACTIVO'
  AND CDGCLI NOT IN (SELECT DISTINCT `cdgcli` FROM facturas WHERE ".$where3.")".$where." ORDER BY DSCCLI";
  
  
  
if ($resultado = $mysqli->query($consulta)) {
$count = 0;
	
	while ($dato = $resultado->fetch_assoc()) {
	
		$cdgcli  = $dato['CDGCLI'];
		$dsccli  = $dato['DSCCLI'];
		$dsccli = substr($dsccli, 0, 55); 
		
		
		// aqui averiguo quienes si han facturando en los ultimos x dias
		$sql="SELECT MAX(codfactura) AS codfactura  FROM facturas WHERE ".$where2." AND cdgcli='$cdgcli'  ORDER BY `facturas`.`codfactura` ";
		$resultadosql = $mysqli->query($sql);
		$filasql = $resultadosql->fetch_array();
		$filas_sql= $resultadosql->num_rows;
		
		 $codfactura = $filasql["codfactura"];
		
		if ($codfactura <> "") { 
		 
		 
		 $sql2="SELECT codfactura,dsccli,cdgcli ,fecha , borrado, totalfactura,estado,numfactura,cdgtipven,cdgven  FROM facturas WHERE ".$where2." AND codfactura='$codfactura' ";
		$resultadosql2 = $mysqli->query($sql2);
		$filasql2 = $resultadosql2->fetch_array();
		
		
		$numfactura = $filasql2["numfactura"];
		 $cdgtipven = $filasql2["cdgtipven"];
		 $cdgven = $filasql2["cdgven"];
		$fecha  = $filasql2['fecha'];
		$fecha= implode("-", array_reverse(explode("-", $fecha)));
		$totalfactura = $filasql2["totalfactura"];
		
		
		
			$fecha2  = $filasql2['fecha'];
			$fechaActual = date('Y-m-d'); 
			$datetime1 = date_create($fecha2);
			$datetime2 = date_create($fechaActual);
			$diferencia = date_diff($datetime1, $datetime2);
			$differenceFormat = '%a';
			$diferencia= $diferencia->format($differenceFormat);
		

		
		//echo "   ".  $cdgcli ."   ".$DSCCLI ."   ".$fecha ."   ".number_format($totalfactura,2,".",",") ;
		//	echo "<br>";	
		
		
			$consultau = "SELECT * FROM usuarios WHERE codusu='$cdgven'";
			$resultadou = $mysqli->query($consultau);
			$rowu = $resultadou->fetch_array();
			$filas_vendedor = $resultadou->num_rows;
			
			if ($filas_vendedor > 0) { 
			$vendedor  = $rowu["usu"]; }else { $vendedor  = "DESCONOCIDO"; }
		

		if ($cdgtipven == 1) { $tipo="CONTADO"; } else { $tipo="CREDITO"; } 

	 			
							
			
			

?>  

 <tr>
   <td></td>



   <td align="center"><font size=1 ><?php echo "num ".$numfactura?></font></td>
   <td align="right"><font size=1 ><?php echo $fecha?></font></td>
   <td align="left"><font size=1 ><?php echo $cdgcli." ".$dsccli ?></font></td>
   <td align="right"><font size=1 ><?php echo $tipo ?></font></td>
   <td align="right"><font size=1 ><?php echo $diferencia ?></font></td>
   <td align="right"><font size=1 ><?php echo  $vendedor ?></font></td>
   <td align="right"><font size=1 ><?php  echo number_format($totalfactura,2,".",",")?></font></td>

    
 </tr>
  
  <?php
	 
  	        
		 } 
		 
}
					}
  
  ?>
  
</table>






</body>
</html>

 <?php