1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2023 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\DB; 23use Fisharebest\Webtrees\Encodings\UTF8; 24use Fisharebest\Webtrees\Family; 25use Fisharebest\Webtrees\GedcomRecord; 26use Fisharebest\Webtrees\Header; 27use Fisharebest\Webtrees\I18N; 28use Fisharebest\Webtrees\Individual; 29use Fisharebest\Webtrees\Media; 30use Fisharebest\Webtrees\Registry; 31use Fisharebest\Webtrees\Site; 32use Fisharebest\Webtrees\Source; 33use Fisharebest\Webtrees\Tree; 34use Illuminate\Database\Query\Expression; 35use Illuminate\Database\Query\JoinClause; 36use Illuminate\Support\Collection; 37use League\Flysystem\FilesystemException; 38use League\Flysystem\FilesystemOperator; 39use League\Flysystem\StorageAttributes; 40 41use function array_map; 42use function array_unique; 43use function explode; 44use function fclose; 45use function fread; 46use function implode; 47use function preg_match; 48use function sort; 49 50/** 51 * Utilities for the control panel. 52 */ 53class AdminService 54{ 55 /** 56 * Count of XREFs used by two trees at the same time. 57 * 58 * @param Tree $tree1 59 * @param Tree $tree2 60 * 61 * @return int 62 */ 63 public function countCommonXrefs(Tree $tree1, Tree $tree2): int 64 { 65 $subquery1 = DB::table('individuals') 66 ->where('i_file', '=', $tree1->id()) 67 ->select(['i_id AS xref']) 68 ->union(DB::table('families') 69 ->where('f_file', '=', $tree1->id()) 70 ->select(['f_id AS xref'])) 71 ->union(DB::table('sources') 72 ->where('s_file', '=', $tree1->id()) 73 ->select(['s_id AS xref'])) 74 ->union(DB::table('media') 75 ->where('m_file', '=', $tree1->id()) 76 ->select(['m_id AS xref'])) 77 ->union(DB::table('other') 78 ->where('o_file', '=', $tree1->id()) 79 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 80 ->select(['o_id AS xref'])); 81 82 $subquery2 = DB::table('change') 83 ->where('gedcom_id', '=', $tree2->id()) 84 ->select(['xref AS other_xref']) 85 ->union(DB::table('individuals') 86 ->where('i_file', '=', $tree2->id()) 87 ->select(['i_id AS xref'])) 88 ->union(DB::table('families') 89 ->where('f_file', '=', $tree2->id()) 90 ->select(['f_id AS xref'])) 91 ->union(DB::table('sources') 92 ->where('s_file', '=', $tree2->id()) 93 ->select(['s_id AS xref'])) 94 ->union(DB::table('media') 95 ->where('m_file', '=', $tree2->id()) 96 ->select(['m_id AS xref'])) 97 ->union(DB::table('other') 98 ->where('o_file', '=', $tree2->id()) 99 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 100 ->select(['o_id AS xref'])); 101 102 return DB::table(new Expression('(' . $subquery1->toSql() . ') AS sub1')) 103 ->mergeBindings($subquery1) 104 ->joinSub($subquery2, 'sub2', 'other_xref', '=', 'xref') 105 ->count(); 106 } 107 108 /** 109 * @param Tree $tree 110 * 111 * @return array<string,array<int,array<int,GedcomRecord>>> 112 */ 113 public function duplicateRecords(Tree $tree): array 114 { 115 // We can't do any reasonable checks using MySQL. 116 // Will need to wait for a "repositories" table. 117 $repositories = []; 118 119 $sources = DB::table('sources') 120 ->where('s_file', '=', $tree->id()) 121 ->groupBy(['s_name']) 122 ->having(new Expression('COUNT(s_id)'), '>', '1') 123 ->select([new Expression(DB::groupConcat('s_id') . ' AS xrefs')]) 124 ->orderBy('xrefs') 125 ->pluck('xrefs') 126 ->map(static function (string $xrefs) use ($tree): array { 127 return array_map(static function (string $xref) use ($tree): Source { 128 return Registry::sourceFactory()->make($xref, $tree); 129 }, explode(',', $xrefs)); 130 }) 131 ->all(); 132 133 // Database agnostic way to do GROUP_CONCAT(DISTINCT x ORDER BY x) 134 $distinct_order_by = static function (string $xrefs): string { 135 $array = explode(',', $xrefs); 136 sort($array); 137 138 return implode(',', array_unique($array)); 139 }; 140 141 $individuals = DB::table('dates') 142 ->join('name', static function (JoinClause $join): void { 143 $join 144 ->on('d_file', '=', 'n_file') 145 ->on('d_gid', '=', 'n_id'); 146 }) 147 ->where('d_file', '=', $tree->id()) 148 ->whereIn('d_fact', ['BIRT', 'CHR', 'BAPM', 'DEAT', 'BURI']) 149 ->groupBy(['d_year', 'd_month', 'd_day', 'd_type', 'd_fact', 'n_type', 'n_full']) 150 ->having(new Expression('COUNT(DISTINCT d_gid)'), '>', '1') 151 ->select([new Expression(DB::groupConcat('d_gid') . ' AS xrefs')]) 152 ->orderBy('xrefs') 153 ->pluck('xrefs') 154 ->map($distinct_order_by) 155 ->unique() 156 ->map(static function (string $xrefs) use ($tree): array { 157 return array_map(static function (string $xref) use ($tree): Individual { 158 return Registry::individualFactory()->make($xref, $tree); 159 }, explode(',', $xrefs)); 160 }) 161 ->all(); 162 163 $families = DB::table('families') 164 ->where('f_file', '=', $tree->id()) 165 ->groupBy([new Expression('LEAST(f_husb, f_wife)')]) 166 ->groupBy([new Expression('GREATEST(f_husb, f_wife)')]) 167 ->having(new Expression('COUNT(f_id)'), '>', '1') 168 ->select([new Expression(DB::groupConcat('f_id') . ' AS xrefs')]) 169 ->orderBy('xrefs') 170 ->pluck('xrefs') 171 ->map(static function (string $xrefs) use ($tree): array { 172 return array_map(static function (string $xref) use ($tree): Family { 173 return Registry::familyFactory()->make($xref, $tree); 174 }, explode(',', $xrefs)); 175 }) 176 ->all(); 177 178 $media = DB::table('media_file') 179 ->where('m_file', '=', $tree->id()) 180 ->where('descriptive_title', '<>', '') 181 ->groupBy(['descriptive_title']) 182 ->having(new Expression('COUNT(DISTINCT m_id)'), '>', '1') 183 ->select([new Expression(DB::groupConcat('m_id') . ' AS xrefs')]) 184 ->orderBy('xrefs') 185 ->pluck('xrefs') 186 ->map(static function (string $xrefs) use ($tree): array { 187 return array_map(static function (string $xref) use ($tree): Media { 188 return Registry::mediaFactory()->make($xref, $tree); 189 }, explode(',', $xrefs)); 190 }) 191 ->all(); 192 193 return [ 194 I18N::translate('Repositories') => $repositories, 195 I18N::translate('Sources') => $sources, 196 I18N::translate('Individuals') => $individuals, 197 I18N::translate('Families') => $families, 198 I18N::translate('Media objects') => $media, 199 ]; 200 } 201 202 /** 203 * Every XREF used by this tree and also used by some other tree 204 * 205 * @param Tree $tree 206 * 207 * @return array<string> 208 */ 209 public function duplicateXrefs(Tree $tree): array 210 { 211 $subquery1 = DB::table('individuals') 212 ->where('i_file', '=', $tree->id()) 213 ->select(['i_id AS xref', new Expression("'INDI' AS type")]) 214 ->union(DB::table('families') 215 ->where('f_file', '=', $tree->id()) 216 ->select(['f_id AS xref', new Expression("'FAM' AS type")])) 217 ->union(DB::table('sources') 218 ->where('s_file', '=', $tree->id()) 219 ->select(['s_id AS xref', new Expression("'SOUR' AS type")])) 220 ->union(DB::table('media') 221 ->where('m_file', '=', $tree->id()) 222 ->select(['m_id AS xref', new Expression("'OBJE' AS type")])) 223 ->union(DB::table('other') 224 ->where('o_file', '=', $tree->id()) 225 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 226 ->select(['o_id AS xref', 'o_type AS type'])); 227 228 $subquery2 = DB::table('change') 229 ->where('gedcom_id', '<>', $tree->id()) 230 ->select(['xref AS other_xref']) 231 ->union(DB::table('individuals') 232 ->where('i_file', '<>', $tree->id()) 233 ->select(['i_id AS xref'])) 234 ->union(DB::table('families') 235 ->where('f_file', '<>', $tree->id()) 236 ->select(['f_id AS xref'])) 237 ->union(DB::table('sources') 238 ->where('s_file', '<>', $tree->id()) 239 ->select(['s_id AS xref'])) 240 ->union(DB::table('media') 241 ->where('m_file', '<>', $tree->id()) 242 ->select(['m_id AS xref'])) 243 ->union(DB::table('other') 244 ->where('o_file', '<>', $tree->id()) 245 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 246 ->select(['o_id AS xref'])); 247 248 return DB::query() 249 ->fromSub($subquery1, 'sub1') 250 ->joinSub($subquery2, 'sub2', 'other_xref', '=', 'xref') 251 ->pluck('type', 'xref') 252 ->all(); 253 } 254 255 /** 256 * A list of GEDCOM files in the data folder. 257 * 258 * @param FilesystemOperator $filesystem 259 * 260 * @return Collection<int,string> 261 */ 262 public function gedcomFiles(FilesystemOperator $filesystem): Collection 263 { 264 try { 265 $files = $filesystem->listContents('') 266 ->filter(static function (StorageAttributes $attributes) use ($filesystem) { 267 if (!$attributes->isFile()) { 268 return false; 269 } 270 271 $stream = $filesystem->readStream($attributes->path()); 272 273 $header = fread($stream, 10); 274 fclose($stream); 275 276 return preg_match('/^(' . UTF8::BYTE_ORDER_MARK . ')?0 HEAD/', $header) > 0; 277 }) 278 ->map(function (StorageAttributes $attributes) { 279 return $attributes->path(); 280 }) 281 ->toArray(); 282 } catch (FilesystemException) { 283 $files = []; 284 } 285 286 return Collection::make($files)->sort(); 287 } 288 289 /** 290 * Change the behaviour a little, when there are a lot of trees. 291 * 292 * @return int 293 */ 294 public function multipleTreeThreshold(): int 295 { 296 return (int) Site::getPreference('MULTIPLE_TREE_THRESHOLD'); 297 } 298} 299