DROP PROCEDURE IF EXISTS sp_CreateDrugRequestFromPatientDemand;

CREATE PROCEDURE sp_CreateDrugRequestFromPatientDemand
(
	"ID" uuid default null::uuid
)
    language plpgsql
as
$$
Begin
	declare 
	"Type" integer;
	"DateBegin" timestamp; 
	"DateEnd" timestamp;
	"Lpu" uuid; 
	"Branch" uuid;
	Begin
		if(exists(select * from AKUZ.T_BASE_DRUG_REQUEST b where b.DrugRequestID = "ID" and b.Status <> 0))
		then
			RAISE EXCEPTION 'Для заполнения заявки она должна быть в статусе - Новый!';
            return;
		end if;
		
		select "Type" = dr.RequestType, "DateBegin" = bdr.BeginPeriod, "DateEnd" = bdr.EndPeriod, "Lpu" = bdr.LpuMain, "Branch" = dr.Branch
		from AKUZ.T_DRUG_REQUEST dr
		join AKUZ.T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
		where dr.DrugRequestID = "ID" limit 1;
		
		update AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi
		set PatientDrugDemand = null
		from AKUZ.T_PATIENT_DRUG_DEMAND pdd
			join AKUZ.T_PHARMACY_DRUG_DEMAND phdd on phdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
			join AKUZ.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
		where pdd.PatientDrugDemandID = dpddi.PatientDrugDemand and pdd.PatientDrugDemandID = dpddi.PatientDrugDemanddpddi.PatientDrugDemand is not null and dd.DrugRequest = "ID";
		
		delete 
		from AKUZ.T_PATIENT_DRUG_DEMAND pdd
			using AKUZ.T_PHARMACY_DRUG_DEMAND phdd
			join AKUZ.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
		where phdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand and dd.DrugRequest = "ID";
		
		delete  
		from AKUZ.T_PHARMACY_DRUG_DEMAND phdd
			using AKUZ.T_DRUG_DEMAND dd
		where dd.DrugDemandID = phdd.DrugDemand and dd.DrugRequest = "ID";
		
		delete from AKUZ.T_DRUG_DEMAND where DrugRequest = "ID";
		
		if(exists(select * 
					from AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi 
						join AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND dpdd on dpdd.DoctorPatientDrugDemandID = dpddi.DoctorPatientDrugDemand
						join AKUZ.S_DLO_DoseView dv ON dv.id = dpdd.DoseView
						join AKUZ.T_RESOURCE p on p.ResourceID = dpddi.Post
						join AKUZ.T_DIVISION d on d.DivisionID = p.Division
						join AKUZ.T_BRANCH b on b.LpuID = d.Branch
						join AKUZ.T_BENEFITS ben on ben.BenefitsID = dpdd.Benefits
					where ben.BenefitType = "Type" and dpddi.BeginPeriod <= "DateBegin" 
							and dpddi.EndPeriod >= "DateEnd" and dpddi.PatientDrugDemand is null and b.LpuMain = "Lpu"
							and ("Branch" is null or b.LpuID = "Branch")
							and not exists (SELECT *
												FROM AKUZ.T_BASE_DRUG_REQUEST_ITEM bdri 
													LEFT JOIN AKUZ.T_REQUEST_CATALOG rc ON rc.BaseDrugRequestItemID = bdri.BaseDrugRequestItemID
													LEFT JOIN AKUZ.S_DLO_DoseView dvv ON dvv.id = rc.DoseView
												WHERE (bdri.AppListBeginDate<=dpddi.BeginPeriod AND bdri.AppListEndDate>=dpddi.EndPeriod AND rc.BenefitType=ben.BenefitType AND 
														((rc.CMnn IS NULL AND dpdd.CMnn IS NULL) OR (rc.CMnn=dpdd.CMnn)) 
														AND ((rc.CTrn IS NULL AND dpdd.CTrn IS NULL) OR (rc.CTrn=dpdd.CTrn)) AND bdri.MedicationForms=dpdd.MedicationForms AND dvv.Value=dv.Value AND rc.IsActive='1'))))
		then
		    RAISE EXCEPTION 'Имеются элементы потребности на пациента для которых не найден элемент заявочного каталога!';
            return;
		end if;

		
		DROP TABLE IF EXISTS DOCTOR_PATIENT_DRUG_DEMAND_ITEMS;
		CREATE TEMP TABLE DOCTOR_PATIENT_DRUG_DEMAND_ITEMS(
		    RequestCode integer
		  , BaseDrugRequestItemID uuid
		  , Pharmacy integer
		  , Benefits uuid
		  , Comment text
		  , Diagnosis varchar(4000)
		  , MKB varchar(10)
		  , Patient uuid
		  , Post uuid
		  , Signa varchar(255)
		  , DrugKekResultMedicament uuid
		  , Mont1 decimal(12,2)
		  , Mont2 decimal(12,2)
		  , Mont3 decimal(12,2)
		  , Mont4 decimal(12,2)
		  , Mont5 decimal(12,2)
		  , Mont6 decimal(12,2)
		  , Mont7 decimal(12,2)
		  , Mont8 decimal(12,2)
		  , Mont9 decimal(12,2)
		  , Mont10 decimal(12,2)
		  , Mont11 decimal(12,2)
		  , Mont12 decimal(12,2)
		  , DemandOnPeriod decimal(12,2)
		  , RequestQuantity decimal(12,2)
		  , dd uuid
		  , pdd uuid
		  , newId uuid
		  , DoctorPatientDrugDemandItemsID uuid
		);

		INSERT INTO "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS"(RequestCode
			  , BaseDrugRequestItemID
			  , Pharmacy
			  , Benefits
			  , Comment
			  , Diagnosis
			  , MKB
			  , Patient
			  , Post
			  , Signa
			  , DrugKekResultMedicament
			  , Mont1
			  , Mont2
			  , Mont3
			  , Mont4
			  , Mont5
			  , Mont6
			  , Mont7
			  , Mont8
			  , Mont9
			  , Mont10
			  , Mont11
			  , Mont12
			  , DemandOnPeriod
			  , RequestQuantity
			  , dd
			  , pdd
			  , newId
			  , DoctorPatientDrugDemandItemsID)
		select bdri.RequestCode, bdri.BaseDrugRequestItemID, dpdd.Pharmacy, dpdd.Benefits, dpdd.Comment, dpdd.Diagnosis, dpdd.MKB, dpdd.Patient, dpddi.Post, dpdd.Signa, dpdd.DrugKekResultMedicament,
				dpddi.Mont1,dpddi.Mont2,dpddi.Mont3,dpddi.Mont4,dpddi.Mont5,dpddi.Mont6,dpddi.Mont7,dpddi.Mont8,dpddi.Mont9,dpddi.Mont10,dpddi.Mont11,dpddi.Mont12,dpddi.DemandOnPeriod,dpddi.RequestQuantity, 
				Cast(null as uuid), Cast(null as uuid), gen_random_uuid(), dpddi.DoctorPatientDrugDemandItemsID
		from AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi 
			join AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND dpdd on dpdd.DoctorPatientDrugDemandID = dpddi.DoctorPatientDrugDemand
			join AKUZ.S_DLO_DoseView dv ON dv.id = dpdd.DoseView
			join AKUZ.T_RESOURCE p on p.ResourceID = dpddi.Post
			join AKUZ.T_DIVISION d on d.DivisionID = p.Division
			join AKUZ.T_BRANCH b on b.LpuID = d.Branch
			join AKUZ.T_BENEFITS ben on ben.BenefitsID = dpdd.Benefits
			join lateral (
				SELECT bdri.RequestCode, bdri.BaseDrugRequestItemID
					FROM AKUZ.T_BASE_DRUG_REQUEST_ITEM bdri 
						LEFT JOIN AKUZ.T_REQUEST_CATALOG rc ON rc.BaseDrugRequestItemID = bdri.BaseDrugRequestItemID
						LEFT JOIN AKUZ.S_DLO_DoseView dvv ON dvv.id = rc.DoseView
					WHERE (bdri.AppListBeginDate<=dpddi.BeginPeriod AND bdri.AppListEndDate>=dpddi.EndPeriod AND rc.BenefitType=ben.BenefitType AND 
						  ((rc.CMnn IS NULL AND dpdd.CMnn IS NULL) OR (rc.CMnn=dpdd.CMnn)) 
						  AND ((rc.CTrn IS NULL AND dpdd.CTrn IS NULL) OR (rc.CTrn=dpdd.CTrn)) AND bdri.MedicationForms=dpdd.MedicationForms AND dvv.Value=dv.Value AND rc.IsActive='1') limit 1
			) bdri on true
		where ben.BenefitType = "Type" and dpddi.BeginPeriod <= "DateBegin" 
			and dpddi.EndPeriod >= "DateEnd" and dpddi.PatientDrugDemand is null and b.LpuMain = "Lpu"
			and ("Branch" is null or b.LpuID = "Branch");
		
		DROP TABLE IF EXISTS "DD";
		CREATE TEMP TABLE "DD"(
		    id uuid,
		    BaseDrugRequestItemID uuid
			);

		INSERT INTO "DD"(id, BaseDrugRequestItemID)
		select gen_random_uuid(), d.BaseDrugRequestItemID
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d
		group by d.BaseDrugRequestItemID;
		
		update "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" dpddi
		set dd = dd.id
		from "DD" dd 
		where dpddi.BaseDrugRequestItemID = dd.BaseDrugRequestItemID;
		
		DROP TABLE IF EXISTS "PDD";
		CREATE TEMP TABLE "PDD"(
		    id uuid,
		    BaseDrugRequestItemID uuid,
		    Pharmacy integer
			);

		INSERT INTO "PDD"
		select gen_random_uuid(), d.BaseDrugRequestItemID, d.Pharmacy
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d
		group by d.BaseDrugRequestItemID, d.Pharmacy;
		
		update "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" dpddi
		set pdd = pdd.id
		from "PDD" pdd 
		where dpddi.BaseDrugRequestItemID = pdd.BaseDrugRequestItemID and pdd.Pharmacy = dpddi.Pharmacy;
		
		drop table "DD";
		drop table "PDD";
		
		INSERT INTO AKUZ.T_DRUG_DEMAND
		           (DrugDemandID
		           ,DrugRequest
		           ,DemandOnPeriod
		           ,RequestQuantity
		           ,RequestCode
		           ,PersRequest
				   ,RequestCatalog)
		select d.dd
				,"ID"
				,0
				,0
				,d.RequestCode
				,1
				,d.BaseDrugRequestItemID
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d
		group by d.dd, d.RequestCode, d.BaseDrugRequestItemID;
		
		INSERT INTO AKUZ.T_PHARMACY_DRUG_DEMAND
		           (PharmacyDrugDemandID
		           ,DrugDemand
		           ,DemandOnPeriod
		           ,Pharmacy
		           ,RequestQuantity
		           ,Mont1
		           ,Mont2
		           ,Mont3
		           ,Mont4
		           ,Mont5
		           ,Mont6
		           ,Mont7
		           ,Mont8
		           ,Mont9
		           ,Mont10
		           ,Mont11
		           ,Mont12)
		select d.pdd
				,d.dd
				,0
				,d.Pharmacy
				,0
				,0
				,0
				,0
				,0
				,0
				,0
				,0
				,0
				,0
				,0
				,0
				,0
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d
		group by d.pdd, d.dd,d.Pharmacy;
		
		INSERT INTO AKUZ.T_PATIENT_DRUG_DEMAND
		           (PatientDrugDemandID
		           ,DemandOnPeriod
		           ,Patient
		           ,PharmacyDrugDemand
		           ,Comment
		           ,Benefits
		           ,MKB
		           ,Diagnosis
		           ,Signa
		           ,RequestQuantity
		           ,Mont1
		           ,Mont2
		           ,Mont3
		           ,Mont4
		           ,Mont5
		           ,Mont6
		           ,Mont7
		           ,Mont8
		           ,Mont9
		           ,Mont10
		           ,Mont11
		           ,Mont12
		           ,Post
		           ,DrugKekResultMedicaments)
		select d.newId	
				,d.DemandOnPeriod
				,d.Patient
				,d.pdd
				,d.Comment
				,d.Benefits
				,d.MKB
				,d.Diagnosis
				,d.Signa
				,d.RequestQuantity
				,d.Mont1
				,d.Mont2
				,d.Mont3
				,d.Mont4
				,d.Mont5
				,d.Mont6
				,d.Mont7
				,d.Mont8
				,d.Mont9
				,d.Mont10
				,d.Mont11
				,d.Mont12
				,d.Post
				,d.DrugKekResultMedicament
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d;
		
		update AKUZ.T_PHARMACY_DRUG_DEMAND pdd
		set DemandOnPeriod = ss."DemandOnPeriod", 
			RequestQuantity = ss."RequestQuantity", 
			Mont1 = ss."Mont1",
			Mont2 = ss."Mont2", 
			Mont3 = ss."Mont3", 
			Mont4 = ss."Mont4", 
			Mont5 = ss."Mont5", 
			Mont6 = ss."Mont6", 
			Mont7 = ss."Mont7", 
			Mont8 = ss."Mont8", 
			Mont9 = ss."Mont9", 
			Mont10 = ss."Mont10", 
			Mont11 = ss."Mont11", 
			Mont12 = ss."Mont12"
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" dd
		join lateral (
			select SUM(d.DemandOnPeriod) as "DemandOnPeriod" , 
			SUM(d.RequestQuantity) as "RequestQuantity", 
			SUM(d.Mont1) as "Mont1", 
			SUM(d.Mont2) as "Mont2", 
			SUM(d.Mont3) as "Mont3", 
			SUM(d.Mont4) as "Mont4"
					, SUM(d.Mont5) as "Mont5", SUM(d.Mont6) as "Mont6", SUM(d.Mont7) as "Mont7", SUM(d.Mont8) as "Mont8", SUM(d.Mont9) as "Mont9", SUM(d.Mont10) as "Mont10", SUM(d.Mont11) as "Mont11", SUM(d.Mont12) as "Mont12"
			from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d 
			where d.pdd = pdd.PharmacyDrugDemandID
			group by d.pdd
		) ss on true
		where pd.pdd = pdd.PharmacyDrugDemandID;
		
		update AKUZ.T_DRUG_DEMAND dd
		set DemandOnPeriod = ss."DemandOnPeriod", 
			RequestQuantity = ss."RequestQuantity"
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" ddd
		join lateral (
			select SUM(d.DemandOnPeriod) as "DemandOnPeriod" , SUM(d.RequestQuantity) as "RequestQuantity"
			from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" d 
			where d.dd = dd.DrugDemandID
			group by d.dd
		) ss on true
		where ddd.dd = dd.DrugDemandID;
		
		update AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi 
		set PatientDrugDemand = dd.newId
		from "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS" dd 
		where dd.DoctorPatientDrugDemandItemsID = dpddi.DoctorPatientDrugDemandItemsID;
		
		drop table "DOCTOR_PATIENT_DRUG_DEMAND_ITEMS";
	End;
End;
$$;

