IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_ExportPrognosisNeedsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_ExportPrognosisNeedsProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ExportPrognosisNeedsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ExportPrognosisNeedsProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024 03 04
-- Description: Выгрузка сведений о сформированных прогнозируемых потребностях Граждан льготных категорий в Медицинской продукции в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_ExportPrognosisNeedsProcess]
    @ExportId int,
    @InfoSysCode varchar(100),
    @Region varchar(5),
    @WithoutError bit,
    @Result varchar(max) OUTPUT
AS
BEGIN
	
	if(exists(select * from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT where FileNumber is null and ExportFrlloNeeds = @ExportId))
	begin
		declare @FileNumber int = 1
		WHILE ((select Count(*) from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT where FileNumber is null and ExportFrlloNeeds = @ExportId) > 0)
			BEGIN
				
				update top (100000) RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT set FileNumber = @FileNumber where ExportFrlloNeeds = @ExportId
				set @FileNumber = @FileNumber + 1
				
			END
	end

	declare @OutputTable table(Val xml)
		begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode																as 'info_sys_code'
			,(
				select 
					need.FrlloDocumentId													as 'document_id',
					need.DocDateTime														as 'doc_date_time',
					Isnull(element.prognosis_needs_id, element.ExportFrlloNeedsElementID)	as 'prognosis_needs/prognosis_needs_id',			
					appl.CodeEHR															as 'prognosis_needs/appl_prcd_code',	
					need.region_okato5														as 'prognosis_needs/region_okato5',
					benCat.CodeEHR															as 'prognosis_needs/benefit_code',		--EhrRusSdo8u3n5ro
					mkb.MKB_CODE															as 'prognosis_needs/mkb10_code',		--EhrRusMkb308
					element.qty_mkb10_code													as 'prognosis_needs/qty_mkb10_code',
					element.qty_benefit														as 'prognosis_needs/qty_benefit',
					(select 
						Isnull(equip.equip_id, equip.ExportFrlloNeedsEquipID)				as 'equips/equip/equip_id',
						equip.equip_ext_id													as 'equips/equip/equip_ext_id',
						medDevice.CodeEHR													as 'equips/equip/drug_smnn_code',	--EhrRus9a9g09e8kl
						equip.drug_name														as 'equips/equip/drug_name',
						medEquip.CodeEHR													as 'equips/equip/med_equip_code',	--EhrRusP08TSH954N
						specNutrition.CodeEHR												as 'equips/equip/nutrition_code',	--EhrRus575VSF88OG
						--element.qty_count													as 'equips/equip/qty_count',
						--element.date_count												as 'equips/equip/date_count',
						equip.qty_confirm													as 'equips/equip/qty_confirm'
					from RDS.T_EXPORT_FRLLO_NEEDS_EQUIP equip
						left join EHR.T_EHR_RUS_9A9G09E8KL medDevice on medDevice.EhrRusID = equip.EhrRus9a9g09e8kl
						left join EHR.T_EHR_RUS_P08TSH954N medEquip on medEquip.EhrRusID = equip.EhrRusP08TSH954N
						left join EHR.T_EHR_RUS_575VSF88OG specNutrition on specNutrition.EhrRusID = equip.EhrRus575VSF88OG
					where equip.ExportFrlloNeedsElement = element.ExportFrlloNeedsElementID
						for xml path('prognosis_needs')
						, type
					)
				from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT element
					left join RDS.T_EXPORT_FRLLO_NEEDS need on need.ExportFrlloNeedsID = element.ExportFrlloNeeds
					left join EHR.T_EHR_RUS_SDO8U3N5RO benCat on benCat.EhrRusID = element.EhrRusSdo8u3n5ro
					left join EHR.T_EHR_RUS_MKB308 mkb on mkb.EhrRusID = element.EhrRusMkb308
					left join EHR.T_EHR_RUS_EJ36PMTJQK appl on appl.EhrRusID = element.EhrRusEj36pmtjqk
				where need.ExportFrlloNeedsID = @ExportId and needsElementMain.FileNumber = element.FileNumber and (@WithoutError = 0 or element.FrlloError is null)
			for xml path(''),
			type
		) as 'documents/document'
		for xml path(''),
        root('root')
		)
	from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT needsElementMain
	where needsElementMain.ExportFrlloNeeds = @ExportId and (@WithoutError = 0 or needsElementMain.FrlloError is null)
	group by needsElementMain.FileNumber
	end	

    SELECT @Result = CAST((SELECT val FROM  @OutputTable FOR XML PATH(''), ROOT('root_list'), TYPE) AS VARCHAR(MAX));
END				
				