<?php
class StatistiquesController extends Controller{

	 function index(){
// //$_SESSION['ind_affiche']=1;
		// $_SESSION['s_groupe']='stats';
		// $_SESSION['s_page']= 'stats';
		// $_SESSION['s_vue']='index';				

	
		// $_SESSION['t_familles'] = array();

		// $this->loadModel('Familles');
		
		// $table = ' oxg_family';
		// $this->Familles->table = $table;
		
		// $result=$this->Familles->find(array(
				// 'fields'     => 'id_family, name'
		// ));

		// foreach($result as $row) {
// //$_SESSION['ind_affiche']=1;				
			// $table = ' oxg_individuals';
			// $this->Familles->table = $table;
			
			// $condition  = "id_family=".$row->id_family;
		
			// $result_cpt=$this->Familles->find(array(
					// 'fields'     => 'count(*) as somme',
					// 'conditions'  => $condition
			// ));

			// foreach ($result_cpt as $row_cpt) {
				// $_SESSION['t_familles'][$row->id_family] = array('nom_famille'=>$row->name, 'id_famille'=>$row->id_family, 'nbre_famille'=>$row_cpt->somme);
			// }

		// }

	 } // fin de fonction index


	function birthday() {
		
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='birthday';	

	// recup variable du formulaire	
		$choice = isset($this->request->data->choice) ? $this->request->data->choice : $_SESSION['s_choice'];
		if ($choice=='') {$choice='all';}
		$period =  isset($this->request->data->period) ? $this->request->data->period : $_SESSION['s_period'];
		if ($period == '') {$period='today';}
		$_SESSION['s_period'] = $period;
		$_SESSION['s_choice'] = $choice;
		
		$page = isset($this->request->params[0])?$this->request->params[0] : 1; 

		if ($page != 'S') { $this->render('birthday'); break;}
		if ($page == 'S') { $page=1;}
		
		if ($period == 'today') {$nbrjours=0;}
		if ($period == 'tomorrow') {$nbrjours=1;}
		if ($period == 'week') {$nbrjours=7;}
		if ($period == 'month') {$nbrjours=30;}
		
	// gestion des années bissextiles
		$bissextile = date('L', mktime(date('d-m-Y')));

		if ($bissextile == 1) {
			$Days_in_Month = Array(31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
		}	
		else {
			$Days_in_Month = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
		}

		$day = date('j');

		$month = date('n');
		$limit = $Days_in_Month[$month-1];
		$month_start = $month; 
		$month_end = $month;
		
		if ($nbrjours > 1){
		
			$liste1='';
			$liste2='';
			$num = 'liste1';
			$$num = $day;  // $liste1
			
			for ($i=0;$i<$nbrjours-1;$i++) {
				$day++;
				if ($day>$limit) { 
					$day=1;
					$num ='liste2';
					$$num = $day;
					$month_end+=1;
				}
				else{
					$$num .= ", ".$day;
				}
			}
		}	

		$_SESSION['t_results_out'] = Array();
			

		$this->loadModel('Statistiques');
			
//$_SESSION['ind_affiche']=1;	
		$table = 'oxg_events2';
		$this->Statistiques->table = $table;
		
		if ($choice=="birth") 	{$event_filter=" and type=51";}
		if ($choice=="wedding") {$event_filter=" and type=115";}
		if ($choice=="death") 	{$event_filter=" and type=77";}
		if ($choice=="other")	{$event_filter="and type!=51 and type!=115 and type!=77";}
		if ($choice=="all") 	{$event_filter="";}

		
		$condition  = "devnt<>'' and length(devnt) = 10 ";
		if ($nbrjours > 1) {
			if (!empty($liste2)) {
				$condition  .= " and (substr(devnt,9,2) IN (".$liste1.") and substr(devnt,6,2) = ".intval($month_start).")";
				$condition  .= " or (substr(devnt,9,2) IN (".$liste2.") and substr(devnt,6,2) = ".intval($month_end).")";
			}
			else {
				$condition  .= " and substr(devnt,6,2) = ".intval($month_start);
				$condition  .= " and substr(devnt,9,2) IN (".$liste1.")";
			}
		}
		else {	
			$condition  .= " and substr(devnt,6,2) = ".intval($month_start);		
			$condition  .= " and substr(devnt,9,2) = ".intval($day);
		}
		$condition .= $event_filter;

		$condition .= $_SESSION['s_ReqFamille'];
		
		$result_evnt=$this->Statistiques->find(array(
				'fields'      => ' type, id_event, devnt, sDtEvnt, id_individu, oxg_events2.id_family',
				'conditions'  => $condition,
				'order' 	  => "substr(devnt,6,2),substr(devnt,9,2),substr(devnt,1,4)" 			
		));

		//affichage du tableau final selon choix
			
		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==77) { $acte=decode_lng_text("GEDTAG_DEAT")." ".decode_lng_text("GEDTAG_OF"); }
			if ($row_evnt->type!=51 AND $row_evnt->type!=115 AND $row_evnt->type!=77 ) { $acte=decode_lng_text("GEDTAG_EVEN")." ";}
		
			$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, 'acte'=>$acte, 'id_family'=>$row_evnt->id_family, 'id_ind'=>$row_evnt->id_individu, 'nom_indiv'=>'', 'compagne'=>$compagne, 'nom_compagne'=>'');

