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