1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2021 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\Carbon; 24use Fisharebest\Webtrees\Gedcom; 25use Fisharebest\Webtrees\GedcomRecord; 26use Fisharebest\Webtrees\I18N; 27use Fisharebest\Webtrees\Individual; 28use Fisharebest\Webtrees\Module\IndividualListModule; 29use Fisharebest\Webtrees\Module\ModuleInterface; 30use Fisharebest\Webtrees\Module\ModuleListInterface; 31use Fisharebest\Webtrees\Registry; 32use Fisharebest\Webtrees\Services\ModuleService; 33use Fisharebest\Webtrees\Statistics\Google\ChartAge; 34use Fisharebest\Webtrees\Statistics\Google\ChartBirth; 35use Fisharebest\Webtrees\Statistics\Google\ChartCommonGiven; 36use Fisharebest\Webtrees\Statistics\Google\ChartCommonSurname; 37use Fisharebest\Webtrees\Statistics\Google\ChartDeath; 38use Fisharebest\Webtrees\Statistics\Google\ChartFamilyWithSources; 39use Fisharebest\Webtrees\Statistics\Google\ChartIndividualWithSources; 40use Fisharebest\Webtrees\Statistics\Google\ChartMortality; 41use Fisharebest\Webtrees\Statistics\Google\ChartSex; 42use Fisharebest\Webtrees\Statistics\Repository\Interfaces\IndividualRepositoryInterface; 43use Fisharebest\Webtrees\Statistics\Service\CenturyService; 44use Fisharebest\Webtrees\Statistics\Service\ColorService; 45use Fisharebest\Webtrees\SurnameTradition; 46use Fisharebest\Webtrees\Tree; 47use Illuminate\Database\Capsule\Manager as DB; 48use Illuminate\Database\Query\Builder; 49use Illuminate\Database\Query\Expression; 50use Illuminate\Database\Query\JoinClause; 51 52use function app; 53use function array_key_exists; 54use function array_keys; 55use function array_reverse; 56use function array_shift; 57use function array_slice; 58use function array_walk; 59use function arsort; 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)) { 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 ->all(); 536 } 537 538 return $surnames; 539 } 540 541 /** 542 * Find common surnames. 543 * 544 * @return string 545 */ 546 public function getCommonSurname(): string 547 { 548 $top_surname = $this->topSurnames(1, 0); 549 550 return implode(', ', array_keys(array_shift($top_surname) ?? [])); 551 } 552 553 /** 554 * Find common surnames. 555 * 556 * @param string $type 557 * @param bool $show_tot 558 * @param int $threshold 559 * @param int $number_of_surnames 560 * @param string $sorting 561 * 562 * @return string 563 */ 564 private function commonSurnamesQuery( 565 string $type, 566 bool $show_tot, 567 int $threshold, 568 int $number_of_surnames, 569 string $sorting 570 ): string { 571 $surnames = $this->topSurnames($number_of_surnames, $threshold); 572 573 switch ($sorting) { 574 default: 575 case 'alpha': 576 uksort($surnames, I18N::comparator()); 577 break; 578 case 'count': 579 break; 580 case 'rcount': 581 $surnames = array_reverse($surnames, true); 582 break; 583 } 584 585 //find a module providing individual lists 586 $module = app(ModuleService::class)->findByComponent(ModuleListInterface::class, $this->tree, Auth::user())->first(static function (ModuleInterface $module): bool { 587 return $module instanceof IndividualListModule; 588 }); 589 590 if ($type === 'list') { 591 return view('lists/surnames-bullet-list', [ 592 'surnames' => $surnames, 593 'module' => $module, 594 'totals' => $show_tot, 595 'tree' => $this->tree, 596 ]); 597 } 598 599 return view('lists/surnames-compact-list', [ 600 'surnames' => $surnames, 601 'module' => $module, 602 'totals' => $show_tot, 603 'tree' => $this->tree, 604 ]); 605 } 606 607 /** 608 * Find common surnames. 609 * 610 * @param int $threshold 611 * @param int $number_of_surnames 612 * @param string $sorting 613 * 614 * @return string 615 */ 616 public function commonSurnames( 617 int $threshold = 1, 618 int $number_of_surnames = 10, 619 string $sorting = 'alpha' 620 ): string { 621 return $this->commonSurnamesQuery('nolist', false, $threshold, $number_of_surnames, $sorting); 622 } 623 624 /** 625 * Find common surnames. 626 * 627 * @param int $threshold 628 * @param int $number_of_surnames 629 * @param string $sorting 630 * 631 * @return string 632 */ 633 public function commonSurnamesTotals( 634 int $threshold = 1, 635 int $number_of_surnames = 10, 636 string $sorting = 'count' 637 ): string { 638 return $this->commonSurnamesQuery('nolist', true, $threshold, $number_of_surnames, $sorting); 639 } 640 641 /** 642 * Find common surnames. 643 * 644 * @param int $threshold 645 * @param int $number_of_surnames 646 * @param string $sorting 647 * 648 * @return string 649 */ 650 public function commonSurnamesList( 651 int $threshold = 1, 652 int $number_of_surnames = 10, 653 string $sorting = 'alpha' 654 ): string { 655 return $this->commonSurnamesQuery('list', false, $threshold, $number_of_surnames, $sorting); 656 } 657 658 /** 659 * Find common surnames. 660 * 661 * @param int $threshold 662 * @param int $number_of_surnames 663 * @param string $sorting 664 * 665 * @return string 666 */ 667 public function commonSurnamesListTotals( 668 int $threshold = 1, 669 int $number_of_surnames = 10, 670 string $sorting = 'count' 671 ): string { 672 return $this->commonSurnamesQuery('list', true, $threshold, $number_of_surnames, $sorting); 673 } 674 675 /** 676 * Get a count of births by month. 677 * 678 * @param int $year1 679 * @param int $year2 680 * 681 * @return Builder 682 */ 683 public function statsBirthQuery(int $year1 = -1, int $year2 = -1): Builder 684 { 685 $query = DB::table('dates') 686 ->select(['d_month', new Expression('COUNT(*) AS total')]) 687 ->where('d_file', '=', $this->tree->id()) 688 ->where('d_fact', '=', 'BIRT') 689 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 690 ->groupBy(['d_month']); 691 692 if ($year1 >= 0 && $year2 >= 0) { 693 $query->whereBetween('d_year', [$year1, $year2]); 694 } 695 696 return $query; 697 } 698 699 /** 700 * Get a count of births by month. 701 * 702 * @param int $year1 703 * @param int $year2 704 * 705 * @return Builder 706 */ 707 public function statsBirthBySexQuery(int $year1 = -1, int $year2 = -1): Builder 708 { 709 return $this->statsBirthQuery($year1, $year2) 710 ->select(['d_month', 'i_sex', new Expression('COUNT(*) AS total')]) 711 ->join('individuals', static function (JoinClause $join): void { 712 $join 713 ->on('i_id', '=', 'd_gid') 714 ->on('i_file', '=', 'd_file'); 715 }) 716 ->groupBy(['i_sex']); 717 } 718 719 /** 720 * General query on births. 721 * 722 * @param string|null $color_from 723 * @param string|null $color_to 724 * 725 * @return string 726 */ 727 public function statsBirth(string $color_from = null, string $color_to = null): string 728 { 729 return (new ChartBirth($this->century_service, $this->color_service, $this->tree)) 730 ->chartBirth($color_from, $color_to); 731 } 732 733 /** 734 * Get a list of death dates. 735 * 736 * @param int $year1 737 * @param int $year2 738 * 739 * @return Builder 740 */ 741 public function statsDeathQuery(int $year1 = -1, int $year2 = -1): Builder 742 { 743 $query = DB::table('dates') 744 ->select(['d_month', new Expression('COUNT(*) AS total')]) 745 ->where('d_file', '=', $this->tree->id()) 746 ->where('d_fact', '=', 'DEAT') 747 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 748 ->groupBy(['d_month']); 749 750 if ($year1 >= 0 && $year2 >= 0) { 751 $query->whereBetween('d_year', [$year1, $year2]); 752 } 753 754 return $query; 755 } 756 757 /** 758 * Get a list of death dates. 759 * 760 * @param int $year1 761 * @param int $year2 762 * 763 * @return Builder 764 */ 765 public function statsDeathBySexQuery(int $year1 = -1, int $year2 = -1): Builder 766 { 767 return $this->statsDeathQuery($year1, $year2) 768 ->select(['d_month', 'i_sex', new Expression('COUNT(*) AS total')]) 769 ->join('individuals', static function (JoinClause $join): void { 770 $join 771 ->on('i_id', '=', 'd_gid') 772 ->on('i_file', '=', 'd_file'); 773 }) 774 ->groupBy(['i_sex']); 775 } 776 777 /** 778 * General query on deaths. 779 * 780 * @param string|null $color_from 781 * @param string|null $color_to 782 * 783 * @return string 784 */ 785 public function statsDeath(string $color_from = null, string $color_to = null): string 786 { 787 return (new ChartDeath($this->century_service, $this->color_service, $this->tree)) 788 ->chartDeath($color_from, $color_to); 789 } 790 791 /** 792 * General query on ages. 793 * 794 * @param string $related 795 * @param string $sex 796 * @param int $year1 797 * @param int $year2 798 * 799 * @return array<object> 800 */ 801 public function statsAgeQuery(string $related = 'BIRT', string $sex = 'BOTH', int $year1 = -1, int $year2 = -1): array 802 { 803 $prefix = DB::connection()->getTablePrefix(); 804 805 $query = $this->birthAndDeathQuery($sex); 806 807 if ($year1 >= 0 && $year2 >= 0) { 808 $query 809 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 810 ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 811 812 if ($related === 'BIRT') { 813 $query->whereBetween('birth.d_year', [$year1, $year2]); 814 } elseif ($related === 'DEAT') { 815 $query->whereBetween('death.d_year', [$year1, $year2]); 816 } 817 } 818 819 return $query 820 ->select(new Expression($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')) 821 ->orderBy('days', 'desc') 822 ->get() 823 ->all(); 824 } 825 826 /** 827 * General query on ages. 828 * 829 * @return string 830 */ 831 public function statsAge(): string 832 { 833 return (new ChartAge($this->century_service, $this->tree))->chartAge(); 834 } 835 836 /** 837 * Lifespan 838 * 839 * @param string $type 840 * @param string $sex 841 * 842 * @return string 843 */ 844 private function longlifeQuery(string $type, string $sex): string 845 { 846 $prefix = DB::connection()->getTablePrefix(); 847 848 $row = $this->birthAndDeathQuery($sex) 849 ->orderBy('days', 'desc') 850 ->select(['individuals.*', new Expression($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')]) 851 ->first(); 852 853 if ($row === null) { 854 return ''; 855 } 856 857 /** @var Individual $individual */ 858 $individual = Registry::individualFactory()->mapper($this->tree)($row); 859 860 if ($type !== 'age' && !$individual->canShow()) { 861 return I18N::translate('This information is private and cannot be shown.'); 862 } 863 864 switch ($type) { 865 default: 866 case 'full': 867 return $individual->formatList(); 868 869 case 'age': 870 return I18N::number((int) ($row->days / 365.25)); 871 872 case 'name': 873 return '<a href="' . e($individual->url()) . '">' . $individual->fullName() . '</a>'; 874 } 875 } 876 877 /** 878 * Find the longest lived individual. 879 * 880 * @return string 881 */ 882 public function longestLife(): string 883 { 884 return $this->longlifeQuery('full', 'BOTH'); 885 } 886 887 /** 888 * Find the age of the longest lived individual. 889 * 890 * @return string 891 */ 892 public function longestLifeAge(): string 893 { 894 return $this->longlifeQuery('age', 'BOTH'); 895 } 896 897 /** 898 * Find the name of the longest lived individual. 899 * 900 * @return string 901 */ 902 public function longestLifeName(): string 903 { 904 return $this->longlifeQuery('name', 'BOTH'); 905 } 906 907 /** 908 * Find the longest lived female. 909 * 910 * @return string 911 */ 912 public function longestLifeFemale(): string 913 { 914 return $this->longlifeQuery('full', 'F'); 915 } 916 917 /** 918 * Find the age of the longest lived female. 919 * 920 * @return string 921 */ 922 public function longestLifeFemaleAge(): string 923 { 924 return $this->longlifeQuery('age', 'F'); 925 } 926 927 /** 928 * Find the name of the longest lived female. 929 * 930 * @return string 931 */ 932 public function longestLifeFemaleName(): string 933 { 934 return $this->longlifeQuery('name', 'F'); 935 } 936 937 /** 938 * Find the longest lived male. 939 * 940 * @return string 941 */ 942 public function longestLifeMale(): string 943 { 944 return $this->longlifeQuery('full', 'M'); 945 } 946 947 /** 948 * Find the age of the longest lived male. 949 * 950 * @return string 951 */ 952 public function longestLifeMaleAge(): string 953 { 954 return $this->longlifeQuery('age', 'M'); 955 } 956 957 /** 958 * Find the name of the longest lived male. 959 * 960 * @return string 961 */ 962 public function longestLifeMaleName(): string 963 { 964 return $this->longlifeQuery('name', 'M'); 965 } 966 967 /** 968 * Returns the calculated age the time of event. 969 * 970 * @param int $days The age from the database record 971 * 972 * @return string 973 */ 974 private function calculateAge(int $days): string 975 { 976 if ($days < 31) { 977 return I18N::plural('%s day', '%s days', $days, I18N::number($days)); 978 } 979 980 if ($days < 365) { 981 $months = (int) ($days / 30.5); 982 return I18N::plural('%s month', '%s months', $months, I18N::number($months)); 983 } 984 985 $years = (int) ($days / 365.25); 986 987 return I18N::plural('%s year', '%s years', $years, I18N::number($years)); 988 } 989 990 /** 991 * Find the oldest individuals. 992 * 993 * @param string $sex 994 * @param int $total 995 * 996 * @return array<array<string,mixed>> 997 */ 998 private function topTenOldestQuery(string $sex, int $total): array 999 { 1000 $prefix = DB::connection()->getTablePrefix(); 1001 1002 $rows = $this->birthAndDeathQuery($sex) 1003 ->groupBy(['i_id', 'i_file']) 1004 ->orderBy('days', 'desc') 1005 ->select(['individuals.*', new Expression('MAX(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')]) 1006 ->take($total) 1007 ->get(); 1008 1009 $top10 = []; 1010 foreach ($rows as $row) { 1011 /** @var Individual $individual */ 1012 $individual = Registry::individualFactory()->mapper($this->tree)($row); 1013 1014 if ($individual->canShow()) { 1015 $top10[] = [ 1016 'person' => $individual, 1017 'age' => $this->calculateAge((int) $row->days), 1018 ]; 1019 } 1020 } 1021 1022 return $top10; 1023 } 1024 1025 /** 1026 * Find the oldest individuals. 1027 * 1028 * @param int $total 1029 * 1030 * @return string 1031 */ 1032 public function topTenOldest(int $total = 10): string 1033 { 1034 $records = $this->topTenOldestQuery('BOTH', $total); 1035 1036 return view('statistics/individuals/top10-nolist', [ 1037 'records' => $records, 1038 ]); 1039 } 1040 1041 /** 1042 * Find the oldest living individuals. 1043 * 1044 * @param int $total 1045 * 1046 * @return string 1047 */ 1048 public function topTenOldestList(int $total = 10): string 1049 { 1050 $records = $this->topTenOldestQuery('BOTH', $total); 1051 1052 return view('statistics/individuals/top10-list', [ 1053 'records' => $records, 1054 ]); 1055 } 1056 1057 /** 1058 * Find the oldest females. 1059 * 1060 * @param int $total 1061 * 1062 * @return string 1063 */ 1064 public function topTenOldestFemale(int $total = 10): string 1065 { 1066 $records = $this->topTenOldestQuery('F', $total); 1067 1068 return view('statistics/individuals/top10-nolist', [ 1069 'records' => $records, 1070 ]); 1071 } 1072 1073 /** 1074 * Find the oldest living females. 1075 * 1076 * @param int $total 1077 * 1078 * @return string 1079 */ 1080 public function topTenOldestFemaleList(int $total = 10): string 1081 { 1082 $records = $this->topTenOldestQuery('F', $total); 1083 1084 return view('statistics/individuals/top10-list', [ 1085 'records' => $records, 1086 ]); 1087 } 1088 1089 /** 1090 * Find the longest lived males. 1091 * 1092 * @param int $total 1093 * 1094 * @return string 1095 */ 1096 public function topTenOldestMale(int $total = 10): string 1097 { 1098 $records = $this->topTenOldestQuery('M', $total); 1099 1100 return view('statistics/individuals/top10-nolist', [ 1101 'records' => $records, 1102 ]); 1103 } 1104 1105 /** 1106 * Find the longest lived males. 1107 * 1108 * @param int $total 1109 * 1110 * @return string 1111 */ 1112 public function topTenOldestMaleList(int $total = 10): string 1113 { 1114 $records = $this->topTenOldestQuery('M', $total); 1115 1116 return view('statistics/individuals/top10-list', [ 1117 'records' => $records, 1118 ]); 1119 } 1120 1121 /** 1122 * Find the oldest living individuals. 1123 * 1124 * @param string $sex "M", "F" or "BOTH" 1125 * @param int $total 1126 * 1127 * @return array<array<string,mixed>> 1128 */ 1129 private function topTenOldestAliveQuery(string $sex, int $total): array 1130 { 1131 $query = DB::table('dates') 1132 ->join('individuals', static function (JoinClause $join): void { 1133 $join 1134 ->on('i_id', '=', 'd_gid') 1135 ->on('i_file', '=', 'd_file'); 1136 }) 1137 ->where('d_file', '=', $this->tree->id()) 1138 ->where('d_julianday1', '<>', 0) 1139 ->where('d_fact', '=', 'BIRT') 1140 ->where('i_gedcom', 'NOT LIKE', "%\n1 DEAT%") 1141 ->where('i_gedcom', 'NOT LIKE', "%\n1 BURI%") 1142 ->where('i_gedcom', 'NOT LIKE', "%\n1 CREM%"); 1143 1144 if ($sex === 'F' || $sex === 'M') { 1145 $query->where('i_sex', '=', $sex); 1146 } 1147 1148 return $query 1149 ->groupBy(['i_id', 'i_file']) 1150 ->orderBy(new Expression('MIN(d_julianday1)')) 1151 ->select(['individuals.*']) 1152 ->take($total) 1153 ->get() 1154 ->map(Registry::individualFactory()->mapper($this->tree)) 1155 ->filter(GedcomRecord::accessFilter()) 1156 ->map(function (Individual $individual): array { 1157 return [ 1158 'person' => $individual, 1159 'age' => $this->calculateAge(Carbon::now()->julianDay() - $individual->getBirthDate()->minimumJulianDay()), 1160 ]; 1161 }) 1162 ->all(); 1163 } 1164 1165 /** 1166 * Find the oldest living individuals. 1167 * 1168 * @param int $total 1169 * 1170 * @return string 1171 */ 1172 public function topTenOldestAlive(int $total = 10): string 1173 { 1174 if (!Auth::isMember($this->tree)) { 1175 return I18N::translate('This information is private and cannot be shown.'); 1176 } 1177 1178 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1179 1180 return view('statistics/individuals/top10-nolist', [ 1181 'records' => $records, 1182 ]); 1183 } 1184 1185 /** 1186 * Find the oldest living individuals. 1187 * 1188 * @param int $total 1189 * 1190 * @return string 1191 */ 1192 public function topTenOldestListAlive(int $total = 10): string 1193 { 1194 if (!Auth::isMember($this->tree)) { 1195 return I18N::translate('This information is private and cannot be shown.'); 1196 } 1197 1198 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1199 1200 return view('statistics/individuals/top10-list', [ 1201 'records' => $records, 1202 ]); 1203 } 1204 1205 /** 1206 * Find the oldest living females. 1207 * 1208 * @param int $total 1209 * 1210 * @return string 1211 */ 1212 public function topTenOldestFemaleAlive(int $total = 10): string 1213 { 1214 if (!Auth::isMember($this->tree)) { 1215 return I18N::translate('This information is private and cannot be shown.'); 1216 } 1217 1218 $records = $this->topTenOldestAliveQuery('F', $total); 1219 1220 return view('statistics/individuals/top10-nolist', [ 1221 'records' => $records, 1222 ]); 1223 } 1224 1225 /** 1226 * Find the oldest living females. 1227 * 1228 * @param int $total 1229 * 1230 * @return string 1231 */ 1232 public function topTenOldestFemaleListAlive(int $total = 10): string 1233 { 1234 if (!Auth::isMember($this->tree)) { 1235 return I18N::translate('This information is private and cannot be shown.'); 1236 } 1237 1238 $records = $this->topTenOldestAliveQuery('F', $total); 1239 1240 return view('statistics/individuals/top10-list', [ 1241 'records' => $records, 1242 ]); 1243 } 1244 1245 /** 1246 * Find the longest lived living males. 1247 * 1248 * @param int $total 1249 * 1250 * @return string 1251 */ 1252 public function topTenOldestMaleAlive(int $total = 10): string 1253 { 1254 if (!Auth::isMember($this->tree)) { 1255 return I18N::translate('This information is private and cannot be shown.'); 1256 } 1257 1258 $records = $this->topTenOldestAliveQuery('M', $total); 1259 1260 return view('statistics/individuals/top10-nolist', [ 1261 'records' => $records, 1262 ]); 1263 } 1264 1265 /** 1266 * Find the longest lived living males. 1267 * 1268 * @param int $total 1269 * 1270 * @return string 1271 */ 1272 public function topTenOldestMaleListAlive(int $total = 10): string 1273 { 1274 if (!Auth::isMember($this->tree)) { 1275 return I18N::translate('This information is private and cannot be shown.'); 1276 } 1277 1278 $records = $this->topTenOldestAliveQuery('M', $total); 1279 1280 return view('statistics/individuals/top10-list', [ 1281 'records' => $records, 1282 ]); 1283 } 1284 1285 /** 1286 * Find the average lifespan. 1287 * 1288 * @param string $sex "M", "F" or "BOTH" 1289 * @param bool $show_years 1290 * 1291 * @return string 1292 */ 1293 private function averageLifespanQuery(string $sex, bool $show_years): string 1294 { 1295 $prefix = DB::connection()->getTablePrefix(); 1296 1297 $days = (int) $this->birthAndDeathQuery($sex) 1298 ->select(new Expression('AVG(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')) 1299 ->value('days'); 1300 1301 if ($show_years) { 1302 return $this->calculateAge($days); 1303 } 1304 1305 return I18N::number((int) ($days / 365.25)); 1306 } 1307 1308 /** 1309 * Find the average lifespan. 1310 * 1311 * @param bool $show_years 1312 * 1313 * @return string 1314 */ 1315 public function averageLifespan(bool $show_years): string 1316 { 1317 return $this->averageLifespanQuery('BOTH', $show_years); 1318 } 1319 1320 /** 1321 * Find the average lifespan of females. 1322 * 1323 * @param bool $show_years 1324 * 1325 * @return string 1326 */ 1327 public function averageLifespanFemale(bool $show_years): string 1328 { 1329 return $this->averageLifespanQuery('F', $show_years); 1330 } 1331 1332 /** 1333 * Find the average male lifespan. 1334 * 1335 * @param bool $show_years 1336 * 1337 * @return string 1338 */ 1339 public function averageLifespanMale(bool $show_years): string 1340 { 1341 return $this->averageLifespanQuery('M', $show_years); 1342 } 1343 1344 /** 1345 * Convert totals into percentages. 1346 * 1347 * @param int $count 1348 * @param int $total 1349 * 1350 * @return string 1351 */ 1352 private function getPercentage(int $count, int $total): string 1353 { 1354 return $total !== 0 ? I18N::percentage($count / $total, 1) : ''; 1355 } 1356 1357 /** 1358 * Returns how many individuals exist in the tree. 1359 * 1360 * @return int 1361 */ 1362 private function totalIndividualsQuery(): int 1363 { 1364 return DB::table('individuals') 1365 ->where('i_file', '=', $this->tree->id()) 1366 ->count(); 1367 } 1368 1369 /** 1370 * Count the number of living individuals. 1371 * 1372 * The totalLiving/totalDeceased queries assume that every dead person will 1373 * have a DEAT record. It will not include individuals who were born more 1374 * than MAX_ALIVE_AGE years ago, and who have no DEAT record. 1375 * A good reason to run the “Add missing DEAT records” batch-update! 1376 * 1377 * @return int 1378 */ 1379 private function totalLivingQuery(): int 1380 { 1381 $query = DB::table('individuals') 1382 ->where('i_file', '=', $this->tree->id()); 1383 1384 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1385 $query->where('i_gedcom', 'NOT LIKE', "%\n1 " . $death_event . '%'); 1386 } 1387 1388 return $query->count(); 1389 } 1390 1391 /** 1392 * Count the number of dead individuals. 1393 * 1394 * @return int 1395 */ 1396 private function totalDeceasedQuery(): int 1397 { 1398 return DB::table('individuals') 1399 ->where('i_file', '=', $this->tree->id()) 1400 ->where(static function (Builder $query): void { 1401 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1402 $query->orWhere('i_gedcom', 'LIKE', "%\n1 " . $death_event . '%'); 1403 } 1404 }) 1405 ->count(); 1406 } 1407 1408 /** 1409 * Returns the total count of a specific sex. 1410 * 1411 * @param string $sex The sex to query 1412 * 1413 * @return int 1414 */ 1415 private function getTotalSexQuery(string $sex): int 1416 { 1417 return DB::table('individuals') 1418 ->where('i_file', '=', $this->tree->id()) 1419 ->where('i_sex', '=', $sex) 1420 ->count(); 1421 } 1422 1423 /** 1424 * Returns the total number of males. 1425 * 1426 * @return int 1427 */ 1428 private function totalSexMalesQuery(): int 1429 { 1430 return $this->getTotalSexQuery('M'); 1431 } 1432 1433 /** 1434 * Returns the total number of females. 1435 * 1436 * @return int 1437 */ 1438 private function totalSexFemalesQuery(): int 1439 { 1440 return $this->getTotalSexQuery('F'); 1441 } 1442 1443 /** 1444 * Returns the total number of individuals with unknown sex. 1445 * 1446 * @return int 1447 */ 1448 private function totalSexUnknownQuery(): int 1449 { 1450 return $this->getTotalSexQuery('U'); 1451 } 1452 1453 /** 1454 * Count the total families. 1455 * 1456 * @return int 1457 */ 1458 private function totalFamiliesQuery(): int 1459 { 1460 return DB::table('families') 1461 ->where('f_file', '=', $this->tree->id()) 1462 ->count(); 1463 } 1464 1465 /** 1466 * How many individuals have one or more sources. 1467 * 1468 * @return int 1469 */ 1470 private function totalIndisWithSourcesQuery(): int 1471 { 1472 return DB::table('individuals') 1473 ->select(['i_id']) 1474 ->distinct() 1475 ->join('link', static function (JoinClause $join): void { 1476 $join->on('i_id', '=', 'l_from') 1477 ->on('i_file', '=', 'l_file'); 1478 }) 1479 ->where('l_file', '=', $this->tree->id()) 1480 ->where('l_type', '=', 'SOUR') 1481 ->count('i_id'); 1482 } 1483 1484 /** 1485 * Count the families with source records. 1486 * 1487 * @return int 1488 */ 1489 private function totalFamsWithSourcesQuery(): int 1490 { 1491 return DB::table('families') 1492 ->select(['f_id']) 1493 ->distinct() 1494 ->join('link', static function (JoinClause $join): void { 1495 $join->on('f_id', '=', 'l_from') 1496 ->on('f_file', '=', 'l_file'); 1497 }) 1498 ->where('l_file', '=', $this->tree->id()) 1499 ->where('l_type', '=', 'SOUR') 1500 ->count('f_id'); 1501 } 1502 1503 /** 1504 * Count the number of repositories. 1505 * 1506 * @return int 1507 */ 1508 private function totalRepositoriesQuery(): int 1509 { 1510 return DB::table('other') 1511 ->where('o_file', '=', $this->tree->id()) 1512 ->where('o_type', '=', 'REPO') 1513 ->count(); 1514 } 1515 1516 /** 1517 * Count the total number of sources. 1518 * 1519 * @return int 1520 */ 1521 private function totalSourcesQuery(): int 1522 { 1523 return DB::table('sources') 1524 ->where('s_file', '=', $this->tree->id()) 1525 ->count(); 1526 } 1527 1528 /** 1529 * Count the number of notes. 1530 * 1531 * @return int 1532 */ 1533 private function totalNotesQuery(): int 1534 { 1535 return DB::table('other') 1536 ->where('o_file', '=', $this->tree->id()) 1537 ->where('o_type', '=', 'NOTE') 1538 ->count(); 1539 } 1540 1541 /** 1542 * Returns the total number of records. 1543 * 1544 * @return int 1545 */ 1546 private function totalRecordsQuery(): int 1547 { 1548 return $this->totalIndividualsQuery() 1549 + $this->totalFamiliesQuery() 1550 + $this->totalNotesQuery() 1551 + $this->totalRepositoriesQuery() 1552 + $this->totalSourcesQuery(); 1553 } 1554 1555 /** 1556 * @return string 1557 */ 1558 public function totalRecords(): string 1559 { 1560 return I18N::number($this->totalRecordsQuery()); 1561 } 1562 1563 /** 1564 * @return string 1565 */ 1566 public function totalIndividuals(): string 1567 { 1568 return I18N::number($this->totalIndividualsQuery()); 1569 } 1570 1571 /** 1572 * Count the number of living individuals. 1573 * 1574 * @return string 1575 */ 1576 public function totalLiving(): string 1577 { 1578 return I18N::number($this->totalLivingQuery()); 1579 } 1580 1581 /** 1582 * Count the number of dead individuals. 1583 * 1584 * @return string 1585 */ 1586 public function totalDeceased(): string 1587 { 1588 return I18N::number($this->totalDeceasedQuery()); 1589 } 1590 1591 /** 1592 * @return string 1593 */ 1594 public function totalSexMales(): string 1595 { 1596 return I18N::number($this->totalSexMalesQuery()); 1597 } 1598 1599 /** 1600 * @return string 1601 */ 1602 public function totalSexFemales(): string 1603 { 1604 return I18N::number($this->totalSexFemalesQuery()); 1605 } 1606 1607 /** 1608 * @return string 1609 */ 1610 public function totalSexUnknown(): string 1611 { 1612 return I18N::number($this->totalSexUnknownQuery()); 1613 } 1614 1615 /** 1616 * @return string 1617 */ 1618 public function totalFamilies(): string 1619 { 1620 return I18N::number($this->totalFamiliesQuery()); 1621 } 1622 1623 /** 1624 * How many individuals have one or more sources. 1625 * 1626 * @return string 1627 */ 1628 public function totalIndisWithSources(): string 1629 { 1630 return I18N::number($this->totalIndisWithSourcesQuery()); 1631 } 1632 1633 /** 1634 * Count the families with with source records. 1635 * 1636 * @return string 1637 */ 1638 public function totalFamsWithSources(): string 1639 { 1640 return I18N::number($this->totalFamsWithSourcesQuery()); 1641 } 1642 1643 /** 1644 * @return string 1645 */ 1646 public function totalRepositories(): string 1647 { 1648 return I18N::number($this->totalRepositoriesQuery()); 1649 } 1650 1651 /** 1652 * @return string 1653 */ 1654 public function totalSources(): string 1655 { 1656 return I18N::number($this->totalSourcesQuery()); 1657 } 1658 1659 /** 1660 * @return string 1661 */ 1662 public function totalNotes(): string 1663 { 1664 return I18N::number($this->totalNotesQuery()); 1665 } 1666 1667 /** 1668 * @return string 1669 */ 1670 public function totalIndividualsPercentage(): string 1671 { 1672 return $this->getPercentage( 1673 $this->totalIndividualsQuery(), 1674 $this->totalRecordsQuery() 1675 ); 1676 } 1677 1678 /** 1679 * @return string 1680 */ 1681 public function totalFamiliesPercentage(): string 1682 { 1683 return $this->getPercentage( 1684 $this->totalFamiliesQuery(), 1685 $this->totalRecordsQuery() 1686 ); 1687 } 1688 1689 /** 1690 * @return string 1691 */ 1692 public function totalRepositoriesPercentage(): string 1693 { 1694 return $this->getPercentage( 1695 $this->totalRepositoriesQuery(), 1696 $this->totalRecordsQuery() 1697 ); 1698 } 1699 1700 /** 1701 * @return string 1702 */ 1703 public function totalSourcesPercentage(): string 1704 { 1705 return $this->getPercentage( 1706 $this->totalSourcesQuery(), 1707 $this->totalRecordsQuery() 1708 ); 1709 } 1710 1711 /** 1712 * @return string 1713 */ 1714 public function totalNotesPercentage(): string 1715 { 1716 return $this->getPercentage( 1717 $this->totalNotesQuery(), 1718 $this->totalRecordsQuery() 1719 ); 1720 } 1721 1722 /** 1723 * @return string 1724 */ 1725 public function totalLivingPercentage(): string 1726 { 1727 return $this->getPercentage( 1728 $this->totalLivingQuery(), 1729 $this->totalIndividualsQuery() 1730 ); 1731 } 1732 1733 /** 1734 * @return string 1735 */ 1736 public function totalDeceasedPercentage(): string 1737 { 1738 return $this->getPercentage( 1739 $this->totalDeceasedQuery(), 1740 $this->totalIndividualsQuery() 1741 ); 1742 } 1743 1744 /** 1745 * @return string 1746 */ 1747 public function totalSexMalesPercentage(): string 1748 { 1749 return $this->getPercentage( 1750 $this->totalSexMalesQuery(), 1751 $this->totalIndividualsQuery() 1752 ); 1753 } 1754 1755 /** 1756 * @return string 1757 */ 1758 public function totalSexFemalesPercentage(): string 1759 { 1760 return $this->getPercentage( 1761 $this->totalSexFemalesQuery(), 1762 $this->totalIndividualsQuery() 1763 ); 1764 } 1765 1766 /** 1767 * @return string 1768 */ 1769 public function totalSexUnknownPercentage(): string 1770 { 1771 return $this->getPercentage( 1772 $this->totalSexUnknownQuery(), 1773 $this->totalIndividualsQuery() 1774 ); 1775 } 1776 1777 /** 1778 * Create a chart of common given names. 1779 * 1780 * @param string|null $color_from 1781 * @param string|null $color_to 1782 * @param int $maxtoshow 1783 * 1784 * @return string 1785 */ 1786 public function chartCommonGiven( 1787 string $color_from = null, 1788 string $color_to = null, 1789 int $maxtoshow = 7 1790 ): string { 1791 $tot_indi = $this->totalIndividualsQuery(); 1792 $given = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow); 1793 1794 if ($given === []) { 1795 return I18N::translate('This information is not available.'); 1796 } 1797 1798 return (new ChartCommonGiven($this->color_service)) 1799 ->chartCommonGiven($tot_indi, $given, $color_from, $color_to); 1800 } 1801 1802 /** 1803 * Create a chart of common surnames. 1804 * 1805 * @param string|null $color_from 1806 * @param string|null $color_to 1807 * @param int $number_of_surnames 1808 * 1809 * @return string 1810 */ 1811 public function chartCommonSurnames( 1812 string $color_from = null, 1813 string $color_to = null, 1814 int $number_of_surnames = 10 1815 ): string { 1816 $tot_indi = $this->totalIndividualsQuery(); 1817 $all_surnames = $this->topSurnames($number_of_surnames, 0); 1818 1819 if ($all_surnames === []) { 1820 return I18N::translate('This information is not available.'); 1821 } 1822 1823 $surname_tradition = SurnameTradition::create($this->tree->getPreference('SURNAME_TRADITION')); 1824 1825 return (new ChartCommonSurname($this->color_service, $surname_tradition)) 1826 ->chartCommonSurnames($tot_indi, $all_surnames, $color_from, $color_to); 1827 } 1828 1829 /** 1830 * Create a chart showing mortality. 1831 * 1832 * @param string|null $color_living 1833 * @param string|null $color_dead 1834 * 1835 * @return string 1836 */ 1837 public function chartMortality(string $color_living = null, string $color_dead = null): string 1838 { 1839 $tot_l = $this->totalLivingQuery(); 1840 $tot_d = $this->totalDeceasedQuery(); 1841 1842 return (new ChartMortality($this->color_service)) 1843 ->chartMortality($tot_l, $tot_d, $color_living, $color_dead); 1844 } 1845 1846 /** 1847 * Create a chart showing individuals with/without sources. 1848 * 1849 * @param string|null $color_from 1850 * @param string|null $color_to 1851 * 1852 * @return string 1853 */ 1854 public function chartIndisWithSources( 1855 string $color_from = null, 1856 string $color_to = null 1857 ): string { 1858 $tot_indi = $this->totalIndividualsQuery(); 1859 $tot_indi_source = $this->totalIndisWithSourcesQuery(); 1860 1861 return (new ChartIndividualWithSources($this->color_service)) 1862 ->chartIndisWithSources($tot_indi, $tot_indi_source, $color_from, $color_to); 1863 } 1864 1865 /** 1866 * Create a chart of individuals with/without sources. 1867 * 1868 * @param string|null $color_from 1869 * @param string|null $color_to 1870 * 1871 * @return string 1872 */ 1873 public function chartFamsWithSources( 1874 string $color_from = null, 1875 string $color_to = null 1876 ): string { 1877 $tot_fam = $this->totalFamiliesQuery(); 1878 $tot_fam_source = $this->totalFamsWithSourcesQuery(); 1879 1880 return (new ChartFamilyWithSources($this->color_service)) 1881 ->chartFamsWithSources($tot_fam, $tot_fam_source, $color_from, $color_to); 1882 } 1883 1884 /** 1885 * @param string|null $color_female 1886 * @param string|null $color_male 1887 * @param string|null $color_unknown 1888 * 1889 * @return string 1890 */ 1891 public function chartSex( 1892 string $color_female = null, 1893 string $color_male = null, 1894 string $color_unknown = null 1895 ): string { 1896 $tot_m = $this->totalSexMalesQuery(); 1897 $tot_f = $this->totalSexFemalesQuery(); 1898 $tot_u = $this->totalSexUnknownQuery(); 1899 1900 return (new ChartSex()) 1901 ->chartSex($tot_m, $tot_f, $tot_u, $color_female, $color_male, $color_unknown); 1902 } 1903 1904 /** 1905 * Query individuals, with their births and deaths. 1906 * 1907 * @param string $sex 1908 * 1909 * @return Builder 1910 */ 1911 private function birthAndDeathQuery(string $sex): Builder 1912 { 1913 $query = DB::table('individuals') 1914 ->where('i_file', '=', $this->tree->id()) 1915 ->join('dates AS birth', static function (JoinClause $join): void { 1916 $join 1917 ->on('birth.d_file', '=', 'i_file') 1918 ->on('birth.d_gid', '=', 'i_id'); 1919 }) 1920 ->join('dates AS death', static function (JoinClause $join): void { 1921 $join 1922 ->on('death.d_file', '=', 'i_file') 1923 ->on('death.d_gid', '=', 'i_id'); 1924 }) 1925 ->where('birth.d_fact', '=', 'BIRT') 1926 ->where('death.d_fact', '=', 'DEAT') 1927 ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2') 1928 ->where('birth.d_julianday2', '<>', 0); 1929 1930 if ($sex === 'M' || $sex === 'F') { 1931 $query->where('i_sex', '=', $sex); 1932 } 1933 1934 return $query; 1935 } 1936} 1937