do $$
begin
	if exists 
	(
		select * from pg_proc p
		where p.proname = 'sp_loadschetact'
	)
	then
		drop function public.sp_loadschetact(uuid, xml, xml, bit, bit);
	end if;
end;
$$ language plpgsql;
-- =============================================
-- Author:		(C) Vitacard System 2012
-- Create date: 2012-10-27
-- Description:	Формирует xml, по фед. счет-реестру
-- =============================================
CREATE or Replace Function public.sp_LoadSchetAct
(
	"Schet" UUID,
	"Act" xml,
	"ActMod" xml = null,
	"First" bit = null,
	"Mod" bit = '0'
)
RETURNS void As 
$$
Declare	pack UUID;
Begin
	
	Drop Table If Exists TmpSCHET;
	Create Temp Table TmpSCHET(SchetID UUID, VERSION varchar(5), Data TimeStamp, FileName varchar(26), Year int, Month int, NSCHET varchar(15), DSCHET TimeStamp
		, SUMMAV decimal(15,2),  SUMMAP decimal(15,2), SANK_MEK decimal(15,2));	
	-----------------akuz.T_SCHET---------------------------
	insert into TmpSCHET(SchetID, VERSION, Data, FileName, Year, Month, NSCHET, DSCHET, SUMMAV,  SUMMAP, SANK_MEK)	
	SELECT	"Schet"
	,	nullif(trim(xpath('/ZL_LIST/ZGLV[1]/VERSION[1]/text()', "Act")::text,'{}"'),'')::varchar(5)
	,	to_timestamp(nullif(trim(xpath('/ZL_LIST/ZGLV[1]/DATA[1]/text()', "Act")::text,'{}"'),''),'YYYY-MM-DD')
	,	nullif(trim(xpath('/ZL_LIST/ZGLV[1]/FILENAME[1]/text()', "Act")::text,'{}"'),'')::varchar(26)
	,	nullif(trim(xpath('/ZL_LIST/SCHET[1]/YEAR[1]/text()', "Act")::text,'{}"'),'')::int
	,	nullif(trim(xpath('/ZL_LIST/SCHET[1]/MONTH[1]/text()', "Act")::text,'{}"'),'')::int
	,	nullif(trim(xpath('/ZL_LIST/SCHET[1]/NSCHET[1]/text()', "Act")::text,'{}"'),'')::varchar(15)
	,	to_timestamp(nullif(trim(xpath('/ZL_LIST/SCHET[1]/DSCHET[1]/text()', "Act")::text,'{}"'),''),'YYYY-MM-DD')
	,	nullif(trim(xpath('/ZL_LIST/SCHET[1]/SUMMAV[1]/text()', "Act")::text,'{}"'),'')::decimal(15,2)
	,	nullif(trim(xpath('/ZL_LIST/SCHET[1]/SUMMAP[1]/text()', "Act")::text,'{}"'),'')::decimal(15,2)
	,	nullif(trim(xpath('/ZL_LIST/SCHET[1]/SANK_MEK[1]/text()', "Act")::text,'{}"'),'')::decimal(15,2);
		
	select p.SchetPackID
	from akuz.T_SCHET s1
	inner join 
		akuz.T_SCHET_PACK p 
		on p.Schet = s1.SchetID
	inner join 
		TmpSCHET s 
		on s1.YEAR = s.YEAR 
		and s1.MONTH = s.MONTH 
		and s1.NSCHET = s.NSCHET 
		and s1.DSCHET = s.DSCHET
	Into pack
	where s1.SchetID = "Schet";
		
	if pack is null
	then
		Raise Exception Using Message = 'Акт не соответствует реестру';
	else	
		if "First" is null or "First" = '1'
		then	
			update akuz.T_SCHET schet
			set	SUMMAV = s1.SUMMAV
			,	SUMMAP = s1.SUMMAP
			,	SANK_MEK = s1.SANK_MEK
			from TmpSCHET s1
			where s1.SchetID = schet.SchetID;
		else		
			update akuz.T_SCHET schet
			set	SUMMAV = schet.SUMMAV + s1.SUMMAV
			,	SUMMAP = schet.SUMMAP + s1.SUMMAP
			,	SANK_MEK = schet.SANK_MEK + s1.SANK_MEK
			from TmpSCHET s1
			where s1.SchetID = schet.SchetID;		    
		end if;

		if "Mod" = '0'
		then
			Drop Table If Exists TmpSLUCH;
			Create Temp Table TmpSLUCH(N_ZAP int, IDCASE int, ED_COL decimal(5,2), IDSP int,TARIF decimal(15,2), SUMV decimal(15,2), SUMP decimal(15,2),REFREASON int, SANK_MEK decimal(15,2), OPLATA int);
			insert into TmpSluch(N_ZAP, IDCASE, ED_COL, IDSP,TARIF, SUMV, SUMP,REFREASON, SANK_MEK, OPLATA)
			select	nullif(trim(xpath('/ZAP/N_ZAP[1]/text()', zap)::text,'{}"'),'')::int as N_ZAP
			,	nullif(trim(xpath('/SLUCH/IDCASE[1]/text()', sluch)::text,'{}"'),'')::int as IDCASE
			,	nullif(trim(xpath('/SLUCH/ED_COL[1]/text()', sluch)::text,'{}"'),'')::decimal(5,2)
			,	nullif(trim(xpath('/SLUCH/IDSP[1]/text()', sluch)::text,'{}"'),'')::int
			,	nullif(trim(xpath('/SLUCH/TARIF[1]/text()', sluch)::text,'{}"'),'')::decimal(15,2)
			,	nullif(trim(xpath('/SLUCH/SUMV[1]/text()', sluch)::text,'{}"'),'')::decimal(15,2)
			,	nullif(trim(xpath('/SLUCH/SUMP[1]/text()', sluch)::text,'{}"'),'')::decimal(15,2)
			,	nullif(trim(xpath('/SLUCH/REFREASON[1]/text()', sluch)::text,'{}"'),'')::int
			,	nullif(trim(xpath('/SLUCH/SANK_MEK[1]/text()', sluch)::text,'{}"'),'')::decimal(15,2)
			,	nullif(trim(xpath('/SLUCH/OPLATA[1]/text()', sluch)::text,'{}"'),'')::int
			from
			(
				select	zap
				,	unnest(xpath('/ZAP/SLUCH', zap)) sluch
				from 
				(
					select	unnest(xpath('/ZL_LIST/ZAP', t)) zap
					from (select "Act" as t) as T
				) Zap
			) Sluch;
		
			update akuz.T_SCHET_SLUCH sluch
			set	ED_COL = s.ED_COL
			,	IDSP = s.IDSP
			,	TARIF = s.TARIF
			,	SUMV = s.SUMV
			,	SUMP = s.SUMP
			,	SANK_MEK = s.SANK_MEK
			,	OPLATA = s.OPLATA
			from TmpSLUCH s			
			where s.IDCASE = sluch.IDCASE
			and exists
			(
				select * from akuz.T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = pack
			);

			Drop Table If Exists TmpUSL;
			Create Temp Table TmpUSL(IDCASE int, IDSERV int, TARIF decimal(15,2), SUMV_USL decimal(15,2));
			insert into TmpUSL(IDCASE, IDSERV, TARIF, SUMV_USL)
			select	nullif(trim(xpath('/SLUCH/IDCASE[1]/text()', sluch)::text,'{}"'),'')::int
			,	nullif(trim(xpath('/USL/IDSERV[1]/text()', usl)::text,'{}"'),'')::int
			,	nullif(trim(xpath('/USL/TARIF[1]/text()', usl)::text,'{}"'),'')::decimal(15,2)
			,	nullif(trim(xpath('/USL/SUMV_USL[1]/text()', usl)::text,'{}"'),'')::decimal(15,2)
			from
			(
				select	sluch
				,	unnest(xpath('/SLUCH/USL', sluch)) usl
				from 
				(
					select	unnest(xpath('/ZL_LIST/ZAP/SLUCH', t)) sluch
					from (select "Act" as t) as T
				) Sluch
			) Usl;
			
			update akuz.T_SCHET_USL usl
			set	TARIF = u.TARIF
			,	SUMV_USL = u.SUMV_USL
			from TmpUSL u 				
			inner join 
				akuz.T_SCHET_SLUCH sluch 
				on sluch.IDCASE = u.IDCASE 				
			where u.IDSERV = usl.IDSERV
			and  sluch.SchetSluchID = usl.SchetSluch
			and exists
			(
				select * from akuz.T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = pack
			);
		
			drop table if exists TmpUSL;		

			delete from akuz.T_SCHET_SLUCH_LINK_F014 t using akuz.T_SCHET_SLUCH sluch, akuz.T_SCHET_ZAP sz
			where sluch.SchetSluchID = t.SchetSluch 
			and sz.SchetZapID = sluch.SchetZap
			and sz.SchetPack = pack;

			INSERT INTO akuz.T_SCHET_SLUCH_LINK_F014(SchetSluchLinkF014ID, SchetSluch, F014DenyReason)
			select uuid_generate_v1()
                , sluch.SchetSluchID
                , (select fdr.F014DenyReasonID
                    from AKUZ.F014_DENY_REASON fdr
                    where fdr.Kod = s.REFREASON
                    and fdr.DateBegin <= CURRENT_DATE
				    and (fdr.DateEnd is null or
				        fdr.DateEnd >= CURRENT_DATE) limit 1)
			from TmpSLUCH s
			inner join
				akuz.T_SCHET_SLUCH sluch
				on sluch.IDCASE = s.IDCASE
			where s.REFREASON is not null 
			and exists
			(
				select * from akuz.T_SCHET_ZAP sz where sluch.SchetZap = sz.SchetZapID and sz.SchetPack = pack
			)
			and exists
			(
				select * from akuz.T_F014_DENY_REASON dr where dr.Kod = s.REFREASON
			);
			
			drop table if exists TmpSLUCH;
		end if;

		if "ActMod" is not null 
		then
			perform public.sp_LoadSchetAct("Schet", "ActMod", null, 0, 1);
		end if;

	end if;
	drop table if exists TmpSCHET;
End;
$$ Language plpgsql;