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
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка остатков в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_SentRemains]
     @Xml xml = null
    ,@IsNew bit = 1
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
	,@PATH VARCHAR(MAX) = 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)
		   ,@SQL VARCHAR(MAX)

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

	IF @PATH IS NOT NULL
		BEGIN 
			
	CREATE TABLE #TEMP_REMAINS_STORAGE
		(
			 [RemainsID]				uniqueidentifier
			,[Storage]					uniqueidentifier
			,[LotNumber]				int
			,[Series]					varchar(max)
			,[BillOfGoods]				uniqueidentifier
			,[Pharmacy]					int
			,[Comment]					varchar(max)
			,[ExpirationDate]			datetime
			,[Amount]					decimal
			,[Price]					decimal
			,[PackageType]				uniqueidentifier
			,[PaymentType]				varchar(max)
			,[Finance]					int
			,[SupplierContract]			uniqueidentifier
			,[SupplierContractNumber]	varchar(max)
			,[StorageInvoiceAdd]		uniqueidentifier
			,[SupplierContractAbr]		varchar(max)
			,[VAT]						int
			,[ProducerPrice]			decimal
			,[GTINSerialNumber]			varchar(max)
			,[ConversionId]				varchar(max)
			,[QuantityInPackage]		decimal
			,[RestID]					uniqueidentifier
			,[Date]						datetime
			,[RestIn]					decimal
			,[RestOut]					decimal
			,[OverIn]					decimal
			,[OverOut]					decimal
			,[Remain]					uniqueidentifier
		)


SET @SQL = 	'INSERT INTO #TEMP_REMAINS_STORAGE
		(
			 [RemainsID]				
			,[Storage]					
			,[LotNumber]				
			,[Series]					
			,[BillOfGoods]
			,[Pharmacy]
			,[Comment]					
			,[ExpirationDate]			
			,[Amount]					
			,[Price]					
			,[PackageType]
			,[PaymentType]
			,[Finance]
			,[SupplierContract]
			,[SupplierContractNumber]
			,[StorageInvoiceAdd]		
			,[SupplierContractAbr]		
			,[VAT]						
			,[ProducerPrice]			
			,[GTINSerialNumber]			
			,[ConversionId]				
			,[QuantityInPackage]		
			,[RestID]					
			,[Date]						
			,[RestIn]					
			,[RestOut]					
			,[OverIn]					
			,[OverOut]					
			,[Remain]					
		)
	SELECT 
		 [RemainsID]
		,[Storage]
		,[LotNumber]
		,[Series]
		,[BillOfGoods]
		,pip.Pharmacy
		,r.[Comment]
		,[ExpirationDate]
		,[Amount]
		,[Price]
		,[PackageType]
		,pt.Name + ''-'' + abr.Abr 
		,pt.Finance	
		,[SupplierContract]
		,sc.Number
		,[StorageInvoiceAdd]
		,r.[SupplierContractAbr]
		,[VAT]
		,[ProducerPrice]
		,[GTINSerialNumber]
		,[ConversionId]
		,[QuantityInPackage]
		,d.[RestID]
		,d.[Date]
		,d.[RestIn]
		,d.[RestOut]
		,d.[OverIn]
		,d.[OverOut]
		,d.[Remain]
	FROM ' + @PATH + 'STOR.[T_REMAINS] R
			JOIN ' + @PATH + '[PHARM].T_BILL_OF_GOODS bg			WITH (NOLOCK) ON bg.BillOfGoodsID = r.BillOfGoods
			JOIN ' + @PATH + '[STOR].T_STORAGE s					WITH (NOLOCK) ON s.StorageID = r.Storage
			JOIN ' + @PATH + '[APS].T_PHARMACY_ISSUE_PLACE pip		WITH (NOLOCK) ON pip.IssuePlaceID = s.IssuePlace
			JOIN ' + @PATH + '[STOR].T_STORAGE_REST_ON_DAY D		WITH (NOLOCK) ON R.RemainsID = D.Remain
			JOIN ' + @PATH + '[STOR].T_SUPPLIER_CONTRACT sc			WITH (NOLOCK) ON sc.SupplierContractID = r.SupplierContract
			JOIN ' + @PATH + '[STOR].T_SUPPLIER_CONTRACT_ABR abr	WITH (NOLOCK) ON abr.Abr = r.SupplierContractAbr
			JOIN ' + @PATH + '[STOR].T_PAYMENT_TYPE pt				WITH (NOLOCK) ON pt.Code = abr.PaymentType
			JOIN ' + @PATH + '[STOR].T_GOODS_SUPPLIER gs			WITH (NOLOCK) ON gs.GoodsSupplierID = sc.GoodsSupplier
            JOIN ' + @PATH + '[PHARM].T_PACKAGE_TYPE pkg			WITH (NOLOCK) ON pkg.PackageTypeID = r.PackageType'

