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