DROP FUNCTION IF EXISTS fn_getschetactxml;
DROP PROCEDURE IF EXISTS fn_getschetactxml;

CREATE FUNCTION public.fn_getschetactxml(
	"SchetID" uuid)
	RETURNS XML 
    LANGUAGE 'plpgsql'
AS $BODY$

declare version_ text;
declare filename text;
declare type_ int;
declare output xml;
begin

	select s.version, s.filename, s.type_ into version_, filename, type_ from ies.t_schet s where s.schetid = "SchetID" limit 1;

    select xmlconcat(
	'<?xml version="1.0" encoding="Windows-1251"?>' ::XML, 
	upper(
    xmlforest(--zl_list
        xmlconcat (
            xmlforest(--zglv
                (
	                select xmlconcat (
	                      xmlforest(VERSION)
	                    , xmlforest(DATA::date as DATA)
	                    , xmlforest(OKATO_OMS)
	                )
	                from ies.t_schet s
	                where s.schetid  = "SchetID"
                )
            as ZGLV)
            ,xmlforest(--schet
                (
                    select xmlconcat (
                       	  xmlforest(YEAR)
                        , xmlforest(MONTH)
                        , xmlforest(NSCHET)
                        , xmlforest(DSCHET::date as DSCHET)
                        , xmlforest(SUMMAV)
                        , xmlforest(SUMMAP)
                        , xmlforest((case when SANK_MEK = 0 then null else SANK_MEK end) as SANK_MEK)
                        , xmlforest((case when SANK_MEE = 0 then null else SANK_MEE end) as SANK_MEE)
                        , xmlforest((case when SANK_EKMP = 0 then null else SANK_EKMP end) as SANK_EKMP)
                    )
                    from ies.t_schet s
                    where s.schetid = "SchetID"
                )
            as SCHET)
            ,(--zap
                select xmlagg(
                    xmlforest (
                        xmlconcat (
                          xmlforest(N_ZAP)
                        , xmlforest(--pacient
                            xmlconcat (
                                  xmlforest(ID_PAC)
                                , xmlforest(VPOLIS)
                                , xmlforest(SPOLIS)
                                , xmlforest(NPOLIS)
                                , xmlforest(ENP)
                            )
                          as PACIENT)
                        , xmlforest(--zsl
                            (select
                            xmlconcat (
                                  xmlforest(IDCASE)
                                ,(--sl
                                    select xmlagg (
                                        xmlforest (
                                            xmlconcat (
                                                  xmlforest(SL_ID)
                                                , xmlforest(NHISTORY)
                                                , xmlforest(ED_COL)
                                                , xmlforest(TARIF)
                                                , case when s.type_ not in (538,555) then
                                                (--usl
                                                    select xmlagg(
                                                        xmlforest (
                                                            xmlconcat (
                                                                  xmlforest(IDSERV)
                                                                , xmlforest(TARIF)
                                                                , xmlforest(SUMV_USL)
                                                            )
                                                        as USL)
                                                    )
                                                    from ies.t_schet_usl usl
                                                    where usl.schetsluch  = sl.schetsluchid and usl.isdelete = false
                                                ) else null end
                                            )
                                        as SL)
                                    )
                                    from ies.t_schet_sluch sl
                                    where sl.schetsluchaccomplished = zsl.schetsluchaccomplishedid and sl.isdelete = false
                                )
                                , xmlforest(IDSP)
                                , xmlforest(SUMV)
                                , xmlforest(OPLATA)
                                , xmlforest(SUMP)
                                ,(--sank
	                                select xmlagg(
	                                    xmlforest (
	                                        xmlconcat (
	                                              xmlforest(S_CODE)
	                                            , xmlforest(S_SUM)
	                                            , xmlforest(S_TIP)
	                                            , (select xmlagg(xmlforest(sl.sl_id as SL_ID)) from ies.t_schet_sluch sl where zsl.schetsluchaccomplishedid = sank.schetsluchaccomplished and sl.schetsluchid = sank.schetsluch)
	                                            , xmlforest((select f014.kod from ies.t_f014_deny_reason f014 where f014.f014denyreasonid = sank.s_osn and f014.datebeg<=zsl.date_z_2 and (f014.dateend is null or f014.dateend >= zsl.date_z_2) limit 1) as S_OSN)
	                                            , xmlforest(DATE_ACT::date as DATE_ACT)
	                                            , xmlforest(NUM_ACT)
	                                            , (select xmlagg(xmlforest(ex.f004expert as CODE_EXP)) from ies.t_schet_sluch_sank_exp ex where ex.schetsluchsank = sank.schetsluchsankid)
	                                            , xmlforest(S_COM)
	                                            --, xmlforest(S_IST)
	                                            , xmlforest((case when s.type_ = 538 then 2 when s.type_ = 555 then 4 else S_IST end) as S_IST)
	                                        )
	                                    as SANK)
	                                )
	                                from ies.t_schet_sluch_sank sank
	                                where sank.schetsluchaccomplished  = zsl.schetsluchaccomplishedid 
                            	)
                            	,(--sank(schet_act_item)
	                                select xmlagg(
	                                    xmlforest (
	                                        xmlconcat (
	                                              xmlforest(dd.expertiseerrorid as S_CODE)
	                                            , xmlforest((case when dd.zsl = 1 then zsl.sank_mek else 0 end) as S_SUM)
	                                            , xmlforest(1 as S_TIP)
	                                            , (select xmlagg(xmlforest(sl.sl_id as SL_ID)) from ies.t_schet_sluch sl where zsl.schetsluchaccomplishedid = sl.schetsluchaccomplished)
	                                            , xmlforest((select f014.kod from ies.t_f014_deny_reason f014 where f014.f014denyreasonid::text = dd.fomscode and f014.datebeg<=zsl.date_z_2 and (f014.dateend is null or f014.dateend >= zsl.date_z_2) limit 1) as S_OSN)
	                                            , xmlforest(dd.createdate::date as DATE_ACT)
	                                            , xmlforest(dd.number as NUM_ACT)
	                                            , xmlforest(dd.name as S_COM)
	                                            , xmlforest((case when s.type_ = 538 then 2 when s.type_ = 555 then 4 else 1 end) as S_IST)
	                                        )
	                                    as SANK)
		                                )
		                                from(
											select ee.expertiseerrorid, ee.fomscode, ee.name, sa.createdate, sa.number
											, row_number() over (partition by ee.expertiseerrorid, ee.fomscode, ee.name order by ee.fomscode) as id --duplicates
											, row_number() over (partition by sai.schetsluchaccomplished order by ee.fomscode) as zsl, sai.schetsluchaccomplished
											from ies.t_act_item_error aie
											join ies.t_schet_act_item sai on sai.schetactitemid = aie.schetactitem 	
											join ies.t_schet_act sa on sai.schetact = sa.schetactid 
											join ies.t_expertise_error ee on ee.expertiseerrorid = aie.expertiseerror 
											where sai.schetsluchaccomplished = zsl.schetsluchaccomplishedid and sai.isaddtoact = true
											group by ee.expertiseerrorid, ee.fomscode, ee.name, sai.schetsluchaccomplished, sa.createdate, sa.number
										) dd
										where dd.id = 1
	                            	)
	                            --, xmlforest(fn_geterrorstext(select ss.schetsluchid from ies.t_schet_sluch ss where ss.schetsluchaccomplished = zsl.schetsluchaccomplishedid and ss.isdelete = false limit 1))
                                , xmlforest((case when SANK_IT = 0 then null else SANK_IT end) as SANK_IT)
                            )
                            from ies.t_schet_sluch_accomplished zsl
                            where zsl.schetzap  = zap.schetzapid and zsl.isdelete = false
                            )
                        as Z_SL)
                        )                       
                    as ZAP)
                )
                from ies.t_schet_zap zap
				join ies.t_schet s on s.schetid = zap.schet
                where s.schetid = "SchetID" and zap.isdelete = false and s.isdelete = false
            )
        )
    as ZL_LIST)::text
	)::xml) into output;
        
    return output;
   
end;
$BODY$;