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