<?php
class RecherchesController extends Controller{
	
	
	// /*
	// * affichage desrésultats
	// * des requetes sur les individus
	// */	
// function resultats_individus() {
	
	// $this->layout = 'modal'; 
	// $this->render('individus');
	
// } // fin de fonction recherches individus
	/*
	* préparation pour les requetes sur les individus
	*/
	function individus() {
		$_SESSION['s_page'] = "search";
		$_SESSION['s_vue']  = 'recherches_individu';
		$_SESSION['s_phase'] = 'recherches_individu';
		
		//$phase = isset($this->request->params[0]) ? 1 : 0;
		$page = isset($this->request->params[0])?$this->request->params[0] : 1; 
		if ($this->request->params[0] == 'S') {$phase = 0; $page=1;} else {$phase = isset($this->request->params[0]) ? 1 : 0;}
	
		if ($phase==0) { 
			$_SESSION['s_rech_nom'] = '';
			$_SESSION['s_rech_nom_exact'] = '';
			$_SESSION['s_rech_prenom'] = '';
			$_SESSION['s_rech_prenom_exact'] = '';
			$_SESSION['s_rech_sexe'] = '';
			$_SESSION['s_rech_lieu'] = '';
			$_SESSION['s_rech_lieu_exact'] = '';
			$_SESSION['s_rech_start'] = '';
			$_SESSION['s_rech_end'] = '';
			$_SESSION['s_rech_choice'] = '';
			$_SESSION['t_recherches_resultats'] = array();
		}
			
		
		$this->loadModel('Outils');
		$this->loadModel('Recherches'); 
 
	// installation des règles de validation
		$this->Recherches->validate = array(
			'nom' => array(
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_INDIV_NOM_R0')
				),
				'R1'=>array(
					'rule' => 'notEmpty',
					'message' => decode_lng_text('SEARCH_INDIV_NOM_R1')
				)
			),
			'prenom' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_INDIV_PRENOM_R0')
				)
			),
			'lieu' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_INDIV_LIEU_R0')
				)
			),
			'start' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0' => array(
					'rule' =>  'min_4',
					'message' => decode_lng_text('SEARCH_INDIV_START_R0')
				 ),
				'R1' => array(
					'rule' =>  'max_4',
					'message' => decode_lng_text('SEARCH_INDIV_START_R1')
				 ),
				 'R2'=>array(
					'rule' => '(1|2)([0-9])+',
					'message' => decode_lng_text('SEARCH_INDIV_START_R2')
				)
			),
			'end' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0' => array(
					'rule' =>  'min_4',
					'message' => decode_lng_text('SEARCH_INDIV_END_R0')
				 ),
				'R1' => array(
					'rule' =>  'max_4',
					'message' => decode_lng_text('SEARCH_INDIV_END_R1')
				 ),
				 'R2'=>array(
					'rule' => '(1|2)([0-9])+',
					'message' => decode_lng_text('SEARCH_INDIV_END_R2')
				)
			)
		);
