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