SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[stor].[sp_CalcRemains]') and xtype = N'P')
drop procedure stor.[sp_CalcRemains]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CalcRemains]') and xtype = N'P')
drop procedure dbo.[sp_CalcRemains]
GO

-- =============================================
-- Author:		Marat Zigangaraev
-- Create date: 2015-04-16
-- Description:	Изменение остатка на складе
-- =============================================
CREATE PROCEDURE stor.[sp_CalcRemains]
	@remainsID uniqueidentifier,
	@date datetime,
	@currentDate datetime,
	@sum decimal(12,2), 
	@delete bit 
AS 
BEGIN 
	set nocount on 

	if @sum = 0
		return
		
	declare @sum2 decimal(12,2), @ActualDate datetime, @DateClose datetime, @curr decimal(20,6), @msg as varchar(4000), @Year int, @PrepName varchar(4000)
	set @sum2 = case when @delete = 0 then @sum else -@sum end

	declare @trancount int 
	select @trancount = @@trancount 
	if @trancount = 0 begin tran 
	
	select TOP 1 @DateClose = pc.CloseDate, @ActualDate = pc.ActualDate 
	from [STOR].t_PERIOD_CLOSING pc
	join [STOR].T_REMAINS r on r.Storage = pc.Storage
	where r.RemainsID = @remainsID
	order by PeriodClosingID desc

	declare @D datetime
	select @d = DATEADD(Day, 1, @ActualDate)

	-- проверка закрытого приода
	If (@d > @date  and @currentDate > @DateClose)
	Begin
		set @msg = 'Не возможно изменить остаток. Закрыт отчетный период.'
		goto error_label
	End


	Select @curr = r.Amount, @PrepName = ISNULL(trn.NameRus,trn.NameEng)
	From [STOR].T_REMAINS r with(rowlock,xlock) 
	left join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
	left join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
	Where RemainsID = @remainsID
	
	-- проверка отрицательности
	If ( @curr + @sum2 ) < 0
	Begin
		set @msg = 'Отрицательный остаток. ID:' + Cast(@remainsID as varchar(36)) + ' Название: %s'
		goto error_label
	End

	-- обновление остатков
	Update [STOR].T_REMAINS 
	Set Amount = @curr + @sum2
	Where RemainsID = @remainsID

	If @@error <> 0  
	Begin  
		set @msg = 'Ошибка при сохранении остатков. ID:' + Cast(@remainsID as varchar(36)) + ' Название: %s'
		goto error_label
	End

	-- остаток на указанную дату
	declare @restID uniqueidentifier
	Select @restID = RestID 
	From [STOR].T_STORAGE_REST_ON_DAY with(rowlock) 
	Where Remain = @remainsID and [Date] = @date

	If @restID is null
	BEGIN
		-- предыдущий остаток
		declare @prevRest decimal(12,2)
		Select Top 1 @prevRest = RestOut 
		From [STOR].T_STORAGE_REST_ON_DAY with(rowlock)
		Where Remain = @remainsID and [Date] < @date 
		Order By [Date] Desc
		
		If @prevRest is null
			Set @prevRest = 0

		-- создаем на указанную дату
		Set @restID = NEWID()
		Insert Into [STOR].T_STORAGE_REST_ON_DAY (RestID, Remain, RestIn, RestOut, OverIn, OverOut, [Date])
		Values (@restID, @remainsID, @prevRest, @prevRest, 0, 0, @date)

		If @@error <> 0  
		Begin  
			set @msg = 'Ошибка при создании остатков. ID:' + Cast(@remainsID as varchar(36)) + ' Название: %s'
			goto error_label
		End
	END

	-- обновляем записи на указанную дату
	Update [STOR].T_STORAGE_REST_ON_DAY
	Set OverIn = case when @sum > 0 then (OverIn + @sum2) else OverIn end
		,RestOut = (RestOut + @sum2)
		,OverOut = case when @sum <= 0 then (OverOut + @sum2) else OverOut end
	Where RestID = @restID 

	If @@error <> 0  
	Begin  
		set @msg = 'Ошибка при обновлении остатков. ID:' + Cast(@remainsID as varchar(36)) + ' Название: %s'
		goto error_label
	End

	-- обновляем предыдущие записи
	Update [STOR].T_STORAGE_REST_ON_DAY 
	Set RestOut = (RestOut + @sum2)
		,RestIn = (RestIn + @sum2)
	Where Remain = @remainsID and [Date] > @date 

	If @@error <> 0  
	Begin  
		set @msg = 'Ошибка при обновлении истории остатков. ID:' + Cast(@remainsID as varchar(36)) + ' Название: %s'
		goto error_label
	End

	-- проверка отрицательных остатков
	 --declare @trueCount decimal(12, 2)
	 --select @trueCount = sum(case when si.type_ <> 1910 then -1*ii.TotalApplicationCount else ii.TotalApplicationCount end)
	 --from STOR.T_INVOICE_ITEM ii
	 --inner join
	 -- STOR.T_STORAGE_INVOICE si
	 -- on ii.StorageInvoice = si.InvoiceID
	 --where ii.Remains = @remainsID
	 --and si.[Date] = @date
	 --and si.type_ <> 1924
	
	 If Exists( Select Top 1 *
	    From [STOR].T_STORAGE_REST_ON_DAY
	    Where Remain = @remainsID and [Date] >= @date 
	     and (RestIn < 0 or RestOut < 0)-- or RestIn + @trueCount <> RestOut) 
	    )
	 Begin
	  set @msg = 'Отрицательное значение в истории остатков. ID:' + Cast(@remainsID as varchar(36)) + ' Название: %s'
	  goto error_label
	 End

	-- удаление пустых записей
	Delete  rest
	from [STOR].T_STORAGE_REST_ON_DAY rest
	join STOR.T_REMAINS r on r.RemainsID = rest.Remain
	Where r.RemainsID = @remainsID and 
				((OverIn = 0 and OverOut = 0) or 
				 (r.Amount = 0 and not exists (select * from STOR.T_INVOICE_ITEM ii where (ii.remains = r.RemainsID or ii.PrepareRemains = r.RemainsID))))
	
	--if(exists (select * from VCLib.T_OBJECT_MODIFICATION_ACTION where Name = 'SP_GetSetStorRemains' and Active = 1))
	--begin
	--	exec dbo.SP_GetSetStorRemains  null, 0,0, @remainsID
	--end
	
	--обнуление места храниения или возврат если отмена накладной 
	if((select Amount from STOR.T_REMAINS r where r.RemainsID = @remainsID) = 0)
	begin
		update STOR.T_REMAINS set StorageShelf = null where RemainsID = @remainsID
	end
	else 
	begin 
		update r
		set r.StorageShelf = ii.StorageShelf
		from STOR.T_REMAINS r 
		join STOR.T_INVOICE_ITEM ii on ii.Remains = r.RemainsID
		where r.RemainsID = @remainsID
		and r.StorageShelf is null and ii.StorageShelf is not null
	end

	If @trancount = 0 
		commit tran
	return
	   
	error_label:
		if @trancount = 0 
			rollback tran   
		raiserror(@msg, 16, 10, @PrepName)
END
GO