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 $query = $this->birthAndDeathQuery($sex); 804 805 if ($year1 >= 0 && $year2 >= 0) { 806 $query 807 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 808 ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 809 810 if ($related === 'BIRT') { 811 $query->whereBetween('birth.d_year', [$year1, $year2]); 812 } elseif ($related === 'DEAT') { 813 $query->whereBetween('death.d_year', [$year1, $year2]); 814 } 815 } 816 817 return $query 818 ->select([new Expression(DB::prefix('death.d_julianday2') . ' - ' . DB::prefix('birth.d_julianday1') . ' AS days')]) 819 ->orderBy('days', 'desc') 820 ->get() 821 ->all(); 822 } 823 824 /** 825 * General query on ages. 826 * 827 * @return string 828 */ 829 public function statsAge(): string 830 { 831 return (new ChartAge($this->century_service, $this->tree))->chartAge(); 832 } 833 834 /** 835 * Lifespan 836 * 837 * @param string $type 838 * @param string $sex 839 * 840 * @return string 841 */ 842 private function longlifeQuery(string $type, string $sex): string 843 { 844 $row = $this->birthAndDeathQuery($sex) 845 ->orderBy('days', 'desc') 846 ->select(['individuals.*', new Expression(DB::prefix('death.d_julianday2') . ' - ' . DB::prefix('birth.d_julianday1') . ' AS days')]) 847 ->first(); 848 849 if ($row === null) { 850 return ''; 851 } 852 853 $individual = Registry::individualFactory()->mapper($this->tree)($row); 854 855 if ($type !== 'age' && !$individual->canShow()) { 856 return I18N::translate('This information is private and cannot be shown.'); 857 } 858 859 switch ($type) { 860 default: 861 case 'full': 862 return $individual->formatList(); 863 864 case 'age': 865 return I18N::number((int) ($row->days / 365.25)); 866 867 case 'name': 868 return '<a href="' . e($individual->url()) . '">' . $individual->fullName() . '</a>'; 869 } 870 } 871 872 /** 873 * Find the longest lived individual. 874 * 875 * @return string 876 */ 877 public function longestLife(): string 878 { 879 return $this->longlifeQuery('full', 'BOTH'); 880 } 881 882 /** 883 * Find the age of the longest lived individual. 884 * 885 * @return string 886 */ 887 public function longestLifeAge(): string 888 { 889 return $this->longlifeQuery('age', 'BOTH'); 890 } 891 892 /** 893 * Find the name of the longest lived individual. 894 * 895 * @return string 896 */ 897 public function longestLifeName(): string 898 { 899 return $this->longlifeQuery('name', 'BOTH'); 900 } 901 902 /** 903 * Find the longest lived female. 904 * 905 * @return string 906 */ 907 public function longestLifeFemale(): string 908 { 909 return $this->longlifeQuery('full', 'F'); 910 } 911 912 /** 913 * Find the age of the longest lived female. 914 * 915 * @return string 916 */ 917 public function longestLifeFemaleAge(): string 918 { 919 return $this->longlifeQuery('age', 'F'); 920 } 921 922 /** 923 * Find the name of the longest lived female. 924 * 925 * @return string 926 */ 927 public function longestLifeFemaleName(): string 928 { 929 return $this->longlifeQuery('name', 'F'); 930 } 931 932 /** 933 * Find the longest lived male. 934 * 935 * @return string 936 */ 937 public function longestLifeMale(): string 938 { 939 return $this->longlifeQuery('full', 'M'); 940 } 941 942 /** 943 * Find the age of the longest lived male. 944 * 945 * @return string 946 */ 947 public function longestLifeMaleAge(): string 948 { 949 return $this->longlifeQuery('age', 'M'); 950 } 951 952 /** 953 * Find the name of the longest lived male. 954 * 955 * @return string 956 */ 957 public function longestLifeMaleName(): string 958 { 959 return $this->longlifeQuery('name', 'M'); 960 } 961 962 /** 963 * Returns the calculated age the time of event. 964 * 965 * @param int $days The age from the database record 966 * 967 * @return string 968 */ 969 private function calculateAge(int $days): string 970 { 971 if ($days < 31) { 972 return I18N::plural('%s day', '%s days', $days, I18N::number($days)); 973 } 974 975 if ($days < 365) { 976 $months = (int) ($days / 30.5); 977 return I18N::plural('%s month', '%s months', $months, I18N::number($months)); 978 } 979 980 $years = (int) ($days / 365.25); 981 982 return I18N::plural('%s year', '%s years', $years, I18N::number($years)); 983 } 984 985 /** 986 * Find the oldest individuals. 987 * 988 * @param string $sex 989 * @param int $total 990 * 991 * @return array<array<string,mixed>> 992 */ 993 private function topTenOldestQuery(string $sex, int $total): array 994 { 995 $rows = $this->birthAndDeathQuery($sex) 996 ->groupBy(['i_id', 'i_file']) 997 ->orderBy('days', 'desc') 998 ->select(['individuals.*', new Expression('MAX(' . DB::prefix('death.d_julianday2') . ' - ' . DB::prefix('birth.d_julianday1') . ') AS days')]) 999 ->take($total) 1000 ->get(); 1001 1002 $top10 = []; 1003 foreach ($rows as $row) { 1004 $individual = Registry::individualFactory()->mapper($this->tree)($row); 1005 1006 if ($individual->canShow()) { 1007 $top10[] = [ 1008 'person' => $individual, 1009 'age' => $this->calculateAge((int) $row->days), 1010 ]; 1011 } 1012 } 1013 1014 return $top10; 1015 } 1016 1017 /** 1018 * Find the oldest individuals. 1019 * 1020 * @param int $total 1021 * 1022 * @return string 1023 */ 1024 public function topTenOldest(int $total = 10): string 1025 { 1026 $records = $this->topTenOldestQuery('BOTH', $total); 1027 1028 return view('statistics/individuals/top10-nolist', [ 1029 'records' => $records, 1030 ]); 1031 } 1032 1033 /** 1034 * Find the oldest living individuals. 1035 * 1036 * @param int $total 1037 * 1038 * @return string 1039 */ 1040 public function topTenOldestList(int $total = 10): string 1041 { 1042 $records = $this->topTenOldestQuery('BOTH', $total); 1043 1044 return view('statistics/individuals/top10-list', [ 1045 'records' => $records, 1046 ]); 1047 } 1048 1049 /** 1050 * Find the oldest females. 1051 * 1052 * @param int $total 1053 * 1054 * @return string 1055 */ 1056 public function topTenOldestFemale(int $total = 10): string 1057 { 1058 $records = $this->topTenOldestQuery('F', $total); 1059 1060 return view('statistics/individuals/top10-nolist', [ 1061 'records' => $records, 1062 ]); 1063 } 1064 1065 /** 1066 * Find the oldest living females. 1067 * 1068 * @param int $total 1069 * 1070 * @return string 1071 */ 1072 public function topTenOldestFemaleList(int $total = 10): string 1073 { 1074 $records = $this->topTenOldestQuery('F', $total); 1075 1076 return view('statistics/individuals/top10-list', [ 1077 'records' => $records, 1078 ]); 1079 } 1080 1081 /** 1082 * Find the longest lived males. 1083 * 1084 * @param int $total 1085 * 1086 * @return string 1087 */ 1088 public function topTenOldestMale(int $total = 10): string 1089 { 1090 $records = $this->topTenOldestQuery('M', $total); 1091 1092 return view('statistics/individuals/top10-nolist', [ 1093 'records' => $records, 1094 ]); 1095 } 1096 1097 /** 1098 * Find the longest lived males. 1099 * 1100 * @param int $total 1101 * 1102 * @return string 1103 */ 1104 public function topTenOldestMaleList(int $total = 10): string 1105 { 1106 $records = $this->topTenOldestQuery('M', $total); 1107 1108 return view('statistics/individuals/top10-list', [ 1109 'records' => $records, 1110 ]); 1111 } 1112 1113 /** 1114 * Find the oldest living individuals. 1115 * 1116 * @param string $sex "M", "F" or "BOTH" 1117 * @param int $total 1118 * 1119 * @return array<array<string,mixed>> 1120 */ 1121 private function topTenOldestAliveQuery(string $sex, int $total): array 1122 { 1123 $query = DB::table('dates') 1124 ->join('individuals', static function (JoinClause $join): void { 1125 $join 1126 ->on('i_id', '=', 'd_gid') 1127 ->on('i_file', '=', 'd_file'); 1128 }) 1129 ->where('d_file', '=', $this->tree->id()) 1130 ->where('d_julianday1', '<>', 0) 1131 ->where('d_fact', '=', 'BIRT') 1132 ->where('i_gedcom', 'NOT LIKE', "%\n1 DEAT%") 1133 ->where('i_gedcom', 'NOT LIKE', "%\n1 BURI%") 1134 ->where('i_gedcom', 'NOT LIKE', "%\n1 CREM%"); 1135 1136 if ($sex === 'F' || $sex === 'M') { 1137 $query->where('i_sex', '=', $sex); 1138 } 1139 1140 return $query 1141 ->groupBy(['i_id', 'i_file']) 1142 ->orderBy(new Expression('MIN(d_julianday1)')) 1143 ->select(['individuals.*']) 1144 ->take($total) 1145 ->get() 1146 ->map(Registry::individualFactory()->mapper($this->tree)) 1147 ->filter(GedcomRecord::accessFilter()) 1148 ->map(fn (Individual $individual): array => [ 1149 'person' => $individual, 1150 'age' => $this->calculateAge(Registry::timestampFactory()->now()->julianDay() - $individual->getBirthDate()->minimumJulianDay()), 1151 ]) 1152 ->all(); 1153 } 1154 1155 /** 1156 * Find the oldest living individuals. 1157 * 1158 * @param int $total 1159 * 1160 * @return string 1161 */ 1162 public function topTenOldestAlive(int $total = 10): string 1163 { 1164 if (!Auth::isMember($this->tree)) { 1165 return I18N::translate('This information is private and cannot be shown.'); 1166 } 1167 1168 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1169 1170 return view('statistics/individuals/top10-nolist', [ 1171 'records' => $records, 1172 ]); 1173 } 1174 1175 /** 1176 * Find the oldest living individuals. 1177 * 1178 * @param int $total 1179 * 1180 * @return string 1181 */ 1182 public function topTenOldestListAlive(int $total = 10): string 1183 { 1184 if (!Auth::isMember($this->tree)) { 1185 return I18N::translate('This information is private and cannot be shown.'); 1186 } 1187 1188 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1189 1190 return view('statistics/individuals/top10-list', [ 1191 'records' => $records, 1192 ]); 1193 } 1194 1195 /** 1196 * Find the oldest living females. 1197 * 1198 * @param int $total 1199 * 1200 * @return string 1201 */ 1202 public function topTenOldestFemaleAlive(int $total = 10): string 1203 { 1204 if (!Auth::isMember($this->tree)) { 1205 return I18N::translate('This information is private and cannot be shown.'); 1206 } 1207 1208 $records = $this->topTenOldestAliveQuery('F', $total); 1209 1210 return view('statistics/individuals/top10-nolist', [ 1211 'records' => $records, 1212 ]); 1213 } 1214 1215 /** 1216 * Find the oldest living females. 1217 * 1218 * @param int $total 1219 * 1220 * @return string 1221 */ 1222 public function topTenOldestFemaleListAlive(int $total = 10): string 1223 { 1224 if (!Auth::isMember($this->tree)) { 1225 return I18N::translate('This information is private and cannot be shown.'); 1226 } 1227 1228 $records = $this->topTenOldestAliveQuery('F', $total); 1229 1230 return view('statistics/individuals/top10-list', [ 1231 'records' => $records, 1232 ]); 1233 } 1234 1235 /** 1236 * Find the longest lived living males. 1237 * 1238 * @param int $total 1239 * 1240 * @return string 1241 */ 1242 public function topTenOldestMaleAlive(int $total = 10): string 1243 { 1244 if (!Auth::isMember($this->tree)) { 1245 return I18N::translate('This information is private and cannot be shown.'); 1246 } 1247 1248 $records = $this->topTenOldestAliveQuery('M', $total); 1249 1250 return view('statistics/individuals/top10-nolist', [ 1251 'records' => $records, 1252 ]); 1253 } 1254 1255 /** 1256 * Find the longest lived living males. 1257 * 1258 * @param int $total 1259 * 1260 * @return string 1261 */ 1262 public function topTenOldestMaleListAlive(int $total = 10): string 1263 { 1264 if (!Auth::isMember($this->tree)) { 1265 return I18N::translate('This information is private and cannot be shown.'); 1266 } 1267 1268 $records = $this->topTenOldestAliveQuery('M', $total); 1269 1270 return view('statistics/individuals/top10-list', [ 1271 'records' => $records, 1272 ]); 1273 } 1274 1275 /** 1276 * Find the average lifespan. 1277 * 1278 * @param string $sex "M", "F" or "BOTH" 1279 * @param bool $show_years 1280 * 1281 * @return string 1282 */ 1283 private function averageLifespanQuery(string $sex, bool $show_years): string 1284 { 1285 $days = (int) $this->birthAndDeathQuery($sex) 1286 ->select([new Expression('AVG(' . DB::prefix('death.d_julianday2') . ' - ' . DB::prefix('birth.d_julianday1') . ') AS days')]) 1287 ->value('days'); 1288 1289 if ($show_years) { 1290 return $this->calculateAge($days); 1291 } 1292 1293 return I18N::number((int) ($days / 365.25)); 1294 } 1295 1296 /** 1297 * Find the average lifespan. 1298 * 1299 * @param bool $show_years 1300 * 1301 * @return string 1302 */ 1303 public function averageLifespan(bool $show_years): string 1304 { 1305 return $this->averageLifespanQuery('BOTH', $show_years); 1306 } 1307 1308 /** 1309 * Find the average lifespan of females. 1310 * 1311 * @param bool $show_years 1312 * 1313 * @return string 1314 */ 1315 public function averageLifespanFemale(bool $show_years): string 1316 { 1317 return $this->averageLifespanQuery('F', $show_years); 1318 } 1319 1320 /** 1321 * Find the average male lifespan. 1322 * 1323 * @param bool $show_years 1324 * 1325 * @return string 1326 */ 1327 public function averageLifespanMale(bool $show_years): string 1328 { 1329 return $this->averageLifespanQuery('M', $show_years); 1330 } 1331 1332 /** 1333 * Convert totals into percentages. 1334 * 1335 * @param int $count 1336 * @param int $total 1337 * 1338 * @return string 1339 */ 1340 private function getPercentage(int $count, int $total): string 1341 { 1342 return $total !== 0 ? I18N::percentage($count / $total, 1) : ''; 1343 } 1344 1345 /** 1346 * Returns how many individuals exist in the tree. 1347 * 1348 * @return int 1349 */ 1350 private function totalIndividualsQuery(): int 1351 { 1352 return DB::table('individuals') 1353 ->where('i_file', '=', $this->tree->id()) 1354 ->count(); 1355 } 1356 1357 /** 1358 * Count the number of living individuals. 1359 * 1360 * The totalLiving/totalDeceased queries assume that every dead person will 1361 * have a DEAT record. It will not include individuals who were born more 1362 * than MAX_ALIVE_AGE years ago, and who have no DEAT record. 1363 * A good reason to run the “Add missing DEAT records” batch-update! 1364 * 1365 * @return int 1366 */ 1367 private function totalLivingQuery(): int 1368 { 1369 $query = DB::table('individuals') 1370 ->where('i_file', '=', $this->tree->id()); 1371 1372 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1373 $query->where('i_gedcom', 'NOT LIKE', "%\n1 " . $death_event . '%'); 1374 } 1375 1376 return $query->count(); 1377 } 1378 1379 /** 1380 * Count the number of dead individuals. 1381 * 1382 * @return int 1383 */ 1384 private function totalDeceasedQuery(): int 1385 { 1386 return DB::table('individuals') 1387 ->where('i_file', '=', $this->tree->id()) 1388 ->where(static function (Builder $query): void { 1389 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1390 $query->orWhere('i_gedcom', 'LIKE', "%\n1 " . $death_event . '%'); 1391 } 1392 }) 1393 ->count(); 1394 } 1395 1396 /** 1397 * Returns the total count of a specific sex. 1398 * 1399 * @param string $sex The sex to query 1400 * 1401 * @return int 1402 */ 1403 private function getTotalSexQuery(string $sex): int 1404 { 1405 return DB::table('individuals') 1406 ->where('i_file', '=', $this->tree->id()) 1407 ->where('i_sex', '=', $sex) 1408 ->count(); 1409 } 1410 1411 /** 1412 * Returns the total number of males. 1413 * 1414 * @return int 1415 */ 1416 private function totalSexMalesQuery(): int 1417 { 1418 return $this->getTotalSexQuery('M'); 1419 } 1420 1421 /** 1422 * Returns the total number of females. 1423 * 1424 * @return int 1425 */ 1426 private function totalSexFemalesQuery(): int 1427 { 1428 return $this->getTotalSexQuery('F'); 1429 } 1430 1431 /** 1432 * Returns the total number of individuals with unknown sex. 1433 * 1434 * @return int 1435 */ 1436 private function totalSexUnknownQuery(): int 1437 { 1438 return $this->getTotalSexQuery('U'); 1439 } 1440 1441 /** 1442 * Count the total families. 1443 * 1444 * @return int 1445 */ 1446 private function totalFamiliesQuery(): int 1447 { 1448 return DB::table('families') 1449 ->where('f_file', '=', $this->tree->id()) 1450 ->count(); 1451 } 1452 1453 /** 1454 * How many individuals have one or more sources. 1455 * 1456 * @return int 1457 */ 1458 private function totalIndisWithSourcesQuery(): int 1459 { 1460 return DB::table('individuals') 1461 ->select(['i_id']) 1462 ->distinct() 1463 ->join('link', static function (JoinClause $join): void { 1464 $join->on('i_id', '=', 'l_from') 1465 ->on('i_file', '=', 'l_file'); 1466 }) 1467 ->where('l_file', '=', $this->tree->id()) 1468 ->where('l_type', '=', 'SOUR') 1469 ->count('i_id'); 1470 } 1471 1472 /** 1473 * Count the families with source records. 1474 * 1475 * @return int 1476 */ 1477 private function totalFamsWithSourcesQuery(): int 1478 { 1479 return DB::table('families') 1480 ->select(['f_id']) 1481 ->distinct() 1482 ->join('link', static function (JoinClause $join): void { 1483 $join->on('f_id', '=', 'l_from') 1484 ->on('f_file', '=', 'l_file'); 1485 }) 1486 ->where('l_file', '=', $this->tree->id()) 1487 ->where('l_type', '=', 'SOUR') 1488 ->count('f_id'); 1489 } 1490 1491 /** 1492 * Count the number of repositories. 1493 * 1494 * @return int 1495 */ 1496 private function totalRepositoriesQuery(): int 1497 { 1498 return DB::table('other') 1499 ->where('o_file', '=', $this->tree->id()) 1500 ->where('o_type', '=', 'REPO') 1501 ->count(); 1502 } 1503 1504 /** 1505 * Count the total number of sources. 1506 * 1507 * @return int 1508 */ 1509 private function totalSourcesQuery(): int 1510 { 1511 return DB::table('sources') 1512 ->where('s_file', '=', $this->tree->id()) 1513 ->count(); 1514 } 1515 1516 /** 1517 * Count the number of notes. 1518 * 1519 * @return int 1520 */ 1521 private function totalNotesQuery(): int 1522 { 1523 return DB::table('other') 1524 ->where('o_file', '=', $this->tree->id()) 1525 ->where('o_type', '=', 'NOTE') 1526 ->count(); 1527 } 1528 1529 /** 1530 * Count the total media. 1531 * 1532 * @return int 1533 */ 1534 private function totalMediaQuery(): int 1535 { 1536 return DB::table('media') 1537 ->where('m_file', '=', $this->tree->id()) 1538 ->count(); 1539 } 1540 1541 /** 1542 * Returns the total number of records. 1543 * 1544 * @return int 1545 */ 1546 private function totalRecordsQuery(): int 1547 { 1548 return $this->totalIndividualsQuery() 1549 + $this->totalFamiliesQuery() 1550 + $this->totalMediaQuery() 1551 + $this->totalNotesQuery() 1552 + $this->totalRepositoriesQuery() 1553 + $this->totalSourcesQuery(); 1554 } 1555 1556 /** 1557 * @return string 1558 */ 1559 public function totalRecords(): string 1560 { 1561 return I18N::number($this->totalRecordsQuery()); 1562 } 1563 1564 /** 1565 * @return string 1566 */ 1567 public function totalIndividuals(): string 1568 { 1569 return I18N::number($this->totalIndividualsQuery()); 1570 } 1571 1572 /** 1573 * Count the number of living individuals. 1574 * 1575 * @return string 1576 */ 1577 public function totalLiving(): string 1578 { 1579 return I18N::number($this->totalLivingQuery()); 1580 } 1581 1582 /** 1583 * Count the number of dead individuals. 1584 * 1585 * @return string 1586 */ 1587 public function totalDeceased(): string 1588 { 1589 return I18N::number($this->totalDeceasedQuery()); 1590 } 1591 1592 /** 1593 * @return string 1594 */ 1595 public function totalSexMales(): string 1596 { 1597 return I18N::number($this->totalSexMalesQuery()); 1598 } 1599 1600 /** 1601 * @return string 1602 */ 1603 public function totalSexFemales(): string 1604 { 1605 return I18N::number($this->totalSexFemalesQuery()); 1606 } 1607 1608 /** 1609 * @return string 1610 */ 1611 public function totalSexUnknown(): string 1612 { 1613 return I18N::number($this->totalSexUnknownQuery()); 1614 } 1615 1616 /** 1617 * @return string 1618 */ 1619 public function totalFamilies(): string 1620 { 1621 return I18N::number($this->totalFamiliesQuery()); 1622 } 1623 1624 /** 1625 * How many individuals have one or more sources. 1626 * 1627 * @return string 1628 */ 1629 public function totalIndisWithSources(): string 1630 { 1631 return I18N::number($this->totalIndisWithSourcesQuery()); 1632 } 1633 1634 /** 1635 * Count the families with with source records. 1636 * 1637 * @return string 1638 */ 1639 public function totalFamsWithSources(): string 1640 { 1641 return I18N::number($this->totalFamsWithSourcesQuery()); 1642 } 1643 1644 /** 1645 * @return string 1646 */ 1647 public function totalRepositories(): string 1648 { 1649 return I18N::number($this->totalRepositoriesQuery()); 1650 } 1651 1652 /** 1653 * @return string 1654 */ 1655 public function totalSources(): string 1656 { 1657 return I18N::number($this->totalSourcesQuery()); 1658 } 1659 1660 /** 1661 * @return string 1662 */ 1663 public function totalNotes(): string 1664 { 1665 return I18N::number($this->totalNotesQuery()); 1666 } 1667 1668 /** 1669 * @return string 1670 */ 1671 public function totalIndividualsPercentage(): string 1672 { 1673 return $this->getPercentage( 1674 $this->totalIndividualsQuery(), 1675 $this->totalRecordsQuery() 1676 ); 1677 } 1678 1679 /** 1680 * @return string 1681 */ 1682 public function totalIndisWithSourcesPercentage(): string 1683 { 1684 return $this->getPercentage( 1685 $this->totalIndisWithSourcesQuery(), 1686 $this->totalIndividualsQuery() 1687 ); 1688 } 1689 1690 /** 1691 * @return string 1692 */ 1693 public function totalFamiliesPercentage(): string 1694 { 1695 return $this->getPercentage( 1696 $this->totalFamiliesQuery(), 1697 $this->totalRecordsQuery() 1698 ); 1699 } 1700 1701 /** 1702 * @return string 1703 */ 1704 public function totalFamsWithSourcesPercentage(): string 1705 { 1706 return $this->getPercentage( 1707 $this->totalFamsWithSourcesQuery(), 1708 $this->totalFamiliesQuery() 1709 ); 1710 } 1711 1712 /** 1713 * @return string 1714 */ 1715 public function totalRepositoriesPercentage(): string 1716 { 1717 return $this->getPercentage( 1718 $this->totalRepositoriesQuery(), 1719 $this->totalRecordsQuery() 1720 ); 1721 } 1722 1723 /** 1724 * @return string 1725 */ 1726 public function totalSourcesPercentage(): string 1727 { 1728 return $this->getPercentage( 1729 $this->totalSourcesQuery(), 1730 $this->totalRecordsQuery() 1731 ); 1732 } 1733 1734 /** 1735 * @return string 1736 */ 1737 public function totalNotesPercentage(): string 1738 { 1739 return $this->getPercentage( 1740 $this->totalNotesQuery(), 1741 $this->totalRecordsQuery() 1742 ); 1743 } 1744 1745 /** 1746 * @return string 1747 */ 1748 public function totalLivingPercentage(): string 1749 { 1750 return $this->getPercentage( 1751 $this->totalLivingQuery(), 1752 $this->totalIndividualsQuery() 1753 ); 1754 } 1755 1756 /** 1757 * @return string 1758 */ 1759 public function totalDeceasedPercentage(): string 1760 { 1761 return $this->getPercentage( 1762 $this->totalDeceasedQuery(), 1763 $this->totalIndividualsQuery() 1764 ); 1765 } 1766 1767 /** 1768 * @return string 1769 */ 1770 public function totalSexMalesPercentage(): string 1771 { 1772 return $this->getPercentage( 1773 $this->totalSexMalesQuery(), 1774 $this->totalIndividualsQuery() 1775 ); 1776 } 1777 1778 /** 1779 * @return string 1780 */ 1781 public function totalSexFemalesPercentage(): string 1782 { 1783 return $this->getPercentage( 1784 $this->totalSexFemalesQuery(), 1785 $this->totalIndividualsQuery() 1786 ); 1787 } 1788 1789 /** 1790 * @return string 1791 */ 1792 public function totalSexUnknownPercentage(): string 1793 { 1794 return $this->getPercentage( 1795 $this->totalSexUnknownQuery(), 1796 $this->totalIndividualsQuery() 1797 ); 1798 } 1799 1800 /** 1801 * Create a chart of common given names. 1802 * 1803 * @param string|null $color_from 1804 * @param string|null $color_to 1805 * @param int $maxtoshow 1806 * 1807 * @return string 1808 */ 1809 public function chartCommonGiven( 1810 string|null $color_from = null, 1811 string|null $color_to = null, 1812 int $maxtoshow = 7 1813 ): string { 1814 $tot_indi = $this->totalIndividualsQuery(); 1815 $given = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow); 1816 1817 if ($given === []) { 1818 return I18N::translate('This information is not available.'); 1819 } 1820 1821 return (new ChartCommonGiven($this->color_service)) 1822 ->chartCommonGiven($tot_indi, $given, $color_from, $color_to); 1823 } 1824 1825 /** 1826 * Create a chart of common surnames. 1827 * 1828 * @param string|null $color_from 1829 * @param string|null $color_to 1830 * @param int $number_of_surnames 1831 * 1832 * @return string 1833 */ 1834 public function chartCommonSurnames( 1835 string|null $color_from = null, 1836 string|null $color_to = null, 1837 int $number_of_surnames = 10 1838 ): string { 1839 $tot_indi = $this->totalIndividualsQuery(); 1840 $all_surnames = $this->topSurnames($number_of_surnames, 0); 1841 1842 if ($all_surnames === []) { 1843 return I18N::translate('This information is not available.'); 1844 } 1845 1846 $surname_tradition = Registry::surnameTraditionFactory() 1847 ->make($this->tree->getPreference('SURNAME_TRADITION')); 1848 1849 return (new ChartCommonSurname($this->color_service, $surname_tradition)) 1850 ->chartCommonSurnames($tot_indi, $all_surnames, $color_from, $color_to); 1851 } 1852 1853 /** 1854 * Create a chart showing mortality. 1855 * 1856 * @param string|null $color_living 1857 * @param string|null $color_dead 1858 * 1859 * @return string 1860 */ 1861 public function chartMortality(string|null $color_living = null, string|null $color_dead = null): string 1862 { 1863 $tot_l = $this->totalLivingQuery(); 1864 $tot_d = $this->totalDeceasedQuery(); 1865 1866 return (new ChartMortality($this->color_service)) 1867 ->chartMortality($tot_l, $tot_d, $color_living, $color_dead); 1868 } 1869 1870 /** 1871 * Create a chart showing individuals with/without sources. 1872 * 1873 * @param string|null $color_from 1874 * @param string|null $color_to 1875 * 1876 * @return string 1877 */ 1878 public function chartIndisWithSources( 1879 string|null $color_from = null, 1880 string|null $color_to = null 1881 ): string { 1882 $tot_indi = $this->totalIndividualsQuery(); 1883 $tot_indi_source = $this->totalIndisWithSourcesQuery(); 1884 1885 return (new ChartIndividualWithSources($this->color_service)) 1886 ->chartIndisWithSources($tot_indi, $tot_indi_source, $color_from, $color_to); 1887 } 1888 1889 /** 1890 * Create a chart of individuals with/without sources. 1891 * 1892 * @param string|null $color_from 1893 * @param string|null $color_to 1894 * 1895 * @return string 1896 */ 1897 public function chartFamsWithSources( 1898 string|null $color_from = null, 1899 string|null $color_to = null 1900 ): string { 1901 $tot_fam = $this->totalFamiliesQuery(); 1902 $tot_fam_source = $this->totalFamsWithSourcesQuery(); 1903 1904 return (new ChartFamilyWithSources($this->color_service)) 1905 ->chartFamsWithSources($tot_fam, $tot_fam_source, $color_from, $color_to); 1906 } 1907 1908 /** 1909 * @param string|null $color_female 1910 * @param string|null $color_male 1911 * @param string|null $color_unknown 1912 * 1913 * @return string 1914 */ 1915 public function chartSex( 1916 string|null $color_female = null, 1917 string|null $color_male = null, 1918 string|null $color_unknown = null 1919 ): string { 1920 $tot_m = $this->totalSexMalesQuery(); 1921 $tot_f = $this->totalSexFemalesQuery(); 1922 $tot_u = $this->totalSexUnknownQuery(); 1923 1924 return (new ChartSex()) 1925 ->chartSex($tot_m, $tot_f, $tot_u, $color_female, $color_male, $color_unknown); 1926 } 1927 1928 /** 1929 * Query individuals, with their births and deaths. 1930 * 1931 * @param string $sex 1932 * 1933 * @return Builder 1934 */ 1935 private function birthAndDeathQuery(string $sex): Builder 1936 { 1937 $query = DB::table('individuals') 1938 ->where('i_file', '=', $this->tree->id()) 1939 ->join('dates AS birth', static function (JoinClause $join): void { 1940 $join 1941 ->on('birth.d_file', '=', 'i_file') 1942 ->on('birth.d_gid', '=', 'i_id'); 1943 }) 1944 ->join('dates AS death', static function (JoinClause $join): void { 1945 $join 1946 ->on('death.d_file', '=', 'i_file') 1947 ->on('death.d_gid', '=', 'i_id'); 1948 }) 1949 ->where('birth.d_fact', '=', 'BIRT') 1950 ->where('death.d_fact', '=', 'DEAT') 1951 ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2') 1952 ->where('birth.d_julianday2', '<>', 0); 1953 1954 if ($sex === 'M' || $sex === 'F') { 1955 $query->where('i_sex', '=', $sex); 1956 } 1957 1958 return $query; 1959 } 1960} 1961