1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2022 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 <https://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Module; 21 22use Fisharebest\Webtrees\Auth; 23use Fisharebest\Webtrees\Contracts\UserInterface; 24use Fisharebest\Webtrees\Family; 25use Fisharebest\Webtrees\I18N; 26use Fisharebest\Webtrees\Individual; 27use Fisharebest\Webtrees\Registry; 28use Fisharebest\Webtrees\Session; 29use Fisharebest\Webtrees\Tree; 30use Fisharebest\Webtrees\Validator; 31use Illuminate\Database\Capsule\Manager as DB; 32use Illuminate\Database\Query\Builder; 33use Illuminate\Database\Query\Expression; 34use Illuminate\Database\Query\JoinClause; 35use Illuminate\Support\Collection; 36use Psr\Http\Message\ResponseInterface; 37use Psr\Http\Message\ServerRequestInterface; 38use Psr\Http\Server\RequestHandlerInterface; 39 40use function app; 41use function array_filter; 42use function array_keys; 43use function array_sum; 44use function assert; 45use function e; 46use function implode; 47use function ob_get_clean; 48use function ob_start; 49use function route; 50use function uksort; 51use function view; 52 53use const ARRAY_FILTER_USE_KEY; 54 55/** 56 * Class IndividualListModule 57 */ 58class IndividualListModule extends AbstractModule implements ModuleListInterface, RequestHandlerInterface 59{ 60 use ModuleListTrait; 61 62 protected const ROUTE_URL = '/tree/{tree}/individual-list'; 63 64 /** 65 * Initialization. 66 * 67 * @return void 68 */ 69 public function boot(): void 70 { 71 Registry::routeFactory()->routeMap() 72 ->get(static::class, static::ROUTE_URL, $this); 73 } 74 75 /** 76 * How should this module be identified in the control panel, etc.? 77 * 78 * @return string 79 */ 80 public function title(): string 81 { 82 /* I18N: Name of a module/list */ 83 return I18N::translate('Individuals'); 84 } 85 86 /** 87 * A sentence describing what this module does. 88 * 89 * @return string 90 */ 91 public function description(): string 92 { 93 /* I18N: Description of the “Individuals” module */ 94 return I18N::translate('A list of individuals.'); 95 } 96 97 /** 98 * CSS class for the URL. 99 * 100 * @return string 101 */ 102 public function listMenuClass(): string 103 { 104 return 'menu-list-indi'; 105 } 106 107 /** 108 * @param Tree $tree 109 * @param array<bool|int|string|array<string>|null> $parameters 110 * 111 * @return string 112 */ 113 public function listUrl(Tree $tree, array $parameters = []): string 114 { 115 $request = app(ServerRequestInterface::class); 116 assert($request instanceof ServerRequestInterface); 117 118 $xref = Validator::attributes($request)->isXref()->string('xref', ''); 119 120 if ($xref !== '') { 121 $individual = Registry::individualFactory()->make($xref, $tree); 122 123 if ($individual instanceof Individual && $individual->canShow()) { 124 $primary_name = $individual->getPrimaryName(); 125 126 $parameters['surname'] ??= $individual->getAllNames()[$primary_name]['surn'] ?? null; 127 } 128 } 129 130 $parameters['tree'] = $tree->name(); 131 132 return route(static::class, $parameters); 133 } 134 135 /** 136 * @return array<string> 137 */ 138 public function listUrlAttributes(): array 139 { 140 return []; 141 } 142 143 /** 144 * @param ServerRequestInterface $request 145 * 146 * @return ResponseInterface 147 */ 148 public function handle(ServerRequestInterface $request): ResponseInterface 149 { 150 $tree = Validator::attributes($request)->tree(); 151 $user = Validator::attributes($request)->user(); 152 153 Auth::checkComponentAccess($this, ModuleListInterface::class, $tree, $user); 154 155 $surname_param = Validator::queryParams($request)->string('surname', ''); 156 $surname = I18N::strtoupper(I18N::language()->normalize($surname_param)); 157 158 $params = [ 159 'alpha' => Validator::queryParams($request)->string('alpha', ''), 160 'falpha' => Validator::queryParams($request)->string('falpha', ''), 161 'show' => Validator::queryParams($request)->string('show', 'surn'), 162 'show_all' => Validator::queryParams($request)->string('show_all', 'no'), 163 'show_all_firstnames' => Validator::queryParams($request)->string('show_all_firstnames', 'no'), 164 'show_marnm' => Validator::queryParams($request)->string('show_marnm', ''), 165 'surname' => $surname, 166 ]; 167 168 if ($surname_param !== $surname) { 169 return Registry::responseFactory()->redirectUrl($this->listUrl($tree, $params)); 170 } 171 172 return $this->createResponse($tree, $user, $params, false); 173 } 174 175 /** 176 * @param Tree $tree 177 * @param UserInterface $user 178 * @param array<string> $params 179 * @param bool $families 180 * 181 * @return ResponseInterface 182 */ 183 protected function createResponse(Tree $tree, UserInterface $user, array $params, bool $families): ResponseInterface 184 { 185 // We show three different lists: initials, surnames and individuals 186 187 // All surnames beginning with this letter, where "@" is unknown and "," is none 188 $alpha = $params['alpha']; 189 190 // All individuals with this surname 191 $surname = $params['surname']; 192 193 // All individuals 194 $show_all = $params['show_all'] === 'yes'; 195 196 // Include/exclude married names 197 $show_marnm = $params['show_marnm']; 198 199 // What type of list to display, if any 200 $show = $params['show']; 201 202 // Break long lists down by given name 203 $show_all_firstnames = $params['show_all_firstnames'] === 'yes'; 204 205 // All first names beginning with this letter where "@" is unknown 206 $falpha = $params['falpha']; 207 208 // Make sure parameters are consistent with each other. 209 if ($show_all_firstnames) { 210 $falpha = ''; 211 } 212 213 if ($show_all) { 214 $alpha = ''; 215 $surname = ''; 216 } 217 218 if ($surname !== '') { 219 $alpha = I18N::language()->initialLetter($surname); 220 } 221 222 $all_surnames = $this->allSurnames($tree, $show_marnm === 'yes', $families); 223 $surname_initials = $this->surnameInitials($all_surnames); 224 225 switch ($show_marnm) { 226 case 'no': 227 case 'yes': 228 $user->setPreference($families ? 'family-list-marnm' : 'individual-list-marnm', $show_marnm); 229 break; 230 default: 231 $show_marnm = $user->getPreference($families ? 'family-list-marnm' : 'individual-list-marnm'); 232 } 233 234 // Make sure selections are consistent. 235 // i.e. can’t specify show_all and surname at the same time. 236 if ($show_all) { 237 if ($show_all_firstnames) { 238 $legend = I18N::translate('All'); 239 $params = ['tree' => $tree->name(), 'show_all' => 'yes']; 240 $show = 'indi'; 241 } elseif ($falpha !== '') { 242 $legend = I18N::translate('All') . ', ' . e($falpha) . '…'; 243 $params = ['tree' => $tree->name(), 'show_all' => 'yes']; 244 $show = 'indi'; 245 } else { 246 $legend = I18N::translate('All'); 247 $params = ['tree' => $tree->name(), 'show_all' => 'yes']; 248 } 249 } elseif ($surname !== '') { 250 $show_all = false; 251 if ($surname === Individual::NOMEN_NESCIO) { 252 $legend = I18N::translateContext('Unknown surname', '…'); 253 $show = 'indi'; // The surname list makes no sense with only one surname. 254 } else { 255 // The surname parameter is a root/canonical form. Display the actual surnames found. 256 $variants = array_keys($all_surnames[$surname] ?? [$surname => $surname]); 257 usort($variants, I18N::comparator()); 258 $variants = array_map(static fn (string $x): string => $x === '' ? I18N::translate('No surname') : $x, $variants); 259 $legend = implode('/', $variants); 260 $show = 'indi'; // The surname list makes no sense with only one surname. 261 } 262 $params = ['tree' => $tree->name(), 'surname' => $surname, 'falpha' => $falpha]; 263 switch ($falpha) { 264 case '': 265 break; 266 case '@': 267 $legend .= ', ' . I18N::translateContext('Unknown given name', '…'); 268 break; 269 default: 270 $legend .= ', ' . e($falpha) . '…'; 271 break; 272 } 273 } elseif ($alpha === '@') { 274 $show_all = false; 275 $legend = I18N::translateContext('Unknown surname', '…'); 276 $params = ['alpha' => $alpha, 'tree' => $tree->name()]; 277 $surname = Individual::NOMEN_NESCIO; 278 $show = 'indi'; // SURN list makes no sense here 279 } elseif ($alpha === ',') { 280 $show_all = false; 281 $legend = I18N::translate('No surname'); 282 $params = ['alpha' => $alpha, 'tree' => $tree->name()]; 283 $show = 'indi'; // SURN list makes no sense here 284 } elseif ($alpha !== '') { 285 $show_all = false; 286 $legend = e($alpha) . '…'; 287 $params = ['alpha' => $alpha, 'tree' => $tree->name()]; 288 } else { 289 $show_all = false; 290 $legend = '…'; 291 $params = ['tree' => $tree->name()]; 292 $show = 'none'; // Don't show lists until something is chosen 293 } 294 $legend = '<bdi>' . $legend . '</bdi>'; 295 296 if ($families) { 297 $title = I18N::translate('Families') . ' — ' . $legend; 298 } else { 299 $title = I18N::translate('Individuals') . ' — ' . $legend; 300 } 301 302 ob_start(); ?> 303 <div class="d-flex flex-column wt-page-options wt-page-options-individual-list d-print-none"> 304 <ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-surname"> 305 306 <?php foreach ($surname_initials as $letter => $count) : ?> 307 <li class="wt-initials-list-item d-flex"> 308 <?php if ($count > 0) : ?> 309 <a href="<?= e($this->listUrl($tree, ['alpha' => $letter, 'tree' => $tree->name()])) ?>" class="wt-initial px-1<?= $letter === $alpha ? ' active' : '' ?> '" title="<?= I18N::number($count) ?>"><?= $this->displaySurnameInitial((string) $letter) ?></a> 310 <?php else : ?> 311 <span class="wt-initial px-1 text-muted"><?= $this->displaySurnameInitial((string) $letter) ?></span> 312 313 <?php endif ?> 314 </li> 315 <?php endforeach ?> 316 317 <?php if (Session::has('initiated')) : ?> 318 <!-- Search spiders don't get the "show all" option as the other links give them everything. --> 319 <li class="wt-initials-list-item d-flex"> 320 <a class="wt-initial px-1<?= $show_all ? ' active' : '' ?>" href="<?= e($this->listUrl($tree, ['show_all' => 'yes'] + $params)) ?>"><?= I18N::translate('All') ?></a> 321 </li> 322 <?php endif ?> 323 </ul> 324 325 <!-- Search spiders don't get an option to show/hide the surname sublists, nor does it make sense on the all/unknown/surname views --> 326 <?php if ($show !== 'none' && Session::has('initiated')) : ?> 327 <?php if ($show_marnm === 'yes') : ?> 328 <p> 329 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'no'] + $params)) ?>"> 330 <?= I18N::translate('Exclude individuals with “%s” as a married name', $legend) ?> 331 </a> 332 </p> 333 <?php else : ?> 334 <p> 335 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'yes'] + $params)) ?>"> 336 <?= I18N::translate('Include individuals with “%s” as a married name', $legend) ?> 337 </a> 338 </p> 339 <?php endif ?> 340 341 <?php if ($alpha !== '@' && $alpha !== ',' && $surname === '') : ?> 342 <?php if ($show === 'surn') : ?> 343 <p> 344 <a href="<?= e($this->listUrl($tree, ['show' => 'indi', 'show_marnm' => 'no'] + $params)) ?>"> 345 <?= I18N::translate('Show the list of individuals') ?> 346 </a> 347 </p> 348 <?php else : ?> 349 <p> 350 <a href="<?= e($this->listUrl($tree, ['show' => 'surn', 'show_marnm' => 'no'] + $params)) ?>"> 351 <?= I18N::translate('Show the list of surnames') ?> 352 </a> 353 </p> 354 <?php endif ?> 355 <?php endif ?> 356 <?php endif ?> 357 </div> 358 359 <div class="wt-page-content"> 360 <?php 361 if ($show === 'indi' || $show === 'surn') { 362 switch ($alpha) { 363 case '@': 364 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === Individual::NOMEN_NESCIO, ARRAY_FILTER_USE_KEY); 365 break; 366 case ',': 367 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === '', ARRAY_FILTER_USE_KEY); 368 break; 369 case '': 370 if ($show_all) { 371 $surns = array_filter($all_surnames, static fn (string $x): bool => $x !== '' && $x !== Individual::NOMEN_NESCIO, ARRAY_FILTER_USE_KEY); 372 } else { 373 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === $surname, ARRAY_FILTER_USE_KEY); 374 } 375 break; 376 default: 377 if ($surname === '') { 378 $surns = array_filter($all_surnames, static fn (string $x): bool => I18N::language()->initialLetter($x) === $alpha, ARRAY_FILTER_USE_KEY); 379 } else { 380 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === $surname, ARRAY_FILTER_USE_KEY); 381 } 382 break; 383 } 384 385 if ($show === 'surn') { 386 // Show the surname list 387 switch ($tree->getPreference('SURNAME_LIST_STYLE')) { 388 case 'style1': 389 echo view('lists/surnames-column-list', [ 390 'module' => $this, 391 'surnames' => $surns, 392 'totals' => true, 393 'tree' => $tree, 394 ]); 395 break; 396 case 'style3': 397 echo view('lists/surnames-tag-cloud', [ 398 'module' => $this, 399 'surnames' => $surns, 400 'totals' => true, 401 'tree' => $tree, 402 ]); 403 break; 404 case 'style2': 405 default: 406 echo view('lists/surnames-table', [ 407 'families' => $families, 408 'module' => $this, 409 'order' => [[0, 'asc']], 410 'surnames' => $surns, 411 'tree' => $tree, 412 ]); 413 break; 414 } 415 } else { 416 // Show the list 417 $count = array_sum(array_map(static fn (array $x): int => array_sum($x), $surns)); 418 419 // Don't sublist short lists. 420 if ($count < $tree->getPreference('SUBLIST_TRIGGER_I')) { 421 $falpha = ''; 422 } else { 423 $givn_initials = $this->givenNameInitials($tree, array_keys($surns), $show_marnm === 'yes', $families); 424 // Break long lists by initial letter of given name 425 if ($surname !== '' || $show_all) { 426 if (!$show_all) { 427 echo '<h2 class="wt-page-title">', I18N::translate('Individuals with surname %s', $legend), '</h2>'; 428 } 429 // Don't show the list until we have some filter criteria 430 $show = $falpha !== '' || $show_all_firstnames ? 'indi' : 'none'; 431 echo '<ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-given-names">'; 432 foreach ($givn_initials as $givn_initial => $given_count) { 433 echo '<li class="wt-initials-list-item d-flex">'; 434 if ($given_count > 0) { 435 if ($show === 'indi' && $givn_initial === $falpha && !$show_all_firstnames) { 436 echo '<a class="wt-initial px-1 active" href="' . e($this->listUrl($tree, ['falpha' => $givn_initial] + $params)) . '" title="' . I18N::number($given_count) . '">' . $this->displayGivenNameInitial((string) $givn_initial) . '</a>'; 437 } else { 438 echo '<a class="wt-initial px-1" href="' . e($this->listUrl($tree, ['falpha' => $givn_initial] + $params)) . '" title="' . I18N::number($given_count) . '">' . $this->displayGivenNameInitial((string) $givn_initial) . '</a>'; 439 } 440 } else { 441 echo '<span class="wt-initial px-1 text-muted">' . $this->displayGivenNameInitial((string) $givn_initial) . '</span>'; 442 } 443 echo '</li>'; 444 } 445 // Search spiders don't get the "show all" option as the other links give them everything. 446 if (Session::has('initiated')) { 447 echo '<li class="wt-initials-list-item d-flex">'; 448 if ($show_all_firstnames) { 449 echo '<span class="wt-initial px-1 active">' . I18N::translate('All') . '</span>'; 450 } else { 451 echo '<a class="wt-initial px-1" href="' . e($this->listUrl($tree, ['show_all_firstnames' => 'yes'] + $params)) . '" title="' . I18N::number($count) . '">' . I18N::translate('All') . '</a>'; 452 } 453 echo '</li>'; 454 } 455 echo '</ul>'; 456 } 457 } 458 if ($show === 'indi') { 459 if ($families) { 460 echo view('lists/families-table', [ 461 'families' => $this->families($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes'), 462 'tree' => $tree, 463 ]); 464 } else { 465 echo view('lists/individuals-table', [ 466 'individuals' => $this->individuals($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes', false), 467 'sosa' => false, 468 'tree' => $tree, 469 ]); 470 } 471 } 472 } 473 } ?> 474 </div> 475 <?php 476 477 $html = ob_get_clean(); 478 479 return $this->viewResponse('modules/individual-list/page', [ 480 'content' => $html, 481 'title' => $title, 482 'tree' => $tree, 483 ]); 484 } 485 486 /** 487 * Some initial letters have a special meaning 488 * 489 * @param string $initial 490 * 491 * @return string 492 */ 493 protected function displayGivenNameInitial(string $initial): string 494 { 495 if ($initial === '@') { 496 return I18N::translateContext('Unknown given name', '…'); 497 } 498 499 return e($initial); 500 } 501 502 /** 503 * Some initial letters have a special meaning 504 * 505 * @param string $initial 506 * 507 * @return string 508 */ 509 protected function displaySurnameInitial(string $initial): string 510 { 511 if ($initial === '@') { 512 return I18N::translateContext('Unknown surname', '…'); 513 } 514 515 if ($initial === ',') { 516 return I18N::translate('No surname'); 517 } 518 519 return e($initial); 520 } 521 522 /** 523 * Restrict a query to individuals that are a spouse in a family record. 524 * 525 * @param bool $fams 526 * @param Builder $query 527 */ 528 protected function whereFamily(bool $fams, Builder $query): void 529 { 530 if ($fams) { 531 $query->join('link', static function (JoinClause $join): void { 532 $join 533 ->on('l_from', '=', 'n_id') 534 ->on('l_file', '=', 'n_file') 535 ->where('l_type', '=', 'FAMS'); 536 }); 537 } 538 } 539 540 /** 541 * Restrict a query to include/exclude married names. 542 * 543 * @param bool $marnm 544 * @param Builder $query 545 */ 546 protected function whereMarriedName(bool $marnm, Builder $query): void 547 { 548 if (!$marnm) { 549 $query->where('n_type', '<>', '_MARNM'); 550 } 551 } 552 553 /** 554 * Get a count of individuals with each initial letter 555 * 556 * @param Tree $tree 557 * @param array<string> $surns if set, only consider people with this surname 558 * @param bool $marnm if set, include married names 559 * @param bool $fams if set, only consider individuals with FAMS records 560 * 561 * @return array<int> 562 */ 563 public function givenNameInitials(Tree $tree, array $surns, bool $marnm, bool $fams): array 564 { 565 $initials = []; 566 567 // Ensure our own language comes before others. 568 foreach (I18N::language()->alphabet() as $initial) { 569 $initials[$initial] = 0; 570 } 571 572 $query = DB::table('name') 573 ->where('n_file', '=', $tree->id()); 574 575 $this->whereFamily($fams, $query); 576 $this->whereMarriedName($marnm, $query); 577 578 if ($surns !== []) { 579 $query->whereIn('n_surn', $surns); 580 } 581 582 $query 583 ->select($this->binaryColumn('n_givn', 'n_givn'), new Expression('COUNT(*) AS count')) 584 ->groupBy([$this->binaryColumn('n_givn')]); 585 586 foreach ($query->get() as $row) { 587 $initial = I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)); 588 $initials[$initial] ??= 0; 589 $initials[$initial] += (int) $row->count; 590 } 591 592 $count_unknown = $initials['@'] ?? 0; 593 594 if ($count_unknown > 0) { 595 unset($initials['@']); 596 $initials['@'] = $count_unknown; 597 } 598 599 return $initials; 600 } 601 602 /** 603 * Get a count of all surnames and variants. 604 * 605 * @param Tree $tree 606 * @param bool $marnm if set, include married names 607 * @param bool $fams if set, only consider individuals with FAMS records 608 * 609 * @return array<array<int>> 610 */ 611 protected function allSurnames(Tree $tree, bool $marnm, bool $fams): array 612 { 613 $query = DB::table('name') 614 ->where('n_file', '=', $tree->id()) 615 ->whereNotNull('n_surn') // Filters old records for sources, repositories, etc. 616 ->whereNotNull('n_surname') 617 ->select([ 618 $this->binaryColumn('n_surn', 'n_surn'), 619 $this->binaryColumn('n_surname', 'n_surname'), 620 new Expression('COUNT(*) AS total'), 621 ]); 622 623 $this->whereFamily($fams, $query); 624 $this->whereMarriedName($marnm, $query); 625 626 $query->groupBy([ 627 $this->binaryColumn('n_surn'), 628 $this->binaryColumn('n_surname'), 629 ]); 630 631 /** @var array<array<int>> $list */ 632 $list = []; 633 634 foreach ($query->get() as $row) { 635 $row->n_surn = $row->n_surn === '' ? $row->n_surname : $row->n_surn; 636 $row->n_surn = I18N::strtoupper(I18N::language()->normalize($row->n_surn)); 637 638 $list[$row->n_surn][$row->n_surname] ??= 0; 639 $list[$row->n_surn][$row->n_surname] += (int) $row->total; 640 } 641 642 uksort($list, I18N::comparator()); 643 644 return $list; 645 } 646 647 /** 648 * Extract initial letters and counts for all surnames. 649 * 650 * @param array<array<int>> $all_surnames 651 * 652 * @return array<int> 653 */ 654 protected function surnameInitials(array $all_surnames): array 655 { 656 $initials = []; 657 658 // Ensure our own language comes before others. 659 foreach (I18N::language()->alphabet() as $initial) { 660 $initials[$initial] = 0; 661 } 662 663 foreach ($all_surnames as $surn => $surnames) { 664 $initial = I18N::language()->initialLetter((string) $surn); 665 666 $initials[$initial] ??= 0; 667 $initials[$initial] += array_sum($surnames); 668 } 669 670 // Move specials to the end 671 $count_none = $initials[''] ?? 0; 672 673 if ($count_none > 0) { 674 unset($initials['']); 675 $initials[','] = $count_none; 676 } 677 678 $count_unknown = $initials['@'] ?? 0; 679 680 if ($count_unknown > 0) { 681 unset($initials['@']); 682 $initials['@'] = $count_unknown; 683 } 684 685 return $initials; 686 } 687 688 /** 689 * Fetch a list of individuals with specified names 690 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 691 * To search for names with no surnames, use $salpha="," 692 * 693 * @param Tree $tree 694 * @param string $surname if set, only fetch people with this n_surn 695 * @param array<string> $surnames if set, only fetch people with this n_surname 696 * @param string $galpha if set, only fetch given names starting with this letter 697 * @param bool $marnm if set, include married names 698 * @param bool $fams if set, only fetch individuals with FAMS records 699 * 700 * @return Collection<int,Individual> 701 */ 702 protected function individuals(Tree $tree, string $surname, array $surnames, string $galpha, bool $marnm, bool $fams): Collection 703 { 704 $query = DB::table('individuals') 705 ->join('name', static function (JoinClause $join): void { 706 $join 707 ->on('n_id', '=', 'i_id') 708 ->on('n_file', '=', 'i_file'); 709 }) 710 ->where('i_file', '=', $tree->id()) 711 ->select(['i_id AS xref', 'i_gedcom AS gedcom', 'n_givn', 'n_surn']); 712 713 $this->whereFamily($fams, $query); 714 $this->whereMarriedName($marnm, $query); 715 716 if ($surnames === []) { 717 // SURN, with no surname 718 $query->where('n_surn', '=', $surname); 719 } else { 720 $query->whereIn($this->binaryColumn('n_surname'), $surnames); 721 } 722 723 $query 724 ->orderBy(new Expression("CASE n_surn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 725 ->orderBy('n_surn') 726 ->orderBy(new Expression("CASE n_givn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 727 ->orderBy('n_givn'); 728 729 $individuals = new Collection(); 730 731 foreach ($query->get() as $row) { 732 $individual = Registry::individualFactory()->make($row->xref, $tree, $row->gedcom); 733 assert($individual instanceof Individual); 734 735 // The name from the database may be private - check the filtered list... 736 foreach ($individual->getAllNames() as $n => $name) { 737 if ($name['givn'] === $row->n_givn && $name['surn'] === $row->n_surn) { 738 if ($galpha === '' || I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)) === $galpha) { 739 $individual->setPrimaryName($n); 740 // We need to clone $individual, as we may have multiple references to the 741 // same individual in this list, and the "primary name" would otherwise 742 // be shared amongst all of them. 743 $individuals->push(clone $individual); 744 break; 745 } 746 } 747 } 748 } 749 750 return $individuals; 751 } 752 753 /** 754 * Fetch a list of families with specified names 755 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 756 * To search for names with no surnames, use $salpha="," 757 * 758 * @param Tree $tree 759 * @param string $surname if set, only fetch people with this n_surn 760 * @param array<string> $surnames if set, only fetch people with this n_surname 761 * @param string $galpha if set, only fetch given names starting with this letter 762 * @param bool $marnm if set, include married names 763 * 764 * @return Collection<int,Family> 765 */ 766 protected function families(Tree $tree, string $surname, array $surnames, string $galpha, bool $marnm): Collection 767 { 768 $families = new Collection(); 769 770 foreach ($this->individuals($tree, $surname, $surnames, $galpha, $marnm, true) as $indi) { 771 foreach ($indi->spouseFamilies() as $family) { 772 $families->push($family); 773 } 774 } 775 776 return $families->unique(); 777 } 778 779 /** 780 * This module assumes the database will use binary collation on the name columns. 781 * Until we convert MySQL databases to use utf8_bin, we need to do this at run-time. 782 * 783 * @param string $column 784 * @param string|null $alias 785 * 786 * @return Expression 787 */ 788 private function binaryColumn(string $column, string $alias = null): Expression 789 { 790 if (DB::connection()->getDriverName() === 'mysql') { 791 $sql = 'CAST(' . $column . ' AS binary)'; 792 } else { 793 $sql = $column; 794 } 795 796 if ($alias !== null) { 797 $sql .= ' AS ' . $alias; 798 } 799 800 return new Expression($sql); 801 } 802} 803