<?
require('lib.php');
require('forms.php');
require('ppl_search_form.php');
require('add_child_form.php');

$result = mysqli_query_safe(<<<EOT
SELECT dob.date geboortedatum, dod.date sterfdatum
FROM ppl
LEFT JOIN datenodes AS dob ON date_of_birth = dob.datenode_id
LEFT JOIN datenodes AS dod ON date_of_death = dod.datenode_id
WHERE ppl_id = '%q'
EOT
, $_GET['ppl_id']);

if (mysqli_num_rows($result) != 1) {
	header('Content-Type: text/plain');
	echo('ppl_id='.$_GET['ppl_id'].' is ongeldig');
	exit;
}

$result2 = mysqli_query_safe(<<<EOT
SELECT firstname_id, firstname, priority,
	firstnames2ppl.established e_id,
	est.date established,
	firstnames2ppl.disestablished d_id,
	disest.date disestablished
FROM firstnames2ppl
JOIN firstnames USING (firstname_id)
LEFT JOIN datenodes AS est ON firstnames2ppl.established = est.datenode_id
LEFT JOIN datenodes AS disest ON firstnames2ppl.disestablished = disest.datenode_id
WHERE ppl_id = '%q'
ORDER BY est.date, priority DESC
EOT
, $_GET['ppl_id']);

$result3 = mysqli_query_safe(<<<EOT
SELECT surname_id, prefix, surname, priority,
	surnames2ppl.established e_id,
	est.date established,
	surnames2ppl.disestablished d_id,
	disest.date disestablished
FROM surnames2ppl
JOIN surnames USING (surname_id)
LEFT JOIN datenodes AS est ON surnames2ppl.established = est.datenode_id
LEFT JOIN datenodes AS disest ON surnames2ppl.disestablished = disest.datenode_id
WHERE ppl_id = '%q'
ORDER BY est.date, priority DESC
EOT
, $_GET['ppl_id']);

$result4 = mysqli_query_safe(<<<EOT
SELECT address_id, street, number, zipcode, city, priority,
	addresses2ppl.established e_id,
	est.date established,
	addresses2ppl.disestablished d_id,
	disest.date disestablished
FROM addresses2ppl
JOIN addresses USING (address_id)
LEFT JOIN datenodes AS est ON addresses2ppl.established = est.datenode_id
LEFT JOIN datenodes AS disest ON addresses2ppl.disestablished = disest.datenode_id
WHERE ppl_id = '%q'
ORDER BY est.date, priority DESC
EOT
, $_GET['ppl_id']);

$result5 = mysqli_query_safe(<<<EOT
SELECT email_id, email, priority,
	email2ppl.established e_id,
	est.date established,
	email2ppl.disestablished d_id,
	disest.date disestablished
FROM email2ppl
JOIN email USING (email_id)
LEFT JOIN datenodes AS est ON email2ppl.established = est.datenode_id
LEFT JOIN datenodes AS disest ON email2ppl.disestablished = disest.datenode_id
WHERE ppl_id = '%q'
ORDER BY est.date, priority DESC
EOT
, $_GET['ppl_id']);

$result6 = mysqli_query_safe(<<<EOT
SELECT phonenumber_id, phonenumber, priority,
	phonenumbers2ppl.established e_id,
	est.date established,
	phonenumbers2ppl.disestablished d_id,
	disest.date disestablished
FROM phonenumbers2ppl
JOIN phonenumbers USING (phonenumber_id)
LEFT JOIN datenodes AS est ON phonenumbers2ppl.established = est.datenode_id
LEFT JOIN datenodes AS disest ON phonenumbers2ppl.disestablished = disest.datenode_id
WHERE ppl_id = '%q'
ORDER BY est.date, priority DESC
EOT
, $_GET['ppl_id']);

$result7 = mysqli_query_safe(<<<EOT
SELECT child_ppl_id, CONCAT(firstname, IF(prefix = '', '', CONCAT(' ', prefix)), ' ', surname, ' (', dob.date, ')') naam FROM familytree
LEFT JOIN firstnames2ppl AS f2p ON child_ppl_id = f2p.ppl_id
LEFT JOIN firstnames USING (firstname_id)
LEFT JOIN surnames2ppl AS s2p ON child_ppl_id = s2p.ppl_id
LEFT JOIN surnames USING (surname_id)
JOIN ppl ON child_ppl_id = ppl.ppl_id
JOIN datenodes AS dob ON dob.datenode_id = ppl.date_of_birth
WHERE parent_ppl_id = '%q'
GROUP BY child_ppl_id
ORDER BY dob.date
EOT
, $_GET['ppl_id']);

$result8 = mysqli_query_safe(<<<EOT
SELECT parent_ppl_id, CONCAT(firstname, IF(prefix = '', '', CONCAT(' ', prefix)), ' ', surname, ' (', dob.date, ')') naam FROM familytree
LEFT JOIN firstnames2ppl AS f2p ON parent_ppl_id = f2p.ppl_id
LEFT JOIN firstnames USING (firstname_id)
LEFT JOIN surnames2ppl AS s2p ON parent_ppl_id = s2p.ppl_id
LEFT JOIN surnames USING (surname_id)
JOIN ppl ON parent_ppl_id = ppl.ppl_id
JOIN datenodes AS dob ON dob.datenode_id = ppl.date_of_birth
WHERE child_ppl_id = '%q'
GROUP BY parent_ppl_id
ORDER BY dob.date
EOT
, $_GET['ppl_id']);

html_head('Family, Friends & Vague Aquiantances DB', <<<EOT
	$('#ppl_id').autocomplete({ source: "search_ppl.php" });
	$('#child_ppl_id').autocomplete({ source: "search_ppl.php" });
EOT
); ?>
<? 
echo(sprint_form($ppl_search_form));
echo('<p>ppl_id='.entenc($_GET['ppl_id']).', ');
echo('<a href="tree.php?ppl_id='.entenc($_GET['ppl_id']).'">tree</a>, ');
echo('<a href="relations.php?ppl_id='.entenc($_GET['ppl_id']).'">relations</a>');
echo('</p>'."\n");
echo(sprint_table($result));
echo(sprint_table($result2));
echo(sprint_table($result3));
echo(sprint_table($result4));
echo(sprint_table($result5));
echo(sprint_table($result6));
echo(sprint_table($result7));
echo(sprint_form($add_child_form, array( 'parent_ppl_id' => entenc($_GET['ppl_id']))));
echo(sprint_table($result8));

html_tail(); ?>