Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00% covered (success)
100.00%
41 / 41
100.00% covered (success)
100.00%
3 / 3
 CRAP
100.00% covered (success)
100.00%
1 / 1
Goodie
100.00% covered (success)
100.00%
41 / 41
100.00% covered (success)
100.00%
3 / 3
 7
100.00% covered (success)
100.00%
1 / 1
 shiftScoreQuery
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
 3
 worklogScoreQuery
100.00% covered (success)
100.00%
11 / 11
100.00% covered (success)
100.00%
1 / 1
 2
 userScore
100.00% covered (success)
100.00%
25 / 25
100.00% covered (success)
100.00%
1 / 1
 2
1<?php
2
3declare(strict_types=1);
4
5namespace Engelsystem\Helpers;
6
7use Engelsystem\Database\Database;
8use Engelsystem\Models\User\User;
9use Illuminate\Database\Query\Expression;
10use Illuminate\Database\Query\Grammars\MySqlGrammar;
11
12class Goodie
13{
14    /**
15     * Generates the query to sum night shifts
16     *
17     * Shifts and shift entries must be available via join
18     */
19    public static function shiftScoreQuery(): Expression
20    {
21        $nightShifts = config('night_shifts');
22
23        /** @var Database $db */
24        $db = app(Database::class);
25        $connection = $db->getConnection();
26
27        if (!$connection->getQueryGrammar() instanceof MySqlGrammar) {
28            return $connection->raw('0');
29        }
30
31        // @codeCoverageIgnoreStart
32        // as sqlite does not support TIMESTAMPDIFF
33
34        if (!$nightShifts['enabled']) {
35            return $connection->raw(
36                /** @lang MySQL */
37                'COALESCE(SUM(TIMESTAMPDIFF(MINUTE, shifts.start, shifts.end) * 60), 0)'
38            );
39        }
40
41        /* @see \Engelsystem\Models\Shifts\Shift::isNightShift to keep them in sync */
42        $query =
43            /** @lang MySQL */
44            '
45                COALESCE(SUM(
46                    /* Shift length */
47                    TIMESTAMPDIFF(MINUTE, shifts.start, shifts.end) * 60
48                    /* Is night shift */
49                    * (
50                        CASE WHEN
51                            /* Starts during night */
52                            HOUR(shifts.start) >= %1$d AND HOUR(shifts.start) < %2$d
53                            /* Ends during night */
54                            OR (
55                                HOUR(shifts.end) > %1$d
56                                || HOUR(shifts.end) = %1$d AND MINUTE(shifts.end) > 0
57                            ) AND HOUR(shifts.end) <= %2$d
58                            /* Starts before and ends after night */
59                            OR HOUR(shifts.start) <= %1$d AND HOUR(shifts.end) >= %2$d
60                        /* Use multiplier */
61                        THEN
62                            /* Handle freeloading */
63                            CASE WHEN `shift_entries`.`freeloaded_by` IS NULL
64                            THEN %3$d
65                            ELSE -%3$d
66                            END
67                        ELSE
68                            /* Handle freeloading */
69                            CASE WHEN `shift_entries`.`freeloaded_by` IS NULL
70                            THEN 1
71                            ELSE -2
72                            END
73                        END
74                    )
75                ), 0)
76            ';
77
78        $query = sprintf($query, $nightShifts['start'], $nightShifts['end'], $nightShifts['multiplier']);
79
80        return $connection->raw($query);
81        // @codeCoverageIgnoreEnd
82    }
83
84    public static function worklogScoreQuery(): Expression
85    {
86        $nightShifts = config('night_shifts');
87
88        /** @var Database $db */
89        $db = app(Database::class);
90        $connection = $db->getConnection();
91
92        if (!$nightShifts['enabled']) {
93            return $connection->raw(
94                /** @lang MySQL */
95                'COALESCE(SUM(`hours`), 0)'
96            );
97        }
98
99        return $connection->raw(sprintf(
100            /** @lang MySQL */
101            'COALESCE(SUM(IF(`night_shift`, `hours` * %d, `hours`)), 0)',
102            $nightShifts['multiplier']
103        ));
104    }
105
106    /**
107     * Returns the goodie score (number of hours counted for goodie score)
108     * Includes only ended shifts
109     */
110    public static function userScore(User $user): float
111    {
112        /** @var Database $db */
113        $db = app(Database::class);
114        $con = $db->getConnection();
115
116        $state = $con
117            ->query()
118            ->from('users')
119            ->selectRaw(sprintf(
120                /** @lang MySQL */
121                'ROUND((%s) / 3600, 2) AS `goodie_score`',
122                self::shiftScoreQuery()->getValue($con->getQueryGrammar())
123            ))
124            ->where('users.id', $user->id)
125            ->join('shift_entries', 'users.id', 'shift_entries.user_id')
126            ->join('shifts', 'shift_entries.shift_id', 'shifts.id')
127            ->where('shifts.end', '<', Carbon::now())
128            ->groupBy('users.id')
129            ->first();
130
131        $shiftHours = 0;
132        if ($state) {
133            // @codeCoverageIgnoreStart
134            $shiftHours = (float) $state->goodie_score;
135            // @codeCoverageIgnoreEnd
136        }
137
138        $worklogHours = $user->worklogs()
139            ->where('worked_at', '<=', Carbon::Now())
140            ->selectRaw(sprintf(
141                /** @lang MySQL */
142                '%s as `total_hours`',
143                self::worklogScoreQuery()->getValue($con->getQueryGrammar())
144            ))
145            ->value('total_hours');
146
147        return $shiftHours + $worklogHours;
148    }
149}