			// if (count($result_out)!=0) {
				// foreach ($result_out as $key => $row_out)
					// {
						// $timestamp_[$key]  = $row_out['timestamp'];
					// }

				// array_multisort($timestamp_, SORT_NUMERIC, SORT_ASC, $result_out);
							
			// }
		}
		/*
		*	 Recherche des noms des individus et de leurs compagnes
		*/	
//$_SESSION['ind_affiche']=1;
//var_dump($result_out);

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

				$id_ind=$result_out[$key]['id_ind'];
	 			$id_fam=$result_out[$key]['id_family'];
			
				$table = 'oxg_individuals';
				$this->Statistiques->table = $table;	
			
				$condition = "id_individual=". $row_out['id_ind'] .' '." and id_family=".$id_fam;
				
				$result_cont=$this->Statistiques->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 != "") {
//$_SESSION['ind_affiche']=1;	
						$table = 'oxg_events';
						$this->Statistiques->table = $table;	
						
						$condition = " id_event=".$compagne." and id_family=".$id_fam;
						
						$req=$this->Statistiques->findFirst(array(
							'fields'     => 'info2',
							'conditions' => $condition
						));
						
						$compagne = $req->info2;
//$_SESSION['ind_affiche']=1;							
						$table = 'oxg_individuals';
						$this->Statistiques->table = $table;	
						
						$condition = 'id_individual='.$compagne.' '. $_SESSION['s_ReqContemporain'].' and id_family='.$id_fam;
						
						$req=$this->Statistiques->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['t_results_out'] = $result_out;
		
//var_dump($_SESSION['t_results_out']);

		$this->render('birthday');
		
	} // fin de fonction birthday


