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