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\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