switch over to "absences reduce the expected working-time"

[pkoevesdi]

First: Thank You very much for implementing this option, see kimai/kimai#5054.
Now, changing that option "destroys" all locked times of the users. The unlocked ones seem unaffected.
Unlocking is for many of our users sheets no option, because they got locked because of change in their contract. We'd have to reset the contract of each of them to the values of the lockes ranges, open the lock, change the setting, lock again, change back the contract. For some users theis may even effect more than one range/change in contract.

Is there an easier / recommended way of changing the option, but keeping also the locked times intact?
I'm, also open to some batch change in the database, if pointed to the right spot.

Thank You!

[kevinpapst]

Now, changing that option "destroys" all locked times of the users. The unlocked ones seem unaffected.

What do you mean by that?

[pkoevesdi]

Thanks a lot! Is there any hint how we can fix this for our installation? For instance, I didn't find out, in which database table exactly the locking happens.

[kevinpapst]

You could test if updating the expected and actual column in kimai2_working_times would fix the problem.

[pkoevesdi]

Thanks. I checked them already, they stay the same, no matter the setting. I propably could tweak the values there, but I can't distinguish there between locked (and to be corrected) and non-locked (to stay as they are) times. Where are the locks stored?

[kevinpapst]

each line represents one locked day for one employee

[pkoevesdi]

Ah, ok, I didn't see that. The unlocked aren't there at all? Ok, with this info I'd try to reduce expected columns all by for instance 28800 s and also decrease actual by the same amount. Thank You, I'll see what I can achieve.

[pkoevesdi]

So, I have changed these settings and corrected everything in the database. This affected the entries of the query:

SELECT
    kwt.*
FROM
    kimaidb.kimai2_working_times AS kwt
WHERE
    (
        EXISTS (
            SELECT 1
            FROM kimaidb.kimai2_absence AS ka
            WHERE kwt.date = ka.date
            AND kwt.user_id = ka.user_id
            AND ka.type != "time_off"
        )
        OR EXISTS (
            SELECT 1
            FROM kimaidb.kimai2_public_holiday AS kp
            WHERE kwt.date = kp.date
        )
    )
    AND (kwt.expected != 0 OR kwt.actual != 0)
ORDER BY
    kwt.user_id, kwt.date;

...in which I manually (for security reasons) substracted the value of expected from the columns actual and expected (thus set expected to 0).
Had previously taken screenshots of all users who were affected (who had locked periods) and afterwards compared, all times now agree with the state before the 4 settings were changed.

[kevinpapst]

Awesome, thanks for sharing 👍

This s great for everyone who wants to switch these settings in an installation with historic data.