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