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 $tree 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 if (empty($fields)) { 710 return true; 711 } 712 713 // Check for XXXX:PLAC fields, which were only partially matched by SQL 714 foreach ($fields as $field_name => $field_value) { 715 $regex_field_value = preg_quote($field_value, '/'); 716 717 $parts = preg_split('/:/', $field_name . '::::'); 718 719 if ($parts[1] === 'PLAC') { 720 // *:PLAC 721 if (preg_match('/\n\d PLAC .*' . $regex_field_value . '/i', $individual->gedcom())) { 722 continue; 723 } 724 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 725 // FAMS:*:PLAC 726 foreach ($individual->getSpouseFamilies() as $family) { 727 if (preg_match('/\n\d PLAC .*' . $regex_field_value . '/i', $family->gedcom())) { 728 continue; 729 } 730 } 731 } elseif ($parts[0] === 'FAMS') { 732 // e.g. searches for occupation, religion, note, etc. 733 foreach ($individual->getSpouseFamilies() as $family) { 734 if (preg_match('/\n1 ' . $parts[1] . ' .*' . $regex_field_value . '/i', $family->gedcom())) { 735 continue; 736 } 737 } 738 } elseif ($parts[1] === 'TYPE') { 739 // e.g. FACT:TYPE or EVEN:TYPE 740 if (preg_match('/\n1 ' . $parts[0] . '.*(\n2.*)*2 TYPE .*' . $regex_field_value . '/i', $individual->gedcom())) { 741 continue; 742 } 743 } else { 744 // e.g. searches for occupation, religion, note, etc. 745 if (preg_match('/\n1 ' . $parts[0] . ' .*' . $regex_field_value . '/i', $individual->gedcom())) { 746 continue; 747 } 748 } 749 750 return false; 751 } 752 753 return true; 754 }); 755 } 756 757 /** 758 * @param string $soundex 759 * @param string $lastname 760 * @param string $firstname 761 * @param string $place 762 * @param Tree[] $search_trees 763 * 764 * @return Collection|Individual[] 765 */ 766 public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees): Collection 767 { 768 switch ($soundex) { 769 default: 770 case 'Russell': 771 $givn_sdx = Soundex::russell($firstname); 772 $surn_sdx = Soundex::russell($lastname); 773 $plac_sdx = Soundex::russell($place); 774 $givn_field = 'n_soundex_givn_std'; 775 $surn_field = 'n_soundex_surn_std'; 776 $plac_field = 'p_std_soundex'; 777 break; 778 case 'DaitchM': 779 $givn_sdx = Soundex::daitchMokotoff($firstname); 780 $surn_sdx = Soundex::daitchMokotoff($lastname); 781 $plac_sdx = Soundex::daitchMokotoff($place); 782 $givn_field = 'n_soundex_givn_dm'; 783 $surn_field = 'n_soundex_surn_dm'; 784 $plac_field = 'p_dm_soundex'; 785 break; 786 } 787 788 // Nothing to search for? Return nothing. 789 if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') { 790 return new Collection; 791 } 792 793 $query = DB::table('individuals') 794 ->select(['individuals.*']) 795 ->distinct(); 796 797 $this->whereTrees($query, 'i_file', $search_trees); 798 799 if ($plac_sdx !== '') { 800 $query->join('placelinks', function (JoinClause $join): void { 801 $join 802 ->on('placelinks.pl_file', '=', 'individuals.i_file') 803 ->on('placelinks.pl_gid', '=', 'individuals.i_id'); 804 }); 805 $query->join('places', function (JoinClause $join): void { 806 $join 807 ->on('places.p_file', '=', 'placelinks.pl_file') 808 ->on('places.p_id', '=', 'placelinks.pl_p_id'); 809 }); 810 811 $this->wherePhonetic($query, $plac_field, $plac_sdx); 812 } 813 814 if ($givn_sdx !== '' || $surn_sdx !== '') { 815 $query->join('name', function (JoinClause $join): void { 816 $join 817 ->on('name.n_file', '=', 'individuals.i_file') 818 ->on('name.n_id', '=', 'individuals.i_id'); 819 }); 820 821 $this->wherePhonetic($query, $givn_field, $givn_sdx); 822 $this->wherePhonetic($query, $surn_field, $surn_sdx); 823 } 824 825 return $query 826 ->get() 827 ->map(Individual::rowMapper()) 828 ->filter(GedcomRecord::accessFilter()); 829 } 830 831 /** 832 * Paginate a search query. 833 * 834 * @param Builder $query Searches the database for the desired records. 835 * @param Closure $row_mapper Converts a row from the query into a record. 836 * @param Closure $row_filter 837 * @param int $offset Skip this many rows. 838 * @param int $limit Take this many rows. 839 * 840 * @return Collection 841 */ 842 private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection 843 { 844 $collection = new Collection(); 845 846 foreach ($query->cursor() as $row) { 847 $record = $row_mapper($row); 848 // If the object has a method "canShow()", then use it to filter for privacy. 849 if ($row_filter($record)) { 850 if ($offset > 0) { 851 $offset--; 852 } else { 853 if ($limit > 0) { 854 $collection->push($record); 855 } 856 857 $limit--; 858 859 if ($limit === 0) { 860 break; 861 } 862 } 863 } 864 } 865 866 return $collection; 867 } 868 869 /** 870 * Apply search filters to a SQL query column. Apply collation rules to MySQL. 871 * 872 * @param Builder $query 873 * @param Expression|string $field 874 * @param string[] $search_terms 875 */ 876 private function whereSearch(Builder $query, $field, array $search_terms): void 877 { 878 if ($field instanceof Expression) { 879 $field = $field->getValue(); 880 } 881 882 $field = DB::raw($field . ' /*! COLLATE ' . 'utf8_' . $this->locale->collation() . ' */'); 883 884 foreach ($search_terms as $search_term) { 885 $query->whereContains($field, $search_term); 886 } 887 } 888 889 /** 890 * Apply soundex search filters to a SQL query column. 891 * 892 * @param Builder $query 893 * @param Expression|string $field 894 * @param string $soundex 895 */ 896 private function wherePhonetic(Builder $query, $field, string $soundex): void 897 { 898 if ($soundex !== '') { 899 $query->where(function (Builder $query) use ($soundex, $field): void { 900 foreach (explode(':', $soundex) as $sdx) { 901 $query->orWhere($field, 'LIKE', '%' . $sdx . '%'); 902 } 903 }); 904 } 905 } 906 907 /** 908 * @param Builder $query 909 * @param string $tree_id_field 910 * @param Tree[] $trees 911 */ 912 private function whereTrees(Builder $query, string $tree_id_field, array $trees): void 913 { 914 $tree_ids = array_map(function (Tree $tree) { 915 return $tree->id(); 916 }, $trees); 917 918 $query->whereIn($tree_id_field, $tree_ids); 919 } 920 921 /** 922 * A closure to filter records by privacy-filtered GEDCOM data. 923 * 924 * @param array $search_terms 925 * 926 * @return Closure 927 */ 928 private function rawGedcomFilter(array $search_terms): Closure 929 { 930 return function (GedcomRecord $record) use ($search_terms): bool { 931 // Ignore non-genealogy fields 932 $gedcom = preg_replace('/\n\d (?:_UID) .*/', '', $record->gedcom()); 933 934 // Ignore matches in links 935 $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom); 936 937 // Re-apply the filtering 938 foreach ($search_terms as $search_term) { 939 if (mb_stripos($gedcom, $search_term) === false) { 940 return false; 941 } 942 } 943 944 return true; 945 }; 946 } 947} 948