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