// ---------------------------------------	
	 function top_10(){
//$_SESSION['ind_affiche']=1;
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='top_10';				

		$this->loadModel('Statistiques');
		
	//Les autres chiffres sont déjà préparés dans Stats_accueil
	
		//calcul du nombre de femmes
		$table = 'oxg_individuals';
		$this->Statistiques->table = $table;
		$condition  = 'sex=1';
		$condition .= $_SESSION['s_ReqFamille'];
		
		$row = $this->Statistiques->find(array(
			'fields'     => 'count(*) as somme',
			'conditions'  => $condition
		));

		$_SESSION['t_stats_accueil']['s_nbr_ind_F'] =$row[0]->somme;
		
	//calcul du nombre d'hommes
		$table = 'oxg_individuals';
		$this->Statistiques->table = $table;
		$condition  = 'sex=0';
		$condition .= $_SESSION['s_ReqFamille'];
		
		$row = $this->Statistiques->find(array(
			'fields'     => 'count(*) as somme',
			'conditions'  => $condition
		));

		$_SESSION['t_stats_accueil']['s_nbr_ind_M'] =$row[0]->somme;
		
	//Calcul de la + grande famille
		$maxi = 0;
		$table = 'oxg_individuals';
		$this->Statistiques->table = $table;
		$condition  = "id_father!='-1' or id_mother!='-1'";
		$condition .= $_SESSION['s_ReqFamille'];
		
		$result = $this->Statistiques->find(array(
			'fields'     => 'count(*) as somme',
			'conditions' => $condition,
			'group'		 => 'id_father, id_mother'
		));
		
		foreach ($result as $k=>$row) {

			if ($row->somme>$maxi) {
				$maxi = $row->somme;
				}
		}
		
		$_SESSION['t_stats_accueil']['s_max_children'] =$maxi;
		
		$this->render('top_10');
		
	 } // fin de fonction top_10
	 
	 
	 
	 function top_date() {
		 
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='top_date';
		 
	// recup variable du formulaire

		if (isset($this->request->params[1])) { $acte =  $this->request->params[1]; }	
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }

		$acte = isset($this->request->data->acte) ? $this->request->data->acte : $_SESSION['s_top_acte'];		
		$nbre = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];

		if ($acte == '') { $acte = 'b'; }
		if ($nbre == '') { $nbre = 15; }
		$_SESSION['s_top_acte'] = $acte;
		$_SESSION['s_top_nbre'] = $nbre;
		
		$_SESSION['t_stats_10'] = array();
		
		$this->loadModel('Statistiques');
		$this->loadModel('Outils');
		
		if ($acte != 'w') {
			if ($acte == 'b') {
				$date = 'date_naissance';
			}
			else {
				$date = 'date_deces';
			}
//$_SESSION['ind_affiche']=1;			
			$table = 'oxg_individuals';
			$this->Statistiques->table = $table;

			$condition  = $date." <>'' ";
			$condition .= $_SESSION['s_ReqFamille'];
			
			$result = $this->Statistiques->find(array(
				'fields'     => 'id_individual, sex, date_naissance, date_deces',
				'conditions' => $condition,
				'order'		 => "right(".$date.",4) DESC",
				'limit'		 => $nbre
			));
			
			foreach ($result as $k=>$row) {
				$individu = $this->Outils->affiche_lien_individu($row->id_individual);
				
				if ($row->sex==1) { $texte=decode_lng_text("STATS_".$type."_F"); $flag=IMAGES."female.png"; }
				if ($row->sex==0) { $texte=decode_lng_text("STATS_".$type."_M"); $flag=IMAGES."male.png"; }
		
				$article = "<img src=".$flag.">";
		
				$article .= " ".$individu. " ".$texte;
		
				if ($row->date_naissance !='' && $acte=='d')  {
					$old = (substr($row->date_deces,-4))-(substr($row->date_naissance,-4));
					$article .= "<i> ".decode_lng_text("STATS_OLD_1")." ".$old." ".decode_lng_text("STATS_OLD_2").",";
				}
			
				if ( strlen($row->$date)==4) {
					$article .= " ".decode_lng_text('STATS_ARTICLE_DATE2');
				}
				else
				{
					$article .= " ".decode_lng_text('STATS_ARTICLE_DATE1');
				}
			
				$article .= " ".$row->$date." </i>";
				
				$_SESSION['t_stats_10'] [] = $article;
				
			}
		
		}
		else {

//$_SESSION['ind_affiche']=1;	
			
			$table = 'oxg_events, oxg_events2';
			$this->Statistiques->table = $table;
			$condition  = "oxg_events.type=115 and left(devnt,4)>1 ";
			$condition  .= " and oxg_events.id_event=oxg_events2.id_event";
			$condition .= " and oxg_events.id_family=".$_SESSION['FAMILY'];
			
			$result_ind = $this->Statistiques->find(array(
				'fields'     => 'devnt, id_individu, oxg_events.id_event,info2 ',
				'conditions' => $condition,
				'order'		 => "devnt DESC",
				'limit'	 	 => $nbre
			));

			foreach ($result_ind as $row_ind) {

						$date1 = $row_ind->devnt;
						$date1 = str_replace("-0", "-", $date1);
						$date1 = str_replace("-", "/", $date1);
						$date2 = $row_ind->sDtEvnt;
						$weddingdate = $date1.":".$date2;
						$article  = decode_date($weddingdate)." ";
						$article .= "<i>".decode_lng_text("GEDTAG_BTW")." </i>";
						$article .=  $this->Outils->affiche_lien_individu($row_ind->id_individu, true, false)."<i> ".decode_lng_text("GEDTAG_AND")." </i> ".$this->Outils->affiche_lien_individu($row_ind->info2, true, false);	

						$_SESSION['t_stats_10'] [] = $article;
					}
				}

		
		 $this->render('top_date');
		 
	 } // fin de fonction top_10
	 
	 function top_prenoms() {
		 	 
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='top_prenoms';
//var_dump($this->request);
		
	// recup variable dans l'URL
		if (isset($this->request->params[1])) { $sexe = $this->request->params[1]; }	
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }
    // recup variable du formulaire
		if (!isset($this->request->params[0])) {
			$sexe = isset($this->request->data->sexe) ? $this->request->data->sexe : $_SESSION['s_top_sexe'];		
			$nbre = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];
		}
			if ($nbre == '') { $nbre = 15; }
		$_SESSION['s_top_sexe'] = $sexe;
		$_SESSION['s_top_nbre'] = $nbre;

		$liste = "";
		$total = 0;	
		$_SESSION['t_prenoms'] = array();
//$_SESSION['ind_affiche']=1;			
		$this->loadModel('Statistiques');
				
		$table = 'oxg_individuals';
		$this->Statistiques->table = $table;
		$condition  = "sex=".$sexe;
		$condition .= $_SESSION['s_ReqFamille'];
		
		$result_ind = $this->Statistiques->find(array(
			'fields'     => 'first_name',
			'conditions' => $condition
		));

		foreach ($result_ind as $row_ind) {
			$row_ind->first_name =str_replace(",", " ",$row_ind->first_name);
			if ($liste!="") {$liste = $liste." ".SuppAccents($row_ind->first_name);}
			else {$liste = SuppAccents($row_ind->first_name);}
			$total = $total + 1;
		}

		$liste = explode(" ",$liste);
		$result = array_count_values($liste);
		arsort($result);
