xref: /webtrees/app/Services/AdminService.php (revision a565e135d9aa2f3d9f55f82407a0703776784d78) 
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\Services;
21
22use Fisharebest\Webtrees\Registry;
23use Fisharebest\Webtrees\Family;
24use Fisharebest\Webtrees\Gedcom;
25use Fisharebest\Webtrees\GedcomRecord;
26use Fisharebest\Webtrees\Header;
27use Fisharebest\Webtrees\I18N;
28use Fisharebest\Webtrees\Individual;
29use Fisharebest\Webtrees\Media;
30use Fisharebest\Webtrees\Site;
31use Fisharebest\Webtrees\Source;
32use Fisharebest\Webtrees\Tree;
33use Illuminate\Database\Capsule\Manager as DB;
34use Illuminate\Database\Query\Expression;
35use Illuminate\Database\Query\JoinClause;
36use Illuminate\Support\Collection;
37use League\Flysystem\FilesystemInterface;
38
39use function array_map;
40use function explode;
41use function fclose;
42use function fread;
43use function preg_match;
44
45/**
46 * Utilities for the control panel.
47 */
48class AdminService
49{
50    // Show a reduced page when there are more than a certain number of trees
51    private const MULTIPLE_TREE_THRESHOLD = '500';
52
53    /**
54     * Count of XREFs used by two trees at the same time.
55     *
56     * @param Tree $tree1
57     * @param Tree $tree2
58     *
59     * @return int
60     */
61    public function countCommonXrefs(Tree $tree1, Tree $tree2): int
62    {
63        $subquery1 = DB::table('individuals')
64            ->where('i_file', '=', $tree1->id())
65            ->select(['i_id AS xref'])
66            ->union(DB::table('families')
67                ->where('f_file', '=', $tree1->id())
68                ->select(['f_id AS xref']))
69            ->union(DB::table('sources')
70                ->where('s_file', '=', $tree1->id())
71                ->select(['s_id AS xref']))
72            ->union(DB::table('media')
73                ->where('m_file', '=', $tree1->id())
74                ->select(['m_id AS xref']))
75            ->union(DB::table('other')
76                ->where('o_file', '=', $tree1->id())
77                ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR'])
78                ->select(['o_id AS xref']));
79
80        $subquery2 = DB::table('change')
81            ->where('gedcom_id', '=', $tree2->id())
82            ->select(['xref AS other_xref'])
83            ->union(DB::table('individuals')
84                ->where('i_file', '=', $tree2->id())
85                ->select(['i_id AS xref']))
86            ->union(DB::table('families')
87                ->where('f_file', '=', $tree2->id())
88                ->select(['f_id AS xref']))
89            ->union(DB::table('sources')
90                ->where('s_file', '=', $tree2->id())
91                ->select(['s_id AS xref']))
92            ->union(DB::table('media')
93                ->where('m_file', '=', $tree2->id())
94                ->select(['m_id AS xref']))
95            ->union(DB::table('other')
96                ->where('o_file', '=', $tree2->id())
97                ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR'])
98                ->select(['o_id AS xref']));
99
100        return DB::table(new Expression('(' . $subquery1->toSql() . ') AS sub1'))
101            ->mergeBindings($subquery1)
102            ->joinSub($subquery2, 'sub2', 'other_xref', '=', 'xref')
103            ->count();
104    }
105
106    /**
107     * @param Tree $tree
108     *
109     * @return array<string,array<int,array<int,GedcomRecord>>>
110     */
111    public function duplicateRecords(Tree $tree): array
112    {
113        // We can't do any reasonable checks using MySQL.
114        // Will need to wait for a "repositories" table.
115        $repositories = [];
116
117        $sources = DB::table('sources')
118            ->where('s_file', '=', $tree->id())
119            ->groupBy(['s_name'])
120            ->having(new Expression('COUNT(s_id)'), '>', '1')
121            ->select([new Expression('GROUP_CONCAT(s_id) AS xrefs')])
122            ->pluck('xrefs')
123            ->map(static function (string $xrefs) use ($tree): array {
124                return array_map(static function (string $xref) use ($tree): Source {
125                    return Registry::sourceFactory()->make($xref, $tree);
126                }, explode(',', $xrefs));
127            })
128            ->all();
129
130        $individuals = DB::table('dates')
131            ->join('name', static function (JoinClause $join): void {
132                $join
133                    ->on('d_file', '=', 'n_file')
134                    ->on('d_gid', '=', 'n_id');
135            })
136            ->where('d_file', '=', $tree->id())
137            ->whereIn('d_fact', ['BIRT', 'CHR', 'BAPM', 'DEAT', 'BURI'])
138            ->groupBy(['d_year', 'd_month', 'd_day', 'd_type', 'd_fact', 'n_type', 'n_full'])
139            ->having(new Expression('COUNT(DISTINCT d_gid)'), '>', '1')
140            ->select([new Expression('GROUP_CONCAT(DISTINCT d_gid ORDER BY d_gid) AS xrefs')])
141            ->distinct()
142            ->pluck('xrefs')
143            ->map(static function (string $xrefs) use ($tree): array {
144                return array_map(static function (string $xref) use ($tree): Individual {
145                    return Registry::individualFactory()->make($xref, $tree);
146                }, explode(',', $xrefs));
147            })
148            ->all();
149
150        $families = DB::table('families')
151            ->where('f_file', '=', $tree->id())
152            ->groupBy([new Expression('LEAST(f_husb, f_wife)')])
153            ->groupBy([new Expression('GREATEST(f_husb, f_wife)')])
154            ->having(new Expression('COUNT(f_id)'), '>', '1')
155            ->select([new Expression('GROUP_CONCAT(f_id) AS xrefs')])
156            ->pluck('xrefs')
157            ->map(static function (string $xrefs) use ($tree): array {
158                return array_map(static function (string $xref) use ($tree): Family {
159                    return Registry::familyFactory()->make($xref, $tree);
160                }, explode(',', $xrefs));
161            })
162            ->all();
163
164        $media = DB::table('media_file')
165            ->where('m_file', '=', $tree->id())
166            ->where('descriptive_title', '<>', '')
167            ->groupBy(['descriptive_title'])
168            ->having(new Expression('COUNT(m_id)'), '>', '1')
169            ->select([new Expression('GROUP_CONCAT(m_id) AS xrefs')])
170            ->pluck('xrefs')
171            ->map(static function (string $xrefs) use ($tree): array {
172                return array_map(static function (string $xref) use ($tree): Media {
173                    return Registry::mediaFactory()->make($xref, $tree);
174                }, explode(',', $xrefs));
175            })
176            ->all();
177
178        return [
179            I18N::translate('Repositories')  => $repositories,
180            I18N::translate('Sources')       => $sources,
181            I18N::translate('Individuals')   => $individuals,
182            I18N::translate('Families')      => $families,
183            I18N::translate('Media objects') => $media,
184        ];
185    }
186
187    /**
188     * Every XREF used by this tree and also used by some other tree
189     *
190     * @param Tree $tree
191     *
192     * @return array<string>
193     */
194    public function duplicateXrefs(Tree $tree): array
195    {
196        $subquery1 = DB::table('individuals')
197            ->where('i_file', '=', $tree->id())
198            ->select(['i_id AS xref', new Expression("'INDI' AS type")])
199            ->union(DB::table('families')
200                ->where('f_file', '=', $tree->id())
201                ->select(['f_id AS xref', new Expression("'FAM' AS type")]))
202            ->union(DB::table('sources')
203                ->where('s_file', '=', $tree->id())
204                ->select(['s_id AS xref', new Expression("'SOUR' AS type")]))
205            ->union(DB::table('media')
206                ->where('m_file', '=', $tree->id())
207                ->select(['m_id AS xref', new Expression("'OBJE' AS type")]))
208            ->union(DB::table('other')
209                ->where('o_file', '=', $tree->id())
210                ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR'])
211                ->select(['o_id AS xref', 'o_type AS type']));
212
213        $subquery2 = DB::table('change')
214            ->where('gedcom_id', '<>', $tree->id())
215            ->select(['xref AS other_xref'])
216            ->union(DB::table('individuals')
217                ->where('i_file', '<>', $tree->id())
218                ->select(['i_id AS xref']))
219            ->union(DB::table('families')
220                ->where('f_file', '<>', $tree->id())
221                ->select(['f_id AS xref']))
222            ->union(DB::table('sources')
223                ->where('s_file', '<>', $tree->id())
224                ->select(['s_id AS xref']))
225            ->union(DB::table('media')
226                ->where('m_file', '<>', $tree->id())
227                ->select(['m_id AS xref']))
228            ->union(DB::table('other')
229                ->where('o_file', '<>', $tree->id())
230                ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR'])
231                ->select(['o_id AS xref']));
232
233        return DB::table(new Expression('(' . $subquery1->toSql() . ') AS sub1'))
234            ->mergeBindings($subquery1)
235            ->joinSub($subquery2, 'sub2', 'other_xref', '=', 'xref')
236            ->pluck('type', 'xref')
237            ->all();
238    }
239
240    /**
241     * A list of GEDCOM files in the data folder.
242     *
243     * @param FilesystemInterface $filesystem
244     *
245     * @return Collection<string>
246     */
247    public function gedcomFiles(FilesystemInterface $filesystem): Collection
248    {
249        return Collection::make($filesystem->listContents())
250            ->filter(static function (array $path) use ($filesystem): bool {
251                if ($path['type'] !== 'file') {
252                    return false;
253                }
254
255                $stream = $filesystem->readStream($path['path']);
256
257                $header = fread($stream, 10);
258                fclose($stream);
259
260                return preg_match('/^(' . Gedcom::UTF8_BOM . ')?0 HEAD/', $header) > 0;
261            })
262            ->map(static function (array $path): string {
263                return $path['path'];
264            })
265            ->sort();
266    }
267
268    /**
269     * Change the behaviour a little, when there are a lot of trees.
270     *
271     * @return int
272     */
273    public function multipleTreeThreshold(): int
274    {
275        return (int) Site::getPreference('MULTIPLE_TREE_THRESHOLD', self::MULTIPLE_TREE_THRESHOLD);
276    }
277}
278