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 array_filter; 42use function array_keys; 43use function array_map; 44use function array_merge; 45use function array_sum; 46use function array_values; 47use function assert; 48use function e; 49use function implode; 50use function ob_get_clean; 51use function ob_start; 52use function route; 53use function uksort; 54use function usort; 55use function view; 56 57use const ARRAY_FILTER_USE_KEY; 58 59/** 60 * Class IndividualListModule 61 */ 62class IndividualListModule extends AbstractModule implements ModuleListInterface, RequestHandlerInterface 63{ 64 use ModuleListTrait; 65 66 protected const ROUTE_URL = '/tree/{tree}/individual-list'; 67 68 /** 69 * Initialization. 70 * 71 * @return void 72 */ 73 public function boot(): void 74 { 75 Registry::routeFactory()->routeMap() 76 ->get(static::class, static::ROUTE_URL, $this); 77 } 78 79 /** 80 * How should this module be identified in the control panel, etc.? 81 * 82 * @return string 83 */ 84 public function title(): string 85 { 86 /* I18N: Name of a module/list */ 87 return I18N::translate('Individuals'); 88 } 89 90 /** 91 * A sentence describing what this module does. 92 * 93 * @return string 94 */ 95 public function description(): string 96 { 97 /* I18N: Description of the “Individuals” module */ 98 return I18N::translate('A list of individuals.'); 99 } 100 101 /** 102 * CSS class for the URL. 103 * 104 * @return string 105 */ 106 public function listMenuClass(): string 107 { 108 return 'menu-list-indi'; 109 } 110 111 /** 112 * @param Tree $tree 113 * @param array<bool|int|string|array<string>|null> $parameters 114 * 115 * @return string 116 */ 117 public function listUrl(Tree $tree, array $parameters = []): string 118 { 119 $request = Registry::container()->get(ServerRequestInterface::class); 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'] ??= $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), StatusCodeInterface::STATUS_MOVED_PERMANENTLY); 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 <div class="d-flex flex-column wt-page-options wt-page-options-individual-list d-print-none"> 306 <ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-surname"> 307 308 <?php foreach ($surname_initials as $letter => $count) : ?> 309 <li class="wt-initials-list-item d-flex"> 310 <?php if ($count > 0) : ?> 311 <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> 312 <?php else : ?> 313 <span class="wt-initial px-1 text-muted"><?= $this->displaySurnameInitial((string) $letter) ?></span> 314 315 <?php endif ?> 316 </li> 317 <?php endforeach ?> 318 319 <?php if (Session::has('initiated')) : ?> 320 <!-- Search spiders don't get the "show all" option as the other links give them everything. --> 321 <li class="wt-initials-list-item d-flex"> 322 <a class="wt-initial px-1<?= $show_all ? ' active' : '' ?>" href="<?= e($this->listUrl($tree, ['show_all' => 'yes'] + $params)) ?>"><?= I18N::translate('All') ?></a> 323 </li> 324 <?php endif ?> 325 </ul> 326 327 <!-- Search spiders don't get an option to show/hide the surname sublists, nor does it make sense on the all/unknown/surname views --> 328 <?php if ($show !== 'none' && Session::has('initiated')) : ?> 329 <?php if ($show_marnm === 'yes') : ?> 330 <p> 331 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'no'] + $params)) ?>"> 332 <?= I18N::translate('Exclude individuals with “%s” as a married name', $legend) ?> 333 </a> 334 </p> 335 <?php else : ?> 336 <p> 337 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'yes'] + $params)) ?>"> 338 <?= I18N::translate('Include individuals with “%s” as a married name', $legend) ?> 339 </a> 340 </p> 341 <?php endif ?> 342 343 <?php if ($alpha !== '@' && $alpha !== ',' && $surname === '') : ?> 344 <?php if ($show === 'surn') : ?> 345 <p> 346 <a href="<?= e($this->listUrl($tree, ['show' => 'indi', 'show_marnm' => 'no'] + $params)) ?>"> 347 <?= I18N::translate('Show the list of individuals') ?> 348 </a> 349 </p> 350 <?php else : ?> 351 <p> 352 <a href="<?= e($this->listUrl($tree, ['show' => 'surn', 'show_marnm' => 'no'] + $params)) ?>"> 353 <?= I18N::translate('Show the list of surnames') ?> 354 </a> 355 </p> 356 <?php endif ?> 357 <?php endif ?> 358 <?php endif ?> 359 </div> 360 361 <div class="wt-page-content"> 362 <?php 363 if ($show === 'indi' || $show === 'surn') { 364 switch ($alpha) { 365 case '@': 366 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === Individual::NOMEN_NESCIO, ARRAY_FILTER_USE_KEY); 367 break; 368 case ',': 369 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === '', ARRAY_FILTER_USE_KEY); 370 break; 371 case '': 372 if ($show_all) { 373 $surns = array_filter($all_surnames, static fn (string $x): bool => $x !== '' && $x !== Individual::NOMEN_NESCIO, ARRAY_FILTER_USE_KEY); 374 } else { 375 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === $surname, ARRAY_FILTER_USE_KEY); 376 } 377 break; 378 default: 379 if ($surname === '') { 380 $surns = array_filter($all_surnames, static fn (string $x): bool => I18N::language()->initialLetter($x) === $alpha, ARRAY_FILTER_USE_KEY); 381 } else { 382 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === $surname, ARRAY_FILTER_USE_KEY); 383 } 384 break; 385 } 386 387 if ($show === 'surn') { 388 // Show the surname list 389 switch ($tree->getPreference('SURNAME_LIST_STYLE')) { 390 case 'style1': 391 echo view('lists/surnames-column-list', [ 392 'module' => $this, 393 'surnames' => $surns, 394 'totals' => true, 395 'tree' => $tree, 396 ]); 397 break; 398 case 'style3': 399 echo view('lists/surnames-tag-cloud', [ 400 'module' => $this, 401 'surnames' => $surns, 402 'totals' => true, 403 'tree' => $tree, 404 ]); 405 break; 406 case 'style2': 407 default: 408 echo view('lists/surnames-table', [ 409 'families' => $families, 410 'module' => $this, 411 'order' => [[0, 'asc']], 412 'surnames' => $surns, 413 'tree' => $tree, 414 ]); 415 break; 416 } 417 } else { 418 // Show the list 419 $count = array_sum(array_map(static fn (array $x): int => array_sum($x), $surns)); 420 421 // Don't sublist short lists. 422 if ($count < $tree->getPreference('SUBLIST_TRIGGER_I')) { 423 $falpha = ''; 424 } else { 425 // Break long lists by initial letter of given name 426 $surns = array_values(array_map(static fn ($x): array => array_keys($x), $surns)); 427 $surns = array_merge(...$surns); 428 $givn_initials = $this->givenNameInitials($tree, $surns, $show_marnm === 'yes', $families); 429 430 if ($surname !== '' || $show_all) { 431 if (!$show_all) { 432 echo '<h2 class="wt-page-title">', I18N::translate('Individuals with surname %s', $legend), '</h2>'; 433 } 434 // Don't show the list until we have some filter criteria 435 $show = $falpha !== '' || $show_all_firstnames ? 'indi' : 'none'; 436 echo '<ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-given-names">'; 437 foreach ($givn_initials as $givn_initial => $given_count) { 438 echo '<li class="wt-initials-list-item d-flex">'; 439 if ($given_count > 0) { 440 if ($show === 'indi' && $givn_initial === $falpha && !$show_all_firstnames) { 441 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>'; 442 } else { 443 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>'; 444 } 445 } else { 446 echo '<span class="wt-initial px-1 text-muted">' . $this->displayGivenNameInitial((string) $givn_initial) . '</span>'; 447 } 448 echo '</li>'; 449 } 450 // Search spiders don't get the "show all" option as the other links give them everything. 451 if (Session::has('initiated')) { 452 echo '<li class="wt-initials-list-item d-flex">'; 453 if ($show_all_firstnames) { 454 echo '<span class="wt-initial px-1 active">' . I18N::translate('All') . '</span>'; 455 } else { 456 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>'; 457 } 458 echo '</li>'; 459 } 460 echo '</ul>'; 461 } 462 } 463 if ($show === 'indi') { 464 if ($families) { 465 echo view('lists/families-table', [ 466 'families' => $this->families($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes'), 467 'tree' => $tree, 468 ]); 469 } else { 470 echo view('lists/individuals-table', [ 471 'individuals' => $this->individuals($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes', false), 472 'sosa' => false, 473 'tree' => $tree, 474 ]); 475 } 476 } 477 } 478 } ?> 479 </div> 480 <?php 481 482 $html = ob_get_clean(); 483 484 return $this->viewResponse('modules/individual-list/page', [ 485 'content' => $html, 486 'title' => $title, 487 'tree' => $tree, 488 ]); 489 } 490 491 /** 492 * Some initial letters have a special meaning 493 * 494 * @param string $initial 495 * 496 * @return string 497 */ 498 protected function displayGivenNameInitial(string $initial): string 499 { 500 if ($initial === '@') { 501 return I18N::translateContext('Unknown given name', '…'); 502 } 503 504 return e($initial); 505 } 506 507 /** 508 * Some initial letters have a special meaning 509 * 510 * @param string $initial 511 * 512 * @return string 513 */ 514 protected function displaySurnameInitial(string $initial): string 515 { 516 if ($initial === '@') { 517 return I18N::translateContext('Unknown surname', '…'); 518 } 519 520 if ($initial === ',') { 521 return I18N::translate('No surname'); 522 } 523 524 return e($initial); 525 } 526 527 /** 528 * Restrict a query to individuals that are a spouse in a family record. 529 * 530 * @param bool $fams 531 * @param Builder $query 532 */ 533 protected function whereFamily(bool $fams, Builder $query): void 534 { 535 if ($fams) { 536 $query->join('link', static function (JoinClause $join): void { 537 $join 538 ->on('l_from', '=', 'n_id') 539 ->on('l_file', '=', 'n_file') 540 ->where('l_type', '=', 'FAMS'); 541 }); 542 } 543 } 544 545 /** 546 * Restrict a query to include/exclude married names. 547 * 548 * @param bool $marnm 549 * @param Builder $query 550 */ 551 protected function whereMarriedName(bool $marnm, Builder $query): void 552 { 553 if (!$marnm) { 554 $query->where('n_type', '<>', '_MARNM'); 555 } 556 } 557 558 /** 559 * Get a count of individuals with each initial letter 560 * 561 * @param Tree $tree 562 * @param array<string> $surns if set, only consider people with this surname 563 * @param bool $marnm if set, include married names 564 * @param bool $fams if set, only consider individuals with FAMS records 565 * 566 * @return array<int> 567 */ 568 public function givenNameInitials(Tree $tree, array $surns, bool $marnm, bool $fams): array 569 { 570 $initials = []; 571 572 // Ensure our own language comes before others. 573 foreach (I18N::language()->alphabet() as $initial) { 574 $initials[$initial] = 0; 575 } 576 577 $query = DB::table('name') 578 ->where('n_file', '=', $tree->id()); 579 580 $this->whereFamily($fams, $query); 581 $this->whereMarriedName($marnm, $query); 582 583 if ($surns !== []) { 584 $query->whereIn('n_surn', $surns); 585 } 586 587 $query 588 ->select([$this->binaryColumn('n_givn', 'n_givn'), new Expression('COUNT(*) AS count')]) 589 ->groupBy([$this->binaryColumn('n_givn')]); 590 591 foreach ($query->get() as $row) { 592 $initial = I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)); 593 $initials[$initial] ??= 0; 594 $initials[$initial] += (int) $row->count; 595 } 596 597 $count_unknown = $initials['@'] ?? 0; 598 599 if ($count_unknown > 0) { 600 unset($initials['@']); 601 $initials['@'] = $count_unknown; 602 } 603 604 return $initials; 605 } 606 607 /** 608 * Get a count of all surnames and variants. 609 * 610 * @param Tree $tree 611 * @param bool $marnm if set, include married names 612 * @param bool $fams if set, only consider individuals with FAMS records 613 * 614 * @return array<array<int>> 615 */ 616 protected function allSurnames(Tree $tree, bool $marnm, bool $fams): array 617 { 618 $query = DB::table('name') 619 ->where('n_file', '=', $tree->id()) 620 ->whereNotNull('n_surn') // Filters old records for sources, repositories, etc. 621 ->whereNotNull('n_surname') 622 ->select([ 623 $this->binaryColumn('n_surn', 'n_surn'), 624 $this->binaryColumn('n_surname', 'n_surname'), 625 new Expression('COUNT(*) AS total'), 626 ]); 627 628 $this->whereFamily($fams, $query); 629 $this->whereMarriedName($marnm, $query); 630 631 $query->groupBy([ 632 $this->binaryColumn('n_surn'), 633 $this->binaryColumn('n_surname'), 634 ]); 635 636 /** @var array<array<int>> $list */ 637 $list = []; 638 639 foreach ($query->get() as $row) { 640 $row->n_surn = $row->n_surn === '' ? $row->n_surname : $row->n_surn; 641 $row->n_surn = I18N::strtoupper(I18N::language()->normalize($row->n_surn)); 642 643 $list[$row->n_surn][$row->n_surname] ??= 0; 644 $list[$row->n_surn][$row->n_surname] += (int) $row->total; 645 } 646 647 uksort($list, I18N::comparator()); 648 649 return $list; 650 } 651 652 /** 653 * Extract initial letters and counts for all surnames. 654 * 655 * @param array<array<int>> $all_surnames 656 * 657 * @return array<int> 658 */ 659 protected function surnameInitials(array $all_surnames): array 660 { 661 $initials = []; 662 663 // Ensure our own language comes before others. 664 foreach (I18N::language()->alphabet() as $initial) { 665 $initials[$initial] = 0; 666 } 667 668 foreach ($all_surnames as $surn => $surnames) { 669 $initial = I18N::language()->initialLetter((string) $surn); 670 671 $initials[$initial] ??= 0; 672 $initials[$initial] += array_sum($surnames); 673 } 674 675 // Move specials to the end 676 $count_none = $initials[''] ?? 0; 677 678 if ($count_none > 0) { 679 unset($initials['']); 680 $initials[','] = $count_none; 681 } 682 683 $count_unknown = $initials['@'] ?? 0; 684 685 if ($count_unknown > 0) { 686 unset($initials['@']); 687 $initials['@'] = $count_unknown; 688 } 689 690 return $initials; 691 } 692 693 /** 694 * Fetch a list of individuals with specified names 695 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 696 * To search for names with no surnames, use $salpha="," 697 * 698 * @param Tree $tree 699 * @param string $surname if set, only fetch people with this n_surn 700 * @param array<string> $surnames if set, only fetch people with this n_surname 701 * @param string $galpha if set, only fetch given names starting with this letter 702 * @param bool $marnm if set, include married names 703 * @param bool $fams if set, only fetch individuals with FAMS records 704 * 705 * @return Collection<int,Individual> 706 */ 707 protected function individuals(Tree $tree, string $surname, array $surnames, string $galpha, bool $marnm, bool $fams): Collection 708 { 709 $query = DB::table('individuals') 710 ->join('name', static function (JoinClause $join): void { 711 $join 712 ->on('n_id', '=', 'i_id') 713 ->on('n_file', '=', 'i_file'); 714 }) 715 ->where('i_file', '=', $tree->id()) 716 ->select(['i_id AS xref', 'i_gedcom AS gedcom', 'n_givn', 'n_surn']); 717 718 $this->whereFamily($fams, $query); 719 $this->whereMarriedName($marnm, $query); 720 721 if ($surnames === []) { 722 // SURN, with no surname 723 $query->where('n_surn', '=', $surname); 724 } else { 725 $query->whereIn($this->binaryColumn('n_surname'), $surnames); 726 } 727 728 $query 729 ->orderBy(new Expression("CASE n_surn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 730 ->orderBy('n_surn') 731 ->orderBy(new Expression("CASE n_givn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 732 ->orderBy('n_givn'); 733 734 $individuals = new Collection(); 735 736 foreach ($query->get() as $row) { 737 $individual = Registry::individualFactory()->make($row->xref, $tree, $row->gedcom); 738 assert($individual instanceof Individual); 739 740 // The name from the database may be private - check the filtered list... 741 foreach ($individual->getAllNames() as $n => $name) { 742 if ($name['givn'] === $row->n_givn && $name['surn'] === $row->n_surn) { 743 if ($galpha === '' || I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)) === $galpha) { 744 $individual->setPrimaryName($n); 745 // We need to clone $individual, as we may have multiple references to the 746 // same individual in this list, and the "primary name" would otherwise 747 // be shared amongst all of them. 748 $individuals->push(clone $individual); 749 break; 750 } 751 } 752 } 753 } 754 755 return $individuals; 756 } 757 758 /** 759 * Fetch a list of families with specified names 760 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 761 * To search for names with no surnames, use $salpha="," 762 * 763 * @param Tree $tree 764 * @param string $surname if set, only fetch people with this n_surn 765 * @param array<string> $surnames if set, only fetch people with this n_surname 766 * @param string $galpha if set, only fetch given names starting with this letter 767 * @param bool $marnm if set, include married names 768 * 769 * @return Collection<int,Family> 770 */ 771 protected function families(Tree $tree, string $surname, array $surnames, string $galpha, bool $marnm): Collection 772 { 773 $families = new Collection(); 774 775 foreach ($this->individuals($tree, $surname, $surnames, $galpha, $marnm, true) as $indi) { 776 foreach ($indi->spouseFamilies() as $family) { 777 $families->push($family); 778 } 779 } 780 781 return $families->unique(); 782 } 783 784 /** 785 * This module assumes the database will use binary collation on the name columns. 786 * Until we convert MySQL databases to use utf8_bin, we need to do this at run-time. 787 * 788 * @param string $column 789 * @param string|null $alias 790 * 791 * @return Expression 792 */ 793 private function binaryColumn(string $column, string $alias = null): Expression 794 { 795 if (DB::connection()->getDriverName() === 'mysql') { 796 $sql = 'CAST(' . $column . ' AS binary)'; 797 } else { 798 $sql = $column; 799 } 800 801 if ($alias !== null) { 802 $sql .= ' AS ' . $alias; 803 } 804 805 return new Expression($sql); 806 } 807} 808