IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetRecipeDrugRequestList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetRecipeDrugRequestList]
GO

--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Dmitry
---- Create date: 05.07.2010
---- Description:	Возвращает DrugRequestList
---- =============================================
--CREATE FUNCTION [dbo].[fn_GetRecipeDrugRequestList]
--(
--	@RecipeID uniqueidentifier
--	, @OnDate datetime =NULL
--)
--RETURNS uniqueidentifier
--AS
--begin
--	Declare 
--		@Mnn int
--		,@Trn int
--		,@MedicationForms uniqueidentifier
--		,@DoseView int
--		,@Dose varchar(255)
--		,@DoseCount int
--		,@Doses decimal(31,10)
--		,@VolumeView int
--		,@Weigth decimal(31,10)
--		,@WeightView int
--		,@Volume decimal(31,10)
	
--		select top 1
--				@Mnn = rf.Mnn
--				,@Trn = rf.Trn
--				,@MedicationForms = r.MedicationForms
--				,@DoseView = rf.RFDoseView
--				,@Dose = r.Dose
--				,@DoseCount = r.DoseCount
--				,@Doses = r.Doses
--				,@VolumeView = r.VolumeView
--				,@Weigth = r.Weigth
--				,@WeightView = r.WeightView
--				,@Volume = r.Volume
--		from [AKUZ].T_RECIPE_FEDERAL rf
--		Join [AKUZ].T_RECIPE r On rf.MedicalRecordID = r.MedicalRecordID
--		where rf.MedicalRecordID = @RecipeID

--	return(
--	select top 1 dr.[BaseDrugRequestItemID]
--	from [AKUZ].T_BASE_DRUG_REQUEST_ITEM dr WITH(NOLOCK)
--    join [AKUZ].T_DRUG_REQUEST_LIST drl on drl.BaseDrugRequestItemID = dr.BaseDrugRequestItemID
--	where (@OnDate IS NULL OR dr.BeginDate = @OnDate) 
--		and drl.Mnn = @Mnn
--		And (drl.Trn Is Null OR drl.Trn = @Trn)
--		and dr.MedicationForms = @MedicationForms
--		and drl.DoseView = @DoseView
--		and drl.Dose = @Dose
--		and drl.DoseCount = @DoseCount
--		and drl.Doses = @Doses

--		and ((drl.Volume IS NULL AND @Volume IS NULL) OR drl.Volume = @Volume)
--		and ((drl.VolumeView IS NULL AND @VolumeView IS NULL) OR drl.VolumeView = @VolumeView)
--		and ((drl.Weigth IS NULL AND @Weigth IS NULL) OR drl.Weigth = @Weigth)
--		and ((drl.WeightView IS NULL AND @WeightView IS NULL) OR drl.WeightView = @WeightView)
--	order by dr.BeginDate desc, drl.Trn Desc)
--end
