-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка льготников по специальной льготе из RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_getsetspecialbenefitslist;

CREATE OR REPLACE PROCEDURE aps.sp_getsetspecialbenefitslist(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" varchar(100) = 'RDSSync'
, "DateTime" TimeStamp = NULL
, "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        _operationtype varchar(36);
    BEGIN
        IF ("Xml" IS NOT NULL)
        THEN
            "DateTime" := COALESCE("DateTime", CURRENT_TIMESTAMP::TIMESTAMP);
            
            -- Получаем тип операции
            SELECT INTO
                _operationtype (XPATH('/SPECIAL_BENEFITS_REGISTR/header[1]/TYPE[1]/text()',
                                      "Xml"::xml))[1]::varchar(36);
            
            
            IF (_operationtype = 'FTLoad')
            THEN
                -- Generate table TMP_SPECIAL_BENEFITS_REGISTR
                DROP TABLE IF EXISTS tmp_special_benefits_registr;
                CREATE TEMP TABLE tmp_special_benefits_registr
                AS
                    SELECT (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/ID[1]/text()', x))[1]::text::uuid                  AS id
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/ADRESS[1]/text()', x))[1]::varchar(256)            AS adress
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/COMMENT[1]/text()', x))[1]::varchar(1024)          AS comment
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/CHANGE_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                               AS dateofchange
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DECLUSION_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                               AS dateofdeclusion
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/INCLUSION_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                               AS dateofinclusion
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/OMS_NUMBER[1]/text()', x))[1]::varchar(30)         AS omsnumber
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/OMS_SERIAL[1]/text()', x))[1]::varchar(30)         AS omsserial
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/UNIQUE_NUMBER[1]/text()', x))[1]::bigint           AS uniquenumber
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/MKB[1]/text()', x))[1]::varchar(10)                AS mkb
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)        AS diagnosis
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_SERIES[1]/text()', x))[1]::varchar(30)         AS docseries
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_NUMBER[1]/text()', x))[1]::varchar(30)         AS docnumber
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/BIRTHDAY[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                               AS birthday
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/FIRST_NAME[1]/text()', x))[1]::varchar(60)         AS firstname
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/LAST_MANE[1]/text()', x))[1]::varchar(60)          AS lastname
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/FATHER_NAME[1]/text()', x))[1]::varchar(60)        AS fathername
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_ISSUE_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                               AS docissuedate
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)     AS docissueorg
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/DOC_TYPE[1]/text()', x))[1]::text::int             AS documenttype
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/INCLUSION_LPU[1]/text()', x))[1]::varchar(6)       AS inclusionlpu
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/SEX[1]/text()', x))[1]::text::int                  AS sex
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/BIRTHDAY_LAST_NAME[1]/text()', x))[1]::varchar(60) AS birthdaylastname
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/SMO[1]/text()', x))[1]::varchar(5)                 AS f002smo
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/SNILS[1]/text()', x))[1]::varchar(14)              AS snils
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/BENIFITS[1]/text()', x))[1]::varchar(255)          AS benefits
                         , (XPATH('SPECIAL_BENEFITS_REGISTR_ITEM/BENIFITS_VIEWS[1]/text()', x))[1]::text::int       AS benefitviews
                    FROM (SELECT UNNEST(XPATH(
                        '/SPECIAL_BENEFITS_REGISTR/body/SPECIAL_BENEFITS_REGISTR_ITEMS/SPECIAL_BENEFITS_REGISTR_ITEM',
                        "Xml"::xml)) AS x
                         ) special_benefits_registr_item;
                
                ANALYZE tmp_special_benefits_registr;
                
                DELETE FROM aps.t_special_benefits_registr;
                
                INSERT INTO
                    aps.t_special_benefits_registr
                ( specialbenefitsregistrid
                , adress
                , comment
                , dateofchange
                , dateofdeclusion
                , dateofinclusion
                , omsnumber
                , omsserial
                , uniquenumber
                , mkb
                , diagnosis
                , docseries
                , docnumber
                , birthday
                , firstname
                , lastname
                , fathername
                , docissuedate
                , docissueorg
                , documenttype
                , inclusionlpu
                , sex
                , snils
                , benefits
                , birthdaylastname
                , f002smo)
                SELECT sbr.id
                     , sbr.adress
                     , sbr.comment
                     , sbr.dateofchange
                     , sbr.dateofdeclusion
                     , sbr.dateofinclusion
                     , sbr.omsnumber
                     , sbr.omsserial
                     , sbr.uniquenumber
                     , sbr.mkb
                     , sbr.diagnosis
                     , sbr.docseries
                     , sbr.docnumber
                     , sbr.birthday
                     , sbr.firstname
                     , sbr.lastname
                     , sbr.fathername
                     , sbr.docissuedate
                     , sbr.docissueorg
                     , (SELECT e.enumid
                        FROM vclib.t_enums e
                        WHERE e.enumtypeid = 105
                          AND e.code = sbr.documenttype
                        LIMIT 1
                       )
                     , sbr.inclusionlpu
                     , (SELECT e.enumid FROM vclib.t_enums e WHERE e.enumtypeid = 103 AND e.code = sbr.sex LIMIT 1)
                     , sbr.snils
                     , (SELECT b.benefitsid
                        FROM aps.t_benefits b
                        WHERE b.benefitcode = sbr.benefits
                          AND b.benefitviews = sbr.benefitviews
                        LIMIT 1
                       )
                     , sbr.birthdaylastname
                     , sbr.f002smo
                FROM tmp_special_benefits_registr sbr;
            ELSE
                -- Generate table TMP_SPECIAL_BENEFITS_REGISTR
                DROP TABLE IF EXISTS tmp_special_benefits_reg;
                CREATE TEMP TABLE tmp_special_benefits_reg
                AS
                    SELECT (XPATH('SPECIAL_BENEFITS_REGISTR/header[1]/ID[1]/text()', x))[1]::text::uuid            AS id
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/ADRESS[1]/text()', x))[1]::varchar(256)        AS adress
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/COMMENT[1]/text()', x))[1]::varchar(1024)      AS comment
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/CHANGE_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                              AS dateofchange
                         , TO_TIMESTAMP(
                        (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DECLUSION_DATE[1]/text()', x))[1]::text,
                        'YYYY.MM.DD')                                                                              AS dateofdeclusion
                         , TO_TIMESTAMP(
                        (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/INCLUSION_DATE[1]/text()', x))[1]::text,
                        'YYYY.MM.DD')                                                                              AS dateofinclusion
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/OMS_NUMBER[1]/text()', x))[1]::varchar(30)     AS omsnumber
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/OMS_SERIAL[1]/text()', x))[1]::varchar(30)     AS omsserial
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/UNIQUE_NUMBER[1]/text()', x))[1]::bigint       AS uniquenumber
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/MKB[1]/text()', x))[1]::varchar(10)            AS mkb
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)    AS diagnosis
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_SERIES[1]/text()', x))[1]::varchar(30)     AS docseries
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_NUMBER[1]/text()', x))[1]::varchar(30)     AS docnumber
                         , TO_TIMESTAMP((XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/BIRTHDAY[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                              AS birthday
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/FIRST_NAME[1]/text()', x))[1]::varchar(60)     AS firstname
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/LAST_MANE[1]/text()', x))[1]::varchar(60)      AS lastname
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/FATHER_NAME[1]/text()', x))[1]::varchar(60)    AS fathername
                         , TO_TIMESTAMP(
                        (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_ISSUE_DATE[1]/text()', x))[1]::text,
                        'YYYY.MM.DD')                                                                              AS docissuedate
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256) AS docissueorg
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/DOC_TYPE[1]/text()', x))[1]::text::int         AS documenttype
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/INCLUSION_LPU[1]/text()', x))[1]::varchar(6)   AS inclusionlpu
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/SEX[1]/text()', x))[1]::text::int              AS sex
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/BIRTHDAY_LAST_NAME[1]/text()',
                                  x))[1]::varchar(60)                                                              AS birthdaylastname
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/SMO[1]/text()', x))[1]::varchar(5)             AS f002smo
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/SNILS[1]/text()', x))[1]::varchar(14)          AS snils
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/BENIFITS[1]/text()', x))[1]::varchar(255)      AS benefits
                         , (XPATH('SPECIAL_BENEFITS_REGISTR/body[1]/BENIFITS_VIEWS[1]/text()', x))[1]::text::int   AS benefitviews
                    FROM (SELECT UNNEST(XPATH('/SPECIAL_BENEFITS_REGISTR', "Xml"::xml)) AS x
                         ) t;
                
                IF (_operationtype = 'Delete')
                THEN
                    DELETE
                    FROM aps.t_special_benefits_registr sbr
                        USING tmp_special_benefits_reg sb
                    WHERE sb.id = sbr.specialbenefitsregistrid;
                ELSE
                    UPDATE aps.t_special_benefits_registr sbr
                    SET adress           = n.adress
                      , comment          = n.comment
                      , dateofchange     = n.dateofchange
                      , dateofdeclusion  = n.dateofdeclusion
                      , dateofinclusion  = n.dateofinclusion
                      , omsnumber        = n.omsnumber
                      , omsserial        = n.omsserial
                      , uniquenumber     = n.uniquenumber
                      , mkb              = n.mkb
                      , diagnosis        = n.diagnosis
                      , docseries        = n.docseries
                      , docnumber        = n.docnumber
                      , birthday         = n.birthday
                      , firstname        = n.firstname
                      , lastname         = n.lastname
                      , fathername       = n.fathername
                      , docissuedate     = n.docissuedate
                      , docissueorg      = n.docissueorg
                      , documenttype     = (SELECT e.enumid
                                            FROM vclib.t_enums e
                                            WHERE e.enumtypeid = 105
                                              AND e.code = n.documenttype
                                            LIMIT 1
                                           )
                      , inclusionlpu     = n.inclusionlpu
                      , sex              = (SELECT e.enumid
                                            FROM vclib.t_enums e
                                            WHERE e.enumtypeid = 103
                                              AND e.code = n.sex
                                            LIMIT 1
                                           )
                      , snils            = n.snils
                      , benefits         = (SELECT b.benefitsid
                                            FROM aps.t_benefits b
                                            WHERE b.benefitcode = n.benefits
                                              AND b.benefitviews = n.benefitviews
                                            LIMIT 1
                                           )
                      , birthdaylastname = n.birthdaylastname
                      , f002smo          = n.f002smo
                    FROM tmp_special_benefits_reg n
                    WHERE n.id = sbr.specialbenefitsregistrid;
                    
                    INSERT INTO
                        aps.t_special_benefits_registr
                    ( specialbenefitsregistrid
                    , adress
                    , comment
                    , dateofchange
                    , dateofdeclusion
                    , dateofinclusion
                    , omsnumber
                    , omsserial
                    , uniquenumber
                    , mkb
                    , diagnosis
                    , docseries
                    , docnumber
                    , birthday
                    , firstname
                    , lastname
                    , fathername
                    , docissuedate
                    , docissueorg
                    , documenttype
                    , inclusionlpu
                    , sex
                    , snils
                    , benefits
                    , birthdaylastname
                    , f002smo)
                    SELECT sbr.id
                         , sbr.adress
                         , sbr.comment
                         , sbr.dateofchange
                         , sbr.dateofdeclusion
                         , sbr.dateofinclusion
                         , sbr.omsnumber
                         , sbr.omsserial
                         , sbr.uniquenumber
                         , sbr.mkb
                         , sbr.diagnosis
                         , sbr.docseries
                         , sbr.docnumber
                         , sbr.birthday
                         , sbr.firstname
                         , sbr.lastname
                         , sbr.fathername
                         , sbr.docissuedate
                         , sbr.docissueorg
                         , (SELECT e.enumid
                            FROM vclib.t_enums e
                            WHERE e.enumtypeid = 105
                              AND e.code = sbr.documenttype
                            LIMIT 1
                           )
                         , sbr.inclusionlpu
                         , (SELECT e.enumid FROM vclib.t_enums e WHERE e.enumtypeid = 103 AND e.code = sbr.sex LIMIT 1)
                         , sbr.snils
                         , (SELECT b.benefitsid
                            FROM aps.t_benefits b
                            WHERE b.benefitcode = sbr.benefits
                              AND b.benefitviews = sbr.benefitviews
                            LIMIT 1
                           )
                         , sbr.birthdaylastname
                         , sbr.f002smo
                    FROM tmp_special_benefits_reg sbr
                    WHERE NOT EXISTS
                              (SELECT *
                               FROM aps.t_special_benefits_registr s
                               WHERE s.specialbenefitsregistrid = sbr.id
                              );
                END IF;
            END IF;
        END IF;
    END;
END;
$$;