<?php
// ************************************
// *** Statistics                   ***
// *** First version: René Janssen. ***
// *** Updated by: Huub.            ***
// ************************************

$kop="statistics";
include("header.php");
include("menu.php");
// *** Standard function for names ***
include('include/person_cls.php');
include('include/language_date.php');
include('include/date_place.php');
include('include/calculate_age_cls.php');

// *** Get general data from family tree ***
$sql = "SELECT * FROM humo_trees WHERE tree_prefix='".veilig($_SESSION['tree_prefix'])."'";
$datasql = mysql_query($sql,$db);
@$dataDb=mysql_fetch_object($datasql);

$datum=$dataDb->tree_date;
$maand=''; // *** empty date ***
if (substr($datum,5,2)=='01'){ $maand=' '.$language["date_jan"].' ';}
if (substr($datum,5,2)=='02'){ $maand=' '.$language["date_feb"].' ';}
if (substr($datum,5,2)=='03'){ $maand=' '.$language["date_mar"].' ';}
if (substr($datum,5,2)=='04'){ $maand=' '.$language["date_apr"].' ';}
if (substr($datum,5,2)=='05'){ $maand=' '.$language["date_may"].' ';}
if (substr($datum,5,2)=='06'){ $maand=' '.$language["date_jun"].' ';}
if (substr($datum,5,2)=='07'){ $maand=' '.$language["date_jul"].' ';}
if (substr($datum,5,2)=='08'){ $maand=' '.$language["date_aug"].' ';}
if (substr($datum,5,2)=='09'){ $maand=' '.$language["date_sep"].' ';}
if (substr($datum,5,2)=='10'){ $maand=' '.$language["date_oct"].' ';}
if (substr($datum,5,2)=='11'){ $maand=' '.$language["date_nov"].' ';}
if (substr($datum,5,2)=='12'){ $maand=' '.$language["date_dec"].' ';}
$datum=substr($datum,8,2).$maand.substr($datum,0,4);

echo '<table width="800" class="humo" align="center">';

// Laatste update van database
print "<tr><td>".$language["stat_latest_update"]."</td>\n";
print "<td align='center'><i>$datum</i></td>\n";
echo '<td><br></td>';
echo '</tr>';

echo '<tr><td colspan="3"><br></td></tr>';

//Aantal gezinnen in database
print "<tr><td>".$language["stat_nr_families"]."</td>\n";
print "<td align='center'><i>$dataDb->tree_families</i></td>\n";
echo '<td><br></td></tr>';

//Gezin met meeste kinderen
print "<tr><td>".$language["stat_most_children"]."</td>\n";
$testaantal="2"; //minimaal 2 kinderen
$res=@mysql_query("SELECT * FROM ".veilig($_SESSION['tree_prefix'])."family");
while (@$record=mysql_fetch_object($res)){
	$aantal=substr_count($record->fam_children, ';');
	$aantal=$aantal + 1;
	if ($aantal > $testaantal){
		$testaantal = "$aantal";
		$man = $record->fam_man;
		$vrouw = $record->fam_woman;
		$gezin_gedcomnummer=$record->fam_gedcomnumber;
	}
}
print "<td align='center'><i>$testaantal</i></td>\n";
$res=@mysql_query("SELECT * FROM ".veilig($_SESSION['tree_prefix'])."person WHERE pers_gedcomnumber='".$man."'");
@$record=mysql_fetch_object($res);
$persoon_cls = New persoon_cls;
$persoon_cls->construct($record);
$man=$persoon_cls->naam($record);
$index = "$record->pers_indexnr";

$res=@mysql_query("SELECT * FROM ".veilig($_SESSION['tree_prefix'])."person WHERE pers_gedcomnumber ='".$vrouw."'");
@$record=mysql_fetch_object($res);
$persoon_cls = New persoon_cls;
$persoon_cls->construct($record);
$vrouw=$persoon_cls->naam($record);

print '<td align="center"><a href="gezin.php?id='.$gezin_gedcomnummer.'"><i><b>'.$man.$language["pers_and"].$vrouw.'</b></i> </a></td></tr>';

