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