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_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 view; 53 54use const ARRAY_FILTER_USE_KEY; 55 56/** 57 * Class IndividualListModule 58 */ 59class IndividualListModule extends AbstractModule implements ModuleListInterface, RequestHandlerInterface 60{ 61 use ModuleListTrait; 62 63 protected const ROUTE_URL = '/tree/{tree}/individual-list'; 64 65 /** 66 * Initialization. 67 * 68 * @return void 69 */ 70 public function boot(): void 71 { 72 Registry::routeFactory()->routeMap() 73 ->get(static::class, static::ROUTE_URL, $this); 74 } 75 76 /** 77 * How should this module be identified in the control panel, etc.? 78 * 79 * @return string 80 */ 81 public function title(): string 82 { 83 /* I18N: Name of a module/list */ 84 return I18N::translate('Individuals'); 85 } 86 87 /** 88 * A sentence describing what this module does. 89 * 90 * @return string 91 */ 92 public function description(): string 93 { 94 /* I18N: Description of the “Individuals” module */ 95 return I18N::translate('A list of individuals.'); 96 } 97 98 /** 99 * CSS class for the URL. 100 * 101 * @return string 102 */ 103 public function listMenuClass(): string 104 { 105 return 'menu-list-indi'; 106 } 107 108 /** 109 * @param Tree $tree 110 * @param array<bool|int|string|array<string>|null> $parameters 111 * 112 * @return string 113 */ 114 public function listUrl(Tree $tree, array $parameters = []): string 115 { 116 $request = app(ServerRequestInterface::class); 117 assert($request instanceof ServerRequestInterface); 118 119 $xref = Validator::attributes($request)->isXref()->string('xref', ''); 120 121 if ($xref !== '') { 122 $individual = Registry::individualFactory()->make($xref, $tree); 123 124 if ($individual instanceof Individual && $individual->canShow()) { 125 $primary_name = $individual->getPrimaryName(); 126 127 $parameters['surname'] ??= $individual->getAllNames()[$primary_name]['surn'] ?? null; 128 } 129 } 130 131 $parameters['tree'] = $tree->name(); 132 133 return route(static::class, $parameters); 134 } 135 136 /** 137 * @return array<string> 138 */ 139 public function listUrlAttributes(): array 140 { 141 return []; 142 } 143 144 /** 145 * @param ServerRequestInterface $request 146 * 147 * @return ResponseInterface 148 */ 149 public function handle(ServerRequestInterface $request): ResponseInterface 150 { 151 $tree = Validator::attributes($request)->tree(); 152 $user = Validator::attributes($request)->user(); 153 154 Auth::checkComponentAccess($this, ModuleListInterface::class, $tree, $user); 155 156 $surname_param = Validator::queryParams($request)->string('surname', ''); 157 $surname = I18N::strtoupper(I18N::language()->normalize($surname_param)); 158 159 $params = [ 160 'alpha' => Validator::queryParams($request)->string('alpha', ''), 161 'falpha' => Validator::queryParams($request)->string('falpha', ''), 162 'show' => Validator::queryParams($request)->string('show', 'surn'), 163 'show_all' => Validator::queryParams($request)->string('show_all', 'no'), 164 'show_all_firstnames' => Validator::queryParams($request)->string('show_all_firstnames', 'no'), 165 'show_marnm' => Validator::queryParams($request)->string('show_marnm', ''), 166 'surname' => $surname, 167 ]; 168 169 if ($surname_param !== $surname) { 170 return Registry::responseFactory()->redirectUrl($this->listUrl($tree, $params), StatusCodeInterface::STATUS_MOVED_PERMANENTLY); 171 } 172 173 return $this->createResponse($tree, $user, $params, false); 174 } 175 176 /** 177 * @param Tree $tree 178 * @param UserInterface $user 179 * @param array<string> $params 180 * @param bool $families 181 * 182 * @return ResponseInterface 183 */ 184 protected function createResponse(Tree $tree, UserInterface $user, array $params, bool $families): ResponseInterface 185 { 186 // We show three different lists: initials, surnames and individuals 187 188 // All surnames beginning with this letter, where "@" is unknown and "," is none 189 $alpha = $params['alpha']; 190 191 // All individuals with this surname 192 $surname = $params['surname']; 193 194 // All individuals 195 $show_all = $params['show_all'] === 'yes'; 196 197 // Include/exclude married names 198 $show_marnm = $params['show_marnm']; 199 200 // What type of list to display, if any 201 $show = $params['show']; 202 203 // Break long lists down by given name 204 $show_all_firstnames = $params['show_all_firstnames'] === 'yes'; 205 206 // All first names beginning with this letter where "@" is unknown 207 $falpha = $params['falpha']; 208 209 // Make sure parameters are consistent with each other. 210 if ($show_all_firstnames) { 211 $falpha = ''; 212 } 213 214 if ($show_all) { 215 $alpha = ''; 216 $surname = ''; 217 } 218 219 if ($surname !== '') { 220 $alpha = I18N::language()->initialLetter($surname); 221 } 222 223 $all_surnames = $this->allSurnames($tree, $show_marnm === 'yes', $families); 224 $surname_initials = $this->surnameInitials($all_surnames); 225 226 switch ($show_marnm) { 227 case 'no': 228 case 'yes': 229 $user->setPreference($families ? 'family-list-marnm' : 'individual-list-marnm', $show_marnm); 230 break; 231 default: 232 $show_marnm = $user->getPreference($families ? 'family-list-marnm' : 'individual-list-marnm'); 233 } 234 235 // Make sure selections are consistent. 236 // i.e. can’t specify show_all and surname at the same time. 237 if ($show_all) { 238 if ($show_all_firstnames) { 239 $legend = I18N::translate('All'); 240 $params = ['tree' => $tree->name(), 'show_all' => 'yes']; 241 $show = 'indi'; 242 } elseif ($falpha !== '') { 243 $legend = I18N::translate('All') . ', ' . e($falpha) . '…'; 244 $params = ['tree' => $tree->name(), 'show_all' => 'yes']; 245 $show = 'indi'; 246 } else { 247 $legend = I18N::translate('All'); 248 $params = ['tree' => $tree->name(), 'show_all' => 'yes']; 249 } 250 } elseif ($surname !== '') { 251 $show_all = false; 252 if ($surname === Individual::NOMEN_NESCIO) { 253 $legend = I18N::translateContext('Unknown surname', '…'); 254 $show = 'indi'; // The surname list makes no sense with only one surname. 255 } else { 256 // The surname parameter is a root/canonical form. Display the actual surnames found. 257 $variants = array_keys($all_surnames[$surname] ?? [$surname => $surname]); 258 usort($variants, I18N::comparator()); 259 $variants = array_map(static fn (string $x): string => $x === '' ? I18N::translate('No surname') : $x, $variants); 260 $legend = implode('/', $variants); 261 $show = 'indi'; // The surname list makes no sense with only one surname. 262 } 263 $params = ['tree' => $tree->name(), 'surname' => $surname, 'falpha' => $falpha]; 264 switch ($falpha) { 265 case '': 266 break; 267 case '@': 268 $legend .= ', ' . I18N::translateContext('Unknown given name', '…'); 269 break; 270 default: 271 $legend .= ', ' . e($falpha) . '…'; 272 break; 273 } 274 } elseif ($alpha === '@') { 275 $show_all = false; 276 $legend = I18N::translateContext('Unknown surname', '…'); 277 $params = ['alpha' => $alpha, 'tree' => $tree->name()]; 278 $surname = Individual::NOMEN_NESCIO; 279 $show = 'indi'; // SURN list makes no sense here 280 } elseif ($alpha === ',') { 281 $show_all = false; 282 $legend = I18N::translate('No surname'); 283 $params = ['alpha' => $alpha, 'tree' => $tree->name()]; 284 $show = 'indi'; // SURN list makes no sense here 285 } elseif ($alpha !== '') { 286 $show_all = false; 287 $legend = e($alpha) . '…'; 288 $params = ['alpha' => $alpha, 'tree' => $tree->name()]; 289 } else { 290 $show_all = false; 291 $legend = '…'; 292 $params = ['tree' => $tree->name()]; 293 $show = 'none'; // Don't show lists until something is chosen 294 } 295 $legend = '<bdi>' . $legend . '</bdi>'; 296 297 if ($families) { 298 $title = I18N::translate('Families') . ' — ' . $legend; 299 } else { 300 $title = I18N::translate('Individuals') . ' — ' . $legend; 301 } 302 303 ob_start(); ?> 304 <div class="d-flex flex-column wt-page-options wt-page-options-individual-list d-print-none"> 305 <ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-surname"> 306 307 <?php foreach ($surname_initials as $letter => $count) : ?> 308 <li class="wt-initials-list-item d-flex"> 309 <?php if ($count > 0) : ?> 310 <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> 311 <?php else : ?> 312 <span class="wt-initial px-1 text-muted"><?= $this->displaySurnameInitial((string) $letter) ?></span> 313 314 <?php endif ?> 315 </li> 316 <?php endforeach ?> 317 318 <?php if (Session::has('initiated')) : ?> 319 <!-- Search spiders don't get the "show all" option as the other links give them everything. --> 320 <li class="wt-initials-list-item d-flex"> 321 <a class="wt-initial px-1<?= $show_all ? ' active' : '' ?>" href="<?= e($this->listUrl($tree, ['show_all' => 'yes'] + $params)) ?>"><?= I18N::translate('All') ?></a> 322 </li> 323 <?php endif ?> 324 </ul> 325 326 <!-- Search spiders don't get an option to show/hide the surname sublists, nor does it make sense on the all/unknown/surname views --> 327 <?php if ($show !== 'none' && Session::has('initiated')) : ?> 328 <?php if ($show_marnm === 'yes') : ?> 329 <p> 330 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'no'] + $params)) ?>"> 331 <?= I18N::translate('Exclude individuals with “%s” as a married name', $legend) ?> 332 </a> 333 </p> 334 <?php else : ?> 335 <p> 336 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'yes'] + $params)) ?>"> 337 <?= I18N::translate('Include individuals with “%s” as a married name', $legend) ?> 338 </a> 339 </p> 340 <?php endif ?> 341 342 <?php if ($alpha !== '@' && $alpha !== ',' && $surname === '') : ?> 343 <?php if ($show === 'surn') : ?> 344 <p> 345 <a href="<?= e($this->listUrl($tree, ['show' => 'indi', 'show_marnm' => 'no'] + $params)) ?>"> 346 <?= I18N::translate('Show the list of individuals') ?> 347 </a> 348 </p> 349 <?php else : ?> 350 <p> 351 <a href="<?= e($this->listUrl($tree, ['show' => 'surn', 'show_marnm' => 'no'] + $params)) ?>"> 352 <?= I18N::translate('Show the list of surnames') ?> 353 </a> 354 </p> 355 <?php endif ?> 356 <?php endif ?> 357 <?php endif ?> 358 </div> 359 360 <div class="wt-page-content"> 361 <?php 362 if ($show === 'indi' || $show === 'surn') { 363 switch ($alpha) { 364 case '@': 365 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === Individual::NOMEN_NESCIO, ARRAY_FILTER_USE_KEY); 366 break; 367 case ',': 368 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === '', ARRAY_FILTER_USE_KEY); 369 break; 370 case '': 371 if ($show_all) { 372 $surns = array_filter($all_surnames, static fn (string $x): bool => $x !== '' && $x !== Individual::NOMEN_NESCIO, ARRAY_FILTER_USE_KEY); 373 } else { 374 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === $surname, ARRAY_FILTER_USE_KEY); 375 } 376 break; 377 default: 378 if ($surname === '') { 379 $surns = array_filter($all_surnames, static fn (string $x): bool => I18N::language()->initialLetter($x) === $alpha, ARRAY_FILTER_USE_KEY); 380 } else { 381 $surns = array_filter($all_surnames, static fn (string $x): bool => $x === $surname, ARRAY_FILTER_USE_KEY); 382 } 383 break; 384 } 385 386 if ($show === 'surn') { 387 // Show the surname list 388 switch ($tree->getPreference('SURNAME_LIST_STYLE')) { 389 case 'style1': 390 echo view('lists/surnames-column-list', [ 391 'module' => $this, 392 'surnames' => $surns, 393 'totals' => true, 394 'tree' => $tree, 395 ]); 396 break; 397 case 'style3': 398 echo view('lists/surnames-tag-cloud', [ 399 'module' => $this, 400 'surnames' => $surns, 401 'totals' => true, 402 'tree' => $tree, 403 ]); 404 break; 405 case 'style2': 406 default: 407 echo view('lists/surnames-table', [ 408 'families' => $families, 409 'module' => $this, 410 'order' => [[0, 'asc']], 411 'surnames' => $surns, 412 'tree' => $tree, 413 ]); 414 break; 415 } 416 } else { 417 // Show the list 418 $count = array_sum(array_map(static fn (array $x): int => array_sum($x), $surns)); 419 420 // Don't sublist short lists. 421 if ($count < $tree->getPreference('SUBLIST_TRIGGER_I')) { 422 $falpha = ''; 423 } else { 424 $givn_initials = $this->givenNameInitials($tree, array_keys($surns), $show_marnm === 'yes', $families); 425 // Break long lists by initial letter of given name 426 if ($surname !== '' || $show_all) { 427 if (!$show_all) { 428 echo '<h2 class="wt-page-title">', I18N::translate('Individuals with surname %s', $legend), '</h2>'; 429 } 430 // Don't show the list until we have some filter criteria 431 $show = $falpha !== '' || $show_all_firstnames ? 'indi' : 'none'; 432 echo '<ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-given-names">'; 433 foreach ($givn_initials as $givn_initial => $given_count) { 434 echo '<li class="wt-initials-list-item d-flex">'; 435 if ($given_count > 0) { 436 if ($show === 'indi' && $givn_initial === $falpha && !$show_all_firstnames) { 437 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>'; 438 } else { 439 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>'; 440 } 441 } else { 442 echo '<span class="wt-initial px-1 text-muted">' . $this->displayGivenNameInitial((string) $givn_initial) . '</span>'; 443 } 444 echo '</li>'; 445 } 446 // Search spiders don't get the "show all" option as the other links give them everything. 447 if (Session::has('initiated')) { 448 echo '<li class="wt-initials-list-item d-flex">'; 449 if ($show_all_firstnames) { 450 echo '<span class="wt-initial px-1 active">' . I18N::translate('All') . '</span>'; 451 } else { 452 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>'; 453 } 454 echo '</li>'; 455 } 456 echo '</ul>'; 457 } 458 } 459 if ($show === 'indi') { 460 if ($families) { 461 echo view('lists/families-table', [ 462 'families' => $this->families($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes'), 463 'tree' => $tree, 464 ]); 465 } else { 466 echo view('lists/individuals-table', [ 467 'individuals' => $this->individuals($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes', false), 468 'sosa' => false, 469 'tree' => $tree, 470 ]); 471 } 472 } 473 } 474 } ?> 475 </div> 476 <?php 477 478 $html = ob_get_clean(); 479 480 return $this->viewResponse('modules/individual-list/page', [ 481 'content' => $html, 482 'title' => $title, 483 'tree' => $tree, 484 ]); 485 } 486 487 /** 488 * Some initial letters have a special meaning 489 * 490 * @param string $initial 491 * 492 * @return string 493 */ 494 protected function displayGivenNameInitial(string $initial): string 495 { 496 if ($initial === '@') { 497 return I18N::translateContext('Unknown given name', '…'); 498 } 499 500 return e($initial); 501 } 502 503 /** 504 * Some initial letters have a special meaning 505 * 506 * @param string $initial 507 * 508 * @return string 509 */ 510 protected function displaySurnameInitial(string $initial): string 511 { 512 if ($initial === '@') { 513 return I18N::translateContext('Unknown surname', '…'); 514 } 515 516 if ($initial === ',') { 517 return I18N::translate('No surname'); 518 } 519 520 return e($initial); 521 } 522 523 /** 524 * Restrict a query to individuals that are a spouse in a family record. 525 * 526 * @param bool $fams 527 * @param Builder $query 528 */ 529 protected function whereFamily(bool $fams, Builder $query): void 530 { 531 if ($fams) { 532 $query->join('link', static function (JoinClause $join): void { 533 $join 534 ->on('l_from', '=', 'n_id') 535 ->on('l_file', '=', 'n_file') 536 ->where('l_type', '=', 'FAMS'); 537 }); 538 } 539 } 540 541 /** 542 * Restrict a query to include/exclude married names. 543 * 544 * @param bool $marnm 545 * @param Builder $query 546 */ 547 protected function whereMarriedName(bool $marnm, Builder $query): void 548 { 549 if (!$marnm) { 550 $query->where('n_type', '<>', '_MARNM'); 551 } 552 } 553 554 /** 555 * Get a count of individuals with each initial letter 556 * 557 * @param Tree $tree 558 * @param array<string> $surns if set, only consider people with this surname 559 * @param bool $marnm if set, include married names 560 * @param bool $fams if set, only consider individuals with FAMS records 561 * 562 * @return array<int> 563 */ 564 public function givenNameInitials(Tree $tree, array $surns, bool $marnm, bool $fams): array 565 { 566 $initials = []; 567 568 // Ensure our own language comes before others. 569 foreach (I18N::language()->alphabet() as $initial) { 570 $initials[$initial] = 0; 571 } 572 573 $query = DB::table('name') 574 ->where('n_file', '=', $tree->id()); 575 576 $this->whereFamily($fams, $query); 577 $this->whereMarriedName($marnm, $query); 578 579 if ($surns !== []) { 580 $query->whereIn('n_surn', $surns); 581 } 582 583 $query 584 ->select($this->binaryColumn('n_givn', 'n_givn'), new Expression('COUNT(*) AS count')) 585 ->groupBy([$this->binaryColumn('n_givn')]); 586 587 foreach ($query->get() as $row) { 588 $initial = I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)); 589 $initials[$initial] ??= 0; 590 $initials[$initial] += (int) $row->count; 591 } 592 593 $count_unknown = $initials['@'] ?? 0; 594 595 if ($count_unknown > 0) { 596 unset($initials['@']); 597 $initials['@'] = $count_unknown; 598 } 599 600 return $initials; 601 } 602 603 /** 604 * Get a count of all surnames and variants. 605 * 606 * @param Tree $tree 607 * @param bool $marnm if set, include married names 608 * @param bool $fams if set, only consider individuals with FAMS records 609 * 610 * @return array<array<int>> 611 */ 612 protected function allSurnames(Tree $tree, bool $marnm, bool $fams): array 613 { 614 $query = DB::table('name') 615 ->where('n_file', '=', $tree->id()) 616 ->whereNotNull('n_surn') // Filters old records for sources, repositories, etc. 617 ->whereNotNull('n_surname') 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((string) $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