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