xref: /webtrees/app/Statistics/Repository/IndividualRepository.php (revision a93f450e7efcf8e9dda787aaa9bfbc882bd2c767) 
1<?php
2/**
3 * webtrees: online genealogy
4 * Copyright (C) 2019 webtrees development team
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
9 * This program is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 * GNU General Public License for more details.
13 * You should have received a copy of the GNU General Public License
14 * along with this program. If not, see <http://www.gnu.org/licenses/>.
15 */
16declare(strict_types=1);
17
18namespace Fisharebest\Webtrees\Statistics\Repository;
19
20use Fisharebest\Webtrees\Auth;
21use Fisharebest\Webtrees\Functions\FunctionsDate;
22use Fisharebest\Webtrees\Functions\FunctionsPrintLists;
23use Fisharebest\Webtrees\Gedcom;
24use Fisharebest\Webtrees\GedcomRecord;
25use Fisharebest\Webtrees\I18N;
26use Fisharebest\Webtrees\Individual;
27use Fisharebest\Webtrees\Statistics\Google\ChartAge;
28use Fisharebest\Webtrees\Statistics\Google\ChartBirth;
29use Fisharebest\Webtrees\Statistics\Google\ChartCommonGiven;
30use Fisharebest\Webtrees\Statistics\Google\ChartCommonSurname;
31use Fisharebest\Webtrees\Statistics\Google\ChartDeath;
32use Fisharebest\Webtrees\Statistics\Google\ChartFamilyWithSources;
33use Fisharebest\Webtrees\Statistics\Google\ChartIndividualWithSources;
34use Fisharebest\Webtrees\Statistics\Google\ChartMortality;
35use Fisharebest\Webtrees\Statistics\Google\ChartSex;
36use Fisharebest\Webtrees\Statistics\Repository\Interfaces\IndividualRepositoryInterface;
37use Fisharebest\Webtrees\Tree;
38use Illuminate\Database\Capsule\Manager as DB;
39use Illuminate\Database\Query\Builder;
40use Illuminate\Database\Query\JoinClause;
41
42/**
43 *
44 */
45class IndividualRepository implements IndividualRepositoryInterface
46{
47    /**
48     * @var Tree
49     */
50    private $tree;
51
52    /**
53     * Constructor.
54     *
55     * @param Tree $tree
56     */
57    public function __construct(Tree $tree)
58    {
59        $this->tree = $tree;
60    }
61
62    /**
63     * Find common given names.
64     *
65     * @param string $sex
66     * @param string $type
67     * @param bool   $show_tot
68     * @param int    $threshold
69     * @param int    $maxtoshow
70     *
71     * @return string|int[]
72     */
73    private function commonGivenQuery(string $sex, string $type, bool $show_tot, int $threshold, int $maxtoshow)
74    {
75        $query = DB::table('name')
76            ->join('individuals', function (JoinClause $join): void {
77                $join
78                    ->on('i_file', '=', 'n_file')
79                    ->on('i_id', '=', 'n_id');
80            })
81            ->where('n_file', '=', $this->tree->id())
82            ->where('n_type', '<>', '_MARNM')
83            ->where('n_givn', '<>', '@P.N.')
84            ->where(DB::raw('LENGTH(n_givn)'), '>', 1);
85
86        switch ($sex) {
87            case 'M':
88            case 'F':
89            case 'U':
90                $query->where('i_sex', '=', $sex);
91                break;
92
93            case 'B':
94            default:
95                $query->where('i_sex', '<>', 'U');
96                break;
97        }
98
99        $rows = $query
100            ->groupBy(['n_givn'])
101            ->select(['n_givn', DB::raw('COUNT(distinct n_id) AS count')])
102            ->pluck('count', 'n_givn');
103
104        $nameList = [];
105
106        foreach ($rows as $n_givn => $count) {
107            // Split “John Thomas” into “John” and “Thomas” and count against both totals
108            foreach (explode(' ', $n_givn) as $given) {
109                // Exclude initials and particles.
110                if (!preg_match('/^([A-Z]|[a-z]{1,3})$/', $given)) {
111                    if (\array_key_exists($given, $nameList)) {
112                        $nameList[$given] += (int) $count;
113                    } else {
114                        $nameList[$given] = (int) $count;
115                    }
116                }
117            }
118        }
119        arsort($nameList);
120        $nameList = \array_slice($nameList, 0, $maxtoshow);
121
122        foreach ($nameList as $given => $total) {
123            if ($total < $threshold) {
124                unset($nameList[$given]);
125            }
126        }
127
128        switch ($type) {
129            case 'chart':
130                return $nameList;
131
132            case 'table':
133                return view('lists/given-names-table', [
134                    'given_names' => $nameList,
135                ]);
136
137            case 'list':
138                return view('lists/given-names-list', [
139                    'given_names' => $nameList,
140                    'show_totals' => $show_tot,
141                ]);
142
143            case 'nolist':
144            default:
145                array_walk($nameList, function (int &$value, string $key) use ($show_tot): void {
146                    if ($show_tot) {
147                        $value = '<span dir="auto">' . e($key);
148                    } else {
149                        $value = '<span dir="auto">' . e($key) . ' (' . I18N::number($value) . ')';
150                    }
151                });
152
153                return implode(I18N::$list_separator, $nameList);
154        }
155    }
156
157    /**
158     * Find common give names.
159     *
160     * @param int $threshold
161     * @param int $maxtoshow
162     *
163     * @return string
164     */
165    public function commonGiven(int $threshold = 1, int $maxtoshow = 10): string
166    {
167        return $this->commonGivenQuery('B', 'nolist', false, $threshold, $maxtoshow);
168    }
169
170    /**
171     * Find common give names.
172     *
173     * @param int $threshold
174     * @param int $maxtoshow
175     *
176     * @return string
177     */
178    public function commonGivenTotals(int $threshold = 1, int $maxtoshow = 10): string
179    {
180        return $this->commonGivenQuery('B', 'nolist', true, $threshold, $maxtoshow);
181    }
182
183    /**
184     * Find common give names.
185     *
186     * @param int $threshold
187     * @param int $maxtoshow
188     *
189     * @return string
190     */
191    public function commonGivenList(int $threshold = 1, int $maxtoshow = 10): string
192    {
193        return $this->commonGivenQuery('B', 'list', false, $threshold, $maxtoshow);
194    }
195
196    /**
197     * Find common give names.
198     *
199     * @param int $threshold
200     * @param int $maxtoshow
201     *
202     * @return string
203     */
204    public function commonGivenListTotals(int $threshold = 1, int $maxtoshow = 10): string
205    {
206        return $this->commonGivenQuery('B', 'list', true, $threshold, $maxtoshow);
207    }
208
209    /**
210     * Find common give names.
211     *
212     * @param int $threshold
213     * @param int $maxtoshow
214     *
215     * @return string
216     */
217    public function commonGivenTable(int $threshold = 1, int $maxtoshow = 10): string
218    {
219        return $this->commonGivenQuery('B', 'table', false, $threshold, $maxtoshow);
220    }
221
222    /**
223     * Find common give names of females.
224     *
225     * @param int $threshold
226     * @param int $maxtoshow
227     *
228     * @return string
229     */
230    public function commonGivenFemale(int $threshold = 1, int $maxtoshow = 10): string
231    {
232        return $this->commonGivenQuery('F', 'nolist', false, $threshold, $maxtoshow);
233    }
234
235    /**
236     * Find common give names of females.
237     *
238     * @param int $threshold
239     * @param int $maxtoshow
240     *
241     * @return string
242     */
243    public function commonGivenFemaleTotals(int $threshold = 1, int $maxtoshow = 10): string
244    {
245        return $this->commonGivenQuery('F', 'nolist', true, $threshold, $maxtoshow);
246    }
247
248    /**
249     * Find common give names of females.
250     *
251     * @param int $threshold
252     * @param int $maxtoshow
253     *
254     * @return string
255     */
256    public function commonGivenFemaleList(int $threshold = 1, int $maxtoshow = 10): string
257    {
258        return $this->commonGivenQuery('F', 'list', false, $threshold, $maxtoshow);
259    }
260
261    /**
262     * Find common give names of females.
263     *
264     * @param int $threshold
265     * @param int $maxtoshow
266     *
267     * @return string
268     */
269    public function commonGivenFemaleListTotals(int $threshold = 1, int $maxtoshow = 10): string
270    {
271        return $this->commonGivenQuery('F', 'list', true, $threshold, $maxtoshow);
272    }
273
274    /**
275     * Find common give names of females.
276     *
277     * @param int $threshold
278     * @param int $maxtoshow
279     *
280     * @return string
281     */
282    public function commonGivenFemaleTable(int $threshold = 1, int $maxtoshow = 10): string
283    {
284        return $this->commonGivenQuery('F', 'table', false, $threshold, $maxtoshow);
285    }
286
287    /**
288     * Find common give names of males.
289     *
290     * @param int $threshold
291     * @param int $maxtoshow
292     *
293     * @return string
294     */
295    public function commonGivenMale(int $threshold = 1, int $maxtoshow = 10): string
296    {
297        return $this->commonGivenQuery('M', 'nolist', false, $threshold, $maxtoshow);
298    }
299
300    /**
301     * Find common give names of males.
302     *
303     * @param int $threshold
304     * @param int $maxtoshow
305     *
306     * @return string
307     */
308    public function commonGivenMaleTotals(int $threshold = 1, int $maxtoshow = 10): string
309    {
310        return $this->commonGivenQuery('M', 'nolist', true, $threshold, $maxtoshow);
311    }
312
313    /**
314     * Find common give names of males.
315     *
316     * @param int $threshold
317     * @param int $maxtoshow
318     *
319     * @return string
320     */
321    public function commonGivenMaleList(int $threshold = 1, int $maxtoshow = 10): string
322    {
323        return $this->commonGivenQuery('M', 'list', false, $threshold, $maxtoshow);
324    }
325
326    /**
327     * Find common give names of males.
328     *
329     * @param int $threshold
330     * @param int $maxtoshow
331     *
332     * @return string
333     */
334    public function commonGivenMaleListTotals(int $threshold = 1, int $maxtoshow = 10): string
335    {
336        return $this->commonGivenQuery('M', 'list', true, $threshold, $maxtoshow);
337    }
338
339    /**
340     * Find common give names of males.
341     *
342     * @param int $threshold
343     * @param int $maxtoshow
344     *
345     * @return string
346     */
347    public function commonGivenMaleTable(int $threshold = 1, int $maxtoshow = 10): string
348    {
349        return $this->commonGivenQuery('M', 'table', false, $threshold, $maxtoshow);
350    }
351
352    /**
353     * Find common give names of unknown sexes.
354     *
355     * @param int $threshold
356     * @param int $maxtoshow
357     *
358     * @return string
359     */
360    public function commonGivenUnknown(int $threshold = 1, int $maxtoshow = 10): string
361    {
362        return $this->commonGivenQuery('U', 'nolist', false, $threshold, $maxtoshow);
363    }
364
365    /**
366     * Find common give names of unknown sexes.
367     *
368     * @param int $threshold
369     * @param int $maxtoshow
370     *
371     * @return string
372     */
373    public function commonGivenUnknownTotals(int $threshold = 1, int $maxtoshow = 10): string
374    {
375        return $this->commonGivenQuery('U', 'nolist', true, $threshold, $maxtoshow);
376    }
377
378    /**
379     * Find common give names of unknown sexes.
380     *
381     * @param int $threshold
382     * @param int $maxtoshow
383     *
384     * @return string
385     */
386    public function commonGivenUnknownList(int $threshold = 1, int $maxtoshow = 10): string
387    {
388        return $this->commonGivenQuery('U', 'list', false, $threshold, $maxtoshow);
389    }
390
391    /**
392     * Find common give names of unknown sexes.
393     *
394     * @param int $threshold
395     * @param int $maxtoshow
396     *
397     * @return string
398     */
399    public function commonGivenUnknownListTotals(int $threshold = 1, int $maxtoshow = 10): string
400    {
401        return $this->commonGivenQuery('U', 'list', true, $threshold, $maxtoshow);
402    }
403
404    /**
405     * Find common give names of unknown sexes.
406     *
407     * @param int $threshold
408     * @param int $maxtoshow
409     *
410     * @return string
411     */
412    public function commonGivenUnknownTable(int $threshold = 1, int $maxtoshow = 10): string
413    {
414        return $this->commonGivenQuery('U', 'table', false, $threshold, $maxtoshow);
415    }
416
417    /**
418     * Count the number of distinct given names (or the number of occurences of specific given names).
419     *
420     * @param string[] ...$params
421     *
422     * @return string
423     */
424    public function totalGivennames(...$params): string
425    {
426        $query = DB::table('name')
427            ->where('n_file', '=', $this->tree->id());
428
429        if (empty($params)) {
430            // Count number of distinct given names.
431            $query
432                ->distinct()
433                ->where('n_givn', '<>', '@P.N.')
434                ->whereNotNull('n_givn');
435        } else {
436            // Count number of occurences of specific given names.
437            $query->whereIn('n_givn', $params);
438        }
439
440        $count = $query->count('n_givn');
441
442        return I18N::number($count);
443    }
444
445    /**
446     * Count the number of distinct surnames (or the number of occurences of specific surnames).
447     *
448     * @param string[] ...$params
449     *
450     * @return string
451     */
452    public function totalSurnames(...$params): string
453    {
454        $query = DB::table('name')
455            ->where('n_file', '=', $this->tree->id());
456
457        if (empty($params)) {
458            // Count number of distinct surnames
459            $query->distinct()
460                ->whereNotNull('n_surn');
461        } else {
462            // Count number of occurences of specific surnames.
463            $query->whereIn('n_surn', $params);
464        }
465
466        $count = $query->count('n_surn');
467
468        return I18N::number($count);
469    }
470
471    /**
472     * @param int $number_of_surnames
473     * @param int $threshold
474     *
475     * @return \stdClass[]
476     */
477    private function topSurnames(int $number_of_surnames, int $threshold): array
478    {
479        // Use the count of base surnames.
480        $top_surnames = DB::table('name')
481            ->where('n_file', '=', $this->tree->id())
482            ->where('n_type', '<>', '_MARNM')
483            ->whereNotIn('n_surn', ['', '@N.N.'])
484            ->select('n_surn')
485            ->groupBy('n_surn')
486            ->orderByRaw('count(n_surn) desc')
487            ->take($number_of_surnames)
488            ->get()
489            ->pluck('n_surn')
490            ->all();
491
492        $surnames = [];
493        foreach ($top_surnames as $top_surname) {
494            $variants = DB::table('name')
495                ->where('n_file', '=', $this->tree->id())
496                ->where(DB::raw('n_surn /* COLLATE ' . I18N::collation() . ' */'), '=', $top_surname)
497                ->select('n_surn', DB::raw('COUNT(*) AS count'))
498                ->groupBy('n_surn')
499                ->get()
500                ->pluck('count', 'n_surn')
501                ->all();
502
503            if (array_sum($variants) > $threshold) {
504                $surnames[$top_surname] = $variants;
505            }
506        }
507
508        return $surnames;
509    }
510
511    /**
512     * Find common surnames.
513     *
514     * @return string
515     */
516    public function getCommonSurname(): string
517    {
518        $top_surname = $this->topSurnames(1, 0);
519
520        return $top_surname
521            ? implode(', ', array_keys(array_shift($top_surname)) ?? [])
522            : '';
523    }
524
525    /**
526     * Find common surnames.
527     *
528     * @param string $type
529     * @param bool   $show_tot
530     * @param int    $threshold
531     * @param int    $number_of_surnames
532     * @param string $sorting
533     *
534     * @return string
535     */
536    private function commonSurnamesQuery(
537        string $type,
538        bool $show_tot,
539        int $threshold,
540        int $number_of_surnames,
541        string $sorting
542    ): string {
543        $surnames = $this->topSurnames($number_of_surnames, $threshold);
544
545        switch ($sorting) {
546            default:
547            case 'alpha':
548                uksort($surnames, [I18N::class, 'strcasecmp']);
549                break;
550            case 'count':
551                break;
552            case 'rcount':
553                $surnames = array_reverse($surnames, true);
554                break;
555        }
556
557        return FunctionsPrintLists::surnameList(
558            $surnames,
559            ($type === 'list' ? 1 : 2),
560            $show_tot,
561            'individual-list',
562            $this->tree
563        );
564    }
565
566    /**
567     * Find common surnames.
568     *
569     * @param int    $threshold
570     * @param int    $number_of_surnames
571     * @param string $sorting
572     *
573     * @return string
574     */
575    public function commonSurnames(
576        int $threshold = 1,
577        int $number_of_surnames = 10,
578        string $sorting = 'alpha'
579    ): string {
580        return $this->commonSurnamesQuery('nolist', false, $threshold, $number_of_surnames, $sorting);
581    }
582
583    /**
584     * Find common surnames.
585     *
586     * @param int    $threshold
587     * @param int    $number_of_surnames
588     * @param string $sorting
589     *
590     * @return string
591     */
592    public function commonSurnamesTotals(
593        int $threshold = 1,
594        int $number_of_surnames = 10,
595        string $sorting = 'rcount'
596    ): string {
597        return $this->commonSurnamesQuery('nolist', true, $threshold, $number_of_surnames, $sorting);
598    }
599
600    /**
601     * Find common surnames.
602     *
603     * @param int    $threshold
604     * @param int    $number_of_surnames
605     * @param string $sorting
606     *
607     * @return string
608     */
609    public function commonSurnamesList(
610        int $threshold = 1,
611        int $number_of_surnames = 10,
612        string $sorting = 'alpha'
613    ): string {
614        return $this->commonSurnamesQuery('list', false, $threshold, $number_of_surnames, $sorting);
615    }
616
617    /**
618     * Find common surnames.
619     *
620     * @param int    $threshold
621     * @param int    $number_of_surnames
622     * @param string $sorting
623     *
624     * @return string
625     */
626    public function commonSurnamesListTotals(
627        int $threshold = 1,
628        int $number_of_surnames = 10,
629        string $sorting = 'rcount'
630    ): string {
631        return $this->commonSurnamesQuery('list', true, $threshold, $number_of_surnames, $sorting);
632    }
633
634    /**
635     * Get a list of birth dates.
636     *
637     * @param bool $sex
638     * @param int  $year1
639     * @param int  $year2
640     *
641     * @return array
642     */
643    public function statsBirthQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array
644    {
645        $query = DB::table('dates')
646            ->select(['d_month', DB::raw('COUNT(*) AS total')])
647            ->where('d_file', '=', $this->tree->id())
648            ->where('d_fact', '=', 'BIRT')
649            ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
650            ->groupBy('d_month');
651
652        if ($year1 >= 0 && $year2 >= 0) {
653            $query->whereBetween('d_year', [$year1, $year2]);
654        }
655
656        if ($sex) {
657            $query
658                ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')])
659                ->join('individuals', function (JoinClause $join): void {
660                    $join
661                        ->on('i_id', '=', 'd_gid')
662                        ->on('i_file', '=', 'd_file');
663                })
664                ->groupBy('i_sex');
665        }
666
667        return $query->get()->all();
668    }
669
670    /**
671     * General query on births.
672     *
673     * @param string|null $color_from
674     * @param string|null $color_to
675     *
676     * @return string
677     */
678    public function statsBirth(string $color_from = null, string $color_to = null): string
679    {
680        return (new ChartBirth($this->tree))
681            ->chartBirth($color_from, $color_to);
682    }
683
684    /**
685     * Get a list of death dates.
686     *
687     * @param bool $sex
688     * @param int  $year1
689     * @param int  $year2
690     *
691     * @return array
692     */
693    public function statsDeathQuery(bool $sex = false, int $year1 = -1, int $year2 = -1): array
694    {
695        $query = DB::table('dates')
696            ->select(['d_month', DB::raw('COUNT(*) AS total')])
697            ->where('d_file', '=', $this->tree->id())
698            ->where('d_fact', '=', 'DEAT')
699            ->whereIn('d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
700            ->groupBy('d_month');
701
702        if ($year1 >= 0 && $year2 >= 0) {
703            $query->whereBetween('d_year', [$year1, $year2]);
704        }
705
706        if ($sex) {
707            $query
708                ->select(['d_month', 'i_sex', DB::raw('COUNT(*) AS total')])
709                ->join('individuals', function (JoinClause $join): void {
710                    $join
711                        ->on('i_id', '=', 'd_gid')
712                        ->on('i_file', '=', 'd_file');
713                })
714                ->groupBy('i_sex');
715        }
716
717        return $query->get()->all();
718    }
719
720    /**
721     * General query on deaths.
722     *
723     * @param string|null $color_from
724     * @param string|null $color_to
725     *
726     * @return string
727     */
728    public function statsDeath(string $color_from = null, string $color_to = null): string
729    {
730        return (new ChartDeath($this->tree))
731            ->chartDeath($color_from, $color_to);
732    }
733
734    /**
735     * General query on ages.
736     *
737     * @param string $related
738     * @param string $sex
739     * @param int    $year1
740     * @param int    $year2
741     *
742     * @return array|string
743     */
744    public function statsAgeQuery(string $related = 'BIRT', string $sex = 'BOTH', int $year1 = -1, int $year2 = -1)
745    {
746        $prefix = DB::connection()->getTablePrefix();
747
748        $query = $this->birthAndDeathQuery($sex);
749
750        if ($year1 >= 0 && $year2 >= 0) {
751            $query
752                ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
753                ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@']);
754
755            if ($related === 'BIRT') {
756                $query->whereBetween('birth.d_year', [$year1, $year2]);
757            } elseif ($related === 'DEAT') {
758                $query->whereBetween('death.d_year', [$year1, $year2]);
759            }
760        }
761
762        return $query
763            ->select(DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days'))
764            ->orderBy('days', 'desc')
765            ->get()
766            ->all();
767    }
768
769    /**
770     * General query on ages.
771     *
772     * @return string
773     */
774    public function statsAge(): string
775    {
776        return (new ChartAge($this->tree))->chartAge();
777    }
778
779    /**
780     * Lifespan
781     *
782     * @param string $type
783     * @param string $sex
784     *
785     * @return string
786     */
787    private function longlifeQuery(string $type, string $sex): string
788    {
789        $prefix = DB::connection()->getTablePrefix();
790
791        $row = $this->birthAndDeathQuery($sex)
792            ->orderBy('days', 'desc')
793            ->select(['individuals.*', DB::raw($prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1 AS days')])
794            ->first();
795
796        if ($row === null) {
797            return '';
798        }
799
800        /** @var Individual $individual */
801        $individual = Individual::rowMapper()($row);
802
803        if (!$individual->canShow()) {
804            return I18N::translate('This information is private and cannot be shown.');
805        }
806
807        switch ($type) {
808            default:
809            case 'full':
810                return $individual->formatList();
811
812            case 'age':
813                return I18N::number((int) ($row->days / 365.25));
814
815            case 'name':
816                return '<a href="' . e($individual->url()) . '">' . $individual->fullName() . '</a>';
817        }
818    }
819
820    /**
821     * Find the longest lived individual.
822     *
823     * @return string
824     */
825    public function longestLife(): string
826    {
827        return $this->longlifeQuery('full', 'BOTH');
828    }
829
830    /**
831     * Find the age of the longest lived individual.
832     *
833     * @return string
834     */
835    public function longestLifeAge(): string
836    {
837        return $this->longlifeQuery('age', 'BOTH');
838    }
839
840    /**
841     * Find the name of the longest lived individual.
842     *
843     * @return string
844     */
845    public function longestLifeName(): string
846    {
847        return $this->longlifeQuery('name', 'BOTH');
848    }
849
850    /**
851     * Find the longest lived female.
852     *
853     * @return string
854     */
855    public function longestLifeFemale(): string
856    {
857        return $this->longlifeQuery('full', 'F');
858    }
859
860    /**
861     * Find the age of the longest lived female.
862     *
863     * @return string
864     */
865    public function longestLifeFemaleAge(): string
866    {
867        return $this->longlifeQuery('age', 'F');
868    }
869
870    /**
871     * Find the name of the longest lived female.
872     *
873     * @return string
874     */
875    public function longestLifeFemaleName(): string
876    {
877        return $this->longlifeQuery('name', 'F');
878    }
879
880    /**
881     * Find the longest lived male.
882     *
883     * @return string
884     */
885    public function longestLifeMale(): string
886    {
887        return $this->longlifeQuery('full', 'M');
888    }
889
890    /**
891     * Find the age of the longest lived male.
892     *
893     * @return string
894     */
895    public function longestLifeMaleAge(): string
896    {
897        return $this->longlifeQuery('age', 'M');
898    }
899
900    /**
901     * Find the name of the longest lived male.
902     *
903     * @return string
904     */
905    public function longestLifeMaleName(): string
906    {
907        return $this->longlifeQuery('name', 'M');
908    }
909
910    /**
911     * Returns the calculated age the time of event.
912     *
913     * @param int $age The age from the database record
914     *
915     * @return string
916     */
917    private function calculateAge(int $age): string
918    {
919        if ((int) ($age / 365.25) > 0) {
920            $result = (int) ($age / 365.25) . 'y';
921        } elseif ((int) ($age / 30.4375) > 0) {
922            $result = (int) ($age / 30.4375) . 'm';
923        } else {
924            $result = $age . 'd';
925        }
926
927        return FunctionsDate::getAgeAtEvent($result);
928    }
929
930    /**
931     * Find the oldest individuals.
932     *
933     * @param string $sex
934     * @param int    $total
935     *
936     * @return array
937     */
938    private function topTenOldestQuery(string $sex, int $total): array
939    {
940        $prefix = DB::connection()->getTablePrefix();
941
942        $rows = $this->birthAndDeathQuery($sex)
943            ->groupBy(['i_id', 'i_file'])
944            ->orderBy('days', 'desc')
945            ->select(['individuals.*', DB::raw('MAX(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days')])
946            ->take($total)
947            ->get();
948
949        $top10 = [];
950        foreach ($rows as $row) {
951            /** @var Individual $individual */
952            $individual = Individual::rowMapper()($row);
953
954            if ($individual->canShow()) {
955                $top10[] = [
956                    'person' => $individual,
957                    'age'    => $this->calculateAge((int) $row->days),
958                ];
959            }
960        }
961
962        return $top10;
963    }
964
965    /**
966     * Find the oldest individuals.
967     *
968     * @param int $total
969     *
970     * @return string
971     */
972    public function topTenOldest(int $total = 10): string
973    {
974        $records = $this->topTenOldestQuery('BOTH', $total);
975
976        return view(
977            'statistics/individuals/top10-nolist',
978            [
979                'records' => $records,
980            ]
981        );
982    }
983
984    /**
985     * Find the oldest living individuals.
986     *
987     * @param int $total
988     *
989     * @return string
990     */
991    public function topTenOldestList(int $total = 10): string
992    {
993        $records = $this->topTenOldestQuery('BOTH', $total);
994
995        return view(
996            'statistics/individuals/top10-list',
997            [
998                'records' => $records,
999            ]
1000        );
1001    }
1002
1003    /**
1004     * Find the oldest females.
1005     *
1006     * @param int $total
1007     *
1008     * @return string
1009     */
1010    public function topTenOldestFemale(int $total = 10): string
1011    {
1012        $records = $this->topTenOldestQuery('F', $total);
1013
1014        return view(
1015            'statistics/individuals/top10-nolist',
1016            [
1017                'records' => $records,
1018            ]
1019        );
1020    }
1021
1022    /**
1023     * Find the oldest living females.
1024     *
1025     * @param int $total
1026     *
1027     * @return string
1028     */
1029    public function topTenOldestFemaleList(int $total = 10): string
1030    {
1031        $records = $this->topTenOldestQuery('F', $total);
1032
1033        return view(
1034            'statistics/individuals/top10-list',
1035            [
1036                'records' => $records,
1037            ]
1038        );
1039    }
1040
1041    /**
1042     * Find the longest lived males.
1043     *
1044     * @param int $total
1045     *
1046     * @return string
1047     */
1048    public function topTenOldestMale(int $total = 10): string
1049    {
1050        $records = $this->topTenOldestQuery('M', $total);
1051
1052        return view(
1053            'statistics/individuals/top10-nolist',
1054            [
1055                'records' => $records,
1056            ]
1057        );
1058    }
1059
1060    /**
1061     * Find the longest lived males.
1062     *
1063     * @param int $total
1064     *
1065     * @return string
1066     */
1067    public function topTenOldestMaleList(int $total = 10): string
1068    {
1069        $records = $this->topTenOldestQuery('M', $total);
1070
1071        return view(
1072            'statistics/individuals/top10-list',
1073            [
1074                'records' => $records,
1075            ]
1076        );
1077    }
1078
1079    /**
1080     * Find the oldest living individuals.
1081     *
1082     * @param string $sex
1083     * @param int    $total
1084     *
1085     * @return array
1086     */
1087    private function topTenOldestAliveQuery(string $sex = 'BOTH', int $total = 10): array
1088    {
1089        $query = DB::table('dates')
1090            ->join('individuals', function (JoinClause $join): void {
1091                $join
1092                    ->on('i_id', '=', 'd_gid')
1093                    ->on('i_file', '=', 'd_file');
1094            })
1095            ->where('d_file', '=', $this->tree->id())
1096            ->where('d_julianday1', '<>', 0)
1097            ->where('d_fact', '=', 'BIRT')
1098            ->where('i_gedcom', 'NOT LIKE', "%\n1 DEAT%")
1099            ->where('i_gedcom', 'NOT LIKE', "%\n1 BURI%")
1100            ->where('i_gedcom', 'NOT LIKE', "%\n1 CREM%");
1101
1102        if ($sex === 'F' || $sex === 'M') {
1103            $query->where('i_sex', '=', $sex);
1104        }
1105
1106        $individuals = $query
1107            ->groupBy(['i_id', 'i_file'])
1108            ->orderBy(DB::raw('MIN(d_julianday1)'))
1109            ->select('individuals.*')
1110            ->take($total)
1111            ->get()
1112            ->map(Individual::rowMapper())
1113            ->filter(GedcomRecord::accessFilter());
1114
1115        $top10 = [];
1116
1117        foreach ($individuals as $individual) {
1118            $birth_jd = $individual->getBirthDate()->minimumJulianDay();
1119
1120            $top10[] = [
1121                'person' => $individual,
1122                'age'    => $this->calculateAge(WT_CLIENT_JD - $birth_jd),
1123            ];
1124        }
1125
1126        return $top10;
1127    }
1128
1129    /**
1130     * Find the oldest living individuals.
1131     *
1132     * @param int $total
1133     *
1134     * @return string
1135     */
1136    public function topTenOldestAlive(int $total = 10): string
1137    {
1138        if (!Auth::isMember($this->tree)) {
1139            return I18N::translate('This information is private and cannot be shown.');
1140        }
1141
1142        $records = $this->topTenOldestAliveQuery('BOTH', $total);
1143
1144        return view(
1145            'statistics/individuals/top10-nolist',
1146            [
1147                'records' => $records,
1148            ]
1149        );
1150    }
1151
1152    /**
1153     * Find the oldest living individuals.
1154     *
1155     * @param int $total
1156     *
1157     * @return string
1158     */
1159    public function topTenOldestListAlive(int $total = 10): string
1160    {
1161        if (!Auth::isMember($this->tree)) {
1162            return I18N::translate('This information is private and cannot be shown.');
1163        }
1164
1165        $records = $this->topTenOldestAliveQuery('BOTH', $total);
1166
1167        return view(
1168            'statistics/individuals/top10-list',
1169            [
1170                'records' => $records,
1171            ]
1172        );
1173    }
1174
1175    /**
1176     * Find the oldest living females.
1177     *
1178     * @param int $total
1179     *
1180     * @return string
1181     */
1182    public function topTenOldestFemaleAlive(int $total = 10): string
1183    {
1184        if (!Auth::isMember($this->tree)) {
1185            return I18N::translate('This information is private and cannot be shown.');
1186        }
1187
1188        $records = $this->topTenOldestAliveQuery('F', $total);
1189
1190        return view(
1191            'statistics/individuals/top10-nolist',
1192            [
1193                'records' => $records,
1194            ]
1195        );
1196    }
1197
1198    /**
1199     * Find the oldest living females.
1200     *
1201     * @param int $total
1202     *
1203     * @return string
1204     */
1205    public function topTenOldestFemaleListAlive(int $total = 10): string
1206    {
1207        if (!Auth::isMember($this->tree)) {
1208            return I18N::translate('This information is private and cannot be shown.');
1209        }
1210
1211        $records = $this->topTenOldestAliveQuery('F', $total);
1212
1213        return view(
1214            'statistics/individuals/top10-list',
1215            [
1216                'records' => $records,
1217            ]
1218        );
1219    }
1220
1221    /**
1222     * Find the longest lived living males.
1223     *
1224     * @param int $total
1225     *
1226     * @return string
1227     */
1228    public function topTenOldestMaleAlive(int $total = 10): string
1229    {
1230        if (!Auth::isMember($this->tree)) {
1231            return I18N::translate('This information is private and cannot be shown.');
1232        }
1233
1234        $records = $this->topTenOldestAliveQuery('M', $total);
1235
1236        return view(
1237            'statistics/individuals/top10-nolist',
1238            [
1239                'records' => $records,
1240            ]
1241        );
1242    }
1243
1244    /**
1245     * Find the longest lived living males.
1246     *
1247     * @param int $total
1248     *
1249     * @return string
1250     */
1251    public function topTenOldestMaleListAlive(int $total = 10): string
1252    {
1253        if (!Auth::isMember($this->tree)) {
1254            return I18N::translate('This information is private and cannot be shown.');
1255        }
1256
1257        $records = $this->topTenOldestAliveQuery('M', $total);
1258
1259        return view(
1260            'statistics/individuals/top10-list',
1261            [
1262                'records' => $records,
1263            ]
1264        );
1265    }
1266
1267    /**
1268     * Find the average lifespan.
1269     *
1270     * @param string $sex
1271     * @param bool   $show_years
1272     *
1273     * @return string
1274     */
1275    private function averageLifespanQuery(string $sex = 'BOTH', bool $show_years = false): string
1276    {
1277        $prefix = DB::connection()->getTablePrefix();
1278
1279        $days = (int) $this->birthAndDeathQuery($sex)
1280            ->select(DB::raw('AVG(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) AS days'))
1281            ->value('days');
1282
1283        if ($show_years) {
1284            return $this->calculateAge($days);
1285        }
1286
1287        return I18N::number((int) ($days / 365.25));
1288    }
1289
1290    /**
1291     * Find the average lifespan.
1292     *
1293     * @param bool $show_years
1294     *
1295     * @return string
1296     */
1297    public function averageLifespan($show_years = false): string
1298    {
1299        return $this->averageLifespanQuery('BOTH', $show_years);
1300    }
1301
1302    /**
1303     * Find the average lifespan of females.
1304     *
1305     * @param bool $show_years
1306     *
1307     * @return string
1308     */
1309    public function averageLifespanFemale($show_years = false): string
1310    {
1311        return $this->averageLifespanQuery('F', $show_years);
1312    }
1313
1314    /**
1315     * Find the average male lifespan.
1316     *
1317     * @param bool $show_years
1318     *
1319     * @return string
1320     */
1321    public function averageLifespanMale($show_years = false): string
1322    {
1323        return $this->averageLifespanQuery('M', $show_years);
1324    }
1325
1326    /**
1327     * Convert totals into percentages.
1328     *
1329     * @param int $count
1330     * @param int $total
1331     *
1332     * @return string
1333     */
1334    private function getPercentage(int $count, int $total): string
1335    {
1336        return ($total !== 0) ? I18N::percentage($count / $total, 1) : '';
1337    }
1338
1339    /**
1340     * Returns how many individuals exist in the tree.
1341     *
1342     * @return int
1343     */
1344    private function totalIndividualsQuery(): int
1345    {
1346        return DB::table('individuals')
1347            ->where('i_file', '=', $this->tree->id())
1348            ->count();
1349    }
1350
1351    /**
1352     * Count the number of living individuals.
1353     *
1354     * The totalLiving/totalDeceased queries assume that every dead person will
1355     * have a DEAT record. It will not include individuals who were born more
1356     * than MAX_ALIVE_AGE years ago, and who have no DEAT record.
1357     * A good reason to run the “Add missing DEAT records” batch-update!
1358     *
1359     * @return int
1360     */
1361    private function totalLivingQuery(): int
1362    {
1363        $query = DB::table('individuals')
1364            ->where('i_file', '=', $this->tree->id());
1365
1366        foreach (Gedcom::DEATH_EVENTS as $death_event) {
1367            $query->where('i_gedcom', 'NOT LIKE', "%\n1 " . $death_event . '%');
1368        }
1369
1370        return $query->count();
1371    }
1372
1373    /**
1374     * Count the number of dead individuals.
1375     *
1376     * @return int
1377     */
1378    private function totalDeceasedQuery(): int
1379    {
1380        return DB::table('individuals')
1381            ->where('i_file', '=', $this->tree->id())
1382            ->where(function (Builder $query): void {
1383                foreach (Gedcom::DEATH_EVENTS as $death_event) {
1384                    $query->orWhere('i_gedcom', 'LIKE', "%\n1 " . $death_event . '%');
1385                }
1386            })
1387            ->count();
1388    }
1389
1390    /**
1391     * Returns the total count of a specific sex.
1392     *
1393     * @param string $sex The sex to query
1394     *
1395     * @return int
1396     */
1397    private function getTotalSexQuery(string $sex): int
1398    {
1399        return DB::table('individuals')
1400            ->where('i_file', '=', $this->tree->id())
1401            ->where('i_sex', '=', $sex)
1402            ->count();
1403    }
1404
1405    /**
1406     * Returns the total number of males.
1407     *
1408     * @return int
1409     */
1410    private function totalSexMalesQuery(): int
1411    {
1412        return $this->getTotalSexQuery('M');
1413    }
1414
1415    /**
1416     * Returns the total number of females.
1417     *
1418     * @return int
1419     */
1420    private function totalSexFemalesQuery(): int
1421    {
1422        return $this->getTotalSexQuery('F');
1423    }
1424
1425    /**
1426     * Returns the total number of individuals with unknown sex.
1427     *
1428     * @return int
1429     */
1430    private function totalSexUnknownQuery(): int
1431    {
1432        return $this->getTotalSexQuery('U');
1433    }
1434
1435    /**
1436     * Count the total families.
1437     *
1438     * @return int
1439     */
1440    private function totalFamiliesQuery(): int
1441    {
1442        return DB::table('families')
1443            ->where('f_file', '=', $this->tree->id())
1444            ->count();
1445    }
1446
1447    /**
1448     * How many individuals have one or more sources.
1449     *
1450     * @return int
1451     */
1452    private function totalIndisWithSourcesQuery(): int
1453    {
1454        return DB::table('individuals')
1455            ->select(['i_id'])
1456            ->distinct()
1457            ->join('link', function (JoinClause $join) {
1458                $join->on('i_id', '=', 'l_from')
1459                    ->on('i_file', '=', 'l_file');
1460            })
1461            ->where('l_file', '=', $this->tree->id())
1462            ->where('l_type', '=', 'SOUR')
1463            ->count('i_id');
1464    }
1465
1466    /**
1467     * Count the families with source records.
1468     *
1469     * @return int
1470     */
1471    private function totalFamsWithSourcesQuery(): int
1472    {
1473        return DB::table('families')
1474            ->select(['f_id'])
1475            ->distinct()
1476            ->join('link', function (JoinClause $join) {
1477                $join->on('f_id', '=', 'l_from')
1478                    ->on('f_file', '=', 'l_file');
1479            })
1480            ->where('l_file', '=', $this->tree->id())
1481            ->where('l_type', '=', 'SOUR')
1482            ->count('f_id');
1483    }
1484
1485    /**
1486     * Count the number of repositories.
1487     *
1488     * @return int
1489     */
1490    private function totalRepositoriesQuery(): int
1491    {
1492        return DB::table('other')
1493            ->where('o_file', '=', $this->tree->id())
1494            ->where('o_type', '=', 'REPO')
1495            ->count();
1496    }
1497
1498    /**
1499     * Count the total number of sources.
1500     *
1501     * @return int
1502     */
1503    private function totalSourcesQuery(): int
1504    {
1505        return DB::table('sources')
1506            ->where('s_file', '=', $this->tree->id())
1507            ->count();
1508    }
1509
1510    /**
1511     * Count the number of notes.
1512     *
1513     * @return int
1514     */
1515    private function totalNotesQuery(): int
1516    {
1517        return DB::table('other')
1518            ->where('o_file', '=', $this->tree->id())
1519            ->where('o_type', '=', 'NOTE')
1520            ->count();
1521    }
1522
1523    /**
1524     * Returns the total number of records.
1525     *
1526     * @return int
1527     */
1528    private function totalRecordsQuery(): int
1529    {
1530        return $this->totalIndividualsQuery()
1531            + $this->totalFamiliesQuery()
1532            + $this->totalNotesQuery()
1533            + $this->totalRepositoriesQuery()
1534            + $this->totalSourcesQuery();
1535    }
1536
1537    /**
1538     * @inheritDoc
1539     */
1540    public function totalRecords(): string
1541    {
1542        return I18N::number($this->totalRecordsQuery());
1543    }
1544
1545    /**
1546     * @inheritDoc
1547     */
1548    public function totalIndividuals(): string
1549    {
1550        return I18N::number($this->totalIndividualsQuery());
1551    }
1552
1553    /**
1554     * Count the number of living individuals.
1555     *
1556     * @return string
1557     */
1558    public function totalLiving(): string
1559    {
1560        return I18N::number($this->totalLivingQuery());
1561    }
1562
1563    /**
1564     * Count the number of dead individuals.
1565     *
1566     * @return string
1567     */
1568    public function totalDeceased(): string
1569    {
1570        return I18N::number($this->totalDeceasedQuery());
1571    }
1572
1573    /**
1574     * @inheritDoc
1575     */
1576    public function totalSexMales(): string
1577    {
1578        return I18N::number($this->totalSexMalesQuery());
1579    }
1580
1581    /**
1582     * @inheritDoc
1583     */
1584    public function totalSexFemales(): string
1585    {
1586        return I18N::number($this->totalSexFemalesQuery());
1587    }
1588
1589    /**
1590     * @inheritDoc
1591     */
1592    public function totalSexUnknown(): string
1593    {
1594        return I18N::number($this->totalSexUnknownQuery());
1595    }
1596
1597    /**
1598     * @inheritDoc
1599     */
1600    public function totalFamilies(): string
1601    {
1602        return I18N::number($this->totalFamiliesQuery());
1603    }
1604
1605    /**
1606     * How many individuals have one or more sources.
1607     *
1608     * @return string
1609     */
1610    public function totalIndisWithSources(): string
1611    {
1612        return I18N::number($this->totalIndisWithSourcesQuery());
1613    }
1614
1615    /**
1616     * Count the families with with source records.
1617     *
1618     * @return string
1619     */
1620    public function totalFamsWithSources(): string
1621    {
1622        return I18N::number($this->totalFamsWithSourcesQuery());
1623    }
1624
1625    /**
1626     * @inheritDoc
1627     */
1628    public function totalRepositories(): string
1629    {
1630        return I18N::number($this->totalRepositoriesQuery());
1631    }
1632
1633    /**
1634     * @inheritDoc
1635     */
1636    public function totalSources(): string
1637    {
1638        return I18N::number($this->totalSourcesQuery());
1639    }
1640
1641    /**
1642     * @inheritDoc
1643     */
1644    public function totalNotes(): string
1645    {
1646        return I18N::number($this->totalNotesQuery());
1647    }
1648
1649    /**
1650     * @inheritDoc
1651     */
1652    public function totalIndividualsPercentage(): string
1653    {
1654        return $this->getPercentage(
1655            $this->totalIndividualsQuery(),
1656            $this->totalRecordsQuery()
1657        );
1658    }
1659
1660    /**
1661     * @inheritDoc
1662     */
1663    public function totalFamiliesPercentage(): string
1664    {
1665        return $this->getPercentage(
1666            $this->totalFamiliesQuery(),
1667            $this->totalRecordsQuery()
1668        );
1669    }
1670
1671    /**
1672     * @inheritDoc
1673     */
1674    public function totalRepositoriesPercentage(): string
1675    {
1676        return $this->getPercentage(
1677            $this->totalRepositoriesQuery(),
1678            $this->totalRecordsQuery()
1679        );
1680    }
1681
1682    /**
1683     * @inheritDoc
1684     */
1685    public function totalSourcesPercentage(): string
1686    {
1687        return $this->getPercentage(
1688            $this->totalSourcesQuery(),
1689            $this->totalRecordsQuery()
1690        );
1691    }
1692
1693    /**
1694     * @inheritDoc
1695     */
1696    public function totalNotesPercentage(): string
1697    {
1698        return $this->getPercentage(
1699            $this->totalNotesQuery(),
1700            $this->totalRecordsQuery()
1701        );
1702    }
1703
1704    /**
1705     * @inheritDoc
1706     */
1707    public function totalLivingPercentage(): string
1708    {
1709        return $this->getPercentage(
1710            $this->totalLivingQuery(),
1711            $this->totalIndividualsQuery()
1712        );
1713    }
1714
1715    /**
1716     * @inheritDoc
1717     */
1718    public function totalDeceasedPercentage(): string
1719    {
1720        return $this->getPercentage(
1721            $this->totalDeceasedQuery(),
1722            $this->totalIndividualsQuery()
1723        );
1724    }
1725
1726    /**
1727     * @inheritDoc
1728     */
1729    public function totalSexMalesPercentage(): string
1730    {
1731        return $this->getPercentage(
1732            $this->totalSexMalesQuery(),
1733            $this->totalIndividualsQuery()
1734        );
1735    }
1736
1737    /**
1738     * @inheritDoc
1739     */
1740    public function totalSexFemalesPercentage(): string
1741    {
1742        return $this->getPercentage(
1743            $this->totalSexFemalesQuery(),
1744            $this->totalIndividualsQuery()
1745        );
1746    }
1747
1748    /**
1749     * @inheritDoc
1750     */
1751    public function totalSexUnknownPercentage(): string
1752    {
1753        return $this->getPercentage(
1754            $this->totalSexUnknownQuery(),
1755            $this->totalIndividualsQuery()
1756        );
1757    }
1758
1759    /**
1760     * Create a chart of common given names.
1761     *
1762     * @param string|null $color_from
1763     * @param string|null $color_to
1764     * @param int         $maxtoshow
1765     *
1766     * @return string
1767     */
1768    public function chartCommonGiven(
1769        string $color_from = null,
1770        string $color_to = null,
1771        int $maxtoshow = 7
1772    ): string {
1773        $tot_indi = $this->totalIndividualsQuery();
1774        $given    = $this->commonGivenQuery('B', 'chart', false, 1, $maxtoshow);
1775
1776        if (empty($given)) {
1777            return I18N::translate('This information is not available.');
1778        }
1779
1780        return (new ChartCommonGiven($this->tree))
1781            ->chartCommonGiven($tot_indi, $given, $color_from, $color_to);
1782    }
1783
1784    /**
1785     * Create a chart of common surnames.
1786     *
1787     * @param string|null $color_from
1788     * @param string|null $color_to
1789     * @param int         $number_of_surnames
1790     *
1791     * @return string
1792     */
1793    public function chartCommonSurnames(
1794        string $color_from = null,
1795        string $color_to = null,
1796        int $number_of_surnames = 10
1797    ): string {
1798        $tot_indi     = $this->totalIndividualsQuery();
1799        $all_surnames = $this->topSurnames($number_of_surnames, 0);
1800
1801        if (empty($all_surnames)) {
1802            return I18N::translate('This information is not available.');
1803        }
1804
1805        return (new ChartCommonSurname($this->tree))
1806            ->chartCommonSurnames($tot_indi, $all_surnames, $color_from, $color_to);
1807    }
1808
1809    /**
1810     * Create a chart showing mortality.
1811     *
1812     * @param string|null $color_living
1813     * @param string|null $color_dead
1814     *
1815     * @return string
1816     */
1817    public function chartMortality(string $color_living = null, string $color_dead = null): string
1818    {
1819        $tot_l = $this->totalLivingQuery();
1820        $tot_d = $this->totalDeceasedQuery();
1821
1822        return (new ChartMortality($this->tree))
1823            ->chartMortality($tot_l, $tot_d, $color_living, $color_dead);
1824    }
1825
1826    /**
1827     * Create a chart showing individuals with/without sources.
1828     *
1829     * @param string|null $color_from
1830     * @param string|null $color_to
1831     *
1832     * @return string
1833     */
1834    public function chartIndisWithSources(
1835        string $color_from = null,
1836        string $color_to   = null
1837    ): string {
1838        $tot_indi        = $this->totalIndividualsQuery();
1839        $tot_indi_source = $this->totalIndisWithSourcesQuery();
1840
1841        return (new ChartIndividualWithSources($this->tree))
1842            ->chartIndisWithSources($tot_indi, $tot_indi_source, $color_from, $color_to);
1843    }
1844
1845    /**
1846     * Create a chart of individuals with/without sources.
1847     *
1848     * @param string|null $color_from
1849     * @param string|null $color_to
1850     *
1851     * @return string
1852     */
1853    public function chartFamsWithSources(
1854        string $color_from = null,
1855        string $color_to   = null
1856    ): string {
1857        $tot_fam        = $this->totalFamiliesQuery();
1858        $tot_fam_source = $this->totalFamsWithSourcesQuery();
1859
1860        return (new ChartFamilyWithSources($this->tree))
1861            ->chartFamsWithSources($tot_fam, $tot_fam_source, $color_from, $color_to);
1862    }
1863
1864    /**
1865     * @inheritDoc
1866     */
1867    public function chartSex(
1868        string $color_female  = null,
1869        string $color_male    = null,
1870        string $color_unknown = null
1871    ): string {
1872        $tot_m = $this->totalSexMalesQuery();
1873        $tot_f = $this->totalSexFemalesQuery();
1874        $tot_u = $this->totalSexUnknownQuery();
1875
1876        return (new ChartSex($this->tree))
1877            ->chartSex($tot_m, $tot_f, $tot_u, $color_female, $color_male, $color_unknown);
1878    }
1879
1880    /**
1881     * Query individuals, with their births and deaths.
1882     *
1883     * @param string $sex
1884     *
1885     * @return Builder
1886     */
1887    private function birthAndDeathQuery(string $sex): Builder
1888    {
1889        $query = DB::table('individuals')
1890            ->where('i_file', '=', $this->tree->id())
1891            ->join('dates AS birth', function (JoinClause $join): void {
1892                $join
1893                    ->on('birth.d_file', '=', 'i_file')
1894                    ->on('birth.d_gid', '=', 'i_id');
1895            })
1896            ->join('dates AS death', function (JoinClause $join): void {
1897                $join
1898                    ->on('death.d_file', '=', 'i_file')
1899                    ->on('death.d_gid', '=', 'i_id');
1900            })
1901            ->where('birth.d_fact', '=', 'BIRT')
1902            ->where('death.d_fact', '=', 'DEAT')
1903            ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2')
1904            ->where('birth.d_julianday2', '<>', 0);
1905
1906        if ($sex === 'M' || $sex === 'F') {
1907            $query->where('i_sex', '=', $sex);
1908        }
1909
1910        return $query;
1911    }
1912}
1913