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