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