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

DROP PROCEDURE IF EXISTS sp_rds_productform;

CREATE OR REPLACE PROCEDURE sp_rds_productform(
    "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) := '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,
                unitprice       boolean,
                writeoff        boolean,
                descr           VARCHAR(50),
                notuseinpackage boolean,
                appointment     boolean,
                EhrRusMdr308     INT,
                EhrRusMdr308Ver  VARCHAR(8)
            );
            
            _sql :=
                'insert into Temp_PRODUCT_FORM(ProductFormID, OrdinalNumber, PackageType, Amount, BillOfGoods, UnitPrice, WriteOff, Descr, NotUseInPackage,Appointment,EhrRusMdr308,EhrRusMdr308Ver); '
                    ||
                ' SELECT * FROM dblink(''dbname=' || "PATH"
                    || ''', SELECT p.ProductFormID, p.OrdinalNumber, p.PackageType, p.Amount, p.BillOfGoods, p.UnitPrice, p.WriteOff, p.Descr, p.NotUseInPackage p.Appointment,mdr.IdEHR,rb.CurrVersion
					FROM PHARM.T_PRODUCT_FORM p
                        LEFT JOIN ehr.T_EHR_RUS_MDR308 mdr ON mdr.ehrrusid = p.EhrRusMdr308
                        LEFT JOIN ehr.t_ehr_rus_ref_books  rb ON rb.ehrrusrefbooksid = mdr.ehrrusrefbooks 
                    
                    '') '
                    || ' AS pf(ProductFormID		uuid
								,OrdinalNumber		INT
								,PackageType		uuid
								,Amount				DECIMAL(12,2)
								,BillOfGoods		uuid
								,UnitPrice			boolean
								,WriteOff			boolean
								,Descr				VARCHAR(50)
								,NotUseInPackage	boolean
								,Appointment	    boolean
                                ,EhrRusMdr308       INT
                                ,EhrRusMdr308Ver    VARCHAR(8)) ';
            
            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'),
			    EhrRusMdr308	= ehr.EhrRusID
            FROM temp_product_form nmf
                    LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.T_EHR_RUS_MDR308 er
                                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = nmf.ehrRusMdr308 AND rrb.currversion = nmf.ehrRusMdr308Ver
                          LIMIT 1 ) ehr ON TRUE
            WHERE nmf.productformid = pf.productformid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_product_form(productformid,
                                     ordinalnumber,
                                     amount,
                                     unitprice,
                                     writeoff,
                                     packagetype,
                                     billofgoods,
                                     descr,
                                     notuseinpackage,
                                     appointment,
                                     ehrRusMdr308)
            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'),
                   ehr.EhrRusID
            FROM temp_product_form nmf
                    LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.T_EHR_RUS_MDR308 er
                                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = nmf.ehrRusMdr308 AND rrb.currversion = nmf.ehrRusMdr308Ver
                          LIMIT 1 ) ehr ON TRUE
            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
                    -- Первичная загрузка
                    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",
								         mdr.IdEHR    	    as "EhrRusMdr308",
								         rb.CurrVersion	    as "EhrRusMdr308Ver"
                                     )
                                 ) AS xml_elem
                          FROM pharm.t_product_form pf
                            LEFT JOIN ehr.T_EHR_RUS_MDR308 mdr ON mdr.ehrrusid = pf.EhrRusMdr308
                            LEFT JOIN ehr.t_ehr_rus_ref_books  rb ON rb.ehrrusrefbooksid = mdr.ehrrusrefbooks
                         ) tt;
                
                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",
								       mdr.IdEHR    	    as "EhrRusMdr308",
								       rb.CurrVersion	    as "EhrRusMdr308Ver"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_product_form pf
                        LEFT JOIN ehr.T_EHR_RUS_MDR308 mdr ON mdr.ehrrusid = pf.EhrRusMdr308
                        LEFT JOIN ehr.t_ehr_rus_ref_books  rb ON rb.ehrrusrefbooksid = mdr.ehrrusrefbooks
                    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);
                
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- 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,
                               (XPATH('//EhrRusMdr308[1]/text()', x))[1]::text::int        AS ehrRusMdr308,
                               (XPATH('//EhrRusMdr308Ver[1]/text()', x))[1]::text          AS ehrRusMdr308Ver
                        FROM (SELECT UNNEST(XPATH('/PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM', "Xml"::xml)) AS x
                             ) t1;
                    
                    ANALYZE tmpproduct_forms;
                    
                    DELETE
                    FROM pharm.t_product_form pf
                    WHERE NOT EXISTS(SELECT* FROM tmpproduct_forms nmf WHERE nmf.id = 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'),
			            EhrRusMdr308	= ehr.EhrRusID
                    FROM tmpproduct_forms nmf
                        LEFT JOIN LATERAL (
                            SELECT er.ehrrusid
                            FROM ehr.T_EHR_RUS_MDR308 er
                                  JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                            WHERE er.idehr = nmf.ehrRusMdr308 AND rrb.currversion = nmf.ehrRusMdr308Ver
                            LIMIT 1 ) ehr ON TRUE
                    WHERE nmf.id = pf.productformid;
                    
                    -- Загружаем новые
                    INSERT INTO
                        pharm.t_product_form(productformid,
                                             ordinalnumber,
                                             amount,
                                             unitprice,
                                             writeoff,
                                             packagetype,
                                             billofgoods,
                                             descr,
                                             notuseinpackage,
                                             appointment,
                                             ehrRusMdr308)
                    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'),
                           ehr.EhrRusID
                    FROM tmpproduct_forms nmf
                        LEFT JOIN LATERAL (
                              SELECT er.ehrrusid
                              FROM ehr.T_EHR_RUS_MDR308 er
                                    JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = nmf.ehrRusMdr308 AND rrb.currversion = nmf.ehrRusMdr308Ver
                              LIMIT 1 ) ehr ON TRUE
                    WHERE NOT EXISTS(SELECT* FROM pharm.t_product_form pf WHERE pf.productformid = nmf.id);
                ELSE
                    -- Generate table #PRODUCT_FORM
                    DROP TABLE IF EXISTS tmpproduct_form;
                    CREATE TEMP TABLE tmpproduct_form
                    AS
                        SELECT (XPATH('//ID[1]/text()', x1))[1]::text::uuid                 AS id,
                               (XPATH('//OrdinalNumber[1]/text()', x2))[1]::text::int       AS ordinalnumber,
                               (XPATH('//Amount[1]/text()', x2))[1]::text::decimal(12, 2)   AS amount,
                               (XPATH('//UnitPrice[1]/text()', x2))[1]::text::boolean       AS unitprice,
                               (XPATH('//WriteOff[1]/text()', x2))[1]::text::boolean        AS writeoff,
                               (XPATH('//PackageType[1]/text()', x2))[1]::text::uuid        AS packagetype,
                               (XPATH('//BillOfGoods[1]/text()', x2))[1]::text::uuid        AS billofgoods,
                               (XPATH('//Descr[1]/text()', x2))[1]::varchar(500)            AS descr,
                               (XPATH('//NotUseInPackage[1]/text()', x2))[1]::text::boolean AS notuseinpackage,
                               (XPATH('//Appointment[1]/text()', x2))[1]::text::boolean     AS appointment,
                               (XPATH('//EhrRusMdr308[1]/text()', x2))[1]::text::int        AS ehrRusMdr308,
                               (XPATH('//EhrRusMdr308Ver[1]/text()', x2))[1]::text          AS ehrRusMdr308Ver
                        FROM (SELECT (XPATH('/PRODUCT_FORM/header', "Xml"::xml))[1] AS x1,
                                     (XPATH('/PRODUCT_FORM/body', "Xml"::xml))[1]   AS x2
                             ) t1;
                    
                    IF _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'),
			                EhrRusMdr308	= ehr.EhrRusID
                        FROM tmpproduct_form nmf
                            LEFT JOIN LATERAL (
                                SELECT er.ehrrusid
                                FROM ehr.T_EHR_RUS_MDR308 er
                                      JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                WHERE er.idehr = nmf.ehrRusMdr308 AND rrb.currversion = nmf.ehrRusMdr308Ver
                                LIMIT 1 ) ehr ON TRUE
                        WHERE nmf.id = pf.productformid
                          AND _operationtype = _update;
                        
                        INSERT INTO
                            pharm.t_product_form(productformid,
                                                 ordinalnumber,
                                                 amount,
                                                 unitprice,
                                                 writeoff,
                                                 packagetype,
                                                 billofgoods,
                                                 descr,
                                                 notuseinpackage,
                                                 appointment,
                                                 ehrRusMdr308)
                        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'),
                               ehr.EhrRusID
                        FROM tmpproduct_form nmf
                            LEFT JOIN LATERAL (
                                SELECT er.ehrrusid
                                FROM ehr.T_EHR_RUS_MDR308 er
                                      JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                WHERE er.idehr = nmf.ehrRusMdr308 AND rrb.currversion = nmf.ehrRusMdr308Ver
                                LIMIT 1 ) ehr ON TRUE
                        WHERE NOT EXISTS(SELECT* FROM pharm.t_product_form pf WHERE pf.productformid = nmf.id)
                          AND _operationtype = _insert;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;