1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2019 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 <http://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Services; 21 22use Fisharebest\Webtrees\Gedcom; 23use Fisharebest\Webtrees\I18N; 24use Fisharebest\Webtrees\PlaceLocation; 25use Illuminate\Database\Capsule\Manager as DB; 26use Illuminate\Database\Query\Expression; 27use Illuminate\Support\Collection; 28use stdClass; 29 30use function array_unshift; 31use function implode; 32 33/** 34 * Process geographic data. 35 */ 36class MapDataService 37{ 38 /** 39 * @param int $id 40 * 41 * @return PlaceLocation 42 */ 43 public function findById(int $id): PlaceLocation 44 { 45 $hierarchy = []; 46 47 while ($id !== 0) { 48 $row = DB::table('placelocation') 49 ->where('pl_id', '=', $id) 50 ->select(['pl_place', 'pl_parent_id']) 51 ->first(); 52 53 if ($row === null) { 54 $id = 0; 55 } else { 56 $hierarchy[] = $row->pl_place; 57 $id = (int) $row->pl_parent_id; 58 } 59 } 60 61 return new PlaceLocation(implode(Gedcom::PLACE_SEPARATOR, $hierarchy)); 62 } 63 64 /** 65 * Which trees use a particular location? 66 * 67 * @param PlaceLocation $location 68 * 69 * @return array<string,array<stdClass>> 70 */ 71 public function activePlaces(PlaceLocation $location): array 72 { 73 $parents = $this->placeIdsForLocation($location); 74 $children = []; 75 76 $rows = DB::table('places') 77 ->join('gedcom', 'gedcom.gedcom_id', '=', 'p_file') 78 ->join('gedcom_setting', 'gedcom_setting.gedcom_id', '=', 'gedcom.gedcom_id') 79 ->where('setting_name', '=', 'title') 80 ->whereIn('p_parent_id', $parents) 81 ->select(['p_place', 'gedcom_name AS tree_name', 'setting_value AS tree_title', 'p_id']) 82 ->get(); 83 84 foreach ($rows as $row) { 85 $children[$row->p_place][] = $row; 86 } 87 88 return $children; 89 } 90 91 /** 92 * Make sure that all places in the genealogy data also exist in the location data. 93 * 94 * @return void 95 */ 96 public function importMissingLocations(): void 97 { 98 $all_places = DB::table('places AS p0') 99 ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 100 ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 101 ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 102 ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 103 ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 104 ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 105 ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 106 ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 107 ->select([ 108 'p0.p_place AS part_0', 109 'p1.p_place AS part_1', 110 'p2.p_place AS part_2', 111 'p3.p_place AS part_3', 112 'p4.p_place AS part_4', 113 'p5.p_place AS part_5', 114 'p6.p_place AS part_6', 115 'p7.p_place AS part_7', 116 'p8.p_place AS part_8', 117 ]) 118 ->get() 119 ->map(static function (stdClass $row): string { 120 return implode(Gedcom::PLACE_SEPARATOR, (array) $row); 121 }); 122 123 $all_locations = DB::table('placelocation AS p0') 124 ->leftJoin('placelocation AS p1', 'p1.pl_id', '=', 'p0.pl_parent_id') 125 ->leftJoin('placelocation AS p2', 'p2.pl_id', '=', 'p1.pl_parent_id') 126 ->leftJoin('placelocation AS p3', 'p3.pl_id', '=', 'p2.pl_parent_id') 127 ->leftJoin('placelocation AS p4', 'p4.pl_id', '=', 'p3.pl_parent_id') 128 ->leftJoin('placelocation AS p5', 'p5.pl_id', '=', 'p4.pl_parent_id') 129 ->leftJoin('placelocation AS p6', 'p6.pl_id', '=', 'p5.pl_parent_id') 130 ->leftJoin('placelocation AS p7', 'p7.pl_id', '=', 'p6.pl_parent_id') 131 ->leftJoin('placelocation AS p8', 'p8.pl_id', '=', 'p7.pl_parent_id') 132 ->select([ 133 'p0.pl_place AS part_0', 134 'p1.pl_place AS part_1', 135 'p2.pl_place AS part_2', 136 'p3.pl_place AS part_3', 137 'p4.pl_place AS part_4', 138 'p5.pl_place AS part_5', 139 'p6.pl_place AS part_6', 140 'p7.pl_place AS part_7', 141 'p8.pl_place AS part_8', 142 ]) 143 ->get() 144 ->map(static function (stdClass $row): string { 145 return implode(Gedcom::PLACE_SEPARATOR, (array) $row); 146 }); 147 148 $missing = $all_places->diff($all_locations); 149 150 151 foreach ($missing as $location) { 152 (new PlaceLocation($location))->id(); 153 } 154 } 155 156 /** 157 * Find all active places that match a location 158 * 159 * @param PlaceLocation $location 160 * 161 * @return array<string> 162 */ 163 private function placeIdsForLocation(PlaceLocation $location): array 164 { 165 $hierarchy = []; 166 167 while ($location->id() !== 0) { 168 array_unshift($hierarchy, $location->locationName()); 169 $location = $location->parent(); 170 } 171 172 $place_ids = ['0']; 173 174 foreach ($hierarchy as $place_name) { 175 $place_ids = DB::table('places') 176 ->whereIn('p_parent_id', $place_ids) 177 ->where('p_place', '=', $place_name) 178 ->groupBy(['p_id']) 179 ->pluck('p_id') 180 ->all(); 181 } 182 183 return $place_ids; 184 } 185 186 /** 187 * @param int $location_id 188 */ 189 public function deleteRecursively(int $location_id): void 190 { 191 $child_ids = DB::table('placelocation') 192 ->where('pl_parent_id', '=', $location_id) 193 ->pluck('pl_id'); 194 195 foreach ($child_ids as $child_id) { 196 $this->deleteRecursively((int) $child_id); 197 } 198 199 DB::table('placelocation') 200 ->where('pl_id', '=', $location_id) 201 ->delete(); 202 } 203 204 /** 205 * Find a list of child places. 206 * How many children does each child place have? How many have co-ordinates? 207 * 208 * @param int $parent_id 209 * 210 * @return Collection<object> 211 */ 212 public function getPlaceListLocation(int $parent_id): Collection 213 { 214 $prefix = DB::connection()->getTablePrefix(); 215 216 $expression = 217 $prefix . 'p1.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p1.pl_lati, '') = '' OR " . 218 $prefix . 'p2.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p2.pl_lati, '') = '' OR " . 219 $prefix . 'p3.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p3.pl_lati, '') = '' OR " . 220 $prefix . 'p4.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p4.pl_lati, '') = '' OR " . 221 $prefix . 'p5.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p5.pl_lati, '') = '' OR " . 222 $prefix . 'p6.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p6.pl_lati, '') = '' OR " . 223 $prefix . 'p7.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p7.pl_lati, '') = '' OR " . 224 $prefix . 'p8.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p8.pl_lati, '') = '' OR " . 225 $prefix . 'p9.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p9.pl_lati, '') = ''"; 226 227 $expression = 'CASE ' . $expression . ' WHEN TRUE THEN 1 ELSE 0 END'; 228 229 return DB::table('placelocation AS p0') 230 ->leftJoin('placelocation AS p1', 'p1.pl_parent_id', '=', 'p0.pl_id') 231 ->leftJoin('placelocation AS p2', 'p2.pl_parent_id', '=', 'p1.pl_id') 232 ->leftJoin('placelocation AS p3', 'p3.pl_parent_id', '=', 'p2.pl_id') 233 ->leftJoin('placelocation AS p4', 'p4.pl_parent_id', '=', 'p3.pl_id') 234 ->leftJoin('placelocation AS p5', 'p5.pl_parent_id', '=', 'p4.pl_id') 235 ->leftJoin('placelocation AS p6', 'p6.pl_parent_id', '=', 'p5.pl_id') 236 ->leftJoin('placelocation AS p7', 'p7.pl_parent_id', '=', 'p6.pl_id') 237 ->leftJoin('placelocation AS p8', 'p8.pl_parent_id', '=', 'p7.pl_id') 238 ->leftJoin('placelocation AS p9', 'p9.pl_parent_id', '=', 'p8.pl_id') 239 ->where('p0.pl_parent_id', '=', $parent_id) 240 ->groupBy(['p0.pl_id']) 241 ->orderBy(new Expression($prefix . 'p0.pl_place /*! COLLATE ' . I18N::collation() . ' */')) 242 ->select([ 243 'p0.*', 244 new Expression('COUNT(' . $prefix . 'p1.pl_id) AS child_count'), 245 new Expression('SUM(' . $expression . ') AS no_coord'), 246 ]) 247 ->get() 248 ->map(static function (stdClass $row): stdClass { 249 $row->child_count = (int) $row->child_count; 250 $row->no_coord = (int) $row->no_coord; 251 252 return $row; 253 }); 254 } 255} 256