-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-10-03
-- Description: Формирование пакета диспансерного наблюдения после идентификации в РДС
-- =============================================
DROP PROCEDURE IF EXISTS sp_ActDnIdentification;
CREATE PROCEDURE sp_ActDnIdentification
(
	"GuidPackage" uuid
,	"XML" xml
)
AS
$BODY$
Declare	_entityId int;
	_number int;
	_stringForGenNumbers varchar(255);
	_d timestamp;
Begin
	_stringForGenNumbers := '4filename_' || to_char(_d,'YYMMDD');
	_entityId := (select ent.EntityID from VCLib.T_ENTITIES ent where ent.EntName = 'DISPANSERISATION_INIT' limit 1);
	_number := sp_GenerateNumber(_entityId, _stringForGenNumbers);

	update AKUZ.T_RDS_DISP_PATIENT pat
	set  RdsDispPackage = "GuidPackage"
    from AKUZ.T_RDS_DISP_PACKAGE dp 
	inner join
		AKUZ.T_RDS_DISP_PACKAGE dp2 
		on dp2.RdsdispPackageID = dp.RdsDispPackage
	where dp.RdsdispPackageID = pat.RdsDispPackage
	and dp2.RdsdispPackageID =  "GuidPackage";

	delete from AKUZ.T_RDS_DISP_PACKAGE where RdsDispPackage = "GuidPackage";

	-- Generate table TmpZGLV
	Drop Table If Exists TmpZGLV;
	Create Temp Table TmpZGLV
	as
	SELECT
		(xpath('/ZGLV/VERSION[1]/text()', x))[1]::varchar(5) as VERSION
	,	(xpath('/ZGLV/DATA[1]/text()', x))[1]::text::timestamp as "DATA"
	,	(xpath('/ZGLV/YEAR[1]/text()', x))[1]::text::int as "YEAR"
	,	(xpath('/ZGLV/FILENAME[1]/text()', x))[1]::varchar(24) as FILENAME
	,	(xpath('/ZGLV/MCOD[1]/text()', x))[1]::text::int as MCOD
	from unnest(xpath('/DN_LIST/ZGLV', "XML")) as x;
	
	Drop Table If Exists TmpZL;
	Create Temp Table TmpZL
	as
	SELECT
		uuid_generate_v4() as ItemID
	,	(xpath('/ZL/ID_PAC[1]/text()', x))[1]::varchar(36) as ID_PAC
	,	(xpath('/ZL/FAM[1]/text()', x))[1]::varchar(40) as FAM
	,	(xpath('/ZL/IM[1]/text()', x))[1]::varchar(40) as IM
	,	(xpath('/ZL/OT[1]/text()', x))[1]::varchar(40) as OT
	,	(xpath('/ZL/DR[1]/text()', x))[1]::text::timestamp as DR
	,	(xpath('/ZL/ENP[1]/text()', x))[1]::varchar(16) as ENP
	,	(xpath('/ZL/VPOLIS[1]/text()', x))[1]::text::int as VPOLIS
	,	(xpath('/ZL/SPOLIS[1]/text()', x))[1]::varchar(10) as SPOLIS
	,	(xpath('/ZL/NPOLIS[1]/text()', x))[1]::varchar(20) as NPOLIS
	,	(xpath('/ZL/SMO[1]/text()', x))[1]::varchar(5) as SMO
	,	(xpath('/ZL/SNILS[1]/text()', x))[1]::varchar(14) as SNILS
	,	(xpath('/ZL/DS1[1]/text()', x))[1]::varchar(10) as DS1
	,	(xpath('/ZL/ERROR[1]/text()', x))[1]::varchar(1000) as ERROR
	from unnest(xpath('/DN_LIST/ZL', "XML")) as x;
	
	Create temp Table TmpSMO(smo varchar(32), ID uniqueidentifier);

	Insert Into TmpSMO(smo) 
	select DISTINCT zl.SMO
	from TmpZL zl 
	where zl.SMO <> ''
	and zl.SMO is not null;

	Insert Into TmpSMO(smo) values(null);  -- Набор пациентов с ошибками

	update TmpSMO set ID = uuid_generate_v4();

	------------------------Создать Childs страховых
	insert into AKUZ.T_RDS_DISP_PACKAGE(RdsdispPackageID, YearDisp, CODE_MO, TimeCreate, InsuranceCompany, RdsDispPackage, QuantumPatients
										, DATA, MONTH, FILENAME, Branch, RdsDispProgram, VERSION)
	select t.ID, rdp.YearDisp, rdp.CODE_MO, _d, t.smo, "GuidPackage"
	, (select count(*) from TmpZL zl where coalesce(ZL.SMO, '') = coalesce(t.smo, '') and coalesce(ZL.NPOLIS, '') <> '' and coalesce(ZL.ENP, '') <> '') q
	, _d, to_char(_d, 'MM'), ('DN' || t.smo || '_' || to_char(_d, 'YYMMDD') || _number::varchar(255)) f, rdp.Branch, rdp.RdsDispProgram, rdp.VERSION
	from TmpSMO t
	left join lateral
	(
		select rd.YearDisp, rd.CODE_MO, rd.Branch, rd.RdsDispProgram, rd.VERSION from AKUZ.T_RDS_DISP_PACKAGE rd where rd.RdsdispPackageID = "GuidPackage" limit 1
	) rdp on true;

	-- Отмечаем пациентов, "пропавших" из акта  ----------------- 
	--update pat  set pat.ERROR = 'Отсутствует в возвращенном акте'
	--from  AKUZ.T_RDS_DISP_PATIENT pat
	--where  pat.YEAR = (select  top 1 YEAR from  #PERS)
	--and pat.ID_PAC not in (select #PERS.ID_PAC from #PERS)

--select * from AKUZ.T_RDS_DISP_PATIENT limit 7
	----------------------- Выставить у пациентов СМО,ENP,ERROR
	update AKUZ.T_RDS_DISP_PATIENT pat
	set SMO = ZL.SMO
	,	ENP = ZL.ENP
	,	ERROR =  REPLACE(ZL.ERROR, chr(10), chr(13) || chr(10))
	from TmpZL zl   
	where ZL.ID_PAC = pat.ID_PAC
	and pat.RdsDispPackage = "GuidPackage";

	------------------------ Выставить у пациентов ссылку на  пакет СМО 
	update AKUZ.T_RDS_DISP_PATIENT pat 
	set	RdsDispPackage = (select t.ID from TmpSMO t where coalesce(t.smo,'') = coalesce(pat.SMO, '') limit 1)
	where pat.RdsDispPackage = "GuidPackage"
	and coalesce(pat.ENP, '') <> '' 
	and coalesce(pat.NPOLIS, '') <> '';

	DROP TABLE if exists TmpZL;
	DROP TABLE if exists TmpZGLV;
	DROP TABLE if exists TmpSMO;
End; $BODY$ LANGUAGE plpgsql;