/*
* pour comprendre les libellés multi-langues
*/		
// "SEARCH_INDIV_END_R0" =>'La date de fin n\'est pas conforme, (quatre chiffres minimum)',
// "SEARCH_INDIV_END_R1" =>'La date de fin n\'est pas conforme, (quatre chiffres maximum)',
// "SEARCH_INDIV_END_R2" =>'La date de fin est hors période autorisée, il ne faut saisir que des chiffres entre 1000 et 2999)',
// "SEARCH_INDIV_LIEU_R0" =>'Le lieu contient des caractères non autorisés',
// "SEARCH_INDIV_NOM_R0" =>'Le patronyme contient des caractères non autorisés',
// "SEARCH_INDIV_NOM_R1"=>'Il faut indiquer un patronyme',
// "SEARCH_INDIV_PRENOM_R0" =>'Le prénom contient des caractères non autorisés',
// "SEARCH_INDIV_START_R0" =>'La date de début  n\'est pas conforme, (quatre chiffres minimum)',
// "SEARCH_INDIV_START_R1" =>'La date de début n\'est pas conforme, (quatre chiffres maximum)',
// "SEARCH_INDIV_START_R2" =>'La date de début est hors période autorisée, il ne faut saisir que des chiffres entre 1000 et 2999)',


		// raz du tableau des ereurs de validation
		$this->Form->errors	= array();
				
		//$page = isset($this->request->params[0])?$this->request->params[0] : 1; 

		// recup variable du formulaire

		$nom = $this->request->data->nom;
		$nom_exact = $this->request->data->nom_exact;
		$prenom = $this->request->data->prenom;
		$prenom_exact = $this->request->data->prenom_exact;
		$sexe = $this->request->data->sexe;
		$lieu = $this->request->data->lieu;

		$start = $this->request->data->start;
		$end = $this->request->data->end;
		$choice = $this->request->data->choice;
		if ($choice=="") {$choice="all";}
	//	$limite = $this->request->data->limite;
	//	$nb_msg_par_page = $this->request->data->nb_msg_par_page;
	
		
	
	/*
	*    Recherches des éléments à afficher après validation des éléments du formulaire
	*/
		
		if($this->Recherches->validates($this->request->data)) {
			
		// preparation de la requete phase 1
		// choix des tables à traiter
					
			// si le critere lieu est renseigné
		if (!empty($lieu)) {
			$table = 'oxg_cities';
				
			$condition = "1=1";
			$test='';
			if ($lieu_exact == true) { 		
				$test = " and oxg_cities.libelle = '".$lieu."'";
			}
			elseif (strpos($lieu, "^")!==false || strpos($lieu, "$")!==false ) {
				$test = " and oxg_cities.libelle REGEXP '".$lieu."'";
			}
			elseif (strpos($lieu, "*")!==false) {
				$lieu = str_replace("*", "%", $lieu);	
				$test = " and oxg_cities.libelle LIKE '".$lieu."'";
			}
			else {
				$test = " and oxg_cities.libelle LIKE '".$lieu."%'";			
			}

			$condition .= $test;
//$_SESSION['ind_affiche']=1;	
		
			$this->Recherches->table = $table;
			$result_cities=$this->Recherches->find(array(
					'temp'        => "city",
					'fields'      => "*",
					'conditions'  => $condition,
					'order'       => "oxg_cities.id_city ASC"
			));
		
		}
	//prise en compte du critère type évenement qui a été choisi		
					
			$table = 'oxg_events2';
				
			$condition = "oxg_events2.id_family=".$_SESSION['FAMILY'];
			
			switch ($choice) {
				case 'birth':
					$condition .= " and oxg_events2.type=51";
					break;
				case 'wedding':
					$condition .= " and oxg_events2.type=115";
					break;
				case 'death':
					$condition .= " and oxg_events2.type=77";
					break;
				case 'other':
					$condition .= " and oxg_events2.type not in('51','115','77')";
					break;
				default:
					$condition .= "";
					break;				
			}
//$_SESSION['ind_affiche']=1;	
			$this->Recherches->table = $table;
			$result_cities=$this->Recherches->find(array(
				'temp'        => "evnt",
				'fields'      => "*",
				'conditions'  => $condition,
				'order'       => "oxg_events2.id_individu ASC"
			));
				

		// preparation de la requete phase 2
		// choix des tables à traiter
		
			$table = 'oxg_individuals, oxg_patronyms';

			if (!empty($choice) or !empty($start) or !empty($end)) {
				$table .= ",evnt"; 
			}
 
			if (!empty($lieu)) {
				$table .= " ,city";
			}
			$fields  = "oxg_individuals.id_family, oxg_individuals.id_individual,oxg_individuals.sex";
			
			$condition  = "1=1";
			
			
			if (!empty($nom) or !empty($prenom) or !empty($sexe) or !empty($lieu) or !empty($start) or !empty($end) ) {
				// si le critere nom est renseigné
				if (!empty($nom)) {
					if ($nom_exact == true) { 		
						$test = " and oxg_patronyms.libelle = '".$nom."'";
					}
					elseif (strpos($nom, "^")!==false || strpos($nom, "$")!==false ) {
						$test = " and oxg_patronyms.libelle REGEXP '".$nom."'";
					}
					elseif (strpos($nom, "*")!==false) {
						$nom = str_replace("*", "%", $nom);	
						$test = " and oxg_patronyms.libelle LIKE '".$nom."'";
					}
					else {
						$test = " and oxg_patronyms.libelle LIKE '".$nom."%'";
						
					}
					
					$condition .= " and oxg_patronyms.id_patronym=oxg_individuals.id_patronym".$test;
				}	

			// si le critere prenom est renseigné
				if (!empty($prenom)) {
					if ($prenom_exact == true) { 		
						$test = " and oxg_individuals.first_name = '".$prenom."'";
					}
					elseif (strpos($prenom, "^")!==false || strpos($prenom, "$")!==false ) {
						$test = " and oxg_individual.first_name REGEXP '".$prenom."'";
					}
					elseif (strpos($prenom, "*")!==false) {
						$prenom = str_replace("*", "%", $prenom);	
						$test = " and oxg_individuals.first_name LIKE '".$prenom."'";
					}
					else {
						$test = " and oxg_individuals.first_name LIKE '".$prenom."%'";
						
					}
					
					$condition .= " and oxg_patronyms.id_patronym=oxg_individuals.id_patronym".$test;
				}
				
			// si le critere sexe est renseigné
				if (!empty($sexe)) {
					if ($sexe=="M") {
						$sex=0;
					} else {
						$sex=1;	
					}	

					$condition .= " and oxg_individuals.sex='".$sex."'";
				}
				
			// si le critere lieu est renseigné
	
				if (!empty($lieu)) {
					$condition .= " and evnt.id_individu=oxg_individuals.id_individual and evnt.id_country=city.id_country and evnt.id_zone=city.id_zone and evnt.id_city=city.id_city";
				}
			
				
				// //prise en compte du critère type évenement qui a été choisi		
				if  ($choice != 'all')  {
					$condition .= " and evnt.id_individu=oxg_individuals.id_individual";
				}
				
			// si un des critères Date est renseigné
				if (!empty($start) or !empty($end)) {
					if (empty($lieu))  { $condition .= " and evnt.id_individu=oxg_individuals.id_individual"; }
					
					if ($start<>"" and empty($end)) { $condition .= " and left(evnt.devnt,4)>=".intval($start); }
					elseif ($end<>"" and empty($start))   { $condition .= " and (left(evnt.devnt,4)+1>1 and left(evnt.devnt,4)<=".intval($end).")"; }
					else {$condition .= " and (left(evnt.devnt,4)>=".intval($start)." and left(evnt.devnt,4)<=".intval($end).")"; } 
				} 
								
				if ($_SESSION['FAMILY']!=-1)
				{
					$condition .= " and oxg_individuals.id_family=".$_SESSION['FAMILY'];
					
				}
//				echo 'family '.$_SESSION['FAMILY'];
			
//$_SESSION['ind_affiche']=1;			
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
					'fields'     => $fields,
					'conditions'  => $condition,
					'group' => "oxg_individuals.id_individual",
					'order' => "oxg_individuals.first_name ASC"
			));
