Bug 11530 - Recalculating an item's total issue count with update_totalissues.pl ignores deleted items
Summary: Recalculating an item's total issue count with update_totalissues.pl ignores ...
Status: Failed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Nick Clemens (kidclamp)
QA Contact: Testopia
URL:
Keywords: no-sandbox
Depends on:
Blocks:
 
Reported: 2014-01-13 15:14 UTC by Olli-Antti Kivilahti
Modified: 2025-02-05 04:54 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This enhancement to the misc/cronjobs/update_totalissues.pl script[1] adds a new --use-deleted-items option. With this option, a records' usage count will now include deleted items. [1] This script updates the total issues usage count for a record. For MARC21, this is recorded in 942$o - Koha issues (borrowed), all copies.
Version(s) released in:
Circulation function:


Attachments
Bug 11530: Add a use-deleted-items option to totalissues.pl (3.25 KB, patch)
2024-12-21 13:00 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 11530: Fix warning of undefined value (740 bytes, patch)
2024-12-21 13:00 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 11530: Add a use-deleted-items option to totalissues.pl (3.30 KB, patch)
2025-01-09 20:07 UTC, David Nind
Details | Diff | Splinter Review
Bug 11530: Fix warning of undefined value (787 bytes, patch)
2025-01-09 20:07 UTC, David Nind
Details | Diff | Splinter Review
Bug 11530: (QA follow-up): fix terminology (3.13 KB, patch)
2025-02-05 04:54 UTC, Victor Grousset/tuxayo
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Olli-Antti Kivilahti 2014-01-13 15:14:07 UTC
--------------------------
- REPLICATING THIS ISSUE -
--------------------------

If we had a biblio, with 4 items, each being issued 50 times.
Running the update_totalissues.pl in stats or items -mode would yield a biblioitems.totalissues value of 200.

If we get rid of one of the books, and recalculate, the biblioitems.totalissues value will drop to 150!

update_totalissues.pl works in incremental mode, but a recalculation will ignore deleted items.
Comment 1 Fridolin Somers 2020-01-28 08:50:37 UTC
Since statistics contains only itemnumber, once item deleted we can not link this itemnumber to its biblionumber.
Comment 2 Katrin Fischer 2020-01-28 20:22:09 UTC
(In reply to Fridolin SOMERS from comment #1)
> Since statistics contains only itemnumber, once item deleted we can not link
> this itemnumber to its biblionumber.

What we do is use a union of items and deleteditems in most reports - then you can.
Comment 3 Nick Clemens (kidclamp) 2024-12-21 13:00:21 UTC
Created attachment 175880 [details] [review]
Bug 11530: Add a use-deleted-items option to totalissues.pl

Some libraries would like to keep the usage from deleted items when calculating
total issues from the items.

To test:
0 - Apply patch
1 - Checkout/Check in an item several times
2 - perl misc/cronjobs/update_totalissues.pl --use-items
3 - Note the 942$o in the record has the correct count
4 - Delete the item
5 - perl misc/cronjobs/update_totalissues.pl --use-items
6 - Note record count now goes to 0
8 - perl misc/cronjobs/update_totalissues.pl --use-items --use-deleted-items
9 - Note record count correctly restored
Comment 4 Nick Clemens (kidclamp) 2024-12-21 13:00:23 UTC
Created attachment 175881 [details] [review]
Bug 11530: Fix warning of undefined value

We always check the return of UpdateTotalIssues, we shouldn't let it return null
Comment 5 Lucas Gass (lukeg) 2025-01-02 19:10:48 UTC
This is a nice fix that I know many libraries will appreciate.
Comment 6 David Nind 2025-01-09 20:07:33 UTC
Created attachment 176300 [details] [review]
Bug 11530: Add a use-deleted-items option to totalissues.pl

Some libraries would like to keep the usage from deleted items when calculating
total issues from the items.

To test:
0 - Apply patch
1 - Checkout/Check in an item several times
2 - perl misc/cronjobs/update_totalissues.pl --use-items
3 - Note the 942$o in the record has the correct count
4 - Delete the item
5 - perl misc/cronjobs/update_totalissues.pl --use-items
6 - Note record count now goes to 0
8 - perl misc/cronjobs/update_totalissues.pl --use-items --use-deleted-items
9 - Note record count correctly restored

Signed-off-by: David Nind <david@davidnind.com>
Comment 7 David Nind 2025-01-09 20:07:35 UTC
Created attachment 176301 [details] [review]
Bug 11530: Fix warning of undefined value

We always check the return of UpdateTotalIssues, we shouldn't let it return null

Signed-off-by: David Nind <david@davidnind.com>
Comment 8 Victor Grousset/tuxayo 2025-02-05 03:21:51 UTC
Comment on attachment 176300 [details] [review]
Bug 11530: Add a use-deleted-items option to totalissues.pl

Review of attachment 176300 [details] [review]:
-----------------------------------------------------------------

::: misc/cronjobs/update_totalissues.pl
@@ +113,5 @@
>  sub process_items {
> +    my $query =
> +        $include_deleted
> +        ? "SELECT all_items.biblionumber, SUM(all_items.issues) FROM (SELECT items.biblionumber,items.issues FROM items UNION ALL SELECT deleteditems.biblionumber, deleteditems.issues FROM deleteditems) all_items GROUP BY all_items.biblionumber;"
> +        : "SELECT items.biblionumber, SUM(items.issues) FROM items GROUP BY items.biblionumber;";

Just a quick glance at code when passing by: some formatting of the SQL is needed:
https://wiki.koha-community.org/wiki/Coding_Guidelines#SQL9:_SELECT
Comment 9 Victor Grousset/tuxayo 2025-02-05 04:54:07 UTC
Created attachment 177523 [details] [review]
Bug 11530: (QA follow-up): fix terminology

Even if the script itself has the wrong terminology (issue vs checkout).
The POD and help can have the right one.