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