IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ExportFrlloRequestDataProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ExportFrlloRequestDataProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_ExportFrlloRequestDataProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_ExportFrlloRequestDataProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2021 01 14
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_ExportFrlloRequestDataProcess]
    @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'																---1
			,(
				select 
				efd.ExportFrlloDrugRequestItemID								as 'document_id',				---2.1
				CONVERT(varchar(19), GETDATE(), 126)							as 'doc_date_time'				---2.2
			,(																	
				select															
					--??														as 'citizen/register_id',		---2.3.1
					--pdd.PatientDrugDemandID									as 'citizen/ext_citizen_id',	-- ?? ---2.3.2
					pdd.FirstName												as 'citizen/name',				--2.3.3
					pdd.LastName												as 'citizen/surname',			--2.3.4
					pdd.FatherName												as 'citizen/patronymic',			--2.3.5
					convert(varchar(10), pdd.Birthday, 20)						as 'citizen/birthdate',			--2.3.6
					eSex.Code													as 'citizen/sex',				--2.3.7
					Replace(Replace(pdd.SNILS, '-',''), ' ', '')				as 'citizen/snils',				--2.3.9
					(case when Len(pdd.OmsNumber) = 16 then pdd.OmsNumber else null end)		as 'citizen/policy_sn',													--2.3.10
					(case when er.CodeEHR is not null and pdd.DocNumber is not null and pdd.DocIssueDate is not null then er.CodeEHR else null end)				as 'citizen/identify_docs/doc/doc_type',						--2.3.11.1
					(case when er.CodeEHR is not null and pdd.DocNumber is not null and pdd.DocIssueDate is not null then pdd.DocSeries else null end)			as 'citizen/identify_docs/doc/serial',							--2.3.11.2
					(case when er.CodeEHR is not null and pdd.DocNumber is not null and pdd.DocIssueDate is not null then pdd.DocNumber else null end)			as 'citizen/identify_docs/doc/num',								--2.3.11.3
					(case when er.CodeEHR is not null and pdd.DocNumber is not null and pdd.DocIssueDate is not null then convert(varchar(10), pdd.DocIssueDate, 20) else null end) as 'citizen/identify_docs/doc/date_issue',	--2.3.11.4
					--'??'				as 'identify_docs/doc/date_expiry',				--2.3.13.5
					(case when er.CodeEHR is not null then pdd.DocIssueOrg else null end)		as 'citizen/identify_docs/doc/authority',				--2.3.13.6
					@Region														as 'citizen/region',				--2.3.8
					--is_delete													as 'plan/is_delete',				--2.4.1
					pdd.PatientDrugDemandID										as 'plan/ext_id',					--2.4.2
					--pers_id													as 'plan/pers_id',					--2.4.3
					erZK.CodeEHR												as 'plan/appl_prcd_code',			--2.4.4
					benEhr.CodeEHR												as 'plan/benefit_code',				--2.4.5
					--is_delete													as 'plan/needs/need/is_delete',		--2.4.5.1
					cmnn.CodeEHR												as 'plan/needs/need/drug_smnn_code',--2.4.5.2
					trn.NameRus  												as 'plan/needs/need/drug_name',		--2.4.5.3
					medProd.CodeEHR  											as 'plan/needs/need/med_equip_code',--2.4.5.4
					medNutr.CodeEHR  											as 'plan/needs/need/nutrition_code',--2.4.5.5
					case when medProd.CodeEHR is not null or medNutr.CodeEHR is not null then pdd.RequestQuantity
						else pdd.RequestQuantity * dali.ConsumerUnits end as 'plan/needs/need/qty'--2.4.5.6
				from RDS.T_PATIENT_DRUG_DEMAND pdd
					left join VCLib.T_ENUMS eSex on eSex.EnumID = pdd.Sex
					left join EHR.T_EHR_RUS_5O5E88VHC6 er on er.EhrRusID = pdd.EhrRus5o5e88vhc6 and er.IS_DUL = 1 
					left join RDS.T_BENEFITS b on b.BenefitsID = pdd.Benefits
				    left join EHR.T_EHR_RUS_SDO8U3N5RO benEhr on benEhr.EhrRusID = b.EhrRusSdo8u3n5ro
					left join PHARM.T_C_TRN trn on trn.CTrnID = dali.CTrn
					left join EHR.T_EHR_RUS_9A9G09E8KL cmnn on cmnn.EhrRusID = dali.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 pddMain.PatientDrugDemandID = pdd.PatientDrugDemandID
					for xml path(''),
					type
			)
			from RDS.T_PATIENT_DRUG_DEMAND pddMain
				join RDS.T_PHARMACY_DRUG_DEMAND phdd on phdd.PharmacyDrugDemandID = pddMain.PharmacyDrugDemand
				join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
				join RDS.T_DRUG_APPLICATION_LIST_ITEM dali on dali.DrugApplicationListItemID = dd.DrugApplicationListItem
				join RDS.T_EXPORT_FRLLO_DRUG_REQUEST_ITEM efd on efd.ObjId = pddMain.PatientDrugDemandID
				join RDS.T_EXPORT_FRLLO_DRUG_REQUEST ef on ef.ExportFrlloDrugRequestID = efd.ExportFrlloDrugRequest
				join RDS.T_DRUG_REQUEST dr on dr.DrugRequestID = ef.DrugRequest
				left join EHR.T_EHR_RUS_EJ36PMTJQK erZK on erZK.EhrRusID = dr.EhrRusEj36pmtjqk
			where ef.ExportFrlloDrugRequestID = @ExportId and ex.FileNumber = efd.FileNumber and (@WithoutError = 0 or efd.FrlloError is null)
			for xml path('document'),
			type
		) as 'documents'
		for xml path(''),
        root('root')
		)
	from RDS.T_EXPORT_FRLLO_DRUG_REQUEST_ITEM ex
	where ex.ExportFrlloDrugRequest = @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				
				