Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00%
163 / 163
100.00%
37 / 37
 CRAP
100.00%
1 / 1
Stats
100.00%
163 / 163
100.00%
37 / 37
 65
100.00%
1 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 usersState
100.00%
17 / 17
100.00%
1 / 1
 3
 usersInfo
100.00%
4 / 4
100.00%
1 / 1
 1
 forceActiveUsers
100.00%
1 / 1
100.00%
1 / 1
 1
 forceFoodUsers
100.00%
1 / 1
100.00%
1 / 1
 1
 usersPronouns
100.00%
1 / 1
100.00%
1 / 1
 1
 email
100.00%
7 / 7
100.00%
1 / 1
 6
 currentlyWorkingUsers
100.00%
10 / 10
100.00%
1 / 1
 3
 vouchersQuery
100.00%
1 / 1
100.00%
1 / 1
 1
 vouchers
100.00%
1 / 1
100.00%
1 / 1
 1
 vouchersBuckets
100.00%
7 / 7
100.00%
1 / 1
 3
 goodies
100.00%
3 / 3
100.00%
1 / 1
 2
 tshirtSizes
100.00%
5 / 5
100.00%
1 / 1
 1
 languages
100.00%
4 / 4
100.00%
1 / 1
 1
 themes
100.00%
4 / 4
100.00%
1 / 1
 1
 licenses
100.00%
16 / 16
100.00%
1 / 1
 2
 workSecondsQuery
n/a
0 / 0
n/a
0 / 0
 5
 workSeconds
n/a
0 / 0
n/a
0 / 0
 1
 workBuckets
n/a
0 / 0
n/a
0 / 0
 1
 getBuckets
100.00%
8 / 8
100.00%
1 / 1
 3
 worklogSeconds
100.00%
2 / 2
100.00%
1 / 1
 1
 worklogBuckets
100.00%
7 / 7
100.00%
1 / 1
 1
 locations
100.00%
2 / 2
100.00%
1 / 1
 1
 shiftTypes
100.00%
2 / 2
100.00%
1 / 1
 1
 angelTypesSum
100.00%
1 / 1
100.00%
1 / 1
 1
 angelTypes
100.00%
21 / 21
100.00%
1 / 1
 3
 shifts
100.00%
1 / 1
100.00%
1 / 1
 1
 announcements
100.00%
2 / 2
100.00%
1 / 1
 2
 comments
100.00%
2 / 2
100.00%
1 / 1
 1
 questions
100.00%
6 / 6
100.00%
1 / 1
 3
 faq
100.00%
1 / 1
100.00%
1 / 1
 1
 messages
100.00%
1 / 1
100.00%
1 / 1
 1
 sessions
100.00%
3 / 3
100.00%
1 / 1
 1
 oauth
100.00%
4 / 4
100.00%
1 / 1
 1
 databaseRead
100.00%
3 / 3
100.00%
1 / 1
 1
 databaseWrite
100.00%
7 / 7
100.00%
1 / 1
 1
 logEntries
100.00%
2 / 2
100.00%
1 / 1
 2
 passwordResets
100.00%
1 / 1
100.00%
1 / 1
 1
 getQuery
100.00%
3 / 3
100.00%
1 / 1
 1
 raw
100.00%
1 / 1
100.00%
1 / 1
 1
