Hello all, I am working on some SQL to get a report for last month's acquisitions, and worked up the following code to do it: SELECT MONTH(CURDATE()) INTO @curmonth; SELECT MONTH(CURDATE())-1 INTO @lastmonth; SELECT YEAR(CURDATE()) INTO @reportyear; SELECT YEAR(CURDATE())-1 INTO @lastmnthyear; SELECT IF (@curmonth=1, 12, @lastmonth) into @lastmonth; SELECT IF (@curmonth=1, @lastmnthyear, @reportyear) into @reportyear; SELECT CONCAT(@reportyear,"-",@lastmonth,"-01") into @begindate; SELECT LAST_DAY(@begindate) into @enddate; SELECT EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') as "Title:", EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') as "statement of responsibility", EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="b"]') as Publisher, EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="c"]') as Date, EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="a"]') as "Call Number first part", EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="b"]') as "Call Number second part", items.dateaccessioned as "Date Accessioned" FROM biblioitems LEFT JOIN items on (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE items.dateaccessioned BETWEEN @begindate and @enddate ORDER BY items.dateaccessioned asc If I put this in a text file on the server, and call it using mysql it runs fine. When I put it into an SQL report on the staff client it fails with this error: "Can't use an undefined value as an ARRAY reference at /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 766" Looking at that line in guided_reports.pl it is where the column headings are being loaded into an array. The problem definitely lies in the first half of the code where I am loading date information into variables to build the begin and end dates. If that code is removed and actual dates entered for the BETWEEN values the report runs fine in the staff client. If the answer here is that it isn't going to run in the staff client, I can set it up to run on the server and create a cron job to automate it. If that is the case should I open a bug for this, or am I getting outside of the scope of the guided reports tool to begin with? Koha 3.10.0 on Debian Squeeze . Thanks, Doug Dearden Director, IT School for Advanced Research sarweb.org
Hi, On Wed, Mar 20, 2013 at 8:50 AM, Doug Dearden <dearden@sarsf.org> wrote:
The problem definitely lies in the first half of the code where I am loading date information into variables to build the begin and end dates. If that code is removed and actual dates entered for the BETWEEN values the report runs fine in the staff client.
The guided reports module has support for placeholders, detailed here: http://manual.koha-community.org/3.10/en/reportsfaq.html#runtimefaq In your specific example, if you were always basing the criteria on the current day, you could likely construct expressions for the WHERE clause that had the desired effect without needing variables or parameters.
If the answer here is that it isn't going to run in the staff client, I can set it up to run on the server and create a cron job to automate it. If that is the case should I open a bug for this, or am I getting outside of the scope of the guided reports tool to begin with?
It's pushing the scope of the guided reports tool, since one of its design points is attempting to prevent the user from constructing SQL statements that could update, delete, or otherwise damage the database. Multi-line statements would be harder to evaluate. Regards, Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
Doug: You wrote:
I am working on some SQL to get a report for last month's acquisitions, and worked up the following code to do it:
[...snip...] A couple of the SQL Reports posted on the wiki have a WHERE clause that purportedly limits to the previous month. Would something like the following work? SELECT EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') as "Title:", EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') as "statement of responsibility", EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="b"]') as Publisher, EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="c"]') as Date, EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="a"]') as "Call Number first part", EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="b"]') as "Call Number second part", items.dateaccessioned as "Date Accessioned" FROM biblioitems LEFT JOIN items on (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) ORDER BY items.dateaccessioned asc; --Robert ********************************************* Robert L. Williams Open Source Services Manager Amigos Library Services, Inc. 14400 Midway Road Dallas, TX 75244-3509 800-843-8482, x2870 972-340-2870 (direct) 972-991-6061 (fax)
Thanks Robert, that did it. A much more elegant solution than mine also. I checked the wiki before I started this but didn't find that report. Best - Doug -----Original Message----- From: koha-bounces@lists.katipo.co.nz [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Robert Williams Sent: Wednesday, March 20, 2013 10:19 AM To: koha@lists.katipo.co.nz Subject: Re: [Koha] SQL runs outside of staff client but not inside it Doug: You wrote:
I am working on some SQL to get a report for last month's acquisitions, and worked up the following code to do it:
[...snip...] A couple of the SQL Reports posted on the wiki have a WHERE clause that purportedly limits to the previous month. Would something like the following work? SELECT EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') as "Title:", EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') as "statement of responsibility", EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="b"]') as Publisher, EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="c"]') as Date, EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="a"]') as "Call Number first part", EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="b"]') as "Call Number second part", items.dateaccessioned as "Date Accessioned" FROM biblioitems LEFT JOIN items on (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) ORDER BY items.dateaccessioned asc; --Robert ********************************************* Robert L. Williams Open Source Services Manager Amigos Library Services, Inc. 14400 Midway Road Dallas, TX 75244-3509 800-843-8482, x2870 972-340-2870 (direct) 972-991-6061 (fax) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Doug Dearden -
Galen Charlton -
Robert Williams