xref: /webtrees/app/Services/SiteLogsService.php (revision d11be7027e34e3121be11cc025421873364403f9) 
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