xref: /webtrees/app/Services/SiteLogsService.php (revision 5cac87ae725131e0533df3938e6bed4a6eda5c1b) 
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 DateInterval;
23use DateTimeImmutable;
24use DateTimeZone;
25use Fisharebest\Webtrees\Auth;
26use Fisharebest\Webtrees\Contracts\UserInterface;
27use Fisharebest\Webtrees\DB;
28use Fisharebest\Webtrees\Registry;
29use Fisharebest\Webtrees\Validator;
30use Illuminate\Database\Query\Builder;
31use Illuminate\Database\Query\Expression;
32use Psr\Http\Message\ServerRequestInterface;
33
34use function addcslashes;
35
36/**
37 * Manage site logs
38 */
39class SiteLogsService
40{
41    /**
42     * Generate a query for filtering the changes log.
43     *
44     * @param ServerRequestInterface $request
45     *
46     * @return Builder
47     */
48    public function logsQuery(ServerRequestInterface $request): Builder
49    {
50        $tree     = Validator::queryParams($request)->string('tree');
51        $from     = Validator::queryParams($request)->string('from');
52        $to       = Validator::queryParams($request)->string('to');
53        $type     = Validator::queryParams($request)->string('type');
54        $text     = Validator::queryParams($request)->string('text');
55        $ip       = Validator::queryParams($request)->string('ip');
56        $username = Validator::queryParams($request)->string('username');
57
58        $query = DB::table('log')
59            ->leftJoin('user', 'user.user_id', '=', 'log.user_id')
60            ->leftJoin('gedcom', 'gedcom.gedcom_id', '=', 'log.gedcom_id')
61            ->select(['log.*', new Expression("COALESCE(user_name, '<none>') AS user_name"), new Expression("COALESCE(gedcom_name, '<none>') AS gedcom_name")]);
62
63        $tz  = new DateTimeZone(Auth::user()->getPreference(UserInterface::PREF_TIME_ZONE, 'UTC'));
64        $utc = new DateTimeZone('UTC');
65
66        if ($from !== '') {
67            $from_time = DateTimeImmutable::createFromFormat('Y-m-d', $from, $tz)
68                ->setTime(0, 0)
69                ->setTimezone($utc)
70                ->format('Y-m-d H:i:s');
71
72            $query->where('log_time', '>=', $from_time);
73        }
74
75        if ($to !== '') {
76            $to_time = DateTimeImmutable::createFromFormat('Y-m-d', $from, $tz)
77                ->setTime(0, 0)
78                ->add(new DateInterval('P1D'))
79                ->setTimezone($utc)
80                ->format('Y-m-d H:i:s');
81
82            $query->where('log_time', '<', $to_time);
83        }
84
85        if ($type !== '') {
86            $query->where('log_type', '=', $type);
87        }
88
89        if ($text !== '') {
90            $query->where('log_message', 'LIKE', '%' . addcslashes($text, '\\%_') . '%');
91        }
92
93        if ($ip !== '') {
94            $query->where('ip_address', 'LIKE', addcslashes($ip, '\\%_') . '%');
95        }
96
97        if ($username !== '') {
98            $query->where('user_name', '=', $username);
99        }
100
101        if ($tree !== '') {
102            $query->where('gedcom_name', '=', $tree);
103        }
104
105        return $query;
106    }
107}
108