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; 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 familes/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 ''; 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 ($husb && ($husb->getAllDeathDates() || !$husb->isDead()) && $wife && ($wife->getAllDeathDates() || !$wife->isDead())) { 1132 if ($family->canShow()) { 1133 if ($type === 'list') { 1134 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')' . '</li>'; 1135 } else { 1136 $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')'; 1137 } 1138 } 1139 if (++$i === $total) { 1140 break; 1141 } 1142 } 1143 } 1144 1145 if ($type === 'list') { 1146 $top10 = implode('', $top10); 1147 } else { 1148 $top10 = implode('; ', $top10); 1149 } 1150 1151 if (I18N::direction() === 'rtl') { 1152 $top10 = str_replace([ 1153 '[', 1154 ']', 1155 '(', 1156 ')', 1157 '+', 1158 ], [ 1159 '‏[', 1160 '‏]', 1161 '‏(', 1162 '‏)', 1163 '‏+', 1164 ], $top10); 1165 } 1166 1167 if ($type === 'list') { 1168 return '<ul>' . $top10 . '</ul>'; 1169 } 1170 1171 return $top10; 1172 } 1173 1174 /** 1175 * General query on marriage ages. 1176 * 1177 * @return string 1178 */ 1179 public function topAgeOfMarriageFamily(): string 1180 { 1181 return $this->ageOfMarriageQuery('name', 'DESC', 1); 1182 } 1183 1184 /** 1185 * General query on marriage ages. 1186 * 1187 * @return string 1188 */ 1189 public function topAgeOfMarriage(): string 1190 { 1191 return $this->ageOfMarriageQuery('age', 'DESC', 1); 1192 } 1193 1194 /** 1195 * General query on marriage ages. 1196 * 1197 * @param int $total 1198 * 1199 * @return string 1200 */ 1201 public function topAgeOfMarriageFamilies(int $total = 10): string 1202 { 1203 return $this->ageOfMarriageQuery('nolist', 'DESC', $total); 1204 } 1205 1206 /** 1207 * General query on marriage ages. 1208 * 1209 * @param int $total 1210 * 1211 * @return string 1212 */ 1213 public function topAgeOfMarriageFamiliesList(int $total = 10): string 1214 { 1215 return $this->ageOfMarriageQuery('list', 'DESC', $total); 1216 } 1217 1218 /** 1219 * General query on marriage ages. 1220 * 1221 * @return string 1222 */ 1223 public function minAgeOfMarriageFamily(): string 1224 { 1225 return $this->ageOfMarriageQuery('name', 'ASC', 1); 1226 } 1227 1228 /** 1229 * General query on marriage ages. 1230 * 1231 * @return string 1232 */ 1233 public function minAgeOfMarriage(): string 1234 { 1235 return $this->ageOfMarriageQuery('age', 'ASC', 1); 1236 } 1237 1238 /** 1239 * General query on marriage ages. 1240 * 1241 * @param int $total 1242 * 1243 * @return string 1244 */ 1245 public function minAgeOfMarriageFamilies(int $total = 10): string 1246 { 1247 return $this->ageOfMarriageQuery('nolist', 'ASC', $total); 1248 } 1249 1250 /** 1251 * General query on marriage ages. 1252 * 1253 * @param int $total 1254 * 1255 * @return string 1256 */ 1257 public function minAgeOfMarriageFamiliesList(int $total = 10): string 1258 { 1259 return $this->ageOfMarriageQuery('list', 'ASC', $total); 1260 } 1261 1262 /** 1263 * Find the ages between spouses. 1264 * 1265 * @param string $age_dir 1266 * @param int $total 1267 * 1268 * @return array<array<string,mixed>> 1269 */ 1270 private function ageBetweenSpousesQuery(string $age_dir, int $total): array 1271 { 1272 $prefix = DB::connection()->getTablePrefix(); 1273 1274 $query = DB::table('families') 1275 ->where('f_file', '=', $this->tree->id()) 1276 ->join('dates AS wife', static function (JoinClause $join): void { 1277 $join 1278 ->on('wife.d_gid', '=', 'f_wife') 1279 ->on('wife.d_file', '=', 'f_file') 1280 ->where('wife.d_fact', '=', 'BIRT') 1281 ->where('wife.d_julianday1', '<>', 0); 1282 }) 1283 ->join('dates AS husb', static function (JoinClause $join): void { 1284 $join 1285 ->on('husb.d_gid', '=', 'f_husb') 1286 ->on('husb.d_file', '=', 'f_file') 1287 ->where('husb.d_fact', '=', 'BIRT') 1288 ->where('husb.d_julianday1', '<>', 0); 1289 }); 1290 1291 if ($age_dir === 'DESC') { 1292 $query 1293 ->whereColumn('wife.d_julianday1', '>=', 'husb.d_julianday1') 1294 ->orderBy(new Expression('MIN(' . $prefix . 'wife.d_julianday1) - MIN(' . $prefix . 'husb.d_julianday1)'), 'DESC'); 1295 } else { 1296 $query 1297 ->whereColumn('husb.d_julianday1', '>=', 'wife.d_julianday1') 1298 ->orderBy(new Expression('MIN(' . $prefix . 'husb.d_julianday1) - MIN(' . $prefix . 'wife.d_julianday1)'), 'DESC'); 1299 } 1300 1301 return $query 1302 ->groupBy(['f_id', 'f_file']) 1303 ->select(['families.*']) 1304 ->take($total) 1305 ->get() 1306 ->map(Registry::familyFactory()->mapper($this->tree)) 1307 ->filter(GedcomRecord::accessFilter()) 1308 ->map(function (Family $family) use ($age_dir): array { 1309 $husb_birt_jd = $family->husband()->getBirthDate()->minimumJulianDay(); 1310 $wife_birt_jd = $family->wife()->getBirthDate()->minimumJulianDay(); 1311 1312 if ($age_dir === 'DESC') { 1313 $diff = $wife_birt_jd - $husb_birt_jd; 1314 } else { 1315 $diff = $husb_birt_jd - $wife_birt_jd; 1316 } 1317 1318 return [ 1319 'family' => $family, 1320 'age' => $this->calculateAge($diff), 1321 ]; 1322 }) 1323 ->all(); 1324 } 1325 1326 /** 1327 * Find the age between husband and wife. 1328 * 1329 * @param int $total 1330 * 1331 * @return string 1332 */ 1333 public function ageBetweenSpousesMF(int $total = 10): string 1334 { 1335 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1336 1337 return view('statistics/families/top10-nolist-spouses', [ 1338 'records' => $records, 1339 ]); 1340 } 1341 1342 /** 1343 * Find the age between husband and wife. 1344 * 1345 * @param int $total 1346 * 1347 * @return string 1348 */ 1349 public function ageBetweenSpousesMFList(int $total = 10): string 1350 { 1351 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1352 1353 return view('statistics/families/top10-list-spouses', [ 1354 'records' => $records, 1355 ]); 1356 } 1357 1358 /** 1359 * Find the age between wife and husband.. 1360 * 1361 * @param int $total 1362 * 1363 * @return string 1364 */ 1365 public function ageBetweenSpousesFM(int $total = 10): string 1366 { 1367 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1368 1369 return view('statistics/families/top10-nolist-spouses', [ 1370 'records' => $records, 1371 ]); 1372 } 1373 1374 /** 1375 * Find the age between wife and husband.. 1376 * 1377 * @param int $total 1378 * 1379 * @return string 1380 */ 1381 public function ageBetweenSpousesFMList(int $total = 10): string 1382 { 1383 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1384 1385 return view('statistics/families/top10-list-spouses', [ 1386 'records' => $records, 1387 ]); 1388 } 1389 1390 /** 1391 * General query on ages at marriage. 1392 * 1393 * @param string $sex "M" or "F" 1394 * @param int $year1 1395 * @param int $year2 1396 * 1397 * @return array<stdClass> 1398 */ 1399 public function statsMarrAgeQuery(string $sex, int $year1 = -1, int $year2 = -1): array 1400 { 1401 $prefix = DB::connection()->getTablePrefix(); 1402 1403 $query = DB::table('dates AS married') 1404 ->join('families', static function (JoinClause $join): void { 1405 $join 1406 ->on('f_file', '=', 'married.d_file') 1407 ->on('f_id', '=', 'married.d_gid'); 1408 }) 1409 ->join('dates AS birth', static function (JoinClause $join) use ($sex): void { 1410 $join 1411 ->on('birth.d_file', '=', 'married.d_file') 1412 ->on('birth.d_gid', '=', $sex === 'M' ? 'f_husb' : 'f_wife') 1413 ->where('birth.d_julianday1', '<>', 0) 1414 ->where('birth.d_fact', '=', 'BIRT') 1415 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 1416 }) 1417 ->where('married.d_file', '=', $this->tree->id()) 1418 ->where('married.d_fact', '=', 'MARR') 1419 ->whereIn('married.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 1420 ->whereColumn('married.d_julianday1', '>', 'birth.d_julianday1') 1421 ->select(['f_id', 'birth.d_gid', new Expression($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')]); 1422 1423 if ($year1 >= 0 && $year2 >= 0) { 1424 $query->whereBetween('married.d_year', [$year1, $year2]); 1425 } 1426 1427 return $query 1428 ->get() 1429 ->map(static function (object $row): object { 1430 $row->age = (int) $row->age; 1431 1432 return $row; 1433 }) 1434 ->all(); 1435 } 1436 1437 /** 1438 * General query on marriage ages. 1439 * 1440 * @return string 1441 */ 1442 public function statsMarrAge(): string 1443 { 1444 return (new ChartMarriageAge($this->century_service, $this->tree)) 1445 ->chartMarriageAge(); 1446 } 1447 1448 /** 1449 * Query the database for marriage tags. 1450 * 1451 * @param string $type "full", "name" or "age" 1452 * @param string $age_dir "ASC" or "DESC" 1453 * @param string $sex "F" or "M" 1454 * @param bool $show_years 1455 * 1456 * @return string 1457 */ 1458 private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string 1459 { 1460 if ($sex === 'F') { 1461 $sex_field = 'f_wife'; 1462 } else { 1463 $sex_field = 'f_husb'; 1464 } 1465 1466 if ($age_dir !== 'ASC') { 1467 $age_dir = 'DESC'; 1468 } 1469 1470 $prefix = DB::connection()->getTablePrefix(); 1471 1472 $row = DB::table('families') 1473 ->join('dates AS married', static function (JoinClause $join): void { 1474 $join 1475 ->on('married.d_file', '=', 'f_file') 1476 ->on('married.d_gid', '=', 'f_id') 1477 ->where('married.d_fact', '=', 'MARR'); 1478 }) 1479 ->join('individuals', static function (JoinClause $join) use ($sex, $sex_field): void { 1480 $join 1481 ->on('i_file', '=', 'f_file') 1482 ->on('i_id', '=', $sex_field) 1483 ->where('i_sex', '=', $sex); 1484 }) 1485 ->join('dates AS birth', static function (JoinClause $join): void { 1486 $join 1487 ->on('birth.d_file', '=', 'i_file') 1488 ->on('birth.d_gid', '=', 'i_id') 1489 ->where('birth.d_fact', '=', 'BIRT') 1490 ->where('birth.d_julianday1', '<>', 0); 1491 }) 1492 ->where('f_file', '=', $this->tree->id()) 1493 ->where('married.d_julianday2', '>', 'birth.d_julianday1') 1494 ->orderBy(new Expression($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1'), $age_dir) 1495 ->select(['f_id AS famid', $sex_field, new Expression($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age'), 'i_id']) 1496 ->take(1) 1497 ->get() 1498 ->first(); 1499 1500 if ($row === null) { 1501 return ''; 1502 } 1503 1504 $family = Registry::familyFactory()->make($row->famid, $this->tree); 1505 $person = Registry::individualFactory()->make($row->i_id, $this->tree); 1506 1507 switch ($type) { 1508 default: 1509 case 'full': 1510 if ($family !== null && $family->canShow()) { 1511 $result = $family->formatList(); 1512 } else { 1513 $result = I18N::translate('This information is private and cannot be shown.'); 1514 } 1515 break; 1516 1517 case 'name': 1518 $result = '<a href="' . e($family->url()) . '">' . $person->fullName() . '</a>'; 1519 break; 1520 1521 case 'age': 1522 $age = $row->age; 1523 1524 if ($show_years) { 1525 $result = $this->calculateAge((int) $row->age); 1526 } else { 1527 $result = I18N::number((int) ($age / 365.25)); 1528 } 1529 1530 break; 1531 } 1532 1533 return $result; 1534 } 1535 1536 /** 1537 * Find the youngest wife. 1538 * 1539 * @return string 1540 */ 1541 public function youngestMarriageFemale(): string 1542 { 1543 return $this->marriageQuery('full', 'ASC', 'F', false); 1544 } 1545 1546 /** 1547 * Find the name of the youngest wife. 1548 * 1549 * @return string 1550 */ 1551 public function youngestMarriageFemaleName(): string 1552 { 1553 return $this->marriageQuery('name', 'ASC', 'F', false); 1554 } 1555 1556 /** 1557 * Find the age of the youngest wife. 1558 * 1559 * @param string $show_years 1560 * 1561 * @return string 1562 */ 1563 public function youngestMarriageFemaleAge(string $show_years = ''): string 1564 { 1565 return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years); 1566 } 1567 1568 /** 1569 * Find the oldest wife. 1570 * 1571 * @return string 1572 */ 1573 public function oldestMarriageFemale(): string 1574 { 1575 return $this->marriageQuery('full', 'DESC', 'F', false); 1576 } 1577 1578 /** 1579 * Find the name of the oldest wife. 1580 * 1581 * @return string 1582 */ 1583 public function oldestMarriageFemaleName(): string 1584 { 1585 return $this->marriageQuery('name', 'DESC', 'F', false); 1586 } 1587 1588 /** 1589 * Find the age of the oldest wife. 1590 * 1591 * @param string $show_years 1592 * 1593 * @return string 1594 */ 1595 public function oldestMarriageFemaleAge(string $show_years = ''): string 1596 { 1597 return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years); 1598 } 1599 1600 /** 1601 * Find the youngest husband. 1602 * 1603 * @return string 1604 */ 1605 public function youngestMarriageMale(): string 1606 { 1607 return $this->marriageQuery('full', 'ASC', 'M', false); 1608 } 1609 1610 /** 1611 * Find the name of the youngest husband. 1612 * 1613 * @return string 1614 */ 1615 public function youngestMarriageMaleName(): string 1616 { 1617 return $this->marriageQuery('name', 'ASC', 'M', false); 1618 } 1619 1620 /** 1621 * Find the age of the youngest husband. 1622 * 1623 * @param string $show_years 1624 * 1625 * @return string 1626 */ 1627 public function youngestMarriageMaleAge(string $show_years = ''): string 1628 { 1629 return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years); 1630 } 1631 1632 /** 1633 * Find the oldest husband. 1634 * 1635 * @return string 1636 */ 1637 public function oldestMarriageMale(): string 1638 { 1639 return $this->marriageQuery('full', 'DESC', 'M', false); 1640 } 1641 1642 /** 1643 * Find the name of the oldest husband. 1644 * 1645 * @return string 1646 */ 1647 public function oldestMarriageMaleName(): string 1648 { 1649 return $this->marriageQuery('name', 'DESC', 'M', false); 1650 } 1651 1652 /** 1653 * Find the age of the oldest husband. 1654 * 1655 * @param string $show_years 1656 * 1657 * @return string 1658 */ 1659 public function oldestMarriageMaleAge(string $show_years = ''): string 1660 { 1661 return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years); 1662 } 1663 1664 /** 1665 * General query on marriages. 1666 * 1667 * @param int $year1 1668 * @param int $year2 1669 * 1670 * @return Builder 1671 */ 1672 public function statsMarriageQuery(int $year1 = -1, int $year2 = -1): Builder 1673 { 1674 $query = DB::table('dates') 1675 ->where('d_file', '=', $this->tree->id()) 1676 ->where('d_fact', '=', 'MARR') 1677 ->select(['d_month', new Expression('COUNT(*) AS total')]) 1678 ->groupBy(['d_month']); 1679 1680 if ($year1 >= 0 && $year2 >= 0) { 1681 $query->whereBetween('d_year', [$year1, $year2]); 1682 } 1683 1684 return $query; 1685 } 1686 1687 /** 1688 * General query on marriages. 1689 * 1690 * @param int $year1 1691 * @param int $year2 1692 * 1693 * @return Builder 1694 */ 1695 public function statsFirstMarriageQuery(int $year1 = -1, int $year2 = -1): Builder 1696 { 1697 $query = DB::table('families') 1698 ->join('dates', static function (JoinClause $join): void { 1699 $join 1700 ->on('d_gid', '=', 'f_id') 1701 ->on('d_file', '=', 'f_file') 1702 ->where('d_fact', '=', 'MARR') 1703 ->whereIn('d_month', ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']) 1704 ->where('d_julianday2', '<>', 0); 1705 }) 1706 ->where('f_file', '=', $this->tree->id()); 1707 1708 if ($year1 >= 0 && $year2 >= 0) { 1709 $query->whereBetween('d_year', [$year1, $year2]); 1710 } 1711 1712 return $query 1713 ->select(['f_husb', 'f_wife', 'd_month AS month']) 1714 ->orderBy('d_julianday2'); 1715 } 1716 1717 /** 1718 * General query on marriages. 1719 * 1720 * @param string|null $color_from 1721 * @param string|null $color_to 1722 * 1723 * @return string 1724 */ 1725 public function statsMarr(string $color_from = null, string $color_to = null): string 1726 { 1727 return (new ChartMarriage($this->century_service, $this->color_service, $this->tree)) 1728 ->chartMarriage($color_from, $color_to); 1729 } 1730 1731 /** 1732 * General divorce query. 1733 * 1734 * @param string|null $color_from 1735 * @param string|null $color_to 1736 * 1737 * @return string 1738 */ 1739 public function statsDiv(string $color_from = null, string $color_to = null): string 1740 { 1741 return (new ChartDivorce($this->century_service, $this->color_service, $this->tree)) 1742 ->chartDivorce($color_from, $color_to); 1743 } 1744} 1745