1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2020 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\Http\RequestHandlers; 21 22use Fisharebest\Webtrees\Http\ViewResponseTrait; 23use Fisharebest\Webtrees\I18N; 24use Fisharebest\Webtrees\Module\PlaceHierarchyListModule; 25use Fisharebest\Webtrees\Services\MapDataService; 26use Fisharebest\Webtrees\Services\ModuleService; 27use Fisharebest\Webtrees\Services\TreeService; 28use Illuminate\Database\Capsule\Manager as DB; 29use Illuminate\Database\Query\Expression; 30use Psr\Http\Message\ResponseInterface; 31use Psr\Http\Message\ServerRequestInterface; 32use Psr\Http\Server\RequestHandlerInterface; 33use stdClass; 34 35use function array_reverse; 36use function redirect; 37use function route; 38 39/** 40 * Show a list of map data. 41 */ 42class MapDataList implements RequestHandlerInterface 43{ 44 use ViewResponseTrait; 45 46 /** @var MapDataService */ 47 private $map_data_service; 48 49 /** @var ModuleService */ 50 private $module_service; 51 52 /** @var TreeService */ 53 private $tree_service; 54 55 /** 56 * Dependency injection. 57 * 58 * @param MapDataService $map_data_service 59 * @param ModuleService $module_service 60 * @param TreeService $tree_service 61 */ 62 public function __construct( 63 MapDataService $map_data_service, 64 ModuleService $module_service, 65 TreeService $tree_service 66 ) { 67 $this->map_data_service = $map_data_service; 68 $this->module_service = $module_service; 69 $this->tree_service = $tree_service; 70 } 71 72 /** 73 * @param ServerRequestInterface $request 74 * 75 * @return ResponseInterface 76 */ 77 public function handle(ServerRequestInterface $request): ResponseInterface 78 { 79 $parent_id = (int) ($request->getQueryParams()['parent_id'] ?? 0); 80 $title = I18N::translate('Geographic data'); 81 $parent = $this->map_data_service->findById($parent_id); 82 83 // Request for a non-existent location? 84 if ($parent_id !== $parent->id()) { 85 return redirect(route(__CLASS__)); 86 } 87 88 // Automatically import any new/missing places. 89 $this->map_data_service->importMissingLocations(); 90 91 $breadcrumbs = [$parent->locationName()]; 92 93 $tmp = $parent->parent(); 94 95 while ($tmp->id() !== 0) { 96 $breadcrumbs[route(__CLASS__, ['parent_id' => $tmp->id()])] = $tmp->locationName(); 97 98 $tmp = $tmp->parent(); 99 } 100 101 $breadcrumbs[route(__CLASS__)] = $title; 102 $breadcrumbs[route(ControlPanel::class)] = I18N::translate('Control panel'); 103 104 $list_module = $this->module_service 105 ->findByInterface(PlaceHierarchyListModule::class) 106 ->first(); 107 108 $this->layout = 'layouts/administration'; 109 110 return $this->viewResponse('admin/locations', [ 111 'active' => $this->map_data_service->activePlaces($parent), 112 'all_trees' => $this->tree_service->all(), 113 'breadcrumbs' => array_reverse($breadcrumbs), 114 'parent_id' => $parent_id, 115 'placelist' => $this->map_data_service->getPlaceListLocation($parent_id), 116 'list_module' => $list_module, 117 'title' => $title, 118 ]); 119 } 120 121 122 /** 123 * Find all of the places in the hierarchy 124 * 125 * @param int $id 126 * 127 * @return stdClass[] 128 */ 129 private function getPlaceListLocation(int $id): array 130 { 131 return DB::table('placelocation') 132 ->where('pl_parent_id', '=', $id) 133 ->orderBy(new Expression('pl_place /*! COLLATE ' . I18N::collation() . ' */')) 134 ->get() 135 ->map(function (stdClass $row): stdClass { 136 // Find/count places without co-ordinates 137 $children = $this->childLocationStatus((int) $row->pl_id); 138 139 $row->child_count = (int) $children->child_count; 140 $row->no_coord = (int) $children->no_coord; 141 142 return $row; 143 }) 144 ->all(); 145 } 146 147 /** 148 * How many children does place have? How many have co-ordinates? 149 * 150 * @param int $parent_id 151 * 152 * @return stdClass 153 */ 154 private function childLocationStatus(int $parent_id): stdClass 155 { 156 $prefix = DB::connection()->getTablePrefix(); 157 158 $expression = 159 $prefix . 'p0.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p0.pl_lati, '') = '' OR " . 160 $prefix . 'p1.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p1.pl_lati, '') = '' OR " . 161 $prefix . 'p2.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p2.pl_lati, '') = '' OR " . 162 $prefix . 'p3.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p3.pl_lati, '') = '' OR " . 163 $prefix . 'p4.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p4.pl_lati, '') = '' OR " . 164 $prefix . 'p5.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p5.pl_lati, '') = '' OR " . 165 $prefix . 'p6.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p6.pl_lati, '') = '' OR " . 166 $prefix . 'p7.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p7.pl_lati, '') = '' OR " . 167 $prefix . 'p8.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p8.pl_lati, '') = '' OR " . 168 $prefix . 'p9.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p9.pl_lati, '') = ''"; 169 170 return DB::table('placelocation AS p0') 171 ->leftJoin('placelocation AS p1', 'p1.pl_parent_id', '=', 'p0.pl_id') 172 ->leftJoin('placelocation AS p2', 'p2.pl_parent_id', '=', 'p1.pl_id') 173 ->leftJoin('placelocation AS p3', 'p3.pl_parent_id', '=', 'p2.pl_id') 174 ->leftJoin('placelocation AS p4', 'p4.pl_parent_id', '=', 'p3.pl_id') 175 ->leftJoin('placelocation AS p5', 'p5.pl_parent_id', '=', 'p4.pl_id') 176 ->leftJoin('placelocation AS p6', 'p6.pl_parent_id', '=', 'p5.pl_id') 177 ->leftJoin('placelocation AS p7', 'p7.pl_parent_id', '=', 'p6.pl_id') 178 ->leftJoin('placelocation AS p8', 'p8.pl_parent_id', '=', 'p7.pl_id') 179 ->leftJoin('placelocation AS p9', 'p9.pl_parent_id', '=', 'p8.pl_id') 180 ->where('p0.pl_parent_id', '=', $parent_id) 181 ->select([new Expression('COUNT(*) AS child_count'), new Expression('SUM(' . $expression . ') AS no_coord')]) 182 ->first(); 183 } 184} 185