MapDataService::deleteRecursively() - Code Metrics - Inspection of "Working on gedcom elements" - fisharebest/webtrees - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — develop ( 5f211d...930448 )

by Greg

created 2021-01-21 17:03 UTC

MapDataService::deleteRecursively() A

↳ Parent: MapDataService

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	3
nc	1
nop	1
dl	0
loc	6
rs	10
c	1
b	0
f	0

<?php

/**
 * webtrees: online genealogy
 * Copyright (C) 2021 webtrees development team
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

declare(strict_types=1);

namespace Fisharebest\Webtrees\Services;

use Fisharebest\Webtrees\Gedcom;
use Fisharebest\Webtrees\I18N;
use Fisharebest\Webtrees\PlaceLocation;
use Illuminate\Database\Capsule\Manager as DB;
use Illuminate\Database\Query\Expression;
use Illuminate\Support\Collection;
use stdClass;

use function abs;
use function array_filter;
use function array_unshift;
use function implode;
use function mb_strtolower;
use function round;

/**
 * Process geographic data.
 */
class MapDataService
{
    // Location of files to import
    public const PLACES_FOLDER = 'places/';

    // Format of CSV files
    public const CSV_SEPARATOR = ';';

    /**
     * @param int $id
     *
     * @return PlaceLocation
     */
    public function findById(int $id): PlaceLocation
    {
        $hierarchy = [];

        while (true) {
            $row = DB::table('place_location')
                ->where('id', '=', $id)
                ->select(['place', 'parent_id'])
                ->first();

            if ($row === null) {
                return new PlaceLocation(implode(Gedcom::PLACE_SEPARATOR, $hierarchy));
            }

            $hierarchy[] = $row->place;
            $id          = $row->parent_id;
        }
    }

    /**
     * Which trees use a particular location?
     *
     * @param PlaceLocation $location
     *
     * @return array<string,array<stdClass>>
     */
    public function activePlaces(PlaceLocation $location): array
    {
        $parents  = $this->placeIdsForLocation($location);
        $children = [];

        $rows = DB::table('places')
            ->join('gedcom', 'gedcom.gedcom_id', '=', 'p_file')
            ->join('gedcom_setting', 'gedcom_setting.gedcom_id', '=', 'gedcom.gedcom_id')
            ->where('setting_name', '=', 'title')
            ->whereIn('p_parent_id', $parents)
            ->select(['p_place', 'gedcom_name AS tree_name', 'setting_value AS tree_title', 'p_id'])
            ->get();

        foreach ($rows as $row) {
            $children[mb_strtolower($row->p_place)][] = $row;
        }

        return $children;
    }