//		var_dump($liste);
//		var_dump($result);

		$rank = 0;

		foreach ($result as $key => $val) {
			if ($key != '' && $key != '?') {
				$rank = $rank + 1;
				if ($rank <= $nbre) {
					$perc = round($val/$total*100,1);
					$link = "<a href='".Router::url('statistiques/detail_prenom/'.$key)."'>".$key."</a>";
				
					$_SESSION['t_prenoms'] [] = array('rank'=>$rank, 'link'=>$link, 'val'=>$val, 'perc'=>$perc);
				}
			}
		}
		
	 }	 // fin de fonction top_prenoms
	 
	 
	 function detail_prenom() {
		 
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='stats_patro';
		 
		if (isset($this->request->params[0])) { $firstname = $this->request->params[0]; }
		
		$_SESSION['t_prenom_detail'] = array();
//$_SESSION['ind_affiche']=1;			
		$this->loadModel('Statistiques');
		$this->loadModel('Outils');
				
		$table = 'oxg_individuals';
		$this->Statistiques->table = $table;

		$condition  = "(first_name LIKE '".$firstname."' or first_name LIKE '".$firstname." %' or first_name LIKE '% ".$firstname." %' or first_name LIKE '%".$firstname."')" ;
		$condition .= $_SESSION['s_ReqFamille'];
	
		$result_prenom = $this->Statistiques->find(array(
			'fields'     => 'id_individual, first_name',
			'conditions' => $condition
		));
//var_dump($result_prenom);
		foreach ($result_prenom as $row_prenom) {
			if ($row_prenom->sex==1) { $flag="female.png"; }
			if ($row_prenom->sex==0) { $flag="male.png"; }
			
			$article  = "  <img src=".IMAGES.$flag."> ";
			$article .= $this->Outils->affiche_lien_individu($row_prenom->id_individual, true, true);
		
			$_SESSION['t_prenom_detail'] [] = $article;
		}
	 }	 // fin de fonction detail_prenom
	 
	 function top_patro() {
		  
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='zoom_prenom';
		
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }
		if (!isset($this->request->params[0])) {
			$nbre = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];
		}
		$_SESSION['s_top_nbre'] = $nbre;
		
		$_SESSION['t_patro_detail']=array();
		
		$this->loadModel('Statistiques');
				
		$table = 'oxg_individuals';
		$this->Statistiques->table = $table;

		$condition  = "1=1";
		$condition .= $_SESSION['s_ReqFamille'];
//$_SESSION['ind_affiche']=1;		
		$result_temp = $this->Statistiques->find(array(
			'temp'       => "temp_patronyms",
			'fields'     => 'id_patronym, COUNT(*) as somme ',
			'conditions' => $condition,
			'group'      => "id_patronym",
			'order'      => "somme DESC",
			'limit'      => $nbre
		));

// recherche des libellés des patronymes		
		$table = 'temp_patronyms, oxg_patronyms ';
		$this->Statistiques->table = $table;

		$condition  = "oxg_patronyms.id_patronym = temp_patronyms.id_patronym";

//$_SESSION['ind_affiche']=1;		
		$result_patro = $this->Statistiques->find(array(
			'fields'     => 'oxg_patronyms.id_patronym as id_patronym, libelle, somme',
			'conditions' => $condition,
			'order'      => "somme DESC"
		));
// var_dump($result_patro);			
		foreach ($result_patro as $row_patro) {
			
		  			$link = "<a href'".router::url('patronymes/detail_patro/'.$row_patro->id_patronym)." >".$row_patro->libelle."</a>";
					
					$_SESSION['t_patro_detail'] [] = array('link'=>$link, 'somme'=>$row_patro->somme);
		}
	  }	 // fin de fonction top_patro
	 

	function top_age()	 {
		
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='zoom_age';
		
		// recup variable dans l'URL
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }
    // recup variable du formulaire
		if (!isset($this->request->params[0])) {
			$nbre = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];
		}
		if ($nbre == '') { $nbre = 15; }
		$_SESSION['s_top_nbre'] = $nbre;
		
		$_SESSION['t_age_details']=array();
		$_SESSION['t_age_wedding']=array();
		$_SESSION['t_age_death'] = array();
		
		$wedding_age_total_h = 0;
		$count_h = 0;
		$wedding_age_total_f = 0;
		$count_f = 0;
		
		$this->loadModel('Statistiques');
				
	// recherche des evenements mariages		
		$table = "oxg_events, oxg_events2";
		$this->Statistiques->table = $table;

		$condition   = "oxg_events.id_family=".$_SESSION['FAMILY'];
		$condition  .= " and (oxg_events.type = 115)";
		$condition  .= " and oxg_events.id_event=oxg_events2.id_event";
		$condition  .= " and oxg_events.id_family=oxg_events2.id_family";
		$condition  .= " and length(devnt)=10";
		
