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