-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2014-03-01
-- Description: Загрузка разрешений на выдачу рецептов из RDS
-- =============================================

DROP PROCEDURE IF EXISTS sp_rds_directionaccessxml;

CREATE PROCEDURE sp_rds_directionaccessxml(
    "Xml" text DEFAULT NULL,
    "QueueName" varchar(100) = 'RDSSync',
    "DateTime" TimeStamp = NULL,
    "LpuTo" varchar(6) = NULL
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    "DateTime" := COALESCE("DateTime", CURRENT_TIMESTAMP::TIMESTAMP);
    
    IF "Xml" IS NOT NULL
    THEN
        -- Получаем тип операции
        "LpuTo" := NULLIF(TRIM(XPATH('/ACCESSES/header[1]/LpuMain[1]/text()', "Xml"::xml)::varchar(6), '{}"'), '');
        
        IF "LpuTo" IS NULL
        THEN
            DELETE FROM akuz.t_dlo_accessed_posts;
        ELSE
            DELETE
            FROM akuz.t_dlo_accessed_posts dap
                USING akuz.t_lpu_main lm
            WHERE dap.lpumain = lm.lpuid
              AND lm.lpucode = "LpuTo";
        END IF;
        
        -- Generate table Tmp_DLO_ACCESSED_POSTS
        DROP TABLE IF EXISTS tmp_dlo_accessed_posts;
        CREATE TEMP TABLE tmp_dlo_accessed_posts
        (
            id                    uuid,
            postcode              varchar(255),
            lastname              varchar(32),
            firstname             varchar(32),
            fathername            varchar(32),
            category              int,
            beginaccessdate       TimeStamp,
            endaccessdate         TimeStamp,
            expulsiondate         TimeStamp,
            beginworkdate         TimeStamp,
            certificatedate       TimeStamp,
            medicalspecialitycode varchar(16),
            posttype              uuid,
            speciality            varchar(3),
            lpumain               uuid,
            snils                 varchar(14)
        );
        INSERT INTO
            tmp_dlo_accessed_posts( id
                                  , postcode
                                  , lastname
                                  , firstname
                                  , fathername
                                  , category
                                  , beginaccessdate
                                  , endaccessdate
                                  , expulsiondate
                                  , beginworkdate
                                  , certificatedate
                                  , medicalspecialitycode
                                  , posttype
                                  , speciality
                                  , lpumain
                                  , snils)
        SELECT dap.id
             , dap.postcode
             , dap.lastname
             , dap.firstname
             , dap.fathername
             , dap.category
             , dap.beginaccessdate
             , dap.endaccessdate
             , dap.expulsiondate
             , dap.beginworkdate
             , dap.certificatedate
             , dap.medicalspecialitycode
             , pt.posttypeid AS posttype
             , dap.speciality
--		,	nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/Speciality[1]/text()', XmlVar)::text,'{}"'),'')::varchar(3) as Speciality
             , lm.lpuid      AS lpumain
             , sn.snils
        FROM akuz.t_lpu_main       lm
        JOIN
                  (SELECT NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/ID[1]/text()', x)::text, '{}"'), '')::uuid AS id
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/PostCode[1]/text()', x)::text, '{}"'),
                                 '')::varchar(255)                                                          AS postcode
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/LastName[1]/text()', x)::text, '{}"'),
                                 '')::varchar(32)                                                           AS lastname
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/FirstName[1]/text()', x)::text, '{}"'),
                                 '')::varchar(32)                                                           AS firstname
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/FatherName[1]/text()', x)::text, '{}"'),
                                 '')::varchar(32)                                                           AS fathername
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/Category[1]/text()', x)::text, '{}"'),
                                 '')::int                                                                   AS category
                        , TO_TIMESTAMP(
                          NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/BeginAccessDate[1]/text()', x)::text, '{}"'), ''),
                          'YYYY-MM-DD HH:MI:SS.MS')                                                         AS beginaccessdate
                        , TO_TIMESTAMP(
                          NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/EndAccessDate[1]/text()', x)::text, '{}"'), ''),
                          'YYYY-MM-DD HH:MI:SS.MS')                                                         AS endaccessdate
                        , TO_TIMESTAMP(
                          NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/ExpulsionDate[1]/text()', x)::text, '{}"'), ''),
                          'YYYY-MM-DD HH:MI:SS.MS')                                                         AS expulsiondate
                        , TO_TIMESTAMP(
                          NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/BeginWorkDate[1]/text()', x)::text, '{}"'), ''),
                          'YYYY-MM-DD HH:MI:SS.MS')                                                         AS beginworkdate
                        , TO_TIMESTAMP(
                          NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/CertificateDate[1]/text()', x)::text, '{}"'), ''),
                          'YYYY-MM-DD HH:MI:SS.MS')                                                         AS certificatedate
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/MedicalSpecialityCode[1]/text()', x)::text, '{}"'),
                                 '')::varchar(16)                                                           AS medicalspecialitycode
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/Speciality[1]/text()', x)::text, '{}"'),
                                 '')::varchar(3)                                                            AS speciality
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/PostType[1]/text()', x)::text, '{}"'),
                                 '')::varchar(4)                                                            AS posttype
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/Snils[1]/text()', x)::text, '{}"'),
                                 '')::char(14)                                                              AS snils
                        , NULLIF(TRIM(XPATH('/DLO_ACCESSED_POSTS/LpuMain[1]/text()', x)::text, '{}"'),
                                 '')::varchar(6)                                                            AS lpumain
                   FROM (SELECT UNNEST(XPATH('/ACCESSES/body/DLO_ACCESSED_POSTS', x)) AS x
                         FROM (SELECT "Xml"::xml AS x
                              ) root
                        ) dlo_accessed_posts
                  )                dap ON dap.lpumain = lm.lpucode
        LEFT JOIN akuz.t_post_type pt
                      ON pt.code = dap.posttype --nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/PostType[1]/text()', XmlVar)::text,'{}"'),'')::varchar(4)
        LEFT JOIN akuz.t_snils     sn ON sn.snils = dap.snils --nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/Snils[1]/text()', XmlVar)::text,'{}"'),'')::char(14)
        WHERE dap.postcode LIKE lm.code || '%';
        --lm.LpuCode = nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/LpuMain[1]/text()', XmlVar)::text,'{}"'),'')::varchar(6)
        --and nullif(trim(xpath('/ACCESSES/body/DLO_ACCESSED_POSTS/PostCode[1]/text()', XmlVar)::text,'{}"'),'')::varchar(255) like lm.Code || '%';
        
        -- INSERT akuz.T_DLO_ACCESSED_POSTS
        INSERT INTO
            akuz.t_dlo_accessed_posts( dloaccessedpostsid
                                     , lpumain
                                     , postcode
                                     , lastname
                                     , firstname
                                     , fathername
                                     , speciality
                                     , posttype
                                     , category
                                     , beginaccessdate
                                     , endaccessdate
                                     , updateinfodate
                                     , beginworkdate
                                     , certificatedate
                                     , medicalspecialitycode
                                     , expulsiondate
                                     , snils)
        SELECT dap.id
             , dap.lpumain
             , dap.postcode
             , dap.lastname
             , dap.firstname
             , dap.fathername
             , (SELECT s.specialityid FROM akuz.t_speciality s WHERE s.code = dap.speciality LIMIT 1)
             , dap.posttype
             , dap.category
             , dap.beginaccessdate
             , dap.endaccessdate
             , "DateTime"
             , dap.beginworkdate
             , dap.certificatedate
             , dap.medicalspecialitycode
             , dap.expulsiondate
             , dap.snils
        FROM tmp_dlo_accessed_posts dap;
    END IF;
END;
$$;