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\Registry; 23use Fisharebest\Webtrees\Validator; 24use Illuminate\Database\Capsule\Manager as DB; 25use Illuminate\Database\Query\Builder; 26use Illuminate\Database\Query\Expression; 27use Psr\Http\Message\ServerRequestInterface; 28 29use function addcslashes; 30 31/** 32 * Manage site logs 33 */ 34class SiteLogsService 35{ 36 /** 37 * Generate a query for filtering the changes log. 38 * 39 * @param ServerRequestInterface $request 40 * 41 * @return Builder 42 */ 43 public function logsQuery(ServerRequestInterface $request): Builder 44 { 45 $tree = Validator::queryParams($request)->string('tree'); 46 $from = Validator::queryParams($request)->string('from'); 47 $to = Validator::queryParams($request)->string('to'); 48 $type = Validator::queryParams($request)->string('type'); 49 $text = Validator::queryParams($request)->string('text'); 50 $ip = Validator::queryParams($request)->string('ip'); 51 $username = Validator::queryParams($request)->string('username'); 52 53 $query = DB::table('log') 54 ->leftJoin('user', 'user.user_id', '=', 'log.user_id') 55 ->leftJoin('gedcom', 'gedcom.gedcom_id', '=', 'log.gedcom_id') 56 ->select(['log.*', new Expression("COALESCE(user_name, '<none>') AS user_name"), new Expression("COALESCE(gedcom_name, '<none>') AS gedcom_name")]); 57 58 if ($from !== '') { 59 $query->where('log_time', '>=', Registry::timestampFactory()->fromString($from, 'Y-m-d')->toDateString()); 60 } 61 62 if ($to !== '') { 63 // before end of the day 64 $query->where('log_time', '<', Registry::timestampFactory()->fromString($to, 'Y-m-d')->addDays(1)->toDateString()); 65 } 66 67 if ($type !== '') { 68 $query->where('log_type', '=', $type); 69 } 70 71 if ($text !== '') { 72 $query->where('log_message', 'LIKE', '%' . addcslashes($text, '\\%_') . '%'); 73 } 74 75 if ($ip !== '') { 76 $query->where('ip_address', 'LIKE', addcslashes($ip, '\\%_') . '%'); 77 } 78 79 if ($username !== '') { 80 $query->where('user_name', '=', $username); 81 } 82 83 if ($tree !== '') { 84 $query->where('gedcom_name', '=', $tree); 85 } 86 87 return $query; 88 } 89} 90