<?php
include_once 'code/init.php';
print_header ('', '', $BodyX );
// echo " + ".$_SESSION['admin']."+";
$agence=$_SESSION['agence'];
// for ($l = 'A' ; strlen($l) == 1 ; $l++)
	// echo "<a href=\"a.php?col=-2&val=$l\">$l</a> " ;
	// echo "<a href=\"a.php\">Voir la liste complète</a> " ;	
	
	
	// echo "<pre>" ; 
	// print_r($_GET) ; 
	// echo "<pre>" ; 
?>





<form action="" method="get" style="margin-top:10px">

<?php

if($_GET['client_actif']=='on' || $_GET['client_actif']=='off' ) $_SESSION['client_actif']=$_GET['client_actif'];
	$where1="";
	$where2="";
	$selected="";
if($_GET['idbureausel'] && !empty($_GET['idbureausel']) )
{
   if($_GET['idbureausel']>0)
	{
		$idbureausel=$_GET['idbureausel'];
		$tabbursel=dbi_fetch_row((dbi_query('select * from bureau where id_bureau='.$_GET['idbureausel'].' and agence_id='.$agence.''))) ;
		$id_bureau=$tabbursel['id_bureau'];
		$nom_bureau=$tabbursel['nom_agence'];
		$where1=" AND ID_Bureau != $id_bureau ";
		$where2=" AND client_bureau = $id_bureau ";
    }
	elseif($_GET['idbureausel']=="INDEFINI")
	{
	$id_bureau=$_GET['idbureausel'];
	$where2=" AND client_bureau='0' ";
	$selected='selected="selected"';
	}
}
?>

	<br><input type="radio" Onclick="submit()" value="on" name="client_actif" <?=($_SESSION['client_actif'] == 'on' || !isset($_SESSION['client_actif']))  ? 'checked' : ''?> >Prospect actifs
	<input type="radio" Onclick="submit()" value="off" name="client_actif" <?=$_SESSION['client_actif'] == 'off' ? 'checked' : ''  ?>>Tous
</form>


<script language="Javascript">
   function GetIdBureau()
   {
		var idbursel="";
  	  idbureausel = document.bureau_form.client_bureau.value ;
	if( idbureausel > 0 || idbureausel=="INDEFINI" )
	  window.location = "prospPP.php?idbureausel="+idbureausel;
	  	else
	window.location = "prospPP.php";
   }
</script>

<form action="" method="get" style="margin-top:10px">

<?php
// Zones :
// for ($i = 1 ; $i <= 12 ; $i++)
	// printf('<input type="checkbox" name="zone[]" value="%1$X"%2$s> %1$d ', $i, !empty($_GET['zone']) && in_array(strtoupper(dechex($i)), $_GET['zone']) ? ' checked' : '') ;

// Lister les intervenants actifs ou non actifs.	
if($_GET['client_actif']=='on' || $_GET['client_actif']=='off' ) $_SESSION['client_actif']=$_GET['client_actif'];
	$where1="";
	$where2="";
	$selected="";
if($_GET['idbureausel'] && !empty($_GET['idbureausel']) )
{
   if($_GET['idbureausel']>0)
	{
		$idbureausel=$_GET['idbureausel'];
		$tabbursel=dbi_fetch_row((dbi_query('select * from bureau where id_bureau='.$_GET['idbureausel'].' and agence_id='.$agence.''))) ;
		$id_bureau=$tabbursel['id_bureau'];
		$nom_bureau=$tabbursel['nom_agence'];
		$where1=" AND ID_Bureau != $id_bureau ";
		$where2=" AND client_bureau = $id_bureau ";
    }
	elseif($_GET['idbureausel']=="INDEFINI")
	{
	$id_bureau=$_GET['idbureausel'];
	$where2=" AND client_bureau='0' ";
	$selected='selected="selected"';
	}
}
?>

	<!--<br><input type="radio" Onclick="submit()" value="on" name="client_actif" <?//=($_SESSION['client_actif'] == 'on' || !isset($_SESSION['client_actif']))  ? 'checked' : ''?> >Clients actifs
	<input type="radio" Onclick="submit()" value="off" name="client_actif" <?//=$_SEION['client_actif'] == 'off' ? 'checked' : ''  ?>>Tous -->

	
	
	<br/><br/><br/><br/><br/>
	
	</form>
