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