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