//var_dump($result);
			 foreach($result as $k => $row) {
				 $individu = $this->Outils->affiche_lien_individu($row->id_individual,true,true);
				$_SESSION['t_recherches_resultats'][] = array('sex'=>$row->sex, 'individu'=>$individu);
			 }
			
		}

		} // fin du validate=ok
		
		
		$this->render('individus');
		
	} // fin de fonction recherches individus
		
	/*
	* préparation pour les requetes sur les evenements
	*/
	function events() {
		$_SESSION['s_page'] = "search";
		$_SESSION['s_vue']  = 'recherches_evenements';
		$_SESSION['s_phase'] = 'recherches_evenements';
		
		//prise en compte premier passage
		$phase = $this->request->params[0]==0 ? 0 : 1;
		$page = isset($this->request->params[0])?$this->request->params[0] : 1; 
		if ($page== 'S') {$phase= 1; $page=1;}
	
		if ($phase==0) { 
			$_SESSION['s_rech_country_evnt'] = '';
			$_SESSION['s_rech_zone_evnt'] = '';
			$_SESSION['s_rech_city_evnt'] = '';
			$_SESSION['s_rech_type__evnt'] = '';
			$_SESSION['s_rech_start_evnt'] = '';
			$_SESSION['s_rech_end_evnt'] = '';
			$_SESSION['t_recherches_resultats'] = array();
		}
			
		
		$this->loadModel('Outils');
		$this->loadModel('Recherches'); 		
		
	// recherche de la liste des type d'événements
	
		if ($phase == 0) {
			$_SESSION['t_type_evenements'] = array();
	
			$condition  = "oxg_events2.type=oxg_events_type.id_type_event ";
			$condition .= " and oxg_events_type.id_type_event IN('47','51','55','77','82','112','115','125','129','163','167')";
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
						// on cherche les types : BAPM, BIRT,BURI,DEAT,DIV,MARC,MARR,NOTE
		//	$condition .= " and oxg_events_type.code not in ('UNION','TITL','OCCU','ASSO','DSCR','EDUC','GIVN','REFN') ";
	
			$table = 'oxg_events2, oxg_events_type';
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
				'fields'     => "oxg_events_type.id_type_event as type, oxg_events_type.code as code" ,
				'conditions' => $condition,
				'group'      => "oxg_events_type.id_type_event, oxg_events_type.code"
				));
				
			foreach($result as $k => $row)	{
				$_SESSION['t_type_evenements'] [] = array('type'=>$row->type,'code'=>$row->code);
			}
			
	// recherche de la liste des pays

			$_SESSION['t_countries'] = array();
			
			$condition  = "oxg_events2.id_country = oxg_countries.id_country";
			$condition .= " and oxg_events2.type IN('47','51','55','77','82','112','115','125','129','163','167')";
			$condition .= " and oxg_events2.id_zone <> -1 and oxg_events2.id_city <> -1 ";
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
			
			$table = 'oxg_events2, oxg_countries ';
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
				'fields'     => "oxg_countries.id_country, oxg_countries.libelle as libelle, count(*) as decompte",
				'conditions' => $condition,
				'group'      => "oxg_countries.id_country, oxg_countries.libelle"
				));
				
			foreach($result as $k => $row)	{
				$_SESSION['t_countries'] [] = array('id_country'=>$row->id_country,'libelle'=>$row->libelle,'decompte'=>$row->decompte);
			}
		}		
	// recherche de la liste des événements	
	// cette recherche se fait en Ajax par le biais du module RecherchesContenuAjax.php et des fonctions de recherches dans oxygen.js
	
	
		$this->render('events');
	} // fin de fonction recherches evenements



	
	/*
	* préparation pour les requetes sur les couples
	*/
	function couples() {
		$_SESSION['s_page'] = "search";		
		$_SESSION['s_vue']  = 'recherches_couples';
		$_SESSION['s_phase'] = 'recherches_couples';
echo 'choix '.$this->request->params[0];
		$page = isset($this->request->params[0])?$this->request->params[0] : 1; 
		if ($this->request->params[0] == 'S') {$phase = 0; $page=1;} else {$phase = isset($this->request->params[0]) ? 1 : 0;}
echo 'phase '.$phase."</br>";

		if ($phase==0) { 
			$_SESSION['s_rech_nom_husband'] = '';
			$_SESSION['s_rech_nom_husband_exact'] = '';
			$_SESSION['s_rech_nom_spouse'] = '';
			$_SESSION['s_rech_nom_spouse_exact'] = '';
			$_SESSION['s_rech_start'] = '';
			$_SESSION['s_rech_end'] = '';
		}

		$this->loadModel('Outils');
		$this->loadModel('Recherches'); 
 
// installation des règles de validation
		$this->Recherches->validate = array(
			'nom_husband' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_COUPLES_NOM_HUSBAND_R0')
				),
			),
			'prenom_husband' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_COUPLES_PRENOM_HUSBAND_R0')
				),

			),
			'nom_spouse' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_COUPLES_NOM_SPOUSE_R0')
				),
	
			),
			'prenom_spouse' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0'=>array(
					'rule' => '[\* \-a-zA-Z]+',
					'message' => decode_lng_text('SEARCH_COUPLES_PRENOM_SPOUSE_R0')
				),
			),
			'start' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0' => array(
					'rule' =>  'min_4',
					'message' => decode_lng_text('SEARCH_COUPLES_START_R0')
				 ),
				'R1' => array(
					'rule' =>  'max_4',
					'message' => decode_lng_text('SEARCH_COUPLES_START_R1')
				 ),
				 'R2'=>array(
					'rule' => '(1|2)([0-9])+',
					'message' => decode_lng_text('SEARCH_COUPLES_START_R2')
				)
			),
			'end' => array(
				'R_'=>array(
					'rule' => 'Empty',
					'message' => ''
				),
				'R0' => array(
					'rule' =>  'min_4',
					'message' => decode_lng_text('SEARCH_COUPLES_END_R0')
				 ),
				'R1' => array(
					'rule' =>  'max_4',
					'message' => decode_lng_text('SEARCH_COUPLES_END_R1')
				 ),
				 'R2'=>array(
					'rule' => '(1|2)([0-9])+',
					'message' => decode_lng_text('SEARCH_COUPLES_END_R2')
				)
			)
		);
