Kimai2: API call adding a timesheet entry in the past without end date is saved with incorrect length

[ZeiP]

Describe the bug
I'm trying to import timesheets from Harvest. I only have the dates and the durations of the logs, so I'm planning to import them by specifying just begin and duration. In the UI this works fine as the end field is automatically populated. However, for past entries entered through the API the end field is null and therefore the timesheet is shown as being active in the UI.

When I edit the timesheet, the duration information is correct and the end field is generated correctly, but it's not really feasible to manually edit all the imported timesheets.

To Reproduce

1. Add a past timesheet entry through the API and leave out the end field altogether, for example with the following curl command:

curl -v -X POST "http://localhost:8001/api/timesheets" -H  "accept: application/json" -H "X-AUTH-USER: xxx" -H "X-AUTH-TOKEN: xxxxxx" -H  "Content-Type: application/json" -d "{  \"begin\": \"2020-11-22T01:34:27\",  \"duration\": \"05:00\",  \"project\": 1,  \"activity\": 2,  \"description\": \"string\",  \"tags\": \"string\",  \"fixedRate\": 0,  \"hourlyRate\": 0,  \"user\": 1,  \"exported\": true}"

1. Go to the UI. The UI shows the duration of the entry incorrectly due to it being still ”active”.
2. Edit the entry, which fixes the end field and the duration.

Logfile

