DROP PROCEDURE IF EXISTS sp_getsetseries;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника серии в(из) RDS
-- =============================================
CREATE PROCEDURE sp_getsetseries(
    "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) := 'SERIES';
        _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_series WHERE seriesid = "ID"))
            THEN
                _operationtype = 'Update';
            ELSIF ("IsNew" = 0 AND NOT EXISTS (SELECT * FROM pharm.t_series WHERE seriesid = "ID"))
            THEN
                _operationtype := 'Delete';
                SELECT XMLELEMENT
                       (
                           NAME "SERIES",
                           XMLELEMENT
                           (
                               NAME header,
                               XMLFOREST
                               (
                                   _operationtype AS "TYPE",
                                   "ID" AS "ID"
                               )
                           )
                       )
                INTO _result;
            END IF;
            
            IF (_operationtype = 'FTLoad')
            THEN
                
                SELECT '<SERIES><header><TYPE>' || 'FTLoad' || '</TYPE></header><body><SERIESS>' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                    ''), '')
                           ||
                       '</SERIESS></body></SERIES>'
                INTO _result
                FROM (SELECT 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"
                                 )
                             ) AS xml_elem
                      FROM pharm.t_series s
                     ) tt;
            
            ELSIF (_operationtype <> 'Delete')
            THEN
                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
                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('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
            _ownerid := (XPATH('/MEDICATION_FORMS/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
            _result = "Xml";
            
            IF (_operationtype = 'FTLoad')
            THEN
                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;
                
                ANALYZE tmp_seriess;
                
                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 1 FROM pharm.t_series s WHERE s.seriesid = nmf.id);
            
            ELSE
                DROP TABLE IF EXISTS tmp_series;
                CREATE TEMP TABLE tmp_series
                AS
                    SELECT (XPATH('./header[1]/ID[1]/text()', t.x1))[1]::text::int                 AS id,
                           (XPATH('./body[1]/Active[1]/text()', t.x2))[1]::text::boolean           AS active,
                           (XPATH('./body[1]/SerialNumber[1]/text()', t.x2))[1]::varchar(255)      AS serialnumber,
                           TO_TIMESTAMP((XPATH('./body[1]/RegistrationDate[1]/text()', t.x2))[1]::text,
                                        'YYYY-MM-DD')                                              AS registrationdate,
                           TO_TIMESTAMP((XPATH('./body[1]/ExpirationDate[1]/text()', t.x2))[1]::text,
                                        'YYYY-MM-DD')                                              AS expirationdate,
                           TO_TIMESTAMP((XPATH('./body[1]/DurationOfCertificate[1]/text()', t.x2))[1]::text,
                                        'YYYY-MM-DD')                                              AS durationofcertificate,
                           (XPATH('./body[1]/CertificateNumber[1]/text()', t.x2))[1]::text::int    AS certificatenumber,
                           (XPATH('./body[1]/IdentificationNumber[1]/text()', t.x2))[1]::text::int AS identificationnumber,
                           (XPATH('./body[1]/BillOfGoods[1]/text()', t.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 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 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;
                
                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 IF;
    END;
END;
$$;