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

CREATE PROCEDURE sp_updatebenefitsfromrds(
    "Xml" text DEFAULT NULL
, "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[1]/text()', x))[1]::text::boolean     AS actual
             , (XPATH('BENEFIT/Private[1]/text()', x))[1]::text::boolean    AS private
             , (XPATH('BENEFIT/PercentPay[1]/text()', x))[1]::text::int     AS percentpay
             , (XPATH('BENEFIT/BenefitViews[1]/text()', x))[1]::text::int   AS benefitviews
             , (XPATH('BENEFIT/BenefitType[1]/text()', x))[1]::text::int    AS benefittype
             , (XPATH('BENEFIT/CloseType[1]/text()', x))[1]::text::int      AS closetype
             , (XPATH('BENEFIT/PeriodOfAct[1]/text()', x))[1]::text::int    AS periodofact
             , (XPATH('BENEFIT/Duration[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"::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/MKBN[1]/text()', x))[1]::varchar(8)              AS mkbn,
               (XPATH('SICKNESS_GROUP/MKBK[1]/text()', x))[1]::varchar(8)              AS mkbk,
               (XPATH('SICKNESS_GROUP/SicknessGroupID[1]/text()', x))[1]::varchar(255) AS sicknessgroupid,
               (XPATH('SICKNESS_GROUP/Benefits[1]/text()', x))[1]::varchar(255)        AS benefits
        FROM (SELECT UNNEST(XPATH('/BENEFITS/BENEFIT/SICKNESS/SICKNESS_GROUP', "Xml"::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 gen_random_uuid()            --<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
        (mkbn, mkbk, sicknessgroupid, benefits)
    SELECT 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 gen_random_uuid(),--<RdsSynchronisationErrorsID, uniqueidentifier,>
           'Не удалось подобрать вид льготы!',--<ErrorDescription, text,>
           "dateTime",--<DateTime, datetime,>
           CAST("Xml" AS TEXT),--<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;

END;
$$;