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

DROP PROCEDURE IF EXISTS sp_rds_series;

CREATE OR REPLACE PROCEDURE sp_rds_series(
    "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) := 'SERIES';
        _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 tmp_t_series;
            CREATE TEMP TABLE tmp_t_series
            (
                seriesid              uuid,
                serialnumber          VARCHAR(255),
                registrationdate      TIMESTAMP,
                expirationdate        TIMESTAMP,
                durationofcertificate TIMESTAMP,
                certificatenumber     VARCHAR(255),
                identificationnumber  VARCHAR(255),
                billofgoods           uuid,
                active                boolean
            );
            
            _sql := 'INSERT INTO TMP_T_SERIES(
                                SeriesID
                				,SerialNumber
                				,RegistrationDate
                				,ExpirationDate
                				,DurationOfCertificate
                				,CertificateNumber
                				,IdentificationNumber
                				,BillOfGoods
                				,Active)
                			 SELECT *
                             FROM dblink(''dbname=' || "PATH"
                || ''', SELECT
                                SeriesID
                				,SerialNumber
                				,RegistrationDate
                				,ExpirationDate
                				,DurationOfCertificate
                				,CertificateNumber
                				,IdentificationNumber
                				,BillOfGoods
                				,Active
                              from PHARM.T_SERIES sss
                              '') AS tt(
                                 SeriesID				uuid
                	            ,SerialNumber			VARCHAR(255)
                	            ,RegistrationDate		TIMESTAMP
                	            ,ExpirationDate			TIMESTAMP
                	            ,DurationOfCertificate	TIMESTAMP
                	            ,CertificateNumber		VARCHAR(255)
                	            ,IdentificationNumber	VARCHAR(255)
                	            ,BillOfGoods			uuid
                	            ,Active					boolean
                            )';
            EXECUTE _sql;
            
            --- Удаление
            DELETE
            FROM pharm.t_series s
            WHERE NOT EXISTS
                      (SELECT *
                       FROM tmp_t_series nmf
                       WHERE nmf.seriesid = s.seriesid
                      );
            
            -- Обновляем имеющиеся
            UPDATE pharm.t_series s
            SET active                = nmf.active,
                serialnumber          = nmf.serialnumber,
                registrationdate      = nmf.registrationdate,
                expirationdate        = nmf.expirationdate,
                durationofcertificate = nmf.durationofcertificate,
                certificatenumber     = nmf.certificatenumber,
                identificationnumber  = nmf.identificationnumber,
                billofgoods           = nmf.billofgoods
            FROM tmp_t_series nmf
            WHERE nmf.seriesid = s.seriesid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_series
            (seriesid,
             serialnumber,
             registrationdate,
             expirationdate,
             durationofcertificate,
             certificatenumber,
             identificationnumber,
             active,
             billofgoods)
            SELECT nmf.seriesid,
                   nmf.serialnumber,
                   nmf.registrationdate,
                   nmf.expirationdate,
                   nmf.durationofcertificate,
                   nmf.certificatenumber,
                   nmf.identificationnumber,
                   nmf.active,
                   nmf.billofgoods
            FROM tmp_t_series nmf
            WHERE NOT EXISTS
                      (SELECT *
                       FROM pharm.t_series s
                       WHERE s.seriesid = nmf.seriesid
                      );
        
        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_series c WHERE c.seriesid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS(SELECT* FROM pharm.t_series c WHERE c.seriesid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "SERIES",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF (_operationtype = _ftload)
                THEN
                    -- Первичная загрузка
                    _result :=
                        XMLELEMENT
                        (
                            NAME "SERIES",
                            XMLELEMENT
                            (
                                NAME "header",
                                XMLFOREST
                                (
                                    _ftload AS "TYPE"
                                )
                            ),
                            XMLELEMENT
                            (
                                NAME "body",
                                XMLELEMENT
                                (
                                    NAME "SERIESS",
                                    (SELECT XMLAGG
                                            (
                                                XMLELEMENT
                                                (
                                                    NAME "SERIES",
                                                    XMLFOREST
                                                    (
                                                        s.seriesid AS "ID",
                                                        s.serialnumber AS "SerialNumber",
                                                        TO_CHAR(s.registrationdate, 'YYYY-MM-DD') AS
                                                        "RegistrationDate",
                                                        TO_CHAR(s.expirationdate, 'YYYY-MM-DD') AS
                                                        "ExpirationDate",
                                                        TO_CHAR(s.durationofcertificate, 'YYYY-MM-DD') AS
                                                        "DurationOfCertificate",
                                                        s.certificatenumber AS
                                                        "CertificateNumber",
                                                        s.identificationnumber AS
                                                        "IdentificationNumber",
                                                        s.active AS "Active",
                                                        s.billofgoods AS "BillOfGoods"
                                                    )
                                                )
                                            )
                                     FROM pharm.t_series s
                                    )
                                )
                            )
                        );
                
                ELSIF (_operationtype <> _delete)
                THEN
                    -- собираем SERIES
                    SELECT XMLELEMENT
                           (
                               NAME "SERIES",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   XMLFOREST
                                   (
                                       s.seriesid AS "ID",
                                       s.serialnumber AS "SerialNumber",
                                       TO_CHAR(s.registrationdate, 'YYYY-MM-DD') AS "RegistrationDate",
                                       TO_CHAR(s.expirationdate, 'YYYY-MM-DD') AS "ExpirationDate",
                                       TO_CHAR(s.durationofcertificate, 'YYYY-MM-DD') AS
                                       "DurationOfCertificate",
                                       s.certificatenumber AS "CertificateNumber",
                                       s.identificationnumber AS "IdentificationNumber",
                                       s.active AS "Active",
                                       s.billofgoods AS "BillOfGoods"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_series s
                    WHERE s.seriesid = "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;
            ELSE
                IF ("Xml" IS NOT NULL)
                THEN
                    
                    -- Получаем тип операции
                    _operationtype := (XPATH('/SERIES/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                    
                    
                    -- Обработка операции
                    IF (_operationtype = _ftload)
                    THEN
                        -- Generate table TMP_SERIESS
                        DROP TABLE IF EXISTS tmp_seriess;
                        CREATE TEMP TABLE tmp_seriess
                        AS
                            SELECT (XPATH('SERIES/ID[1]/text()', x))[1]::text::uuid                  AS id,
                                   (XPATH('SERIES/Active[1]/text()', x))[1]::text::boolean           AS active,
                                   (XPATH('SERIES/SerialNumber[1]/text()', x))[1]::varchar(255)      AS serialnumber,
                                   TO_TIMESTAMP((XPATH('SERIES/RegistrationDate[1]/text()', x))[1]::text,
                                                'YYYY-MM-DD')                                        AS registrationdate,
                                   TO_TIMESTAMP((XPATH('SERIES/ExpirationDate[1]/text()', x))[1]::text,
                                                'YYYY-MM-DD')                                        AS expirationdate,
                                   TO_TIMESTAMP((XPATH('SERIES/DurationOfCertificate[1]/text()', x))[1]::text,
                                                'YYYY-MM-DD')                                        AS durationofcertificate,
                                   (XPATH('SERIES/CertificateNumber[1]/text()', x))[1]::text::int    AS certificatenumber,
                                   (XPATH('SERIES/IdentificationNumber[1]/text()', x))[1]::text::int AS identificationnumber,
                                   (XPATH('SERIES/BillOfGoods[1]/text()', x))[1]::text::uuid         AS billofgoods
                            FROM UNNEST(XPATH('/SERIES/body/SERIESS/SERIES', "Xml"::xml)) AS x;
                        
                        DELETE
                        FROM pharm.t_series s
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM tmp_seriess nmf
                                   WHERE nmf.id = s.seriesid
                                  );
                        
                        -- Обновляем имеющиеся
                        UPDATE pharm.t_series s
                        SET active                = nmf.active,
                            serialnumber          = nmf.serialnumber,
                            registrationdate      = nmf.registrationdate,
                            expirationdate        = nmf.expirationdate,
                            durationofcertificate = nmf.durationofcertificate,
                            certificatenumber     = nmf.certificatenumber,
                            identificationnumber  = nmf.identificationnumber,
                            billofgoods           = nmf.billofgoods
                        FROM tmp_seriess nmf
                        WHERE nmf.id = s.seriesid;
                        
                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_series
                        (seriesid,
                         serialnumber,
                         registrationdate,
                         expirationdate,
                         durationofcertificate,
                         certificatenumber,
                         identificationnumber,
                         active,
                         billofgoods)
                        SELECT nmf.id,
                               nmf.serialnumber,
                               nmf.registrationdate,
                               nmf.expirationdate,
                               nmf.durationofcertificate,
                               nmf.certificatenumber,
                               nmf.identificationnumber,
                               nmf.active,
                               nmf.billofgoods
                        FROM tmp_seriess nmf
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM pharm.t_series s
                                   WHERE s.seriesid = nmf.id
                                  );
                    ELSE
                        -- Generate table TMP_SERIES
                        DROP TABLE IF EXISTS tmp_series;
                        CREATE TEMP TABLE tmp_series
                        AS
                            SELECT (XPATH('./header[1]/ID[1]/text()', x1))[1]::text::int                 AS id,
                                   (XPATH('./body[1]/Active[1]/text()', x2))[1]::text::boolean           AS active,
                                   (XPATH('./body[1]/SerialNumber[1]/text()', x2))[1]::varchar(255)      AS serialnumber,
                                   TO_TIMESTAMP((XPATH('./body[1]/RegistrationDate[1]/text()', x2))[1]::text,
                                                'YYYY-MM-DD')                                            AS registrationdate,
                                   TO_TIMESTAMP((XPATH('./body[1]/ExpirationDate[1]/text()', x2))[1]::text,
                                                'YYYY-MM-DD')                                            AS expirationdate,
                                   TO_TIMESTAMP((XPATH('./body[1]/DurationOfCertificate[1]/text()', x2))[1]::text,
                                                'YYYY-MM-DD')                                            AS durationofcertificate,
                                   (XPATH('./body[1]/CertificateNumber[1]/text()', x2))[1]::text::int    AS certificatenumber,
                                   (XPATH('./body[1]/IdentificationNumber[1]/text()', x2))[1]::text::int AS identificationnumber,
                                   (XPATH('./body[1]/BillOfGoods[1]/text()', x2))[1]::text::uuid         AS billofgoods
                            FROM (SELECT UNNEST(XPATH('/SERIES/header', x)) AS x1,
                                         UNNEST(XPATH('/SERIES/body', x))   AS x2
                                  FROM UNNEST(XPATH('/SERIES', "Xml"::xml)) x
                                 ) t;
                        
                        IF (_operationtype = _delete)
                        THEN
                            -- Удаление
                            DELETE
                            FROM pharm.t_series s
                                USING tmp_series nmf
                            WHERE nmf.id = s.seriesid;
                        ELSE
                            -- update PHARM.T_SERIES
                            UPDATE pharm.t_series s
                            SET active                = nmf.active,
                                serialnumber          = nmf.serialnumber,
                                registrationdate      = nmf.registrationdate,
                                expirationdate        = nmf.expirationdate,
                                durationofcertificate = nmf.durationofcertificate,
                                certificatenumber     = nmf.certificatenumber,
                                identificationnumber  = nmf.identificationnumber,
                                billofgoods           = nmf.billofgoods
                            FROM tmp_series nmf
                            WHERE nmf.id = s.seriesid
                              AND _operationtype = _update;
                            
                            -- insert PHARM.T_SERIES
                            INSERT INTO
                                pharm.t_series
                            (seriesid,
                             serialnumber,
                             registrationdate,
                             expirationdate,
                             durationofcertificate,
                             certificatenumber,
                             identificationnumber,
                             active,
                             billofgoods)
                            SELECT nmf.id,
                                   nmf.serialnumber,
                                   nmf.registrationdate,
                                   nmf.expirationdate,
                                   nmf.durationofcertificate,
                                   nmf.certificatenumber,
                                   nmf.identificationnumber,
                                   nmf.active,
                                   nmf.billofgoods
                            FROM tmp_series nmf
                            WHERE NOT EXISTS
                                (SELECT *
                                 FROM pharm.t_series s
                                 WHERE s.seriesid = nmf.id
                                )
                              AND _operationtype = _insert;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;