1<?php
2
3declare(strict_types=1);
4
5namespace Engelsystem\Controllers\Metrics;
6
7use Carbon\Carbon;
8use Engelsystem\Database\Database;
9use Engelsystem\Models\AngelType;
10use Engelsystem\Models\EventConfig;
11use Engelsystem\Models\Faq;
12use Engelsystem\Models\Location;
13use Engelsystem\Models\LogEntry;
14use Engelsystem\Models\Message;
15use Engelsystem\Models\News;
16use Engelsystem\Models\NewsComment;
17use Engelsystem\Models\OAuth;
18use Engelsystem\Models\Question;
19use Engelsystem\Models\Shifts\Shift;
20use Engelsystem\Models\Shifts\ShiftType;
21use Engelsystem\Models\User\License;
22use Engelsystem\Models\User\PasswordReset;
23use Engelsystem\Models\User\PersonalData;
24use Engelsystem\Models\User\Settings;
25use Engelsystem\Models\User\State;
26use Engelsystem\Models\User\User;
27use Engelsystem\Models\UserAngelType;
28use Engelsystem\Models\Worklog;
29use Illuminate\Contracts\Database\Query\Builder as BuilderContract;
30use Illuminate\Database\Eloquent\Builder;
31use Illuminate\Database\Query\Builder as QueryBuilder;
32use Illuminate\Database\Query\Expression as QueryExpression;
33use Illuminate\Support\Collection;
34
35class Stats
36{
37    public function __construct(protected Database $db)
38    {
39    }
40
41    /**
42     * The number of users that arrived/not arrived and/or did some work
43     *
44     */
45    public function usersState(?bool $working = null, bool $arrived = true): int
46    {
47        $query = State::whereArrived($arrived);
48
49        if (!is_null($working)) {
50            $query
51                ->leftJoin('worklogs', 'worklogs.user_id', '=', 'users_state.user_id')
52                ->leftJoin('shift_entries', 'shift_entries.user_id', '=', 'users_state.user_id')
53                ->distinct();
54
55            $query->where(function ($query) use ($working): void {
56                /** @var QueryBuilder $query */
57                if ($working) {
58                    $query
59                        ->whereNotNull('shift_entries.shift_id')
60                        ->orWhereNotNull('worklogs.hours');
61
62                    return;
63                }
64
65                $query
66                    ->whereNull('shift_entries.shift_id')
67                    ->whereNull('worklogs.hours');
68            });
69        }
70
71        return $query->count('users_state.user_id');
72    }
73
74    public function usersInfo(): int
75    {
76        return State::query()
77            ->whereNotNull('user_info')
78            ->whereNot('user_info', '')
79            ->count();
80    }
81
82    public function forceActiveUsers(): int
83    {
84        return State::whereForceActive(true)->count();
85    }
86
87    public function forceFoodUsers(): int
88    {
89        return State::whereForceFood(true)->count();
90    }
91
92    public function usersPronouns(): int
93    {
94        return PersonalData::query()->where('pronoun', '!=', '')->count();
95    }
96
97    public function email(string $type): int
98    {
99        return match ($type) {
100            'system' => Settings::whereEmailShiftinfo(true)->count(),
101            'humans' => Settings::whereEmailHuman(true)->count(),
102            'goodie'  => Settings::whereEmailGoodie(true)->count(),
103            'news'   => Settings::whereEmailNews(true)->count(),
104            default  => 0,
105        };
106    }
107
108    /**
109     * The number of currently working users
110     *
111     */
112    public function currentlyWorkingUsers(?bool $freeloaded = null): int
113    {
114        $query = User::query()
115            ->join('shift_entries', 'shift_entries.user_id', '=', 'users.id')
116            ->join('shifts', 'shifts.id', '=', 'shift_entries.shift_id')
117            ->where('shifts.start', '<=', Carbon::now())
118            ->where('shifts.end', '>', Carbon::now());
119
120        if (!is_null($freeloaded)) {
121            $freeloaded
122                ? $query->whereNotNull('shift_entries.freeloaded_by')
123                : $query->whereNull('shift_entries.freeloaded_by');
124        }
125
126        return $query->count();
127    }
128
129    protected function vouchersQuery(): Builder
130    {
131        return State::query();
132    }
133
134    public function vouchers(): int
135    {
136        return (int) $this->vouchersQuery()->sum('got_voucher');
137    }
138
139    public function vouchersBuckets(array $buckets): array
140    {
141        $return = [];
142        foreach ($buckets as $bucket) {
143            $query = $this->vouchersQuery();
144
145            if ($bucket !== '+Inf') {
146                $query->where('got_voucher', '<=', $bucket);
147            }
148
149            $return[$bucket] = $query->count('got_voucher');
150        }
151
152        return $return;
153    }
154
155    public function goodies(bool $issued): int
156    {
157        if ($issued) {
158            return State::whereGotGoodie(true)->count();
159        }
160        return State::whereReservedGoodie(true)->whereGotGoodie(false)->count();
161    }
162
163    public function tshirtSizes(): Collection
164    {
165        return PersonalData::query()
166            ->select(['shirt_size', $this->raw('COUNT(shirt_size) AS count')])
167            ->whereNotNull('shirt_size')
168            ->groupBy(['shirt_size'])
169            ->get();
170    }
171
172    public function languages(): Collection
173    {
174        return Settings::query()
175            ->select(['language', $this->raw('COUNT(language) AS count')])
176            ->groupBy(['language'])
177            ->get();
178    }
179
180    public function themes(): Collection
181    {
182        return Settings::query()
183            ->select(['theme', $this->raw('COUNT(theme) AS count')])
184            ->groupBy(['theme'])
185            ->get();
186    }
187
188    public function licenses(string $license, bool $confirmed = false): int
189    {
190        $mapping = [
191            'has_car'   => ['has_car', null],
192            'forklift' => ['drive_forklift', 'drive_confirmed'],
193            'car' => ['drive_car', 'drive_confirmed'],
194            '3.5t' => ['drive_3_5t', 'drive_confirmed'],
195            '7.5t' => ['drive_7_5t', 'drive_confirmed'],
196            '12t' => ['drive_12t', 'drive_confirmed'],
197            'ifsg_light' => ['ifsg_certificate_light', 'ifsg_confirmed'],
198            'ifsg' => ['ifsg_certificate', 'ifsg_confirmed'],
199        ];
200
201        $query = (new License())
202            ->getQuery()
203            ->where($mapping[$license][0], true);
204        if (!is_null($mapping[$license][1])) {
205            $query->where($mapping[$license][1], $confirmed);
206        }
207
208        return $query->count();
209    }
210
211    /**
212     *
213     * @codeCoverageIgnore because it is only used in functions that use TIMESTAMPDIFF
214     */
215    protected function workSecondsQuery(?bool $done = null, ?bool $freeloaded = null): QueryBuilder
216    {
217        $query = $this
218            ->getQuery('shift_entries')
219            ->join('shifts', 'shifts.id', '=', 'shift_entries.shift_id');
220
221        if (!is_null($freeloaded)) {
222            $freeloaded
223                ? $query->whereNotNull('freeloaded_by')
224                : $query->whereNull('freeloaded_by');
225        }
226
227        if (!is_null($done)) {
228            $query->where('end', ($done ? '<' : '>='), Carbon::now());
229        }
230
231        return $query;
232    }
233
234    /**
235     * The amount of worked seconds
236     *
237     *
238     * @codeCoverageIgnore as TIMESTAMPDIFF is not implemented in SQLite
239     */
240    public function workSeconds(?bool $done = null, ?bool $freeloaded = null): int
241    {
242        $query = $this->workSecondsQuery($done, $freeloaded);
243
244        return (int) $query->sum($this->raw('TIMESTAMPDIFF(MINUTE, start, end) * 60'));
245    }
246
247    /**
248     * The number of worked shifts
249     *
250     *
251     * @codeCoverageIgnore as TIMESTAMPDIFF is not implemented in SQLite
252     */
253    public function workBuckets(array $buckets, ?bool $done = null, ?bool $freeloaded = null): array
254    {
255        return $this->getBuckets(
256            $buckets,
257            $this->workSecondsQuery($done, $freeloaded),
258            'user_id',
259            'SUM(TIMESTAMPDIFF(MINUTE, start, end) * 60)',
260            'SUM(TIMESTAMPDIFF(MINUTE, start, end) * 60)'
261        );
262    }
263
264    protected function getBuckets(
265        array $buckets,
266        BuilderContract $basicQuery,
267        string $groupBy,
268        string $having,
269        string $count
270    ): array {
271        $return = [];
272
273        foreach ($buckets as $bucket) {
274            $query = clone $basicQuery;
275            $query->groupBy($groupBy);
276
277            if ($bucket !== '+Inf') {
278                $query->having($this->raw($having), '<=', $bucket);
279            }
280
281            $return[$bucket] = count($query->get($this->raw($count)));
282        }
283
284        return $return;
285    }
286
287    public function worklogSeconds(): int
288    {
289        return (int) Worklog::query()
290            ->sum($this->raw('hours * 60 * 60'));
291    }
292
293    public function worklogBuckets(array $buckets): array
294    {
295        return $this->getBuckets(
296            $buckets,
297            Worklog::query(),
298            'user_id',
299            'SUM(hours * 60 * 60)',
300            'SUM(hours * 60 * 60)'
301        );
302    }
303
304    public function locations(): int
305    {
306        return Location::query()
307            ->count();
308    }
309
310    public function shiftTypes(): int
311    {
312        return ShiftType::query()
313            ->count();
314    }
315
316    public function angelTypesSum(): int
317    {
318        return AngelType::query()->count();
319    }
320
321    public function angelTypes(): array
322    {
323        $angelTypes = [];
324        $rawAngelTypes = AngelType::query()->select(['id', 'name', 'restricted'])->orderBy('name')->get();
325        foreach ($rawAngelTypes as $angelType) {
326            $restricted = $angelType->restricted;
327            $userAngelTypeQuery = UserAngelType::query()
328                ->where('angel_type_id', $angelType->id);
329
330            $members = $userAngelTypeQuery->count();
331            $supporters = (clone $userAngelTypeQuery)->where('supporter', true)->count();
332            $confirmed = $members - $supporters;
333            $unconfirmed = 0;
334            if ($restricted) {
335                $confirmed = (clone $userAngelTypeQuery)->whereNotNull('confirm_user_id')->count() - $supporters;
336                $unconfirmed = $members - ($supporters + $confirmed);
337            }
338
339            $angelTypes[] = [
340                'name' => $angelType->name,
341                'restricted' => $restricted,
342                'unconfirmed' => $unconfirmed,
343                'supporters' => $supporters,
344                'confirmed' => $confirmed,
345            ];
346        }
347        return $angelTypes;
348    }
349
350    public function shifts(): int
351    {
352        return Shift::query()->count();
353    }
354
355    public function announcements(?bool $meeting = null): int
356    {
357        $query = is_null($meeting) ? News::query() : News::whereIsMeeting($meeting);
358
359        return $query->count();
360    }
361
362    public function comments(): int
363    {
364        return NewsComment::query()
365            ->count();
366    }
367
368    public function questions(?bool $answered = null): int
369    {
370        $query = Question::query();
371        if (!is_null($answered)) {
372            if ($answered) {
373                $query->whereNotNull('answerer_id');
374            } else {
375                $query->whereNull('answerer_id');
376            }
377        }
378
379        return $query->count();
380    }
381
382    public function faq(): int
383    {
384        return Faq::query()->count();
385    }
386
387    public function messages(): int
388    {
389        return Message::query()->count();
390    }
391
392    public function sessions(): int
393    {
394        return $this
395            ->getQuery('sessions')
396            ->count();
397    }
398
399    public function oauth(): Collection
400    {
401        return OAuth::query()
402            ->select(['provider', $this->raw('COUNT(provider) AS count')])
403            ->groupBy(['provider'])
404            ->get();
405    }
406
407    public function databaseRead(): float
408    {
409        $start = microtime(true);
410
411        (new EventConfig())->findOrNew('last_metrics');
412
413        return microtime(true) - $start;
414    }
415
416    public function databaseWrite(): float
417    {
418        $config = (new EventConfig())->findOrNew('last_metrics');
419        $config
420            ->setAttribute('name', 'last_metrics')
421            ->setAttribute('value', new Carbon());
422
423        $start = microtime(true);
424
425        $config->save();
426
427        return microtime(true) - $start;
428    }
429
430    public function logEntries(?string $level = null): int
431    {
432        $query = is_null($level) ? LogEntry::query() : LogEntry::whereLevel($level);
433
434        return $query->count();
435    }
436
437    public function passwordResets(): int
438    {
439        return PasswordReset::query()->count();
440    }
441
442    protected function getQuery(string $table): QueryBuilder
443    {
444        return $this->db
445            ->getConnection()
446            ->table($table);
447    }
448
449    protected function raw(mixed $value): QueryExpression
450    {
451        return $this->db->getConnection()->raw($value);
452    }
453}