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_RDS_DrugRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DrugRequest]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-03-14
-- Description: Выгрузка/загрузка заявок по ДЛО в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_DrugRequest]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

SET NOCOUNT ON;

declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)
           ,@FTSEND varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'
          ,@FTSEND = 'FTSend'

	IF (@Xml is null)
    BEGIN
	-- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'DRUG_REQUEST'
              ,@OwnerID = @ID

	--выясняем тип операции
        IF (@ID is null)
        BEGIN
            select @OperationType = @FTSEND
        END
        ELSE IF(@IsNew = 1)
        BEGIN
            select @OperationType = @INSERT
        END
        ELSE IF (@IsNew = 0 and exists (select * from [AKUZ].T_DRUG_REQUEST dr where dr.DrugRequestID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_DRUG_REQUEST dr where dr.DrugRequestID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result = 
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'DrugRequestID'
                FOR XML PATH(''),
                ROOT('DRUG_REQUEST')
            ) 
        END

		-- Обработка операции
        IF (@OperationType = @FTSEND)
        BEGIN

		-- собираем все DRUG_REQUEST
            SELECT @Result = 
            ( 
				select @FTSEND                      as 'header/TYPE'
                      ,(
						select dr.DrugRequestID     as 'DrugRequestID'
							  ,convert(varchar(10), bdr.RequestDate, 20)		as 'RequestDate'
							  ,bdr.Number			as 'Number'
							  ,bdr.Active			as 'Active'
							  ,bdr.Additional			as 'Additional'
							  ,convert(varchar(10), bdr.BeginPeriod, 20)		as 'BeginPeriod'
							  ,convert(varchar(10), bdr.EndPeriod, 20)		as 'EndPeriod'
							  ,bdr.Name				as 'Name'
							  ,dr.ImportFileName	as 'ImportFileName'
							  ,bdr.Comment			as 'Comment'
							  ,lp.LpuCode			as 'LpuMain'
							  ,br.BranchCode		as 'BranchCode'
							  ,drM.DrugRequestID	as 'DrugRequestID'
							  ,dr.RequestType		as 'RequestType'
							  ,bdr.[Status]			as 'Status'
							  ,bdr.[Limit]			as 'Limit'
							  ,er.CodeEHR			as 'EhrRusEj36pmtjqk'
							  ,rb.CurrVersion		as 'EhrRusEj36pmtjqkVer'
							  ,erVzn.IdEHR			as 'EhrRusVzn17'
							  ,rbVzn.CurrVersion	as 'EhrRusVzn17Ver'
							  --собираем DRUG_DEMAND
							  ,(
									select	 dd.DrugDemandID		as 'DrugDemandID'
											,dd.DemandOnPeriod		as 'DemandOnPeriod'
											,dd.RequestQuantity     as 'RequestQuantity'
											,dd.RequestCode			as 'RequestCode'
											,dd.LSFO				as 'LSFO'
											,dd.Comment				as 'Comment'
											,dd.DrugRequest			as 'DrugRequest'
											,dd.SectionCode			as 'SectionCode'
											,dd.RequestCatalog		as 'RequestCatalog'
											--собираем PHARMACY_DRUG_DEMAND
											,(
												select pdd.PharmacyDrugDemandID as 'PharmacyDrugDemandID'
													  ,pdd.DemandOnPeriod		as 'DemandOnPeriod'
													  ,pdd.RequestQuantity		as 'RequestQuantity'
													  ,pdd.Mont1				as 'Mont1'
													  ,pdd.Mont2				as 'Mont2'
													  ,pdd.Mont3				as 'Mont3'
													  ,pdd.Mont4				as 'Mont4'
													  ,pdd.Mont5				as 'Mont5'
													  ,pdd.Mont6				as 'Mont6'
													  ,pdd.Mont7				as 'Mont7'
													  ,pdd.Mont8				as 'Mont8'
													  ,pdd.Mont9				as 'Mont9'
													  ,pdd.Mont10				as 'Mont10'
													  ,pdd.Mont11				as 'Mont11'
													  ,pdd.Mont12				as 'Mont12'
													  ,pdd.DrugDemand			as 'DrugDemand'
													  ,pdd.Pharmacy				as 'Pharmacy'
													  --собираем PATIENT_DRUG_DEMAND
													  ,(
														select pat.PatientDrugDemandID	as 'PatientDrugDemandID'
															  ,pat.PharmacyDrugDemand	as 'PharmacyDrugDemand'
															  ,pat.DemandOnPeriod		as 'DemandOnPeriod'
															  ,pat.RequestQuantity		as 'RequestQuantity'
															  ,pat.Mont1				as 'Mont1'
															  ,pat.Mont2				as 'Mont2'
															  ,pat.Mont3				as 'Mont3'
															  ,pat.Mont4				as 'Mont4'
															  ,pat.Mont5				as 'Mont5'
															  ,pat.Mont6				as 'Mont6'
															  ,pat.Mont7				as 'Mont7'
															  ,pat.Mont8				as 'Mont8'
															  ,pat.Mont9				as 'Mont9'
															  ,pat.Mont10				as 'Mont10'
															  ,pat.Mont11				as 'Mont11'
															  ,pat.Mont12				as 'Mont12'
															  ,p.Birthday				as 'Birthday'
															  ,p.FirstName				as 'FirstName'
															  ,p.FatherName				as 'FatherName'
															  ,p.LastName				as 'LastName'
															  ,eSex.Code				as 'Sex'
															  ,b.BenefitCode			as 'BenefitCode'
															  ,pat.Signa				as 'Signa'
															  ,worker.FIO				as 'Post'
															  ,b.BenefitViews			as 'BenefitsType'
															  ,pat.MKB					as 'MKB'
															  ,pat.Comment				as 'Comment'
															  ,p.SNILS					as 'Snils'
															  ,case when p.DocSeries = '0' then null
															  		else p.DocSeries 
															   end						as 'DocSeries'
															  ,case when p.DocNumber = '0' then null
															  		else p.DocNumber 
															   end						as 'DocNumber'
															  ,convert(varchar(10), p.DocIssueDate, 104)  as 'DocIssueDate'
															  ,p.DocIssueOrg							  as 'DocIssueOrg'
															  ,eDt.Code									  as 'DocType'
															  ,polis.Number as 'PolicyNumber'
															  ,polis.Serial as 'PolicySerial'
														from [AKUZ].T_PATIENT_DRUG_DEMAND pat
															join [AKUZ].T_PATIENT p on p.PatientID = pat.Patient
														    join [VCLib].T_ENUMS eDt on eDt.EnumID = p.DocType
															join [VCLib].T_ENUMS eSex on eSex.EnumID = p.Sex
															left join [AKUZ].T_BENEFITS b on b.BenefitsID = pat.Benefits
															left join AKUZ.T_POST post on post.ResourceID = pat.Post
															left join AKUZ.T_WORKER worker on post.Worker = worker.WorkerID
															left join [AKUZ].T_POLICY polis on polis.PolicyID = [dbo].fn_GetOmsPolicy(pat.Patient, @dateTime)
														where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
														FOR XML PATH('PATIENT_DRUG_DEMAND'),
														TYPE
													   )
												from [AKUZ].T_PHARMACY_DRUG_DEMAND pdd
												where dd.DrugDemandID = pdd.DrugDemand
												FOR XML PATH('PHARMACY_DRUG_DEMAND'),
												TYPE
											 )
									from [AKUZ].T_DRUG_DEMAND dd
									where dd.DrugRequest = dr.DrugRequestID
									FOR XML PATH('DRUG_DEMAND'),
									TYPE
							   )
						from [AKUZ].T_DRUG_REQUEST dr
							join [AKUZ].T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
							join [AKUZ].T_LPU_MAIN lp on lp.LpuID = bdr.LpuMain
							left join [AKUZ].T_BRANCH br on br.LpuID = dr.Branch
							left join [AKUZ].T_DRUG_REQUEST drM on drM.DrugRequestID = bdr.DrugRequestMain
							left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = bdr.EhrRusEj36pmtjqk
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rb ON rb.EhrRusRefBooksID = er.EhrRusRefBooks
							left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = bdr.EhrRusVzn17
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rbVzn ON rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
						where bdr.Status = 3
						FOR XML PATH('DRUG_REQUEST'),
						TYPE
						) as 'body/DRUG_REQUEST_LIST'
                FOR XML PATH(''),
                ROOT('DRUG_REQUEST')
            )

		END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

		-- собираем DRUG_REQUEST
            SELECT @Result = 
            ( 
                select @OperationType       as 'header/TYPE'
                      ,@ID                  as 'DrugRequestID'
					  ,bdr.RequestDate		as 'RequestDate'
					  ,bdr.Number			as 'Number'
					  ,bdr.Active			as 'Active'
					  ,bdr.Additional		as 'Additional'
					  ,bdr.BeginPeriod		as 'BeginPeriod'
					  ,bdr.EndPeriod		as 'EndPeriod'
					  ,bdr.Name				as 'Name'
					  ,dr.ImportFileName	as 'ImportFileName'
					  ,bdr.Comment			as 'Comment'
					  ,lp.LpuCode			as 'LpuMain'
					  ,drM.DrugRequestID	as 'DrugRequestID'
					  ,dr.RequestType		as 'RequestType'
					  ,br.BranchCode		as 'BranchCode'
					  ,bdr.[Status]			as 'Status'
					  ,bdr.[Limit]			as 'Limit'
					  ,er.CodeEHR			as 'EhrRusEj36pmtjqk'
					  ,rb.CurrVersion		as 'EhrRusEj36pmtjqkVer'
					  ,erVzn.IdEHR			as 'EhrRusVzn17'
					  ,rbVzn.CurrVersion	as 'EhrRusVzn17Ver'
					  --собираем DRUG_DEMAND
					  ,(
							select	 dd.DrugDemandID		as 'DrugDemandID'
									,dd.DemandOnPeriod		as 'DemandOnPeriod'
									,dd.RequestQuantity     as 'RequestQuantity'
									,dd.RequestCode			as 'RequestCode'
									,dd.LSFO				as 'LSFO'
									,dd.Comment				as 'Comment'
									,dd.DrugRequest			as 'DrugRequest'
									,dd.SectionCode			as 'SectionCode'
									,dd.RequestCatalog		as 'RequestCatalog'
									--собираем PHARMACY_DRUG_DEMAND
											,(
												select pdd.PharmacyDrugDemandID as 'PharmacyDrugDemandID'
													  ,pdd.DemandOnPeriod		as 'DemandOnPeriod'
													  ,pdd.RequestQuantity		as 'RequestQuantity'
													  ,pdd.Mont1				as 'Mont1'
													  ,pdd.Mont2				as 'Mont2'
													  ,pdd.Mont3				as 'Mont3'
													  ,pdd.Mont4				as 'Mont4'
													  ,pdd.Mont5				as 'Mont5'
													  ,pdd.Mont6				as 'Mont6'
													  ,pdd.Mont7				as 'Mont7'
													  ,pdd.Mont8				as 'Mont8'
													  ,pdd.Mont9				as 'Mont9'
													  ,pdd.Mont10				as 'Mont10'
													  ,pdd.Mont11				as 'Mont11'
													  ,pdd.Mont12				as 'Mont12'
													  ,pdd.DrugDemand			as 'DrugDemand'
													  ,pdd.Pharmacy				as 'Pharmacy'
													  --собираем PATIENT_DRUG_DEMAND
													  ,(
														select pat.PatientDrugDemandID	as 'PatientDrugDemandID'
															  ,pat.PharmacyDrugDemand	as 'PharmacyDrugDemand'
															  ,pat.DemandOnPeriod		as 'DemandOnPeriod'
															  ,pat.RequestQuantity		as 'RequestQuantity'
															  ,pat.Mont1				as 'Mont1'
															  ,pat.Mont2				as 'Mont2'
															  ,pat.Mont3				as 'Mont3'
															  ,pat.Mont4				as 'Mont4'
															  ,pat.Mont5				as 'Mont5'
															  ,pat.Mont6				as 'Mont6'
															  ,pat.Mont7				as 'Mont7'
															  ,pat.Mont8				as 'Mont8'
															  ,pat.Mont9				as 'Mont9'
															  ,pat.Mont10				as 'Mont10'
															  ,pat.Mont11				as 'Mont11'
															  ,pat.Mont12				as 'Mont12'
															  ,p.Birthday				as 'Birthday'
															  ,p.FirstName				as 'FirstName'
															  ,p.FatherName				as 'FatherName'
															  ,p.LastName				as 'LastName'
															  ,eSex.Code				as 'Sex'
															  ,b.BenefitCode			as 'BenefitCode'
															  ,pat.Signa				as 'Signa'
															  ,worker.FIO				as 'Post'
															  ,b.BenefitViews			as 'BenefitsType'
															  ,pat.MKB					as 'MKB'
															  ,pat.Comment				as 'Comment'
															  ,p.SNILS					as 'Snils'
															  ,case when p.DocSeries = '0' then null
															  		else p.DocSeries 
															   end						as 'DocSeries'
															  ,case when p.DocNumber = '0' then null
															  		else p.DocNumber 
															   end						as 'DocNumber'
															  ,convert(varchar(10), p.DocIssueDate, 104)  as 'DocIssueDate'
															  ,p.DocIssueOrg							  as 'DocIssueOrg'
															  ,eDt.Code									  as 'DocType'
															  ,polis.Number as 'PolicyNumber'
															  ,polis.Serial as 'PolicySerial'
														from [AKUZ].T_PATIENT_DRUG_DEMAND pat
															join [AKUZ].T_PATIENT p on p.PatientID = pat.Patient
														    join [VCLib].T_ENUMS eDt on eDt.EnumID = p.DocType
															join [VCLib].T_ENUMS eSex on eSex.EnumID = p.Sex
															left join [AKUZ].T_BENEFITS b on b.BenefitsID = pat.Benefits
															left join AKUZ.T_POST post on post.ResourceID = pat.Post
															left join AKUZ.T_WORKER worker on post.Worker = worker.WorkerID
															left join [AKUZ].T_POLICY polis on polis.PolicyID = [dbo].fn_GetOmsPolicy(pat.Patient, @dateTime)
														where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
														FOR XML PATH('PATIENT_DRUG_DEMAND'),
														TYPE
													   )
												from [AKUZ].T_PHARMACY_DRUG_DEMAND pdd
												where dd.DrugDemandID = pdd.DrugDemand
												FOR XML PATH('PHARMACY_DRUG_DEMAND'),
												TYPE
											 )
							from [AKUZ].T_DRUG_DEMAND dd
							where dd.DrugRequest = dr.DrugRequestID
							FOR XML PATH('DRUG_DEMAND'),
							TYPE
					   )
                from [AKUZ].T_DRUG_REQUEST dr
					join [AKUZ].T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
					join [AKUZ].T_LPU_MAIN lp on lp.LpuID = bdr.LpuMain
					left join [AKUZ].T_BRANCH br on br.LpuID = dr.Branch
					left join [AKUZ].T_DRUG_REQUEST drM on drM.DrugRequestID = bdr.DrugRequestMain
					left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = bdr.EhrRusEj36pmtjqk
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rb ON rb.EhrRusRefBooksID = er.EhrRusRefBooks
					left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = bdr.EhrRusVzn17
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rbVzn ON rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
				where dr.DrugRequestID = @ID
                FOR XML PATH(''),
                ROOT('DRUG_REQUEST')
            ) 

		END

		-- Отправка
        INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,@QueueName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
	END
	ELSE 
    IF (@Xml is not null)
    BEGIN
		
		-- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
        from @Xml.nodes('DRUG_REQUEST') T(c)

		-- Generate table #DRUG_REQUEST
        select t.c.value('./DrugRequestID[1]', 'uniqueidentifier')      as [DrugRequestID]
			  ,t.c.value('./RequestDate[1]', 'datetime')				as [RequestDate]
              ,t.c.value('./Active[1]', 'bit')							as [Active]
              ,t.c.value('./Additional[1]', 'bit')						as [Additional]
			  ,t.c.value('./Number[1]', 'int')							as [Number]
              ,t.c.value('./BeginPeriod[1]', 'datetime')				as [BeginPeriod]
              ,t.c.value('./EndPeriod[1]', 'datetime')					as [EndPeriod]
              ,t.c.value('./Name[1]', 'varchar(255)')					as [Name]
              ,t.c.value('./ImportFileName[1]', 'varchar(255)')			as [ImportFileName]
              ,t.c.value('./Comment[1]', 'varchar(8000)')				as [Comment]
              ,t.c.value('./LpuMain[1]', 'varchar(6)')					as [LpuMain]
              ,t.c.value('./DrugRequestMain[1]', 'uniqueidentifier')	as [DrugRequestMain]
              ,t.c.value('./RequestType[1]', 'int')						as [RequestType]
			  ,t.c.value('./Status[1]', 'int')							as [Status]
			  ,t.c.value('./Limit[1]', 'decimal(31, 10)')				as [Limit]
			  ,t.c.value('./BranchCode[1]', 'varchar(16)')				as [BranchCode]
            INTO #DRUG_REQUEST
        from @Xml.nodes('DRUG_REQUEST') T(c)
        where @OperationType <> @FTLOAD

		-- Generate table #DRUG_DEMAND
        select t.c.value('./DrugDemandID[1]', 'uniqueidentifier')	as [DrugDemandID]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')	as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')	as [RequestQuantity]
			  ,t.c.value('./RequestCode[1]', 'int')					as [RequestCode]
			  ,t.c.value('./LSFO[1]', 'int')						as [LSFO]
			  ,t.c.value('./Comment[1]', 'varchar(8000)')           as [Comment]
			  ,t.c.value('./DrugRequest[1]', 'uniqueidentifier')	as [DrugRequest]
			  ,t.c.value('./SectionCode[1]', 'varchar(255)')        as [SectionCode]
			  ,t.c.value('./RequestCatalog[1]', 'uniqueidentifier') as [RequestCatalog]
		INTO #DRUG_DEMAND
        from @Xml.nodes('DRUG_REQUEST/DRUG_DEMAND') T(c)
        where @OperationType <> @FTLOAD

		
		-- Generate table #PATIENT_DRUG_DEMAND
		select t.c.value('./PatientDrugDemandID[1]', 'uniqueidentifier')	as PatientDrugDemandID
			  ,t.c.value('./PharmacyDrugDemand[1]', 'uniqueidentifier')		as [PharmacyDrugDemand]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')						as [DemandOnPeriod]
			  ,t.c.value('./Birthday[1]', 'datetime')						as [Birthday]
			  ,t.c.value('./FirstName[1]', 'varchar(40)')					as [FirstName]
			  ,t.c.value('./FatherName[1]', 'varchar(40)')					as [FatherName]
			  ,t.c.value('./LastName[1]', 'varchar(40)')					as [LastName]
			  ,t.c.value('./BenefitCode[1]', 'int')							as [BenefitCode]
			  ,t.c.value('./BenefitType[1]', 'int')							as [BenefitType]
			  ,t.c.value('./MKB[1]', 'varchar(10)')							as [MKB]
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
			  ,t.c.value('./Snils[1]', 'varchar(14)')						as [Snils]
		INTO #PATIENT_DRUG_DEMAND
        from @Xml.nodes('DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND') T(c)
        where @OperationType <> @FTLOAD

		-- Generate table #DRUG_REQUEST_COMMENTS
		select t.c.value('./DrugRequestCommentsID[1]', 'uniqueidentifier')	as DrugRequestCommentsID
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as Comment
			  ,t.c.value('./RequestCode[1]', 'int')							as RequestCode
			  ,t.c.value('./Processed[1]', 'int')							as Processed
			  ,t.c.value('./UserProcessed[1]', 'int')						as UserProcessed
			  ,t.c.value('./Worker[1]', 'varchar(500)')						as Worker
			  ,t.c.value('./DrugRequest[1]', 'uniqueidentifier')			as DrugRequest
		INTO #DRUG_REQUEST_COMMENTS
        from @Xml.nodes('DRUG_REQUEST/DRUG_REQUEST_COMMENTS') T(c)
        where @OperationType <> @FTLOAD
		
		IF(@OperationType = @DELETE)
		BEGIN
			RAISERROR('Операция не поддерживается', 16, 10)
		END
		ELSE
		BEGIN

			update dr
            set dr.Comment = dre.Comment, dr.Status = dre.Status, dr.Limit = dre.Limit
            from [AKUZ].T_BASE_DRUG_REQUEST dr
                join #DRUG_REQUEST dre on dre.DrugRequestID = dr.DrugRequestID

			update dd
            set dd.Comment = ddl.Comment
            from [AKUZ].T_DRUG_DEMAND dd
                join #DRUG_DEMAND ddl on ddl.DrugDemandID = dd.DrugDemandID
				
			update pdd
            set pdd.Comment = pddl.Comment
            from [AKUZ].T_PATIENT_DRUG_DEMAND pdd
                join #PATIENT_DRUG_DEMAND pddl on pddl.PatientDrugDemandID = pdd.PatientDrugDemandID

			update drc
			set drc.Comment = rc.Comment,
				drc.Processed = rc.Processed
			from [AKUZ].T_DRUG_REQUEST_COMMENTS drc
                join #DRUG_REQUEST_COMMENTS rc on rc.DrugRequestCommentsID = rc.DrugRequestCommentsID

			delete drc
			from [AKUZ].[T_DRUG_REQUEST_COMMENTS] drc
			where not exists (select * from #DRUG_REQUEST_COMMENTS rc where drc.DrugRequestCommentsID = rc.DrugRequestCommentsID)

			INSERT INTO [AKUZ].[T_DRUG_REQUEST_COMMENTS]
			           ([DrugRequestCommentsID]
			           ,[Comment]
			           ,[RequestCode]
			           ,[Processed]
			           ,[UserProcessed]
			           ,[Worker]
			           ,[DrugRequest])
			select 
			           DrugRequestCommentsID,
					   Comment,
					   RequestCode,
					   Processed,
					   UserProcessed,
					   Worker,
					   DrugRequest
			from #DRUG_REQUEST_COMMENTS drc
			where not exists (select * from AKUZ.[T_DRUG_REQUEST_COMMENTS] rc where drc.DrugRequestCommentsID = rc.DrugRequestCommentsID)
		END

		drop table #DRUG_REQUEST
		drop table #DRUG_DEMAND
		drop table #PATIENT_DRUG_DEMAND
		drop table #DRUG_REQUEST_COMMENTS
	END
END
GO
