DROP FUNCTION IF EXISTS akuz.recipe_gennumber_insert CASCADE;

CREATE FUNCTION akuz.recipe_gennumber_insert()
  RETURNS trigger AS
$BODY$
declare	_recipeID UUID;
	_num bigint;
	_minNumber bigint;
	_maxNumber bigint;
	_minHandNumber bigint;
    _maxHandNumber bigint;
    _lpuSettings UUID;
	_NumberType bit;
	_RecType int;
	_EntityType int;
BEGIN
	select into
		_recipeID, _num, _minNumber, _maxNumber, _minHandNumber, _maxHandNumber, _lpuSettings, _NumberType, _RecType, _EntityType
		i.MedicalRecordID, i.Number, ls.RecipeNumberBegin, ls.RecipeNumberEnd, ls.RecipeHandNumberBegin, ls.RecipeHandNumberEnd, ls.LpuSettingsID,
		CASE  ls.RecipeNumberGenerationType WHEN 0 THEN '0' ELSE '1' END,
		m.type_,
		(select EntityID from VCLib.T_ENTITIES where EntName = 'RECIPE_FORM' and Schema = 'AKUZ' limit 1)
	from akuz.T_Recipe i
	inner join 
		AKUZ.T_MEDICAL_RECORD m 
		on m.MedicalRecordID = i.MedicalRecordID
	left join
		AKUZ.T_LPU_SETTINGS ls 
		on ls.LpuSettingsId = i.LpuSettings       
	where i.MedicalRecordID = New.MedicalRecordID;

	if _EntityType = _RecType
	then
		return null;-- ничего не проверяем
	end if;
	
	if _lpuSettings is null
	then
		Raise Exception Using Message = 'Настройки ЛПУ не заданы';
		return null;
	end if;	
    
	if _NumberType = '0'
	then
		if _minNumber is null or _minNumber >= _maxNumber
		then
			Raise Exception Using Message = 'Неправильно задан диапазон номеров';
			return null;
		end if;
		if (_minHandNumber is not null and _maxHandNumber is null) or (_minHandNumber is null and _maxHandNumber is not null)  or _minHandNumber > _maxHandNumber
        then
            Raise Exception Using Message = 'Неправильно задан ручной диапазон номеров';
            return null;
        end if;
		
		if _num = -1
		then    
			select * from AKUZ.T_LPU_SETTINGS ls where ls.LpuSettingsID = _lpuSettings for update;
			
			select t.num
			from
			(
				select ROW_NUMBER() OVER(order by Number) + _minNumber - 1 as num
				from
				(
					select t.Number from akuz.T_RECIPE t where t.LpuSettings = _lpuSettings and t.Number >= _minNumber and t.Number <= _maxNumber
					union
					select _minNumber
					union
					select _maxNumber
				) t0
			) t
			into _num
			where not exists
			(
				select * from akuz.T_RECIPE t0 where LpuSettings = _lpuSettings and t0.Number = t.num				
			)
			limit 1;
			
			if num = -1
			then
				Raise Exception Using Message = 'Нет свободных номеров в диапазоне';
				return null;
			end if;
			
			--update akuz.T_RECIPE 
			--set	Number = _num
			--,	LpuSettings = _lpuSettings
			--where RecipeID = _recipeID;
		else
			if _num < _minNumber or _num > _maxNumber
			then
				Raise Exception Using Message = 'Номер за границами диапазона номеров';
				return null;
			end if;

			if exists 
			(
				select * from akuz.T_Recipe where LpuSettings = _lpuSettings and Number = _num and Deleted = '0' and MedicalRecordID <> _recipeID
			)
			then
				Raise Exception Using Message = 'Заданный номер уже используется';
				return null;
			end if;

			--update AKUZ.T_RECIPE 
			--set LpuSettings = _lpuSettings
			--where RecipeID = _recipeID;
		end if;
	end if;
	if _num = -1
    then
		Raise Exception Using Message = 'Ошибка проставления номера рецепта!';
    	return null;
    end if;
	
	update AKUZ.T_RECIPE 
	set Number = _num
	,	MedicalRecordType = _RecType 
	where MedicalRecordID = _recipeID;
	
	Return New;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
--ALTER FUNCTION akuz.recipe_gennumber_insert() OWNER TO postgres;


CREATE TRIGGER recipe_gennumber
  AFTER INSERT
  ON akuz.t_recipe
  FOR EACH ROW
  EXECUTE PROCEDURE akuz.recipe_gennumber_insert();