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