drop view if exists pgp_doklady02;
go

create view pgp_doklady02 as
select
	ab.PACIENT as ID_POJ,
	999 as ID_ZP,
	aa.icz as IDZZ,
	ab.uwid_ab as ID_DOKLADU,
	ab.ODB as ODB,
	ab.PRI as DATUM_PRI,
	ab.PRO as DATUM_PRO,
	nar.dat_nar as DATUM_NAR,
	case when nar.dat_nar is not null then null else DATEDIFF(year, cast((SUBSTRING(cast(ae.NAROZENI as varchar), 1, 4) + '-01-01') as date), ab.PRI) end as VEKLET,
	null as VEKDEN,
	case when ae.POHLAVI='M' then 1 else (case when ae.POHLAVI='Z' then 2 else 0 end) end as POHLAVI,
	null as HMOTNOST,
	null as GEST_VEK,
	ab.DOPOR as PRIJETI,
	null as DRU_PRI,
	null as DUV_PRI,
	ab.ZPUKON as UKONCENI,
	ab.ZDG as DG_ZAKLADNI,
	DG_VEDLEJSI1,
	null as DG_VEDLEJSI_TYP1,
	DG_VEDLEJSI2,
	null as DG_VEDLEJSI_TYP2,
	DG_VEDLEJSI3,
	null as DG_VEDLEJSI_TYP3,
	DG_VEDLEJSI4,
	null as DG_VEDLEJSI_TYP4,
	DG_VEDLEJSI5,
	null as DG_VEDLEJSI_TYP5,
	DG_VEDLEJSI6,
	null as DG_VEDLEJSI_TYP6,
	DG_VEDLEJSI7,
	null as DG_VEDLEJSI_TYP7,
	DG_VEDLEJSI8,
	null as DG_VEDLEJSI_TYP8,
	DG_VEDLEJSI9,
	null as DG_VEDLEJSI_TYP9,
	DG_VEDLEJSI10,
	null as DG_VEDLEJSI_TYP10,
	DG_VEDLEJSI11,
	null as DG_VEDLEJSI_TYP11,
	DG_VEDLEJSI12,
	null as DG_VEDLEJSI_TYP12,
	DG_VEDLEJSI13,
	null as DG_VEDLEJSI_TYP13,
	DG_VEDLEJSI14,
	null as DG_VEDLEJSI_TYP14,
	null as UPV
from dr_veta_ab ab
	join dr_veta_aa aa on (aa.uwid_aa = ab.uwid_aa)
	join dr_veta_ae ae on (ae.pacient = ab.pacient)
	left join (
			select uwid_ab, 
				MAX(case when id_rn = 1 then KOD else '' end) as dg_vedlejsi1,
				MAX(case when id_rn = 2 then KOD else '' end) as dg_vedlejsi2, 
				MAX(case when id_rn = 3 then KOD else '' end) as dg_vedlejsi3, 
				MAX(case when id_rn = 4 then KOD else '' end) as dg_vedlejsi4, 
				MAX(case when id_rn = 5 then KOD else '' end) as dg_vedlejsi5, 
				MAX(case when id_rn = 6 then KOD else '' end) as dg_vedlejsi6, 
				MAX(case when id_rn = 7 then KOD else '' end) as dg_vedlejsi7, 
				MAX(case when id_rn = 8 then KOD else '' end) as dg_vedlejsi8, 
				MAX(case when id_rn = 9 then KOD else '' end) as dg_vedlejsi9, 
				MAX(case when id_rn = 10 then KOD else '' end) as dg_vedlejsi10, 
				MAX(case when id_rn = 11 then KOD else '' end) as dg_vedlejsi11, 
				MAX(case when id_rn = 12 then KOD else '' end) as dg_vedlejsi12, 
				MAX(case when id_rn = 13 then KOD else '' end) as dg_vedlejsi13, 
				MAX(case when id_rn = 14 then KOD else '' end) as dg_vedlejsi14, 
				case when count(KOD)>=14 then 14 else count(KOD) end as pocet_diagnoz 
			from (
					select ROW_NUMBER() OVER (PARTITION BY ab.uwid_ab order by radek) AS id_rn, ab.uwid_ab, ac.KOD 
					from dr_veta_ab ab 
						left join dr_veta_ac ac on (ab.uwid_ab=ac.uwid_ab)
					where ab.DRUDOK='02' and ac.DRUH_POL=8 and ac.KOD <> '' 
				) sq1 
			group by uwid_ab	
		) dg on (ab.uwid_ab=dg.uwid_ab)
	left join (
		select pacient, min(pri) as dat_nar
		from dr_veta_ab AB
			join dr_veta_ae AE on (AB.pacient = AE.pacient)
		where AB.drudok = '02' and AB.dopor = '0' and year(pri) = cast(substring(cast(AE.NAROZENI as varchar), 1, 4) as int)
		group by pacient
	) as nar on (AB.pacient = nar.pacient)
where 
	ab.DRUDOK='02' AND
	ab.PACIENT is not null AND
	aa.icz is not null AND
	ab.ODB is not null AND
	ab.PRI is not null AND
	ab.PRO is not null AND
	ae.POHLAVI is not null AND
	ab.ZPUKON is not null AND
	ab.ZDG is not null
;
go

drop view if exists pgp_doklady06;
go

create view pgp_doklady06 as 
select 
	ab.PACIENT as ID_POJ,
	999 as ID_ZP,
	aa.ICZ as IDZZ,
	ab.uwid_ab as ID_DOKLADU,
	DRUDOK,
	ab.ODB,
	ab.ICP_ZAD,
	ab.ODB_ZAD,
	ab.DAT_VYST as DATUM_ZAD
from dr_veta_ab AB
	join dr_veta_aa AA on (AB.uwid_aa = AA.uwid_aa)
where ab.DRUDOK='06' AND
	ab.PACIENT is not null AND
	aa.ICZ is not null AND
	ab.ODB is not null AND
	ab.ICP_ZAD is not null AND
	ab.ODB_ZAD is not null
;
go
	
drop view if exists pgp_polozky;	
go
	
CREATE VIEW pgp_polozky AS
SELECT
	aa.ICZ as IDZZ, 
	999 as ID_ZP,
	ab.uwid_ab as ID_DOKLADU, 
	ac.DEN, 
	ac.DRUH_POL as TYP, 
	ac.KOD, 
	ac.MNOZSTVI as MNO 
FROM
	dr_veta_ac ac 
	JOIN dr_veta_ab ab on (ac.uwid_ab=ab.uwid_ab)
	JOIN dr_veta_aa aa on (ab.uwid_aa=aa.uwid_aa)
where ac.DRUH_POL IN (0,1,2,3) AND ab.DRUDOK IN ('02', '06') AND
	ac.DEN IS NOT NULL AND
	ac.DRUH_POL IS NOT NULL AND
	ac.KOD IS NOT NULL AND
	ac.MNOZSTVI IS NOT NULL
;	
go
