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