DROP PROCEDURE IF EXISTS sp_getsetlpuoverrequest;
-- ==================================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-29
-- Description: Синхронизация превышения заявки по МО
-- ==================================================
CREATE PROCEDURE sp_getsetlpuoverrequest(
    "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);
        _type            varchar(255);
        _operationtype   varchar(255);
        _ownerid         uuid;
    
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        _operationtype := 'FTLoad';
        
        IF ("Xml" IS NULL)
        THEN
            
            SELECT
                '<LPU_OVER_REQUEST><header><TYPE>' || _operationtype || '</TYPE></header><body><LPU_OVER_REQUESTS>' ||
                COALESCE(STRING_AGG(
                             XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                             ''), '')
                    ||
                '</LPU_OVER_REQUESTS></body></LPU_OVER_REQUEST>'
            INTO _result
            FROM (SELECT XMLELEMENT(
                             NAME "LPU_OVER_REQUEST",
                             XMLFOREST(
                                 lor.lpuoverrequestid AS "ID",
                                 lor.drugapplicationlistitem AS "DrugApplicationListItemID",
                                 lpu.mcod AS "LpuMainID"
                             )
                         ) AS xml_elem
                  FROM rds.t_lpu_over_request lor
                  LEFT JOIN rds.t_lpu_main    lpu ON lpu.mcod = lor.lpumain
                 ) tt;
            
            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 AS s
            WHERE ("QueueName" IS NOT NULL AND s.servername = "QueueName")
               OR ("QueueName" IS NULL AND s.servertype IN (0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
        END IF;
    END;
END;
$$;