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