    /**
     * Make sure that all places in the genealogy data also exist in the location data.
     *
     * @return void
     */
    public function importMissingLocations(): void
    {
        $all_places = DB::table('places AS p0')
            ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id')
            ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id')
            ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id')
            ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id')
            ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id')
            ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id')
            ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id')
            ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id')
            ->select([
                'p0.p_place AS part_0',
                'p1.p_place AS part_1',
                'p2.p_place AS part_2',
                'p3.p_place AS part_3',
                'p4.p_place AS part_4',
                'p5.p_place AS part_5',
                'p6.p_place AS part_6',
                'p7.p_place AS part_7',
                'p8.p_place AS part_8',
            ])
            ->get()
            ->map(static function (stdClass $row): string {
                return implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row));
            });

        $all_locations = DB::table('place_location AS p0')
            ->leftJoin('place_location AS p1', 'p1.id', '=', 'p0.parent_id')
            ->leftJoin('place_location AS p2', 'p2.id', '=', 'p1.parent_id')
            ->leftJoin('place_location AS p3', 'p3.id', '=', 'p2.parent_id')
            ->leftJoin('place_location AS p4', 'p4.id', '=', 'p3.parent_id')
            ->leftJoin('place_location AS p5', 'p5.id', '=', 'p4.parent_id')
            ->leftJoin('place_location AS p6', 'p6.id', '=', 'p5.parent_id')
            ->leftJoin('place_location AS p7', 'p7.id', '=', 'p6.parent_id')
            ->leftJoin('place_location AS p8', 'p8.id', '=', 'p7.parent_id')
            ->select([
                'p0.place AS part_0',
                'p1.place AS part_1',
                'p2.place AS part_2',
                'p3.place AS part_3',
                'p4.place AS part_4',
                'p5.place AS part_5',
                'p6.place AS part_6',
                'p7.place AS part_7',
                'p8.place AS part_8',
            ])
            ->get()
            ->map(static function (stdClass $row): string {
                return implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row));
            });

        $missing = $all_places->diff($all_locations);

        foreach ($missing as $location) {
            (new PlaceLocation($location))->id();
        }
    }

    /**
     * Find all active places that match a location
     *
     * @param PlaceLocation $location
     *
     * @return array<string>
     */
    private function placeIdsForLocation(PlaceLocation $location): array
    {
        $hierarchy = [];

        while ($location->id() !== null) {
            array_unshift($hierarchy, $location->locationName());
            $location = $location->parent();
        }

        $place_ids = ['0'];

        foreach ($hierarchy as $place_name) {
            $place_ids = DB::table('places')
                ->whereIn('p_parent_id', $place_ids)
                ->where('p_place', '=', $place_name)
                ->groupBy(['p_id'])
                ->pluck('p_id')
                ->all();
        }

        return $place_ids;
    }

    /**
     * @param int $id
     */
    public function deleteRecursively(int $id): void
    {
        // Uses on-delete-cascade
        DB::table('place_location')
            ->where('id', '=', $id)
            ->delete();
    }

    /**
     * Find a list of child places.
     * How many children does each child place have?  How many have co-ordinates?
     *
     * @param int|null $parent_id
     *
     * @return Collection<object>
     */
    public function getPlaceListLocation(?int $parent_id): Collection
    {
        $prefix = DB::connection()->getTablePrefix();

        $expression =
            $prefix . 'p1.place IS NOT NULL AND ' . $prefix . 'p1.latitude IS NULL OR ' .
            $prefix . 'p2.place IS NOT NULL AND ' . $prefix . 'p2.latitude IS NULL OR ' .
            $prefix . 'p3.place IS NOT NULL AND ' . $prefix . 'p3.latitude IS NULL OR ' .
            $prefix . 'p4.place IS NOT NULL AND ' . $prefix . 'p4.latitude IS NULL OR ' .
            $prefix . 'p5.place IS NOT NULL AND ' . $prefix . 'p5.latitude IS NULL OR ' .
            $prefix . 'p6.place IS NOT NULL AND ' . $prefix . 'p6.latitude IS NULL OR ' .
            $prefix . 'p7.place IS NOT NULL AND ' . $prefix . 'p7.latitude IS NULL OR ' .
            $prefix . 'p8.place IS NOT NULL AND ' . $prefix . 'p8.latitude IS NULL OR ' .
            $prefix . 'p9.place IS NOT NULL AND ' . $prefix . 'p9.latitude IS NULL';

        $expression = 'CASE ' . $expression . ' WHEN TRUE THEN 1 ELSE 0 END';

        $query = DB::table('place_location AS p0')
            ->leftJoin('place_location AS p1', 'p1.parent_id', '=', 'p0.id')
            ->leftJoin('place_location AS p2', 'p2.parent_id', '=', 'p1.id')
            ->leftJoin('place_location AS p3', 'p3.parent_id', '=', 'p2.id')
            ->leftJoin('place_location AS p4', 'p4.parent_id', '=', 'p3.id')
            ->leftJoin('place_location AS p5', 'p5.parent_id', '=', 'p4.id')
            ->leftJoin('place_location AS p6', 'p6.parent_id', '=', 'p5.id')
            ->leftJoin('place_location AS p7', 'p7.parent_id', '=', 'p6.id')
            ->leftJoin('place_location AS p8', 'p8.parent_id', '=', 'p7.id')
            ->leftJoin('place_location AS p9', 'p9.parent_id', '=', 'p8.id');

        if ($parent_id === null) {
            $query->whereNull('p0.parent_id');
        } else {
            $query->where('p0.parent_id', '=', $parent_id);
        }

        return $query
            ->groupBy(['p0.id'])
            ->orderBy(new Expression($prefix . 'p0.place /*! COLLATE ' . I18N::collation() . ' */'))
            ->select([
                'p0.*',
                new Expression('COUNT(' . $prefix . 'p1.id) AS child_count'),
                new Expression('SUM(' . $expression . ') AS no_coord'),
            ])
            ->get()
            ->map(static function (stdClass $row): stdClass {
                $row->child_count = (int) $row->child_count;
                $row->no_coord    = (int) $row->no_coord;
                $row->key         = mb_strtolower($row->place);

                return $row;
            });
    }

    /**
     * @param float $latitude
     *
     * @return string
     */
    public function writeLatitude(float $latitude): string
    {
        return $this->writeDegrees($latitude, Gedcom::LATITUDE_NORTH, Gedcom::LATITUDE_SOUTH);
    }

    /**
     * @param float $longitude
     *
     * @return string
     */
    public function writeLongitude(float $longitude): string
    {
        return $this->writeDegrees($longitude, Gedcom::LONGITUDE_EAST, Gedcom::LONGITUDE_WEST);
    }

    /**
     * @param float  $degrees
     * @param string $positive
     * @param string $negative
     *
     * @return string
     */
    private function writeDegrees(float $degrees, string $positive, string $negative): string
    {
        $degrees = round($degrees, 5);

        if ($degrees < 0.0) {
            return $negative . abs($degrees);
        }

        return $positive . $degrees;
    }
}


