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