<?php
 
session_start();
include("DMMYSQLG.php");
//include("DSA1000.php");
include("DMMYSQLG.php");
include("DF30000.inc");
include("DFJQUERY.inc");
include("DF80000.php");
?>
<html>
<link rel="stylesheet" href="css/estilo1.css" type="text/css"/>
<script type="text/javascript" language="JavaScript1.2" src="DSSCRIPT.js"></script>
<script language="javascript">
$(document).ready(function(){
	/*AutoSugestion*/
	var idRetVal2="id_cli";
	var idRetDes2="nom_cli";
	

	$("#"+idRetDes2).autocomplete('DYCLIBASCPS.php',{
		minChars: 1,                 //Caracteres Minimos antes de autocompletar
		//max:3                        //maximo de resultados mostrados, se manda como limit en autosugestion
		delay: 200,                  //Retardo de autocompletar 
		width: 600,                  //ancho del cuadro de despliegue
		highlight: false,            //Resalta letras coincidentes
		autoFill: false,             //autocompleta en texto la posible palabra
		scroll: true,				 //si cuadro tiene barra scroll
		scrollHeight: 300,           //alto del cuadro para el scroll
		formatItem:formatItem,	     //formato de presentacion de las posibles busquedas
		extraParams:{typeRequest:'ac',tipBusCat:'nom'}     //Parametros Extra a Mandar en autosugestion
	});
	$("#"+idRetVal2).autocomplete('DYCLIBASCPS.php',{
		minChars: 1,                 //Caracteres Minimos antes de autocompletar
		//max:3                        //maximo de resultados mostrados, se manda como limit en autosugestion
		delay: 200,                  //Retardo de autocompletar 
		width: 600,                  //ancho del cuadro de despliegue
		highlight: false,            //Resalta letras coincidentes
		autoFill: false,             //autocompleta en texto la posible palabra
		scroll: true,				 //si cuadro tiene barra scroll
		scrollHeight: 300,           //alto del cuadro para el scroll
		formatItem:formatItem,       //formato de presentacion de las posibles busquedas
		formatResult:function(row){  //formato de seleccion de busqueda
			return row[1];
		},	                        
		extraParams:{tipBusCat:'id',typeRequest:'ac'}     //Parametros Extra a Mandar en autosugestion
	});
	$("#"+idRetVal2).result(function(event, data, formatted){
		$('#'+idRetDes2).val(data[0]);
		$('#'+idRetDes2).focus();
	});	
	$("#"+idRetDes2).result(function(event, data, formatted){
		$('#'+idRetVal2).val(data[1]);
	});		
	function formatItem(row) {
		return " (<strong>" + row[1] + "</strong>)"+row[0]+", RFC:"+row[2]+", SUC:"+row[3];
	}	
	/**************/
});
</script>


<body>
<div align="center">
  <form name="form1" method="post" action="">
    <table  align="center">
      <tr>
			<th>COMISION X NIVEL </th>
      </tr>
      <tr>
        <td>     <span class="titulo"><span class="style13">Del 
			<input name="fecini" type="text" class="emplea" id="fecini" size="15" 
			<?php
 
				if(isset($_POST['fecini']))
			  		echo 'value="'.$_POST['fecini'].'"'; 
			  	else
			  		echo 'value="'.date('d/m/Y').'"'; 
			?>>
          <input name="botcal1" id="botcal1" value=" ... " type="image" src="logos/cal4.jpg"/>
			<script type="text/javascript">
				Calendar.setup(
					{
						inputField  : "fecini",        // ID of the input field
						ifFormat    : "%d/%m/%Y",    // the date format
						button      : "botcal1"      // ID of the button
					}
				);
		  </script>		  
