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