Bug 21214 - cleanup_database.pl --mail should let admin choose which letter codes to keep
Summary: cleanup_database.pl --mail should let admin choose which letter codes to keep
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Ivan Dziuba
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-08-13 15:03 UTC by Charles Farmer
Modified: 2024-02-12 17:13 UTC (History)
10 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
BZ 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code (4.07 KB, patch)
2018-08-13 15:06 UTC, Charles Farmer
Details | Diff | Splinter Review
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code (4.07 KB, patch)
2018-08-13 17:53 UTC, Charles Farmer
Details | Diff | Splinter Review
Bug 21214: I changed cleanup_database.pl and some corrections with --keep-lc to dodge deletion in message_queue based on letter_code (2.99 KB, patch)
2020-11-04 13:41 UTC, Ivan Dziuba
Details | Diff | Splinter Review
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code (4.56 KB, patch)
2020-11-04 13:49 UTC, Ivan Dziuba
Details | Diff | Splinter Review
Bug 21214: I changed cleanup_database.pl and some corrections with --keep-lc to dodge deletion in message_queue based on letter_code (2.99 KB, patch)
2020-11-04 13:49 UTC, Ivan Dziuba
Details | Diff | Splinter Review
Bug_21214: Changes after merge (2.10 KB, patch)
2020-11-04 15:00 UTC, Ivan Dziuba
Details | Diff | Splinter Review
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code (4.44 KB, patch)
2020-11-04 15:35 UTC, Ivan Dziuba
Details | Diff | Splinter Review
Bug_21214: Change of SQL request in condition $mail with letter_code and code modification (1.78 KB, patch)
2020-11-04 15:36 UTC, Ivan Dziuba
Details | Diff | Splinter Review
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code (4.53 KB, patch)
2023-03-23 20:34 UTC, Hammat wele
Details | Diff | Splinter Review
Bug_21214: Change of SQL request in condition $mail with letter_code and code modification (1.77 KB, patch)
2023-03-23 20:34 UTC, Hammat wele
Details | Diff | Splinter Review
Bug 21214: (fix) Change letter_code with letter_id in the sql query and code modification (3.83 KB, patch)
2023-03-23 21:14 UTC, Hammat wele
Details | Diff | Splinter Review
Bug 21214: (fix) Rebuild the sql query (2.42 KB, patch)
2023-03-23 22:31 UTC, Hammat wele
Details | Diff | Splinter Review
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code (4.58 KB, patch)
2023-11-08 22:06 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug_21214: Change of SQL request in condition $mail with letter_code and code modification (1.82 KB, patch)
2023-11-08 22:06 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug 21214: (fix) Rebuild the sql query (2.47 KB, patch)
2023-11-08 22:06 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug 21214: cleanup_database.pl rebase to master (4.30 KB, patch)
2024-02-12 17:10 UTC, Matthias Le Gac
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Charles Farmer 2018-08-13 15:03:00 UTC
We are often asked if an email was sent to a patron, and despite not having 100% confirmation that the message reached the person, it feels bad when fines are attached to it and no information's left in the database for our librarians.

A new patch would let certain messages stay in the database, despite their age, based on their letter_code.
Comment 1 Charles Farmer 2018-08-13 15:06:20 UTC
Created attachment 77756 [details] [review]
BZ 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code

Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database.
This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script.

TEST PLAN:
1) Either you have already have data, or you'll want to add some new test lines in your message_queue table
    . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued)
      VALUES(1,<YOUR CODE OF CHOICE>,'email','sent','2018-01-01 00:00:42');
2) Make a backup of this table
3) Run the script patchless with the --mail 1 option
    3.1) Messages older than 1 day should have been deleted
4) Put the data back in
5) Apply patch
6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc <CODE1>[,<CODE2>]
7) Messages older than 1 day that don't have the code <CODE1> [or <CODE2>] should've been deleted
Comment 2 Charles Farmer 2018-08-13 17:53:10 UTC
Created attachment 77762 [details] [review]
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code

Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database.
This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script.

TEST PLAN:
1) Either you have already have data, or you'll want to add some new test lines in your message_queue table
    . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued)
      VALUES(1,<YOUR CODE OF CHOICE>,'email','sent','2018-01-01 00:00:42');
2) Make a backup of this table
3) Run the script patchless with the --mail 1 option
    3.1) Messages older than 1 day should have been deleted
4) Put the data back in
5) Apply patch
6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc <CODE1>[,<CODE2>]
7) Messages older than 1 day that don't have the code <CODE1> [or <CODE2>] should've been deleted
Comment 3 Katrin Fischer 2018-12-20 21:28:46 UTC
Hi Charles, please don't forget to assign the bug to yourself when providing a patch!
Comment 4 Bernardo Gonzalez Kriegel 2020-03-05 23:55:48 UTC
Hi, there is a problem in the code

