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