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