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