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