IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_ExportFrlloCertificateDataProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_ExportFrlloCertificateDataProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2025 07 14
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_ExportFrlloCertificateDataProcess]
    @ExportId uniqueidentifier,
    @InfoSysCode varchar(100),
    @Region varchar(5),
    @WithoutError bit,
    @Result varchar(max) OUTPUT
AS
BEGIN
	declare @OutputTable table(Val xml)

	Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'																
			,(
				select 
				statusMain.ElectronicCertificateStatusID						as 'document_id',				--2.1
				max(cer.CreateDate)												as 'doc_date_time'				--2.2
			,(																	
				select															
					'false'           											as 'cert/is_delete',			--2.3.1
					r.FrlloId												    as 'cert/recipe_id',		    --2.3.2
					cmnn.CodeEHR		                                        as 'cert/drug_smnn_code',		--2.3.3				
					medProd.CodeEHR		                                        as 'cert/med_equip_code',		--2.3.4				
					medNutr.CodeEHR		                                        as 'cert/nutrition_code',		--2.3.5				
					medNutr.CodeEHR		                                        as 'cert/nutrition_code',		--2.3.6.1				
                    (
                        select 
                            re.FrlloId                                          as 'release/release_id',        --2.3.6.1
                            cast(re.Amount*re.Price as decimal(31,2))           as 'release/sum_release_cert'   --2.3.6.2
                            
                        from RDS.T_RECIPE_ELEMENT re
                        where re.Recipe = r.RecipeID
						for xml path('releases'),
						TYPE
                    ) as 'cert',
                    stat.CertificateNumber                                      as 'cert/ext_cert_id',               --2.3.7
                    --stat.cert_id                                              as 'cert/cert_id',                   --2.3.8
                    stat.CertificateNumber                                      as 'cert/cert_num',                  --2.3.9
                    --app.LimitPrice                                     as 'cert/unit_cost',                 --2.3.10
                    cast(r.ConsumerUnits * r.UnitsCount as decimal(31,2))                    as 'cert/item_qty',                  --2.3.11
                    cast((app.LimitPrice * r.ConsumerUnits * r.UnitsCount) as decimal(31,2)) as 'cert/sum_cert',                  --2.3.12
                    case when stat.CurrentStatus = 6 then 'true'
                    else 'false' end                                             as 'cert/is_cancel',                 --2.3.13
                    convert(varchar(10), stat.StartDate, 20)                    as 'cert/start_date',                --2.3.14
                    convert(varchar(10), stat.EndDate, 20)                      as 'cert/end_date'                   --2.3.15
				from RDS.T_ELECTRONIC_CERTIFICATE_STATUS stat
                    join RDS.T_ELECTRONIC_CERTIFICATE_APPLICATION app on app.ElectronicCertificateApplicationID = stat.ElectronicCertificateApplication
                    left join RDS.T_RECIPE r on r.RecipeID = app.Recipe
					left join PHARM.T_C_TRN trn on trn.CTrnID = r.CTrn
					left join EHR.T_EHR_RUS_9A9G09E8KL cmnn on cmnn.EhrRusID = r.EhrRus9a9g09e8kl
					left join EHR.T_EHR_RUS_P08TSH954N medProd on medProd.EhrRusID = trn.EhrRusP08TSH954N
					left join EHR.T_EHR_RUS_575VSF88OG medNutr on medNutr.EhrRusID = trn.EhrRus575VSF88OG
					where stat.ElectronicCertificateStatusID = statusMain.ElectronicCertificateStatusID
					for xml path(''),
					type
			)
			from RDS.T_ELECTRONIC_CERTIFICATE_STATUS statusMain
				join RDS.T_EXPORT_FRLLO_EC_DATA efe on efe.ObjId = statusMain.ElectronicCertificateStatusID
			where efe.ExportFrlloElectronicCertificate = @ExportId and ex.FileNumber = efe.FileNumber and (@WithoutError = 0 or efe.FrlloError is null)
			for xml path('document'),
			type
		) as 'documents'
		for xml path(''),
        root('root')
		)
	from RDS.T_EXPORT_FRLLO_EC_DATA ex
        join RDS.T_EXPORT_FRLLO_ELECTRONIC_CERTIFICATE cer on cer.ExportFrlloElectronicCertificateID = ex.ExportFrlloElectronicCertificate
	where ex.ExportFrlloElectronicCertificate = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber

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