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