[Wed Nov 25 00:04:45.932250 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT t0.id AS id_1, t0.name AS name_2, t0.color AS color_3 FROM kimai2_tags t0 WHERE t0.name = ? ["string"] []
[Wed Nov 25 00:04:45.952996 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: INSERT INTO kimai2_timesheet (start_time, end_time, timezone, duration, description, rate, internal_rate, fixed_rate, hourly_rate, exported, billable, category, modified_at, `user`, activity_id, project_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {"1":"2020-11-22 01:34:27","2":null,"3":"Europe/Helsinki","4":18000,"5":"string","6":0,"7":0,"8":0,"9":0,"10":true,"11":true,"12":"work","13":"2020-11-25 02:04:45","14":1,"15":2,"16":1} []
[Wed Nov 25 00:04:45.965500 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: INSERT INTO kimai2_timesheet_tags (timesheet_id, tag_id) VALUES (?, ?) [10,1] []
[Wed Nov 25 00:04:45.971599 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k0_.start_time AS start_time_1, k0_.end_time AS end_time_2, k0_.timezone AS timezone_3, k0_.duration AS duration_4, k0_.description AS description_5, k0_.rate AS rate_6, k0_.internal_rate AS internal_rate_7, k0_.fixed_rate AS fixed_rate_8, k0_.hourly_rate AS hourly_rate_9, k0_.exported AS exported_10, k0_.billable AS billable_11, k0_.category AS category_12, k0_.modified_at AS modified_at_13, k0_.`user` AS user_14, k0_.activity_id AS activity_id_15, k0_.project_id AS project_id_16 FROM kimai2_timesheet k0_ WHERE k0_.start_time IS NOT NULL AND k0_.end_time IS NULL AND k0_.user = ? ORDER BY k0_.start_time DESC [1] []
[Wed Nov 25 00:04:45.979343 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k1_.id AS id_1, k1_.name AS name_2, k1_.order_number AS order_number_3, k1_.order_date AS order_date_4, k1_.start AS start_5, k1_.end AS end_6, k1_.timezone AS timezone_7, k1_.comment AS comment_8, k1_.visible AS visible_9, k1_.budget AS budget_10, k1_.time_budget AS time_budget_11, k1_.color AS color_12, k0_.`user` AS user_13, k0_.activity_id AS activity_id_14, k0_.project_id AS project_id_15, k1_.customer_id AS customer_id_16 FROM kimai2_timesheet k0_ LEFT JOIN kimai2_projects k1_ ON k0_.project_id = k1_.id WHERE k0_.id IN (10) [] []
[Wed Nov 25 00:04:45.983053 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k1_.id AS id_1, k1_.name AS name_2, k1_.number AS number_3, k1_.comment AS comment_4, k1_.visible AS visible_5, k1_.company AS company_6, k1_.vat_id AS vat_id_7, k1_.contact AS contact_8, k1_.address AS address_9, k1_.country AS country_10, k1_.currency AS currency_11, k1_.phone AS phone_12, k1_.fax AS fax_13, k1_.mobile AS mobile_14, k1_.email AS email_15, k1_.homepage AS homepage_16, k1_.timezone AS timezone_17, k1_.budget AS budget_18, k1_.time_budget AS time_budget_19, k1_.color AS color_20, k0_.customer_id AS customer_id_21 FROM kimai2_projects k0_ LEFT JOIN kimai2_customers k1_ ON k0_.customer_id = k1_.id WHERE k0_.id IN (1) [] []
[Wed Nov 25 00:04:45.986165 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k1_.id AS id_1, k1_.name AS name_2, k1_.comment AS comment_3, k1_.visible AS visible_4, k1_.budget AS budget_5, k1_.time_budget AS time_budget_6, k1_.color AS color_7, k0_.`user` AS user_8, k0_.activity_id AS activity_id_9, k0_.project_id AS project_id_10, k1_.project_id AS project_id_11 FROM kimai2_timesheet k0_ LEFT JOIN kimai2_activities k1_ ON k0_.activity_id = k1_.id WHERE k0_.id IN (10) [] []
[Wed Nov 25 00:04:45.990151 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k1_.username AS username_1, k1_.username_canonical AS username_canonical_2, k1_.email AS email_3, k1_.email_canonical AS email_canonical_4, k1_.enabled AS enabled_5, k1_.salt AS salt_6, k1_.password AS password_7, k1_.last_login AS last_login_8, k1_.confirmation_token AS confirmation_token_9, k1_.password_requested_at AS password_requested_at_10, k1_.roles AS roles_11, k1_.id AS id_12, k1_.alias AS alias_13, k1_.registration_date AS registration_date_14, k1_.title AS title_15, k1_.avatar AS avatar_16, k1_.api_token AS api_token_17, k1_.auth AS auth_18, k0_.`user` AS user_19, k0_.activity_id AS activity_id_20, k0_.project_id AS project_id_21 FROM kimai2_timesheet k0_ LEFT JOIN kimai2_users k1_ ON k0_.`user` = k1_.`id` WHERE k0_.id IN (10) [] []
[Wed Nov 25 00:04:45.994078 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k1_.id AS id_1, k1_.name AS name_2, k1_.color AS color_3, k0_.`user` AS user_4, k0_.activity_id AS activity_id_5, k0_.project_id AS project_id_6 FROM kimai2_timesheet k0_ LEFT JOIN kimai2_timesheet_tags k2_ ON k0_.id = k2_.timesheet_id LEFT JOIN kimai2_tags k1_ ON k1_.id = k2_.tag_id WHERE k0_.id IN (10) [] []
[Wed Nov 25 00:04:45.998701 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:45] doctrine.DEBUG: SELECT k0_.id AS id_0, k1_.id AS id_1, k1_.name AS name_2, k1_.value AS value_3, k1_.visible AS visible_4, k0_.`user` AS user_5, k0_.activity_id AS activity_id_6, k0_.project_id AS project_id_7, k1_.timesheet_id AS timesheet_id_8 FROM kimai2_timesheet k0_ LEFT JOIN kimai2_timesheet_meta k1_ ON k0_.id = k1_.timesheet_id WHERE k0_.id IN (10) [] []
[Wed Nov 25 00:04:46.005353 2020] [php7:notice] [pid 133] [client 172.17.0.1:38922] [2020-11-25 00:04:46] doctrine.DEBUG: "COMMIT" [] []

Additional context

• Kimai version: 1.11.1 stable
• PHP version: 7.4.12
• Database: MySQL

[kevinpapst]

In that case I guess you need a quick fix to import the data?
Use master branch, edit this method https://github.com/kevinpapst/kimai2/blob/master/src/Timesheet/TimesheetService.php#L277
and add (untested):

        if (null === $timesheet->getEnd() && null !== ($duration = $timesheet->getDuration())) {
            $end = clone $timesheet->getBegin();
            $end->setTimestamp($end->getTimestamp() + $duration);
            $timesheet->setEnd($end);
        }

[ZeiP]

Thanks for the quick reply! This almost worked correctly. With this the duration of the time log is one minute more than the original duration. So should it just be

$end->setTimestamp($end->getTimestamp() + ($duration-1));

or is there a better way to fix that?

[kevinpapst]

Thats happens due to default rounding: deactivate rounding in "Settings" (set duration to 0).

[ZeiP]

So it does, thanks very much!

[kevinpapst]

Oh and if you could share your "Harvest importer" that would be an awesome addition for the community.
I guess it is more a quick & dirty solution, but still might be worth a section in the documentation.

[ZeiP]

I can definitely share the script. However it will be pretty slim, as I decided to create my projects and actions by hand due to the lousy structure I had created in Harvest, so it only imports the timesheets.

Where would you like me to share it once it's done?

[kevinpapst]

Nice, thanks 👍

You can add a section here at the end: https://github.com/kimai/www.kimai.org/blob/master/_documentation/imports.md

[ZeiP]

I got the import done with the quickfix you provided, thank you for that! The quickfix does, however, break at least Kemai (the desktop client), as it causes the timelog started by the application to be terminated at the same minute.

I'm wondering if the script is useful as it seems that an import could've also been done directly with the console, although it would've probably required a bit of extra formatting. I uploaded the script as a Gist so you or anyone else interested can take a look: https://gist.github.com/ZeiP/f693819d704c54a834a535f4913d7cac

[kevinpapst]

I don't know how Kemai works, maybe it submits 0 as duration for active records?

But you should remove the fix after the import anyway, it was just meant as a quick solution for your problem.
And you probably want to reactivate the rounding back to (at least) 1.

[ZeiP]

I was wondering the same, but actually it doesn't (if the debug output is correct):

[2020-11-25 17:17:54.458] [kemai] [debug] ===> [POST] https://time.example.com/api/timesheets {
    "activity": 18,
    "begin": "2020-11-25T17:17:54+02:00",
    "description": "Testi",
    "project": 1,
    "tags": ""
}

[2020-11-25 17:17:54.883] [kemai] [debug] <=== {"activity":18,"project":1,"user":1,"tags":[],"id":1329,"begin":"2020-11-25T17:17:54+0200","end":"2020-11-25T17:17:54+0200","duration":0,"description":"Testi","rate":0.0,"internalRate":0.0,"fixedRate":null,"hourlyRate":0.0,"exported":false,"metaFields":[]}

Yeah, I removed the fix anyway, but just thought to mention in case the same fix would be used to permanently fix the issue.

[kevinpapst]

Oh, duration is 0 by default. Missed that ... Sorry!

Edit: Thats why the check does not trigger with Kemai. Your import is fine, because you always submitted a duration.

[ZeiP]

Yep, this is only relevant for a possible permanent fix, the import seems to be all ok :)