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