<?php
class LieuxController extends Controller{
	
		public $sReqPays     = '';
		public $sReqEvent    = '';
		public $sRexclusion  = '';
		public $sReqFiltre   =  '';
		public $sReqPaysComp =  '';
		public $condition    = '';
		public $reponse      = array();
		
	/*
	* Préparation de l'affichage de la page Index pour les Lieux
	*/
	function index(){
	
		$_SESSION['s_page']='lieux';
		$_SESSION['s_vue']='index';
	
		$this->loadModel('Lieux');
		
		$event = $this->request->params[0];
		$_SESSION['s_event']= $event;
	
		
		$reponse = self::PreparerSelect($event);
//$_SESSION['ind_affiche']=1;		
		$table = 'oxg_countries, oxg_events2';
		$this->Lieux->table = $table;
		
		$condition  = $reponse['ReqPays'];
		$condition .= $reponse['ReqFiltre'];
		$condition .= $reponse['Rexclusion'];
		$condition .= $reponse['ReqEvent'];
		$condition .= $_SESSION['s_ReqFamille'];
		
		$result=$this->Lieux->find(array(
				'fields'     => 'oxg_countries.id_country, oxg_countries.libelle, oxg_countries.extension, COUNT(*) as somme',
				'group'      => 'oxg_countries.id_country',
				'conditions'  => $condition,
				'order'      => 'oxg_countries.libelle'
				));

		$_SESSION['s_Ligne_Pays'] = array();
		$_SESSION['s_Pays'] = array();
		
		foreach($result as $k=>$row) {

			$lien = 'lieux/detail_zone/'.$row->id_country.'/'.$event;
			$ligne = "<p><a href='".Router::url($lien)."'>";
			$ligne .= $row->libelle."</a> (".$row->somme.")";
//$_SESSION['ind_affiche']=1;				
			$table = 'oxg_zones';
			$this->Lieux->table = $table;	
			
			$condition  = "oxg_zones.id_country=".$row->id_country;
			$condition .=  " and oxg_zones.pos_remp_x<>''";
			
			$result_zones=$this->Lieux->findFirst(array(
					'fields'     => '*',
					'conditions' => $condition
					));

			if($result_zones) {
				$ligne .= " <a href='".Router::url('cartes/index/'.$row->id_country)."'>";
				$ligne .= display_lng_text("IDX_LIEUX_MAP");
				$ligne .= "</a>";
			}
			$ligne .= "<br>";
			$_SESSION['s_Ligne_Pays'][] = $ligne;
			$_SESSION['s_Pays'][$row->id_country] = array('extension'=>$row->extension, 'libelle'=>$row->libelle,'nombre'=>$row->somme);
		}
//var_dump($_SESSION['s_Pays']);
		$this->render('index'); 

	} // fin de la fonction Index
	
