DROP PROCEDURE IF EXISTS public.sp_getsetbgdoseviews;

CREATE OR REPLACE PROCEDURE public.sp_getsetbgdoseviews(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew"::boolean;
    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) := 'BILL_OF_GOODS_LINK_DOSE_VIEW';
    v_operationtype   varchar(6);
    v_ownerid         uuid;
    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
        v_ownerid := p_id;
        
        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_bill_of_goods_link_dose_view s WHERE s.billofgoodslinkdoseviewid = p_id
                     )) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = FALSE AND
               NOT EXISTS (SELECT 1 FROM pharm.t_bill_of_goods_link_dose_view s WHERE s.billofgoodslinkdoseviewid = p_id
                          )) THEN
            v_operationtype := c_delete;
            SELECT XMLELEMENT(
                       NAME "BILL_OF_GOODS_LINK_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 '<BILL_OF_GOODS_LINK_DOSE_VIEW><header><TYPE>' || c_ftload ||
                   '</TYPE></header><body><BILL_OF_GOODS_LINK_DOSE_VIEWS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''), '')
                       ||
                   '</BILL_OF_GOODS_LINK_DOSE_VIEWS></body></BILL_OF_GOODS_LINK_DOSE_VIEW>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "BILL_OF_GOODS_LINK_DOSE_VIEW",
                             XMLFOREST(
                                 dgdv.billofgoodslinkdoseviewid AS "ID",
                                 dgdv.doseview AS "DoseView",
                                 dgdv.billofgoods AS "BillOfGoods",
                                 dgdv.val AS "Val",
                                 dgdv.valtext AS "ValText",
                                 dgdv.ordernum AS "OrderNum",
                                 dgdv.additionalsymbol AS "AdditionalSymbol"
                             )
                         ) AS xml_elem
                  FROM pharm.t_bill_of_goods_link_dose_view dgdv
                 ) tt;
        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                       NAME "BILL_OF_GOODS_LINK_DOSE_VIEW",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLFOREST(
                               dgdv.doseview AS "DoseView",
                               dgdv.billofgoods AS "BillOfGoods",
                               dgdv.val AS "Val",
                               dgdv.valtext AS "ValText",
                               dgdv.ordernum AS "OrderNum",
                               dgdv.additionalsymbol AS "AdditionalSymbol"
                           )
                       )
                   )
            INTO v_result
            FROM pharm.t_bill_of_goods_link_dose_view dgdv
            WHERE dgdv.billofgoodslinkdoseviewid = 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(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));
    ELSIF (p_xml IS NOT NULL) THEN
        SELECT (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/header/TYPE/text()', p_xml))[1]::text,
               (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/header/ID/text()', p_xml))[1]::text::uuid,
               p_xml::text
        INTO v_operationtype, v_ownerid, v_result;
        
        IF (v_operationtype = c_ftload) THEN
            CREATE TEMP TABLE temp_bill_of_goods_link_dose_views AS
                SELECT (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/ID/text()', x))[1]::text::uuid             AS id,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/DoseView/text()', x))[1]::text::integer    AS doseview,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/BillOfGoods/text()', x))[1]::text::uuid    AS billofgoods,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/Val/text()', x))[1]::text::numeric(31, 10) AS val,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/ValText/text()', x))[1]::text              AS valtext,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/OrderNum/text()', x))[1]::text::integer    AS ordernum,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/AdditionalSymbol/text()', x))[1]::text     AS additionalsymbol
                FROM UNNEST(XPATH(
                    '/BILL_OF_GOODS_LINK_DOSE_VIEW/body/BILL_OF_GOODS_LINK_DOSE_VIEWS/BILL_OF_GOODS_LINK_DOSE_VIEW',
                    p_xml)) x;
            
            DELETE
            FROM pharm.t_bill_of_goods_link_dose_view s
            WHERE NOT EXISTS (SELECT 1
                              FROM temp_bill_of_goods_link_dose_views nmf
                              WHERE nmf.id = s.billofgoodslinkdoseviewid
                             );
            
            UPDATE pharm.t_bill_of_goods_link_dose_view s
            SET doseview         = nmf.doseview,
                billofgoods      = nmf.billofgoods,
                val              = nmf.val,
                ordernum         = nmf.ordernum,
                additionalsymbol = nmf.additionalsymbol,
                valtext          = nmf.valtext
            FROM temp_bill_of_goods_link_dose_views nmf
            WHERE nmf.id = s.billofgoodslinkdoseviewid;
            
            INSERT INTO
                pharm.t_bill_of_goods_link_dose_view (billofgoodslinkdoseviewid,
                                                      doseview,
                                                      billofgoods,
                                                      val,
                                                      ordernum,
                                                      additionalsymbol,
                                                      valtext)
            SELECT nmf.id,
                   nmf.doseview,
                   nmf.billofgoods,
                   nmf.val,
                   nmf.ordernum,
                   nmf.additionalsymbol,
                   nmf.valtext
            FROM temp_bill_of_goods_link_dose_views nmf
            WHERE NOT EXISTS (SELECT 1
                              FROM pharm.t_bill_of_goods_link_dose_view s
                              WHERE s.billofgoodslinkdoseviewid = nmf.id
                             );
        ELSE
            CREATE TEMP TABLE temp_bill_of_goods_link_dose_view AS
                SELECT (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/header/ID/text()', x))[1]::text::uuid           AS id,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/DoseView/text()', x))[1]::text::integer    AS doseview,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/BillOfGoods/text()', x))[1]::text::uuid    AS billofgoods,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/Val/text()', x))[1]::text::numeric(31, 10) AS val,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/ValText/text()', x))[1]::text              AS valtext,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/OrderNum/text()', x))[1]::text::integer    AS ordernum,
                       (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/AdditionalSymbol/text()', x))[1]::text     AS additionalsymbol
                FROM UNNEST(XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW', p_xml)) x;
            
            IF (v_operationtype = c_delete) THEN
                
                DELETE
                FROM pharm.t_bill_of_goods_link_dose_view s
                    USING temp_bill_of_goods_link_dose_view nmf
                WHERE nmf.id = s.billofgoodslinkdoseviewid;
            ELSE
                
                IF (v_operationtype = c_update) THEN
                    UPDATE pharm.t_bill_of_goods_link_dose_view s
                    SET doseview         = nmf.doseview,
                        billofgoods      = nmf.billofgoods,
                        val              = nmf.val,
                        ordernum         = nmf.ordernum,
                        additionalsymbol = nmf.additionalsymbol,
                        valtext          = nmf.valtext
                    FROM temp_bill_of_goods_link_dose_view nmf
                    WHERE nmf.id = s.billofgoodslinkdoseviewid;
                
                ELSIF (v_operationtype = c_insert) THEN
                    INSERT INTO
                        pharm.t_bill_of_goods_link_dose_view (billofgoodslinkdoseviewid,
                                                              doseview,
                                                              billofgoods,
                                                              val,
                                                              ordernum,
                                                              additionalsymbol,
                                                              valtext)
                    SELECT nmf.id,
                           nmf.doseview,
                           nmf.billofgoods,
                           nmf.val,
                           nmf.ordernum,
                           nmf.additionalsymbol,
                           nmf.valtext
                    FROM temp_bill_of_goods_link_dose_view nmf
                    WHERE NOT EXISTS (SELECT 1
                                      FROM pharm.t_bill_of_goods_link_dose_view s
                                      WHERE s.billofgoodslinkdoseviewid = nmf.id
                                     );
                END IF;
            END IF;
            
            INSERT INTO
                rds.t_rabbit_send_object (rabbitsendobjectid,
                                          queuename,
                                          content,
                                          messageid,
                                          headers,
                                          contentencoding,
                                          type,
                                          userid,
                                          state,
                                          createdatetime,
                                          ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   public.converttexttobinaryzip(v_result, ''),
                   NULL,
                   NULL,
                   v_contentencoding,
                   v_type,
                   p_user,
                   0,
                   p_datetime,
                   v_ownerid
            FROM rds.t_sync_servers s
            WHERE s.servername <> p_queuename;
        
        END IF;
    
    END IF;

END;
$$;