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