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