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