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