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