<?php
if(!empty($_SESSION['admin'])){
?>

<table style="position:absolute; top:35px;  right:20px;">
<form id="bureau_form" name="bureau_form" method="POST" action="">
		<tr>
				<td align="left" valign="top" class="td_listing0" >Bureau :</td>
				<td align="left" valign="top" class="td_listing0">
				<select id="client_bureau" name="client_bureau" onchange="GetIdBureau()";>
				<option selected="selected" value=""> TOUS </option>
				<?php if(isset($id_bureau) && isset($nom_bureau)) { ?>
						<option selected="selected" value="<?=$id_bureau ?>"><?=$nom_bureau?></option>
				<?php } ?>

					<?php 
							$Reqbureau=dbi_query("select * from bureau where agence_id=$agence $where1") or die(mysql_error());
							while($listbureau=dbi_fetch_row($Reqbureau)){ ?>
				<option value="<?php echo $listbureau['id_bureau']?>"><?php echo $listbureau['nom_agence'] ?></option>
							<?php } ?>
				<option <?=$selected ?> value="INDEFINI">INDEFINI</option>
				</select>
				  </td>
			</tr>
</form>
</table>

<?php }
elseif($bureau)
{
$where2=" AND client_bureau = $bureau ";
}
if (empty($_GET['val']) || !isset($_GET['col']))
{
	$_GET['col'] = -1 ;
	$_GET['val'] = '' ;
}
error_reporting(E_ALL|E_STRICT) ;
$cols = array('client_nom', 'client_cp', 'client_ville', -1 => '', -2 => 'client_nom') ;
$soundex = array (1, 0, 1, -1 => 2, -2 => 3) ;
$tel = array('client_teldom', 'client_teldom', 'client_telport1', 'client_telpro') ;
$s = '$s' ;
$queries = array("(SELECT *, 1 AS exact, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." WHERE %s = '%s' AND client_genre NOT LIKE 'FORMATION' $where2 AND agence_id=$agence%3\$s ".( isset($_SESSION['client_actif'])&&$_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' AND   client_prospect ='1' ").") UNION (SELECT *, 0 AS exact, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." WHERE %1\$s LIKE '%%%2$\s%%' AND %1\$s != '%2\$s' AND client_genre NOT LIKE 'FORMATION' $where2 AND agence_id=$agence%3\$s ".( isset($_SESSION['client_actif'])&&$_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' AND   client_prospect ='1' ").")", "(SELECT *, 1 AS exact, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." WHERE %s = '%s' AND client_genre NOT LIKE 'FORMATION' $where2 AND agence_id=$agence%3\$s ".( isset($_SESSION['client_actif'])&&$_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' AND   client_prospect ='1'").") UNION (SELECT *, 0 AS exact, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." WHERE (%1\$s LIKE '%%%2\$s%%' OR soundex(%1\$s) = soundex('%2\$s')) AND %1\$s  != '%2\$s' AND client_genre NOT LIKE 'FORMATION' AND agence_id=$agence%3\$s ".( isset($_SESSION['client_actif'])&&$_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' AND   client_prospect ='1' ")." )", "SELECT *, 1 AS exact, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." WHERE client_prospect ='1' AND agence_id=$agence%3\$s AND client_genre NOT LIKE 'FORMATION' $where2 ".( isset($_SESSION['client_actif'])&&$_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' AND   client_prospect ='1' "), "SELECT *, 1 AS exact, IF(client_genre='SERENA',1,0) as serena, IF(client_genre='FORMATION',1,0) as formation FROM ".TABLE_CLIENT." WHERE %1\$s LIKE '%2\$s%%' AND client_genre NOT LIKE 'FORMATION' AND agence_id=$agence%3\$s $where2".( isset($_SESSION['client_actif'])&&$_SESSION['client_actif']=='off' ? "" : " AND client_actif='0' AND   client_prospect ='1' ")." ") ;
$tri="client_id, client_nom, client_adr1, exact";
$ordre='desc';
if(!empty($_GET['tri']) && !empty($_GET['ordre']))
{
$tri='client_'.$_GET['tri'].' '.$_GET['ordre'];
	if($_GET['ordre']=='asc')
		$ordre='desc';
	else
		$ordre='asc';
}
else
{
$tri=" client_nom asc ";
}

