-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2021-05-12
-- Description: Загрузка справочника льгот на AKUZ
-- =============================================
DROP PROCEDURE IF EXISTS sp_updatebenefitsfromrds;

CREATE PROCEDURE sp_updatebenefitsfromrds
(
	"Xml" xml DEFAULT NULL::xml
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
)
    language plpgsql
as
$$
Begin

	"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

    -- Получаем данные из XML по таблице Benefits
		Drop Table If Exists TMP_benefits;
		Create Temp Table TMP_benefits
		As
        SELECT
             (xpath('BENEFIT/BenefitsID[1]/text()', x))[1]::varchar(255) as BenefitsID
            ,(xpath('BENEFIT/BenefitCode[1]/text()', x))[1]::varchar(255) as BenefitCode
            ,(xpath('BENEFIT/BenefitName[1]/text()', x))[1]::varchar(255) as BenefitName
            ,(xpath('BENEFIT/Actual/text())[1]/text()', x))[1]::text::boolean as Actual
            ,(xpath('BENEFIT/Private/text())[1]/text()', x))[1]::text::boolean as Private
            ,(xpath('BENEFIT/PercentPay/text())[1]/text()', x))[1]::text::int as PercentPay
            ,(xpath('BENEFIT/BenefitViews/text())[1]/text()', x))[1]::text::int as BenefitViews
            ,(xpath('BENEFIT/BenefitType/text())[1]/text()', x))[1]::text::int as BenefitType
            ,(xpath('BENEFIT/CloseType/text())[1]/text()', x))[1]::text::int as CloseType
            ,(xpath('BENEFIT/PeriodOfAct/text())[1]/text()', x))[1]::text::int as PeriodOfAct
            ,(xpath('BENEFIT/Duration/text())[1]/text()', x))[1]::text::int as Duration
            ,bView.EnumID as BenefitViewsEnum
            ,bType.EnumID as BenefitTypeEnum
            ,eh1.EhrRusID as EhrRus1u0gd15h2k
            ,eh2.EhrRusID as EhrRusSdo8u3n5ro
            ,eh3.EhrRusID as EhrRusVzn438
            from
			(
				select unnest(xpath('/BENEFITS/BENEFIT', "Xml")) as x
			) BENEFIT
            left join VCLib.T_ENUMS bView on bView.EnumTypeID = 190 and bView.Code = (xpath('BENEFIT/BenefitViews[1]/text()', x))[1]::text::int
            left join VCLib.T_ENUMS bType on bType.EnumTypeID = 242 and bType.Code = (xpath('BENEFIT/BenefitType[1]/text()', x))[1]::text::int
            left join lateral (
		    	select n1.EhrRusID
		    	from EHR.T_EHR_RUS_1U0GD15H2K n1
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
				where n1.CodeEHR = (xpath('BENEFIT/EHRRUS1U0GD15H2K[1]/text()', x))[1]::varchar(255) and RRB.CURRVERSION = (xpath('BENEFIT/EHRRUS1U0GD15H2KVer[1]/text()', x))[1]::varchar(255) 
                limit 1
		    ) eh1 on true
		    left join lateral (
		    	select n1.EhrRusID
		    	from EHR.T_EHR_RUS_SDO8U3N5RO n1
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
				where n1.CodeEHR = (xpath('BENEFIT/EHRRUSSDO8U3N5RO[1]/text()', x))[1]::varchar(255) and RRB.CURRVERSION = (xpath('BENEFIT/EHRRUSSDO8U3N5ROVer[1]/text()', x))[1]::varchar(255) 
                limit 1
		    ) eh2 on true
		    left join lateral (
		    	select ehr1.EhrRusID
		    	from EHR.T_EHR_RUS_VZN438 ehr1
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
				where RRB.CURRVERSION = (xpath('BENEFIT/EhrRusVzn438Ver[1]/text()', x))[1]::varchar(255) and ehr1.IdEHR = (xpath('BENEFIT/EhrRusVzn438[1]/text()', x))[1]::text::int 
                limit 1
		    ) eh3 on true;
    -- Получаем данные из XML по таблице SICKNESS_GROUP
    
			Drop Table If Exists TMP_SICKNESS_GROUP;
			Create Temp Table TMP_SICKNESS_GROUP
			As
			select
			    (xpath('SICKNESS_GROUP/Zab/text())[1]/text()', x))[1]::varchar(3)           AS Zab,
                (xpath('SICKNESS_GROUP/MKBN/text())[1]/text()', x))[1]::varchar(8)          AS MKBN,
                (xpath('SICKNESS_GROUP/MKBK/text())[1]/text()', x))[1]::varchar(8)          AS MKBK,
                (xpath('SICKNESS_GROUP/SicknessGroupID[1]/text()', x))[1]::varchar(255)     AS SicknessGroupID,
                (xpath('SICKNESS_GROUP/Benefits/text())[1]/text()', x))[1]::varchar(255)    AS Benefits
            from
			(
				select unnest(xpath('/BENEFITS/BENEFIT/SICKNESS/SICKNESS_GROUP', "Xml")) as x
			) SICKNESS_GROUP;

    -- Переключаем в неактуальные те льготы, которых нету в XML
    update AKUZ.T_BENEFITS b
        set Actual = 0
		where not exists
		    (
		        select *
		        from TMP_benefits nb
		        where nb.BenefitCode = b.BenefitCode AND nb.BenefitViewsEnum = b.BenefitViews
		    );

    -- Обновляем имеющиеся льготы
    UPDATE AKUZ.T_BENEFITS b
        SET
            BenefitName = nb.BenefitName,
            Actual = nb.Actual,
            PercentPay = coalesce(nb.PercentPay, 100),
            Private = coalesce(nb.Private, b.Private),
            BenefitType = nb.BenefitTypeEnum,
            EhrRus1u0gd15h2k = nb.EhrRus1u0gd15h2k,
            EhrRusSdo8u3n5ro = nb.EhrRusSdo8u3n5ro,
            EhrRusVzn438 = nb.EhrRusVzn438,
            CloseType = nb.CloseType,
            PeriodOfAct = nb.PeriodOfAct,
            Duration = nb.Duration
        FROM TMP_benefits nb 
        where nb.BenefitCode = b.BenefitCode AND nb.BenefitViewsEnum = b.BenefitViews;


    -- Загружаем новые льготы
    INSERT INTO AKUZ.T_BENEFITS (BenefitsID ,BenefitViews ,BenefitCode ,BenefitName ,Actual ,PercentPay ,RecipeValidationLexem,Private, BenefitType, EhrRus1u0gd15h2k, EhrRusSdo8u3n5ro, CloseType, PeriodOfAct, Duration, EhrRusVzn438)
        SELECT
             uuid_generate_v4()	--<BenefitsID, uniqueidentifier,>
            ,nb.BenefitViewsEnum	--<BenefitViews, int,>
            ,nb.BenefitCode	--<BenefitCode, varchar(255),>
            ,nb.BenefitName	--<BenefitName, varchar(255),>
            ,nb.Actual	--<Actual, bit,>
            ,coalesce(nb.PercentPay, 100)	--<PercentPay, int,>
            ,NULL	--<RecipeValidationLexem, text,>)
            ,coalesce(nb.Private, '0')
            ,nb.BenefitTypeEnum
            ,nb.EhrRus1u0gd15h2k
            ,nb.EhrRusSdo8u3n5ro
			,nb.CloseType
            ,nb.PeriodOfAct
            ,nb.Duration
			,nb.EhrRusVzn438
        FROM TMP_benefits nb
		WHERE NOT EXISTS
		    (
		        SELECT *
		        FROM AKUZ.T_BENEFITS b
		        WHERE nb.BenefitCode = b.BenefitCode AND nb.BenefitViewsEnum = b.BenefitViews
		    );

    --Очищаем таблицу льготников перед загрузкой
    TRUNCATE TABLE AKUZ.T_SICKNESS_GROUP;

    --Загружаем новых льготников
    INSERT INTO AKUZ.T_SICKNESS_GROUP
        (ZAB, MKBN, MKBK, SicknessGroupID, Benefits)
    SELECT Zab,MKBN,MKBK,SicknessGroupID,ATB.BenefitsID
	FROM TMP_SICKNESS_GROUP SG
	    JOIN TMP_benefits B ON B.BenefitsID = SG.Benefits
	    JOIN AKUZ.T_BENEFITS ATB ON ATB.BenefitCode = B.BenefitCode AND ATB.BenefitViews = B.BenefitViewsEnum;


    -- Логируем записи с ошибками
    INSERT INTO AKUZ.T_RDS_SYNCHRONISATION_ERRORS
            (RdsSynchronisationErrorsID ,ErrorDescription ,DateTime ,Contents ,Users ,PackageType ,ErrorType ,ProcessedError ,LockResource)
    SELECT 
            uuid_generate_v4(),--<RdsSynchronisationErrorsID, uniqueidentifier,>
            'Не удалось подобрать вид льготы!',--<ErrorDescription, text,>
            "dateTime",--<DateTime, datetime,>
            CAST("Xml" AS NVARCHAR(MAX)),--<Contents, text,>
            NULL,--<Users, uniqueidentifier,>
            'BENEFITS',--<PackageType, varchar(255),>
            0,--<ErrorType, int,>
            '0',--<ProcessedError, bit,>
            NULL--<LockResource, varchar(255),>)
    FROM TMP_benefits nb
    WHERE nb.BenefitViewsEnum IS NULL limit 1;

    -- Drop Table
    DROP TABLE TMP_benefits;
End;
$$;