/*
* pour comprendre les libellés multi-langues
*/				
// "SEARCH_COUPLES_NOM_HUSBAND_R1"=>'Il faut indiquer un patronyme pour l\'époux',
// "SEARCH_COUPLES_NOM_HUSBAND_R0" =>'Le patronyme de l\'époux contient des caractères non autorisés',
// "SEARCH_COUPLES_PRENOM_HUSBAND_R0" => 'Le prénom de l\'époux contient des caractères non autorisés',
// "SEARCH_COUPLES_NOM_SPOUSE_R1"=>'Il faut indiquer un patronyme pour l\'épouse',
// "SEARCH_COUPLES_NOM_SPOUSE_R0" =>'Le nom de l\'épouse contient des caractères non autorisés',
// "SEARCH_COUPLES_PRENOM_SPOUSE_R0" => 'Le prénom de l\'épouse contient des caractères non autorisés',
// "SEARCH_COUPLES_END_R1" =>'La date de fin n\'est pas conforme, (quatre chiffres maximum)',
// "SEARCH_COUPLES_END_R0" =>'La date de fin n\'est pas conforme, (quatre chiffres minimum)',
// "SEARCH_COUPLES_END_R2" =>'La date de fin est hors période autorisée, il ne faut saisir que des chiffres entre 1000 et 2999)',
// "SEARCH_COUPLES_START_R0" =>'La date de début  n\'est pas conforme, (quatre chiffres minimum)',
// "SEARCH_COUPLES_START_R2" =>'La date de début est hors période autorisée, il ne faut saisir que des chiffres entre 1000 et 2999)',
// "SEARCH_COUPLES_START_R1" =>'La date de début n\'est pas conforme, (quatre chiffres maximum)',		


	// raz du tableau des ereurs de validation
		$this->Form->errors	= array();
					
	// recup variable du formulaire
		$nom_husband = $this->request->data->nom_husband;
		$nom_husband_exact = $this->request->data->nom_husband_exact;
		$prenom_husband = $this->request->data->prenom_husband;
		$prenom_husband_exact = $this->request->data->prenom_husband_exact;
		$nom_spouse = $this->request->data->nom_spouse;
		$nom_spouse_exact = $this->request->data->nom_spouse_exact;
		$prenom_spouse = $this->request->data->prenom_spouse;
		$prenom_spouse_exact = $this->request->data->prenom_spouse_exact;
		$start = $this->request->data->start;
		$end = $this->request->data->end;
		

