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