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 ->groupBy('f_id', 'f_file') 107 ->get() 108 ->map(Registry::familyFactory()->mapper($record->tree())) 109 ->filter(GedcomRecord::accessFilter()); 110 } 111 112 /** 113 * Find individuals linked to a record. 114 * 115 * @param GedcomRecord $record 116 * @param string|null $link_type 117 * 118 * @return Collection<int,Individual> 119 */ 120 public function linkedIndividuals(GedcomRecord $record, string $link_type = null): Collection 121 { 122 $query = DB::table('individuals') 123 ->join('link', static function (JoinClause $join): void { 124 $join 125 ->on('l_file', '=', 'i_file') 126 ->on('l_from', '=', 'i_id'); 127 }) 128 ->where('i_file', '=', $record->tree()->id()) 129 ->where('l_to', '=', $record->xref()); 130 131 if ($link_type !== null) { 132 $query->where('l_type', '=', $link_type); 133 } 134 135 return $query 136 ->select(['individuals.*']) 137 ->groupBy('i_id', 'i_file') 138 ->get() 139 ->map(Registry::individualFactory()->mapper($record->tree())) 140 ->filter(GedcomRecord::accessFilter()); 141 } 142 143 /** 144 * Find locations linked to a record. 145 * 146 * @param GedcomRecord $record 147 * 148 * @return Collection<int,Location> 149 */ 150 public function linkedLocations(GedcomRecord $record): Collection 151 { 152 return DB::table('other') 153 ->join('link', static function (JoinClause $join): void { 154 $join 155 ->on('l_file', '=', 'o_file') 156 ->on('l_from', '=', 'o_id'); 157 }) 158 ->where('o_file', '=', $record->tree()->id()) 159 ->where('o_type', '=', Location::RECORD_TYPE) 160 ->where('l_to', '=', $record->xref()) 161 ->select(['other.*']) 162 ->groupBy('o_id', 'o_file') 163 ->get() 164 ->map(Registry::locationFactory()->mapper($record->tree())) 165 ->filter(GedcomRecord::accessFilter()); 166 } 167 168 /** 169 * Find media objects linked to a record. 170 * 171 * @param GedcomRecord $record 172 * 173 * @return Collection<int,Media> 174 */ 175 public function linkedMedia(GedcomRecord $record): Collection 176 { 177 return DB::table('media') 178 ->join('link', static function (JoinClause $join): void { 179 $join 180 ->on('l_file', '=', 'm_file') 181 ->on('l_from', '=', 'm_id'); 182 }) 183 ->where('m_file', '=', $record->tree()->id()) 184 ->where('l_to', '=', $record->xref()) 185 ->select(['media.*']) 186 ->groupBy('m_id', 'm_file') 187 ->get() 188 ->map(Registry::mediaFactory()->mapper($record->tree())) 189 ->filter(GedcomRecord::accessFilter()); 190 } 191 192 /** 193 * Find notes linked to a record. 194 * 195 * @param GedcomRecord $record 196 * 197 * @return Collection<int,Note> 198 */ 199 public function linkedNotes(GedcomRecord $record): Collection 200 { 201 return DB::table('other') 202 ->join('link', static function (JoinClause $join): void { 203 $join 204 ->on('l_file', '=', 'o_file') 205 ->on('l_from', '=', 'o_id'); 206 }) 207 ->where('o_file', '=', $record->tree()->id()) 208 ->where('o_type', '=', Note::RECORD_TYPE) 209 ->where('l_to', '=', $record->xref()) 210 ->select(['other.*']) 211 ->groupBy('o_id', 'o_file') 212 ->get() 213 ->map(Registry::noteFactory()->mapper($record->tree())) 214 ->filter(GedcomRecord::accessFilter()); 215 } 216 217 /** 218 * Find repositories linked to a record. 219 * 220 * @param GedcomRecord $record 221 * 222 * @return Collection<int,Repository> 223 */ 224 public function linkedRepositories(GedcomRecord $record): Collection 225 { 226 return DB::table('other') 227 ->join('link', static function (JoinClause $join): void { 228 $join 229 ->on('l_file', '=', 'o_file') 230 ->on('l_from', '=', 'o_id'); 231 }) 232 ->where('o_file', '=', $record->tree()->id()) 233 ->where('o_type', '=', Repository::RECORD_TYPE) 234 ->where('l_to', '=', $record->xref()) 235 ->select(['other.*']) 236 ->groupBy('o_id', 'o_file') 237 ->get() 238 ->map(Registry::repositoryFactory()->mapper($record->tree())) 239 ->filter(GedcomRecord::accessFilter()); 240 } 241 242 /** 243 * Find sources linked to a record. 244 * 245 * @param GedcomRecord $record 246 * 247 * @return Collection<int,Source> 248 */ 249 public function linkedSources(GedcomRecord $record): Collection 250 { 251 return DB::table('sources') 252 ->join('link', static function (JoinClause $join): void { 253 $join 254 ->on('l_file', '=', 's_file') 255 ->on('l_from', '=', 's_id'); 256 }) 257 ->where('s_file', '=', $record->tree()->id()) 258 ->where('l_to', '=', $record->xref()) 259 ->select(['sources.*']) 260 ->groupBy('s_id', 's_file') 261 ->get() 262 ->map(Registry::sourceFactory()->mapper($record->tree())) 263 ->filter(GedcomRecord::accessFilter()); 264 } 265 266 /** 267 * Find submitters linked to a record. 268 * 269 * @param GedcomRecord $record 270 * 271 * @return Collection<int,Repository> 272 */ 273 public function linkedSubmitters(GedcomRecord $record): Collection 274 { 275 return DB::table('other') 276 ->join('link', static function (JoinClause $join): void { 277 $join 278 ->on('l_file', '=', 'o_file') 279 ->on('l_from', '=', 'o_id'); 280 }) 281 ->where('o_file', '=', $record->tree()->id()) 282 ->where('o_type', '=', Submitter::RECORD_TYPE) 283 ->where('l_to', '=', $record->xref()) 284 ->select(['other.*']) 285 ->groupBy('o_id', 'o_file') 286 ->get() 287 ->map(Registry::repositoryFactory()->mapper($record->tree())) 288 ->filter(GedcomRecord::accessFilter()); 289 } 290} 291