// var_dump($this->request->data);		
	
		if($this->Recherches->validates($this->request->data)) {

		$_SESSION['t_recherches_resultats'] = array();
		
	/*
	*    Recherches des éléments à afficher après validation des éléments du formulaire
	*/
	
		// preparation de la requete
		// choix des tables à traiter
		
		// recherche des mariages de la famille  et éventuellement la plage de dates indiquées
			$table = 'oxg_events2, oxg_events';
			
			$fields  = "oxg_events2.id_family, oxg_events2.id_country as country, oxg_events2.id_zone as zone, oxg_events2.id_city as city, oxg_events2.id_individu as id_husband, oxg_events.info2 as id_spouse, oxg_events2.sPlace as place, oxg_events2.sDtEvnt as date";	
			$condition  = "oxg_events2.type= 115 ";
			$condition .= " and oxg_events2.id_event = oxg_events.id_event";
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY']." and oxg_events.id_family=".$_SESSION['FAMILY'] ;
			
		// si un des critères Date est renseigné
			if(!empty($start) or !empty($end) ){
				if ($start<>"" and empty($end)) { $condition .= " and left(oxg_events2.devnt,4)>=".intval($start); }
				elseif ($end<>"" and empty($start))   { $condition .= " and (left(oxg_events2.devnt,4)+1>1 and left(oxg_events2.devnt,4)<=".intval($end).")"; }
				else {$condition .= " and (left(oxg_events2.devnt,4)>=".intval($start)." and left(oxg_events2.devnt,4)<=".intval($end).")"; } 
			}	
// $_SESSION['ind_affiche']=1;	
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
					'temp' 		=> "mariages",
					'fields'     => $fields,
					'conditions'  => $condition,
					'order' => "id_husband ASC"
			));

