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