Bug 28408

Summary: Last modification date for suggestions is wrong
Product: Koha Reporter: Nick Clemens <nick>
Component: AcquisitionsAssignee: Nick Clemens <nick>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: andrewfh, fridolin.somers, kyle, victor
Version: master   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
21.11.00,21.05.03,20.11.09
Bug Depends on: 23590    
Bug Blocks: 28855    
Attachments: Bug 28408: Add 'Last updated' column to suggestions table
Bug 28408: Add 'Last updated' column to suggestions table
Bug 28408: Add 'Last updated' column to suggestions table
Bug 28408: Add new column to the table settings config

Description Nick Clemens 2021-05-20 20:05:48 UTC
In the DB suggestions have a column: last_modification_date

`lastmodificationdate` date DEFAULT NULL COMMENT 'date of the last modification',

It is not updated when a suggestion is modified, however, the date field is:

`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'date and time the suggestion was updated',


On the interface we display the first as "Last modification on" - which is confusing if you update a suggestion, and the date doesn't change
Comment 1 Katrin Fischer 2021-05-24 11:20:55 UTC
Linking this to the introducing bug 23590.

The last_modification_date is linked to last_modified_by - so not all changes will udpate the date.

I am not sure to make this more clear, but comments on bug 23590 indicate it is the intended behaviour. See: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23590#c101
Comment 2 Nick Clemens 2021-05-28 13:09:05 UTC
Created attachment 121482 [details] [review]
Bug 28408: Add 'Last updated' column to suggestions table

We have specific meanings for the different dates in suggestions
Managed - means the status was changed or the suggestion edited, but doesn't change if item type updated
Modification - implies an edit of the subscript, not updated if status changed alone
Updated - this is the date field in the DB, which is actually a timestamp

The cronjob purge_suggestions.pl uses teh 'date' field

For a user, a suggestion can look old in the interface, but be considered recent by the script

To clear the confusion we should show the timestamp as well

To test:
1 - Add a suggestion
2 - In the DB, pretend you set it in the past:
    UPDATE suggestions SET manageddate='2021-01-01', suggesteddate='2021-01-01', date='2021-01-01 12:12:12';
3 - Click 'Go' In Organize by: Status
4 - Note the suggestion looks old now
5 - Check the box, and click 'Update item types with:' -> Submit
6 - Prganize by status again
7 - No change?
8 - Check the DB:
    SELECT date FROM suggestions;
9 - The field is updated
10 - Apply patch
11 - Organize by status again
12 - See the 'Last updated' ate set to today
Comment 3 David Nind 2021-06-04 21:30:57 UTC
Created attachment 121634 [details] [review]
Bug 28408: Add 'Last updated' column to suggestions table

We have specific meanings for the different dates in suggestions
Managed - means the status was changed or the suggestion edited, but doesn't change if item type updated
Modification - implies an edit of the subscript, not updated if status changed alone
Updated - this is the date field in the DB, which is actually a timestamp

The cronjob purge_suggestions.pl uses teh 'date' field

For a user, a suggestion can look old in the interface, but be considered recent by the script

To clear the confusion we should show the timestamp as well

To test:
1 - Add a suggestion
2 - In the DB, pretend you set it in the past:
    UPDATE suggestions SET manageddate='2021-01-01', suggesteddate='2021-01-01', date='2021-01-01 12:12:12';
3 - Click 'Go' In Organize by: Status
4 - Note the suggestion looks old now
5 - Check the box, and click 'Update item types with:' -> Submit
6 - Prganize by status again
7 - No change?
8 - Check the DB:
    SELECT date FROM suggestions;
9 - The field is updated
10 - Apply patch
11 - Organize by status again
12 - See the 'Last updated' ate set to today

Signed-off-by: David Nind <david@davidnind.com>
Comment 4 Kyle M Hall 2021-07-23 14:50:40 UTC
Created attachment 123136 [details] [review]
Bug 28408: Add 'Last updated' column to suggestions table

We have specific meanings for the different dates in suggestions
Managed - means the status was changed or the suggestion edited, but doesn't change if item type updated
Modification - implies an edit of the subscript, not updated if status changed alone
Updated - this is the date field in the DB, which is actually a timestamp

The cronjob purge_suggestions.pl uses teh 'date' field

For a user, a suggestion can look old in the interface, but be considered recent by the script

To clear the confusion we should show the timestamp as well

To test:
1 - Add a suggestion
2 - In the DB, pretend you set it in the past:
    UPDATE suggestions SET manageddate='2021-01-01', suggesteddate='2021-01-01', date='2021-01-01 12:12:12';
3 - Click 'Go' In Organize by: Status
4 - Note the suggestion looks old now
5 - Check the box, and click 'Update item types with:' -> Submit
6 - Prganize by status again
7 - No change?
8 - Check the DB:
    SELECT date FROM suggestions;
9 - The field is updated
10 - Apply patch
11 - Organize by status again
12 - See the 'Last updated' ate set to today

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 5 Katrin Fischer 2021-07-24 11:39:03 UTC
Should the bug title be updated a bit to make the issue/solution a bit clearer?
Comment 6 Jonathan Druart 2021-07-26 09:02:07 UTC
Created attachment 123179 [details] [review]
Bug 28408: Add new column to the table settings config
Comment 7 Jonathan Druart 2021-07-26 09:23:59 UTC
Pushed to master for 21.11, thanks to everybody involved!
Comment 8 Kyle M Hall 2021-07-30 14:30:03 UTC
Pushed to 21.05.x for 21.05.03
Comment 9 Fridolin Somers 2021-08-03 04:45:35 UTC
Pushed to 20.11.x for 20.11.09
Comment 10 Victor Grousset/tuxayo 2021-08-16 18:59:12 UTC
Not backported to oldoldstable (20.05.x). Feel free to ask if it's needed.