//$_SESSION['ind_affiche']=1;		
		$result_marriage = $this->Statistiques->find(array(
			'fields'     => "id_individu, devnt, sDtEvnt, info2 ",
			'conditions' => $condition
		));
	//var_dump($result_marriage);
	
	foreach ($result_marriage as $row_marr) {
		//var_dump ($row_marr);
		$wedding_age_h=0;
		$wedding_age_f=0;
		// recherche des dates des individus	
		$table = "oxg_individuals";
		$this->Statistiques->table = $table;
//$_SESSION['ind_affiche']=1;
		$condition   = "date_naissance!=''";
		$condition .=  " and id_individual = ".$row_marr->id_individu;
		$condition .= $_SESSION['s_ReqFamille'];
	
		$result_individu = $this->Statistiques->findFirst(array(
			'fields'     => "sex, date_naissance",
			'conditions' => $condition

		));
		
		if ($result_individu  && strlen($result_individu->date_naissance) >=4) {
//	var_dump($result_individu);
		
		//Recherche age du marié
		if(strlen($result_individu->date_naissance) > 4) {
			$birthdate_h = substr($result_individu->date_naissance,-4);
		}
		else{
			$birthdate_h = $result_individu->date_naissance;
		}

		
		$wedding_age_h = intval(substr($row_marr->devnt,0,4)) - intval($birthdate_h);
		$_SESSION['t_age_wedding'][] =array('id'=>$row_marr->id_individu, 'sex' =>$result_individu->sex, 'age'=>$wedding_age_h, 'date'=>$row_marr->sDtEvnt);
// echo 'id= '.$row_marr->id_individu.' date mariage '.substr($row_marr->devnt,0,4). " - date naiss. ".substr($result_individu->date_naissance,-4)." ". floor($wedding_age_h). "<br>";
// if (floor($wedding_age_h)>50 or floor($wedding_age_h)<=0) { $ERREUR_M+=1;}
		$wedding_age_total_h += intval($wedding_age_h); 
		$count_h += 1;
		}
	
	
		$table = "oxg_individuals";
		$this->Statistiques->table = $table;
// $_SESSION['ind_affiche']=1;
		$condition   = "date_naissance!=''";
		$condition .=  " and id_individual = ".$row_marr->info2;
		$condition .= $_SESSION['s_ReqFamille'];
		
		$result_individu = $this->Statistiques->findFirst(array(
			'fields'     => "sex, date_naissance",
			'conditions' => $condition
		));
		
		if ($result_individu && strlen($result_individu->date_naissance) >=4) { 
//var_dump($result_individu);

		if(strlen($result_individu->date_naissance)>4) {
			$birthdate_f = substr($result_individu->date_naissance,-4);
		}
		else {
			$birthdate_f = $result_individu->date_naissance;
		}

		
		$wedding_age_f = intval(substr($row_marr->devnt,0,4)) - intval($birthdate_f);
		$_SESSION['t_age_wedding'][] =array('id'=>$row_marr->info2, 'sex' =>$result_individu->sex, 'age'=>$wedding_age_f, 'date'=>$row_marr->sDtEvnt, 'date2'=>$row_marr->devnt);
		
		$wedding_age_total_f += intval($wedding_age_f);
		$count_f += 1;
		}
	}

		$wedding_age_average_h = round($wedding_age_total_h / $count_h);
		$wedding_age_average_f = round($wedding_age_total_f / $count_h);

	
	// recherche des éléments sur les décès
		$count_h = 0;
		$count_adults_h = 0;
		$count_children_h = 0;
		$count_f = 0;
		$count_adults_f = 0;
		$count_children_f = 0;
		$death_age_total_h = 0;
		$death_age_total_adults_h = 0;
		$death_age_total_children_h = 0;
		$death_age_total_f = 0;
		$death_age_total_adults_f = 0;
		$death_age_total_children_f = 0;
	
		$table = "oxg_individuals";
		$this->Statistiques->table = $table;
// $_SESSION['ind_affiche']=1;
		$condition   = "date_naissance!='' and date_deces != '' ";
		$condition .= $_SESSION['s_ReqFamille'];
		
		//select sex, date_naissance, date_deces from oxg_individuals WHERE date_naissance!=\"\" and date_deces!=\"\" $sReqCont $sReqFam");
		$result_individu = $this->Statistiques->find(array(
			'fields'     => "id_individual, sex, date_naissance, date_deces",
			'conditions' => $condition
		));

		// Calcul de l'age moyen au moment du décès 
	
