.
*/
declare(strict_types=1);
namespace Fisharebest\Webtrees\Statistics\Repository;
use Fisharebest\Webtrees\Database;
use Fisharebest\Webtrees\Family;
use Fisharebest\Webtrees\Functions\FunctionsDate;
use Fisharebest\Webtrees\I18N;
use Fisharebest\Webtrees\Individual;
use Fisharebest\Webtrees\Statistics\Google\ChartChildren;
use Fisharebest\Webtrees\Statistics\Google\ChartDivorce;
use Fisharebest\Webtrees\Statistics\Google\ChartFamily;
use Fisharebest\Webtrees\Statistics\Google\ChartFamilyLargest;
use Fisharebest\Webtrees\Statistics\Google\ChartMarriage;
use Fisharebest\Webtrees\Statistics\Google\ChartMarriageAge;
use Fisharebest\Webtrees\Statistics\Google\ChartNoChildrenFamilies;
use Fisharebest\Webtrees\Statistics\Helper\Sql;
use Fisharebest\Webtrees\Tree;
use stdClass;
/**
*
*/
class FamilyRepository
{
/**
* @var Tree
*/
private $tree;
/**
* Constructor.
*
* @param Tree $tree
*/
public function __construct(Tree $tree)
{
$this->tree = $tree;
}
/**
* General query on family.
*
* @param string $type
*
* @return string
*/
private function familyQuery(string $type): string
{
$rows = $this->runSql(
" SELECT f_numchil AS tot, f_id AS id" .
" FROM `##families`" .
" WHERE" .
" f_file={$this->tree->id()}" .
" AND f_numchil = (" .
" SELECT max( f_numchil )" .
" FROM `##families`" .
" WHERE f_file ={$this->tree->id()}" .
" )" .
" LIMIT 1"
);
if (!isset($rows[0])) {
return '';
}
$row = $rows[0];
$family = Family::getInstance($row->id, $this->tree);
if (!$family) {
return '';
}
switch ($type) {
default:
case 'full':
if ($family->canShow()) {
$result = $family->formatList();
} else {
$result = I18N::translate('This information is private and cannot be shown.');
}
break;
case 'size':
$result = I18N::number((int) $row->tot);
break;
case 'name':
$result = '' . $family->getFullName() . '';
break;
}
return $result;
}
/**
* Run an SQL query and cache the result.
*
* @param string $sql
*
* @return stdClass[]
*/
private function runSql($sql): array
{
return Sql::runSql($sql);
}
/**
* Find the family with the most children.
*
* @return string
*/
public function largestFamily(): string
{
return $this->familyQuery('full');
}
/**
* Find the number of children in the largest family.
*
* @return string
*/
public function largestFamilySize(): string
{
return $this->familyQuery('size');
}
/**
* Find the family with the most children.
*
* @return string
*/
public function largestFamilyName(): string
{
return $this->familyQuery('name');
}
/**
* Find the couple with the most grandchildren.
*
* @param int $total
*
* @return array
*/
private function topTenGrandFamilyQuery(int $total): array
{
$rows = $this->runSql(
"SELECT COUNT(*) AS tot, f_id AS id" .
" FROM `##families`" .
" JOIN `##link` AS children ON children.l_file = {$this->tree->id()}" .
" JOIN `##link` AS mchildren ON mchildren.l_file = {$this->tree->id()}" .
" JOIN `##link` AS gchildren ON gchildren.l_file = {$this->tree->id()}" .
" WHERE" .
" f_file={$this->tree->id()} AND" .
" children.l_from=f_id AND" .
" children.l_type='CHIL' AND" .
" children.l_to=mchildren.l_from AND" .
" mchildren.l_type='FAMS' AND" .
" mchildren.l_to=gchildren.l_from AND" .
" gchildren.l_type='CHIL'" .
" GROUP BY id" .
" ORDER BY tot DESC" .
" LIMIT " . $total
);
if (!isset($rows[0])) {
return [];
}
$top10 = [];
foreach ($rows as $row) {
$family = Family::getInstance($row->id, $this->tree);
if ($family && $family->canShow()) {
$total = (int) $row->tot;
$top10[] = [
'family' => $family,
'count' => $total,
];
}
}
// TODO
// if (I18N::direction() === 'rtl') {
// $top10 = str_replace([
// '[',
// ']',
// '(',
// ')',
// '+',
// ], [
// '[',
// ']',
// '(',
// ')',
// '+',
// ], $top10);
// }
return $top10;
}
/**
* Find the couple with the most grandchildren.
*
* @param int $total
*
* @return string
*/
public function topTenLargestGrandFamily(int $total = 10): string
{
$records = $this->topTenGrandFamilyQuery($total);
return view(
'statistics/families/top10-nolist-grand',
[
'records' => $records,
]
);
}
/**
* Find the couple with the most grandchildren.
*
* @param int $total
*
* @return string
*/
public function topTenLargestGrandFamilyList(int $total = 10): string
{
$records = $this->topTenGrandFamilyQuery($total);
return view(
'statistics/families/top10-list-grand',
[
'records' => $records,
]
);
}
/**
* Find the families with no children.
*
* @return int
*/
private function noChildrenFamiliesQuery(): int
{
$rows = $this->runSql(
" SELECT COUNT(*) AS tot" .
" FROM `##families`" .
" WHERE f_numchil = 0 AND f_file = {$this->tree->id()}"
);
return (int) $rows[0]->tot;
}
/**
* Find the families with no children.
*
* @return string
*/
public function noChildrenFamilies(): string
{
return I18N::number($this->noChildrenFamiliesQuery());
}
/**
* Find the families with no children.
*
* @param string $type
*
* @return string
*/
public function noChildrenFamiliesList($type = 'list'): string
{
$rows = $this->runSql(
" SELECT f_id AS family" .
" FROM `##families` AS fam" .
" WHERE f_numchil = 0 AND fam.f_file = {$this->tree->id()}"
);
if (!isset($rows[0])) {
return '';
}
$top10 = [];
foreach ($rows as $row) {
$family = Family::getInstance($row->family, $this->tree);
if ($family->canShow()) {
if ($type === 'list') {
$top10[] = '' . $family->getFullName() . '';
} else {
$top10[] = '' . $family->getFullName() . '';
}
}
}
if ($type === 'list') {
$top10 = implode('', $top10);
} else {
$top10 = implode('; ', $top10);
}
if (I18N::direction() === 'rtl') {
$top10 = str_replace([
'[',
']',
'(',
')',
'+',
], [
'[',
']',
'(',
')',
'+',
], $top10);
}
if ($type === 'list') {
return '';
}
return $top10;
}
/**
* Create a chart of children with no families.
*
* @param string $size
* @param int $year1
* @param int $year2
*
* @return string
*/
public function chartNoChildrenFamilies(string $size = '220x200', int $year1 = -1, int $year2 = -1): string
{
$no_child_fam = $this->noChildrenFamiliesQuery();
return (new ChartNoChildrenFamilies($this->tree))
->chartNoChildrenFamilies($no_child_fam, $size, $year1, $year2);
}
/**
* Returns the ages between siblings.
*
* @param int $total The total number of records to query
*
* @return array
*/
private function ageBetweenSiblingsQuery(int $total): array
{
$rows = $this->runSql(
" SELECT DISTINCT" .
" link1.l_from AS family," .
" link1.l_to AS ch1," .
" link2.l_to AS ch2," .
" child1.d_julianday2-child2.d_julianday2 AS age" .
" FROM `##link` AS link1" .
" LEFT JOIN `##dates` AS child1 ON child1.d_file = {$this->tree->id()}" .
" LEFT JOIN `##dates` AS child2 ON child2.d_file = {$this->tree->id()}" .
" LEFT JOIN `##link` AS link2 ON link2.l_file = {$this->tree->id()}" .
" WHERE" .
" link1.l_file = {$this->tree->id()} AND" .
" link1.l_from = link2.l_from AND" .
" link1.l_type = 'CHIL' AND" .
" child1.d_gid = link1.l_to AND" .
" child1.d_fact = 'BIRT' AND" .
" link2.l_type = 'CHIL' AND" .
" child2.d_gid = link2.l_to AND" .
" child2.d_fact = 'BIRT' AND" .
" child1.d_julianday2 > child2.d_julianday2 AND" .
" child2.d_julianday2 <> 0 AND" .
" child1.d_gid <> child2.d_gid" .
" ORDER BY age DESC" .
" LIMIT " . $total
);
if (!isset($rows[0])) {
return [];
}
return $rows;
}
/**
* Returns the calculated age the time of event.
*
* @param int $age The age from the database record
*
* @return string
*/
private function calculateAge(int $age): string
{
if ((int) ($age / 365.25) > 0) {
$result = (int) ($age / 365.25) . 'y';
} elseif ((int) ($age / 30.4375) > 0) {
$result = (int) ($age / 30.4375) . 'm';
} else {
$result = $age . 'd';
}
return FunctionsDate::getAgeAtEvent($result);
}
/**
* Find the ages between siblings.
*
* @param int $total The total number of records to query
*
* @return array
* @throws \Exception
*/
private function ageBetweenSiblingsNoList(int $total): array
{
$rows = $this->ageBetweenSiblingsQuery($total);
foreach ($rows as $fam) {
$family = Family::getInstance($fam->family, $this->tree);
$child1 = Individual::getInstance($fam->ch1, $this->tree);
$child2 = Individual::getInstance($fam->ch2, $this->tree);
if ($child1->canShow() && $child2->canShow()) {
// ! Single array (no list)
return [
'child1' => $child1,
'child2' => $child2,
'family' => $family,
'age' => $this->calculateAge((int) $fam->age),
];
}
}
return [];
}
/**
* Find the ages between siblings.
*
* @param int $total The total number of records to query
* @param bool $one Include each family only once if true
*
* @return array
* @throws \Exception
*/
private function ageBetweenSiblingsList(int $total, bool $one): array
{
$rows = $this->ageBetweenSiblingsQuery($total);
$top10 = [];
$dist = [];
foreach ($rows as $fam) {
$family = Family::getInstance($fam->family, $this->tree);
$child1 = Individual::getInstance($fam->ch1, $this->tree);
$child2 = Individual::getInstance($fam->ch2, $this->tree);
$age = $this->calculateAge((int) $fam->age);
if ($one && !\in_array($fam->family, $dist, true)) {
if ($child1->canShow() && $child2->canShow()) {
$top10[] = [
'child1' => $child1,
'child2' => $child2,
'family' => $family,
'age' => $age,
];
$dist[] = $fam->family;
}
} elseif (!$one && $child1->canShow() && $child2->canShow()) {
$top10[] = [
'child1' => $child1,
'child2' => $child2,
'family' => $family,
'age' => $age,
];
}
}
// TODO
// if (I18N::direction() === 'rtl') {
// $top10 = str_replace([
// '[',
// ']',
// '(',
// ')',
// '+',
// ], [
// '[',
// ']',
// '(',
// ')',
// '+',
// ], $top10);
// }
return $top10;
}
/**
* Find the ages between siblings.
*
* @param int $total The total number of records to query
*
* @return string
*/
private function ageBetweenSiblingsAge(int $total): string
{
$rows = $this->ageBetweenSiblingsQuery($total);
foreach ($rows as $fam) {
return $this->calculateAge((int) $fam->age);
}
return '';
}
/**
* Find the ages between siblings.
*
* @param int $total The total number of records to query
*
* @return string
* @throws \Exception
*/
private function ageBetweenSiblingsName(int $total): string
{
$rows = $this->ageBetweenSiblingsQuery($total);
foreach ($rows as $fam) {
$family = Family::getInstance($fam->family, $this->tree);
$child1 = Individual::getInstance($fam->ch1, $this->tree);
$child2 = Individual::getInstance($fam->ch2, $this->tree);
if ($child1->canShow() && $child2->canShow()) {
$return = '' . $child2->getFullName() . ' ';
$return .= I18N::translate('and') . ' ';
$return .= '' . $child1->getFullName() . '';
$return .= ' [' . I18N::translate('View this family') . ']';
} else {
$return = I18N::translate('This information is private and cannot be shown.');
}
return $return;
}
return '';
}
/**
* Find the names of siblings with the widest age gap.
*
* @param int $total
*
* @return string
*/
public function topAgeBetweenSiblingsName(int $total = 10): string
{
return $this->ageBetweenSiblingsName($total);
}
/**
* Find the widest age gap between siblings.
*
* @param int $total
*
* @return string
*/
public function topAgeBetweenSiblings(int $total = 10): string
{
return $this->ageBetweenSiblingsAge($total);
}
/**
* Find the name of siblings with the widest age gap.
*
* @param int $total
*
* @return string
*/
public function topAgeBetweenSiblingsFullName(int $total = 10): string
{
$record = $this->ageBetweenSiblingsNoList($total);
return view(
'statistics/families/top10-nolist-age',
[
'record' => $record,
]
);
}
/**
* Find the siblings with the widest age gaps.
*
* @param int $total
* @param string $one
*
* @return string
*/
public function topAgeBetweenSiblingsList(int $total = 10, string $one = ''): string
{
$records = $this->ageBetweenSiblingsList($total, (bool) $one);
return view(
'statistics/families/top10-list-age',
[
'records' => $records,
]
);
}
/**
* General query on familes/children.
*
* @param string $sex
* @param int $year1
* @param int $year2
*
* @return stdClass[]
*/
public function statsChildrenQuery(string $sex = 'BOTH', int $year1 = -1, int $year2 = -1): array
{
if ($sex === 'M') {
$sql =
"SELECT num, COUNT(*) AS total FROM " .
"(SELECT count(i_sex) AS num FROM `##link` " .
"LEFT OUTER JOIN `##individuals` " .
"ON l_from=i_id AND l_file=i_file AND i_sex='M' AND l_type='FAMC' " .
"JOIN `##families` ON f_file=l_file AND f_id=l_to WHERE f_file={$this->tree->id()} GROUP BY l_to" .
") boys" .
" GROUP BY num" .
" ORDER BY num";
} elseif ($sex === 'F') {
$sql =
"SELECT num, COUNT(*) AS total FROM " .
"(SELECT count(i_sex) AS num FROM `##link` " .
"LEFT OUTER JOIN `##individuals` " .
"ON l_from=i_id AND l_file=i_file AND i_sex='F' AND l_type='FAMC' " .
"JOIN `##families` ON f_file=l_file AND f_id=l_to WHERE f_file={$this->tree->id()} GROUP BY l_to" .
") girls" .
" GROUP BY num" .
" ORDER BY num";
} else {
$sql = "SELECT f_numchil, COUNT(*) AS total FROM `##families` ";
if ($year1 >= 0 && $year2 >= 0) {
$sql .=
"AS fam LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}"
. " WHERE"
. " married.d_gid = fam.f_id AND"
. " fam.f_file = {$this->tree->id()} AND"
. " married.d_fact = 'MARR' AND"
. " married.d_year BETWEEN '{$year1}' AND '{$year2}'";
} else {
$sql .= "WHERE f_file={$this->tree->id()}";
}
$sql .= ' GROUP BY f_numchil';
}
return $this->runSql($sql);
}
/**
* Genearl query on families/children.
*
* @param string $size
*
* @return string
*/
public function statsChildren(string $size = '220x200'): string
{
return (new ChartChildren($this->tree))
->chartChildren($size);
}
/**
* Count the total children.
*
* @return string
*/
public function totalChildren(): string
{
$total = (int) Database::prepare(
"SELECT SUM(f_numchil) FROM `##families` WHERE f_file = :tree_id"
)->execute([
'tree_id' => $this->tree->id(),
])->fetchOne();
return I18N::number($total);
}
/**
* Find the average number of children in families.
*
* @return string
*/
public function averageChildren(): string
{
$average = (float) Database::prepare(
"SELECT AVG(f_numchil) AS tot FROM `##families` WHERE f_file = :tree_id"
)->execute([
'tree_id' => $this->tree->id(),
])->fetchOne();
return I18N::number($average, 2);
}
/**
* General query on families.
*
* @param int $total
*
* @return array
*/
private function topTenFamilyQuery(int $total): array
{
$rows = $this->runSql(
"SELECT f_numchil AS tot, f_id AS id" .
" FROM `##families`" .
" WHERE" .
" f_file={$this->tree->id()}" .
" ORDER BY tot DESC" .
" LIMIT " . $total
);
if (empty($rows)) {
return [];
}
$top10 = [];
foreach ($rows as $row) {
$family = Family::getInstance($row->id, $this->tree);
if ($family && $family->canShow()) {
$top10[] = [
'family' => $family,
'count' => (int) $row->tot,
];
}
}
// TODO
// if (I18N::direction() === 'rtl') {
// $top10 = str_replace([
// '[',
// ']',
// '(',
// ')',
// '+',
// ], [
// '[',
// ']',
// '(',
// ')',
// '+',
// ], $top10);
// }
return $top10;
}
/**
* The the families with the most children.
*
* @param int $total
*
* @return string
*/
public function topTenLargestFamily(int $total = 10): string
{
$records = $this->topTenFamilyQuery($total);
return view(
'statistics/families/top10-nolist',
[
'records' => $records,
]
);
}
/**
* Find the families with the most children.
*
* @param int $total
*
* @return string
*/
public function topTenLargestFamilyList(int $total = 10): string
{
$records = $this->topTenFamilyQuery($total);
return view(
'statistics/families/top10-list',
[
'records' => $records,
]
);
}
/**
* Create a chart of the largest families.
*
* @param string|null $size
* @param string|null $color_from
* @param string|null $color_to
* @param int $total
*
* @return string
*/
public function chartLargestFamilies(
string $size = null,
string $color_from = null,
string $color_to = null,
int $total = 10
): string {
return (new ChartFamilyLargest($this->tree))
->chartLargestFamilies($size, $color_from, $color_to, $total);
}
/**
* Find the month in the year of the birth of the first child.
*
* @param bool $sex
*
* @return stdClass[]
*/
public function monthFirstChildQuery(bool $sex = false): array
{
if ($sex) {
$sql_sex1 = ', i_sex';
$sql_sex2 = " JOIN `##individuals` AS child ON child1.d_file = i_file AND child1.d_gid = child.i_id ";
} else {
$sql_sex1 = '';
$sql_sex2 = '';
}
$sql =
"SELECT d_month{$sql_sex1}, COUNT(*) AS total " .
"FROM (" .
" SELECT family{$sql_sex1}, MIN(date) AS d_date, d_month" .
" FROM (" .
" SELECT" .
" link1.l_from AS family," .
" link1.l_to AS child," .
" child1.d_julianday2 AS date," .
" child1.d_month as d_month" .
$sql_sex1 .
" FROM `##link` AS link1" .
" LEFT JOIN `##dates` AS child1 ON child1.d_file = {$this->tree->id()}" .
$sql_sex2 .
" WHERE" .
" link1.l_file = {$this->tree->id()} AND" .
" link1.l_type = 'CHIL' AND" .
" child1.d_gid = link1.l_to AND" .
" child1.d_fact = 'BIRT' AND" .
" child1.d_month IN ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC')" .
" ORDER BY date" .
" ) AS children" .
" GROUP BY family, d_month{$sql_sex1}" .
") AS first_child " .
"GROUP BY d_month";
if ($sex) {
$sql .= ', i_sex';
}
return $this->runSql($sql);
}
/**
* Number of husbands.
*
* @return string
*/
public function totalMarriedMales(): string
{
$n = (int) Database::prepare(
"SELECT COUNT(DISTINCT f_husb) FROM `##families` WHERE f_file = :tree_id AND f_gedcom LIKE '%\\n1 MARR%'"
)->execute([
'tree_id' => $this->tree->id(),
])->fetchOne();
return I18N::number($n);
}
/**
* Number of wives.
*
* @return string
*/
public function totalMarriedFemales(): string
{
$n = (int) Database::prepare(
"SELECT COUNT(DISTINCT f_wife) FROM `##families` WHERE f_file = :tree_id AND f_gedcom LIKE '%\\n1 MARR%'"
)->execute([
'tree_id' => $this->tree->id(),
])->fetchOne();
return I18N::number($n);
}
/**
* General query on parents.
*
* @param string $type
* @param string $age_dir
* @param string $sex
* @param bool $show_years
*
* @return string
*/
private function parentsQuery(string $type, string $age_dir, string $sex, bool $show_years): string
{
if ($sex === 'F') {
$sex_field = 'WIFE';
} else {
$sex_field = 'HUSB';
}
if ($age_dir !== 'ASC') {
$age_dir = 'DESC';
}
$rows = $this->runSql(
" SELECT" .
" parentfamily.l_to AS id," .
" childbirth.d_julianday2-birth.d_julianday1 AS age" .
" FROM `##link` AS parentfamily" .
" JOIN `##link` AS childfamily ON childfamily.l_file = {$this->tree->id()}" .
" JOIN `##dates` AS birth ON birth.d_file = {$this->tree->id()}" .
" JOIN `##dates` AS childbirth ON childbirth.d_file = {$this->tree->id()}" .
" WHERE" .
" birth.d_gid = parentfamily.l_to AND" .
" childfamily.l_to = childbirth.d_gid AND" .
" childfamily.l_type = 'CHIL' AND" .
" parentfamily.l_type = '{$sex_field}' AND" .
" childfamily.l_from = parentfamily.l_from AND" .
" parentfamily.l_file = {$this->tree->id()} AND" .
" birth.d_fact = 'BIRT' AND" .
" childbirth.d_fact = 'BIRT' AND" .
" birth.d_julianday1 <> 0 AND" .
" childbirth.d_julianday2 > birth.d_julianday1" .
" ORDER BY age {$age_dir} LIMIT 1"
);
if (!isset($rows[0])) {
return '';
}
$row = $rows[0];
if (isset($row->id)) {
$person = Individual::getInstance($row->id, $this->tree);
}
switch ($type) {
default:
case 'full':
if ($person && $person->canShow()) {
$result = $person->formatList();
} else {
$result = I18N::translate('This information is private and cannot be shown.');
}
break;
case 'name':
$result = '' . $person->getFullName() . '';
break;
case 'age':
$age = $row->age;
if ($show_years) {
$result = $this->calculateAge((int) $row->age);
} else {
$result = (string) floor($age / 365.25);
}
break;
}
return $result;
}
/**
* Find the youngest mother
*
* @return string
*/
public function youngestMother(): string
{
return $this->parentsQuery('full', 'ASC', 'F', false);
}
/**
* Find the name of the youngest mother.
*
* @return string
*/
public function youngestMotherName(): string
{
return $this->parentsQuery('name', 'ASC', 'F', false);
}
/**
* Find the age of the youngest mother.
*
* @param string $show_years
*
* @return string
*/
public function youngestMotherAge(string $show_years = ''): string
{
return $this->parentsQuery('age', 'ASC', 'F', (bool) $show_years);
}
/**
* Find the oldest mother.
*
* @return string
*/
public function oldestMother(): string
{
return $this->parentsQuery('full', 'DESC', 'F', false);
}
/**
* Find the name of the oldest mother.
*
* @return string
*/
public function oldestMotherName(): string
{
return $this->parentsQuery('name', 'DESC', 'F', false);
}
/**
* Find the age of the oldest mother.
*
* @param string $show_years
*
* @return string
*/
public function oldestMotherAge(string $show_years = ''): string
{
return $this->parentsQuery('age', 'DESC', 'F', (bool) $show_years);
}
/**
* Find the youngest father.
*
* @return string
*/
public function youngestFather(): string
{
return $this->parentsQuery('full', 'ASC', 'M', false);
}
/**
* Find the name of the youngest father.
*
* @return string
*/
public function youngestFatherName(): string
{
return $this->parentsQuery('name', 'ASC', 'M', false);
}
/**
* Find the age of the youngest father.
*
* @param string $show_years
*
* @return string
*/
public function youngestFatherAge(string $show_years = ''): string
{
return $this->parentsQuery('age', 'ASC', 'M', (bool) $show_years);
}
/**
* Find the oldest father.
*
* @return string
*/
public function oldestFather(): string
{
return $this->parentsQuery('full', 'DESC', 'M', false);
}
/**
* Find the name of the oldest father.
*
* @return string
*/
public function oldestFatherName(): string
{
return $this->parentsQuery('name', 'DESC', 'M', false);
}
/**
* Find the age of the oldest father.
*
* @param string $show_years
*
* @return string
*/
public function oldestFatherAge(string $show_years = ''): string
{
return $this->parentsQuery('age', 'DESC', 'M', (bool) $show_years);
}
/**
* General query on age at marriage.
*
* @param string $type
* @param string $age_dir
* @param int $total
*
* @return string
*/
private function ageOfMarriageQuery(string $type, string $age_dir, int $total): string
{
if ($age_dir !== 'ASC') {
$age_dir = 'DESC';
}
$hrows = $this->runSql(
" SELECT DISTINCT fam.f_id AS family, MIN(husbdeath.d_julianday2-married.d_julianday1) AS age" .
" FROM `##families` AS fam" .
" LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" .
" LEFT JOIN `##dates` AS husbdeath ON husbdeath.d_file = {$this->tree->id()}" .
" WHERE" .
" fam.f_file = {$this->tree->id()} AND" .
" husbdeath.d_gid = fam.f_husb AND" .
" husbdeath.d_fact = 'DEAT' AND" .
" married.d_gid = fam.f_id AND" .
" married.d_fact = 'MARR' AND" .
" married.d_julianday1 < husbdeath.d_julianday2 AND" .
" married.d_julianday1 <> 0" .
" GROUP BY family" .
" ORDER BY age {$age_dir}"
);
$wrows = $this->runSql(
" SELECT DISTINCT fam.f_id AS family, MIN(wifedeath.d_julianday2-married.d_julianday1) AS age" .
" FROM `##families` AS fam" .
" LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" .
" LEFT JOIN `##dates` AS wifedeath ON wifedeath.d_file = {$this->tree->id()}" .
" WHERE" .
" fam.f_file = {$this->tree->id()} AND" .
" wifedeath.d_gid = fam.f_wife AND" .
" wifedeath.d_fact = 'DEAT' AND" .
" married.d_gid = fam.f_id AND" .
" married.d_fact = 'MARR' AND" .
" married.d_julianday1 < wifedeath.d_julianday2 AND" .
" married.d_julianday1 <> 0" .
" GROUP BY family" .
" ORDER BY age {$age_dir}"
);
$drows = $this->runSql(
" SELECT DISTINCT fam.f_id AS family, MIN(divorced.d_julianday2-married.d_julianday1) AS age" .
" FROM `##families` AS fam" .
" LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" .
" LEFT JOIN `##dates` AS divorced ON divorced.d_file = {$this->tree->id()}" .
" WHERE" .
" fam.f_file = {$this->tree->id()} AND" .
" married.d_gid = fam.f_id AND" .
" married.d_fact = 'MARR' AND" .
" divorced.d_gid = fam.f_id AND" .
" divorced.d_fact IN ('DIV', 'ANUL', '_SEPR', '_DETS') AND" .
" married.d_julianday1 < divorced.d_julianday2 AND" .
" married.d_julianday1 <> 0" .
" GROUP BY family" .
" ORDER BY age {$age_dir}"
);
$rows = [];
foreach ($drows as $family) {
$rows[$family->family] = $family->age;
}
foreach ($hrows as $family) {
if (!isset($rows[$family->family])) {
$rows[$family->family] = $family->age;
}
}
foreach ($wrows as $family) {
if (!isset($rows[$family->family])) {
$rows[$family->family] = $family->age;
} elseif ($rows[$family->family] > $family->age) {
$rows[$family->family] = $family->age;
}
}
if ($age_dir === 'DESC') {
arsort($rows);
} else {
asort($rows);
}
$top10 = [];
$i = 0;
foreach ($rows as $fam => $age) {
$family = Family::getInstance($fam, $this->tree);
if ($type === 'name') {
return $family->formatList();
}
$age = $this->calculateAge((int) $age);
if ($type === 'age') {
return $age;
}
$husb = $family->getHusband();
$wife = $family->getWife();
if (($husb && ($husb->getAllDeathDates() || !$husb->isDead()))
&& ($wife && ($wife->getAllDeathDates() || !$wife->isDead()))
) {
if ($family && $family->canShow()) {
if ($type === 'list') {
$top10[] = '' . $family->getFullName() . ' (' . $age . ')' . '';
} else {
$top10[] = '' . $family->getFullName() . ' (' . $age . ')';
}
}
if (++$i === $total) {
break;
}
}
}
if ($type === 'list') {
$top10 = implode('', $top10);
} else {
$top10 = implode('; ', $top10);
}
if (I18N::direction() === 'rtl') {
$top10 = str_replace([
'[',
']',
'(',
')',
'+',
], [
'[',
']',
'(',
')',
'+',
], $top10);
}
if ($type === 'list') {
return '';
}
return $top10;
}
/**
* General query on marriage ages.
*
* @return string
*/
public function topAgeOfMarriageFamily(): string
{
return $this->ageOfMarriageQuery('name', 'DESC', 1);
}
/**
* General query on marriage ages.
*
* @return string
*/
public function topAgeOfMarriage(): string
{
return $this->ageOfMarriageQuery('age', 'DESC', 1);
}
/**
* General query on marriage ages.
*
* @param int $total
*
* @return string
*/
public function topAgeOfMarriageFamilies(int $total = 10): string
{
return $this->ageOfMarriageQuery('nolist', 'DESC', $total);
}
/**
* General query on marriage ages.
*
* @param int $total
*
* @return string
*/
public function topAgeOfMarriageFamiliesList(int $total = 10): string
{
return $this->ageOfMarriageQuery('list', 'DESC', $total);
}
/**
* General query on marriage ages.
*
* @return string
*/
public function minAgeOfMarriageFamily(): string
{
return $this->ageOfMarriageQuery('name', 'ASC', 1);
}
/**
* General query on marriage ages.
*
* @return string
*/
public function minAgeOfMarriage(): string
{
return $this->ageOfMarriageQuery('age', 'ASC', 1);
}
/**
* General query on marriage ages.
*
* @param int $total
*
* @return string
*/
public function minAgeOfMarriageFamilies(int $total = 10): string
{
return $this->ageOfMarriageQuery('nolist', 'ASC', $total);
}
/**
* General query on marriage ages.
*
* @param int $total
*
* @return string
*/
public function minAgeOfMarriageFamiliesList(int $total = 10): string
{
return $this->ageOfMarriageQuery('list', 'ASC', $total);
}
/**
* Find the ages between spouses.
*
* @param string $age_dir
* @param int $total
*
* @return array
*/
private function ageBetweenSpousesQuery(string $age_dir, int $total): array
{
if ($age_dir === 'DESC') {
$sql =
"SELECT f_id AS xref, MIN(wife.d_julianday2-husb.d_julianday1) AS age" .
" FROM `##families`" .
" JOIN `##dates` AS wife ON wife.d_gid = f_wife AND wife.d_file = f_file" .
" JOIN `##dates` AS husb ON husb.d_gid = f_husb AND husb.d_file = f_file" .
" WHERE f_file = :tree_id" .
" AND husb.d_fact = 'BIRT'" .
" AND wife.d_fact = 'BIRT'" .
" AND wife.d_julianday2 >= husb.d_julianday1 AND husb.d_julianday1 <> 0" .
" GROUP BY xref" .
" ORDER BY age DESC" .
" LIMIT :limit";
} else {
$sql =
"SELECT f_id AS xref, MIN(husb.d_julianday2-wife.d_julianday1) AS age" .
" FROM `##families`" .
" JOIN `##dates` AS wife ON wife.d_gid = f_wife AND wife.d_file = f_file" .
" JOIN `##dates` AS husb ON husb.d_gid = f_husb AND husb.d_file = f_file" .
" WHERE f_file = :tree_id" .
" AND husb.d_fact = 'BIRT'" .
" AND wife.d_fact = 'BIRT'" .
" AND husb.d_julianday2 >= wife.d_julianday1 AND wife.d_julianday1 <> 0" .
" GROUP BY xref" .
" ORDER BY age DESC" .
" LIMIT :limit";
}
$rows = Database::prepare(
$sql
)->execute([
'tree_id' => $this->tree->id(),
'limit' => $total,
])->fetchAll();
$top10 = [];
foreach ($rows as $fam) {
$family = Family::getInstance($fam->xref, $this->tree);
if ($fam->age < 0) {
break;
}
if ($family->canShow()) {
$top10[] = [
'family' => $family,
'age' => $this->calculateAge((int) $fam->age),
];
}
}
return $top10;
}
/**
* Find the age between husband and wife.
*
* @param int $total
*
* @return string
*/
public function ageBetweenSpousesMF(int $total = 10): string
{
$records = $this->ageBetweenSpousesQuery('DESC', $total);
return view(
'statistics/families/top10-nolist-spouses',
[
'records' => $records,
]
);
}
/**
* Find the age between husband and wife.
*
* @param int $total
*
* @return string
*/
public function ageBetweenSpousesMFList(int $total = 10): string
{
$records = $this->ageBetweenSpousesQuery('DESC', $total);
return view(
'statistics/families/top10-list-spouses',
[
'records' => $records,
]
);
}
/**
* Find the age between wife and husband..
*
* @param int $total
*
* @return string
*/
public function ageBetweenSpousesFM(int $total = 10): string
{
$records = $this->ageBetweenSpousesQuery('ASC', $total);
return view(
'statistics/families/top10-nolist-spouses',
[
'records' => $records,
]
);
}
/**
* Find the age between wife and husband..
*
* @param int $total
*
* @return string
*/
public function ageBetweenSpousesFMList(int $total = 10): string
{
$records = $this->ageBetweenSpousesQuery('ASC', $total);
return view(
'statistics/families/top10-list-spouses',
[
'records' => $records,
]
);
}
/**
* General query on ages at marriage.
*
* @param string $sex
* @param int $year1
* @param int $year2
*
* @return array
*/
public function statsMarrAgeQuery($sex = 'M', $year1 = -1, $year2 = -1): array
{
if ($year1 >= 0 && $year2 >= 0) {
$years = " married.d_year BETWEEN {$year1} AND {$year2} AND ";
} else {
$years = '';
}
$rows = $this->runSql(
"SELECT " .
" fam.f_id, " .
" birth.d_gid, " .
" married.d_julianday2-birth.d_julianday1 AS age " .
"FROM `##dates` AS married " .
"JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) " .
"JOIN `##dates` AS birth ON (birth.d_gid=fam.f_husb AND birth.d_file=fam.f_file) " .
"WHERE " .
" '{$sex}' IN ('M', 'BOTH') AND {$years} " .
" married.d_file={$this->tree->id()} AND married.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND married.d_fact='MARR' AND " .
" birth.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND birth.d_fact='BIRT' AND " .
" married.d_julianday1>birth.d_julianday1 AND birth.d_julianday1<>0 " .
"UNION ALL " .
"SELECT " .
" fam.f_id, " .
" birth.d_gid, " .
" married.d_julianday2-birth.d_julianday1 AS age " .
"FROM `##dates` AS married " .
"JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) " .
"JOIN `##dates` AS birth ON (birth.d_gid=fam.f_wife AND birth.d_file=fam.f_file) " .
"WHERE " .
" '{$sex}' IN ('F', 'BOTH') AND {$years} " .
" married.d_file={$this->tree->id()} AND married.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND married.d_fact='MARR' AND " .
" birth.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND birth.d_fact='BIRT' AND " .
" married.d_julianday1>birth.d_julianday1 AND birth.d_julianday1<>0 "
);
foreach ($rows as $row) {
$row->age = (int) $row->age;
}
return $rows;
}
/**
* General query on marriage ages.
*
* @param string $size
*
* @return string
*/
public function statsMarrAge(string $size = '200x250'): string
{
return (new ChartMarriageAge($this->tree))
->chartMarriageAge($size);
}
/**
* Query the database for marriage tags.
*
* @param string $type
* @param string $age_dir
* @param string $sex
* @param bool $show_years
*
* @return string
*/
private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string
{
if ($sex === 'F') {
$sex_field = 'f_wife';
} else {
$sex_field = 'f_husb';
}
if ($age_dir !== 'ASC') {
$age_dir = 'DESC';
}
$rows = $this->runSql(
" SELECT fam.f_id AS famid, fam.{$sex_field}, married.d_julianday2-birth.d_julianday1 AS age, indi.i_id AS i_id" .
" FROM `##families` AS fam" .
" LEFT JOIN `##dates` AS birth ON birth.d_file = {$this->tree->id()}" .
" LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" .
" LEFT JOIN `##individuals` AS indi ON indi.i_file = {$this->tree->id()}" .
" WHERE" .
" birth.d_gid = indi.i_id AND" .
" married.d_gid = fam.f_id AND" .
" indi.i_id = fam.{$sex_field} AND" .
" fam.f_file = {$this->tree->id()} AND" .
" birth.d_fact = 'BIRT' AND" .
" married.d_fact = 'MARR' AND" .
" birth.d_julianday1 <> 0 AND" .
" married.d_julianday2 > birth.d_julianday1 AND" .
" i_sex='{$sex}'" .
" ORDER BY" .
" married.d_julianday2-birth.d_julianday1 {$age_dir} LIMIT 1"
);
if (!isset($rows[0])) {
return '';
}
$row = $rows[0];
if (isset($row->famid)) {
$family = Family::getInstance($row->famid, $this->tree);
}
if (isset($row->i_id)) {
$person = Individual::getInstance($row->i_id, $this->tree);
}
switch ($type) {
default:
case 'full':
if ($family && $family->canShow()) {
$result = $family->formatList();
} else {
$result = I18N::translate('This information is private and cannot be shown.');
}
break;
case 'name':
$result = '' . $person->getFullName() . '';
break;
case 'age':
$age = $row->age;
if ($show_years) {
$result = $this->calculateAge((int) $row->age);
} else {
$result = I18N::number((int) ($age / 365.25));
}
break;
}
return $result;
}
/**
* Find the youngest wife.
*
* @return string
*/
public function youngestMarriageFemale(): string
{
return $this->marriageQuery('full', 'ASC', 'F', false);
}
/**
* Find the name of the youngest wife.
*
* @return string
*/
public function youngestMarriageFemaleName(): string
{
return $this->marriageQuery('name', 'ASC', 'F', false);
}
/**
* Find the age of the youngest wife.
*
* @param string $show_years
*
* @return string
*/
public function youngestMarriageFemaleAge(string $show_years = ''): string
{
return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years);
}
/**
* Find the oldest wife.
*
* @return string
*/
public function oldestMarriageFemale(): string
{
return $this->marriageQuery('full', 'DESC', 'F', false);
}
/**
* Find the name of the oldest wife.
*
* @return string
*/
public function oldestMarriageFemaleName(): string
{
return $this->marriageQuery('name', 'DESC', 'F', false);
}
/**
* Find the age of the oldest wife.
*
* @param string $show_years
*
* @return string
*/
public function oldestMarriageFemaleAge(string $show_years = ''): string
{
return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years);
}
/**
* Find the youngest husband.
*
* @return string
*/
public function youngestMarriageMale(): string
{
return $this->marriageQuery('full', 'ASC', 'M', false);
}
/**
* Find the name of the youngest husband.
*
* @return string
*/
public function youngestMarriageMaleName(): string
{
return $this->marriageQuery('name', 'ASC', 'M', false);
}
/**
* Find the age of the youngest husband.
*
* @param string $show_years
*
* @return string
*/
public function youngestMarriageMaleAge(string $show_years = ''): string
{
return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years);
}
/**
* Find the oldest husband.
*
* @return string
*/
public function oldestMarriageMale(): string
{
return $this->marriageQuery('full', 'DESC', 'M', false);
}
/**
* Find the name of the oldest husband.
*
* @return string
*/
public function oldestMarriageMaleName(): string
{
return $this->marriageQuery('name', 'DESC', 'M', false);
}
/**
* Find the age of the oldest husband.
*
* @param string $show_years
*
* @return string
*/
public function oldestMarriageMaleAge(string $show_years = ''): string
{
return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years);
}
/**
* General query on marriages.
*
* @param bool $first
* @param int $year1
* @param int $year2
*
* @return array
*/
public function statsMarrQuery(bool $first = false, int $year1 = -1, int $year2 = -1): array
{
if ($first) {
$years = '';
if ($year1 >= 0 && $year2 >= 0) {
$years = " married.d_year BETWEEN '{$year1}' AND '{$year2}' AND";
}
$sql =
" SELECT fam.f_id AS fams, fam.f_husb, fam.f_wife, married.d_julianday2 AS age, married.d_month AS month, indi.i_id AS indi" .
" FROM `##families` AS fam" .
" LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" .
" LEFT JOIN `##individuals` AS indi ON indi.i_file = {$this->tree->id()}" .
" WHERE" .
" married.d_gid = fam.f_id AND" .
" fam.f_file = {$this->tree->id()} AND" .
" married.d_fact = 'MARR' AND" .
" married.d_julianday2 <> 0 AND" .
$years .
" (indi.i_id = fam.f_husb OR indi.i_id = fam.f_wife)" .
" ORDER BY fams, indi, age ASC";
} else {
$sql =
"SELECT d_month, COUNT(*) AS total" .
" FROM `##dates`" .
" WHERE d_file={$this->tree->id()} AND d_fact='MARR'";
if ($year1 >= 0 && $year2 >= 0) {
$sql .= " AND d_year BETWEEN '{$year1}' AND '{$year2}'";
}
$sql .= " GROUP BY d_month";
}
return $this->runSql($sql);
}
/**
* General query on marriages.
*
* @param string|null $size
* @param string|null $color_from
* @param string|null $color_to
*
* @return string
*/
public function statsMarr(string $size = null, string $color_from = null, string $color_to = null): string
{
return (new ChartMarriage($this->tree))
->chartMarriage($size, $color_from, $color_to);
}
/**
* General divorce query.
*
* @param string|null $size
* @param string|null $color_from
* @param string|null $color_to
*
* @return string
*/
public function statsDiv(string $size = null, string $color_from = null, string $color_to = null): string
{
return (new ChartDivorce($this->tree))
->chartDivorce($size, $color_from, $color_to);
}
}