SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateDrugRequestFromPatientDemand]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CreateDrugRequestFromPatientDemand]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-07-26
-- Description: Создание заявки из потребности
-- =============================================
CREATE PROCEDURE [dbo].[sp_CreateDrugRequestFromPatientDemand]
    @ID uniqueidentifier = null
AS
BEGIN
	
if(exists(select * from AKUZ.T_BASE_DRUG_REQUEST b where b.DrugRequestID = @ID and b.Status <> 0))
begin
    raiserror('Для заполнения заявки она должна быть в статусе - Новый!', 16, 10)
end

declare @Type int, @DateBegin datetime, @DateEnd datetime, @Lpu uniqueidentifier, @Branch uniqueidentifier

select top 1 @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

update dpddi
set dpddi.PatientDrugDemand = null
from AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi
join AKUZ.T_PATIENT_DRUG_DEMAND pdd on pdd.PatientDrugDemandID = dpddi.PatientDrugDemand
join AKUZ.T_PHARMACY_DRUG_DEMAND phdd on phdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
join AKUZ.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
where dpddi.PatientDrugDemand is not null and dd.DrugRequest = @ID

delete pdd 
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 dd.DrugRequest = @ID

delete phdd 
from AKUZ.T_PHARMACY_DRUG_DEMAND phdd
join AKUZ.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
where dd.DrugRequest = @ID

delete dd 
from AKUZ.T_DRUG_DEMAND dd
where dd.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))))
begin
    raiserror('Имеются элементы потребности на пациента для которых не найден элемент заявочного каталога!', 16, 10)
    return
end

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 uniqueidentifier) as 'dd', Cast(null as uniqueidentifier) as 'pdd', newid() as 'newId', dpddi.DoctorPatientDrugDemandItemsID
INTO #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS
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
	cross apply (
		SELECT top 1 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)
	) bdri
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)

select newid() as 'id', d.BaseDrugRequestItemID
INTO #DD
	from #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS d
	group by d.BaseDrugRequestItemID

update dpddi
set dpddi.dd = dd.id
from #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi
join #DD dd on dpddi.BaseDrugRequestItemID = dd.BaseDrugRequestItemID

select newid() as 'id' , d.BaseDrugRequestItemID, d.Pharmacy
INTO #PDD
from #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS d
group by d.BaseDrugRequestItemID, d.Pharmacy

update dpddi
set pdd = pdd.id
from #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi
join #PDD pdd on 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 pdd
set pdd.DemandOnPeriod = ss.DemandOnPeriod, pdd.RequestQuantity = ss.RequestQuantity, pdd.Mont1 = ss.Mont1, pdd.Mont2 = ss.Mont2, pdd.Mont3 = ss.Mont3, pdd.Mont4 = ss.Mont4, pdd.Mont5 = ss.Mont5, 
pdd.Mont6 = ss.Mont6, pdd.Mont7 = ss.Mont7, pdd.Mont8 = ss.Mont8, pdd.Mont9 = ss.Mont9, pdd.Mont10 = ss.Mont10, pdd.Mont11 = ss.Mont11, pdd.Mont12 = ss.Mont12
from AKUZ.T_PHARMACY_DRUG_DEMAND pdd
cross apply (
	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
where exists(select * from #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS d where d.pdd = pdd.PharmacyDrugDemandID)

update dd
set dd.DemandOnPeriod = ss.DemandOnPeriod, dd.RequestQuantity = ss.RequestQuantity
from AKUZ.T_DRUG_DEMAND dd
cross apply(
	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
where exists(select * from #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS d where d.dd = dd.DrugDemandID)

update dpddi
set dpddi.PatientDrugDemand = dd.newId
from AKUZ.T_DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dpddi 
join #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS dd on dd.DoctorPatientDrugDemandItemsID = dpddi.DoctorPatientDrugDemandItemsID

drop table #DOCTOR_PATIENT_DRUG_DEMAND_ITEMS
END
GO