//var_dump($result_individu);
		foreach ($result_individu as $row_individu) {
			if(strlen($result_individu->date_naissance) > 4) {
				$birthdate_h = substr($result_individu->date_naissance,-4);
			}
			else{
				$birthdate_h = $result_individu->date_naissance;
			}
			if(strlen($row_individu->date_deces)>4) {
				$deathdate = substr($row_individu->date_deces,-4);
			}
			else {
				$deathdate = $row_individu->date_deces;
			}
		
			$death_age = intval($deathdate) - intval($birthdate);
			if ($row_individu->sex ==0) {
				$death_age_total_h += $death_age;
				$count_h += 1;
				if ($death_age>20) {
					$death_age_total_adults_h += $death_age;
					$count_adults_h += 1;
				}
				else {
					$death_age_total_children_h += $death_age;
					$count_children_h += 1;
				}
			}
			else{
				$death_age_total_f += $death_age;
				$count_f += 1;
				if ($death_age>20) {
					$death_age_total_adults_f += $death_age;
					$count_adults_f += 1;
				}
				else {
					$death_age_total_children_f += $death_age;
					$count_children_f += 1;
				}
			
			}
		}

		// calculs des moyennes
		if ($count_h!=0) {$death_age_average_h = $death_age_total_h / $count_h;} else {$death_age_average_h = 1;}
		// echo $death_age_total_h.' '.$count_h.'<br>';
		if ($count_f!=0) {$death_age_average_f = $death_age_total_f / $count_f;} else {$death_age_average_f = 1;}
		
		if ($count_adults_h!=0) {$death_age_average_adults_h = $death_age_total_adults_h / $count_adults_h;} else {$death_age_average_adults_h = 1;}
		if ($count_adults_f!=0) {$death_age_average_adults_f = $death_age_total_adults_f / $count_adults_f;} else {$death_age_average_adults_f = 1;}
		
		if ($count_children_h!=0) {$death_age_average_children_h = $death_age_total_children_h / $count_children_h;} else {$death_age_average_children_h = 1;}
		if ($count_children_f!=0) {$death_age_average_children_f = $death_age_total_children_f / $count_children_f;} else {$death_age_average_children_f = 1;}
		
		$_SESSION['t_age_death'][] =array('id'=>$row_individu->id_individual, 'sex' =>$row_individu->sex, 'age'=>$wedding_age_f, 'date_naissance'=>$row_individu->date_naissance,'date_deces'=>$row_individu->date_deces);

	
	$_SESSION['t_age_details'] = array('wedding_age_average_h'=>$wedding_age_average_h, 'wedding_age_average_f'=>$wedding_age_average_f, 'death_age_average_h'=>$death_age_average_h, 'death_age_average_f'=>$death_age_average_f, 'death_age_average_adults_h'=>$death_age_average_adults_h, 'death_age_average_adults_f'=>$death_age_average_adults_f, 'death_age_average_children_h'=>$death_age_average_children_h,  'death_age_average_children_f'=>$death_age_average_children_f);
//var_dump($_SESSION['t_age_wedding']);
	}	// fin de fonction top_age  
	
	
	function top_wedding() {
		
			
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='zoom_wedding';
		
		$this->loadModel('Outils');
// $_SESSION['ind_affiche']=1;
		$_SESSION['t_ligne'] =array();
		// recup variable dans l'URL
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }
		if (isset($this->request->params[1])) { $ordre = $this->request->params[1]; }
    // recup variable du formulaire
		if (!isset($this->request->params[0])) {
			$nbre  = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];
			$ordre = isset($this->request->data->ordre) ? $this->request->data->ordre : $_SESSION['s_top_ordre'];
		}
		if ($nbre == '') { $nbre = 15; }
		if ($ordre == '') { $ordre = 'asc'; }
		
		$_SESSION['s_top_nbre'] = $nbre;
		$_SESSION['s_top_ordre'] = $ordre;
		
		foreach ($_SESSION['t_age_wedding'] as $key => $row) {
			// $_SESSION['t_age_wedding'][] =array('id'=>$row_marr->id_individu, 'sex' =>$result_individu->sex, 'age'=>$wedding_age_h, 'date'=>$row_marr->sDtEvnt);
			$t_age[$key]  = $row['age'];
			$t_date[$key] = $row['date2'];
		}
		
		if ($ordre=="asc") { array_multisort($t_age, SORT_ASC, $t_date, SORT_ASC, $_SESSION['t_age_wedding']);}
		if ($ordre=="desc") { array_multisort($t_age, SORT_DESC, $t_date, SORT_ASC, $_SESSION['t_age_wedding']);}

		
		$k=0;
		foreach( $_SESSION['t_age_wedding'] as $k => $ligne) {
		
			if ($k>=$nbre) { break;}
			
			if ($ligne['sex'] == 1) { $flag="female.png"; }
			if ($ligne['sex'] == 0) { $flag="male.png"; }
			
			$_SESSION['t_ligne'] [] = "<tr><td>".decode_date($ligne['date'])."</td><td><img src=".IMAGES.$flag."> ".$this->Outils->affiche_lien_individu($ligne['id'])." ".decode_lng_text('STATS_OLD_1')." ".$ligne['age']." ".decode_lng_text('STATS_OLD_2')."</td></tr>";
			
			$k++;
		}
