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