<?php
class PatronymesController extends Controller{

	function index(){

		$_SESSION['s_groupe']='patronymes';
		$_SESSION['s_page']='patro';

		$INITIALE = isset($this->request->params[0])?$this->request->params[0] : 'A'; 
		$_SESSION['initiale'] = $INITIALE;
		
		$this->loadModel('Patronymes');

		$patronymes= array();
		
		if ($INITIALE=='Tous') {
//$_SESSION['ind_affiche']=1;
				$table = 'oxg_patronyms, oxg_individuals';
				$this->Patronymes->table = $table;
				
				$condition  = "oxg_patronyms.id_patronym=oxg_individuals.id_patronym";
				$condition .= " and (ASCII(upper(left(oxg_patronyms.libelle_ord,1)))>=65 and ASCII(upper(left(oxg_patronyms.libelle_ord,1)))<=90)";
				$condition .= $_SESSION['s_ReqFamille'];
				$condition .= $_SESSION['s_ReqContemporain'];
				
				$result=$this->Patronymes->find(array(
						'fields'      => 'oxg_patronyms.libelle_ord, oxg_patronyms.id_patronym, upper(left(oxg_patronyms.libelle_ord,1)) as Initiale, COUNT(*) as somme',
						'conditions'  => $condition, 
						'group' 	  => "oxg_patronyms.libelle_ord"

				));
			}
			elseif ($INITIALE=='Autres') {
//$_SESSION['ind_affiche']=1;	
	//	$sql_query="select oxg_patronyms.id_patronym, oxg_patronyms.libelle_ord, COUNT(*) as SOMME from oxg_patronyms, oxg_individuals where oxg_patronyms.id_patronym=oxg_individuals.id_patronym $sReqFam $sReqCont GROUP BY oxg_patronyms.id_patronym, oxg_patronyms.libelle, oxg_patronyms.libelle_ord having (ASCII(upper(left(oxg_patronyms.libelle_ord,1)))<65 or ASCII(upper(left(oxg_patronyms.libelle_ord,1)))>90) ORDER BY oxg_patronyms.libelle_ord, oxg_patronyms.id_patronym";
		
				$table = 'oxg_patronyms, oxg_individuals';
				$this->Patronymes->table = $table;
				
				$condition  = "oxg_patronyms.id_patronym=oxg_individuals.id_patronym";
				$condition .= $_SESSION['s_ReqFamille'];
				$condition .= $_SESSION['s_ReqContemporain'];
				
				$result=$this->Patronymes->find(array(
						'fields'      => ' oxg_patronyms.id_patronym, oxg_patronyms.libelle_ord, COUNT(*) as somme ',
						'conditions'  => $condition, 
						'group' 	  => "oxg_patronyms.id_patronym, oxg_patronyms.libelle, oxg_patronyms.libelle_ord having (ASCII(upper(left(oxg_patronyms.libelle_ord,1)))<65 or ASCII(upper(left(oxg_patronyms.libelle_ord,1)))>90)",
						'order'       => "oxg_patronyms.libelle_ord, oxg_patronyms.id_patronym"
				));

			}
			else {
//$_SESSION['ind_affiche']=1;
				
				$table = 'oxg_patronyms';
				$this->Patronymes->table = $table;
				
				$condition  = "upper(left(oxg_patronyms.libelle_ord,1))='".$INITIALE."' ";

				
				$result=$this->Patronymes->find(array(
						'fields'      => 'oxg_patronyms.id_patronym, oxg_patronyms.libelle_ord',
						'conditions'  => $condition, 
						'order'       => "oxg_patronyms.libelle_ord, oxg_patronyms.id_patronym"
				));


			}
//var_dump($result);			
			foreach($result as $k => $row) {
				$table = 'oxg_individuals';
				$this->Patronymes->table = $table;
				
				$condition  = "id_patronym=".$row->id_patronym;
				$condition .= $_SESSION['s_ReqFamille'];
				$condition .= $_SESSION['s_ReqContemporain'];
				
				$result2=$this->Patronymes->find(array(
						'fields'      => 'COUNT(*) as somme ',
						'conditions'  => $condition
				));
				foreach ($result2 as $k => $row2) {
					$nom_individu=array();
					$nom_individu = array( 'id_patronym'=>$row->id_patronym, 'initiale'=>substr($row->libelle_ord,0,1), 'nom'=>$row->libelle_ord, 'nombre'=>$row2->somme);
					$patronymes [] = $nom_individu;
					
				}
				
			}
		$_SESSION['t_patronymes'] = $patronymes;
//		var_dump($patronymes);
		$this->render('index'); 
	} // fin de fonction index

	
	function detail_patro(){
		
		$_SESSION['s_groupe'] = 'patronymes';
		$_SESSION['s_page']   = 'zoom_patro';
		
		//$PATRO=isset($_REQUEST["PATRO"])?$_REQUEST["PATRO"] : 0; 
		$PATRO = isset($this->request->params[0])?$this->request->params[0] : 0;
		
		$prenoms = array();
		
		$this->loadModel('Patronymes');
		$this->loadModel('Outils');
		
		$sId_Event_Type_GIVN=$this->Outils->get_id_event_type("GIVN");	// 99
		$sId_Event_Type_NICK=$this->Outils->get_id_event_type("NICK");	// 123
		
		
		/*
		* Recherche du patronyme
		*/
		
		$table = 'oxg_patronyms';
		$this->Patronymes->table = $table;
		
		$condition  = "id_patronym=".$PATRO;

		
		$result=$this->Patronymes->find(array(
				'fields'      => '*',
				'conditions'  => $condition
		));
		foreach ($result as $k => $row) {
			if($row->libelle =='') 	{
				$_SESSION['s_nom'] = " ?";
			}
			else
			{
				$_SESSION['s_nom'] = $row->libelle;
			}

		}
		// $prenom_individu=array();
		// $prenom_individu = array('nom'=>$nom);
		// array_push($prenoms,$prenom_individu);

		
		/*
		* Recherche des prénoms
		*/		
		
		
		$table  = "oxg_individuals";
		$table .= " left join (select oxg_events_givn.id_individu, oxg_events_nick.libelle from oxg_events2 oxg_events_givn, oxg_events oxg_events_nick where oxg_events_givn.type=".$sId_Event_Type_GIVN;
		$table .= " and oxg_events_nick.type_father=1 and oxg_events_nick.type=".$sId_Event_Type_NICK." and oxg_events_nick.id_father=oxg_events_givn.id_event ";
		$table .= " and oxg_events_givn.id_family=".$_SESSION['FAMILY'];
		$table .= " and oxg_events_nick.id_family=".$_SESSION['FAMILY'];
		$table .=") event_nickname ";
		$table.=" on (oxg_individuals.id_individual=event_nickname.id_individu) ";

		$this->Patronymes->table = $table;
		
		$condition  = "id_patronym=".$PATRO;
		$condition .= $_SESSION['s_ReqFamille'];
		
		$result=$this->Patronymes->find(array(
				'fields'      => 'id_family, id_individual, sex, event_nickname.libelle as nickname, first_name, date_naissance, date_deces, sosa, contemporary ',
				'conditions'  => $condition,
				'order'		  => "first_name"
		));
		foreach ($result as $k => $row) {
			$prenom_individu=array();
			$prenom_individu = array('id_family'=>$row->id_family, 'id_patronym'=>$row->id_patronym, 'id_individual'=>$row->id_individual, 'sosa'=>$row->sosa, 'contemporary'=>$row->contemporary, 'prenom'=>$row->first_name, 'nickname'=>$row->nickname, 'sexe'=>$row->sex, 'date_naissance'=>$row->date_naissance ,'date_deces'=>$row->date_deces );
			$prenoms [] = $prenom_individu;
			
		}
//var_dump($prenoms)	;
	$_SESSION['t_prenoms'] = $prenoms;
	
	$this->render('detail_patro'); 
		
	}	 // fin de la fonction detail_patro