// echo $_SESSION['Req_Count']."</br>";
	
		// recherche des époux 
			$table = 'oxg_individuals LEFT OUTER JOIN oxg_patronyms on oxg_individuals.id_patronym=oxg_patronyms.id_patronym';

			// si le critere nom époux est renseigné
				//if (!empty($nom_husband)) {
					$condition = "oxg_individuals.id_family=".$_SESSION['FAMILY'];
					if ($nom_husband_exact == true) { 		
						$test = " and oxg_patronyms.libelle = '".$nom_husband."'";
					}
					elseif (strpos($nom_husband, "^")!==false || strpos($nom_husband, "$")!==false ) {
						$test = " and oxg_patronyms.libelle REGEXP '".$nom_husband."'";
					}
					elseif (strpos($nom_husband, "*")!==false) {
						$nom_husband = str_replace("*", "%", $nom_husband);	
						
						$test = " and oxg_patronyms.libelle LIKE '".$nom_husband."'";
					}
					else {
						$test = " and oxg_patronyms.libelle LIKE '".$nom_husband."%'";
						
					}
					
					$condition .= $test;
				
			// si le critere prenom époux est renseigné
				if (!empty($prenom_husband) and $prenom_husband!='*') {
					if ($prenom_husband_exact == true) { 		
						$test = " and oxg_individuals.first_name = '".$prenom_husband."'";
					}
					elseif (strpos($prenom_husband, "^")!==false || strpos($prenom_husband, "$")!==false ) {
						$test = " and oxg_individuals.first_name REGEXP '".$prenom_husband."'";
					}
					elseif (strpos($prenom_husband, "*")!==false) {
						$prenom_husband = str_replace("*", "%", $prenom_husband);	
						$test = " and oxg_individuals.first_name LIKE '".$prenom_husband."'";
					}
					else {
						$test = " and oxg_individuals.first_name LIKE '".$prenom_husband."%'";
						
					}
					
					$condition .= $test;
				}
				
//$_SESSION['ind_affiche']=1;	
		
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
					'temp' => 'husbands',
					'fields'     => "oxg_individuals.id_individual as id_husband, oxg_patronyms.libelle, oxg_individuals.first_name",
					'conditions'  => $condition,
					'order' => "id_husband ASC"
			));	
