xref: /webtrees/app/Statistics/Google/ChartAge.php (revision dd7dd2a11a7399e56fa4d21fb56b0ecdff69c7d0) 
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\Google;
19
20use Fisharebest\Webtrees\I18N;
21use Fisharebest\Webtrees\Statistics\AbstractGoogle;
22use Fisharebest\Webtrees\Statistics\Helper\Century;
23use Fisharebest\Webtrees\Tree;
24use Illuminate\Database\Capsule\Manager as DB;
25use Illuminate\Database\Query\JoinClause;
26
27/**
28 *
29 */
30class ChartAge extends AbstractGoogle
31{
32    /**
33     * @var Century
34     */
35    private $centuryHelper;
36
37    /**
38     * Constructor.
39     *
40     * @param Tree $tree
41     */
42    public function __construct(Tree $tree)
43    {
44        parent::__construct($tree);
45
46        $this->centuryHelper = new Century();
47    }
48
49    /**
50     * Returns the related database records.
51     *
52     * @return \stdClass[]
53     */
54    private function queryRecords(): array
55    {
56        $prefix = DB::connection()->getTablePrefix();
57
58        return DB::table('individuals')
59            ->select([
60                DB::raw('ROUND(AVG(' . $prefix . 'death.d_julianday2 - ' . $prefix . 'birth.d_julianday1) / 365.25,1) AS age'),
61                DB::raw('ROUND((' . $prefix . 'death.d_year - 50) / 100) AS century'),
62                'i_sex AS sex'
63            ])
64            ->join('dates AS birth', function (JoinClause $join): void {
65                $join
66                    ->on('birth.d_file', '=', 'i_file')
67                    ->on('birth.d_gid', '=', 'i_id');
68            })
69            ->join('dates AS death', function (JoinClause $join): void {
70                $join
71                    ->on('death.d_file', '=', 'i_file')
72                    ->on('death.d_gid', '=', 'i_id');
73            })
74            ->where('i_file', '=', $this->tree->id())
75            ->where('birth.d_fact', '=', 'BIRT')
76            ->where('death.d_fact', '=', 'DEAT')
77            ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
78            ->whereIn('death.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
79            ->whereColumn('death.d_julianday1', '>=', 'birth.d_julianday2')
80            ->where('birth.d_julianday2', '<>', 0)
81            ->groupBy(['century', 'sex'])
82            ->orderBy('century')
83            ->orderBy('sex')
84            ->get()
85            ->all();
86    }
87
88    /**
89     * General query on ages.
90     *
91     * @return string
92     */
93    public function chartAge(): string
94    {
95        $out = [];
96        foreach ($this->queryRecords() as $record) {
97            $out[(int) $record->century][$record->sex] = (float) $record->age;
98        }
99
100        $data = [
101            [
102                I18N::translate('Century'),
103                I18N::translate('Males'),
104                I18N::translate('Females'),
105                I18N::translate('Average age'),
106            ]
107        ];
108
109        foreach ($out as $century => $values) {
110            $female_age  = $values['F'] ?? 0;
111            $male_age    = $values['M'] ?? 0;
112            $average_age = ($female_age + $male_age) / 2.0;
113
114            $data[] = [
115                $this->centuryHelper->centuryName($century),
116                $male_age,
117                $female_age,
118                $average_age,
119            ];
120        }
121
122        return view(
123            'statistics/other/charts/combo',
124            [
125                'data'            => $data,
126                'colors'          => ['#84beff', '#ffd1dc', '#ff0000'],
127                'chart_title'     => I18N::translate('Average age related to death century'),
128                'chart_sub_title' => I18N::translate('Average age at death'),
129                'hAxis_title'     => I18N::translate('Century'),
130                'vAxis_title'     => I18N::translate('Age'),
131            ]
132        );
133    }
134}
135