xref: /webtrees/app/Services/SiteLogsService.php (revision b9a4a6c608ac9f9f3bf6c12fbe1fbe871f30fedb) 
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                ->setTimezone($utc)
69                ->format('Y-m-d H:i:s');
70
71            $query->where('log_time', '>=', $from_time);
72        }
73
74        if ($to !== '') {
75            $to_time = DateTimeImmutable::createFromFormat('!Y-m-d', $to, $tz)
76                ->add(new DateInterval('P1D'))
77                ->setTimezone($utc)
78                ->format('Y-m-d H:i:s');
79
80            $query->where('log_time', '<', $to_time);
81        }
82
83        if ($type !== '') {
84            $query->where('log_type', '=', $type);
85        }
86
87        if ($text !== '') {
88            $query->where('log_message', 'LIKE', '%' . addcslashes($text, '\\%_') . '%');
89        }
90
91        if ($ip !== '') {
92            $query->where('ip_address', 'LIKE', addcslashes($ip, '\\%_') . '%');
93        }
94
95        if ($username !== '') {
96            $query->where('user_name', '=', $username);
97        }
98
99        if ($tree !== '') {
100            $query->where('gedcom_name', '=', $tree);
101        }
102
103        return $query;
104    }
105}
106