Category=Database Maintenance
Name=02.1. Migrate- Institution
Description=Grabs data from current VMR tables and imports it into the data model

OutputLevel=3

Heading=Import Institution table data from Besitzer table data

ImportSQL=SELECT	\
					\
BesitzerID,			\
(CASE WHEN InstitutionCurrentName is not null and LENGTH(InstitutionCurrentName) > 1 THEN InstitutionCurrentName WHEN Institution is not null and LENGTH(Institution) > 1 THEN INSTITUTION ELSE Institut END) NAME,					\
Institution KLNAME,				\
concat(Place, ', ', Institut) KLNAMEABBREV,		\
Address,			\
addr_strasse,		\
Place,				\
Country,			\
CASE WHEN addr_plz is null THEN null WHEN addr_plz = 0 THEN null END POSTCODE,			\
URL,				\
URL1,				\
Googlemaps,				\
concat('http://intf.uni-muenster.de/vmr/NTVMR/images/logoInstitution/',logo),		\
concat('http://intf.uni-muenster.de/vmr/NTVMR/images/logoInstitution/',logo2),		\
coalesce(`Contact (person)`, concat(Con_titel, Con_vorname, ' ', Con_nachname)),	\
Email,				\
Phone,				\
Fax					\
					\
FROM Besitzer T1|				\
INSERT INTO INSTITUTION (		\
					\
INSTITUTIONID,		\
NAME,				\
KLNAME,				\
KLNAMEABBREV,		\
ADDRESS1,			\
ADDRESS2,			\
PLACE,				\
COUNTRY,			\
POSTCODE,			\
INSTITUTIONURL,		\
COLLECTIONURL1,		\
COLLECTIONURL2,		\
LOGOURL1,			\
LOGOURL2,			\
CONTACTNAME,		\
CONTACTEMAIL,		\
CONTACTPHONE,		\
CONTACTFAX			\
					\
) VALUES ( {0},   '{1}',  '{2}',  '{3}',  '{4}',		\
		  '{5}',  '{6}',  '{7}',  '{8}',  '{9}',		\
		 '{10}', '{11}', '{12}', '{13}', '{14}',		\
		 '{15}', '{16}', '{17}')