-- =============================================
-- Author:      Vitacore (с)
-- Create date: 
-- Description:  поиск фактов пм и дн в загруженном счете
-- =============================================
-- DROP FUNCTION sp_SchetSearch_PM_DN_facts(uuid);
CREATE OR REPLACE FUNCTION sp_SchetSearch_PM_DN_facts
(
	"FileID" uuid
)
RETURNS void AS
$BODY$
declare
	_ts timestamp;
	fileid uuid;
begin
	fileid := "FileID";
	_ts := now()::timestamp;
	
	Drop Table If Exists TmpDN;
	Create Temp Table TmpDN
	As
	select
		gen_random_uuid() as DnFactID
	,	ss.DATE_1 as DATE_1
	,	ss.DATE_2 as DATE_2
	,	ss.RSLT as V009Result
	,	ss.Mkb as DS_MkbID
	,	ssa.MO as MO
	,	_ts as CreationDate
	,	ss.SchetSluchID as SchetSluchID
	,	sz.Insured as Insured
	,	null::uuid as DnItem
	from ISZL.T_SCHET_SLUCH ss
	left join 
		ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa 
		on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
	inner join
		ISZL.T_SCHET_ZAP sz
		on sz.SchetZapID = ss.SchetZap
	inner join
		ISZL.T_SCHET s
		on s.SchetID = sz.Schet
	where s.SchetFile = fileid
	and (ss.DN is not null and ss.P_CEL = '1.3');--1.3	Диспансерное наблюдение	01.01.2018		
	
	update TmpDN dn
	set DnItem = 
	(
		select di.DnItemId 
		from ISZL.T_DN_ITEM di
		where di.Insured = dn.Insured 
		and di.DS_DISP = dn.DS_MkbID 
		and di.MO_IN = dn.MO
		limit 1
	)
	where dn.Insured is not null;

	insert into ISZL.T_DN_FACT(DnFactID, DnItem, DATE_1, DATE_2, V009Result, DS, MO, CreationDate, SchetSluch, Insured)
	select d.DnFactID, d.DnItem, d.DATE_1, d.DATE_2, d.V009Result, d.DS_MkbID, d.MO, d.CreationDate, d.SchetSluchID, d.Insured
	from TmpDN d;

	update ISZL.T_INSURED t0
	set IsSubjectDN = case when ( EXISTS (SELECT *
										FROM ISZL.T_SCHET_SLUCH t4 
											left join iszl.T_MKB mkb1 on mkb1.MCBCode = t4.DS1
											left join iszl.t_DN_PERIODICITY dnp1 on dnp1.Mkb = mkb1.MkbID and dnp1.Periodicity is not null
											left join iszl.T_MKB mkb2 on mkb2.MCBCode = t4.DS2
											left join iszl.t_DN_PERIODICITY dnp2 on dnp2.Mkb = mkb2.MkbID and dnp2.Periodicity is not null
										WHERE t3.SchetSluchAccomplishedID=t4.SchetSluchAccomplished 
													AND (dnp1.ID is not null OR dnp2.ID is not null) 
													AND  NOT EXISTS (SELECT *
													FROM ISZL.T_DN_ITEM t62 
														LEFT JOIN ISZL.T_MKB t63 ON t63.MkbID = t62.DS_DISP
													WHERE (t63.MCBCode=t4.DS1 OR t63.MCBCode=t4.DS2) AND t62.Insured=t0.InsuredID)
										))
			then true else false end
	FROM ISZL.T_SCHET_ZAP sz
	JOIN ISZL.T_SCHET_SLUCH_ACCOMPLISHED t3 ON t3.SchetZap = sz.SchetZapID
	join ISZL.T_SCHET s on s.SchetID = sz.Schet
	WHERE sz.Insured = t0.InsuredID and s.SchetFile = fileid AND t0.DateDeath IS NULL;


