1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2018 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->getFullName() . '</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->getFullName() . '</a></li>'; 292 } else { 293 $top10[] = '<a href="' . e($family->url()) . '">' . $family->getFullName() . '</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 string $size 330 * @param int $year1 331 * @param int $year2 332 * 333 * @return string 334 */ 335 public function chartNoChildrenFamilies(string $size = '220x200', 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, $size, $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->getFullName() . '</a> '; 536 $return .= I18N::translate('and') . ' '; 537 $return .= '<a href="' . e($child1->url()) . '">' . $child1->getFullName() . '</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 return view( 585 'statistics/families/top10-nolist-age', 586 [ 587 'record' => $record, 588 ] 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( 605 'statistics/families/top10-list-age', 606 [ 607 'records' => $records, 608 ] 609 ); 610 } 611 612 /** 613 * General query on familes/children. 614 * 615 * @param string $sex 616 * @param int $year1 617 * @param int $year2 618 * 619 * @return stdClass[] 620 */ 621 public function statsChildrenQuery(string $sex = 'BOTH', int $year1 = -1, int $year2 = -1): array 622 { 623 if ($sex === 'M') { 624 $sql = 625 "SELECT num, COUNT(*) AS total FROM " . 626 "(SELECT count(i_sex) AS num FROM `##link` " . 627 "LEFT OUTER JOIN `##individuals` " . 628 "ON l_from=i_id AND l_file=i_file AND i_sex='M' AND l_type='FAMC' " . 629 "JOIN `##families` ON f_file=l_file AND f_id=l_to WHERE f_file={$this->tree->id()} GROUP BY l_to" . 630 ") boys" . 631 " GROUP BY num" . 632 " ORDER BY num"; 633 } elseif ($sex === 'F') { 634 $sql = 635 "SELECT num, COUNT(*) AS total FROM " . 636 "(SELECT count(i_sex) AS num FROM `##link` " . 637 "LEFT OUTER JOIN `##individuals` " . 638 "ON l_from=i_id AND l_file=i_file AND i_sex='F' AND l_type='FAMC' " . 639 "JOIN `##families` ON f_file=l_file AND f_id=l_to WHERE f_file={$this->tree->id()} GROUP BY l_to" . 640 ") girls" . 641 " GROUP BY num" . 642 " ORDER BY num"; 643 } else { 644 $sql = "SELECT f_numchil, COUNT(*) AS total FROM `##families` "; 645 646 if ($year1 >= 0 && $year2 >= 0) { 647 $sql .= 648 "AS fam LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" 649 . " WHERE" 650 . " married.d_gid = fam.f_id AND" 651 . " fam.f_file = {$this->tree->id()} AND" 652 . " married.d_fact = 'MARR' AND" 653 . " married.d_year BETWEEN '{$year1}' AND '{$year2}'"; 654 } else { 655 $sql .= "WHERE f_file={$this->tree->id()}"; 656 } 657 658 $sql .= ' GROUP BY f_numchil'; 659 } 660 661 return $this->runSql($sql); 662 } 663 664 /** 665 * Genearl query on families/children. 666 * 667 * @param string $size 668 * 669 * @return string 670 */ 671 public function statsChildren(string $size = '220x200'): string 672 { 673 return (new ChartChildren($this->tree)) 674 ->chartChildren($size); 675 } 676 677 /** 678 * Count the total children. 679 * 680 * @return string 681 */ 682 public function totalChildren(): string 683 { 684 $total = (int) Database::prepare( 685 "SELECT SUM(f_numchil) FROM `##families` WHERE f_file = :tree_id" 686 )->execute([ 687 'tree_id' => $this->tree->id(), 688 ])->fetchOne(); 689 690 return I18N::number($total); 691 } 692 693 /** 694 * Find the average number of children in families. 695 * 696 * @return string 697 */ 698 public function averageChildren(): string 699 { 700 $average = (float) Database::prepare( 701 "SELECT AVG(f_numchil) AS tot FROM `##families` WHERE f_file = :tree_id" 702 )->execute([ 703 'tree_id' => $this->tree->id(), 704 ])->fetchOne(); 705 706 return I18N::number($average, 2); 707 } 708 709 /** 710 * General query on families. 711 * 712 * @param int $total 713 * 714 * @return array 715 */ 716 private function topTenFamilyQuery(int $total): array 717 { 718 $rows = $this->runSql( 719 "SELECT f_numchil AS tot, f_id AS id" . 720 " FROM `##families`" . 721 " WHERE" . 722 " f_file={$this->tree->id()}" . 723 " ORDER BY tot DESC" . 724 " LIMIT " . $total 725 ); 726 727 if (empty($rows)) { 728 return []; 729 } 730 731 $top10 = []; 732 foreach ($rows as $row) { 733 $family = Family::getInstance($row->id, $this->tree); 734 735 if ($family && $family->canShow()) { 736 $top10[] = [ 737 'family' => $family, 738 'count' => (int) $row->tot, 739 ]; 740 } 741 } 742 743 // TODO 744 // if (I18N::direction() === 'rtl') { 745 // $top10 = str_replace([ 746 // '[', 747 // ']', 748 // '(', 749 // ')', 750 // '+', 751 // ], [ 752 // '‏[', 753 // '‏]', 754 // '‏(', 755 // '‏)', 756 // '‏+', 757 // ], $top10); 758 // } 759 760 return $top10; 761 } 762 763 /** 764 * The the families with the most children. 765 * 766 * @param int $total 767 * 768 * @return string 769 */ 770 public function topTenLargestFamily(int $total = 10): string 771 { 772 $records = $this->topTenFamilyQuery($total); 773 774 return view( 775 'statistics/families/top10-nolist', 776 [ 777 'records' => $records, 778 ] 779 ); 780 } 781 782 /** 783 * Find the families with the most children. 784 * 785 * @param int $total 786 * 787 * @return string 788 */ 789 public function topTenLargestFamilyList(int $total = 10): string 790 { 791 $records = $this->topTenFamilyQuery($total); 792 793 return view( 794 'statistics/families/top10-list', 795 [ 796 'records' => $records, 797 ] 798 ); 799 } 800 801 /** 802 * Create a chart of the largest families. 803 * 804 * @param string|null $size 805 * @param string|null $color_from 806 * @param string|null $color_to 807 * @param int $total 808 * 809 * @return string 810 */ 811 public function chartLargestFamilies( 812 string $size = null, 813 string $color_from = null, 814 string $color_to = null, 815 int $total = 10 816 ): string 817 { 818 return (new ChartFamilyLargest($this->tree)) 819 ->chartLargestFamilies($size, $color_from, $color_to, $total); 820 } 821 822 /** 823 * Find the month in the year of the birth of the first child. 824 * 825 * @param bool $sex 826 * 827 * @return stdClass[] 828 */ 829 public function monthFirstChildQuery(bool $sex = false): array 830 { 831 if ($sex) { 832 $sql_sex1 = ', i_sex'; 833 $sql_sex2 = " JOIN `##individuals` AS child ON child1.d_file = i_file AND child1.d_gid = child.i_id "; 834 } else { 835 $sql_sex1 = ''; 836 $sql_sex2 = ''; 837 } 838 839 $sql = 840 "SELECT d_month{$sql_sex1}, COUNT(*) AS total " . 841 "FROM (" . 842 " SELECT family{$sql_sex1}, MIN(date) AS d_date, d_month" . 843 " FROM (" . 844 " SELECT" . 845 " link1.l_from AS family," . 846 " link1.l_to AS child," . 847 " child1.d_julianday2 AS date," . 848 " child1.d_month as d_month" . 849 $sql_sex1 . 850 " FROM `##link` AS link1" . 851 " LEFT JOIN `##dates` AS child1 ON child1.d_file = {$this->tree->id()}" . 852 $sql_sex2 . 853 " WHERE" . 854 " link1.l_file = {$this->tree->id()} AND" . 855 " link1.l_type = 'CHIL' AND" . 856 " child1.d_gid = link1.l_to AND" . 857 " child1.d_fact = 'BIRT' AND" . 858 " child1.d_month IN ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC')" . 859 " ORDER BY date" . 860 " ) AS children" . 861 " GROUP BY family, d_month{$sql_sex1}" . 862 ") AS first_child " . 863 "GROUP BY d_month"; 864 865 if ($sex) { 866 $sql .= ', i_sex'; 867 } 868 869 return $this->runSql($sql); 870 } 871 872 /** 873 * Number of husbands. 874 * 875 * @return string 876 */ 877 public function totalMarriedMales(): string 878 { 879 $n = (int) Database::prepare( 880 "SELECT COUNT(DISTINCT f_husb) FROM `##families` WHERE f_file = :tree_id AND f_gedcom LIKE '%\\n1 MARR%'" 881 )->execute([ 882 'tree_id' => $this->tree->id(), 883 ])->fetchOne(); 884 885 return I18N::number($n); 886 } 887 888 /** 889 * Number of wives. 890 * 891 * @return string 892 */ 893 public function totalMarriedFemales(): string 894 { 895 $n = (int) Database::prepare( 896 "SELECT COUNT(DISTINCT f_wife) FROM `##families` WHERE f_file = :tree_id AND f_gedcom LIKE '%\\n1 MARR%'" 897 )->execute([ 898 'tree_id' => $this->tree->id(), 899 ])->fetchOne(); 900 901 return I18N::number($n); 902 } 903 904 /** 905 * General query on parents. 906 * 907 * @param string $type 908 * @param string $age_dir 909 * @param string $sex 910 * @param bool $show_years 911 * 912 * @return string 913 */ 914 private function parentsQuery(string $type, string $age_dir, string $sex, bool $show_years): string 915 { 916 if ($sex === 'F') { 917 $sex_field = 'WIFE'; 918 } else { 919 $sex_field = 'HUSB'; 920 } 921 922 if ($age_dir !== 'ASC') { 923 $age_dir = 'DESC'; 924 } 925 926 $rows = $this->runSql( 927 " SELECT" . 928 " parentfamily.l_to AS id," . 929 " childbirth.d_julianday2-birth.d_julianday1 AS age" . 930 " FROM `##link` AS parentfamily" . 931 " JOIN `##link` AS childfamily ON childfamily.l_file = {$this->tree->id()}" . 932 " JOIN `##dates` AS birth ON birth.d_file = {$this->tree->id()}" . 933 " JOIN `##dates` AS childbirth ON childbirth.d_file = {$this->tree->id()}" . 934 " WHERE" . 935 " birth.d_gid = parentfamily.l_to AND" . 936 " childfamily.l_to = childbirth.d_gid AND" . 937 " childfamily.l_type = 'CHIL' AND" . 938 " parentfamily.l_type = '{$sex_field}' AND" . 939 " childfamily.l_from = parentfamily.l_from AND" . 940 " parentfamily.l_file = {$this->tree->id()} AND" . 941 " birth.d_fact = 'BIRT' AND" . 942 " childbirth.d_fact = 'BIRT' AND" . 943 " birth.d_julianday1 <> 0 AND" . 944 " childbirth.d_julianday2 > birth.d_julianday1" . 945 " ORDER BY age {$age_dir} LIMIT 1" 946 ); 947 948 if (!isset($rows[0])) { 949 return ''; 950 } 951 952 $row = $rows[0]; 953 if (isset($row->id)) { 954 $person = Individual::getInstance($row->id, $this->tree); 955 } 956 957 switch ($type) { 958 default: 959 case 'full': 960 if ($person && $person->canShow()) { 961 $result = $person->formatList(); 962 } else { 963 $result = I18N::translate('This information is private and cannot be shown.'); 964 } 965 break; 966 967 case 'name': 968 $result = '<a href="' . e($person->url()) . '">' . $person->getFullName() . '</a>'; 969 break; 970 971 case 'age': 972 $age = $row->age; 973 974 if ($show_years) { 975 $result = $this->calculateAge((int) $row->age); 976 } else { 977 $result = (string) floor($age / 365.25); 978 } 979 980 break; 981 } 982 983 return $result; 984 } 985 986 /** 987 * Find the youngest mother 988 * 989 * @return string 990 */ 991 public function youngestMother(): string 992 { 993 return $this->parentsQuery('full', 'ASC', 'F', false); 994 } 995 996 /** 997 * Find the name of the youngest mother. 998 * 999 * @return string 1000 */ 1001 public function youngestMotherName(): string 1002 { 1003 return $this->parentsQuery('name', 'ASC', 'F', false); 1004 } 1005 1006 /** 1007 * Find the age of the youngest mother. 1008 * 1009 * @param string $show_years 1010 * 1011 * @return string 1012 */ 1013 public function youngestMotherAge(string $show_years = ''): string 1014 { 1015 return $this->parentsQuery('age', 'ASC', 'F', (bool) $show_years); 1016 } 1017 1018 /** 1019 * Find the oldest mother. 1020 * 1021 * @return string 1022 */ 1023 public function oldestMother(): string 1024 { 1025 return $this->parentsQuery('full', 'DESC', 'F', false); 1026 } 1027 1028 /** 1029 * Find the name of the oldest mother. 1030 * 1031 * @return string 1032 */ 1033 public function oldestMotherName(): string 1034 { 1035 return $this->parentsQuery('name', 'DESC', 'F', false); 1036 } 1037 1038 /** 1039 * Find the age of the oldest mother. 1040 * 1041 * @param string $show_years 1042 * 1043 * @return string 1044 */ 1045 public function oldestMotherAge(string $show_years = ''): string 1046 { 1047 return $this->parentsQuery('age', 'DESC', 'F', (bool) $show_years); 1048 } 1049 1050 /** 1051 * Find the youngest father. 1052 * 1053 * @return string 1054 */ 1055 public function youngestFather(): string 1056 { 1057 return $this->parentsQuery('full', 'ASC', 'M', false); 1058 } 1059 1060 /** 1061 * Find the name of the youngest father. 1062 * 1063 * @return string 1064 */ 1065 public function youngestFatherName(): string 1066 { 1067 return $this->parentsQuery('name', 'ASC', 'M', false); 1068 } 1069 1070 /** 1071 * Find the age of the youngest father. 1072 * 1073 * @param string $show_years 1074 * 1075 * @return string 1076 */ 1077 public function youngestFatherAge(string $show_years = ''): string 1078 { 1079 return $this->parentsQuery('age', 'ASC', 'M', (bool) $show_years); 1080 } 1081 1082 /** 1083 * Find the oldest father. 1084 * 1085 * @return string 1086 */ 1087 public function oldestFather(): string 1088 { 1089 return $this->parentsQuery('full', 'DESC', 'M', false); 1090 } 1091 1092 /** 1093 * Find the name of the oldest father. 1094 * 1095 * @return string 1096 */ 1097 public function oldestFatherName(): string 1098 { 1099 return $this->parentsQuery('name', 'DESC', 'M', false); 1100 } 1101 1102 /** 1103 * Find the age of the oldest father. 1104 * 1105 * @param string $show_years 1106 * 1107 * @return string 1108 */ 1109 public function oldestFatherAge(string $show_years = ''): string 1110 { 1111 return $this->parentsQuery('age', 'DESC', 'M', (bool) $show_years); 1112 } 1113 1114 /** 1115 * General query on age at marriage. 1116 * 1117 * @param string $type 1118 * @param string $age_dir 1119 * @param int $total 1120 * 1121 * @return string 1122 */ 1123 private function ageOfMarriageQuery(string $type, string $age_dir, int $total): string 1124 { 1125 if ($age_dir !== 'ASC') { 1126 $age_dir = 'DESC'; 1127 } 1128 1129 $hrows = $this->runSql( 1130 " SELECT DISTINCT fam.f_id AS family, MIN(husbdeath.d_julianday2-married.d_julianday1) AS age" . 1131 " FROM `##families` AS fam" . 1132 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1133 " LEFT JOIN `##dates` AS husbdeath ON husbdeath.d_file = {$this->tree->id()}" . 1134 " WHERE" . 1135 " fam.f_file = {$this->tree->id()} AND" . 1136 " husbdeath.d_gid = fam.f_husb AND" . 1137 " husbdeath.d_fact = 'DEAT' AND" . 1138 " married.d_gid = fam.f_id AND" . 1139 " married.d_fact = 'MARR' AND" . 1140 " married.d_julianday1 < husbdeath.d_julianday2 AND" . 1141 " married.d_julianday1 <> 0" . 1142 " GROUP BY family" . 1143 " ORDER BY age {$age_dir}" 1144 ); 1145 1146 $wrows = $this->runSql( 1147 " SELECT DISTINCT fam.f_id AS family, MIN(wifedeath.d_julianday2-married.d_julianday1) AS age" . 1148 " FROM `##families` AS fam" . 1149 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1150 " LEFT JOIN `##dates` AS wifedeath ON wifedeath.d_file = {$this->tree->id()}" . 1151 " WHERE" . 1152 " fam.f_file = {$this->tree->id()} AND" . 1153 " wifedeath.d_gid = fam.f_wife AND" . 1154 " wifedeath.d_fact = 'DEAT' AND" . 1155 " married.d_gid = fam.f_id AND" . 1156 " married.d_fact = 'MARR' AND" . 1157 " married.d_julianday1 < wifedeath.d_julianday2 AND" . 1158 " married.d_julianday1 <> 0" . 1159 " GROUP BY family" . 1160 " ORDER BY age {$age_dir}" 1161 ); 1162 1163 $drows = $this->runSql( 1164 " SELECT DISTINCT fam.f_id AS family, MIN(divorced.d_julianday2-married.d_julianday1) AS age" . 1165 " FROM `##families` AS fam" . 1166 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1167 " LEFT JOIN `##dates` AS divorced ON divorced.d_file = {$this->tree->id()}" . 1168 " WHERE" . 1169 " fam.f_file = {$this->tree->id()} AND" . 1170 " married.d_gid = fam.f_id AND" . 1171 " married.d_fact = 'MARR' AND" . 1172 " divorced.d_gid = fam.f_id AND" . 1173 " divorced.d_fact IN ('DIV', 'ANUL', '_SEPR', '_DETS') AND" . 1174 " married.d_julianday1 < divorced.d_julianday2 AND" . 1175 " married.d_julianday1 <> 0" . 1176 " GROUP BY family" . 1177 " ORDER BY age {$age_dir}" 1178 ); 1179 1180 $rows = []; 1181 foreach ($drows as $family) { 1182 $rows[$family->family] = $family->age; 1183 } 1184 1185 foreach ($hrows as $family) { 1186 if (!isset($rows[$family->family])) { 1187 $rows[$family->family] = $family->age; 1188 } 1189 } 1190 1191 foreach ($wrows as $family) { 1192 if (!isset($rows[$family->family])) { 1193 $rows[$family->family] = $family->age; 1194 } elseif ($rows[$family->family] > $family->age) { 1195 $rows[$family->family] = $family->age; 1196 } 1197 } 1198 1199 if ($age_dir === 'DESC') { 1200 arsort($rows); 1201 } else { 1202 asort($rows); 1203 } 1204 1205 $top10 = []; 1206 $i = 0; 1207 foreach ($rows as $fam => $age) { 1208 $family = Family::getInstance($fam, $this->tree); 1209 if ($type === 'name') { 1210 return $family->formatList(); 1211 } 1212 1213 $age = $this->calculateAge((int) $age); 1214 1215 if ($type === 'age') { 1216 return $age; 1217 } 1218 1219 $husb = $family->getHusband(); 1220 $wife = $family->getWife(); 1221 1222 if (($husb && ($husb->getAllDeathDates() || !$husb->isDead())) 1223 && ($wife && ($wife->getAllDeathDates() || !$wife->isDead())) 1224 ) { 1225 if ($family && $family->canShow()) { 1226 if ($type === 'list') { 1227 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->getFullName() . '</a> (' . $age . ')' . '</li>'; 1228 } else { 1229 $top10[] = '<a href="' . e($family->url()) . '">' . $family->getFullName() . '</a> (' . $age . ')'; 1230 } 1231 } 1232 if (++$i === $total) { 1233 break; 1234 } 1235 } 1236 } 1237 1238 if ($type === 'list') { 1239 $top10 = implode('', $top10); 1240 } else { 1241 $top10 = implode('; ', $top10); 1242 } 1243 1244 if (I18N::direction() === 'rtl') { 1245 $top10 = str_replace([ 1246 '[', 1247 ']', 1248 '(', 1249 ')', 1250 '+', 1251 ], [ 1252 '‏[', 1253 '‏]', 1254 '‏(', 1255 '‏)', 1256 '‏+', 1257 ], $top10); 1258 } 1259 1260 if ($type === 'list') { 1261 return '<ul>' . $top10 . '</ul>'; 1262 } 1263 1264 return $top10; 1265 } 1266 1267 /** 1268 * General query on marriage ages. 1269 * 1270 * @return string 1271 */ 1272 public function topAgeOfMarriageFamily(): string 1273 { 1274 return $this->ageOfMarriageQuery('name', 'DESC', 1); 1275 } 1276 1277 /** 1278 * General query on marriage ages. 1279 * 1280 * @return string 1281 */ 1282 public function topAgeOfMarriage(): string 1283 { 1284 return $this->ageOfMarriageQuery('age', 'DESC', 1); 1285 } 1286 1287 /** 1288 * General query on marriage ages. 1289 * 1290 * @param int $total 1291 * 1292 * @return string 1293 */ 1294 public function topAgeOfMarriageFamilies(int $total = 10): string 1295 { 1296 return $this->ageOfMarriageQuery('nolist', 'DESC', $total); 1297 } 1298 1299 /** 1300 * General query on marriage ages. 1301 * 1302 * @param int $total 1303 * 1304 * @return string 1305 */ 1306 public function topAgeOfMarriageFamiliesList(int $total = 10): string 1307 { 1308 return $this->ageOfMarriageQuery('list', 'DESC', $total); 1309 } 1310 1311 /** 1312 * General query on marriage ages. 1313 * 1314 * @return string 1315 */ 1316 public function minAgeOfMarriageFamily(): string 1317 { 1318 return $this->ageOfMarriageQuery('name', 'ASC', 1); 1319 } 1320 1321 /** 1322 * General query on marriage ages. 1323 * 1324 * @return string 1325 */ 1326 public function minAgeOfMarriage(): string 1327 { 1328 return $this->ageOfMarriageQuery('age', 'ASC', 1); 1329 } 1330 1331 /** 1332 * General query on marriage ages. 1333 * 1334 * @param int $total 1335 * 1336 * @return string 1337 */ 1338 public function minAgeOfMarriageFamilies(int $total = 10): string 1339 { 1340 return $this->ageOfMarriageQuery('nolist', 'ASC', $total); 1341 } 1342 1343 /** 1344 * General query on marriage ages. 1345 * 1346 * @param int $total 1347 * 1348 * @return string 1349 */ 1350 public function minAgeOfMarriageFamiliesList(int $total = 10): string 1351 { 1352 return $this->ageOfMarriageQuery('list', 'ASC', $total); 1353 } 1354 1355 /** 1356 * Find the ages between spouses. 1357 * 1358 * @param string $age_dir 1359 * @param int $total 1360 * 1361 * @return array 1362 */ 1363 private function ageBetweenSpousesQuery(string $age_dir, int $total): array 1364 { 1365 if ($age_dir === 'DESC') { 1366 $sql = 1367 "SELECT f_id AS xref, MIN(wife.d_julianday2-husb.d_julianday1) AS age" . 1368 " FROM `##families`" . 1369 " JOIN `##dates` AS wife ON wife.d_gid = f_wife AND wife.d_file = f_file" . 1370 " JOIN `##dates` AS husb ON husb.d_gid = f_husb AND husb.d_file = f_file" . 1371 " WHERE f_file = :tree_id" . 1372 " AND husb.d_fact = 'BIRT'" . 1373 " AND wife.d_fact = 'BIRT'" . 1374 " AND wife.d_julianday2 >= husb.d_julianday1 AND husb.d_julianday1 <> 0" . 1375 " GROUP BY xref" . 1376 " ORDER BY age DESC" . 1377 " LIMIT :limit"; 1378 } else { 1379 $sql = 1380 "SELECT f_id AS xref, MIN(husb.d_julianday2-wife.d_julianday1) AS age" . 1381 " FROM `##families`" . 1382 " JOIN `##dates` AS wife ON wife.d_gid = f_wife AND wife.d_file = f_file" . 1383 " JOIN `##dates` AS husb ON husb.d_gid = f_husb AND husb.d_file = f_file" . 1384 " WHERE f_file = :tree_id" . 1385 " AND husb.d_fact = 'BIRT'" . 1386 " AND wife.d_fact = 'BIRT'" . 1387 " AND husb.d_julianday2 >= wife.d_julianday1 AND wife.d_julianday1 <> 0" . 1388 " GROUP BY xref" . 1389 " ORDER BY age DESC" . 1390 " LIMIT :limit"; 1391 } 1392 1393 $rows = Database::prepare( 1394 $sql 1395 )->execute([ 1396 'tree_id' => $this->tree->id(), 1397 'limit' => $total, 1398 ])->fetchAll(); 1399 1400 $top10 = []; 1401 1402 foreach ($rows as $fam) { 1403 $family = Family::getInstance($fam->xref, $this->tree); 1404 1405 if ($fam->age < 0) { 1406 break; 1407 } 1408 1409 if ($family->canShow()) { 1410 $top10[] = [ 1411 'family' => $family, 1412 'age' => $this->calculateAge((int) $fam->age), 1413 ]; 1414 } 1415 } 1416 1417 return $top10; 1418 } 1419 1420 /** 1421 * Find the age between husband and wife. 1422 * 1423 * @param int $total 1424 * 1425 * @return string 1426 */ 1427 public function ageBetweenSpousesMF(int $total = 10): string 1428 { 1429 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1430 1431 return view( 1432 'statistics/families/top10-nolist-spouses', 1433 [ 1434 'records' => $records, 1435 ] 1436 ); 1437 } 1438 1439 /** 1440 * Find the age between husband and wife. 1441 * 1442 * @param int $total 1443 * 1444 * @return string 1445 */ 1446 public function ageBetweenSpousesMFList(int $total = 10): string 1447 { 1448 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1449 1450 return view( 1451 'statistics/families/top10-list-spouses', 1452 [ 1453 'records' => $records, 1454 ] 1455 ); 1456 } 1457 1458 /** 1459 * Find the age between wife and husband.. 1460 * 1461 * @param int $total 1462 * 1463 * @return string 1464 */ 1465 public function ageBetweenSpousesFM(int $total = 10): string 1466 { 1467 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1468 1469 return view( 1470 'statistics/families/top10-nolist-spouses', 1471 [ 1472 'records' => $records, 1473 ] 1474 ); 1475 } 1476 1477 /** 1478 * Find the age between wife and husband.. 1479 * 1480 * @param int $total 1481 * 1482 * @return string 1483 */ 1484 public function ageBetweenSpousesFMList(int $total = 10): string 1485 { 1486 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1487 1488 return view( 1489 'statistics/families/top10-list-spouses', 1490 [ 1491 'records' => $records, 1492 ] 1493 ); 1494 } 1495 1496 /** 1497 * General query on ages at marriage. 1498 * 1499 * @param string $sex 1500 * @param int $year1 1501 * @param int $year2 1502 * 1503 * @return array 1504 */ 1505 public function statsMarrAgeQuery($sex = 'M', $year1 = -1, $year2 = -1): array 1506 { 1507 if ($year1 >= 0 && $year2 >= 0) { 1508 $years = " married.d_year BETWEEN {$year1} AND {$year2} AND "; 1509 } else { 1510 $years = ''; 1511 } 1512 1513 $rows = $this->runSql( 1514 "SELECT " . 1515 " fam.f_id, " . 1516 " birth.d_gid, " . 1517 " married.d_julianday2-birth.d_julianday1 AS age " . 1518 "FROM `##dates` AS married " . 1519 "JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) " . 1520 "JOIN `##dates` AS birth ON (birth.d_gid=fam.f_husb AND birth.d_file=fam.f_file) " . 1521 "WHERE " . 1522 " '{$sex}' IN ('M', 'BOTH') AND {$years} " . 1523 " married.d_file={$this->tree->id()} AND married.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND married.d_fact='MARR' AND " . 1524 " birth.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND birth.d_fact='BIRT' AND " . 1525 " married.d_julianday1>birth.d_julianday1 AND birth.d_julianday1<>0 " . 1526 "UNION ALL " . 1527 "SELECT " . 1528 " fam.f_id, " . 1529 " birth.d_gid, " . 1530 " married.d_julianday2-birth.d_julianday1 AS age " . 1531 "FROM `##dates` AS married " . 1532 "JOIN `##families` AS fam ON (married.d_gid=fam.f_id AND married.d_file=fam.f_file) " . 1533 "JOIN `##dates` AS birth ON (birth.d_gid=fam.f_wife AND birth.d_file=fam.f_file) " . 1534 "WHERE " . 1535 " '{$sex}' IN ('F', 'BOTH') AND {$years} " . 1536 " married.d_file={$this->tree->id()} AND married.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND married.d_fact='MARR' AND " . 1537 " birth.d_type IN ('@#DGREGORIAN@', '@#DJULIAN@') AND birth.d_fact='BIRT' AND " . 1538 " married.d_julianday1>birth.d_julianday1 AND birth.d_julianday1<>0 " 1539 ); 1540 1541 foreach ($rows as $row) { 1542 $row->age = (int) $row->age; 1543 } 1544 1545 return $rows; 1546 } 1547 1548 /** 1549 * General query on marriage ages. 1550 * 1551 * @param string $size 1552 * 1553 * @return string 1554 */ 1555 public function statsMarrAge(string $size = '200x250'): string 1556 { 1557 return (new ChartMarriageAge($this->tree)) 1558 ->chartMarriageAge($size); 1559 } 1560 1561 /** 1562 * Query the database for marriage tags. 1563 * 1564 * @param string $type 1565 * @param string $age_dir 1566 * @param string $sex 1567 * @param bool $show_years 1568 * 1569 * @return string 1570 */ 1571 private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string 1572 { 1573 if ($sex === 'F') { 1574 $sex_field = 'f_wife'; 1575 } else { 1576 $sex_field = 'f_husb'; 1577 } 1578 1579 if ($age_dir !== 'ASC') { 1580 $age_dir = 'DESC'; 1581 } 1582 1583 $rows = $this->runSql( 1584 " SELECT fam.f_id AS famid, fam.{$sex_field}, married.d_julianday2-birth.d_julianday1 AS age, indi.i_id AS i_id" . 1585 " FROM `##families` AS fam" . 1586 " LEFT JOIN `##dates` AS birth ON birth.d_file = {$this->tree->id()}" . 1587 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1588 " LEFT JOIN `##individuals` AS indi ON indi.i_file = {$this->tree->id()}" . 1589 " WHERE" . 1590 " birth.d_gid = indi.i_id AND" . 1591 " married.d_gid = fam.f_id AND" . 1592 " indi.i_id = fam.{$sex_field} AND" . 1593 " fam.f_file = {$this->tree->id()} AND" . 1594 " birth.d_fact = 'BIRT' AND" . 1595 " married.d_fact = 'MARR' AND" . 1596 " birth.d_julianday1 <> 0 AND" . 1597 " married.d_julianday2 > birth.d_julianday1 AND" . 1598 " i_sex='{$sex}'" . 1599 " ORDER BY" . 1600 " married.d_julianday2-birth.d_julianday1 {$age_dir} LIMIT 1" 1601 ); 1602 1603 if (!isset($rows[0])) { 1604 return ''; 1605 } 1606 1607 $row = $rows[0]; 1608 if (isset($row->famid)) { 1609 $family = Family::getInstance($row->famid, $this->tree); 1610 } 1611 1612 if (isset($row->i_id)) { 1613 $person = Individual::getInstance($row->i_id, $this->tree); 1614 } 1615 1616 switch ($type) { 1617 default: 1618 case 'full': 1619 if ($family && $family->canShow()) { 1620 $result = $family->formatList(); 1621 } else { 1622 $result = I18N::translate('This information is private and cannot be shown.'); 1623 } 1624 break; 1625 1626 case 'name': 1627 $result = '<a href="' . e($family->url()) . '">' . $person->getFullName() . '</a>'; 1628 break; 1629 1630 case 'age': 1631 $age = $row->age; 1632 1633 if ($show_years) { 1634 $result = $this->calculateAge((int) $row->age); 1635 } else { 1636 $result = I18N::number((int) ($age / 365.25)); 1637 } 1638 1639 break; 1640 } 1641 1642 return $result; 1643 } 1644 1645 /** 1646 * Find the youngest wife. 1647 * 1648 * @return string 1649 */ 1650 public function youngestMarriageFemale(): string 1651 { 1652 return $this->marriageQuery('full', 'ASC', 'F', false); 1653 } 1654 1655 /** 1656 * Find the name of the youngest wife. 1657 * 1658 * @return string 1659 */ 1660 public function youngestMarriageFemaleName(): string 1661 { 1662 return $this->marriageQuery('name', 'ASC', 'F', false); 1663 } 1664 1665 /** 1666 * Find the age of the youngest wife. 1667 * 1668 * @param string $show_years 1669 * 1670 * @return string 1671 */ 1672 public function youngestMarriageFemaleAge(string $show_years = ''): string 1673 { 1674 return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years); 1675 } 1676 1677 /** 1678 * Find the oldest wife. 1679 * 1680 * @return string 1681 */ 1682 public function oldestMarriageFemale(): string 1683 { 1684 return $this->marriageQuery('full', 'DESC', 'F', false); 1685 } 1686 1687 /** 1688 * Find the name of the oldest wife. 1689 * 1690 * @return string 1691 */ 1692 public function oldestMarriageFemaleName(): string 1693 { 1694 return $this->marriageQuery('name', 'DESC', 'F', false); 1695 } 1696 1697 /** 1698 * Find the age of the oldest wife. 1699 * 1700 * @param string $show_years 1701 * 1702 * @return string 1703 */ 1704 public function oldestMarriageFemaleAge(string $show_years = ''): string 1705 { 1706 return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years); 1707 } 1708 1709 /** 1710 * Find the youngest husband. 1711 * 1712 * @return string 1713 */ 1714 public function youngestMarriageMale(): string 1715 { 1716 return $this->marriageQuery('full', 'ASC', 'M', false); 1717 } 1718 1719 /** 1720 * Find the name of the youngest husband. 1721 * 1722 * @return string 1723 */ 1724 public function youngestMarriageMaleName(): string 1725 { 1726 return $this->marriageQuery('name', 'ASC', 'M', false); 1727 } 1728 1729 /** 1730 * Find the age of the youngest husband. 1731 * 1732 * @param string $show_years 1733 * 1734 * @return string 1735 */ 1736 public function youngestMarriageMaleAge(string $show_years = ''): string 1737 { 1738 return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years); 1739 } 1740 1741 /** 1742 * Find the oldest husband. 1743 * 1744 * @return string 1745 */ 1746 public function oldestMarriageMale(): string 1747 { 1748 return $this->marriageQuery('full', 'DESC', 'M', false); 1749 } 1750 1751 /** 1752 * Find the name of the oldest husband. 1753 * 1754 * @return string 1755 */ 1756 public function oldestMarriageMaleName(): string 1757 { 1758 return $this->marriageQuery('name', 'DESC', 'M', false); 1759 } 1760 1761 /** 1762 * Find the age of the oldest husband. 1763 * 1764 * @param string $show_years 1765 * 1766 * @return string 1767 */ 1768 public function oldestMarriageMaleAge(string $show_years = ''): string 1769 { 1770 return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years); 1771 } 1772 1773 /** 1774 * General query on marriages. 1775 * 1776 * @param bool $first 1777 * @param int $year1 1778 * @param int $year2 1779 * 1780 * @return array 1781 */ 1782 public function statsMarrQuery(bool $first = false, int $year1 = -1, int $year2 = -1): array 1783 { 1784 if ($first) { 1785 $years = ''; 1786 1787 if ($year1 >= 0 && $year2 >= 0) { 1788 $years = " married.d_year BETWEEN '{$year1}' AND '{$year2}' AND"; 1789 } 1790 1791 $sql = 1792 " 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" . 1793 " FROM `##families` AS fam" . 1794 " LEFT JOIN `##dates` AS married ON married.d_file = {$this->tree->id()}" . 1795 " LEFT JOIN `##individuals` AS indi ON indi.i_file = {$this->tree->id()}" . 1796 " WHERE" . 1797 " married.d_gid = fam.f_id AND" . 1798 " fam.f_file = {$this->tree->id()} AND" . 1799 " married.d_fact = 'MARR' AND" . 1800 " married.d_julianday2 <> 0 AND" . 1801 $years . 1802 " (indi.i_id = fam.f_husb OR indi.i_id = fam.f_wife)" . 1803 " ORDER BY fams, indi, age ASC"; 1804 } else { 1805 $sql = 1806 "SELECT d_month, COUNT(*) AS total" . 1807 " FROM `##dates`" . 1808 " WHERE d_file={$this->tree->id()} AND d_fact='MARR'"; 1809 1810 if ($year1 >= 0 && $year2 >= 0) { 1811 $sql .= " AND d_year BETWEEN '{$year1}' AND '{$year2}'"; 1812 } 1813 1814 $sql .= " GROUP BY d_month"; 1815 } 1816 1817 return $this->runSql($sql); 1818 } 1819 1820 /** 1821 * General query on marriages. 1822 * 1823 * @param string|null $size 1824 * @param string|null $color_from 1825 * @param string|null $color_to 1826 * 1827 * @return string 1828 */ 1829 public function statsMarr(string $size = null, string $color_from = null, string $color_to = null): string 1830 { 1831 return (new ChartMarriage($this->tree)) 1832 ->chartMarriage($size, $color_from, $color_to); 1833 } 1834 1835 /** 1836 * General divorce query. 1837 * 1838 * @param string|null $size 1839 * @param string|null $color_from 1840 * @param string|null $color_to 1841 * 1842 * @return string 1843 */ 1844 public function statsDiv(string $size = null, string $color_from = null, string $color_to = null): string 1845 { 1846 return (new ChartDivorce($this->tree)) 1847 ->chartDivorce($size, $color_from, $color_to); 1848 } 1849} 1850