DROP PROCEDURE IF EXISTS sp_getsetproductform;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника форм выпуска в(из) RDS
-- =============================================
CREATE PROCEDURE sp_getsetproductform(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT NULL::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
    "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'PRODUCT_FORM';
        _operationtype   varchar(255);
        _ownerid         uuid         := "ID";
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        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_product_form WHERE productformid = "ID"))
            THEN
                _operationtype = 'Update';
            ELSIF ("IsNew" = 0 AND NOT EXISTS (SELECT * FROM pharm.t_product_form WHERE productformid = "ID"))
            THEN
                _operationtype := 'Delete';
                SELECT XMLELEMENT
                       (
                           NAME "PRODUCT_FORM",
                           XMLELEMENT
                           (
                               NAME header,
                               XMLFOREST
                               (
                                   _operationtype AS "TYPE",
                                   "ID" AS "ID"
                               )
                           )
                       )
                INTO _result;
            END IF;
            
            IF (_operationtype = 'FTLoad')
            THEN
                
                SELECT '<PRODUCT_FORM><header><TYPE>' || 'FTLoad' || '</TYPE></header><body><PRODUCT_FORMS>' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                    ''), '')
                           ||
                       '</PRODUCT_FORMS></body></PRODUCT_FORM>'
                INTO _result
                FROM (SELECT XMLELEMENT(
                                 NAME "PRODUCT_FORM",
                                 XMLFOREST(
                                     pf.productformid AS "ID",
                                     pf.ordinalnumber AS "OrdinalNumber",
                                     pf.amount AS "Amount",
                                     pf.unitprice AS "UnitPrice",
                                     pf.writeoff AS "WriteOff",
                                     pf.packagetype AS "PackageType",
                                     pf.billofgoods AS "BillOfGoods",
                                     pf.descr AS "Descr",
                                     pf.notuseinpackage AS "NotUseInPackage",
                                     pf.appointment AS "Appointment"
                                 )
                             ) AS xml_elem
                      FROM pharm.t_product_form pf
                     ) tt;
            
            ELSIF (_operationtype <> 'Delete')
            THEN
                SELECT XMLELEMENT
                       (
                           NAME "PRODUCT_FORM",
                           XMLELEMENT
                           (
                               NAME header,
                               XMLFOREST
                               (
                                   _operationtype AS "TYPE",
                                   "ID" AS "ID"
                               )
                           ),
                           XMLELEMENT
                           (
                               NAME body,
                               XMLFOREST
                               (
                                   pf.ordinalnumber AS "OrdinalNumber",
                                   pf.amount AS "Amount",
                                   pf.unitprice AS "UnitPrice",
                                   pf.writeoff AS "WriteOff",
                                   pf.packagetype AS "PackageType",
                                   pf.billofgoods AS "BillOfGoods",
                                   pf.descr AS "Descr",
                                   pf.notuseinpackage AS "NotUseInPackage",
                                   pf.appointment AS "Appointment"
                               )
                           )
                       )
                INTO _result
                FROM pharm.t_product_form pf
                WHERE pf.productformid = "ID";
            END IF; --_OperationType <> 'Delete'
            
            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),--(<RabbitSendObjectID, uniqueidentifier,>
                   s.servername,--,<QueueName, varchar(255),>
                   converttexttobinaryzip(_result, ''),--,<Content, text,>
                   NULL,--,<MessageID, varchar(255),>
                   NULL,--,<Headers, varchar(255),>
                   _contentencoding,--,<ContentEncoding, varchar(255),>
                   _type,--,<Type, varchar(255),>
                   "User",--,<UserID, varchar(255),>
                   0,--,<State, int,>)
                   "dateTime",--,<CreateDateTime, datetime,>)
                   _ownerid--,<OwnerID, varchar(255),>
            FROM rds.t_sync_servers s
            WHERE ("QueueName" IS NOT NULL AND s.servername = "QueueName")
               OR ("QueueName" IS NULL AND s.servertype IN (0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
        
        ELSIF ("Xml" IS NOT NULL)
        THEN
            _operationtype := (XPATH('/PRODUCT_FORM/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
            _ownerid := (XPATH('/PRODUCT_FORM/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
            _result = "Xml";
            
            DROP TABLE IF EXISTS tmp_product_forms;
            CREATE TEMP TABLE tmp_product_forms
            AS
                SELECT (XPATH('/PRODUCT_FORM/ID[1]/text()', x))[1]::text::uuid                 AS id,
                       (XPATH('/PRODUCT_FORM/OrdinalNumber[1]/text()', x))[1]::text::int       AS ordinalnumber,
                       (XPATH('/PRODUCT_FORM/Amount[1]/text()', x))[1]::text::decimal(12, 2)   AS amount,
                       (XPATH('/PRODUCT_FORM/UnitPrice[1]/text()', x))[1]::text::boolean       AS unitprice,
                       (XPATH('/PRODUCT_FORM/WriteOff[1]/text()', x))[1]::text::boolean        AS writeoff,
                       (XPATH('/PRODUCT_FORM/PackageType[1]/text()', x))[1]::text::uuid        AS packagetype,
                       (XPATH('/PRODUCT_FORM/BillOfGoods[1]/text()', x))[1]::text::uuid        AS billofgoods,
                       (XPATH('/PRODUCT_FORM/Descr[1]/text()', x))[1]::varchar(500)            AS descr,
                       (XPATH('/PRODUCT_FORM/NotUseInPackage[1]/text()', x))[1]::text::boolean AS notuseinpackage,
                       (XPATH('/PRODUCT_FORM/Appointment[1]/text()', x))[1]::text::boolean     AS appointment
                FROM UNNEST(XPATH('PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM', "Xml"::xml)) x
                WHERE _operationtype = 'FTLoad';
            
            DROP TABLE IF EXISTS tmp_product_form;
            CREATE TEMP TABLE tmpproduct_form
            AS
                SELECT (XPATH('/header[1]/ID[1]/text()', x1))[1]::text::uuid               AS id,
                       (XPATH('/body[1]/OrdinalNumber[1]/text()', x2))[1]::text::int       AS ordinalnumber,
                       (XPATH('/body[1]/Amount[1]/text()', x2))[1]::text::decimal(12, 2)   AS amount,
                       (XPATH('/body[1]/UnitPrice[1]/text()', x2))[1]::text::boolean       AS unitprice,
                       (XPATH('/body[1]/WriteOff[1]/text()', x2))[1]::text::boolean        AS writeoff,
                       (XPATH('/body[1]/PackageType[1]/text()', x2))[1]::text::uuid        AS packagetype,
                       (XPATH('/body[1]/BillOfGoods[1]/text()', x2))[1]::text::uuid        AS billofgoods,
                       (XPATH('/body[1]/Descr[1]/text()', x2))[1]::varchar(500)            AS descr,
                       (XPATH('/body[1]/NotUseInPackage[1]/text()', x2))[1]::text::boolean AS notuseinpackage,
                       (XPATH('/body[1]/Appointment[1]/text()', x2))[1]::text::boolean     AS appointment
                FROM (SELECT (XPATH('/PRODUCT_FORM/header', "Xml"::xml))[1] AS x1
                           , (XPATH('/PRODUCT_FORM/body', "Xml"::xml))[1]   AS x2
                     ) t1
                WHERE _operationtype <> 'FTLoad';
            
            IF (_operationtype = 'FTLoad')
            THEN
                DELETE
                FROM pharm.t_product_form
                WHERE NOT EXISTS (SELECT * FROM tmp_product_forms nmf WHERE nmf.id = productformid);
                
                UPDATE pharm.t_product_form pf
                SET ordinalnumber   = nmf.ordinalnumber,
                    amount          = nmf.amount,
                    unitprice       = nmf.unitprice,
                    writeoff        = nmf.writeoff,
                    packagetype     = nmf.packagetype,
                    billofgoods     = nmf.billofgoods,
                    descr           = nmf.descr,
                    notuseinpackage = COALESCE(nmf.notuseinpackage, '0'),
                    appointment     = COALESCE(nmf.appointment, '0')
                FROM tmp_product_forms nmf
                WHERE nmf.id = pf.productformid;
                
                -- Загружаем новые
                INSERT INTO
                    pharm.t_product_form
                (productformid,
                 ordinalnumber,
                 amount,
                 unitprice,
                 writeoff,
                 packagetype,
                 billofgoods,
                 descr,
                 notuseinpackage,
                 appointment)
                SELECT nmf.id,
                       nmf.ordinalnumber,
                       nmf.amount,
                       nmf.unitprice,
                       nmf.writeoff,
                       nmf.packagetype,
                       nmf.billofgoods,
                       nmf.descr,
                       COALESCE(nmf.notuseinpackage, '0'),
                       COALESCE(nmf.appointment, '0')
                FROM tmp_product_forms nmf
                WHERE NOT EXISTS(SELECT * FROM pharm.t_product_form pf WHERE pf.productformid = nmf.id);
            ELSIF (_operationtype = 'Delete')
            THEN
                DELETE
                FROM pharm.t_product_form
                WHERE EXISTS (SELECT * FROM tmp_product_forms nmf WHERE nmf.id = productformid);
            ELSE
                UPDATE pharm.t_product_form pf
                SET ordinalnumber   = nmf.ordinalnumber,
                    amount          = nmf.amount,
                    unitprice       = nmf.unitprice,
                    writeoff        = nmf.writeoff,
                    packagetype     = nmf.packagetype,
                    billofgoods     = nmf.billofgoods,
                    descr           = nmf.descr,
                    notuseinpackage = COALESCE(nmf.notuseinpackage, '0'),
                    appointment     = COALESCE(nmf.appointment, '0')
                FROM tmpproduct_form nmf
                WHERE nmf.id = pf.productformid
                  AND _operationtype = 'Update';
                
                INSERT INTO
                    pharm.t_product_form
                (productformid,
                 ordinalnumber,
                 amount,
                 unitprice,
                 writeoff,
                 packagetype,
                 billofgoods,
                 descr,
                 notuseinpackage,
                 appointment)
                SELECT nmf.id,
                       nmf.ordinalnumber,
                       nmf.amount,
                       nmf.unitprice,
                       nmf.writeoff,
                       nmf.packagetype,
                       nmf.billofgoods,
                       nmf.descr,
                       COALESCE(nmf.notuseinpackage, '0'),
                       COALESCE(nmf.appointment, '0')
                FROM tmp_product_forms nmf
                WHERE NOT EXISTS (SELECT * FROM pharm.t_product_form pf WHERE pf.productformid = nmf.id)
                  AND _operationtype = 'Insert';
            END IF;
            
            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),--(<RabbitSendObjectID, uniqueidentifier,>
                   s.servername,--,<QueueName, varchar(255),>
                   converttexttobinaryzip(_result, ''),--,<Content, text,>
                   NULL,--,<MessageID, varchar(255),>
                   NULL,--,<Headers, varchar(255),>
                   _contentencoding,--,<ContentEncoding, varchar(255),>
                   _type,--,<Type, varchar(255),>
                   "User",--,<UserID, varchar(255),>
                   0,--,<State, int,>)
                   "dateTime",--,<CreateDateTime, datetime,>)
                   _ownerid--,<OwnerID, varchar(255),>
            FROM rds.t_sync_servers s
            WHERE s.servername <> "QueueName"
              AND _operationtype <> 'FTLoad';
        END IF;
    END;
END;
$$;