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