1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2023 webtrees development team 6 * This program is free software: you can redistribute it and/or modify 7 * it under the terms of the GNU General Public License as published by 8 * the Free Software Foundation, either version 3 of the License, or 9 * (at your option) any later version. 10 * This program is distributed in the hope that it will be useful, 11 * but WITHOUT ANY WARRANTY; without even the implied warranty of 12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 * GNU General Public License for more details. 14 * You should have received a copy of the GNU General Public License 15 * along with this program. If not, see <https://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Statistics\Google; 21 22use Fisharebest\Webtrees\I18N; 23use Fisharebest\Webtrees\Statistics\Repository\Interfaces\IndividualRepositoryInterface; 24use Fisharebest\Webtrees\Statistics\Service\CountryService; 25use Fisharebest\Webtrees\Tree; 26use Illuminate\Database\Capsule\Manager as DB; 27use Illuminate\Database\Query\Builder; 28use Illuminate\Database\Query\Expression; 29use Illuminate\Database\Query\JoinClause; 30 31use function preg_match; 32use function preg_quote; 33use function view; 34 35/** 36 * A chart showing the distribution of different events on a map. 37 */ 38class ChartDistribution 39{ 40 private Tree $tree; 41 42 private CountryService $country_service; 43 44 private IndividualRepositoryInterface $individual_repository; 45 46 /** 47 * @var array<string> 48 */ 49 private array $country_to_iso3166; 50 51 /** 52 * @param Tree $tree 53 * @param CountryService $country_service 54 * @param IndividualRepositoryInterface $individual_repository 55 */ 56 public function __construct( 57 Tree $tree, 58 CountryService $country_service, 59 IndividualRepositoryInterface $individual_repository 60 ) { 61 $this->tree = $tree; 62 $this->country_service = $country_service; 63 $this->individual_repository = $individual_repository; 64 65 // Get the country names for each language 66 $this->country_to_iso3166 = $this->getIso3166Countries(); 67 } 68 69 /** 70 * Returns the country names for each language. 71 * 72 * @return array<string> 73 */ 74 private function getIso3166Countries(): array 75 { 76 // Get the country names for each language 77 $country_to_iso3166 = []; 78 79 $current_language = I18N::languageTag(); 80 81 foreach (I18N::activeLocales() as $locale) { 82 I18N::init($locale->languageTag()); 83 84 $countries = $this->country_service->getAllCountries(); 85 86 foreach ($this->country_service->iso3166() as $three => $two) { 87 $country_to_iso3166[$three] = $two; 88 $country_to_iso3166[$countries[$three]] = $two; 89 } 90 } 91 92 I18N::init($current_language); 93 94 return $country_to_iso3166; 95 } 96 97 /** 98 * Returns the data structure required by google geochart. 99 * 100 * @param array<int> $places 101 * 102 * @return array<int,array<int|string|array<string,string>>> 103 */ 104 private function createChartData(array $places): array 105 { 106 $data = [ 107 [ 108 I18N::translate('Country'), 109 I18N::translate('Total'), 110 ], 111 ]; 112 113 // webtrees uses 3-letter country codes and localised country names, but google uses 2 letter codes. 114 foreach ($places as $country => $count) { 115 $data[] = [ 116 [ 117 'v' => $country, 118 'f' => $this->country_service->mapTwoLetterToName($country), 119 ], 120 $count 121 ]; 122 } 123 124 return $data; 125 } 126 127 /** 128 * @param Tree $tree 129 * 130 * @return array<int> 131 */ 132 private function countIndividualsByCountry(Tree $tree): array 133 { 134 $rows = DB::table('places') 135 ->where('p_file', '=', $tree->id()) 136 ->where('p_parent_id', '=', 0) 137 ->join('placelinks', static function (JoinClause $join): void { 138 $join 139 ->on('pl_file', '=', 'p_file') 140 ->on('pl_p_id', '=', 'p_id'); 141 }) 142 ->join('individuals', static function (JoinClause $join): void { 143 $join 144 ->on('pl_file', '=', 'i_file') 145 ->on('pl_gid', '=', 'i_id'); 146 }) 147 ->groupBy('p_place') 148 ->pluck(new Expression('COUNT(*)'), 'p_place'); 149 150 $totals = []; 151 152 foreach ($rows as $country => $count) { 153 $country_code = $this->country_to_iso3166[$country] ?? null; 154 155 if ($country_code !== null) { 156 $totals[$country_code] = $count + ($totals[$country_code] ?? 0); 157 } 158 } 159 160 return $totals; 161 } 162 163 /** 164 * @param Tree $tree 165 * @param string $surname 166 * 167 * @return array<int> 168 */ 169 private function countSurnamesByCountry(Tree $tree, string $surname): array 170 { 171 $rows = 172 DB::table('places') 173 ->where('p_file', '=', $tree->id()) 174 ->where('p_parent_id', '=', 0) 175 ->join('placelinks', static function (JoinClause $join): void { 176 $join 177 ->on('pl_file', '=', 'p_file') 178 ->on('pl_p_id', '=', 'p_id'); 179 }) 180 ->join('name', static function (JoinClause $join): void { 181 $join 182 ->on('n_file', '=', 'pl_file') 183 ->on('n_id', '=', 'pl_gid'); 184 }) 185 ->where('n_surn', '=', $surname) 186 ->groupBy('p_place') 187 ->pluck(new Expression('COUNT(*)'), 'p_place'); 188 189 $totals = []; 190 191 foreach ($rows as $country => $count) { 192 $country_code = $this->country_to_iso3166[$country] ?? null; 193 194 if ($country_code !== null) { 195 $totals[$country_code] = $count + ($totals[$country_code] ?? 0); 196 } 197 } 198 199 return $totals; 200 } 201 202 /** 203 * @param Tree $tree 204 * @param string $fact 205 * 206 * @return array<int> 207 */ 208 private function countFamilyEventsByCountry(Tree $tree, string $fact): array 209 { 210 $query = DB::table('places') 211 ->where('p_file', '=', $tree->id()) 212 ->where('p_parent_id', '=', 0) 213 ->join('placelinks', static function (JoinClause $join): void { 214 $join 215 ->on('pl_file', '=', 'p_file') 216 ->on('pl_p_id', '=', 'p_id'); 217 }) 218 ->join('families', static function (JoinClause $join): void { 219 $join 220 ->on('pl_file', '=', 'f_file') 221 ->on('pl_gid', '=', 'f_id'); 222 }) 223 ->select(['p_place AS place', 'f_gedcom AS gedcom']); 224 225 return $this->filterEventPlaces($query, $fact); 226 } 227 228 /** 229 * @param Tree $tree 230 * @param string $fact 231 * 232 * @return array<int> 233 */ 234 private function countIndividualEventsByCountry(Tree $tree, string $fact): array 235 { 236 $query = DB::table('places') 237 ->where('p_file', '=', $tree->id()) 238 ->where('p_parent_id', '=', 0) 239 ->join('placelinks', static function (JoinClause $join): void { 240 $join 241 ->on('pl_file', '=', 'p_file') 242 ->on('pl_p_id', '=', 'p_id'); 243 }) 244 ->join('individuals', static function (JoinClause $join): void { 245 $join 246 ->on('pl_file', '=', 'i_file') 247 ->on('pl_gid', '=', 'i_id'); 248 }) 249 ->select(['p_place AS place', 'i_gedcom AS gedcom']); 250 251 return $this->filterEventPlaces($query, $fact); 252 } 253 254 /** 255 * @param Builder $query 256 * @param string $fact 257 * 258 * @return array<int> 259 */ 260 private function filterEventPlaces(Builder $query, string $fact): array 261 { 262 $totals = []; 263 264 foreach ($query->cursor() as $row) { 265 $country_code = $this->country_to_iso3166[$row->place] ?? null; 266 267 if ($country_code !== null) { 268 $place_regex = '/\n1 ' . $fact . '(?:\n[2-9].*)*\n2 PLAC.*[, ]' . preg_quote($row->place, '(?:\n|$)/i') . '\n/'; 269 270 if (preg_match($place_regex, $row->gedcom) === 1) { 271 $totals[$country_code] = 1 + ($totals[$country_code] ?? 0); 272 } 273 } 274 } 275 276 return $totals; 277 } 278 279 /** 280 * Create a chart showing where events occurred. 281 * 282 * @param string $chart_shows The type of chart map to show 283 * @param string $chart_type The type of chart to show 284 * @param string $surname The surname for surname based distribution chart 285 * 286 * @return string 287 */ 288 public function chartDistribution( 289 string $chart_shows = 'world', 290 string $chart_type = '', 291 string $surname = '' 292 ): string { 293 switch ($chart_type) { 294 case 'surname_distribution_chart': 295 $chart_title = I18N::translate('Surname distribution chart') . ': ' . $surname; 296 $surname = $surname ?: $this->individual_repository->getCommonSurname(); 297 $data = $this->createChartData($this->countSurnamesByCountry($this->tree, $surname)); 298 break; 299 300 case 'birth_distribution_chart': 301 $chart_title = I18N::translate('Birth by country'); 302 $data = $this->createChartData($this->countIndividualEventsByCountry($this->tree, 'BIRT')); 303 break; 304 305 case 'death_distribution_chart': 306 $chart_title = I18N::translate('Death by country'); 307 $data = $this->createChartData($this->countIndividualEventsByCountry($this->tree, 'DEAT')); 308 break; 309 310 case 'marriage_distribution_chart': 311 $chart_title = I18N::translate('Marriage by country'); 312 $data = $this->createChartData($this->countFamilyEventsByCountry($this->tree, 'MARR')); 313 break; 314 315 case 'indi_distribution_chart': 316 default: 317 $chart_title = I18N::translate('Individual distribution chart'); 318 $data = $this->createChartData($this->countIndividualsByCountry($this->tree)); 319 break; 320 } 321 322 return view('statistics/other/charts/geo', [ 323 'chart_title' => $chart_title, 324 'chart_color2' => '84beff', 325 'chart_color3' => 'c3dfff', 326 'region' => $chart_shows, 327 'data' => $data, 328 'language' => I18N::languageTag(), 329 ]); 330 } 331} 332