/****** Object:  StoredProcedure dbo.sp_GetRequestItemRest    Script Date: 31.01.2017 13:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================
-- Author:		ЗАО Витакор
-- Create date:	17.01.2020
-- Description:	Возвращает остаток заявочного элемента
-- ===================================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetRequestItemRest]') AND type in (N'P', N'PC'))
drop PROCEDURE dbo.sp_GetRequestItemRest
GO

CREATE PROCEDURE dbo.sp_GetRequestItemRest(@RequestItemID      uniqueidentifier,		       -- BASE_DRUG_REQUEST_ITEM.BaseDrugRequestItemID
											  @OnDate             datetime         = NULL,	   
											  @LpuID              uniqueidentifier = NULL,	   
											  @RequestCatalogId   uniqueidentifier = NULL,    
											  @BranchID			  uniqueidentifier = NULL,     
											  @PatientID		  uniqueidentifier = NULL,     

											  @RestOnDate         decimal(31, 10)  = 0 OUTPUT, -- Остаток на дату
											  @RestOnYear         decimal(31, 10)  = 0 OUTPUT) -- Остаток за год
AS
		BEGIN
			-- обязательные параметры не указаны
			IF @OnDate IS NULL
			   OR @LpuID IS NULL
				BEGIN
					RETURN;
				END;

			-- номер месяца
			DECLARE @Month int, @FistDay datetime, @LastDay datetime;
			SELECT @Month = case when DATEPART(YEAR, @OnDate) > DATEPART(YEAR, getdate()) then 12  else DATEPART(MONTH, @OnDate) end;
			select @FistDay = (case when DATEPART(YEAR, @OnDate) > DATEPART(YEAR, getdate()) then cast(DATEPART(YEAR, getdate()) as varchar(4)) else cast(DATEPART(YEAR, @OnDate) as varchar(4)) end)  + '0101';
			select @LastDay = (case when DATEPART(YEAR, @OnDate) > DATEPART(YEAR, getdate()) then cast(DATEPART(YEAR, getdate()) as varchar(4)) else cast(DATEPART(YEAR, @OnDate) as varchar(4)) end) + '1231'

			-- сколько заказывали
			DECLARE @RequestQuantity decimal(31, 10), @AllRequestQuantity decimal(31, 10),@RequestQuantityPers decimal(31, 10), @AllRequestQuantityPers decimal(31, 10);

			-- сколько выписали
			DECLARE @OverReuqestPers decimal(31, 10) -- к месяцу
			DECLARE @OverReuqestPersAfter decimal(31, 10) -- после

			set @RequestQuantity= 0
			set @AllRequestQuantity = 0
			set @RequestQuantityPers = 0
			set @AllRequestQuantityPers = 0
			set @OverReuqestPers = 0
			set @OverReuqestPersAfter = 0

			--RECIPE
			SELECT r.Year, ben.BenefitType, rg.CMnn, rg.CTrn, rg.RGDoseView, r.MedicationForms, mr.PostingDate, r.UnitsCount, r.MedicalRecordID
			INTO #PreRECIPE
			FROM   AKUZ.T_RECIPE_GENERAL AS rg
			JOIN AKUZ.T_RECIPE AS r ON rg.MedicalRecordID = r.MedicalRecordID
            JOIN AKUZ.T_MEDICAL_RECORD AS mr ON mr.MedicalRecordID = r.MedicalRecordID
			join AKUZ.T_BENEFITS ben on ben.BenefitsID = r.Benefits
			WHERE  r.Deleted = 0
			and (mr.PostingDate between @FistDay and @LastDay)
			and exists
			(
				select top 1 rc.BaseDrugRequestItemID 
				from AKUZ.T_REQUEST_CATALOG rc 
				join AKUZ.T_BASE_DRUG_REQUEST_ITEM bdri on bdri.BaseDrugRequestItemID = rc.BaseDrugRequestItemID
				where rc.BaseDrugRequestItemID = @RequestItemID 
					and bdri.RequestCode = r.DrugFormCode
					and ben.BenefitType = rc.BenefitType
					AND rg.RGDoseView = rc.DoseView
					AND rg.CMnn = rc.CMnn
					AND (rc.CTrn IS NULL OR rg.CTrn IS NULL OR rg.CTrn = rc.CTrn)
					AND r.MedicationForms = bdri.MedicationForms
					and (bdri.AppListBeginDate between @FistDay and @LastDay)
			);

			SELECT ac.Patient, pr.Year, br.LpuMain ,div.Branch
				,pr.BenefitType, pr.CMnn, pr.CTrn, pr.RGDoseView, pr.MedicationForms,
				(case when DATEPART(MONTH, pr.PostingDate) = 1 then SUM(pr.UnitsCount) else 0 end) as RecMont1,
				(case when DATEPART(MONTH, pr.PostingDate) = 2 then SUM(pr.UnitsCount) else 0 end) as RecMont2,
				(case when DATEPART(MONTH, pr.PostingDate) = 3 then SUM(pr.UnitsCount) else 0 end) as RecMont3,
				(case when DATEPART(MONTH, pr.PostingDate) = 4 then SUM(pr.UnitsCount) else 0 end) as RecMont4,
				(case when DATEPART(MONTH, pr.PostingDate) = 5 then SUM(pr.UnitsCount) else 0 end) as RecMont5,
				(case when DATEPART(MONTH, pr.PostingDate) = 6 then SUM(pr.UnitsCount) else 0 end) as RecMont6,
				(case when DATEPART(MONTH, pr.PostingDate) = 7 then SUM(pr.UnitsCount) else 0 end) as RecMont7,
				(case when DATEPART(MONTH, pr.PostingDate) = 8 then SUM(pr.UnitsCount) else 0 end) as RecMont8,
				(case when DATEPART(MONTH, pr.PostingDate) = 9 then SUM(pr.UnitsCount) else 0 end) as RecMont9,
				(case when DATEPART(MONTH, pr.PostingDate) = 10 then SUM(pr.UnitsCount) else 0 end) as RecMont10,
				(case when DATEPART(MONTH, pr.PostingDate) = 11 then SUM(pr.UnitsCount) else 0 end) as RecMont11,
				(case when DATEPART(MONTH, pr.PostingDate) = 12 then SUM(pr.UnitsCount) else 0 end) as RecMont12
			INTO #RECIPE
			FROM   #PreRECIPE pr
			join AKUZ.T_MEDICAL_RECORD medRecord on pr.MedicalRecordID = medRecord.MedicalRecordID
			JOIN AKUZ.T_EVENT_BASE AS eb ON eb.EventID = medRecord.AmbulanceEvent
			JOIN AKUZ.T_AMBULANCE_CARD AS ac ON ac.AmbulanceCardID = eb.AmbulanceCard
			JOIN AKUZ.T_RESOURCE AS rs ON medRecord.Post = rs.ResourceId
			JOIN AKUZ.T_DIVISION AS div ON rs.Division = div.DivisionID
			JOIN AKUZ.T_BRANCH AS br ON div.Branch = br.LpuID
			WHERE  ac.CardLpu = @LpuID
			group by ac.Patient, pr.Year, DATEPART(MONTH, pr.PostingDate), br.LpuMain, pr.BenefitType, pr.CMnn, pr.CTrn, pr.RGDoseView, pr.MedicationForms, div.Branch

			create table #DEMAND(BaseDrugRequestItemID uniqueidentifier, LpuMain uniqueidentifier, BeginPeriod datetime, EndPeriod datetime, RequestType int, 
								Patient uniqueidentifier, Branch uniqueidentifier, Mont1 int, Mont2 int, Mont3 int, Mont4 int, Mont5 int, Mont6 int, Mont7 int,
								Mont8 int, Mont9 int, Mont10 int, Mont11 int, Mont12 int,RecMont1 int,RecMont2 int,RecMont3 int,RecMont4 int,RecMont5 int,
								RecMont6 int,RecMont7 int ,RecMont8 int,RecMont9 int,RecMont10 int,RecMont11 int,RecMont12 int, Curr int, CurrYear int) 

			--DEMAND
			if(@BranchID is null)
			begin
				insert INTO #DEMAND (BaseDrugRequestItemID, LpuMain, BeginPeriod, EndPeriod, RequestType, Patient, Mont1, Mont2, Mont3, Mont4, Mont5, Mont6, Mont7,Mont8, Mont9, Mont10, Mont11, Mont12)
				SELECT bdri.BaseDrugRequestItemID, bdr.LpuMain, bdr.BeginPeriod, bdr.EndPeriod, dr.RequestType
					,(case when pers.PatientDrugDemandID is not null then pers.Patient end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont1 else pdd.Mont1 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont2 else pdd.Mont2 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont3 else pdd.Mont3 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont4 else pdd.Mont4 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont5 else pdd.Mont5 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont6 else pdd.Mont6 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont7 else pdd.Mont7 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont8 else pdd.Mont8 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont9 else pdd.Mont9 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont10 else pdd.Mont10 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont11 else pdd.Mont11 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont12 else pdd.Mont12 end)
				FROM   AKUZ.T_PHARMACY_DRUG_DEMAND AS pdd
				   JOIN AKUZ.T_DRUG_DEMAND AS dd ON pdd.DrugDemand = dd.DrugDemandID
				   JOIN AKUZ.T_DRUG_REQUEST AS dr ON dr.DrugRequestID = dd.DrugRequest
				   JOIN AKUZ.T_BASE_DRUG_REQUEST AS bdr ON bdr.DrugRequestID = dd.DrugRequest
				   outer apply (
							select p.Patient,p.PatientDrugDemandID ,p.Mont1 ,p.Mont2 ,p.Mont3 ,p.Mont4 ,p.Mont5 ,p.Mont6 ,p.Mont7 ,p.Mont8 ,p.Mont9 ,p.Mont10,p.Mont11,p.Mont12
							from AKUZ.T_PATIENT_DRUG_DEMAND p 
							join AKUZ.T_PHARMACY_DRUG_DEMAND AS pharmpers on pharmpers.PharmacyDrugDemandID = p.PharmacyDrugDemand
							join AKUZ.T_DRUG_DEMAND AS ddpers ON ddpers.DrugDemandID = pharmpers.DrugDemand and ddpers.PersRequest = 1
							where p.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
						) pers
				   cross apply
				   (
			   			SELECT TOP 1 [drl].[BaseDrugRequestItemID], drl.MedicationForms
			   			FROM         [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] AS [drl]
			   						 LEFT JOIN [AKUZ].[T_REQUEST_CATALOG] AS [rc] ON [rc].[BaseDrugRequestItemID] = [drl].[BaseDrugRequestItemID] 
			   			WHERE        [drl].[RequestCode] = [dd].[RequestCode] 
			   						 AND [drl].[AppListBeginDate]/*Элемент зс*/ <= bdr.[BeginPeriod]/*Заявка*/
			   						 AND [drl].[AppListEndDate]/*Элемент зс*/ >= bdr.[EndPeriod]/*Заявка*/
			   						 AND ([rc].[BaseDrugRequestItemID] IS NULL
			   							  OR [dr].[RequestType] = [rc].[BenefitType])
			   						AND ((rc.SectionCode is null and  dd.SectionCode is null)
			   								or rc.SectionCode = dd.SectionCode)
				   ) bdri
				   join AKUZ.T_REQUEST_CATALOG rc on rc.BaseDrugRequestItemID = bdri.BaseDrugRequestItemID
			   
				WHERE  bdr.Active = 1 -- активна
					   AND bdr.Status = 3 -- подтверждена
					   and bdri.BaseDrugRequestItemID = @RequestItemID
				and bdr.LpuMain = @LpuID
				and (bdr.BeginPeriod between @FistDay and @LastDay)
				group by bdri.BaseDrugRequestItemID, bdr.LpuMain, bdr.BeginPeriod, bdr.EndPeriod, dr.RequestType, (case when pers.PatientDrugDemandID is not null then pers.Patient end)
			end
			else
			begin
				insert INTO #DEMAND (BaseDrugRequestItemID, LpuMain, BeginPeriod, EndPeriod, RequestType, Branch, Patient, Mont1, Mont2, Mont3, Mont4, Mont5, Mont6, Mont7,Mont8, Mont9, Mont10, Mont11, Mont12)
				SELECT bdri.BaseDrugRequestItemID, bdr.LpuMain, bdr.BeginPeriod, bdr.EndPeriod, dr.RequestType, dr.Branch
					,(case when pers.PatientDrugDemandID is not null then pers.Patient end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont1 else pdd.Mont1 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont2 else pdd.Mont2 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont3 else pdd.Mont3 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont4 else pdd.Mont4 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont5 else pdd.Mont5 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont6 else pdd.Mont6 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont7 else pdd.Mont7 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont8 else pdd.Mont8 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont9 else pdd.Mont9 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont10 else pdd.Mont10 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont11 else pdd.Mont11 end)
					,SUM(case when pers.PatientDrugDemandID is not null then pers.Mont12 else pdd.Mont12 end)
				FROM   AKUZ.T_PHARMACY_DRUG_DEMAND AS pdd
				   JOIN AKUZ.T_DRUG_DEMAND AS dd ON pdd.DrugDemand = dd.DrugDemandID
				   JOIN AKUZ.T_DRUG_REQUEST AS dr ON dr.DrugRequestID = dd.DrugRequest
				   JOIN AKUZ.T_BASE_DRUG_REQUEST AS bdr ON bdr.DrugRequestID = dd.DrugRequest
				   outer apply (
							select p.Patient,p.PatientDrugDemandID ,p.Mont1 ,p.Mont2 ,p.Mont3 ,p.Mont4 ,p.Mont5 ,p.Mont6 ,p.Mont7 ,p.Mont8 ,p.Mont9 ,p.Mont10,p.Mont11,p.Mont12
							from AKUZ.T_PATIENT_DRUG_DEMAND p 
							join AKUZ.T_PHARMACY_DRUG_DEMAND AS pharmpers on pharmpers.PharmacyDrugDemandID = p.PharmacyDrugDemand
							join AKUZ.T_DRUG_DEMAND AS ddpers ON ddpers.DrugDemandID = pharmpers.DrugDemand and ddpers.PersRequest = 1
							where p.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
						) pers
				   cross apply
				   (
			   			SELECT TOP 1 [drl].[BaseDrugRequestItemID], drl.MedicationForms
			   			FROM         [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] AS [drl]
			   						 LEFT JOIN [AKUZ].[T_REQUEST_CATALOG] AS [rc] ON [rc].[BaseDrugRequestItemID] = [drl].[BaseDrugRequestItemID] 
			   			WHERE        [drl].[RequestCode] = [dd].[RequestCode] 
			   						 AND [drl].[AppListBeginDate]/*Элемент зс*/ <= bdr.[BeginPeriod]/*Заявка*/
			   						 AND [drl].[AppListEndDate]/*Элемент зс*/ >= bdr.[EndPeriod]/*Заявка*/
			   						 AND ([rc].[BaseDrugRequestItemID] IS NULL
			   							  OR [dr].[RequestType] = [rc].[BenefitType])
			   						AND ((rc.SectionCode is null and  dd.SectionCode is null)
			   								or rc.SectionCode = dd.SectionCode)
				   ) bdri
				   join AKUZ.T_REQUEST_CATALOG rc on rc.BaseDrugRequestItemID = bdri.BaseDrugRequestItemID
			   
				WHERE  bdr.Active = 1 -- активна
					   AND bdr.Status = 3 -- подтверждена
					   and bdri.BaseDrugRequestItemID = @RequestItemID
				and bdr.LpuMain = @LpuID
				and (bdr.BeginPeriod between @FistDay and @LastDay)
				group by bdri.BaseDrugRequestItemID, bdr.LpuMain, bdr.BeginPeriod, bdr.EndPeriod, dr.RequestType, dr.Branch,(case when pers.PatientDrugDemandID is not null then pers.Patient end)
			end
			--CREATE UNIQUE CLUSTERED INDEX IX_V_DEMAND_BaseDrugRequestItemID ON #DEMAND(BaseDrugRequestItemID, Patient, LpuMain, Branch);
		
			update d
			set d.Curr = Case when ((d.Mont1 - d.RecMont1)
									+ CASE WHEN @Month > 1  THEN (d.Mont2 - d.RecMont2)  ELSE 0 END
									+ CASE WHEN @Month > 2  THEN (d.Mont3 - d.RecMont3)  ELSE 0 END
									+ CASE WHEN @Month > 3  THEN (d.Mont4 - d.RecMont4)  ELSE 0 END
									+ CASE WHEN @Month > 4  THEN (d.Mont5 - d.RecMont5)  ELSE 0 END
									+ CASE WHEN @Month > 5  THEN (d.Mont6 - d.RecMont6)  ELSE 0 END
									+ CASE WHEN @Month > 6  THEN (d.Mont7 - d.RecMont7)  ELSE 0 END
									+ CASE WHEN @Month > 7  THEN (d.Mont8 - d.RecMont8)  ELSE 0 END
									+ CASE WHEN @Month > 8  THEN (d.Mont9 - d.RecMont9)  ELSE 0 END
									+ CASE WHEN @Month > 9  THEN (d.Mont10 - d.RecMont10) ELSE 0 END
									+ CASE WHEN @Month > 10 THEN (d.Mont11 - d.RecMont11) ELSE 0 END
									+ CASE WHEN @Month > 11 THEN (d.Mont12 - d.RecMont12) ELSE 0 END) > 0 
							then 0 
							else ((d.Mont1 - d.RecMont1)
									+ CASE WHEN @Month > 1  THEN (d.Mont2 - d.RecMont2)  ELSE 0 END
									+ CASE WHEN @Month > 2  THEN (d.Mont3 - d.RecMont3)  ELSE 0 END
									+ CASE WHEN @Month > 3  THEN (d.Mont4 - d.RecMont4)  ELSE 0 END
									+ CASE WHEN @Month > 4  THEN (d.Mont5 - d.RecMont5)  ELSE 0 END
									+ CASE WHEN @Month > 5  THEN (d.Mont6 - d.RecMont6)  ELSE 0 END
									+ CASE WHEN @Month > 6  THEN (d.Mont7 - d.RecMont7)  ELSE 0 END
									+ CASE WHEN @Month > 7  THEN (d.Mont8 - d.RecMont8)  ELSE 0 END
									+ CASE WHEN @Month > 8  THEN (d.Mont9 - d.RecMont9)  ELSE 0 END
									+ CASE WHEN @Month > 9  THEN (d.Mont10 - d.RecMont10) ELSE 0 END
									+ CASE WHEN @Month > 10 THEN (d.Mont11 - d.RecMont11) ELSE 0 END
									+ CASE WHEN @Month > 11 THEN (d.Mont12 - d.RecMont12) ELSE 0 END) end
			from #DEMAND d

			update d
			set d.CurrYear = Case when ((d.Mont1 - d.RecMont1)
										+ (d.Mont2 - d.RecMont2)
										+ (d.Mont3 - d.RecMont3)
										+ (d.Mont4 - d.RecMont4)
										+ (d.Mont5 - d.RecMont5)
										+ (d.Mont6 - d.RecMont6)
										+ (d.Mont7 - d.RecMont7)
										+ (d.Mont8 - d.RecMont8)
										+ (d.Mont9 - d.RecMont9)
										+ (d.Mont10 - d.RecMont10)
										+ (d.Mont11 - d.RecMont11)
										+ (d.Mont12 - d.RecMont12)) > 0 
							then 0 
							else ((d.Mont1 - d.RecMont1)
									+ (d.Mont2 - d.RecMont2)
									+ (d.Mont3 - d.RecMont3)
									+ (d.Mont4 - d.RecMont4)
									+ (d.Mont5 - d.RecMont5)
									+ (d.Mont6 - d.RecMont6)
									+ (d.Mont7 - d.RecMont7)
									+ (d.Mont8 - d.RecMont8)
									+ (d.Mont9 - d.RecMont9)
									+ (d.Mont10 - d.RecMont10)
									+ (d.Mont11 - d.RecMont11)
									+ (d.Mont12 - d.RecMont12)) end
			from #DEMAND d

			--поиск остатков только по ЛПУ + перс
			IF (@BranchID is null)
			BEGIN
				--перс
				update d
					set	   d.RecMont1 =   ISNULL(rec.RecMont1, 0)
						   ,d.RecMont2 =  ISNULL(rec.RecMont2, 0)
						   ,d.RecMont3 =  ISNULL(rec.RecMont3, 0)
						   ,d.RecMont4 =  ISNULL(rec.RecMont4, 0)
						   ,d.RecMont5 =  ISNULL(rec.RecMont5, 0)
						   ,d.RecMont6 =  ISNULL(rec.RecMont6, 0)
						   ,d.RecMont7 =  ISNULL(rec.RecMont7, 0)
						   ,d.RecMont8 =  ISNULL(rec.RecMont8, 0)
						   ,d.RecMont9 =  ISNULL(rec.RecMont9, 0)
						   ,d.RecMont10 = ISNULL(rec.RecMont10, 0)
						   ,d.RecMont11 = ISNULL(rec.RecMont11, 0)
						   ,d.RecMont12 = ISNULL(rec.RecMont12, 0)
					from #DEMAND d
					outer apply (
						select SUM(RecMont1) as RecMont1,
							   SUM(RecMont2) as RecMont2,
							   SUM(RecMont3) as RecMont3,
							   SUM(RecMont4) as RecMont4,
							   SUM(RecMont5) as RecMont5,
							   SUM(RecMont6) as RecMont6,
							   SUM(RecMont7) as RecMont7,
							   SUM(RecMont8) as RecMont8,
							   SUM(RecMont9) as RecMont9,
							   SUM(RecMont10) as RecMont10,
							   SUM(RecMont11) as RecMont11,
							   SUM(RecMont12) as RecMont12
						from #RECIPE r
						where r.Patient = d.Patient
						group by r.LpuMain, r.Patient
						) rec
					where d.[Patient] is not null
				--все остальные	
				update d
					set	   d.RecMont1 =   ISNULL(rec.RecMont1, 0)
						   ,d.RecMont2 =  ISNULL(rec.RecMont2, 0)
						   ,d.RecMont3 =  ISNULL(rec.RecMont3, 0)
						   ,d.RecMont4 =  ISNULL(rec.RecMont4, 0)
						   ,d.RecMont5 =  ISNULL(rec.RecMont5, 0)
						   ,d.RecMont6 =  ISNULL(rec.RecMont6, 0)
						   ,d.RecMont7 =  ISNULL(rec.RecMont7, 0)
						   ,d.RecMont8 =  ISNULL(rec.RecMont8, 0)
						   ,d.RecMont9 =  ISNULL(rec.RecMont9, 0)
						   ,d.RecMont10 = ISNULL(rec.RecMont10, 0)
						   ,d.RecMont11 = ISNULL(rec.RecMont11, 0)
						   ,d.RecMont12 = ISNULL(rec.RecMont12, 0)
					from #DEMAND d
					outer apply (
						select SUM(RecMont1) as RecMont1,
							   SUM(RecMont2) as RecMont2,
							   SUM(RecMont3) as RecMont3,
							   SUM(RecMont4) as RecMont4,
							   SUM(RecMont5) as RecMont5,
							   SUM(RecMont6) as RecMont6,
							   SUM(RecMont7) as RecMont7,
							   SUM(RecMont8) as RecMont8,
							   SUM(RecMont9) as RecMont9,
							   SUM(RecMont10) as RecMont10,
							   SUM(RecMont11) as RecMont11,
							   SUM(RecMont12) as RecMont12
						from #RECIPE r
						where not exists (select * from #DEMAND dd where dd.Patient = r.Patient)
						group by r.LpuMain
						) rec
					where d.[Patient] is null
		
				--перс потребность
				SELECT @RequestQuantityPers = ISNULL(SUM -- на текущий месяц перс
					(								 (d.Mont1 - d.RecMont1)
						+ CASE WHEN @Month > 1  THEN (d.Mont2 - d.RecMont2)  ELSE 0 END
						+ CASE WHEN @Month > 2  THEN (d.Mont3 - d.RecMont3)  ELSE 0 END
						+ CASE WHEN @Month > 3  THEN (d.Mont4 - d.RecMont4)  ELSE 0 END
						+ CASE WHEN @Month > 4  THEN (d.Mont5 - d.RecMont5)  ELSE 0 END
						+ CASE WHEN @Month > 5  THEN (d.Mont6 - d.RecMont6)  ELSE 0 END
						+ CASE WHEN @Month > 6  THEN (d.Mont7 - d.RecMont7)  ELSE 0 END
						+ CASE WHEN @Month > 7  THEN (d.Mont8 - d.RecMont8)  ELSE 0 END
						+ CASE WHEN @Month > 8  THEN (d.Mont9 - d.RecMont9)  ELSE 0 END
						+ CASE WHEN @Month > 9  THEN (d.Mont10 - d.RecMont10) ELSE 0 END
						+ CASE WHEN @Month > 10 THEN (d.Mont11 - d.RecMont11) ELSE 0 END
						+ CASE WHEN @Month > 11 THEN (d.Mont12 - d.RecMont12) ELSE 0 END
					),0),
					   @AllRequestQuantityPers = ISNULL(SUM -- за год перс
					(
						 (d.Mont1 - d.RecMont1)
						+(d.Mont2 - d.RecMont2)  
						+(d.Mont3 - d.RecMont3)  
						+(d.Mont4 - d.RecMont4)  
						+(d.Mont5 - d.RecMont5)  
						+(d.Mont6 - d.RecMont6)  
						+(d.Mont7 - d.RecMont7)  
						+(d.Mont8 - d.RecMont8)  
						+(d.Mont9 - d.RecMont9)  
						+(d.Mont10 - d.RecMont10)
						+(d.Mont11 - d.RecMont11)
						+(d.Mont12 - d.RecMont12)
					),0)
				FROM #DEMAND d
				where d.Patient is not null and d.Patient = @PatientID

				--общая потребность
				SELECT @RequestQuantity = ISNULL(SUM -- на текущий месяц
				(									 (d.Mont1 - d.RecMont1)
						+ CASE WHEN @Month > 1  THEN (d.Mont2 - d.RecMont2)  ELSE 0 END
						+ CASE WHEN @Month > 2  THEN (d.Mont3 - d.RecMont3)  ELSE 0 END
						+ CASE WHEN @Month > 3  THEN (d.Mont4 - d.RecMont4)  ELSE 0 END
						+ CASE WHEN @Month > 4  THEN (d.Mont5 - d.RecMont5)  ELSE 0 END
						+ CASE WHEN @Month > 5  THEN (d.Mont6 - d.RecMont6)  ELSE 0 END
						+ CASE WHEN @Month > 6  THEN (d.Mont7 - d.RecMont7)  ELSE 0 END
						+ CASE WHEN @Month > 7  THEN (d.Mont8 - d.RecMont8)  ELSE 0 END
						+ CASE WHEN @Month > 8  THEN (d.Mont9 - d.RecMont9)  ELSE 0 END
						+ CASE WHEN @Month > 9  THEN (d.Mont10 - d.RecMont10) ELSE 0 END
						+ CASE WHEN @Month > 10 THEN (d.Mont11 - d.RecMont11) ELSE 0 END
						+ CASE WHEN @Month > 11 THEN (d.Mont12 - d.RecMont12) ELSE 0 END
					),0),
				   @AllRequestQuantity = ISNULL(SUM -- за год
				(
						 (d.Mont1 - d.RecMont1)
						+(d.Mont2 - d.RecMont2)  
						+(d.Mont3 - d.RecMont3)  
						+(d.Mont4 - d.RecMont4)  
						+(d.Mont5 - d.RecMont5)  
						+(d.Mont6 - d.RecMont6)  
						+(d.Mont7 - d.RecMont7)  
						+(d.Mont8 - d.RecMont8)  
						+(d.Mont9 - d.RecMont9)  
						+(d.Mont10 - d.RecMont10)
						+(d.Mont11 - d.RecMont11)
						+(d.Mont12 - d.RecMont12)
					),0)
				FROM #DEMAND d
				where d.Patient is null
			
				 -- превышение
				SELECT @OverReuqestPers = ISNULL(SUM(d.Curr),0),
					   @OverReuqestPersAfter = ISNULL(SUM(d.CurrYear),0)
				FROM #DEMAND d
				where d.Patient is not null and d.Patient <> @PatientID
			
			END
			--поиск остатков только по ЛПУ + филиал + перс
			ELSE
			BEGIN
				--перс + бранч
				update d
					set	   d.RecMont1 =   ISNULL(rec.RecMont1, 0)
						   ,d.RecMont2 =  ISNULL(rec.RecMont2, 0)
						   ,d.RecMont3 =  ISNULL(rec.RecMont3, 0)
						   ,d.RecMont4 =  ISNULL(rec.RecMont4, 0)
						   ,d.RecMont5 =  ISNULL(rec.RecMont5, 0)
						   ,d.RecMont6 =  ISNULL(rec.RecMont6, 0)
						   ,d.RecMont7 =  ISNULL(rec.RecMont7, 0)
						   ,d.RecMont8 =  ISNULL(rec.RecMont8, 0)
						   ,d.RecMont9 =  ISNULL(rec.RecMont9, 0)
						   ,d.RecMont10 = ISNULL(rec.RecMont10, 0)
						   ,d.RecMont11 = ISNULL(rec.RecMont11, 0)
						   ,d.RecMont12 = ISNULL(rec.RecMont12, 0)
					from #DEMAND d
					outer apply (
						select SUM(RecMont1) as RecMont1,
							   SUM(RecMont2) as RecMont2,
							   SUM(RecMont3) as RecMont3,
							   SUM(RecMont4) as RecMont4,
							   SUM(RecMont5) as RecMont5,
							   SUM(RecMont6) as RecMont6,
							   SUM(RecMont7) as RecMont7,
							   SUM(RecMont8) as RecMont8,
							   SUM(RecMont9) as RecMont9,
							   SUM(RecMont10) as RecMont10,
							   SUM(RecMont11) as RecMont11,
							   SUM(RecMont12) as RecMont12
						from #RECIPE r
						where r.Patient = d.Patient and r.Branch = d.Branch
						group by r.LpuMain, r.Patient, r.Branch
						) rec
					where d.[Patient] is not null and d.Branch is not null

				--перс без бранча
				update d
					set	   d.RecMont1 =   ISNULL(rec.RecMont1, 0)
						   ,d.RecMont2 =  ISNULL(rec.RecMont2, 0)
						   ,d.RecMont3 =  ISNULL(rec.RecMont3, 0)
						   ,d.RecMont4 =  ISNULL(rec.RecMont4, 0)
						   ,d.RecMont5 =  ISNULL(rec.RecMont5, 0)
						   ,d.RecMont6 =  ISNULL(rec.RecMont6, 0)
						   ,d.RecMont7 =  ISNULL(rec.RecMont7, 0)
						   ,d.RecMont8 =  ISNULL(rec.RecMont8, 0)
						   ,d.RecMont9 =  ISNULL(rec.RecMont9, 0)
						   ,d.RecMont10 = ISNULL(rec.RecMont10, 0)
						   ,d.RecMont11 = ISNULL(rec.RecMont11, 0)
						   ,d.RecMont12 = ISNULL(rec.RecMont12, 0)
					from #DEMAND d
					outer apply (
						select SUM(RecMont1) as RecMont1,
							   SUM(RecMont2) as RecMont2,
							   SUM(RecMont3) as RecMont3,
							   SUM(RecMont4) as RecMont4,
							   SUM(RecMont5) as RecMont5,
							   SUM(RecMont6) as RecMont6,
							   SUM(RecMont7) as RecMont7,
							   SUM(RecMont8) as RecMont8,
							   SUM(RecMont9) as RecMont9,
							   SUM(RecMont10) as RecMont10,
							   SUM(RecMont11) as RecMont11,
							   SUM(RecMont12) as RecMont12
						from #RECIPE r
						where r.Patient = d.Patient
						and not exists (select * from #DEMAND dd where dd.[Patient] is not null and dd.Branch is not null and dd.Patient = r.Patient and dd.Branch = r.Branch)
						group by r.LpuMain, r.Patient
						) rec
					where d.[Patient] is not null and d.Branch is null

				--бранч без перса 
				update d
					set	   d.RecMont1 =   ISNULL(rec.RecMont1, 0)
						   ,d.RecMont2 =  ISNULL(rec.RecMont2, 0)
						   ,d.RecMont3 =  ISNULL(rec.RecMont3, 0)
						   ,d.RecMont4 =  ISNULL(rec.RecMont4, 0)
						   ,d.RecMont5 =  ISNULL(rec.RecMont5, 0)
						   ,d.RecMont6 =  ISNULL(rec.RecMont6, 0)
						   ,d.RecMont7 =  ISNULL(rec.RecMont7, 0)
						   ,d.RecMont8 =  ISNULL(rec.RecMont8, 0)
						   ,d.RecMont9 =  ISNULL(rec.RecMont9, 0)
						   ,d.RecMont10 = ISNULL(rec.RecMont10, 0)
						   ,d.RecMont11 = ISNULL(rec.RecMont11, 0)
						   ,d.RecMont12 = ISNULL(rec.RecMont12, 0)
					from #DEMAND d
					outer apply (
						select SUM(RecMont1) as RecMont1,
							   SUM(RecMont2) as RecMont2,
							   SUM(RecMont3) as RecMont3,
							   SUM(RecMont4) as RecMont4,
							   SUM(RecMont5) as RecMont5,
							   SUM(RecMont6) as RecMont6,
							   SUM(RecMont7) as RecMont7,
							   SUM(RecMont8) as RecMont8,
							   SUM(RecMont9) as RecMont9,
							   SUM(RecMont10) as RecMont10,
							   SUM(RecMont11) as RecMont11,
							   SUM(RecMont12) as RecMont12
						from #RECIPE r
						where r.Branch = d.Branch
						and not exists (select * from #DEMAND dd where (dd.Patient = r.Patient and dd.Branch = r.Branch)
																		or (dd.Patient = r.Patient and dd.Branch is null))
						group by r.LpuMain, r.Branch
						) rec
					where d.[Patient] is null and d.Branch is not null

				--только МО
				update d
					set	   d.RecMont1 =   ISNULL(rec.RecMont1, 0)
						   ,d.RecMont2 =  ISNULL(rec.RecMont2, 0)
						   ,d.RecMont3 =  ISNULL(rec.RecMont3, 0)
						   ,d.RecMont4 =  ISNULL(rec.RecMont4, 0)
						   ,d.RecMont5 =  ISNULL(rec.RecMont5, 0)
						   ,d.RecMont6 =  ISNULL(rec.RecMont6, 0)
						   ,d.RecMont7 =  ISNULL(rec.RecMont7, 0)
						   ,d.RecMont8 =  ISNULL(rec.RecMont8, 0)
						   ,d.RecMont9 =  ISNULL(rec.RecMont9, 0)
						   ,d.RecMont10 = ISNULL(rec.RecMont10, 0)
						   ,d.RecMont11 = ISNULL(rec.RecMont11, 0)
						   ,d.RecMont12 = ISNULL(rec.RecMont12, 0)
					from #DEMAND d
					outer apply (
						select SUM(RecMont1) as RecMont1,
							   SUM(RecMont2) as RecMont2,
							   SUM(RecMont3) as RecMont3,
							   SUM(RecMont4) as RecMont4,
							   SUM(RecMont5) as RecMont5,
							   SUM(RecMont6) as RecMont6,
							   SUM(RecMont7) as RecMont7,
							   SUM(RecMont8) as RecMont8,
							   SUM(RecMont9) as RecMont9,
							   SUM(RecMont10) as RecMont10,
							   SUM(RecMont11) as RecMont11,
							   SUM(RecMont12) as RecMont12
						from #RECIPE r
						where not exists (select * from #DEMAND dd where (dd.Patient = r.Patient and dd.Branch = r.Branch)
																		or (dd.Patient = r.Patient and dd.Branch is null)
																		or (dd.Branch = r.Branch and dd.Patient is null))
						group by r.LpuMain
						) rec
					where d.[Patient] is null and d.Branch is null
		
				--перс потребность
				SELECT @RequestQuantityPers = ISNULL(SUM -- на текущий месяц перс
					(								 (d.Mont1 - d.RecMont1)
						+ CASE WHEN @Month > 1  THEN (d.Mont2 - d.RecMont2)  ELSE 0 END
						+ CASE WHEN @Month > 2  THEN (d.Mont3 - d.RecMont3)  ELSE 0 END
						+ CASE WHEN @Month > 3  THEN (d.Mont4 - d.RecMont4)  ELSE 0 END
						+ CASE WHEN @Month > 4  THEN (d.Mont5 - d.RecMont5)  ELSE 0 END
						+ CASE WHEN @Month > 5  THEN (d.Mont6 - d.RecMont6)  ELSE 0 END
						+ CASE WHEN @Month > 6  THEN (d.Mont7 - d.RecMont7)  ELSE 0 END
						+ CASE WHEN @Month > 7  THEN (d.Mont8 - d.RecMont8)  ELSE 0 END
						+ CASE WHEN @Month > 8  THEN (d.Mont9 - d.RecMont9)  ELSE 0 END
						+ CASE WHEN @Month > 9  THEN (d.Mont10 - d.RecMont10) ELSE 0 END
						+ CASE WHEN @Month > 10 THEN (d.Mont11 - d.RecMont11) ELSE 0 END
						+ CASE WHEN @Month > 11 THEN (d.Mont12 - d.RecMont12) ELSE 0 END
					),0),
					   @AllRequestQuantityPers = ISNULL(SUM -- за год перс
					(
						 (d.Mont1 - d.RecMont1)
						+(d.Mont2 - d.RecMont2)  
						+(d.Mont3 - d.RecMont3)  
						+(d.Mont4 - d.RecMont4)  
						+(d.Mont5 - d.RecMont5)  
						+(d.Mont6 - d.RecMont6)  
						+(d.Mont7 - d.RecMont7)  
						+(d.Mont8 - d.RecMont8)  
						+(d.Mont9 - d.RecMont9)  
						+(d.Mont10 - d.RecMont10)
						+(d.Mont11 - d.RecMont11)
						+(d.Mont12 - d.RecMont12)
					),0)
				FROM #DEMAND d
				where d.Patient is not null and d.Patient = @PatientID and (d.Branch = @BranchID or d.Branch is null)

				--общая потребность
				SELECT @RequestQuantity = ISNULL(SUM -- на текущий месяц
				(									 (d.Mont1 - d.RecMont1)
						+ CASE WHEN @Month > 1  THEN (d.Mont2 - d.RecMont2)  ELSE 0 END
						+ CASE WHEN @Month > 2  THEN (d.Mont3 - d.RecMont3)  ELSE 0 END
						+ CASE WHEN @Month > 3  THEN (d.Mont4 - d.RecMont4)  ELSE 0 END
						+ CASE WHEN @Month > 4  THEN (d.Mont5 - d.RecMont5)  ELSE 0 END
						+ CASE WHEN @Month > 5  THEN (d.Mont6 - d.RecMont6)  ELSE 0 END
						+ CASE WHEN @Month > 6  THEN (d.Mont7 - d.RecMont7)  ELSE 0 END
						+ CASE WHEN @Month > 7  THEN (d.Mont8 - d.RecMont8)  ELSE 0 END
						+ CASE WHEN @Month > 8  THEN (d.Mont9 - d.RecMont9)  ELSE 0 END
						+ CASE WHEN @Month > 9  THEN (d.Mont10 - d.RecMont10) ELSE 0 END
						+ CASE WHEN @Month > 10 THEN (d.Mont11 - d.RecMont11) ELSE 0 END
						+ CASE WHEN @Month > 11 THEN (d.Mont12 - d.RecMont12) ELSE 0 END
					),0),
				   @AllRequestQuantity = ISNULL(SUM -- за год
				(
						 (d.Mont1 - d.RecMont1)
						+(d.Mont2 - d.RecMont2)  
						+(d.Mont3 - d.RecMont3)  
						+(d.Mont4 - d.RecMont4)  
						+(d.Mont5 - d.RecMont5)  
						+(d.Mont6 - d.RecMont6)  
						+(d.Mont7 - d.RecMont7)  
						+(d.Mont8 - d.RecMont8)  
						+(d.Mont9 - d.RecMont9)  
						+(d.Mont10 - d.RecMont10)
						+(d.Mont11 - d.RecMont11)
						+(d.Mont12 - d.RecMont12)
					),0)
				FROM #DEMAND d
				where d.Patient is null and (d.Branch is null or d.Branch = @BranchID)
			
				 -- превышение
				SELECT @OverReuqestPers = ISNULL(SUM(d.Curr),0),
					   @OverReuqestPersAfter = ISNULL(SUM(d.CurrYear),0)
				FROM #DEMAND d
				where (d.Patient is not null and d.Branch is not null and d.Patient <> @PatientID and d.Branch <> @BranchID)
					or (d.Patient is not null and d.Branch is null and d.Patient <> @PatientID)
					or (d.Patient is null and d.Branch is not null and d.Branch <> @BranchID)
			END
		
			SELECT @RestOnDate = @RequestQuantity + @RequestQuantityPers + @OverReuqestPers
		
			SELECT @RestOnYear =  @AllRequestQuantity + @AllRequestQuantityPers + @OverReuqestPersAfter

			drop table #RECIPE
			drop table #DEMAND
		END