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