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