al
          <input name="fecfin" type="text" class="emplea" id="fecfin" size="15" 
			<?php
 
				if(isset($_POST['fecfin']))
			  		echo 'value="'.$_POST['fecfin'].'"'; 
			  	else
			  		echo 'value="'.date('d/m/Y').'"'; 
			?>>
          <input name="botcal" id="botcal" value=" ... " type="image" src="logos/cal4.jpg"/>
          <script type="text/javascript">Calendar.setup(
					{
						inputField  : "fecfin",        // ID of the input field
						ifFormat    : "%d/%m/%Y",    // the date format
						button      : "botcal"      // ID of the button
					}
				);
		  </script>
       Max. Niveles:
            <label>
            <input name="max_niv" type="text" id="max_niv" size="10" value="<?php
echo $_REQUEST['max_niv'];?>">
            </label>
           </span></span></td>
      </tr>
      <tr>
        <td><span class="titulo">Promotor:<span class="style13">
        <input name="id_cli" type="text" id="id_cli" size="10" value="<?php
echo trim($_REQUEST['id_cli']);?>" >
        <input name="nom_cli" type="text" id="nom_cli" size="60" value="<?php
echo trim($_REQUEST['nom_cli']);?>">
        </span></span></td>
      </tr>
       <tr>
        <td><div align="center">
          <input type="submit" name="Submit32" value="Buscar" openpage('DG71300_PR.php','_self')/>
        </div></td>
      </tr>
    </table>
	
  </form>
</div>
<table  align="center">
<tr>
	<td style="#0099FF"><span class="Estilo8">Nombre</span></td>
	<TD style="#0099FF"><span class="Estilo8">Puntos</span></TD>
	<td style="#0099FF"><span class="Estilo8">Nivel</span></td>
	<td style="#0099FF"><span class="Estilo8">Clientes Directos</span></td>				
	<td style="#0099FF"><span class="Estilo8">Puntos Personales</span></td>	
	<td style="#0099FF"><span class="Estilo8">Total Personal</span></td>		
	<td style="#0099FF"><span class="Estilo8">Puntos Grupales</span></td>
	<td style="#0099FF"><span class="Estilo8">Total Grupal</span></td>			
	<td style="#0099FF"><span class="Estilo8">Total General Puntos </span></td>
	<td style="#0099FF"><span class="Estilo8">Total General Venta</span></td>	
	<td style="#0099FF"><span class="Estilo8">Clasificacion</span></td>					
	<td style="#0099FF"><span class="Estilo8">Total Comision</span></td>						
</tr>
<?php


