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