1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2021 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 Aura\Router\RouterContainer; 23use Fisharebest\Localization\Locale\LocaleInterface; 24use Fisharebest\Webtrees\Auth; 25use Fisharebest\Webtrees\Contracts\UserInterface; 26use Fisharebest\Webtrees\Family; 27use Fisharebest\Webtrees\GedcomRecord; 28use Fisharebest\Webtrees\I18N; 29use Fisharebest\Webtrees\Individual; 30use Fisharebest\Webtrees\Registry; 31use Fisharebest\Webtrees\Services\LocalizationService; 32use Fisharebest\Webtrees\Session; 33use Fisharebest\Webtrees\Tree; 34use Illuminate\Database\Capsule\Manager as DB; 35use Illuminate\Database\Query\Builder; 36use Illuminate\Database\Query\Expression; 37use Illuminate\Database\Query\JoinClause; 38use Psr\Http\Message\ResponseInterface; 39use Psr\Http\Message\ServerRequestInterface; 40use Psr\Http\Server\RequestHandlerInterface; 41 42use function app; 43use function array_keys; 44use function assert; 45use function e; 46use function implode; 47use function in_array; 48use function ob_get_clean; 49use function ob_start; 50use function redirect; 51use function route; 52use function usort; 53use function view; 54 55/** 56 * Class IndividualListModule 57 */ 58class IndividualListModule extends AbstractModule implements ModuleListInterface, RequestHandlerInterface 59{ 60 use ModuleListTrait; 61 62 protected const ROUTE_URL = '/tree/{tree}/individual-list'; 63 64 private LocalizationService $localization_service; 65 66 /** 67 * IndividualListModule constructor. 68 * 69 * @param LocalizationService $localization_service 70 */ 71 public function __construct(LocalizationService $localization_service) 72 { 73 $this->localization_service = $localization_service; 74 } 75 76 /** 77 * Initialization. 78 * 79 * @return void 80 */ 81 public function boot(): void 82 { 83 $router_container = app(RouterContainer::class); 84 assert($router_container instanceof RouterContainer); 85 86 $router_container->getMap() 87 ->get(static::class, static::ROUTE_URL, $this); 88 } 89 90 /** 91 * How should this module be identified in the control panel, etc.? 92 * 93 * @return string 94 */ 95 public function title(): string 96 { 97 /* I18N: Name of a module/list */ 98 return I18N::translate('Individuals'); 99 } 100 101 /** 102 * A sentence describing what this module does. 103 * 104 * @return string 105 */ 106 public function description(): string 107 { 108 /* I18N: Description of the “Individuals” module */ 109 return I18N::translate('A list of individuals.'); 110 } 111 112 /** 113 * CSS class for the URL. 114 * 115 * @return string 116 */ 117 public function listMenuClass(): string 118 { 119 return 'menu-list-indi'; 120 } 121 122 /** 123 * @param Tree $tree 124 * @param array<bool|int|string|array|null> $parameters 125 * 126 * @return string 127 */ 128 public function listUrl(Tree $tree, array $parameters = []): string 129 { 130 $xref = app(ServerRequestInterface::class)->getAttribute('xref', ''); 131 132 if ($xref !== '') { 133 $individual = Registry::individualFactory()->make($xref, $tree); 134 135 if ($individual instanceof Individual && $individual->canShow()) { 136 $primary_name = $individual->getPrimaryName(); 137 138 $parameters['surname'] = $parameters['surname'] ?? $individual->getAllNames()[$primary_name]['surn'] ?? null; 139 } 140 } 141 142 $parameters['tree'] = $tree->name(); 143 144 return route(static::class, $parameters); 145 } 146 147 /** 148 * @return array<string> 149 */ 150 public function listUrlAttributes(): array 151 { 152 return []; 153 } 154 155 /** 156 * Handle URLs generated by older versions of webtrees 157 * 158 * @param ServerRequestInterface $request 159 * 160 * @return ResponseInterface 161 */ 162 public function getListAction(ServerRequestInterface $request): ResponseInterface 163 { 164 return redirect($this->listUrl($request->getAttribute('tree'), $request->getQueryParams())); 165 } 166 167 /** 168 * @param ServerRequestInterface $request 169 * 170 * @return ResponseInterface 171 */ 172 public function handle(ServerRequestInterface $request): ResponseInterface 173 { 174 $tree = $request->getAttribute('tree'); 175 assert($tree instanceof Tree); 176 177 $user = $request->getAttribute('user'); 178 assert($user instanceof UserInterface); 179 180 Auth::checkComponentAccess($this, ModuleListInterface::class, $tree, $user); 181 182 return $this->createResponse($tree, $user, $request->getQueryParams(), false); 183 } 184 185 /** 186 * @param Tree $tree 187 * @param UserInterface $user 188 * @param array<string> $params 189 * @param bool $families 190 * 191 * @return ResponseInterface 192 */ 193 protected function createResponse(Tree $tree, UserInterface $user, array $params, bool $families): ResponseInterface 194 { 195 ob_start(); 196 197 // We show three different lists: initials, surnames and individuals 198 199 // All surnames beginning with this letter where "@"=unknown and ","=none 200 $alpha = $params['alpha'] ?? ''; 201 202 // All individuals with this surname 203 $surname = $params['surname'] ?? ''; 204 205 // All individuals 206 $show_all = $params['show_all'] ?? 'no'; 207 208 // Long lists can be broken down by given name 209 $show_all_firstnames = $params['show_all_firstnames'] ?? 'no'; 210 if ($show_all_firstnames === 'yes') { 211 $falpha = ''; 212 } else { 213 // All first names beginning with this letter 214 $falpha = $params['falpha'] ?? ''; 215 } 216 217 $show_marnm = $params['show_marnm'] ?? ''; 218 switch ($show_marnm) { 219 case 'no': 220 case 'yes': 221 $user->setPreference($families ? 'family-list-marnm' : 'individual-list-marnm', $show_marnm); 222 break; 223 default: 224 $show_marnm = $user->getPreference($families ? 'family-list-marnm' : 'individual-list-marnm'); 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 $alpha = ''; 232 $surname = ''; 233 $legend = I18N::translate('All'); 234 $params = [ 235 'tree' => $tree->name(), 236 'show_all' => 'yes', 237 ]; 238 $show = 'indi'; 239 } elseif ($falpha !== '') { 240 $alpha = ''; 241 $surname = ''; 242 $legend = I18N::translate('All') . ', ' . e($falpha) . '…'; 243 $params = [ 244 'tree' => $tree->name(), 245 'show_all' => 'yes', 246 ]; 247 $show = 'indi'; 248 } else { 249 $alpha = ''; 250 $surname = ''; 251 $legend = I18N::translate('All'); 252 $show = $params['show'] ?? 'surn'; 253 $params = [ 254 'tree' => $tree->name(), 255 'show_all' => 'yes', 256 ]; 257 } 258 } elseif ($surname !== '') { 259 $alpha = $this->localization_service->initialLetter($surname, I18N::locale()); // so we can highlight the initial letter 260 $show_all = 'no'; 261 if ($surname === Individual::NOMEN_NESCIO) { 262 $legend = I18N::translateContext('Unknown surname', '…'); 263 } else { 264 // The surname parameter is a root/canonical form. 265 // Display it as the actual surname 266 $legend = implode('/', array_keys($this->surnames($tree, $surname, $alpha, $show_marnm === 'yes', $families, I18N::locale()))); 267 } 268 $params = [ 269 'tree' => $tree->name(), 270 'surname' => $surname, 271 'falpha' => $falpha, 272 ]; 273 switch ($falpha) { 274 case '': 275 break; 276 case '@': 277 $legend .= ', ' . I18N::translateContext('Unknown given name', '…'); 278 break; 279 default: 280 $legend .= ', ' . e($falpha) . '…'; 281 break; 282 } 283 $show = 'indi'; // SURN list makes no sense here 284 } elseif ($alpha === '@') { 285 $show_all = 'no'; 286 $legend = I18N::translateContext('Unknown surname', '…'); 287 $params = [ 288 'alpha' => $alpha, 289 'tree' => $tree->name(), 290 ]; 291 $show = 'indi'; // SURN list makes no sense here 292 } elseif ($alpha === ',') { 293 $show_all = 'no'; 294 $legend = I18N::translate('None'); 295 $params = [ 296 'alpha' => $alpha, 297 'tree' => $tree->name(), 298 ]; 299 $show = 'indi'; // SURN list makes no sense here 300 } elseif ($alpha !== '') { 301 $show_all = 'no'; 302 $legend = e($alpha) . '…'; 303 $show = $params['show'] ?? 'surn'; 304 $params = [ 305 'alpha' => $alpha, 306 'tree' => $tree->name(), 307 ]; 308 } else { 309 $show_all = 'no'; 310 $legend = '…'; 311 $params = [ 312 'tree' => $tree->name(), 313 ]; 314 $show = 'none'; // Don't show lists until something is chosen 315 } 316 $legend = '<bdi>' . $legend . '</bdi>'; 317 318 if ($families) { 319 $title = I18N::translate('Families') . ' — ' . $legend; 320 } else { 321 $title = I18N::translate('Individuals') . ' — ' . $legend; 322 } ?> 323 <div class="d-flex flex-column wt-page-options wt-page-options-individual-list d-print-none"> 324 <ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-surname"> 325 326 <?php foreach ($this->surnameAlpha($tree, $show_marnm === 'yes', $families, I18N::locale()) as $letter => $count) : ?> 327 <li class="wt-initials-list-item d-flex"> 328 <?php if ($count > 0) : ?> 329 <a href="<?= e($this->listUrl($tree, ['alpha' => $letter, 'tree' => $tree->name()])) ?>" class="wt-initial px-1<?= $letter === $alpha ? ' active' : '' ?> '" title="<?= I18N::number($count) ?>"><?= $this->surnameInitial((string) $letter) ?></a> 330 <?php else : ?> 331 <span class="wt-initial px-1 text-muted"><?= $this->surnameInitial((string) $letter) ?></span> 332 333 <?php endif ?> 334 </li> 335 <?php endforeach ?> 336 337 <?php if (Session::has('initiated')) : ?> 338 <!-- Search spiders don't get the "show all" option as the other links give them everything. --> 339 <li class="wt-initials-list-item d-flex"> 340 <a class="wt-initial px-1<?= $show_all === 'yes' ? ' active' : '' ?>" href="<?= e($this->listUrl($tree, ['show_all' => 'yes'] + $params)) ?>"><?= I18N::translate('All') ?></a> 341 </li> 342 <?php endif ?> 343 </ul> 344 345 <!-- Search spiders don't get an option to show/hide the surname sublists, nor does it make sense on the all/unknown/surname views --> 346 <?php if ($show !== 'none' && Session::has('initiated')) : ?> 347 <?php if ($show_marnm === 'yes') : ?> 348 <p> 349 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'no'] + $params)) ?>"> 350 <?= I18N::translate('Exclude individuals with “%s” as a married name', $legend) ?> 351 </a> 352 </p> 353 <?php else : ?> 354 <p> 355 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'yes'] + $params)) ?>"> 356 <?= I18N::translate('Include individuals with “%s” as a married name', $legend) ?> 357 </a> 358 </p> 359 <?php endif ?> 360 361 <?php if ($alpha !== '@' && $alpha !== ',' && $surname === '') : ?> 362 <?php if ($show === 'surn') : ?> 363 <p> 364 <a href="<?= e($this->listUrl($tree, ['show' => 'indi', 'show_marnm' => 'no'] + $params)) ?>"> 365 <?= I18N::translate('Show the list of individuals') ?> 366 </a> 367 </p> 368 <?php else : ?> 369 <p> 370 <a href="<?= e($this->listUrl($tree, ['show' => 'surn', 'show_marnm' => 'no'] + $params)) ?>"> 371 <?= I18N::translate('Show the list of surnames') ?> 372 </a> 373 </p> 374 <?php endif ?> 375 <?php endif ?> 376 <?php endif ?> 377 </div> 378 379 <div class="wt-page-content"> 380 <?php 381 382 if ($show === 'indi' || $show === 'surn') { 383 $surns = $this->surnames($tree, $surname, $alpha, $show_marnm === 'yes', $families, I18N::locale()); 384 if ($show === 'surn') { 385 // Show the surname list 386 switch ($tree->getPreference('SURNAME_LIST_STYLE')) { 387 case 'style1': 388 echo view('lists/surnames-column-list', [ 389 'module' => $this, 390 'surnames' => $surns, 391 'totals' => true, 392 'tree' => $tree, 393 ]); 394 break; 395 case 'style3': 396 echo view('lists/surnames-tag-cloud', [ 397 'module' => $this, 398 'surnames' => $surns, 399 'totals' => true, 400 'tree' => $tree, 401 ]); 402 break; 403 case 'style2': 404 default: 405 echo view('lists/surnames-table', [ 406 'families' => $families, 407 'module' => $this, 408 'order' => [[1, 'desc']], 409 'surnames' => $surns, 410 'tree' => $tree, 411 ]); 412 break; 413 } 414 } else { 415 // Show the list 416 $count = 0; 417 foreach ($surns as $surnames) { 418 foreach ($surnames as $total) { 419 $count += $total; 420 } 421 } 422 // Don't sublist short lists. 423 if ($count < $tree->getPreference('SUBLIST_TRIGGER_I')) { 424 $falpha = ''; 425 } else { 426 $givn_initials = $this->givenAlpha($tree, $surname, $alpha, $show_marnm === 'yes', $families, I18N::locale()); 427 // Break long lists by initial letter of given name 428 if ($surname !== '' || $show_all === 'yes') { 429 if ($show_all === 'no') { 430 echo '<h2 class="wt-page-title">', I18N::translate('Individuals with surname %s', $legend), '</h2>'; 431 } 432 // Don't show the list until we have some filter criteria 433 $show = $falpha !== '' || $show_all_firstnames === 'yes' ? 'indi' : 'none'; 434 $list = []; 435 echo '<ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-given-names">'; 436 foreach ($givn_initials as $givn_initial => $given_count) { 437 echo '<li class="wt-initials-list-item d-flex">'; 438 if ($given_count > 0) { 439 if ($show === 'indi' && $givn_initial === $falpha && $show_all_firstnames === 'no') { 440 echo '<a class="wt-initial px-1 active" href="' . e($this->listUrl($tree, ['falpha' => $givn_initial] + $params)) . '" title="' . I18N::number($given_count) . '">' . $this->givenNameInitial((string) $givn_initial) . '</a>'; 441 } else { 442 echo '<a class="wt-initial px-1" href="' . e($this->listUrl($tree, ['falpha' => $givn_initial] + $params)) . '" title="' . I18N::number($given_count) . '">' . $this->givenNameInitial((string) $givn_initial) . '</a>'; 443 } 444 } else { 445 echo '<span class="wt-initial px-1 text-muted">' . $this->givenNameInitial((string) $givn_initial) . '</span>'; 446 } 447 echo '</li>'; 448 } 449 // Search spiders don't get the "show all" option as the other links give them everything. 450 if (Session::has('initiated')) { 451 echo '<li class="wt-initials-list-item d-flex">'; 452 if ($show_all_firstnames === 'yes') { 453 echo '<span class="wt-initial px-1 active">' . I18N::translate('All') . '</span>'; 454 } else { 455 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>'; 456 } 457 echo '</li>'; 458 } 459 echo '</ul>'; 460 echo '<p class="text-center alpha_index">', implode(' | ', $list), '</p>'; 461 } 462 } 463 if ($show === 'indi') { 464 if (!$families) { 465 echo view('lists/individuals-table', [ 466 'individuals' => $this->individuals($tree, $surname, $alpha, $falpha, $show_marnm === 'yes', false, I18N::locale()), 467 'sosa' => false, 468 'tree' => $tree, 469 ]); 470 } else { 471 echo view('lists/families-table', [ 472 'families' => $this->families($tree, $surname, $alpha, $falpha, $show_marnm === 'yes', I18N::locale()), 473 'tree' => $tree, 474 ]); 475 } 476 } 477 } 478 } ?> 479 </div> 480 <?php 481 482 $html = ob_get_clean(); 483 484 return $this->viewResponse('modules/individual-list/page', [ 485 'content' => $html, 486 'title' => $title, 487 'tree' => $tree, 488 ]); 489 } 490 491 /** 492 * Some initial letters have a special meaning 493 * 494 * @param string $initial 495 * 496 * @return string 497 */ 498 protected function givenNameInitial(string $initial): string 499 { 500 if ($initial === '@') { 501 return I18N::translateContext('Unknown given name', '…'); 502 } 503 504 return e($initial); 505 } 506 507 /** 508 * Some initial letters have a special meaning 509 * 510 * @param string $initial 511 * 512 * @return string 513 */ 514 protected function surnameInitial(string $initial): string 515 { 516 if ($initial === '@') { 517 return I18N::translateContext('Unknown surname', '…'); 518 } 519 520 if ($initial === ',') { 521 return I18N::translate('None'); 522 } 523 524 return e($initial); 525 } 526 527 /** 528 * Restrict a query to individuals that are a spouse in a family record. 529 * 530 * @param bool $fams 531 * @param Builder $query 532 */ 533 protected function whereFamily(bool $fams, Builder $query): void 534 { 535 if ($fams) { 536 $query->join('link', static function (JoinClause $join): void { 537 $join 538 ->on('l_from', '=', 'n_id') 539 ->on('l_file', '=', 'n_file') 540 ->where('l_type', '=', 'FAMS'); 541 }); 542 } 543 } 544 545 /** 546 * Restrict a query to include/exclude married names. 547 * 548 * @param bool $marnm 549 * @param Builder $query 550 */ 551 protected function whereMarriedName(bool $marnm, Builder $query): void 552 { 553 if (!$marnm) { 554 $query->where('n_type', '<>', '_MARNM'); 555 } 556 } 557 558 /** 559 * Get a list of initial surname letters. 560 * 561 * @param Tree $tree 562 * @param bool $marnm if set, include married names 563 * @param bool $fams if set, only consider individuals with FAMS records 564 * @param LocaleInterface $locale 565 * 566 * @return array<int> 567 */ 568 public function surnameAlpha(Tree $tree, bool $marnm, bool $fams, LocaleInterface $locale): array 569 { 570 $collation = $this->localization_service->collation($locale); 571 572 $n_surn = $this->fieldWithCollation('n_surn', $collation); 573 $alphas = []; 574 575 $query = DB::table('name')->where('n_file', '=', $tree->id()); 576 577 $this->whereFamily($fams, $query); 578 $this->whereMarriedName($marnm, $query); 579 580 // Fetch all the letters in our alphabet, whether or not there 581 // are any names beginning with that letter. It looks better to 582 // show the full alphabet, rather than omitting rare letters such as X. 583 foreach ($this->localization_service->alphabet($locale) as $letter) { 584 $query2 = clone $query; 585 586 $this->whereInitial($query2, 'n_surn', $letter, $locale); 587 588 $alphas[$letter] = $query2->count(); 589 } 590 591 // Now fetch initial letters that are not in our alphabet, 592 // including "@" (for "@N.N.") and "" for no surname. 593 foreach ($this->localization_service->alphabet($locale) as $letter) { 594 $query->where($n_surn, 'NOT LIKE', $letter . '%'); 595 } 596 597 $rows = $query 598 ->groupBy(['initial']) 599 ->orderBy('initial') 600 ->pluck(new Expression('COUNT(*) AS aggregate'), new Expression('SUBSTR(n_surn, 1, 1) AS initial')); 601 602 $specials = ['@', '']; 603 604 foreach ($rows as $alpha => $count) { 605 if (!in_array($alpha, $specials, true)) { 606 $alphas[$alpha] = (int) $count; 607 } 608 } 609 610 // Empty surnames have a special code ',' - as we search for SURN.GIVN 611 foreach ($specials as $special) { 612 if ($rows->has($special)) { 613 $alphas[$special ?: ','] = (int) $rows[$special]; 614 } 615 } 616 617 return $alphas; 618 } 619 620 /** 621 * Get a list of initial given name letters for indilist.php and famlist.php 622 * 623 * @param Tree $tree 624 * @param string $surn if set, only consider people with this surname 625 * @param string $salpha if set, only consider surnames starting with this letter 626 * @param bool $marnm if set, include married names 627 * @param bool $fams if set, only consider individuals with FAMS records 628 * @param LocaleInterface $locale 629 * 630 * @return array<int> 631 */ 632 public function givenAlpha(Tree $tree, string $surn, string $salpha, bool $marnm, bool $fams, LocaleInterface $locale): array 633 { 634 $collation = $this->localization_service->collation($locale); 635 636 $alphas = []; 637 638 $query = DB::table('name') 639 ->where('n_file', '=', $tree->id()); 640 641 $this->whereFamily($fams, $query); 642 $this->whereMarriedName($marnm, $query); 643 644 if ($surn !== '') { 645 $n_surn = $this->fieldWithCollation('n_surn', $collation); 646 $query->where($n_surn, '=', $surn); 647 } elseif ($salpha === ',') { 648 $query->where('n_surn', '=', ''); 649 } elseif ($salpha === '@') { 650 $query->where('n_surn', '=', Individual::NOMEN_NESCIO); 651 } elseif ($salpha !== '') { 652 $this->whereInitial($query, 'n_surn', $salpha, $locale); 653 } else { 654 // All surnames 655 $query->whereNotIn('n_surn', ['', Individual::NOMEN_NESCIO]); 656 } 657 658 // Fetch all the letters in our alphabet, whether or not there 659 // are any names beginning with that letter. It looks better to 660 // show the full alphabet, rather than omitting rare letters such as X 661 foreach ($this->localization_service->alphabet($locale) as $letter) { 662 $query2 = clone $query; 663 664 $this->whereInitial($query2, 'n_givn', $letter, $locale); 665 666 $alphas[$letter] = $query2->distinct()->count('n_id'); 667 } 668 669 $rows = $query 670 ->groupBy(['initial']) 671 ->orderBy('initial') 672 ->pluck(new Expression('COUNT(*) AS aggregate'), new Expression('UPPER(SUBSTR(n_givn, 1, 1)) AS initial')); 673 674 foreach ($rows as $alpha => $count) { 675 if ($alpha !== '@') { 676 $alphas[$alpha] = (int) $count; 677 } 678 } 679 680 if ($rows->has('@')) { 681 $alphas['@'] = (int) $rows['@']; 682 } 683 684 return $alphas; 685 } 686 687 /** 688 * Get a count of actual surnames and variants, based on a "root" surname. 689 * 690 * @param Tree $tree 691 * @param string $surn if set, only count people with this surname 692 * @param string $salpha if set, only consider surnames starting with this letter 693 * @param bool $marnm if set, include married names 694 * @param bool $fams if set, only consider individuals with FAMS records 695 * @param LocaleInterface $locale 696 * 697 * @return array<array<int>> 698 */ 699 public function surnames( 700 Tree $tree, 701 string $surn, 702 string $salpha, 703 bool $marnm, 704 bool $fams, 705 LocaleInterface $locale 706 ): array { 707 $collation = $this->localization_service->collation($locale); 708 709 $query = DB::table('name') 710 ->where('n_file', '=', $tree->id()) 711 ->select([ 712 new Expression('UPPER(n_surn /*! COLLATE ' . $collation . ' */) AS n_surn'), 713 new Expression('n_surname /*! COLLATE utf8_bin */ AS n_surname'), 714 new Expression('COUNT(*) AS total'), 715 ]); 716 717 $this->whereFamily($fams, $query); 718 $this->whereMarriedName($marnm, $query); 719 720 if ($surn !== '') { 721 $query->where('n_surn', '=', $surn); 722 } elseif ($salpha === ',') { 723 $query->where('n_surn', '=', ''); 724 } elseif ($salpha === '@') { 725 $query->where('n_surn', '=', Individual::NOMEN_NESCIO); 726 } elseif ($salpha !== '') { 727 $this->whereInitial($query, 'n_surn', $salpha, $locale); 728 } else { 729 // All surnames 730 $query->whereNotIn('n_surn', ['', Individual::NOMEN_NESCIO]); 731 } 732 $query 733 ->groupBy(['n_surn']) 734 ->groupBy(['n_surname']) 735 ->orderBy('n_surname'); 736 737 $list = []; 738 739 foreach ($query->get() as $row) { 740 $list[$row->n_surn][$row->n_surname] = (int) $row->total; 741 } 742 743 return $list; 744 } 745 746 /** 747 * Fetch a list of individuals with specified names 748 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 749 * To search for names with no surnames, use $salpha="," 750 * 751 * @param Tree $tree 752 * @param string $surn if set, only fetch people with this surname 753 * @param string $salpha if set, only fetch surnames starting with this letter 754 * @param string $galpha if set, only fetch given names starting with this letter 755 * @param bool $marnm if set, include married names 756 * @param bool $fams if set, only fetch individuals with FAMS records 757 * @param LocaleInterface $locale 758 * 759 * @return array<Individual> 760 */ 761 public function individuals( 762 Tree $tree, 763 string $surn, 764 string $salpha, 765 string $galpha, 766 bool $marnm, 767 bool $fams, 768 LocaleInterface $locale 769 ): array { 770 $collation = $this->localization_service->collation($locale); 771 772 // Use specific collation for name fields. 773 $n_givn = $this->fieldWithCollation('n_givn', $collation); 774 $n_surn = $this->fieldWithCollation('n_surn', $collation); 775 776 $query = DB::table('individuals') 777 ->join('name', static function (JoinClause $join): void { 778 $join 779 ->on('n_id', '=', 'i_id') 780 ->on('n_file', '=', 'i_file'); 781 }) 782 ->where('i_file', '=', $tree->id()) 783 ->select(['i_id AS xref', 'i_gedcom AS gedcom', 'n_givn', 'n_surn']); 784 785 $this->whereFamily($fams, $query); 786 $this->whereMarriedName($marnm, $query); 787 788 if ($surn) { 789 $query->where($n_surn, '=', $surn); 790 } elseif ($salpha === ',') { 791 $query->where($n_surn, '=', ''); 792 } elseif ($salpha === '@') { 793 $query->where($n_surn, '=', Individual::NOMEN_NESCIO); 794 } elseif ($salpha) { 795 $this->whereInitial($query, 'n_surn', $salpha, $locale); 796 } else { 797 // All surnames 798 $query->whereNotIn($n_surn, ['', Individual::NOMEN_NESCIO]); 799 } 800 if ($galpha) { 801 $this->whereInitial($query, 'n_givn', $galpha, $locale); 802 } 803 804 $query 805 ->orderBy(new Expression("CASE n_surn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 806 ->orderBy($n_surn) 807 ->orderBy(new Expression("CASE n_givn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 808 ->orderBy($n_givn); 809 810 $list = []; 811 $rows = $query->get(); 812 813 foreach ($rows as $row) { 814 $individual = Registry::individualFactory()->make($row->xref, $tree, $row->gedcom); 815 assert($individual instanceof Individual); 816 817 // The name from the database may be private - check the filtered list... 818 foreach ($individual->getAllNames() as $n => $name) { 819 if ($name['givn'] === $row->n_givn && $name['surn'] === $row->n_surn) { 820 $individual->setPrimaryName($n); 821 // We need to clone $individual, as we may have multiple references to the 822 // same individual in this list, and the "primary name" would otherwise 823 // be shared amongst all of them. 824 $list[] = clone $individual; 825 break; 826 } 827 } 828 } 829 830 return $list; 831 } 832 833 /** 834 * Fetch a list of families with specified names 835 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 836 * To search for names with no surnames, use $salpha="," 837 * 838 * @param Tree $tree 839 * @param string $surn if set, only fetch people with this surname 840 * @param string $salpha if set, only fetch surnames starting with this letter 841 * @param string $galpha if set, only fetch given names starting with this letter 842 * @param bool $marnm if set, include married names 843 * @param LocaleInterface $locale 844 * 845 * @return array<Family> 846 */ 847 public function families(Tree $tree, string $surn, string $salpha, string $galpha, bool $marnm, LocaleInterface $locale): array 848 { 849 $list = []; 850 foreach ($this->individuals($tree, $surn, $salpha, $galpha, $marnm, true, $locale) as $indi) { 851 foreach ($indi->spouseFamilies() as $family) { 852 $list[$family->xref()] = $family; 853 } 854 } 855 usort($list, GedcomRecord::nameComparator()); 856 857 return $list; 858 } 859 860 /** 861 * Use MySQL-specific comments so we can run these queries on other RDBMS. 862 * 863 * @param string $field 864 * @param string $collation 865 * 866 * @return Expression 867 */ 868 protected function fieldWithCollation(string $field, string $collation): Expression 869 { 870 return new Expression($field . ' /*! COLLATE ' . $collation . ' */'); 871 } 872 873 /** 874 * Modify a query to restrict a field to a given initial letter. 875 * Take account of digraphs, equialent letters, etc. 876 * 877 * @param Builder $query 878 * @param string $field 879 * @param string $letter 880 * @param LocaleInterface $locale 881 * 882 * @return void 883 */ 884 protected function whereInitial( 885 Builder $query, 886 string $field, 887 string $letter, 888 LocaleInterface $locale 889 ): void { 890 $collation = $this->localization_service->collation($locale); 891 892 // Use MySQL-specific comments so we can run these queries on other RDBMS. 893 $field_with_collation = $this->fieldWithCollation($field, $collation); 894 895 switch ($locale->languageTag()) { 896 case 'cs': 897 $this->whereInitialCzech($query, $field_with_collation, $letter); 898 break; 899 900 case 'da': 901 case 'nb': 902 case 'nn': 903 $this->whereInitialNorwegian($query, $field_with_collation, $letter); 904 break; 905 906 case 'sv': 907 case 'fi': 908 $this->whereInitialSwedish($query, $field_with_collation, $letter); 909 break; 910 911 case 'hu': 912 $this->whereInitialHungarian($query, $field_with_collation, $letter); 913 break; 914 915 case 'nl': 916 $this->whereInitialDutch($query, $field_with_collation, $letter); 917 break; 918 919 default: 920 $query->where($field_with_collation, 'LIKE', '\\' . $letter . '%'); 921 } 922 } 923 924 /** 925 * @param Builder $query 926 * @param Expression $field 927 * @param string $letter 928 */ 929 protected function whereInitialCzech(Builder $query, Expression $field, string $letter): void 930 { 931 if ($letter === 'C') { 932 $query->where($field, 'LIKE', 'C%')->where($field, 'NOT LIKE', 'CH%'); 933 } else { 934 $query->where($field, 'LIKE', '\\' . $letter . '%'); 935 } 936 } 937 938 /** 939 * @param Builder $query 940 * @param Expression $field 941 * @param string $letter 942 */ 943 protected function whereInitialDutch(Builder $query, Expression $field, string $letter): void 944 { 945 if ($letter === 'I') { 946 $query->where($field, 'LIKE', 'I%')->where($field, 'NOT LIKE', 'IJ%'); 947 } else { 948 $query->where($field, 'LIKE', '\\' . $letter . '%'); 949 } 950 } 951 952 /** 953 * Hungarian has many digraphs and trigraphs, so exclude these from prefixes. 954 * 955 * @param Builder $query 956 * @param Expression $field 957 * @param string $letter 958 */ 959 protected function whereInitialHungarian(Builder $query, Expression $field, string $letter): void 960 { 961 switch ($letter) { 962 case 'C': 963 $query->where($field, 'LIKE', 'C%')->where($field, 'NOT LIKE', 'CS%'); 964 break; 965 966 case 'D': 967 $query->where($field, 'LIKE', 'D%')->where($field, 'NOT LIKE', 'DZ%'); 968 break; 969 970 case 'DZ': 971 $query->where($field, 'LIKE', 'DZ%')->where($field, 'NOT LIKE', 'DZS%'); 972 break; 973 974 case 'G': 975 $query->where($field, 'LIKE', 'G%')->where($field, 'NOT LIKE', 'GY%'); 976 break; 977 978 case 'L': 979 $query->where($field, 'LIKE', 'L%')->where($field, 'NOT LIKE', 'LY%'); 980 break; 981 982 case 'N': 983 $query->where($field, 'LIKE', 'N%')->where($field, 'NOT LIKE', 'NY%'); 984 break; 985 986 case 'S': 987 $query->where($field, 'LIKE', 'S%')->where($field, 'NOT LIKE', 'SZ%'); 988 break; 989 990 case 'T': 991 $query->where($field, 'LIKE', 'T%')->where($field, 'NOT LIKE', 'TY%'); 992 break; 993 994 case 'Z': 995 $query->where($field, 'LIKE', 'Z%')->where($field, 'NOT LIKE', 'ZS%'); 996 break; 997 998 default: 999 $query->where($field, 'LIKE', '\\' . $letter . '%'); 1000 break; 1001 } 1002 } 1003 1004 /** 1005 * In Norwegian and Danish, AA gets listed under Å, NOT A 1006 * 1007 * @param Builder $query 1008 * @param Expression $field 1009 * @param string $letter 1010 */ 1011 protected function whereInitialNorwegian(Builder $query, Expression $field, string $letter): void 1012 { 1013 switch ($letter) { 1014 case 'A': 1015 $query->where($field, 'LIKE', 'A%')->where($field, 'NOT LIKE', 'AA%'); 1016 break; 1017 1018 case 'Å': 1019 $query->where(static function (Builder $query) use ($field): void { 1020 $query 1021 ->where($field, 'LIKE', 'Å%') 1022 ->orWhere($field, 'LIKE', 'AA%'); 1023 }); 1024 break; 1025 1026 default: 1027 $query->where($field, 'LIKE', '\\' . $letter . '%'); 1028 break; 1029 } 1030 } 1031 1032 /** 1033 * In Swedish and Finnish, AA gets listed under A, NOT Å (even though Swedish collation says they should). 1034 * 1035 * @param Builder $query 1036 * @param Expression $field 1037 * @param string $letter 1038 */ 1039 protected function whereInitialSwedish(Builder $query, Expression $field, string $letter): void 1040 { 1041 if ($letter === 'Å') { 1042 $query->where($field, 'LIKE', 'Å%')->where($field, 'NOT LIKE', 'AA%'); 1043 } else { 1044 $query->where($field, 'LIKE', '\\' . $letter . '%'); 1045 } 1046 } 1047} 1048