	function flashlist() {

		$_SESSION['s_groupe'] = 'patronymes';
		$_SESSION['s_page']   = 'flashlist';
		
		$_SESSION['t_flashlist'] = array();
		
		if (!isset($this->request->params[0]) || $this->request->params[0]== "place") {
			$sortby = "pays, ville, name";
		}
		else {  
			$sortby = "name, pays, ville";
		}

	//	$req_select = "select * from oxg_flashlist where 1=1 $sReqFam order by $sortby";
		$this->loadModel('Patronymes');
				/*
		* Recherche du patronyme
		*/
//$_SESSION['ind_affiche']=1;		
		$table = 'oxg_flashlist';
		$this->Patronymes->table = $table;
		
		$conditions = "1=1";
		$conditions .= $_SESSION['s_ReqFamille'];

		$result=$this->Patronymes->find(array(
				'fields'      => '*',
				'conditions'  => $conditions,
				'order'       => $sortby	
		));
		
		foreach ($result as $k => $row) {

			$birth	  = $row->birth<> "" ? $row->birth : 0;
			$wedding  = $row->wedding<> "" ? $row->wedding : 0;
			$death	  = $row->death<> "" ? $row->death : 0;
			$other	  = $row->other<> "" ? $row->other : 0;
			$date_min = $row->date_min <> "" ? substr($row->date_min,0,4) : "?";
			$date_max = $row->date_max<> "" ? substr($row->date_max,0,4) : "?";
			
			$_SESSION['t_flashlist'] [] = array('country'=>$row->country, 'zone'=>$row->zone, 'city'=>$row->city, 'ville'=>$row->ville, 'pays'=>$row->pays, 'name'=>$row->name, 'birth'=>$birth, 'wedding'=>$wedding, 'death'=>$death, 'other'=>$other, 'date_min' =>$date_min, 'date_max' =>$date_max);
}	
		// $country = $row['country'];
	// $zone = $row['zone'];
	// $city = $row['city'];
	// $ville = $row['ville'];
	// $link_city = "<a href='detail_ind_zone.php?FAMILY=$FAMILY&COUNTRY=$country&ZONE=$zone&CITY=$city'>".$ville."</a>";
	// if ($row['birth'] <> "") {$birth = $row['birth'];} else {$birth = 0;}
	// if ($row['wedding'] <> "") {$wedding = $row['wedding'];} else {$wedding = 0;}
	// if ($row['death'] <> "") {$death = $row['death'];} else {$death = 0;}
	// if ($row['other'] <> "") {$other = $row['other'];} else {$other = 0;}
	// if ($row['date_min'] <> "") {$date_min = substr($row['date_min'],0,4);} else {$date_min = "?";}
	// if ($row['date_max'] <> "") {$date_max = substr($row['date_max'],0,4);} else {$date_max = "?";}
	// if ($type=="place") { echo "<TD>".$row['pays']."</TD><TD>".$link_city."</TD><TD>".$row['name']."</TD>";}
	// if ($type=="name") { echo "<TD>".$row['name']."</TD><TD>".$row['pays']."</TD><TD>".$link_city."</TD>";}

		
		$this->render('flashlist');
			
		} // fin de la fonction flashlist
	
} // fin de la classe LieuxController
?>