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