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 'surnames' => $surns, 374 'totals' => true, 375 'tree' => $tree, 376 ]); 377 break; 378 case 'style3': 379 echo view('lists/surnames-tag-cloud', [ 380 'module' => $this, 381 'surnames' => $surns, 382 'totals' => true, 383 'tree' => $tree, 384 ]); 385 break; 386 case 'style2': 387 default: 388 echo view('lists/surnames-table', [ 389 'families' => $this->families, 390 'module' => $this, 391 'order' => [[0, 'asc']], 392 'surnames' => $surns, 393 'tree' => $tree, 394 ]); 395 break; 396 } 397 } else { 398 // Show the list 399 $count = array_sum(array_map(static fn (array $x): int => array_sum($x), $surns)); 400 401 // Don't sublist short lists. 402 if ($count < $tree->getPreference('SUBLIST_TRIGGER_I')) { 403 $falpha = ''; 404 } else { 405 // Break long lists by initial letter of given name 406 $surns = array_values(array_map(static fn ($x): array => array_keys($x), $surns)); 407 $surns = array_merge(...$surns); 408 $givn_initials = $this->givenNameInitials($tree, $surns, $show_marnm === 'yes', $this->families); 409 410 if ($surname !== '' || $show_all === 'yes') { 411 if ($show_all !== 'yes') { 412 echo '<h2 class="wt-page-title">', I18N::translate('Individuals with surname %s', $legend), '</h2>'; 413 } 414 // Don't show the list until we have some filter criteria 415 $show = $falpha !== '' || $show_all_firstnames === 'yes' ? 'indi' : 'none'; 416 echo '<ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-given-names">'; 417 foreach ($givn_initials as $givn_initial => $given_count) { 418 echo '<li class="wt-initials-list-item d-flex">'; 419 if ($given_count > 0) { 420 if ($show === 'indi' && $givn_initial === $falpha && $show_all_firstnames !== 'yes') { 421 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>'; 422 } else { 423 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>'; 424 } 425 } else { 426 echo '<span class="wt-initial px-1 text-muted">' . $this->displayGivenNameInitial((string) $givn_initial) . '</span>'; 427 } 428 echo '</li>'; 429 } 430 // Search spiders don't get the "show all" option as the other links give them everything. 431 if (Session::has('initiated')) { 432 echo '<li class="wt-initials-list-item d-flex">'; 433 if ($show_all_firstnames === 'yes') { 434 echo '<span class="wt-initial px-1 active">' . I18N::translate('All') . '</span>'; 435 } else { 436 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>'; 437 } 438 echo '</li>'; 439 } 440 echo '</ul>'; 441 } 442 } 443 if ($show === 'indi') { 444 if ($this->families) { 445 echo view('lists/families-table', [ 446 'families' => $this->families($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes'), 447 'tree' => $tree, 448 ]); 449 } else { 450 echo view('lists/individuals-table', [ 451 'individuals' => $this->individuals($tree, $surname, array_keys($all_surnames[$surname] ?? []), $falpha, $show_marnm === 'yes', false), 452 'sosa' => false, 453 'tree' => $tree, 454 ]); 455 } 456 } 457 } 458 } ?> 459 </div> 460 <?php 461 462 $html = ob_get_clean(); 463 464 return $this->viewResponse('modules/individual-list/page', [ 465 'content' => $html, 466 'title' => $title, 467 'tree' => $tree, 468 ]); 469 } 470 471 /** 472 * Some initial letters have a special meaning 473 * 474 * @param string $initial 475 * 476 * @return string 477 */ 478 protected function displayGivenNameInitial(string $initial): string 479 { 480 if ($initial === '@') { 481 return I18N::translateContext('Unknown given name', '…'); 482 } 483 484 return e($initial); 485 } 486 487 /** 488 * Some initial letters have a special meaning 489 * 490 * @param string $initial 491 * 492 * @return string 493 */ 494 protected function displaySurnameInitial(string $initial): string 495 { 496 if ($initial === '@') { 497 return I18N::translateContext('Unknown surname', '…'); 498 } 499 500 if ($initial === ',') { 501 return I18N::translate('No surname'); 502 } 503 504 return e($initial); 505 } 506 507 /** 508 * Restrict a query to individuals that are a spouse in a family record. 509 * 510 * @param bool $fams 511 * @param Builder $query 512 */ 513 protected function whereFamily(bool $fams, Builder $query): void 514 { 515 if ($fams) { 516 $query->join('link', static function (JoinClause $join): void { 517 $join 518 ->on('l_from', '=', 'n_id') 519 ->on('l_file', '=', 'n_file') 520 ->where('l_type', '=', 'FAMS'); 521 }); 522 } 523 } 524 525 /** 526 * Restrict a query to include/exclude married names. 527 * 528 * @param bool $marnm 529 * @param Builder $query 530 */ 531 protected function whereMarriedName(bool $marnm, Builder $query): void 532 { 533 if (!$marnm) { 534 $query->where('n_type', '<>', '_MARNM'); 535 } 536 } 537 538 /** 539 * Get a count of individuals with each initial letter 540 * 541 * @param Tree $tree 542 * @param array<string> $surns if set, only consider people with this surname 543 * @param bool $marnm if set, include married names 544 * @param bool $fams if set, only consider individuals with FAMS records 545 * 546 * @return array<int> 547 */ 548 public function givenNameInitials(Tree $tree, array $surns, bool $marnm, bool $fams): array 549 { 550 $initials = []; 551 552 // Ensure our own language comes before others. 553 foreach (I18N::language()->alphabet() as $initial) { 554 $initials[$initial] = 0; 555 } 556 557 $query = DB::table('name') 558 ->where('n_file', '=', $tree->id()); 559 560 $this->whereFamily($fams, $query); 561 $this->whereMarriedName($marnm, $query); 562 563 if ($surns !== []) { 564 $query->whereIn('n_surn', $surns); 565 } 566 567 $query 568 ->select([$this->binaryColumn('n_givn', 'n_givn'), new Expression('COUNT(*) AS count')]) 569 ->groupBy([$this->binaryColumn('n_givn')]); 570 571 foreach ($query->get() as $row) { 572 $initial = I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)); 573 $initials[$initial] ??= 0; 574 $initials[$initial] += (int) $row->count; 575 } 576 577 $count_unknown = $initials['@'] ?? 0; 578 579 if ($count_unknown > 0) { 580 unset($initials['@']); 581 $initials['@'] = $count_unknown; 582 } 583 584 return $initials; 585 } 586 587 /** 588 * Get a count of all surnames and variants. 589 * 590 * @param Tree $tree 591 * @param bool $marnm if set, include married names 592 * @param bool $fams if set, only consider individuals with FAMS records 593 * 594 * @return array<array<int>> 595 */ 596 protected function allSurnames(Tree $tree, bool $marnm, bool $fams): array 597 { 598 $query = DB::table('name') 599 ->where('n_file', '=', $tree->id()) 600 ->whereNotNull('n_surn') // Filters old records for sources, repositories, etc. 601 ->whereNotNull('n_surname') 602 ->select([ 603 $this->binaryColumn('n_surn', 'n_surn'), 604 $this->binaryColumn('n_surname', 'n_surname'), 605 new Expression('COUNT(*) AS total'), 606 ]); 607 608 $this->whereFamily($fams, $query); 609 $this->whereMarriedName($marnm, $query); 610 611 $query->groupBy([ 612 $this->binaryColumn('n_surn'), 613 $this->binaryColumn('n_surname'), 614 ]); 615 616 /** @var array<array<int>> $list */ 617 $list = []; 618 619 foreach ($query->get() as $row) { 620 $row->n_surn = $row->n_surn === '' ? $row->n_surname : $row->n_surn; 621 $row->n_surn = I18N::strtoupper(I18N::language()->normalize($row->n_surn)); 622 623 $list[$row->n_surn][$row->n_surname] ??= 0; 624 $list[$row->n_surn][$row->n_surname] += (int) $row->total; 625 } 626 627 uksort($list, I18N::comparator()); 628 629 return $list; 630 } 631 632 /** 633 * Extract initial letters and counts for all surnames. 634 * 635 * @param array<array<int>> $all_surnames 636 * 637 * @return array<int> 638 */ 639 protected function surnameInitials(array $all_surnames): array 640 { 641 $initials = []; 642 643 // Ensure our own language comes before others. 644 foreach (I18N::language()->alphabet() as $initial) { 645 $initials[$initial] = 0; 646 } 647 648 foreach ($all_surnames as $surn => $surnames) { 649 $initial = I18N::language()->initialLetter((string) $surn); 650 651 $initials[$initial] ??= 0; 652 $initials[$initial] += array_sum($surnames); 653 } 654 655 // Move specials to the end 656 $count_none = $initials[''] ?? 0; 657 658 if ($count_none > 0) { 659 unset($initials['']); 660 $initials[','] = $count_none; 661 } 662 663 $count_unknown = $initials['@'] ?? 0; 664 665 if ($count_unknown > 0) { 666 unset($initials['@']); 667 $initials['@'] = $count_unknown; 668 } 669 670 return $initials; 671 } 672 673 /** 674 * Fetch a list of individuals with specified names 675 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 676 * To search for names with no surnames, use $salpha="," 677 * 678 * @param Tree $tree 679 * @param string $surname if set, only fetch people with this n_surn 680 * @param array<string> $surnames if set, only fetch people with this n_surname 681 * @param string $galpha if set, only fetch given names starting with this letter 682 * @param bool $marnm if set, include married names 683 * @param bool $fams if set, only fetch individuals with FAMS records 684 * 685 * @return Collection<int,Individual> 686 */ 687 protected function individuals(Tree $tree, string $surname, array $surnames, string $galpha, bool $marnm, bool $fams): Collection 688 { 689 $query = DB::table('individuals') 690 ->join('name', static function (JoinClause $join): void { 691 $join 692 ->on('n_id', '=', 'i_id') 693 ->on('n_file', '=', 'i_file'); 694 }) 695 ->where('i_file', '=', $tree->id()) 696 ->select(['i_id AS xref', 'i_gedcom AS gedcom', 'n_givn', 'n_surn']); 697 698 $this->whereFamily($fams, $query); 699 $this->whereMarriedName($marnm, $query); 700 701 if ($surnames === []) { 702 // SURN, with no surname 703 $query->where('n_surn', '=', $surname); 704 } else { 705 $query->whereIn($this->binaryColumn('n_surname'), $surnames); 706 } 707 708 $query 709 ->orderBy(new Expression("CASE n_surn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 710 ->orderBy('n_surn') 711 ->orderBy(new Expression("CASE n_givn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 712 ->orderBy('n_givn'); 713 714 $individuals = new Collection(); 715 716 foreach ($query->get() as $row) { 717 $individual = Registry::individualFactory()->make($row->xref, $tree, $row->gedcom); 718 assert($individual instanceof Individual); 719 720 // The name from the database may be private - check the filtered list... 721 foreach ($individual->getAllNames() as $n => $name) { 722 if ($name['givn'] === $row->n_givn && $name['surn'] === $row->n_surn) { 723 if ($galpha === '' || I18N::strtoupper(I18N::language()->initialLetter($row->n_givn)) === $galpha) { 724 $individual->setPrimaryName($n); 725 // We need to clone $individual, as we may have multiple references to the 726 // same individual in this list, and the "primary name" would otherwise 727 // be shared amongst all of them. 728 $individuals->push(clone $individual); 729 break; 730 } 731 } 732 } 733 } 734 735 return $individuals; 736 } 737 738 /** 739 * Fetch a list of families with specified names 740 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 741 * To search for names with no surnames, use $salpha="," 742 * 743 * @param Tree $tree 744 * @param string $surname if set, only fetch people with this n_surn 745 * @param array<string> $surnames if set, only fetch people with this n_surname 746 * @param string $galpha if set, only fetch given names starting with this letter 747 * @param bool $marnm if set, include married names 748 * 749 * @return Collection<int,Family> 750 */ 751 protected function families(Tree $tree, string $surname, array $surnames, string $galpha, bool $marnm): Collection 752 { 753 $families = new Collection(); 754 755 foreach ($this->individuals($tree, $surname, $surnames, $galpha, $marnm, true) as $indi) { 756 foreach ($indi->spouseFamilies() as $family) { 757 $families->push($family); 758 } 759 } 760 761 return $families->unique(); 762 } 763 764 /** 765 * This module assumes the database will use binary collation on the name columns. 766 * Until we convert MySQL databases to use utf8_bin, we need to do this at run-time. 767 * 768 * @param string $column 769 * @param string|null $alias 770 * 771 * @return Expression 772 */ 773 private function binaryColumn(string $column, string $alias = null): Expression 774 { 775 if (DB::connection()->getDriverName() === 'mysql') { 776 $sql = 'CAST(' . $column . ' AS binary)'; 777 } else { 778 $sql = $column; 779 } 780 781 if ($alias !== null) { 782 $sql .= ' AS ' . $alias; 783 } 784 785 return new Expression($sql); 786 } 787} 788