xref: /webtrees/app/Statistics/Repository/FamilyRepository.php (revision 1aafee7a88266ac6f741344d71884942a844d174)
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): void {
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): void {
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): void {
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('statistics/families/top10-nolist', [
639            'records' => $records,
640        ]);
641    }
642
643    /**
644     * Find the families with the most children.
645     *
646     * @param int $total
647     *
648     * @return string
649     */
650    public function topTenLargestFamilyList(int $total = 10): string
651    {
652        $records = $this->topTenFamilyQuery($total);
653
654        return view('statistics/families/top10-list', [
655            'records' => $records,
656        ]);
657    }
658
659    /**
660     * Create a chart of the largest families.
661     *
662     * @param string|null $color_from
663     * @param string|null $color_to
664     * @param int         $total
665     *
666     * @return string
667     */
668    public function chartLargestFamilies(
669        string $color_from = null,
670        string $color_to = null,
671        int $total = 10
672    ): string {
673        return (new ChartFamilyLargest($this->tree))
674            ->chartLargestFamilies($color_from, $color_to, $total);
675    }
676
677    /**
678     * Find the month in the year of the birth of the first child.
679     *
680     * @param int $year1
681     * @param int $year2
682     *
683     * @return Builder
684     */
685    public function monthFirstChildQuery(int $year1 = -1, int $year2 = -1): Builder
686    {
687        $first_child_subquery = DB::table('link')
688            ->join('dates', function (JoinClause $join): void {
689                $join
690                    ->on('d_gid', '=', 'l_to')
691                    ->on('d_file', '=', 'l_file')
692                    ->where('d_julianday1', '<>', 0)
693                    ->whereIn('d_month', ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']);
694            })
695            ->where('l_file', '=', $this->tree->id())
696            ->where('l_type', '=', 'CHIL')
697            ->select(['l_from AS family_id', DB::raw('MIN(d_julianday1) AS min_birth_jd')])
698            ->groupBy('family_id');
699
700        $query = DB::table('link')
701            ->join('dates', function (JoinClause $join): void {
702                $join
703                    ->on('d_gid', '=', 'l_to')
704                    ->on('d_file', '=', 'l_file');
705            })
706            ->joinSub($first_child_subquery, 'subquery', function (JoinClause $join): void {
707                $join
708                    ->on('family_id', '=', 'l_from')
709                    ->on('min_birth_jd', '=', 'd_julianday1');
710            })
711            ->where('link.l_file', '=', $this->tree->id())
712            ->where('link.l_type', '=', 'CHIL')
713            ->select(['d_month', DB::raw('COUNT(*) AS total')])
714            ->groupBy(['d_month']);
715
716        if ($year1 >= 0 && $year2 >= 0) {
717            $query->whereBetween('d_year', [$year1, $year2]);
718        }
719
720        return $query;
721    }
722
723    /**
724     * Find the month in the year of the birth of the first child.
725     *
726     * @param int $year1
727     * @param int $year2
728     *
729     * @return Builder
730     */
731    public function monthFirstChildBySexQuery(int $year1 = -1, int $year2 = -1): Builder
732    {
733        return $this->monthFirstChildQuery($year1, $year2)
734                ->join('individuals', function (JoinClause $join): void {
735                    $join
736                        ->on('i_file', '=', 'l_file')
737                        ->on('i_id', '=', 'l_to');
738                })
739                ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')])
740                ->groupBy(['d_month', 'i_sex']);
741    }
742
743    /**
744     * Number of husbands.
745     *
746     * @return string
747     */
748    public function totalMarriedMales(): string
749    {
750        $n = (int) DB::table('families')
751            ->where('f_file', '=', $this->tree->id())
752            ->where('f_gedcom', 'LIKE', "%\n1 MARR%")
753            ->distinct()
754            ->count('f_husb');
755
756        return I18N::number($n);
757    }
758
759    /**
760     * Number of wives.
761     *
762     * @return string
763     */
764    public function totalMarriedFemales(): string
765    {
766        $n = (int) DB::table('families')
767            ->where('f_file', '=', $this->tree->id())
768            ->where('f_gedcom', 'LIKE', "%\n1 MARR%")
769            ->distinct()
770            ->count('f_wife');
771
772        return I18N::number($n);
773    }
774
775    /**
776     * General query on parents.
777     *
778     * @param string $type
779     * @param string $age_dir
780     * @param string $sex
781     * @param bool   $show_years
782     *
783     * @return string
784     */
785    private function parentsQuery(string $type, string $age_dir, string $sex, bool $show_years): string
786    {
787        if ($sex === 'F') {
788            $sex_field = 'WIFE';
789        } else {
790            $sex_field = 'HUSB';
791        }
792
793        if ($age_dir !== 'ASC') {
794            $age_dir = 'DESC';
795        }
796
797        $prefix = DB::connection()->getTablePrefix();
798
799        $row = DB::table('link AS parentfamily')
800            ->join('link AS childfamily', function (JoinClause $join): void {
801                $join
802                    ->on('childfamily.l_file', '=', 'parentfamily.l_file')
803                    ->on('childfamily.l_from', '=', 'parentfamily.l_from')
804                    ->where('childfamily.l_type', '=', 'CHIL');
805            })
806            ->join('dates AS birth', function (JoinClause $join): void {
807                $join
808                    ->on('birth.d_file', '=', 'parentfamily.l_file')
809                    ->on('birth.d_gid', '=', 'parentfamily.l_to')
810                    ->where('birth.d_fact', '=', 'BIRT')
811                    ->where('birth.d_julianday1', '<>', 0);
812            })
813            ->join('dates AS childbirth', function (JoinClause $join): void {
814                $join
815                    ->on('childbirth.d_file', '=', 'parentfamily.l_file')
816                    ->on('childbirth.d_gid', '=', 'childfamily.l_to')
817                    ->where('childbirth.d_fact', '=', 'BIRT');
818            })
819            ->where('childfamily.l_file', '=', $this->tree->id())
820            ->where('parentfamily.l_type', '=', $sex_field)
821            ->where('childbirth.d_julianday2', '>', 'birth.d_julianday1')
822            ->select(['parentfamily.l_to AS id', DB::raw($prefix . 'childbirth.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')])
823            ->take(1)
824            ->orderBy('age', $age_dir)
825            ->get()
826            ->first();
827
828        if ($row === null) {
829            return '';
830        }
831
832        $person = Individual::getInstance($row->id, $this->tree);
833
834        switch ($type) {
835            default:
836            case 'full':
837                if ($person && $person->canShow()) {
838                    $result = $person->formatList();
839                } else {
840                    $result = I18N::translate('This information is private and cannot be shown.');
841                }
842                break;
843
844            case 'name':
845                $result = '<a href="' . e($person->url()) . '">' . $person->fullName() . '</a>';
846                break;
847
848            case 'age':
849                $age = $row->age;
850
851                if ($show_years) {
852                    $result = $this->calculateAge((int) $row->age);
853                } else {
854                    $result = (string) floor($age / 365.25);
855                }
856
857                break;
858        }
859
860        return $result;
861    }
862
863    /**
864     * Find the youngest mother
865     *
866     * @return string
867     */
868    public function youngestMother(): string
869    {
870        return $this->parentsQuery('full', 'ASC', 'F', false);
871    }
872
873    /**
874     * Find the name of the youngest mother.
875     *
876     * @return string
877     */
878    public function youngestMotherName(): string
879    {
880        return $this->parentsQuery('name', 'ASC', 'F', false);
881    }
882
883    /**
884     * Find the age of the youngest mother.
885     *
886     * @param string $show_years
887     *
888     * @return string
889     */
890    public function youngestMotherAge(string $show_years = ''): string
891    {
892        return $this->parentsQuery('age', 'ASC', 'F', (bool) $show_years);
893    }
894
895    /**
896     * Find the oldest mother.
897     *
898     * @return string
899     */
900    public function oldestMother(): string
901    {
902        return $this->parentsQuery('full', 'DESC', 'F', false);
903    }
904
905    /**
906     * Find the name of the oldest mother.
907     *
908     * @return string
909     */
910    public function oldestMotherName(): string
911    {
912        return $this->parentsQuery('name', 'DESC', 'F', false);
913    }
914
915    /**
916     * Find the age of the oldest mother.
917     *
918     * @param string $show_years
919     *
920     * @return string
921     */
922    public function oldestMotherAge(string $show_years = ''): string
923    {
924        return $this->parentsQuery('age', 'DESC', 'F', (bool) $show_years);
925    }
926
927    /**
928     * Find the youngest father.
929     *
930     * @return string
931     */
932    public function youngestFather(): string
933    {
934        return $this->parentsQuery('full', 'ASC', 'M', false);
935    }
936
937    /**
938     * Find the name of the youngest father.
939     *
940     * @return string
941     */
942    public function youngestFatherName(): string
943    {
944        return $this->parentsQuery('name', 'ASC', 'M', false);
945    }
946
947    /**
948     * Find the age of the youngest father.
949     *
950     * @param string $show_years
951     *
952     * @return string
953     */
954    public function youngestFatherAge(string $show_years = ''): string
955    {
956        return $this->parentsQuery('age', 'ASC', 'M', (bool) $show_years);
957    }
958
959    /**
960     * Find the oldest father.
961     *
962     * @return string
963     */
964    public function oldestFather(): string
965    {
966        return $this->parentsQuery('full', 'DESC', 'M', false);
967    }
968
969    /**
970     * Find the name of the oldest father.
971     *
972     * @return string
973     */
974    public function oldestFatherName(): string
975    {
976        return $this->parentsQuery('name', 'DESC', 'M', false);
977    }
978
979    /**
980     * Find the age of the oldest father.
981     *
982     * @param string $show_years
983     *
984     * @return string
985     */
986    public function oldestFatherAge(string $show_years = ''): string
987    {
988        return $this->parentsQuery('age', 'DESC', 'M', (bool) $show_years);
989    }
990
991    /**
992     * General query on age at marriage.
993     *
994     * @param string $type
995     * @param string $age_dir "ASC" or "DESC"
996     * @param int    $total
997     *
998     * @return string
999     */
1000    private function ageOfMarriageQuery(string $type, string $age_dir, int $total): string
1001    {
1002        $prefix = DB::connection()->getTablePrefix();
1003
1004        $hrows = DB::table('families')
1005            ->where('f_file', '=', $this->tree->id())
1006            ->join('dates AS married', function (JoinClause $join): void {
1007                $join
1008                    ->on('married.d_file', '=', 'f_file')
1009                    ->on('married.d_gid', '=', 'f_id')
1010                    ->where('married.d_fact', '=', 'MARR')
1011                    ->where('married.d_julianday1', '<>', 0);
1012            })
1013            ->join('dates AS husbdeath', function (JoinClause $join): void {
1014                $join
1015                    ->on('husbdeath.d_gid', '=', 'f_husb')
1016                    ->on('husbdeath.d_file', '=', 'f_file')
1017                    ->where('husbdeath.d_fact', '=', 'DEAT');
1018            })
1019            ->whereColumn('married.d_julianday1', '<', 'husbdeath.d_julianday2')
1020            ->groupBy('f_id')
1021            ->select(['f_id AS family', DB::raw('MIN(' . $prefix . 'husbdeath.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')])
1022            ->get()
1023            ->all();
1024
1025        $wrows = DB::table('families')
1026            ->where('f_file', '=', $this->tree->id())
1027            ->join('dates AS married', function (JoinClause $join): void {
1028                $join
1029                    ->on('married.d_file', '=', 'f_file')
1030                    ->on('married.d_gid', '=', 'f_id')
1031                    ->where('married.d_fact', '=', 'MARR')
1032                    ->where('married.d_julianday1', '<>', 0);
1033            })
1034            ->join('dates AS wifedeath', function (JoinClause $join): void {
1035                $join
1036                    ->on('wifedeath.d_gid', '=', 'f_wife')
1037                    ->on('wifedeath.d_file', '=', 'f_file')
1038                    ->where('wifedeath.d_fact', '=', 'DEAT');
1039            })
1040            ->whereColumn('married.d_julianday1', '<', 'wifedeath.d_julianday2')
1041            ->groupBy('f_id')
1042            ->select(['f_id AS family', DB::raw('MIN(' . $prefix . 'wifedeath.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')])
1043            ->get()
1044            ->all();
1045
1046        $drows = DB::table('families')
1047            ->where('f_file', '=', $this->tree->id())
1048            ->join('dates AS married', function (JoinClause $join): void {
1049                $join
1050                    ->on('married.d_file', '=', 'f_file')
1051                    ->on('married.d_gid', '=', 'f_id')
1052                    ->where('married.d_fact', '=', 'MARR')
1053                    ->where('married.d_julianday1', '<>', 0);
1054            })
1055            ->join('dates AS divorced', function (JoinClause $join): void {
1056                $join
1057                    ->on('divorced.d_gid', '=', 'f_id')
1058                    ->on('divorced.d_file', '=', 'f_file')
1059                    ->whereIn('divorced.d_fact', ['DIV', 'ANUL', '_SEPR']);
1060            })
1061            ->whereColumn('married.d_julianday1', '<', 'divorced.d_julianday2')
1062            ->groupBy('f_id')
1063            ->select(['f_id AS family', DB::raw('MIN(' . $prefix . 'divorced.d_julianday2 - ' . $prefix . 'married.d_julianday1) AS age')])
1064            ->get()
1065            ->all();
1066
1067        $rows = [];
1068        foreach ($drows as $family) {
1069            $rows[$family->family] = $family->age;
1070        }
1071
1072        foreach ($hrows as $family) {
1073            if (!isset($rows[$family->family])) {
1074                $rows[$family->family] = $family->age;
1075            }
1076        }
1077
1078        foreach ($wrows as $family) {
1079            if (!isset($rows[$family->family])) {
1080                $rows[$family->family] = $family->age;
1081            } elseif ($rows[$family->family] > $family->age) {
1082                $rows[$family->family] = $family->age;
1083            }
1084        }
1085
1086        if ($age_dir === 'DESC') {
1087            arsort($rows);
1088        } else {
1089            asort($rows);
1090        }
1091
1092        $top10 = [];
1093        $i     = 0;
1094        foreach ($rows as $fam => $age) {
1095            $family = Family::getInstance($fam, $this->tree);
1096            if ($type === 'name') {
1097                return $family->formatList();
1098            }
1099
1100            $age = $this->calculateAge((int) $age);
1101
1102            if ($type === 'age') {
1103                return $age;
1104            }
1105
1106            $husb = $family->husband();
1107            $wife = $family->wife();
1108
1109            if (($husb && ($husb->getAllDeathDates() || !$husb->isDead()))
1110                && ($wife && ($wife->getAllDeathDates() || !$wife->isDead()))
1111            ) {
1112                if ($family && $family->canShow()) {
1113                    if ($type === 'list') {
1114                        $top10[] = '<li><a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')' . '</li>';
1115                    } else {
1116                        $top10[] = '<a href="' . e($family->url()) . '">' . $family->fullName() . '</a> (' . $age . ')';
1117                    }
1118                }
1119                if (++$i === $total) {
1120                    break;
1121                }
1122            }
1123        }
1124
1125        if ($type === 'list') {
1126            $top10 = implode('', $top10);
1127        } else {
1128            $top10 = implode('; ', $top10);
1129        }
1130
1131        if (I18N::direction() === 'rtl') {
1132            $top10 = str_replace([
1133                '[',
1134                ']',
1135                '(',
1136                ')',
1137                '+',
1138            ], [
1139                '&rlm;[',
1140                '&rlm;]',
1141                '&rlm;(',
1142                '&rlm;)',
1143                '&rlm;+',
1144            ], $top10);
1145        }
1146
1147        if ($type === 'list') {
1148            return '<ul>' . $top10 . '</ul>';
1149        }
1150
1151        return $top10;
1152    }
1153
1154    /**
1155     * General query on marriage ages.
1156     *
1157     * @return string
1158     */
1159    public function topAgeOfMarriageFamily(): string
1160    {
1161        return $this->ageOfMarriageQuery('name', 'DESC', 1);
1162    }
1163
1164    /**
1165     * General query on marriage ages.
1166     *
1167     * @return string
1168     */
1169    public function topAgeOfMarriage(): string
1170    {
1171        return $this->ageOfMarriageQuery('age', 'DESC', 1);
1172    }
1173
1174    /**
1175     * General query on marriage ages.
1176     *
1177     * @param int $total
1178     *
1179     * @return string
1180     */
1181    public function topAgeOfMarriageFamilies(int $total = 10): string
1182    {
1183        return $this->ageOfMarriageQuery('nolist', 'DESC', $total);
1184    }
1185
1186    /**
1187     * General query on marriage ages.
1188     *
1189     * @param int $total
1190     *
1191     * @return string
1192     */
1193    public function topAgeOfMarriageFamiliesList(int $total = 10): string
1194    {
1195        return $this->ageOfMarriageQuery('list', 'DESC', $total);
1196    }
1197
1198    /**
1199     * General query on marriage ages.
1200     *
1201     * @return string
1202     */
1203    public function minAgeOfMarriageFamily(): string
1204    {
1205        return $this->ageOfMarriageQuery('name', 'ASC', 1);
1206    }
1207
1208    /**
1209     * General query on marriage ages.
1210     *
1211     * @return string
1212     */
1213    public function minAgeOfMarriage(): string
1214    {
1215        return $this->ageOfMarriageQuery('age', 'ASC', 1);
1216    }
1217
1218    /**
1219     * General query on marriage ages.
1220     *
1221     * @param int $total
1222     *
1223     * @return string
1224     */
1225    public function minAgeOfMarriageFamilies(int $total = 10): string
1226    {
1227        return $this->ageOfMarriageQuery('nolist', 'ASC', $total);
1228    }
1229
1230    /**
1231     * General query on marriage ages.
1232     *
1233     * @param int $total
1234     *
1235     * @return string
1236     */
1237    public function minAgeOfMarriageFamiliesList(int $total = 10): string
1238    {
1239        return $this->ageOfMarriageQuery('list', 'ASC', $total);
1240    }
1241
1242    /**
1243     * Find the ages between spouses.
1244     *
1245     * @param string $age_dir
1246     * @param int    $total
1247     *
1248     * @return array
1249     */
1250    private function ageBetweenSpousesQuery(string $age_dir, int $total): array
1251    {
1252        $prefix = DB::connection()->getTablePrefix();
1253
1254        $query = DB::table('families')
1255            ->where('f_file', '=', $this->tree->id())
1256            ->join('dates AS wife', function (JoinClause $join): void {
1257                $join
1258                    ->on('wife.d_gid', '=', 'f_wife')
1259                    ->on('wife.d_file', '=', 'f_file')
1260                    ->where('wife.d_fact', '=', 'BIRT')
1261                    ->where('wife.d_julianday1', '<>', 0);
1262            })
1263            ->join('dates AS husb', function (JoinClause $join): void {
1264                $join
1265                    ->on('husb.d_gid', '=', 'f_husb')
1266                    ->on('husb.d_file', '=', 'f_file')
1267                    ->where('husb.d_fact', '=', 'BIRT')
1268                    ->where('husb.d_julianday1', '<>', 0);
1269            });
1270
1271        if ($age_dir === 'DESC') {
1272            $query
1273                ->whereColumn('wife.d_julianday1', '>=', 'husb.d_julianday1')
1274                ->orderBy(DB::raw('MIN(' . $prefix . 'wife.d_julianday1) - MIN(' . $prefix . 'husb.d_julianday1)'), 'DESC');
1275        } else {
1276            $query
1277                ->whereColumn('husb.d_julianday1', '>=', 'wife.d_julianday1')
1278                ->orderBy(DB::raw('MIN(' . $prefix . 'husb.d_julianday1) - MIN(' . $prefix . 'wife.d_julianday1)'), 'DESC');
1279        }
1280
1281        return $query
1282            ->groupBy(['f_id', 'f_file'])
1283            ->select('families.*')
1284            ->take($total)
1285            ->get()
1286            ->map(Family::rowMapper())
1287            ->filter(GedcomRecord::accessFilter())
1288            ->map(function (Family $family) use ($age_dir): array {
1289                $husb_birt_jd = $family->husband()->getBirthDate()->minimumJulianDay();
1290                $wife_birt_jd = $family->wife()->getBirthDate()->minimumJulianDay();
1291
1292                if ($age_dir === 'DESC') {
1293                    $diff = $wife_birt_jd - $husb_birt_jd;
1294                } else {
1295                    $diff = $husb_birt_jd - $wife_birt_jd;
1296                }
1297
1298                return [
1299                    'family' => $family,
1300                    'age'    => $this->calculateAge($diff),
1301                ];
1302            })
1303            ->all();
1304    }
1305
1306    /**
1307     * Find the age between husband and wife.
1308     *
1309     * @param int $total
1310     *
1311     * @return string
1312     */
1313    public function ageBetweenSpousesMF(int $total = 10): string
1314    {
1315        $records = $this->ageBetweenSpousesQuery('DESC', $total);
1316
1317        return view('statistics/families/top10-nolist-spouses', [
1318            'records' => $records,
1319        ]);
1320    }
1321
1322    /**
1323     * Find the age between husband and wife.
1324     *
1325     * @param int $total
1326     *
1327     * @return string
1328     */
1329    public function ageBetweenSpousesMFList(int $total = 10): string
1330    {
1331        $records = $this->ageBetweenSpousesQuery('DESC', $total);
1332
1333        return view('statistics/families/top10-list-spouses', [
1334            'records' => $records,
1335        ]);
1336    }
1337
1338    /**
1339     * Find the age between wife and husband..
1340     *
1341     * @param int $total
1342     *
1343     * @return string
1344     */
1345    public function ageBetweenSpousesFM(int $total = 10): string
1346    {
1347        $records = $this->ageBetweenSpousesQuery('ASC', $total);
1348
1349        return view('statistics/families/top10-nolist-spouses', [
1350            'records' => $records,
1351        ]);
1352    }
1353
1354    /**
1355     * Find the age between wife and husband..
1356     *
1357     * @param int $total
1358     *
1359     * @return string
1360     */
1361    public function ageBetweenSpousesFMList(int $total = 10): string
1362    {
1363        $records = $this->ageBetweenSpousesQuery('ASC', $total);
1364
1365        return view('statistics/families/top10-list-spouses', [
1366            'records' => $records,
1367        ]);
1368    }
1369
1370    /**
1371     * General query on ages at marriage.
1372     *
1373     * @param string $sex   "M" or "F"
1374     * @param int    $year1
1375     * @param int    $year2
1376     *
1377     * @return array
1378     */
1379    public function statsMarrAgeQuery($sex, $year1 = -1, $year2 = -1): array
1380    {
1381        $prefix = DB::connection()->getTablePrefix();
1382
1383        $query = DB::table('dates AS married')
1384            ->join('families', function (JoinClause $join): void {
1385                $join
1386                    ->on('f_file', '=', 'married.d_file')
1387                    ->on('f_id', '=', 'married.d_gid');
1388            })
1389            ->join('dates AS birth', function (JoinClause $join) use ($sex): void {
1390                $join
1391                    ->on('birth.d_file', '=', 'married.d_file')
1392                    ->on('birth.d_gid', '=', $sex === 'M' ? 'f_husb' : 'f_wife')
1393                    ->where('birth.d_julianday1', '<>', 0)
1394                    ->where('birth.d_fact', '=', 'BIRT')
1395                    ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']);
1396            })
1397            ->where('married.d_file', '=', $this->tree->id())
1398            ->where('married.d_fact', '=', 'MARR')
1399            ->whereIn('married.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
1400            ->whereColumn('married.d_julianday1', '>', 'birth.d_julianday1')
1401            ->select(['f_id', 'birth.d_gid', DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age')]);
1402
1403        if ($year1 >= 0 && $year2 >= 0) {
1404            $query->whereBetween('married.d_year', [$year1, $year2]);
1405        }
1406
1407        return $query
1408            ->get()
1409            ->map(function (stdClass $row): stdClass {
1410                $row->age = (int) $row->age;
1411
1412                return $row;
1413            })
1414            ->all();
1415    }
1416
1417    /**
1418     * General query on marriage ages.
1419     *
1420     * @return string
1421     */
1422    public function statsMarrAge(): string
1423    {
1424        return (new ChartMarriageAge($this->tree))
1425            ->chartMarriageAge();
1426    }
1427
1428    /**
1429     * Query the database for marriage tags.
1430     *
1431     * @param string $type       "full", "name" or "age"
1432     * @param string $age_dir    "ASC" or "DESC"
1433     * @param string $sex        "F" or "M"
1434     * @param bool   $show_years
1435     *
1436     * @return string
1437     */
1438    private function marriageQuery(string $type, string $age_dir, string $sex, bool $show_years): string
1439    {
1440        if ($sex === 'F') {
1441            $sex_field = 'f_wife';
1442        } else {
1443            $sex_field = 'f_husb';
1444        }
1445
1446        if ($age_dir !== 'ASC') {
1447            $age_dir = 'DESC';
1448        }
1449
1450        $prefix = DB::connection()->getTablePrefix();
1451
1452        $row = DB::table('families')
1453            ->join('dates AS married', function (JoinClause $join): void {
1454                $join
1455                    ->on('married.d_file', '=', 'f_file')
1456                    ->on('married.d_gid', '=', 'f_id')
1457                    ->where('married.d_fact', '=', 'MARR');
1458            })
1459            ->join('individuals', function (JoinClause $join) use ($sex, $sex_field): void {
1460                $join
1461                    ->on('i_file', '=', 'f_file')
1462                    ->on('i_id', '=', $sex_field)
1463                    ->where('i_sex', '=', $sex);
1464            })
1465            ->join('dates AS birth', function (JoinClause $join): void {
1466                $join
1467                    ->on('birth.d_file', '=', 'i_file')
1468                    ->on('birth.d_gid', '=', 'i_id')
1469                    ->where('birth.d_fact', '=', 'BIRT')
1470                    ->where('birth.d_julianday1', '<>', 0);
1471            })
1472            ->where('f_file', '=', $this->tree->id())
1473            ->where('married.d_julianday2', '>', 'birth.d_julianday1')
1474            ->orderBy(DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1'), $age_dir)
1475            ->select(['f_id AS famid', $sex_field, DB::raw($prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS age'), 'i_id'])
1476            ->take(1)
1477            ->get()
1478            ->first();
1479
1480        if ($row === null) {
1481            return '';
1482        }
1483
1484        $family = Family::getInstance($row->famid, $this->tree);
1485        $person = Individual::getInstance($row->i_id, $this->tree);
1486
1487        switch ($type) {
1488            default:
1489            case 'full':
1490                if ($family && $family->canShow()) {
1491                    $result = $family->formatList();
1492                } else {
1493                    $result = I18N::translate('This information is private and cannot be shown.');
1494                }
1495                break;
1496
1497            case 'name':
1498                $result = '<a href="' . e($family->url()) . '">' . $person->fullName() . '</a>';
1499                break;
1500
1501            case 'age':
1502                $age = $row->age;
1503
1504                if ($show_years) {
1505                    $result = $this->calculateAge((int) $row->age);
1506                } else {
1507                    $result = I18N::number((int) ($age / 365.25));
1508                }
1509
1510                break;
1511        }
1512
1513        return $result;
1514    }
1515
1516    /**
1517     * Find the youngest wife.
1518     *
1519     * @return string
1520     */
1521    public function youngestMarriageFemale(): string
1522    {
1523        return $this->marriageQuery('full', 'ASC', 'F', false);
1524    }
1525
1526    /**
1527     * Find the name of the youngest wife.
1528     *
1529     * @return string
1530     */
1531    public function youngestMarriageFemaleName(): string
1532    {
1533        return $this->marriageQuery('name', 'ASC', 'F', false);
1534    }
1535
1536    /**
1537     * Find the age of the youngest wife.
1538     *
1539     * @param string $show_years
1540     *
1541     * @return string
1542     */
1543    public function youngestMarriageFemaleAge(string $show_years = ''): string
1544    {
1545        return $this->marriageQuery('age', 'ASC', 'F', (bool) $show_years);
1546    }
1547
1548    /**
1549     * Find the oldest wife.
1550     *
1551     * @return string
1552     */
1553    public function oldestMarriageFemale(): string
1554    {
1555        return $this->marriageQuery('full', 'DESC', 'F', false);
1556    }
1557
1558    /**
1559     * Find the name of the oldest wife.
1560     *
1561     * @return string
1562     */
1563    public function oldestMarriageFemaleName(): string
1564    {
1565        return $this->marriageQuery('name', 'DESC', 'F', false);
1566    }
1567
1568    /**
1569     * Find the age of the oldest wife.
1570     *
1571     * @param string $show_years
1572     *
1573     * @return string
1574     */
1575    public function oldestMarriageFemaleAge(string $show_years = ''): string
1576    {
1577        return $this->marriageQuery('age', 'DESC', 'F', (bool) $show_years);
1578    }
1579
1580    /**
1581     * Find the youngest husband.
1582     *
1583     * @return string
1584     */
1585    public function youngestMarriageMale(): string
1586    {
1587        return $this->marriageQuery('full', 'ASC', 'M', false);
1588    }
1589
1590    /**
1591     * Find the name of the youngest husband.
1592     *
1593     * @return string
1594     */
1595    public function youngestMarriageMaleName(): string
1596    {
1597        return $this->marriageQuery('name', 'ASC', 'M', false);
1598    }
1599
1600    /**
1601     * Find the age of the youngest husband.
1602     *
1603     * @param string $show_years
1604     *
1605     * @return string
1606     */
1607    public function youngestMarriageMaleAge(string $show_years = ''): string
1608    {
1609        return $this->marriageQuery('age', 'ASC', 'M', (bool) $show_years);
1610    }
1611
1612    /**
1613     * Find the oldest husband.
1614     *
1615     * @return string
1616     */
1617    public function oldestMarriageMale(): string
1618    {
1619        return $this->marriageQuery('full', 'DESC', 'M', false);
1620    }
1621
1622    /**
1623     * Find the name of the oldest husband.
1624     *
1625     * @return string
1626     */
1627    public function oldestMarriageMaleName(): string
1628    {
1629        return $this->marriageQuery('name', 'DESC', 'M', false);
1630    }
1631
1632    /**
1633     * Find the age of the oldest husband.
1634     *
1635     * @param string $show_years
1636     *
1637     * @return string
1638     */
1639    public function oldestMarriageMaleAge(string $show_years = ''): string
1640    {
1641        return $this->marriageQuery('age', 'DESC', 'M', (bool) $show_years);
1642    }
1643
1644    /**
1645     * General query on marriages.
1646     *
1647     * @param int  $year1
1648     * @param int  $year2
1649     *
1650     * @return Builder
1651     */
1652    public function statsMarriageQuery(int $year1 = -1, int $year2 = -1): Builder
1653    {
1654        $query = DB::table('dates')
1655            ->where('d_file', '=', $this->tree->id())
1656            ->where('d_fact', '=', 'MARR')
1657            ->select(['d_month', DB::raw('COUNT(*) AS total')])
1658            ->groupBy('d_month');
1659
1660        if ($year1 >= 0 && $year2 >= 0) {
1661            $query->whereBetween('d_year', [$year1, $year2]);
1662        }
1663
1664        return $query;
1665    }
1666
1667    /**
1668     * General query on marriages.
1669     *
1670     * @param int  $year1
1671     * @param int  $year2
1672     *
1673     * @return Builder
1674     */
1675    public function statsFirstMarriageQuery(int $year1 = -1, int $year2 = -1): Builder
1676    {
1677        $query = DB::table('families')
1678            ->join('dates', function (JoinClause $join): void {
1679                $join
1680                    ->on('d_gid', '=', 'f_id')
1681                    ->on('d_file', '=', 'f_file')
1682                    ->where('d_fact', '=', 'MARR')
1683                    ->whereIn('d_month', ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'])
1684                    ->where('d_julianday2', '<>', 0);
1685            })->join('individuals', function (JoinClause $join): void {
1686                $join
1687                    ->on('i_file', '=', 'f_file');
1688            })
1689            ->where('f_file', '=', $this->tree->id())
1690            ->where(function (Builder $query): void {
1691                $query
1692                    ->whereColumn('i_id', '=', 'f_husb')
1693                    ->orWhereColumn('i_id', '=', 'f_wife');
1694            });
1695
1696        if ($year1 >= 0 && $year2 >= 0) {
1697            $query->whereBetween('d_year', [$year1, $year2]);
1698        }
1699
1700        return $query
1701            ->select(['f_id AS fams', 'f_husb', 'f_wife', 'd_julianday2 AS age', 'd_month AS month', 'i_id AS indi'])
1702            ->orderBy('f_id')
1703            ->orderBy('i_id')
1704            ->orderBy('d_julianday2');
1705    }
1706
1707    /**
1708     * General query on marriages.
1709     *
1710     * @param string|null $color_from
1711     * @param string|null $color_to
1712     *
1713     * @return string
1714     */
1715    public function statsMarr(string $color_from = null, string $color_to = null): string
1716    {
1717        return (new ChartMarriage($this->tree))
1718            ->chartMarriage($color_from, $color_to);
1719    }
1720
1721    /**
1722     * General divorce query.
1723     *
1724     * @param string|null $color_from
1725     * @param string|null $color_to
1726     *
1727     * @return string
1728     */
1729    public function statsDiv(string $color_from = null, string $color_to = null): string
1730    {
1731        return (new ChartDivorce($this->tree))
1732            ->chartDivorce($color_from, $color_to);
1733    }
1734}
1735