BEGIN

	-- synchronisatie zolang geen ziekenhuisontslag gekend, ook voor opgenomen patienten.
	INSERT INTO #tmp_admission_to_update
	select v.patientid, v.patnumber, v.SocSecurity, hdi.admissionid is not null as in_tbl
	from (
		SELECT pg.PatientID, pg.PatNumber, pg.SocSecurity 
		FROM iziswh.P_GeneralData AS pg 
		WHERE pg.AdmissionTime >= PARSEDATE('2008-01-01','yyyy-MM-dd') AND 
			pg.PatNumber IS NOT NULL AND 
			pg.SocSecurity IS NOT NULL 
		UNION 
		SELECT pg.PatientID, pg.PatNumber, pg.SocSecurity 
		FROM izisprod.P_GeneralData AS pg 
		WHERE pg.PatNumber IS NOT NULL AND 
			pg.SocSecurity IS NOT NULL AND 
			(
				pg.status = 1 or
				(pg.Status >= 4 AND pg.Status <> 5) OR 
				pg.ResearchPat = 1
			)
	) v 
	left join wodandb.hospitaldischargeinfo hdi on
		hdi.admissionid = v.patientid
	where hdi.hospitaldischargetime is null;

	create temporary table tmp_newvals(
		admissionid	integer not null,
		adremanumber	string,
		episodenumber	string,
		hospadmissiontime	timestamp,
		origin	string,
		hospital	string,
		gender	string,
		nationality	string,
		postalcode	string,
		country	string,
		hospitaldischargetime	timestamp,
		destination	string,
		warddischarge	string,
		foto	blob,
		primary key (admissionid)
	);

	INSERT INTO tmp_newvals (admissionid, adremanumber, episodenumber, hospadmissiontime, origin, hospital, gender, nationality, postalcode, country, hospitaldischargetime, destination, warddischarge, foto) 
	SELECT v.PatientID AS admissionid, c.PAT_ID AS adremanumber, RTRIM(c.VISIT_ID) AS episodenumber, 
		c.ADM_DATETIME AS hospadmissiontime, c.HERKOMST AS origin, c.ZIEKENHUIS AS hospital, 
		c.GENDER AS gender, c.NATIONALITY AS nationality, c.POSTAL_CODE AS postalcode, 
		c.COUNTRY AS country, c.DIS_DATETIME AS hospitaldischargetime, c.BESTEMMING AS destination, 
		c.WARDONTSLAG AS warddischarge,c.FOTO as foto 
	FROM #tmp_admission_to_update AS v 
	-- join ipv left join. Enkel indien adrema en episode correct in IZIS.
	JOIN OAZISP.UITVOERENCUR c on
		c.PATID_IN = replace(upper(v.PatNumber), ' ', '') AND 
		c.VISITID_IN = v.SocSecurity;

	create temporary table tmp_oldvals(
		admissionid	integer not null,
		adremanumber	string,
		episodenumber	string,
		hospadmissiontime	timestamp,
		origin	string,
		hospital	string,
		gender	string,
		nationality	string,
		postalcode	string,
		country	string,
		hospitaldischargetime	timestamp,
		destination	string,
		warddischarge	string,
		foto	blob,
		primary key (admissionid)
	);

	INSERT INTO tmp_oldvals (admissionid, adremanumber, episodenumber, hospadmissiontime, origin, hospital, gender, nationality, postalcode, country, hospitaldischargetime, destination, warddischarge, foto) 
	SELECT v.PatientID AS admissionid, hdi.adremanumber, hdi.episodenumber, 
		hdi.hospadmissiontime, hdi.origin, hdi.hospital, 
		hdi.gender, hdi.nationality, hdi.postalcode, 
		hdi.country, hdi.hospitaldischargetime, hdi.destination, 
		hdi.warddischarge,hdi.foto
	FROM #tmp_admission_to_update AS v 
	JOIN wodandb.hospitaldischargeinfo hdi on
		hdi.admissionid = v.patientid;

	-- invoeren nieuwe records
	INSERT INTO wodandb.hospitaldischargeinfo (admissionid, adremanumber, episodenumber, 
		hospadmissiontime, origin, hospital, gender, nationality, postalcode, country, 
		hospitaldischargetime, destination, warddischarge, foto, createtimestamp, updatetimestamp) 
	SELECT n.*, now() as createtimestamp, now() as updatetimestamp
	FROM #tmp_admission_to_update tu
	join tmp_newvals n on n.admissionid = tu.patientid
	where not tu.in_tbl;

	LOOP ON (
		-- enkel voor records die geupdate kunnen worden (in_tbl = true)
		SELECT n.*
		FROM #tmp_admission_to_update tu
		join tmp_newvals n on n.admissionid = tu.patientid
		join tmp_oldvals o on o.admissionid = n.admissionid
		where tu.in_tbl and
			(
				(n.hospadmissiontime is null and o.hospadmissiontime is not null) or
				(n.hospadmissiontime is not null and o.hospadmissiontime is null) or
				(n.hospadmissiontime <> o.hospadmissiontime) or
				(n.origin is null and o.origin is not null) or
				(n.origin is not null and o.origin is null) or
				(n.origin <> o.origin) or
				(n.hospital is null and o.hospital is not null) or
				(n.hospital is not null and o.hospital is null) or
				(n.hospital <> o.hospital) or
				(n.gender is null and o.gender is not null) or
				(n.gender is not null and o.gender is null) or
				(n.gender <> o.gender) or
				(n.nationality is null and o.nationality is not null) or
				(n.nationality is not null and o.nationality is null) or
				(n.nationality <> o.nationality) or
				(n.postalcode is null and o.postalcode is not null) or
				(n.postalcode is not null and o.postalcode is null) or
				(n.postalcode <> o.postalcode) or
				(n.country is null and o.country is not null) or
				(n.country is not null and o.country is null) or
				(n.country <> o.country) or
				(n.hospitaldischargetime is null and o.hospitaldischargetime is not null) or
				(n.hospitaldischargetime is not null and o.hospitaldischargetime is null) or
				(n.hospitaldischargetime <> o.hospitaldischargetime) or
				(n.destination is null and o.destination is not null) or
				(n.destination is not null and o.destination is null) or
				(n.destination <> o.destination) or
				(n.warddischarge is null and o.warddischarge is not null) or
				(n.warddischarge is not null and o.warddischarge is null) or
				(n.warddischarge <> o.warddischarge) or
				(n.foto is null and o.foto is not null)
			)
	)as cur
	BEGIN
		update wodandb.hospitaldischargeinfo
		set hospadmissiontime = cur.hospadmissiontime,
			origin = cur.origin,
			hospital = cur.hospital,
			gender = cur.gender,
			nationality = cur.nationality,
			postalcode = cur.postalcode,
			country = cur.country,
			hospitaldischargetime = cur.hospitaldischargetime,
			destination = cur.destination,
			warddischarge = cur.warddischarge,
			foto = cur.foto,
			updatetimestamp = now()
		where admissionid = cur.admissionid;
	END
END