if(isset($_POST['id_cli'])){
	$cliBus=$_POST['id_cli'];
	$maxNivBus=$_POST['max_niv'];
	$fecIni=chfecha($_POST['fecini'],'d/m/Y','Y-m-d');
	$fecFin=chfecha($_POST['fecfin'],'d/m/Y','Y-m-d');	
	if($cliBus=="0" || $cliBus==""){
		$sq_pemema=mysqli_query($con ,"SELECT dgpemema.cod_cli,dxbascps.codigo FROM dgpemema LEFT JOIN dgcatcli ON dgcatcli.cod_cli=dgpemema.cod_cli left join dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE fec_ven BETWEEN '$fecIni' AND '$fecFin' group by dgpemema.cod_cli");
	}else{
		$sq_pemema=mysqli_query($con ,"SELECT dgpemema.cod_cli,dxbascps.codigo FROM dgpemema  LEFT JOIN dgcatcli ON dgcatcli.cod_cli=dgpemema.cod_cli left join dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE dxbascps.codigo='$cliBus' AND  fec_ven BETWEEN '$fecIni' AND '$fecFin' group by dgpemema.cod_cli");
	}
	$dt_catcliRoot=mysqli_fetch_array(mysqli_query($con ,"SELECT cod_cli,codigo FROM dgcatcli WHERE codigo='$cliBus'"));
	//echo mysqli_error($con);
	$totPunPer=getPunPer($dt_catcliRoot['cod_cli'],$fecIni,$fecFin,1,$dt_catcliRoot['cod_cli']);
	$totPunGru=getPunGru($dt_catcliRoot['cod_cli'],$fecIni,$fecFin,1,$dt_catcliRoot['cod_cli']);
	$totVenPer=getTotPer($dt_catcliRoot['cod_cli'],$fecIni,$fecFin,1,$dt_catcliRoot['cod_cli']);
	$totVenGru=getTotGru($dt_catcliRoot['cod_cli'],$fecIni,$fecFin,1,$dt_catcliRoot['cod_cli']);
	$numDirNiv=getNumDirChildsCli($dt_catcliRoot['cod_cli']);
	$dt_tipcli=mysqli_fetch_array(mysqli_query($con ,$sq="SELECT * FROM dgtipcli WHERE min_per<='$numDirNiv' AND (num_pun+num_pun_per)<=('$totPunPer'+'$totPunPer') order by min_per desc,(num_pun_per+num_pun) desc limit 1"));
	echo mysqli_error($con);
	//echo $sq;
	//$porComNiv = ;
	$arrWhere[]=" t1.codigo='$cliBus'";
	$dt_niv=mysqli_fetch_array(mysqli_query($con ,getTreeQueryBus($maxNivBus,$arrWhere," group by t1.codigo")));
	$totComCli=getTotComCli($dt_catcliRoot['cod_cli'],$fecIni,$fecFin,0,$dt_catcliRoot['cod_cli'],getPorComNiv($dt_tipcli));
	echo "
	<tr>
		<td>$dt_niv['nomLev1']</td>
		<td>".getPointsCli($dt_niv,"punCli")."</td>
		<td>".getLevelCli($dt_niv,"codLev")."</td>	
		<td>".$numDirNiv."</td>			
		<td>".number_format($totPunPer,2,'.',',')."</td>									
		<td>".number_format($totVenPer,2,'.',',')."</td>											
		<td>".number_format($totPunGru,2,'.',',')."</td>									
		<td>".number_format($totVenGru,2,'.',',')."</td>													
		<td>".number_format($totPunGru+$totPunPer,2,'.',',')."</td>															
		<td>".number_format($totVenGru+$totVenPer,2,'.',',')."</td>
		<td>".$dt_tipcli['des_tipcli']."</td>																	
		<td>".number_format($totComCli,2,'.',',')."</td>																			
	</tr>";

/*	while($dt_pemema=mysqli_fetch_array($sq_pemema)){
		//echo "ES:".$dt_pemema['codigo']."<br>";
		$arrWhere[]="t1.codigo='".$dt_pemema['codigo']."'";
		$dt_niv=mysqli_fetch_array(mysqli_query($con ,getTreeQueryBus($maxNivBus,$arrWhere," group by t1.codigo")));
		//echo mysqli_error($con);
		echo "
		<tr>
			<td>$dt_niv['nomLev1']</td>
			<td>".getPointsCli($dt_niv,"punCli")."</td>
			<td>".getLevelCli($dt_niv,"codLev")."</td>	
			<td>".number_format($totCom,2,'.',',')."</td>									
		</tr>";
		unset($arrWhere);
	}*/
}

function getLevelCli($arr,$validField){
	$level=0;
	$i=1;
	if(count($arr)>0) foreach($arr as $key=>$value){

		if("$key"=="$validField$i"){
		//echo "<br>$key :: $validField$i<br>";
			$i++;
			if(trim($value)!="") $level++;
		}
		
	}
	return $level;
}
function getPointsCli($arr,$validField){
	$sum=0;
	$i=1;
	if(count($arr)>0) foreach($arr as $key=>$value){

		if("$key"=="$validField$i"){
		//echo "<br>:    $key::$validField.$i<br>";
			$i++;
			if(trim($value)!="") $sum +=$value;
		}

	}
	return $sum;
}

