xref: /webtrees/app/Services/SearchService.php (revision 5d4b7ec24d2b390be41475c9b93e00d957e93fef)
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\Services;
21
22use Closure;
23use Fisharebest\Webtrees\Date;
24use Fisharebest\Webtrees\Exceptions\HttpServiceUnavailableException;
25use Fisharebest\Webtrees\Factory;
26use Fisharebest\Webtrees\Family;
27use Fisharebest\Webtrees\Gedcom;
28use Fisharebest\Webtrees\GedcomRecord;
29use Fisharebest\Webtrees\I18N;
30use Fisharebest\Webtrees\Individual;
31use Fisharebest\Webtrees\Media;
32use Fisharebest\Webtrees\Note;
33use Fisharebest\Webtrees\Place;
34use Fisharebest\Webtrees\Repository;
35use Fisharebest\Webtrees\Soundex;
36use Fisharebest\Webtrees\Source;
37use Fisharebest\Webtrees\Submitter;
38use Fisharebest\Webtrees\Tree;
39use Illuminate\Database\Capsule\Manager as DB;
40use Illuminate\Database\Query\Builder;
41use Illuminate\Database\Query\Expression;
42use Illuminate\Database\Query\JoinClause;
43use Illuminate\Support\Collection;
44use stdClass;
45
46use function addcslashes;
47use function array_unique;
48use function explode;
49use function mb_stripos;
50
51/**
52 * Search trees for genealogy records.
53 */
54class SearchService
55{
56    /** @var TreeService */
57    private $tree_service;
58
59    /**
60     * SearchService constructor.
61     *
62     * @param TreeService $tree_service
63     */
64    public function __construct(
65        TreeService $tree_service
66    ) {
67        $this->tree_service = $tree_service;
68    }
69
70    /**
71     * @param Tree[]   $trees
72     * @param string[] $search
73     *
74     * @return Collection<Family>
75     */
76    public function searchFamilies(array $trees, array $search): Collection
77    {
78        $query = DB::table('families');
79
80        $this->whereTrees($query, 'f_file', $trees);
81        $this->whereSearch($query, 'f_gedcom', $search);
82
83        return $query
84            ->get()
85            ->each($this->rowLimiter())
86            ->map($this->familyRowMapper())
87            ->filter(GedcomRecord::accessFilter())
88            ->filter($this->rawGedcomFilter($search));
89    }
90
91    /**
92     * Search for families by name.
93     *
94     * @param Tree[]   $trees
95     * @param string[] $search
96     * @param int      $offset
97     * @param int      $limit
98     *
99     * @return Collection<Family>
100     */
101    public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
102    {
103        $query = DB::table('families')
104            ->leftJoin('name AS husb_name', static function (JoinClause $join): void {
105                $join
106                    ->on('husb_name.n_file', '=', 'families.f_file')
107                    ->on('husb_name.n_id', '=', 'families.f_husb')
108                    ->where('husb_name.n_type', '<>', '_MARNM');
109            })
110            ->leftJoin('name AS wife_name', static function (JoinClause $join): void {
111                $join
112                    ->on('wife_name.n_file', '=', 'families.f_file')
113                    ->on('wife_name.n_id', '=', 'families.f_wife')
114                    ->where('wife_name.n_type', '<>', '_MARNM');
115            });
116
117        $prefix = DB::connection()->getTablePrefix();
118        $field  = new Expression('COALESCE(' . $prefix . "husb_name.n_full, '') || COALESCE(" . $prefix . "wife_name.n_full, '')");
119
120        $this->whereTrees($query, 'f_file', $trees);
121        $this->whereSearch($query, $field, $search);
122
123        $query
124            ->orderBy('husb_name.n_sort')
125            ->orderBy('wife_name.n_sort')
126            ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort'])
127            ->distinct();
128
129        return $this->paginateQuery($query, $this->familyRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
130    }
131
132    /**
133     * @param Place $place
134     *
135     * @return Collection<Family>
136     */
137    public function searchFamiliesInPlace(Place $place): Collection
138    {
139        return DB::table('families')
140            ->join('placelinks', static function (JoinClause $query) {
141                $query
142                    ->on('families.f_file', '=', 'placelinks.pl_file')
143                    ->on('families.f_id', '=', 'placelinks.pl_gid');
144            })
145            ->where('f_file', '=', $place->tree()->id())
146            ->where('pl_p_id', '=', $place->id())
147            ->select(['families.*'])
148            ->get()
149            ->each($this->rowLimiter())
150            ->map($this->familyRowMapper())
151            ->filter(GedcomRecord::accessFilter());
152    }
153
154    /**
155     * @param Tree[]   $trees
156     * @param string[] $search
157     *
158     * @return Collection<Individual>
159     */
160    public function searchIndividuals(array $trees, array $search): Collection
161    {
162        $query = DB::table('individuals');
163
164        $this->whereTrees($query, 'i_file', $trees);
165        $this->whereSearch($query, 'i_gedcom', $search);
166
167        return $query
168            ->get()
169            ->each($this->rowLimiter())
170            ->map($this->individualRowMapper())
171            ->filter(GedcomRecord::accessFilter())
172            ->filter($this->rawGedcomFilter($search));
173    }
174
175    /**
176     * Search for individuals by name.
177     *
178     * @param Tree[]   $trees
179     * @param string[] $search
180     * @param int      $offset
181     * @param int      $limit
182     *
183     * @return Collection<Individual>
184     */
185    public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
186    {
187        $query = DB::table('individuals')
188            ->join('name', static function (JoinClause $join): void {
189                $join
190                    ->on('name.n_file', '=', 'individuals.i_file')
191                    ->on('name.n_id', '=', 'individuals.i_id');
192            })
193            ->orderBy('n_sort')
194            ->distinct()
195            ->select(['individuals.*', 'n_sort']);
196
197        $this->whereTrees($query, 'i_file', $trees);
198        $this->whereSearch($query, 'n_full', $search);
199
200        return $this->paginateQuery($query, $this->individualRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
201    }
202
203    /**
204     * @param Place $place
205     *
206     * @return Collection<Individual>
207     */
208    public function searchIndividualsInPlace(Place $place): Collection
209    {
210        return DB::table('individuals')
211            ->join('placelinks', static function (JoinClause $join) {
212                $join
213                    ->on('i_file', '=', 'pl_file')
214                    ->on('i_id', '=', 'pl_gid');
215            })
216            ->where('i_file', '=', $place->tree()->id())
217            ->where('pl_p_id', '=', $place->id())
218            ->select(['individuals.*'])
219            ->get()
220            ->each($this->rowLimiter())
221            ->map($this->individualRowMapper())
222            ->filter(GedcomRecord::accessFilter());
223    }
224
225    /**
226     * Search for media objects.
227     *
228     * @param Tree[]   $trees
229     * @param string[] $search
230     * @param int      $offset
231     * @param int      $limit
232     *
233     * @return Collection<Media>
234     */
235    public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
236    {
237        $query = DB::table('media');
238
239        $this->whereTrees($query, 'media.m_file', $trees);
240        $this->whereSearch($query, 'm_gedcom', $search);
241
242        return $this->paginateQuery($query, $this->mediaRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
243    }
244
245    /**
246     * Search for notes.
247     *
248     * @param Tree[]   $trees
249     * @param string[] $search
250     * @param int      $offset
251     * @param int      $limit
252     *
253     * @return Collection<Note>
254     */
255    public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
256    {
257        $query = DB::table('other')
258            ->where('o_type', '=', 'NOTE');
259
260        $this->whereTrees($query, 'o_file', $trees);
261        $this->whereSearch($query, 'o_gedcom', $search);
262
263        return $this->paginateQuery($query, $this->noteRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
264    }
265
266    /**
267     * Search for repositories.
268     *
269     * @param Tree[]   $trees
270     * @param string[] $search
271     * @param int      $offset
272     * @param int      $limit
273     *
274     * @return Collection<Repository>
275     */
276    public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
277    {
278        $query = DB::table('other')
279            ->where('o_type', '=', 'REPO');
280
281        $this->whereTrees($query, 'o_file', $trees);
282        $this->whereSearch($query, 'o_gedcom', $search);
283
284        return $this->paginateQuery($query, $this->repositoryRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
285    }
286
287    /**
288     * Search for sources.
289     *
290     * @param Tree[]   $trees
291     * @param string[] $search
292     * @param int      $offset
293     * @param int      $limit
294     *
295     * @return Collection<Source>
296     */
297    public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
298    {
299        $query = DB::table('sources');
300
301        $this->whereTrees($query, 's_file', $trees);
302        $this->whereSearch($query, 's_gedcom', $search);
303
304        return $this->paginateQuery($query, $this->sourceRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
305    }
306
307    /**
308     * Search for sources by name.
309     *
310     * @param Tree[]   $trees
311     * @param string[] $search
312     * @param int      $offset
313     * @param int      $limit
314     *
315     * @return Collection<Source>
316     */
317    public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
318    {
319        $query = DB::table('sources')
320            ->orderBy('s_name');
321
322        $this->whereTrees($query, 's_file', $trees);
323        $this->whereSearch($query, 's_name', $search);
324
325        return $this->paginateQuery($query, $this->sourceRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
326    }
327
328    /**
329     * Search for submitters.
330     *
331     * @param Tree[]   $trees
332     * @param string[] $search
333     * @param int      $offset
334     * @param int      $limit
335     *
336     * @return Collection<Submitter>
337     */
338    public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
339    {
340        $query = DB::table('other')
341            ->where('o_type', '=', 'SUBM');
342
343        $this->whereTrees($query, 'o_file', $trees);
344        $this->whereSearch($query, 'o_gedcom', $search);
345
346        return $this->paginateQuery($query, $this->submitterRowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
347    }
348
349    /**
350     * Search for places.
351     *
352     * @param Tree   $tree
353     * @param string $search
354     * @param int    $offset
355     * @param int    $limit
356     *
357     * @return Collection<Place>
358     */
359    public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
360    {
361        $query = DB::table('places AS p0')
362            ->where('p0.p_file', '=', $tree->id())
363            ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id')
364            ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id')
365            ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id')
366            ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id')
367            ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id')
368            ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id')
369            ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id')
370            ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id')
371            ->orderBy('p0.p_place')
372            ->orderBy('p1.p_place')
373            ->orderBy('p2.p_place')
374            ->orderBy('p3.p_place')
375            ->orderBy('p4.p_place')
376            ->orderBy('p5.p_place')
377            ->orderBy('p6.p_place')
378            ->orderBy('p7.p_place')
379            ->orderBy('p8.p_place')
380            ->select([
381                'p0.p_place AS place0',
382                'p1.p_place AS place1',
383                'p2.p_place AS place2',
384                'p3.p_place AS place3',
385                'p4.p_place AS place4',
386                'p5.p_place AS place5',
387                'p6.p_place AS place6',
388                'p7.p_place AS place7',
389                'p8.p_place AS place8',
390            ]);
391
392        // Filter each level of the hierarchy.
393        foreach (explode(',', $search, 9) as $level => $string) {
394            $query->where('p' . $level . '.p_place', 'LIKE', '%' . addcslashes($string, '\\%_') . '%');
395        }
396
397        $row_mapper = static function (stdClass $row) use ($tree): Place {
398            $place = implode(', ', array_filter((array) $row));
399
400            return new Place($place, $tree);
401        };
402
403        $filter = static function (): bool {
404            return true;
405        };
406
407        return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit);
408    }
409
410    /**
411     * @param Tree[]   $trees
412     * @param string[] $fields
413     * @param string[] $modifiers
414     *
415     * @return Collection<Individual>
416     */
417    public function searchIndividualsAdvanced(array $trees, array $fields, array $modifiers): Collection
418    {
419        $fields = array_filter($fields);
420
421        $query = DB::table('individuals')
422            ->select(['individuals.*'])
423            ->distinct();
424
425        $this->whereTrees($query, 'i_file', $trees);
426
427        // Join the following tables
428        $father_name   = false;
429        $mother_name   = false;
430        $spouse_family = false;
431        $indi_name     = false;
432        $indi_dates    = [];
433        $fam_dates     = [];
434        $indi_plac     = false;
435        $fam_plac      = false;
436
437        foreach ($fields as $field_name => $field_value) {
438            if ($field_value !== '') {
439                // Fields can have up to 4 parts, but we only need the first 3 to identify
440                // which tables to select
441                $field_parts = explode(':', $field_name . '::');
442
443                if ($field_parts[0] === 'FAMC') {
444                    // Parent name - FAMC:[HUSB|WIFE]:NAME:[GIVN|SURN]
445                    if ($field_parts[1] === 'HUSB') {
446                        $father_name = true;
447                    } else {
448                        $mother_name = true;
449                    }
450                } elseif ($field_parts[0] === 'NAME') {
451                    // Individual name - NAME:[GIVN|SURN]
452                    $indi_name = true;
453                } elseif ($field_parts[0] === 'FAMS') {
454                    // Family facts - FAMS:NOTE or FAMS:[FACT]:[DATE|PLAC]
455                    $spouse_family = true;
456                    if ($field_parts[2] === 'DATE') {
457                        $fam_dates[] = $field_parts[1];
458                    } elseif ($field_parts[2] === 'PLAC') {
459                        $fam_plac = true;
460                    }
461                } else {
462                    // Individual facts - [FACT] or [FACT]:[DATE|PLAC]
463                    if ($field_parts[1] === 'DATE') {
464                        $indi_dates[] = $field_parts[0];
465                    } elseif ($field_parts[1] === 'PLAC') {
466                        $indi_plac = true;
467                    }
468                }
469            }
470        }
471
472        if ($father_name || $mother_name) {
473            $query->join('link AS l1', static function (JoinClause $join): void {
474                $join
475                    ->on('l1.l_file', '=', 'individuals.i_file')
476                    ->on('l1.l_from', '=', 'individuals.i_id')
477                    ->where('l1.l_type', '=', 'FAMC');
478            });
479
480            if ($father_name) {
481                $query->join('link AS l2', static function (JoinClause $join): void {
482                    $join
483                        ->on('l2.l_file', '=', 'l1.l_file')
484                        ->on('l2.l_from', '=', 'l1.l_to')
485                        ->where('l2.l_type', '=', 'HUSB');
486                });
487                $query->join('name AS father_name', static function (JoinClause $join): void {
488                    $join
489                        ->on('father_name.n_file', '=', 'l2.l_file')
490                        ->on('father_name.n_id', '=', 'l2.l_to');
491                });
492            }
493
494            if ($mother_name) {
495                $query->join('link AS l3', static function (JoinClause $join): void {
496                    $join
497                        ->on('l3.l_file', '=', 'l1.l_file')
498                        ->on('l3.l_from', '=', 'l1.l_to')
499                        ->where('l3.l_type', '=', 'WIFE');
500                });
501                $query->join('name AS mother_name', static function (JoinClause $join): void {
502                    $join
503                        ->on('mother_name.n_file', '=', 'l3.l_file')
504                        ->on('mother_name.n_id', '=', 'l3.l_to');
505                });
506            }
507        }
508
509        if ($spouse_family) {
510            $query->join('link AS l4', static function (JoinClause $join): void {
511                $join
512                    ->on('l4.l_file', '=', 'individuals.i_file')
513                    ->on('l4.l_from', '=', 'individuals.i_id')
514                    ->where('l4.l_type', '=', 'FAMS');
515            });
516            $query->join('families AS spouse_families', static function (JoinClause $join): void {
517                $join
518                    ->on('spouse_families.f_file', '=', 'l4.l_file')
519                    ->on('spouse_families.f_id', '=', 'l4.l_to');
520            });
521        }
522
523        if ($indi_name) {
524            $query->join('name AS individual_name', static function (JoinClause $join): void {
525                $join
526                    ->on('individual_name.n_file', '=', 'individuals.i_file')
527                    ->on('individual_name.n_id', '=', 'individuals.i_id');
528            });
529        }
530
531        foreach (array_unique($indi_dates) as $indi_date) {
532            $query->join('dates AS date_' . $indi_date, static function (JoinClause $join) use ($indi_date): void {
533                $join
534                    ->on('date_' . $indi_date . '.d_file', '=', 'individuals.i_file')
535                    ->on('date_' . $indi_date . '.d_gid', '=', 'individuals.i_id');
536            });
537        }
538
539        foreach (array_unique($fam_dates) as $fam_date) {
540            $query->join('dates AS date_' . $fam_date, static function (JoinClause $join) use ($fam_date): void {
541                $join
542                    ->on('date_' . $fam_date . '.d_file', '=', 'spouse_families.i_file')
543                    ->on('date_' . $fam_date . '.d_gid', '=', 'spouse_families.i_id');
544            });
545        }
546
547        if ($indi_plac) {
548            $query->join('placelinks AS individual_placelinks', static function (JoinClause $join): void {
549                $join
550                    ->on('individual_placelinks.pl_file', '=', 'individuals.i_file')
551                    ->on('individual_placelinks.pl_gid', '=', 'individuals.i_id');
552            });
553            $query->join('places AS individual_places', static function (JoinClause $join): void {
554                $join
555                    ->on('individual_places.p_file', '=', 'individual_placelinks.pl_file')
556                    ->on('individual_places.p_id', '=', 'individual_placelinks.pl_p_id');
557            });
558        }
559
560        if ($fam_plac) {
561            $query->join('placelinks AS familyl_placelinks', static function (JoinClause $join): void {
562                $join
563                    ->on('familyl_placelinks.pl_file', '=', 'individuals.i_file')
564                    ->on('familyl_placelinks.pl_gid', '=', 'individuals.i_id');
565            });
566            $query->join('places AS family_places', static function (JoinClause $join): void {
567                $join
568                    ->on('family_places.p_file', '=', 'familyl_placelinks.pl_file')
569                    ->on('family_places.p_id', '=', 'familyl_placelinks.pl_p_id');
570            });
571        }
572
573        foreach ($fields as $field_name => $field_value) {
574            $parts = explode(':', $field_name . ':::');
575            if ($parts[0] === 'NAME') {
576                // NAME:*
577                switch ($parts[1]) {
578                    case 'GIVN':
579                        switch ($modifiers[$field_name]) {
580                            case 'EXACT':
581                                $query->where('individual_name.n_givn', '=', $field_value);
582                                break;
583                            case 'BEGINS':
584                                $query->where('individual_name.n_givn', 'LIKE', $field_value . '%');
585                                break;
586                            case 'CONTAINS':
587                                $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%');
588                                break;
589                            case 'SDX_STD':
590                                $sdx = Soundex::russell($field_value);
591                                if ($sdx !== '') {
592                                    $this->wherePhonetic($query, 'individual_name.n_soundex_givn_std', $sdx);
593                                } else {
594                                    // No phonetic content? Use a substring match
595                                    $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%');
596                                }
597                                break;
598                            case 'SDX': // SDX uses DM by default.
599                            case 'SDX_DM':
600                                $sdx = Soundex::daitchMokotoff($field_value);
601                                if ($sdx !== '') {
602                                    $this->wherePhonetic($query, 'individual_name.n_soundex_givn_dm', $sdx);
603                                } else {
604                                    // No phonetic content? Use a substring match
605                                    $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%');
606                                }
607                                break;
608                        }
609                        break;
610                    case 'SURN':
611                        switch ($modifiers[$field_name]) {
612                            case 'EXACT':
613                                $query->where(function (Builder $query) use ($field_value): void {
614                                    $query
615                                        ->where('individual_name.n_surn', '=', $field_value)
616                                        ->orWhere('individual_name.n_surname', '=', $field_value);
617                                });
618                                break;
619                            case 'BEGINS':
620                                $query->where(function (Builder $query) use ($field_value): void {
621                                    $query
622                                        ->where('individual_name.n_surn', 'LIKE', $field_value . '%')
623                                        ->orWhere('individual_name.n_surname', 'LIKE', $field_value . '%');
624                                });
625                                break;
626                            case 'CONTAINS':
627                                $query->where(function (Builder $query) use ($field_value): void {
628                                    $query
629                                        ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%')
630                                        ->orWhere('individual_name.n_surname', 'LIKE', '%' . $field_value . '%');
631                                });
632                                break;
633                            case 'SDX_STD':
634                                $sdx = Soundex::russell($field_value);
635                                if ($sdx !== '') {
636                                    $this->wherePhonetic($query, 'individual_name.n_soundex_surn_std', $sdx);
637                                } else {
638                                    // No phonetic content? Use a substring match
639                                    $query->where(function (Builder $query) use ($field_value): void {
640                                        $query
641                                            ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%')
642                                            ->orWhere('individual_name.n_surname', 'LIKE', '%' . $field_value . '%');
643                                    });
644                                }
645                                break;
646                            case 'SDX': // SDX uses DM by default.
647                            case 'SDX_DM':
648                                $sdx = Soundex::daitchMokotoff($field_value);
649                                if ($sdx !== '') {
650                                    $this->wherePhonetic($query, 'individual_name.n_soundex_surn_dm', $sdx);
651                                } else {
652                                    // No phonetic content? Use a substring match
653                                    $query->where(function (Builder $query) use ($field_value): void {
654                                        $query
655                                            ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%')
656                                            ->orWhere('individual_name.n_surname', 'LIKE', '%' . $field_value . '%');
657                                    });
658                                }
659                                break;
660                        }
661                        break;
662                    case 'NICK':
663                    case '_MARNM':
664                    case '_HEB':
665                    case '_AKA':
666                        $query
667                            ->where('individual_name', '=', $parts[1])
668                            ->where('individual_name', 'LIKE', '%' . $field_value . '%');
669                        break;
670                }
671                unset($fields[$field_name]);
672            } elseif ($parts[1] === 'DATE') {
673                // *:DATE
674                $date = new Date($field_value);
675                if ($date->isOK()) {
676                    $delta = 365 * ($modifiers[$field_name] ?? 0);
677                    $query
678                        ->where('date_' . $parts[0] . '.d_fact', '=', $parts[0])
679                        ->where('date_' . $parts[0] . '.d_julianday1', '>=', $date->minimumJulianDay() - $delta)
680                        ->where('date_' . $parts[0] . '.d_julianday2', '<=', $date->maximumJulianDay() + $delta);
681                }
682                unset($fields[$field_name]);
683            } elseif ($parts[0] === 'FAMS' && $parts[2] === 'DATE') {
684                // FAMS:*:DATE
685                $date = new Date($field_value);
686                if ($date->isOK()) {
687                    $delta = 365 * $modifiers[$field_name];
688                    $query
689                        ->where('date_' . $parts[1] . '.d_fact', '=', $parts[1])
690                        ->where('date_' . $parts[1] . '.d_julianday1', '>=', $date->minimumJulianDay() - $delta)
691                        ->where('date_' . $parts[1] . '.d_julianday2', '<=', $date->maximumJulianDay() + $delta);
692                }
693                unset($fields[$field_name]);
694            } elseif ($parts[1] === 'PLAC') {
695                // *:PLAC
696                // SQL can only link a place to a person/family, not to an event.
697                $query->where('individual_places.p_place', 'LIKE', '%' . $field_value . '%');
698            } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') {
699                // FAMS:*:PLAC
700                // SQL can only link a place to a person/family, not to an event.
701                $query->where('family_places.p_place', 'LIKE', '%' . $field_value . '%');
702            } elseif ($parts[0] === 'FAMC' && $parts[2] === 'NAME') {
703                $table = $parts[1] === 'HUSB' ? 'father_name' : 'mother_name';
704                // NAME:*
705                switch ($parts[3]) {
706                    case 'GIVN':
707                        switch ($modifiers[$field_name]) {
708                            case 'EXACT':
709                                $query->where($table . '.n_givn', '=', $field_value);
710                                break;
711                            case 'BEGINS':
712                                $query->where($table . '.n_givn', 'LIKE', $field_value . '%');
713                                break;
714                            case 'CONTAINS':
715                                $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%');
716                                break;
717                            case 'SDX_STD':
718                                $sdx = Soundex::russell($field_value);
719                                if ($sdx !== '') {
720                                    $this->wherePhonetic($query, $table . '.n_soundex_givn_std', $sdx);
721                                } else {
722                                    // No phonetic content? Use a substring match
723                                    $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%');
724                                }
725                                break;
726                            case 'SDX': // SDX uses DM by default.
727                            case 'SDX_DM':
728                                $sdx = Soundex::daitchMokotoff($field_value);
729                                if ($sdx !== '') {
730                                    $this->wherePhonetic($query, $table . '.n_soundex_givn_dm', $sdx);
731                                } else {
732                                    // No phonetic content? Use a substring match
733                                    $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%');
734                                }
735                                break;
736                        }
737                        break;
738                    case 'SURN':
739                        switch ($modifiers[$field_name]) {
740                            case 'EXACT':
741                                $query->where($table . '.n_surn', '=', $field_value);
742                                break;
743                            case 'BEGINS':
744                                $query->where($table . '.n_surn', 'LIKE', $field_value . '%');
745                                break;
746                            case 'CONTAINS':
747                                $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%');
748                                break;
749                            case 'SDX_STD':
750                                $sdx = Soundex::russell($field_value);
751                                if ($sdx !== '') {
752                                    $this->wherePhonetic($query, $table . '.n_soundex_surn_std', $sdx);
753                                } else {
754                                    // No phonetic content? Use a substring match
755                                    $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%');
756                                }
757                                break;
758                            case 'SDX': // SDX uses DM by default.
759                            case 'SDX_DM':
760                                $sdx = Soundex::daitchMokotoff($field_value);
761                                if ($sdx !== '') {
762                                    $this->wherePhonetic($query, $table . '.n_soundex_surn_dm', $sdx);
763                                } else {
764                                    // No phonetic content? Use a substring match
765                                    $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%');
766                                }
767                                break;
768                        }
769                        break;
770                }
771                unset($fields[$field_name]);
772            } elseif ($parts[0] === 'FAMS') {
773                // e.g. searches for occupation, religion, note, etc.
774                // Initial matching only.  Need PHP to apply filter.
775                $query->where('spouse_families.f_gedcom', 'LIKE', "%\n1 " . $parts[1] . ' %' . $field_value . '%');
776            } elseif ($parts[1] === 'TYPE') {
777                // e.g. FACT:TYPE or EVEN:TYPE
778                // Initial matching only.  Need PHP to apply filter.
779                $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . '%\n2 TYPE %' . $field_value . '%');
780            } else {
781                // e.g. searches for occupation, religion, note, etc.
782                // Initial matching only.  Need PHP to apply filter.
783                $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . '%' . $parts[1] . '%' . $field_value . '%');
784            }
785        }
786        return $query
787            ->get()
788            ->each($this->rowLimiter())
789            ->map($this->individualRowMapper())
790            ->filter(GedcomRecord::accessFilter())
791            ->filter(static function (Individual $individual) use ($fields): bool {
792                // Check for searches which were only partially matched by SQL
793                foreach ($fields as $field_name => $field_value) {
794                    $regex = '/' . preg_quote($field_value, '/') . '/i';
795
796                    $parts = explode(':', $field_name . '::::');
797
798                    // *:PLAC
799                    if ($parts[1] === 'PLAC') {
800                        foreach ($individual->facts([$parts[0]]) as $fact) {
801                            if (preg_match($regex, $fact->place()->gedcomName())) {
802                                continue 2;
803                            }
804                        }
805                        return false;
806                    }
807
808                    // FAMS:*:PLAC
809                    if ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') {
810                        foreach ($individual->spouseFamilies() as $family) {
811                            foreach ($family->facts([$parts[1]]) as $fact) {
812                                if (preg_match($regex, $fact->place()->gedcomName())) {
813                                    continue 2;
814                                }
815                            }
816                        }
817                        return false;
818                    }
819
820                    // e.g. searches for occupation, religion, note, etc.
821                    if ($parts[0] === 'FAMS') {
822                        foreach ($individual->spouseFamilies() as $family) {
823                            foreach ($family->facts([$parts[1]]) as $fact) {
824                                if (preg_match($regex, $fact->value())) {
825                                    continue 3;
826                                }
827                            }
828                        }
829                        return false;
830                    }
831
832                    // e.g. FACT:TYPE or EVEN:TYPE
833                    if ($parts[1] === 'TYPE' || $parts[1] === '_WT_USER') {
834                        foreach ($individual->facts([$parts[0]]) as $fact) {
835                            if (preg_match($regex, $fact->attribute($parts[1]))) {
836                                continue 2;
837                            }
838                        }
839
840                        return false;
841                    }
842                }
843
844                return true;
845            });
846    }
847
848    /**
849     * @param string $soundex
850     * @param string $lastname
851     * @param string $firstname
852     * @param string $place
853     * @param Tree[] $search_trees
854     *
855     * @return Collection<Individual>
856     */
857    public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees): Collection
858    {
859        switch ($soundex) {
860            default:
861            case 'Russell':
862                $givn_sdx   = Soundex::russell($firstname);
863                $surn_sdx   = Soundex::russell($lastname);
864                $plac_sdx   = Soundex::russell($place);
865                $givn_field = 'n_soundex_givn_std';
866                $surn_field = 'n_soundex_surn_std';
867                $plac_field = 'p_std_soundex';
868                break;
869            case 'DaitchM':
870                $givn_sdx   = Soundex::daitchMokotoff($firstname);
871                $surn_sdx   = Soundex::daitchMokotoff($lastname);
872                $plac_sdx   = Soundex::daitchMokotoff($place);
873                $givn_field = 'n_soundex_givn_dm';
874                $surn_field = 'n_soundex_surn_dm';
875                $plac_field = 'p_dm_soundex';
876                break;
877        }
878
879        // Nothing to search for? Return nothing.
880        if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') {
881            return new Collection();
882        }
883
884        $query = DB::table('individuals')
885            ->select(['individuals.*'])
886            ->distinct();
887
888        $this->whereTrees($query, 'i_file', $search_trees);
889
890        if ($plac_sdx !== '') {
891            $query->join('placelinks', static function (JoinClause $join): void {
892                $join
893                    ->on('placelinks.pl_file', '=', 'individuals.i_file')
894                    ->on('placelinks.pl_gid', '=', 'individuals.i_id');
895            });
896            $query->join('places', static function (JoinClause $join): void {
897                $join
898                    ->on('places.p_file', '=', 'placelinks.pl_file')
899                    ->on('places.p_id', '=', 'placelinks.pl_p_id');
900            });
901
902            $this->wherePhonetic($query, $plac_field, $plac_sdx);
903        }
904
905        if ($givn_sdx !== '' || $surn_sdx !== '') {
906            $query->join('name', static function (JoinClause $join): void {
907                $join
908                    ->on('name.n_file', '=', 'individuals.i_file')
909                    ->on('name.n_id', '=', 'individuals.i_id');
910            });
911
912            $this->wherePhonetic($query, $givn_field, $givn_sdx);
913            $this->wherePhonetic($query, $surn_field, $surn_sdx);
914        }
915
916        return $query
917            ->get()
918            ->each($this->rowLimiter())
919            ->map($this->individualRowMapper())
920            ->filter(GedcomRecord::accessFilter());
921    }
922
923    /**
924     * Paginate a search query.
925     *
926     * @param Builder $query      Searches the database for the desired records.
927     * @param Closure $row_mapper Converts a row from the query into a record.
928     * @param Closure $row_filter
929     * @param int     $offset     Skip this many rows.
930     * @param int     $limit      Take this many rows.
931     *
932     * @return Collection<mixed>
933     */
934    private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection
935    {
936        $collection = new Collection();
937
938        foreach ($query->cursor() as $row) {
939            $record = $row_mapper($row);
940            // If the object has a method "canShow()", then use it to filter for privacy.
941            if ($row_filter($record)) {
942                if ($offset > 0) {
943                    $offset--;
944                } else {
945                    if ($limit > 0) {
946                        $collection->push($record);
947                    }
948
949                    $limit--;
950
951                    if ($limit === 0) {
952                        break;
953                    }
954                }
955            }
956        }
957
958
959        return $collection;
960    }
961
962    /**
963     * Apply search filters to a SQL query column.  Apply collation rules to MySQL.
964     *
965     * @param Builder           $query
966     * @param Expression|string $field
967     * @param string[]          $search_terms
968     */
969    private function whereSearch(Builder $query, $field, array $search_terms): void
970    {
971        if ($field instanceof Expression) {
972            $field = $field->getValue();
973        }
974
975        foreach ($search_terms as $search_term) {
976            $query->where(new Expression($field), 'LIKE', '%' . addcslashes($search_term, '\\%_') . '%');
977        }
978    }
979
980    /**
981     * Apply soundex search filters to a SQL query column.
982     *
983     * @param Builder           $query
984     * @param Expression|string $field
985     * @param string            $soundex
986     */
987    private function wherePhonetic(Builder $query, $field, string $soundex): void
988    {
989        if ($soundex !== '') {
990            $query->where(static function (Builder $query) use ($soundex, $field): void {
991                foreach (explode(':', $soundex) as $sdx) {
992                    $query->orWhere($field, 'LIKE', '%' . $sdx . '%');
993                }
994            });
995        }
996    }
997
998    /**
999     * @param Builder $query
1000     * @param string  $tree_id_field
1001     * @param Tree[]  $trees
1002     */
1003    private function whereTrees(Builder $query, string $tree_id_field, array $trees): void
1004    {
1005        $tree_ids = array_map(static function (Tree $tree): int {
1006            return $tree->id();
1007        }, $trees);
1008
1009        $query->whereIn($tree_id_field, $tree_ids);
1010    }
1011
1012    /**
1013     * Find the media object that uses a particular media file.
1014     *
1015     * @param string $file
1016     *
1017     * @return Media[]
1018     */
1019    public function findMediaObjectsForMediaFile(string $file): array
1020    {
1021        return DB::table('media')
1022            ->join('media_file', static function (JoinClause $join): void {
1023                $join
1024                    ->on('media_file.m_file', '=', 'media.m_file')
1025                    ->on('media_file.m_id', '=', 'media.m_id');
1026            })
1027            ->join('gedcom_setting', 'media.m_file', '=', 'gedcom_setting.gedcom_id')
1028            ->where(new Expression('setting_value || multimedia_file_refn'), '=', $file)
1029            ->select(['media.*'])
1030            ->distinct()
1031            ->get()
1032            ->map($this->mediaRowMapper())
1033            ->all();
1034    }
1035
1036    /**
1037     * A closure to filter records by privacy-filtered GEDCOM data.
1038     *
1039     * @param array<string> $search_terms
1040     *
1041     * @return Closure
1042     */
1043    private function rawGedcomFilter(array $search_terms): Closure
1044    {
1045        return static function (GedcomRecord $record) use ($search_terms): bool {
1046            // Ignore non-genealogy fields
1047            $gedcom = preg_replace('/\n\d (?:_UID|_WT_USER) .*/', '', $record->gedcom());
1048
1049            // Ignore matches in links
1050            $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom);
1051
1052            // Re-apply the filtering
1053            foreach ($search_terms as $search_term) {
1054                if (mb_stripos($gedcom, $search_term) === false) {
1055                    return false;
1056                }
1057            }
1058
1059            return true;
1060        };
1061    }
1062
1063    /**
1064     * Searching for short or common text can give more results than the system can process.
1065     *
1066     * @param int $limit
1067     *
1068     * @return Closure
1069     */
1070    private function rowLimiter(int $limit = 1000): Closure
1071    {
1072        return static function () use ($limit): void {
1073            static $n = 0;
1074
1075            if (++$n > $limit) {
1076                $message = I18N::translate('The search returned too many results.');
1077
1078                throw new HttpServiceUnavailableException($message);
1079            }
1080        };
1081    }
1082
1083    /**
1084     * Convert a row from any tree in the families table into a family object.
1085     *
1086     * @return Closure
1087     */
1088    private function familyRowMapper(): Closure
1089    {
1090        return function (stdClass $row): Family {
1091            $tree = $this->tree_service->find((int) $row->f_file);
1092
1093            return Factory::family()->mapper($tree)($row);
1094        };
1095    }
1096
1097    /**
1098     * Convert a row from any tree in the individuals table into an individual object.
1099     *
1100     * @return Closure
1101     */
1102    private function individualRowMapper(): Closure
1103    {
1104        return function (stdClass $row): Individual {
1105            $tree = $this->tree_service->find((int) $row->i_file);
1106
1107            return Factory::individual()->mapper($tree)($row);
1108        };
1109    }
1110
1111    /**
1112     * Convert a row from any tree in the media table into an media object.
1113     *
1114     * @return Closure
1115     */
1116    private function mediaRowMapper(): Closure
1117    {
1118        return function (stdClass $row): Media {
1119            $tree = $this->tree_service->find((int) $row->m_file);
1120
1121            return Factory::media()->mapper($tree)($row);
1122        };
1123    }
1124
1125    /**
1126     * Convert a row from any tree in the other table into a note object.
1127     *
1128     * @return Closure
1129     */
1130    private function noteRowMapper(): Closure
1131    {
1132        return function (stdClass $row): Note {
1133            $tree = $this->tree_service->find((int) $row->o_file);
1134
1135            return Factory::note()->mapper($tree)($row);
1136        };
1137    }
1138
1139    /**
1140     * Convert a row from any tree in the other table into a repository object.
1141     *
1142     * @return Closure
1143     */
1144    private function repositoryRowMapper(): Closure
1145    {
1146        return function (stdClass $row): Repository {
1147            $tree = $this->tree_service->find((int) $row->o_file);
1148
1149            return Factory::repository()->mapper($tree)($row);
1150        };
1151    }
1152
1153    /**
1154     * Convert a row from any tree in the sources table into a source object.
1155     *
1156     * @return Closure
1157     */
1158    private function sourceRowMapper(): Closure
1159    {
1160        return function (stdClass $row): Source {
1161            $tree = $this->tree_service->find((int) $row->s_file);
1162
1163            return Factory::source()->mapper($tree)($row);
1164        };
1165    }
1166
1167    /**
1168     * Convert a row from any tree in the other table into a submitter object.
1169     *
1170     * @return Closure
1171     */
1172    private function submitterRowMapper(): Closure
1173    {
1174        return function (stdClass $row): Submitter {
1175            $tree = $this->tree_service->find((int) $row->o_file);
1176
1177            return Factory::submitter()->mapper($tree)($row);
1178        };
1179    }
1180}
1181