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