-- =============================================
-- Author:		Marat Zigangaraev
-- Create date: 2015-04-16
-- Description:	Изменение остатка на складе
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_CalcRemains;

CREATE PROCEDURE stor.sp_CalcRemains
(
	"remainsID" uuid
,	"date" timestamp
,	"currentDate" timestamp
,	"sum" decimal(12,2)
,	"delete" boolean
)
    language plpgsql
as
$$
Begin 

	if "sum" = 0
	then
		return;
	end if;

	Declare	
		_sum2 decimal(12,2);
		_ActualDate timestamp;
		_DateClose timestamp;
		_curr decimal(20,6);
		_PrepName text;
		_d timestamp;
		_msg text;
		_restID uuid;
		_prevRest decimal(12,2);
		_date timestamp;
	Begin
		_date := "date";
	
		_sum2 := case when "delete" = '0' then "sum" else -"sum" end;
		_d := _ActualDate + interval '1 day';
		select into
			_DateClose, _ActualDate
			pc.CloseDate, pc.ActualDate
		from STOR.T_PERIOD_CLOSING pc join STOR.T_REMAINS r on r.Storage = pc.Storage
		where r.RemainsID = "remainsID"
		order by pc.PeriodClosingID desc
		limit 1;
	
		-- проверка закрытого приода
		If _d > _date and "currentDate" > _DateClose
		then
			Raise Exception Using Message = 'Не возможно изменить остаток. Закрыт отчетный период.';
			RETURN;
		End if;
	
		Select into
			_curr, _PrepName
			r.Amount, coalesce(trn.NameRus, trn.NameEng)
		From STOR.T_REMAINS r
		join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
		join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
		Where r.RemainsID = "remainsID"
		for update;
	
		-- проверка отрицательности
		If _curr + _sum2 < 0
		then
			_msg := 'Отрицательный остаток. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');
			Raise Exception Using Message = _msg;
			RETURN;
		End if;
	
		_msg := 'Ошибка при сохранении остатков. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');

		-- обновление остатков
		Update STOR.T_REMAINS
		Set Amount = _curr + _sum2
		Where RemainsID = "remainsID";
	
		-- остаток на указанную дату
		_restID := (Select s.RestID From STOR.T_STORAGE_REST_ON_DAY s Where s.Remain = "remainsID" and s.Date = _date);
	
		If _restID is null
		then
			-- предыдущий остаток
			_prevRest := (Select s.RestOut 
							From STOR.T_STORAGE_REST_ON_DAY s 
							Where s.Remain = "remainsID" and s.Date < _date
							Order By s.Date Desc limit 1);
	
			_prevRest := coalesce(_prevRest, 0);
	
			-- создаем на указанную дату
			_restID := gen_random_uuid();
	
			_msg := 'Ошибка при создании остатков. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');
			Insert Into STOR.T_STORAGE_REST_ON_DAY(RestID, Remain, RestIn, RestOut, OverIn, OverOut, Date)
			Values(_restID, "remainsID", _prevRest, _prevRest, 0, 0, _date);
		END if;
	
		-- обновляем записи на указанную дату
		_msg := 'Ошибка при обновлении остатков. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');
		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;
	
		-- обновляем предыдущие записи
		_msg := 'Ошибка при обновлении истории остатков. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');
		Update STOR.T_STORAGE_REST_ON_DAY d
		Set RestOut = (d.RestOut + _sum2)
			,RestIn = (d.RestIn + _sum2)
	 	from STOR.T_STORAGE_REST_ON_DAY dd
		Where d.Remain = "remainsID"
		and d.Date > _date and dd.RestID = d.RestID;
	
		 If Exists(Select * From STOR.T_STORAGE_REST_ON_DAY s Where s.Remain = "remainsID" and s.Date >= _date and (s.RestIn < 0 or s.RestOut < 0))
		 then
			_msg := 'Отрицательное значение в истории остатков. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');
			Raise Exception Using Message = _msg;
			RETURN;
		 End if;
	
		_msg := 'Ошибка при удаление пустых записей. ID:' || "remainsID"::varchar(36) || ' Название: ' || coalesce(_PrepName, '');
		-- удаление пустых записей
		Delete from STOR.T_STORAGE_REST_ON_DAY rest
			using STOR.T_REMAINS r 
		Where r.RemainsID = rest.Remain and 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')
		--then
		--	call SP_GetSetStorRemains(null, 0,0, "remainsID");
		--end if;
	
		--обнуление места храниения или возврат если отмена накладной 
		if((select Amount from STOR.T_REMAINS r where r.RemainsID = "remainsID") = 0)
		then
			update STOR.T_REMAINS set StorageShelf = null where RemainsID = "remainsID";
		else 
			update STOR.T_REMAINS r 
			set StorageShelf = ii.StorageShelf
			from STOR.T_INVOICE_ITEM ii 
			where ii.Remains = r.RemainsID and r.RemainsID = "remainsID"
			and r.StorageShelf is null and ii.StorageShelf is not null;
		end if;
		

	Exception
		WHEN Others
		THEN Raise Exception Using Message = _msg;
		RETURN;
	end;
End;
$$;