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 if ($show_all_firstnames === 'yes') { 229 $alpha = ''; 230 $surname = ''; 231 $legend = I18N::translate('All'); 232 $params = [ 233 'tree' => $tree->name(), 234 'show_all' => 'yes', 235 ]; 236 $show = 'indi'; 237 } elseif ($falpha !== '') { 238 $alpha = ''; 239 $surname = ''; 240 $legend = I18N::translate('All') . ', ' . e($falpha) . '…'; 241 $params = [ 242 'tree' => $tree->name(), 243 'show_all' => 'yes', 244 ]; 245 $show = 'indi'; 246 } else { 247 $alpha = ''; 248 $surname = ''; 249 $legend = I18N::translate('All'); 250 $show = $params['show'] ?? 'surn'; 251 $params = [ 252 'tree' => $tree->name(), 253 'show_all' => 'yes', 254 ]; 255 } 256 } elseif ($surname !== '') { 257 $alpha = $this->localization_service->initialLetter($surname, I18N::locale()); // so we can highlight the initial letter 258 $show_all = 'no'; 259 if ($surname === Individual::NOMEN_NESCIO) { 260 $legend = I18N::translateContext('Unknown surname', '…'); 261 } else { 262 // The surname parameter is a root/canonical form. 263 // Display it as the actual surname 264 $legend = implode('/', array_keys($this->surnames($tree, $surname, $alpha, $show_marnm === 'yes', $families, I18N::locale()))); 265 } 266 $params = [ 267 'tree' => $tree->name(), 268 'surname' => $surname, 269 'falpha' => $falpha, 270 ]; 271 switch ($falpha) { 272 case '': 273 break; 274 case '@': 275 $legend .= ', ' . I18N::translateContext('Unknown given name', '…'); 276 break; 277 default: 278 $legend .= ', ' . e($falpha) . '…'; 279 break; 280 } 281 $show = 'indi'; // SURN list makes no sense here 282 } elseif ($alpha === '@') { 283 $show_all = 'no'; 284 $legend = I18N::translateContext('Unknown surname', '…'); 285 $params = [ 286 'alpha' => $alpha, 287 'tree' => $tree->name(), 288 ]; 289 $show = 'indi'; // SURN list makes no sense here 290 } elseif ($alpha === ',') { 291 $show_all = 'no'; 292 $legend = I18N::translate('No surname'); 293 $params = [ 294 'alpha' => $alpha, 295 'tree' => $tree->name(), 296 ]; 297 $show = 'indi'; // SURN list makes no sense here 298 } elseif ($alpha !== '') { 299 $show_all = 'no'; 300 $legend = e($alpha) . '…'; 301 $show = $params['show'] ?? 'surn'; 302 $params = [ 303 'alpha' => $alpha, 304 'tree' => $tree->name(), 305 ]; 306 } else { 307 $show_all = 'no'; 308 $legend = '…'; 309 $params = [ 310 'tree' => $tree->name(), 311 ]; 312 $show = 'none'; // Don't show lists until something is chosen 313 } 314 $legend = '<bdi>' . $legend . '</bdi>'; 315 316 if ($families) { 317 $title = I18N::translate('Families') . ' — ' . $legend; 318 } else { 319 $title = I18N::translate('Individuals') . ' — ' . $legend; 320 } ?> 321 <div class="d-flex flex-column wt-page-options wt-page-options-individual-list d-print-none"> 322 <ul class="d-flex flex-wrap list-unstyled justify-content-center wt-initials-list wt-initials-list-surname"> 323 324 <?php foreach ($this->surnameAlpha($tree, $show_marnm === 'yes', $families, I18N::locale()) as $letter => $count) : ?> 325 <li class="wt-initials-list-item d-flex"> 326 <?php if ($count > 0) : ?> 327 <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> 328 <?php else : ?> 329 <span class="wt-initial px-1 text-muted"><?= $this->surnameInitial((string) $letter) ?></span> 330 331 <?php endif ?> 332 </li> 333 <?php endforeach ?> 334 335 <?php if (Session::has('initiated')) : ?> 336 <!-- Search spiders don't get the "show all" option as the other links give them everything. --> 337 <li class="wt-initials-list-item d-flex"> 338 <a class="wt-initial px-1<?= $show_all === 'yes' ? ' active' : '' ?>" href="<?= e($this->listUrl($tree, ['show_all' => 'yes'] + $params)) ?>"><?= I18N::translate('All') ?></a> 339 </li> 340 <?php endif ?> 341 </ul> 342 343 <!-- Search spiders don't get an option to show/hide the surname sublists, nor does it make sense on the all/unknown/surname views --> 344 <?php if ($show !== 'none' && Session::has('initiated')) : ?> 345 <?php if ($show_marnm === 'yes') : ?> 346 <p> 347 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'no'] + $params)) ?>"> 348 <?= I18N::translate('Exclude individuals with “%s” as a married name', $legend) ?> 349 </a> 350 </p> 351 <?php else : ?> 352 <p> 353 <a href="<?= e($this->listUrl($tree, ['show' => $show, 'show_marnm' => 'yes'] + $params)) ?>"> 354 <?= I18N::translate('Include individuals with “%s” as a married name', $legend) ?> 355 </a> 356 </p> 357 <?php endif ?> 358 359 <?php if ($alpha !== '@' && $alpha !== ',' && $surname === '') : ?> 360 <?php if ($show === 'surn') : ?> 361 <p> 362 <a href="<?= e($this->listUrl($tree, ['show' => 'indi', 'show_marnm' => 'no'] + $params)) ?>"> 363 <?= I18N::translate('Show the list of individuals') ?> 364 </a> 365 </p> 366 <?php else : ?> 367 <p> 368 <a href="<?= e($this->listUrl($tree, ['show' => 'surn', 'show_marnm' => 'no'] + $params)) ?>"> 369 <?= I18N::translate('Show the list of surnames') ?> 370 </a> 371 </p> 372 <?php endif ?> 373 <?php endif ?> 374 <?php endif ?> 375 </div> 376 377 <div class="wt-page-content"> 378 <?php 379 380 if ($show === 'indi' || $show === 'surn') { 381 $surns = $this->surnames($tree, $surname, $alpha, $show_marnm === 'yes', $families, I18N::locale()); 382 if ($show === 'surn') { 383 // Show the surname list 384 switch ($tree->getPreference('SURNAME_LIST_STYLE')) { 385 case 'style1': 386 echo view('lists/surnames-column-list', [ 387 'module' => $this, 388 'surnames' => $surns, 389 'totals' => true, 390 'tree' => $tree, 391 ]); 392 break; 393 case 'style3': 394 echo view('lists/surnames-tag-cloud', [ 395 'module' => $this, 396 'surnames' => $surns, 397 'totals' => true, 398 'tree' => $tree, 399 ]); 400 break; 401 case 'style2': 402 default: 403 echo view('lists/surnames-table', [ 404 'families' => $families, 405 'module' => $this, 406 'order' => [[1, 'desc']], 407 'surnames' => $surns, 408 'tree' => $tree, 409 ]); 410 break; 411 } 412 } else { 413 // Show the list 414 $count = 0; 415 foreach ($surns as $surnames) { 416 foreach ($surnames as $total) { 417 $count += $total; 418 } 419 } 420 // Don't sublist short lists. 421 if ($count < $tree->getPreference('SUBLIST_TRIGGER_I')) { 422 $falpha = ''; 423 } else { 424 $givn_initials = $this->givenAlpha($tree, $surname, $alpha, $show_marnm === 'yes', $families, I18N::locale()); 425 // Break long lists by initial letter of given name 426 if ($surname !== '' || $show_all === 'yes') { 427 if ($show_all === 'no') { 428 echo '<h2 class="wt-page-title">', I18N::translate('Individuals with surname %s', $legend), '</h2>'; 429 } 430 // Don't show the list until we have some filter criteria 431 $show = $falpha !== '' || $show_all_firstnames === 'yes' ? 'indi' : 'none'; 432 $list = []; 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 === 'no') { 438 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>'; 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->givenNameInitial((string) $givn_initial) . '</a>'; 441 } 442 } else { 443 echo '<span class="wt-initial px-1 text-muted">' . $this->givenNameInitial((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 echo '<p class="text-center alpha_index">', implode(' | ', $list), '</p>'; 459 } 460 } 461 if ($show === 'indi') { 462 if (!$families) { 463 echo view('lists/individuals-table', [ 464 'individuals' => $this->individuals($tree, $surname, $alpha, $falpha, $show_marnm === 'yes', false, I18N::locale()), 465 'sosa' => false, 466 'tree' => $tree, 467 ]); 468 } else { 469 echo view('lists/families-table', [ 470 'families' => $this->families($tree, $surname, $alpha, $falpha, $show_marnm === 'yes', I18N::locale()), 471 'tree' => $tree, 472 ]); 473 } 474 } 475 } 476 } ?> 477 </div> 478 <?php 479 480 $html = ob_get_clean(); 481 482 return $this->viewResponse('modules/individual-list/page', [ 483 'content' => $html, 484 'title' => $title, 485 'tree' => $tree, 486 ]); 487 } 488 489 /** 490 * Some initial letters have a special meaning 491 * 492 * @param string $initial 493 * 494 * @return string 495 */ 496 protected function givenNameInitial(string $initial): string 497 { 498 if ($initial === '@') { 499 return I18N::translateContext('Unknown given name', '…'); 500 } 501 502 return e($initial); 503 } 504 505 /** 506 * Some initial letters have a special meaning 507 * 508 * @param string $initial 509 * 510 * @return string 511 */ 512 protected function surnameInitial(string $initial): string 513 { 514 if ($initial === '@') { 515 return I18N::translateContext('Unknown surname', '…'); 516 } 517 518 if ($initial === ',') { 519 return I18N::translate('No surname'); 520 } 521 522 return e($initial); 523 } 524 525 /** 526 * Restrict a query to individuals that are a spouse in a family record. 527 * 528 * @param bool $fams 529 * @param Builder $query 530 */ 531 protected function whereFamily(bool $fams, Builder $query): void 532 { 533 if ($fams) { 534 $query->join('link', static function (JoinClause $join): void { 535 $join 536 ->on('l_from', '=', 'n_id') 537 ->on('l_file', '=', 'n_file') 538 ->where('l_type', '=', 'FAMS'); 539 }); 540 } 541 } 542 543 /** 544 * Restrict a query to include/exclude married names. 545 * 546 * @param bool $marnm 547 * @param Builder $query 548 */ 549 protected function whereMarriedName(bool $marnm, Builder $query): void 550 { 551 if (!$marnm) { 552 $query->where('n_type', '<>', '_MARNM'); 553 } 554 } 555 556 /** 557 * Get a list of initial surname letters. 558 * 559 * @param Tree $tree 560 * @param bool $marnm if set, include married names 561 * @param bool $fams if set, only consider individuals with FAMS records 562 * @param LocaleInterface $locale 563 * 564 * @return array<int> 565 */ 566 public function surnameAlpha(Tree $tree, bool $marnm, bool $fams, LocaleInterface $locale): array 567 { 568 $collation = $this->localization_service->collation($locale); 569 570 $n_surn = $this->fieldWithCollation('n_surn', $collation); 571 $alphas = []; 572 573 $query = DB::table('name')->where('n_file', '=', $tree->id()); 574 575 $this->whereFamily($fams, $query); 576 $this->whereMarriedName($marnm, $query); 577 578 // Fetch all the letters in our alphabet, whether or not there 579 // are any names beginning with that letter. It looks better to 580 // show the full alphabet, rather than omitting rare letters such as X. 581 foreach ($this->localization_service->alphabet($locale) as $letter) { 582 $query2 = clone $query; 583 584 $this->whereInitial($query2, 'n_surn', $letter, $locale); 585 586 $alphas[$letter] = $query2->count(); 587 } 588 589 // Now fetch initial letters that are not in our alphabet, 590 // including "@" (for "@N.N.") and "" for no surname. 591 foreach ($this->localization_service->alphabet($locale) as $letter) { 592 $query->where($n_surn, 'NOT LIKE', $letter . '%'); 593 } 594 595 $rows = $query 596 ->groupBy(['initial']) 597 ->orderBy('initial') 598 ->pluck(new Expression('COUNT(*) AS aggregate'), new Expression('SUBSTR(n_surn, 1, 1) AS initial')); 599 600 $specials = ['@', '']; 601 602 foreach ($rows as $alpha => $count) { 603 if (!in_array($alpha, $specials, true)) { 604 $alphas[$alpha] = (int) $count; 605 } 606 } 607 608 // Empty surnames have a special code ',' - as we search for SURN.GIVN 609 foreach ($specials as $special) { 610 if ($rows->has($special)) { 611 $alphas[$special ?: ','] = (int) $rows[$special]; 612 } 613 } 614 615 return $alphas; 616 } 617 618 /** 619 * Get a list of initial given name letters for indilist.php and famlist.php 620 * 621 * @param Tree $tree 622 * @param string $surn if set, only consider people with this surname 623 * @param string $salpha if set, only consider surnames starting with this letter 624 * @param bool $marnm if set, include married names 625 * @param bool $fams if set, only consider individuals with FAMS records 626 * @param LocaleInterface $locale 627 * 628 * @return array<int> 629 */ 630 public function givenAlpha(Tree $tree, string $surn, string $salpha, bool $marnm, bool $fams, LocaleInterface $locale): array 631 { 632 $collation = $this->localization_service->collation($locale); 633 634 $alphas = []; 635 636 $query = DB::table('name') 637 ->where('n_file', '=', $tree->id()); 638 639 $this->whereFamily($fams, $query); 640 $this->whereMarriedName($marnm, $query); 641 642 if ($surn !== '') { 643 $n_surn = $this->fieldWithCollation('n_surn', $collation); 644 $query->where($n_surn, '=', $surn); 645 } elseif ($salpha === ',') { 646 $query->where('n_surn', '=', ''); 647 } elseif ($salpha === '@') { 648 $query->where('n_surn', '=', Individual::NOMEN_NESCIO); 649 } elseif ($salpha !== '') { 650 $this->whereInitial($query, 'n_surn', $salpha, $locale); 651 } else { 652 // All surnames 653 $query->whereNotIn('n_surn', ['', Individual::NOMEN_NESCIO]); 654 } 655 656 // Fetch all the letters in our alphabet, whether or not there 657 // are any names beginning with that letter. It looks better to 658 // show the full alphabet, rather than omitting rare letters such as X 659 foreach ($this->localization_service->alphabet($locale) as $letter) { 660 $query2 = clone $query; 661 662 $this->whereInitial($query2, 'n_givn', $letter, $locale); 663 664 $alphas[$letter] = $query2->distinct()->count('n_id'); 665 } 666 667 $rows = $query 668 ->groupBy(['initial']) 669 ->orderBy('initial') 670 ->pluck(new Expression('COUNT(*) AS aggregate'), new Expression('UPPER(SUBSTR(n_givn, 1, 1)) AS initial')); 671 672 foreach ($rows as $alpha => $count) { 673 if ($alpha !== '@') { 674 $alphas[$alpha] = (int) $count; 675 } 676 } 677 678 if ($rows->has('@')) { 679 $alphas['@'] = (int) $rows['@']; 680 } 681 682 return $alphas; 683 } 684 685 /** 686 * Get a count of actual surnames and variants, based on a "root" surname. 687 * 688 * @param Tree $tree 689 * @param string $surn if set, only count people with this surname 690 * @param string $salpha if set, only consider surnames starting with this letter 691 * @param bool $marnm if set, include married names 692 * @param bool $fams if set, only consider individuals with FAMS records 693 * @param LocaleInterface $locale 694 * 695 * @return array<array<int>> 696 */ 697 protected function surnames( 698 Tree $tree, 699 string $surn, 700 string $salpha, 701 bool $marnm, 702 bool $fams, 703 LocaleInterface $locale 704 ): array { 705 $collation = $this->localization_service->collation($locale); 706 707 $query = DB::table('name') 708 ->where('n_file', '=', $tree->id()) 709 ->select([ 710 new Expression('UPPER(n_surn /*! COLLATE ' . $collation . ' */) AS n_surn'), 711 new Expression('n_surname /*! COLLATE utf8_bin */ AS n_surname'), 712 new Expression('COUNT(*) AS total'), 713 ]); 714 715 $this->whereFamily($fams, $query); 716 $this->whereMarriedName($marnm, $query); 717 718 if ($surn !== '') { 719 $query->where('n_surn', '=', $surn); 720 } elseif ($salpha === ',') { 721 $query->where('n_surn', '=', ''); 722 } elseif ($salpha === '@') { 723 $query->where('n_surn', '=', Individual::NOMEN_NESCIO); 724 } elseif ($salpha !== '') { 725 $this->whereInitial($query, 'n_surn', $salpha, $locale); 726 } else { 727 // All surnames 728 $query->whereNotIn('n_surn', ['', Individual::NOMEN_NESCIO]); 729 } 730 $query 731 ->groupBy(['n_surn']) 732 ->groupBy(['n_surname']) 733 ->orderBy('n_surname'); 734 735 $list = []; 736 737 foreach ($query->get() as $row) { 738 $list[$row->n_surn][$row->n_surname] = (int) $row->total; 739 } 740 741 return $list; 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 string $surn if set, only fetch people with this surname 751 * @param string $salpha if set, only fetch surnames starting with this letter 752 * @param string $galpha if set, only fetch given names starting with this letter 753 * @param bool $marnm if set, include married names 754 * @param bool $fams if set, only fetch individuals with FAMS records 755 * @param LocaleInterface $locale 756 * 757 * @return Collection<Individual> 758 */ 759 protected function individuals( 760 Tree $tree, 761 string $surn, 762 string $salpha, 763 string $galpha, 764 bool $marnm, 765 bool $fams, 766 LocaleInterface $locale 767 ): Collection { 768 $collation = $this->localization_service->collation($locale); 769 770 // Use specific collation for name fields. 771 $n_givn = $this->fieldWithCollation('n_givn', $collation); 772 $n_surn = $this->fieldWithCollation('n_surn', $collation); 773 774 $query = DB::table('individuals') 775 ->join('name', static function (JoinClause $join): void { 776 $join 777 ->on('n_id', '=', 'i_id') 778 ->on('n_file', '=', 'i_file'); 779 }) 780 ->where('i_file', '=', $tree->id()) 781 ->select(['i_id AS xref', 'i_gedcom AS gedcom', 'n_givn', 'n_surn']); 782 783 $this->whereFamily($fams, $query); 784 $this->whereMarriedName($marnm, $query); 785 786 if ($surn) { 787 $query->where($n_surn, '=', $surn); 788 } elseif ($salpha === ',') { 789 $query->where($n_surn, '=', ''); 790 } elseif ($salpha === '@') { 791 $query->where($n_surn, '=', Individual::NOMEN_NESCIO); 792 } elseif ($salpha) { 793 $this->whereInitial($query, 'n_surn', $salpha, $locale); 794 } else { 795 // All surnames 796 $query->whereNotIn($n_surn, ['', Individual::NOMEN_NESCIO]); 797 } 798 if ($galpha) { 799 $this->whereInitial($query, 'n_givn', $galpha, $locale); 800 } 801 802 $query 803 ->orderBy(new Expression("CASE n_surn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 804 ->orderBy($n_surn) 805 ->orderBy(new Expression("CASE n_givn WHEN '" . Individual::NOMEN_NESCIO . "' THEN 1 ELSE 0 END")) 806 ->orderBy($n_givn); 807 808 $individuals = new Collection(); 809 $rows = $query->get(); 810 811 foreach ($rows as $row) { 812 $individual = Registry::individualFactory()->make($row->xref, $tree, $row->gedcom); 813 assert($individual instanceof Individual); 814 815 // The name from the database may be private - check the filtered list... 816 foreach ($individual->getAllNames() as $n => $name) { 817 if ($name['givn'] === $row->n_givn && $name['surn'] === $row->n_surn) { 818 $individual->setPrimaryName($n); 819 // We need to clone $individual, as we may have multiple references to the 820 // same individual in this list, and the "primary name" would otherwise 821 // be shared amongst all of them. 822 $individuals->push(clone $individual); 823 break; 824 } 825 } 826 } 827 828 return $individuals; 829 } 830 831 /** 832 * Fetch a list of families with specified names 833 * To search for unknown names, use $surn="@N.N.", $salpha="@" or $galpha="@" 834 * To search for names with no surnames, use $salpha="," 835 * 836 * @param Tree $tree 837 * @param string $surn if set, only fetch people with this surname 838 * @param string $salpha if set, only fetch surnames starting with this letter 839 * @param string $galpha if set, only fetch given names starting with this letter 840 * @param bool $marnm if set, include married names 841 * @param LocaleInterface $locale 842 * 843 * @return Collection<Family> 844 */ 845 protected function families(Tree $tree, string $surn, string $salpha, string $galpha, bool $marnm, LocaleInterface $locale): Collection 846 { 847 $families = new Collection(); 848 849 foreach ($this->individuals($tree, $surn, $salpha, $galpha, $marnm, true, $locale) as $indi) { 850 foreach ($indi->spouseFamilies() as $family) { 851 $families->push($family); 852 } 853 } 854 855 return $families->unique(); 856 } 857 858 /** 859 * Use MySQL-specific comments so we can run these queries on other RDBMS. 860 * 861 * @param string $field 862 * @param string $collation 863 * 864 * @return Expression 865 */ 866 protected function fieldWithCollation(string $field, string $collation): Expression 867 { 868 return new Expression($field . ' /*! COLLATE ' . $collation . ' */'); 869 } 870 871 /** 872 * Modify a query to restrict a field to a given initial letter. 873 * Take account of digraphs, equialent letters, etc. 874 * 875 * @param Builder $query 876 * @param string $field 877 * @param string $letter 878 * @param LocaleInterface $locale 879 * 880 * @return void 881 */ 882 protected function whereInitial( 883 Builder $query, 884 string $field, 885 string $letter, 886 LocaleInterface $locale 887 ): void { 888 $collation = $this->localization_service->collation($locale); 889 890 // Use MySQL-specific comments so we can run these queries on other RDBMS. 891 $field_with_collation = $this->fieldWithCollation($field, $collation); 892 893 switch ($locale->languageTag()) { 894 case 'cs': 895 $this->whereInitialCzech($query, $field_with_collation, $letter); 896 break; 897 898 case 'da': 899 case 'nb': 900 case 'nn': 901 $this->whereInitialNorwegian($query, $field_with_collation, $letter); 902 break; 903 904 case 'sv': 905 case 'fi': 906 $this->whereInitialSwedish($query, $field_with_collation, $letter); 907 break; 908 909 case 'hu': 910 $this->whereInitialHungarian($query, $field_with_collation, $letter); 911 break; 912 913 case 'nl': 914 $this->whereInitialDutch($query, $field_with_collation, $letter); 915 break; 916 917 default: 918 $query->where($field_with_collation, 'LIKE', '\\' . $letter . '%'); 919 } 920 } 921 922 /** 923 * @param Builder $query 924 * @param Expression $field 925 * @param string $letter 926 */ 927 protected function whereInitialCzech(Builder $query, Expression $field, string $letter): void 928 { 929 if ($letter === 'C') { 930 $query->where($field, 'LIKE', 'C%')->where($field, 'NOT LIKE', 'CH%'); 931 } else { 932 $query->where($field, 'LIKE', '\\' . $letter . '%'); 933 } 934 } 935 936 /** 937 * @param Builder $query 938 * @param Expression $field 939 * @param string $letter 940 */ 941 protected function whereInitialDutch(Builder $query, Expression $field, string $letter): void 942 { 943 if ($letter === 'I') { 944 $query->where($field, 'LIKE', 'I%')->where($field, 'NOT LIKE', 'IJ%'); 945 } else { 946 $query->where($field, 'LIKE', '\\' . $letter . '%'); 947 } 948 } 949 950 /** 951 * Hungarian has many digraphs and trigraphs, so exclude these from prefixes. 952 * 953 * @param Builder $query 954 * @param Expression $field 955 * @param string $letter 956 */ 957 protected function whereInitialHungarian(Builder $query, Expression $field, string $letter): void 958 { 959 switch ($letter) { 960 case 'C': 961 $query->where($field, 'LIKE', 'C%')->where($field, 'NOT LIKE', 'CS%'); 962 break; 963 964 case 'D': 965 $query->where($field, 'LIKE', 'D%')->where($field, 'NOT LIKE', 'DZ%'); 966 break; 967 968 case 'DZ': 969 $query->where($field, 'LIKE', 'DZ%')->where($field, 'NOT LIKE', 'DZS%'); 970 break; 971 972 case 'G': 973 $query->where($field, 'LIKE', 'G%')->where($field, 'NOT LIKE', 'GY%'); 974 break; 975 976 case 'L': 977 $query->where($field, 'LIKE', 'L%')->where($field, 'NOT LIKE', 'LY%'); 978 break; 979 980 case 'N': 981 $query->where($field, 'LIKE', 'N%')->where($field, 'NOT LIKE', 'NY%'); 982 break; 983 984 case 'S': 985 $query->where($field, 'LIKE', 'S%')->where($field, 'NOT LIKE', 'SZ%'); 986 break; 987 988 case 'T': 989 $query->where($field, 'LIKE', 'T%')->where($field, 'NOT LIKE', 'TY%'); 990 break; 991 992 case 'Z': 993 $query->where($field, 'LIKE', 'Z%')->where($field, 'NOT LIKE', 'ZS%'); 994 break; 995 996 default: 997 $query->where($field, 'LIKE', '\\' . $letter . '%'); 998 break; 999 } 1000 } 1001 1002 /** 1003 * In Norwegian and Danish, AA gets listed under Å, NOT A 1004 * 1005 * @param Builder $query 1006 * @param Expression $field 1007 * @param string $letter 1008 */ 1009 protected function whereInitialNorwegian(Builder $query, Expression $field, string $letter): void 1010 { 1011 switch ($letter) { 1012 case 'A': 1013 $query->where($field, 'LIKE', 'A%')->where($field, 'NOT LIKE', 'AA%'); 1014 break; 1015 1016 case 'Å': 1017 $query->where(static function (Builder $query) use ($field): void { 1018 $query 1019 ->where($field, 'LIKE', 'Å%') 1020 ->orWhere($field, 'LIKE', 'AA%'); 1021 }); 1022 break; 1023 1024 default: 1025 $query->where($field, 'LIKE', '\\' . $letter . '%'); 1026 break; 1027 } 1028 } 1029 1030 /** 1031 * In Swedish and Finnish, AA gets listed under A, NOT Å (even though Swedish collation says they should). 1032 * 1033 * @param Builder $query 1034 * @param Expression $field 1035 * @param string $letter 1036 */ 1037 protected function whereInitialSwedish(Builder $query, Expression $field, string $letter): void 1038 { 1039 if ($letter === 'Å') { 1040 $query->where($field, 'LIKE', 'Å%')->where($field, 'NOT LIKE', 'AA%'); 1041 } else { 1042 $query->where($field, 'LIKE', '\\' . $letter . '%'); 1043 } 1044 } 1045} 1046