Category=Database Maintenance
Name=09b. Migrate- MORE Document Features
Description=Grabs document data from current VMR tables and imports it to populate the Features table

Heading=Import Document Data Into FEATURES
OutputLevel=1

ImportSQL=SELECT DOCUMENTID from DOCUMENT where NOT EXISTS(SELECT 1 FROM FEATURE WHERE FEATURECODE='LectionContent' AND FEATURE.DOCUMENTID=DOCUMENT.DOCUMENTID) AND EXISTS(select 1 from FEATURE where FEATURECODE='LiturgicalBookType' AND DOCUMENT.DOCUMENTID=FEATURE.DOCUMENTID) AND CONTENTOVERVIEW not like '%+%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionContent', {0}, 'Gospels', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'la%' AND NOT EXISTS(SELECT 1 FROM FEATURE WHERE FEATURECODE='LectionContent' AND FEATURE.DOCUMENTID=DOCUMENT.DOCUMENTID) AND EXISTS(select 1 from FEATURE where FEATURECODE='LiturgicalBookType' AND DOCUMENT.DOCUMENTID=FEATURE.DOCUMENTID)|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionContent', {0}, 'Apostolos', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW = 'lk' |INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LiturgicalBookType', {0}, 'Sun', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT d where DOCUMENTID >=40000 and DOCUMENTID < 49999 and not exists (select 1 from FEATURE f where f.DOCUMENTID=d.DOCUMENTID and f.FEATURECODE='LectionBookType')|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%k%' |INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'cop-sa', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where DOCUMENTID < 50000 and DOCUMENTID >= 30000 AND LANGUAGE LIKE '%g%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Script', {0}, 'Minuscule', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%l%' AND LANGUAGE NOT LIKE '%il%' and LANGUAGE NOT LIKE '%sl%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'lat', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%arm%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'hye', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%ar%' AND LANGUAGE NOT like '%arm%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'arb', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%chu%' or LANGUAGE like '%sl%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'chu', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%syc%' |INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'syc', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%bo%' |INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'cop-bo', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%sa%' |INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'cop-sa', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where LANGUAGE like '%g%' |INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Language', {0}, 'grc', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID,COMMENT from DOCUMENT where COMMENT is not null and COMMENT <> ''|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('GeneralComment', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where DESTROYED=1|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, USERINSTITUTIONID, USERID) VALUES ('ManuscriptDestroyed', {0}, 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID,FORMERGANUMBER from DOCUMENT where FORMERGANUMBER is not null and FORMERGANUMBER <> ''|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('FormerGA', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID,CASE PALEMPSESTUNDER WHEN 1 THEN 'Undertext' WHEN 0 THEN 'Overtext' END,PALEMPSESTOTHER from DOCUMENT where PALEMPSESTUNDER is not null and PALEMPSESTOTHER not like '%''%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, STRINGVAL2, USERINSTITUTIONID, USERID) VALUES ('Palimpsest', {0}, '{1}', '{2}', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID,KLFOOTNOTES from DOCUMENT where KLFOOTNOTES <> '' and KLFOOTNOTES NOT LIKE '%''%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, STRINGVAL2, USERINSTITUTIONID, USERID) VALUES ('GeneralManuscriptObservation', {0}, '{1}', 'Yes', 49, 'ECM')

### This is wrong because we already have some mechanism to escape single quotes
#ImportSQL2=SELECT DOCUMENTID,KLFOOTNOTES from DOCUMENT where KLFOOTNOTES <> '' and KLFOOTNOTES LIKE '%''%' AND KLFOOTNOTES NOT LIKE '%"%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, STRINGVAL2, USERINSTITUTIONID, USERID) VALUES ('GeneralManuscriptObservation', {0}, "{1}", 'Yes', 49, 'ECM')

#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'l%+%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionContent', {0}, 'Gospels+Apostolos', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'l%e%' and CONTENTOVERVIEW not like 'l%k%' and CONTENTOVERVIEW not like 'l%sel%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Weekdays', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'l%ek%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Weekdays+Sun', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'l%sk%' and CONTENTOVERVIEW not like 'l%esk%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Sat+Sun', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'l%sel%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Selected', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like '%esk%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Sat+Sun+Easter-Pent Weekdays', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where DOCUMENTID between 30000 and 49999 and CONTENTOVERVIEW like '%K%' and CONTENTOVERVIEW NOT LIKE '%:%' and CONTENTOVERVIEW NOT LIKE '%1K%' and CONTENTOVERVIEW NOT LIKE '%2K%' and CONTENTOVERVIEW NOT LIKE '%Kol%' and CONTENTOVERVIEW NOT LIKE '%Kath%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, USERINSTITUTIONID, USERID) VALUES ('Commentary', {0}, 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where DOCUMENTID IN (\
#20040,\
#20050,\
#20141,\
#30041,\
#30087,\
#30092,\
#30095,\
#30136,\
#30139,\
#30146,\
#30197,\
#30239,\
#30243,\
#30249,\
#30300,\
#30304,\
#30306,\
#30310,\
#30311,\
#30313,\
#30315,\
#30316,\
#30317,\
#30318,\
#30320,\
#30333,\
#30334,\
#30354,\
#30357,\
#30362,\
#30366,\
#30381,\
#30397,\
#30423,\
#30426,\
#30427,\
#30430,\
#30434,\
#30437,\
#30455,\
#30589,\
#30590,\
#30596,\
#30598,\
#30621,\
#30640,\
#30721,\
#30722,\
#30734,\
#30735,\
#30736,\
#30737,\
#30738,\
#30739,\
#30742,\
#30743,\
#30770,\
#30819,\
#30821,\
#30822,\
#30832,\
#30836,\
#30840,\
#30841,\
#30842,\
#30846,\
#30847,\
#30848,\
#30849,\
#30850,\
#30853,\
#30857,\
#30859,\
#30862,\
#30865,\
#30868,\
#30869,\
#30874,\
#30879,\
#30882,\
#30883,\
#30884,\
#30887,\
#30893,\
#30970,\
#30993,\
#30994,\
#31016,\
#31027,\
#31028,\
#31043,\
#31112,\
#31156,\
#31177,\
#31184,\
#31254,\
#31255,\
#31256,\
#31264,\
#31267,\
#31271,\
#31277,\
#31332,\
#31337,\
#31366,\
#31370,\
#31374,\
#31411,\
#31412,\
#31437,\
#31516,\
#31523,\
#31527,\
#31537,\
#31613,\
#31631,\
#31707,\
#31764,\
#31772,\
#31817,\
#31818,\
#31819,\
#31820,\
#31821,\
#31822,\
#31844,\
#31878,\
#31879,\
#31910,\
#31913,\
#31915,\
#31925,\
#31926,\
#31928,\
#31937,\
#31938,\
#31942,\
#31968,\
#31976,\
#31979,\
#31983,\
#31988,\
#31993,\
#32001,\
#32002,\
#32006,\
#32013,\
#32092,\
#32103,\
#32106,\
#32111,\
#32125,\
#32128,\
#32129,\
#32130,\
#32184,\
#32185,\
#32187,\
#32190,\
#32192,\
#32202,\
#32207,\
#32240,\
#32242,\
#32257,\
#32285,\
#32450,\
#32480,\
#32481,\
#32490,\
#32538,\
#32572,\
#32573,\
#32574,\
#32575,\
#32577,\
#32579,\
#32581,\
#32583,\
#32593,\
#32596,\
#32597,\
#32607,\
#32668,\
#32738,\
#32741,\
#32755,\
#32763,\
#32764,\
#32768,\
#32770,\
#32838,\
#32890)|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, USERINSTITUTIONID, USERID) VALUES ('Commentary', {0}, 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where DOCUMENTID between 30000 and 49999 and CONTENTOVERVIEW like '%P%' and CONTENTOVERVIEW NOT LIKE '%:%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Extant', {0}, 'Partial', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like '%Jerus%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionTradition', {0}, 'Jerusalem', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like '%Lit%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Other', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like '%PsO%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('LectionBookType', {0}, 'Psalms+Odes', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where DOCUMENTID between 20000 and 29999;|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Script', {0}, 'Majuscule', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like '%INCOMPLETE%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Extant', {0}, 'Incomplete', 49, 'ECM')
#select distinct CONTENTOVERVIEW, CONCAT(LEFT(CONTENTOVERVIEW, INSTR(CONTENTOVERVIEW, '†')-1), RIGHT(CONTENTOVERVIEW, CHAR_LENGTH(CONTENTOVERVIEW)-INSTR(CONTENTOVERVIEW, '†'))) from DOCUMENT WHERE DOCUMENTID between 40000 and 49999 and CONTENTOVERVIEW like '%†%';
#ImportSQL=SELECT DOCUMENTID from DOCUMENT where CONTENTOVERVIEW like 'U-%' and DOCUMENTID between 40000 and 49999;|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Script', {0}, 'Majuscule', 49, 'ECM')
#ImportSQL=SELECT DOCUMENTID, right(CONTENTOVERVIEW, length(CONTENTOVERVIEW)-2) NC from DOCUMENT where CONTENTOVERVIEW like 'U-%' and DOCUMENTID between 40000 and 49999;|UPDATE DOCUMENT SET CONTENTOVERVIEW='{1}' WHERE DOCUMENTID={0}

#ImportSQL=SELECT ObjID, (CASE WHEN Beschreibstoff ='Pap' THEN 'Paper' WHEN Beschreibstoff like'Pg%' THEN 'Parchment' WHEN Beschreibstoff ='PuPg' THEN 'Parchment' ELSE Beschreibstoff END) from vmrold.HSSListe where Beschreibstoff is not null and Beschreibstoff <> '' and Beschreibstoff <> '?'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('CanvasMaterial', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT ObjID, 'Paper' from vmrold.HSSListe where Beschreibstoff like 'Pg%Pap'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('CanvasMaterial', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT ObjID from vmrold.HSSListe where Beschreibstoff = 'PuPg'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, USERINSTITUTIONID, USERID) VALUES ('PurpleParchment', {0}, 49, 'ECM')
#ImportSQL=SELECT ObjID, 'Gold' from vmrold.HSSListe where Beschreibstoff like '%Gold%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('WritingMaterial', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT ObjID, 'Silver' from vmrold.HSSListe where Beschreibstoff like '%Silber%'|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('WritingMaterial', {0}, '{1}', 49, 'ECM')

#ImportSQL=SELECT ObjID, Soden from vmrold.HSSListe where Soden is not null|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('VonSoden', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT ObjID, Oxy from vmrold.HSSListe where Oxy is not null|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, USERINSTITUTIONID, USERID) VALUES ('Oxy', {0}, '{1}', 49, 'ECM')
#ImportSQL=SELECT ObjID, LDAB, LDABurl from vmrold.HSSListe where LDAB<>0|INSERT INTO FEATURE (FEATURECODE, DOCUMENTID, STRINGVAL1, STRINGVAL2, USERINSTITUTIONID, USERID) VALUES ('LDAB', {0}, '{1}', '{2}', 49, 'ECM')
IgnoreException=Error Code: 1146