	function detail_zone(){
	/*
	*  Préparation de l'affichage des détails par zones et des villes sur un pays
	*/
		
		$_SESSION['s_page']='places_zones';
		$_SESSION['s_vue']='detail_zone';
		
		$event = $this->request->params[2];
		$id_zone = $this->request->params[1];
		$country = $this->request->params[0];
		if ($id_zone=='All') {$id_zone='';}
		$_SESSION['s_id_zone']= $id_zone;
		$_SESSION['s_event']= $event;
		$_SESSION['s_country']= $country;
//var_dump($this->request->params);

		$reponse = self::PreparerSelect($event);
	
		$this->loadModel('Lieux');

	/*
	*	recherche du libellé du pays sélectionné
	*/
		// $_SESSION['s_Title'] = decode_lng_text("DETAIL_ZONE_TITLE");
	
		// $table = 'oxg_countries';
		// $this->Lieux->table = $table;	

		// $condition  = "id_country=".$country;
		// $result=$this->Lieux->find(array(
				// 'fields'     => 'id_country, libelle, extension',
				// 'conditions' => $condition
				// ));

		// $_SESSION['s_Title']  .= " : ".$result[0]->libelle;


		$_SESSION['s_Title'] = decode_lng_text("DETAIL_ZONE_TITLE");
		$_SESSION['s_Title']  .= " : ".$_SESSION['s_Pays'][$country]['libelle'];
			
	/*
	*	Recherches des zones dans le pays sélectionné
	*/		
	/*
	*	1ère étape :  Recherches des zones non connues dans le pays
	*/		
	
		$_SESSION['t_Zone_Pays'] = array();
		$_SESSION['t_Zone_Villes'] = array();
		
		$table = 'oxg_events2';
		$this->Lieux->table = $table;	

		$condition  = " id_country=".$country."  and  id_country<>-1 and id_zone=-1";
		$condition .= $reponse['ReqFiltre'];
		$condition .= $reponse['Rexclusion'];
		$condition .= $reponse['ReqEvent'];
		$condition .= $_SESSION['s_ReqFamille'];
			
		$result=$this->Lieux->find(array(
			'fields'     => "count(*) as CNT",
			'conditions' => $condition
		));

		foreach($result as $k => $v) {
			if ($v->CNT !=0) {
				
				$_SESSION['s_Zone_Pays'][0]  = array('id_zone'=>'-1', 'id_city'=>'-1','libelle'=>display_lng_text('DETAIL_ZONE_UNKNOWN_CNTRY'), 'nombre'=>$v->CNT);
			}
		}	
		/*
		*	2ème étape :  Recherches des zones connues dans le pays
		*/		


		$table = 'oxg_events2';
		$this->Lieux->table = $table;	

		$condition  = " id_country=".$country." and  id_country<>-1 and id_zone<>-1 ";
		$condition .= $reponse['ReqFiltre'];
		$condition .= $reponse['Rexclusion'];
		$condition .= $reponse['ReqEvent'];
		$condition .= $_SESSION['s_ReqFamille'];
//$_SESSION['ind_affiche']=1;			
		$result_pays = $this->Lieux->find(array(
			'fields'     => '*, count(*) as somme',
			'conditions' => $condition,
			'group'      => 'id_zone',
			'order'      => 'id_zone'
		));

		$j=1;
//echo 'select pays avec zone <>1';var_dump($result_pays);		
		foreach($result_pays as $k => $zone) {
	//			$_SESSION['s_Zone_Pays'][$j]  = array('id_zone'=>$v->id_zone, 'libelle'=>'', 'nombre'=>$v->somme);
				/*
				*	  Recherche du libelle de la zone
				*/				
				
				$table = 'oxg_zones';
				$this->Lieux->table = $table;	

				$condition  = " oxg_zones.id_country=".$country." and oxg_zones.id_zone=".$zone->id_zone;
//$_SESSION['ind_affiche']=1;					
				$result_zone = $this->Lieux->find(array(
					'fields'     => 'oxg_zones.code, oxg_zones.id_zone, oxg_zones.libelle ',
					'conditions' => $condition
				));
//echo 'select libelle zone'; var_dump($result_zone);	
				if ($result_zone) {	
					foreach($result_zone as $l=>$zoneLib) {
							$_SESSION['t_Zone_Pays'][$j]  = array('id_zone'=>$zone->id_zone, 'code'=>$zoneLib->code, 'libelle'=>$zoneLib->libelle, 'nombre'=>$zone->somme);
					}
				}	
				else {
					$_SESSION['t_Zone_Pays'][$j]  = array('id_zone'=>$zone->id_zone, 'id_city'=>'-1','libelle'=>display_lng_text('DETAIL_ZONE_UNKNOWN_CNTRY'), 'nombre'=>$zone->somme);
				}
//echo 'zones '; 

		/*
		*	Recherches des villes dans chaque zone
		*/		
			/*
			*	1ère étape :  Recherches des villes inconnues dans chaque zone
			*/		
				
			$table = 'oxg_events2';
			$this->Lieux->table = $table;	

			$condition  = " id_country=".$country." and id_zone=".$zone->id_zone." and id_city=-1 ";
			$condition .= $reponse['ReqFiltre'];
			$condition .= $reponse['Rexclusion'];
			$condition .= $reponse['ReqEvent'];
			$condition .= $_SESSION['s_ReqFamille'];
//			$condition .= $_SESSION['s_ReqContemporain'];
//$_SESSION['ind_affiche']=1;			
			$result=$this->Lieux->find(array(
				'fields'     => 'count(*) as CNT',
				'conditions' => $condition
			));

			foreach($result as $m => $ville_inc) {
				if ($ville_inc->CNT !=0) {
					$_SESSION['t_Zone_Villes'][$j][0]  = array('id_zone'=>$zone->id_zone, 'id_city'=>'-1', 'libelle'=>display_lng_text('DETAIL_ZONE_UNKNOWN'), 'nombre'=>$ville_inc->CNT);

				}
			}

			
			/*
			*	2ème étape :  Recherches des villes connues dans chaque zone
			*/					

			$table = 'oxg_cities, oxg_events2';
			$this->Lieux->table = $table;	

			$condition  = " oxg_events2.id_country=".$country." and oxg_events2.id_zone=".$zone->id_zone;
			$condition .= " and oxg_cities.id_zone=".$zone->id_zone." and oxg_cities.id_country=".$country;
			$condition .= " and oxg_cities.id_city=oxg_events2.id_city";
			$condition .= $reponse['ReqFiltre'];
			$condition .= $reponse['Rexclusion'];
			$condition .= $reponse['ReqEvent'];
			$condition .= $_SESSION['s_ReqFamille'];
	//		$condition .= $_SESSION['s_ReqContemporain'];
			
			$result_villes=$this->Lieux->find(array(
				'fields'     => 'oxg_cities.code, oxg_cities.id_city, oxg_cities.libelle, oxg_cities.longitude, oxg_cities.latitude, count(*) as nombre',
				'conditions' => $condition,
				'group'		 => "oxg_cities.code, oxg_cities.id_city, oxg_cities.libelle ",
				'order'      => "oxg_cities.libelle, oxg_cities.code"
			));
//var_dump($result_ville);
			$ind_ville=1;
			foreach($result_villes as $n => $villes) {
					if ($villes->latitude <> 0) {
						$lien_Google ="https://www.google.com/maps/place/".$villes->libelle."/@".$villes->latitude.",".$villes->longitude.",12.5z"; // creation lien vers GoogleMaps
					}
					else {
						$lien_Google = '';
					}
					$_SESSION['t_Zone_Villes'][$j][$ind_ville]  = array('id_zone'=>$zone->id_zone, 'id_city'=>$villes->id_city, 'libelle'=>$villes->libelle , 'nombre'=>$villes->nombre, 'lien_Google'=>$lien_Google);
					$ind_ville++;

			}

			$j++;
		}
// var_dump	($_SESSION['t_Zone_Pays']);
			
		$this->render('detail_zone'); 
	
	}	// fin de la fonction detail_zone
	
