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

CREATE PROCEDURE sp_getsetspecialbenefitslist(
    "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);
        p_xml          xml := "Xml" :: xml;
    BEGIN
        IF (p_xml IS NOT NULL)
        THEN
            "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
            
            -- Получаем тип операции
            SELECT INTO
                _operationtype (XPATH('/SPECIAL_BENEFITS_REGISTR/header[1]/TYPE[1]/text()', p_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]::varchar(128)     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',
                        p_xml::xml)) AS x
                         ) special_benefits_registr_item;
                
                ALTER TABLE tmp_special_benefits_registr
                    ADD patient uuid;
                
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                UPDATE tmp_special_benefits_registr nos
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.snils = nos.snils
                  AND p.birthday = nos.birthday
                  AND nos.patient IS NULL
                  AND nos.snils IS NOT NULL
                  AND nos.birthday IS NOT NULL;
                
                -- Поиск по серии и номеру документа
                UPDATE tmp_special_benefits_registr nos
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE LEFT(p.docseries, 8) = nos.docseries
                  AND LEFT(p.docnumber, 8) = nos.docnumber
                  AND p.birthday = nos.birthday
                  AND nos.patient IS NULL
                  AND nos.docseries IS NOT NULL
                  AND nos.docnumber IS NOT NULL
                  AND nos.birthday IS NOT NULL;
                
                -- Поиск по ФИО + ДР
                UPDATE tmp_special_benefits_registr nos
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.lastname = nos.lastname
                  AND p.firstname = nos.firstname
                  AND (p.fathername = nos.fathername OR p.fathername IS NULL AND nos.fathername IS NULL)
                  AND p.birthday = nos.birthday
                  AND nos.patient IS NULL;
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING akuz.t_benefits b
                WHERE b.benefitsid = pd.benefits
                  AND b.benefitviews = 19009
                  AND createtype = 2;
                
                INSERT INTO
                    akuz.t_patient_benefits
                ( patientbenefitsid
                , patient
                , benefits
                , benefitdocument
                , benefitdocumentnumber
                , begindate
                , enddate
                , mkb
                , active
                , deleted
                , createtype
                , pac_id
                , lpu_inf
                , sost_r
                , reasonregistration
                , reasonderegistration
                , benefitdocumentseries
                , benefitdoctype
                , regdate)
                SELECT gen_random_uuid()
                     , nos.patient
                     , (SELECT b.benefitsid
                        FROM akuz.t_benefits b
                        WHERE b.benefitcode = nos.benefits
                          AND b.benefitviews = 19009
                        LIMIT 1
                       )
                     , NULL
                     , NULL
                     , nos.dateofinclusion
                     , nos.dateofdeclusion
                     , (SELECT m.mkbid FROM akuz.t_mkb m WHERE nos.mkb = m.mcbcode LIMIT 1)
                     , '1'
                     , '0'
                     , 2
                     , nos.uniquenumber
                     , nos.inclusionlpu
                     , NULL
                     , NULL
                     , NULL
                     , NULL
                     , NULL
                     , nos.dateofinclusion
                FROM tmp_special_benefits_registr nos
                WHERE nos.patient IS NOT NULL;
            
            ELSIF (_operationtype = 'Delete')
            THEN
                -- Generate table TMP_SPECIAL_BENEFITS_REG
                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]::varchar(128) 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', p_xml::xml)) AS x
                         ) t;
                
                
                ALTER TABLE tmp_special_benefits_reg
                    ADD patient uuid;
                
                
                -- Поиск по СНИЛС + ДР
                UPDATE tmp_special_benefits_reg nos
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.snils = nos.snils
                  AND p.birthday = nos.birthday
                  AND nos.patient IS NULL
                  AND nos.snils IS NOT NULL
                  AND nos.birthday IS NOT NULL;
                
                -- Поиск по серии и номеру документа
                UPDATE tmp_special_benefits_reg nos
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE LEFT(p.docseries, 8) = nos.docseries
                  AND LEFT(p.docnumber, 8) = nos.docnumber
                  AND p.birthday = nos.birthday
                  AND nos.patient IS NULL
                  AND nos.docseries IS NOT NULL
                  AND nos.docnumber IS NOT NULL
                  AND nos.birthday IS NOT NULL;
                
                -- Поиск по ФИО + ДР
                UPDATE tmp_special_benefits_reg nos
                SET patient = p.patientid
                FROM akuz.t_patient p
                WHERE p.lastname = nos.lastname
                  AND p.firstname = nos.firstname
                  AND (p.fathername = nos.fathername OR p.fathername IS NULL AND nos.fathername IS NULL)
                  AND p.birthday = nos.birthday
                  AND nos.patient IS NULL;
                
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING akuz.t_benefits b
                        JOIN tmp_special_benefits_reg nos ON nos.patient = pd.patient
                WHERE b.benefitsid = pd.benefits
                  AND b.benefitviews = 19009
                  AND pd.createtype = 2
                  AND b.benefitcode = nos.benefits;
            
            ELSE
                
                DELETE
                FROM akuz.t_patient_benefits pd
                    USING akuz.t_benefits b
                        JOIN tmp_special_benefits_reg nos ON nos.patient = pd.patient
                WHERE b.benefitsid = pd.benefits
                  AND b.benefitviews = 19009
                  AND pd.createtype = 2
                  AND b.benefitcode = nos.benefits;
                
                INSERT INTO
                    akuz.t_patient_benefits
                ( patientbenefitsid
                , patient
                , benefits
                , benefitdocument
                , benefitdocumentnumber
                , begindate
                , enddate
                , mkb
                , active
                , deleted
                , createtype
                , pac_id
                , lpu_inf
                , sost_r
                , reasonregistration
                , reasonderegistration
                , benefitdocumentseries
                , benefitdoctype
                , regdate)
                SELECT gen_random_uuid()
                     , nos.patient
                     , (SELECT b.benefitsid
                        FROM akuz.t_benefits b
                        WHERE b.benefitcode = nos.benefits
                          AND b.benefitviews = 19009
                        LIMIT 1
                       )
                     , NULL
                     , NULL
                     , nos.dateofinclusion
                     , nos.dateofdeclusion
                     , (SELECT m.mkbid FROM akuz.t_mkb m WHERE nos.mkb = m.mcbcode LIMIT 1)
                     , '1'
                     , '0'
                     , 2
                     , nos.uniquenumber
                     , nos.inclusionlpu
                     , NULL
                     , NULL
                     , NULL
                     , NULL
                     , NULL
                     , nos.dateofinclusion
                FROM tmp_special_benefits_reg nos
                WHERE nos.patient IS NOT NULL;
            
            END IF;
        
        END IF;
    END;
END;
$$;