Bug 28456 - Add option to use a WHERE statement in membership_expiry.pl cronjob
Summary: Add option to use a WHERE statement in membership_expiry.pl cronjob
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Nick Clemens
QA Contact: Marcel de Rooy
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-05-25 18:46 UTC by Nick Clemens
Modified: 2022-12-19 10:30 UTC (History)
6 users (show)

See Also:
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 (4.03 KB, patch)
2021-05-25 18:54 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28456: Add WHERE option to membership_expiry cronjob (4.09 KB, patch)
2021-06-14 18:29 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28456: Add WHERE option to membership_expiry cronjob (4.22 KB, patch)
2021-08-27 07:12 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 Nick Clemens 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 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 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.