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\Family; 21use Fisharebest\Webtrees\Functions\FunctionsDate; 22use Fisharebest\Webtrees\GedcomRecord; 23use Fisharebest\Webtrees\I18N; 24use Fisharebest\Webtrees\Individual; 25use Fisharebest\Webtrees\Statistics\Google\ChartChildren; 26use Fisharebest\Webtrees\Statistics\Google\ChartDivorce; 27use Fisharebest\Webtrees\Statistics\Google\ChartFamilyLargest; 28use Fisharebest\Webtrees\Statistics\Google\ChartMarriage; 29use Fisharebest\Webtrees\Statistics\Google\ChartMarriageAge; 30use Fisharebest\Webtrees\Statistics\Google\ChartNoChildrenFamilies; 31use Fisharebest\Webtrees\Tree; 32use Illuminate\Database\Capsule\Manager as DB; 33use Illuminate\Database\Query\Builder; 34use Illuminate\Database\Query\JoinClause; 35use stdClass; 36 37/** 38 * 39 */ 40class FamilyRepository 41{ 42 /** 43 * @var Tree 44 */ 45 private $tree; 46 47 /** 48 * Constructor. 49 * 50 * @param Tree $tree 51 */ 52 public function __construct(Tree $tree) 53 { 54 $this->tree = $tree; 55 } 56 57 /** 58 * General query on family. 59 * 60 * @param string $type 61 * 62 * @return string 63 */ 64 private function familyQuery(string $type): string 65 { 66 $row = DB::table('families') 67 ->where('f_file', '=', $this->tree->id()) 68 ->orderBy('f_numchil', 'desc') 69 ->first(); 70 71 if ($row === null) { 72 return ''; 73 } 74 75 /** @var Family $family */ 76 $family = Family::rowMapper()($row); 77 78 if (!$family->canShow()) { 79 return I18N::translate('This information is private and cannot be shown.'); 80 } 81 82 switch ($type) { 83 default: 84 case 'full': 85 return $family->formatList(); 86 87 case 'size': 88 return I18N::number((int) $row->f_numchil); 89 90 case 'name': 91 return '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a>'; 92 } 93 } 94 95 /** 96 * Find the family with the most children. 97 * 98 * @return string 99 */ 100 public function largestFamily(): string 101 { 102 return $this->familyQuery('full'); 103 } 104 105 /** 106 * Find the number of children in the largest family. 107 * 108 * @return string 109 */ 110 public function largestFamilySize(): string 111 { 112 return $this->familyQuery('size'); 113 } 114 115 /** 116 * Find the family with the most children. 117 * 118 * @return string 119 */ 120 public function largestFamilyName(): string 121 { 122 return $this->familyQuery('name'); 123 } 124 125 /** 126 * Find the couple with the most grandchildren. 127 * 128 * @param int $total 129 * 130 * @return array 131 */ 132 private function topTenGrandFamilyQuery(int $total): array 133 { 134 return DB::table('families') 135 ->join('link AS children', function (JoinClause $join) { 136 $join 137 ->on('children.l_from', '=', 'f_id') 138 ->on('children.l_file', '=', 'f_file') 139 ->where('children.l_type', '=', 'CHIL'); 140 })->join('link AS mchildren', function (JoinClause $join) { 141 $join 142 ->on('mchildren.l_file', '=', 'children.l_file') 143 ->on('mchildren.l_from', '=', 'children.l_to') 144 ->where('mchildren.l_type', '=', 'FAMS'); 145 })->join('link AS gchildren', function (JoinClause $join) { 146 $join 147 ->on('gchildren.l_file', '=', 'mchildren.l_file') 148 ->on('gchildren.l_from', '=', 'mchildren.l_to') 149 ->where('gchildren.l_type', '=', 'CHIL'); 150 }) 151 ->where('f_file', '=', $this->tree->id()) 152 ->groupBy(['f_id', 'f_file']) 153 ->orderBy(DB::raw('COUNT(*)'), 'DESC') 154 ->select('families.*') 155 ->limit($total) 156 ->get() 157 ->map(Family::rowMapper()) 158 ->filter(GedcomRecord::accessFilter()) 159 ->map(function (Family $family): array { 160 $count = 0; 161 foreach ($family->children() as $child) { 162 foreach ($child->spouseFamilies() as $spouse_family) { 163 $count += $spouse_family->children()->count(); 164 } 165 } 166 167 return [ 168 'family' => $family, 169 'count' => $count, 170 ]; 171 }) 172 ->all(); 173 } 174 175 /** 176 * Find the couple with the most grandchildren. 177 * 178 * @param int $total 179 * 180 * @return string 181 */ 182 public function topTenLargestGrandFamily(int $total = 10): string 183 { 184 return view('statistics/families/top10-nolist-grand', [ 185 'records' => $this->topTenGrandFamilyQuery($total), 186 ]); 187 } 188 189 /** 190 * Find the couple with the most grandchildren. 191 * 192 * @param int $total 193 * 194 * @return string 195 */ 196 public function topTenLargestGrandFamilyList(int $total = 10): string 197 { 198 return view('statistics/families/top10-list-grand', [ 199 'records' => $this->topTenGrandFamilyQuery($total), 200 ]); 201 } 202 203 /** 204 * Find the families with no children. 205 * 206 * @return int 207 */ 208 private function noChildrenFamiliesQuery(): int 209 { 210 return DB::table('families') 211 ->where('f_file', '=', $this->tree->id()) 212 ->where('f_numchil', '=', 0) 213 ->count(); 214 } 215 216 /** 217 * Find the families with no children. 218 * 219 * @return string 220 */ 221 public function noChildrenFamilies(): string 222 { 223 return I18N::number($this->noChildrenFamiliesQuery()); 224 } 225 226 /** 227 * Find the families with no children. 228 * 229 * @param string $type 230 * 231 * @return string 232 */ 233 public function noChildrenFamiliesList($type = 'list'): string 234 { 235 $families = DB::table('families') 236 ->where('f_file', '=', $this->tree->id()) 237 ->where('f_numchil', '=', 0) 238 ->get() 239 ->map(Family::rowMapper()) 240 ->filter(GedcomRecord::accessFilter()); 241 242 $top10 = []; 243 244 /** @var Family $family */ 245 foreach ($families as $family) { 246 if ($type === 'list') { 247 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a></li>'; 248 } else { 249 $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a>'; 250 } 251 } 252 253 if ($type === 'list') { 254 $top10 = implode('', $top10); 255 } else { 256 $top10 = implode('; ', $top10); 257 } 258 259 260 if ($type === 'list') { 261 return '<ul>' . $top10 . '</ul>'; 262 } 263 264 return $top10; 265 } 266 267 /** 268 * Create a chart of children with no families. 269 * 270 * @param int $year1 271 * @param int $year2 272 * 273 * @return string 274 */ 275 public function chartNoChildrenFamilies(int $year1 = -1, int $year2 = -1): string 276 { 277 $no_child_fam = $this->noChildrenFamiliesQuery(); 278 279 return (new ChartNoChildrenFamilies($this->tree)) 280 ->chartNoChildrenFamilies($no_child_fam, $year1, $year2); 281 } 282 283 /** 284 * Returns the ages between siblings. 285 * 286 * @param int $total The total number of records to query 287 * 288 * @return array 289 */ 290 private function ageBetweenSiblingsQuery(int $total): array 291 { 292 $prefix = DB::connection()->getTablePrefix(); 293 294 return DB::table('link AS link1') 295 ->join('link AS link2', function (JoinClause $join): void { 296 $join 297 ->on('link2.l_from', '=', 'link1.l_from') 298 ->on('link2.l_type', '=', 'link1.l_type') 299 ->on('link2.l_file', '=', 'link1.l_file'); 300 }) 301 ->join('dates AS child1', function (JoinClause $join): void { 302 $join 303 ->on('child1.d_gid', '=', 'link1.l_to') 304 ->on('child1.d_file', '=', 'link1.l_file') 305 ->where('child1.d_fact', '=', 'BIRT') 306 ->where('child1.d_julianday1', '<>', 0); 307 }) 308 ->join('dates AS child2', function (JoinClause $join): void { 309 $join 310 ->on('child2.d_gid', '=', 'link2.l_to') 311 ->on('child2.d_file', '=', 'link2.l_file') 312 ->where('child2.d_fact', '=', 'BIRT') 313 ->whereColumn('child2.d_julianday2', '>', 'child1.d_julianday1'); 314 }) 315 ->where('link1.l_type', '=', 'CHIL') 316 ->where('link1.l_file', '=', $this->tree->id()) 317 ->distinct() 318 ->select(['link1.l_from AS family', 'link1.l_to AS ch1', 'link2.l_to AS ch2', DB::raw($prefix . 'child2.d_julianday2 - ' . $prefix . 'child1.d_julianday1 AS age')]) 319 ->orderBy('age', 'DESC') 320 ->take($total) 321 ->get() 322 ->all(); 323 } 324 325 /** 326 * Returns the calculated age the time of event. 327 * 328 * @param int $age The age from the database record 329 * 330 * @return string 331 */ 332 private function calculateAge(int $age): string 333 { 334 if ((int) ($age / 365.25) > 0) { 335 $result = (int) ($age / 365.25) . 'y'; 336 } elseif ((int) ($age / 30.4375) > 0) { 337 $result = (int) ($age / 30.4375) . 'm'; 338 } else { 339 $result = $age . 'd'; 340 } 341 342 return FunctionsDate::getAgeAtEvent($result); 343 } 344 345 /** 346 * Find the ages between siblings. 347 * 348 * @param int $total The total number of records to query 349 * 350 * @return array 351 * @throws \Exception 352 */ 353 private function ageBetweenSiblingsNoList(int $total): array 354 { 355 $rows = $this->ageBetweenSiblingsQuery($total); 356 357 foreach ($rows as $fam) { 358 $family = Family::getInstance($fam->family, $this->tree); 359 $child1 = Individual::getInstance($fam->ch1, $this->tree); 360 $child2 = Individual::getInstance($fam->ch2, $this->tree); 361 362 if ($child1->canShow() && $child2->canShow()) { 363 // ! Single array (no list) 364 return [ 365 'child1' => $child1, 366 'child2' => $child2, 367 'family' => $family, 368 'age' => $this->calculateAge((int) $fam->age), 369 ]; 370 } 371 } 372 373 return []; 374 } 375 376 /** 377 * Find the ages between siblings. 378 * 379 * @param int $total The total number of records to query 380 * @param bool $one Include each family only once if true 381 * 382 * @return array 383 * @throws \Exception 384 */ 385 private function ageBetweenSiblingsList(int $total, bool $one): array 386 { 387 $rows = $this->ageBetweenSiblingsQuery($total); 388 $top10 = []; 389 $dist = []; 390 391 foreach ($rows as $fam) { 392 $family = Family::getInstance($fam->family, $this->tree); 393 $child1 = Individual::getInstance($fam->ch1, $this->tree); 394 $child2 = Individual::getInstance($fam->ch2, $this->tree); 395 396 $age = $this->calculateAge((int) $fam->age); 397 398 if ($one && !\in_array($fam->family, $dist, true)) { 399 if ($child1->canShow() && $child2->canShow()) { 400 $top10[] = [ 401 'child1' => $child1, 402 'child2' => $child2, 403 'family' => $family, 404 'age' => $age, 405 ]; 406 407 $dist[] = $fam->family; 408 } 409 } elseif (!$one && $child1->canShow() && $child2->canShow()) { 410 $top10[] = [ 411 'child1' => $child1, 412 'child2' => $child2, 413 'family' => $family, 414 'age' => $age, 415 ]; 416 } 417 } 418 419 return $top10; 420 } 421 422 /** 423 * Find the ages between siblings. 424 * 425 * @param int $total The total number of records to query 426 * 427 * @return string 428 */ 429 private function ageBetweenSiblingsAge(int $total): string 430 { 431 $rows = $this->ageBetweenSiblingsQuery($total); 432 433 foreach ($rows as $fam) { 434 return $this->calculateAge((int) $fam->age); 435 } 436 437 return ''; 438 } 439 440 /** 441 * Find the ages between siblings. 442 * 443 * @param int $total The total number of records to query 444 * 445 * @return string 446 * @throws \Exception 447 */ 448 private function ageBetweenSiblingsName(int $total): string 449 { 450 $rows = $this->ageBetweenSiblingsQuery($total); 451 452 foreach ($rows as $fam) { 453 $family = Family::getInstance($fam->family, $this->tree); 454 $child1 = Individual::getInstance($fam->ch1, $this->tree); 455 $child2 = Individual::getInstance($fam->ch2, $this->tree); 456 457 if ($child1->canShow() && $child2->canShow()) { 458 $return = '<a href="' . e($child2->url()) . '">' . $child2->fullName() . '</a> '; 459 $return .= I18N::translate('and') . ' '; 460 $return .= '<a href="' . e($child1->url()) . '">' . $child1->fullName() . '</a>'; 461 $return .= ' <a href="' . e($family->url()) . '">[' . I18N::translate('View this family') . ']</a>'; 462 } else { 463 $return = I18N::translate('This information is private and cannot be shown.'); 464 } 465 466 return $return; 467 } 468 469 return ''; 470 } 471 472 /** 473 * Find the names of siblings with the widest age gap. 474 * 475 * @param int $total 476 * 477 * @return string 478 */ 479 public function topAgeBetweenSiblingsName(int $total = 10): string 480 { 481 return $this->ageBetweenSiblingsName($total); 482 } 483 484 /** 485 * Find the widest age gap between siblings. 486 * 487 * @param int $total 488 * 489 * @return string 490 */ 491 public function topAgeBetweenSiblings(int $total = 10): string 492 { 493 return $this->ageBetweenSiblingsAge($total); 494 } 495 496 /** 497 * Find the name of siblings with the widest age gap. 498 * 499 * @param int $total 500 * 501 * @return string 502 */ 503 public function topAgeBetweenSiblingsFullName(int $total = 10): string 504 { 505 $record = $this->ageBetweenSiblingsNoList($total); 506 507 if (empty($record)) { 508 return I18N::translate('This information is not available.'); 509 } 510 511 return view('statistics/families/top10-nolist-age', [ 512 'record' => $record, 513 ]); 514 } 515 516 /** 517 * Find the siblings with the widest age gaps. 518 * 519 * @param int $total 520 * @param string $one 521 * 522 * @return string 523 */ 524 public function topAgeBetweenSiblingsList(int $total = 10, string $one = ''): string 525 { 526 $records = $this->ageBetweenSiblingsList($total, (bool) $one); 527 528 return view('statistics/families/top10-list-age', [ 529 'records' => $records, 530 ]); 531 } 532 533 /** 534 * General query on familes/children. 535 * 536 * @param int $year1 537 * @param int $year2 538 * 539 * @return stdClass[] 540 */ 541 public function statsChildrenQuery(int $year1 = -1, int $year2 = -1): array 542 { 543 $query = DB::table('families') 544 ->where('f_file', '=', $this->tree->id()) 545 ->groupBy('f_numchil') 546 ->select(['f_numchil', DB::raw('COUNT(*) AS total')]); 547 548 if ($year1 >= 0 && $year2 >= 0) { 549 $query 550 ->join('dates', function (JoinClause $join): void { 551 $join 552 ->on('d_file', '=', 'f_file') 553 ->on('d_gid', '=', 'f_id'); 554 }) 555 ->where('d_fact', '=', 'MARR') 556 ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 557 ->whereBetween('d_year', [$year1, $year2]); 558 } 559 560 return $query->get()->all(); 561 } 562 563 /** 564 * Genearl query on families/children. 565 * 566 * @return string 567 */ 568 public function statsChildren(): string 569 { 570 return (new ChartChildren($this->tree)) 571 ->chartChildren(); 572 } 573 574 /** 575 * Count the total children. 576 * 577 * @return string 578 */ 579 public function totalChildren(): string 580 { 581 $total = (int) DB::table('families') 582 ->where('f_file', '=', $this->tree->id()) 583 ->sum('f_numchil'); 584 585 return I18N::number($total); 586 } 587 588 /** 589 * Find the average number of children in families. 590 * 591 * @return string 592 */ 593 public function averageChildren(): string 594 { 595 $average = (float) DB::table('families') 596 ->where('f_file', '=', $this->tree->id()) 597 ->avg('f_numchil'); 598 599 return I18N::number($average, 2); 600 } 601 602 /** 603 * General query on families. 604 * 605 * @param int $total 606 * 607 * @return array 608 */ 609 private function topTenFamilyQuery(int $total): array 610 { 611 return DB::table('families') 612 ->where('f_file', '=', $this->tree->id()) 613 ->orderBy('f_numchil', 'DESC') 614 ->limit($total) 615 ->get() 616 ->map(Family::rowMapper()) 617 ->filter(GedcomRecord::accessFilter()) 618 ->map(function (Family $family): array { 619 return [ 620 'family' => $family, 621 'count' => $family->numberOfChildren(), 622 ]; 623 }) 624 ->all(); 625 } 626 627 /** 628 * The the families with the most children. 629 * 630 * @param int $total 631 * 632 * @return string 633 */ 634 public function topTenLargestFamily(int $total = 10): string 635 { 636 $records = $this->topTenFamilyQuery($total); 637 638 return view( 639 'statistics/families/top10-nolist', 640 [ 641 'records' => $records, 642 ] 643 ); 644 } 645 646 /** 647 * Find the families with the most children. 648 * 649 * @param int $total 650 * 651 * @return string 652 */ 653 public function topTenLargestFamilyList(int $total = 10): string 654 { 655 $records = $this->topTenFamilyQuery($total); 656 657 return view( 658 'statistics/families/top10-list', 659 [ 660 'records' => $records, 661 ] 662 ); 663 } 664 665 /** 666 * Create a chart of the largest families. 667 * 668 * @param string|null $color_from 669 * @param string|null $color_to 670 * @param int $total 671 * 672 * @return string 673 */ 674 public function chartLargestFamilies( 675 string $color_from = null, 676 string $color_to = null, 677 int $total = 10 678 ): string { 679 return (new ChartFamilyLargest($this->tree)) 680 ->chartLargestFamilies($color_from, $color_to, $total); 681 } 682 683 /** 684 * Find the month in the year of the birth of the first child. 685 * 686 * @param bool $sex 687 * 688 * @return stdClass[] 689 */ 690 public function monthFirstChildQuery(bool $sex = false): array 691 { 692 $first_child_subquery = DB::table('link') 693 ->join('dates', function (JoinClause $join): void { 694 $join 695 ->on('d_gid', '=', 'l_to') 696 ->on('d_file', '=', 'l_file') 697 ->where('d_julianday1', '<>', 0) 698 ->whereIn('d_month', ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']); 699 }) 700 ->where('l_file', '=', $this->tree->id()) 701 ->where('l_type', '=', 'CHIL') 702 ->select(['l_from AS family_id', DB::raw('MIN(d_julianday1) AS min_birth_jd')]) 703 ->groupBy('family_id'); 704 705 $query = DB::table('link') 706 ->join('dates', function (JoinClause $join): void { 707 $join 708 ->on('d_gid', '=', 'l_to') 709 ->on('d_file', '=', 'l_file'); 710 }) 711 ->joinSub($first_child_subquery, 'subquery', function (JoinClause $join): void { 712 $join 713 ->on('family_id', '=', 'l_from') 714 ->on('min_birth_jd', '=', 'd_julianday1'); 715 }) 716 ->where('link.l_file', '=', $this->tree->id()) 717 ->where('link.l_type', '=', 'CHIL'); 718 719 720 if ($sex) { 721 $query 722 ->join('individuals', function (JoinClause $join) use ($sex): void { 723 $join 724 ->on('i_file', '=', 'l_file') 725 ->on('i_id', '=', 'l_to'); 726 }) 727 ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')]) 728 ->groupBy(['d_month', 'i_sex']); 729 } else { 730 $query 731 ->select(['d_month', DB::raw('COUNT(*) AS total')]) 732 ->groupBy(['d_month']); 733 } 734 735 return $query 736 ->get() 737 ->all(); 738 } 739 740 /** 741 * Number of husbands. 742 * 743 * @return string 744 */ 745 public function totalMarriedMales(): string 746 { 747 $n = (int) DB::table('families') 748 ->where('f_file', '=', $this->tree->id()) 749 ->where('f_gedcom', 'LIKE', "%\n1 MARR%") 750 ->distinct() 751 ->count('f_husb'); 752 753 return I18N::number($n); 754 } 755 756 /** 757 * Number of wives. 758 * 759 * @return string 760 */ 761 public function totalMarriedFemales(): string 762 { 763 $n = (int) DB::table('families') 764 ->where('f_file', '=', $this->tree->id()) 765 ->where('f_gedcom', 'LIKE', "%\n1 MARR%") 766 ->distinct() 767 ->count('f_wife'); 768 769 return I18N::number($n); 770 } 771 772 /** 773 * General query on parents. 774 * 775 * @param string $type 776 * @param string $age_dir 777 * @param string $sex 778 * @param bool $show_years 779 * 780 * @return string 781 */ 782 private function parentsQuery(string $type, string $age_dir, string $sex, bool $show_years): string 783 { 784 if ($sex === 'F') { 785 $sex_field = 'WIFE'; 786 } else { 787 $sex_field = 'HUSB'; 788 } 789 790 if ($age_dir !== 'ASC') { 791 $age_dir = 'DESC'; 792 } 793 794 $prefix = DB::connection()->getTablePrefix(); 795 796 $row = DB::table('link AS parentfamily') 797 ->join('link AS childfamily', function (JoinClause $join): void { 798 $join 799 ->on('childfamily.l_file', '=', 'parentfamily.l_file') 800 ->on('childfamily.l_from', '=', 'parentfamily.l_from') 801 ->where('childfamily.l_type', '=', 'CHIL'); 802 }) 803 ->join('dates AS birth', function (JoinClause $join): void { 804 $join 805 ->on('birth.d_file', '=', 'parentfamily.l_file') 806 ->on('birth.d_gid', '=', 'parentfamily.l_to') 807 ->where('birth.d_fact', '=', 'BIRT') 808 ->where('birth.d_julianday1', '<>', 0); 809 }) 810 ->join('dates AS childbirth', function (JoinClause $join): void { 811 $join 812 ->on('childbirth.d_file', '=', 'parentfamily.l_file') 813 ->on('childbirth.d_gid', '=', 'childfamily.l_to') 814 ->where('childbirth.d_fact', '=', 'BIRT'); 815 }) 816 ->where('childfamily.l_file', '=', $this->tree->id()) 817 ->where('parentfamily.l_type', '=', $sex_field) 818 ->where('childbirth.d_julianday2', '>', 'birth.d_julianday1') 819 ->select(['parentfamily.l_to AS id', DB::raw($prefix . 'childbirth.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')]) 820 ->take(1) 821 ->orderBy('age', $age_dir) 822 ->get() 823 ->first(); 824 825 if ($row === null) { 826 return ''; 827 } 828 829 $person = Individual::getInstance($row->id, $this->tree); 830 831 switch ($type) { 832 default: 833 case 'full': 834 if ($person && $person->canShow()) { 835 $result = $person->formatList(); 836 } else { 837 $result = I18N::translate('This information is private and cannot be shown.'); 838 } 839 break; 840 841 case 'name': 842 $result = '<a href="' . e($person->url()) . '">' . $person->fullName() . '</a>'; 843 break; 844 845 case 'age': 846 $age = $row->age; 847 848 if ($show_years) { 849 $result = $this->calculateAge((int) $row->age); 850 } else { 851 $result = (string) floor($age / 365.25); 852 } 853 854 break; 855 } 856 857 return $result; 858 } 859 860 /** 861 * Find the youngest mother 862 * 863 * @return string 864 */ 865 public function youngestMother(): string 866 { 867 return $this->parentsQuery('full', 'ASC', 'F', false); 868 } 869 870 /** 871 * Find the name of the youngest mother. 872 * 873 * @return string 874 */ 875 public function youngestMotherName(): string 876 { 877 return $this->parentsQuery('name', 'ASC', 'F', false); 878 } 879 880 /** 881 * Find the age of the youngest mother. 882 * 883 * @param string $show_years 884 * 885 * @return string 886 */ 887 public function youngestMotherAge(string $show_years = ''): string 888 { 889 return $this->parentsQuery('age', 'ASC', 'F', (bool) $show_years); 890 } 891 892 /** 893 * Find the oldest mother. 894 * 895 * @return string 896 */ 897 public function oldestMother(): string 898 { 899 return $this->parentsQuery('full', 'DESC', 'F', false); 900 } 901 902 /** 903 * Find the name of the oldest mother. 904 * 905 * @return string 906 */ 907 public function oldestMotherName(): string 908 { 909 return $this->parentsQuery('name', 'DESC', 'F', false); 910 } 911 912 /** 913 * Find the age of the oldest mother. 914 * 915 * @param string $show_years 916 * 917 * @return string 918 */ 919 public function oldestMotherAge(string $show_years = ''): string 920 { 921 return $this->parentsQuery('age', 'DESC', 'F', (bool) $show_years); 922 } 923 924 /** 925 * Find the youngest father. 926 * 927 * @return string 928 */ 929 public function youngestFather(): string 930 { 931 return $this->parentsQuery('full', 'ASC', 'M', false); 932 } 933 934 /** 935 * Find the name of the youngest father. 936 * 937 * @return string 938 */ 939 public function youngestFatherName(): string 940 { 941 return $this->parentsQuery('name', 'ASC', 'M', false); 942 } 943 944 /** 945 * Find the age of the youngest father. 946 * 947 * @param string $show_years 948 * 949 * @return string 950 */ 951 public function youngestFatherAge(string $show_years = ''): string 952 { 953 return $this->parentsQuery('age', 'ASC', 'M', (bool) $show_years); 954 } 955 956 /** 957 * Find the oldest father. 958 * 959 * @return string 960 */ 961 public function oldestFather(): string 962 { 963 return $this->parentsQuery('full', 'DESC', 'M', false); 964 } 965 966 /** 967 * Find the name of the oldest father. 968 * 969 * @return string 970 */ 971 public function oldestFatherName(): string 972 { 973 return $this->parentsQuery('name', 'DESC', 'M', false); 974 } 975 976 /** 977 * Find the age of the oldest father. 978 * 979 * @param string $show_years 980 * 981 * @return string 982 */ 983 public function oldestFatherAge(string $show_years = ''): string 984 { 985 return $this->parentsQuery('age', 'DESC', 'M', (bool) $show_years); 986 } 987 988 /** 989 * General query on age at marriage. 990 * 991 * @param string $type 992 * @param string $age_dir "ASC" or "DESC" 993 * @param int $total 994 * 995 * @return string 996 */ 997 private function ageOfMarriageQuery(string $type, string $age_dir, int $total): string 998 { 999 $prefix = DB::connection()->getTablePrefix(); 1000 1001 $hrows = DB::table('families') 1002 ->where('f_file', '=', $this->tree->id()) 1003 ->join('dates AS married', function (JoinClause $join): void { 1004 $join 1005 ->on('married.d_file', '=', 'f_file') 1006 ->on('married.d_gid', '=', 'f_id') 1007 ->where('married.d_fact', '=', 'MARR') 1008 ->where('married.d_julianday1', '<>', 0); 1009 }) 1010 ->join('dates AS husbdeath', function (JoinClause $join): void { 1011 $join 1012 ->on('husbdeath.d_gid', '=', 'f_husb') 1013 ->on('husbdeath.d_file', '=', 'f_file') 1014 ->where('husbdeath.d_fact', '=', 'DEAT'); 1015 }) 1016 ->whereColumn('married.d_julianday1', '<', 'husbdeath.d_julianday2') 1017 ->groupBy('f_id') 1018 ->select(['f_id AS family', DB::raw('MIN(' . $prefix . 'husbdeath.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')]) 1019 ->get() 1020 ->all(); 1021 1022 $wrows = DB::table('families') 1023 ->where('f_file', '=', $this->tree->id()) 1024 ->join('dates AS married', function (JoinClause $join): void { 1025 $join 1026 ->on('married.d_file', '=', 'f_file') 1027 ->on('married.d_gid', '=', 'f_id') 1028 ->where('married.d_fact', '=', 'MARR') 1029 ->where('married.d_julianday1', '<>', 0); 1030 }) 1031 ->join('dates AS wifedeath', function (JoinClause $join): void { 1032 $join 1033 ->on('wifedeath.d_gid', '=', 'f_wife') 1034 ->on('wifedeath.d_file', '=', 'f_file') 1035 ->where('wifedeath.d_fact', '=', 'DEAT'); 1036 }) 1037 ->whereColumn('married.d_julianday1', '<', 'wifedeath.d_julianday2') 1038 ->groupBy('f_id') 1039 ->select(['f_id AS family', DB::raw('MIN(' . $prefix . 'wifedeath.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')]) 1040 ->get() 1041 ->all(); 1042 1043 $drows = DB::table('families') 1044 ->where('f_file', '=', $this->tree->id()) 1045 ->join('dates AS married', function (JoinClause $join): void { 1046 $join 1047 ->on('married.d_file', '=', 'f_file') 1048 ->on('married.d_gid', '=', 'f_id') 1049 ->where('married.d_fact', '=', 'MARR') 1050 ->where('married.d_julianday1', '<>', 0); 1051 }) 1052 ->join('dates AS divorced', function (JoinClause $join): void { 1053 $join 1054 ->on('divorced.d_gid', '=', 'f_id') 1055 ->on('divorced.d_file', '=', 'f_file') 1056 ->whereIn('divorced.d_fact', ['DIV', 'ANUL', '_SEPR']); 1057 }) 1058 ->whereColumn('married.d_julianday1', '<', 'divorced.d_julianday2') 1059 ->groupBy('f_id') 1060 ->select(['f_id AS family', DB::raw('MIN(' . $prefix . 'divorced.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')]) 1061 ->get() 1062 ->all(); 1063 1064 $rows = []; 1065 foreach ($drows as $family) { 1066 $rows[$family->family] = $family->age; 1067 } 1068 1069 foreach ($hrows as $family) { 1070 if (!isset($rows[$family->family])) { 1071 $rows[$family->family] = $family->age; 1072 } 1073 } 1074 1075 foreach ($wrows as $family) { 1076 if (!isset($rows[$family->family])) { 1077 $rows[$family->family] = $family->age; 1078 } elseif ($rows[$family->family] > $family->age) { 1079 $rows[$family->family] = $family->age; 1080 } 1081 } 1082 1083 if ($age_dir === 'DESC') { 1084 arsort($rows); 1085 } else { 1086 asort($rows); 1087 } 1088 1089 $top10 = []; 1090 $i = 0; 1091 foreach ($rows as $fam => $age) { 1092 $family = Family::getInstance($fam, $this->tree); 1093 if ($type === 'name') { 1094 return $family->formatList(); 1095 } 1096 1097 $age = $this->calculateAge((int) $age); 1098 1099 if ($type === 'age') { 1100 return $age; 1101 } 1102 1103 $husb = $family->husband(); 1104 $wife = $family->wife(); 1105 1106 if (($husb && ($husb->getAllDeathDates() || !$husb->isDead())) 1107 && ($wife && ($wife->getAllDeathDates() || !$wife->isDead())) 1108 ) { 1109 if ($family && $family->canShow()) { 1110 if ($type === 'list') { 1111 $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')' . '</li>'; 1112 } else { 1113 $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')'; 1114 } 1115 } 1116 if (++$i === $total) { 1117 break; 1118 } 1119 } 1120 } 1121 1122 if ($type === 'list') { 1123 $top10 = implode('', $top10); 1124 } else { 1125 $top10 = implode('; ', $top10); 1126 } 1127 1128 if (I18N::direction() === 'rtl') { 1129 $top10 = str_replace([ 1130 '[', 1131 ']', 1132 '(', 1133 ')', 1134 '+', 1135 ], [ 1136 '‏[', 1137 '‏]', 1138 '‏(', 1139 '‏)', 1140 '‏+', 1141 ], $top10); 1142 } 1143 1144 if ($type === 'list') { 1145 return '<ul>' . $top10 . '</ul>'; 1146 } 1147 1148 return $top10; 1149 } 1150 1151 /** 1152 * General query on marriage ages. 1153 * 1154 * @return string 1155 */ 1156 public function topAgeOfMarriageFamily(): string 1157 { 1158 return $this->ageOfMarriageQuery('name', 'DESC', 1); 1159 } 1160 1161 /** 1162 * General query on marriage ages. 1163 * 1164 * @return string 1165 */ 1166 public function topAgeOfMarriage(): string 1167 { 1168 return $this->ageOfMarriageQuery('age', 'DESC', 1); 1169 } 1170 1171 /** 1172 * General query on marriage ages. 1173 * 1174 * @param int $total 1175 * 1176 * @return string 1177 */ 1178 public function topAgeOfMarriageFamilies(int $total = 10): string 1179 { 1180 return $this->ageOfMarriageQuery('nolist', 'DESC', $total); 1181 } 1182 1183 /** 1184 * General query on marriage ages. 1185 * 1186 * @param int $total 1187 * 1188 * @return string 1189 */ 1190 public function topAgeOfMarriageFamiliesList(int $total = 10): string 1191 { 1192 return $this->ageOfMarriageQuery('list', 'DESC', $total); 1193 } 1194 1195 /** 1196 * General query on marriage ages. 1197 * 1198 * @return string 1199 */ 1200 public function minAgeOfMarriageFamily(): string 1201 { 1202 return $this->ageOfMarriageQuery('name', 'ASC', 1); 1203 } 1204 1205 /** 1206 * General query on marriage ages. 1207 * 1208 * @return string 1209 */ 1210 public function minAgeOfMarriage(): string 1211 { 1212 return $this->ageOfMarriageQuery('age', 'ASC', 1); 1213 } 1214 1215 /** 1216 * General query on marriage ages. 1217 * 1218 * @param int $total 1219 * 1220 * @return string 1221 */ 1222 public function minAgeOfMarriageFamilies(int $total = 10): string 1223 { 1224 return $this->ageOfMarriageQuery('nolist', 'ASC', $total); 1225 } 1226 1227 /** 1228 * General query on marriage ages. 1229 * 1230 * @param int $total 1231 * 1232 * @return string 1233 */ 1234 public function minAgeOfMarriageFamiliesList(int $total = 10): string 1235 { 1236 return $this->ageOfMarriageQuery('list', 'ASC', $total); 1237 } 1238 1239 /** 1240 * Find the ages between spouses. 1241 * 1242 * @param string $age_dir 1243 * @param int $total 1244 * 1245 * @return array 1246 */ 1247 private function ageBetweenSpousesQuery(string $age_dir, int $total): array 1248 { 1249 $prefix = DB::connection()->getTablePrefix(); 1250 1251 $query = DB::table('families') 1252 ->where('f_file', '=', $this->tree->id()) 1253 ->join('dates AS wife', function (JoinClause $join): void { 1254 $join 1255 ->on('wife.d_gid', '=', 'f_wife') 1256 ->on('wife.d_file', '=', 'f_file') 1257 ->where('wife.d_fact', '=', 'BIRT') 1258 ->where('wife.d_julianday1', '<>', 0); 1259 }) 1260 ->join('dates AS husb', function (JoinClause $join): void { 1261 $join 1262 ->on('husb.d_gid', '=', 'f_husb') 1263 ->on('husb.d_file', '=', 'f_file') 1264 ->where('husb.d_fact', '=', 'BIRT') 1265 ->where('husb.d_julianday1', '<>', 0); 1266 }); 1267 1268 if ($age_dir === 'DESC') { 1269 $query 1270 ->whereColumn('wife.d_julianday1', '>=', 'husb.d_julianday1') 1271 ->orderBy(DB::raw('MIN(' . $prefix . 'wife.d_julianday1) - MIN(' . $prefix . 'husb.d_julianday1)'), 'DESC'); 1272 } else { 1273 $query 1274 ->whereColumn('husb.d_julianday1', '>=', 'wife.d_julianday1') 1275 ->orderBy(DB::raw('MIN(' . $prefix . 'husb.d_julianday1) - MIN(' . $prefix . 'wife.d_julianday1)'), 'DESC'); 1276 } 1277 1278 $families = $query 1279 ->groupBy(['f_id', 'f_file']) 1280 ->select('families.*') 1281 ->take($total) 1282 ->get() 1283 ->map(Family::rowMapper()) 1284 ->filter(GedcomRecord::accessFilter()); 1285 1286 $top10 = []; 1287 1288 /** @var Family $family */ 1289 foreach ($families as $family) { 1290 $husb_birt_jd = $family->husband()->getBirthDate()->minimumJulianDay(); 1291 $wife_birt_jd = $family->wife()->getBirthDate()->minimumJulianDay(); 1292 1293 if ($age_dir === 'DESC') { 1294 $diff = $wife_birt_jd - $husb_birt_jd; 1295 } else { 1296 $diff = $husb_birt_jd - $wife_birt_jd; 1297 } 1298 1299 $top10[] = [ 1300 'family' => $family, 1301 'age' => $this->calculateAge($diff), 1302 ]; 1303 } 1304 1305 return $top10; 1306 } 1307 1308 /** 1309 * Find the age between husband and wife. 1310 * 1311 * @param int $total 1312 * 1313 * @return string 1314 */ 1315 public function ageBetweenSpousesMF(int $total = 10): string 1316 { 1317 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1318 1319 return view( 1320 'statistics/families/top10-nolist-spouses', 1321 [ 1322 'records' => $records, 1323 ] 1324 ); 1325 } 1326 1327 /** 1328 * Find the age between husband and wife. 1329 * 1330 * @param int $total 1331 * 1332 * @return string 1333 */ 1334 public function ageBetweenSpousesMFList(int $total = 10): string 1335 { 1336 $records = $this->ageBetweenSpousesQuery('DESC', $total); 1337 1338 return view( 1339 'statistics/families/top10-list-spouses', 1340 [ 1341 'records' => $records, 1342 ] 1343 ); 1344 } 1345 1346 /** 1347 * Find the age between wife and husband.. 1348 * 1349 * @param int $total 1350 * 1351 * @return string 1352 */ 1353 public function ageBetweenSpousesFM(int $total = 10): string 1354 { 1355 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1356 1357 return view( 1358 'statistics/families/top10-nolist-spouses', 1359 [ 1360 'records' => $records, 1361 ] 1362 ); 1363 } 1364 1365 /** 1366 * Find the age between wife and husband.. 1367 * 1368 * @param int $total 1369 * 1370 * @return string 1371 */ 1372 public function ageBetweenSpousesFMList(int $total = 10): string 1373 { 1374 $records = $this->ageBetweenSpousesQuery('ASC', $total); 1375 1376 return view( 1377 'statistics/families/top10-list-spouses', 1378 [ 1379 'records' => $records, 1380 ] 1381 ); 1382 } 1383 1384 /** 1385 * General query on ages at marriage. 1386 * 1387 * @param string $sex "M" or "F" 1388 * @param int $year1 1389 * @param int $year2 1390 * 1391 * @return array 1392 */ 1393 public function statsMarrAgeQuery($sex, $year1 = -1, $year2 = -1): array 1394 { 1395 $prefix = DB::connection()->getTablePrefix(); 1396 1397 $query = DB::table('dates AS married') 1398 ->join('families', function (JoinClause $join): void { 1399 $join 1400 ->on('f_file', '=', 'married.d_file') 1401 ->on('f_id', '=', 'married.d_gid'); 1402 }) 1403 ->join('dates AS birth', function (JoinClause $join) use ($sex): void { 1404 $join 1405 ->on('birth.d_file', '=', 'married.d_file') 1406 ->on('birth.d_gid', '=', $sex === 'M' ? 'f_husb' : 'f_wife') 1407 ->where('birth.d_julianday1', '<>', 0) 1408 ->where('birth.d_fact', '=', 'BIRT') 1409 ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']); 1410 }) 1411 ->where('married.d_file', '=', $this->tree->id()) 1412 ->where('married.d_fact', '=', 'MARR') 1413 ->whereIn('married.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']) 1414 ->whereColumn('married.d_julianday1', '>', 'birth.d_julianday1') 1415 ->select(['f_id', 'birth.d_gid', DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')]); 1416 1417 if ($year1 >= 0 && $year2 >= 0) { 1418 $query->whereBetween('married.d_year', [$year1, $year2]); 1419 } 1420 1421 return $query 1422 ->get() 1423 ->map(function (stdClass $row): stdClass { 1424 $row->age = (int) $row->age; 1425 1426 return $row; 1427 }) 1428 ->all(); 1429 } 1430 1431 /** 1432 * General query on marriage ages. 1433 * 1434 * @return string 1435 */ 1436 public function statsMarrAge(): string 1437 { 1438 return (new ChartMarriageAge($this->tree)) 1439 ->chartMarriageAge(); 1440 } 1441 1442 /** 1443 * Query the database for marriage tags. 1444 * 1445 * @param string $type "full", "name" or "age" 1446 * @param string $age_dir "ASC" or "DESC" 1447 * @param string $sex "F" or "M" 1448 * @param bool $show_years 1449 * 1450 * @return string 1451 */ 1452 private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string 1453 { 1454 if ($sex === 'F') { 1455 $sex_field = 'f_wife'; 1456 } else { 1457 $sex_field = 'f_husb'; 1458 } 1459 1460 if ($age_dir !== 'ASC') { 1461 $age_dir = 'DESC'; 1462 } 1463 1464 $prefix = DB::connection()->getTablePrefix(); 1465 1466 $row = DB::table('families') 1467 ->join('dates AS married', function (JoinClause $join): void { 1468 $join 1469 ->on('married.d_file', '=', 'f_file') 1470 ->on('married.d_gid', '=', 'f_id') 1471 ->where('married.d_fact', '=', 'MARR'); 1472 }) 1473 ->join('individuals', function (JoinClause $join) use ($sex, $sex_field): void { 1474 $join 1475 ->on('i_file', '=', 'f_file') 1476 ->on('i_id', '=', $sex_field) 1477 ->where('i_sex', '=', $sex); 1478 }) 1479 ->join('dates AS birth', function (JoinClause $join): void { 1480 $join 1481 ->on('birth.d_file', '=', 'i_file') 1482 ->on('birth.d_gid', '=', 'i_id') 1483 ->where('birth.d_fact', '=', 'BIRT') 1484 ->where('birth.d_julianday1', '<>', 0); 1485 }) 1486 ->where('f_file', '=', $this->tree->id()) 1487 ->where('married.d_julianday2', '>', 'birth.d_julianday1') 1488 ->orderBy(DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1'), $age_dir) 1489 ->select(['f_id AS famid', $sex_field, DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age'), 'i_id']) 1490 ->take(1) 1491 ->get() 1492 ->first(); 1493 1494 if ($row === null) { 1495 return ''; 1496 } 1497 1498 $family = Family::getInstance($row->famid, $this->tree); 1499 $person = Individual::getInstance($row->i_id, $this->tree); 1500 1501 switch ($type) { 1502 default: 1503 case 'full': 1504 if ($family && $family->canShow()) { 1505 $result = $family->formatList(); 1506 } else { 1507 $result = I18N::translate('This information is private and cannot be shown.'); 1508 } 1509 break; 1510 1511 case 'name': 1512 $result = '<a href="' . e($family->url()) . '">' . $person->fullName() . '</a>'; 1513 break; 1514 1515 case 'age': 1516 $age = $row->age; 1517 1518 if ($show_years) { 1519 $result = $this->calculateAge((int) $row->age); 1520 } else { 1521 $result = I18N::number((int) ($age / 365.25)); 1522 } 1523 1524 break; 1525 } 1526 1527 return $result; 1528 } 1529 1530 /** 1531 * Find the youngest wife. 1532 * 1533 * @return string 1534 */ 1535 public function youngestMarriageFemale(): string 1536 { 1537 return $this->marriageQuery('full', 'ASC', 'F', false); 1538 } 1539 1540 /** 1541 * Find the name of the youngest wife. 1542 * 1543 * @return string 1544 */ 1545 public function youngestMarriageFemaleName(): string 1546 { 1547 return $this->marriageQuery('name', 'ASC', 'F', false); 1548 } 1549 1550 /** 1551 * Find the age of the youngest wife. 1552 * 1553 * @param string $show_years 1554 * 1555 * @return string 1556 */ 1557 public function youngestMarriageFemaleAge(string $show_years = ''): string 1558 { 1559 return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years); 1560 } 1561 1562 /** 1563 * Find the oldest wife. 1564 * 1565 * @return string 1566 */ 1567 public function oldestMarriageFemale(): string 1568 { 1569 return $this->marriageQuery('full', 'DESC', 'F', false); 1570 } 1571 1572 /** 1573 * Find the name of the oldest wife. 1574 * 1575 * @return string 1576 */ 1577 public function oldestMarriageFemaleName(): string 1578 { 1579 return $this->marriageQuery('name', 'DESC', 'F', false); 1580 } 1581 1582 /** 1583 * Find the age of the oldest wife. 1584 * 1585 * @param string $show_years 1586 * 1587 * @return string 1588 */ 1589 public function oldestMarriageFemaleAge(string $show_years = ''): string 1590 { 1591 return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years); 1592 } 1593 1594 /** 1595 * Find the youngest husband. 1596 * 1597 * @return string 1598 */ 1599 public function youngestMarriageMale(): string 1600 { 1601 return $this->marriageQuery('full', 'ASC', 'M', false); 1602 } 1603 1604 /** 1605 * Find the name of the youngest husband. 1606 * 1607 * @return string 1608 */ 1609 public function youngestMarriageMaleName(): string 1610 { 1611 return $this->marriageQuery('name', 'ASC', 'M', false); 1612 } 1613 1614 /** 1615 * Find the age of the youngest husband. 1616 * 1617 * @param string $show_years 1618 * 1619 * @return string 1620 */ 1621 public function youngestMarriageMaleAge(string $show_years = ''): string 1622 { 1623 return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years); 1624 } 1625 1626 /** 1627 * Find the oldest husband. 1628 * 1629 * @return string 1630 */ 1631 public function oldestMarriageMale(): string 1632 { 1633 return $this->marriageQuery('full', 'DESC', 'M', false); 1634 } 1635 1636 /** 1637 * Find the name of the oldest husband. 1638 * 1639 * @return string 1640 */ 1641 public function oldestMarriageMaleName(): string 1642 { 1643 return $this->marriageQuery('name', 'DESC', 'M', false); 1644 } 1645 1646 /** 1647 * Find the age of the oldest husband. 1648 * 1649 * @param string $show_years 1650 * 1651 * @return string 1652 */ 1653 public function oldestMarriageMaleAge(string $show_years = ''): string 1654 { 1655 return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years); 1656 } 1657 1658 /** 1659 * General query on marriages. 1660 * 1661 * @param int $year1 1662 * @param int $year2 1663 * 1664 * @return Builder 1665 */ 1666 public function statsMarriageQuery(int $year1 = -1, int $year2 = -1): Builder 1667 { 1668 $query = DB::table('dates') 1669 ->where('d_file', '=', $this->tree->id()) 1670 ->where('d_fact', '=', 'MARR') 1671 ->select(['d_month', DB::raw('COUNT(*) AS total')]) 1672 ->groupBy('d_month'); 1673 1674 if ($year1 >= 0 && $year2 >= 0) { 1675 $query->whereBetween('d_year', [$year1, $year2]); 1676 } 1677 1678 return $query; 1679 } 1680 1681 /** 1682 * General query on marriages. 1683 * 1684 * @param int $year1 1685 * @param int $year2 1686 * 1687 * @return Builder 1688 */ 1689 public function statsFirstMarriageQuery(int $year1 = -1, int $year2 = -1): Builder 1690 { 1691 $query = DB::table('families') 1692 ->join('dates', function (JoinClause $join): void { 1693 $join 1694 ->on('d_gid', '=', 'f_id') 1695 ->on('d_file', '=', 'f_file') 1696 ->where('d_fact', '=', 'MARR') 1697 ->where('d_julianday2', '<>', 0); 1698 })->join('individuals', function (JoinClause $join): void { 1699 $join 1700 ->on('i_file', '=', 'f_file'); 1701 }) 1702 ->where('f_file', '=', $this->tree->id()) 1703 ->where(function (Builder $query): void { 1704 $query 1705 ->whereColumn('i_id', '=', 'f_husb') 1706 ->orWhereColumn('i_id', '=', 'f_wife'); 1707 }); 1708 1709 if ($year1 >= 0 && $year2 >= 0) { 1710 $query->whereBetween('d_year', [$year1, $year2]); 1711 } 1712 1713 return $query 1714 ->select(['f_id AS fams', 'f_husb', 'f_wife', 'd_julianday2 AS age', 'd_month AS month', 'i_id AS indi']) 1715 ->orderBy('f_id') 1716 ->orderBy('i_id') 1717 ->orderBy('d_julianday2'); 1718 } 1719 1720 /** 1721 * General query on marriages. 1722 * 1723 * @param string|null $color_from 1724 * @param string|null $color_to 1725 * 1726 * @return string 1727 */ 1728 public function statsMarr(string $color_from = null, string $color_to = null): string 1729 { 1730 return (new ChartMarriage($this->tree)) 1731 ->chartMarriage($color_from, $color_to); 1732 } 1733 1734 /** 1735 * General divorce query. 1736 * 1737 * @param string|null $color_from 1738 * @param string|null $color_to 1739 * 1740 * @return string 1741 */ 1742 public function statsDiv(string $color_from = null, string $color_to = null): string 1743 { 1744 return (new ChartDivorce($this->tree)) 1745 ->chartDivorce($color_from, $color_to); 1746 } 1747} 1748