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