execute (@SQL)

	DELETE FROM [RDS].T_STORAGE_REST_ON_DAY
	DELETE FROM [RDS].[T_REMAINS]

	-- Загружаем новые остатки
	INSERT INTO [RDS].[T_REMAINS]
	           ([RemainsID] ,[Storage] ,[LotNumber] ,[Series] ,[BillOfGoods] --,[MedicationForms] 
			   ,[PaymentType]
	           ,[Comment] ,[ExpirationDate] ,[Amount] ,[Price] --,[PackageType] 
			   ,[Pharmacy] --, [GoodsSupplier]
			   , [SupplierContractNumber], [Finance], [DrugCode],[LastModifiedDate])
	SELECT DISTINCT
	           nr.RemainsID--<RemainsID, uniqueidentifier,>
	          ,nr.Storage--<Storage, varchar(255),>
	          ,nr.LotNumber--<LotNumber, int,>
	          ,nr.Series--<Series, varchar(100),>
	          ,nr.BillOfGoods--<BillOfGoods, uniqueidentifier,>
	          --,nr.MedicationForms--<MedicationForms, uniqueidentifier,>
	          ,nr.PaymentType--<PaymentType, varchar(255),>
	          ,nr.Comment--<Comment, varchar(255),>
	          ,nr.ExpirationDate--<ExpirationDate, datetime,>
	          ,nr.Amount--<Amount, decimal(12,2),>
	          ,nr.Price--<Price, decimal(12,2),>
	          --,nr.PackageType--<PackageType, varchar(255),>
	          ,nr.Pharmacy--<Pharmacy, int,>)
	          --,nr.GoodsSupplier--<GoodsSupplier, varchar(255),>
	          ,nr.SupplierContractNumber--<SupplierContractNumber, varchar(255),>
	          ,nr.Finance--<Finance, int,>)
			  ,bg.OtherID
			  ,Getdate()
	FROM #TEMP_REMAINS_STORAGE nr
		join PHARM.T_BILL_OF_GOODS bg on nr.BillOfGoods = bg.BillOfGoodsID
	WHERE NOT EXISTS
	(
	    SELECT *
			FROM [RDS].T_REMAINS r
	    WHERE r.RemainsID = nr.RemainsID
	)

	INSERT INTO [RDS].[T_STORAGE_REST_ON_DAY]
	           ([RestID]
	           ,[Date]
	           ,[RestIn]
	           ,[RestOut]
	           ,[OverIn]
	           ,[OverOut]
	           ,[Remain])
	SELECT
	           NEWID()--<RestID, uniqueidentifier,>
	          ,nr.[Date]--<Date, datetime,>
	          ,nr.RestIn--<RestIn, decimal(12,2),>
	          ,nr.RestOut--<RestOut, decimal(12,2),>
	          ,nr.OverIn--<OverIn, decimal(12,2),>
	          ,nr.OverOut--<OverOut, decimal(12,2),>
	          ,nr.RemainsID--<Remain, uniqueidentifier,>)
	FROM #TEMP_REMAINS_STORAGE nr
	WHERE NOT EXISTS
	(
	    SELECT *
	    FROM [RDS].T_STORAGE_REST_ON_DAY srd
	    WHERE srd.Remain = nr.RemainsID AND srd.[Date] = nr.[Date]
	)
	
	END
	ELSE
	BEGIN

    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
        from @Xml.nodes('SENT_REMAIN') T(c)

        -- Generate table #SENT_REMAINS
        select t.c.value('../../ID[1]', 'uniqueidentifier')                                as [ID]

               -- Rest
              ,t.c.value('../../Price[1]', 'decimal(12, 2)')                               as [Price]
              ,t.c.value('../../LotNumber[1]', 'int')                                      as [LotNumber]
              ,t.c.value('../../Series[1]', 'varchar(100)')                                as [Series]
              ,t.c.value('../../Comment[1]', 'varchar(255)')                               as [Comment]
              ,CONVERT(datetime, t.c.value('../../ExpirationDate[1]', 'varchar(10)'), 104) as [ExpirationDate]
              ,t.c.value('../../Amount[1]', 'decimal(12, 2)')                              as [Amount]
              ,t.c.value('../../Storage[1]', 'varchar(255)')                               as [Storage]
              ,t.c.value('../../PaymentType[1]', 'varchar(255)')                           as [PaymentType]
              ,t.c.value('../../PackageType[1]', 'varchar(255)')                           as [PackageType]
              ,t.c.value('../../GoodsSupplier[1]', 'varchar(255)')                         as [GoodsSupplier]
              ,t.c.value('../../SupplierContractNumber[1]', 'varchar(255)')                as [SupplierContractNumber]
              ,t.c.value('../../BillOfGoods[1]', 'uniqueidentifier')                       as [BillOfGoods]
              ,t.c.value('../../MedicationForms[1]', 'uniqueidentifier')                   as [MedicationForms]
              ,t.c.value('../../Pharmacy[1]', 'int')                                       as [Pharmacy]
              ,t.c.value('../../Finance[1]', 'int')                                        as [Finance]

               -- Rest on day
              ,CONVERT(datetime, t.c.value('./Date[1]', 'varchar(10)'), 104)               as [Date]
              ,t.c.value('./RestIn[1]', 'decimal(12, 2)')                                  as [RestIn]
              ,t.c.value('./RestOut[1]', 'decimal(12, 2)')                                 as [RestOut]
              ,t.c.value('./OverIn[1]', 'decimal(12, 2)')                                  as [OverIn]
              ,t.c.value('./OverOut[1]', 'decimal(12, 2)')                                 as [OverOut]
            INTO #SENT_REMAINS
        from @Xml.nodes('SENT_REMAIN/body/SENT_REMAINS/SENT_REMAIN/DAYS/ON_DAY') T(c)
        where @OperationType = @FTSEND

        -- Generate table #SENT_REMAIN
        select T.c.value('../../header[1]/ID[1]', 'uniqueidentifier') as [ID]

               -- Rest
              ,t.c.value('../Price[1]', 'decimal(12, 2)')                               as [Price]
              ,t.c.value('../LotNumber[1]', 'int')                                      as [LotNumber]
              ,t.c.value('../Series[1]', 'varchar(100)')                                as [Series]
              ,t.c.value('../Comment[1]', 'varchar(255)')                               as [Comment]
              ,CONVERT(datetime, t.c.value('../ExpirationDate[1]', 'varchar(10)'), 104) as [ExpirationDate]
              ,t.c.value('../Amount[1]', 'decimal(12, 2)')                              as [Amount]
              ,t.c.value('../Storage[1]', 'varchar(255)')                               as [Storage]
              ,t.c.value('../PaymentType[1]', 'varchar(255)')                           as [PaymentType]
              ,t.c.value('../GoodsSupplier[1]', 'varchar(255)')                         as [GoodsSupplier]
              ,t.c.value('../SupplierContractNumber[1]', 'varchar(255)')                as [SupplierContractNumber]
              ,t.c.value('../PackageType[1]', 'varchar(255)')                           as [PackageType]
              ,t.c.value('../BillOfGoods[1]', 'uniqueidentifier')                       as [BillOfGoods]
              ,t.c.value('../MedicationForms[1]', 'uniqueidentifier')                   as [MedicationForms]
              ,t.c.value('../Pharmacy[1]', 'int')                                       as [Pharmacy]
              ,t.c.value('../Finance[1]', 'int')                                        as [Finance]

               -- Rest on day
              ,CONVERT(datetime, t.c.value('./Date[1]', 'varchar(10)'), 104)            as [Date]
              ,t.c.value('./RestIn[1]', 'decimal(12, 2)')                               as [RestIn]
              ,t.c.value('./RestOut[1]', 'decimal(12, 2)')                              as [RestOut]
              ,t.c.value('./OverIn[1]', 'decimal(12, 2)')                               as [OverIn]
              ,t.c.value('./OverOut[1]', 'decimal(12, 2)')                              as [OverOut]
            INTO #SENT_REMAIN
        from @Xml.nodes('SENT_REMAIN/body/ON_DAY') T(c)
        where @OperationType <> @FTSEND

		-- Generate table #SENT_REMAIN
        select T.c.value('./header[1]/ID[1]', 'uniqueidentifier') as [ID]
            INTO #SENT_REMAIN_DEL
        from @Xml.nodes('SENT_REMAIN') T(c)
        where @OperationType = @DELETE

        -- Обработка операции
        if (@OperationType = @FTSEND)
        BEGIN
            -- Первичная загрузка
            -- Обновляем имеющиеся остатки
           delete from [RDS].T_STORAGE_REST_ON_DAY
           delete from [RDS].[T_REMAINS]

            -- Загружаем новые остатки
            INSERT INTO [RDS].[T_REMAINS]
                       ([RemainsID] ,[Storage] ,[LotNumber] ,[Series] ,[BillOfGoods] --,[MedicationForms] 
					   ,[PaymentType]
                       ,[Comment] ,[ExpirationDate] ,[Amount] ,[Price] --,[PackageType] 
					   ,[Pharmacy] --, [GoodsSupplier]
					   , [SupplierContractNumber], [Finance], [DrugCode],[LastModifiedDate])
            SELECT distinct
                       nr.ID--<RemainsID, uniqueidentifier,>
                      ,nr.Storage--<Storage, varchar(255),>
                      ,nr.LotNumber--<LotNumber, int,>
                      ,nr.Series--<Series, varchar(100),>
                      ,nr.BillOfGoods--<BillOfGoods, uniqueidentifier,>
                      --,nr.MedicationForms--<MedicationForms, uniqueidentifier,>
                      ,nr.PaymentType--<PaymentType, varchar(255),>
                      ,nr.Comment--<Comment, varchar(255),>
                      ,nr.ExpirationDate--<ExpirationDate, datetime,>
                      ,nr.Amount--<Amount, decimal(12,2),>
                      ,nr.Price--<Price, decimal(12,2),>
                      --,nr.PackageType--<PackageType, varchar(255),>
                      ,nr.Pharmacy--<Pharmacy, int,>)
                      --,nr.GoodsSupplier--<GoodsSupplier, varchar(255),>
                      ,nr.SupplierContractNumber--<SupplierContractNumber, varchar(255),>
                      ,nr.Finance--<Finance, int,>)
					  ,bg.OtherID
					  ,Getdate()
            from #SENT_REMAINS nr
				join PHARM.T_BILL_OF_GOODS bg on nr.BillOfGoods = bg.BillOfGoodsID
            where not exists
            (
                select *
                from [RDS].T_REMAINS r
                where r.RemainsID = nr.ID
            )

            -- Загружаем новые остатки на день
            INSERT INTO [RDS].[T_STORAGE_REST_ON_DAY]
                       ([RestID]
                       ,[Date]
                       ,[RestIn]
                       ,[RestOut]
                       ,[OverIn]
                       ,[OverOut]
                       ,[Remain])
            SELECT
                       NEWID()--<RestID, uniqueidentifier,>
                      ,nr.[Date]--<Date, datetime,>
                      ,nr.RestIn--<RestIn, decimal(12,2),>
                      ,nr.RestOut--<RestOut, decimal(12,2),>
                      ,nr.OverIn--<OverIn, decimal(12,2),>
                      ,nr.OverOut--<OverOut, decimal(12,2),>
                      ,nr.ID--<Remain, uniqueidentifier,>)
            from #SENT_REMAINS nr
            where not exists
            (
                select *
                from [RDS].T_STORAGE_REST_ON_DAY srd
                where srd.Remain = nr.ID
                        and
                    srd.[Date] = nr.[Date]
            )

        END
        ELSE IF (@OperationType = @UPDATE)
        BEGIN

            -- Обновляем имеющиеся остатки
            update r
            set r.Price = nr.Price
               ,r.LotNumber = nr.LotNumber
               ,r.Series = nr.Series
               ,r.Comment = nr.Comment
               ,r.ExpirationDate = nr.ExpirationDate
               ,r.Amount = nr.Amount
               ,r.Storage = nr.Storage
               ,r.PaymentType = nr.PaymentType
               --,r.PackageType = nr.PackageType
               ,r.BillOfGoods = nr.BillOfGoods
               --,r.MedicationForms = nr.MedicationForms
               ,r.Pharmacy = nr.Pharmacy
               --,r.GoodsSupplier = nr.GoodsSupplier
               ,r.SupplierContractNumber = nr.SupplierContractNumber
               ,r.Finance = nr.Finance
			   ,r.DrugCode = bg.OtherID
				,r.[LastModifiedDate] = Getdate()
            from [RDS].T_REMAINS r
                join #SENT_REMAIN nr on nr.ID = r.RemainsID
				join PHARM.T_BILL_OF_GOODS bg on nr.BillOfGoods = bg.BillOfGoodsID

            -- Обновляем имеющиеся остатки на день
            update srd
            set srd.RestIn = nr.RestIn
               ,srd.RestOut = nr.RestOut
               ,srd.OverIn = nr.OverIn
               ,srd.OverOut = nr.OverOut
            from [RDS].T_STORAGE_REST_ON_DAY srd
                join #SENT_REMAIN nr on nr.ID = srd.Remain
                        and
                    nr.[Date] = srd.[Date]

            -- Загружаем новые остатки
            INSERT INTO [RDS].[T_REMAINS]
                       ([RemainsID] ,[Storage] ,[LotNumber] ,[Series] ,[BillOfGoods] --,[MedicationForms] 
					   ,[PaymentType]
                       ,[Comment] ,[ExpirationDate] ,[Amount] ,[Price] --,[PackageType] 
					   ,[Pharmacy]--, [GoodsSupplier]
					   , [SupplierContractNumber], [Finance],[DrugCode], [LastModifiedDate])
            SELECT distinct
                       nr.ID--<RemainsID, uniqueidentifier,>
                      ,nr.Storage--<Storage, varchar(255),>
                      ,nr.LotNumber--<LotNumber, int,>
                      ,nr.Series--<Series, varchar(100),>
                      ,nr.BillOfGoods--<BillOfGoods, uniqueidentifier,>
                      --,nr.MedicationForms--<MedicationForms, uniqueidentifier,>
                      ,nr.PaymentType--<PaymentType, varchar(255),>
                      ,nr.Comment--<Comment, varchar(255),>
                      ,nr.ExpirationDate--<ExpirationDate, datetime,>
                      ,nr.Amount--<Amount, decimal(12,2),>
                      ,nr.Price--<Price, decimal(12,2),>
                      --,nr.PackageType--<PackageType, varchar(255),>
                      ,nr.Pharmacy--<Pharmacy, int,>)
                      --,nr.GoodsSupplier--<GoodsSupplier, varchar(255),>
                      ,nr.SupplierContractNumber--<SupplierContractNumber, varchar(255),>
                      ,nr.Finance--<Finance, int,>)
					  ,bg.OtherID
						,Getdate()
            from #SENT_REMAIN nr
				join PHARM.T_BILL_OF_GOODS bg on nr.BillOfGoods = bg.BillOfGoodsID
            where not exists
            (
                select *
                from [RDS].T_REMAINS r
                where r.RemainsID = nr.ID
            )

            -- Загружаем новые остатки на день
            INSERT INTO [RDS].[T_STORAGE_REST_ON_DAY]
                       ([RestID]
                       ,[Date]
                       ,[RestIn]
                       ,[RestOut]
                       ,[OverIn]
                       ,[OverOut]
                       ,[Remain])
            SELECT
                       NEWID()--<RestID, uniqueidentifier,>
                      ,nr.[Date]--<Date, datetime,>
                      ,nr.RestIn--<RestIn, decimal(12,2),>
                      ,nr.RestOut--<RestOut, decimal(12,2),>
                      ,nr.OverIn--<OverIn, decimal(12,2),>
                      ,nr.OverOut--<OverOut, decimal(12,2),>
                      ,nr.ID--<Remain, uniqueidentifier,>)
            from #SENT_REMAIN nr
            where not exists
            (
                select *
                from [RDS].T_STORAGE_REST_ON_DAY srd
                where srd.Remain = nr.ID
                        and
                    srd.[Date] = nr.[Date]
            )

        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            delete r
            from [RDS].T_STORAGE_REST_ON_DAY r
                join #SENT_REMAIN_DEL nr on nr.ID = r.Remain
            delete r
            from [RDS].T_REMAINS r
                join #SENT_REMAIN_DEL nr on nr.ID = r.RemainsID
        END
        ELSE
            raiserror('Операция не поддерживается', 16, 10)

    END
END
END