SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[sp_RDS_SentRemains]') AND type in (N'P', N'PC'))
DROP PROCEDURE [DBO].[sp_RDS_SentRemains]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_SentRemains]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_SentRemains]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка остатков в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_SentRemains]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
           ,@usedIn int
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)
           ,@FTSEND varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- RDS 
          ,@usedIn = 1
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'
          ,@FTSEND = 'FTSend'

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'SENT_REMAIN'
              ,@OwnerID = @ID

        --выясняем тип операции
        IF (@ID is null and @Xml is null)
        BEGIN
            select @OperationType = @FTSEND
        END
		ELSE if(not exists (select * from STOR.T_REMAINS r join APS.T_SENT_REMAIN sr on sr.Remains = r.RemainsID and sr.SentRemainID = @ID))
		BEGIN
			select @OperationType = @DELETE
            select @Result = 
            (
                select @OperationType as 'header/TYPE'
                      ,sr.Remains           as 'header/ID'
                from [APS].T_SENT_REMAIN sr
                where sr.SentRemainID = @ID
                FOR XML PATH(''),
                ROOT('SENT_REMAIN')
            )
		END
        ELSE
        BEGIN
            select @OperationType = @UPDATE
        END

        -- Обработка операции
        IF (@OperationType = @FTSEND)
        BEGIN

            -- Отправка в РДС
            select @Result = 
            (
                select @FTSEND                                                       as 'header/TYPE'
                      ,(
                            select r.RemainsID                                       as 'SENT_REMAIN/ID'
                                  ,r.Price                                           as 'SENT_REMAIN/Price'
                                  ,r.LotNumber                                       as 'SENT_REMAIN/LotNumber'
                                  ,r.Series                                          as 'SENT_REMAIN/Series'
                                  ,r.Comment                                         as 'SENT_REMAIN/Comment'
                                  ,convert(varchar(10), r.ExpirationDate, 104)       as 'SENT_REMAIN/ExpirationDate'
                                  ,r.Amount                                          as 'SENT_REMAIN/Amount'
                                  ,s.Name                                            as 'SENT_REMAIN/Storage'
                                  ,pip.Pharmacy                                      as 'SENT_REMAIN/Pharmacy'
                                  ,r.BillOfGoods                                     as 'SENT_REMAIN/BillOfGoods'
                                  ,bg.MedicationForms                                as 'SENT_REMAIN/MedicationForms'
                                  ,pt.Name + '-' + abr.Abr                           as 'SENT_REMAIN/PaymentType'
                                  ,pt.Finance                                        as 'SENT_REMAIN/Finance'
                                  ,gs.Name                                           as 'SENT_REMAIN/GoodsSupplier'
                                  ,sc.Number                                         as 'SENT_REMAIN/SupplierContractNumber'
                                  ,pkg.Name                                          as 'SENT_REMAIN/PackageType'
                                  ,(
                                        select convert(varchar(10), srd.[Date], 104) 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
                                        for xml path(''),
                                        type
                                  )                                                  as 'SENT_REMAIN/DAYS'
                            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
                            for xml path(''),
                            type
                      ) as 'body/SENT_REMAINS'
                for xml path(''),
                root('SENT_REMAIN')
            )

        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN 

            -- собираем SENT_REMAIN
            SELECT @Result = 
            ( 
                select @OperationType                              as 'header/TYPE'
                      ,r.RemainsID                                 as 'header/ID'

                      ,r.Price                                     as 'body/Price'
                      ,r.LotNumber                                 as 'body/LotNumber'
                      ,r.Series                                    as 'body/Series'
                      ,r.Comment                                   as 'body/Comment'
                      ,convert(varchar(10), r.ExpirationDate, 104) as 'body/ExpirationDate'
                      ,r.Amount                                    as 'body/Amount'
                      ,s.Name                                      as 'body/Storage'
                      ,pip.Pharmacy                                as 'body/Pharmacy'
                      ,r.BillOfGoods                               as 'body/BillOfGoods'
                      ,bg.MedicationForms                          as 'body/MedicationForms'
                      ,pt.Name + '-' + abr.Abr                       as 'body/PaymentType'
                      ,pt.Finance                                   as 'body/Finance'
                      ,gs.Name                                     as 'body/GoodsSupplier'
                      ,sc.Number                                   as 'body/SupplierContractNumber'
                      ,pkg.Name                                    as 'body/PackageType'
                      ,ISNULL(convert(varchar(10), srd.[Date], 104),convert(varchar(10), sr.ModifiedDate, 104))        as 'body/ON_DAY/Date'
                      ,ISNULL(srd.OverIn, 0)                                  as 'body/ON_DAY/OverIn'
                      ,ISNULL(srd.OverOut, 0)                                 as 'body/ON_DAY/OverOut'
                      ,ISNULL(srd.RestIn, 0)                                  as 'body/ON_DAY/RestIn'
                      ,ISNULL(srd.RestOut, 0)                                 as 'body/ON_DAY/RestOut'
                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
                for xml path(''),
                root('SENT_REMAIN')
            )

        END

        declare @rsoID uniqueidentifier
        select @rsoID = NEWID()

        -- Отправка
        INSERT INTO [APS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select @rsoID--(<RabbitSendObjectID, uniqueidentifier,>
              ,@QueueName--,<QueueName, varchar(255),>
              ,dbo.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),>

        -- Проставляем ссылку на RabbitSendObject
        update sr
        set sr.RabbitSendObject = @rsoID
        from [APS].T_SENT_REMAIN sr
        where sr.SentRemainID = @ID
                and
            @ID is not null

    END
END
GO
