1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2020 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 <http://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Services; 21 22use Fisharebest\Webtrees\Registry; 23use Fisharebest\Webtrees\Family; 24use Fisharebest\Webtrees\Gedcom; 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\FilesystemInterface; 38 39use function array_map; 40use function explode; 41use function fclose; 42use function fread; 43use function preg_match; 44 45/** 46 * Utilities for the control panel. 47 */ 48class AdminService 49{ 50 // Show a reduced page when there are more than a certain number of trees 51 private const MULTIPLE_TREE_THRESHOLD = '500'; 52 53 /** 54 * Count of XREFs used by two trees at the same time. 55 * 56 * @param Tree $tree1 57 * @param Tree $tree2 58 * 59 * @return int 60 */ 61 public function countCommonXrefs(Tree $tree1, Tree $tree2): int 62 { 63 $subquery1 = DB::table('individuals') 64 ->where('i_file', '=', $tree1->id()) 65 ->select(['i_id AS xref']) 66 ->union(DB::table('families') 67 ->where('f_file', '=', $tree1->id()) 68 ->select(['f_id AS xref'])) 69 ->union(DB::table('sources') 70 ->where('s_file', '=', $tree1->id()) 71 ->select(['s_id AS xref'])) 72 ->union(DB::table('media') 73 ->where('m_file', '=', $tree1->id()) 74 ->select(['m_id AS xref'])) 75 ->union(DB::table('other') 76 ->where('o_file', '=', $tree1->id()) 77 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 78 ->select(['o_id AS xref'])); 79 80 $subquery2 = DB::table('change') 81 ->where('gedcom_id', '=', $tree2->id()) 82 ->select(['xref AS other_xref']) 83 ->union(DB::table('individuals') 84 ->where('i_file', '=', $tree2->id()) 85 ->select(['i_id AS xref'])) 86 ->union(DB::table('families') 87 ->where('f_file', '=', $tree2->id()) 88 ->select(['f_id AS xref'])) 89 ->union(DB::table('sources') 90 ->where('s_file', '=', $tree2->id()) 91 ->select(['s_id AS xref'])) 92 ->union(DB::table('media') 93 ->where('m_file', '=', $tree2->id()) 94 ->select(['m_id AS xref'])) 95 ->union(DB::table('other') 96 ->where('o_file', '=', $tree2->id()) 97 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 98 ->select(['o_id AS xref'])); 99 100 return DB::table(new Expression('(' . $subquery1->toSql() . ') AS sub1')) 101 ->mergeBindings($subquery1) 102 ->joinSub($subquery2, 'sub2', 'other_xref', '=', 'xref') 103 ->count(); 104 } 105 106 /** 107 * @param Tree $tree 108 * 109 * @return array<string,array<GedcomRecord>> 110 */ 111 public function duplicateRecords(Tree $tree): array 112 { 113 // We can't do any reasonable checks using MySQL. 114 // Will need to wait for a "repositories" table. 115 $repositories = []; 116 117 $sources = DB::table('sources') 118 ->where('s_file', '=', $tree->id()) 119 ->groupBy(['s_name']) 120 ->having(new Expression('COUNT(s_id)'), '>', '1') 121 ->select([new Expression('GROUP_CONCAT(s_id) AS 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 ->pluck('xrefs') 143 ->map(static function (string $xrefs) use ($tree): array { 144 return array_map(static function (string $xref) use ($tree): Individual { 145 return Registry::individualFactory()->make($xref, $tree); 146 }, explode(',', $xrefs)); 147 }) 148 ->all(); 149 150 $families = DB::table('families') 151 ->where('f_file', '=', $tree->id()) 152 ->groupBy([new Expression('LEAST(f_husb, f_wife)')]) 153 ->groupBy([new Expression('GREATEST(f_husb, f_wife)')]) 154 ->having(new Expression('COUNT(f_id)'), '>', '1') 155 ->select([new Expression('GROUP_CONCAT(f_id) AS xrefs')]) 156 ->pluck('xrefs') 157 ->map(static function (string $xrefs) use ($tree): array { 158 return array_map(static function (string $xref) use ($tree): Family { 159 return Registry::familyFactory()->make($xref, $tree); 160 }, explode(',', $xrefs)); 161 }) 162 ->all(); 163 164 $media = DB::table('media_file') 165 ->where('m_file', '=', $tree->id()) 166 ->where('descriptive_title', '<>', '') 167 ->groupBy(['descriptive_title']) 168 ->having(new Expression('COUNT(m_id)'), '>', '1') 169 ->select([new Expression('GROUP_CONCAT(m_id) AS xrefs')]) 170 ->pluck('xrefs') 171 ->map(static function (string $xrefs) use ($tree): array { 172 return array_map(static function (string $xref) use ($tree): Media { 173 return Registry::mediaFactory()->make($xref, $tree); 174 }, explode(',', $xrefs)); 175 }) 176 ->all(); 177 178 return [ 179 I18N::translate('Repositories') => $repositories, 180 I18N::translate('Sources') => $sources, 181 I18N::translate('Individuals') => $individuals, 182 I18N::translate('Families') => $families, 183 I18N::translate('Media objects') => $media, 184 ]; 185 } 186 187 /** 188 * Every XREF used by this tree and also used by some other tree 189 * 190 * @param Tree $tree 191 * 192 * @return string[] 193 */ 194 public function duplicateXrefs(Tree $tree): array 195 { 196 $subquery1 = DB::table('individuals') 197 ->where('i_file', '=', $tree->id()) 198 ->select(['i_id AS xref', new Expression("'INDI' AS type")]) 199 ->union(DB::table('families') 200 ->where('f_file', '=', $tree->id()) 201 ->select(['f_id AS xref', new Expression("'FAM' AS type")])) 202 ->union(DB::table('sources') 203 ->where('s_file', '=', $tree->id()) 204 ->select(['s_id AS xref', new Expression("'SOUR' AS type")])) 205 ->union(DB::table('media') 206 ->where('m_file', '=', $tree->id()) 207 ->select(['m_id AS xref', new Expression("'OBJE' AS type")])) 208 ->union(DB::table('other') 209 ->where('o_file', '=', $tree->id()) 210 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 211 ->select(['o_id AS xref', 'o_type AS type'])); 212 213 $subquery2 = DB::table('change') 214 ->where('gedcom_id', '<>', $tree->id()) 215 ->select(['xref AS other_xref']) 216 ->union(DB::table('individuals') 217 ->where('i_file', '<>', $tree->id()) 218 ->select(['i_id AS xref'])) 219 ->union(DB::table('families') 220 ->where('f_file', '<>', $tree->id()) 221 ->select(['f_id AS xref'])) 222 ->union(DB::table('sources') 223 ->where('s_file', '<>', $tree->id()) 224 ->select(['s_id AS xref'])) 225 ->union(DB::table('media') 226 ->where('m_file', '<>', $tree->id()) 227 ->select(['m_id AS xref'])) 228 ->union(DB::table('other') 229 ->where('o_file', '<>', $tree->id()) 230 ->whereNotIn('o_type', [Header::RECORD_TYPE, 'TRLR']) 231 ->select(['o_id AS xref'])); 232 233 return DB::table(new Expression('(' . $subquery1->toSql() . ') AS sub1')) 234 ->mergeBindings($subquery1) 235 ->joinSub($subquery2, 'sub2', 'other_xref', '=', 'xref') 236 ->pluck('type', 'xref') 237 ->all(); 238 } 239 240 /** 241 * A list of GEDCOM files in the data folder. 242 * 243 * @param FilesystemInterface $filesystem 244 * 245 * @return Collection<string> 246 */ 247 public function gedcomFiles(FilesystemInterface $filesystem): Collection 248 { 249 return Collection::make($filesystem->listContents()) 250 ->filter(static function (array $path) use ($filesystem): bool { 251 if ($path['type'] !== 'file') { 252 return false; 253 } 254 255 $stream = $filesystem->readStream($path['path']); 256 257 $header = fread($stream, 10); 258 fclose($stream); 259 260 return preg_match('/^(' . Gedcom::UTF8_BOM . ')?0 HEAD/', $header) > 0; 261 }) 262 ->map(static function (array $path): string { 263 return $path['path']; 264 }) 265 ->sort(); 266 } 267 268 /** 269 * Change the behaviour a little, when there are a lot of trees. 270 * 271 * @return int 272 */ 273 public function multipleTreeThreshold(): int 274 { 275 return (int) Site::getPreference('MULTIPLE_TREE_THRESHOLD', self::MULTIPLE_TREE_THRESHOLD); 276 } 277} 278