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