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