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