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\Services; 21 22use Fisharebest\Webtrees\Family; 23use Fisharebest\Webtrees\GedcomRecord; 24use Fisharebest\Webtrees\Individual; 25use Fisharebest\Webtrees\Location; 26use Fisharebest\Webtrees\Media; 27use Fisharebest\Webtrees\Note; 28use Fisharebest\Webtrees\Registry; 29use Fisharebest\Webtrees\Repository; 30use Fisharebest\Webtrees\Source; 31use Fisharebest\Webtrees\Submitter; 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; 37 38use function addcslashes; 39use function assert; 40 41/** 42 * Find records linked to other records 43 */ 44class LinkedRecordService 45{ 46 /** 47 * Find all records linked to a record. 48 * 49 * @param GedcomRecord $record 50 * 51 * @return Collection<int,Family> 52 */ 53 public function allLinkedRecords(GedcomRecord $record): Collection 54 { 55 $like = addcslashes($record->xref(), '\\%_'); 56 57 $union = DB::table('change') 58 ->where('gedcom_id', '=', $record->tree()->id()) 59 ->where('new_gedcom', 'LIKE', '%@' . $like . '@%') 60 ->where('new_gedcom', 'NOT LIKE', '0 @' . $like . '@%') 61 ->whereIn('change_id', function (Builder $query) use ($record): void { 62 $query 63 ->select(new Expression('MAX(change_id)')) 64 ->from('change') 65 ->where('gedcom_id', '=', $record->tree()->id()) 66 ->where('status', '=', 'pending') 67 ->groupBy(['xref']); 68 }) 69 ->select(['xref']); 70 71 $xrefs = DB::table('link') 72 ->where('l_file', '=', $record->tree()->id()) 73 ->where('l_to', '=', $record->xref()) 74 ->select(['l_from']) 75 ->union($union) 76 ->pluck('l_from'); 77 78 return $xrefs->map(static fn (string $xref) => Registry::gedcomRecordFactory()->make($xref, $record->tree())); 79 } 80 81 /** 82 * Find families linked to a record. 83 * 84 * @param GedcomRecord $record 85 * @param string|null $link_type 86 * 87 * @return Collection<int,Family> 88 */ 89 public function linkedFamilies(GedcomRecord $record, string $link_type = null): Collection 90 { 91 $query = DB::table('families') 92 ->join('link', static function (JoinClause $join): void { 93 $join 94 ->on('l_file', '=', 'f_file') 95 ->on('l_from', '=', 'f_id'); 96 }) 97 ->where('f_file', '=', $record->tree()->id()) 98 ->where('l_to', '=', $record->xref()); 99 100 if ($link_type !== null) { 101 $query->where('l_type', '=', $link_type); 102 } 103 104 return $query 105 ->select(['families.*']) 106 ->get() 107 ->map(Registry::familyFactory()->mapper($record->tree())) 108 ->filter(GedcomRecord::accessFilter()); 109 } 110 111 /** 112 * Find individuals linked to a record. 113 * 114 * @param GedcomRecord $record 115 * @param string|null $link_type 116 * 117 * @return Collection<int,Individual> 118 */ 119 public function linkedIndividuals(GedcomRecord $record, string $link_type = null): Collection 120 { 121 $query = DB::table('individuals') 122 ->join('link', static function (JoinClause $join): void { 123 $join 124 ->on('l_file', '=', 'i_file') 125 ->on('l_from', '=', 'i_id'); 126 }) 127 ->where('i_file', '=', $record->tree()->id()) 128 ->where('l_to', '=', $record->xref()); 129 130 if ($link_type !== null) { 131 $query->where('l_type', '=', $link_type); 132 } 133 134 return $query 135 ->select(['individuals.*']) 136 ->get() 137 ->map(Registry::individualFactory()->mapper($record->tree())) 138 ->filter(GedcomRecord::accessFilter()); 139 } 140 141 /** 142 * Find locations linked to a record. 143 * 144 * @param GedcomRecord $record 145 * 146 * @return Collection<int,Location> 147 */ 148 public function linkedLocations(GedcomRecord $record): Collection 149 { 150 return DB::table('other') 151 ->join('link', static function (JoinClause $join): void { 152 $join 153 ->on('l_file', '=', 'o_file') 154 ->on('l_from', '=', 'o_id'); 155 }) 156 ->where('o_file', '=', $record->tree()->id()) 157 ->where('o_type', '=', Location::RECORD_TYPE) 158 ->where('l_to', '=', $record->xref()) 159 ->select(['other.*']) 160 ->get() 161 ->map(Registry::locationFactory()->mapper($record->tree())) 162 ->filter(GedcomRecord::accessFilter()); 163 } 164 165 /** 166 * Find media objects linked to a record. 167 * 168 * @param GedcomRecord $record 169 * 170 * @return Collection<int,Media> 171 */ 172 public function linkedMedia(GedcomRecord $record): Collection 173 { 174 return DB::table('media') 175 ->join('link', static function (JoinClause $join): void { 176 $join 177 ->on('l_file', '=', 'm_file') 178 ->on('l_from', '=', 'm_id'); 179 }) 180 ->where('m_file', '=', $record->tree()->id()) 181 ->where('l_to', '=', $record->xref()) 182 ->select(['media.*']) 183 ->get() 184 ->map(Registry::mediaFactory()->mapper($record->tree())) 185 ->filter(GedcomRecord::accessFilter()); 186 } 187 188 /** 189 * Find notes linked to a record. 190 * 191 * @param GedcomRecord $record 192 * 193 * @return Collection<int,Note> 194 */ 195 public function linkedNotes(GedcomRecord $record): Collection 196 { 197 return DB::table('other') 198 ->join('link', static function (JoinClause $join): void { 199 $join 200 ->on('l_file', '=', 'o_file') 201 ->on('l_from', '=', 'o_id'); 202 }) 203 ->where('o_file', '=', $record->tree()->id()) 204 ->where('o_type', '=', Note::RECORD_TYPE) 205 ->where('l_to', '=', $record->xref()) 206 ->select(['other.*']) 207 ->get() 208 ->map(Registry::noteFactory()->mapper($record->tree())) 209 ->filter(GedcomRecord::accessFilter()); 210 } 211 212 /** 213 * Find repositories linked to a record. 214 * 215 * @param GedcomRecord $record 216 * 217 * @return Collection<int,Repository> 218 */ 219 public function linkedRepositories(GedcomRecord $record): Collection 220 { 221 return DB::table('other') 222 ->join('link', static function (JoinClause $join): void { 223 $join 224 ->on('l_file', '=', 'o_file') 225 ->on('l_from', '=', 'o_id'); 226 }) 227 ->where('o_file', '=', $record->tree()->id()) 228 ->where('o_type', '=', Repository::RECORD_TYPE) 229 ->where('l_to', '=', $record->xref()) 230 ->select(['other.*']) 231 ->get() 232 ->map(Registry::repositoryFactory()->mapper($record->tree())) 233 ->filter(GedcomRecord::accessFilter()); 234 } 235 236 /** 237 * Find sources linked to a record. 238 * 239 * @param GedcomRecord $record 240 * 241 * @return Collection<int,Source> 242 */ 243 public function linkedSources(GedcomRecord $record): Collection 244 { 245 return DB::table('sources') 246 ->join('link', static function (JoinClause $join): void { 247 $join 248 ->on('l_file', '=', 's_file') 249 ->on('l_from', '=', 's_id'); 250 }) 251 ->where('s_file', '=', $record->tree()->id()) 252 ->where('l_to', '=', $record->xref()) 253 ->select(['sources.*']) 254 ->get() 255 ->map(Registry::sourceFactory()->mapper($record->tree())) 256 ->filter(GedcomRecord::accessFilter()); 257 } 258 259 /** 260 * Find submitters linked to a record. 261 * 262 * @param GedcomRecord $record 263 * 264 * @return Collection<int,Repository> 265 */ 266 public function linkedSubmitters(GedcomRecord $record): Collection 267 { 268 return DB::table('other') 269 ->join('link', static function (JoinClause $join): void { 270 $join 271 ->on('l_file', '=', 'o_file') 272 ->on('l_from', '=', 'o_id'); 273 }) 274 ->where('o_file', '=', $record->tree()->id()) 275 ->where('o_type', '=', Submitter::RECORD_TYPE) 276 ->where('l_to', '=', $record->xref()) 277 ->select(['other.*']) 278 ->get() 279 ->map(Registry::repositoryFactory()->mapper($record->tree())) 280 ->filter(GedcomRecord::accessFilter()); 281 } 282} 283