Bug 28456

Summary: Add option to use a WHERE statement in membership_expiry.pl cronjob
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: Command-line UtilitiesAssignee: Nick Clemens (kidclamp) <nick>
Status: CLOSED FIXED QA Contact: Marcel de Rooy <m.de.rooy>
Severity: enhancement    
Priority: P5 - low CC: dcook, kyle, m.de.rooy, martin.renvoize, robin, susan.merner
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26062
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Add an optional `--where` parameter to the `membership_expiry.pl` task. This allows for arbitrarily complex SQL where statements to be passed to the script to filter affected patrons.
Version(s) released in:
21.11.00
Attachments: Bug 28456: Add WHERE option to membership_expiry cronjob
Bug 28456: Add WHERE option to membership_expiry cronjob
Bug 28456: Add WHERE option to membership_expiry cronjob

Description Nick Clemens (kidclamp) 2021-05-25 18:46:18 UTC
Some libraries have a large amount of accounts and want to be able to limit how many email reminders they send - adding a where statement will allow for flexibility
Comment 1 Nick Clemens (kidclamp) 2021-05-25 18:54:08 UTC
Created attachment 121397 [details] [review]
Bug 28456: Add WHERE option to membership_expiry cronjob

Some libraries have a large amount of accounts and want to be able to limit how many email reminders they send.
Adding a where statement will allow for flexibility

To test:
1 - Set some borrowers to expire soon
2 - Set MembershipExpiryDaysNotice to 10
3 - perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100
4 - Note the patrons that yo uadjusted show up
5 - Limit by various patron fields, e.g.
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="lastseen IS NULL"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="lastseen IS NOT NULL"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="surname LIKE '%a%'"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="surname NOT LIKE '%a%'"
6 - Confirm expected results
7 - perl misc/cronjobs/membership_expiry.pl
8 - Confirm the help message makes sense
Comment 2 Nick Clemens (kidclamp) 2021-06-14 18:29:08 UTC
Created attachment 121922 [details] [review]
Bug 28456: Add WHERE option to membership_expiry cronjob

Some libraries have a large amount of accounts and want to be able to limit how many email reminders they send.
Adding a where statement will allow for flexibility

To test:
1 - Set some borrowers to expire soon
2 - Set MembershipExpiryDaysNotice to 10
3 - perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100
4 - Note the patrons that yo uadjusted show up
5 - Limit by various patron fields, e.g.
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="lastseen IS NULL"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="lastseen IS NOT NULL"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="surname LIKE '%a%'"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="surname NOT LIKE '%a%'"
6 - Confirm expected results
7 - perl misc/cronjobs/membership_expiry.pl
8 - Confirm the help message makes sense

Signed-off-by: Azucena Aguayo <azucena.aguayo@gmail.com>
Comment 3 Marcel de Rooy 2021-08-27 07:12:34 UTC
Created attachment 124133 [details] [review]
Bug 28456: Add WHERE option to membership_expiry cronjob

Some libraries have a large amount of accounts and want to be able to limit how many email reminders they send.
Adding a where statement will allow for flexibility

To test:
1 - Set some borrowers to expire soon
2 - Set MembershipExpiryDaysNotice to 10
3 - perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100
4 - Note the patrons that yo uadjusted show up
5 - Limit by various patron fields, e.g.
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="lastseen IS NULL"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="lastseen IS NOT NULL"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="surname LIKE '%a%'"
    perl misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="surname NOT LIKE '%a%'"
6 - Confirm expected results
7 - perl misc/cronjobs/membership_expiry.pl
8 - Confirm the help message makes sense

Signed-off-by: Azucena Aguayo <azucena.aguayo@gmail.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
[EDIT] Replace two inclue by include.
Comment 4 Marcel de Rooy 2021-08-27 07:14:12 UTC
Note: There is no checking on the $where values. Passing invalid columns will crash the script etc. No blocker to me for this cron job.
Comment 5 Jonathan Druart 2021-08-30 15:11:40 UTC
Pushed to master for 21.11, thanks to everybody involved!
Comment 6 Susan 2022-12-19 10:30:42 UTC
Comment on attachment 124133 [details] [review]
Bug 28456: Add WHERE option to membership_expiry cronjob

I would like to be able to ensure students who are on short placement of 3 weeks do not get the expiry email at all. Excluding a specific university (UMED) would assist.