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