	function detail_ind_zone(){
	/*
	*  Préparation de l'affichage de la liste des évenements de la ville sélectionnée
	*/
		
		$_SESSION['s_page']='places_ind';
		$_SESSION['s_vue']='detail_ind_zone';
		
		// récupération des paramètres d'appel	
		$country = isset($this->request->params[0])?$this->request->params[0] : -1; 
		$zone	 = isset($this->request->params[1])?$this->request->params[1] : -1; 
		$city    = isset($this->request->params[2])?$this->request->params[2] : -1; 
		$event   = isset($this->request->params[3])?$this->request->params[3] : 'all'; 
		
		$_SESSION['s_country'] = $country;
		$_SESSION['s_zone']    = $zone;
		$_SESSION['s_city']    = $city;
		$_SESSION['s_event']   = $event;
		
		$reponse = self::PreparerSelect($event);
	
		$this->loadModel('Lieux');

	/*
	*	recherche du libellé de la ville et du pays sélectionné
	*/
	
		$table = 'oxg_cities';
		$this->Lieux->table = $table;	

		$condition  = " id_city=".$city." and id_zone=".$zone." and id_country=".$country;
		$row_evnt=$this->Lieux->findFirst(array(
			'fields'     => 'libelle',
			'conditions' => $condition
		));
		if($row_evnt) {
			$ville = $row_evnt->libelle;
		}

		$table = 'oxg_countries';
		$this->Lieux->table = $table;	

		$condition  = " id_country=".$country;
		$row_evnt=$this->Lieux->findFirst(array(
			'fields'     => 'libelle',
			'conditions' => $condition
		));
		if($row_evnt) {
			$pays = $row_evnt->libelle;
		}
	
		$_SESSION['s_Title'] = $ville." (".$pays.")";


		$id_type_evnt_plac='';
	
		$table = 'oxg_events_type';
		$this->Lieux->table = $table;	

		$condition  = " code='PLAC'";
		$row_evnt=$this->Lieux->findFirst(array(
			'fields'     => 'id_type_event',
			'conditions' => $condition
		));
		if($row_evnt) {
			$id_type_evnt_plac = $row_evnt->id_type_event;
		}

		$result_out = array ();	
				
		$table = 'oxg_events2';
		if($_SESSION['b_connecte']==false)
		{
			$table .= ", oxg_individuals ";
		}
	
		$this->Lieux->table = $table;	

		$condition = " oxg_events2.id_country=".$country." and oxg_events2.id_zone=".$zone." and oxg_events2.id_city=".$city ;
		
		if($_SESSION['FAMILY']!=-1)
		{
			$condition .= " and oxg_events2.id_family=".$_SESSION['FAMILY'];
			if($_SESSION['b_connecte']==false)
			{
				$condition .= " and oxg_individuals.id_family=".$_SESSION['FAMILY'];
			}
		}
		$condition .= $reponse['ReqFiltre'];
		$condition .= $reponse['Rexclusion'];
		$condition .= $reponse['ReqEvent'];
		
		if($_SESSION['b_connecte']==false)
		{
			$condition .= " and oxg_events2.id_family=oxg_individuals.id_family ";
			$condition .= " and oxg_events2.id_individu=oxg_individuals.id_individual ";
			$condition .= $_SESSION['s_ReqContemporain'];
		}
		
		$result_evnt=$this->Lieux->find(array(
			'fields'     => 'type, id_event, devnt, sDtEvnt, id_individu, oxg_events2.id_family',
			'conditions' => $condition
		));

		foreach ($result_evnt as $k => $row_evnt) {
			$compagne="";
		if ($row_evnt->type==51) { $acte=decode_lng_text("GEDTAG_BIRT")." ".decode_lng_text("GEDTAG_OF"); }
		if ($row_evnt->type==115) { $acte=decode_lng_text("GEDTAG_MARR")." ".decode_lng_text("GEDTAG_BTW"); $compagne = $row_evnt->id_event;}
		if ($row_evnt->type==82) { $acte=decode_lng_text("GEDTAG_DIV")." ".decode_lng_text("GEDTAG_BTW"); $compagne = $row_evnt->id_event;}
		if ($row_evnt->type==77) { $acte=decode_lng_text("GEDTAG_DEAT")." ".decode_lng_text("GEDTAG_OF"); }
		if ($row_evnt->type==55) { $acte=decode_lng_text("GEDTAG_BURI")." ".decode_lng_text("GEDTAG_OF"); }
		if ($row_evnt->type!=51 AND $row_evnt->type!=115 AND $row_evnt->type!=82 AND $row_evnt->type!=77 AND $row_evnt->type!=55) { $acte=decode_lng_text("GEDTAG_EVEN")." ";}

			$year  = date('Y',strtotime($row_evnt->devnt));
			$month = date('M',strtotime($row_evnt->devnt));
			$day   = date('j',strtotime($row_evnt->devnt));
			$date1 = $row_evnt->devnt;
			$date1 = str_replace("-0", "-", $date1);
			$date1 = str_replace("-", "/", $date1);
			$date2 = $row_evnt->sDtEvnt;
			$date = $date1.":".$date2;
			$timestamp = mktime($row_evnt->devnt);
			
			$result_out[] = array('date'=>$date, 'timestamp'=> $timestamp, 'year'=>$year, 'month'=>$month, 'day'=>$day, 'acte'=>$acte, 'id_family'=>$row_evnt->id_family, 'id_father'=>$row_evnt->id_individu, 'nom_indiv'=>'', 'compagne'=>$compagne, 'nom_compagne'=>'');
		
		}
		/*
		*	 Tri des évenements par date
		*/	
		
		//transformation des lignes en colonnes pour le tri avec array_multisort
		if (count($result_out)!=0) {
				foreach ($result_out as $key => $row_out)
				{
					$timestamp_[$key]  = $row_out['timestamp'];
					// $year_[$key]  = $row_out['year'];
					// $month_[$key] = $row_out['month'];
					// $day_[$key] = $row_out['day'];
				}
				// array_multisort($year_, SORT_NUMERIC, SORT_ASC, $result_out,
						// $month_, SORT_NUMERIC, SORT_ASC, $result_out,
							// $day_, SORT_NUMERIC, SORT_ASC, $result_out );
				array_multisort($timestamp_, SORT_NUMERIC, SORT_ASC, $result_out);
							
		}
		/*
		*	 Recherche des noms des individus et de leurs compagnes
		*/	

		$this->loadModel('Outils');
		
		foreach ($result_out as $key => $row_out)
			{

				$id_ind=$result_out[$key]['id_father'];
	 			$id_fam=$result_out[$key]['id_family'];
			
				$table = 'oxg_individuals';
				$this->Lieux->table = $table;	
			
				$condition = "id_individual=". $row_out['id_father'] .' '.$_SESSION['s_ReqContemporain'].' '." and id_family=".$id_fam;
				
				$result_cont=$this->Lieux->findFirst(array(
					'fields'     => '*',
					'conditions' => $condition
				));

					$result_out[$key]['nom_indiv'] =  $this->Outils->affiche_lien_individu($id_ind, true, false);

					$compagne = $row_out['compagne'];
					if ($compagne != "")
					{
						$table = 'oxg_events';
						$this->Lieux->table = $table;	
						
						$condition = " id_event=".$compagne." and id_family=".$id_fam;
						
						$req=$this->Lieux->findFirst(array(
							'fields'     => 'info2',
							'conditions' => $condition
						));
						
						$compagne = $req->info2;
						$table = 'oxg_individuals';
						$this->Lieux->table = $table;	
						
						$condition = 'id_individual='.$compagne.' '. $_SESSION['s_ReqContemporain'].' and id_family='.$id_fam;
						
						$req=$this->Lieux->findFirst(array(
							'fields'     => '*',
							'conditions' => $condition
						));
						
						if ($req) {
							$result_out[$key]['nom_compagne'] = $this->Outils->affiche_lien_individu($compagne, true, false);
						}
						else
						{
							$result_out[$key]['nom_compagne'] = ' X';
						}
					}
				}
				
		$_SESSION['result_out'] = $result_out;
			
		$this->render('detail_ind_zone'); 
	
	}	// fin de la fonction detail_ind_zone
		
	
	/*
	* Préparation des éléments du SELECT
	*/
	function PreparerSelect($sevent) {

		switch ($sevent) {
				case 'all':
				case '':
					$sRexclusion = " and oxg_events2.type NOT IN ('45','99','125','129','148','156','163','167','179')";
					$sReqEvent = "";
					$sReqFiltre = "";
					$sReqPays    = " oxg_countries.id_country=oxg_events2.id_country and oxg_events2.id_country<>'' and oxg_events2.id_country<>'999999'";
					$_SESSION['s_event_titre'] = decode_lng_text('IDX_LIEUX_ALL');
					break;
				
				case 'birth':
					$sRexclusion = "";
					$sReqFiltre = "";
					$sReqPays  = " oxg_countries.id_country=oxg_events2.id_country and oxg_events2.id_country<>'' and oxg_events2.id_country<>'999999'";
					$sReqEvent = " and oxg_events2.type='51'";
					$_SESSION['s_event_titre'] = decode_lng_text('GEDTAG_BIRT');
					break;
							
				case 'wedding':
					$sRexclusion = "";
					$sReqFiltre = "";
					$sReqPays  = " oxg_countries.id_country=oxg_events2.id_country and oxg_events2.id_country<>'' and oxg_events2.id_country<>'999999'";
					$sReqEvent = " and oxg_events2.type='115'";
					$_SESSION['s_event_titre'] = decode_lng_text('GEDTAG_MARR');
					break;
					
				case 'death':
					$sRexclusion = "";	
					$sReqFiltre = "";					
					$sReqPays = " oxg_countries.id_country=oxg_events2.id_country and oxg_events2.id_country<>'' and oxg_events2.id_country<>'999999'";
					$sReqEvent = " and oxg_events2.type='77'";
					$_SESSION['s_event_titre'] = decode_lng_text('GEDTAG_DEAT');
					break;
					
				case 'other':
					$sRexclusion = " and oxg_events2.type NOT IN ('45','99','125','129','148','156','163','167','179')";
					$sReqEvent = "";
					$sReqFiltre  =  " and oxg_events2.type NOT IN ('51','77', '115')";
					$sReqPays    = " oxg_countries.id_country=oxg_events2.id_country and oxg_events2.id_country<>'' and oxg_events2.id_country<>'999999'";
					$_SESSION['s_event_titre'] = decode_lng_text('PATRO_OTHER');
					break;
			}
		$sreponse = array('Rexclusion'=>$sRexclusion,'ReqPays'=>$sReqPays,'ReqEvent'=>$sReqEvent,'ReqFiltre'=>$sReqFiltre);
//var_dump($sreponse);
		return $sreponse;
			
	} // fin de la fonction PreparerSelect

	

	
} // fin de la classe LieuxController
?>
