Category=Sandbox
Name=batch import tests
Description=batch import tests

Heading=Try to get folio number correct
ImportSQL=\
SELECT BI.REALDOCUMENTID, SI.SHELFID, BI.FOLIOCOUNT*2, SUBSTRING_INDEX(BI.FOLIONUMBER,'-',1) from BATCHIMPORT BI \
join SHELFINSTANCE SI on SI.INSTITUTIONID = BI.INSTITUTIONID AND BI.SHELFNUMBER = SI.SHELFNUMBER \
where NOT EXISTS (SELECT * FROM SHELFINSTANCEPAGES SIP where SIP.SHELFID = SI.SHELFID) and USERDATA > 0 \
AND NOT EXISTS(SELECT 1 FROM PAGE WHERE PAGE.DOCUMENTID=BI.REALDOCUMENTID) AND BI.REALDOCUMENTID > 0 \
|[1..{2}]SELECT		\
		\
{1} DOCUMENTID,	\
COALESCE(MAX(PAGEID), 0) + 10 PAGEID,	\
CASE WHEN CAST('{4}' as UNSIGNED) > 0 then CONCAT(CAST({4}+CEIL({0}/2)-1 as char),(CASE WHEN MOD(CAST('{0}' as UNSIGNED),2)=0 THEN 'v' else 'r' END))  else '' END  FOLIONUMBER, \
cast(lpad(COALESCE(MAX(PAGEID), 0) + 10, 5, '0') as char)		\
FROM PAGE WHERE DOCUMENTID={1}| \
[1..{2}]SELECT		\
		\
{1},		\
COALESCE(MAX(PAGEID), 0),	\
{2},		\
CASE WHEN CAST('{4}' as UNSIGNED) > 0 then CONCAT(CAST({4}+CEIL({0}/2)-1 as char),(CASE WHEN MOD(CAST('{0}' as UNSIGNED),2)=0 THEN 'v' else 'r' END))  else '' END  FOLIONUMBER,  \
cast(lpad(COALESCE(MAX(PAGEID), 0), 5, '0') as char)		\
FROM PAGE P WHERE DOCUMENTID={1};