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_id', 'n_givn']) 101 ->select(['n_givn', DB::raw('COUNT(*) 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 ->take($number_of_surnames) 487 ->get() 488 ->pluck('n_surn') 489 ->all(); 490 491 $surnames = []; 492 foreach ($top_surnames as $top_surname) { 493 $variants = DB::table('name') 494 ->where('n_file', '=', $this->tree->id()) 495 ->where(DB::raw('n_surn /* COLLATE ' . I18N::collation() . ' */'), '=', $top_surname) 496 ->select('n_surn', DB::raw('COUNT(*) AS count')) 497 ->groupBy('n_surn') 498 ->get() 499 ->pluck('count', 'n_surn') 500 ->all(); 501 502 if (array_sum($variants) > $threshold) { 503 $surnames[$top_surname] = $variants; 504 } 505 } 506 507 return $surnames; 508 } 509 510 /** 511 * Find common surnames. 512 * 513 * @return string 514 */ 515 public function getCommonSurname(): string 516 { 517 $top_surname = $this->topSurnames(1, 0); 518 519 return $top_surname 520 ? implode(', ', array_keys(array_shift($top_surname)) ?? []) 521 : ''; 522 } 523 524 /** 525 * Find common surnames. 526 * 527 * @param string $type 528 * @param bool $show_tot 529 * @param int $threshold 530 * @param int $number_of_surnames 531 * @param string $sorting 532 * 533 * @return string 534 */ 535 private function commonSurnamesQuery( 536 string $type, 537 bool $show_tot, 538 int $threshold, 539 int $number_of_surnames, 540 string $sorting 541 ): string { 542 $surnames = $this->topSurnames($number_of_surnames, $threshold); 543 544 switch ($sorting) { 545 default: 546 case 'alpha': 547 uksort($surnames, [I18N::class, 'strcasecmp']); 548 break; 549 case 'count': 550 break; 551 case 'rcount': 552 $surnames = array_reverse($surnames, true); 553 break; 554 } 555 556 return FunctionsPrintLists::surnameList( 557 $surnames, 558 ($type === 'list' ? 1 : 2), 559 $show_tot, 560 'individual-list', 561 $this->tree 562 ); 563 } 564 565 /** 566 * Find common surnames. 567 * 568 * @param int $threshold 569 * @param int $number_of_surnames 570 * @param string $sorting 571 * 572 * @return string 573 */ 574 public function commonSurnames( 575 int $threshold = 1, 576 int $number_of_surnames = 10, 577 string $sorting = 'alpha' 578 ): string { 579 return $this->commonSurnamesQuery('nolist', false, $threshold, $number_of_surnames, $sorting); 580 } 581 582 /** 583 * Find common surnames. 584 * 585 * @param int $threshold 586 * @param int $number_of_surnames 587 * @param string $sorting 588 * 589 * @return string 590 */ 591 public function commonSurnamesTotals( 592 int $threshold = 1, 593 int $number_of_surnames = 10, 594 string $sorting = 'rcount' 595 ): string { 596 return $this->commonSurnamesQuery('nolist', true, $threshold, $number_of_surnames, $sorting); 597 } 598 599 /** 600 * Find common surnames. 601 * 602 * @param int $threshold 603 * @param int $number_of_surnames 604 * @param string $sorting 605 * 606 * @return string 607 */ 608 public function commonSurnamesList( 609 int $threshold = 1, 610 int $number_of_surnames = 10, 611 string $sorting = 'alpha' 612 ): string { 613 return $this->commonSurnamesQuery('list', false, $threshold, $number_of_surnames, $sorting); 614 } 615 616 /** 617 * Find common surnames. 618 * 619 * @param int $threshold 620 * @param int $number_of_surnames 621 * @param string $sorting 622 * 623 * @return string 624 */ 625 public function commonSurnamesListTotals( 626 int $threshold = 1, 627 int $number_of_surnames = 10, 628 string $sorting = 'rcount' 629 ): string { 630 return $this->commonSurnamesQuery('list', true, $threshold, $number_of_surnames, $sorting); 631 } 632 633 /** 634 * Get a list of birth dates. 635 * 636 * @param bool $sex 637 * @param int $year1 638 * @param int $year2 639 * 640 * @return array 641 */ 642 public function statsBirthQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array 643 { 644 $query = DB::table('dates') 645 ->where('d_file', '=', $this->tree->id()) 646 ->where('d_fact', '=', 'BIRT') 647 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 648 ->groupBy('d_month'); 649 650 if ($year1 >= 0 && $year2 >= 0) { 651 $query->whereBetween('d_year', [$year1, $year2]); 652 } 653 654 if ($sex) { 655 $query 656 ->join('individuals', function (JoinClause $join): void { 657 $join 658 ->on('i_id', '=', 'd_gid') 659 ->on('i_file', '=', 'd_file'); 660 }) 661 ->groupBy('i_sex') 662 ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')]); 663 } else { 664 $query->select(['d_month', DB::raw('COUNT(*) AS total')]); 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 ->where('d_file', '=', $this->tree->id()) 697 ->where('d_fact', '=', 'DEAT') 698 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 699 ->groupBy('d_month'); 700 701 if ($year1 >= 0 && $year2 >= 0) { 702 $query->whereBetween('d_year', [$year1, $year2]); 703 } 704 705 if ($sex) { 706 $query 707 ->join('individuals', function (JoinClause $join): void { 708 $join 709 ->on('i_id', '=', 'd_gid') 710 ->on('i_file', '=', 'd_file'); 711 }) 712 ->groupBy('i_sex') 713 ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')]); 714 } else { 715 $query->select(['d_month', DB::raw('COUNT(*) AS total')]); 716 } 717 718 return $query->get()->all(); 719 } 720 721 /** 722 * General query on deaths. 723 * 724 * @param string|null $color_from 725 * @param string|null $color_to 726 * 727 * @return string 728 */ 729 public function statsDeath(string $color_from = null, string $color_to = null): string 730 { 731 return (new ChartDeath($this->tree)) 732 ->chartDeath($color_from, $color_to); 733 } 734 735 /** 736 * General query on ages. 737 * 738 * @param string $related 739 * @param string $sex 740 * @param int $year1 741 * @param int $year2 742 * 743 * @return array|string 744 */ 745 public function statsAgeQuery(string $related = 'BIRT', string $sex = 'BOTH', int $year1 = -1, int $year2 = -1) 746 { 747 $prefix = DB::connection()->getTablePrefix(); 748 749 $query = $this->birthAndDeathQuery($sex); 750 751 if ($year1 >= 0 && $year2 >= 0) { 752 $query 753 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 754 ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 755 756 if ($related === 'BIRT') { 757 $query->whereBetween('birth.d_year', [$year1, $year2]); 758 } elseif ($related === 'DEAT') { 759 $query->whereBetween('death.d_year', [$year1, $year2]); 760 } 761 } 762 763 return $query 764 ->select(DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')) 765 ->orderBy('days', 'desc') 766 ->get() 767 ->all(); 768 } 769 770 /** 771 * General query on ages. 772 * 773 * @return string 774 */ 775 public function statsAge(): string 776 { 777 return (new ChartAge($this->tree))->chartAge(); 778 } 779 780 /** 781 * Lifespan 782 * 783 * @param string $type 784 * @param string $sex 785 * 786 * @return string 787 */ 788 private function longlifeQuery(string $type, string $sex): string 789 { 790 $prefix = DB::connection()->getTablePrefix(); 791 792 $row = $this->birthAndDeathQuery($sex) 793 ->orderBy('days', 'desc') 794 ->select(['individuals.*', DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')]) 795 ->first(); 796 797 if ($row === null) { 798 return ''; 799 } 800 801 /** @var Individual $individual */ 802 $individual = Individual::rowMapper()($row); 803 804 if (!$individual->canShow()) { 805 return I18N::translate('This information is private and cannot be shown.'); 806 } 807 808 switch ($type) { 809 default: 810 case 'full': 811 return $individual->formatList(); 812 813 case 'age': 814 return I18N::number((int) ($row->days / 365.25)); 815 816 case 'name': 817 return '<a href="' . e($individual->url()) . '">' . $individual->fullName() . '</a>'; 818 } 819 } 820 821 /** 822 * Find the longest lived individual. 823 * 824 * @return string 825 */ 826 public function longestLife(): string 827 { 828 return $this->longlifeQuery('full', 'BOTH'); 829 } 830 831 /** 832 * Find the age of the longest lived individual. 833 * 834 * @return string 835 */ 836 public function longestLifeAge(): string 837 { 838 return $this->longlifeQuery('age', 'BOTH'); 839 } 840 841 /** 842 * Find the name of the longest lived individual. 843 * 844 * @return string 845 */ 846 public function longestLifeName(): string 847 { 848 return $this->longlifeQuery('name', 'BOTH'); 849 } 850 851 /** 852 * Find the longest lived female. 853 * 854 * @return string 855 */ 856 public function longestLifeFemale(): string 857 { 858 return $this->longlifeQuery('full', 'F'); 859 } 860 861 /** 862 * Find the age of the longest lived female. 863 * 864 * @return string 865 */ 866 public function longestLifeFemaleAge(): string 867 { 868 return $this->longlifeQuery('age', 'F'); 869 } 870 871 /** 872 * Find the name of the longest lived female. 873 * 874 * @return string 875 */ 876 public function longestLifeFemaleName(): string 877 { 878 return $this->longlifeQuery('name', 'F'); 879 } 880 881 /** 882 * Find the longest lived male. 883 * 884 * @return string 885 */ 886 public function longestLifeMale(): string 887 { 888 return $this->longlifeQuery('full', 'M'); 889 } 890 891 /** 892 * Find the age of the longest lived male. 893 * 894 * @return string 895 */ 896 public function longestLifeMaleAge(): string 897 { 898 return $this->longlifeQuery('age', 'M'); 899 } 900 901 /** 902 * Find the name of the longest lived male. 903 * 904 * @return string 905 */ 906 public function longestLifeMaleName(): string 907 { 908 return $this->longlifeQuery('name', 'M'); 909 } 910 911 /** 912 * Returns the calculated age the time of event. 913 * 914 * @param int $age The age from the database record 915 * 916 * @return string 917 */ 918 private function calculateAge(int $age): string 919 { 920 if ((int) ($age / 365.25) > 0) { 921 $result = (int) ($age / 365.25) . 'y'; 922 } elseif ((int) ($age / 30.4375) > 0) { 923 $result = (int) ($age / 30.4375) . 'm'; 924 } else { 925 $result = $age . 'd'; 926 } 927 928 return FunctionsDate::getAgeAtEvent($result); 929 } 930 931 /** 932 * Find the oldest individuals. 933 * 934 * @param string $sex 935 * @param int $total 936 * 937 * @return array 938 */ 939 private function topTenOldestQuery(string $sex, int $total): array 940 { 941 $prefix = DB::connection()->getTablePrefix(); 942 943 $rows = $this->birthAndDeathQuery($sex) 944 ->groupBy(['i_id', 'i_file']) 945 ->orderBy('days', 'desc') 946 ->select(['individuals.*', DB::raw('MAX(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')]) 947 ->take($total) 948 ->get(); 949 950 $top10 = []; 951 foreach ($rows as $row) { 952 /** @var Individual $individual */ 953 $individual = Individual::rowMapper()($row); 954 955 if ($individual->canShow()) { 956 $top10[] = [ 957 'person' => $individual, 958 'age' => $this->calculateAge((int) $row->days), 959 ]; 960 } 961 } 962 963 return $top10; 964 } 965 966 /** 967 * Find the oldest individuals. 968 * 969 * @param int $total 970 * 971 * @return string 972 */ 973 public function topTenOldest(int $total = 10): string 974 { 975 $records = $this->topTenOldestQuery('BOTH', $total); 976 977 return view( 978 'statistics/individuals/top10-nolist', 979 [ 980 'records' => $records, 981 ] 982 ); 983 } 984 985 /** 986 * Find the oldest living individuals. 987 * 988 * @param int $total 989 * 990 * @return string 991 */ 992 public function topTenOldestList(int $total = 10): string 993 { 994 $records = $this->topTenOldestQuery('BOTH', $total); 995 996 return view( 997 'statistics/individuals/top10-list', 998 [ 999 'records' => $records, 1000 ] 1001 ); 1002 } 1003 1004 /** 1005 * Find the oldest females. 1006 * 1007 * @param int $total 1008 * 1009 * @return string 1010 */ 1011 public function topTenOldestFemale(int $total = 10): string 1012 { 1013 $records = $this->topTenOldestQuery('F', $total); 1014 1015 return view( 1016 'statistics/individuals/top10-nolist', 1017 [ 1018 'records' => $records, 1019 ] 1020 ); 1021 } 1022 1023 /** 1024 * Find the oldest living females. 1025 * 1026 * @param int $total 1027 * 1028 * @return string 1029 */ 1030 public function topTenOldestFemaleList(int $total = 10): string 1031 { 1032 $records = $this->topTenOldestQuery('F', $total); 1033 1034 return view( 1035 'statistics/individuals/top10-list', 1036 [ 1037 'records' => $records, 1038 ] 1039 ); 1040 } 1041 1042 /** 1043 * Find the longest lived males. 1044 * 1045 * @param int $total 1046 * 1047 * @return string 1048 */ 1049 public function topTenOldestMale(int $total = 10): string 1050 { 1051 $records = $this->topTenOldestQuery('M', $total); 1052 1053 return view( 1054 'statistics/individuals/top10-nolist', 1055 [ 1056 'records' => $records, 1057 ] 1058 ); 1059 } 1060 1061 /** 1062 * Find the longest lived males. 1063 * 1064 * @param int $total 1065 * 1066 * @return string 1067 */ 1068 public function topTenOldestMaleList(int $total = 10): string 1069 { 1070 $records = $this->topTenOldestQuery('M', $total); 1071 1072 return view( 1073 'statistics/individuals/top10-list', 1074 [ 1075 'records' => $records, 1076 ] 1077 ); 1078 } 1079 1080 /** 1081 * Find the oldest living individuals. 1082 * 1083 * @param string $sex 1084 * @param int $total 1085 * 1086 * @return array 1087 */ 1088 private function topTenOldestAliveQuery(string $sex = 'BOTH', int $total = 10): array 1089 { 1090 $query = DB::table('dates') 1091 ->join('individuals', function (JoinClause $join): void { 1092 $join 1093 ->on('i_id', '=', 'd_gid') 1094 ->on('i_file', '=', 'd_file'); 1095 }) 1096 ->where('d_file', '=', $this->tree->id()) 1097 ->where('d_julianday1', '<>', 0) 1098 ->where('d_fact', '=', 'BIRT') 1099 ->where('i_gedcom', 'NOT LIKE', "%\n1 DEAT%") 1100 ->where('i_gedcom', 'NOT LIKE', "%\n1 BURI%") 1101 ->where('i_gedcom', 'NOT LIKE', "%\n1 CREM%"); 1102 1103 if ($sex === 'F' || $sex === 'M') { 1104 $query->where('i_sex', '=', $sex); 1105 } 1106 1107 $individuals = $query 1108 ->groupBy(['i_id', 'i_file']) 1109 ->orderBy(DB::raw('MIN(d_julianday1)')) 1110 ->select('individuals.*') 1111 ->take($total) 1112 ->get() 1113 ->map(Individual::rowMapper()) 1114 ->filter(GedcomRecord::accessFilter()); 1115 1116 $top10 = []; 1117 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($this->tree)) 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($this->tree)) 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($this->tree)) 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($this->tree)) 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($this->tree)) 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