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