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