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 $breadcrumbs = []; 66 $parent = $this->map_data_service->findById($parent_id); 67 68 // Request for a non-existent location? 69 if ($parent_id !== $parent->id()) { 70 return redirect(route(__CLASS__)); 71 } 72 73 // Automatically import any new/missing places. 74 $this->map_data_service->importMissingChildren($parent); 75 76 $tmp = clone $parent; 77 78 while ($tmp->id() !== 0) { 79 $breadcrumbs[route(__CLASS__, ['parent_id' => $tmp->id()])] = $tmp->locationName(); 80 81 $tmp = $tmp->parent(); 82 } 83 84 $breadcrumbs[route(__CLASS__)] = $title; 85 $breadcrumbs[route(ControlPanel::class)] = I18N::translate('Control panel'); 86 87 $this->layout = 'layouts/administration'; 88 89 return $this->viewResponse('admin/locations', [ 90 'title' => $title, 91 'active' => $this->map_data_service->activePlaces($parent), 92 'breadcrumbs' => array_reverse($breadcrumbs), 93 'parent_id' => $parent_id, 94 'placelist' => $this->getPlaceListLocation($parent_id), 95 ]); 96 } 97 98 99 /** 100 * Find all of the places in the hierarchy 101 * 102 * @param int $id 103 * 104 * @return stdClass[] 105 */ 106 private function getPlaceListLocation(int $id): array 107 { 108 return DB::table('placelocation') 109 ->where('pl_parent_id', '=', $id) 110 ->orderBy(new Expression('pl_place /*! COLLATE ' . I18N::collation() . ' */')) 111 ->get() 112 ->map(function (stdClass $row): stdClass { 113 // Find/count places without co-ordinates 114 $children = $this->childLocationStatus((int) $row->pl_id); 115 116 $row->child_count = (int) $children->child_count; 117 $row->no_coord = (int) $children->no_coord; 118 119 return $row; 120 }) 121 ->all(); 122 } 123 124 /** 125 * How many children does place have? How many have co-ordinates? 126 * 127 * @param int $parent_id 128 * 129 * @return stdClass 130 */ 131 private function childLocationStatus(int $parent_id): stdClass 132 { 133 $prefix = DB::connection()->getTablePrefix(); 134 135 $expression = 136 $prefix . 'p0.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p0.pl_lati, '') = '' OR " . 137 $prefix . 'p1.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p1.pl_lati, '') = '' OR " . 138 $prefix . 'p2.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p2.pl_lati, '') = '' OR " . 139 $prefix . 'p3.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p3.pl_lati, '') = '' OR " . 140 $prefix . 'p4.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p4.pl_lati, '') = '' OR " . 141 $prefix . 'p5.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p5.pl_lati, '') = '' OR " . 142 $prefix . 'p6.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p6.pl_lati, '') = '' OR " . 143 $prefix . 'p7.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p7.pl_lati, '') = '' OR " . 144 $prefix . 'p8.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p8.pl_lati, '') = '' OR " . 145 $prefix . 'p9.pl_place IS NOT NULL AND COALESCE(' . $prefix . "p9.pl_lati, '') = ''"; 146 147 return DB::table('placelocation AS p0') 148 ->leftJoin('placelocation AS p1', 'p1.pl_parent_id', '=', 'p0.pl_id') 149 ->leftJoin('placelocation AS p2', 'p2.pl_parent_id', '=', 'p1.pl_id') 150 ->leftJoin('placelocation AS p3', 'p3.pl_parent_id', '=', 'p2.pl_id') 151 ->leftJoin('placelocation AS p4', 'p4.pl_parent_id', '=', 'p3.pl_id') 152 ->leftJoin('placelocation AS p5', 'p5.pl_parent_id', '=', 'p4.pl_id') 153 ->leftJoin('placelocation AS p6', 'p6.pl_parent_id', '=', 'p5.pl_id') 154 ->leftJoin('placelocation AS p7', 'p7.pl_parent_id', '=', 'p6.pl_id') 155 ->leftJoin('placelocation AS p8', 'p8.pl_parent_id', '=', 'p7.pl_id') 156 ->leftJoin('placelocation AS p9', 'p9.pl_parent_id', '=', 'p8.pl_id') 157 ->where('p0.pl_parent_id', '=', $parent_id) 158 ->select([new Expression('COUNT(*) AS child_count'), new Expression('SUM(' . $expression . ') AS no_coord')]) 159 ->first(); 160 } 161} 162