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\Carbon; 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 return [ 1124 'person' => $individual, 1125 'age' => $this->calculateAge(Carbon::now()->julianDay() - $individual->getBirthDate()->minimumJulianDay()), 1126 ]; 1127 }) 1128 ->all(); 1129 } 1130 1131 /** 1132 * Find the oldest living individuals. 1133 * 1134 * @param int $total 1135 * 1136 * @return string 1137 */ 1138 public function topTenOldestAlive(int $total = 10): string 1139 { 1140 if (!Auth::isMember($this->tree)) { 1141 return I18N::translate('This information is private and cannot be shown.'); 1142 } 1143 1144 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1145 1146 return view('statistics/individuals/top10-nolist', [ 1147 'records' => $records, 1148 ]); 1149 } 1150 1151 /** 1152 * Find the oldest living individuals. 1153 * 1154 * @param int $total 1155 * 1156 * @return string 1157 */ 1158 public function topTenOldestListAlive(int $total = 10): string 1159 { 1160 if (!Auth::isMember($this->tree)) { 1161 return I18N::translate('This information is private and cannot be shown.'); 1162 } 1163 1164 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1165 1166 return view('statistics/individuals/top10-list', [ 1167 'records' => $records, 1168 ]); 1169 } 1170 1171 /** 1172 * Find the oldest living females. 1173 * 1174 * @param int $total 1175 * 1176 * @return string 1177 */ 1178 public function topTenOldestFemaleAlive(int $total = 10): string 1179 { 1180 if (!Auth::isMember($this->tree)) { 1181 return I18N::translate('This information is private and cannot be shown.'); 1182 } 1183 1184 $records = $this->topTenOldestAliveQuery('F', $total); 1185 1186 return view('statistics/individuals/top10-nolist', [ 1187 'records' => $records, 1188 ]); 1189 } 1190 1191 /** 1192 * Find the oldest living females. 1193 * 1194 * @param int $total 1195 * 1196 * @return string 1197 */ 1198 public function topTenOldestFemaleListAlive(int $total = 10): string 1199 { 1200 if (!Auth::isMember($this->tree)) { 1201 return I18N::translate('This information is private and cannot be shown.'); 1202 } 1203 1204 $records = $this->topTenOldestAliveQuery('F', $total); 1205 1206 return view('statistics/individuals/top10-list', [ 1207 'records' => $records, 1208 ]); 1209 } 1210 1211 /** 1212 * Find the longest lived living males. 1213 * 1214 * @param int $total 1215 * 1216 * @return string 1217 */ 1218 public function topTenOldestMaleAlive(int $total = 10): string 1219 { 1220 if (!Auth::isMember($this->tree)) { 1221 return I18N::translate('This information is private and cannot be shown.'); 1222 } 1223 1224 $records = $this->topTenOldestAliveQuery('M', $total); 1225 1226 return view('statistics/individuals/top10-nolist', [ 1227 'records' => $records, 1228 ]); 1229 } 1230 1231 /** 1232 * Find the longest lived living males. 1233 * 1234 * @param int $total 1235 * 1236 * @return string 1237 */ 1238 public function topTenOldestMaleListAlive(int $total = 10): string 1239 { 1240 if (!Auth::isMember($this->tree)) { 1241 return I18N::translate('This information is private and cannot be shown.'); 1242 } 1243 1244 $records = $this->topTenOldestAliveQuery('M', $total); 1245 1246 return view('statistics/individuals/top10-list', [ 1247 'records' => $records, 1248 ]); 1249 } 1250 1251 /** 1252 * Find the average lifespan. 1253 * 1254 * @param string $sex "M", "F" or "BOTH" 1255 * @param bool $show_years 1256 * 1257 * @return string 1258 */ 1259 private function averageLifespanQuery(string $sex, bool $show_years): string 1260 { 1261 $prefix = DB::connection()->getTablePrefix(); 1262 1263 $days = (int) $this->birthAndDeathQuery($sex) 1264 ->select(DB::raw('AVG(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')) 1265 ->value('days'); 1266 1267 if ($show_years) { 1268 return $this->calculateAge($days); 1269 } 1270 1271 return I18N::number((int) ($days / 365.25)); 1272 } 1273 1274 /** 1275 * Find the average lifespan. 1276 * 1277 * @param bool $show_years 1278 * 1279 * @return string 1280 */ 1281 public function averageLifespan($show_years = false): string 1282 { 1283 return $this->averageLifespanQuery('BOTH', $show_years); 1284 } 1285 1286 /** 1287 * Find the average lifespan of females. 1288 * 1289 * @param bool $show_years 1290 * 1291 * @return string 1292 */ 1293 public function averageLifespanFemale($show_years = false): string 1294 { 1295 return $this->averageLifespanQuery('F', $show_years); 1296 } 1297 1298 /** 1299 * Find the average male lifespan. 1300 * 1301 * @param bool $show_years 1302 * 1303 * @return string 1304 */ 1305 public function averageLifespanMale($show_years = false): string 1306 { 1307 return $this->averageLifespanQuery('M', $show_years); 1308 } 1309 1310 /** 1311 * Convert totals into percentages. 1312 * 1313 * @param int $count 1314 * @param int $total 1315 * 1316 * @return string 1317 */ 1318 private function getPercentage(int $count, int $total): string 1319 { 1320 return ($total !== 0) ? I18N::percentage($count / $total, 1) : ''; 1321 } 1322 1323 /** 1324 * Returns how many individuals exist in the tree. 1325 * 1326 * @return int 1327 */ 1328 private function totalIndividualsQuery(): int 1329 { 1330 return DB::table('individuals') 1331 ->where('i_file', '=', $this->tree->id()) 1332 ->count(); 1333 } 1334 1335 /** 1336 * Count the number of living individuals. 1337 * 1338 * The totalLiving/totalDeceased queries assume that every dead person will 1339 * have a DEAT record. It will not include individuals who were born more 1340 * than MAX_ALIVE_AGE years ago, and who have no DEAT record. 1341 * A good reason to run the “Add missing DEAT records” batch-update! 1342 * 1343 * @return int 1344 */ 1345 private function totalLivingQuery(): int 1346 { 1347 $query = DB::table('individuals') 1348 ->where('i_file', '=', $this->tree->id()); 1349 1350 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1351 $query->where('i_gedcom', 'NOT LIKE', "%\n1 " . $death_event . '%'); 1352 } 1353 1354 return $query->count(); 1355 } 1356 1357 /** 1358 * Count the number of dead individuals. 1359 * 1360 * @return int 1361 */ 1362 private function totalDeceasedQuery(): int 1363 { 1364 return DB::table('individuals') 1365 ->where('i_file', '=', $this->tree->id()) 1366 ->where(function (Builder $query): void { 1367 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1368 $query->orWhere('i_gedcom', 'LIKE', "%\n1 " . $death_event . '%'); 1369 } 1370 }) 1371 ->count(); 1372 } 1373 1374 /** 1375 * Returns the total count of a specific sex. 1376 * 1377 * @param string $sex The sex to query 1378 * 1379 * @return int 1380 */ 1381 private function getTotalSexQuery(string $sex): int 1382 { 1383 return DB::table('individuals') 1384 ->where('i_file', '=', $this->tree->id()) 1385 ->where('i_sex', '=', $sex) 1386 ->count(); 1387 } 1388 1389 /** 1390 * Returns the total number of males. 1391 * 1392 * @return int 1393 */ 1394 private function totalSexMalesQuery(): int 1395 { 1396 return $this->getTotalSexQuery('M'); 1397 } 1398 1399 /** 1400 * Returns the total number of females. 1401 * 1402 * @return int 1403 */ 1404 private function totalSexFemalesQuery(): int 1405 { 1406 return $this->getTotalSexQuery('F'); 1407 } 1408 1409 /** 1410 * Returns the total number of individuals with unknown sex. 1411 * 1412 * @return int 1413 */ 1414 private function totalSexUnknownQuery(): int 1415 { 1416 return $this->getTotalSexQuery('U'); 1417 } 1418 1419 /** 1420 * Count the total families. 1421 * 1422 * @return int 1423 */ 1424 private function totalFamiliesQuery(): int 1425 { 1426 return DB::table('families') 1427 ->where('f_file', '=', $this->tree->id()) 1428 ->count(); 1429 } 1430 1431 /** 1432 * How many individuals have one or more sources. 1433 * 1434 * @return int 1435 */ 1436 private function totalIndisWithSourcesQuery(): int 1437 { 1438 return DB::table('individuals') 1439 ->select(['i_id']) 1440 ->distinct() 1441 ->join('link', function (JoinClause $join): void { 1442 $join->on('i_id', '=', 'l_from') 1443 ->on('i_file', '=', 'l_file'); 1444 }) 1445 ->where('l_file', '=', $this->tree->id()) 1446 ->where('l_type', '=', 'SOUR') 1447 ->count('i_id'); 1448 } 1449 1450 /** 1451 * Count the families with source records. 1452 * 1453 * @return int 1454 */ 1455 private function totalFamsWithSourcesQuery(): int 1456 { 1457 return DB::table('families') 1458 ->select(['f_id']) 1459 ->distinct() 1460 ->join('link', function (JoinClause $join): void { 1461 $join->on('f_id', '=', 'l_from') 1462 ->on('f_file', '=', 'l_file'); 1463 }) 1464 ->where('l_file', '=', $this->tree->id()) 1465 ->where('l_type', '=', 'SOUR') 1466 ->count('f_id'); 1467 } 1468 1469 /** 1470 * Count the number of repositories. 1471 * 1472 * @return int 1473 */ 1474 private function totalRepositoriesQuery(): int 1475 { 1476 return DB::table('other') 1477 ->where('o_file', '=', $this->tree->id()) 1478 ->where('o_type', '=', 'REPO') 1479 ->count(); 1480 } 1481 1482 /** 1483 * Count the total number of sources. 1484 * 1485 * @return int 1486 */ 1487 private function totalSourcesQuery(): int 1488 { 1489 return DB::table('sources') 1490 ->where('s_file', '=', $this->tree->id()) 1491 ->count(); 1492 } 1493 1494 /** 1495 * Count the number of notes. 1496 * 1497 * @return int 1498 */ 1499 private function totalNotesQuery(): int 1500 { 1501 return DB::table('other') 1502 ->where('o_file', '=', $this->tree->id()) 1503 ->where('o_type', '=', 'NOTE') 1504 ->count(); 1505 } 1506 1507 /** 1508 * Returns the total number of records. 1509 * 1510 * @return int 1511 */ 1512 private function totalRecordsQuery(): int 1513 { 1514 return $this->totalIndividualsQuery() 1515 + $this->totalFamiliesQuery() 1516 + $this->totalNotesQuery() 1517 + $this->totalRepositoriesQuery() 1518 + $this->totalSourcesQuery(); 1519 } 1520 1521 /** 1522 * @inheritDoc 1523 */ 1524 public function totalRecords(): string 1525 { 1526 return I18N::number($this->totalRecordsQuery()); 1527 } 1528 1529 /** 1530 * @inheritDoc 1531 */ 1532 public function totalIndividuals(): string 1533 { 1534 return I18N::number($this->totalIndividualsQuery()); 1535 } 1536 1537 /** 1538 * Count the number of living individuals. 1539 * 1540 * @return string 1541 */ 1542 public function totalLiving(): string 1543 { 1544 return I18N::number($this->totalLivingQuery()); 1545 } 1546 1547 /** 1548 * Count the number of dead individuals. 1549 * 1550 * @return string 1551 */ 1552 public function totalDeceased(): string 1553 { 1554 return I18N::number($this->totalDeceasedQuery()); 1555 } 1556 1557 /** 1558 * @inheritDoc 1559 */ 1560 public function totalSexMales(): string 1561 { 1562 return I18N::number($this->totalSexMalesQuery()); 1563 } 1564 1565 /** 1566 * @inheritDoc 1567 */ 1568 public function totalSexFemales(): string 1569 { 1570 return I18N::number($this->totalSexFemalesQuery()); 1571 } 1572 1573 /** 1574 * @inheritDoc 1575 */ 1576 public function totalSexUnknown(): string 1577 { 1578 return I18N::number($this->totalSexUnknownQuery()); 1579 } 1580 1581 /** 1582 * @inheritDoc 1583 */ 1584 public function totalFamilies(): string 1585 { 1586 return I18N::number($this->totalFamiliesQuery()); 1587 } 1588 1589 /** 1590 * How many individuals have one or more sources. 1591 * 1592 * @return string 1593 */ 1594 public function totalIndisWithSources(): string 1595 { 1596 return I18N::number($this->totalIndisWithSourcesQuery()); 1597 } 1598 1599 /** 1600 * Count the families with with source records. 1601 * 1602 * @return string 1603 */ 1604 public function totalFamsWithSources(): string 1605 { 1606 return I18N::number($this->totalFamsWithSourcesQuery()); 1607 } 1608 1609 /** 1610 * @inheritDoc 1611 */ 1612 public function totalRepositories(): string 1613 { 1614 return I18N::number($this->totalRepositoriesQuery()); 1615 } 1616 1617 /** 1618 * @inheritDoc 1619 */ 1620 public function totalSources(): string 1621 { 1622 return I18N::number($this->totalSourcesQuery()); 1623 } 1624 1625 /** 1626 * @inheritDoc 1627 */ 1628 public function totalNotes(): string 1629 { 1630 return I18N::number($this->totalNotesQuery()); 1631 } 1632 1633 /** 1634 * @inheritDoc 1635 */ 1636 public function totalIndividualsPercentage(): string 1637 { 1638 return $this->getPercentage( 1639 $this->totalIndividualsQuery(), 1640 $this->totalRecordsQuery() 1641 ); 1642 } 1643 1644 /** 1645 * @inheritDoc 1646 */ 1647 public function totalFamiliesPercentage(): string 1648 { 1649 return $this->getPercentage( 1650 $this->totalFamiliesQuery(), 1651 $this->totalRecordsQuery() 1652 ); 1653 } 1654 1655 /** 1656 * @inheritDoc 1657 */ 1658 public function totalRepositoriesPercentage(): string 1659 { 1660 return $this->getPercentage( 1661 $this->totalRepositoriesQuery(), 1662 $this->totalRecordsQuery() 1663 ); 1664 } 1665 1666 /** 1667 * @inheritDoc 1668 */ 1669 public function totalSourcesPercentage(): string 1670 { 1671 return $this->getPercentage( 1672 $this->totalSourcesQuery(), 1673 $this->totalRecordsQuery() 1674 ); 1675 } 1676 1677 /** 1678 * @inheritDoc 1679 */ 1680 public function totalNotesPercentage(): string 1681 { 1682 return $this->getPercentage( 1683 $this->totalNotesQuery(), 1684 $this->totalRecordsQuery() 1685 ); 1686 } 1687 1688 /** 1689 * @inheritDoc 1690 */ 1691 public function totalLivingPercentage(): string 1692 { 1693 return $this->getPercentage( 1694 $this->totalLivingQuery(), 1695 $this->totalIndividualsQuery() 1696 ); 1697 } 1698 1699 /** 1700 * @inheritDoc 1701 */ 1702 public function totalDeceasedPercentage(): string 1703 { 1704 return $this->getPercentage( 1705 $this->totalDeceasedQuery(), 1706 $this->totalIndividualsQuery() 1707 ); 1708 } 1709 1710 /** 1711 * @inheritDoc 1712 */ 1713 public function totalSexMalesPercentage(): string 1714 { 1715 return $this->getPercentage( 1716 $this->totalSexMalesQuery(), 1717 $this->totalIndividualsQuery() 1718 ); 1719 } 1720 1721 /** 1722 * @inheritDoc 1723 */ 1724 public function totalSexFemalesPercentage(): string 1725 { 1726 return $this->getPercentage( 1727 $this->totalSexFemalesQuery(), 1728 $this->totalIndividualsQuery() 1729 ); 1730 } 1731 1732 /** 1733 * @inheritDoc 1734 */ 1735 public function totalSexUnknownPercentage(): string 1736 { 1737 return $this->getPercentage( 1738 $this->totalSexUnknownQuery(), 1739 $this->totalIndividualsQuery() 1740 ); 1741 } 1742 1743 /** 1744 * Create a chart of common given names. 1745 * 1746 * @param string|null $color_from 1747 * @param string|null $color_to 1748 * @param int $maxtoshow 1749 * 1750 * @return string 1751 */ 1752 public function chartCommonGiven( 1753 string $color_from = null, 1754 string $color_to = null, 1755 int $maxtoshow = 7 1756 ): string { 1757 $tot_indi = $this->totalIndividualsQuery(); 1758 $given = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow); 1759 1760 if (empty($given)) { 1761 return I18N::translate('This information is not available.'); 1762 } 1763 1764 return (new ChartCommonGiven()) 1765 ->chartCommonGiven($tot_indi, $given, $color_from, $color_to); 1766 } 1767 1768 /** 1769 * Create a chart of common surnames. 1770 * 1771 * @param string|null $color_from 1772 * @param string|null $color_to 1773 * @param int $number_of_surnames 1774 * 1775 * @return string 1776 */ 1777 public function chartCommonSurnames( 1778 string $color_from = null, 1779 string $color_to = null, 1780 int $number_of_surnames = 10 1781 ): string { 1782 $tot_indi = $this->totalIndividualsQuery(); 1783 $all_surnames = $this->topSurnames($number_of_surnames, 0); 1784 1785 if (empty($all_surnames)) { 1786 return I18N::translate('This information is not available.'); 1787 } 1788 1789 return (new ChartCommonSurname($this->tree)) 1790 ->chartCommonSurnames($tot_indi, $all_surnames, $color_from, $color_to); 1791 } 1792 1793 /** 1794 * Create a chart showing mortality. 1795 * 1796 * @param string|null $color_living 1797 * @param string|null $color_dead 1798 * 1799 * @return string 1800 */ 1801 public function chartMortality(string $color_living = null, string $color_dead = null): string 1802 { 1803 $tot_l = $this->totalLivingQuery(); 1804 $tot_d = $this->totalDeceasedQuery(); 1805 1806 return (new ChartMortality()) 1807 ->chartMortality($tot_l, $tot_d, $color_living, $color_dead); 1808 } 1809 1810 /** 1811 * Create a chart showing individuals with/without sources. 1812 * 1813 * @param string|null $color_from 1814 * @param string|null $color_to 1815 * 1816 * @return string 1817 */ 1818 public function chartIndisWithSources( 1819 string $color_from = null, 1820 string $color_to = null 1821 ): string { 1822 $tot_indi = $this->totalIndividualsQuery(); 1823 $tot_indi_source = $this->totalIndisWithSourcesQuery(); 1824 1825 return (new ChartIndividualWithSources()) 1826 ->chartIndisWithSources($tot_indi, $tot_indi_source, $color_from, $color_to); 1827 } 1828 1829 /** 1830 * Create a chart of individuals with/without sources. 1831 * 1832 * @param string|null $color_from 1833 * @param string|null $color_to 1834 * 1835 * @return string 1836 */ 1837 public function chartFamsWithSources( 1838 string $color_from = null, 1839 string $color_to = null 1840 ): string { 1841 $tot_fam = $this->totalFamiliesQuery(); 1842 $tot_fam_source = $this->totalFamsWithSourcesQuery(); 1843 1844 return (new ChartFamilyWithSources()) 1845 ->chartFamsWithSources($tot_fam, $tot_fam_source, $color_from, $color_to); 1846 } 1847 1848 /** 1849 * @inheritDoc 1850 */ 1851 public function chartSex( 1852 string $color_female = null, 1853 string $color_male = null, 1854 string $color_unknown = null 1855 ): string { 1856 $tot_m = $this->totalSexMalesQuery(); 1857 $tot_f = $this->totalSexFemalesQuery(); 1858 $tot_u = $this->totalSexUnknownQuery(); 1859 1860 return (new ChartSex()) 1861 ->chartSex($tot_m, $tot_f, $tot_u, $color_female, $color_male, $color_unknown); 1862 } 1863 1864 /** 1865 * Query individuals, with their births and deaths. 1866 * 1867 * @param string $sex 1868 * 1869 * @return Builder 1870 */ 1871 private function birthAndDeathQuery(string $sex): Builder 1872 { 1873 $query = DB::table('individuals') 1874 ->where('i_file', '=', $this->tree->id()) 1875 ->join('dates AS birth', function (JoinClause $join): void { 1876 $join 1877 ->on('birth.d_file', '=', 'i_file') 1878 ->on('birth.d_gid', '=', 'i_id'); 1879 }) 1880 ->join('dates AS death', function (JoinClause $join): void { 1881 $join 1882 ->on('death.d_file', '=', 'i_file') 1883 ->on('death.d_gid', '=', 'i_id'); 1884 }) 1885 ->where('birth.d_fact', '=', 'BIRT') 1886 ->where('death.d_fact', '=', 'DEAT') 1887 ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2') 1888 ->where('birth.d_julianday2', '<>', 0); 1889 1890 if ($sex === 'M' || $sex === 'F') { 1891 $query->where('i_sex', '=', $sex); 1892 } 1893 1894 return $query; 1895 } 1896} 1897