IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetDrugFormRest]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetDrugFormRest]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 04.04.2014
---- Description:	Возвращает остаток лекарственной формы
---- =============================================
--CREATE FUNCTION [dbo].[fn_GetDrugFormRest] 
--(
--	@Code int -- код лекарственной формы (id T_DRUG_FORM)
--	,@OnDate datetime = NULL
--	,@RequestType int = NULL
--	,@Pharmacy int = NULL
--	,@LpuMain uniqueidentifier = NULL
--)
--RETURNS decimal(31,10)
--AS
--BEGIN
--	IF @OnDate IS NULL 
--		RETURN -1;

--declare @Month int, @BeginDate datetime, @RecipeType int
--	, @LSFO int, @MedicationForms uniqueidentifier, @DoseView int, @Dose varchar(255)
--	, @DoseCount int, @Weigth decimal(31,10), @Volume decimal(31,10), @Mnn int

--Select @Month = DATEPART(MONTH, @OnDate), @RecipeType = case when @RequestType = 1 Then 607 Else 673 End
--Select @BeginDate = (select MIN(dr.BeginPeriod) 
--from [AKUZ].T_BASE_DRUG_REQUEST dr 
--where dr.BeginPeriod <= @OnDate and dr.EndPeriod >= @OnDate and dr.[Status] = 3)

--SELECT 
--	@LSFO = t0.LSFO
--	,@MedicationForms = t0.MedicationForms
--	,@DoseView = t0.DoseView
--	,@Dose = t0.Dose
--	,@DoseCount = t0.DoseCount
--	,@Weigth = t0.Weigth
--	,@Volume = t0.Volume
--	,@Mnn = t0.Mnn
--FROM [AKUZ].V_DRUG_FORM t0  
--WHERE  t0.Code = @Code

---- кол-во на указанный месяц
--declare @RequestQuantity int
--SELECT @RequestQuantity = SUM(
--	pdd.Mont1
--	+ case when @Month > 1 then pdd.Mont2 else 0 end
--	+ case when @Month > 2 then pdd.Mont3 else 0 end
--	+ case when @Month > 3 then pdd.Mont4 else 0 end
--	+ case when @Month > 4 then pdd.Mont5 else 0 end
--	+ case when @Month > 5 then pdd.Mont6 else 0 end
--	+ case when @Month > 6 then pdd.Mont7 else 0 end
--	+ case when @Month > 7 then pdd.Mont8 else 0 end
--	+ case when @Month > 8 then pdd.Mont9 else 0 end
--	+ case when @Month > 9 then pdd.Mont10 else 0 end
--	+ case when @Month > 10 then pdd.Mont11 else 0 end
--	+ case when @Month > 11 then pdd.Mont12 else 0 end
--	) 
--FROM [AKUZ].T_PHARMACY_DRUG_DEMAND pdd
--	Join [AKUZ].T_DRUG_DEMAND dd On pdd.DrugDemand = dd.DrugDemandID
--	JOIN [AKUZ].T_DRUG_REQUEST dr ON dr.DrugRequestID = dd.DrugRequest 
--	JOIN [AKUZ].T_BASE_DRUG_REQUEST bdr ON bdr.DrugRequestID = dd.DrugRequest 
--	JOIN [AKUZ].T_BASE_DRUG_REQUEST_ITEM bdri ON ( bdri.RequestCode = dd.RequestCode
--											AND bdri.AppListBeginDate/*Элемент зс*/ <= bdr.BeginPeriod/*Заявка*/
--											AND bdri.AppListEndDate/*Элемент зс*/ >= bdr.EndPeriod/*Заявка*/
--                                              )
--	JOIN [AKUZ].T_DRUG_REQUEST_LIST drl ON drl.BaseDrugRequestItemID = bdri.BaseDrugRequestItemID
--WHERE pdd.Pharmacy = @Pharmacy AND bdr.LpuMain = @LpuMain AND bdr.Active=1 AND dr.RequestType=@RequestType AND drl.Mnn=@Mnn 
--and (bdr.BeginPeriod <= @OnDate and bdr.EndPeriod >= @OnDate and bdr.[Status] = 3)
--AND 
--(
--	@LSFO IS NOT NULL AND dd.LSFO = @LSFO 
--	OR 
--	(
--		@LSFO IS NULL 
--		AND 
--		(bdri.MedicationForms=@MedicationForms OR (bdri.MedicationForms IS NULL AND @MedicationForms IS NULL)) 
--		AND 
--		(drl.DoseView=@DoseView OR (drl.DoseView IS NULL AND @DoseView IS NULL)) 
--		AND 
--		(drl.Dose=@Dose OR (drl.Dose IS NULL AND @Dose IS NULL)) 
--		AND 
--		(drl.DoseCount=@DoseCount OR (drl.DoseCount IS NULL AND @DoseCount IS NULL)) 
--		AND 
--		(drl.Weigth=@Weigth OR (drl.Weigth IS NULL AND @Weigth IS NULL)) 
--		AND 
--		(drl.Volume=@Volume OR (drl.Volume IS NULL AND @Volume IS NULL))
--	)
--)

--	IF @RequestQuantity IS NULL 
--		RETURN -2;

---- кол-во выписанных рецептов
--declare @Count decimal(31,10)
--Select @Count = SUM(r.UnitsCount)
--From [AKUZ].T_RECIPE r
--	Join [AKUZ].T_RESOURCE rs On r.Post = rs.ResourceId
--	Join [AKUZ].T_DIVISION div On rs.Division = div.DivisionID
--	Join [AKUZ].T_BRANCH br On div.Branch = br.LpuID
--	Join T_DRUG_FORM df On r.DrugFormCode = df.Code
--Where r.Pharmacy = @Pharmacy AND br.LpuMain = @LpuMain AND r.Deleted = 0 AND r.type_ = @RecipeType
--AND (r.RecipeDate between @BeginDate And @OnDate)
----lia_140718 /--
--And df.Mnn = @Mnn
----/ lia_140718--
--AND 
--(
--	@LSFO IS NOT NULL AND df.LSFO = @LSFO 
--	OR 
--	(
--		@LSFO IS NULL 
--		AND 
--		(df.MedicationForms=@MedicationForms OR (df.MedicationForms IS NULL AND @MedicationForms IS NULL)) 
--		AND 
--		(df.DoseView=@DoseView OR (df.DoseView IS NULL AND @DoseView IS NULL)) 
--		AND 
--		(df.Dose=@Dose OR (df.Dose IS NULL AND @Dose IS NULL)) 
--		AND 
--		(df.DoseCount=@DoseCount OR (df.DoseCount IS NULL AND @DoseCount IS NULL)) 
--		AND 
--		(df.Weigth=@Weigth OR (df.Weigth IS NULL AND @Weigth IS NULL)) 
--		AND 
--		(df.Volume=@Volume OR (df.Volume IS NULL AND @Volume IS NULL))
--	)
--)

--	RETURN (cast(@RequestQuantity as decimal(31,10)) - ISNULL(@Count,0));
--END
--GO

