New night shift goodie score calculation #1590

xuwhite · Dec 13, 2025

Night shifts hours get multiplied partially. Only hours inside the night time get night shift multiplier.

olebittner · Dec 13, 2025

db/migrations/2025_12_13_000000_add_goodie_score_calculation_function.php

+                            JOIN shift_entries se ON se.user_id = u.id
+                            JOIN shifts s ON se.shift_id = s.id
+                            WHERE u.id = user_id
+                            AND s.end < CURRENT_TIMESTAMP;


start and end timestamps are calculated by the engelsystem and then compared to a timestamp calculated by the database. This might cause issues if the database clock and engelsystem clock are not in sync.

Only a thought that came to mind, not sure how much of a problem this is in practice.

You are right this should be changed.
It was Carbon::now() - This parameter should be added

olebittner · Dec 13, 2025

db/migrations/2025_12_13_000000_add_goodie_score_calculation_function.php

+                                LEAVE read_loop;
+                        END IF;
+                        IF freeloaded THEN
+                            SET SHIFT_SUM = TIMESTAMPDIFF(SECOND, shift_start, shift_end) * -2;


previously the negated night_shift_multiplier was used freeloading. Not sure which is better, just wanted to point it out.

AFAIK the multiplier was hard coded so no difference at all ;)

@obit is right. I'll change this.

olebittner · Dec 13, 2025

db/migrations/2025_12_13_000000_add_goodie_score_calculation_function.php

+                            END WHILE;
+                            
+                            SET SHIFT_SUM = TIMESTAMPDIFF(SECOND, shift_start, shift_end) 
+                                                + (NIGHT_SHIFT_SECONDS * night_shift_multiplier);


wouldn't the night_shift part be added once too many because it is also part of the TIMESTAMPDIFF?

nvm, I see you reduce the multiplier when calling the function

now that I think of it, I think the multipler should be reduced in the function, so it is self contained and you don't need to remeber to reduce it when calling the function

We discussed about it - IMHO the multiplier is now used correctly - Before that change the whole shift was multiplied.

Now only the night shift part is the amount of time in question.

I think what @obit means is to input the real multiplier into the function and then reduce it by one in the function. When reintroducing the night shift multiplier as freeloading I will probably change that and agree with @obit even though I was the person that was against this in the first place. 😇

olebittner · Dec 13, 2025

resources/lang/de_DE/default.po

-msgid "Night shifts between %d and %d am are multiplied by %d for the goodie score."
-msgstr "Nachtschichten zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert."
+msgid "night_shift.goodie_score.hint"
+msgstr "Nachtschichtstunden zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert. Stunden außerhalb der Nachtzeit zählen normal."


might be nitpicking, but this chage was introduced because shift with as little as 15min during the nightshift would be multiplied. I think specifing hours in the hint might cause confusion in those cases.

Suggested change

msgstr "Nachtschichtstunden zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert. Stunden außerhalb der Nachtzeit zählen normal."

msgstr "Die Zeit in der Nacht zwischen %d und %d Uhr wird für den Goodie Score mit %3$d multipliziert. Die Zeit außerhalb der Nacht zählt normal."

olebittner · Dec 13, 2025

resources/lang/en_US/default.po

 msgstr "Edit goodie"

+msgid "night_shift.goodie_score.hint"
+msgstr "Night shift hours between %d and %d am are multiplied by %d for the goodie score. Hours outside of the night time are counted regularly."


Suggested change

msgstr "Night shift hours between %d and %d am are multiplied by %d for the goodie score. Hours outside of the night time are counted regularly."

msgstr "Night shift time between %d and %d am is multiplied by %d for the goodie score. Time outside of the night is counted regularly."

Maybe "Time worked between ... and ..." instead of "Night shift hours/time"?

Also, I would leave out the "time outside of the night is counted regularly" - that's the default after all.

I like that wording.

MichiK · Dec 15, 2025

I played with this a bit and tried a few exotic shift times and didn't manage to break the calculation.

However, it would be nice to see the hours that a shift is counted with somewhere (maybe in the tooltip and/or in the table in the user profile) to make the system a bit more intuitive and the calculation easier to understand.

rehleinBo · Dec 15, 2025

@MichiK - This would unfortunatetly take a little more rework.

I see it the same way. It will lead to questions and it's not very easy to calculate in your head except you are some kind of genius...

I'm willing to enhance it for 40C3 if @xuwhite will do the PHP part again :-)

MyIgel

A preliminary review:

The tests are not running and the linter says nope:

 ------ ----------------------------------------------------------------------------------- 
  Line   tests/Unit/Helpers/GoodieTest.php                                                  
 ------ ----------------------------------------------------------------------------------- 
  26     Call to an undefined static method Engelsystem\Helpers\Goodie::shiftScoreQuery().  
         🪪  staticMethod.notFound                                                          
 ------ -----------------------------------------------------------------------------------

In sum i don't really like SQL functions as the can only be changed by migrations and thus as a whole but if you really want to do it this way i can't stop you.

MyIgel · Dec 21, 2025

db/migrations/2025_12_13_000000_add_goodie_score_calculation_function.php

