-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка льготников по орфанным заболеваниям
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_getsetrdsorphregister;
DROP PROCEDURE IF EXISTS aps.sp_getsetrdsorphregister;
CREATE PROCEDURE aps.sp_getsetrdsorphregister(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT 'RDSSync'::character varying
, "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
, "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('/ORPHAN_REGISTER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            
            
            IF (_operationtype = 'FTLoad')
            THEN
                -- Generate table TMP_ORPHAN_REGISTERS
                DROP TABLE IF EXISTS tmp_orphan_registers;
                CREATE TEMP TABLE tmp_orphan_registers
                AS
                    SELECT (XPATH('ORPHAN_REGISTER_ITEM/ID[1]/text()', x))[1]::text::uuid               AS id
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/DATE_BEGIN[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                   AS datebegin
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/DATE_END[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                   AS dateend
                         , (XPATH('ORPHAN_REGISTER_ITEM/FIRST_NAME[1]/text()', x))[1]::varchar(60)      AS firstname
                         , (XPATH('ORPHAN_REGISTER_ITEM/LAST_NAME[1]/text()', x))[1]::varchar(60)       AS lastname
                         , (XPATH('ORPHAN_REGISTER_ITEM/BIRTH_LAST_NAME[1]/text()', x))[1]::varchar(60) AS birthlastname
                         , (XPATH('ORPHAN_REGISTER_ITEM/FATHER_NAME[1]/text()', x))[1]::varchar(60)     AS fathername
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/BIRTHDAY[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                   AS birthday
                         , (XPATH('ORPHAN_REGISTER_ITEM/SEX[1]/text()', x))[1]::text::int               AS sex
                         , (XPATH('ORPHAN_REGISTER_ITEM/SNILS[1]/text()', x))[1]::varchar(14)           AS snils
                         , (XPATH('ORPHAN_REGISTER_ITEM/DIASBILITY[1]/text()', x))[1]::text::int        AS disability
                         , (XPATH('ORPHAN_REGISTER_ITEM/ADRESS[1]/text()', x))[1]::varchar(1000)        AS adress
                         , (XPATH('ORPHAN_REGISTER_ITEM/OKATO[1]/text()', x))[1]::varchar(256)          AS okato
                         , (XPATH('ORPHAN_REGISTER_ITEM/FEDERAL[1]/text()', x))[1]::text::boolean       AS federal
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER_ITEM/DOC_ISSUE_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                   AS docissuedate
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)  AS docissueorg
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_TYPE[1]/text()', x))[1]::text::int          AS doctype
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_SERIES[1]/text()', x))[1]::varchar(30)      AS docseries
                         , (XPATH('ORPHAN_REGISTER_ITEM/DOC_NUMBER[1]/text()', x))[1]::varchar(30)      AS docnumber
                         , (XPATH('ORPHAN_REGISTER_ITEM/SMO[1]/text()', x))[1]::varchar(5)              AS f002smo
                         , (XPATH('ORPHAN_REGISTER_ITEM/OMS_SERIAL[1]/text()', x))[1]::varchar(12)      AS omsserial
                         , (XPATH('ORPHAN_REGISTER_ITEM/OMS_NUMBER[1]/text()', x))[1]::varchar(32)      AS omsnumber
                         , (XPATH('ORPHAN_REGISTER_ITEM/MKB[1]/text()', x))[1]::varchar(10)             AS mkb
                         , (XPATH('ORPHAN_REGISTER_ITEM/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)     AS diagnosis
                         , (XPATH('ORPHAN_REGISTER_ITEM/MKB_DOP[1]/text()', x))[1]::varchar(10)         AS mkbdop
                         , (XPATH('ORPHAN_REGISTER_ITEM/DIAGNOSIS_DOP[1]/text()', x))[1]::varchar(4096) AS diagnosisdop
                         , (XPATH('ORPHAN_REGISTER_ITEM/LPU_MAIN_FIRST[1]/text()', x))[1]::varchar(6)   AS lpumainfirst
                         , (XPATH('ORPHAN_REGISTER_ITEM/LPU_MAIN[1]/text()', x))[1]::varchar(6)         AS lpumain
                         , (XPATH('ORPHAN_REGISTER_ITEM/POST_FIO[1]/text()', x))[1]::varchar(255)       AS postfio
                         , (XPATH('ORPHAN_REGISTER_ITEM/REGISTER_ID[1]/text()', x))[1]::text::int       AS registerid
                    FROM (SELECT UNNEST(XPATH('/ORPHAN_REGISTER/body[1]/ORPHAN_REGISTERS/ORPHAN_REGISTER_ITEM',
                                              "Xml"::xml)) AS x
                         ) orphan_register_item;
                
                ANALYZE tmp_orphan_registers;
                
                DELETE FROM aps.t_orphan_register;
                
                INSERT INTO
                    aps.t_orphan_register
                ( orphanregisterid
                , snils
                , birthday
                , firstname
                , lastname
                , fathername
                , docissuedate
                , docissueorg
                , mkb
                , diagnosis
                , docseries
                , docnumber
                , datebegin
                , dateend
                , adress
                , sex
                , doctype
                , mkbdop
                , diagnosisdop
                , kodadr
                , birthlastname
                , disability
                , omsserial
                , omsnumber
                , f002smo
                , lpumainfirst
                , lpumain
                , federal
                , postfio
                , registerid)
                SELECT id
                     , snils
                     , birthday
                     , firstname
                     , lastname
                     , fathername
                     , docissuedate
                     , docissueorg
                     , mkb
                     , diagnosis
                     , docseries
                     , docnumber
                     , datebegin
                     , dateend
                     , adress
                     , sex
                     , doctype
                     , mkbdop
                     , diagnosisdop
                     , okato
                     , birthlastname
                     , disability
                     , omsserial
                     , omsnumber
                     , f002smo
                     , lpumainfirst
                     , lpumain
                     , federal
                     , postfio
                     , registerid
                FROM tmp_orphan_registers;
            ELSE
                -- Generate table TMP_ORPHAN_REGISTER
                DROP TABLE IF EXISTS tmp_orphan_register;
                CREATE TEMP TABLE tmp_orphan_register
                AS
                    SELECT (XPATH('ORPHAN_REGISTER/body[1]/ID[1]/text()', x))[1]::text::uuid               AS id
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/DATE_BEGIN[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      AS datebegin
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/DATE_END[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      AS dateend
                         , (XPATH('ORPHAN_REGISTER/body[1]/FIRST_NAME[1]/text()', x))[1]::varchar(60)      AS firstname
                         , (XPATH('ORPHAN_REGISTER/body[1]/LAST_NAME[1]/text()', x))[1]::varchar(60)       AS lastname
                         , (XPATH('ORPHAN_REGISTER/body[1]/BIRTH_LAST_NAME[1]/text()', x))[1]::varchar(60) AS birthlastname
                         , (XPATH('ORPHAN_REGISTER/body[1]/FATHER_NAME[1]/text()', x))[1]::varchar(60)     AS fathername
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/BIRTHDAY[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      AS birthday
                         , (XPATH('ORPHAN_REGISTER/body[1]/SEX[1]/text()', x))[1]::text::int               AS sex
                         , (XPATH('ORPHAN_REGISTER/body[1]/SNILS[1]/text()', x))[1]::varchar(14)           AS snils
                         , (XPATH('ORPHAN_REGISTER/body[1]/DIASBILITY[1]/text()', x))[1]::text::int        AS disability
                         , (XPATH('ORPHAN_REGISTER/body[1]/ADRESS[1]/text()', x))[1]::varchar(1000)        AS adress
                         , (XPATH('ORPHAN_REGISTER/body[1]/OKATO[1]/text()', x))[1]::varchar(256)          AS okato
                         , (XPATH('ORPHAN_REGISTER/body[1]/FEDERAL[1]/text()', x))[1]::text::boolean       AS federal
                         , TO_TIMESTAMP((XPATH('ORPHAN_REGISTER/body[1]/DOC_ISSUE_DATE[1]/text()', x))[1]::text,
                                        'YYYY.MM.DD')                                                      AS docissuedate
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_ISSUE_ORG[1]/text()', x))[1]::varchar(256)  AS docissueorg
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_TYPE[1]/text()', x))[1]::text::int          AS doctype
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_SERIES[1]/text()', x))[1]::varchar(30)      AS docseries
                         , (XPATH('ORPHAN_REGISTER/body[1]/DOC_NUMBER[1]/text()', x))[1]::varchar(30)      AS docnumber
                         , (XPATH('ORPHAN_REGISTER/body[1]/SMO[1]/text()', x))[1]::varchar(5)              AS f002smo
                         , (XPATH('ORPHAN_REGISTER/body[1]/OMS_SERIAL[1]/text()', x))[1]::varchar(12)      AS omsserial
                         , (XPATH('ORPHAN_REGISTER/body[1]/OMS_NUMBER[1]/text()', x))[1]::varchar(32)      AS omsnumber
                         , (XPATH('ORPHAN_REGISTER/body[1]/MKB[1]/text()', x))[1]::varchar(10)             AS mkb
                         , (XPATH('ORPHAN_REGISTER/body[1]/DIAGNOSIS[1]/text()', x))[1]::varchar(4096)     AS diagnosis
                         , (XPATH('ORPHAN_REGISTER/body[1]/MKB_DOP[1]/text()', x))[1]::varchar(10)         AS mkbdop
                         , (XPATH('ORPHAN_REGISTER/body[1]/DIAGNOSIS_DOP[1]/text()', x))[1]::varchar(4096) AS diagnosisdop
                         , (XPATH('ORPHAN_REGISTER/body[1]/LPU_MAIN_FIRST[1]/text()', x))[1]::varchar(6)   AS lpumainfirst
                         , (XPATH('ORPHAN_REGISTER/body[1]/LPU_MAIN[1]/text()', x))[1]::varchar(6)         AS lpumain
                         , (XPATH('ORPHAN_REGISTER/body[1]/POST_FIO[1]/text()', x))[1]::varchar(255)       AS postfio
                         , (XPATH('ORPHAN_REGISTER/body[1]/REGISTER_ID[1]/text()', x))[1]::text::int       AS registerid
                    FROM (SELECT UNNEST(XPATH('/ORPHAN_REGISTER', "Xml"::xml)) AS x
                         ) orphan_register;
                
                IF (_operationtype = 'Delete')
                THEN
                    DELETE
                    FROM aps.t_orphan_register orph
                        USING tmp_orphan_register tmporph
                    WHERE tmporph.id = orph.orphanregisterid;
                ELSE
                    DELETE
                    FROM aps.t_orphan_register orph
                        USING tmp_orphan_register tmporph
                    WHERE tmporph.id = orph.orphanregisterid;
                    
                    INSERT INTO
                        aps.t_orphan_register
                    ( orphanregisterid
                    , snils
                    , birthday
                    , firstname
                    , lastname
                    , fathername
                    , docissuedate
                    , docissueorg
                    , mkb
                    , diagnosis
                    , docseries
                    , docnumber
                    , datebegin
                    , dateend
                    , adress
                    , sex
                    , doctype
                    , mkbdop
                    , diagnosisdop
                    , kodadr
                    , birthlastname
                    , disability
                    , omsserial
                    , omsnumber
                    , f002smo
                    , lpumainfirst
                    , lpumain
                    , federal
                    , postfio
                    , registerid)
                    SELECT id
                         , snils
                         , birthday
                         , firstname
                         , lastname
                         , fathername
                         , docissuedate
                         , docissueorg
                         , mkb
                         , diagnosis
                         , docseries
                         , docnumber
                         , datebegin
                         , dateend
                         , adress
                         , sex
                         , doctype
                         , mkbdop
                         , diagnosisdop
                         , okato
                         , birthlastname
                         , disability
                         , omsserial
                         , omsnumber
                         , f002smo
                         , lpumainfirst
                         , lpumain
                         , federal
                         , postfio
                         , registerid
                    FROM tmp_orphan_register;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;