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