-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника форм выпуска в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_rds_productform;

CREATE PROCEDURE sp_rds_productform(
    "Xml" text DEFAULT NULL
, "IsNew" boolean DEFAULT '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) := 'PRODUCT_FORM';
        _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;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "PATH" IS NOT NULL
        THEN
            DROP TABLE IF EXISTS temp_product_form;
            CREATE TEMP TABLE temp_product_form
            (
                productformid   uuid,
                ordinalnumber   INT,
                packagetype     uuid,
                amount          DECIMAL(12, 2),
                billofgoods     uuid,
                formtype        INT,
                unitprice       boolean,
                writeoff        boolean,
                descr           VARCHAR(50),
                notuseinpackage boolean,
                appointment     boolean
            );
            
            _sql :=
                'insert into Temp_PRODUCT_FORM(ProductFormID, OrdinalNumber, PackageType, Amount, BillOfGoods, FormType, UnitPrice, WriteOff, Descr, NotUseInPackage,Appointment); '
                    ||
                ' SELECT pf.ProductFormID, pf.OrdinalNumber, pf.PackageType, pf.Amount, pf.BillOfGoods, pf.FormType, pf.UnitPrice, pf.WriteOff, pf.Descr, pf.NotUseInPackage, pf.Appointment '
                    || 'FROM dblink(''dbname=' || "PATH"
                    || ''','
                    || ''' SELECT p.ProductFormID, p.OrdinalNumber, p.PackageType, p.Amount, p.BillOfGoods, p.FormType, p.UnitPrice, p.WriteOff, p.Descr, p.NotUseInPackage ,p.Appointment
					FROM PHARM.T_PRODUCT_FORM p '') '
                    || ' AS pf(ProductFormID		uuid
								,OrdinalNumber		INT
								,PackageType		uuid
								,Amount				DECIMAL(12,2)
								,BillOfGoods		uuid
								,FormType			INT
								,UnitPrice			boolean
								,WriteOff			boolean
								,Descr				VARCHAR(50)
								,NotUseInPackage	boolean
								,Appointment	boolean) ';
            
            EXECUTE _sql;
            
            DELETE
            FROM pharm.t_product_form pf
            WHERE NOT EXISTS(SELECT* FROM temp_product_form nmf WHERE nmf.productformid = pf.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 temp_product_form nmf
            WHERE nmf.productformid = pf.productformid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_product_form(productformid,
                                     ordinalnumber,
                                     amount,
                                     unitprice,
                                     writeoff,
                                     packagetype,
                                     billofgoods,
                                     descr,
                                     notuseinpackage,
                                     appointment)
            SELECT nmf.productformid,
                   nmf.ordinalnumber,
                   nmf.amount,
                   nmf.unitprice,
                   nmf.writeoff,
                   nmf.packagetype,
                   nmf.billofgoods,
                   nmf.descr,
                   COALESCE(nmf.notuseinpackage, '0'),
                   COALESCE(nmf.appointment, '0')
            FROM temp_product_form nmf
            WHERE NOT EXISTS(SELECT* FROM pharm.t_product_form pf WHERE pf.productformid = nmf.productformid);
        ELSE
            IF "Xml" IS NULL
            THEN
                -- Инициализация
                _ownerid := "ID";
                
                --выясняем тип операции
                IF "ID" IS NULL
                THEN
                    _operationtype := _ftload;
                ELSIF "IsNew" = '1'
                THEN
                    _operationtype := _insert;
                ELSIF "IsNew" = '0' AND EXISTS(SELECT* FROM pharm.t_product_form pf WHERE pf.productformid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = '0' AND NOT EXISTS(SELECT* FROM pharm.t_product_form pf WHERE pf.productformid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT
                               (
                        NAME "PRODUCT_FORM"
                        , XMLELEMENT
                          (
                            NAME header
                            , XMLFOREST
                              (
                                _operationtype AS "TYPE"
                                , "ID" AS "ID"
                              )
                          )
                               );
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- Первичная загрузка
                    _result :=
                        XMLELEMENT
                        (
                            NAME "PRODUCT_FORM"
                            , XMLELEMENT
                              (
                                NAME header
                                , XMLFOREST
                                  (
                                    _ftload AS "TYPE"
                                  )
                              )
                            , XMLELEMENT
                              (
                                NAME body
                                , XMLELEMENT
                                  (
                                    NAME "PRODUCT_FORMS"
                                    , (SELECT XMLAGG
                                              (
                                                  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"
                                                        )
                                                  )
                                              )
                                       FROM pharm.t_product_form pf
                                      )
                                  )
                              )
                        );
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем PRODUCT_FORM
                    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;
                
                -- Отправка
                INSERT INTO
                    akuz.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;
            ELSIF "Xml" IS NOT NULL
            THEN
                -- Получаем тип операции
                _operationtype := (XPATH('/PRODUCT_FORM/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                
                -- Generate table #PRODUCT_FORM
                DROP TABLE IF EXISTS tmpproduct_forms;
                CREATE TEMP TABLE tmpproduct_forms
                AS
                    SELECT (XPATH('//ID[1]/text()', x))[1]::text::uuid                 AS id
                         , (XPATH('//OrdinalNumber[1]/text()', x))[1]::text::int       AS ordinalnumber
                         , (XPATH('//Amount[1]/text()', x))[1]::text::decimal(12, 2)   AS amount
                         , (XPATH('//UnitPrice[1]/text()', x))[1]::text::boolean       AS unitprice
                         , (XPATH('//WriteOff[1]/text()', x))[1]::text::boolean        AS writeoff
                         , (XPATH('//PackageType[1]/text()', x))[1]::text::uuid        AS packagetype
                         , (XPATH('//BillOfGoods[1]/text()', x))[1]::text::uuid        AS billofgoods
                         , (XPATH('//Descr[1]/text()', x))[1]::varchar(500)            AS descr
                         , (XPATH('//NotUseInPackage[1]/text()', x))[1]::text::boolean AS notuseinpackage
                         , (XPATH('//Appointment[1]/text()', x))[1]::text::boolean     AS appointment
                    FROM UNNEST(XPATH('/PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM', "Xml"::xml)) AS x
                    WHERE _operationtype = _ftload;
                
                -- Generate table #PRODUCT_FORM
                DROP TABLE IF EXISTS tmpproduct_form;
                CREATE TEMP TABLE tmpproduct_form
                AS
                    SELECT (XPATH('//ID[1]/text()', t.x1))[1]::text::uuid                AS id
                         , (XPATH('//OrdinalNumber[1]/text()', t.x2))[1]::text::int      AS ordinalnumber
                         , (XPATH('//Amount[1]/text()', t.x2))[1]::text::decimal(12, 2)  AS amount
                         , (XPATH('//UnitPrice[1]/text()', t.x2))[1]::text::boolean      AS unitprice
                         , (XPATH('//WriteOff[1]/text()', t.x2))[1]::text::boolean       AS writeoff
                         , (XPATH('//PackageType[1]/text()', t.x2))[1]::text::uuid       AS packagetype
                         , (XPATH('//BillOfGoods[1]/text()', t.x2))[1]::text::uuid       AS billofgoods
                         , (XPATH('//Descr[1]/text()', t.x2))[1]::varchar(500)           AS descr
                         , (XPATH('//NotUseInPackage[1]/text()', t.x2))[1]::text::boolean AS notuseinpackage
                         , (XPATH('//Appointment[1]/text()', t.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
                         ) t
                    WHERE _operationtype <> _ftload;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    DELETE
                    FROM pharm.t_product_form f
                    WHERE NOT EXISTS(SELECT* FROM tmpproduct_forms nmf WHERE nmf.id = f.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 tmpproduct_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 tmpproduct_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 pf
                    WHERE EXISTS(SELECT* FROM tmpproduct_form nmf WHERE nmf.id = pf.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 tmpproduct_form nmf
                    WHERE NOT EXISTS(SELECT* FROM pharm.t_product_form pf WHERE pf.productformid = nmf.id)
                      AND _operationtype = _insert;
                END IF;
                
                -- Drop tables
                DROP TABLE IF EXISTS tmpproduct_form;
                DROP TABLE IF EXISTS tmpproduct_forms;
            END IF;
        END IF;
    END;
END;
$$;