DROP PROCEDURE IF EXISTS public.sp_rds_doseview;

CREATE OR REPLACE PROCEDURE public.sp_rds_doseview(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "ID" int DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml" :: xml;
    p_isnew           boolean      := "IsNew";
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) = 'utf-8';
    v_type            varchar(255) := 'DOSE_VIEW';
    v_operationtype   varchar(6);
    v_ownerid         uuid         := gen_random_uuid();
    v_entid           int;
    c_insert          varchar(6)   := 'Insert';
    c_update          VARCHAR(6)   := 'Update';
    c_delete          VARCHAR(6)   := 'Delete';
    c_ftload          VARCHAR(6)   := 'FTLoad';

BEGIN
    
    IF (p_xml IS NULL) THEN
        
        IF (p_id IS NULL) THEN
            v_operationtype = c_ftload;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype = c_insert;
        ELSIF (p_isnew = FALSE AND EXISTS(SELECT 1 FROM pharm.t_dose_view dv WHERE dv.code = p_id)) THEN
            v_operationtype = c_update;
        ELSIF (p_isnew = FALSE AND NOT EXISTS (SELECT 1 FROM pharm.t_dose_view dv WHERE dv.code = p_id)) THEN
            v_operationtype = c_delete;
            SELECT XMLELEMENT
                   (
                       NAME "DOSE_VIEW",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       )
                   )
            INTO v_result;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "DOSE_VIEW",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST(c_ftload AS "TYPE")
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "DOSE_VIEW_LIST",
                               (SELECT XMLAGG(
                                           XMLELEMENT
                                           (
                                               NAME "DOSE_VIEW",
                                               XMLFOREST
                                               (
                                                   dv.code AS "ID",
                                                   dv.name AS "Name",
                                                   dv.fullname AS "FullName",
                                                   dv.otherid AS "OtherID",
                                                   dv.viewtype AS "ViewType",
                                                   dv.regcode AS "RegCode",
                                                   dv.fedcode AS "FedCode",
                                                   er.idehr AS "EhrRusS0JLHGTLBJ",
                                                   rrb.currversion AS "EhrRusS0JLHGTLBJVer"
                                               )
                                           )
                                       )
                                FROM pharm.t_dose_view dv
                                LEFT JOIN ehr.t_ehr_rus_s0jlhgtlbj er ON er.ehrrusid = dv.ehrruss0jlhgtlbj
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                               )
                           )
                       )
                   )
            INTO v_result;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "DOSE_VIEW",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               v_operationtype AS "TYPE"
                           )
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLFOREST
                           (
                               dv.code AS "ID",
                               dv.name AS "Name",
                               dv.fullname AS "FullName",
                               dv.otherid AS "OtherID",
                               dv.viewtype AS "ViewType",
                               dv.regcode AS "RegCode",
                               dv.fedcode AS "FedCode",
                               er.idehr AS "EhrRusS0JLHGTLBJ",
                               rrb.currversion AS "EhrRusS0JLHGTLBJVer"
                           )
                       )
                   )
            INTO v_result
            FROM pharm.t_dose_view dv
            LEFT JOIN ehr.t_ehr_rus_s0jlhgtlbj er ON er.ehrrusid = dv.ehrruss0jlhgtlbj
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
            WHERE dv.code = p_id;
        
        END IF;
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               p_queuename,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid;
    
    
    ELSIF (p_xml IS NOT NULL) THEN
        
        SELECT (XPATH('/DOSE_VIEW/header/TYPE', p_xml))::text
        INTO v_operationtype;
        
        IF (v_operationtype = c_ftload) THEN
            CREATE TEMP TABLE temp_dose_view_list AS
                SELECT (XPATH('/DOSE_VIEW/ID/text()', x))[1]::text::uuid                     AS id,
                       (XPATH('/DOSE_VIEW/Name/text()', x))[1]::varchar(256)                 AS name,
                       (XPATH('/DOSE_VIEW/FullName/text()', x))[1]::varchar(4000)            AS fullname,
                       (XPATH('/DOSE_VIEW/OtherID/text()', x))[1]::varchar(10)::int          AS otherid,
                       (XPATH('/DOSE_VIEW/ViewType/text()', x))[1]::varchar(10)::int         AS viewtype,
                       (XPATH('/DOSE_VIEW/RegCode/text()', x))[1]::varchar(50)               AS regcode,
                       (XPATH('/DOSE_VIEW/FedCode/text()', x))[1]::varchar(50)               AS fedcode,
                       (XPATH('/DOSE_VIEW/EhrRusS0JLHGTLBJ/text()', x))[1]::varchar(10)::int AS ehrruss0jlhgtlbj,
                       (XPATH('/DOSE_VIEW/EhrRusS0JLHGTLBJVer/text()', x))[1]::varchar(8)    AS ehrruss0jlhgtlbjver
                FROM UNNEST(XPATH('/DOSE_VIEW/body/DOSE_VIEW_LIST/DOSE_VIEW', p_xml)) x;
            
            UPDATE pharm.t_dose_view dv
            SET name             = dvl.name,
                fullname         = dvl.fullname,
                otherid          = dvl.otherid,
                viewtype         = dvl.viewtype,
                regcode          = dvl.regcode,
                fullname         = dvl.fedcode,
                ehrruss0jlhgtlbj = ehr.ehrrusid
            FROM temp_dose_view_list dvl
            LEFT JOIN LATERAL (SELECT er.ehrrusid
                               FROM ehr.t_ehr_rus_s0jlhgtlbj er
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                               WHERE er.idehr = dvl.ehrruss0jlhgtlbj
                                 AND rrb.currversion = dvl.ehrruss0jlhgtlbjver
                               LIMIT 1) ehr ON TRUE
            WHERE dvl.id = dv.code;
            
            INSERT INTO
                pharm.t_dose_view (code, name, fullname, otherid, viewtype, regcode, fedcode, ehrruss0jlhgtlbj)
            SELECT dvl.id,
                   dvl.name,
                   dvl.fullname,
                   dvl.otherid,
                   dvl.viewtype,
                   regcode,
                   fedcode,
                   ehr.ehrrusid
            FROM temp_dose_view_list dvl
            LEFT JOIN LATERAL (SELECT er.ehrrusid
                               FROM ehr.t_ehr_rus_s0jlhgtlbj er
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                               WHERE er.idehr = dvl.ehrruss0jlhgtlbj
                                 AND rrb.currversion = dvl.ehrruss0jlhgtlbjver
                               LIMIT 1) ehr ON TRUE
            WHERE NOT EXISTS (SELECT 1 FROM pharm.t_dose_view dv WHERE dv.code = dvl.id);
        
        ELSE
            CREATE TEMP TABLE temp_dose_view AS
                SELECT (XPATH('/header/DOSE_VIEW/ID/text()', x))[1]::text::uuid                   AS id,
                       (XPATH('/body/DOSE_VIEW/Name/text()', x))[1]::varchar(256)                 AS name,
                       (XPATH('/body/DOSE_VIEW/FullName/text()', x))[1]::varchar(4000)            AS fullname,
                       (XPATH('/body/DOSE_VIEW/OtherID/text()', x))[1]::varchar(10)::int          AS otherid,
                       (XPATH('/body/DOSE_VIEW/ViewType/text()', x))[1]::varchar(10)::int         AS viewtype,
                       (XPATH('/body/DOSE_VIEW/RegCode/text()', x))[1]::varchar(50)               AS regcode,
                       (XPATH('/body/DOSE_VIEW/FedCode/text()', x))[1]::varchar(50)               AS fedcode,
                       (XPATH('/body/DOSE_VIEW/EhrRusS0JLHGTLBJ/text()', x))[1]::varchar(10)::int AS ehrruss0jlhgtlbj,
                       (XPATH('/body/DOSE_VIEW/EhrRusS0JLHGTLBJVer/text()', x))[1]::varchar(8)    AS ehrruss0jlhgtlbjver
                FROM UNNEST(XPATH('/DOSE_VIEW', p_xml)) x;
            
            IF (v_operationtype = c_update) THEN
                
                UPDATE pharm.t_dose_view dv
                SET name             = dvl.name,
                    fullname         = dvl.fullname,
                    otherid          = dvl.otherid,
                    viewtype         = dvl.viewtype,
                    regcode          = dvl.regcode,
                    fullname         = dvl.fedcode,
                    ehrruss0jlhgtlbj = ehr.ehrrusid
                FROM temp_dose_view dvl
                LEFT JOIN LATERAL (SELECT er.ehrrusid
                                   FROM ehr.t_ehr_rus_s0jlhgtlbj er
                                   LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                   WHERE er.idehr = dvl.ehrruss0jlhgtlbj
                                     AND rrb.currversion = dvl.ehrruss0jlhgtlbjver
                                   LIMIT 1) ehr ON TRUE
                WHERE dvl.id = dv.code;
            
            ELSIF (v_operationtype = c_insert) THEN
                
                INSERT INTO
                    pharm.t_dose_view (code, name, fullname, otherid, viewtype, regcode, fedcode, ehrruss0jlhgtlbj)
                SELECT dvl.id,
                       dvl.name,
                       dvl.fullname,
                       dvl.otherid,
                       dvl.viewtype,
                       regcode,
                       fedcode,
                       ehr.ehrrusid
                FROM temp_dose_view dvl
                LEFT JOIN LATERAL (SELECT er.ehrrusid
                                   FROM ehr.t_ehr_rus_s0jlhgtlbj er
                                   LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                   WHERE er.idehr = dvl.ehrruss0jlhgtlbj
                                     AND rrb.currversion = dvl.ehrruss0jlhgtlbjver
                                   LIMIT 1) ehr ON TRUE
                WHERE NOT EXISTS (SELECT 1 FROM pharm.t_dose_view dv WHERE dv.code = dvl.id);
            
            END IF;
        
        END IF;
        
        SELECT entityid
        INTO v_entid
        FROM vclib.t_entities
        WHERE entname = 'DOSE_VIEW'
          AND schema = 'PHARM';
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.DOSE_VIEW/0'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 0), 1)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.DOSE_VIEW/0';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.DOSE_VIEW/0',
                    COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 0), 1));
        
        END IF;
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.DOSE_VIEW/1'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 1), 1)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.DOSE_VIEW/1';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.DOSE_VIEW/1',
                    COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 1), 1));
        
        END IF;
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.DOSE_VIEW/2'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 2), 1)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.DOSE_VIEW/2';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.DOSE_VIEW/2',
                    COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 2), 1));
        
        END IF;
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.DOSE_VIEW/3'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 3), 1)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.DOSE_VIEW/3';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.DOSE_VIEW/3',
                    COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 3), 1));
        
        END IF;
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.DOSE_VIEW/4'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 4), 1)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.DOSE_VIEW/4';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.DOSE_VIEW/4',
                    COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 4), 1));
        
        END IF;
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.DOSE_VIEW/5'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 5), 1)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.DOSE_VIEW/5';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.DOSE_VIEW/5',
                    COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 5), 1));
        
        END IF;
    END IF;
END;
$$