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