$q = dbi_query(sprintf($queries[$soundex[$_GET['col']]],$cols[$_GET['col']] , $_GET['val'], !empty($_GET['zone']) ? ' AND client_zones_id REGEXP "['.join('', $_GET['zone']).']"' : '').'  ORDER BY '.'client_prospect asc,'.' '.$tri.'') ;
 
 
 // $q = dbi_query(" select * from client where client_actif =  '0'
// AND client_prospect =  '1'
// ORDER BY client_prospect ASC  ") ;
 
  $nombre=0;
 ?>
 <div id="tab">
<TABLE width=100%>
<thead>
<TR>

  <th ><A href="prospPP.php?tri=nom&ordre=<?php echo $ordre?>"style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;text-transform : uppercase;font-size: 14px;" >Nom</A><SPAN class="sort">&uarr;</SPAN></th>
  <th><A href="prospPP.php?tri=cp&ordre=<?php echo $ordre?>" style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;text-transform : uppercase;font-size: 14px;" >Bureau</A></th>

  <th nowrap><A href="prospPP.php?tri=id&ordre=<?php echo $ordre?>" style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;text-transform : uppercase;font-size: 14px;" >Montant devis</A></th>
<th nowrap><A href="prospPP.php?tri=id&ordre=<?php echo $ordre?>" style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;text-transform : uppercase;font-size: 14px;" > Type de prestation </A></th>
<th nowrap><A href="prospPP.php?tri=id&ordre=<?php echo $ordre?>" style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;text-transform : uppercase;font-size: 14px;" > Date de la demande </A></th>

<th nowrap><A href="prospPP.php?tri=id&ordre=<?php echo $ordre?>" style="cursor:pointer; font-weight:bold;color: white; text-decoration:none;text-transform : uppercase;font-size: 14px;" >N° Client</A></th>

  </TR>
</thead>
    <tbody>
<?
	$compteur=0;
	$previous=1;
	$serena=0;
	$prospect=0;
	while($c = dbi_fetch_row($q)) {
	
		$next=$c['exact'];
		if (!($compteur % 2)) $style='td_listing0';
		else $style='td_listing1';
		
		if (($previous)&&!($next))
			echo '<br/><b>Orthographe approchée</b>';

		$icone  = '<img src="images/icones/client.png" width="16" height="16" border="0" alt="">';
		
		$prospect=$c['client_prospect'];
		
		$serena=$c['serena'];
		if (($serena)&&!($serenap)) echo '<tr><td></td><td><h2>Serena</h2></td></tr>';
		if ($serena) $icone  = '<img src="images/icones/serena.gif" width="16" height="16" border="0" alt="">';
		
		$formation=$c['formation'];
		if (($formation)&&!($formationp)) echo '<td></td><td><h2>Formation</h2></td></tr>';
		if ($formation) $icone  = '<img src="images/icones/icon-16-default.png" width="16" height="16" border="0" alt="">';
list($bur) = dbi_fetch_row(dbi_query(" SELECT nom_agence FROM  bureau  where id_bureau = ".$c['client_bureau'] )) ;
  
		?>

<TR>
  <TD><A href="top.php?link=prospecto&amp;page=aaa_pross&amp;id=<?=$c['client_id']?>" target="ident"><?=strtoupper($c['client_nom']).' '.ucwords(strtolower($c['client_pnom']))?></A></TD>
  
  <TD><?=$bur?></TD>
  <TD><?=$c['Client_montant_devis']?></TD>
  <?
  
  
 
  
  if($c['Client_devis_type']== 1)
  {
	  $prest = " Prestation ponctuelle" ;
  }
  elseif($c['Client_devis_type']== 2)
  {
	$prest = "Prestation  r&eacute;guli&egrave;re"   ; 
  }
  else
  {
	$prest ="" ;   
  }
  ?>
  <TD><?=$prest?></TD>
  <TD><?=int2date($c['Client_date_demande'])?></TD>
 
<TD nowrap><A href="top.php?link=client&amp;page=aaa_pross&amp;id=<?=$c['client_id']?>" target="ident"><?=sprintf('%05d',$c['client_id'])?></A></TD>

</TR>
<?
$compteur++;
$previous=$c['exact'];
$serenap=$c['serena'];
$formationp=$c['formation'];
$prospectp=$c['client_prospect'];
$nombre++;

} ?>
<?php

?>
    </tbody>
</table>
</div>
<br/>
<?php
// $nombre=mysql_num_rows($q);

?>



</body>
</html>