// Aantal personen in database
$nr_persons=$dataDb->tree_persons;
print "<tr><td>".$language["stat_nr_persons"]."</td>\n";
print "<td align='center'><i>$nr_persons</i></td>\n";
echo '<td><br></td></tr>';

echo '</table>';

// *** Men and women table ***
function show_person($row) {
	global $humo_option, $uri_pad;
	$persoon_cls = New persoon_cls;
	$persoon_cls->construct($row);
	if ($humo_option["url_rewrite"]=="j"){
		// *** $uri_pad is gemaakt in header.php ***
		return '<td align="center"><a href="'.$uri_pad.'gezin/'.$_SESSION['tree_prefix'].'/'.$row->pers_indexnr.
			'/'.$row->pers_gedcomnumber.'/"><i><b>'.$persoon_cls->naam($row).'</b></i> </a> </td>';
	}
	else{
		return '<td align="center"><a href="gezin.php?id='.$row->pers_indexnr.'"><i><b>'.$persoon_cls->naam($row).'</b></i> </a> </td>';
	}
}	

echo '<br><table width="900" class="humo" align="center">';

echo '<tr style="font-weight:bold; text-align:center;"><td>'.$language["stat_item"].'</td><td colspan="2">'.$language["stat_men"].'</td><td colspan="2">'.$language["stat_women"].'</td></tr>';

// *** Aantal mannen ***
$persoon=mysql_query("SELECT * FROM ".veilig($_SESSION['tree_prefix'])."person WHERE pers_sexe='m'",$db);
$personen=mysql_num_rows($persoon);
print "<tr><td>".$language["stat_nr_persons"]."</td>\n";
print "<td align='center'><i>$personen</i></td>\n";
@$percent=($personen/$nr_persons)*100;
echo '<td align="center">'.floor($percent).'%</td>';

// *** Aantal vrouwen ***
$persoon=mysql_query("SELECT * FROM ".veilig($_SESSION['tree_prefix'])."person WHERE pers_sexe='f'",$db);
$personen=mysql_num_rows($persoon);
print "<td align='center'><i>$personen</i></td>\n";
@$percent=($personen/$nr_persons)*100;
echo '<td align="center">'.floor($percent).'%</td>';

echo '<tr><td colspan="5"><br></td></tr>';

