Bug 28995 - Add --added_after to writeoff_debts.pl
Summary: Add --added_after to writeoff_debts.pl
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Martin Renvoize
QA Contact: Marcel de Rooy
URL:
Keywords:
Depends on: 28994
Blocks: 34077
  Show dependency treegraph
 
Reported: 2021-09-10 09:22 UTC by Magnus Enger
Modified: 2023-12-18 21:38 UTC (History)
8 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact: Caroline Cyr La Rose
Documentation submission: https://gitlab.com/koha-community/koha-manual/-/merge_requests/796
Text to go in the release notes:
Version(s) released in:
23.11.00,23.05.04


Attachments
Bug 28995: Add `--added_after` filter to writeoff_debts script (2.46 KB, patch)
2021-11-09 10:34 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 28995: Add `--added_after` filter to writeoff_debts script (2.46 KB, patch)
2021-11-15 09:35 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 28995: Update aliases (1.98 KB, patch)
2021-11-15 09:35 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 28995: Add `--added_after` filter to writeoff_debts script (2.53 KB, patch)
2023-06-21 14:06 UTC, Caroline Cyr La Rose
Details | Diff | Splinter Review
Bug 28995: Update aliases (2.05 KB, patch)
2023-06-21 14:06 UTC, Caroline Cyr La Rose
Details | Diff | Splinter Review
Bug 28995: Add `--added_after` filter to writeoff_debts script (2.62 KB, patch)
2023-09-08 09:46 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 28995: Update aliases (2.14 KB, patch)
2023-09-08 09:46 UTC, Marcel de Rooy
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Magnus Enger 2021-09-10 09:22:48 UTC
writeoff_debts.pl currently supports --added_before to make it work on fines added before a given date. It would be nice if we could also tell it to work on fines added after a given date, with --added_after.
Comment 1 Martin Renvoize 2021-11-09 10:34:09 UTC
Created attachment 127463 [details] [review]
Bug 28995: Add `--added_after` filter to writeoff_debts script

This adds, as requested, the `added_after` filter option to
writeoff_debts.
Comment 2 Jonathan Druart 2021-11-15 08:45:08 UTC
Options should be named --added-before and --added-after (_vs -)
Comment 3 Martin Renvoize 2021-11-15 09:35:53 UTC
Created attachment 127624 [details] [review]
Bug 28995: Add `--added_after` filter to writeoff_debts script

This adds, as requested, the `added_after` filter option to
writeoff_debts.
Comment 4 Martin Renvoize 2021-11-15 09:35:56 UTC
Created attachment 127625 [details] [review]
Bug 28995: Update aliases

Add an alias for added-before, added-after (Using an alias makes it
backwards compatible) and update the POD.
Comment 5 Martin Renvoize 2021-11-15 09:36:39 UTC
Fair.. follow-up added.. I kept it backward compatible with the _ notation as I believe there may be uses already int the wild.
Comment 6 Lucas Gass 2022-04-27 15:32:37 UTC
Martin, this still applies cleanly and seems to work for me. Do you have a test plan so I can sign it off?
Comment 7 Martin Renvoize 2022-04-27 15:50:43 UTC
Hmm, I was hoping Magnus would SO as he asked for the feature.. 

From memory, it would be to add some debts to a patron record.. adapt the `date` (i.e when the charge was added) field of those charges to a date in the past and then run this script with the --added_after option with a date after the date you just set and check the lines remain unchanged.. then run again passing a date before the date you added above and this time the lines should have been written off.
Comment 8 Caroline Cyr La Rose 2023-06-21 14:06:22 UTC
Created attachment 152520 [details] [review]
Bug 28995: Add `--added_after` filter to writeoff_debts script

This adds, as requested, the `added_after` filter option to
writeoff_debts.

Signed-off-by: Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com>
Comment 9 Caroline Cyr La Rose 2023-06-21 14:06:25 UTC
Created attachment 152521 [details] [review]
Bug 28995: Update aliases

Add an alias for added-before, added-after (Using an alias makes it
backwards compatible) and update the POD.

Signed-off-by: Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com>
Comment 10 Caroline Cyr La Rose 2023-06-21 14:06:57 UTC
I wrote what I did as a test plan, I hope it helps

1. Add two fees in a patron account
   1.1. Go into a patron record > Accounting tab
   1.2. Go to the Create a manual invoice tab
   1.3. Fill out the form
   1.4. Click 'Save'
   1.5. Redo steps 1.2 to 1.4 to add a second fee
   
2. Manually change the date in the database
   2.1. In the database, find the accountlines_id for the two fees
   
   Here's what I did
   
   select * from accountlines where borrowernumber = 21 and amountoutstanding > 0;
