1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2023 webtrees development team 6 * This program is free software: you can redistribute it and/or modify 7 * it under the terms of the GNU General Public License as published by 8 * the Free Software Foundation, either version 3 of the License, or 9 * (at your option) any later version. 10 * This program is distributed in the hope that it will be useful, 11 * but WITHOUT ANY WARRANTY; without even the implied warranty of 12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 * GNU General Public License for more details. 14 * You should have received a copy of the GNU General Public License 15 * along with this program. If not, see <https://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Statistics\Repository; 21 22use Exception; 23use Fisharebest\Webtrees\DB; 24use Fisharebest\Webtrees\Family; 25use Fisharebest\Webtrees\GedcomRecord; 26use Fisharebest\Webtrees\I18N; 27use Fisharebest\Webtrees\Individual; 28use Fisharebest\Webtrees\Registry; 29use Fisharebest\Webtrees\Statistics\Google\ChartChildren; 30use Fisharebest\Webtrees\Statistics\Google\ChartDivorce; 31use Fisharebest\Webtrees\Statistics\Google\ChartFamilyLargest; 32use Fisharebest\Webtrees\Statistics\Google\ChartMarriage; 33use Fisharebest\Webtrees\Statistics\Google\ChartMarriageAge; 34use Fisharebest\Webtrees\Statistics\Google\ChartNoChildrenFamilies; 35use Fisharebest\Webtrees\Statistics\Service\CenturyService; 36use Fisharebest\Webtrees\Statistics\Service\ColorService; 37use Fisharebest\Webtrees\Tree; 38use Illuminate\Database\Query\Builder; 39use Illuminate\Database\Query\Expression; 40use Illuminate\Database\Query\JoinClause; 41use stdClass; 42 43use function arsort; 44use function asort; 45use function e; 46use function floor; 47use function implode; 48use function in_array; 49use function str_replace; 50use function view; 51 52/** 53 * A repository providing methods for family related statistics. 54 */ 55class FamilyRepository 56{ 57 private CenturyService $century_service; 58 59 private ColorService $color_service; 60 61 private Tree $tree; 62 63 /** 64 * @param CenturyService $century_service 65 * @param ColorService $color_service 66 * @param Tree $tree 67 */ 68 public function __construct(CenturyService $century_service, ColorService $color_service, Tree $tree) 69 { 70 $this->century_service = $century_service; 71 $this->color_service = $color_service; 72 $this->tree = $tree; 73 } 74 75 /** 76 * General query on family. 77 * 78 * @param string $type 79 * 80 * @return string 81 */ 82 private function familyQuery(string $type): string 83 { 84 $row = DB::table('families') 85 ->where('f_file', '=', $this->tree->id()) 86 ->orderBy('f_numchil', 'desc') 87 ->first(); 88 89 if ($row === null) { 90 return ''; 91 } 92 93 $family = Registry::familyFactory()->mapper($this->tree)($row); 94 95 if (!$family->canShow()) { 96 return I18N::translate('This information is private and cannot be shown.'); 97 } 98 99 switch ($type) { 100 default: 101 case 'full': 102 return $family->formatList(); 103 104 case 'size': 105 return I18N::number((int) $row->f_numchil); 106 107 case 'name': 108 return '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a>'; 109 } 110 } 111 112 /** 113 * Find the family with the most children. 114 * 115 * @return string 116 */ 117 public function largestFamily(): string 118 { 119 return $this->familyQuery('full'); 120 } 121 122 /** 123 * Find the number of children in the largest family. 124 * 125 * @return string 126 */ 127 public function largestFamilySize(): string 128 { 129 return $this->familyQuery('size'); 130 } 131 132 /** 133 * Find the family with the most children. 134 * 135 * @return string 136 */ 137 public function largestFamilyName(): string 138 { 139 return $this->familyQuery('name'); 140 } 141 142 /** 143 * Find the couple with the most grandchildren. 144 * 145 * @param int $total 146 * 147 * @return array<array<string,int|Family>> 148 */ 149 private function topTenGrandFamilyQuery(int $total): array 150 { 151 return DB::table('families') 152 ->join('link AS children', static function (JoinClause $join): void { 153 $join 154 ->on('children.l_from', '=', 'f_id') 155 ->on('children.l_file', '=', 'f_file') 156 ->where('children.l_type', '=', 'CHIL'); 157 })->join('link AS mchildren', static function (JoinClause $join): void { 158 $join 159 ->on('mchildren.l_file', '=', 'children.l_file') 160 ->on('mchildren.l_from', '=', 'children.l_to') 161 ->where('mchildren.l_type', '=', 'FAMS'); 162 })->join('link AS gchildren', static function (JoinClause $join): void { 163 $join 164 ->on('gchildren.l_file', '=', 'mchildren.l_file') 165 ->on('gchildren.l_from', '=', 'mchildren.l_to') 166 ->where('gchildren.l_type', '=', 'CHIL'); 167 }) 168 ->where('f_file', '=', $this->tree->id()) 169 ->groupBy(['f_id', 'f_file']) 170 ->orderBy(new Expression('COUNT(*)'), 'DESC') 171 ->select(['families.*']) 172 ->limit($total) 173 ->get() 174 ->map(Registry::familyFactory()->mapper($this->tree)) 175 ->filter(GedcomRecord::accessFilter()) 176 ->map(static function (Family $family): array { 177 $count = 0; 178 foreach ($family->children() as $child) { 179 foreach ($child->spouseFamilies() as $spouse_family) { 180 $count += $spouse_family->children()->count(); 181 } 182 } 183 184 return [ 185 'family' => $family, 186 'count' => $count, 187 ]; 188 }) 189 ->all(); 190 } 191 192 /** 193 * Find the couple with the most grandchildren. 194 * 195 * @param int $total 196 * 197 * @return string 198 */ 199 public function topTenLargestGrandFamily(int $total = 10): string 200 { 201 return view('statistics/families/top10-nolist-grand', [ 202 'records' => $this->topTenGrandFamilyQuery($total), 203 ]); 204 } 205 206 /** 207 * Find the couple with the most grandchildren. 208 * 209 * @param int $total 210 * 211 * @return string 212 */ 213 public function topTenLargestGrandFamilyList(int $total = 10): string 214 { 215 return view('statistics/families/top10-list-grand', [ 216 'records' => $this->topTenGrandFamilyQuery($total), 217 ]); 218 } 219 220 /** 221 * Find the families with no children. 222 * 223 * @return int 224 */ 225 private function noChildrenFamiliesQuery(): int 226 { 227 return DB::table('families') 228 ->where('f_file', '=', $this->tree->id()) 229 ->where('f_numchil', '=', 0) 230 ->count(); 231 } 232 233 /** 234 * Find the families with no children. 235 * 236 * @return string 237 */ 238 public function noChildrenFamilies(): string 239 { 240 return I18N::number($this->noChildrenFamiliesQuery()); 241 } 242 243 /** 244 * Find the families with no children. 245 * 246 * @param string $type 247 * 248 * @return string 249 */ 250 public function noChildrenFamiliesList(string $type = 'list'): string 251 { 252 $families = DB::table('families') 253 ->where('f_file', '=', $this->tree->id()) 254 ->where('f_numchil', '=', 0) 255 ->get() 256 ->map(Registry::familyFactory()->mapper($this->tree)) 257 ->filter(GedcomRecord::accessFilter()); 258 259 $top10 = []; 260 261 foreach ($families as $family) { 262 if ($type === 'list') { 263 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a></li>'; 264 } else { 265 $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a>'; 266 } 267 } 268 269 if ($type === 'list') { 270 $top10 = implode('', $top10); 271 } else { 272 $top10 = implode('; ', $top10); 273 } 274 275 276 if ($type === 'list') { 277 return '<ul>' . $top10 . '</ul>'; 278 } 279 280 return $top10; 281 } 282 283 /** 284 * Create a chart of children with no families. 285 * 286 * @param int $year1 287 * @param int $year2 288 * 289 * @return string 290 */ 291 public function chartNoChildrenFamilies(int $year1 = -1, int $year2 = -1): string 292 { 293 $no_child_fam = $this->noChildrenFamiliesQuery(); 294 295 return (new ChartNoChildrenFamilies($this->century_service, $this->tree)) 296 ->chartNoChildrenFamilies($no_child_fam, $year1, $year2); 297 } 298 299 /** 300 * Returns the ages between siblings. 301 * 302 * @param int $total The total number of records to query 303 * 304 * @return array<object> 305 */ 306 private function ageBetweenSiblingsQuery(int $total): array 307 { 308 $prefix = DB::connection()->getTablePrefix(); 309 310 return DB::table('link AS link1') 311 ->join('link AS link2', static function (JoinClause $join): void { 312 $join 313 ->on('link2.l_from', '=', 'link1.l_from') 314 ->on('link2.l_type', '=', 'link1.l_type') 315 ->on('link2.l_file', '=', 'link1.l_file'); 316 }) 317 ->join('dates AS child1', static function (JoinClause $join): void { 318 $join 319 ->on('child1.d_gid', '=', 'link1.l_to') 320 ->on('child1.d_file', '=', 'link1.l_file') 321 ->where('child1.d_fact', '=', 'BIRT') 322 ->where('child1.d_julianday1', '<>', 0); 323 }) 324 ->join('dates AS child2', static function (JoinClause $join): void { 325 $join 326 ->on('child2.d_gid', '=', 'link2.l_to') 327 ->on('child2.d_file', '=', 'link2.l_file') 328 ->where('child2.d_fact', '=', 'BIRT') 329 ->whereColumn('child2.d_julianday2', '>', 'child1.d_julianday1'); 330 }) 331 ->where('link1.l_type', '=', 'CHIL') 332 ->where('link1.l_file', '=', $this->tree->id()) 333 ->distinct() 334 ->select(['link1.l_from AS family', 'link1.l_to AS ch1', 'link2.l_to AS ch2', new Expression($prefix . 'child2.d_julianday2 - ' . $prefix . 'child1.d_julianday1 AS age')]) 335 ->orderBy('age', 'DESC') 336 ->take($total) 337 ->get() 338 ->all(); 339 } 340 341 /** 342 * Returns the calculated age the time of event. 343 * 344 * @param int $age The age from the database record 345 * 346 * @return string 347 */ 348 private function calculateAge(int $age): string 349 { 350 if ($age < 31) { 351 return I18N::plural('%s day', '%s days', $age, I18N::number($age)); 352 } 353 354 if ($age < 365) { 355 $months = (int) ($age / 30.5); 356 357 return I18N::plural('%s month', '%s months', $months, I18N::number($months)); 358 } 359 360 $years = (int) ($age / 365.25); 361 362 return I18N::plural('%s year', '%s years', $years, I18N::number($years)); 363 } 364 365 /** 366 * Find the ages between siblings. 367 * 368 * @param int $total The total number of records to query 369 * 370 * @return array<string,Individual|Family|string> 371 * @throws Exception 372 */ 373 private function ageBetweenSiblingsNoList(int $total): array 374 { 375 $rows = $this->ageBetweenSiblingsQuery($total); 376 377 foreach ($rows as $fam) { 378 $family = Registry::familyFactory()->make($fam->family, $this->tree); 379 $child1 = Registry::individualFactory()->make($fam->ch1, $this->tree); 380 $child2 = Registry::individualFactory()->make($fam->ch2, $this->tree); 381 382 if ($family !== null && $child1 !== null && $child2 !== null && $child1->canShow() && $child2->canShow()) { 383 // ! Single array (no list) 384 return [ 385 'child1' => $child1, 386 'child2' => $child2, 387 'family' => $family, 388 'age' => $this->calculateAge((int) $fam->age), 389 ]; 390 } 391 } 392 393 return []; 394 } 395 396 /** 397 * Find the ages between siblings. 398 * 399 * @param int $total The total number of records to query 400 * @param bool $one Include each family only once if true 401 * 402 * @return array<int,array<string,Individual|Family|string>> 403 * @throws Exception 404 */ 405 private function ageBetweenSiblingsList(int $total, bool $one): array 406 { 407 $rows = $this->ageBetweenSiblingsQuery($total); 408 $top10 = []; 409 $dist = []; 410 411 foreach ($rows as $fam) { 412 $family = Registry::familyFactory()->make($fam->family, $this->tree); 413 $child1 = Registry::individualFactory()->make($fam->ch1, $this->tree); 414 $child2 = Registry::individualFactory()->make($fam->ch2, $this->tree); 415 416 $age = $this->calculateAge((int) $fam->age); 417 418 if ($one && !in_array($fam->family, $dist, true)) { 419 if ($family !== null && $child1 !== null && $child2 !== null && $child1->canShow() && $child2->canShow()) { 420 $top10[] = [ 421 'child1' => $child1, 422 'child2' => $child2, 423 'family' => $family, 424 'age' => $age, 425 ]; 426 427 $dist[] = $fam->family; 428 } 429 } elseif (!$one && $family !== null && $child1 !== null && $child2 !== null && $child1->canShow() && $child2->canShow()) { 430 $top10[] = [ 431 'child1' => $child1, 432 'child2' => $child2, 433 'family' => $family, 434 'age' => $age, 435 ]; 436 } 437 } 438 439 return $top10; 440 } 441 442 /** 443 * Find the ages between siblings. 444 * 445 * @param int $total The total number of records to query 446 * 447 * @return string 448 */ 449 private function ageBetweenSiblingsAge(int $total): string 450 { 451 $rows = $this->ageBetweenSiblingsQuery($total); 452 453 foreach ($rows as $fam) { 454 return $this->calculateAge((int) $fam->age); 455 } 456 457 return ''; 458 } 459 460 /** 461 * Find the ages between siblings. 462 * 463 * @param int $total The total number of records to query 464 * 465 * @return string 466 * @throws Exception 467 */ 468 private function ageBetweenSiblingsName(int $total): string 469 { 470 $rows = $this->ageBetweenSiblingsQuery($total); 471 472 foreach ($rows as $fam) { 473 $family = Registry::familyFactory()->make($fam->family, $this->tree); 474 $child1 = Registry::individualFactory()->make($fam->ch1, $this->tree); 475 $child2 = Registry::individualFactory()->make($fam->ch2, $this->tree); 476 477 if ($family !== null && $child1 !== null && $child2 !== null && $child1->canShow() && $child2->canShow()) { 478 $return = '<a href="' . e($child2->url()) . '">' . $child2->fullName() . '</a> '; 479 $return .= I18N::translate('and') . ' '; 480 $return .= '<a href="' . e($child1->url()) . '">' . $child1->fullName() . '</a>'; 481 $return .= ' <a href="' . e($family->url()) . '">[' . I18N::translate('View this family') . ']</a>'; 482 } else { 483 $return = I18N::translate('This information is private and cannot be shown.'); 484 } 485 486 return $return; 487 } 488 489 return ''; 490 } 491 492 /** 493 * Find the names of siblings with the widest age gap. 494 * 495 * @param int $total 496 * 497 * @return string 498 */ 499 public function topAgeBetweenSiblingsName(int $total = 10): string 500 { 501 return $this->ageBetweenSiblingsName($total); 502 } 503 504 /** 505 * Find the widest age gap between siblings. 506 * 507 * @param int $total 508 * 509 * @return string 510 */ 511 public function topAgeBetweenSiblings(int $total = 10): string 512 { 513 return $this->ageBetweenSiblingsAge($total); 514 } 515 516 /** 517 * Find the name of siblings with the widest age gap. 518 * 519 * @param int $total 520 * 521 * @return string 522 */ 523 public function topAgeBetweenSiblingsFullName(int $total = 10): string 524 { 525 $record = $this->ageBetweenSiblingsNoList($total); 526 527 if ($record === []) { 528 return I18N::translate('This information is not available.'); 529 } 530 531 return view('statistics/families/top10-nolist-age', [ 532 'record' => $record, 533 ]); 534 } 535 536 /** 537 * Find the siblings with the widest age gaps. 538 * 539 * @param int $total 540 * @param string $one 541 * 542 * @return string 543 */ 544 public function topAgeBetweenSiblingsList(int $total = 10, string $one = ''): string 545 { 546 $records = $this->ageBetweenSiblingsList($total, (bool) $one); 547 548 return view('statistics/families/top10-list-age', [ 549 'records' => $records, 550 ]); 551 } 552 553 /** 554 * General query on families/children. 555 * 556 * @param int $year1 557 * @param int $year2 558 * 559 * @return array<stdClass> 560 */ 561 public function statsChildrenQuery(int $year1 = -1, int $year2 = -1): array 562 { 563 $query = DB::table('families') 564 ->where('f_file', '=', $this->tree->id()) 565 ->groupBy(['f_numchil']) 566 ->select(['f_numchil', new Expression('COUNT(*) AS total')]); 567 568 if ($year1 >= 0 && $year2 >= 0) { 569 $query 570 ->join('dates', static function (JoinClause $join): void { 571 $join 572 ->on('d_file', '=', 'f_file') 573 ->on('d_gid', '=', 'f_id'); 574 }) 575 ->where('d_fact', '=', 'MARR') 576 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 577 ->whereBetween('d_year', [$year1, $year2]); 578 } 579 580 return $query->get()->all(); 581 } 582 583 /** 584 * Genearl query on families/children. 585 * 586 * @return string 587 */ 588 public function statsChildren(): string 589 { 590 return (new ChartChildren($this->century_service, $this->tree)) 591 ->chartChildren(); 592 } 593 594 /** 595 * Count the total children. 596 * 597 * @return string 598 */ 599 public function totalChildren(): string 600 { 601 $total = (int) DB::table('families') 602 ->where('f_file', '=', $this->tree->id()) 603 ->sum('f_numchil'); 604 605 return I18N::number($total); 606 } 607 608 /** 609 * Find the average number of children in families. 610 * 611 * @return string 612 */ 613 public function averageChildren(): string 614 { 615 $average = (float) DB::table('families') 616 ->where('f_file', '=', $this->tree->id()) 617 ->avg('f_numchil'); 618 619 return I18N::number($average, 2); 620 } 621 622 /** 623 * General query on families. 624 * 625 * @param int $total 626 * 627 * @return array<array<string,mixed>> 628 */ 629 private function topTenFamilyQuery(int $total): array 630 { 631 return DB::table('families') 632 ->where('f_file', '=', $this->tree->id()) 633 ->orderBy('f_numchil', 'DESC') 634 ->limit($total) 635 ->get() 636 ->map(Registry::familyFactory()->mapper($this->tree)) 637 ->filter(GedcomRecord::accessFilter()) 638 ->map(static function (Family $family): array { 639 return [ 640 'family' => $family, 641 'count' => $family->numberOfChildren(), 642 ]; 643 }) 644 ->all(); 645 } 646 647 /** 648 * The the families with the most children. 649 * 650 * @param int $total 651 * 652 * @return string 653 */ 654 public function topTenLargestFamily(int $total = 10): string 655 { 656 $records = $this->topTenFamilyQuery($total); 657 658 return view('statistics/families/top10-nolist', [ 659 'records' => $records, 660 ]); 661 } 662 663 /** 664 * Find the families with the most children. 665 * 666 * @param int $total 667 * 668 * @return string 669 */ 670 public function topTenLargestFamilyList(int $total = 10): string 671 { 672 $records = $this->topTenFamilyQuery($total); 673 674 return view('statistics/families/top10-list', [ 675 'records' => $records, 676 ]); 677 } 678 679 /** 680 * Create a chart of the largest families. 681 * 682 * @param string|null $color_from 683 * @param string|null $color_to 684 * @param int $total 685 * 686 * @return string 687 */ 688 public function chartLargestFamilies( 689 string $color_from = null, 690 string $color_to = null, 691 int $total = 10 692 ): string { 693 return (new ChartFamilyLargest($this->color_service, $this->tree)) 694 ->chartLargestFamilies($color_from, $color_to, $total); 695 } 696 697 /** 698 * Find the month in the year of the birth of the first child. 699 * 700 * @param int $year1 701 * @param int $year2 702 * 703 * @return Builder 704 */ 705 public function monthFirstChildQuery(int $year1 = -1, int $year2 = -1): Builder 706 { 707 $first_child_subquery = DB::table('link') 708 ->join('dates', static function (JoinClause $join): void { 709 $join 710 ->on('d_gid', '=', 'l_to') 711 ->on('d_file', '=', 'l_file') 712 ->where('d_julianday1', '<>', 0) 713 ->whereIn('d_month', ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']); 714 }) 715 ->where('l_file', '=', $this->tree->id()) 716 ->where('l_type', '=', 'CHIL') 717 ->select(['l_from AS family_id', new Expression('MIN(d_julianday1) AS min_birth_jd')]) 718 ->groupBy(['family_id']); 719 720 $query = DB::table('link') 721 ->join('dates', static function (JoinClause $join): void { 722 $join 723 ->on('d_gid', '=', 'l_to') 724 ->on('d_file', '=', 'l_file'); 725 }) 726 ->joinSub($first_child_subquery, 'subquery', static function (JoinClause $join): void { 727 $join 728 ->on('family_id', '=', 'l_from') 729 ->on('min_birth_jd', '=', 'd_julianday1'); 730 }) 731 ->where('link.l_file', '=', $this->tree->id()) 732 ->where('link.l_type', '=', 'CHIL') 733 ->select(['d_month', new Expression('COUNT(*) AS total')]) 734 ->groupBy(['d_month']); 735 736 if ($year1 >= 0 && $year2 >= 0) { 737 $query->whereBetween('d_year', [$year1, $year2]); 738 } 739 740 return $query; 741 } 742 743 /** 744 * Find the month in the year of the birth of the first child. 745 * 746 * @param int $year1 747 * @param int $year2 748 * 749 * @return Builder 750 */ 751 public function monthFirstChildBySexQuery(int $year1 = -1, int $year2 = -1): Builder 752 { 753 return $this->monthFirstChildQuery($year1, $year2) 754 ->join('individuals', static function (JoinClause $join): void { 755 $join 756 ->on('i_file', '=', 'l_file') 757 ->on('i_id', '=', 'l_to'); 758 }) 759 ->select(['d_month', 'i_sex', new Expression('COUNT(*) AS total')]) 760 ->groupBy(['d_month', 'i_sex']); 761 } 762 763 /** 764 * Number of husbands. 765 * 766 * @return string 767 */ 768 public function totalMarriedMales(): string 769 { 770 $n = DB::table('families') 771 ->where('f_file', '=', $this->tree->id()) 772 ->where('f_gedcom', 'LIKE', "%\n1 MARR%") 773 ->distinct() 774 ->count('f_husb'); 775 776 return I18N::number($n); 777 } 778 779 /** 780 * Number of wives. 781 * 782 * @return string 783 */ 784 public function totalMarriedFemales(): string 785 { 786 $n = DB::table('families') 787 ->where('f_file', '=', $this->tree->id()) 788 ->where('f_gedcom', 'LIKE', "%\n1 MARR%") 789 ->distinct() 790 ->count('f_wife'); 791 792 return I18N::number($n); 793 } 794 795 /** 796 * General query on parents. 797 * 798 * @param string $type 799 * @param string $age_dir 800 * @param string $sex 801 * @param bool $show_years 802 * 803 * @return string 804 */ 805 private function parentsQuery(string $type, string $age_dir, string $sex, bool $show_years): string 806 { 807 if ($sex === 'F') { 808 $sex_field = 'WIFE'; 809 } else { 810 $sex_field = 'HUSB'; 811 } 812 813 if ($age_dir !== 'ASC') { 814 $age_dir = 'DESC'; 815 } 816 817 $prefix = DB::connection()->getTablePrefix(); 818 819 $row = DB::table('link AS parentfamily') 820 ->join('link AS childfamily', static function (JoinClause $join): void { 821 $join 822 ->on('childfamily.l_file', '=', 'parentfamily.l_file') 823 ->on('childfamily.l_from', '=', 'parentfamily.l_from') 824 ->where('childfamily.l_type', '=', 'CHIL'); 825 }) 826 ->join('dates AS birth', static function (JoinClause $join): void { 827 $join 828 ->on('birth.d_file', '=', 'parentfamily.l_file') 829 ->on('birth.d_gid', '=', 'parentfamily.l_to') 830 ->where('birth.d_fact', '=', 'BIRT') 831 ->where('birth.d_julianday1', '<>', 0); 832 }) 833 ->join('dates AS childbirth', static function (JoinClause $join): void { 834 $join 835 ->on('childbirth.d_file', '=', 'parentfamily.l_file') 836 ->on('childbirth.d_gid', '=', 'childfamily.l_to') 837 ->where('childbirth.d_fact', '=', 'BIRT'); 838 }) 839 ->where('childfamily.l_file', '=', $this->tree->id()) 840 ->where('parentfamily.l_type', '=', $sex_field) 841 ->where('childbirth.d_julianday2', '>', new Expression($prefix . 'birth.d_julianday1')) 842 ->select(['parentfamily.l_to AS id', new Expression($prefix . 'childbirth.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')]) 843 ->take(1) 844 ->orderBy('age', $age_dir) 845 ->get() 846 ->first(); 847 848 if ($row === null) { 849 return I18N::translate('This information is not available.'); 850 } 851 852 $person = Registry::individualFactory()->make($row->id, $this->tree); 853 854 switch ($type) { 855 default: 856 case 'full': 857 if ($person !== null && $person->canShow()) { 858 $result = $person->formatList(); 859 } else { 860 $result = I18N::translate('This information is private and cannot be shown.'); 861 } 862 break; 863 864 case 'name': 865 $result = '<a href="' . e($person->url()) . '">' . $person->fullName() . '</a>'; 866 break; 867 868 case 'age': 869 $age = $row->age; 870 871 if ($show_years) { 872 $result = $this->calculateAge((int) $row->age); 873 } else { 874 $result = (string) floor($age / 365.25); 875 } 876 877 break; 878 } 879 880 return $result; 881 } 882 883 /** 884 * Find the youngest mother 885 * 886 * @return string 887 */ 888 public function youngestMother(): string 889 { 890 return $this->parentsQuery('full', 'ASC', 'F', false); 891 } 892 893 /** 894 * Find the name of the youngest mother. 895 * 896 * @return string 897 */ 898 public function youngestMotherName(): string 899 { 900 return $this->parentsQuery('name', 'ASC', 'F', false); 901 } 902 903 /** 904 * Find the age of the youngest mother. 905 * 906 * @param string $show_years 907 * 908 * @return string 909 */ 910 public function youngestMotherAge(string $show_years = ''): string 911 { 912 return $this->parentsQuery('age', 'ASC', 'F', (bool) $show_years); 913 } 914 915 /** 916 * Find the oldest mother. 917 * 918 * @return string 919 */ 920 public function oldestMother(): string 921 { 922 return $this->parentsQuery('full', 'DESC', 'F', false); 923 } 924 925 /** 926 * Find the name of the oldest mother. 927 * 928 * @return string 929 */ 930 public function oldestMotherName(): string 931 { 932 return $this->parentsQuery('name', 'DESC', 'F', false); 933 } 934 935 /** 936 * Find the age of the oldest mother. 937 * 938 * @param string $show_years 939 * 940 * @return string 941 */ 942 public function oldestMotherAge(string $show_years = ''): string 943 { 944 return $this->parentsQuery('age', 'DESC', 'F', (bool) $show_years); 945 } 946 947 /** 948 * Find the youngest father. 949 * 950 * @return string 951 */ 952 public function youngestFather(): string 953 { 954 return $this->parentsQuery('full', 'ASC', 'M', false); 955 } 956 957 /** 958 * Find the name of the youngest father. 959 * 960 * @return string 961 */ 962 public function youngestFatherName(): string 963 { 964 return $this->parentsQuery('name', 'ASC', 'M', false); 965 } 966 967 /** 968 * Find the age of the youngest father. 969 * 970 * @param string $show_years 971 * 972 * @return string 973 */ 974 public function youngestFatherAge(string $show_years = ''): string 975 { 976 return $this->parentsQuery('age', 'ASC', 'M', (bool) $show_years); 977 } 978 979 /** 980 * Find the oldest father. 981 * 982 * @return string 983 */ 984 public function oldestFather(): string 985 { 986 return $this->parentsQuery('full', 'DESC', 'M', false); 987 } 988 989 /** 990 * Find the name of the oldest father. 991 * 992 * @return string 993 */ 994 public function oldestFatherName(): string 995 { 996 return $this->parentsQuery('name', 'DESC', 'M', false); 997 } 998 999 /** 1000 * Find the age of the oldest father. 1001 * 1002 * @param string $show_years 1003 * 1004 * @return string 1005 */ 1006 public function oldestFatherAge(string $show_years = ''): string 1007 { 1008 return $this->parentsQuery('age', 'DESC', 'M', (bool) $show_years); 1009 } 1010 1011 /** 1012 * General query on age at marriage. 1013 * 1014 * @param string $type 1015 * @param string $age_dir "ASC" or "DESC" 1016 * @param int $total 1017 * 1018 * @return string 1019 */ 1020 private function ageOfMarriageQuery(string $type, string $age_dir, int $total): string 1021 { 1022 $prefix = DB::connection()->getTablePrefix(); 1023 1024 $hrows = DB::table('families') 1025 ->where('f_file', '=', $this->tree->id()) 1026 ->join('dates AS married', static function (JoinClause $join): void { 1027 $join 1028 ->on('married.d_file', '=', 'f_file') 1029 ->on('married.d_gid', '=', 'f_id') 1030 ->where('married.d_fact', '=', 'MARR') 1031 ->where('married.d_julianday1', '<>', 0); 1032 }) 1033 ->join('dates AS husbdeath', static function (JoinClause $join): void { 1034 $join 1035 ->on('husbdeath.d_gid', '=', 'f_husb') 1036 ->on('husbdeath.d_file', '=', 'f_file') 1037 ->where('husbdeath.d_fact', '=', 'DEAT'); 1038 }) 1039 ->whereColumn('married.d_julianday1', '<', 'husbdeath.d_julianday2') 1040 ->groupBy(['f_id']) 1041 ->select(['f_id AS family', new Expression('MIN(' . $prefix . 'husbdeath.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')]) 1042 ->get() 1043 ->all(); 1044 1045 $wrows = DB::table('families') 1046 ->where('f_file', '=', $this->tree->id()) 1047 ->join('dates AS married', static function (JoinClause $join): void { 1048 $join 1049 ->on('married.d_file', '=', 'f_file') 1050 ->on('married.d_gid', '=', 'f_id') 1051 ->where('married.d_fact', '=', 'MARR') 1052 ->where('married.d_julianday1', '<>', 0); 1053 }) 1054 ->join('dates AS wifedeath', static function (JoinClause $join): void { 1055 $join 1056 ->on('wifedeath.d_gid', '=', 'f_wife') 1057 ->on('wifedeath.d_file', '=', 'f_file') 1058 ->where('wifedeath.d_fact', '=', 'DEAT'); 1059 }) 1060 ->whereColumn('married.d_julianday1', '<', 'wifedeath.d_julianday2') 1061 ->groupBy(['f_id']) 1062 ->select(['f_id AS family', new Expression('MIN(' . $prefix . 'wifedeath.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')]) 1063 ->get() 1064 ->all(); 1065 1066 $drows = DB::table('families') 1067 ->where('f_file', '=', $this->tree->id()) 1068 ->join('dates AS married', static function (JoinClause $join): void { 1069 $join 1070 ->on('married.d_file', '=', 'f_file') 1071 ->on('married.d_gid', '=', 'f_id') 1072 ->where('married.d_fact', '=', 'MARR') 1073 ->where('married.d_julianday1', '<>', 0); 1074 }) 1075 ->join('dates AS divorced', static function (JoinClause $join): void { 1076 $join 1077 ->on('divorced.d_gid', '=', 'f_id') 1078 ->on('divorced.d_file', '=', 'f_file') 1079 ->whereIn('divorced.d_fact', ['DIV', 'ANUL', '_SEPR']); 1080 }) 1081 ->whereColumn('married.d_julianday1', '<', 'divorced.d_julianday2') 1082 ->groupBy(['f_id']) 1083 ->select(['f_id AS family', new Expression('MIN(' . $prefix . 'divorced.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')]) 1084 ->get() 1085 ->all(); 1086 1087 $rows = []; 1088 foreach ($drows as $family) { 1089 $rows[$family->family] = $family->age; 1090 } 1091 1092 foreach ($hrows as $family) { 1093 if (!isset($rows[$family->family])) { 1094 $rows[$family->family] = $family->age; 1095 } 1096 } 1097 1098 foreach ($wrows as $family) { 1099 if (!isset($rows[$family->family])) { 1100 $rows[$family->family] = $family->age; 1101 } elseif ($rows[$family->family] > $family->age) { 1102 $rows[$family->family] = $family->age; 1103 } 1104 } 1105 1106 if ($age_dir === 'DESC') { 1107 arsort($rows); 1108 } else { 1109 asort($rows); 1110 } 1111 1112 $top10 = []; 1113 $i = 0; 1114 foreach ($rows as $xref => $age) { 1115 $family = Registry::familyFactory()->make((string) $xref, $this->tree); 1116 if ($type === 'name') { 1117 return $family->formatList(); 1118 } 1119 1120 $age = $this->calculateAge((int) $age); 1121 1122 if ($type === 'age') { 1123 return $age; 1124 } 1125 1126 $husb = $family->husband(); 1127 $wife = $family->wife(); 1128 1129 if ( 1130 $husb instanceof Individual && 1131 $wife instanceof Individual && 1132 ($husb->getAllDeathDates() || !$husb->isDead()) && 1133 ($wife->getAllDeathDates() || !$wife->isDead()) 1134 ) { 1135 if ($family->canShow()) { 1136 if ($type === 'list') { 1137 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')' . '</li>'; 1138 } else { 1139 $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')'; 1140 } 1141 } 1142 if (++$i === $total) { 1143 break; 1144 } 1145 } 1146 } 1147 1148 if ($type === 'list') { 1149 $top10 = implode('', $top10); 1150 } else { 1151 $top10 = implode('; ', $top10); 1152 } 1153 1154 if (I18N::direction() === 'rtl') { 1155 $top10 = str_replace([ 1156 '[', 1157 ']', 1158 '(', 1159 ')', 1160 '+', 1161 ], [ 1162 '‏[', 1163 '‏]', 1164 '‏(', 1165 '‏)', 1166 '‏+', 1167 ], $top10); 1168 } 1169 1170 if ($type === 'list') { 1171 return '<ul>' . $top10 . '</ul>'; 1172 } 1173 1174 return $top10; 1175 } 1176 1177 /** 1178 * General query on marriage ages. 1179 * 1180 * @return string 1181 */ 1182 public function topAgeOfMarriageFamily(): string 1183 { 1184 return $this->ageOfMarriageQuery('name', 'DESC', 1); 1185 } 1186 1187 /** 1188 * General query on marriage ages. 1189 * 1190 * @return string 1191 */ 1192 public function topAgeOfMarriage(): string 1193 { 1194 return $this->ageOfMarriageQuery('age', 'DESC', 1); 1195 } 1196 1197 /** 1198 * General query on marriage ages. 1199 * 1200 * @param int $total 1201 * 1202 * @return string 1203 */ 1204 public function topAgeOfMarriageFamilies(int $total = 10): string 1205 { 1206 return $this->ageOfMarriageQuery('nolist', 'DESC', $total); 1207 } 1208 1209 /** 1210 * General query on marriage ages. 1211 * 1212 * @param int $total 1213 * 1214 * @return string 1215 */ 1216 public function topAgeOfMarriageFamiliesList(int $total = 10): string 1217 { 1218 return $this->ageOfMarriageQuery('list', 'DESC', $total); 1219 } 1220 1221 /** 1222 * General query on marriage ages. 1223 * 1224 * @return string 1225 */ 1226 public function minAgeOfMarriageFamily(): string 1227 { 1228 return $this->ageOfMarriageQuery('name', 'ASC', 1); 1229 } 1230 1231 /** 1232 * General query on marriage ages. 1233 * 1234 * @return string 1235 */ 1236 public function minAgeOfMarriage(): string 1237 { 1238 return $this->ageOfMarriageQuery('age', 'ASC', 1); 1239 } 1240 1241 /** 1242 * General query on marriage ages. 1243 * 1244 * @param int $total 1245 * 1246 * @return string 1247 */ 1248 public function minAgeOfMarriageFamilies(int $total = 10): string 1249 { 1250 return $this->ageOfMarriageQuery('nolist', 'ASC', $total); 1251 } 1252 1253 /** 1254 * General query on marriage ages. 1255 * 1256 * @param int $total 1257 * 1258 * @return string 1259 */ 1260 public function minAgeOfMarriageFamiliesList(int $total = 10): string 1261 { 1262 return $this->ageOfMarriageQuery('list', 'ASC', $total); 1263 } 1264 1265 /** 1266 * Find the ages between spouses. 1267 * 1268 * @param string $age_dir 1269 * @param int $total 1270 * 1271 * @return array<array<string,mixed>> 1272 */ 1273 private function ageBetweenSpousesQuery(string $age_dir, int $total): array 1274 { 1275 $prefix = DB::connection()->getTablePrefix(); 1276 1277 $query = DB::table('families') 1278 ->where('f_file', '=', $this->tree->id()) 1279 ->join('dates AS wife', static function (JoinClause $join): void { 1280 $join 1281 ->on('wife.d_gid', '=', 'f_wife') 1282 ->on('wife.d_file', '=', 'f_file') 1283 ->where('wife.d_fact', '=', 'BIRT') 1284 ->where('wife.d_julianday1', '<>', 0); 1285 }) 1286 ->join('dates AS husb', static function (JoinClause $join): void { 1287 $join 1288 ->on('husb.d_gid', '=', 'f_husb') 1289 ->on('husb.d_file', '=', 'f_file') 1290 ->where('husb.d_fact', '=', 'BIRT') 1291 ->where('husb.d_julianday1', '<>', 0); 1292 }); 1293 1294 if ($age_dir === 'DESC') { 1295 $query 1296 ->whereColumn('wife.d_julianday1', '>=', 'husb.d_julianday1') 1297 ->orderBy(new Expression('MIN(' . $prefix . 'wife.d_julianday1) - MIN(' . $prefix . 'husb.d_julianday1)'), 'DESC'); 1298 } else { 1299 $query 1300 ->whereColumn('husb.d_julianday1', '>=', 'wife.d_julianday1') 1301 ->orderBy(new Expression('MIN(' . $prefix . 'husb.d_julianday1) - MIN(' . $prefix . 'wife.d_julianday1)'), 'DESC'); 1302 } 1303 1304 return $query 1305 ->groupBy(['f_id', 'f_file']) 1306 ->select(['families.*']) 1307 ->take($total) 1308 ->get() 1309 ->map(Registry::familyFactory()->mapper($this->tree)) 1310 ->filter(GedcomRecord::accessFilter()) 1311 ->map(function (Family $family) use ($age_dir): array { 1312 $husb_birt_jd = $family->husband()->getBirthDate()->minimumJulianDay(); 1313 $wife_birt_jd = $family->wife()->getBirthDate()->minimumJulianDay(); 1314 1315 if ($age_dir === 'DESC') { 1316 $diff = $wife_birt_jd - $husb_birt_jd; 1317 } else { 1318 $diff = $husb_birt_jd - $wife_birt_jd; 1319 } 1320 1321 return [ 1322 'family' => $family, 1323 'age' => $this->calculateAge($diff), 1324 ]; 1325 }) 1326 ->all(); 1327 } 1328 1329 /** 1330 * Find the age between husband and wife. 1331 * 1332 * @param int $total 1333 * 1334 * @return string 1335 */ 1336 public function ageBetweenSpousesMF(int $total = 10): string 1337 { 1338 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1339 1340 return view('statistics/families/top10-nolist-spouses', [ 1341 'records' => $records, 1342 ]); 1343 } 1344 1345 /** 1346 * Find the age between husband and wife. 1347 * 1348 * @param int $total 1349 * 1350 * @return string 1351 */ 1352 public function ageBetweenSpousesMFList(int $total = 10): string 1353 { 1354 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1355 1356 return view('statistics/families/top10-list-spouses', [ 1357 'records' => $records, 1358 ]); 1359 } 1360 1361 /** 1362 * Find the age between wife and husband.. 1363 * 1364 * @param int $total 1365 * 1366 * @return string 1367 */ 1368 public function ageBetweenSpousesFM(int $total = 10): string 1369 { 1370 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1371 1372 return view('statistics/families/top10-nolist-spouses', [ 1373 'records' => $records, 1374 ]); 1375 } 1376 1377 /** 1378 * Find the age between wife and husband.. 1379 * 1380 * @param int $total 1381 * 1382 * @return string 1383 */ 1384 public function ageBetweenSpousesFMList(int $total = 10): string 1385 { 1386 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1387 1388 return view('statistics/families/top10-list-spouses', [ 1389 'records' => $records, 1390 ]); 1391 } 1392 1393 /** 1394 * General query on ages at marriage. 1395 * 1396 * @param string $sex "M" or "F" 1397 * @param int $year1 1398 * @param int $year2 1399 * 1400 * @return array<stdClass> 1401 */ 1402 public function statsMarrAgeQuery(string $sex, int $year1 = -1, int $year2 = -1): array 1403 { 1404 $prefix = DB::connection()->getTablePrefix(); 1405 1406 $query = DB::table('dates AS married') 1407 ->join('families', static function (JoinClause $join): void { 1408 $join 1409 ->on('f_file', '=', 'married.d_file') 1410 ->on('f_id', '=', 'married.d_gid'); 1411 }) 1412 ->join('dates AS birth', static function (JoinClause $join) use ($sex): void { 1413 $join 1414 ->on('birth.d_file', '=', 'married.d_file') 1415 ->on('birth.d_gid', '=', $sex === 'M' ? 'f_husb' : 'f_wife') 1416 ->where('birth.d_julianday1', '<>', 0) 1417 ->where('birth.d_fact', '=', 'BIRT') 1418 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 1419 }) 1420 ->where('married.d_file', '=', $this->tree->id()) 1421 ->where('married.d_fact', '=', 'MARR') 1422 ->whereIn('married.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 1423 ->whereColumn('married.d_julianday1', '>', 'birth.d_julianday1') 1424 ->select(['f_id', 'birth.d_gid', new Expression($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')]); 1425 1426 if ($year1 >= 0 && $year2 >= 0) { 1427 $query->whereBetween('married.d_year', [$year1, $year2]); 1428 } 1429 1430 return $query 1431 ->get() 1432 ->map(static function (stdClass $row): stdClass { 1433 $row->age = (int) $row->age; 1434 1435 return $row; 1436 }) 1437 ->all(); 1438 } 1439 1440 /** 1441 * General query on marriage ages. 1442 * 1443 * @return string 1444 */ 1445 public function statsMarrAge(): string 1446 { 1447 return (new ChartMarriageAge($this->century_service, $this->tree)) 1448 ->chartMarriageAge(); 1449 } 1450 1451 /** 1452 * Query the database for marriage tags. 1453 * 1454 * @param string $type "full", "name" or "age" 1455 * @param string $age_dir "ASC" or "DESC" 1456 * @param string $sex "F" or "M" 1457 * @param bool $show_years 1458 * 1459 * @return string 1460 */ 1461 private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string 1462 { 1463 if ($sex === 'F') { 1464 $sex_field = 'f_wife'; 1465 } else { 1466 $sex_field = 'f_husb'; 1467 } 1468 1469 if ($age_dir !== 'ASC') { 1470 $age_dir = 'DESC'; 1471 } 1472 1473 $prefix = DB::connection()->getTablePrefix(); 1474 1475 $row = DB::table('families') 1476 ->join('dates AS married', static function (JoinClause $join): void { 1477 $join 1478 ->on('married.d_file', '=', 'f_file') 1479 ->on('married.d_gid', '=', 'f_id') 1480 ->where('married.d_fact', '=', 'MARR'); 1481 }) 1482 ->join('individuals', static function (JoinClause $join) use ($sex, $sex_field): void { 1483 $join 1484 ->on('i_file', '=', 'f_file') 1485 ->on('i_id', '=', $sex_field) 1486 ->where('i_sex', '=', $sex); 1487 }) 1488 ->join('dates AS birth', static function (JoinClause $join): void { 1489 $join 1490 ->on('birth.d_file', '=', 'i_file') 1491 ->on('birth.d_gid', '=', 'i_id') 1492 ->where('birth.d_fact', '=', 'BIRT') 1493 ->where('birth.d_julianday1', '<>', 0); 1494 }) 1495 ->where('f_file', '=', $this->tree->id()) 1496 ->where('married.d_julianday2', '>', new Expression($prefix . 'birth.d_julianday1')) 1497 ->orderBy(new Expression($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1'), $age_dir) 1498 ->select(['f_id AS famid', $sex_field, new Expression($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age'), 'i_id']) 1499 ->take(1) 1500 ->get() 1501 ->first(); 1502 1503 if ($row === null) { 1504 return I18N::translate('This information is not available.'); 1505 } 1506 1507 $family = Registry::familyFactory()->make($row->famid, $this->tree); 1508 $person = Registry::individualFactory()->make($row->i_id, $this->tree); 1509 1510 switch ($type) { 1511 default: 1512 case 'full': 1513 if ($family !== null && $family->canShow()) { 1514 $result = $family->formatList(); 1515 } else { 1516 $result = I18N::translate('This information is private and cannot be shown.'); 1517 } 1518 break; 1519 1520 case 'name': 1521 $result = '<a href="' . e($family->url()) . '">' . $person->fullName() . '</a>'; 1522 break; 1523 1524 case 'age': 1525 $age = $row->age; 1526 1527 if ($show_years) { 1528 $result = $this->calculateAge((int) $row->age); 1529 } else { 1530 $result = I18N::number((int) ($age / 365.25)); 1531 } 1532 1533 break; 1534 } 1535 1536 return $result; 1537 } 1538 1539 /** 1540 * Find the youngest wife. 1541 * 1542 * @return string 1543 */ 1544 public function youngestMarriageFemale(): string 1545 { 1546 return $this->marriageQuery('full', 'ASC', 'F', false); 1547 } 1548 1549 /** 1550 * Find the name of the youngest wife. 1551 * 1552 * @return string 1553 */ 1554 public function youngestMarriageFemaleName(): string 1555 { 1556 return $this->marriageQuery('name', 'ASC', 'F', false); 1557 } 1558 1559 /** 1560 * Find the age of the youngest wife. 1561 * 1562 * @param string $show_years 1563 * 1564 * @return string 1565 */ 1566 public function youngestMarriageFemaleAge(string $show_years = ''): string 1567 { 1568 return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years); 1569 } 1570 1571 /** 1572 * Find the oldest wife. 1573 * 1574 * @return string 1575 */ 1576 public function oldestMarriageFemale(): string 1577 { 1578 return $this->marriageQuery('full', 'DESC', 'F', false); 1579 } 1580 1581 /** 1582 * Find the name of the oldest wife. 1583 * 1584 * @return string 1585 */ 1586 public function oldestMarriageFemaleName(): string 1587 { 1588 return $this->marriageQuery('name', 'DESC', 'F', false); 1589 } 1590 1591 /** 1592 * Find the age of the oldest wife. 1593 * 1594 * @param string $show_years 1595 * 1596 * @return string 1597 */ 1598 public function oldestMarriageFemaleAge(string $show_years = ''): string 1599 { 1600 return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years); 1601 } 1602 1603 /** 1604 * Find the youngest husband. 1605 * 1606 * @return string 1607 */ 1608 public function youngestMarriageMale(): string 1609 { 1610 return $this->marriageQuery('full', 'ASC', 'M', false); 1611 } 1612 1613 /** 1614 * Find the name of the youngest husband. 1615 * 1616 * @return string 1617 */ 1618 public function youngestMarriageMaleName(): string 1619 { 1620 return $this->marriageQuery('name', 'ASC', 'M', false); 1621 } 1622 1623 /** 1624 * Find the age of the youngest husband. 1625 * 1626 * @param string $show_years 1627 * 1628 * @return string 1629 */ 1630 public function youngestMarriageMaleAge(string $show_years = ''): string 1631 { 1632 return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years); 1633 } 1634 1635 /** 1636 * Find the oldest husband. 1637 * 1638 * @return string 1639 */ 1640 public function oldestMarriageMale(): string 1641 { 1642 return $this->marriageQuery('full', 'DESC', 'M', false); 1643 } 1644 1645 /** 1646 * Find the name of the oldest husband. 1647 * 1648 * @return string 1649 */ 1650 public function oldestMarriageMaleName(): string 1651 { 1652 return $this->marriageQuery('name', 'DESC', 'M', false); 1653 } 1654 1655 /** 1656 * Find the age of the oldest husband. 1657 * 1658 * @param string $show_years 1659 * 1660 * @return string 1661 */ 1662 public function oldestMarriageMaleAge(string $show_years = ''): string 1663 { 1664 return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years); 1665 } 1666 1667 /** 1668 * General query on marriages. 1669 * 1670 * @param int $year1 1671 * @param int $year2 1672 * 1673 * @return Builder 1674 */ 1675 public function statsMarriageQuery(int $year1 = -1, int $year2 = -1): Builder 1676 { 1677 $query = DB::table('dates') 1678 ->where('d_file', '=', $this->tree->id()) 1679 ->where('d_fact', '=', 'MARR') 1680 ->select(['d_month', new Expression('COUNT(*) AS total')]) 1681 ->groupBy(['d_month']); 1682 1683 if ($year1 >= 0 && $year2 >= 0) { 1684 $query->whereBetween('d_year', [$year1, $year2]); 1685 } 1686 1687 return $query; 1688 } 1689 1690 /** 1691 * General query on marriages. 1692 * 1693 * @param int $year1 1694 * @param int $year2 1695 * 1696 * @return Builder 1697 */ 1698 public function statsFirstMarriageQuery(int $year1 = -1, int $year2 = -1): Builder 1699 { 1700 $query = DB::table('families') 1701 ->join('dates', static function (JoinClause $join): void { 1702 $join 1703 ->on('d_gid', '=', 'f_id') 1704 ->on('d_file', '=', 'f_file') 1705 ->where('d_fact', '=', 'MARR') 1706 ->whereIn('d_month', ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']) 1707 ->where('d_julianday2', '<>', 0); 1708 }) 1709 ->where('f_file', '=', $this->tree->id()); 1710 1711 if ($year1 >= 0 && $year2 >= 0) { 1712 $query->whereBetween('d_year', [$year1, $year2]); 1713 } 1714 1715 return $query 1716 ->select(['f_husb', 'f_wife', 'd_month AS month']) 1717 ->orderBy('d_julianday2'); 1718 } 1719 1720 /** 1721 * General query on marriages. 1722 * 1723 * @param string|null $color_from 1724 * @param string|null $color_to 1725 * 1726 * @return string 1727 */ 1728 public function statsMarr(string $color_from = null, string $color_to = null): string 1729 { 1730 return (new ChartMarriage($this->century_service, $this->color_service, $this->tree)) 1731 ->chartMarriage($color_from, $color_to); 1732 } 1733 1734 /** 1735 * General divorce query. 1736 * 1737 * @param string|null $color_from 1738 * @param string|null $color_to 1739 * 1740 * @return string 1741 */ 1742 public function statsDiv(string $color_from = null, string $color_to = null): string 1743 { 1744 return (new ChartDivorce($this->century_service, $this->color_service, $this->tree)) 1745 ->chartDivorce($color_from, $color_to); 1746 } 1747} 1748