IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[AKUZ].[MILK_RECIPE_GenNumber]'))
DROP TRIGGER [AKUZ].[MILK_RECIPE_GenNumber]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [AKUZ].[MILK_RECIPE_GenNumber]
   ON [AKUZ].[T_MILK_RECIPE]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON

 declare @num int
  , @minNumber int
  , @maxNumber int
  , @seria varchar(4)
  
 Select @minNumber = ls.MilkNumberBegin
		,@maxNumber = ls.MilkNumberEnd
		,@seria = (case when ls.RegionCode = '1600000000000' then ls.LpuMainCode else cast(ls.MilkSeria as varchar(4)) end)
 From inserted i
	Join [AKUZ].T_MEDICAL_RECORD mr On i.MedicalRecordID = mr.MedicalRecordID
	Join [AKUZ].T_RESOURCE r On mr.Post = r.ResourceID
	Join [AKUZ].T_DIVISION d On r.Division = d.DivisionID
	Join [AKUZ].T_BRANCH br On d.Branch = br.LpuID
	Join [AKUZ].T_LPU_SETTINGS ls On br.LpuMain = ls.LpuMain

 if @minNumber >= @maxNumber
 begin
   raiserror('Неправильно задан диапазон номеров', 16, 10)
   rollback tran
 end

 -- проверка такого же диапазона по этому пациенту
 If Exists (
		Select *
		From inserted i
			Join [AKUZ].T_AMBULANCE_CARD_RECORD acr On i.MedicalRecordID = acr.MedicalRecordID
		Where
		-- исключаем дополнительный (его проверять не надо) 
		i.Additional = 0
			And
		Exists (
				Select *
				From [AKUZ].T_MILK_RECIPE mr
					Join [AKUZ].T_AMBULANCE_CARD_RECORD acr2 On mr.MedicalRecordID = acr2.MedicalRecordID
				Where 
				-- исключаем себя
				mr.MedicalRecordID not in (select MedicalRecordID from inserted)
					And
				-- исключаем отбракованные
				not exists ( Select * From [AKUZ].T_RECIPE_DISCARD rd Where rd.MilkRecipe = mr.MedicalRecordID )
					And
				-- связка по этому пациенту
				acr2.AmbulanceCard = acr.AmbulanceCard
					And
				-- дополнительный
				mr.Additional = 0
					And
					mr.ProductType = i.ProductType
					and
				-- проверка диапазона начала и конца дейтсвия
					(
						( i.[BeginDate] <= mr.[EndDate] 
						AND
						i.[EndDate] >= mr.[BeginDate] )
					)
				)
			)
 begin
   raiserror('У данного пациента существует выписанный рецепт за указанный период.', 16, 10)
   rollback tran
 end

 IF (select Number from inserted) = '-1'
	 BEGIN
	    
	  select @num = max(Number) + 1 from [AKUZ].T_MILK_RECIPE where number >= @minNumber and number <= @maxNumber and Seria = @seria
	  if @num is null set @num = @minNumber
	  if @num is null or @num > @maxNumber
	  begin
		raiserror('Нет свободных номеров в диапазоне', 16, 10)
		rollback tran
	  end

	  update [AKUZ].T_MILK_RECIPE set number = @num where MedicalRecordID = (select MedicalRecordID from inserted)

	 END
 ELSE
	 BEGIN
	  
	  select @num = Number from inserted
	  
	  if exists (
			select * 
			from [AKUZ].T_MILK_RECIPE mr
			where Number = @num 
				and 
				not exists (Select * From [AKUZ].T_RECIPE_DISCARD rd Where rd.MilkRecipe = mr.MedicalRecordID)
				and 
				MedicalRecordID not in (select MedicalRecordID from inserted)
				)
	  begin
		raiserror('Заданный номер уже используется', 16, 10)
		rollback tran
	  end
  
	 END

END

GO