xref: /webtrees/app/Statistics/Repository/PlaceRepository.php (revision 43f2f523bcb6d4090564d23802872c0679ede6bc) 
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 <https://www.gnu.org/licenses/>.
16 */
17
18declare(strict_types=1);
19
20namespace Fisharebest\Webtrees\Statistics\Repository;
21
22use Fisharebest\Webtrees\Family;
23use Fisharebest\Webtrees\Gedcom;
24use Fisharebest\Webtrees\I18N;
25use Fisharebest\Webtrees\Individual;
26use Fisharebest\Webtrees\Location;
27use Fisharebest\Webtrees\Place;
28use Fisharebest\Webtrees\Statistics\Google\ChartDistribution;
29use Fisharebest\Webtrees\Statistics\Repository\Interfaces\IndividualRepositoryInterface;
30use Fisharebest\Webtrees\Statistics\Repository\Interfaces\PlaceRepositoryInterface;
31use Fisharebest\Webtrees\Statistics\Service\CountryService;
32use Fisharebest\Webtrees\Tree;
33use Illuminate\Database\Capsule\Manager as DB;
34use Illuminate\Database\Query\JoinClause;
35
36use function array_key_exists;
37use function arsort;
38use function end;
39use function explode;
40use function preg_match;
41use function trim;
42use function view;
43
44/**
45 * A repository providing methods for place related statistics.
46 */
47class PlaceRepository implements PlaceRepositoryInterface
48{
49    private Tree $tree;
50
51    private CountryService $country_service;
52
53    private IndividualRepositoryInterface $individual_repository;
54
55    /**
56     * @param Tree                          $tree
57     * @param CountryService                $country_service
58     * @param IndividualRepositoryInterface $individual_repository
59     */
60    public function __construct(
61        Tree $tree,
62        CountryService $country_service,
63        IndividualRepositoryInterface $individual_repository
64    ) {
65        $this->tree                  = $tree;
66        $this->country_service       = $country_service;
67        $this->individual_repository = $individual_repository;
68    }
69
70    /**
71     * Places
72     *
73     * @param string $fact
74     * @param string $what
75     * @param bool   $country
76     *
77     * @return array<int|string,int>
78     */
79    private function queryFactPlaces(string $fact, string $what = 'ALL', bool $country = false): array
80    {
81        $rows = [];
82
83        if ($what === 'INDI') {
84            $rows = DB::table('individuals')
85                ->select(['i_gedcom as tree'])
86                ->where('i_file', '=', $this->tree->id())
87                ->where('i_gedcom', 'LIKE', "%\n2 PLAC %")
88                ->get()
89                ->all();
90        } elseif ($what === 'FAM') {
91            $rows = DB::table('families')->select(['f_gedcom as tree'])
92                ->where('f_file', '=', $this->tree->id())
93                ->where('f_gedcom', 'LIKE', "%\n2 PLAC %")
94                ->get()
95                ->all();
96        }
97
98        $placelist = [];
99
100        foreach ($rows as $row) {
101            if (preg_match('/\n1 ' . $fact . '(?:\n[2-9].*)*\n2 PLAC (.+)/', $row->tree, $match)) {
102                if ($country) {
103                    $tmp   = explode(Gedcom::PLACE_SEPARATOR, $match[1]);
104                    $place = end($tmp);
105                } else {
106                    $place = $match[1];
107                }
108
109                $placelist[$place] = ($placelist[$place] ?? 0) + 1;
110            }
111        }
112
113        return $placelist;
114    }
115
116    /**
117     * Query places.
118     *
119     * @param string $what
120     * @param string $fact
121     * @param int    $parent
122     * @param bool   $country
123     *
124     * @return array<int|object>
125     */
126    public function statsPlaces(string $what = 'ALL', string $fact = '', int $parent = 0, bool $country = false): array
127    {
128        if ($fact) {
129            return $this->queryFactPlaces($fact, $what, $country);
130        }
131
132        $query = DB::table('places')
133            ->join('placelinks', static function (JoinClause $join): void {
134                $join->on('pl_file', '=', 'p_file')
135                    ->on('pl_p_id', '=', 'p_id');
136            })
137            ->where('p_file', '=', $this->tree->id());
138
139        if ($parent > 0) {
140            // Used by placehierarchy map modules
141            $query->select(['p_place AS place'])
142                ->selectRaw('COUNT(*) AS tot')
143                ->where('p_id', '=', $parent)
144                ->groupBy(['place']);
145        } else {
146            $query->select(['p_place AS country'])
147                ->selectRaw('COUNT(*) AS tot')
148                ->where('p_parent_id', '=', 0)
149                ->groupBy(['country'])
150                ->orderByDesc('tot')
151                ->orderBy('country');
152        }
153
154        if ($what === Individual::RECORD_TYPE) {
155            $query->join('individuals', static function (JoinClause $join): void {
156                $join->on('pl_file', '=', 'i_file')
157                    ->on('pl_gid', '=', 'i_id');
158            });
159        } elseif ($what === Family::RECORD_TYPE) {
160            $query->join('families', static function (JoinClause $join): void {
161                $join->on('pl_file', '=', 'f_file')
162                    ->on('pl_gid', '=', 'f_id');
163            });
164        } elseif ($what === Location::RECORD_TYPE) {
165            $query->join('other', static function (JoinClause $join): void {
166                $join->on('pl_file', '=', 'o_file')
167                    ->on('pl_gid', '=', 'o_id');
168            })
169                ->where('o_type', '=', Location::RECORD_TYPE);
170        }
171
172        return $query
173            ->get()
174            ->map(static function (object $entry) {
175                // Map total value to integer
176                $entry->tot = (int) $entry->tot;
177
178                return $entry;
179            })
180            ->all();
181    }
182
183    /**
184     * Get the top 10 places list.
185     *
186     * @param array<int> $places
187     *
188     * @return array<array<string,int|Place>>
189     */
190    private function getTop10Places(array $places): array
191    {
192        $top10 = [];
193        $i     = 0;
194
195        arsort($places);
196
197        foreach ($places as $place => $count) {
198            $tmp     = new Place((string) $place, $this->tree);
199            $top10[] = [
200                'place' => $tmp,
201                'count' => $count,
202            ];
203
204            ++$i;
205
206            if ($i === 10) {
207                break;
208            }
209        }
210
211        return $top10;
212    }
213
214    /**
215     * Renders the top 10 places list.
216     *
217     * @param array<int|string,int> $places
218     *
219     * @return string
220     */
221    private function renderTop10(array $places): string
222    {
223        $top10Records = $this->getTop10Places($places);
224
225        return view(
226            'statistics/other/top10-list',
227            [
228                'records' => $top10Records,
229            ]
230        );
231    }
232
233    /**
234     * A list of common birth places.
235     *
236     * @return string
237     */
238    public function commonBirthPlacesList(): string
239    {
240        $places = $this->queryFactPlaces('BIRT', 'INDI');
241        return $this->renderTop10($places);
242    }
243
244    /**
245     * A list of common death places.
246     *
247     * @return string
248     */
249    public function commonDeathPlacesList(): string
250    {
251        $places = $this->queryFactPlaces('DEAT', 'INDI');
252        return $this->renderTop10($places);
253    }
254
255    /**
256     * A list of common marriage places.
257     *
258     * @return string
259     */
260    public function commonMarriagePlacesList(): string
261    {
262        $places = $this->queryFactPlaces('MARR', 'FAM');
263        return $this->renderTop10($places);
264    }
265
266    /**
267     * A list of common countries.
268     *
269     * @return string
270     */
271    public function commonCountriesList(): string
272    {
273        $countries = $this->statsPlaces();
274
275        if ($countries === []) {
276            return '';
277        }
278
279        $top10 = [];
280        $i     = 1;
281
282        // Get the country names for each language
283        $country_names = [];
284        $old_language = I18N::languageTag();
285
286        foreach (I18N::activeLocales() as $locale) {
287            I18N::init($locale->languageTag());
288            $all_countries = $this->country_service->getAllCountries();
289            foreach ($all_countries as $country_code => $country_name) {
290                $country_names[$country_name] = $country_code;
291            }
292        }
293
294        I18N::init($old_language);
295
296        $all_db_countries = [];
297        foreach ($countries as $place) {
298            $country = trim($place->country);
299            if (array_key_exists($country, $country_names)) {
300                if (isset($all_db_countries[$country_names[$country]][$country])) {
301                    $all_db_countries[$country_names[$country]][$country] += (int) $place->tot;
302                } else {
303                    $all_db_countries[$country_names[$country]][$country] = (int) $place->tot;
304                }
305            }
306        }
307
308        // get all the user’s countries names
309        $all_countries = $this->country_service->getAllCountries();
310
311        foreach ($all_db_countries as $country_code => $country) {
312            foreach ($country as $country_name => $tot) {
313                $tmp     = new Place($country_name, $this->tree);
314
315                $top10[] = [
316                    'place' => $tmp,
317                    'count' => $tot,
318                    'name'  => $all_countries[$country_code],
319                ];
320            }
321
322            if ($i++ === 10) {
323                break;
324            }
325        }
326
327        return view(
328            'statistics/other/top10-list',
329            [
330                'records' => $top10,
331            ]
332        );
333    }
334
335    /**
336     * Count total places.
337     *
338     * @return int
339     */
340    private function totalPlacesQuery(): int
341    {
342        return DB::table('places')
343            ->where('p_file', '=', $this->tree->id())
344            ->count();
345    }
346
347    /**
348     * Count total places.
349     *
350     * @return string
351     */
352    public function totalPlaces(): string
353    {
354        return I18N::number($this->totalPlacesQuery());
355    }
356
357    /**
358     * Create a chart showing where events occurred.
359     *
360     * @param string $chart_shows
361     * @param string $chart_type
362     * @param string $surname
363     *
364     * @return string
365     */
366    public function chartDistribution(
367        string $chart_shows = 'world',
368        string $chart_type = '',
369        string $surname = ''
370    ): string {
371        return (new ChartDistribution($this->tree, $this->country_service, $this->individual_repository, $this))
372            ->chartDistribution($chart_shows, $chart_type, $surname);
373    }
374}
375