DROP PROCEDURE IF EXISTS public.sp_getrequestitemrest;

CREATE PROCEDURE public.sp_getrequestitemrest
(
	"RequestItemID" uuid								-- BASE_DRUG_REQUEST_ITEM.BaseDrugRequestItemID
,	"OnDate" timestamp default NULL::timestamp
,	"LpuID" uuid default NULL::uuid
,	"RequestCatalogId" uuid default NULL::uuid			-- BASE_DRUG_REQUEST_ITEM.RequestCode
,	"BranchID" uuid default null::uuid
,	"PatientID" uuid default null::uuid
,	INOUT "RestOnDate" decimal(31, 10) DEFAULT 0 					-- Остаток на дату
,	INOUT "RestOnYear" decimal(31, 10) DEFAULT 0					-- Остаток за год
)
    language plpgsql
as
$$
Begin

	Declare	
		_FirstDay timestamp := (case when date_part('Year', "OnDate") > date_part('Year', now()) then date_trunc('year', now()::timestamp) else date_trunc('year', "OnDate") end);
		_LastDay timestamp := (case when date_part('Year', "OnDate") > date_part('Year', now()) 
									then date_trunc('year', now()::timestamp + interval '1 year') - interval '1 day' 
									else date_trunc('year', "OnDate" + interval '1 year') - interval '1 day'
								end);
		_Month integer := (case when date_part('Year', "OnDate") > date_part('Year', now()) then 12 else date_part('Month', "OnDate") end);
		_RequestQuantity decimal(31, 10) := 0;
		_AllRequestQuantity decimal(31, 10) := 0;
		_RequestQuantityPers decimal(31, 10) := 0;
		_AllRequestQuantityPers decimal(31, 10) := 0;
		_OverReuqestPers decimal(31, 10) := 0; 		-- к месяцу
		_OverReuqestPersAfter decimal(31, 10) := 0;	-- после
	Begin
		"RestOnDate" := 0;
		"RestOnYear" := 0;
		IF "OnDate" IS NULL OR "LpuID" IS NULL
		then
			RETURN;
		END if;
	
		--RECIPE
		Drop Table If Exists PreRECIPE;
		Create Temp Table PreRECIPE
		as
		SELECT r.Year
		, ben.BenefitType
		, rg.CMnn
		, rg.CTrn
		, rg.RGDoseView
		, r.MedicationForms
		, mr.PostingDate
		, r.UnitsCount
		, r.MedicalRecordID
		FROM AKUZ.T_RECIPE_GENERAL rg
		join AKUZ.T_RECIPE r ON rg.MedicalRecordID = r.MedicalRecordID
        JOIN AKUZ.T_MEDICAL_RECORD mr ON mr.MedicalRecordID = r.MedicalRecordID
		join AKUZ.T_BENEFITS ben on ben.BenefitsID = r.Benefits
		WHERE r.Deleted = '0'
		and mr.PostingDate between _FirstDay and _LastDay
		and exists
		(
			select *
			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 _FirstDay and _LastDay
		);
		
		Drop Table If Exists TmpRECIPE;
		Create Temp Table TmpRECIPE
		as
		SELECT ac.Patient
		, pr.Year
		, br.LpuMain
		, div.Branch
		, pr.BenefitType
		, pr.CMnn
		, pr.CTrn
		, pr.RGDoseView
		, pr.MedicationForms
		, case when DATE_PART('Month', pr.PostingDate) = 1 then SUM(pr.UnitsCount) else 0 end as RecMont1
		, case when DATE_PART('Month', pr.PostingDate) = 2 then SUM(pr.UnitsCount) else 0 end as RecMont2
		, case when DATE_PART('Month', pr.PostingDate) = 3 then SUM(pr.UnitsCount) else 0 end as RecMont3
		, case when DATE_PART('Month', pr.PostingDate) = 4 then SUM(pr.UnitsCount) else 0 end as RecMont4
		, case when DATE_PART('Month', pr.PostingDate) = 5 then SUM(pr.UnitsCount) else 0 end as RecMont5
		, case when DATE_PART('Month', pr.PostingDate) = 6 then SUM(pr.UnitsCount) else 0 end as RecMont6
		, case when DATE_PART('Month', pr.PostingDate) = 7 then SUM(pr.UnitsCount) else 0 end as RecMont7
		, case when DATE_PART('Month', pr.PostingDate) = 8 then SUM(pr.UnitsCount) else 0 end as RecMont8
		, case when DATE_PART('Month', pr.PostingDate) = 9 then SUM(pr.UnitsCount) else 0 end as RecMont9
		, case when DATE_PART('Month', pr.PostingDate) = 10 then SUM(pr.UnitsCount) else 0 end as RecMont10
		, case when DATE_PART('Month', pr.PostingDate) = 11 then SUM(pr.UnitsCount) else 0 end as RecMont11
		, case when DATE_PART('Month', pr.PostingDate) = 12 then SUM(pr.UnitsCount) else 0 end as RecMont12
		FROM PreRECIPE pr
		join AKUZ.T_MEDICAL_RECORD medRecord on pr.MedicalRecordID  = medRecord.MedicalRecordID
		join AKUZ.T_EVENT_BASE eb ON eb.EventID = medRecord.AmbulanceEvent
		join AKUZ.T_AMBULANCE_CARD ac ON ac.AmbulanceCardID = eb.AmbulanceCard
		join AKUZ.T_RESOURCE rs ON medRecord.Post = rs.ResourceId
		join AKUZ.T_DIVISION div ON rs.Division = div.DivisionID
		join AKUZ.T_BRANCH br ON div.Branch = br.LpuID
		where ac.CardLpu = "LpuID"
		group by ac.Patient, pr.Year, DATE_PART('Month', pr.PostingDate), br.LpuMain
		, pr.BenefitType, pr.CMnn, pr.CTrn, pr.RGDoseView, pr.MedicationForms, div.Branch;
	
		Drop Table If Exists TmpDEMAND;
		Create Temp Table TmpDEMAND(BaseDrugRequestItemID uuid, LpuMain uuid, BeginPeriod timestamp, EndPeriod timestamp, RequestType integer, Patient uuid, Branch uuid
								, Mont1 integer, Mont2 integer, Mont3 integer, Mont4 integer, Mont5 integer, Mont6 integer, Mont7 integer, Mont8 integer, Mont9 integer, Mont10 integer, Mont11 integer, Mont12 integer
								, RecMont1 integer, RecMont2 integer, RecMont3 integer, RecMont4 integer, RecMont5 integer, RecMont6 integer, RecMont7 integer, RecMont8 integer, RecMont9 integer
								, RecMont10 integer, RecMont11 integer, RecMont12 integer, Curr integer, CurrYear integer); 
	
		--DEMAND
		if "BranchID" is null
		then
			insert into TmpDEMAND (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 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
				left join lateral 
				(	
					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 on true
				left join lateral
				(
					select 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)
					limit 1
				) bdri on true
				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 _FirstDay 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;
		else
			insert into TmpDEMAND(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
				left join lateral
				(
					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 on true
				left join lateral
				(
					select 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)
					limit 1
				) bdri on true
				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 _FirstDay 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 if;
		--CREATE UNIQUE CLUSTERED INDEX IX_V_DEMAND_BaseDrugRequestItemID ON #DEMAND(BaseDrugRequestItemID, Patient, LpuMain, Branch);
			
		update TmpDEMAND d
		set 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
		,	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;
	
		--поиск остатков только по ЛПУ + перс
		if "BranchID" is null
		then
			--перс
			with pers
			as
			(
				select coalesce(rec.RecMont1, 0) as persRecMont1, coalesce(rec.RecMont2, 0) as persRecMont2, coalesce(rec.RecMont3, 0) as persRecMont3
				, coalesce(rec.RecMont4, 0) as persRecMont4, coalesce(rec.RecMont5, 0) as persRecMont5, coalesce(rec.RecMont6, 0) as persRecMont6
				, coalesce(rec.RecMont7, 0) as persRecMont7, coalesce(rec.RecMont8, 0) as persRecMont8, coalesce(rec.RecMont9, 0) as persRecMont9
				, coalesce(rec.RecMont10, 0) as persRecMont10, coalesce(rec.RecMont11, 0) as persRecMont11, coalesce(rec.RecMont12, 0) as persRecMont12
				, d.RecMont1, d.RecMont2 , d.RecMont3 , d.RecMont4 , d.RecMont5 , d.RecMont6 , d.RecMont7 , d.RecMont8 , d.RecMont9 , d.RecMont10, d.RecMont11, d.RecMont12
				from TmpDEMAND d
				left join lateral
				(
					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 TmpRECIPE r
					where r.Patient = d.Patient
					group by r.LpuMain, r.Patient
				) rec on true
				where d.Patient is not null
			)
			update pers
			set RecMont1 = persRecMont1
			,	RecMont2 = persRecMont2
			,	RecMont3 = persRecMont3
			,	RecMont4 = persRecMont4
			,	RecMont5 = persRecMont5
			,	RecMont6 = persRecMont6
			,	RecMont7 = persRecMont7
			,	RecMont8 = persRecMont8
			,	RecMont9 = persRecMont9
			,	RecMont10 = persRecMont10
			,	RecMont11 = persRecMont11
			,	RecMont12 = persRecMont12;
			--все остальные	
			with rest
			as
			(
				select d.RecMont1, coalesce(rec.RecMont1, 0) as restRecMont1
				, d.RecMont2, coalesce(rec.RecMont2, 0) as restRecMont2
				, d.RecMont3, coalesce(rec.RecMont3, 0) as restRecMont3
				, d.RecMont4, coalesce(rec.RecMont4, 0) as restRecMont4
				, d.RecMont5, coalesce(rec.RecMont5, 0) as restRecMont5
				, d.RecMont6, coalesce(rec.RecMont6, 0) as restRecMont6
				, d.RecMont7, coalesce(rec.RecMont7, 0) as restRecMont7
				, d.RecMont8, coalesce(rec.RecMont8, 0) as restRecMont8
				, d.RecMont9, coalesce(rec.RecMont9, 0) as restRecMont9
				, d.RecMont10, coalesce(rec.RecMont10, 0) as restRecMont10
				, d.RecMont11, coalesce(rec.RecMont11, 0) as restRecMont11
				, d.RecMont12, coalesce(rec.RecMont12, 0) as restRecMont12
				from TmpDEMAND d
				left join
				(
					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 TmpRECIPE r
					where not exists(select* from TmpDEMAND dd where dd.Patient = r.Patient)
					group by r.LpuMain
				) rec on true
				where d.Patient is null
			)
			update rest
			set	RecMont1 =  restRecMont1
			,	RecMont2 =  restRecMont2
			,	RecMont3 =  restRecMont3
			,	RecMont4 =  restRecMont4
			,	RecMont5 =  restRecMont5
			,	RecMont6 =  restRecMont6
			,	RecMont7 =  restRecMont7
			,	RecMont8 =  restRecMont8
			,	RecMont9 =  restRecMont9
			,	RecMont10 = restRecMont10
			,	RecMont11 = restRecMont11
			,	RecMont12 = restRecMont12;
			
			--перс потребность
			SELECT into
				_RequestQuantityPers, _AllRequestQuantityPers
				coalesce(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)
			,	coalesce(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 TmpDEMAND d
			where d.Patient is not null 
			and d.Patient = "PatientID";
	
			--общая потребность
			select into
				_RequestQuantity, _AllRequestQuantity
				coalesce(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)
			,	coalesce(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 TmpDEMAND d
			where d.Patient is null;
			
			 -- превышение
			select into
				_OverReuqestPers, _OverReuqestPersAfter
				coalesce(SUM(d.Curr), 0), coalesce(SUM(d.CurrYear), 0)
			from TmpDEMAND d
			where d.Patient is not null 
			and d.Patient <> "PatientID";		
			--поиск остатков только по ЛПУ + филиал + перс
		else
			--перс + бранч
			with persBranch
			as
			(
				select d.RecMont1, coalesce(rec.RecMont1, 0) as persRecMont1
				, d.RecMont2, coalesce(rec.RecMont2, 0) as persRecMont2
				, d.RecMont3, coalesce(rec.RecMont3, 0) as persRecMont3
				, d.RecMont4, coalesce(rec.RecMont4, 0) as persRecMont4
				, d.RecMont5, coalesce(rec.RecMont5, 0) as persRecMont5
				, d.RecMont6, coalesce(rec.RecMont6, 0) as persRecMont6
				, d.RecMont7, coalesce(rec.RecMont7, 0) as persRecMont7
				, d.RecMont8, coalesce(rec.RecMont8, 0) as persRecMont8
				, d.RecMont9, coalesce(rec.RecMont9, 0) as persRecMont9
				, d.RecMont10, coalesce(rec.RecMont10, 0) as persRecMont10
				, d.RecMont11, coalesce(rec.RecMont11, 0) as persRecMont11
				, d.RecMont12, coalesce(rec.RecMont12, 0) as persRecMont12
				from TmpDEMAND d
				left join lateral
				(
					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 TmpRECIPE r
					where r.Patient = d.Patient
					and r.Branch = d.Branch
					group by r.LpuMain, r.Patient, r.Branch
				) rec on true
				where d.Patient is not null 
				and d.Branch is not null
			)
			update persBranch
			set	RecMont1 = persRecMont1
			,	RecMont2 = persRecMont2
			,	RecMont3 = persRecMont3
			,	RecMont4 = persRecMont4
			,	RecMont5 = persRecMont5
			,	RecMont6 = persRecMont6
			,	RecMont7 = persRecMont7
			,	RecMont8 = persRecMont8
			,	RecMont9 = persRecMont9
			,	RecMont10 = persRecMont10
			,	RecMont11 = persRecMont11
			,	RecMont12 = persRecMont12;
	
			--перс без бранча
			with pers
			as
			(
				select d.RecMont1, coalesce(rec.RecMont1, 0) as persRecMont1
				, d.RecMont2, coalesce(rec.RecMont2, 0) as persRecMont2
				, d.RecMont3, coalesce(rec.RecMont3, 0) as persRecMont3
				, d.RecMont4, coalesce(rec.RecMont4, 0) as persRecMont4
				, d.RecMont5, coalesce(rec.RecMont5, 0) as persRecMont5
				, d.RecMont6, coalesce(rec.RecMont6, 0) as persRecMont6
				, d.RecMont7, coalesce(rec.RecMont7, 0) as persRecMont7
				, d.RecMont8, coalesce(rec.RecMont8, 0) as persRecMont8
				, d.RecMont9, coalesce(rec.RecMont9, 0) as persRecMont9
				, d.RecMont10, coalesce(rec.RecMont10, 0) as persRecMont10
				, d.RecMont11, coalesce(rec.RecMont11, 0) as persRecMont11
				, d.RecMont12, coalesce(rec.RecMont12, 0) as persRecMont12
				from TmpDEMAND d
				left join lateral
				(
					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 TmpRECIPE r
					where r.Patient = d.Patient
					and not exists(select* from TmpDEMAND 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 on true
				where d.Patient is not null 
				and d.Branch is null
			)
			update pers
			set	RecMont1 =  persRecMont1
			,	RecMont2 =  persRecMont2
			,	RecMont3 =  persRecMont3
			,	RecMont4 =  persRecMont4
			,	RecMont5 =  persRecMont5
			,	RecMont6 =  persRecMont6
			,	RecMont7 =  persRecMont7
			,	RecMont8 =  persRecMont8
			,	RecMont9 =  persRecMont9
			,	RecMont10 = persRecMont10
			,	RecMont11 = persRecMont11
			,	RecMont12 = persRecMont12;
	
			--бранч без перса 
			with branch
			as
			(
				select d.RecMont1, coalesce(rec.RecMont1, 0) as branchRecMont1
				, d.RecMont2, coalesce(rec.RecMont2, 0) as branchRecMont2
				, d.RecMont3, coalesce(rec.RecMont3, 0) as branchRecMont3
				, d.RecMont4, coalesce(rec.RecMont4, 0) as branchRecMont4
				, d.RecMont5, coalesce(rec.RecMont5, 0) as branchRecMont5
				, d.RecMont6, coalesce(rec.RecMont6, 0) as branchRecMont6
				, d.RecMont7, coalesce(rec.RecMont7, 0) as branchRecMont7
				, d.RecMont8, coalesce(rec.RecMont8, 0) as branchRecMont8
				, d.RecMont9, coalesce(rec.RecMont9, 0) as branchRecMont9
				, d.RecMont10, coalesce(rec.RecMont10, 0) as branchRecMont10
				, d.RecMont11, coalesce(rec.RecMont11, 0) as branchRecMont11
				, d.RecMont12, coalesce(rec.RecMont12, 0) as branchRecMont12
				from TmpDEMAND d
				left join lateral
				(
					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 TmpRECIPE r
					where r.Branch = d.Branch
					and not exists
					(
						select* from TmpDEMAND 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 on true
				where d.Patient is null 
				and d.Branch is not null
			)
			update branch
			set	RecMont1 =  branchRecMont1
			,	RecMont2 =  branchRecMont2
			,	RecMont3 =  branchRecMont3
			,	RecMont4 =  branchRecMont4
			,	RecMont5 =  branchRecMont5
			,	RecMont6 =  branchRecMont6
			,	RecMont7 =  branchRecMont7
			,	RecMont8 =  branchRecMont8
			,	RecMont9 =  branchRecMont9
			,	RecMont10 = branchRecMont10
			,	RecMont11 = branchRecMont11
			,	RecMont12 = branchRecMont12;
	
			--только МО
			with mo
			as
			(
				select d.RecMont1, coalesce(rec.RecMont1, 0) as moRecMont1
				, d.RecMont2, coalesce(rec.RecMont2, 0) as moRecMont2
				, d.RecMont3, coalesce(rec.RecMont3, 0) as moRecMont3
				, d.RecMont4, coalesce(rec.RecMont4, 0) as moRecMont4
				, d.RecMont5, coalesce(rec.RecMont5, 0) as moRecMont5
				, d.RecMont6, coalesce(rec.RecMont6, 0) as moRecMont6
				, d.RecMont7, coalesce(rec.RecMont7, 0) as moRecMont7
				, d.RecMont8, coalesce(rec.RecMont8, 0) as moRecMont8
				, d.RecMont9, coalesce(rec.RecMont9, 0) as moRecMont9
				, d.RecMont10, coalesce(rec.RecMont10, 0) as moRecMont10
				, d.RecMont11, coalesce(rec.RecMont11, 0) as moRecMont11
				, d.RecMont12, coalesce(rec.RecMont12, 0) as moRecMont12
				from TmpDEMAND d
				left join lateral
				(
					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 TmpRECIPE r
					where not exists
					(
						select* from TmpDEMAND 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 on true
				where d.Patient is null 
				and d.Branch is null
			)
			update mo
			set	RecMont1 = moRecMont1
			,	RecMont2 = moRecMont2
			,	RecMont3 = moRecMont3
			,	RecMont4 = moRecMont4
			,	RecMont5 = moRecMont5
			,	RecMont6 = moRecMont6
			,	RecMont7 = moRecMont7
			,	RecMont8 = moRecMont8
			,	RecMont9 = moRecMont9
			,	RecMont10 = moRecMont10
			,	RecMont11 = moRecMont11
			,	RecMont12 = moRecMont12;
			
			--перс потребность
			select into 
				_RequestQuantityPers, _AllRequestQuantityPers
				coalesce(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)
			,	coalesce(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 TmpDEMAND d
			where d.Patient is not null 
			and d.Patient = "PatientID"
			and (d.Branch = "BranchID" or d.Branch is null);
	
			--общая потребность
			select into
				_RequestQuantity, _AllRequestQuantity
				coalesce(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)
			,	coalesce(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 TmpDEMAND d
			where d.Patient is null 
			and (d.Branch is null or d.Branch = "BranchID");
			
			 -- превышение
			select into
				_OverReuqestPers, _OverReuqestPersAfter
				coalesce(SUM(d.Curr), 0), coalesce(SUM(d.CurrYear), 0)
			FROM TmpDEMAND 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 if;
			
		"RestOnDate" := _RequestQuantity + _RequestQuantityPers + _OverReuqestPers;
		"RestOnYear" := _AllRequestQuantity + _AllRequestQuantityPers + _OverReuqestPersAfter;
	
		drop table if exists TmpRECIPE;
		drop table if exists TmpDEMAND;
	End;
End;
$$;