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