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