+     */
+    public function up(): void
+    {
+        $this->db->statement(


This needs a check to not be run during tests, see other migrations for reference:

[...] .......WF...................EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE 61 / 1122 ( 5%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE 122 / 1122 ( 10%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE......E 183 / 1122 ( 16%) EEEEEEEEEEE.EEEEEEEE....................EEEEEEEEEEE...EEEEEEE 244 / 1122 ( 21%) EEEEEEEEEEEEEEEEEE......EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE 305 / 1122 ( 27%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE 366 / 1122 ( 32%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE.................. 427 / 1122 ( 38%) ...........EEEEEEEEEEEEEEEEEEEEEE............................ 488 / 1122 ( 43%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE....EEEEEEEEEEEEE... 549 / 1122 ( 48%) ................................................EE........... 610 / 1122 ( 54%) .......................EEEE.E...........EEEE................. 671 / 1122 ( 59%) ............................................................. 732 / 1122 ( 65%) ....................................................EEEE..... 793 / 1122 ( 70%) ............................................................. 854 / 1122 ( 76%) EEEEEEEEEEEEEEEEEE.E......................................... 915 / 1122 ( 81%) ...................................E........EEEEEEEEEEEEEEEEE 976 / 1122 ( 86%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE 1037 / 1122 ( 92%) EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE..........E.......E....... 1098 / 1122 ( 97%) ........................ 1122 / 1122 (100%). Time: 03:22.754, Memory: 134.50 MB There were 571 errors: 1) Engelsystem\Test\Unit\Controllers\Admin\ConfigControllerTest::testIndex Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 near "DEFINER": syntax error (Connection: default, SQL: CREATE DEFINER=`engelsystem`@`%` FUNCTION `goodie_score` ( [...]

Now there are less so its better debuggable ;)

MyIgel · Dec 21, 2025

db/migrations/2025_12_13_000000_add_goodie_score_calculation_function.php

+                    DECLARE NIGHT_END TIMESTAMP;
+                    DECLARE NIGHT_SHIFT_SECONDS int;
+                    DECLARE SHIFT_SUM int;
+                    DECLARE GOODY_SUM int;


The consistent wording would be goodie

Suggested change

DECLARE GOODY_SUM int;

DECLARE GOODIE_SUM int;

(same for following)

MyIgel · Dec 21, 2025

resources/lang/de_DE/default.po

-msgid "Night shifts between %d and %d am are multiplied by %d for the goodie score."
-msgstr "Nachtschichten zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert."
+msgid "night_shift.goodie_score.hint"
+msgstr "Gearbeitete Zeit zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert."


Suggested change

msgstr "Gearbeitete Zeit zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert."

msgstr "Gearbeitete Zeit zwischen %d und %d Uhr wird für den Goodie Score mit %3$d multipliziert."

MyIgel · Dec 21, 2025

resources/lang/en_US/default.po

 msgstr "Edit goodie"

+msgid "night_shift.goodie_score.hint"
+msgstr "Time worked between %d and %d am are multiplied by %d for the goodie score."


Suggested change

msgstr "Time worked between %d and %d am are multiplied by %d for the goodie score."

msgstr "Time worked between %d and %d am is multiplied by %d for the goodie score."

MyIgel · Dec 21, 2025

src/Helpers/Goodie.php

-     *
-     * Shifts and shift entries must be available via join
-     */
-    public static function shiftScoreQuery(): Expression


That function is still used in multiple places (admin_active, it is referenced in isNightShift),, please replace those too or re-add it n a compatible way

MyIgel · Dec 21, 2025

src/Helpers/Goodie.php

-        $connection = $db->getConnection();
-
-        if (!$connection->getQueryGrammar() instanceof MySqlGrammar) {
-            return $connection->raw('0');


This will be a still required distinction imho

src/Helpers/Goodie.php

MyIgel · Dec 21, 2025

db/migrations/2025_12_13_000000_add_goodie_score_calculation_function.php

+                    `night_shift_start` INT,
+                    `night_shift_end` INT,
+                    `night_shift_multiplier` INT,
+                    `now` TIMESTAMP


Suggested change

`now` TIMESTAMP

`until` TIMESTAMP

or something similar, now is imho not the right wording or could be directly replaced with NOW()

olebittner reviewed Dec 13, 2025

View reviewed changes

xuwhite added 2 commits December 16, 2025 14:23

new night shift goodie score calculation

7e8b9b5

linter

af4f7af

xuwhite force-pushed the goodie_score branch from e2df954 to af4f7af Compare December 16, 2025 13:24

xuwhite changed the base branch from 39c3 to main December 16, 2025 13:25

MyIgel requested changes Dec 21, 2025

View reviewed changes

MyIgel marked this pull request as draft December 21, 2025 15:17

MyIgel and others added 2 commits December 23, 2025 00:28

Fix most tests

4ecd8be

fix some tests

4c57b6a

	msgstr "Nachtschichtstunden zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert. Stunden außerhalb der Nachtzeit zählen normal."
	msgstr "Die Zeit in der Nacht zwischen %d und %d Uhr wird für den Goodie Score mit %3$d multipliziert. Die Zeit außerhalb der Nacht zählt normal."

	msgstr "Night shift hours between %d and %d am are multiplied by %d for the goodie score. Hours outside of the night time are counted regularly."
	msgstr "Night shift time between %d and %d am is multiplied by %d for the goodie score. Time outside of the night is counted regularly."

	msgstr "Gearbeitete Zeit zwischen %d und %d Uhr werden für den Goodie Score mit %3$d multipliziert."
	msgstr "Gearbeitete Zeit zwischen %d und %d Uhr wird für den Goodie Score mit %3$d multipliziert."

	msgstr "Time worked between %d and %d am are multiplied by %d for the goodie score."
	msgstr "Time worked between %d and %d am is multiplied by %d for the goodie score."

Search code, repositories, users, issues, pull requests...

New night shift goodie score calculation #1590

Are you sure you want to change the base?

New night shift goodie score calculation #1590

Uh oh!

Conversation

xuwhite commented Dec 13, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

MichiK commented Dec 15, 2025

Uh oh!

rehleinBo commented Dec 15, 2025

Uh oh!

MyIgel left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

MyIgel Dec 21, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

MyIgel Dec 21, 2025 •

edited

Loading