//cho $_SESSION['Req_Count']."</br>";			
		// recherche des épouses			
			if ($nom_spouse !='*' and $prenom_spouse !='*') {		

				$table = 'oxg_individuals LEFT OUTER JOIN oxg_patronyms on oxg_individuals.id_patronym=oxg_patronyms.id_patronym';
		
			// si le critere nom épouse est renseigné					
				if (!empty($nom_spouse) and $nom_spouse !='*') {

					$condition = "oxg_individuals.id_family=".$_SESSION['FAMILY'];
					if ($nom_spouse_exact == true) { 		
						$test = " and oxg_patronyms.libelle = '".$nom_spouse."'";
					}
					elseif (strpos($nom_spouse, "^")!==false || strpos($nom_spouse, "$")!==false ) {
						$test = " and oxg_patronyms.libelle REGEXP '".$nom_spouse."'";
					}
					elseif (strpos($nom_spouse, "*")!==false) {
						$nom_spouse = str_replace("*", "%", $nom_spouse);	
						
						$test = " and oxg_patronyms.libelle LIKE '".$nom_spouse."'";
					}
					else {
						$test = " and oxg_patronyms.libelle LIKE '".$nom_spouse."%'";
						
					}
					
					$condition .= $test;
				}
				
			// si le critere prenom épouse est renseigné
				if (!empty($prenom_spouse) and $prenom_spouse !='*') {
					if ($prenom_spouse_exact == true) { 		
						$test = " and oxg_individuals.first_name = '".$prenom_spouse."'";
					}
					elseif (strpos($prenom_spouse, "^")!==false || strpos($prenom_spouse, "$")!==false ) {
						$test = " and oxg_individuals.first_name REGEXP '".$prenom_spouse."'";
					}
					elseif (strpos($prenom_spouse, "*")!==false) {
						$prenom_spouse = str_replace("*", "%", $prenom_spouse);	
						$test = " and oxg_individuals.first_name LIKE '".$prenom_spouse."'";
					}
					else {
						$test = " and oxg_individuals.first_name LIKE '".$prenom_spouse."%'";
						
					}
					
					$condition .= $test;
				}
			
// $_SESSION['ind_affiche']=1;	
		
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
					'temp' => 'spouses',
					'fields'     => "oxg_individuals.id_individual as id_spouse, oxg_patronyms.libelle, oxg_individuals.first_name",
					'conditions'  => $condition,
					'order' => "id_spouse ASC"
			));

		}
			
			
// $_SESSION['ind_affiche']=1;	
// echo $_SESSION['Req_Count']."</br>";
	
			//$table = "mariages LEFT OUTER husbands on mariages.id_husband = husbands.id_husband";
			$table = "mariages, husbands";
			if ((!empty($nom_spouse) and $nom_spouse !='*') or (!empty($prenom_spouse) and $prenom_spouse !='*')) {
				//$table.= ", spouses";
				$table.= ", spouses";
			}
			$fields  = "mariages.id_family, mariages.country, mariages.zone, mariages.city, mariages.id_husband, mariages.id_spouse, mariages.place as place, mariages.date";
			$condition = "mariages.id_husband = husbands.id_husband ";
			if ((!empty($nom_spouse) and $nom_spouse !='*') or (!empty($prenom_spouse) and $prenom_spouse !='*')) {
				$condition .= " and mariages.id_spouse = spouses.id_spouse";
			}
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
					'fields'     => $fields,
					'conditions'  => $condition,
					'order' => "husbands.id_husband ASC"
			));