->  243	    my $quoted_codes = '';
    244	    if ($letter_codes) {
    245	        my @codes = map { "'$_'" } split(',', $letter_codes);
->  246	        my $quoted_codes = join(',', @codes);
    247	    }

Two "my $quoted_codes", in lines 243 and 246.
$quoted_codes passed to execute will always be empty.
Remove the second 'my'
Comment 5 Ivan Dziuba 2020-11-04 13:41:59 UTC Comment hidden (obsolete)
Comment 6 Ivan Dziuba 2020-11-04 13:49:39 UTC Comment hidden (obsolete)
Comment 7 Ivan Dziuba 2020-11-04 13:49:42 UTC Comment hidden (obsolete)
Comment 8 Ivan Dziuba 2020-11-04 15:00:01 UTC Comment hidden (obsolete)
Comment 9 Ivan Dziuba 2020-11-04 15:35:59 UTC
Created attachment 113008 [details] [review]
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code

Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database.
This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script.

TEST PLAN:
1) Either you have already have data, or you'll want to add some new test lines in your message_queue table
    . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued)
      VALUES(1,<YOUR CODE OF CHOICE>,'email','sent','2018-01-01 00:00:42');
2) Make a backup of this table
3) Run the script patchless with the --mail 1 option
    3.1) Messages older than 1 day should have been deleted
4) Put the data back in
5) Apply patch
6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc <CODE1>[,<CODE2>]
7) Messages older than 1 day that don't have the code <CODE1> [or <CODE2>] should've been deleted
Comment 10 Ivan Dziuba 2020-11-04 15:36:02 UTC
Created attachment 113009 [details] [review]
Bug_21214: Change of SQL request in condition $mail with letter_code and code modification
Comment 11 Hammat wele 2023-03-23 20:34:31 UTC
Created attachment 148622 [details] [review]
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code

Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database.
This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script.

TEST PLAN:
1) Either you have already have data, or you'll want to add some new test lines in your message_queue table
    . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued)
      VALUES(1,<YOUR CODE OF CHOICE>,'email','sent','2018-01-01 00:00:42');
2) Make a backup of this table
3) Run the script patchless with the --mail 1 option
    3.1) Messages older than 1 day should have been deleted
4) Put the data back in
5) Apply patch
6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc <CODE1>[,<CODE2>]
7) Messages older than 1 day that don't have the code <CODE1> [or <CODE2>] should've been deleted
Comment 12 Hammat wele 2023-03-23 20:34:35 UTC
Created attachment 148623 [details] [review]
Bug_21214: Change of SQL request in condition $mail with letter_code and code modification
Comment 13 Hammat wele 2023-03-23 20:35:05 UTC
Rebase
Comment 14 Hammat wele 2023-03-23 21:14:29 UTC Comment hidden (obsolete)
Comment 15 Hammat wele 2023-03-23 22:31:19 UTC
Created attachment 148625 [details] [review]
Bug 21214: (fix) Rebuild the sql query
Comment 16 Lucas Gass 2023-11-08 22:06:48 UTC
Created attachment 158679 [details] [review]
Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code

Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database.
This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script.

TEST PLAN:
1) Either you have already have data, or you'll want to add some new test lines in your message_queue table
    . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued)
      VALUES(1,<YOUR CODE OF CHOICE>,'email','sent','2018-01-01 00:00:42');
2) Make a backup of this table
3) Run the script patchless with the --mail 1 option
    3.1) Messages older than 1 day should have been deleted
4) Put the data back in
5) Apply patch
6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc <CODE1>[,<CODE2>]
7) Messages older than 1 day that don't have the code <CODE1> [or <CODE2>] should've been deleted

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 17 Lucas Gass 2023-11-08 22:06:51 UTC
Created attachment 158680 [details] [review]
Bug_21214: Change of SQL request in condition $mail with letter_code and code modification

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 18 Lucas Gass 2023-11-08 22:06:54 UTC
Created attachment 158681 [details] [review]
Bug 21214: (fix) Rebuild the sql query

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 19 Lucas Gass 2023-11-08 22:07:11 UTC
Works as described.
Comment 20 Matthias Le Gac 2024-02-12 17:10:46 UTC
Created attachment 162064 [details] [review]
Bug 21214: cleanup_database.pl rebase to master

Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database.
This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script.

TEST PLAN:
1) Either you have already have data, or you'll want to add some new test lines in your message_queue table
    . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued)
      VALUES(1,<YOUR CODE OF CHOICE>,'email','sent','2018-01-01 00:00:42');
2) Make a backup of this table
3) Run the script patchless with the --mail 1 option
    3.1) Messages older than 1 day should have been deleted
4) Put the data back in
5) Apply patch
6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc <CODE1>[,<CODE2>]
7) Messages older than 1 day that don't have the code <CODE1> [or <CODE2>] should've been deleted
Comment 21 Matthias Le Gac 2024-02-12 17:13:53 UTC
Rebase to master