Bug 27049

Summary: Add a script to bulk writeoff debts
Product: Koha Reporter: Martin Renvoize <martin.renvoize>
Component: Command-line UtilitiesAssignee: Martin Renvoize <martin.renvoize>
Status: CLOSED FIXED QA Contact: Kyle M Hall <kyle>
Severity: enhancement    
Priority: P5 - low CC: andrewfh, david.roberts, fridolin.somers, ian.bays, jonathan.druart, katrin.fischer, kyle, lucas, m.de.rooy, marjorie.barry-vila, robin
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
This new script can be scheduled to writeoff user debts prior to the `delete_patrons.pl` script attempting to remove them. Example: ./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
Version(s) released in:
21.05.00
Bug Depends on:    
Bug Blocks: 34077, 34078, 35596, 28994, 35074    
Attachments: Bug 27049: Add bulkwaiveaccounts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl
Bug 27049: (QA follow-up) Add short params, replace warn with say

Description Martin Renvoize 2020-11-18 12:47:03 UTC
With extended periods of libraries being closed and the recent complexities introduced into the accounts system for properly tracking accountline writeoffs, it would be helpful to have a command line too allowing administrators to writeoff accounts in bulk using the proper routines within Koha to properly record the action.
Comment 1 Martin Renvoize 2020-11-18 14:00:32 UTC
Created attachment 113785 [details] [review]
Bug 27049: Add bulkwaiveaccounts.pl

This patch adds a new misc/bulkwaiveaccounts.pl script to allow the bulk
waiver of debts from the system. The script is passed a file of line
delimited accountline_ids and will apply a WRITEOFF account against them
for the amount of the debt.
Comment 2 Martin Renvoize 2020-11-18 14:02:23 UTC
This script has been kept deliberately simple for now to allow easy backport.. I would like to further enhance it to allow the use of different credit types (CANCEL vs WRITEOFF) and perhaps allow partial writeoffs and discounts in the future... but I intend to do that work as followups on new bugs.
Comment 3 Lucas Gass 2020-12-09 23:20:15 UTC
Martin,

This seems pretty handy! Is there a test plan I can follow?
Comment 4 Martin Renvoize 2021-01-04 16:40:39 UTC
Created attachment 114812 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.
Comment 5 Lucas Gass 2021-01-13 18:39:39 UTC
Martin,

When I run the script I get the following error and no debts are written off:

Can't locate object method "search" via package "Koha::Account::Lines" (perhaps you forgot to load "Koha::Account::Lines"?) at /kohadevbox/koha/misc/cronjobs/writeoff_debts.pl line 49.


If I add 'use Koha::Account::Lines;' to the script and restart_all I can make it work. Is this missing or am I doing something wrong?

I am running this in my Koha testing docker like so:


