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