1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2019 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees\Services; 19 20use Closure; 21use Fisharebest\Webtrees\Family; 22use Fisharebest\Webtrees\GedcomRecord; 23use Fisharebest\Webtrees\Individual; 24use Fisharebest\Webtrees\Media; 25use Fisharebest\Webtrees\Note; 26use Fisharebest\Webtrees\Place; 27use Fisharebest\Webtrees\Repository; 28use Fisharebest\Webtrees\Source; 29use Fisharebest\Webtrees\Tree; 30use Illuminate\Database\Capsule\Manager as DB; 31use Illuminate\Database\Query\Builder; 32use Illuminate\Database\Query\JoinClause; 33use Illuminate\Support\Collection; 34use function method_exists; 35use stdClass; 36 37/** 38 * Search trees for genealogy records. 39 */ 40class SearchService 41{ 42 /** 43 * Search for families by name. 44 * 45 * @param Tree $tree 46 * @param string $search 47 * @param int $offset 48 * @param int $limit 49 * 50 * @return Collection|Family[] 51 */ 52 public function searchFamiliesByName(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 53 { 54 $prefix = DB::connection()->getTablePrefix(); 55 56 $query = DB::table('families') 57 ->where('f_file', '=', $tree->id()) 58 ->join('name AS husb_name', function (JoinClause $join) use ($search): void { 59 $join 60 ->on('husb_name.n_file', '=', 'families.f_file') 61 ->on('husb_name.n_id', '=', 'families.f_husb') 62 ->where('husb_name.n_type', '<>', '_MARNM'); 63 }) 64 ->join('name AS wife_name', function (JoinClause $join) use ($search): void { 65 $join 66 ->on('wife_name.n_file', '=', 'families.f_file') 67 ->on('wife_name.n_id', '=', 'families.f_wife') 68 ->where('wife_name.n_type', '<>', '_MARNM'); 69 }) 70 ->whereContains(DB::raw("CONCAT(" . $prefix . "husb_name.n_full, ' ', " . $prefix . "wife_name.n_full)"), $search) 71 ->orderBy('husb_name.n_sort') 72 ->orderBy('wife_name.n_sort') 73 ->select(['families.f_id', 'families.f_gedcom', 'husb_name.n_sort', 'wife_name.n_sort']) 74 ->distinct(); 75 76 $row_mapper = Family::rowMapper($tree); 77 78 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 79 } 80 81 /** 82 * Search for individuals by name. 83 * 84 * @param Tree $tree 85 * @param string $search 86 * @param int $offset 87 * @param int $limit 88 * 89 * @return Collection|Individual[] 90 */ 91 public function searchIndividualsByName(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 92 { 93 $query = DB::table('individuals') 94 ->where('i_file', '=', $tree->id()) 95 ->join('name', function (JoinClause $join) use ($search): void { 96 $join 97 ->on('name.n_file', '=', 'individuals.i_file') 98 ->on('name.n_id', '=', 'individuals.i_id') 99 ->whereContains('n_full', $search); 100 }) 101 ->orderBy('n_sort') 102 ->select(['individuals.i_id', 'individuals.i_gedcom', 'n_sort', 'n_num']) 103 ->distinct(); 104 105 $row_mapper = Individual::rowMapper($tree); 106 107 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 108 } 109 110 /** 111 * Search for media objects. 112 * 113 * @param Tree $tree 114 * @param string $search 115 * @param int $offset 116 * @param int $limit 117 * 118 * @return Collection|Media[] 119 */ 120 public function searchMedia(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 121 { 122 $query = DB::table('media') 123 ->where('media.m_file', '=', $tree->id()) 124 ->join('media_file', function (JoinClause $join) use ($search): void { 125 $join 126 ->on('media_file.m_file', '=', 'media.m_file') 127 ->on('media_file.m_id', '=', 'media.m_id'); 128 }) 129 ->where(function (Builder $query) use ($search): void { 130 $query 131 ->whereContains('multimedia_file_refn', $search) 132 ->whereContains('descriptive_title', $search, 'or'); 133 }) 134 ->select(['media.m_id', 'media.m_gedcom']) 135 ->distinct(); 136 137 $row_mapper = Media::rowMapper($tree); 138 139 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 140 } 141 142 /** 143 * Search for notes. 144 * 145 * @param Tree $tree 146 * @param string $search 147 * @param int $offset 148 * @param int $limit 149 * 150 * @return Collection|Note[] 151 */ 152 public function searchNotes(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 153 { 154 $query = DB::table('other') 155 ->where('o_file', '=', $tree->id()) 156 ->where('o_type', '=', 'NOTE') 157 ->whereContains('o_gedcom', $search) 158 ->orderBy('o_id') 159 ->select(['o_id', 'o_gedcom']); 160 161 $row_mapper = Note::rowMapper($tree); 162 163 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 164 } 165 166 /** 167 * Search for repositories. 168 * 169 * @param Tree $tree 170 * @param string $search 171 * @param int $offset 172 * @param int $limit 173 * 174 * @return Collection|Repository[] 175 */ 176 public function searchRepositories(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 177 { 178 $query = DB::table('other') 179 ->where('o_file', '=', $tree->id()) 180 ->where('o_type', '=', 'REPO') 181 ->whereContains('o_gedcom', $search) 182 ->orderBy('o_id') 183 ->select(['o_id', 'o_gedcom']); 184 185 $row_mapper = Repository::rowMapper($tree); 186 187 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 188 } 189 190 /** 191 * Search for sources by name. 192 * 193 * @param Tree $tree 194 * @param string $search 195 * @param int $offset 196 * @param int $limit 197 * 198 * @return Collection|Source[] 199 */ 200 public function searchSourcesByName(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 201 { 202 $query = DB::table('sources') 203 ->where('s_file', '=', $tree->id()) 204 ->whereContains('s_name', $search) 205 ->orderBy('s_name') 206 ->select(['s_id', 's_gedcom']); 207 208 $row_mapper = Source::rowMapper($tree); 209 210 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 211 } 212 213 /** 214 * Search for submitters. 215 * 216 * @param Tree $tree 217 * @param string $search 218 * @param int $offset 219 * @param int $limit 220 * 221 * @return Collection|GedcomRecord[] 222 */ 223 public function searchSubmitters(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 224 { 225 $query = DB::table('other') 226 ->where('o_file', '=', $tree->id()) 227 ->where('o_type', '=', 'SUBM') 228 ->whereContains('o_gedcom', $search) 229 ->orderBy('o_id') 230 ->select(['o_id', 'o_gedcom']); 231 232 $row_mapper = GedcomRecord::rowMapper($tree); 233 234 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 235 } 236 237 /** 238 * Search for places. 239 * 240 * @param Tree $tree 241 * @param string $search 242 * @param int $offset 243 * @param int $limit 244 * 245 * @return Collection|Place[] 246 */ 247 public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 248 { 249 $query = DB::table('places AS p0') 250 ->where('p0.p_file', '=', $tree->id()) 251 ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 252 ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 253 ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 254 ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 255 ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 256 ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 257 ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 258 ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 259 ->orderBy('p0.p_place') 260 ->orderBy('p1.p_place') 261 ->orderBy('p2.p_place') 262 ->orderBy('p3.p_place') 263 ->orderBy('p4.p_place') 264 ->orderBy('p5.p_place') 265 ->orderBy('p6.p_place') 266 ->orderBy('p7.p_place') 267 ->orderBy('p8.p_place') 268 ->select([ 269 'p0.p_place AS place0', 270 'p1.p_place AS place1', 271 'p2.p_place AS place2', 272 'p3.p_place AS place3', 273 'p4.p_place AS place4', 274 'p5.p_place AS place5', 275 'p6.p_place AS place6', 276 'p7.p_place AS place7', 277 'p8.p_place AS place8', 278 ]); 279 280 // Filter each level of the hierarchy. 281 foreach (explode(',', $search, 9) as $level => $string) { 282 $query->whereContains('p' . $level . '.p_place', $string); 283 } 284 285 $row_mapper = function (stdClass $row) use ($tree): Place { 286 $place = implode(', ', array_filter((array) $row)); 287 288 return new Place($place, $tree); 289 }; 290 291 return $this->paginateQuery($query, $row_mapper, $offset, $limit); 292 } 293 294 295 /** 296 * Paginate a search query. 297 * 298 * @param Builder $query Searches the database for the desired records. 299 * @param Closure $row_mapper Converts a row from the query into a record. 300 * @param int $offset Skip this many rows. 301 * @param int $limit Take this many rows. 302 * 303 * @return Collection 304 */ 305 private function paginateQuery(Builder $query, Closure $row_mapper, int $offset, int $limit): Collection 306 { 307 $collection = new Collection(); 308 309 foreach ($query->cursor() as $row) { 310 $record = $row_mapper($row); 311 312 // If the object has a method "canShow()", then use it to filter for privacy. 313 if (!method_exists($record, 'canShow') || $record->canShow()) { 314 if ($offset > 0) { 315 $offset--; 316 } else { 317 if ($limit > 0) { 318 $collection->push($record); 319 } 320 321 $limit--; 322 323 if ($limit === 0) { 324 break; 325 } 326 } 327 } 328 } 329 330 return $collection; 331 } 332} 333