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