Category=coptot - Database Maintenance
Name=08. Generate- Pages for Empty Documents
Description=Generates pages for documents with no page information and assigns those pages to a shelf instance if possible

Heading=Generate Pages Into PAGE from LEAVES * 2

OutputLevel=1
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}]INSERT INTO PAGE (	\
		\
DOCUMENTID,	\
PAGEID,		\
FOLIONUMBER,	\
SORTORDER)	\
		\
SELECT		\
{1} DOCUMENTID,	\
COALESCE(MAX(PAGEID), 0) + 10 PAGEID,	\
CONCAT((CASE WHEN '{4}' REGEXP '^[0-9]+$' then CAST(CAST('{4}' as INT)+CEIL({0}/2)-1 as char) else '{4}' END),(CASE WHEN MOD(CAST('{0}' as UNSIGNED),2)=0 THEN 'v' else 'r' END))  FOLIONUMBER, \
cast(lpad(COALESCE(MAX(PAGEID), 0) + 10, 5, '0') as char)		\
FROM PAGE WHERE DOCUMENTID={1}| \
 									\
[1..{2}]INSERT INTO SHELFINSTANCEPAGES ( \
DOCUMENTID,	\
PAGEID,		\
SHELFID,	\
FOLIONUMBER,	\
SORTORDER)	\
		\
SELECT		\
{1},		\
COALESCE(MAX(PAGEID)-(10*{3})+({0}*10), 0),	\
{2},		\
CONCAT((CASE WHEN '{4}' REGEXP '^[0-9]+$' then CAST(CAST('{4}' as INT)+CEIL({0}/2)-1 as char) else '{4}' END),(CASE WHEN MOD(CAST('{0}' as UNSIGNED),2)=0 THEN 'v' else 'r' END))  FOLIONUMBER, \
cast(lpad(COALESCE(MAX(PAGEID), 0), 5, '0') as char)		\
FROM PAGE P WHERE DOCUMENTID={1};
IgnoreException=Error Code: 1146