[Koha] SQL runs outside of staff client but not inside it

[Doug Dearden]

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,"-", at 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