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