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