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