1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2019 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees\Statistics\Repository; 19 20use Fisharebest\Webtrees\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\ChartIndividualWithSources; 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 given names. 437 $query 438 ->distinct() 439 ->where('n_givn', '<>', '@P.N.') 440 ->whereNotNull('n_givn'); 441 } else { 442 // Count number of occurences of specific given names. 443 $query->whereIn('n_givn', $params); 444 } 445 446 $count = $query->count('n_givn'); 447 448 return I18N::number($count); 449 } 450 451 /** 452 * Count the number of distinct surnames (or the number of occurences of specific surnames). 453 * 454 * @param string[] ...$params 455 * 456 * @return string 457 */ 458 public function totalSurnames(...$params): string 459 { 460 $query = DB::table('name') 461 ->where('n_file', '=', $this->tree->id()); 462 463 if (empty($params)) { 464 // Count number of distinct surnames 465 $query->distinct() 466 ->whereNotNull('n_surn'); 467 } else { 468 // Count number of occurences of specific surnames. 469 $query->whereIn('n_surn', $params); 470 } 471 472 $count = $query->count('n_surn'); 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 524 return $top_surname 525 ? implode(', ', array_keys(array_shift($top_surname)) ?? []) 526 : ''; 527 } 528 529 /** 530 * Find common surnames. 531 * 532 * @param string $type 533 * @param bool $show_tot 534 * @param int $threshold 535 * @param int $number_of_surnames 536 * @param string $sorting 537 * 538 * @return string 539 */ 540 private function commonSurnamesQuery( 541 string $type, 542 bool $show_tot, 543 int $threshold, 544 int $number_of_surnames, 545 string $sorting 546 ): string { 547 $surnames = $this->topSurnames($number_of_surnames, $threshold); 548 549 switch ($sorting) { 550 default: 551 case 'alpha': 552 uksort($surnames, [I18N::class, 'strcasecmp']); 553 break; 554 case 'count': 555 break; 556 case 'rcount': 557 $surnames = array_reverse($surnames, true); 558 break; 559 } 560 561 return FunctionsPrintLists::surnameList( 562 $surnames, 563 ($type === 'list' ? 1 : 2), 564 $show_tot, 565 'individual-list', 566 $this->tree 567 ); 568 } 569 570 /** 571 * Find common surnames. 572 * 573 * @param int $threshold 574 * @param int $number_of_surnames 575 * @param string $sorting 576 * 577 * @return string 578 */ 579 public function commonSurnames( 580 int $threshold = 1, 581 int $number_of_surnames = 10, 582 string $sorting = 'alpha' 583 ): string { 584 return $this->commonSurnamesQuery('nolist', false, $threshold, $number_of_surnames, $sorting); 585 } 586 587 /** 588 * Find common surnames. 589 * 590 * @param int $threshold 591 * @param int $number_of_surnames 592 * @param string $sorting 593 * 594 * @return string 595 */ 596 public function commonSurnamesTotals( 597 int $threshold = 1, 598 int $number_of_surnames = 10, 599 string $sorting = 'rcount' 600 ): string { 601 return $this->commonSurnamesQuery('nolist', true, $threshold, $number_of_surnames, $sorting); 602 } 603 604 /** 605 * Find common surnames. 606 * 607 * @param int $threshold 608 * @param int $number_of_surnames 609 * @param string $sorting 610 * 611 * @return string 612 */ 613 public function commonSurnamesList( 614 int $threshold = 1, 615 int $number_of_surnames = 10, 616 string $sorting = 'alpha' 617 ): string { 618 return $this->commonSurnamesQuery('list', false, $threshold, $number_of_surnames, $sorting); 619 } 620 621 /** 622 * Find common surnames. 623 * 624 * @param int $threshold 625 * @param int $number_of_surnames 626 * @param string $sorting 627 * 628 * @return string 629 */ 630 public function commonSurnamesListTotals( 631 int $threshold = 1, 632 int $number_of_surnames = 10, 633 string $sorting = 'rcount' 634 ): string { 635 return $this->commonSurnamesQuery('list', true, $threshold, $number_of_surnames, $sorting); 636 } 637 638 /** 639 * Get a list of birth dates. 640 * 641 * @param bool $sex 642 * @param int $year1 643 * @param int $year2 644 * 645 * @return array 646 */ 647 public function statsBirthQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 648 { 649 if ($sex) { 650 $sql = 651 "SELECT d_month, i_sex, COUNT(*) AS total FROM `##dates` " . 652 "JOIN `##individuals` ON d_file = i_file AND d_gid = i_id " . 653 "WHERE " . 654 "d_file={$this->tree->id()} AND " . 655 "d_fact='BIRT' AND " . 656 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 657 } else { 658 $sql = 659 "SELECT d_month, COUNT(*) AS total FROM `##dates` " . 660 "WHERE " . 661 "d_file={$this->tree->id()} AND " . 662 "d_fact='BIRT' AND " . 663 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 664 } 665 666 if ($year1 >= 0 && $year2 >= 0) { 667 $sql .= " AND d_year BETWEEN '{$year1}' AND '{$year2}'"; 668 } 669 670 $sql .= " GROUP BY d_month"; 671 672 if ($sex) { 673 $sql .= ", i_sex"; 674 } 675 676 return $this->runSql($sql); 677 } 678 679 /** 680 * General query on births. 681 * 682 * @param string|null $color_from 683 * @param string|null $color_to 684 * 685 * @return string 686 */ 687 public function statsBirth(string $color_from = null, string $color_to = null): string 688 { 689 return (new ChartBirth($this->tree)) 690 ->chartBirth($color_from, $color_to); 691 } 692 693 /** 694 * Get a list of death dates. 695 * 696 * @param bool $sex 697 * @param int $year1 698 * @param int $year2 699 * 700 * @return array 701 */ 702 public function statsDeathQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 703 { 704 if ($sex) { 705 $sql = 706 "SELECT d_month, i_sex, COUNT(*) AS total FROM `##dates` " . 707 "JOIN `##individuals` ON d_file = i_file AND d_gid = i_id " . 708 "WHERE " . 709 "d_file={$this->tree->id()} AND " . 710 "d_fact='DEAT' AND " . 711 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 712 } else { 713 $sql = 714 "SELECT d_month, COUNT(*) AS total FROM `##dates` " . 715 "WHERE " . 716 "d_file={$this->tree->id()} AND " . 717 "d_fact='DEAT' AND " . 718 "d_type IN ('@#DGREGORIAN@', '@#DJULIAN@')"; 719 } 720 721 if ($year1 >= 0 && $year2 >= 0) { 722 $sql .= " AND d_year BETWEEN '{$year1}' AND '{$year2}'"; 723 } 724 725 $sql .= " GROUP BY d_month"; 726 727 if ($sex) { 728 $sql .= ", i_sex"; 729 } 730 731 return $this->runSql($sql); 732 } 733 734 /** 735 * General query on deaths. 736 * 737 * @param string|null $color_from 738 * @param string|null $color_to 739 * 740 * @return string 741 */ 742 public function statsDeath(string $color_from = null, string $color_to = null): string 743 { 744 return (new ChartDeath($this->tree)) 745 ->chartDeath($color_from, $color_to); 746 } 747 748 /** 749 * General query on ages. 750 * 751 * @param string $related 752 * @param string $sex 753 * @param int $year1 754 * @param int $year2 755 * 756 * @return array|string 757 */ 758 public function statsAgeQuery(string $related = 'BIRT', string $sex = 'BOTH', int $year1 = -1, int $year2 = -1) 759 { 760 $prefix = DB::connection()->getTablePrefix(); 761 762 $query = $this->birthAndDeathQuery($sex); 763 764 if ($year1 >= 0 && $year2 >= 0) { 765 $query 766 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 767 ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 768 769 if ($related === 'BIRT') { 770 $query->whereBetween('birth.d_year', [$year1, $year2]); 771 } elseif ($related === 'DEAT') { 772 $query->whereBetween('death.d_year', [$year1, $year2]); 773 } 774 } 775 776 return $query 777 ->select(DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')) 778 ->orderBy('days', 'desc') 779 ->get() 780 ->all(); 781 } 782 783 /** 784 * General query on ages. 785 * 786 * @return string 787 */ 788 public function statsAge(): string 789 { 790 return (new ChartAge($this->tree))->chartAge(); 791 } 792 793 /** 794 * Lifespan 795 * 796 * @param string $type 797 * @param string $sex 798 * 799 * @return string 800 */ 801 private function longlifeQuery(string $type, string $sex): string 802 { 803 $prefix = DB::connection()->getTablePrefix(); 804 805 $row = $this->birthAndDeathQuery($sex) 806 ->orderBy('days', 'desc') 807 ->select(['individuals.*', DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')]) 808 ->first(); 809 810 if ($row === null) { 811 return ''; 812 } 813 814 /** @var Individual $individual */ 815 $individual = Individual::rowMapper()($row); 816 817 if (!$individual->canShow()) { 818 return I18N::translate('This information is private and cannot be shown.'); 819 } 820 821 switch ($type) { 822 default: 823 case 'full': 824 return $individual->formatList(); 825 826 case 'age': 827 return I18N::number((int) ($row->days / 365.25)); 828 829 case 'name': 830 return '<a href="' . e($individual->url()) . '">' . $individual->fullName() . '</a>'; 831 } 832 } 833 834 /** 835 * Find the longest lived individual. 836 * 837 * @return string 838 */ 839 public function longestLife(): string 840 { 841 return $this->longlifeQuery('full', 'BOTH'); 842 } 843 844 /** 845 * Find the age of the longest lived individual. 846 * 847 * @return string 848 */ 849 public function longestLifeAge(): string 850 { 851 return $this->longlifeQuery('age', 'BOTH'); 852 } 853 854 /** 855 * Find the name of the longest lived individual. 856 * 857 * @return string 858 */ 859 public function longestLifeName(): string 860 { 861 return $this->longlifeQuery('name', 'BOTH'); 862 } 863 864 /** 865 * Find the longest lived female. 866 * 867 * @return string 868 */ 869 public function longestLifeFemale(): string 870 { 871 return $this->longlifeQuery('full', 'F'); 872 } 873 874 /** 875 * Find the age of the longest lived female. 876 * 877 * @return string 878 */ 879 public function longestLifeFemaleAge(): string 880 { 881 return $this->longlifeQuery('age', 'F'); 882 } 883 884 /** 885 * Find the name of the longest lived female. 886 * 887 * @return string 888 */ 889 public function longestLifeFemaleName(): string 890 { 891 return $this->longlifeQuery('name', 'F'); 892 } 893 894 /** 895 * Find the longest lived male. 896 * 897 * @return string 898 */ 899 public function longestLifeMale(): string 900 { 901 return $this->longlifeQuery('full', 'M'); 902 } 903 904 /** 905 * Find the age of the longest lived male. 906 * 907 * @return string 908 */ 909 public function longestLifeMaleAge(): string 910 { 911 return $this->longlifeQuery('age', 'M'); 912 } 913 914 /** 915 * Find the name of the longest lived male. 916 * 917 * @return string 918 */ 919 public function longestLifeMaleName(): string 920 { 921 return $this->longlifeQuery('name', 'M'); 922 } 923 924 /** 925 * Returns the calculated age the time of event. 926 * 927 * @param int $age The age from the database record 928 * 929 * @return string 930 */ 931 private function calculateAge(int $age): string 932 { 933 if ((int) ($age / 365.25) > 0) { 934 $result = (int) ($age / 365.25) . 'y'; 935 } elseif ((int) ($age / 30.4375) > 0) { 936 $result = (int) ($age / 30.4375) . 'm'; 937 } else { 938 $result = $age . 'd'; 939 } 940 941 return FunctionsDate::getAgeAtEvent($result); 942 } 943 944 /** 945 * Find the oldest individuals. 946 * 947 * @param string $sex 948 * @param int $total 949 * 950 * @return array 951 */ 952 private function topTenOldestQuery(string $sex, int $total): array 953 { 954 $prefix = DB::connection()->getTablePrefix(); 955 956 $rows = $this->birthAndDeathQuery($sex) 957 ->groupBy(['i_id', 'i_file']) 958 ->orderBy('days', 'desc') 959 ->select(['individuals.*', DB::raw('MAX(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')]) 960 ->take($total) 961 ->get(); 962 963 $top10 = []; 964 foreach ($rows as $row) { 965 /** @var Individual $individual */ 966 $individual = Individual::rowMapper()($row); 967 968 if ($individual->canShow()) { 969 $top10[] = [ 970 'person' => $individual, 971 'age' => $this->calculateAge((int) $row->days), 972 ]; 973 } 974 } 975 976 return $top10; 977 } 978 979 /** 980 * Find the oldest individuals. 981 * 982 * @param int $total 983 * 984 * @return string 985 */ 986 public function topTenOldest(int $total = 10): string 987 { 988 $records = $this->topTenOldestQuery('BOTH', $total); 989 990 return view( 991 'statistics/individuals/top10-nolist', 992 [ 993 'records' => $records, 994 ] 995 ); 996 } 997 998 /** 999 * Find the oldest living individuals. 1000 * 1001 * @param int $total 1002 * 1003 * @return string 1004 */ 1005 public function topTenOldestList(int $total = 10): string 1006 { 1007 $records = $this->topTenOldestQuery('BOTH', $total); 1008 1009 return view( 1010 'statistics/individuals/top10-list', 1011 [ 1012 'records' => $records, 1013 ] 1014 ); 1015 } 1016 1017 /** 1018 * Find the oldest females. 1019 * 1020 * @param int $total 1021 * 1022 * @return string 1023 */ 1024 public function topTenOldestFemale(int $total = 10): string 1025 { 1026 $records = $this->topTenOldestQuery('F', $total); 1027 1028 return view( 1029 'statistics/individuals/top10-nolist', 1030 [ 1031 'records' => $records, 1032 ] 1033 ); 1034 } 1035 1036 /** 1037 * Find the oldest living females. 1038 * 1039 * @param int $total 1040 * 1041 * @return string 1042 */ 1043 public function topTenOldestFemaleList(int $total = 10): string 1044 { 1045 $records = $this->topTenOldestQuery('F', $total); 1046 1047 return view( 1048 'statistics/individuals/top10-list', 1049 [ 1050 'records' => $records, 1051 ] 1052 ); 1053 } 1054 1055 /** 1056 * Find the longest lived males. 1057 * 1058 * @param int $total 1059 * 1060 * @return string 1061 */ 1062 public function topTenOldestMale(int $total = 10): string 1063 { 1064 $records = $this->topTenOldestQuery('M', $total); 1065 1066 return view( 1067 'statistics/individuals/top10-nolist', 1068 [ 1069 'records' => $records, 1070 ] 1071 ); 1072 } 1073 1074 /** 1075 * Find the longest lived males. 1076 * 1077 * @param int $total 1078 * 1079 * @return string 1080 */ 1081 public function topTenOldestMaleList(int $total = 10): string 1082 { 1083 $records = $this->topTenOldestQuery('M', $total); 1084 1085 return view( 1086 'statistics/individuals/top10-list', 1087 [ 1088 'records' => $records, 1089 ] 1090 ); 1091 } 1092 1093 /** 1094 * Find the oldest living individuals. 1095 * 1096 * @param string $sex 1097 * @param int $total 1098 * 1099 * @return array 1100 */ 1101 private function topTenOldestAliveQuery(string $sex = 'BOTH', int $total = 10): array 1102 { 1103 if ($sex === 'F') { 1104 $sex_search = " AND i_sex='F'"; 1105 } elseif ($sex === 'M') { 1106 $sex_search = " AND i_sex='M'"; 1107 } else { 1108 $sex_search = ''; 1109 } 1110 1111 $rows = $this->runSql( 1112 "SELECT" . 1113 " birth.d_gid AS id," . 1114 " MIN(birth.d_julianday1) AS age" . 1115 " FROM" . 1116 " `##dates` AS birth," . 1117 " `##individuals` AS indi" . 1118 " WHERE" . 1119 " indi.i_id=birth.d_gid AND" . 1120 " indi.i_gedcom NOT REGEXP '\\n1 (" . implode('|', Gedcom::DEATH_EVENTS) . ")' AND" . 1121 " birth.d_file={$this->tree->id()} AND" . 1122 " birth.d_fact='BIRT' AND" . 1123 " birth.d_file=indi.i_file AND" . 1124 " birth.d_julianday1<>0" . 1125 $sex_search . 1126 " GROUP BY id" . 1127 " ORDER BY age" . 1128 " ASC LIMIT " . $total 1129 ); 1130 1131 $top10 = []; 1132 1133 foreach ($rows as $row) { 1134 $person = Individual::getInstance($row->id, $this->tree); 1135 1136 $top10[] = [ 1137 'person' => $person, 1138 'age' => $this->calculateAge(WT_CLIENT_JD - ((int) $row->age)), 1139 ]; 1140 } 1141 1142 return $top10; 1143 } 1144 1145 /** 1146 * Find the oldest living individuals. 1147 * 1148 * @param int $total 1149 * 1150 * @return string 1151 */ 1152 public function topTenOldestAlive(int $total = 10): string 1153 { 1154 if (!Auth::isMember($this->tree)) { 1155 return I18N::translate('This information is private and cannot be shown.'); 1156 } 1157 1158 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1159 1160 return view( 1161 'statistics/individuals/top10-nolist', 1162 [ 1163 'records' => $records, 1164 ] 1165 ); 1166 } 1167 1168 /** 1169 * Find the oldest living individuals. 1170 * 1171 * @param int $total 1172 * 1173 * @return string 1174 */ 1175 public function topTenOldestListAlive(int $total = 10): string 1176 { 1177 if (!Auth::isMember($this->tree)) { 1178 return I18N::translate('This information is private and cannot be shown.'); 1179 } 1180 1181 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1182 1183 return view( 1184 'statistics/individuals/top10-list', 1185 [ 1186 'records' => $records, 1187 ] 1188 ); 1189 } 1190 1191 /** 1192 * Find the oldest living females. 1193 * 1194 * @param int $total 1195 * 1196 * @return string 1197 */ 1198 public function topTenOldestFemaleAlive(int $total = 10): string 1199 { 1200 if (!Auth::isMember($this->tree)) { 1201 return I18N::translate('This information is private and cannot be shown.'); 1202 } 1203 1204 $records = $this->topTenOldestAliveQuery('F', $total); 1205 1206 return view( 1207 'statistics/individuals/top10-nolist', 1208 [ 1209 'records' => $records, 1210 ] 1211 ); 1212 } 1213 1214 /** 1215 * Find the oldest living females. 1216 * 1217 * @param int $total 1218 * 1219 * @return string 1220 */ 1221 public function topTenOldestFemaleListAlive(int $total = 10): string 1222 { 1223 if (!Auth::isMember($this->tree)) { 1224 return I18N::translate('This information is private and cannot be shown.'); 1225 } 1226 1227 $records = $this->topTenOldestAliveQuery('F', $total); 1228 1229 return view( 1230 'statistics/individuals/top10-list', 1231 [ 1232 'records' => $records, 1233 ] 1234 ); 1235 } 1236 1237 /** 1238 * Find the longest lived living males. 1239 * 1240 * @param int $total 1241 * 1242 * @return string 1243 */ 1244 public function topTenOldestMaleAlive(int $total = 10): string 1245 { 1246 if (!Auth::isMember($this->tree)) { 1247 return I18N::translate('This information is private and cannot be shown.'); 1248 } 1249 1250 $records = $this->topTenOldestAliveQuery('M', $total); 1251 1252 return view( 1253 'statistics/individuals/top10-nolist', 1254 [ 1255 'records' => $records, 1256 ] 1257 ); 1258 } 1259 1260 /** 1261 * Find the longest lived living males. 1262 * 1263 * @param int $total 1264 * 1265 * @return string 1266 */ 1267 public function topTenOldestMaleListAlive(int $total = 10): string 1268 { 1269 if (!Auth::isMember($this->tree)) { 1270 return I18N::translate('This information is private and cannot be shown.'); 1271 } 1272 1273 $records = $this->topTenOldestAliveQuery('M', $total); 1274 1275 return view( 1276 'statistics/individuals/top10-list', 1277 [ 1278 'records' => $records, 1279 ] 1280 ); 1281 } 1282 1283 /** 1284 * Find the average lifespan. 1285 * 1286 * @param string $sex 1287 * @param bool $show_years 1288 * 1289 * @return string 1290 */ 1291 private function averageLifespanQuery(string $sex = 'BOTH', bool $show_years = false): string 1292 { 1293 $prefix = DB::connection()->getTablePrefix(); 1294 1295 $days = (int) $this->birthAndDeathQuery($sex) 1296 ->select(DB::raw('AVG(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')) 1297 ->value('days'); 1298 1299 if ($show_years) { 1300 return $this->calculateAge($days); 1301 } 1302 1303 return I18N::number((int) ($days / 365.25)); 1304 } 1305 1306 /** 1307 * Find the average lifespan. 1308 * 1309 * @param bool $show_years 1310 * 1311 * @return string 1312 */ 1313 public function averageLifespan($show_years = false): string 1314 { 1315 return $this->averageLifespanQuery('BOTH', $show_years); 1316 } 1317 1318 /** 1319 * Find the average lifespan of females. 1320 * 1321 * @param bool $show_years 1322 * 1323 * @return string 1324 */ 1325 public function averageLifespanFemale($show_years = false): string 1326 { 1327 return $this->averageLifespanQuery('F', $show_years); 1328 } 1329 1330 /** 1331 * Find the average male lifespan. 1332 * 1333 * @param bool $show_years 1334 * 1335 * @return string 1336 */ 1337 public function averageLifespanMale($show_years = false): string 1338 { 1339 return $this->averageLifespanQuery('M', $show_years); 1340 } 1341 1342 /** 1343 * Convert totals into percentages. 1344 * 1345 * @param int $count 1346 * @param int $total 1347 * 1348 * @return string 1349 */ 1350 private function getPercentage(int $count, int $total): string 1351 { 1352 return ($total !== 0) ? I18N::percentage($count / $total, 1) : ''; 1353 } 1354 1355 /** 1356 * Returns how many individuals exist in the tree. 1357 * 1358 * @return int 1359 */ 1360 private function totalIndividualsQuery(): int 1361 { 1362 return DB::table('individuals') 1363 ->where('i_file', '=', $this->tree->id()) 1364 ->count(); 1365 } 1366 1367 /** 1368 * Count the number of living individuals. 1369 * 1370 * The totalLiving/totalDeceased queries assume that every dead person will 1371 * have a DEAT record. It will not include individuals who were born more 1372 * than MAX_ALIVE_AGE years ago, and who have no DEAT record. 1373 * A good reason to run the “Add missing DEAT records” batch-update! 1374 * 1375 * @return int 1376 */ 1377 private function totalLivingQuery(): int 1378 { 1379 $query = DB::table('individuals') 1380 ->where('i_file', '=', $this->tree->id()); 1381 1382 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1383 $query->where('i_gedcom', 'NOT LIKE', "%\n1 " . $death_event . '%'); 1384 } 1385 1386 return $query->count(); 1387 } 1388 1389 /** 1390 * Count the number of dead individuals. 1391 * 1392 * @return int 1393 */ 1394 private function totalDeceasedQuery(): int 1395 { 1396 return DB::table('individuals') 1397 ->where('i_file', '=', $this->tree->id()) 1398 ->where(function (Builder $query): void { 1399 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1400 $query->orWhere('i_gedcom', 'LIKE', "%\n1 " . $death_event . '%'); 1401 } 1402 }) 1403 ->count(); 1404 } 1405 1406 /** 1407 * Returns the total count of a specific sex. 1408 * 1409 * @param string $sex The sex to query 1410 * 1411 * @return int 1412 */ 1413 private function getTotalSexQuery(string $sex): int 1414 { 1415 return DB::table('individuals') 1416 ->where('i_file', '=', $this->tree->id()) 1417 ->where('i_sex', '=', $sex) 1418 ->count(); 1419 } 1420 1421 /** 1422 * Returns the total number of males. 1423 * 1424 * @return int 1425 */ 1426 private function totalSexMalesQuery(): int 1427 { 1428 return $this->getTotalSexQuery('M'); 1429 } 1430 1431 /** 1432 * Returns the total number of females. 1433 * 1434 * @return int 1435 */ 1436 private function totalSexFemalesQuery(): int 1437 { 1438 return $this->getTotalSexQuery('F'); 1439 } 1440 1441 /** 1442 * Returns the total number of individuals with unknown sex. 1443 * 1444 * @return int 1445 */ 1446 private function totalSexUnknownQuery(): int 1447 { 1448 return $this->getTotalSexQuery('U'); 1449 } 1450 1451 /** 1452 * Count the total families. 1453 * 1454 * @return int 1455 */ 1456 private function totalFamiliesQuery(): int 1457 { 1458 return DB::table('families') 1459 ->where('f_file', '=', $this->tree->id()) 1460 ->count(); 1461 } 1462 1463 /** 1464 * How many individuals have one or more sources. 1465 * 1466 * @return int 1467 */ 1468 private function totalIndisWithSourcesQuery(): int 1469 { 1470 return DB::table('individuals') 1471 ->select(['i_id']) 1472 ->distinct() 1473 ->join('link', function (JoinClause $join) { 1474 $join->on('i_id', '=', 'l_from') 1475 ->on('i_file', '=', 'l_file'); 1476 }) 1477 ->where('l_file', '=', $this->tree->id()) 1478 ->where('l_type', '=', 'SOUR') 1479 ->count('i_id'); 1480 } 1481 1482 /** 1483 * Count the families with source records. 1484 * 1485 * @return int 1486 */ 1487 private function totalFamsWithSourcesQuery(): int 1488 { 1489 return DB::table('families') 1490 ->select(['f_id']) 1491 ->distinct() 1492 ->join('link', function (JoinClause $join) { 1493 $join->on('f_id', '=', 'l_from') 1494 ->on('f_file', '=', 'l_file'); 1495 }) 1496 ->where('l_file', '=', $this->tree->id()) 1497 ->where('l_type', '=', 'SOUR') 1498 ->count('f_id'); 1499 } 1500 1501 /** 1502 * Count the number of repositories. 1503 * 1504 * @return int 1505 */ 1506 private function totalRepositoriesQuery(): int 1507 { 1508 return DB::table('other') 1509 ->where('o_file', '=', $this->tree->id()) 1510 ->where('o_type', '=', 'REPO') 1511 ->count(); 1512 } 1513 1514 /** 1515 * Count the total number of sources. 1516 * 1517 * @return int 1518 */ 1519 private function totalSourcesQuery(): int 1520 { 1521 return DB::table('sources') 1522 ->where('s_file', '=', $this->tree->id()) 1523 ->count(); 1524 } 1525 1526 /** 1527 * Count the number of notes. 1528 * 1529 * @return int 1530 */ 1531 private function totalNotesQuery(): int 1532 { 1533 return DB::table('other') 1534 ->where('o_file', '=', $this->tree->id()) 1535 ->where('o_type', '=', 'NOTE') 1536 ->count(); 1537 } 1538 1539 /** 1540 * Returns the total number of records. 1541 * 1542 * @return int 1543 */ 1544 private function totalRecordsQuery(): int 1545 { 1546 return $this->totalIndividualsQuery() 1547 + $this->totalFamiliesQuery() 1548 + $this->totalNotesQuery() 1549 + $this->totalRepositoriesQuery() 1550 + $this->totalSourcesQuery(); 1551 } 1552 1553 /** 1554 * @inheritDoc 1555 */ 1556 public function totalRecords(): string 1557 { 1558 return I18N::number($this->totalRecordsQuery()); 1559 } 1560 1561 /** 1562 * @inheritDoc 1563 */ 1564 public function totalIndividuals(): string 1565 { 1566 return I18N::number($this->totalIndividualsQuery()); 1567 } 1568 1569 /** 1570 * Count the number of living individuals. 1571 * 1572 * @return string 1573 */ 1574 public function totalLiving(): string 1575 { 1576 return I18N::number($this->totalLivingQuery()); 1577 } 1578 1579 /** 1580 * Count the number of dead individuals. 1581 * 1582 * @return string 1583 */ 1584 public function totalDeceased(): string 1585 { 1586 return I18N::number($this->totalDeceasedQuery()); 1587 } 1588 1589 /** 1590 * @inheritDoc 1591 */ 1592 public function totalSexMales(): string 1593 { 1594 return I18N::number($this->totalSexMalesQuery()); 1595 } 1596 1597 /** 1598 * @inheritDoc 1599 */ 1600 public function totalSexFemales(): string 1601 { 1602 return I18N::number($this->totalSexFemalesQuery()); 1603 } 1604 1605 /** 1606 * @inheritDoc 1607 */ 1608 public function totalSexUnknown(): string 1609 { 1610 return I18N::number($this->totalSexUnknownQuery()); 1611 } 1612 1613 /** 1614 * @inheritDoc 1615 */ 1616 public function totalFamilies(): string 1617 { 1618 return I18N::number($this->totalFamiliesQuery()); 1619 } 1620 1621 /** 1622 * How many individuals have one or more sources. 1623 * 1624 * @return string 1625 */ 1626 public function totalIndisWithSources(): string 1627 { 1628 return I18N::number($this->totalIndisWithSourcesQuery()); 1629 } 1630 1631 /** 1632 * Count the families with with source records. 1633 * 1634 * @return string 1635 */ 1636 public function totalFamsWithSources(): string 1637 { 1638 return I18N::number($this->totalFamsWithSourcesQuery()); 1639 } 1640 1641 /** 1642 * @inheritDoc 1643 */ 1644 public function totalRepositories(): string 1645 { 1646 return I18N::number($this->totalRepositoriesQuery()); 1647 } 1648 1649 /** 1650 * @inheritDoc 1651 */ 1652 public function totalSources(): string 1653 { 1654 return I18N::number($this->totalSourcesQuery()); 1655 } 1656 1657 /** 1658 * @inheritDoc 1659 */ 1660 public function totalNotes(): string 1661 { 1662 return I18N::number($this->totalNotesQuery()); 1663 } 1664 1665 /** 1666 * @inheritDoc 1667 */ 1668 public function totalIndividualsPercentage(): string 1669 { 1670 return $this->getPercentage( 1671 $this->totalIndividualsQuery(), 1672 $this->totalRecordsQuery() 1673 ); 1674 } 1675 1676 /** 1677 * @inheritDoc 1678 */ 1679 public function totalFamiliesPercentage(): string 1680 { 1681 return $this->getPercentage( 1682 $this->totalFamiliesQuery(), 1683 $this->totalRecordsQuery() 1684 ); 1685 } 1686 1687 /** 1688 * @inheritDoc 1689 */ 1690 public function totalRepositoriesPercentage(): string 1691 { 1692 return $this->getPercentage( 1693 $this->totalRepositoriesQuery(), 1694 $this->totalRecordsQuery() 1695 ); 1696 } 1697 1698 /** 1699 * @inheritDoc 1700 */ 1701 public function totalSourcesPercentage(): string 1702 { 1703 return $this->getPercentage( 1704 $this->totalSourcesQuery(), 1705 $this->totalRecordsQuery() 1706 ); 1707 } 1708 1709 /** 1710 * @inheritDoc 1711 */ 1712 public function totalNotesPercentage(): string 1713 { 1714 return $this->getPercentage( 1715 $this->totalNotesQuery(), 1716 $this->totalRecordsQuery() 1717 ); 1718 } 1719 1720 /** 1721 * @inheritDoc 1722 */ 1723 public function totalLivingPercentage(): string 1724 { 1725 return $this->getPercentage( 1726 $this->totalLivingQuery(), 1727 $this->totalIndividualsQuery() 1728 ); 1729 } 1730 1731 /** 1732 * @inheritDoc 1733 */ 1734 public function totalDeceasedPercentage(): string 1735 { 1736 return $this->getPercentage( 1737 $this->totalDeceasedQuery(), 1738 $this->totalIndividualsQuery() 1739 ); 1740 } 1741 1742 /** 1743 * @inheritDoc 1744 */ 1745 public function totalSexMalesPercentage(): string 1746 { 1747 return $this->getPercentage( 1748 $this->totalSexMalesQuery(), 1749 $this->totalIndividualsQuery() 1750 ); 1751 } 1752 1753 /** 1754 * @inheritDoc 1755 */ 1756 public function totalSexFemalesPercentage(): string 1757 { 1758 return $this->getPercentage( 1759 $this->totalSexFemalesQuery(), 1760 $this->totalIndividualsQuery() 1761 ); 1762 } 1763 1764 /** 1765 * @inheritDoc 1766 */ 1767 public function totalSexUnknownPercentage(): string 1768 { 1769 return $this->getPercentage( 1770 $this->totalSexUnknownQuery(), 1771 $this->totalIndividualsQuery() 1772 ); 1773 } 1774 1775 /** 1776 * Create a chart of common given names. 1777 * 1778 * @param string|null $color_from 1779 * @param string|null $color_to 1780 * @param int $maxtoshow 1781 * 1782 * @return string 1783 */ 1784 public function chartCommonGiven( 1785 string $color_from = null, 1786 string $color_to = null, 1787 int $maxtoshow = 7 1788 ): string { 1789 $tot_indi = $this->totalIndividualsQuery(); 1790 $given = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow); 1791 1792 if (empty($given)) { 1793 return I18N::translate('This information is not available.'); 1794 } 1795 1796 return (new ChartCommonGiven($this->tree)) 1797 ->chartCommonGiven($tot_indi, $given, $color_from, $color_to); 1798 } 1799 1800 /** 1801 * Create a chart of common surnames. 1802 * 1803 * @param string|null $color_from 1804 * @param string|null $color_to 1805 * @param int $number_of_surnames 1806 * 1807 * @return string 1808 */ 1809 public function chartCommonSurnames( 1810 string $color_from = null, 1811 string $color_to = null, 1812 int $number_of_surnames = 10 1813 ): string { 1814 $tot_indi = $this->totalIndividualsQuery(); 1815 $all_surnames = $this->topSurnames($number_of_surnames, 0); 1816 1817 if (empty($all_surnames)) { 1818 return I18N::translate('This information is not available.'); 1819 } 1820 1821 return (new ChartCommonSurname($this->tree)) 1822 ->chartCommonSurnames($tot_indi, $all_surnames, $color_from, $color_to); 1823 } 1824 1825 /** 1826 * Create a chart showing mortality. 1827 * 1828 * @param string|null $color_living 1829 * @param string|null $color_dead 1830 * 1831 * @return string 1832 */ 1833 public function chartMortality(string $color_living = null, string $color_dead = null): string 1834 { 1835 $tot_l = $this->totalLivingQuery(); 1836 $tot_d = $this->totalDeceasedQuery(); 1837 1838 return (new ChartMortality($this->tree)) 1839 ->chartMortality($tot_l, $tot_d, $color_living, $color_dead); 1840 } 1841 1842 /** 1843 * Create a chart showing individuals with/without sources. 1844 * 1845 * @param string|null $color_from 1846 * @param string|null $color_to 1847 * 1848 * @return string 1849 */ 1850 public function chartIndisWithSources( 1851 string $color_from = null, 1852 string $color_to = null 1853 ): string { 1854 $tot_indi = $this->totalIndividualsQuery(); 1855 $tot_indi_source = $this->totalIndisWithSourcesQuery(); 1856 1857 return (new ChartIndividualWithSources($this->tree)) 1858 ->chartIndisWithSources($tot_indi, $tot_indi_source, $color_from, $color_to); 1859 } 1860 1861 /** 1862 * Create a chart of individuals with/without sources. 1863 * 1864 * @param string|null $color_from 1865 * @param string|null $color_to 1866 * 1867 * @return string 1868 */ 1869 public function chartFamsWithSources( 1870 string $color_from = null, 1871 string $color_to = null 1872 ): string { 1873 $tot_fam = $this->totalFamiliesQuery(); 1874 $tot_fam_source = $this->totalFamsWithSourcesQuery(); 1875 1876 return (new ChartFamilyWithSources($this->tree)) 1877 ->chartFamsWithSources($tot_fam, $tot_fam_source, $color_from, $color_to); 1878 } 1879 1880 /** 1881 * @inheritDoc 1882 */ 1883 public function chartSex( 1884 string $color_female = null, 1885 string $color_male = null, 1886 string $color_unknown = null 1887 ): string { 1888 $tot_m = $this->totalSexMalesQuery(); 1889 $tot_f = $this->totalSexFemalesQuery(); 1890 $tot_u = $this->totalSexUnknownQuery(); 1891 1892 return (new ChartSex($this->tree)) 1893 ->chartSex($tot_m, $tot_f, $tot_u, $color_female, $color_male, $color_unknown); 1894 } 1895 1896 /** 1897 * Query individuals, with their births and deaths. 1898 * 1899 * @param string $sex 1900 * 1901 * @return Builder 1902 */ 1903 private function birthAndDeathQuery(string $sex): Builder 1904 { 1905 $query = DB::table('individuals') 1906 ->where('i_file', '=', $this->tree->id()) 1907 ->join('dates AS birth', function (JoinClause $join): void { 1908 $join 1909 ->on('birth.d_file', '=', 'i_file') 1910 ->on('birth.d_gid', '=', 'i_id'); 1911 }) 1912 ->join('dates AS death', function (JoinClause $join): void { 1913 $join 1914 ->on('death.d_file', '=', 'i_file') 1915 ->on('death.d_gid', '=', 'i_id'); 1916 }) 1917 ->where('birth.d_fact', '=', 'BIRT') 1918 ->where('death.d_fact', '=', 'DEAT') 1919 ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2') 1920 ->where('birth.d_julianday2', '<>', 0); 1921 1922 if ($sex === 'M' || $sex === 'F') { 1923 $query->where('i_sex', '=', $sex); 1924 } 1925 1926 return $query; 1927 } 1928} 1929