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 1036 * @param int $total 1037 * 1038 * @return string 1039 */ 1040 private function ageOfMarriageQuery(string $type, string $age_dir, int $total): string 1041 { 1042 if ($age_dir !== 'ASC') { 1043 $age_dir = 'DESC'; 1044 } 1045 1046 $hrows = $this->runSql( 1047 " SELECT DISTINCT fam.f_id AS family, MIN(husbdeath.d_julianday2-married.d_julianday1) AS age" . 1048 " FROM `##families` AS fam" . 1049 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1050 " LEFT JOIN `##dates` AS husbdeath ON husbdeath.d_file = {$this->tree->id()}" . 1051 " WHERE" . 1052 " fam.f_file = {$this->tree->id()} AND" . 1053 " husbdeath.d_gid = fam.f_husb AND" . 1054 " husbdeath.d_fact = 'DEAT' AND" . 1055 " married.d_gid = fam.f_id AND" . 1056 " married.d_fact = 'MARR' AND" . 1057 " married.d_julianday1 < husbdeath.d_julianday2 AND" . 1058 " married.d_julianday1 <> 0" . 1059 " GROUP BY family" . 1060 " ORDER BY age {$age_dir}" 1061 ); 1062 1063 $wrows = $this->runSql( 1064 " SELECT DISTINCT fam.f_id AS family, MIN(wifedeath.d_julianday2-married.d_julianday1) AS age" . 1065 " FROM `##families` AS fam" . 1066 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1067 " LEFT JOIN `##dates` AS wifedeath ON wifedeath.d_file = {$this->tree->id()}" . 1068 " WHERE" . 1069 " fam.f_file = {$this->tree->id()} AND" . 1070 " wifedeath.d_gid = fam.f_wife AND" . 1071 " wifedeath.d_fact = 'DEAT' AND" . 1072 " married.d_gid = fam.f_id AND" . 1073 " married.d_fact = 'MARR' AND" . 1074 " married.d_julianday1 < wifedeath.d_julianday2 AND" . 1075 " married.d_julianday1 <> 0" . 1076 " GROUP BY family" . 1077 " ORDER BY age {$age_dir}" 1078 ); 1079 1080 $drows = $this->runSql( 1081 " SELECT DISTINCT fam.f_id AS family, MIN(divorced.d_julianday2-married.d_julianday1) AS age" . 1082 " FROM `##families` AS fam" . 1083 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1084 " LEFT JOIN `##dates` AS divorced ON divorced.d_file = {$this->tree->id()}" . 1085 " WHERE" . 1086 " fam.f_file = {$this->tree->id()} AND" . 1087 " married.d_gid = fam.f_id AND" . 1088 " married.d_fact = 'MARR' AND" . 1089 " divorced.d_gid = fam.f_id AND" . 1090 " divorced.d_fact IN ('DIV', 'ANUL', '_SEPR', '_DETS') AND" . 1091 " married.d_julianday1 < divorced.d_julianday2 AND" . 1092 " married.d_julianday1 <> 0" . 1093 " GROUP BY family" . 1094 " ORDER BY age {$age_dir}" 1095 ); 1096 1097 $rows = []; 1098 foreach ($drows as $family) { 1099 $rows[$family->family] = $family->age; 1100 } 1101 1102 foreach ($hrows as $family) { 1103 if (!isset($rows[$family->family])) { 1104 $rows[$family->family] = $family->age; 1105 } 1106 } 1107 1108 foreach ($wrows as $family) { 1109 if (!isset($rows[$family->family])) { 1110 $rows[$family->family] = $family->age; 1111 } elseif ($rows[$family->family] > $family->age) { 1112 $rows[$family->family] = $family->age; 1113 } 1114 } 1115 1116 if ($age_dir === 'DESC') { 1117 arsort($rows); 1118 } else { 1119 asort($rows); 1120 } 1121 1122 $top10 = []; 1123 $i = 0; 1124 foreach ($rows as $fam => $age) { 1125 $family = Family::getInstance($fam, $this->tree); 1126 if ($type === 'name') { 1127 return $family->formatList(); 1128 } 1129 1130 $age = $this->calculateAge((int) $age); 1131 1132 if ($type === 'age') { 1133 return $age; 1134 } 1135 1136 $husb = $family->husband(); 1137 $wife = $family->wife(); 1138 1139 if (($husb && ($husb->getAllDeathDates() || !$husb->isDead())) 1140 && ($wife && ($wife->getAllDeathDates() || !$wife->isDead())) 1141 ) { 1142 if ($family && $family->canShow()) { 1143 if ($type === 'list') { 1144 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')' . '</li>'; 1145 } else { 1146 $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')'; 1147 } 1148 } 1149 if (++$i === $total) { 1150 break; 1151 } 1152 } 1153 } 1154 1155 if ($type === 'list') { 1156 $top10 = implode('', $top10); 1157 } else { 1158 $top10 = implode('; ', $top10); 1159 } 1160 1161 if (I18N::direction() === 'rtl') { 1162 $top10 = str_replace([ 1163 '[', 1164 ']', 1165 '(', 1166 ')', 1167 '+', 1168 ], [ 1169 '‏[', 1170 '‏]', 1171 '‏(', 1172 '‏)', 1173 '‏+', 1174 ], $top10); 1175 } 1176 1177 if ($type === 'list') { 1178 return '<ul>' . $top10 . '</ul>'; 1179 } 1180 1181 return $top10; 1182 } 1183 1184 /** 1185 * General query on marriage ages. 1186 * 1187 * @return string 1188 */ 1189 public function topAgeOfMarriageFamily(): string 1190 { 1191 return $this->ageOfMarriageQuery('name', 'DESC', 1); 1192 } 1193 1194 /** 1195 * General query on marriage ages. 1196 * 1197 * @return string 1198 */ 1199 public function topAgeOfMarriage(): string 1200 { 1201 return $this->ageOfMarriageQuery('age', 'DESC', 1); 1202 } 1203 1204 /** 1205 * General query on marriage ages. 1206 * 1207 * @param int $total 1208 * 1209 * @return string 1210 */ 1211 public function topAgeOfMarriageFamilies(int $total = 10): string 1212 { 1213 return $this->ageOfMarriageQuery('nolist', 'DESC', $total); 1214 } 1215 1216 /** 1217 * General query on marriage ages. 1218 * 1219 * @param int $total 1220 * 1221 * @return string 1222 */ 1223 public function topAgeOfMarriageFamiliesList(int $total = 10): string 1224 { 1225 return $this->ageOfMarriageQuery('list', 'DESC', $total); 1226 } 1227 1228 /** 1229 * General query on marriage ages. 1230 * 1231 * @return string 1232 */ 1233 public function minAgeOfMarriageFamily(): string 1234 { 1235 return $this->ageOfMarriageQuery('name', 'ASC', 1); 1236 } 1237 1238 /** 1239 * General query on marriage ages. 1240 * 1241 * @return string 1242 */ 1243 public function minAgeOfMarriage(): string 1244 { 1245 return $this->ageOfMarriageQuery('age', 'ASC', 1); 1246 } 1247 1248 /** 1249 * General query on marriage ages. 1250 * 1251 * @param int $total 1252 * 1253 * @return string 1254 */ 1255 public function minAgeOfMarriageFamilies(int $total = 10): string 1256 { 1257 return $this->ageOfMarriageQuery('nolist', 'ASC', $total); 1258 } 1259 1260 /** 1261 * General query on marriage ages. 1262 * 1263 * @param int $total 1264 * 1265 * @return string 1266 */ 1267 public function minAgeOfMarriageFamiliesList(int $total = 10): string 1268 { 1269 return $this->ageOfMarriageQuery('list', 'ASC', $total); 1270 } 1271 1272 /** 1273 * Find the ages between spouses. 1274 * 1275 * @param string $age_dir 1276 * @param int $total 1277 * 1278 * @return array 1279 */ 1280 private function ageBetweenSpousesQuery(string $age_dir, int $total): array 1281 { 1282 $prefix = DB::connection()->getTablePrefix(); 1283 1284 $query = DB::table('families') 1285 ->where('f_file', '=', $this->tree->id()) 1286 ->join('dates AS wife', function (JoinClause $join): void { 1287 $join 1288 ->on('wife.d_gid', '=', 'f_wife') 1289 ->on('wife.d_file', '=', 'f_file') 1290 ->where('wife.d_fact', '=', 'BIRT') 1291 ->where('wife.d_julianday1', '<>', 0); 1292 }) 1293 ->join('dates AS husb', function (JoinClause $join): void { 1294 $join 1295 ->on('husb.d_gid', '=', 'f_husb') 1296 ->on('husb.d_file', '=', 'f_file') 1297 ->where('husb.d_fact', '=', 'BIRT') 1298 ->where('husb.d_julianday1', '<>', 0); 1299 }); 1300 1301 if ($age_dir === 'DESC') { 1302 $query 1303 ->whereColumn('wife.d_julianday1', '>=', 'husb.d_julianday1') 1304 ->orderBy(DB::raw('MIN(' . $prefix . 'wife.d_julianday1) - MIN(' . $prefix . 'husb.d_julianday1)'), 'DESC'); 1305 } else { 1306 $query 1307 ->whereColumn('husb.d_julianday1', '>=', 'wife.d_julianday1') 1308 ->orderBy(DB::raw('MIN(' . $prefix . 'husb.d_julianday1) - MIN(' . $prefix . 'wife.d_julianday1)'), 'DESC'); 1309 } 1310 1311 $families = $query 1312 ->groupBy(['f_id', 'f_file']) 1313 ->select('families.*') 1314 ->take($total) 1315 ->get() 1316 ->map(Family::rowMapper()) 1317 ->filter(GedcomRecord::accessFilter()); 1318 1319 $top10 = []; 1320 1321 /** @var Family $family */ 1322 foreach ($families as $family) { 1323 $husb_birt_jd = $family->husband()->getBirthDate()->minimumJulianDay(); 1324 $wife_birt_jd = $family->wife()->getBirthDate()->minimumJulianDay(); 1325 1326 if ($age_dir === 'DESC') { 1327 $diff = $wife_birt_jd - $husb_birt_jd; 1328 } else { 1329 $diff = $husb_birt_jd - $wife_birt_jd; 1330 } 1331 1332 $top10[] = [ 1333 'family' => $family, 1334 'age' => $this->calculateAge((int) $diff), 1335 ]; 1336 } 1337 1338 return $top10; 1339 } 1340 1341 /** 1342 * Find the age between husband and wife. 1343 * 1344 * @param int $total 1345 * 1346 * @return string 1347 */ 1348 public function ageBetweenSpousesMF(int $total = 10): string 1349 { 1350 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1351 1352 return view( 1353 'statistics/families/top10-nolist-spouses', 1354 [ 1355 'records' => $records, 1356 ] 1357 ); 1358 } 1359 1360 /** 1361 * Find the age between husband and wife. 1362 * 1363 * @param int $total 1364 * 1365 * @return string 1366 */ 1367 public function ageBetweenSpousesMFList(int $total = 10): string 1368 { 1369 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1370 1371 return view( 1372 'statistics/families/top10-list-spouses', 1373 [ 1374 'records' => $records, 1375 ] 1376 ); 1377 } 1378 1379 /** 1380 * Find the age between wife and husband.. 1381 * 1382 * @param int $total 1383 * 1384 * @return string 1385 */ 1386 public function ageBetweenSpousesFM(int $total = 10): string 1387 { 1388 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1389 1390 return view( 1391 'statistics/families/top10-nolist-spouses', 1392 [ 1393 'records' => $records, 1394 ] 1395 ); 1396 } 1397 1398 /** 1399 * Find the age between wife and husband.. 1400 * 1401 * @param int $total 1402 * 1403 * @return string 1404 */ 1405 public function ageBetweenSpousesFMList(int $total = 10): string 1406 { 1407 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1408 1409 return view( 1410 'statistics/families/top10-list-spouses', 1411 [ 1412 'records' => $records, 1413 ] 1414 ); 1415 } 1416 1417 /** 1418 * General query on ages at marriage. 1419 * 1420 * @param string $sex 1421 * @param int $year1 1422 * @param int $year2 1423 * 1424 * @return array 1425 */ 1426 public function statsMarrAgeQuery($sex = 'M', $year1 = -1, $year2 = -1): array 1427 { 1428 if ($year1 >= 0 && $year2 >= 0) { 1429 $years = " married.d_year BETWEEN {$year1} AND {$year2} AND "; 1430 } else { 1431 $years = ''; 1432 } 1433 1434 $rows = $this->runSql( 1435 "SELECT " . 1436 " fam.f_id, " . 1437 " birth.d_gid, " . 1438 " married.d_julianday2-birth.d_julianday1 AS age " . 1439 "FROM `##dates` AS married " . 1440 "JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) " . 1441 "JOIN `##dates` AS birth ON (birth.d_gid=fam.f_husb AND birth.d_file=fam.f_file) " . 1442 "WHERE " . 1443 " '{$sex}' IN ('M', 'BOTH') AND {$years} " . 1444 " married.d_file={$this->tree->id()} AND married.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND married.d_fact='MARR' AND " . 1445 " birth.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND birth.d_fact='BIRT' AND " . 1446 " married.d_julianday1>birth.d_julianday1 AND birth.d_julianday1<>0 " . 1447 "UNION ALL " . 1448 "SELECT " . 1449 " fam.f_id, " . 1450 " birth.d_gid, " . 1451 " married.d_julianday2-birth.d_julianday1 AS age " . 1452 "FROM `##dates` AS married " . 1453 "JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) " . 1454 "JOIN `##dates` AS birth ON (birth.d_gid=fam.f_wife AND birth.d_file=fam.f_file) " . 1455 "WHERE " . 1456 " '{$sex}' IN ('F', 'BOTH') AND {$years} " . 1457 " married.d_file={$this->tree->id()} AND married.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND married.d_fact='MARR' AND " . 1458 " birth.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND birth.d_fact='BIRT' AND " . 1459 " married.d_julianday1>birth.d_julianday1 AND birth.d_julianday1<>0 " 1460 ); 1461 1462 foreach ($rows as $row) { 1463 $row->age = (int) $row->age; 1464 } 1465 1466 return $rows; 1467 } 1468 1469 /** 1470 * General query on marriage ages. 1471 * 1472 * @return string 1473 */ 1474 public function statsMarrAge(): string 1475 { 1476 return (new ChartMarriageAge($this->tree)) 1477 ->chartMarriageAge(); 1478 } 1479 1480 /** 1481 * Query the database for marriage tags. 1482 * 1483 * @param string $type "full", "name" or "age" 1484 * @param string $age_dir "ASC" or "DESC" 1485 * @param string $sex "F" or "M" 1486 * @param bool $show_years 1487 * 1488 * @return string 1489 */ 1490 private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string 1491 { 1492 if ($sex === 'F') { 1493 $sex_field = 'f_wife'; 1494 } else { 1495 $sex_field = 'f_husb'; 1496 } 1497 1498 if ($age_dir !== 'ASC') { 1499 $age_dir = 'DESC'; 1500 } 1501 1502 $prefix = DB::connection()->getTablePrefix(); 1503 1504 $row = DB::table('families') 1505 ->join('dates AS married', function (JoinClause $join): void { 1506 $join 1507 ->on('married.d_file', '=', 'f_file') 1508 ->on('married.d_gid', '=', 'f_id') 1509 ->where('married.d_fact', '=', 'MARR'); 1510 }) 1511 ->join('individuals', function (JoinClause $join) use ($sex, $sex_field): void { 1512 $join 1513 ->on('i_file', '=', 'f_file') 1514 ->on('i_id', '=', $sex_field) 1515 ->where('i_sex', '=', $sex); 1516 }) 1517 ->join('dates AS birth', function (JoinClause $join): void { 1518 $join 1519 ->on('birth.d_file', '=', 'i_file') 1520 ->on('birth.d_gid', '=', 'i_id') 1521 ->where('birth.d_fact', '=', 'BIRT') 1522 ->where('birth.d_julianday1', '<>', 0); 1523 }) 1524 ->where('f_file', '=', $this->tree->id()) 1525 ->where('married.d_julianday2', '>', 'birth.d_julianday1') 1526 ->orderBy(DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1'), $age_dir) 1527 ->select(['f_id AS famid', $sex_field, DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age'), 'i_id']) 1528 ->take(1) 1529 ->get() 1530 ->first(); 1531 1532 if ($row === null) { 1533 return ''; 1534 } 1535 1536 $family = Family::getInstance($row->famid, $this->tree); 1537 $person = Individual::getInstance($row->i_id, $this->tree); 1538 1539 switch ($type) { 1540 default: 1541 case 'full': 1542 if ($family && $family->canShow()) { 1543 $result = $family->formatList(); 1544 } else { 1545 $result = I18N::translate('This information is private and cannot be shown.'); 1546 } 1547 break; 1548 1549 case 'name': 1550 $result = '<a href="' . e($family->url()) . '">' . $person->fullName() . '</a>'; 1551 break; 1552 1553 case 'age': 1554 $age = $row->age; 1555 1556 if ($show_years) { 1557 $result = $this->calculateAge((int) $row->age); 1558 } else { 1559 $result = I18N::number((int) ($age / 365.25)); 1560 } 1561 1562 break; 1563 } 1564 1565 return $result; 1566 } 1567 1568 /** 1569 * Find the youngest wife. 1570 * 1571 * @return string 1572 */ 1573 public function youngestMarriageFemale(): string 1574 { 1575 return $this->marriageQuery('full', 'ASC', 'F', false); 1576 } 1577 1578 /** 1579 * Find the name of the youngest wife. 1580 * 1581 * @return string 1582 */ 1583 public function youngestMarriageFemaleName(): string 1584 { 1585 return $this->marriageQuery('name', 'ASC', 'F', false); 1586 } 1587 1588 /** 1589 * Find the age of the youngest wife. 1590 * 1591 * @param string $show_years 1592 * 1593 * @return string 1594 */ 1595 public function youngestMarriageFemaleAge(string $show_years = ''): string 1596 { 1597 return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years); 1598 } 1599 1600 /** 1601 * Find the oldest wife. 1602 * 1603 * @return string 1604 */ 1605 public function oldestMarriageFemale(): string 1606 { 1607 return $this->marriageQuery('full', 'DESC', 'F', false); 1608 } 1609 1610 /** 1611 * Find the name of the oldest wife. 1612 * 1613 * @return string 1614 */ 1615 public function oldestMarriageFemaleName(): string 1616 { 1617 return $this->marriageQuery('name', 'DESC', 'F', false); 1618 } 1619 1620 /** 1621 * Find the age of the oldest wife. 1622 * 1623 * @param string $show_years 1624 * 1625 * @return string 1626 */ 1627 public function oldestMarriageFemaleAge(string $show_years = ''): string 1628 { 1629 return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years); 1630 } 1631 1632 /** 1633 * Find the youngest husband. 1634 * 1635 * @return string 1636 */ 1637 public function youngestMarriageMale(): string 1638 { 1639 return $this->marriageQuery('full', 'ASC', 'M', false); 1640 } 1641 1642 /** 1643 * Find the name of the youngest husband. 1644 * 1645 * @return string 1646 */ 1647 public function youngestMarriageMaleName(): string 1648 { 1649 return $this->marriageQuery('name', 'ASC', 'M', false); 1650 } 1651 1652 /** 1653 * Find the age of the youngest husband. 1654 * 1655 * @param string $show_years 1656 * 1657 * @return string 1658 */ 1659 public function youngestMarriageMaleAge(string $show_years = ''): string 1660 { 1661 return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years); 1662 } 1663 1664 /** 1665 * Find the oldest husband. 1666 * 1667 * @return string 1668 */ 1669 public function oldestMarriageMale(): string 1670 { 1671 return $this->marriageQuery('full', 'DESC', 'M', false); 1672 } 1673 1674 /** 1675 * Find the name of the oldest husband. 1676 * 1677 * @return string 1678 */ 1679 public function oldestMarriageMaleName(): string 1680 { 1681 return $this->marriageQuery('name', 'DESC', 'M', false); 1682 } 1683 1684 /** 1685 * Find the age of the oldest husband. 1686 * 1687 * @param string $show_years 1688 * 1689 * @return string 1690 */ 1691 public function oldestMarriageMaleAge(string $show_years = ''): string 1692 { 1693 return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years); 1694 } 1695 1696 /** 1697 * General query on marriages. 1698 * 1699 * @param bool $first_marriage 1700 * @param int $year1 1701 * @param int $year2 1702 * 1703 * @return array 1704 */ 1705 public function statsMarrQuery(bool $first_marriage = false, int $year1 = -1, int $year2 = -1): array 1706 { 1707 if ($first_marriage) { 1708 $query = DB::table('families') 1709 ->join('dates', function (JoinClause $join): void { 1710 $join 1711 ->on('d_gid', '=', 'f_id') 1712 ->on('d_file', '=', 'f_file') 1713 ->where('d_fact', '=', 'MARR') 1714 ->where('d_julianday2', '<>', 0); 1715 })->join('individuals', function (JoinClause $join): void { 1716 $join 1717 ->on('i_file', '=', 'f_file'); 1718 }) 1719 ->where('f_file', '=', $this->tree->id()) 1720 ->where(function (Builder $query): void { 1721 $query 1722 ->whereColumn('i_id', '=', 'f_husb') 1723 ->orWhereColumn('i_id', '=', 'f_wife'); 1724 }); 1725 1726 if ($year1 >= 0 && $year2 >= 0) { 1727 $query->whereBetween('d_year', [$year1, $year2]); 1728 } 1729 1730 return $query 1731 ->select(['f_id AS fams', 'f_husb', 'f_wife', 'd_julianday2 AS age', 'd_month AS month', 'i_id AS indi']) 1732 ->orderBy('f_id') 1733 ->orderBy('i_id') 1734 ->orderBy('d_julianday2') 1735 ->get() 1736 ->all(); 1737 } else { 1738 $query = DB::table('dates') 1739 ->where('d_file', '=', $this->tree->id()) 1740 ->where('d_fact', '=', 'MARR') 1741 ->select(['d_month', DB::raw('COUNT(*) AS total')]) 1742 ->groupBy('d_month'); 1743 1744 if ($year1 >= 0 && $year2 >= 0) { 1745 $query->whereBetween('d_year', [$year1, $year2]); 1746 } 1747 1748 return $query 1749 ->get() 1750 ->all(); 1751 } 1752 } 1753 1754 /** 1755 * General query on marriages. 1756 * 1757 * @param string|null $color_from 1758 * @param string|null $color_to 1759 * 1760 * @return string 1761 */ 1762 public function statsMarr(string $color_from = null, string $color_to = null): string 1763 { 1764 return (new ChartMarriage($this->tree)) 1765 ->chartMarriage($color_from, $color_to); 1766 } 1767 1768 /** 1769 * General divorce query. 1770 * 1771 * @param string|null $color_from 1772 * @param string|null $color_to 1773 * 1774 * @return string 1775 */ 1776 public function statsDiv(string $color_from = null, string $color_to = null): string 1777 { 1778 return (new ChartDivorce($this->tree)) 1779 ->chartDivorce($color_from, $color_to); 1780 } 1781} 1782