perl /kohadevbox/koha/misc/cronjobs/writeoff_debts.pl --confirm
Comment 6 Martin Renvoize 2021-01-14 12:53:52 UTC
Created attachment 115155 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Examples:
./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
./writeoff_debts.pl --type COPY --verbose --confirm
./writeoff_debts.pl --file path/to/file --verbose

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.
Comment 7 Martin Renvoize 2021-01-14 12:55:32 UTC
Ooops, seems the latest version of the script was not attached.. :(.

I've refreshed it now and slightly improved the commit message with some examples of how to call the script.
Comment 8 Martin Renvoize 2021-01-14 12:58:41 UTC
So, the idea here in most cases now is that it could be used via the CRON to allow for regular cleanup/writeoff of debts after a certain period of time has passed (allowing for subsequent cleanup of borrower via /misc/cronjobs/delete_patrons.pl and /misc/cronjobs/cleanup_database.pl).

So, for example.. if you want to automatically writeoff unpaid overdues when they've been dormant for 18 months.. you could add the following to the corn.

./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --type OVERDUE --confirm
Comment 9 Lucas Gass 2021-01-14 22:21:41 UTC
The --type parameter should take an argument that matches what is found in accountlines.debit_type_code? 


These do not write off for me:

./writeoff_debts.pl --type LOST --verbose --confirm 
./writeoff_debts.pl --type OVERDUE --verbose --confirm 


But this will write them all off:

./writeoff_debts.pl --verbose --confirm


As soon as I attempt to use --type, I am unable to write off anything. 


Probably I'm just doing something wrong.
Comment 10 Martin Renvoize 2021-01-15 09:47:07 UTC
Interesting.. I'll have a dig my end and report back.
Comment 11 Martin Renvoize 2021-01-15 09:53:21 UTC
Oh man, that's an embarassing typo... I'd put the selection against 'credit_type_code' instead of 'debit_type_code'... of course, that's never going to work!
Comment 12 Martin Renvoize 2021-01-15 09:59:20 UTC
Created attachment 115186 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Examples:
./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
./writeoff_debts.pl --type COPY --verbose --confirm
./writeoff_debts.pl --file path/to/file --verbose

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.
Comment 13 Martin Renvoize 2021-01-15 10:04:15 UTC
Created attachment 115187 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Examples:
./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
./writeoff_debts.pl --type COPY --verbose --confirm
./writeoff_debts.pl --file path/to/file --verbose

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.
Comment 14 Martin Renvoize 2021-01-15 10:10:45 UTC
Created attachment 115188 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Examples:
./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
./writeoff_debts.pl --type COPY --verbose --confirm
./writeoff_debts.pl --file path/to/file --verbose

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 15 Martin Renvoize 2021-01-15 10:15:01 UTC
OK, that final upload fixes the credit/debt filter, catches a bug in the --file handling option and documents the --verbose option better as well as adding a little more feedback with vebose.

Sorry Lucas, not my proudest bug.. must have been overly tired when working through it first time.

Thanks for persevering.
Comment 16 Lucas Gass 2021-01-15 15:44:38 UTC
Created attachment 115208 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Examples:
./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
./writeoff_debts.pl --type COPY --verbose --confirm
./writeoff_debts.pl --file path/to/file --verbose

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 17 Kyle M Hall 2021-04-09 15:45:31 UTC
Created attachment 119399 [details] [review]
Bug 27049: Add /misc/cronjobs/writeoff_debts.pl

This patch adds a new misc/cronjobs/writeoff_debts.pl script to allow the bulk
waiver of debts from the system. The script accepts some filter parameters,
including the option to pass a line delimited file of accountline_ids, and will
apply a WRITEOFF account against them for the amount of the outstanding debt.

Examples:
./writeoff_debts.pl --added_before $(date -d '-18 month' --iso-8601) --confirm
./writeoff_debts.pl --type COPY --verbose --confirm
./writeoff_debts.pl --file path/to/file --verbose

Test plan
1/ Add some debts to the system for various users.
2/ Output a line delimited report for accountlines for those debts.
3/ Run the script with the --file parameter and confirm those debts were
written off.
4/ Repeat steps 1-3 above but add in a step to partially pay some debts
prior to running the script.
5/ Repeat steps 1-3 above but pay of some of the debts prior to running
the script.
6/ Repeat steps 1-2 above, but instead of passing --file use a
combination of the other parameters to limit your list of debts to
writeoff.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 18 Kyle M Hall 2021-04-09 15:45:47 UTC
Created attachment 119400 [details] [review]
Bug 27049: (QA follow-up) Add short params, replace warn with say

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 19 Martin Renvoize 2021-04-12 07:55:58 UTC
Thanks for the follow-up, nice improvement.
Comment 20 Jonathan Druart 2021-04-12 13:32:29 UTC
Pushed to master for 21.05, thanks to everybody involved!
Comment 21 Fridolin Somers 2021-04-19 12:46:07 UTC
Do you think it can be backported to 20.11.x ?
Comment 22 Jonathan Druart 2021-05-19 13:12:57 UTC
Release notes needed for 21.05 please.
Comment 23 Marcel de Rooy 2021-07-06 12:16:56 UTC
Should this script have been placed in another folder than cronjobs ?
Comment 24 Martin Renvoize 2021-07-06 12:22:50 UTC
(In reply to Marcel de Rooy from comment #23)
> Should this script have been placed in another folder than cronjobs ?

Well.. I was envisaging it used regularly so cronjobs made sense to me.. it goes hand in hand with cleanup database.