-------------------------------------------------------------------------------------------------------------------
	
	Drop Table If Exists TmpPM;
	Create Temp Table TmpPM
	As
	select
		gen_random_uuid() as DispDirectFactID
	,	ss.DATE_1 as DateBegin
	,	ss.DATE_2 as DateEnd
	,	sz.FAM as FAM
	,	sz.IM as IM
	,	sz.OT as OT
	,	ssa.MO as LpuMain
	,	ssa.RSLT_D as RSLT_D
	,	sz.DR
	,	coalesce(sz.ENP, ins.ENP) as ENP
	,	ssa.MO as CODE_MO
	,	coalesce(ss.ISHOD, ssa.ISHOD) as ISHOD
	,	s.NSCHET as NSCHET
	,	s.YEAR as YEAR
	,	s.MONTH as MONTH
	,	v016.V016ID as V016Dispt
	,	null::uuid as DispDirectFactFile
	,	null::varchar(4000) as Comments
	,	case when ss.DN = 1 or ss.DN = 2 then 1 else 0 end as PR_D_N
	,	ss.DS1 as DS1
	,	sz.Insured as Insured
	,	_ts as CreationDate
	,	ss.SchetSluchID as SchetSluchID
	,	sz.ID_PAC as ID_PAC
	,	sz.W as Sex
	,	sz.VPOLIS
	,	sz.SPOLIS
	,	sz.NPOLIS
	,	sz.SNILS as SNILS_P
	,	sz.F002smo as SMOCOD
	,	sz.InsuredPolicyRecord as InsuredPolicyRecordID
	,	null::uuid as DispDirect
	from ISZL.T_SCHET_SLUCH ss
	left join 
		ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa
		on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
	inner join
		ISZL.T_SCHET_ZAP sz 
		on sz.SchetZapID = ss.SchetZap
	left join
		iszl.T_INSURED ins 
		on ins.InsuredID = sz.Insured
	inner join
		ISZL.T_SCHET s
		on s.SchetID = sz.Schet
	inner join
		ISZL.T_V016_DISPT v016
		on v016.IDDT = s.DISP
	where s.SchetFile = fileid;
		--and (ss.DN is not null or ss.P_CEL = '2.2')--1.3	Диспансерное наблюдение	01.01.2018
	
	Drop Table If Exists TmpNAZ;
	Create Temp Table TmpNAZ
	As
	select 
		naz.DestinationType as DestinationType
	,	pm.DispDirectFactID as DispDirectFactID
	from ISZL.T_SCHET_SLUCH_DEST naz
	inner join
		TmpPM pm
		on pm.SchetSluchID = naz.OmsSchetSluch;
	
	insert into TmpNAZ(DestinationType, DispDirectFactID)
	select naz.NAZR, pm.DispDirectFactID
	from ISZL.T_SCHET_SLUCH_NAZ naz
	inner join
		TmpPM pm
		on pm.SchetSluchID = naz.SchetSluch;

	update TmpPM pm
	set DispDirect = 
	(
		select dd.DispDirectID 
		from ISZL.T_DISP_DIRECT dd 
		left join
			ISZL.T_DISP_DIRECT_FACT ddf 
			on ddf.DispDirect = dd.DispDirectID
		where dd.Insured = pm.Insured 
		and dd.V016Dispt = pm.V016Dispt
		and dd.YEAR = extract(year from pm.DateBegin)
		and ddf.DispDirectFactID is null
		and dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
		limit 1
	)
	where pm.Insured is not null;	

	insert into ISZL.T_DISP_DIRECT_FACT(DispDirectFactID, DateBegin, DateEnd, FAM, IM, OT, RSLT_D, DispDirect
	, DR, ENP, CODE_MO, ISHOD, NSCHET, YEAR, MONTH, V016Dispt, DispDirectFactFile, Comments, PR_D_N, DS1
	, Insured, CreationDate, SchetSluch)
	select pm.DispDirectFactID, pm.DateBegin, pm.DateEnd, pm.FAM, pm.IM, pm.OT, pm.RSLT_D, pm.DispDirect, pm.DR
	, pm.ENP, pm.CODE_MO, pm.ISHOD, pm.NSCHET, pm.YEAR, pm.MONTH, pm.V016Dispt, null, null, pm.PR_D_N
	, pm.DS1, pm.Insured, pm.CreationDate, pm.SchetSluchID
	from TmpPM pm;

	---------------------------------------------
	---Создаём автоматом направления на 2 этап--- 
	---------------------------------------------
	INSERT INTO iszl.T_DISP_DIRECT(DispDirectID, ID_PAC, FAM, IM, OT, Sex, DR, ENP, VPOLIS, SPOLIS, NPOLIS
	, SNILS_P, YEAR, V016Dispt, MONTH_Q, Smo, Mo, Insured, InsuredPolicyRecord, CreationDate, SchetSluch)
	select gen_random_uuid(), p.ID_PAC, p.FAM, p.IM, p.OT, p.Sex, p.DR, p.ENP, p.VPOLIS, p.SPOLIS, p.NPOLIS, p.SNILS_P
	, case when extract(month from p.DateEnd) = 12 then p.YEAR + 1 else p.YEAR end
	, v016.V016ID
	, case when extract(month from p.DateEnd) = 12 then 1 else extract(month from p.DateEnd) end
  	, orgsmo.OrganizationID, orgmo.OrganizationID, p.Insured, p.InsuredPolicyRecordID
	, now()::timestamp, p.SchetSluchID
	from TmpPM p
	left join
		ISZL.T_ORGANIZATION orgsmo
		on orgsmo.Code = p.SMOCOD
	left join
		ISZL.T_ORGANIZATION orgmo
		on orgmo.Code = p.CODE_MO
	left join
		ISZL.T_V016_DISPT v016
		on v016.IDDT = 'ДВ2'
		and v016.DATEEND is null
	where p.RSLT_D in (6, 11, 12, 13, 14, 15, 17, 18, 19);
	---------------------------------------------
	---------------------------------------------
	---------------------------------------------

	insert into ISZL.T_DISP_DIRECT_FACT_DESTINATION( DispDirectFactDestinationID, DestinationType, DispDirectFact)
	select gen_random_uuid(), n.DestinationType, n.DispDirectFactID
	from TmpNAZ n;

	drop table if exists TmpNAZ;
	drop table if exists TmpPM;
	drop table if exists TmpDN;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_SchetSearch_PM_DN_facts(uuid) OWNER TO postgres;
