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