//var_dump($_SESSION['t_ligne']);
		$this->render('top_wedding');
	} // fin de fonction top_wedding
	

/*	
* Préparation pour les personnes les plus agées de la base
*/		
	function top_oldest() {
		
			
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='zoom_oldest';
		
		$sLimitAge="110";	// Age limit displayed


// $_SESSION['ind_affiche']=1;
		$_SESSION['t_ligne'] =array();
		// recup variable dans l'URL
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }

    // recup variable du formulaire
		if (!isset($this->request->params[0])) {
			$nbre  = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];
		}
		if ($nbre == '') { $nbre = 15; }
		
		$_SESSION['s_top_nbre'] = $nbre;
		
		$this->loadModel('Statistiques');
		$this->loadModel('Outils');
		
		$table = "oxg_individuals, oxg_events2 e_birth";
		$this->Statistiques->table = $table;
//$_SESSION['ind_affiche']=1;

		$fields  = "id_individual, oxg_individuals.id_family, first_name, id_patronym, date_naissance, date_deces, sex, sosa,";
		$fields .= " DATE_FORMAT(e_birth.devnt,'%Y-%m-%d') AS php_date_naissance,";
		$fields .= " DATE_FORMAT(CURDATE(),'%Y-%m-%d') AS php_date_jour";
		
		$condition   = "oxg_individuals.id_individual=e_birth.id_individu";
		$condition  .= " and oxg_individuals.id_family=e_birth.id_family ";
		$condition  .= " and e_birth.devnt is not null and date_naissance<>'' and date_deces ='' ";
		$condition  .= " and e_birth.type=51";
		$condition  .= " and TIMESTAMPDIFF(YEAR,e_birth.devnt,CURDATE())<".$sLimitAge;	
		$condition  .= " and oxg_individuals.id_family=".$_SESSION['FAMILY'];
		
		$result_individu = $this->Statistiques->find(array(
			'fields'     => $fields,
			'conditions' => $condition,
			'order'      => "e_birth.devnt ASC",
			'limit'		 => $nbre
		));		

//var_dump($result_individu);		
		foreach ($result_individu as $row_individu) {

			if ($row_individu->sex==1) { $article = decode_lng_text("STATS_BIRTH_F"); $texte_age=decode_lng_text("SOSA_AGED_F"); $flag="female.png"; }
			if ($row_individu->sex==0) { $article = decode_lng_text("STATS_BIRTH_M"); $texte_age=decode_lng_text("SOSA_AGED_M"); $flag="male.png"; }
			
			if (substr($row_individu->date_naissance,0,6)=="autour" ) {
				$article .= " ".decode_lng_text('STATS_ARTICLE_DATE3');
				$birthdate = substr($row_individu->date_naissance, -(strlen($row_individu->date_naissance)- 9));
			}
			else if (substr($row_individu->date_naissance,0,8)=="calculée") {
				$article .= " ".decode_lng_text('STATS_ARTICLE_DATE3');
				$birthdate = substr($row_individu->date_naissance, -(strlen($row_individu->date_naissance)- 11));
				
			}
			else if(strlen($row_individu->date_naissance)>4) {
					$article .= " ".decode_lng_text('STATS_ARTICLE_DATE1');
					$birthdate =  $row_individu->date_naissance;
			}
					

			$texte_age = " (".$age." ".dateDiff(strtotime($row_individu->php_date_jour) , strtotime($row_individu->php_date_naissance)).")";
					
			$_SESSION['t_ligne'] [] = "<tr><td><img src=".IMAGES.$flag."> ".$this->Outils->affiche_lien_individu($row_individu->id_individual)." ".$article." ".$birthdate." ".$texte_age."</td></tr>";

		}
//var_dump($_SESSION['t_ligne']);

		$this->render('top_oldest');
	} // fin de fonction top_oldest

