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