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