function getTreeQueryBus($maxNiv,$arrWhere=NULL,$groupBy){
	//$fieldsByLevel[]=" ";
	//$joinsLevel[]=" ";
	for($i=1;$i<=$maxNiv;$i++){
		$fieldsByLevel[]="t$i.codigo as codLev$i";
		$fieldsByLevel[]="t$i.nombre as nomLev$i";
		$joinsLevel[]="	LEFT JOIN dxbascps AS t".($i+1)." ON t".($i+1).".self_codigo = t$i.codigo ";
		$arrOrder[]="t".($maxNiv-$i+1).".codigo desc";
	}
	if(count($arrWhere)>0){
		$orderBy=implode(", ",$arrOrder);
		$orderBy=" order by ".$orderBy;
		$WHERE="WHERE ".implode(" AND ",$arrWhere);
	}
	$sq="SELECT ".implode(", ",$fieldsByLevel)." FROM dxbascps AS t1 ".implode(" ",$joinsLevel)."  ".$WHERE." $orderBy limit 1";
	return $sq;
}


function getTotCli($codCli,$fecIni,$fecFin,$nivProc,$rootCli){
		$dt_pemSumCli=mysqli_fetch_array(mysqli_query($con ,$sq="
			SELECT 
				SUM(dgpememd.subtotal) as totVen,SUM(dginvent.num_pun) as totPun 
			FROM dgpememd
				INNER JOIN dginvent on dginvent.nparte1=dgpememd.nparte1 
				INNER join dgpemema on dgpemema.fol_ven=dgpememd.cons_ven 
			WHERE 
				dgpemema.cod_cli='$codCli' 
				AND dgpememd.fec_ven BETWEEN '$fecIni' AND '$fecFin' 
			GROUP BY 
				dgpemema.cod_cli
		"));

	$cli=mysqli_fetch_array(mysqli_query($con ,"SELECT dgcatcli.cod_cli,dxbascps.codigo FROM dgcatcli INNER JOIN dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE dgcatcli.cod_cli='$codCli'"));
	$childCli=mysqli_query($con ,$sq="SELECT dxbascps.codigo,dgcatcli.cod_cli,dxbascps.self_codigo FROM dxbascps INNER JOIN dgcatcli on dgcatcli.codigo=dxbascps.codigo WHERE dxbascps.self_codigo='".$cli['codigo']."'");
	while($dtChildCli=mysqli_fetch_array($childCli)){
		$totChilds +=getTotCli($dtChildCli['cod_cli'],$fecIni,$fecFin,++$nivProc,$rootCli);
	}
	return ($totChilds+$dt_pemSumCli['totVen']);
}


function getNumDirChildsCli($codCli){
	$cli=mysqli_fetch_array(mysqli_query($con ,"SELECT dgcatcli.cod_cli,dxbascps.codigo FROM dgcatcli INNER JOIN dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE dgcatcli.cod_cli='$codCli'"));
	$numcli=mysqli_fetch_array(mysqli_query($con ,"SELECT count(*) as numRows FROM dxbascps INNER JOIN dgcatcli on dgcatcli.codigo=dxbascps.codigo WHERE dxbascps.self_codigo='".$cli['codigo']."' group by dxbascps.self_codigo"));
	return $numcli['numRows'];
}

function getTotPer($codCli,$fecIni,$fecFin){
		$dt_pemSumCli=mysqli_fetch_array(mysqli_query($con ,$sq="
			SELECT 
				SUM(dgpememd.subtotal) as totVen,SUM(dginvent.num_pun) as totPun 
			FROM dgpememd
				INNER JOIN dginvent on dginvent.nparte1=dgpememd.nparte1 
				INNER join dgpemema on dgpemema.fol_ven=dgpememd.cons_ven 
			WHERE 
				dgpemema.cod_cli='$codCli' 
				AND dgpememd.fec_ven BETWEEN '$fecIni' AND '$fecFin' 
			GROUP BY 
				dgpemema.cod_cli
		"));
	return ($dt_pemSumCli['totVen']);

}

function getPunPer($codCli,$fecIni,$fecFin){
		$dt_pemSumCli=mysqli_fetch_array(mysqli_query($con ,$sq="
			SELECT 
				SUM(dgpememd.subtotal) as totVen,SUM(dginvent.num_pun*dgpememd.can_ref) as totPun 
			FROM dgpememd
				INNER JOIN dginvent on dginvent.nparte1=dgpememd.nparte1 
				INNER join dgpemema on dgpemema.fol_ven=dgpememd.cons_ven 
			WHERE 
				dgpemema.cod_cli='$codCli' 
				AND dgpememd.fec_ven BETWEEN '$fecIni' AND '$fecFin' 
			GROUP BY 
				dgpemema.cod_cli
		"));
	//echo $sq;	
	return ($dt_pemSumCli['totPun']);

}

function getTotGru($codCli,$fecIni,$fecFin,$nivProc,$rootCli){
	$cli=mysqli_fetch_array(mysqli_query($con ,"SELECT dgcatcli.cod_cli,dxbascps.codigo FROM dgcatcli INNER JOIN dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE dgcatcli.cod_cli='$codCli'"));
	$childCli=mysqli_query($con ,$sq="SELECT dxbascps.codigo,dgcatcli.cod_cli,dxbascps.self_codigo FROM dxbascps INNER JOIN dgcatcli on dgcatcli.codigo=dxbascps.codigo WHERE dxbascps.self_codigo='".$cli['codigo']."'");
	while($dtChildCli=mysqli_fetch_array($childCli)){
		$dt_pemSumCli=mysqli_fetch_array(mysqli_query($con ,$sq="
			SELECT 
				SUM(dgpememd.subtotal) as totVen,SUM(dginvent.num_pun*dgpememd.can_ref) as totPun 
			FROM dgpememd
				INNER JOIN dginvent on dginvent.nparte1=dgpememd.nparte1 
				INNER join dgpemema on dgpemema.fol_ven=dgpememd.cons_ven 
			WHERE 
				dgpemema.cod_cli='".$dtChildCli['cod_cli']."' 
				AND dgpememd.fec_ven BETWEEN '$fecIni' AND '$fecFin' 
			GROUP BY 
				dgpemema.cod_cli
		"));	
		$totChilds +=$dt_pemSumCli['totVen']+getTotGru($dtChildCli['cod_cli'],$fecIni,$fecFin,++$nivProc,$rootCli);
	}
	return ($totChilds);
}

function getPunGru($codCli,$fecIni,$fecFin,$nivProc,$rootCli){
	$cli=mysqli_fetch_array(mysqli_query($con ,"SELECT dgcatcli.cod_cli,dxbascps.codigo FROM dgcatcli INNER JOIN dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE dgcatcli.cod_cli='$codCli'"));
	$childCli=mysqli_query($con ,$sq="SELECT dxbascps.codigo,dgcatcli.cod_cli,dxbascps.self_codigo FROM dxbascps INNER JOIN dgcatcli on dgcatcli.codigo=dxbascps.codigo WHERE dxbascps.self_codigo='".$cli['codigo']."'");
	while($dtChildCli=mysqli_fetch_array($childCli)){
		$dt_pemSumCli=mysqli_fetch_array(mysqli_query($con ,$sq="
			SELECT 
				SUM(dgpememd.subtotal) as totVen,SUM(dginvent.num_pun*dgpememd.can_ref) as totPun 
			FROM dgpememd
				INNER JOIN dginvent on dginvent.nparte1=dgpememd.nparte1 
				INNER join dgpemema on dgpemema.fol_ven=dgpememd.cons_ven 
			WHERE 
				dgpemema.cod_cli='".$dtChildCli['cod_cli']."' 
				AND dgpememd.fec_ven BETWEEN '$fecIni' AND '$fecFin' 
			GROUP BY 
				dgpemema.cod_cli
		"));	
		$totChilds +=$dt_pemSumCli['totPun']+getPunGru($dtChildCli['cod_cli'],$fecIni,$fecFin,++$nivProc,$rootCli);
	}
	return ($totChilds);
}

function getTotComCli($codCli,$fecIni,$fecFin,$nivProc,$rootCli,$datNiv,$rootPun=0){
		$flagRec=true;
		$dt_pemSumCli=mysqli_fetch_array(mysqli_query($con ,$sq="
			SELECT 
				SUM(dgpememd.subtotal) as totVen,SUM(dginvent.num_pun*dgpememd.can_ref) as totPun 
			FROM dgpememd
				INNER JOIN dginvent on dginvent.nparte1=dgpememd.nparte1 
				INNER join dgpemema on dgpemema.fol_ven=dgpememd.cons_ven 
			WHERE 
				dgpemema.cod_cli='$codCli' 
				AND dgpememd.fec_ven BETWEEN '$fecIni' AND '$fecFin' AND dgpememd.total_cd>0 
			GROUP BY 
				dgpemema.cod_cli
		"));
	
	$cli=mysqli_fetch_array(mysqli_query($con ,"SELECT dgcatcli.cod_cli,dxbascps.codigo,CONCAT(dxbascps.ap_paterno,' ',dxbascps.ap_materno,' ',dxbascps.nombre) as nomCli FROM dgcatcli INNER JOIN dxbascps on dxbascps.codigo=dgcatcli.codigo WHERE dgcatcli.cod_cli='$codCli'"));
	$childCli=mysqli_query($con ,$sq="SELECT dxbascps.codigo,dgcatcli.cod_cli,dxbascps.self_codigo,CONCAT(dxbascps.ap_paterno,' ',dxbascps.ap_materno,' ',dxbascps.nombre) as nomCli FROM dxbascps INNER JOIN dgcatcli on dgcatcli.codigo=dxbascps.codigo WHERE dxbascps.self_codigo='".$cli['codigo']."'");
	if($nivProc<=$datNiv['totNiv']) $porCom=$datNiv['nivel'][$nivProc];
	else $porCom=$datNiv['nivel'][$datNiv['totNiv'];

	echo "<tr>
			<td>$cli['codigo'] $cli['nomCli']</td>
			<td>&nbsp;</td>
			<td>".($nivProc)."</td>
			<td>&nbsp;</td>
			<td>".number_format($dt_pemSumCli['totPun'],2,'.',',')."</td>
			<td>".number_format($dt_pemSumCli['totVen'],2,'.',',')."</td>
			<td colspan=\"5\">&nbsp;</td>
			<td>".number_format($dt_pemSumCli['totVen']*$porCom/100,2,'.',',')."</td>
		</tr>";
	if($rootCli!=$codCli && $rootPun<=$dt_pemSumCli['totPun'] && $nivProc>2){
		$flagRec=false;
	}	
	if((($nivProc==$datNiv['totNiv'] && $datNiv[$nivProc]['es_rec']==1) || $nivProc<$datNiv['totNiv']) && $flagRec){
		while($dtChildCli=mysqli_fetch_array($childCli)){
			$auxTotChild=getTotComCli($dtChildCli['cod_cli'],$fecIni,$fecFin,($nivProc+1),$rootCli,$datNiv,$rootPun);
			$totChilds +=$auxTotChild;
		}
	}
	//echo "Com: $porCom <br>";
	return ($totChilds+$dt_pemSumCli['totVen']*$porCom/100);
}


function getPorComNiv($dt_tipcli){
	$sq_tipniv=mysqli_query($con ,"SELECT * FROM dgtipniv WHERE id_tipcli='".$dt_tipcli['id_tipcli']."' order by num_niv");
	$arrTipNiv['esRec']=$dt_tipcli['es_rec'];
	$arrTipNiv['numPunGru']=$dt_tipcli['num_pun'];	
	$arrTipNiv['numPunPer']=$dt_tipcli['num_pun_per'];		
	while($dt_tipniv=mysqli_fetch_array($sq_tipniv)){
		$arrTipNiv['totNiv']++;	
		$arrTipNiv['nivel'][$dt_tipniv['num_niv']=$dt_tipniv['por_com'];
	}
	return $arrTipNiv;
}
?>
</table>
</body>
</html>