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