-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника доз в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_doseview;

DROP PROCEDURE IF EXISTS aps.sp_rds_doseview;

CREATE OR REPLACE PROCEDURE aps.sp_rds_doseview(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "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,
    "PATH" character varying DEFAULT NULL::character varying
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'DOSE_VIEW';
        _operationtype   varchar(255);
        _ownerid         uuid         := "ID";
        -- Operation types
        _insert          varchar(6)   := 'Insert';
        _update          varchar(6)   := 'Update';
        _delete          varchar(6)   := 'Delete';
        _ftload          varchar(6)   := 'FTLoad';
        _sql             text;
        _entid           int;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "PATH" IS NOT NULL
        THEN
            DROP TABLE IF EXISTS temp_dose_view;
            CREATE TEMP TABLE temp_dose_view
            (
                name                text,
                code                INT,
                viewtype            INT,
                otherid             INT,
                fullname            VARCHAR(4000),
                fedcode             VARCHAR(50),
                regcode             VARCHAR(50),
                ehrruss0jlhgtlbj    INT,
                ehrruss0jlhgtlbjver VARCHAR(8)
            );
            
            _sql := 'insert into Temp_DOSE_VIEW
					(
						Name,
						Code,
						ViewType,
						OtherID,
						FullName,
						FedCode,
						RegCode,
						EhrRusS0JLHGTLBJ,
						EhrRusS0JLHGTLBJVer
					);'
                        ||
                    ' SELECT dv.Name, dv.Code, dv.ViewType, dv.OtherID, dv.FullName, dv.FedCode, dv.RegCode, dv.EhrRusS0JLHGTLBJ, dv.EhrRusS0JLHGTLBJVer '
                        || 'FROM dblink(''dbname=' || "PATH"
                        || ''','
                        || ''' SELECT tdv.Name, tdv.Code, tdv.ViewType, tdv.OtherID, tdv.FullName, tdv.FedCode, tdv.RegCode, EHR.IdEHR AS EhrRusS0JLHGTLBJ, EHR.CurrVersion AS EhrRusS0JLHGTLBJVer
				FROM PHARM.T_DOSE_VIEW TDV
				left join lateral
				(
					SELECT EH.IDEHR, rrb.CurrVersion
					FROM EHR.T_EHR_RUS_S0JLHGTLBJ EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					WHERE TDV.EhrRusS0JLHGTLBJ = EH.EHRRUSID
					limit 1
				) EHR on true '') '
                || ' AS dv(Name					text,
					Code					INT,
					ViewType				INT,
					OtherID					INT,
					FullName				VARCHAR(4000),
					FedCode					VARCHAR(50),
					RegCode					VARCHAR(50),
					EhrRusS0JLHGTLBJ		INT,
					EhrRusS0JLHGTLBJVer		VARCHAR(8)) ';
            
            EXECUTE _sql;
            
            -- Обновляем имеющиеся
            UPDATE pharm.t_dose_view dv
            SET name             = dvl.name,
                fullname         = dvl.fullname,
                otherid          = dvl.otherid,
                viewtype         = dvl.viewtype,
                regcode          = dvl.regcode,
                fedcode          = 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.code = dv.code;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_dose_view
            (code, name, fullname, otherid, viewtype, regcode, fedcode, ehrruss0jlhgtlbj)
            SELECT dvl.code,
                   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 *
                       FROM pharm.t_dose_view dv
                       WHERE dv.code = dvl.code
                      );
        ELSE
            IF "Xml" IS NULL
            THEN
                --выясняем тип операции
                IF "ID" IS NULL
                THEN
                    _operationtype := _ftload;
                ELSIF "IsNew" = 1
                THEN
                    _operationtype := _insert;
                ELSIF "IsNew" = 0 AND EXISTS(SELECT* FROM pharm.t_dose_view dv WHERE dv.code = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS(SELECT* FROM pharm.t_dose_view dv WHERE dv.code = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "DOSE_VIEW",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN -- Первичная загрузка
                    SELECT '<DOSE_VIEW><header><TYPE>' || _ftload || '</TYPE></header><body><DOSE_VIEW_LIST>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                        ''), '')
                               ||
                           '</DOSE_VIEW_LIST></body></DOSE_VIEW>'
                    INTO _result
                    FROM (SELECT 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"
                                     )
                                 ) AS xml_elem
                          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
                         ) tt;
                
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем DOSE_VIEW
                    SELECT XMLELEMENT
                           (
                               NAME "DOSE_VIEW",
                               XMLELEMENT
                               (
                                   NAME header,
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME body,
                                   XMLFOREST
                                   (
                                       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 _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 = "ID";
                END IF;
                -- Отправка
                INSERT INTO
                    aps.t_rabbit_send_object
                (rabbitsendobjectid,
                 queuename,
                 content,
                 messageid,
                 headers,
                 contentencoding,
                 type,
                 userid,
                 state,
                 createdatetime,
                 ownerid)
                SELECT gen_random_uuid(),
                       "QueueName",
                       converttexttobinaryzip(_result, ''),
                       NULL,
                       NULL,
                       _contentencoding,
                       _type,
                       "User",
                       0,
                       "dateTime",
                       _ownerid;
            ELSE
                IF "Xml" IS NOT NULL
                THEN
                    -- Получаем тип операции
                    _operationtype := (XPATH('/DOSE_VIEW/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Generate table Tmp_DOSE_VIEW_LIST
                        DROP TABLE IF EXISTS tmp_dose_view_list;
                        CREATE TEMP TABLE tmp_dose_view_list
                        AS
                            SELECT (XPATH('/DOSE_VIEW/ID[1]/text()', x))[1]::text::int                   AS id,
                                   (XPATH('/DOSE_VIEW/Name[1]/text()', x))[1]::varchar(256)              AS name,
                                   (XPATH('/DOSE_VIEW/FullName[1]/text()', x))[1]::varchar(4000)         AS fullname,
                                   (XPATH('/DOSE_VIEW/OtherID[1]/text()', x))[1]::text::int              AS otherid,
                                   (XPATH('/DOSE_VIEW/ViewType[1]/text()', x))[1]::text::int             AS viewtype,
                                   (XPATH('/DOSE_VIEW/RegCode[1]/text()', x))[1]::varchar(50)            AS regcode,
                                   (XPATH('/DOSE_VIEW/FedCode[1]/text()', x))[1]::varchar(50)            AS fedcode,
                                   (XPATH('/DOSE_VIEW/EhrRusS0JLHGTLBJ[1]/text()', x))[1]::text::int     AS ehrruss0jlhgtlbj,
                                   (XPATH('/DOSE_VIEW/EhrRusS0JLHGTLBJVer[1]/text()', x))[1]::varchar(8) AS ehrruss0jlhgtlbjver
                            FROM UNNEST(XPATH('/DOSE_VIEW/body/DOSE_VIEW_LIST/DOSE_VIEW', "Xml"::xml)) AS x;
                        
                        ANALYZE tmp_dose_view_list;
                        
                        -- Обновляем имеющиеся
                        UPDATE pharm.t_dose_view dv
                        SET name             = dvl.name,
                            fullname         = dvl.fullname,
                            otherid          = dvl.otherid,
                            viewtype         = dvl.viewtype,
                            regcode          = dvl.regcode,
                            fedcode          = dvl.fedcode,
                            ehrruss0jlhgtlbj = ehr.ehrrusid
                        FROM tmp_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,
                               dvl.regcode,
                               dvl.fedcode,
                               ehr.ehrrusid
                        FROM tmp_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* FROM pharm.t_dose_view dv WHERE dv.code = dvl.id);
                    ELSE
                        -- Generate table Tmp_DOSE_VIEW
                        DROP TABLE IF EXISTS tmp_dose_view;
                        CREATE TEMP TABLE tmp_dose_view
                        AS
                            SELECT (XPATH('/header[1]/ID[1]/text()', t.x1))[1]::text::int                 AS id,
                                   (XPATH('/body[1]/Name[1]/text()', t.x2))[1]::varchar(256)              AS name,
                                   (XPATH('/body[1]/FullName[1]/text()', t.x2))[1]::varchar(4000)         AS fullname,
                                   (XPATH('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int              AS otherid,
                                   (XPATH('/body[1]/ViewType[1]/text()', t.x2))[1]::text::int             AS viewtype,
                                   (XPATH('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50)            AS regcode,
                                   (XPATH('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50)            AS fedcode,
                                   (XPATH('/body[1]/EhrRusS0JLHGTLBJ[1]/text()', t.x2))[1]::text::int     AS ehrruss0jlhgtlbj,
                                   (XPATH('/body[1]/EhrRusS0JLHGTLBJVer[1]/text()', t.x2))[1]::varchar(8) AS ehrruss0jlhgtlbjver
                            FROM (SELECT UNNEST(XPATH('/DOSE_VIEW/header', x)) AS x1,
                                         UNNEST(XPATH('/DOSE_VIEW/body', x))   AS x2
                                  FROM UNNEST(XPATH('/DOSE_VIEW', "Xml"::xml)) x
                                 ) t;
                        
                        UPDATE pharm.t_dose_view d
                        SET name             = dv.name,
                            fullname         = dv.fullname,
                            otherid          = dv.otherid,
                            viewtype         = dv.viewtype,
                            regcode          = dv.regcode,
                            fedcode          = dv.fedcode,
                            ehrruss0jlhgtlbj = ehr.ehrrusid
                        FROM tmp_dose_view dv
                        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 = dv.ehrruss0jlhgtlbj
                                        AND rrb.currversion = dv.ehrruss0jlhgtlbjver
                                      LIMIT 1
                                      )    ehr ON TRUE
                        WHERE dv.id = d.code
                          AND _operationtype = _update;
                        
                        -- Загружаем новый
                        INSERT INTO
                            pharm.t_dose_view
                        (code, name, fullname, otherid, viewtype, regcode, fedcode, ehrruss0jlhgtlbj)
                        SELECT dv.id,
                               dv.name,
                               dv.fullname,
                               dv.otherid,
                               dv.viewtype,
                               dv.regcode,
                               dv.fedcode,
                               ehr.ehrrusid
                        FROM tmp_dose_view dv
                        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 = dv.ehrruss0jlhgtlbj
                                        AND rrb.currversion = dv.ehrruss0jlhgtlbjver
                                      LIMIT 1
                                      )    ehr ON TRUE
                        WHERE NOT EXISTS
                            (SELECT *
                             FROM pharm.t_dose_view dvv
                             WHERE dvv.code = dv.id
                            )
                          AND _operationtype = _insert;
                    END IF;
                    
                    _entid := (SELECT entityid
                               FROM vclib.t_entities
                               WHERE entname = 'DOSE_VIEW'
                                 AND schema = 'PHARM'
                               LIMIT 1
                              );
                    
                    IF EXISTS (SELECT *
                               FROM vclib.t_gen_numbers
                               WHERE entityid = _entid
                                 AND numbername = 'PHARM.DOSE_VIEW/0'
                              )
                    THEN
                        ---- генерируем след. номер
                        --perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 0), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.DOSE_VIEW/0';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers
                            (gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.DOSE_VIEW/0',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_dose_view WHERE viewtype = 0), 1));
                    END IF;
                    
                    IF EXISTS (SELECT *
                               FROM vclib.t_gen_numbers
                               WHERE entityid = _entid
                                 AND numbername = 'PHARM.DOSE_VIEW/1'
                              )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 1), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.DOSE_VIEW/1';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.DOSE_VIEW/1',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_dose_view WHERE viewtype = 1), 1));
                    END IF;
                    
                    IF EXISTS (SELECT *
                               FROM vclib.t_gen_numbers
                               WHERE entityid = _entid
                                 AND numbername = 'PHARM.DOSE_VIEW/2'
                              )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 2), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.DOSE_VIEW/2';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.DOSE_VIEW/2',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_dose_view WHERE viewtype = 2), 1));
                    END IF;
                    
                    IF EXISTS (SELECT *
                               FROM vclib.t_gen_numbers
                               WHERE entityid = _entid
                                 AND numbername = 'PHARM.DOSE_VIEW/3'
                              )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 3), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.DOSE_VIEW/3';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.DOSE_VIEW/3',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_dose_view WHERE viewtype = 3), 1));
                    END IF;
                    
                    IF EXISTS (SELECT *
                               FROM vclib.t_gen_numbers
                               WHERE entityid = _entid
                                 AND numbername = 'PHARM.DOSE_VIEW/4'
                              )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 4), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.DOSE_VIEW/4';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.DOSE_VIEW/4',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_dose_view WHERE viewtype = 4), 1));
                    END IF;
                    
                    IF EXISTS (SELECT *
                               FROM vclib.t_gen_numbers
                               WHERE entityid = _entid
                                 AND numbername = 'PHARM.DOSE_VIEW/5'
                              )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_dose_view WHERE viewtype = 5), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.DOSE_VIEW/5';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.DOSE_VIEW/5',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_dose_view WHERE viewtype = 5), 1));
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;