// echo $_SESSION['Req_Count']."</br>";
//var_dump($result);
			 foreach($result as $k => $row) {
				$husband = $this->Outils->affiche_lien_individu($row->id_husband,true,true);
				$spouse  = $this->Outils->affiche_lien_individu($row->id_spouse,true,true);
				$_SESSION['t_recherches_resultats'][] = array('id_family'=>$row->id_family, 'id_husband'=>$husband, 'id_spouse'=>$spouse, 'place'=>$row->place, 'date'=>$row->date);
				//$_SESSION['t_recherches_resultats'][] = array('id_family'=>$row->id_family, 'id_husband'=>$row->id_husband, 'id_spouse'=>$row->id_spouse, 'place'=>$row->place, 'date'=>$row->date);
			 }
	
		} // fin du validate=ok	
		
		$this->render('couples');
		
	} // fin de fonction recherches couples
	
	function lieux() {
		$_SESSION['s_page'] = "search";		
		$_SESSION['s_vue']  = 'recherches_lieux';
		$_SESSION['s_phase'] = 'recherches_lieux';
// var_dump ($_GET); echo "</br>";
// var_dump ($_POST);echo "</br>";
// var_dump ($this->request->params); echo "</br>";
// var_dump ($this->request->data);echo "</br>";
	//prise en compte premier passage
		// $phase = $this->request->params[0]==0 ? 0 : 1;
		// $page = isset($this->request->params[0])?$this->request->params[0] : 1; 
		// if ($page== 'S') {$phase= 1; $page=1;}
	
		// $_SESSION['s_rech_country_evnt'] = $this->request->params[0];
		// $_SESSION['s_rech_zone_evnt'] = $this->request->params[1];
		// $_SESSION['s_rech_city_evnt'] = $this->request->params[2];
		$country = $this->request->params[0];
		$zone = $this->request->params[1];
		$city = $this->request->params[2];
		
		$this->loadModel('Outils');
		$this->loadModel('Recherches'); 		
		
	// recherche de la liste des type d'événements
	
			$_SESSION['t_type_evenements'] = array();
	
			$condition  = "oxg_events2.type=oxg_events_type.id_type_event ";
			$condition .= " and oxg_events_type.id_type_event IN('47','51','55','77','82','112','115','125','129','163','167')";
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
						// on cherche les types : BAPM, BIRT,BURI,DEAT,DIV,MARC,MARR,NOTE
		//	$condition .= " and oxg_events_type.code not in ('UNION','TITL','OCCU','ASSO','DSCR','EDUC','GIVN','REFN') ";
//$_SESSION['ind_affiche']=1;		
			$table = 'oxg_events2, oxg_events_type';
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
				'fields'     => "oxg_events_type.id_type_event as type, oxg_events_type.code as code" ,
				'conditions' => $condition,
				'group'      => "oxg_events_type.id_type_event, oxg_events_type.code"
				));
				
			foreach($result as $k => $row)	{
				$_SESSION['t_type_evenements'] [] = array('type'=>$row->type,'code'=>$row->code);
			}
			
	// recherche de la liste des pays

			$_SESSION['t_countries'] = array();
			
			$condition  = "oxg_events2.id_country = oxg_countries.id_country";
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
//$_SESSION['ind_affiche']=1;				
			$table = 'oxg_events2, oxg_countries ';
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
				'fields'     => "oxg_countries.id_country, oxg_countries.libelle as libelle, count(*) as decompte",
				'conditions' => $condition,
				'group'      => "oxg_countries.id_country, oxg_countries.libelle"
				));
				
			foreach($result as $k => $row)	{
				$_SESSION['t_countries'] [] = array('id_country'=>$row->id_country,'libelle'=>$row->libelle,'decompte'=>$row->decompte);
			}
	
		
	// recherche de la liste des zones
			$_SESSION['t_zones'] = array();
			
			$table = 'oxg_events2, oxg_zones ';
			$condition =  "oxg_events2.id_country=oxg_zones.id_country and oxg_events2.id_zone=oxg_zones.id_zone and oxg_zones.id_country=".$country;
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
//$_SESSION['ind_affiche']=1;				
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
				'fields'     => "oxg_zones.id_zone, oxg_zones.libelle as libelle, count(*) as decompte",  
				'conditions' => $condition,
				'group'      => "oxg_zones.id_zone, oxg_zones.libelle"
				));

				foreach($result as $k => $row)	{
					$_SESSION['t_zones'] [] = array('id_zone'=>$row->id_zone, 'libelle'=>$row->libelle, 'decompte'=>$row->decompte);
				}
			
	// recherche de la liste des villes	
			$_SESSION['t_cities'] = array();
			
			$table = 'oxg_events2, oxg_cities';
			$condition =  "oxg_events2.id_country=oxg_cities.id_country and oxg_events2.id_zone=oxg_cities.id_zone and oxg_events2.id_city=oxg_cities.id_city and oxg_cities.id_country=".$country." and oxg_cities.id_zone=".$zone;
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
//$_SESSION['ind_affiche']=1;	
			$this->Recherches->table = $table;
			$result=$this->Recherches->find(array(
				'fields'     => "oxg_cities.id_city, oxg_cities.libelle as libelle, count(*) as decompte",  
				'conditions' => $condition,
				'group'      => "oxg_cities.id_city, oxg_cities.libelle"
				));
				
				foreach($result as $k => $row)	{
					$_SESSION['t_cities'] [] = array('id_city'=>$row->id_city, 'libelle'=>$row->libelle, 'decompte'=>$row->decompte);
				} 
			
	// recherche de la liste des villes

		var_dump($_SESSION['t_cities']);
		
		$this->render('events');
		
	} // fin de fonction recherches couples
} // fin de classe RecherchesController