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