<?php
class AccueilController extends Controller{

	
	function index(){

//$_SESSION['ind_affiche']=1;

		$_SESSION['s_groupe']='accueil';
		$_SESSION['s_page']='accueil';	
	
		//sinon on la fait pour la première fois
		$stats_accueil = array();
		
		$this->loadModel('Accueil');

	// Vérification du nombre de famille si aucune n'est sélectionnée

		if($_SESSION['FAMILY']==-1 || !isset($_SESSION['FAMILY'])) {

			$table = 'oxg_family';
			$this->Accueil->table = $table;
			$res_fam = $this->Accueil->find(array(
			'fields' => '*',
			'order'  => 'id_family'
			));

			$_SESSION['FAMILY'] = $res_fam[0]->id_family; // 1ère occurence de la table;
			$_SESSION['s_ReqFamille'] = " and id_family=".$_SESSION['FAMILY'];
			$_SESSION['b_init'] = 0;
			$_SESSION['t_stats_accueil'] = array();

			
		}

		if ($_SESSION['b_connecte'] == true ) {
			$_SESSION['s_ReqContemporain'] = " and contemporary = 1";
		}
		else {
			$_SESSION['s_ReqContemporain'] = "";
		}
		
		//Affichage du titre si 1 famille est sélectionnée
		if($_SESSION['s_ReqFamille']!="" || $_SESSION['s_TitreGeneral']=="")
		{
			// $sql_query = "select name libelle from oxg_family where 1=1 $sReqFam";
			// $resFam = mysql_query($sql_query) or mysql_die();
			// $sTitle = mysql_fetch_array($resFam);
			// $sTitle = decode_lng_text("INDEX_TITLE")." $sTitle[libelle]";
			
			$table = 'oxg_family';
			$this->Accueil->table = $table;
		
			$condition = "id_family=".$_SESSION['FAMILY']; 
			$sTitre= $this->Accueil->findFirst(array(
			'fields'      => 'name',
			'conditions'  => $condition
			));

			$_SESSION['s_Title'] = decode_lng_text('INDEX_TITLE'). ' '.$sTitre->name;

		}
		else
		{
			$_SESSION['s_Title'] = $_SESSION['s_TitreGeneral'];
		} 
	
		//on vérifie si la recherche a déjà été faite
		if ($_SESSION['b_init'] ==1) { 
			$this->render('index');
		}
	//	set_time_limit(0);

		$id_type_evnt_plac=-1;
		$table = 'oxg_events_type';
		$this->Accueil->table = $table;	
		$row_evnt=$this->Accueil->findFirst(array(
			'fields'      =>  'id_type_event',
			'conditions'  => "code='PLAC'"
		));
		
		if($row_evnt)
		{
			$id_type_evnt_plac = $row_evnt->id_type_event;
		}

		$table = 'oxg_events, oxg_countries, oxg_zones';
		$this->Accueil->table = $table;	
		
		$condition  = " oxg_events.info1<>-1 and oxg_events.info2<>-1"; 
		$condition .= $_SESSION['s_ReqFamille'];
		$condition .= " and oxg_events.info1=oxg_countries.id_country";
		$condition .= " and oxg_events.info1=oxg_zones.id_country";
		$condition .= " and oxg_events.info2=oxg_zones.id_zone";
		$condition .= " and type = " .$id_type_evnt_plac;

		$result=$this->Accueil->find(array(
				'fields'     => 'oxg_countries.libelle as libelle, oxg_zones.libelle as zone, count(*) as somme',
				'group'      => 'libelle,  zone',
				'conditions'  => $condition,
				'order'      => 'somme desc'
				));

		//$_SESSION['s_places']= array();
		foreach ($result as $k=>$v) {
			$stats_accueil['s_places'][]    = array('zone'=>$v->zone, 'libelle'=>$v->libelle);
		}

		// Calcul des 5 lieux les plus présents

		$table = 'oxg_events, oxg_countries';
		$this->Accueil->table = $table;	
		$condition  = " oxg_events.info1<>-1 and oxg_events.info2<>-1"; 
		$condition .= $_SESSION['s_ReqFamille'];
		$condition .= " and oxg_events.info1=oxg_countries.id_country";
		$condition .= " and type = " .$id_type_evnt_plac;

		$place=$this->Accueil->find(array(
				'fields'     => 'oxg_countries.libelle as libelle, oxg_countries.id_country, count(*) as somme',
				'group'      => 'libelle',
				'conditions'  => $condition,
				'order'      => 'somme desc LIMIT 5'
				));
		//$_SESSION['s_place']= array();

		foreach($place as $k=>$v){
			$stats_accueil['s_place'][] = array('name'=>$v->libelle, 'code'=>$v->id_country);
		}

		
		// Calcul de la personne la plus ancienne
		$id_type_evnt_date=-1;
		$table = 'oxg_events_type';
		$this->Accueil->table = $table;	
		$row_evnt=$this->Accueil->findFirst(array(
			'fields'      =>  'id_type_event',
			'conditions'  => "code='DATE'"
		));
		
		if($row_evnt)
		{
			$id_type_evnt_date = $row_evnt->id_type_event;
		}

		$table = 'oxg_events';
		$this->Accueil->table = $table;	
		
		$condition  = " type = " .$id_type_evnt_date;
		$condition .= $_SESSION['s_ReqFamille'];

		$peoples=$this->Accueil->find(array(
				'fields'     => 'id_event, libelle',
				'conditions'  => $condition,
				'order'      => 'libelle ASC'
		));

		$this->loadModel('Outils');
		foreach($peoples as $k => $people) {
			$stats_accueil['s_birth_date'] = substr($people->libelle, 0, 4);
			$sIndividu   = $this->Outils->donne_ind_evnt($_SESSION['FAMILY'], $people->id_event);
			$stats_accueil['s_Individu']   = $this->Outils->affiche_lien_individu($sIndividu);

		break;
		}

		//calcul du nombre de personnes
		$table = 'oxg_individuals';
		$this->Accueil->table = $table;
		$condition  = '1=1';
		$condition .= $_SESSION['s_ReqFamille'];
		
		$row = $this->Accueil->find(array(
			'fields'     => 'count(*) as somme',
			'conditions'  => $condition
		));

		$stats_accueil['s_nbr_ind'] =$row[0]->somme;
		
		//calcul du nombre de patronymes
		$row = $this->Accueil->find(array(
			'fields'     => 'distinct id_patronym',
			'conditions'  => $condition
		));	

		$stats_accueil['s_patro'] = count($row);
			
		$fam = 0;
		$child = 0;	
		$condition = '(id_father!=-1';
		$condition .= $_SESSION['s_ReqFamille'].")";
		$condition .= ' or (id_mother!=-1';
		$condition .= $_SESSION['s_ReqFamille'].")";
		
		$row = $this->Accueil->find(array(
				'fields'     => 'count(*) as somme',
				'conditions'  => $condition,
				'group'		=> 'id_father, id_mother'
		));	
		

		foreach ($row as $k=>$v) {
			$fam += 1;
			$child += $v->somme;
		}
		if($fam != 0)
		{
			$child = round($child/$fam,2);
		}
		else
		{
			$child = 0;
		}
		
		$stats_accueil['s_child'] 	= $child;	
		$stats_accueil['s_fam'] 	= $fam;

		//calcul du nombre de lieux
		
		$table = 'oxg_events_type';
		$this->Accueil->table = $table;
		
		$row_evnt = $this->Accueil->findFirst(array(
				'fields'     => 'id_type_event',
				'conditions'  => "code='PLAC'"
		));	

		$id_type_evnt_plac = $row_evnt->id_type_event;
		
		$table = 'oxg_events';
		$this->Accueil->table = $table;
		$condition = 'type = '.$id_type_evnt_plac;
		$condition .= $_SESSION['s_ReqFamille'];

		$row = $this->Accueil->find(array(
				'fields'     => 'libelle as lib, COUNT(*) as somme',
				'conditions'  => $condition,
				'group'		=> 'lib'
		));	

		$stats_accueil['s_lieux']  = count($row);

		//récupération des data de statistiques

		$table = 'oxg_stat_index';
		$this->Accueil->table = $table;
		$condition = 'id_family= '.$_SESSION['FAMILY'];

		$rows = $this->Accueil->find(array(
				'fields'     => 'birth, birth_d, birth_p, wedding, wedding_d, wedding_p, death, death_d, death_p, event, event_d, event_p',
				'conditions'  => $condition
		));	
		foreach ($rows as $k=>$row) {

			$stats_accueil['s_birth']     =	$row->birth;
			$stats_accueil['s_birth_d']   =	$row->birth_d;
			$stats_accueil['s_birth_p']   =	$row->birth_p;

			$stats_accueil['s_wedding']   = 	$row->wedding;
			$stats_accueil['s_wedding_d'] =	$row->wedding_d;
			$stats_accueil['s_wedding_p'] =	$row->wedding_p;

			$stats_accueil['s_death']     =	$row->death;
			$stats_accueil['s_death_d']   =	$row->death_d;
			$stats_accueil['s_death_p']   =	$row->death_p;

			$stats_accueil['s_event']     =	$row->event;
			$stats_accueil['s_event_d']   =	$row->event_d;
			$stats_accueil['s_event_p']   =	$row->event_p;

			$stats_accueil['s_other']   =	$stats_accueil['s_event']   - $stats_accueil['s_birth']   - $stats_accueil['s_wedding']   - $stats_accueil['s_death'];
			$stats_accueil['s_other_d'] =	$stats_accueil['s_event_d'] - $stats_accueil['s_birth_d'] - $stats_accueil['s_wedding_d'] - $stats_accueil['s_death_d'];
			$stats_accueil['s_other_p'] =	$stats_accueil['s_event_p'] - $stats_accueil['s_birth_p'] - $stats_accueil['s_wedding_p'] - $stats_accueil['s_death_p'];
		}	
			
		$table = 'oxg_individuals, oxg_patronyms';
		$this->Accueil->table = $table;
		$condition  = "sosa='1' ";
		$condition .= " and oxg_individuals.id_patronym=oxg_patronyms.id_patronym";
		$condition .= $_SESSION['s_ReqFamille'];
	
		$persoon = $this->Accueil->find(array(
				'fields'     => 'id_individual, first_name, libelle ',
				'conditions'  => $condition
		));
		
		if($persoon) {
			foreach($persoon as $k => $v) {
				$stats_accueil['s_persoon']['id_individual'] = $v->id_individual;
				$stats_accueil['s_persoon']['sosa'] = '1';
				$stats_accueil['s_persoon']['first_name'] = $v->first_name;
				$stats_accueil['s_persoon']['libelle'] = $v->libelle;
			}
		}
		else {
			$stats_accueil['s_persoon'] = array();
			$stats_accueil['s_persoon']['sosa'] = '0';
			$stats_accueil['s_persoon']['first_name'] = display_lng_text('INDEX_NO_SOSA');
		}	
		$_SESSION['t_stats_accueil'] = $stats_accueil;
		
		$_SESSION['b_init']=1;

	$this->render('index'); 
	}

	// function language() {
		// $lang= array('fra','eng','spa','deu','nld','prt','pol','cro','rus','tur','fin');
		// $_SESSION['s_lang'] ='fra';
		// if (isset($this->request->params[0]) && in_array($this->request->params[0] ,$lang)) {
			// $_SESSION['s_lang'] = $this->request->params[0];
		// }
	// } // fin de fonction language
	
}// fin de classe