/*	
* Préparation pour les personnes ayant vécu le plus longtemps
*/	
	function top_oldestdead() {
		
			
		$_SESSION['s_groupe']='stats';
		$_SESSION['s_page']= 'stats';
		$_SESSION['s_vue']='zoom_oldestdead';
		
		$sLimitAge="110";	// Age limit used to limit selection


// $_SESSION['ind_affiche']=1;
		$_SESSION['t_ligne'] =array();
		// recup variable dans l'URL
		if (isset($this->request->params[0])) { $nbre = $this->request->params[0]; }

    // recup variable du formulaire
		if (!isset($this->request->params[0])) {
			$nbre  = isset($this->request->data->nbre) ? $this->request->data->nbre : $_SESSION['s_top_nbre'];
		}
		if ($nbre == '') { $nbre = 15; }
		
		$_SESSION['s_top_nbre'] = $nbre;
		
		$k=0;
		
		$this->loadModel('Statistiques');
		$this->loadModel('Outils');

		$table = "oxg_individuals, oxg_events2 e_birth, oxg_events2 e_death ";
		$this->Statistiques->table = $table;
//$_SESSION['ind_affiche']=1;

		$fields  = "id_individual, oxg_individuals.id_family, first_name, id_patronym, date_naissance, date_deces, sex, sosa,";
		$fields .= " DATE_FORMAT(adddate(e_birth.devnt,datediff(CURDATE(),e_death.devnt)),'%Y-%m-%d') AS php_date_naissance,";
		$fields .= " DATE_FORMAT(adddate(e_death.devnt,datediff(CURDATE(),e_death.devnt)),'%Y-%m-%d') AS php_date_deces";
		
		$condition   = "oxg_individuals.id_individual=e_birth.id_individu";
		$condition  .= " and oxg_individuals.id_individual=e_death.id_individu";
		$condition  .= " and oxg_individuals.id_family=e_birth.id_family ";
		$condition  .= " and oxg_individuals.id_family=e_death.id_family ";
		$condition  .= " and e_birth.devnt is not null and date_naissance<>\"\" ";
		$condition  .= " and e_death.devnt is not null and date_deces<>\"\" ";
		$condition  .= " and e_birth.type=51";
		$condition  .= " and e_death.type=77";
		$condition  .= " and oxg_individuals.id_family=".$_SESSION['FAMILY'];
		
		
		$result_individu = $this->Statistiques->find(array(
			'fields'     => $fields,
			'conditions' => $condition,
			'order'      => "datediff(e_death.devnt,e_birth.devnt) DESC ",
			'limit'		 => $nbre
		));
		
//var_dump($result_individu);
			
		foreach ($result_individu as $row_individu) {
			
				if ($row_individu->sex==1)
				{
					$age=decode_lng_text("SOSA_DEATH_F")." ".decode_lng_text("STATS_OLD_1");
					$flag="female.png";
				}
				else if ($row_individu->sex==0)
				{
					$age=decode_lng_text("SOSA_DEATH_M")." ".decode_lng_text("STATS_OLD_1");
					$flag="male.png";
				}
				if ($row_individu->sex==1) { $article = decode_lng_text("STATS_BIRTH_F"); $texte_age=decode_lng_text("SOSA_AGED_F"); $flag="female.png"; }
				if ($row_individu->sex==0) { $article = decode_lng_text("STATS_BIRTH_M"); $texte_age=decode_lng_text("SOSA_AGED_M"); $flag="male.png"; }
				
				if (substr($row_individu->date_naissance,0,6)=="autour" ) {
					$article .= " ".decode_lng_text('STATS_ARTICLE_DATE3');
					$birthdate = substr($row_individu->date_naissance, -(strlen($row_individu->date_naissance)- 9));
				}
				else if (substr($row_individu->date_naissance,0,8)=="calculée") {
					$article .= " ".decode_lng_text('STATS_ARTICLE_DATE3');
					$birthdate = substr($row_individu->date_naissance, -(strlen($row_individu->date_naissance)- 11));
					
				}
				else if(strlen($row_individu->date_naissance)>4) {
						$article .= " ".decode_lng_text('STATS_ARTICLE_DATE1');
						$birthdate =  $row_individu->date_naissance;
				}

				$texte_age = " (".$age." ".dateDiff(strtotime($row_individu->php_date_deces) , strtotime($row_individu->php_date_naissance)).")";
							
					
				$_SESSION['t_ligne'] [] = "<tr><td><img src=".IMAGES.$flag."> ".$this->Outils->affiche_lien_individu($row_individu->id_individual, true, true)." ".$texte_age."</td></tr>";

			}
			//var_dump($_SESSION['t_ligne']);

		
		$this->render('top_oldestdead');
	} // fin de fonction top_oldestdeaad
	
} // fin de la classe FamillesController
?>