CREATE OR REPLACE PROCEDURE public.sp_actpmidentiszl(
	IN "GuidPackage" text,
	IN "XML" text)
LANGUAGE 'plpgsql'
AS $BODY$
Declare	_entityId int;
	_number int;
	_stringForGenNumbers varchar(255);
	_d timestamp;
	_GuidPackage uuid :="GuidPackage"::uuid;	
Begin	
	--  Удаляем данные предыдущего акта
	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/FILENAME[1]/text()', x))[1]::varchar(26) as FILENAME
	,	(xpath('/ZGLV/MCOD[1]/text()', x))[1]::text::int as MCOD
	,	date_trunc('day', now()::timestamp) as ReceivedDate
	,	now()::timestamp as ReceivedTime
	from unnest(xpath('/IDENTIFICATION_RESULT_PM/ZGLV', "XML"::xml)) as x;

	Drop Table If Exists TmpPERS;
	Create Temp Table TmpPERS
	as
	SELECT	gen_random_uuid() as ItemID
	,	(xpath('/ZL[1]/ID_PAC[1]/text()', t.x1))[1]::varchar(36) as ID_PAC
	,	(xpath('/ZL[1]/ENP[1]/text()', t.x1))[1]::varchar(16) as ENP
	,	(xpath('/ZL[1]/MO[1]/text()', t.x1))[1]::varchar(5) as MO	
	,	(xpath('/ZL[1]/SMO[1]/text()', t.x1))[1]::text as SMO
	,	(xpath('/ZL[1]/ERROR[1]/text()', t.x1))[1]::text as ERROR
	,	(xpath('/ZGLV[1]/MCOD[1]/text()', t.x2))[1]::text::int as MCOD
	from
	(
		select	unnest(xpath('/IDENTIFICATION_RESULT_PM/ZL', x)) as x1
		,	unnest(xpath('/IDENTIFICATION_RESULT_PM/ZGLV', x)) as x2
		from unnest(xpath('/IDENTIFICATION_RESULT_PM', "XML"::xml)) x
	) t;

	Create temp Table TmpSMO(smo varchar(32), ID uuid);

	Insert Into TmpSMO(smo) 
	select DISTINCT t.SMO
	from TmpPERS t 
	where coalesce(t.SMO,'') <> '';

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

	update TmpSMO set ID = gen_random_uuid();
	
	------------------------Создать Childs страховых
	insert into AKUZ.T_RDS_DISP_PACKAGE(RdsdispPackageID, YearDisp, CODE_MO, TimeCreate, InsuranceCompany, RdsDispPackage, QuantumPatients, DATA, MONTH, FILENAME, Branch, RdsDispProgram)
	select ts.ID, rdp.YearDisp, rdp.CODE_MO, now()::timestamp, ts.smo, _GuidPackage
	, (select count(*) from TmpPERS tp where coalesce(tp.SMO, '') = coalesce(ts.smo, ''))
	, now()::timestamp, date_part('Month', now())::text, 'PROF_LIST' || ts.smo || '_' || to_char(_d, 'YYMMDDHH24MI'), rdp.Branch, rdp.RdsDispProgram  
	from TmpSMO ts
	left join lateral
	(
		select rd.YearDisp, rd.CODE_MO, rd.Branch, rd.RdsDispProgram 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)

	----------------------- Выставить у пациентов СМО,ENP,ERROR
	update AKUZ.T_RDS_DISP_PATIENT pat
	set SMO = tp.SMO
	,	ENP = tp.ENP
	,	ERROR = CASE when tp.ERROR ~ '[a-zA-Zа-яА-Я]' then REPLACE(tp.ERROR, chr(10), chr(13) || chr(10)) else '' end
	from TmpPERS tp
	where tp.ID_PAC = pat.ID_PAC
	and pat.RdsDispPackage = _GuidPackage;

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

	update AKUZ.T_RDS_DISP_PACKAGE
	set PacketStatus = 3
	where RdsdispPackageID = _GuidPackage;

	DROP TABLE if exists TmpPERS;
	DROP TABLE if exists TmpZGLV;
	DROP TABLE if exists TmpSMO;
End;
$BODY$;