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