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