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