---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 04.04.2014
---- Description:	Возвращает остаток лекарственной формы
---- =============================================
--CREATE OR REPLACE FUNCTION fn_GetDrugFormRest
--(
--	"Code" Integer -- код лекарственной формы (id T_DRUG_FORM)
--,	"OnDate" TimeStamp = NULL
--,	"RequestType" Integer = NULL
--,	"Pharmacy" Integer = NULL
--,	"LpuMain" UUID = NULL
--)
--RETURNS decimal(31,10) AS
-- $$
-- declare _Month integer; 
--	_BeginDate TimeStamp;
--	_RecipeType integer;
--	_LSFO integer; 
--	_MedicationForms integer;
--	_DoseView integer; 
--	_Dose varchar(255);
--	_DoseCount integer; 
--	_Weigth decimal(31,10);
--	_Volume decimal(31,10);
--	_Mnn integer;
--	_RequestQuantity integer;
--	_Count decimal(31,10);
-- Begin
--	if "OnDate" is null
--	then return -1;
--	end if;

--	_Month := DATE_PART('MONTH', "OnDate");
--	_RecipeType := case when "RequestType" = 1 Then 607 Else 673 End;
--	select MIN(dr.BeginPeriod) 
--	from akuz.T_BASE_DRUG_REQUEST dr 
--		into _BeginDate
--	where "OnDate" between dr.BeginPeriod and dr.EndPeriod and dr.Status = 3;
	
--	SELECT Into
--		_LSFO, _MedicationForms, _DoseView, _Dose, _DoseCount, _Weigth, _Volume, _Mnn
--		t0.LSFO, t0.MedicationForms, t0.DoseView, t0.Dose, t0.DoseCount, t0.Weigth, t0.Volume, t0.Mnn
--	FROM akuz.V_DRUG_FORM t0  
--	WHERE t0.Code = "Code";

--	SELECT 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
--	inner join 
--		akuz.T_DRUG_DEMAND dd 
--		On pdd.DrugDemand = dd.DrugDemandID
--	inner join 
--		akuz.T_DRUG_REQUEST dr 
--		ON dr.DrugRequestID = dd.DrugRequest 
--	inner join
--		AKUZ.T_BASE_DRUG_REQUEST bdr 
--		ON bdr.DrugRequestID = dd.DrugRequest
--	inner join 
--		akuz.T_BASE_DRUG_REQUEST_ITEM bdri 
--		ON bdri.RequestCode = dd.RequestCode
--		AND bdri.AppListBeginDate <= bdr.BeginPeriod
--		AND bdri.AppListEndDate >= bdr.EndPeriod 
--	inner join 
--		akuz.T_DRUG_REQUEST_LIST drl 
--		ON drl.BaseDrugRequestItemID = bdri.BaseDrugRequestItemID
--	into _RequestQuantity
--	WHERE pdd.Pharmacy = "Pharmacy" 
--	AND dr.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 
--	then RETURN -2;
--	end if;

--	-- кол-во выписанных рецептов
--	Select SUM(r.UnitsCount)
--	From akuz.T_RECIPE r
--	inner join 
--		AKUZ.T_MEDICAL_RECORD mr 
--		On r.MedicalRecordID = mr.MedicalRecordID
--	inner join 
--		akuz.T_RESOURCE rs 
--		On mr.Post = rs.ResourceId
--	inner join 
--		akuz.T_DIVISION div 
--		On rs.Division = div.DivisionID
--	inner join 
--		akuz.T_BRANCH br 
--		On div.Branch = br.LpuID
--	inner join 
--		T_DRUG_FORM df 
--		On r.DrugFormCode = df.Code
--	Into _Count
--	Where r.Pharmacy = "Pharmacy" 
--	AND br.LpuMain = "LpuMain" 
--	AND r.Deleted = '0' 
--	AND mr.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)) - Coalesce(_Count,0));
	
--END $$
--language plpgsql stable;