xref: /webtrees/app/Statistics/Google/ChartMarriageAge.php (revision 34b20f294deb14f65541b617e67c4eb4ff84d013)
18add1155SRico Sonntag<?php
23976b470SGreg Roach
38add1155SRico Sonntag/**
48add1155SRico Sonntag * webtrees: online genealogy
589f7189bSGreg Roach * Copyright (C) 2021 webtrees development team
68add1155SRico Sonntag * This program is free software: you can redistribute it and/or modify
78add1155SRico Sonntag * it under the terms of the GNU General Public License as published by
88add1155SRico Sonntag * the Free Software Foundation, either version 3 of the License, or
98add1155SRico Sonntag * (at your option) any later version.
108add1155SRico Sonntag * This program is distributed in the hope that it will be useful,
118add1155SRico Sonntag * but WITHOUT ANY WARRANTY; without even the implied warranty of
128add1155SRico Sonntag * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
138add1155SRico Sonntag * GNU General Public License for more details.
148add1155SRico Sonntag * You should have received a copy of the GNU General Public License
1589f7189bSGreg Roach * along with this program. If not, see <https://www.gnu.org/licenses/>.
168add1155SRico Sonntag */
17fcfa147eSGreg Roach
188add1155SRico Sonntagdeclare(strict_types=1);
198add1155SRico Sonntag
208add1155SRico Sonntagnamespace Fisharebest\Webtrees\Statistics\Google;
218add1155SRico Sonntag
228add1155SRico Sonntaguse Fisharebest\Webtrees\I18N;
2393ccd686SRico Sonntaguse Fisharebest\Webtrees\Statistics\Service\CenturyService;
248add1155SRico Sonntaguse Fisharebest\Webtrees\Tree;
250892c7deSRico Sonntaguse Illuminate\Database\Capsule\Manager as DB;
26a69f5655SGreg Roachuse Illuminate\Database\Query\Expression;
270892c7deSRico Sonntaguse Illuminate\Database\Query\JoinClause;
2834e3587bSGreg Roachuse Illuminate\Support\Collection;
298add1155SRico Sonntag
30*34b20f29SGreg Roachuse function round;
31*34b20f29SGreg Roachuse function view;
32*34b20f29SGreg Roach
338add1155SRico Sonntag/**
3493ccd686SRico Sonntag * A chart showing the marriage ages by century.
358add1155SRico Sonntag */
3693ccd686SRico Sonntagclass ChartMarriageAge
378add1155SRico Sonntag{
38*34b20f29SGreg Roach    private Tree $tree;
3993ccd686SRico Sonntag
40*34b20f29SGreg Roach    private CenturyService $century_service;
418add1155SRico Sonntag
428add1155SRico Sonntag    /**
438add1155SRico Sonntag     * Constructor.
448add1155SRico Sonntag     *
458add1155SRico Sonntag     * @param Tree $tree
468add1155SRico Sonntag     */
478add1155SRico Sonntag    public function __construct(Tree $tree)
488add1155SRico Sonntag    {
4993ccd686SRico Sonntag        $this->tree            = $tree;
5093ccd686SRico Sonntag        $this->century_service = new CenturyService();
518add1155SRico Sonntag    }
528add1155SRico Sonntag
538add1155SRico Sonntag    /**
548add1155SRico Sonntag     * Returns the related database records.
558add1155SRico Sonntag     *
56f70bcff5SGreg Roach     * @return Collection<object>
578add1155SRico Sonntag     */
5834e3587bSGreg Roach    private function queryRecords(): Collection
598add1155SRico Sonntag    {
600892c7deSRico Sonntag        $prefix = DB::connection()->getTablePrefix();
610892c7deSRico Sonntag
620892c7deSRico Sonntag        $male = DB::table('dates as married')
630892c7deSRico Sonntag            ->select([
6434e3587bSGreg Roach                new Expression('AVG(' . $prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 - 182.5) / 365.25 AS age'),
65a69f5655SGreg Roach                new Expression('ROUND((' . $prefix . 'married.d_year + 49) / 100) AS century'),
66a69f5655SGreg Roach                new Expression("'M' as sex")
670892c7deSRico Sonntag            ])
680b5fd0a6SGreg Roach            ->join('families as fam', static function (JoinClause $join): void {
690892c7deSRico Sonntag                $join->on('fam.f_id', '=', 'married.d_gid')
700892c7deSRico Sonntag                    ->on('fam.f_file', '=', 'married.d_file');
710892c7deSRico Sonntag            })
720b5fd0a6SGreg Roach            ->join('dates as birth', static function (JoinClause $join): void {
730892c7deSRico Sonntag                $join->on('birth.d_gid', '=', 'fam.f_husb')
740892c7deSRico Sonntag                    ->on('birth.d_file', '=', 'fam.f_file');
750892c7deSRico Sonntag            })
760892c7deSRico Sonntag            ->whereIn('married.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
770892c7deSRico Sonntag            ->where('married.d_file', '=', $this->tree->id())
780892c7deSRico Sonntag            ->where('married.d_fact', '=', 'MARR')
790892c7deSRico Sonntag            ->where('married.d_julianday1', '>', 'birth.d_julianday1')
800892c7deSRico Sonntag            ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
810892c7deSRico Sonntag            ->where('birth.d_fact', '=', 'BIRT')
820892c7deSRico Sonntag            ->where('birth.d_julianday1', '<>', 0)
830892c7deSRico Sonntag            ->groupBy(['century', 'sex']);
840892c7deSRico Sonntag
850892c7deSRico Sonntag        $female = DB::table('dates as married')
860892c7deSRico Sonntag            ->select([
87a69f5655SGreg Roach                new Expression('ROUND(AVG(' . $prefix . 'married.d_julianday2 - ' . $prefix . 'birth.d_julianday1 - 182.5) / 365.25, 1) AS age'),
88a69f5655SGreg Roach                new Expression('ROUND((' . $prefix . 'married.d_year + 49) / 100) AS century'),
89a69f5655SGreg Roach                new Expression("'F' as sex")
900892c7deSRico Sonntag            ])
910b5fd0a6SGreg Roach            ->join('families as fam', static function (JoinClause $join): void {
920892c7deSRico Sonntag                $join->on('fam.f_id', '=', 'married.d_gid')
930892c7deSRico Sonntag                    ->on('fam.f_file', '=', 'married.d_file');
940892c7deSRico Sonntag            })
950b5fd0a6SGreg Roach            ->join('dates as birth', static function (JoinClause $join): void {
960892c7deSRico Sonntag                $join->on('birth.d_gid', '=', 'fam.f_wife')
970892c7deSRico Sonntag                    ->on('birth.d_file', '=', 'fam.f_file');
980892c7deSRico Sonntag            })
990892c7deSRico Sonntag            ->whereIn('married.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
1000892c7deSRico Sonntag            ->where('married.d_file', '=', $this->tree->id())
1010892c7deSRico Sonntag            ->where('married.d_fact', '=', 'MARR')
1020892c7deSRico Sonntag            ->where('married.d_julianday1', '>', 'birth.d_julianday1')
1030892c7deSRico Sonntag            ->whereIn('birth.d_type', ['@#DGREGORIAN@', '@#DJULIAN@'])
1040892c7deSRico Sonntag            ->where('birth.d_fact', '=', 'BIRT')
1050892c7deSRico Sonntag            ->where('birth.d_julianday1', '<>', 0)
1060892c7deSRico Sonntag            ->groupBy(['century', 'sex']);
1070892c7deSRico Sonntag
1080892c7deSRico Sonntag        return $male->unionAll($female)
1090892c7deSRico Sonntag            ->orderBy('century')
1100892c7deSRico Sonntag            ->get()
111f70bcff5SGreg Roach            ->map(static function (object $row): object {
11234e3587bSGreg Roach                return (object) [
11334e3587bSGreg Roach                    'age'     => (float) $row->age,
11434e3587bSGreg Roach                    'century' => (int) $row->century,
11534e3587bSGreg Roach                    'sex'     => $row->sex,
11634e3587bSGreg Roach                ];
11734e3587bSGreg Roach            });
1188add1155SRico Sonntag    }
1198add1155SRico Sonntag
1208add1155SRico Sonntag    /**
1218add1155SRico Sonntag     * General query on ages at marriage.
1228add1155SRico Sonntag     *
1238add1155SRico Sonntag     * @return string
1248add1155SRico Sonntag     */
12588de55fdSRico Sonntag    public function chartMarriageAge(): string
1268add1155SRico Sonntag    {
1278add1155SRico Sonntag        $out = [];
1288add1155SRico Sonntag
1290892c7deSRico Sonntag        foreach ($this->queryRecords() as $record) {
13034e3587bSGreg Roach            $out[$record->century][$record->sex] = $record->age;
1318add1155SRico Sonntag        }
1328add1155SRico Sonntag
13388de55fdSRico Sonntag        $data = [
13488de55fdSRico Sonntag            [
13588de55fdSRico Sonntag                I18N::translate('Century'),
13688de55fdSRico Sonntag                I18N::translate('Males'),
13788de55fdSRico Sonntag                I18N::translate('Females'),
13888de55fdSRico Sonntag                I18N::translate('Average age'),
13988de55fdSRico Sonntag            ]
14088de55fdSRico Sonntag        ];
14188de55fdSRico Sonntag
1428add1155SRico Sonntag        foreach ($out as $century => $values) {
14388de55fdSRico Sonntag            $female_age  = $values['F'] ?? 0;
14488de55fdSRico Sonntag            $male_age    = $values['M'] ?? 0;
14588de55fdSRico Sonntag            $average_age = ($female_age + $male_age) / 2.0;
1468add1155SRico Sonntag
14788de55fdSRico Sonntag            $data[] = [
14893ccd686SRico Sonntag                $this->century_service->centuryName($century),
14934e3587bSGreg Roach                round($male_age, 1),
15034e3587bSGreg Roach                round($female_age, 1),
15134e3587bSGreg Roach                round($average_age, 1),
15288de55fdSRico Sonntag            ];
1538add1155SRico Sonntag        }
1548add1155SRico Sonntag
1551b860509SRico Sonntag        $chart_title   = I18N::translate('Average age in century of marriage');
1561b860509SRico Sonntag        $chart_options = [
1571b860509SRico Sonntag            'title' => $chart_title,
1581b860509SRico Sonntag            'subtitle' => I18N::translate('Average age at marriage'),
1591b860509SRico Sonntag            'vAxis' => [
1601b860509SRico Sonntag                'title' => I18N::translate('Age'),
1611b860509SRico Sonntag            ],
1621b860509SRico Sonntag            'hAxis' => [
1631b860509SRico Sonntag                'title' => I18N::translate('Century'),
1641b860509SRico Sonntag            ],
1651b860509SRico Sonntag            'colors' => [
1661b860509SRico Sonntag                '#84beff',
1671b860509SRico Sonntag                '#ffd1dc',
1681b860509SRico Sonntag                '#ff0000',
1691b860509SRico Sonntag            ],
1701b860509SRico Sonntag        ];
1711b860509SRico Sonntag
17290a2f718SGreg Roach        return view('statistics/other/charts/combo', [
17388de55fdSRico Sonntag            'data'          => $data,
1741b860509SRico Sonntag            'chart_options' => $chart_options,
1751b860509SRico Sonntag            'chart_title'   => $chart_title,
17665cf5706SGreg Roach            'language'      => I18N::languageTag(),
17790a2f718SGreg Roach        ]);
1788add1155SRico Sonntag    }
1798add1155SRico Sonntag}
180