[Bug 31787] New: [DOCS] Add description and examples to membership_expiry.pl about --where option
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Bug ID: 31787 Summary: [DOCS] Add description and examples to membership_expiry.pl about --where option Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Documentation Assignee: koha-bugs@lists.koha-community.org Reporter: david@davidnind.com QA Contact: testopia@bugs.koha-community.org An optional `--where` parameter was added to the `membership_expiry.pl` cronjob in 21.11. This allows for arbitrarily complex SQL where statements to be passed to the script to filter affected patrons. Add some additional information about this, and some examples on how to construct SQL queries, for example: to restrict to patrons in a specific category. See IRC discussion: http://irc.koha-community.org/koha/2022-10-14#i_2456049 See bug 28456. Topic affected: https://koha-community.org/manual/22.11/en/html/cron_jobs.html#notify-patron... -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=28456 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs documenting -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |david@davidnind.com |ity.org | Documentation| |David Nind contact| | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Anke Bruns <anke.bruns@gwdg.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |anke.bruns@gwdg.de --- Comment #1 from Anke Bruns <anke.bruns@gwdg.de> --- Filtering for categorycode with the where parameter doesn't work: koha-foreach /usr/share/koha/bin/cronjobs/membership_expiry.pl -n -c -v --where="categorycode='STUD'" results in no mails being sent even though there are expiring memberships within the time covered by the cronjob. We tried every combination of single and double quotes we could think of. We also tried the above command with "me.categorycode" instead of categorycode. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Marie-Luce Laflamme <marie-luce.laflamme@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |marie-luce.laflamme@inlibro | |.com --- Comment #2 from Marie-Luce Laflamme <marie-luce.laflamme@inlibro.com> --- I've also had a hard time testing the categorycode argument. However, David’s description had the answer in the IRC discussion link. We need to add a "me" before the categorycode, so it should look like this: --where="me.categorycode = 'CODE' " e.g. to run send email only for one category, try: ./misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="me.categorycode = 'S'" to exclude a category, try: ./misc/cronjobs/membership_expiry.pl -c -where="me.categorycode !='YA'" -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Marie-Luce Laflamme <marie-luce.laflamme@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|david@davidnind.com |marie-luce.laflamme@inlibro | |.com --- Comment #3 from Marie-Luce Laflamme <marie-luce.laflamme@inlibro.com> --- I've added the info in the manual - merge request pending -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 --- Comment #4 from Anke Bruns <anke.bruns@gwdg.de> --- Yes, we tried the "me" but it didn't help, either. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 --- Comment #5 from Marie-Luce Laflamme <marie-luce.laflamme@inlibro.com> --- The cron probably doesn't like the dot (.) in the argument. My colleague fix this issue by adding a "\" before and after the argument. For example, our cron to exclude some categories look like this: /misc/cronjobs/membership_expiry.pl -c -where=\"me.categorycode NOT IN ('EM','ADM','SU', 'TA')\" -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 --- Comment #6 from Marie-Luce Laflamme <marie-luce.laflamme@inlibro.com> --- ignore my previous comment 5. The main issue lies with the quotation marks (") in the argument. You need to escape them to ensure that the script is fully read and executed correctly, otherwise the script will only read the first quotation mark and think it ends there. For example, our cron to exclude some categories look like this: /misc/cronjobs/membership_expiry.pl -c -where=\"me.categorycode NOT IN ('EM','ADM','SU', 'TA')\" -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Jan Kissig <bibliothek@th-wildau.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |bibliothek@th-wildau.de --- Comment #7 from Jan Kissig <bibliothek@th-wildau.de> --- see https://chat.koha-community.org/koha-community/pl/a17z7r3zaig1ibqy1s74whhudh I tested a little, set one patron (categroycode S) to expire in 2 days, set MembershipExpiryDaysNotice in sysprefs to 2 days and the following call worked: sudo koha-foreach --chdir --enabled /kohadevbox//koha/misc/cronjobs/membership_expiry.pl -c -renew -where='me.categorycode=\"S\"' -v See the missing spaces between categorycode and the escaped S. Somehow the parameter -where gets cut after a space -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 --- Comment #8 from Jan Kissig <bibliothek@th-wildau.de> --- If someone can confirm my previous solution I would update the docs. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com --- Comment #9 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- The following all worked for me: perl misc/cronjobs/membership_expiry.pl -v -letter MEMBERSHIP_EXPIRY -c -where="me.categorycode = 's'" perl misc/cronjobs/membership_expiry.pl -v -letter MEMBERSHIP_EXPIRY -c -where="me.categorycode in ('s','pt')" perl misc/cronjobs/membership_expiry.pl -v -letter MEMBERSHIP_EXPIRY -c -where "me.categorycode in ('s','pt')" I haven't needed to escape any quotation marks, though it did error out when I made mistakes and my quotation marks didn't match up correctly. I guess I just forgot to include the "=" after "where" at first, but apparently it doesn't care? All of the above fail if I leave off the "me." before "categorycode." This also works: perl misc/cronjobs/membership_expiry.pl -v -letter MEMBERSHIP_EXPIRY -c -where 'borrowernumber in (select borrowernumber from borrower_attributes where code="show_bcode" and attribute=0)' No clear idea why that doesn't need a "me." It works with or without it. I ended up here because I was doing a search before filing a bug to create a clearer way to check attributes here. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=39595 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 --- Comment #10 from Jan Kissig <bibliothek@th-wildau.de> --- (In reply to Andrew Fuerste-Henry from comment #9) ...
I haven't needed to escape any quotation marks,
As I recall the quotation marks were only needed when using koha-foreach -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31787 --- Comment #11 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Jan Kissig from comment #10)
(In reply to Andrew Fuerste-Henry from comment #9) ...
I haven't needed to escape any quotation marks,
As I recall the quotation marks were only needed when using koha-foreach
I believe so too. What worked on command line stopped working as soon as it was added to the cronjob. Took a while to understand that. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org