132cd2800SGreg Roach<?php 232cd2800SGreg Roach/** 332cd2800SGreg Roach * webtrees: online genealogy 432cd2800SGreg Roach * Copyright (C) 2019 webtrees development team 532cd2800SGreg Roach * This program is free software: you can redistribute it and/or modify 632cd2800SGreg Roach * it under the terms of the GNU General Public License as published by 732cd2800SGreg Roach * the Free Software Foundation, either version 3 of the License, or 832cd2800SGreg Roach * (at your option) any later version. 932cd2800SGreg Roach * This program is distributed in the hope that it will be useful, 1032cd2800SGreg Roach * but WITHOUT ANY WARRANTY; without even the implied warranty of 1132cd2800SGreg Roach * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 1232cd2800SGreg Roach * GNU General Public License for more details. 1332cd2800SGreg Roach * You should have received a copy of the GNU General Public License 1432cd2800SGreg Roach * along with this program. If not, see <http://www.gnu.org/licenses/>. 1532cd2800SGreg Roach */ 1632cd2800SGreg Roachdeclare(strict_types=1); 1732cd2800SGreg Roach 1832cd2800SGreg Roachnamespace Fisharebest\Webtrees\Services; 1932cd2800SGreg Roach 2032cd2800SGreg Roachuse Closure; 21a7a24840SGreg Roachuse Fisharebest\Localization\Locale\LocaleInterface; 2232cd2800SGreg Roachuse Fisharebest\Webtrees\Family; 23a7a24840SGreg Roachuse Fisharebest\Webtrees\Gedcom; 2432cd2800SGreg Roachuse Fisharebest\Webtrees\GedcomRecord; 2532cd2800SGreg Roachuse Fisharebest\Webtrees\Individual; 2632cd2800SGreg Roachuse Fisharebest\Webtrees\Media; 2732cd2800SGreg Roachuse Fisharebest\Webtrees\Note; 28b68caec6SGreg Roachuse Fisharebest\Webtrees\Place; 2932cd2800SGreg Roachuse Fisharebest\Webtrees\Repository; 30*2d686e68SGreg Roachuse Fisharebest\Webtrees\Soundex; 3132cd2800SGreg Roachuse Fisharebest\Webtrees\Source; 3232cd2800SGreg Roachuse Fisharebest\Webtrees\Tree; 3332cd2800SGreg Roachuse Illuminate\Database\Capsule\Manager as DB; 3432cd2800SGreg Roachuse Illuminate\Database\Query\Builder; 35a7a24840SGreg Roachuse Illuminate\Database\Query\Expression; 3632cd2800SGreg Roachuse Illuminate\Database\Query\JoinClause; 3732cd2800SGreg Roachuse Illuminate\Support\Collection; 3832cd2800SGreg Roachuse stdClass; 39a7a24840SGreg Roachuse function mb_stripos; 4032cd2800SGreg Roach 4132cd2800SGreg Roach/** 4232cd2800SGreg Roach * Search trees for genealogy records. 4332cd2800SGreg Roach */ 4432cd2800SGreg Roachclass SearchService 4532cd2800SGreg Roach{ 46a7a24840SGreg Roach /** @var LocaleInterface */ 47a7a24840SGreg Roach private $locale; 48a7a24840SGreg Roach 49a7a24840SGreg Roach /** 50a7a24840SGreg Roach * SearchService constructor. 51a7a24840SGreg Roach * 52a7a24840SGreg Roach * @param LocaleInterface $locale 53a7a24840SGreg Roach */ 54a7a24840SGreg Roach public function __construct(LocaleInterface $locale) 55a7a24840SGreg Roach { 56a7a24840SGreg Roach $this->locale = $locale; 57a7a24840SGreg Roach } 58a7a24840SGreg Roach 59a7a24840SGreg Roach /** 60a7a24840SGreg Roach * @param Tree[] $trees 61a7a24840SGreg Roach * @param string[] $search 62a7a24840SGreg Roach * 63a7a24840SGreg Roach * @return Collection|Family[] 64a7a24840SGreg Roach */ 65a7a24840SGreg Roach public function searchFamilies(array $trees, array $search): Collection 66a7a24840SGreg Roach { 67a7a24840SGreg Roach $query = DB::table('families'); 68a7a24840SGreg Roach 69a7a24840SGreg Roach $this->whereTrees($query, 'f_file', $trees); 70a7a24840SGreg Roach $this->whereSearch($query, 'f_gedcom', $search); 71a7a24840SGreg Roach 72a7a24840SGreg Roach return $query 73a7a24840SGreg Roach ->get() 74a7a24840SGreg Roach ->map(Family::rowMapper()) 75a7a24840SGreg Roach ->filter(GedcomRecord::accessFilter()) 767f5fa3c2SGreg Roach ->filter($this->rawGedcomFilter($search)); 77a7a24840SGreg Roach } 78a7a24840SGreg Roach 7932cd2800SGreg Roach /** 8032cd2800SGreg Roach * Search for families by name. 8132cd2800SGreg Roach * 82a7a24840SGreg Roach * @param Tree[] $trees 83a7a24840SGreg Roach * @param string[] $search 8432cd2800SGreg Roach * @param int $offset 8532cd2800SGreg Roach * @param int $limit 8632cd2800SGreg Roach * 8732cd2800SGreg Roach * @return Collection|Family[] 8832cd2800SGreg Roach */ 89a7a24840SGreg Roach public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 9032cd2800SGreg Roach { 9132cd2800SGreg Roach $query = DB::table('families') 92a7a24840SGreg Roach ->join('name AS husb_name', function (JoinClause $join): void { 9332cd2800SGreg Roach $join 9432cd2800SGreg Roach ->on('husb_name.n_file', '=', 'families.f_file') 95a7a24840SGreg Roach ->on('husb_name.n_id', '=', 'families.f_husb'); 9632cd2800SGreg Roach }) 97a7a24840SGreg Roach ->join('name AS wife_name', function (JoinClause $join): void { 9832cd2800SGreg Roach $join 9932cd2800SGreg Roach ->on('wife_name.n_file', '=', 'families.f_file') 100a7a24840SGreg Roach ->on('wife_name.n_id', '=', 'families.f_wife'); 10132cd2800SGreg Roach }) 102a7a24840SGreg Roach ->where('wife_name.n_type', '<>', '_MARNM') 103a7a24840SGreg Roach ->where('husb_name.n_type', '<>', '_MARNM'); 104a7a24840SGreg Roach 105a7a24840SGreg Roach $prefix = DB::connection()->getTablePrefix(); 106e3bddf11SGreg Roach $field = DB::raw($prefix . '(husb_name.n_full || ' . $prefix . 'wife_name.n_full)'); 107a7a24840SGreg Roach 108a7a24840SGreg Roach $this->whereTrees($query, 'f_file', $trees); 109a7a24840SGreg Roach $this->whereSearch($query, $field, $search); 110a7a24840SGreg Roach 111a7a24840SGreg Roach $query 11232cd2800SGreg Roach ->orderBy('husb_name.n_sort') 11332cd2800SGreg Roach ->orderBy('wife_name.n_sort') 114c0804649SGreg Roach ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort']) 11532cd2800SGreg Roach ->distinct(); 11632cd2800SGreg Roach 117a7a24840SGreg Roach return $this->paginateQuery($query, Family::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 118a7a24840SGreg Roach } 119a7a24840SGreg Roach 120a7a24840SGreg Roach /** 121a7a24840SGreg Roach * @param Tree[] $trees 122a7a24840SGreg Roach * @param string[] $search 123a7a24840SGreg Roach * 124a7a24840SGreg Roach * @return Collection|Individual[] 125a7a24840SGreg Roach */ 126a7a24840SGreg Roach public function searchIndividuals(array $trees, array $search): Collection 127a7a24840SGreg Roach { 128a7a24840SGreg Roach $query = DB::table('individuals'); 129a7a24840SGreg Roach 130a7a24840SGreg Roach $this->whereTrees($query, 'i_file', $trees); 131a7a24840SGreg Roach $this->whereSearch($query, 'i_gedcom', $search); 132a7a24840SGreg Roach 133a7a24840SGreg Roach return $query 134a7a24840SGreg Roach ->get() 135a7a24840SGreg Roach ->map(Individual::rowMapper()) 136a7a24840SGreg Roach ->filter(GedcomRecord::accessFilter()) 1377f5fa3c2SGreg Roach ->filter($this->rawGedcomFilter($search)); 13832cd2800SGreg Roach } 13932cd2800SGreg Roach 14032cd2800SGreg Roach /** 14132cd2800SGreg Roach * Search for individuals by name. 14232cd2800SGreg Roach * 143a7a24840SGreg Roach * @param Tree[] $trees 144a7a24840SGreg Roach * @param string[] $search 14532cd2800SGreg Roach * @param int $offset 14632cd2800SGreg Roach * @param int $limit 14732cd2800SGreg Roach * 14832cd2800SGreg Roach * @return Collection|Individual[] 14932cd2800SGreg Roach */ 150a7a24840SGreg Roach public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 15132cd2800SGreg Roach { 15232cd2800SGreg Roach $query = DB::table('individuals') 153a7a24840SGreg Roach ->join('name', function (JoinClause $join): void { 15432cd2800SGreg Roach $join 15532cd2800SGreg Roach ->on('name.n_file', '=', 'individuals.i_file') 156a7a24840SGreg Roach ->on('name.n_id', '=', 'individuals.i_id'); 15732cd2800SGreg Roach }) 158e84cf2deSGreg Roach ->orderBy('n_sort') 159c0804649SGreg Roach ->select(['individuals.*', 'n_sort', 'n_num']) 16032cd2800SGreg Roach ->distinct(); 16132cd2800SGreg Roach 162a7a24840SGreg Roach $this->whereTrees($query, 'i_file', $trees); 163a7a24840SGreg Roach $this->whereSearch($query, 'n_full', $search); 164a7a24840SGreg Roach 165a7a24840SGreg Roach return $this->paginateQuery($query, Individual::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 16632cd2800SGreg Roach } 16732cd2800SGreg Roach 16832cd2800SGreg Roach /** 16932cd2800SGreg Roach * Search for media objects. 17032cd2800SGreg Roach * 171a7a24840SGreg Roach * @param Tree[] $trees 172a7a24840SGreg Roach * @param string[] $search 17332cd2800SGreg Roach * @param int $offset 17432cd2800SGreg Roach * @param int $limit 17532cd2800SGreg Roach * 17632cd2800SGreg Roach * @return Collection|Media[] 17732cd2800SGreg Roach */ 178a7a24840SGreg Roach public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 17932cd2800SGreg Roach { 180a7a24840SGreg Roach $query = DB::table('media'); 18132cd2800SGreg Roach 182a7a24840SGreg Roach $this->whereTrees($query, 'media.m_file', $trees); 183a7a24840SGreg Roach $this->whereSearch($query, 'm_gedcom', $search); 184a7a24840SGreg Roach 185a7a24840SGreg Roach return $this->paginateQuery($query, Media::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 18632cd2800SGreg Roach } 18732cd2800SGreg Roach 18832cd2800SGreg Roach /** 18932cd2800SGreg Roach * Search for notes. 19032cd2800SGreg Roach * 191a7a24840SGreg Roach * @param Tree[] $trees 192a7a24840SGreg Roach * @param string[] $search 19332cd2800SGreg Roach * @param int $offset 19432cd2800SGreg Roach * @param int $limit 19532cd2800SGreg Roach * 19632cd2800SGreg Roach * @return Collection|Note[] 19732cd2800SGreg Roach */ 198a7a24840SGreg Roach public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 19932cd2800SGreg Roach { 20032cd2800SGreg Roach $query = DB::table('other') 201a7a24840SGreg Roach ->where('o_type', '=', 'NOTE'); 20232cd2800SGreg Roach 203a7a24840SGreg Roach $this->whereTrees($query, 'o_file', $trees); 204a7a24840SGreg Roach $this->whereSearch($query, 'o_gedcom', $search); 205a7a24840SGreg Roach 206a7a24840SGreg Roach return $this->paginateQuery($query, Note::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 20732cd2800SGreg Roach } 20832cd2800SGreg Roach 20932cd2800SGreg Roach /** 21032cd2800SGreg Roach * Search for repositories. 21132cd2800SGreg Roach * 212a7a24840SGreg Roach * @param Tree[] $trees 213a7a24840SGreg Roach * @param string[] $search 21432cd2800SGreg Roach * @param int $offset 21532cd2800SGreg Roach * @param int $limit 21632cd2800SGreg Roach * 21732cd2800SGreg Roach * @return Collection|Repository[] 21832cd2800SGreg Roach */ 219a7a24840SGreg Roach public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 22032cd2800SGreg Roach { 22132cd2800SGreg Roach $query = DB::table('other') 222a7a24840SGreg Roach ->where('o_type', '=', 'REPO'); 22332cd2800SGreg Roach 224a7a24840SGreg Roach $this->whereTrees($query, 'o_file', $trees); 225a7a24840SGreg Roach $this->whereSearch($query, 'o_gedcom', $search); 226a7a24840SGreg Roach 227a7a24840SGreg Roach return $this->paginateQuery($query, Repository::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 22832cd2800SGreg Roach } 22932cd2800SGreg Roach 23032cd2800SGreg Roach /** 231a7a24840SGreg Roach * Search for sources. 23232cd2800SGreg Roach * 233a7a24840SGreg Roach * @param Tree[] $trees 234a7a24840SGreg Roach * @param string[] $search 23532cd2800SGreg Roach * @param int $offset 23632cd2800SGreg Roach * @param int $limit 23732cd2800SGreg Roach * 23832cd2800SGreg Roach * @return Collection|Source[] 23932cd2800SGreg Roach */ 240a7a24840SGreg Roach public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 241a7a24840SGreg Roach { 242a7a24840SGreg Roach $query = DB::table('sources'); 243a7a24840SGreg Roach 244a7a24840SGreg Roach $this->whereTrees($query, 's_file', $trees); 245a7a24840SGreg Roach $this->whereSearch($query, 's_gedcom', $search); 246a7a24840SGreg Roach 247a7a24840SGreg Roach return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 248a7a24840SGreg Roach } 249a7a24840SGreg Roach 250a7a24840SGreg Roach /** 251a7a24840SGreg Roach * Search for sources by name. 252a7a24840SGreg Roach * 253a7a24840SGreg Roach * @param Tree[] $trees 254a7a24840SGreg Roach * @param string[] $search 255a7a24840SGreg Roach * @param int $offset 256a7a24840SGreg Roach * @param int $limit 257a7a24840SGreg Roach * 258a7a24840SGreg Roach * @return Collection|Source[] 259a7a24840SGreg Roach */ 260a7a24840SGreg Roach public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 26132cd2800SGreg Roach { 26232cd2800SGreg Roach $query = DB::table('sources') 263c0804649SGreg Roach ->orderBy('s_name'); 26432cd2800SGreg Roach 265a7a24840SGreg Roach $this->whereTrees($query, 's_file', $trees); 266a7a24840SGreg Roach $this->whereSearch($query, 's_name', $search); 267a7a24840SGreg Roach 268a7a24840SGreg Roach return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 26932cd2800SGreg Roach } 27032cd2800SGreg Roach 27132cd2800SGreg Roach /** 27232cd2800SGreg Roach * Search for submitters. 27332cd2800SGreg Roach * 274a7a24840SGreg Roach * @param Tree[] $trees 275a7a24840SGreg Roach * @param string[] $search 27632cd2800SGreg Roach * @param int $offset 27732cd2800SGreg Roach * @param int $limit 27832cd2800SGreg Roach * 27932cd2800SGreg Roach * @return Collection|GedcomRecord[] 28032cd2800SGreg Roach */ 281a7a24840SGreg Roach public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 28232cd2800SGreg Roach { 28332cd2800SGreg Roach $query = DB::table('other') 284a7a24840SGreg Roach ->where('o_type', '=', 'SUBM'); 28532cd2800SGreg Roach 286a7a24840SGreg Roach $this->whereTrees($query, 'o_file', $trees); 287a7a24840SGreg Roach $this->whereSearch($query, 'o_gedcom', $search); 288a7a24840SGreg Roach 289a7a24840SGreg Roach return $this->paginateQuery($query, GedcomRecord::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 29032cd2800SGreg Roach } 29132cd2800SGreg Roach 29232cd2800SGreg Roach /** 293b68caec6SGreg Roach * Search for places. 294b68caec6SGreg Roach * 295b68caec6SGreg Roach * @param Tree $tree 296b68caec6SGreg Roach * @param string $search 297b68caec6SGreg Roach * @param int $offset 298b68caec6SGreg Roach * @param int $limit 299b68caec6SGreg Roach * 300b68caec6SGreg Roach * @return Collection|Place[] 301b68caec6SGreg Roach */ 302b68caec6SGreg Roach public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 303b68caec6SGreg Roach { 304b68caec6SGreg Roach $query = DB::table('places AS p0') 305b68caec6SGreg Roach ->where('p0.p_file', '=', $tree->id()) 306b68caec6SGreg Roach ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 307b68caec6SGreg Roach ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 308b68caec6SGreg Roach ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 309b68caec6SGreg Roach ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 310b68caec6SGreg Roach ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 311b68caec6SGreg Roach ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 312b68caec6SGreg Roach ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 313b68caec6SGreg Roach ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 314b68caec6SGreg Roach ->orderBy('p0.p_place') 315b68caec6SGreg Roach ->orderBy('p1.p_place') 316b68caec6SGreg Roach ->orderBy('p2.p_place') 317b68caec6SGreg Roach ->orderBy('p3.p_place') 318b68caec6SGreg Roach ->orderBy('p4.p_place') 319b68caec6SGreg Roach ->orderBy('p5.p_place') 320b68caec6SGreg Roach ->orderBy('p6.p_place') 321b68caec6SGreg Roach ->orderBy('p7.p_place') 322b68caec6SGreg Roach ->orderBy('p8.p_place') 323b68caec6SGreg Roach ->select([ 324b68caec6SGreg Roach 'p0.p_place AS place0', 325b68caec6SGreg Roach 'p1.p_place AS place1', 326b68caec6SGreg Roach 'p2.p_place AS place2', 327b68caec6SGreg Roach 'p3.p_place AS place3', 328b68caec6SGreg Roach 'p4.p_place AS place4', 329b68caec6SGreg Roach 'p5.p_place AS place5', 330b68caec6SGreg Roach 'p6.p_place AS place6', 331b68caec6SGreg Roach 'p7.p_place AS place7', 332b68caec6SGreg Roach 'p8.p_place AS place8', 333b68caec6SGreg Roach ]); 334b68caec6SGreg Roach 335b68caec6SGreg Roach // Filter each level of the hierarchy. 336b68caec6SGreg Roach foreach (explode(',', $search, 9) as $level => $string) { 337b68caec6SGreg Roach $query->whereContains('p' . $level . '.p_place', $string); 338b68caec6SGreg Roach } 339b68caec6SGreg Roach 340b68caec6SGreg Roach $row_mapper = function (stdClass $row) use ($tree): Place { 341b68caec6SGreg Roach $place = implode(', ', array_filter((array) $row)); 342b68caec6SGreg Roach 343b68caec6SGreg Roach return new Place($place, $tree); 344b68caec6SGreg Roach }; 345b68caec6SGreg Roach 346a7a24840SGreg Roach $filter = function (): bool { 347a7a24840SGreg Roach return true; 348a7a24840SGreg Roach }; 349b68caec6SGreg Roach 350a7a24840SGreg Roach return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit); 351a7a24840SGreg Roach } 352b68caec6SGreg Roach 353b68caec6SGreg Roach /** 35432cd2800SGreg Roach * Paginate a search query. 35532cd2800SGreg Roach * 35632cd2800SGreg Roach * @param Builder $query Searches the database for the desired records. 35732cd2800SGreg Roach * @param Closure $row_mapper Converts a row from the query into a record. 358a7a24840SGreg Roach * @param Closure $row_filter 35932cd2800SGreg Roach * @param int $offset Skip this many rows. 36032cd2800SGreg Roach * @param int $limit Take this many rows. 36132cd2800SGreg Roach * 36232cd2800SGreg Roach * @return Collection 36332cd2800SGreg Roach */ 364a7a24840SGreg Roach private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection 36532cd2800SGreg Roach { 36632cd2800SGreg Roach $collection = new Collection(); 36732cd2800SGreg Roach 36832cd2800SGreg Roach foreach ($query->cursor() as $row) { 36932cd2800SGreg Roach $record = $row_mapper($row); 370b68caec6SGreg Roach // If the object has a method "canShow()", then use it to filter for privacy. 371a7a24840SGreg Roach if ($row_filter($record)) { 37232cd2800SGreg Roach if ($offset > 0) { 37332cd2800SGreg Roach $offset--; 37432cd2800SGreg Roach } else { 37532cd2800SGreg Roach if ($limit > 0) { 37632cd2800SGreg Roach $collection->push($record); 37732cd2800SGreg Roach } 37832cd2800SGreg Roach 37932cd2800SGreg Roach $limit--; 38032cd2800SGreg Roach 38132cd2800SGreg Roach if ($limit === 0) { 38232cd2800SGreg Roach break; 38332cd2800SGreg Roach } 38432cd2800SGreg Roach } 38532cd2800SGreg Roach } 38632cd2800SGreg Roach } 38732cd2800SGreg Roach 38832cd2800SGreg Roach return $collection; 38932cd2800SGreg Roach } 390a7a24840SGreg Roach 391a7a24840SGreg Roach /** 392a7a24840SGreg Roach * Apply search filters to a SQL query column. Apply collation rules to MySQL. 393a7a24840SGreg Roach * 394a7a24840SGreg Roach * @param Builder $query 395a7a24840SGreg Roach * @param Expression|string $field 396a7a24840SGreg Roach * @param string[] $search_terms 397a7a24840SGreg Roach */ 398a7a24840SGreg Roach private function whereSearch(Builder $query, $field, array $search_terms): void 399a7a24840SGreg Roach { 400a7a24840SGreg Roach if ($field instanceof Expression) { 401a7a24840SGreg Roach $field = $field->getValue(); 402a7a24840SGreg Roach } 403a7a24840SGreg Roach 404a7a24840SGreg Roach $field = DB::raw($field . ' /*! COLLATE ' . 'utf8_' . $this->locale->collation() . ' */'); 405a7a24840SGreg Roach 406a7a24840SGreg Roach foreach ($search_terms as $search_term) { 407a7a24840SGreg Roach $query->whereContains($field, $search_term); 408a7a24840SGreg Roach } 409a7a24840SGreg Roach } 410a7a24840SGreg Roach 411a7a24840SGreg Roach /** 412*2d686e68SGreg Roach * Apply soundex search filters to a SQL query column. 413*2d686e68SGreg Roach * 414*2d686e68SGreg Roach * @param Builder $query 415*2d686e68SGreg Roach * @param Expression|string $field 416*2d686e68SGreg Roach * @param string $soundex 417*2d686e68SGreg Roach */ 418*2d686e68SGreg Roach private function wherePhonetic(Builder $query, $field, string $soundex): void 419*2d686e68SGreg Roach { 420*2d686e68SGreg Roach if ($soundex !== '') { 421*2d686e68SGreg Roach $query->where(function (Builder $query) use ($soundex, $field): void { 422*2d686e68SGreg Roach foreach (explode(':', $soundex) as $sdx) { 423*2d686e68SGreg Roach $query->orWhere($field, 'LIKE', '%' . $sdx . '%'); 424*2d686e68SGreg Roach } 425*2d686e68SGreg Roach }); 426*2d686e68SGreg Roach } 427*2d686e68SGreg Roach } 428*2d686e68SGreg Roach 429*2d686e68SGreg Roach /** 430a7a24840SGreg Roach * @param Builder $query 431a7a24840SGreg Roach * @param string $tree_id_field 432a7a24840SGreg Roach * @param Tree[] $trees 433a7a24840SGreg Roach */ 434a7a24840SGreg Roach private function whereTrees(Builder $query, string $tree_id_field, array $trees): void 435a7a24840SGreg Roach { 436a7a24840SGreg Roach $tree_ids = array_map(function (Tree $tree) { 437a7a24840SGreg Roach return $tree->id(); 438a7a24840SGreg Roach }, $trees); 439a7a24840SGreg Roach 440a7a24840SGreg Roach $query->whereIn($tree_id_field, $tree_ids); 441a7a24840SGreg Roach } 442a7a24840SGreg Roach 443a7a24840SGreg Roach /** 444a7a24840SGreg Roach * A closure to filter records by privacy-filtered GEDCOM data. 445a7a24840SGreg Roach * 446a7a24840SGreg Roach * @param array $search_terms 447a7a24840SGreg Roach * 448a7a24840SGreg Roach * @return Closure 449a7a24840SGreg Roach */ 450a7a24840SGreg Roach private function rawGedcomFilter(array $search_terms): Closure 451a7a24840SGreg Roach { 452a7a24840SGreg Roach return function (GedcomRecord $record) use ($search_terms): bool { 453a7a24840SGreg Roach // Ignore non-genealogy fields 454a7a24840SGreg Roach $gedcom = preg_replace('/\n\d (?:_UID) .*/', '', $record->gedcom()); 455a7a24840SGreg Roach 456a7a24840SGreg Roach // Ignore matches in links 457a7a24840SGreg Roach $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom); 458a7a24840SGreg Roach 459a7a24840SGreg Roach // Re-apply the filtering 460a7a24840SGreg Roach foreach ($search_terms as $search_term) { 461a7a24840SGreg Roach if (mb_stripos($gedcom, $search_term) === false) { 462a7a24840SGreg Roach return false; 463a7a24840SGreg Roach } 464a7a24840SGreg Roach } 465a7a24840SGreg Roach 466a7a24840SGreg Roach return true; 467a7a24840SGreg Roach }; 468a7a24840SGreg Roach } 469*2d686e68SGreg Roach 470*2d686e68SGreg Roach /** 471*2d686e68SGreg Roach * @param string $soundex 472*2d686e68SGreg Roach * @param string $lastname 473*2d686e68SGreg Roach * @param string $firstname 474*2d686e68SGreg Roach * @param string $place 475*2d686e68SGreg Roach * @param Tree[] $search_trees 476*2d686e68SGreg Roach * 477*2d686e68SGreg Roach * @return Collection|Individual[] 478*2d686e68SGreg Roach */ 479*2d686e68SGreg Roach public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees) 480*2d686e68SGreg Roach { 481*2d686e68SGreg Roach switch ($soundex) { 482*2d686e68SGreg Roach default: 483*2d686e68SGreg Roach case 'Russell': 484*2d686e68SGreg Roach $givn_sdx = Soundex::russell($firstname); 485*2d686e68SGreg Roach $surn_sdx = Soundex::russell($lastname); 486*2d686e68SGreg Roach $plac_sdx = Soundex::russell($place); 487*2d686e68SGreg Roach $givn_field = 'n_soundex_givn_std'; 488*2d686e68SGreg Roach $surn_field = 'n_soundex_surn_std'; 489*2d686e68SGreg Roach $plac_field = 'p_std_soundex'; 490*2d686e68SGreg Roach break; 491*2d686e68SGreg Roach case 'DaitchM': 492*2d686e68SGreg Roach $givn_sdx = Soundex::daitchMokotoff($firstname); 493*2d686e68SGreg Roach $surn_sdx = Soundex::daitchMokotoff($lastname); 494*2d686e68SGreg Roach $plac_sdx = Soundex::daitchMokotoff($place); 495*2d686e68SGreg Roach $givn_field = 'n_soundex_givn_dm'; 496*2d686e68SGreg Roach $surn_field = 'n_soundex_surn_dm'; 497*2d686e68SGreg Roach $plac_field = 'p_dm_soundex'; 498*2d686e68SGreg Roach break; 499*2d686e68SGreg Roach } 500*2d686e68SGreg Roach 501*2d686e68SGreg Roach // Nothing to search for? Return nothing. 502*2d686e68SGreg Roach if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') { 503*2d686e68SGreg Roach return new Collection; 504*2d686e68SGreg Roach } 505*2d686e68SGreg Roach 506*2d686e68SGreg Roach $query = DB::table('individuals') 507*2d686e68SGreg Roach ->select(['individuals.*']) 508*2d686e68SGreg Roach ->distinct(); 509*2d686e68SGreg Roach 510*2d686e68SGreg Roach $this->whereTrees($query, 'i_file', $search_trees); 511*2d686e68SGreg Roach 512*2d686e68SGreg Roach if ($plac_sdx !== '') { 513*2d686e68SGreg Roach $query->join('placelinks', function (JoinClause $join): void { 514*2d686e68SGreg Roach $join 515*2d686e68SGreg Roach ->on('placelinks.pl_file', '=', 'individuals.i_file') 516*2d686e68SGreg Roach ->on('placelinks.pl_gid', '=', 'individuals.i_id'); 517*2d686e68SGreg Roach }); 518*2d686e68SGreg Roach $query->join('places', function (JoinClause $join): void { 519*2d686e68SGreg Roach $join 520*2d686e68SGreg Roach ->on('places.p_file', '=', 'placelinks.pl_file') 521*2d686e68SGreg Roach ->on('places.p_id', '=', 'placelinks.pl_p_id'); 522*2d686e68SGreg Roach }); 523*2d686e68SGreg Roach 524*2d686e68SGreg Roach $this->wherePhonetic($query, $plac_field, $plac_sdx); 525*2d686e68SGreg Roach } 526*2d686e68SGreg Roach 527*2d686e68SGreg Roach if ($givn_sdx !== '' || $surn_sdx !== '') { 528*2d686e68SGreg Roach $query->join('name', function (JoinClause $join): void { 529*2d686e68SGreg Roach $join 530*2d686e68SGreg Roach ->on('name.n_file', '=', 'individuals.i_file') 531*2d686e68SGreg Roach ->on('name.n_id', '=', 'individuals.i_id'); 532*2d686e68SGreg Roach }); 533*2d686e68SGreg Roach 534*2d686e68SGreg Roach $this->wherePhonetic($query, $givn_field, $givn_sdx); 535*2d686e68SGreg Roach $this->wherePhonetic($query, $surn_field, $surn_sdx); 536*2d686e68SGreg Roach } 537*2d686e68SGreg Roach 538*2d686e68SGreg Roach return $query 539*2d686e68SGreg Roach ->get() 540*2d686e68SGreg Roach ->map(Individual::rowMapper()) 541*2d686e68SGreg Roach ->filter(GedcomRecord::accessFilter()); 542*2d686e68SGreg Roach } 54332cd2800SGreg Roach} 544