+-----------------+----------+----------------+------------+---------------------+-----------+-------------+------------------+-----------------+---------------+--------+--------------+-------------------+---------------------+------+------------+-------------+-----------+------------+
| accountlines_id | issue_id | borrowernumber | itemnumber | date                | amount    | description | credit_type_code | debit_type_code | credit_number | status | payment_type | amountoutstanding | timestamp           | note | manager_id | register_id | interface | branchcode |
+-----------------+----------+----------------+------------+---------------------+-----------+-------------+------------------+-----------------+---------------+--------+--------------+-------------------+---------------------+------+------------+-------------+-----------+------------+
|             652 |     NULL |             21 |       NULL | 2023-06-21 09:34:05 | 10.000000 | Lost item   | NULL             | LOST            | NULL          | NULL   | NULL         |         10.000000 | 2023-06-21 09:34:05 |      |         51 |        NULL | intranet  | CPL        |
|             653 |     NULL |             21 |       NULL | 2023-06-21 09:35:44 |  2.000000 | Lost item   | NULL             | LOST            | NULL          | NULL   | NULL         |          2.000000 | 2023-06-21 09:35:44 |      |         51 |        NULL | intranet  | CPL        |
+-----------------+----------+----------------+------------+---------------------+-----------+-------------+------------------+-----------------+---------------+--------+--------------+-------------------+---------------------+------+------------+-------------+-----------+------------+
2 rows in set (0.001 sec)

   select * from account_offsets where debit_id = '652';
+-----+-----------+----------+--------+-----------+---------------------+
| id  | credit_id | debit_id | type   | amount    | created_on          |
+-----+-----------+----------+--------+-----------+---------------------+
| 799 |      NULL |      652 | CREATE | 10.000000 | 2023-06-21 09:34:05 |
+-----+-----------+----------+--------+-----------+---------------------+
1 row in set (0.001 sec)

   2.2. Update the date for one of the fees using the accountlines_id
   
   Here's what I did
   
   update accountlines set date = '2023-06-01 09:34:05' where accountlines_id = 652;
   
   update account_offsets set created_on = '2023-06-01 09:34:05' where debit_id = '652';
   
   I now have one fee on June 1st and one fee on June 21st, the account_offset is also modified (not sure if that last part is necessary).
   
   select * from accountlines where borrowernumber = 21 and amountoutstanding > 0;
+-----------------+----------+----------------+------------+---------------------+-----------+-------------+------------------+-----------------+---------------+--------+--------------+-------------------+---------------------+------+------------+-------------+-----------+------------+
| accountlines_id | issue_id | borrowernumber | itemnumber | date                | amount    | description | credit_type_code | debit_type_code | credit_number | status | payment_type | amountoutstanding | timestamp           | note | manager_id | register_id | interface | branchcode |
+-----------------+----------+----------------+------------+---------------------+-----------+-------------+------------------+-----------------+---------------+--------+--------------+-------------------+---------------------+------+------------+-------------+-----------+------------+
|             652 |     NULL |             21 |       NULL | 2023-06-01 09:34:05 | 10.000000 | Lost item   | NULL             | LOST            | NULL          | NULL   | NULL         |         10.000000 | 2023-06-21 09:40:46 |      |         51 |        NULL | intranet  | CPL        |
|             653 |     NULL |             21 |       NULL | 2023-06-21 09:35:44 |  2.000000 | Lost item   | NULL             | LOST            | NULL          | NULL   | NULL         |          2.000000 | 2023-06-21 09:35:44 |      |         51 |        NULL | intranet  | CPL        |
+-----------------+----------+----------------+------------+---------------------+-----------+-------------+------------------+-----------------+---------------+--------+--------------+-------------------+---------------------+------+------------+-------------+-----------+------------+
2 rows in set (0.001 sec)

  select * from account_offsets where debit_id = '652';
+-----+-----------+----------+--------+-----------+---------------------+
| id  | credit_id | debit_id | type   | amount    | created_on          |
+-----+-----------+----------+--------+-----------+---------------------+
| 799 |      NULL |      652 | CREATE | 10.000000 | 2023-06-01 09:34:05 |
+-----+-----------+----------+--------+-----------+---------------------+
1 row in set (0.001 sec)

3. Run writeoff_debts.pl with --added_after with a date after the first fee but before the second
   3.1. In the terminal, run (for example)
   
   ./misc/cronjobs/writeoff_debts.pl --added_after 2023-06-20 --confirm
   
4. Check the patron record
   --> The fee with the date after should be written off
Comment 11 Marcel de Rooy 2023-09-08 09:40:48 UTC
Looking here
Comment 12 Marcel de Rooy 2023-09-08 09:46:27 UTC
Created attachment 155385 [details] [review]
Bug 28995: Add `--added_after` filter to writeoff_debts script

This adds, as requested, the `added_after` filter option to
writeoff_debts.

Signed-off-by: Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 13 Marcel de Rooy 2023-09-08 09:46:30 UTC
Created attachment 155386 [details] [review]
Bug 28995: Update aliases

Add an alias for added-before, added-after (Using an alias makes it
backwards compatible) and update the POD.

Signed-off-by: Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 14 Marcel de Rooy 2023-09-08 09:47:02 UTC
-    print " added before " . $added if $added;
+    print " added before " . $before if $before;
Nothing about after here? No blocker
Comment 15 Tomás Cohen Arazi 2023-09-08 14:54:03 UTC
Pushed to master for 23.11.

Nice work everyone, thanks!
Comment 16 Fridolin Somers 2023-09-08 19:51:04 UTC
Small nice enhancement. I think it is safe to backport.

Pushed to 23.05.x for 23.05.04
Comment 17 Matt Blenkinsop 2023-09-15 09:58:25 UTC
Enhancement - not backporting to 22.11.x

Nice work everyone!