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\Auth; 21use Fisharebest\Webtrees\Database; 22use Fisharebest\Webtrees\Functions\FunctionsDate; 23use Fisharebest\Webtrees\Functions\FunctionsPrintLists; 24use Fisharebest\Webtrees\Gedcom; 25use Fisharebest\Webtrees\I18N; 26use Fisharebest\Webtrees\Individual; 27use Fisharebest\Webtrees\Statistics\Google\ChartAge; 28use Fisharebest\Webtrees\Statistics\Google\ChartBirth; 29use Fisharebest\Webtrees\Statistics\Google\ChartCommonGiven; 30use Fisharebest\Webtrees\Statistics\Google\ChartCommonSurname; 31use Fisharebest\Webtrees\Statistics\Google\ChartDeath; 32use Fisharebest\Webtrees\Statistics\Google\ChartFamilyWithSources; 33use Fisharebest\Webtrees\Statistics\Google\ChartIndividual; 34use Fisharebest\Webtrees\Statistics\Google\ChartMortality; 35use Fisharebest\Webtrees\Statistics\Google\ChartSex; 36use Fisharebest\Webtrees\Statistics\Helper\Sql; 37use Fisharebest\Webtrees\Statistics\Repository\Interfaces\IndividualRepositoryInterface; 38use Fisharebest\Webtrees\Tree; 39use Illuminate\Database\Capsule\Manager as DB; 40use Illuminate\Database\Query\Builder; 41use Illuminate\Database\Query\JoinClause; 42 43/** 44 * 45 */ 46class IndividualRepository implements IndividualRepositoryInterface 47{ 48 /** 49 * @var Tree 50 */ 51 private $tree; 52 53 /** 54 * Constructor. 55 * 56 * @param Tree $tree 57 */ 58 public function __construct(Tree $tree) 59 { 60 $this->tree = $tree; 61 } 62 63 /** 64 * Run an SQL query and cache the result. 65 * 66 * @param string $sql 67 * 68 * @return \stdClass[] 69 */ 70 private function runSql(string $sql): array 71 { 72 return Sql::runSql($sql); 73 } 74 75 /** 76 * Find common given names. 77 * 78 * @param string $sex 79 * @param string $type 80 * @param bool $show_tot 81 * @param int $threshold 82 * @param int $maxtoshow 83 * 84 * @return string|int[] 85 */ 86 private function commonGivenQuery(string $sex, string $type, bool $show_tot, int $threshold, int $maxtoshow) 87 { 88 switch ($sex) { 89 case 'M': 90 $sex_sql = "i_sex='M'"; 91 break; 92 case 'F': 93 $sex_sql = "i_sex='F'"; 94 break; 95 case 'U': 96 $sex_sql = "i_sex='U'"; 97 break; 98 case 'B': 99 default: 100 $sex_sql = "i_sex<>'U'"; 101 break; 102 } 103 104 $ged_id = $this->tree->id(); 105 106 $rows = Database::prepare("SELECT n_givn, COUNT(*) AS num FROM `##name` JOIN `##individuals` ON (n_id=i_id AND n_file=i_file) WHERE n_file={$ged_id} AND n_type<>'_MARNM' AND n_givn NOT IN ('@P.N.', '') AND LENGTH(n_givn)>1 AND {$sex_sql} GROUP BY n_id, n_givn") 107 ->fetchAll(); 108 109 $nameList = []; 110 foreach ($rows as $row) { 111 $row->num = (int) $row->num; 112 113 // Split “John Thomas” into “John” and “Thomas” and count against both totals 114 foreach (explode(' ', $row->n_givn) as $given) { 115 // Exclude initials and particles. 116 if (!preg_match('/^([A-Z]|[a-z]{1,3})$/', $given)) { 117 if (\array_key_exists($given, $nameList)) { 118 $nameList[$given] += (int) $row->num; 119 } else { 120 $nameList[$given] = (int) $row->num; 121 } 122 } 123 } 124 } 125 arsort($nameList); 126 $nameList = \array_slice($nameList, 0, $maxtoshow); 127 128 foreach ($nameList as $given => $total) { 129 if ($total < $threshold) { 130 unset($nameList[$given]); 131 } 132 } 133 134 switch ($type) { 135 case 'chart': 136 return $nameList; 137 138 case 'table': 139 return view('lists/given-names-table', [ 140 'given_names' => $nameList, 141 ]); 142 143 case 'list': 144 return view('lists/given-names-list', [ 145 'given_names' => $nameList, 146 'show_totals' => $show_tot, 147 ]); 148 149 case 'nolist': 150 default: 151 array_walk($nameList, function (int &$value, string $key) use ($show_tot): void { 152 if ($show_tot) { 153 $value = '<span dir="auto">' . e($key); 154 } else { 155 $value = '<span dir="auto">' . e($key) . ' (' . I18N::number($value) . ')'; 156 } 157 }); 158 159 return implode(I18N::$list_separator, $nameList); 160 } 161 } 162 163 /** 164 * Find common give names. 165 * 166 * @param int $threshold 167 * @param int $maxtoshow 168 * 169 * @return string 170 */ 171 public function commonGiven(int $threshold = 1, int $maxtoshow = 10): string 172 { 173 return $this->commonGivenQuery('B', 'nolist', false, $threshold, $maxtoshow); 174 } 175 176 /** 177 * Find common give names. 178 * 179 * @param int $threshold 180 * @param int $maxtoshow 181 * 182 * @return string 183 */ 184 public function commonGivenTotals(int $threshold = 1, int $maxtoshow = 10): string 185 { 186 return $this->commonGivenQuery('B', 'nolist', true, $threshold, $maxtoshow); 187 } 188 189 /** 190 * Find common give names. 191 * 192 * @param int $threshold 193 * @param int $maxtoshow 194 * 195 * @return string 196 */ 197 public function commonGivenList(int $threshold = 1, int $maxtoshow = 10): string 198 { 199 return $this->commonGivenQuery('B', 'list', false, $threshold, $maxtoshow); 200 } 201 202 /** 203 * Find common give names. 204 * 205 * @param int $threshold 206 * @param int $maxtoshow 207 * 208 * @return string 209 */ 210 public function commonGivenListTotals(int $threshold = 1, int $maxtoshow = 10): string 211 { 212 return $this->commonGivenQuery('B', 'list', true, $threshold, $maxtoshow); 213 } 214 215 /** 216 * Find common give names. 217 * 218 * @param int $threshold 219 * @param int $maxtoshow 220 * 221 * @return string 222 */ 223 public function commonGivenTable(int $threshold = 1, int $maxtoshow = 10): string 224 { 225 return $this->commonGivenQuery('B', 'table', false, $threshold, $maxtoshow); 226 } 227 228 /** 229 * Find common give names of females. 230 * 231 * @param int $threshold 232 * @param int $maxtoshow 233 * 234 * @return string 235 */ 236 public function commonGivenFemale(int $threshold = 1, int $maxtoshow = 10): string 237 { 238 return $this->commonGivenQuery('F', 'nolist', false, $threshold, $maxtoshow); 239 } 240 241 /** 242 * Find common give names of females. 243 * 244 * @param int $threshold 245 * @param int $maxtoshow 246 * 247 * @return string 248 */ 249 public function commonGivenFemaleTotals(int $threshold = 1, int $maxtoshow = 10): string 250 { 251 return $this->commonGivenQuery('F', 'nolist', true, $threshold, $maxtoshow); 252 } 253 254 /** 255 * Find common give names of females. 256 * 257 * @param int $threshold 258 * @param int $maxtoshow 259 * 260 * @return string 261 */ 262 public function commonGivenFemaleList(int $threshold = 1, int $maxtoshow = 10): string 263 { 264 return $this->commonGivenQuery('F', 'list', false, $threshold, $maxtoshow); 265 } 266 267 /** 268 * Find common give names of females. 269 * 270 * @param int $threshold 271 * @param int $maxtoshow 272 * 273 * @return string 274 */ 275 public function commonGivenFemaleListTotals(int $threshold = 1, int $maxtoshow = 10): string 276 { 277 return $this->commonGivenQuery('F', 'list', true, $threshold, $maxtoshow); 278 } 279 280 /** 281 * Find common give names of females. 282 * 283 * @param int $threshold 284 * @param int $maxtoshow 285 * 286 * @return string 287 */ 288 public function commonGivenFemaleTable(int $threshold = 1, int $maxtoshow = 10): string 289 { 290 return $this->commonGivenQuery('F', 'table', false, $threshold, $maxtoshow); 291 } 292 293 /** 294 * Find common give names of males. 295 * 296 * @param int $threshold 297 * @param int $maxtoshow 298 * 299 * @return string 300 */ 301 public function commonGivenMale(int $threshold = 1, int $maxtoshow = 10): string 302 { 303 return $this->commonGivenQuery('M', 'nolist', false, $threshold, $maxtoshow); 304 } 305 306 /** 307 * Find common give names of males. 308 * 309 * @param int $threshold 310 * @param int $maxtoshow 311 * 312 * @return string 313 */ 314 public function commonGivenMaleTotals(int $threshold = 1, int $maxtoshow = 10): string 315 { 316 return $this->commonGivenQuery('M', 'nolist', true, $threshold, $maxtoshow); 317 } 318 319 /** 320 * Find common give names of males. 321 * 322 * @param int $threshold 323 * @param int $maxtoshow 324 * 325 * @return string 326 */ 327 public function commonGivenMaleList(int $threshold = 1, int $maxtoshow = 10): string 328 { 329 return $this->commonGivenQuery('M', 'list', false, $threshold, $maxtoshow); 330 } 331 332 /** 333 * Find common give names of males. 334 * 335 * @param int $threshold 336 * @param int $maxtoshow 337 * 338 * @return string 339 */ 340 public function commonGivenMaleListTotals(int $threshold = 1, int $maxtoshow = 10): string 341 { 342 return $this->commonGivenQuery('M', 'list', true, $threshold, $maxtoshow); 343 } 344 345 /** 346 * Find common give names of males. 347 * 348 * @param int $threshold 349 * @param int $maxtoshow 350 * 351 * @return string 352 */ 353 public function commonGivenMaleTable(int $threshold = 1, int $maxtoshow = 10): string 354 { 355 return $this->commonGivenQuery('M', 'table', false, $threshold, $maxtoshow); 356 } 357 358 /** 359 * Find common give names of unknown sexes. 360 * 361 * @param int $threshold 362 * @param int $maxtoshow 363 * 364 * @return string 365 */ 366 public function commonGivenUnknown(int $threshold = 1, int $maxtoshow = 10): string 367 { 368 return $this->commonGivenQuery('U', 'nolist', false, $threshold, $maxtoshow); 369 } 370 371 /** 372 * Find common give names of unknown sexes. 373 * 374 * @param int $threshold 375 * @param int $maxtoshow 376 * 377 * @return string 378 */ 379 public function commonGivenUnknownTotals(int $threshold = 1, int $maxtoshow = 10): string 380 { 381 return $this->commonGivenQuery('U', 'nolist', true, $threshold, $maxtoshow); 382 } 383 384 /** 385 * Find common give names of unknown sexes. 386 * 387 * @param int $threshold 388 * @param int $maxtoshow 389 * 390 * @return string 391 */ 392 public function commonGivenUnknownList(int $threshold = 1, int $maxtoshow = 10): string 393 { 394 return $this->commonGivenQuery('U', 'list', false, $threshold, $maxtoshow); 395 } 396 397 /** 398 * Find common give names of unknown sexes. 399 * 400 * @param int $threshold 401 * @param int $maxtoshow 402 * 403 * @return string 404 */ 405 public function commonGivenUnknownListTotals(int $threshold = 1, int $maxtoshow = 10): string 406 { 407 return $this->commonGivenQuery('U', 'list', true, $threshold, $maxtoshow); 408 } 409 410 /** 411 * Find common give names of unknown sexes. 412 * 413 * @param int $threshold 414 * @param int $maxtoshow 415 * 416 * @return string 417 */ 418 public function commonGivenUnknownTable(int $threshold = 1, int $maxtoshow = 10): string 419 { 420 return $this->commonGivenQuery('U', 'table', false, $threshold, $maxtoshow); 421 } 422 423 /** 424 * Count the number of distinct given names (or the number of occurences of specific given names). 425 * 426 * @param string[] ...$params 427 * 428 * @return string 429 */ 430 public function totalGivennames(...$params): string 431 { 432 $query = DB::table('name') 433 ->where('n_file', '=', $this->tree->id()); 434 435 if (empty($params)) { 436 // Count number of distinct surnames 437 $query 438 ->whereNotIn('n_givn', ['', '@N.N.']) 439 ->groupBy('n_givn'); 440 } else { 441 // Count number of occurences of specific surnames. 442 $query->whereIn('n_givn', $params); 443 } 444 445 $count = $query->count(); 446 447 return I18N::number($count); 448 } 449 450 /** 451 * Count the number of distinct surnames (or the number of occurences of specific surnmaes). 452 * 453 * @param string[] ...$params 454 * 455 * @return string 456 */ 457 public function totalSurnames(...$params): string 458 { 459 $query = DB::table('name') 460 ->where('n_file', '=', $this->tree->id()); 461 462 if (empty($params)) { 463 // Count number of distinct surnames 464 $query 465 ->whereNotIn('n_surn', ['', '@N.N.']) 466 ->groupBy('n_surn'); 467 } else { 468 // Count number of occurences of specific surnames. 469 $query->whereIn('n_surn', $params); 470 } 471 472 $count = $query->count(); 473 474 return I18N::number($count); 475 } 476 477 /** 478 * @param int $number_of_surnames 479 * @param int $threshold 480 * 481 * @return \stdClass[] 482 */ 483 private function topSurnames(int $number_of_surnames, int $threshold): array 484 { 485 // Use the count of base surnames. 486 $top_surnames = Database::prepare( 487 "SELECT n_surn FROM `##name`" . 488 " WHERE n_file = :tree_id AND n_type != '_MARNM' AND n_surn NOT IN ('@N.N.', '')" . 489 " GROUP BY n_surn" . 490 " ORDER BY COUNT(n_surn) DESC" . 491 " LIMIT :limit" 492 )->execute([ 493 'tree_id' => $this->tree->id(), 494 'limit' => $number_of_surnames, 495 ])->fetchOneColumn(); 496 497 $surnames = []; 498 foreach ($top_surnames as $top_surname) { 499 $variants = Database::prepare( 500 "SELECT n_surname COLLATE utf8_bin, COUNT(*) FROM `##name` WHERE n_file = :tree_id AND n_surn COLLATE :collate = :surname GROUP BY 1" 501 )->execute([ 502 'collate' => I18N::collation(), 503 'surname' => $top_surname, 504 'tree_id' => $this->tree->id(), 505 ])->fetchAssoc(); 506 507 if (array_sum($variants) > $threshold) { 508 $surnames[$top_surname] = $variants; 509 } 510 } 511 512 return $surnames; 513 } 514 515 /** 516 * Find common surnames. 517 * 518 * @return string 519 */ 520 public function getCommonSurname(): string 521 { 522 $top_surname = $this->topSurnames(1, 0); 523 return implode(', ', array_keys(array_shift($top_surname)) ?? []); 524 } 525 526 /** 527 * Find common surnames. 528 * 529 * @param string $type 530 * @param bool $show_tot 531 * @param int $threshold 532 * @param int $number_of_surnames 533 * @param string $sorting 534 * 535 * @return string 536 */ 537 private function commonSurnamesQuery( 538 string $type, 539 bool $show_tot, 540 int $threshold, 541 int $number_of_surnames, 542 string $sorting 543 ): string { 544 $surnames = $this->topSurnames($number_of_surnames, $threshold); 545 546 switch ($sorting) { 547 default: 548 case 'alpha': 549 uksort($surnames, [I18N::class, 'strcasecmp']); 550 break; 551 case 'count': 552 break; 553 case 'rcount': 554 $surnames = array_reverse($surnames, true); 555 break; 556 } 557 558 return FunctionsPrintLists::surnameList( 559 $surnames, 560 ($type === 'list' ? 1 : 2), 561 $show_tot, 562 'individual-list', 563 $this->tree 564 ); 565 } 566 567 /** 568 * Find common surnames. 569 * 570 * @param int $threshold 571 * @param int $number_of_surnames 572 * @param string $sorting 573 * 574 * @return string 575 */ 576 public function commonSurnames( 577 int $threshold = 1, 578 int $number_of_surnames = 10, 579 string $sorting = 'alpha' 580 ): string { 581 return $this->commonSurnamesQuery('nolist', false, $threshold, $number_of_surnames, $sorting); 582 } 583 584 /** 585 * Find common surnames. 586 * 587 * @param int $threshold 588 * @param int $number_of_surnames 589 * @param string $sorting 590 * 591 * @return string 592 */ 593 public function commonSurnamesTotals( 594 int $threshold = 1, 595 int $number_of_surnames = 10, 596 string $sorting = 'rcount' 597 ): string { 598 return $this->commonSurnamesQuery('nolist', true, $threshold, $number_of_surnames, $sorting); 599 } 600 601 /** 602 * Find common surnames. 603 * 604 * @param int $threshold 605 * @param int $number_of_surnames 606 * @param string $sorting 607 * 608 * @return string 609 */ 610 public function commonSurnamesList( 611 int $threshold = 1, 612 int $number_of_surnames = 10, 613 string $sorting = 'alpha' 614 ): string { 615 return $this->commonSurnamesQuery('list', false, $threshold, $number_of_surnames, $sorting); 616 } 617 618 /** 619 * Find common surnames. 620 * 621 * @param int $threshold 622 * @param int $number_of_surnames 623 * @param string $sorting 624 * 625 * @return string 626 */ 627 public function commonSurnamesListTotals( 628 int $threshold = 1, 629 int $number_of_surnames = 10, 630 string $sorting = 'rcount' 631 ): string { 632 return $this->commonSurnamesQuery('list', true, $threshold, $number_of_surnames, $sorting); 633 } 634 635 /** 636 * Get a list of birth dates. 637 * 638 * @param bool $sex 639 * @param int $year1 640 * @param int $year2 641 * 642 * @return array 643 */ 644 public function statsBirthQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 645 { 646 if ($sex) { 647 $sql = 648 "SELECT d_month, i_sex, COUNT(*) AS total FROM `##dates` " . 649 "JOIN `##individuals` ON d_file = i_file AND d_gid = i_id " . 650 "WHERE " . 651 "d_file={$this->tree->id()} AND " . 652 "d_fact='BIRT' AND " . 653 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 654 } else { 655 $sql = 656 "SELECT d_month, COUNT(*) AS total FROM `##dates` " . 657 "WHERE " . 658 "d_file={$this->tree->id()} AND " . 659 "d_fact='BIRT' AND " . 660 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 661 } 662 663 if ($year1 >= 0 && $year2 >= 0) { 664 $sql .= " AND d_year BETWEEN '{$year1}' AND '{$year2}'"; 665 } 666 667 $sql .= " GROUP BY d_month"; 668 669 if ($sex) { 670 $sql .= ", i_sex"; 671 } 672 673 return $this->runSql($sql); 674 } 675 676 /** 677 * General query on births. 678 * 679 * @param string|null $size 680 * @param string|null $color_from 681 * @param string|null $color_to 682 * 683 * @return string 684 */ 685 public function statsBirth(string $size = null, string $color_from = null, string $color_to = null): string 686 { 687 return (new ChartBirth($this->tree)) 688 ->chartBirth($size, $color_from, $color_to); 689 } 690 691 /** 692 * Get a list of death dates. 693 * 694 * @param bool $sex 695 * @param int $year1 696 * @param int $year2 697 * 698 * @return array 699 */ 700 public function statsDeathQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 701 { 702 if ($sex) { 703 $sql = 704 "SELECT d_month, i_sex, COUNT(*) AS total FROM `##dates` " . 705 "JOIN `##individuals` ON d_file = i_file AND d_gid = i_id " . 706 "WHERE " . 707 "d_file={$this->tree->id()} AND " . 708 "d_fact='DEAT' AND " . 709 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 710 } else { 711 $sql = 712 "SELECT d_month, COUNT(*) AS total FROM `##dates` " . 713 "WHERE " . 714 "d_file={$this->tree->id()} AND " . 715 "d_fact='DEAT' AND " . 716 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 717 } 718 719 if ($year1 >= 0 && $year2 >= 0) { 720 $sql .= " AND d_year BETWEEN '{$year1}' AND '{$year2}'"; 721 } 722 723 $sql .= " GROUP BY d_month"; 724 725 if ($sex) { 726 $sql .= ", i_sex"; 727 } 728 729 return $this->runSql($sql); 730 } 731 732 /** 733 * General query on deaths. 734 * 735 * @param string|null $size 736 * @param string|null $color_from 737 * @param string|null $color_to 738 * 739 * @return string 740 */ 741 public function statsDeath(string $size = null, string $color_from = null, string $color_to = null): string 742 { 743 return (new ChartDeath($this->tree)) 744 ->chartDeath($size, $color_from, $color_to); 745 } 746 747 /** 748 * General query on ages. 749 * 750 * @param string $related 751 * @param string $sex 752 * @param int $year1 753 * @param int $year2 754 * 755 * @return array|string 756 */ 757 public function statsAgeQuery(string $related = 'BIRT', string $sex = 'BOTH', int $year1 = -1, int $year2 = -1) 758 { 759 $prefix = DB::connection()->getTablePrefix(); 760 761 $query = $this->birthAndDeathQuery($sex); 762 763 if ($year1 >= 0 && $year2 >= 0) { 764 $query 765 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 766 ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 767 768 if ($related === 'BIRT') { 769 $query->whereBetween('birth.d_year', [$year1, $year2]); 770 } elseif ($related === 'DEAT') { 771 $query->whereBetween('death.d_year', [$year1, $year2]); 772 } 773 } 774 775 return $query 776 ->select(DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')) 777 ->orderby('days', 'desc') 778 ->get() 779 ->all(); 780 } 781 782 /** 783 * General query on ages. 784 * 785 * @param string $size 786 * 787 * @return string 788 */ 789 public function statsAge(string $size = '230x250'): string 790 { 791 return (new ChartAge($this->tree))->chartAge($size); 792 } 793 794 /** 795 * Lifespan 796 * 797 * @param string $type 798 * @param string $sex 799 * 800 * @return string 801 */ 802 private function longlifeQuery(string $type, string $sex): string 803 { 804 $prefix = DB::connection()->getTablePrefix(); 805 806 $row = $this->birthAndDeathQuery($sex) 807 ->orderBy('days', 'desc') 808 ->select(['individuals.*', DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')]) 809 ->first(); 810 811 if ($row === null) { 812 return ''; 813 } 814 815 /** @var Individual $individual */ 816 $individual = Individual::rowMapper()($row); 817 818 if (!$individual->canShow()) { 819 return I18N::translate('This information is private and cannot be shown.'); 820 } 821 822 switch ($type) { 823 default: 824 case 'full': 825 return $individual->formatList(); 826 827 case 'age': 828 return I18N::number((int) ($row->days / 365.25)); 829 830 case 'name': 831 return '<a href="' . e($individual->url()) . '">' . $individual->getFullName() . '</a>'; 832 } 833 } 834 835 /** 836 * Find the longest lived individual. 837 * 838 * @return string 839 */ 840 public function longestLife(): string 841 { 842 return $this->longlifeQuery('full', 'BOTH'); 843 } 844 845 /** 846 * Find the age of the longest lived individual. 847 * 848 * @return string 849 */ 850 public function longestLifeAge(): string 851 { 852 return $this->longlifeQuery('age', 'BOTH'); 853 } 854 855 /** 856 * Find the name of the longest lived individual. 857 * 858 * @return string 859 */ 860 public function longestLifeName(): string 861 { 862 return $this->longlifeQuery('name', 'BOTH'); 863 } 864 865 /** 866 * Find the longest lived female. 867 * 868 * @return string 869 */ 870 public function longestLifeFemale(): string 871 { 872 return $this->longlifeQuery('full', 'F'); 873 } 874 875 /** 876 * Find the age of the longest lived female. 877 * 878 * @return string 879 */ 880 public function longestLifeFemaleAge(): string 881 { 882 return $this->longlifeQuery('age', 'F'); 883 } 884 885 /** 886 * Find the name of the longest lived female. 887 * 888 * @return string 889 */ 890 public function longestLifeFemaleName(): string 891 { 892 return $this->longlifeQuery('name', 'F'); 893 } 894 895 /** 896 * Find the longest lived male. 897 * 898 * @return string 899 */ 900 public function longestLifeMale(): string 901 { 902 return $this->longlifeQuery('full', 'M'); 903 } 904 905 /** 906 * Find the age of the longest lived male. 907 * 908 * @return string 909 */ 910 public function longestLifeMaleAge(): string 911 { 912 return $this->longlifeQuery('age', 'M'); 913 } 914 915 /** 916 * Find the name of the longest lived male. 917 * 918 * @return string 919 */ 920 public function longestLifeMaleName(): string 921 { 922 return $this->longlifeQuery('name', 'M'); 923 } 924 925 /** 926 * Returns the calculated age the time of event. 927 * 928 * @param int $age The age from the database record 929 * 930 * @return string 931 */ 932 private function calculateAge(int $age): string 933 { 934 if ((int) ($age / 365.25) > 0) { 935 $result = (int) ($age / 365.25) . 'y'; 936 } elseif ((int) ($age / 30.4375) > 0) { 937 $result = (int) ($age / 30.4375) . 'm'; 938 } else { 939 $result = $age . 'd'; 940 } 941 942 return FunctionsDate::getAgeAtEvent($result); 943 } 944 945 /** 946 * Find the oldest individuals. 947 * 948 * @param string $sex 949 * @param int $total 950 * 951 * @return array 952 */ 953 private function topTenOldestQuery(string $sex, int $total): array 954 { 955 $prefix = DB::connection()->getTablePrefix(); 956 957 $rows = $this->birthAndDeathQuery($sex) 958 ->groupBy(['i_id', 'i_file']) 959 ->orderBy('days', 'desc') 960 ->select(['individuals.*', DB::raw('MAX(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')]) 961 ->take($total) 962 ->get(); 963 964 $top10 = []; 965 foreach ($rows as $row) { 966 /** @var Individual $individual */ 967 $individual = Individual::rowMapper()($row); 968 969 if ($individual->canShow()) { 970 $top10[] = [ 971 'person' => $individual, 972 'age' => $this->calculateAge((int) $row->days), 973 ]; 974 } 975 } 976 977 return $top10; 978 } 979 980 /** 981 * Find the oldest individuals. 982 * 983 * @param int $total 984 * 985 * @return string 986 */ 987 public function topTenOldest(int $total = 10): string 988 { 989 $records = $this->topTenOldestQuery('BOTH', $total); 990 991 return view( 992 'statistics/individuals/top10-nolist', 993 [ 994 'records' => $records, 995 ] 996 ); 997 } 998 999 /** 1000 * Find the oldest living individuals. 1001 * 1002 * @param int $total 1003 * 1004 * @return string 1005 */ 1006 public function topTenOldestList(int $total = 10): string 1007 { 1008 $records = $this->topTenOldestQuery('BOTH', $total); 1009 1010 return view( 1011 'statistics/individuals/top10-list', 1012 [ 1013 'records' => $records, 1014 ] 1015 ); 1016 } 1017 1018 /** 1019 * Find the oldest females. 1020 * 1021 * @param int $total 1022 * 1023 * @return string 1024 */ 1025 public function topTenOldestFemale(int $total = 10): string 1026 { 1027 $records = $this->topTenOldestQuery('F', $total); 1028 1029 return view( 1030 'statistics/individuals/top10-nolist', 1031 [ 1032 'records' => $records, 1033 ] 1034 ); 1035 } 1036 1037 /** 1038 * Find the oldest living females. 1039 * 1040 * @param int $total 1041 * 1042 * @return string 1043 */ 1044 public function topTenOldestFemaleList(int $total = 10): string 1045 { 1046 $records = $this->topTenOldestQuery('F', $total); 1047 1048 return view( 1049 'statistics/individuals/top10-list', 1050 [ 1051 'records' => $records, 1052 ] 1053 ); 1054 } 1055 1056 /** 1057 * Find the longest lived males. 1058 * 1059 * @param int $total 1060 * 1061 * @return string 1062 */ 1063 public function topTenOldestMale(int $total = 10): string 1064 { 1065 $records = $this->topTenOldestQuery('M', $total); 1066 1067 return view( 1068 'statistics/individuals/top10-nolist', 1069 [ 1070 'records' => $records, 1071 ] 1072 ); 1073 } 1074 1075 /** 1076 * Find the longest lived males. 1077 * 1078 * @param int $total 1079 * 1080 * @return string 1081 */ 1082 public function topTenOldestMaleList(int $total = 10): string 1083 { 1084 $records = $this->topTenOldestQuery('M', $total); 1085 1086 return view( 1087 'statistics/individuals/top10-list', 1088 [ 1089 'records' => $records, 1090 ] 1091 ); 1092 } 1093 1094 /** 1095 * Find the oldest living individuals. 1096 * 1097 * @param string $sex 1098 * @param int $total 1099 * 1100 * @return array 1101 */ 1102 private function topTenOldestAliveQuery(string $sex = 'BOTH', int $total = 10): array 1103 { 1104 if ($sex === 'F') { 1105 $sex_search = " AND i_sex='F'"; 1106 } elseif ($sex === 'M') { 1107 $sex_search = " AND i_sex='M'"; 1108 } else { 1109 $sex_search = ''; 1110 } 1111 1112 $rows = $this->runSql( 1113 "SELECT" . 1114 " birth.d_gid AS id," . 1115 " MIN(birth.d_julianday1) AS age" . 1116 " FROM" . 1117 " `##dates` AS birth," . 1118 " `##individuals` AS indi" . 1119 " WHERE" . 1120 " indi.i_id=birth.d_gid AND" . 1121 " indi.i_gedcom NOT REGEXP '\\n1 (" . implode('|', Gedcom::DEATH_EVENTS) . ")' AND" . 1122 " birth.d_file={$this->tree->id()} AND" . 1123 " birth.d_fact='BIRT' AND" . 1124 " birth.d_file=indi.i_file AND" . 1125 " birth.d_julianday1<>0" . 1126 $sex_search . 1127 " GROUP BY id" . 1128 " ORDER BY age" . 1129 " ASC LIMIT " . $total 1130 ); 1131 1132 $top10 = []; 1133 1134 foreach ($rows as $row) { 1135 $person = Individual::getInstance($row->id, $this->tree); 1136 1137 $top10[] = [ 1138 'person' => $person, 1139 'age' => $this->calculateAge(WT_CLIENT_JD - ((int) $row->age)), 1140 ]; 1141 } 1142 1143 return $top10; 1144 } 1145 1146 /** 1147 * Find the oldest living individuals. 1148 * 1149 * @param int $total 1150 * 1151 * @return string 1152 */ 1153 public function topTenOldestAlive(int $total = 10): string 1154 { 1155 if (!Auth::isMember($this->tree)) { 1156 return I18N::translate('This information is private and cannot be shown.'); 1157 } 1158 1159 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1160 1161 return view( 1162 'statistics/individuals/top10-nolist', 1163 [ 1164 'records' => $records, 1165 ] 1166 ); 1167 } 1168 1169 /** 1170 * Find the oldest living individuals. 1171 * 1172 * @param int $total 1173 * 1174 * @return string 1175 */ 1176 public function topTenOldestListAlive(int $total = 10): string 1177 { 1178 if (!Auth::isMember($this->tree)) { 1179 return I18N::translate('This information is private and cannot be shown.'); 1180 } 1181 1182 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1183 1184 return view( 1185 'statistics/individuals/top10-list', 1186 [ 1187 'records' => $records, 1188 ] 1189 ); 1190 } 1191 1192 /** 1193 * Find the oldest living females. 1194 * 1195 * @param int $total 1196 * 1197 * @return string 1198 */ 1199 public function topTenOldestFemaleAlive(int $total = 10): string 1200 { 1201 if (!Auth::isMember($this->tree)) { 1202 return I18N::translate('This information is private and cannot be shown.'); 1203 } 1204 1205 $records = $this->topTenOldestAliveQuery('F', $total); 1206 1207 return view( 1208 'statistics/individuals/top10-nolist', 1209 [ 1210 'records' => $records, 1211 ] 1212 ); 1213 } 1214 1215 /** 1216 * Find the oldest living females. 1217 * 1218 * @param int $total 1219 * 1220 * @return string 1221 */ 1222 public function topTenOldestFemaleListAlive(int $total = 10): string 1223 { 1224 if (!Auth::isMember($this->tree)) { 1225 return I18N::translate('This information is private and cannot be shown.'); 1226 } 1227 1228 $records = $this->topTenOldestAliveQuery('F', $total); 1229 1230 return view( 1231 'statistics/individuals/top10-list', 1232 [ 1233 'records' => $records, 1234 ] 1235 ); 1236 } 1237 1238 /** 1239 * Find the longest lived living males. 1240 * 1241 * @param int $total 1242 * 1243 * @return string 1244 */ 1245 public function topTenOldestMaleAlive(int $total = 10): string 1246 { 1247 if (!Auth::isMember($this->tree)) { 1248 return I18N::translate('This information is private and cannot be shown.'); 1249 } 1250 1251 $records = $this->topTenOldestAliveQuery('M', $total); 1252 1253 return view( 1254 'statistics/individuals/top10-nolist', 1255 [ 1256 'records' => $records, 1257 ] 1258 ); 1259 } 1260 1261 /** 1262 * Find the longest lived living males. 1263 * 1264 * @param int $total 1265 * 1266 * @return string 1267 */ 1268 public function topTenOldestMaleListAlive(int $total = 10): string 1269 { 1270 if (!Auth::isMember($this->tree)) { 1271 return I18N::translate('This information is private and cannot be shown.'); 1272 } 1273 1274 $records = $this->topTenOldestAliveQuery('M', $total); 1275 1276 return view( 1277 'statistics/individuals/top10-list', 1278 [ 1279 'records' => $records, 1280 ] 1281 ); 1282 } 1283 1284 /** 1285 * Find the average lifespan. 1286 * 1287 * @param string $sex 1288 * @param bool $show_years 1289 * 1290 * @return string 1291 */ 1292 private function averageLifespanQuery(string $sex = 'BOTH', bool $show_years = false): string 1293 { 1294 $prefix = DB::connection()->getTablePrefix(); 1295 1296 $days = (int) $this->birthAndDeathQuery($sex) 1297 ->groupBy('i_id') 1298 ->select(DB::raw('AVG(' . $prefix . 'death.d_julianday1 - ' . $prefix . 'birth.d_julianday2) AS days')) 1299 ->value('days'); 1300 1301 if ($show_years) { 1302 return $this->calculateAge($days); 1303 } 1304 1305 return I18N::number((int) ($days / 365.25)); 1306 } 1307 1308 /** 1309 * Find the average lifespan. 1310 * 1311 * @param bool $show_years 1312 * 1313 * @return string 1314 */ 1315 public function averageLifespan($show_years = false): string 1316 { 1317 return $this->averageLifespanQuery('BOTH', $show_years); 1318 } 1319 1320 /** 1321 * Find the average lifespan of females. 1322 * 1323 * @param bool $show_years 1324 * 1325 * @return string 1326 */ 1327 public function averageLifespanFemale($show_years = false): string 1328 { 1329 return $this->averageLifespanQuery('F', $show_years); 1330 } 1331 1332 /** 1333 * Find the average male lifespan. 1334 * 1335 * @param bool $show_years 1336 * 1337 * @return string 1338 */ 1339 public function averageLifespanMale($show_years = false): string 1340 { 1341 return $this->averageLifespanQuery('M', $show_years); 1342 } 1343 1344 /** 1345 * Convert totals into percentages. 1346 * 1347 * @param int $count 1348 * @param int $total 1349 * 1350 * @return string 1351 */ 1352 private function getPercentage(int $count, int $total): string 1353 { 1354 return I18N::percentage($count / $total, 1); 1355 } 1356 1357 /** 1358 * Returns how many individuals exist in the tree. 1359 * 1360 * @return int 1361 */ 1362 private function totalIndividualsQuery(): int 1363 { 1364 return DB::table('individuals') 1365 ->where('i_file', '=', $this->tree->id()) 1366 ->count(); 1367 } 1368 1369 /** 1370 * Count the number of living individuals. 1371 * 1372 * The totalLiving/totalDeceased queries assume that every dead person will 1373 * have a DEAT record. It will not include individuals who were born more 1374 * than MAX_ALIVE_AGE years ago, and who have no DEAT record. 1375 * A good reason to run the “Add missing DEAT records” batch-update! 1376 * 1377 * @return int 1378 */ 1379 private function totalLivingQuery(): int 1380 { 1381 $query = DB::table('individuals') 1382 ->where('i_file', '=', $this->tree->id()); 1383 1384 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1385 $query->where('i_gedcom', 'NOT LIKE', '%\n1 ' . $death_event); 1386 } 1387 1388 return $query->count(); 1389 } 1390 1391 /** 1392 * Count the number of dead individuals. 1393 * 1394 * @return int 1395 */ 1396 private function totalDeceasedQuery(): int 1397 { 1398 return DB::table('individuals') 1399 ->where('i_file', '=', $this->tree->id()) 1400 ->where(function (Builder $query): void { 1401 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1402 $query->orWhere('i_gedcom', 'NOT LIKE', '%\n1 ' . $death_event); 1403 } 1404 }) 1405 ->count(); 1406 } 1407 1408 /** 1409 * Returns the total count of a specific sex. 1410 * 1411 * @param string $sex The sex to query 1412 * 1413 * @return int 1414 */ 1415 private function getTotalSexQuery(string $sex): int 1416 { 1417 return DB::table('individuals') 1418 ->where('i_file', '=', $this->tree->id()) 1419 ->where('i_sex', '=', $sex) 1420 ->count(); 1421 } 1422 1423 /** 1424 * Returns the total number of males. 1425 * 1426 * @return int 1427 */ 1428 private function totalSexMalesQuery(): int 1429 { 1430 return $this->getTotalSexQuery('M'); 1431 } 1432 1433 /** 1434 * Returns the total number of females. 1435 * 1436 * @return int 1437 */ 1438 private function totalSexFemalesQuery(): int 1439 { 1440 return $this->getTotalSexQuery('F'); 1441 } 1442 1443 /** 1444 * Returns the total number of individuals with unknown sex. 1445 * 1446 * @return int 1447 */ 1448 private function totalSexUnknownQuery(): int 1449 { 1450 return $this->getTotalSexQuery('U'); 1451 } 1452 1453 /** 1454 * Count the total families. 1455 * 1456 * @return int 1457 */ 1458 private function totalFamiliesQuery(): int 1459 { 1460 return DB::table('families') 1461 ->where('f_file', '=', $this->tree->id()) 1462 ->count(); 1463 } 1464 1465 /** 1466 * How many individuals have one or more sources. 1467 * 1468 * @return int 1469 */ 1470 private function totalIndisWithSourcesQuery(): int 1471 { 1472 return DB::table('individuals') 1473 ->select(['i_id']) 1474 ->distinct() 1475 ->join('link', function (JoinClause $join) { 1476 $join->on('i_id', '=', 'l_from') 1477 ->on('i_file', '=', 'l_file'); 1478 }) 1479 ->where('l_file', '=', $this->tree->id()) 1480 ->where('l_type', '=', 'SOUR') 1481 ->count('i_id'); 1482 } 1483 1484 /** 1485 * Count the families with source records. 1486 * 1487 * @return int 1488 */ 1489 private function totalFamsWithSourcesQuery(): int 1490 { 1491 return DB::table('families') 1492 ->select(['f_id']) 1493 ->distinct() 1494 ->join('link', function (JoinClause $join) { 1495 $join->on('f_id', '=', 'l_from') 1496 ->on('f_file', '=', 'l_file'); 1497 }) 1498 ->where('l_file', '=', $this->tree->id()) 1499 ->where('l_type', '=', 'SOUR') 1500 ->count('f_id'); 1501 } 1502 1503 /** 1504 * Count the number of repositories. 1505 * 1506 * @return int 1507 */ 1508 private function totalRepositoriesQuery(): int 1509 { 1510 return DB::table('other') 1511 ->where('o_file', '=', $this->tree->id()) 1512 ->where('o_type', '=', 'REPO') 1513 ->count(); 1514 } 1515 1516 /** 1517 * Count the total number of sources. 1518 * 1519 * @return int 1520 */ 1521 private function totalSourcesQuery(): int 1522 { 1523 return DB::table('sources') 1524 ->where('s_file', '=', $this->tree->id()) 1525 ->count(); 1526 } 1527 1528 /** 1529 * Count the number of notes. 1530 * 1531 * @return int 1532 */ 1533 private function totalNotesQuery(): int 1534 { 1535 return DB::table('other') 1536 ->where('o_file', '=', $this->tree->id()) 1537 ->where('o_type', '=', 'NOTE') 1538 ->count(); 1539 } 1540 1541 /** 1542 * Returns the total number of records. 1543 * 1544 * @return int 1545 */ 1546 private function totalRecordsQuery(): int 1547 { 1548 return $this->totalIndividualsQuery() 1549 + $this->totalFamiliesQuery() 1550 + $this->totalNotesQuery() 1551 + $this->totalRepositoriesQuery() 1552 + $this->totalSourcesQuery(); 1553 } 1554 1555 /** 1556 * @inheritDoc 1557 */ 1558 public function totalRecords(): string 1559 { 1560 return I18N::number($this->totalRecordsQuery()); 1561 } 1562 1563 /** 1564 * @inheritDoc 1565 */ 1566 public function totalIndividuals(): string 1567 { 1568 return I18N::number($this->totalIndividualsQuery()); 1569 } 1570 1571 /** 1572 * Count the number of living individuals. 1573 * 1574 * @return string 1575 */ 1576 public function totalLiving(): string 1577 { 1578 return I18N::number($this->totalLivingQuery()); 1579 } 1580 1581 /** 1582 * Count the number of dead individuals. 1583 * 1584 * @return string 1585 */ 1586 public function totalDeceased(): string 1587 { 1588 return I18N::number($this->totalDeceasedQuery()); 1589 } 1590 1591 /** 1592 * @inheritDoc 1593 */ 1594 public function totalSexMales(): string 1595 { 1596 return I18N::number($this->totalSexMalesQuery()); 1597 } 1598 1599 /** 1600 * @inheritDoc 1601 */ 1602 public function totalSexFemales(): string 1603 { 1604 return I18N::number($this->totalSexFemalesQuery()); 1605 } 1606 1607 /** 1608 * @inheritDoc 1609 */ 1610 public function totalSexUnknown(): string 1611 { 1612 return I18N::number($this->totalSexUnknownQuery()); 1613 } 1614 1615 /** 1616 * @inheritDoc 1617 */ 1618 public function totalFamilies(): string 1619 { 1620 return I18N::number($this->totalFamiliesQuery()); 1621 } 1622 1623 /** 1624 * How many individuals have one or more sources. 1625 * 1626 * @return string 1627 */ 1628 public function totalIndisWithSources(): string 1629 { 1630 return I18N::number($this->totalIndisWithSourcesQuery()); 1631 } 1632 1633 /** 1634 * Count the families with with source records. 1635 * 1636 * @return string 1637 */ 1638 public function totalFamsWithSources(): string 1639 { 1640 return I18N::number($this->totalFamsWithSourcesQuery()); 1641 } 1642 1643 /** 1644 * @inheritDoc 1645 */ 1646 public function totalRepositories(): string 1647 { 1648 return I18N::number($this->totalRepositoriesQuery()); 1649 } 1650 1651 /** 1652 * @inheritDoc 1653 */ 1654 public function totalSources(): string 1655 { 1656 return I18N::number($this->totalSourcesQuery()); 1657 } 1658 1659 /** 1660 * @inheritDoc 1661 */ 1662 public function totalNotes(): string 1663 { 1664 return I18N::number($this->totalNotesQuery()); 1665 } 1666 1667 /** 1668 * @inheritDoc 1669 */ 1670 public function totalIndividualsPercentage(): string 1671 { 1672 return $this->getPercentage( 1673 $this->totalIndividualsQuery(), 1674 $this->totalRecordsQuery() 1675 ); 1676 } 1677 1678 /** 1679 * @inheritDoc 1680 */ 1681 public function totalFamiliesPercentage(): string 1682 { 1683 return $this->getPercentage( 1684 $this->totalFamiliesQuery(), 1685 $this->totalRecordsQuery() 1686 ); 1687 } 1688 1689 /** 1690 * @inheritDoc 1691 */ 1692 public function totalRepositoriesPercentage(): string 1693 { 1694 return $this->getPercentage( 1695 $this->totalRepositoriesQuery(), 1696 $this->totalRecordsQuery() 1697 ); 1698 } 1699 1700 /** 1701 * @inheritDoc 1702 */ 1703 public function totalSourcesPercentage(): string 1704 { 1705 return $this->getPercentage( 1706 $this->totalSourcesQuery(), 1707 $this->totalRecordsQuery() 1708 ); 1709 } 1710 1711 /** 1712 * @inheritDoc 1713 */ 1714 public function totalNotesPercentage(): string 1715 { 1716 return $this->getPercentage( 1717 $this->totalNotesQuery(), 1718 $this->totalRecordsQuery() 1719 ); 1720 } 1721 1722 /** 1723 * @inheritDoc 1724 */ 1725 public function totalLivingPercentage(): string 1726 { 1727 return $this->getPercentage( 1728 $this->totalLivingQuery(), 1729 $this->totalIndividualsQuery() 1730 ); 1731 } 1732 1733 /** 1734 * @inheritDoc 1735 */ 1736 public function totalDeceasedPercentage(): string 1737 { 1738 return $this->getPercentage( 1739 $this->totalDeceasedQuery(), 1740 $this->totalIndividualsQuery() 1741 ); 1742 } 1743 1744 /** 1745 * @inheritDoc 1746 */ 1747 public function totalSexMalesPercentage(): string 1748 { 1749 return $this->getPercentage( 1750 $this->totalSexMalesQuery(), 1751 $this->totalIndividualsQuery() 1752 ); 1753 } 1754 1755 /** 1756 * @inheritDoc 1757 */ 1758 public function totalSexFemalesPercentage(): string 1759 { 1760 return $this->getPercentage( 1761 $this->totalSexFemalesQuery(), 1762 $this->totalIndividualsQuery() 1763 ); 1764 } 1765 1766 /** 1767 * @inheritDoc 1768 */ 1769 public function totalSexUnknownPercentage(): string 1770 { 1771 return $this->getPercentage( 1772 $this->totalSexUnknownQuery(), 1773 $this->totalIndividualsQuery() 1774 ); 1775 } 1776 1777 /** 1778 * Create a chart of common given names. 1779 * 1780 * @param string|null $size 1781 * @param string|null $color_from 1782 * @param string|null $color_to 1783 * @param int $maxtoshow 1784 * 1785 * @return string 1786 */ 1787 public function chartCommonGiven( 1788 string $size = null, 1789 string $color_from = null, 1790 string $color_to = null, 1791 int $maxtoshow = 7 1792 ): string { 1793 $tot_indi = $this->totalIndividualsQuery(); 1794 $given = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow); 1795 1796 return (new ChartCommonGiven()) 1797 ->chartCommonGiven($tot_indi, $given, $size, $color_from, $color_to); 1798 } 1799 1800 /** 1801 * Create a chart of common surnames. 1802 * 1803 * @param string|null $size 1804 * @param string|null $color_from 1805 * @param string|null $color_to 1806 * @param int $number_of_surnames 1807 * 1808 * @return string 1809 */ 1810 public function chartCommonSurnames( 1811 string $size = null, 1812 string $color_from = null, 1813 string $color_to = null, 1814 int $number_of_surnames = 10 1815 ): string { 1816 $tot_indi = $this->totalIndividualsQuery(); 1817 $all_surnames = $this->topSurnames($number_of_surnames, 0); 1818 1819 return (new ChartCommonSurname($this->tree)) 1820 ->chartCommonSurnames($tot_indi, $all_surnames, $size, $color_from, $color_to); 1821 } 1822 1823 /** 1824 * Create a chart showing mortality. 1825 * 1826 * @param string|null $size 1827 * @param string|null $color_living 1828 * @param string|null $color_dead 1829 * 1830 * @return string 1831 */ 1832 public function chartMortality(string $size = null, string $color_living = null, string $color_dead = null): string 1833 { 1834 $tot_l = $this->totalLivingQuery(); 1835 $tot_d = $this->totalDeceasedQuery(); 1836 1837 return (new ChartMortality($this->tree)) 1838 ->chartMortality($tot_l, $tot_d, $size, $color_living, $color_dead); 1839 } 1840 1841 /** 1842 * Create a chart showing individuals with/without sources. 1843 * 1844 * @param string|null $size 1845 * @param string|null $color_from 1846 * @param string|null $color_to 1847 * 1848 * @return string 1849 */ 1850 public function chartIndisWithSources( 1851 string $size = null, 1852 string $color_from = null, 1853 string $color_to = null 1854 ): string { 1855 $tot_indi = $this->totalIndividualsQuery(); 1856 $tot_indi_source = $this->totalIndisWithSourcesQuery(); 1857 1858 return (new ChartIndividual()) 1859 ->chartIndisWithSources($tot_indi, $tot_indi_source, $size, $color_from, $color_to); 1860 } 1861 1862 /** 1863 * Create a chart of individuals with/without sources. 1864 * 1865 * @param string|null $size 1866 * @param string|null $color_from 1867 * @param string|null $color_to 1868 * 1869 * @return string 1870 */ 1871 public function chartFamsWithSources( 1872 string $size = null, 1873 string $color_from = null, 1874 string $color_to = null 1875 ): string { 1876 $tot_fam = $this->totalFamiliesQuery(); 1877 $tot_fam_source = $this->totalFamsWithSourcesQuery(); 1878 1879 return (new ChartFamilyWithSources()) 1880 ->chartFamsWithSources($tot_fam, $tot_fam_source, $size, $color_from, $color_to); 1881 } 1882 1883 /** 1884 * @inheritDoc 1885 */ 1886 public function chartSex( 1887 string $size = null, 1888 string $color_female = null, 1889 string $color_male = null, 1890 string $color_unknown = null 1891 ): string { 1892 $tot_m = $this->totalSexMalesQuery(); 1893 $tot_f = $this->totalSexFemalesQuery(); 1894 $tot_u = $this->totalSexUnknownQuery(); 1895 1896 return (new ChartSex($this->tree)) 1897 ->chartSex($tot_m, $tot_f, $tot_u, $size, $color_female, $color_male, $color_unknown); 1898 } 1899 1900 /** 1901 * Query individuals, with their births and deaths. 1902 * 1903 * @param string $sex 1904 * 1905 * @return Builder 1906 */ 1907 private function birthAndDeathQuery(string $sex): Builder 1908 { 1909 $query = DB::table('individuals') 1910 ->where('i_file', '=', $this->tree->id()) 1911 ->join('dates AS birth', function (JoinClause $join): void { 1912 $join 1913 ->on('birth.d_file', '=', 'i_file') 1914 ->on('birth.d_gid', '=', 'i_id'); 1915 }) 1916 ->join('dates AS death', function (JoinClause $join): void { 1917 $join 1918 ->on('death.d_file', '=', 'i_file') 1919 ->on('death.d_gid', '=', 'i_id'); 1920 }) 1921 ->where('birth.d_fact', '=', 'BIRT') 1922 ->where('death.d_fact', '=', 'DEAT') 1923 ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2') 1924 ->where('birth.d_julianday2', '<>', 0); 1925 1926 if ($sex === 'M' || $sex === 'F') { 1927 $query->where('i_sex', '=', $sex); 1928 } 1929 1930 return $query; 1931 } 1932} 1933