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