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 list of birth dates. 636 * 637 * @param bool $sex 638 * @param int $year1 639 * @param int $year2 640 * 641 * @return array 642 */ 643 public function statsBirthQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 644 { 645 $query = DB::table('dates') 646 ->select(['d_month', DB::raw('COUNT(*) AS total')]) 647 ->where('d_file', '=', $this->tree->id()) 648 ->where('d_fact', '=', 'BIRT') 649 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 650 ->groupBy('d_month'); 651 652 if ($year1 >= 0 && $year2 >= 0) { 653 $query->whereBetween('d_year', [$year1, $year2]); 654 } 655 656 if ($sex) { 657 $query 658 ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')]) 659 ->join('individuals', function (JoinClause $join): void { 660 $join 661 ->on('i_id', '=', 'd_gid') 662 ->on('i_file', '=', 'd_file'); 663 }) 664 ->groupBy('i_sex'); 665 } 666 667 return $query->get()->all(); 668 } 669 670 /** 671 * General query on births. 672 * 673 * @param string|null $color_from 674 * @param string|null $color_to 675 * 676 * @return string 677 */ 678 public function statsBirth(string $color_from = null, string $color_to = null): string 679 { 680 return (new ChartBirth($this->tree)) 681 ->chartBirth($color_from, $color_to); 682 } 683 684 /** 685 * Get a list of death dates. 686 * 687 * @param bool $sex 688 * @param int $year1 689 * @param int $year2 690 * 691 * @return array 692 */ 693 public function statsDeathQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 694 { 695 $query = DB::table('dates') 696 ->select(['d_month', DB::raw('COUNT(*) AS total')]) 697 ->where('d_file', '=', $this->tree->id()) 698 ->where('d_fact', '=', 'DEAT') 699 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 700 ->groupBy('d_month'); 701 702 if ($year1 >= 0 && $year2 >= 0) { 703 $query->whereBetween('d_year', [$year1, $year2]); 704 } 705 706 if ($sex) { 707 $query 708 ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')]) 709 ->join('individuals', function (JoinClause $join): void { 710 $join 711 ->on('i_id', '=', 'd_gid') 712 ->on('i_file', '=', 'd_file'); 713 }) 714 ->groupBy('i_sex'); 715 } 716 717 return $query->get()->all(); 718 } 719 720 /** 721 * General query on deaths. 722 * 723 * @param string|null $color_from 724 * @param string|null $color_to 725 * 726 * @return string 727 */ 728 public function statsDeath(string $color_from = null, string $color_to = null): string 729 { 730 return (new ChartDeath($this->tree)) 731 ->chartDeath($color_from, $color_to); 732 } 733 734 /** 735 * General query on ages. 736 * 737 * @param string $related 738 * @param string $sex 739 * @param int $year1 740 * @param int $year2 741 * 742 * @return array|string 743 */ 744 public function statsAgeQuery(string $related = 'BIRT', string $sex = 'BOTH', int $year1 = -1, int $year2 = -1) 745 { 746 $prefix = DB::connection()->getTablePrefix(); 747 748 $query = $this->birthAndDeathQuery($sex); 749 750 if ($year1 >= 0 && $year2 >= 0) { 751 $query 752 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 753 ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 754 755 if ($related === 'BIRT') { 756 $query->whereBetween('birth.d_year', [$year1, $year2]); 757 } elseif ($related === 'DEAT') { 758 $query->whereBetween('death.d_year', [$year1, $year2]); 759 } 760 } 761 762 return $query 763 ->select(DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')) 764 ->orderBy('days', 'desc') 765 ->get() 766 ->all(); 767 } 768 769 /** 770 * General query on ages. 771 * 772 * @return string 773 */ 774 public function statsAge(): string 775 { 776 return (new ChartAge($this->tree))->chartAge(); 777 } 778 779 /** 780 * Lifespan 781 * 782 * @param string $type 783 * @param string $sex 784 * 785 * @return string 786 */ 787 private function longlifeQuery(string $type, string $sex): string 788 { 789 $prefix = DB::connection()->getTablePrefix(); 790 791 $row = $this->birthAndDeathQuery($sex) 792 ->orderBy('days', 'desc') 793 ->select(['individuals.*', DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')]) 794 ->first(); 795 796 if ($row === null) { 797 return ''; 798 } 799 800 /** @var Individual $individual */ 801 $individual = Individual::rowMapper()($row); 802 803 if (!$individual->canShow()) { 804 return I18N::translate('This information is private and cannot be shown.'); 805 } 806 807 switch ($type) { 808 default: 809 case 'full': 810 return $individual->formatList(); 811 812 case 'age': 813 return I18N::number((int) ($row->days / 365.25)); 814 815 case 'name': 816 return '<a href="' . e($individual->url()) . '">' . $individual->fullName() . '</a>'; 817 } 818 } 819 820 /** 821 * Find the longest lived individual. 822 * 823 * @return string 824 */ 825 public function longestLife(): string 826 { 827 return $this->longlifeQuery('full', 'BOTH'); 828 } 829 830 /** 831 * Find the age of the longest lived individual. 832 * 833 * @return string 834 */ 835 public function longestLifeAge(): string 836 { 837 return $this->longlifeQuery('age', 'BOTH'); 838 } 839 840 /** 841 * Find the name of the longest lived individual. 842 * 843 * @return string 844 */ 845 public function longestLifeName(): string 846 { 847 return $this->longlifeQuery('name', 'BOTH'); 848 } 849 850 /** 851 * Find the longest lived female. 852 * 853 * @return string 854 */ 855 public function longestLifeFemale(): string 856 { 857 return $this->longlifeQuery('full', 'F'); 858 } 859 860 /** 861 * Find the age of the longest lived female. 862 * 863 * @return string 864 */ 865 public function longestLifeFemaleAge(): string 866 { 867 return $this->longlifeQuery('age', 'F'); 868 } 869 870 /** 871 * Find the name of the longest lived female. 872 * 873 * @return string 874 */ 875 public function longestLifeFemaleName(): string 876 { 877 return $this->longlifeQuery('name', 'F'); 878 } 879 880 /** 881 * Find the longest lived male. 882 * 883 * @return string 884 */ 885 public function longestLifeMale(): string 886 { 887 return $this->longlifeQuery('full', 'M'); 888 } 889 890 /** 891 * Find the age of the longest lived male. 892 * 893 * @return string 894 */ 895 public function longestLifeMaleAge(): string 896 { 897 return $this->longlifeQuery('age', 'M'); 898 } 899 900 /** 901 * Find the name of the longest lived male. 902 * 903 * @return string 904 */ 905 public function longestLifeMaleName(): string 906 { 907 return $this->longlifeQuery('name', 'M'); 908 } 909 910 /** 911 * Returns the calculated age the time of event. 912 * 913 * @param int $age The age from the database record 914 * 915 * @return string 916 */ 917 private function calculateAge(int $age): string 918 { 919 if ((int) ($age / 365.25) > 0) { 920 $result = (int) ($age / 365.25) . 'y'; 921 } elseif ((int) ($age / 30.4375) > 0) { 922 $result = (int) ($age / 30.4375) . 'm'; 923 } else { 924 $result = $age . 'd'; 925 } 926 927 return FunctionsDate::getAgeAtEvent($result); 928 } 929 930 /** 931 * Find the oldest individuals. 932 * 933 * @param string $sex 934 * @param int $total 935 * 936 * @return array 937 */ 938 private function topTenOldestQuery(string $sex, int $total): array 939 { 940 $prefix = DB::connection()->getTablePrefix(); 941 942 $rows = $this->birthAndDeathQuery($sex) 943 ->groupBy(['i_id', 'i_file']) 944 ->orderBy('days', 'desc') 945 ->select(['individuals.*', DB::raw('MAX(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')]) 946 ->take($total) 947 ->get(); 948 949 $top10 = []; 950 foreach ($rows as $row) { 951 /** @var Individual $individual */ 952 $individual = Individual::rowMapper()($row); 953 954 if ($individual->canShow()) { 955 $top10[] = [ 956 'person' => $individual, 957 'age' => $this->calculateAge((int) $row->days), 958 ]; 959 } 960 } 961 962 return $top10; 963 } 964 965 /** 966 * Find the oldest individuals. 967 * 968 * @param int $total 969 * 970 * @return string 971 */ 972 public function topTenOldest(int $total = 10): string 973 { 974 $records = $this->topTenOldestQuery('BOTH', $total); 975 976 return view( 977 'statistics/individuals/top10-nolist', 978 [ 979 'records' => $records, 980 ] 981 ); 982 } 983 984 /** 985 * Find the oldest living individuals. 986 * 987 * @param int $total 988 * 989 * @return string 990 */ 991 public function topTenOldestList(int $total = 10): string 992 { 993 $records = $this->topTenOldestQuery('BOTH', $total); 994 995 return view( 996 'statistics/individuals/top10-list', 997 [ 998 'records' => $records, 999 ] 1000 ); 1001 } 1002 1003 /** 1004 * Find the oldest females. 1005 * 1006 * @param int $total 1007 * 1008 * @return string 1009 */ 1010 public function topTenOldestFemale(int $total = 10): string 1011 { 1012 $records = $this->topTenOldestQuery('F', $total); 1013 1014 return view( 1015 'statistics/individuals/top10-nolist', 1016 [ 1017 'records' => $records, 1018 ] 1019 ); 1020 } 1021 1022 /** 1023 * Find the oldest living females. 1024 * 1025 * @param int $total 1026 * 1027 * @return string 1028 */ 1029 public function topTenOldestFemaleList(int $total = 10): string 1030 { 1031 $records = $this->topTenOldestQuery('F', $total); 1032 1033 return view( 1034 'statistics/individuals/top10-list', 1035 [ 1036 'records' => $records, 1037 ] 1038 ); 1039 } 1040 1041 /** 1042 * Find the longest lived males. 1043 * 1044 * @param int $total 1045 * 1046 * @return string 1047 */ 1048 public function topTenOldestMale(int $total = 10): string 1049 { 1050 $records = $this->topTenOldestQuery('M', $total); 1051 1052 return view( 1053 'statistics/individuals/top10-nolist', 1054 [ 1055 'records' => $records, 1056 ] 1057 ); 1058 } 1059 1060 /** 1061 * Find the longest lived males. 1062 * 1063 * @param int $total 1064 * 1065 * @return string 1066 */ 1067 public function topTenOldestMaleList(int $total = 10): string 1068 { 1069 $records = $this->topTenOldestQuery('M', $total); 1070 1071 return view( 1072 'statistics/individuals/top10-list', 1073 [ 1074 'records' => $records, 1075 ] 1076 ); 1077 } 1078 1079 /** 1080 * Find the oldest living individuals. 1081 * 1082 * @param string $sex 1083 * @param int $total 1084 * 1085 * @return array 1086 */ 1087 private function topTenOldestAliveQuery(string $sex = 'BOTH', int $total = 10): array 1088 { 1089 $query = DB::table('dates') 1090 ->join('individuals', function (JoinClause $join): void { 1091 $join 1092 ->on('i_id', '=', 'd_gid') 1093 ->on('i_file', '=', 'd_file'); 1094 }) 1095 ->where('d_file', '=', $this->tree->id()) 1096 ->where('d_julianday1', '<>', 0) 1097 ->where('d_fact', '=', 'BIRT') 1098 ->where('i_gedcom', 'NOT LIKE', "%\n1 DEAT%") 1099 ->where('i_gedcom', 'NOT LIKE', "%\n1 BURI%") 1100 ->where('i_gedcom', 'NOT LIKE', "%\n1 CREM%"); 1101 1102 if ($sex === 'F' || $sex === 'M') { 1103 $query->where('i_sex', '=', $sex); 1104 } 1105 1106 $individuals = $query 1107 ->groupBy(['i_id', 'i_file']) 1108 ->orderBy(DB::raw('MIN(d_julianday1)')) 1109 ->select('individuals.*') 1110 ->take($total) 1111 ->get() 1112 ->map(Individual::rowMapper()) 1113 ->filter(GedcomRecord::accessFilter()); 1114 1115 $top10 = []; 1116 1117 /** @var Individual $individual */ 1118 foreach ($individuals as $individual) { 1119 $birth_jd = $individual->getBirthDate()->minimumJulianDay(); 1120 1121 $top10[] = [ 1122 'person' => $individual, 1123 'age' => $this->calculateAge(WT_CLIENT_JD - $birth_jd), 1124 ]; 1125 } 1126 1127 return $top10; 1128 } 1129 1130 /** 1131 * Find the oldest living individuals. 1132 * 1133 * @param int $total 1134 * 1135 * @return string 1136 */ 1137 public function topTenOldestAlive(int $total = 10): string 1138 { 1139 if (!Auth::isMember($this->tree)) { 1140 return I18N::translate('This information is private and cannot be shown.'); 1141 } 1142 1143 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1144 1145 return view( 1146 'statistics/individuals/top10-nolist', 1147 [ 1148 'records' => $records, 1149 ] 1150 ); 1151 } 1152 1153 /** 1154 * Find the oldest living individuals. 1155 * 1156 * @param int $total 1157 * 1158 * @return string 1159 */ 1160 public function topTenOldestListAlive(int $total = 10): string 1161 { 1162 if (!Auth::isMember($this->tree)) { 1163 return I18N::translate('This information is private and cannot be shown.'); 1164 } 1165 1166 $records = $this->topTenOldestAliveQuery('BOTH', $total); 1167 1168 return view( 1169 'statistics/individuals/top10-list', 1170 [ 1171 'records' => $records, 1172 ] 1173 ); 1174 } 1175 1176 /** 1177 * Find the oldest living females. 1178 * 1179 * @param int $total 1180 * 1181 * @return string 1182 */ 1183 public function topTenOldestFemaleAlive(int $total = 10): string 1184 { 1185 if (!Auth::isMember($this->tree)) { 1186 return I18N::translate('This information is private and cannot be shown.'); 1187 } 1188 1189 $records = $this->topTenOldestAliveQuery('F', $total); 1190 1191 return view( 1192 'statistics/individuals/top10-nolist', 1193 [ 1194 'records' => $records, 1195 ] 1196 ); 1197 } 1198 1199 /** 1200 * Find the oldest living females. 1201 * 1202 * @param int $total 1203 * 1204 * @return string 1205 */ 1206 public function topTenOldestFemaleListAlive(int $total = 10): string 1207 { 1208 if (!Auth::isMember($this->tree)) { 1209 return I18N::translate('This information is private and cannot be shown.'); 1210 } 1211 1212 $records = $this->topTenOldestAliveQuery('F', $total); 1213 1214 return view( 1215 'statistics/individuals/top10-list', 1216 [ 1217 'records' => $records, 1218 ] 1219 ); 1220 } 1221 1222 /** 1223 * Find the longest lived living males. 1224 * 1225 * @param int $total 1226 * 1227 * @return string 1228 */ 1229 public function topTenOldestMaleAlive(int $total = 10): string 1230 { 1231 if (!Auth::isMember($this->tree)) { 1232 return I18N::translate('This information is private and cannot be shown.'); 1233 } 1234 1235 $records = $this->topTenOldestAliveQuery('M', $total); 1236 1237 return view( 1238 'statistics/individuals/top10-nolist', 1239 [ 1240 'records' => $records, 1241 ] 1242 ); 1243 } 1244 1245 /** 1246 * Find the longest lived living males. 1247 * 1248 * @param int $total 1249 * 1250 * @return string 1251 */ 1252 public function topTenOldestMaleListAlive(int $total = 10): string 1253 { 1254 if (!Auth::isMember($this->tree)) { 1255 return I18N::translate('This information is private and cannot be shown.'); 1256 } 1257 1258 $records = $this->topTenOldestAliveQuery('M', $total); 1259 1260 return view( 1261 'statistics/individuals/top10-list', 1262 [ 1263 'records' => $records, 1264 ] 1265 ); 1266 } 1267 1268 /** 1269 * Find the average lifespan. 1270 * 1271 * @param string $sex 1272 * @param bool $show_years 1273 * 1274 * @return string 1275 */ 1276 private function averageLifespanQuery(string $sex = 'BOTH', bool $show_years = false): string 1277 { 1278 $prefix = DB::connection()->getTablePrefix(); 1279 1280 $days = (int) $this->birthAndDeathQuery($sex) 1281 ->select(DB::raw('AVG(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')) 1282 ->value('days'); 1283 1284 if ($show_years) { 1285 return $this->calculateAge($days); 1286 } 1287 1288 return I18N::number((int) ($days / 365.25)); 1289 } 1290 1291 /** 1292 * Find the average lifespan. 1293 * 1294 * @param bool $show_years 1295 * 1296 * @return string 1297 */ 1298 public function averageLifespan($show_years = false): string 1299 { 1300 return $this->averageLifespanQuery('BOTH', $show_years); 1301 } 1302 1303 /** 1304 * Find the average lifespan of females. 1305 * 1306 * @param bool $show_years 1307 * 1308 * @return string 1309 */ 1310 public function averageLifespanFemale($show_years = false): string 1311 { 1312 return $this->averageLifespanQuery('F', $show_years); 1313 } 1314 1315 /** 1316 * Find the average male lifespan. 1317 * 1318 * @param bool $show_years 1319 * 1320 * @return string 1321 */ 1322 public function averageLifespanMale($show_years = false): string 1323 { 1324 return $this->averageLifespanQuery('M', $show_years); 1325 } 1326 1327 /** 1328 * Convert totals into percentages. 1329 * 1330 * @param int $count 1331 * @param int $total 1332 * 1333 * @return string 1334 */ 1335 private function getPercentage(int $count, int $total): string 1336 { 1337 return ($total !== 0) ? I18N::percentage($count / $total, 1) : ''; 1338 } 1339 1340 /** 1341 * Returns how many individuals exist in the tree. 1342 * 1343 * @return int 1344 */ 1345 private function totalIndividualsQuery(): int 1346 { 1347 return DB::table('individuals') 1348 ->where('i_file', '=', $this->tree->id()) 1349 ->count(); 1350 } 1351 1352 /** 1353 * Count the number of living individuals. 1354 * 1355 * The totalLiving/totalDeceased queries assume that every dead person will 1356 * have a DEAT record. It will not include individuals who were born more 1357 * than MAX_ALIVE_AGE years ago, and who have no DEAT record. 1358 * A good reason to run the “Add missing DEAT records” batch-update! 1359 * 1360 * @return int 1361 */ 1362 private function totalLivingQuery(): int 1363 { 1364 $query = DB::table('individuals') 1365 ->where('i_file', '=', $this->tree->id()); 1366 1367 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1368 $query->where('i_gedcom', 'NOT LIKE', "%\n1 " . $death_event . '%'); 1369 } 1370 1371 return $query->count(); 1372 } 1373 1374 /** 1375 * Count the number of dead individuals. 1376 * 1377 * @return int 1378 */ 1379 private function totalDeceasedQuery(): int 1380 { 1381 return DB::table('individuals') 1382 ->where('i_file', '=', $this->tree->id()) 1383 ->where(function (Builder $query): void { 1384 foreach (Gedcom::DEATH_EVENTS as $death_event) { 1385 $query->orWhere('i_gedcom', 'LIKE', "%\n1 " . $death_event . '%'); 1386 } 1387 }) 1388 ->count(); 1389 } 1390 1391 /** 1392 * Returns the total count of a specific sex. 1393 * 1394 * @param string $sex The sex to query 1395 * 1396 * @return int 1397 */ 1398 private function getTotalSexQuery(string $sex): int 1399 { 1400 return DB::table('individuals') 1401 ->where('i_file', '=', $this->tree->id()) 1402 ->where('i_sex', '=', $sex) 1403 ->count(); 1404 } 1405 1406 /** 1407 * Returns the total number of males. 1408 * 1409 * @return int 1410 */ 1411 private function totalSexMalesQuery(): int 1412 { 1413 return $this->getTotalSexQuery('M'); 1414 } 1415 1416 /** 1417 * Returns the total number of females. 1418 * 1419 * @return int 1420 */ 1421 private function totalSexFemalesQuery(): int 1422 { 1423 return $this->getTotalSexQuery('F'); 1424 } 1425 1426 /** 1427 * Returns the total number of individuals with unknown sex. 1428 * 1429 * @return int 1430 */ 1431 private function totalSexUnknownQuery(): int 1432 { 1433 return $this->getTotalSexQuery('U'); 1434 } 1435 1436 /** 1437 * Count the total families. 1438 * 1439 * @return int 1440 */ 1441 private function totalFamiliesQuery(): int 1442 { 1443 return DB::table('families') 1444 ->where('f_file', '=', $this->tree->id()) 1445 ->count(); 1446 } 1447 1448 /** 1449 * How many individuals have one or more sources. 1450 * 1451 * @return int 1452 */ 1453 private function totalIndisWithSourcesQuery(): int 1454 { 1455 return DB::table('individuals') 1456 ->select(['i_id']) 1457 ->distinct() 1458 ->join('link', function (JoinClause $join) { 1459 $join->on('i_id', '=', 'l_from') 1460 ->on('i_file', '=', 'l_file'); 1461 }) 1462 ->where('l_file', '=', $this->tree->id()) 1463 ->where('l_type', '=', 'SOUR') 1464 ->count('i_id'); 1465 } 1466 1467 /** 1468 * Count the families with source records. 1469 * 1470 * @return int 1471 */ 1472 private function totalFamsWithSourcesQuery(): int 1473 { 1474 return DB::table('families') 1475 ->select(['f_id']) 1476 ->distinct() 1477 ->join('link', function (JoinClause $join) { 1478 $join->on('f_id', '=', 'l_from') 1479 ->on('f_file', '=', 'l_file'); 1480 }) 1481 ->where('l_file', '=', $this->tree->id()) 1482 ->where('l_type', '=', 'SOUR') 1483 ->count('f_id'); 1484 } 1485 1486 /** 1487 * Count the number of repositories. 1488 * 1489 * @return int 1490 */ 1491 private function totalRepositoriesQuery(): int 1492 { 1493 return DB::table('other') 1494 ->where('o_file', '=', $this->tree->id()) 1495 ->where('o_type', '=', 'REPO') 1496 ->count(); 1497 } 1498 1499 /** 1500 * Count the total number of sources. 1501 * 1502 * @return int 1503 */ 1504 private function totalSourcesQuery(): int 1505 { 1506 return DB::table('sources') 1507 ->where('s_file', '=', $this->tree->id()) 1508 ->count(); 1509 } 1510 1511 /** 1512 * Count the number of notes. 1513 * 1514 * @return int 1515 */ 1516 private function totalNotesQuery(): int 1517 { 1518 return DB::table('other') 1519 ->where('o_file', '=', $this->tree->id()) 1520 ->where('o_type', '=', 'NOTE') 1521 ->count(); 1522 } 1523 1524 /** 1525 * Returns the total number of records. 1526 * 1527 * @return int 1528 */ 1529 private function totalRecordsQuery(): int 1530 { 1531 return $this->totalIndividualsQuery() 1532 + $this->totalFamiliesQuery() 1533 + $this->totalNotesQuery() 1534 + $this->totalRepositoriesQuery() 1535 + $this->totalSourcesQuery(); 1536 } 1537 1538 /** 1539 * @inheritDoc 1540 */ 1541 public function totalRecords(): string 1542 { 1543 return I18N::number($this->totalRecordsQuery()); 1544 } 1545 1546 /** 1547 * @inheritDoc 1548 */ 1549 public function totalIndividuals(): string 1550 { 1551 return I18N::number($this->totalIndividualsQuery()); 1552 } 1553 1554 /** 1555 * Count the number of living individuals. 1556 * 1557 * @return string 1558 */ 1559 public function totalLiving(): string 1560 { 1561 return I18N::number($this->totalLivingQuery()); 1562 } 1563 1564 /** 1565 * Count the number of dead individuals. 1566 * 1567 * @return string 1568 */ 1569 public function totalDeceased(): string 1570 { 1571 return I18N::number($this->totalDeceasedQuery()); 1572 } 1573 1574 /** 1575 * @inheritDoc 1576 */ 1577 public function totalSexMales(): string 1578 { 1579 return I18N::number($this->totalSexMalesQuery()); 1580 } 1581 1582 /** 1583 * @inheritDoc 1584 */ 1585 public function totalSexFemales(): string 1586 { 1587 return I18N::number($this->totalSexFemalesQuery()); 1588 } 1589 1590 /** 1591 * @inheritDoc 1592 */ 1593 public function totalSexUnknown(): string 1594 { 1595 return I18N::number($this->totalSexUnknownQuery()); 1596 } 1597 1598 /** 1599 * @inheritDoc 1600 */ 1601 public function totalFamilies(): string 1602 { 1603 return I18N::number($this->totalFamiliesQuery()); 1604 } 1605 1606 /** 1607 * How many individuals have one or more sources. 1608 * 1609 * @return string 1610 */ 1611 public function totalIndisWithSources(): string 1612 { 1613 return I18N::number($this->totalIndisWithSourcesQuery()); 1614 } 1615 1616 /** 1617 * Count the families with with source records. 1618 * 1619 * @return string 1620 */ 1621 public function totalFamsWithSources(): string 1622 { 1623 return I18N::number($this->totalFamsWithSourcesQuery()); 1624 } 1625 1626 /** 1627 * @inheritDoc 1628 */ 1629 public function totalRepositories(): string 1630 { 1631 return I18N::number($this->totalRepositoriesQuery()); 1632 } 1633 1634 /** 1635 * @inheritDoc 1636 */ 1637 public function totalSources(): string 1638 { 1639 return I18N::number($this->totalSourcesQuery()); 1640 } 1641 1642 /** 1643 * @inheritDoc 1644 */ 1645 public function totalNotes(): string 1646 { 1647 return I18N::number($this->totalNotesQuery()); 1648 } 1649 1650 /** 1651 * @inheritDoc 1652 */ 1653 public function totalIndividualsPercentage(): string 1654 { 1655 return $this->getPercentage( 1656 $this->totalIndividualsQuery(), 1657 $this->totalRecordsQuery() 1658 ); 1659 } 1660 1661 /** 1662 * @inheritDoc 1663 */ 1664 public function totalFamiliesPercentage(): string 1665 { 1666 return $this->getPercentage( 1667 $this->totalFamiliesQuery(), 1668 $this->totalRecordsQuery() 1669 ); 1670 } 1671 1672 /** 1673 * @inheritDoc 1674 */ 1675 public function totalRepositoriesPercentage(): string 1676 { 1677 return $this->getPercentage( 1678 $this->totalRepositoriesQuery(), 1679 $this->totalRecordsQuery() 1680 ); 1681 } 1682 1683 /** 1684 * @inheritDoc 1685 */ 1686 public function totalSourcesPercentage(): string 1687 { 1688 return $this->getPercentage( 1689 $this->totalSourcesQuery(), 1690 $this->totalRecordsQuery() 1691 ); 1692 } 1693 1694 /** 1695 * @inheritDoc 1696 */ 1697 public function totalNotesPercentage(): string 1698 { 1699 return $this->getPercentage( 1700 $this->totalNotesQuery(), 1701 $this->totalRecordsQuery() 1702 ); 1703 } 1704 1705 /** 1706 * @inheritDoc 1707 */ 1708 public function totalLivingPercentage(): string 1709 { 1710 return $this->getPercentage( 1711 $this->totalLivingQuery(), 1712 $this->totalIndividualsQuery() 1713 ); 1714 } 1715 1716 /** 1717 * @inheritDoc 1718 */ 1719 public function totalDeceasedPercentage(): string 1720 { 1721 return $this->getPercentage( 1722 $this->totalDeceasedQuery(), 1723 $this->totalIndividualsQuery() 1724 ); 1725 } 1726 1727 /** 1728 * @inheritDoc 1729 */ 1730 public function totalSexMalesPercentage(): string 1731 { 1732 return $this->getPercentage( 1733 $this->totalSexMalesQuery(), 1734 $this->totalIndividualsQuery() 1735 ); 1736 } 1737 1738 /** 1739 * @inheritDoc 1740 */ 1741 public function totalSexFemalesPercentage(): string 1742 { 1743 return $this->getPercentage( 1744 $this->totalSexFemalesQuery(), 1745 $this->totalIndividualsQuery() 1746 ); 1747 } 1748 1749 /** 1750 * @inheritDoc 1751 */ 1752 public function totalSexUnknownPercentage(): string 1753 { 1754 return $this->getPercentage( 1755 $this->totalSexUnknownQuery(), 1756 $this->totalIndividualsQuery() 1757 ); 1758 } 1759 1760 /** 1761 * Create a chart of common given names. 1762 * 1763 * @param string|null $color_from 1764 * @param string|null $color_to 1765 * @param int $maxtoshow 1766 * 1767 * @return string 1768 */ 1769 public function chartCommonGiven( 1770 string $color_from = null, 1771 string $color_to = null, 1772 int $maxtoshow = 7 1773 ): string { 1774 $tot_indi = $this->totalIndividualsQuery(); 1775 $given = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow); 1776 1777 if (empty($given)) { 1778 return I18N::translate('This information is not available.'); 1779 } 1780 1781 return (new ChartCommonGiven()) 1782 ->chartCommonGiven($tot_indi, $given, $color_from, $color_to); 1783 } 1784 1785 /** 1786 * Create a chart of common surnames. 1787 * 1788 * @param string|null $color_from 1789 * @param string|null $color_to 1790 * @param int $number_of_surnames 1791 * 1792 * @return string 1793 */ 1794 public function chartCommonSurnames( 1795 string $color_from = null, 1796 string $color_to = null, 1797 int $number_of_surnames = 10 1798 ): string { 1799 $tot_indi = $this->totalIndividualsQuery(); 1800 $all_surnames = $this->topSurnames($number_of_surnames, 0); 1801 1802 if (empty($all_surnames)) { 1803 return I18N::translate('This information is not available.'); 1804 } 1805 1806 return (new ChartCommonSurname($this->tree)) 1807 ->chartCommonSurnames($tot_indi, $all_surnames, $color_from, $color_to); 1808 } 1809 1810 /** 1811 * Create a chart showing mortality. 1812 * 1813 * @param string|null $color_living 1814 * @param string|null $color_dead 1815 * 1816 * @return string 1817 */ 1818 public function chartMortality(string $color_living = null, string $color_dead = null): string 1819 { 1820 $tot_l = $this->totalLivingQuery(); 1821 $tot_d = $this->totalDeceasedQuery(); 1822 1823 return (new ChartMortality()) 1824 ->chartMortality($tot_l, $tot_d, $color_living, $color_dead); 1825 } 1826 1827 /** 1828 * Create a chart showing individuals with/without sources. 1829 * 1830 * @param string|null $color_from 1831 * @param string|null $color_to 1832 * 1833 * @return string 1834 */ 1835 public function chartIndisWithSources( 1836 string $color_from = null, 1837 string $color_to = null 1838 ): string { 1839 $tot_indi = $this->totalIndividualsQuery(); 1840 $tot_indi_source = $this->totalIndisWithSourcesQuery(); 1841 1842 return (new ChartIndividualWithSources()) 1843 ->chartIndisWithSources($tot_indi, $tot_indi_source, $color_from, $color_to); 1844 } 1845 1846 /** 1847 * Create a chart of individuals with/without sources. 1848 * 1849 * @param string|null $color_from 1850 * @param string|null $color_to 1851 * 1852 * @return string 1853 */ 1854 public function chartFamsWithSources( 1855 string $color_from = null, 1856 string $color_to = null 1857 ): string { 1858 $tot_fam = $this->totalFamiliesQuery(); 1859 $tot_fam_source = $this->totalFamsWithSourcesQuery(); 1860 1861 return (new ChartFamilyWithSources()) 1862 ->chartFamsWithSources($tot_fam, $tot_fam_source, $color_from, $color_to); 1863 } 1864 1865 /** 1866 * @inheritDoc 1867 */ 1868 public function chartSex( 1869 string $color_female = null, 1870 string $color_male = null, 1871 string $color_unknown = null 1872 ): string { 1873 $tot_m = $this->totalSexMalesQuery(); 1874 $tot_f = $this->totalSexFemalesQuery(); 1875 $tot_u = $this->totalSexUnknownQuery(); 1876 1877 return (new ChartSex()) 1878 ->chartSex($tot_m, $tot_f, $tot_u, $color_female, $color_male, $color_unknown); 1879 } 1880 1881 /** 1882 * Query individuals, with their births and deaths. 1883 * 1884 * @param string $sex 1885 * 1886 * @return Builder 1887 */ 1888 private function birthAndDeathQuery(string $sex): Builder 1889 { 1890 $query = DB::table('individuals') 1891 ->where('i_file', '=', $this->tree->id()) 1892 ->join('dates AS birth', function (JoinClause $join): void { 1893 $join 1894 ->on('birth.d_file', '=', 'i_file') 1895 ->on('birth.d_gid', '=', 'i_id'); 1896 }) 1897 ->join('dates AS death', function (JoinClause $join): void { 1898 $join 1899 ->on('death.d_file', '=', 'i_file') 1900 ->on('death.d_gid', '=', 'i_id'); 1901 }) 1902 ->where('birth.d_fact', '=', 'BIRT') 1903 ->where('death.d_fact', '=', 'DEAT') 1904 ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2') 1905 ->where('birth.d_julianday2', '<>', 0); 1906 1907 if ($sex === 'M' || $sex === 'F') { 1908 $query->where('i_sex', '=', $sex); 1909 } 1910 1911 return $query; 1912 } 1913} 1914