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