1			<?php
2
3			/**
4			* webtrees: online genealogy
5			* Copyright (C) 2021 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
18			declare(strict_types=1);
19
20			namespace Fisharebest\Webtrees\Services;
21
22			use Fisharebest\Webtrees\Gedcom;
23			use Fisharebest\Webtrees\I18N;
24			use Fisharebest\Webtrees\PlaceLocation;
25			use Illuminate\Database\Capsule\Manager as DB;
26			use Illuminate\Database\Query\Expression;
27			use Illuminate\Support\Collection;
28			use stdClass;
29
30			use function abs;
31			use function array_filter;
32			use function array_unshift;
33			use function implode;
34			use function mb_strtolower;
35			use function round;
36
37			/**
38			* Process geographic data.
39			*/
40			class MapDataService
41			{
42			// Location of files to import
43			public const PLACES_FOLDER = 'places/';
44
45			// Format of CSV files
46			public const CSV_SEPARATOR = ';';
47
48			/**
49			* @param int $id
50			*
51			* @return PlaceLocation
52			*/
53			public function findById(int $id): PlaceLocation
54			{
55			$hierarchy = [];
56
57			while (true) {
58			$row = DB::table('place_location')
59			->where('id', '=', $id)
60			->select(['place', 'parent_id'])
61			->first();
62
63			if ($row === null) {
64			return new PlaceLocation(implode(Gedcom::PLACE_SEPARATOR, $hierarchy));
65			}
66
67			$hierarchy[] = $row->place;
68			$id = $row->parent_id;
69			}
70			}
71
72			/**
73			* Which trees use a particular location?
74			*
75			* @param PlaceLocation $location
76			*
77			* @return array<string,array<stdClass>>
78			*/
79			public function activePlaces(PlaceLocation $location): array
80			{
81			$parents = $this->placeIdsForLocation($location);
82			$children = [];
83
84			$rows = DB::table('places')
85			->join('gedcom', 'gedcom.gedcom_id', '=', 'p_file')
86			->join('gedcom_setting', 'gedcom_setting.gedcom_id', '=', 'gedcom.gedcom_id')
87			->where('setting_name', '=', 'title')
88			->whereIn('p_parent_id', $parents)
89			->select(['p_place', 'gedcom_name AS tree_name', 'setting_value AS tree_title', 'p_id'])
90			->get();
91
92			foreach ($rows as $row) {
93			$children[mb_strtolower($row->p_place)][] = $row;
94			}
95
96			return $children;
97			}
98
99			/**
100			* Make sure that all places in the genealogy data also exist in the location data.
101			*
102			* @return void
103			*/
104			public function importMissingLocations(): void
105			{
106			$all_places = DB::table('places AS p0')
107			->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id')
108			->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id')
109			->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id')
110			->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id')
111			->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id')
112			->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id')
113			->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id')
114			->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id')
115			->select([
116			'p0.p_place AS part_0',
117			'p1.p_place AS part_1',
118			'p2.p_place AS part_2',
119			'p3.p_place AS part_3',
120			'p4.p_place AS part_4',
121			'p5.p_place AS part_5',
122			'p6.p_place AS part_6',
123			'p7.p_place AS part_7',
124			'p8.p_place AS part_8',
125			])
126			->get()
127			->map(static function (stdClass $row): string {
128			return implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row));
129			});
130
131			$all_locations = DB::table('place_location AS p0')
132			->leftJoin('place_location AS p1', 'p1.id', '=', 'p0.parent_id')
133			->leftJoin('place_location AS p2', 'p2.id', '=', 'p1.parent_id')
134			->leftJoin('place_location AS p3', 'p3.id', '=', 'p2.parent_id')
135			->leftJoin('place_location AS p4', 'p4.id', '=', 'p3.parent_id')
136			->leftJoin('place_location AS p5', 'p5.id', '=', 'p4.parent_id')
137			->leftJoin('place_location AS p6', 'p6.id', '=', 'p5.parent_id')
138			->leftJoin('place_location AS p7', 'p7.id', '=', 'p6.parent_id')
139			->leftJoin('place_location AS p8', 'p8.id', '=', 'p7.parent_id')
140			->select([
141			'p0.place AS part_0',
142			'p1.place AS part_1',
143			'p2.place AS part_2',
144			'p3.place AS part_3',
145			'p4.place AS part_4',
146			'p5.place AS part_5',
147			'p6.place AS part_6',
148			'p7.place AS part_7',
149			'p8.place AS part_8',
150			])
151			->get()
152			->map(static function (stdClass $row): string {
153			return implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row));
154			});
155
156			$missing = $all_places->diff($all_locations);
157
158			foreach ($missing as $location) {
159			(new PlaceLocation($location))->id();
160			}
161			}
162
163			/**
164			* Find all active places that match a location
165			*
166			* @param PlaceLocation $location
167			*
168			* @return array<string>
169			*/
170			private function placeIdsForLocation(PlaceLocation $location): array
171			{
172			$hierarchy = [];
173
174			while ($location->id() !== null) {
175			array_unshift($hierarchy, $location->locationName());
176			$location = $location->parent();
177			}
178
179			$place_ids = ['0'];
180
181			foreach ($hierarchy as $place_name) {
182			$place_ids = DB::table('places')
183			->whereIn('p_parent_id', $place_ids)
184			->where('p_place', '=', $place_name)
185			->groupBy(['p_id'])
186			->pluck('p_id')
187			->all();
188			}
189
190			return $place_ids;
191			}
192
193			/**
194			* @param int $id
195			*/
196			public function deleteRecursively(int $id): void
197			{
198			// Uses on-delete-cascade
199			DB::table('place_location')
200			->where('id', '=', $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\|null $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.place IS NOT NULL AND ' . $prefix . 'p1.latitude IS NULL OR ' .
218			$prefix . 'p2.place IS NOT NULL AND ' . $prefix . 'p2.latitude IS NULL OR ' .
219			$prefix . 'p3.place IS NOT NULL AND ' . $prefix . 'p3.latitude IS NULL OR ' .
220			$prefix . 'p4.place IS NOT NULL AND ' . $prefix . 'p4.latitude IS NULL OR ' .
221			$prefix . 'p5.place IS NOT NULL AND ' . $prefix . 'p5.latitude IS NULL OR ' .
222			$prefix . 'p6.place IS NOT NULL AND ' . $prefix . 'p6.latitude IS NULL OR ' .
223			$prefix . 'p7.place IS NOT NULL AND ' . $prefix . 'p7.latitude IS NULL OR ' .
224			$prefix . 'p8.place IS NOT NULL AND ' . $prefix . 'p8.latitude IS NULL OR ' .
225			$prefix . 'p9.place IS NOT NULL AND ' . $prefix . 'p9.latitude IS NULL';
226
227			$expression = 'CASE ' . $expression . ' WHEN TRUE THEN 1 ELSE 0 END';
228
229			$query = DB::table('place_location AS p0')
230			->leftJoin('place_location AS p1', 'p1.parent_id', '=', 'p0.id')
231			->leftJoin('place_location AS p2', 'p2.parent_id', '=', 'p1.id')
232			->leftJoin('place_location AS p3', 'p3.parent_id', '=', 'p2.id')
233			->leftJoin('place_location AS p4', 'p4.parent_id', '=', 'p3.id')
234			->leftJoin('place_location AS p5', 'p5.parent_id', '=', 'p4.id')
235			->leftJoin('place_location AS p6', 'p6.parent_id', '=', 'p5.id')
236			->leftJoin('place_location AS p7', 'p7.parent_id', '=', 'p6.id')
237			->leftJoin('place_location AS p8', 'p8.parent_id', '=', 'p7.id')
238			->leftJoin('place_location AS p9', 'p9.parent_id', '=', 'p8.id');
239
240			if ($parent_id === null) {
241			$query->whereNull('p0.parent_id');
242			} else {
243			$query->where('p0.parent_id', '=', $parent_id);
244			}
245
246			return $query
247			->groupBy(['p0.id'])
248			->orderBy(new Expression($prefix . 'p0.place /! COLLATE ' . I18N::collation() . ' /'))
249			->select([
250			'p0.*',
251			new Expression('COUNT(' . $prefix . 'p1.id) AS child_count'),
252			new Expression('SUM(' . $expression . ') AS no_coord'),
253			])
254			->get()
255			->map(static function (stdClass $row): stdClass {
256			$row->child_count = (int) $row->child_count;
257			$row->no_coord = (int) $row->no_coord;
258			$row->key = mb_strtolower($row->place);
259
260			return $row;
261			});
262			}
263
264			/**
265			* @param float $latitude
266			*
267			* @return string
268			*/
269			public function writeLatitude(float $latitude): string
270			{
271			return $this->writeDegrees($latitude, Gedcom::LATITUDE_NORTH, Gedcom::LATITUDE_SOUTH);
272			}
273
274			/**
275			* @param float $longitude
276			*
277			* @return string
278			*/
279			public function writeLongitude(float $longitude): string
280			{
281			return $this->writeDegrees($longitude, Gedcom::LONGITUDE_EAST, Gedcom::LONGITUDE_WEST);
282			}
283
284			/**
285			* @param float $degrees
286			* @param string $positive
287			* @param string $negative
288			*
289			* @return string
290			*/
291			private function writeDegrees(float $degrees, string $positive, string $negative): string
292			{
293			$degrees = round($degrees, 5);
294
295			if ($degrees < 0.0) {
296			return $negative . abs($degrees);
297			}
298
299			return $positive . $degrees;
300			}
301			}
302

fisharebest / webtrees

Push — develop ( 5f211d...930448 )

MapDataService::deleteRecursively() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like