// Oldest pers_birth_date man. Check only full birth date (10 or 11 characters).
print "<tr><td>".$language["stat_oldest_birth"]."</td>\n";
$res = mysql_query("SELECT *, STR_TO_DATE(pers_birth_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_birth_date)=10 OR CHAR_LENGTH(pers_birth_date)=11)
	AND pers_sexe='M'
	ORDER BY search LIMIT 0,1
	") or die (mysql_error());
$row=mysql_fetch_object($res);
if ($row->pers_birth_date!=NULL){
	print "<td align='center'><i>".datumplaats($row->pers_birth_date,'')."</i></td>\n";
	echo show_person($row);
}
// Oldest pers_birth_date woman
$res = mysql_query("SELECT *, STR_TO_DATE(pers_birth_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_birth_date)=10 OR CHAR_LENGTH(pers_birth_date)=11)
	AND pers_sexe='F'
	ORDER BY search LIMIT 0,1
	") or die (mysql_error());
$row=mysql_fetch_object($res);
if (@$row->pers_birth_date!=NULL){
	print "<td align='center'><i>".datumplaats($row->pers_birth_date,'')."</i></td>\n";
	echo show_person($row);
}

// Youngest pers_birth_date man
print "<tr><td>".$language["stat_youngest_birth"]."</td>\n";
$res = mysql_query("SELECT *, STR_TO_DATE(pers_birth_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_birth_date)=10 OR CHAR_LENGTH(pers_birth_date)=11)
	AND pers_sexe='M'
	ORDER BY search DESC LIMIT 0,1") or die (mysql_error());
$row=mysql_fetch_object($res);
if ($row->pers_birth_date!=NULL){
	$persoon_cls = New persoon_cls;
	$persoon_cls->construct($row);
	if (!$persoon_cls->privacy){
		echo '<td align="center"><i>'.datumplaats($row->pers_birth_date,'').'</i></td>'; 
		echo show_person($row);
	}
	else{
		echo '<td align="center"><b>'.$language["privacy"].'</b></td><td align="center"><b>'.$language["privacy"].'</b></td>';
	}
}
// Youngest pers_birth_date woman
$res = mysql_query("SELECT *, STR_TO_DATE(pers_birth_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_birth_date)=10 OR CHAR_LENGTH(pers_birth_date)=11)
	AND pers_sexe='F'
	ORDER BY search DESC LIMIT 0,1") or die (mysql_error());
$row=mysql_fetch_object($res);
if ($row->pers_birth_date!=NULL){
	$persoon_cls = New persoon_cls;
	$persoon_cls->construct($row);
	if (!$persoon_cls->privacy){
		echo '<td align="center"><i>'.datumplaats($row->pers_birth_date,'').'</i></td>'; 
		echo show_person($row);
	}
	else{
		echo '<td align="center"><b>'.$language["privacy"].'</b></td><td align="center"><b>'.$language["privacy"].'</b></td></tr>';
	}
}

// Oldest death date man
print "<tr><td>".$language["stat_oldest_death"]."</td>\n";
$res = mysql_query("SELECT *, STR_TO_DATE(pers_death_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_death_date)=10 OR CHAR_LENGTH(pers_death_date)=11)
	AND pers_sexe='M'
	ORDER BY search LIMIT 0,1") or die (mysql_error());
$row=mysql_fetch_object($res);
if (@$row->pers_death_date!=NULL){
	print "<td align='center'><i>".datumplaats($row->pers_death_date,'')."</i></td>\n";
	echo show_person($row);
}
// Oldest death date woman
$res = mysql_query("SELECT *, STR_TO_DATE(pers_death_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_death_date)=10 OR CHAR_LENGTH(pers_death_date)=11)
	AND pers_sexe='F'
	ORDER BY search LIMIT 0,1") or die (mysql_error());
$row=mysql_fetch_object($res);
if (@$row->pers_death_date!=NULL){
	print "<td align='center'><i>".datumplaats($row->pers_death_date,'')."</i></td>\n";
	echo show_person($row);
}

// Youngest death date man
print "<tr><td>".$language["stat_youngest_death"]."</td>\n";
$res = mysql_query("SELECT *, STR_TO_DATE(pers_death_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_death_date)=10 OR CHAR_LENGTH(pers_death_date)=11)
	AND pers_sexe='M'
	ORDER BY search DESC LIMIT 0,1") or die (mysql_error());
$row=mysql_fetch_object($res);
if ($row->pers_death_date!=NULL){
	print "<td align='center'><i>".datumplaats($row->pers_death_date,'')."</i></td>\n";
	$pers_prefix=str_replace("_", " ", $row->pers_prefix);
	echo show_person($row);
}
// Youngest death date woman
$res = mysql_query("SELECT *, STR_TO_DATE(pers_death_date,'%e %b %Y') as search
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE (CHAR_LENGTH(pers_death_date)=10 OR CHAR_LENGTH(pers_death_date)=11)
	AND pers_sexe='F'
	ORDER BY search DESC LIMIT 0,1") or die (mysql_error());
$row=mysql_fetch_object($res);
if ($row->pers_death_date!=NULL){
	print "<td align='center'><i>".datumplaats($row->pers_death_date,'')."</i></td>\n";
	$pers_prefix=str_replace("_", " ", $row->pers_prefix);
	echo show_person($row);
}

echo '<tr><td colspan="5"><br></td></tr>';

//Langst levende man
$man_min=50;
$man_max=0;
$man_min_married=50;
$man_max_married=0;

print "<tr><td>".$language["stat_oldest_person"]."</td>\n";

$res = @mysql_query("SELECT *
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE pers_sexe='M' AND pers_death_date LIKE '_%'
	") or die(mysql_error());

$testjaar="10";
while(@$record = mysql_fetch_object($res)){
	$age = New berekening_jaar_cls;
	$age_cal=$age->leeftijd_berekenen($record->pers_bapt_date,$record->pers_birth_date,$record->pers_death_date,true);
	$age_man[]=$age_cal;
	if ($age_cal>$man_max){ $man_max=$age_cal; }
	if ($age_cal>0 AND $age_cal<$man_min){ $man_min=$age_cal; }

	if ($record->pers_fams!='' AND $age_cal>0){
		$age_man_married[]=$age_cal;
		if ($age_cal>$man_max_married){ $man_max_married=$age_cal; }
		if ($age_cal>0 AND $age_cal<$man_min_married){ $man_min_married=$age_cal; }
	}
	else{
		$age_man_unmarried[]=$age_cal;
	}

	$leeftijd=$age_cal;
	if ($leeftijd >= $testjaar && $leeftijd < 120){
		$index = "$record->pers_indexnr";
		$testjaar = "$leeftijd";
		$oldest_person=$record;
	}
}
if (isset($oldest_person)){
	print '<td align="center"><i>'.$testjaar.' '.$language["age_year"]."</i></td>\n";
	echo show_person($oldest_person);
}

//vrouw
$woman_min=50;
$woman_max=0;
$woman_min_married=50;
$woman_max_married=0;

$res = @mysql_query("SELECT *
	FROM ".veilig($_SESSION['tree_prefix'])."person
	WHERE pers_sexe='F' AND pers_death_date LIKE '_%'
	") or die(mysql_error());
$testjaar="10";
while(@$record = mysql_fetch_object($res)){
	$age = New berekening_jaar_cls;
	$age_cal=$age->leeftijd_berekenen($record->pers_bapt_date,$record->pers_birth_date,$record->pers_death_date,true);
	$age_woman[]=$age_cal;
	if ($age_cal>$woman_max){ $woman_max=$age_cal; }
	if ($age_cal>0 AND $age_cal<$woman_min){ $woman_min=$age_cal; }

	if ($record->pers_fams AND $age_cal>0){
		$age_woman_married[]=$age_cal;
		if ($age_cal>$woman_max_married){ $woman_max_married=$age_cal; }
		if ($age_cal>0 AND $age_cal<$woman_min_married){ $woman_min_married=$age_cal; }
	}
	else{
		$age_woman_unmarried[]=$age_cal;
	}

	//$leeftijd = ($record->ovjaar - $record->gebjaar);
	$leeftijd=$age_cal;
	if ($leeftijd >= $testjaar && $leeftijd < 120){
		$index = "$record->pers_indexnr";
		$testjaar = "$leeftijd";
		$oldest_person=$record;
	}
}
if (isset($oldest_person)){
	print "<td align='center'><i>$testjaar ".$language["age_year"]."</i></td>\n";
	echo show_person($oldest_person);
}

// Gemiddelde leeftijd
print "<tr><td>".$language["stat_average_age"]."</td>\n";

echo '<td align="center">';
@$average=(array_sum($age_man) / count($age_man));
echo round($average,1);
echo '</td>';
if ($man_min==0){ $man_min='0'; }
echo '<td align="center">'.$man_min.' - '.$man_max.'</td>';

echo '<td align="center">';
@$average=array_sum($age_woman) / count($age_woman);
echo round($average,1);
echo '</td>';
if ($woman_min==0){ $woman_min='0'; }
echo '<td align="center">'.$woman_min.' - '.$woman_max.'</td>';
echo '</tr>';

// Gemiddelde leeftijd GEHUWD
print "<tr><td>".$language["stat_average_age_mar"]."</td>\n";

echo '<td align="center">';
@$average=(array_sum($age_man_married) / count($age_man_married));
echo round($average,1);
echo '</td>';
if ($man_min_married==0){ $man_min_married='0'; }
echo '<td align="center">'.$man_min_married.' - '.$man_max_married.'</td>';

echo '<td align="center">';
@$average=array_sum($age_woman_married) / count($age_woman_married);
echo round($average,1);
echo '</td>';
if ($woman_min_married==0){ $woman_min_married='0'; }
echo '<td align="center">'.$woman_min_married.' - '.$woman_max_married.'</td>';
echo '</tr>';

print '</table>';

include("footer.php");
?>