IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetPrognosisNeedsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetPrognosisNeedsProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_GetPrognosisNeedsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_GetPrognosisNeedsProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Natalia.Danilova
-- Create date: 2024 01 31
-- Description: Получение сведений о сформированных прогнозируемых потребностях Граждан льготных категорий в Медицинской продукции (ФРЛЛО)
-- =============================================
CREATE PROCEDURE [rds].[sp_GetPrognosisNeedsProcess]
    @ExportId int,
    @Xml xml
	--,@Clean bit = 0

AS
BEGIN
if (@Xml is not null)
	begin
		--if(@Clean = 1)
		--begin
		--	delete from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT where ExportFrlloNeeds = @ExportId
		--	delete from RDS.T_EXPORT_FRLLO_NEEDS where ExportFrlloNeedsID = @ExportId
		--end

		select
			t.c.value('./document_id[1]', 'varchar(100)')							as [document_id]
			,t.c.value('./doc_date_time[1]', 'datetime')							as [doc_date_time]
			into #NEED
			from @Xml.nodes('root/documents/document') T(c)

		select
			prog.c.value('./document_id[1]', 'varchar(100)')					as [document_id] -- чтобы сцепить с #NEEED_ELEMENTS
			,t.c.value('./prognosis_needs_id[1]', 'varchar(100)')					as [prognosis_needs_id]
			,t.c.value('./appl_prcd_code[1]', 'varchar(64)')						as [appl_prcd_code]
			,t.c.value('./region_okato5[1]', 'varchar(5)')							as [region_okato5]
			,t.c.value('./benefit_code[1]', 'varchar(100)')							as [benefit_code]
			,t.c.value('./mkb10_code[1]', 'varchar(100)')							as [mkb10_code]
			,t.c.value('./qty_mkb10_code[1]', 'int')								as [qty_mkb10_code]
			,t.c.value('./qty_benefit[1]', 'int')							        as [qty_benefit]
			into #NEED_ELEMENTS
			from @Xml.nodes('root/documents/document') prog(c)
			OUTER APPLY prog.c.nodes('./prognosis_needs') t(c)
			
		select
			prog.c.value('./prognosis_needs_id[1]', 'varchar(100)')					as [prognosis_needs_id] -- чтобы сцепить с #NEEED_ELEMENTS
			,t.c.value('./equip_id[1]', 'varchar(100)')								as [equip_id]
			,t.c.value('./equip_ext_id[1]', 'varchar(100)')							as [equip_ext_id]
			,t.c.value('./drug_smnn_code[1]', 'varchar(100)')						as [drug_smnn_code]
			,t.c.value('./drug_name[1]', 'varchar(1000)')							as [drug_name]
			,t.c.value('./med_equip_code[1]', 'varchar(100)')						as [med_equip_code]
			,t.c.value('./nutrition_code[1]', 'int')								as [nutrition_code]
			,t.c.value('./qty_count[1]', 'decimal(31,10)')							as [qty_count]
			,t.c.value('./date_count[1]', 'datetime')								as [date_count]
			into #NEEED_ELEMENTS_EQUIP
			from @Xml.nodes('root/documents/document/prognosis_needs') prog(c)
			OUTER APPLY prog.c.nodes('./equips/equip') t(c)
	
		select
			prog.c.value('./prognosis_needs_id[1]', 'varchar(100)')					as [prognosis_needs_id] -- чтобы сцепить с #NEEED_ELEMENTS
			,convert(nvarchar(max), ('./statistics[1]')) as statXml
			into #STATICTICS		
			from @Xml.nodes('root/documents/document/prognosis_needs') prog(c)
			OUTER APPLY prog.c.nodes('./equips') T(c)
			
		update needFrllo
		set needFrllo.DocDateTime = n.doc_date_time
			--,needFrllo.FrlloDocumentId = n.document_id
			,needFrllo.region_okato5 = n.region_okato5
		from RDS.T_EXPORT_FRLLO_NEEDS needFrllo
		cross apply(
			select top 1 n.doc_date_time, n.document_id , needElement.region_okato5 
				from #NEED n 
			join #NEED_ELEMENTS needElement on n.document_id = needElement.document_id
		) n
        where needFrllo.ExportFrlloNeedsID = @ExportId

		declare @EhrRusSdo8u3n5ro int, @EhrRusMkb308 int, @EhrRus9a9g09e8kl int, @EhrRusP08TSH954N int, @EhrRus575VSF88OG int, @EhrRusEj36pmtjqk int, @EntIdE int, @EntIdQ int
		select @EhrRusSdo8u3n5ro = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EHR_RUS_SDO8U3N5RO'
		select @EhrRusMkb308 = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EHR_RUS_MKB308'
		select @EhrRus9a9g09e8kl = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EHR_RUS_9A9G09E8KL'
		select @EhrRusP08TSH954N = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EHR_RUS_P08TSH954N'
		select @EhrRus575VSF88OG = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EHR_RUS_575VSF88OG'
		select @EhrRusEj36pmtjqk = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EHR_RUS_EJ36PMTJQK'
		select @EntIdE = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EXPORT_FRLLO_NEEDS_ELEMENT'
		select @EntIdQ = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'EXPORT_FRLLO_NEEDS_EQUIP'
		
		declare @StartId int
		select top 1 @StartId = id from dbo.T_ENTITY_IDS where EntName = 'RDS.EXPORT_FRLLO_NEEDS_ELEMENT' and FieldName = 'PrimaryKey'

		INSERT INTO [RDS].[T_EXPORT_FRLLO_NEEDS_ELEMENT]
           ([ExportFrlloNeedsElementID]
           ,[prognosis_needs_id]
           ,[qty_mkb10_code]
           ,[qty_benefit]
           ,[Statistics]
           ,[ExportFrlloNeeds]
           ,[EhrRusSdo8u3n5ro]
           ,[EhrRusMkb308]
           ,[EhrRusEj36pmtjqk])
		SELECT ROW_NUMBER() OVER(order by needElements.document_id) + ISNULL(@StartId,0)
			,needElements.prognosis_needs_id
			,needElements.qty_mkb10_code
			,needElements.qty_benefit
			,dbo.ConvertTextToBinaryZip(stat.statXml,'')
			,@ExportId
			,ehr.EhrRusID -- EhrRusSdo8u3n5ro
			,ehr1.EhrRusID -- EhrRusMkb308
			,ehr5.EhrRusID
		FROM #NEED_ELEMENTS needElements
			left join #STATICTICS stat on stat.prognosis_needs_id = needElements.prognosis_needs_id
			outer apply 
			(select top 1 prep.EhrRusID
				from EHR.T_EHR_RUS_SDO8U3N5RO prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
					join EHR.T_EHR_REFERENCE ref on ref.EhrRusRefBooks = rrb.EhrRusRefBooksID and ref.AttributeName = 'EhrRusSdo8u3n5ro' and ref.EhrEntity = @EhrRusSdo8u3n5ro and ref.Entities = @EntIdE
				where prep.CodeEHR = needElements.benefit_code
			) ehr
			outer apply (
				select top 1 prep.EhrRusID 
				from EHR.T_EHR_RUS_MKB308 prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
					join EHR.T_EHR_REFERENCE ref on ref.EhrRusRefBooks = rrb.EhrRusRefBooksID and ref.AttributeName = 'EhrRusMkb308' and ref.EhrEntity = @EhrRusMkb308 and ref.Entities = @EntIdE
				where prep.MKB_CODE = needElements.mkb10_code
			) ehr1
			outer apply (
				select top 1 prep.EhrRusID 
				from EHR.T_EHR_RUS_EJ36PMTJQK prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
					join EHR.T_EHR_REFERENCE ref on ref.EhrRusRefBooks = rrb.EhrRusRefBooksID and ref.AttributeName = 'EhrRusEj36pmtjqk' and ref.EhrEntity = @EhrRus575VSF88OG and ref.Entities = @EntIdE
				where prep.CodeEHR = needElements.appl_prcd_code
			) ehr5

		select top 1 @StartId = id from dbo.T_ENTITY_IDS where EntName = 'RDS.EXPORT_FRLLO_NEEDS_EQUIP' and FieldName = 'PrimaryKey'
		INSERT INTO [RDS].[T_EXPORT_FRLLO_NEEDS_EQUIP]
		           ([ExportFrlloNeedsEquipID]
		           ,[equip_id]
		           ,[equip_ext_id]
		           ,[drug_name]
		           ,[qty_count]
		           ,[date_count]
		           ,[ExportFrlloNeedsElement]
		           ,[EhrRus9a9g09e8kl]
		           ,[EhrRusP08TSH954N]
		           ,[EhrRus575VSF88OG])
		select	ROW_NUMBER() OVER(order by equip.equip_id) + ISNULL(@StartId,0) 
				,equip.equip_id
				,equip.equip_ext_id
				,equip.drug_name
				,equip.qty_count
				,equip.date_count
				,elem.ExportFrlloNeedsElementID
				,ehr2.EhrRusID -- EhrRus9a9g09e8kl
				,ehr3.EhrRusID -- EhrRusP08TSH954N
				,ehr4.EhrRusID -- EhrRus575VSF88OG
		from #NEEED_ELEMENTS_EQUIP equip
		join [RDS].[T_EXPORT_FRLLO_NEEDS_ELEMENT] elem on elem.prognosis_needs_id = equip.prognosis_needs_id and elem.ExportFrlloNeeds = @ExportId
			outer apply (
				select top 1 prep.EhrRusID 
				from EHR.T_EHR_RUS_9A9G09E8KL prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
					join EHR.T_EHR_REFERENCE ref on ref.EhrRusRefBooks = rrb.EhrRusRefBooksID and ref.AttributeName = 'EhrRus9a9g09e8kl' and ref.EhrEntity = @EhrRus9a9g09e8kl and ref.Entities = @EntIdQ
				where prep.CodeEHR = equip.drug_smnn_code
			) ehr2
			outer apply (
				select top 1 prep.EhrRusID 
				from EHR.T_EHR_RUS_P08TSH954N prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
					join EHR.T_EHR_REFERENCE ref on ref.EhrRusRefBooks = rrb.EhrRusRefBooksID and ref.AttributeName = 'EhrRusP08TSH954N' and ref.EhrEntity = @EhrRusP08TSH954N and ref.Entities = @EntIdQ
				where prep.CodeEHR = equip.med_equip_code
			) ehr3
			outer apply (
				select top 1 prep.EhrRusID 
				from EHR.T_EHR_RUS_575VSF88OG prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
					join EHR.T_EHR_REFERENCE ref on ref.EhrRusRefBooks = rrb.EhrRusRefBooksID and ref.AttributeName = 'EhrRus575VSF88OG' and ref.EhrEntity = @EhrRus575VSF88OG and ref.Entities = @EntIdQ
				where prep.CodeEHR = equip.nutrition_code 
			) ehr4

        update dbo.T_ENTITY_IDS set id = Coalesce((select max(ExportFrlloNeedsElementID) + 1 from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT), 0) where EntName = 'RDS.EXPORT_FRLLO_NEEDS_ELEMENT' and FieldName = 'PrimaryKey';
        update dbo.T_ENTITY_IDS set id = Coalesce((select max(ExportFrlloNeedsEquipID) + 1 from RDS.T_EXPORT_FRLLO_NEEDS_EQUIP), 0) where EntName = 'RDS.EXPORT_FRLLO_NEEDS_EQUIP' and FieldName = 'PrimaryKey';
	end
END				
				