DROP PROCEDURE IF EXISTS DBO.sp_rds_sentremains;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-21
-- Description: Выгрузка/загрузка остатков в(из) RDS
-- =============================================
CREATE OR REPLACE PROCEDURE APS.sp_rds_sentremains
(
    "Xml" xml = null  
    ,"IsNew" boolean = true
 	,"ID" uuid DEFAULT NULL::uuid
    ,"QueueName" varchar(100) = 'RDSSync'
    ,"DateTime" TimeStamp = null
 	,"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
 Begin
    declare 
        _OperationType varchar(255);
        _OwnerID uuid default null::uuid;
        _Result text;
        _Type varchar(255);
        _ContentEncoding varchar(255);
        _rsoID uuid = uuid_generate_v4();
    begin
        "DateTime" := coalesce("DateTime", Current_TimeStamp::TIMESTAMP);  

    IF (@Xml is null)
    then
        -- Инициализация
        _ContentEncoding := 'utf-8';
        _Type := 'SENT_REMAIN';
        _OwnerID := "ID";

        --выясняем тип операции
        if "ID" is null and "Xml" is null
        then
            _OperationType := 'FTSEND';
        elsif not exists (select * from STOR.T_REMAINS r join APS.T_SENT_REMAIN sr on sr.Remains = r.RemainsID and sr.SentRemainID = "ID")
		then
			_OperationType := 'DELETE';
            select XMLELEMENT(name "SENT_REMAIN", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", sr.Remains as "ID"))) 
            into _Result
                from APS.T_SENT_REMAIN sr where SentRemainID = "ID";
        else
             _OperationType := 'UPDATE';
        end if;

        -- Обработка операции
        IF (_OperationType = 'FTSEND')
        then
            select 
            xmlelement
            (
                name "SENT_REMAIN",
                xmlelement
                (
                    name header,
                    xmlforest('FTSEND' as "TYPE")
                ),
                xmlelement
                (
                    name body,
                    xmlelement
                    (
                        name "SENT_REMAINS",
                        (
                        select xmlagg
                        (
                            xmlelement
                                (
                            name "SENT_REMAIN",
                            
                                    xmlforest
                                    (
                                          r.RemainsID                                        as "ID"
                                          ,r.Price                                           as "Price"
                                          ,r.LotNumber                                       as "LotNumber"
                                          ,r.Series                                          as "Series"
                                          ,r.Comment                                         as "Comment"
                                          ,to_char(r.ExpirationDate, 'dd.MM.yyyy')      as "ExpirationDate"
                                          ,r.Amount                                          as "Amount"
                                          ,s.Name                                            as "Storage"
                                          ,pip.Pharmacy                                      as "Pharmacy"
                                          ,r.BillOfGoods                                     as "BillOfGoods"
                                          ,bg.MedicationForms                                as "MedicationForms"
                                          ,concat(pt.Name, '-', abr.Abr)                     as "PaymentType"
                                          ,pt.Finance                                        as "Finance"
                                          ,gs.Name                                           as "GoodsSupplier"
                                          ,sc.Number                                         as "SupplierContractNumber"
                                          ,pkg.Name                                          as "PackageType"
                                    ),
                                    (
                                        select xmlagg
                                        (
                                            xmlelement
                                            (
                                                name "DAYS",
                                                xmlforest
                                                (
                                                    to_char(srd.Date, 'dd.MM.yyyy')   as "ON_DAY/Date"
                                                    ,srd.OverIn                            as "ON_DAY/OverIn"
                                                    ,srd.OverOut                           as "ON_DAY/OverOut"
                                                    ,srd.RestIn                            as "ON_DAY/RestIn"
                                                    ,srd.RestOut                           as "ON_DAY/RestOut"
                                                )
                                            )
                                        )
                                        from STOR.T_STORAGE_REST_ON_DAY srd
                                        where srd.Remain = r.RemainsID 
                                    )
                                )
                                )
                                from STOR.T_REMAINS r
                                    join STOR.T_STORAGE s on s.StorageID = r.Storage
                                    join APS.T_PHARMACY_ISSUE_PLACE pip on pip.IssuePlaceID = s.IssuePlace
                                    join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = r.SupplierContract
                                    join STOR.T_SUPPLIER_CONTRACT_ABR abr on abr.Abr = r.SupplierContractAbr
                                    join STOR.T_PAYMENT_TYPE pt on pt.Code = abr.PaymentType
                                    join PHARM.T_PACKAGE_TYPE pkg on pkg.PackageTypeID = r.PackageType
                                    join STOR.T_GOODS_SUPPLIER gs on gs.GoodsSupplierID = sc.GoodsSupplier
                                    join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
                                where r.Amount > 0
                        )
                    )
                )
            )
            into _Result;
        elsif (_OperationType <> 'DELETE')
        then 
            -- собираем SENT_REMAIN
         select xmlagg
         (
            xmlelement
            (
                    name "SENT_REMAIN",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            r.RemainsID as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest 
                        (
                           r.Price                                                                                          as "Price"
                          ,r.LotNumber                                                                                      as "LotNumber"
                          ,r.Series                                                                                         as "Series"
                          ,r.Comment                                                                                        as "Comment"
                          ,to_char(r.ExpirationDate, 'dd.MM.yyyy')                                                          as "ExpirationDate"
                          ,r.Amount                                                                                         as "Amount"
                          ,s.Name                                                                                           as "Storage"
                          ,pip.Pharmacy                                                                                     as "Pharmacy"
                          ,r.BillOfGoods                                                                                    as "BillOfGoods"
                          ,bg.MedicationForms                                                                               as "MedicationForms"
                          ,pt.Name + '-' + abr.Abr                                                                          as "PaymentType"
                          ,pt.Finance                                                                                       as "Finance"
                          ,gs.Name                                                                                          as "GoodsSupplier"
                          ,sc.Number                                                                                        as "SupplierContractNumber"
                          ,pkg.Name                                                                                         as "PackageType"
                          ,ISNULL(to_char(srd.Date, 'dd.MM.yyyy'),to_char(sr.ModifiedDate, 'dd.MM.yyyy'))                   as "ON_DAY/Date"
                          ,ISNULL(srd.OverIn, 0)                                                                            as "ON_DAY/OverIn"
                          ,ISNULL(srd.OverOut, 0)                                                                           as "ON_DAY/OverOut"
                          ,ISNULL(srd.RestIn, 0)                                                                            as "ON_DAY/RestIn"
                          ,ISNULL(srd.RestOut, 0)                                                                           as "ON_DAY/RestOut"
                        )
                    )
                )
                )
            into _Result
                from APS.T_SENT_REMAIN sr
                    join STOR.T_REMAINS r on r.RemainsID = sr.Remains
                    join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
                    join STOR.T_STORAGE s on s.StorageID = r.Storage
                    join APS.T_PHARMACY_ISSUE_PLACE pip on pip.IssuePlaceID = s.IssuePlace
                    join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = r.SupplierContract
                    join STOR.T_SUPPLIER_CONTRACT_ABR abr on abr.Abr = r.SupplierContractAbr
                    join STOR.T_PAYMENT_TYPE pt on pt.Code = abr.PaymentType
                    join STOR.T_GOODS_SUPPLIER gs on gs.GoodsSupplierID = sc.GoodsSupplier
                    join PHARM.T_PACKAGE_TYPE pkg on pkg.PackageTypeID = r.PackageType
                    left join STOR.T_STORAGE_REST_ON_DAY srd on srd.Date = sr.ModifiedDate 
                    and srd.Remain = sr.Remains where sr.SentRemainID = "ID";
        end if;
	
        -- Отправка
        INSERT INTO APS.T_RABBIT_SEND_OBJECT
				(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
        select _rsoID
              ,"QueueName"
              ,ConvertTextToBinaryZip(_Result, '')
              ,null
              ,null
              ,_ContentEncoding
              ,_Type
              ,"User"
              ,0
              ,"DateTime"
              ,_OwnerID;

        -- Проставляем ссылку на RabbitSendObject
        update APS.T_SENT_REMAIN sr
        set sr.RabbitSendObject = _rsoID
        where sr.SentRemainID = "ID" and "ID" is not null;

		end if;
	End;
End;
$$;
