IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ExportFrlloDataProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ExportFrlloDataProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2021 01 14
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [dbo].[sp_ExportFrlloDataProcess]
    @ExportId uniqueidentifier,
    @InfoSysCode varchar(100),
    @Region varchar(5),
    @WithoutError bit
AS
BEGIN
	declare @Type varchar(100)
	select @Type = ee.EntName 
	from RDS.T_EXPORT_FRLLO e 
	join VCLib.T_ENTITIES ee on ee.EntityID = e.Entities
	where e.ExportFrlloID = @ExportId

	declare @OutputTable table(Val xml)

	if(@Type = 'RR_PATIENT')
	begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'
			,(
				select 
				efd.ExportFrlloDataID	as 'document_id',
				ef.ActualDate			as 'doc_date_time'
			,(
				select
					--'??'				as 'register_id',							--2.3.1
					r.RrPatientID		as 'ext_citizen_id',						--2.3.2
					r.FirstName			as 'name',									--2.3.3
					r.LastName			as 'surname',								--2.3.4
					r.FatherName		as 'patronymic',								--2.3.5
					r.BirthSurname		as 'firstsurname',							--2.3.6
					convert(varchar(10), r.Birthday, 20) as 'birthdate',								--2.3.7
					eSex.Code			as 'sex',									--2.3.8
					643					as 'citizenship',							--2.3.9
					--''				as 'citizenship_confirm/doc_type',			--2.3.10.1
					--''				as 'citizenship_confirm/doc_type_name',		--2.3.10.2
					--''				as 'citizenship_confirm/serial',			--2.3.10.3
					--''				as 'citizenship_confirm/num',				--2.3.10.4
					--''				as 'citizenship_confirm/date_issue',		--2.3.10.5
					--''				as 'citizenship_confirm/date_expiry',		--2.3.10.6
					--''				as 'citizenship_confirm/authority',			--2.3.10.7
					Replace(Replace(r.SNILS, '-',''), ' ', '')				as 'snils',									--2.3.11
					r.Enp				as 'policy_sn',								--2.3.12
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then er.CodeEHR else null end)			as 'identify_docs/doc/doc_type',						--2.3.13.1
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocSeries else null end)			as 'identify_docs/doc/serial',					--2.3.13.2
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocNumber else null end)			as 'identify_docs/doc/num',						--2.3.13.3
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then convert(varchar(10), r.DocIssueDate, 20) else null end) as 'identify_docs/doc/date_issue',				--2.3.13.4
					--'??'				as 'identify_docs/doc/date_expiry',				--2.3.13.5
					(case when er.CodeEHR is not null then r.DocIssueOrg else null end)		as 'identify_docs/doc/authority',				--2.3.13.6
					@Region				as 'region'									--2.3.14
					--r.				as 'addresses/address/address_type_code',	--2.3.15.1
					--r.				as 'addresses/address/fias_address',		--2.3.15.2
					--r.				as 'addresses/address/fias_home',			--2.3.15.3
					--r.				as 'addresses/address/region',				--2.3.15.4
					--r.				as 'addresses/address/area',				--2.3.15.5
					--r.				as 'addresses/address/prefix_area',			--2.3.15.6
					--r.				as 'addresses/address/street',				--2.3.15.7
					--r.				as 'addresses/address/prefix_street',		--2.3.15.8
					--r.				as 'addresses/address/house_num',			--2.3.15.9
					--r.				as 'addresses/address/build_num',			--2.3.15.10
					--r.				as 'addresses/address/struc_num',			--2.3.15.11
					--r.				as 'addresses/address/room_num',			--2.3.15.12
					--r.				as 'addresses/address/live_start_date',		--2.3.15.13
					--r.				as 'addresses/address/live_end_date',		--2.3.15.14
				from RDS.T_RR_PATIENT r
					join VCLib.T_ENUMS eSex on eSex.EnumID = r.Sex
					left join EHR.T_EHR_RUS_5O5E88VHC6 er on er.EhrRusID = r.EhrRus5o5e88vhc6 and er.IS_DUL = 1 
					where r.Id = efd.ObjId
					for xml path('citizen'),
					type
			)
			,(select
					ehrr.CodeEHR		as 'benefit/benefit_code',							--2.4.1
					--??				as 'benefit/ext_benefit_code',						--2.4.2
					Substring(rpb.Mkb, 1,6)				as 'benefit/diagnosis',								--2.4.3
					convert(varchar(10), rpb.BeginDate, 20)	as 'benefit/receive_date',							--2.4.4
					convert(varchar(10), rpb.EndDate, 20)as 'benefit/cancel_date'							--2.4.5
				  from RDS.T_RR_PATIENT_BENEFIT rpb
				  join RDs.T_BENEFITS b on b.BenefitsID = rpb.Benefits
				  join EHR.T_EHR_RUS_SDO8U3N5RO ehrr on ehrr.EhrRusID = b.EhrRusSdo8u3n5ro
				  join RDS.T_EXPORT_FRLLO_DATA efdB on efdB.ObjId = rpb.RrPatientBenefitID and efdb.ExportFrllo = efd.ExportFrllo and ex.FileNumber = efdB.FileNumber and (@WithoutError = 0 or efdB.FrlloError is null)
				  where rpb.RrPatient = efd.ObjId
				  for xml path(''),
				  type
				) as 'benefits'
			--select * 
			from RDS.T_EXPORT_FRLLO_DATA efd
			join RDS.T_EXPORT_FRLLO ef on ef.ExportFrlloID = efd.ExportFrllo
			where efd.Type = 0 and ef.ExportFrlloID = @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_DATA ex
	where ex.Type = 0 and ex.ExportFrllo = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber
	end
	else if(@Type = 'BSK_REGISTER')
	begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'
			,(
				select 
				efd.ExportFrlloDataID	as 'document_id',
				ef.ActualDate			as 'doc_date_time'
			,(
				select
					--'??'				as 'register_id',							--2.3.1
					r.BskRegisterID		as 'ext_citizen_id',						--2.3.2
					r.FirstName			as 'name',									--2.3.3
					r.LastName			as 'surname',								--2.3.4
					r.FatherName		as 'patronymic',								--2.3.5
					--r.BirthSurname		as 'firstsurname',							--2.3.6
					convert(varchar(10), r.Birthday, 20) as 'birthdate',								--2.3.7
					eSex.Code			as 'sex',									--2.3.8
					643					as 'citizenship',							--2.3.9
					--''				as 'citizenship_confirm/doc_type',			--2.3.10.1
					--''				as 'citizenship_confirm/doc_type_name',		--2.3.10.2
					--''				as 'citizenship_confirm/serial',			--2.3.10.3
					--''				as 'citizenship_confirm/num',				--2.3.10.4
					--''				as 'citizenship_confirm/date_issue',		--2.3.10.5
					--''				as 'citizenship_confirm/date_expiry',		--2.3.10.6
					--''				as 'citizenship_confirm/authority',			--2.3.10.7
					Replace(Replace(r.SNILS, '-',''), ' ', '')				as 'snils',									--2.3.11
					r.Enp				as 'policy_sn',								--2.3.12
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then er.CodeEHR else null end)			as 'identify_docs/doc/doc_type',						--2.3.13.1
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocSeries else null end)			as 'identify_docs/doc/serial',					--2.3.13.2
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocNumber else null end)			as 'identify_docs/doc/num',						--2.3.13.3
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then convert(varchar(10), r.DocIssueDate, 20) else null end) as 'identify_docs/doc/date_issue',				--2.3.13.4
					--'??'				as 'identify_docs/doc/date_expiry',				--2.3.13.5
					(case when er.CodeEHR is not null then r.DocIssueOrg else null end)		as 'identify_docs/doc/authority',				--2.3.13.6
					@Region				as 'region'									--2.3.14
					--r.				as 'addresses/address/address_type_code',	--2.3.15.1
					--r.				as 'addresses/address/fias_address',		--2.3.15.2
					--r.				as 'addresses/address/fias_home',			--2.3.15.3
					--r.				as 'addresses/address/region',				--2.3.15.4
					--r.				as 'addresses/address/area',				--2.3.15.5
					--r.				as 'addresses/address/prefix_area',			--2.3.15.6
					--r.				as 'addresses/address/street',				--2.3.15.7
					--r.				as 'addresses/address/prefix_street',		--2.3.15.8
					--r.				as 'addresses/address/house_num',			--2.3.15.9
					--r.				as 'addresses/address/build_num',			--2.3.15.10
					--r.				as 'addresses/address/struc_num',			--2.3.15.11
					--r.				as 'addresses/address/room_num',			--2.3.15.12
					--r.				as 'addresses/address/live_start_date',		--2.3.15.13
					--r.				as 'addresses/address/live_end_date',		--2.3.15.14
				from RDS.T_BSK_REGISTER r
					join VCLib.T_ENUMS eSex on eSex.EnumID = r.Sex
					left join EHR.T_EHR_RUS_5O5E88VHC6 er on er.EhrRusID = r.EhrRus5o5e88vhc6 and er.IS_DUL = 1 
					where r.BskRegisterID = efd.ObjId
					for xml path('citizen'),
					type
			)
			,(select
					ehrr.CodeEHR		as 'benefit/benefit_code',							--2.4.1
					--??				as 'benefit/ext_benefit_code',						--2.4.2
					Substring(brd.Mkb, 1,6)				as 'benefit/diagnosis',								--2.4.3
					convert(varchar(10), brd.BeginDate, 20)	as 'benefit/receive_date',							--2.4.4
					convert(varchar(10), brd.EndDate, 20)as 'benefit/cancel_date'							--2.4.5
				  from RDS.T_BSK_REGISTER_DATA brd
				  join RDs.T_BENEFITS b on b.BenefitsID = brd.Benefits
				  join EHR.T_EHR_RUS_SDO8U3N5RO ehrr on ehrr.EhrRusID = b.EhrRusSdo8u3n5ro
				  join RDS.T_EXPORT_FRLLO_DATA efdB on efdB.ObjId = brd.BskRegisterDataID and efdb.ExportFrllo = efd.ExportFrllo and ex.FileNumber = efdB.FileNumber and (@WithoutError = 0 or efdB.FrlloError is null)
				  where brd.BskRegister = efd.ObjId
				  for xml path(''),
				  type
				) as 'benefits'
			--select * 
			from RDS.T_EXPORT_FRLLO_DATA efd
			join RDS.T_EXPORT_FRLLO ef on ef.ExportFrlloID = efd.ExportFrllo
			where efd.Type = 0 and ef.ExportFrlloID = @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_DATA ex
	where ex.Type = 0 and ex.ExportFrllo = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber
	end
	else if(@Type = 'PALLIATIVE_REGISTER')
	begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'
			,(
				select 
				efd.ExportFrlloDataID	as 'document_id',
				ef.ActualDate			as 'doc_date_time'
			,(
				select
					--'??'				as 'register_id',							--2.3.1
					r.PalliativeRegisterID	as 'ext_citizen_id',						--2.3.2
					r.FirstName			as 'name',									--2.3.3
					r.LastName			as 'surname',								--2.3.4
					r.FatherName		as 'patronymic',								--2.3.5
					--r.BirthSurname		as 'firstsurname',							--2.3.6
					convert(varchar(10), r.Birthday, 20) as 'birthdate',								--2.3.7
					eSex.Code			as 'sex',									--2.3.8
					643					as 'citizenship',							--2.3.9
					--''				as 'citizenship_confirm/doc_type',			--2.3.10.1
					--''				as 'citizenship_confirm/doc_type_name',		--2.3.10.2
					--''				as 'citizenship_confirm/serial',			--2.3.10.3
					--''				as 'citizenship_confirm/num',				--2.3.10.4
					--''				as 'citizenship_confirm/date_issue',		--2.3.10.5
					--''				as 'citizenship_confirm/date_expiry',		--2.3.10.6
					--''				as 'citizenship_confirm/authority',			--2.3.10.7
					Replace(Replace(r.SNILS, '-',''), ' ', '')				as 'snils',									--2.3.11
					r.Enp				as 'policy_sn',								--2.3.12
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then er.CodeEHR else null end)			as 'identify_docs/doc/doc_type',						--2.3.13.1
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocSeries else null end)			as 'identify_docs/doc/serial',					--2.3.13.2
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocNumber else null end)			as 'identify_docs/doc/num',						--2.3.13.3
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then convert(varchar(10), r.DocIssueDate, 20) else null end) as 'identify_docs/doc/date_issue',				--2.3.13.4
					--'??'				as 'identify_docs/doc/date_expiry',				--2.3.13.5
					(case when er.CodeEHR is not null then r.DocIssueOrg else null end)		as 'identify_docs/doc/authority',				--2.3.13.6
					@Region				as 'region'									--2.3.14
					--r.				as 'addresses/address/address_type_code',	--2.3.15.1
					--r.				as 'addresses/address/fias_address',		--2.3.15.2
					--r.				as 'addresses/address/fias_home',			--2.3.15.3
					--r.				as 'addresses/address/region',				--2.3.15.4
					--r.				as 'addresses/address/area',				--2.3.15.5
					--r.				as 'addresses/address/prefix_area',			--2.3.15.6
					--r.				as 'addresses/address/street',				--2.3.15.7
					--r.				as 'addresses/address/prefix_street',		--2.3.15.8
					--r.				as 'addresses/address/house_num',			--2.3.15.9
					--r.				as 'addresses/address/build_num',			--2.3.15.10
					--r.				as 'addresses/address/struc_num',			--2.3.15.11
					--r.				as 'addresses/address/room_num',			--2.3.15.12
					--r.				as 'addresses/address/live_start_date',		--2.3.15.13
					--r.				as 'addresses/address/live_end_date',		--2.3.15.14
				from RDS.T_PALLIATIVE_REGISTER r
					join VCLib.T_ENUMS eSex on eSex.EnumID = r.Sex
					left join EHR.T_EHR_RUS_5O5E88VHC6 er on er.EhrRusID = r.EhrRus5o5e88vhc6 and er.IS_DUL = 1 
					where r.PalliativeRegisterID =efd.ObjId
					for xml path('citizen'),
					type
			)
			,(select
					ehrr.CodeEHR		as 'benefit/benefit_code',							--2.4.1
					--??				as 'benefit/ext_benefit_code',						--2.4.2
					Substring(rpb.Mkb, 1,6)				as 'benefit/diagnosis',								--2.4.3
					convert(varchar(10), rpb.BeginDate, 20)	as 'benefit/receive_date',							--2.4.4
					convert(varchar(10), rpb.EndDate, 20)as 'benefit/cancel_date'							--2.4.5
				  from RDS.T_PALLIATIVE_REGISTER_DATA rpb
				  join RDs.T_BENEFITS b on b.BenefitsID = rpb.Benefits
				  join EHR.T_EHR_RUS_SDO8U3N5RO ehrr on ehrr.EhrRusID = b.EhrRusSdo8u3n5ro
				  join RDS.T_EXPORT_FRLLO_DATA efdB on efdB.ObjId = rpb.PalliativeRegisterDataID and efdb.ExportFrllo = efd.ExportFrllo and ex.FileNumber = efdB.FileNumber and (@WithoutError = 0 or efdB.FrlloError is null)
				  where rpb.PalliativeRegister = efd.ObjId
				  for xml path(''),
				  type
				) as 'benefits'
			--select * 
			from RDS.T_EXPORT_FRLLO_DATA efd
			join RDS.T_EXPORT_FRLLO ef on ef.ExportFrlloID = efd.ExportFrllo
			where efd.Type = 0 and ef.ExportFrlloID = @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_DATA ex
	where ex.Type = 0 and ex.ExportFrllo = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber
	end
	else if(@Type = 'SPECIAL_BENEFITS_REGISTR')
	begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'
			,(
				select 
				efd.ExportFrlloDataID	as 'document_id',
				ef.ActualDate			as 'doc_date_time'
			,(
				select
					--'??'				as 'register_id',							--2.3.1
					r.SpecialBenefitsRegistrID	as 'ext_citizen_id',				--2.3.2
					r.FirstName			as 'name',									--2.3.3
					r.LastName			as 'surname',								--2.3.4
					r.FatherName		as 'patronymic',							--2.3.5
					r.BirthdayLastName	as 'firstsurname',							--2.3.6
					convert(varchar(10), r.Birthday, 20) as 'birthdate',								--2.3.7
					eSex.Code			as 'sex',									--2.3.8
					643					as 'citizenship',							--2.3.9
					--''				as 'citizenship_confirm/doc_type',			--2.3.10.1
					--''				as 'citizenship_confirm/doc_type_name',		--2.3.10.2
					--''				as 'citizenship_confirm/serial',			--2.3.10.3
					--''				as 'citizenship_confirm/num',				--2.3.10.4
					--''				as 'citizenship_confirm/date_issue',		--2.3.10.5
					--''				as 'citizenship_confirm/date_expiry',		--2.3.10.6
					--''				as 'citizenship_confirm/authority',			--2.3.10.7
					Replace(Replace(r.SNILS, '-',''), ' ', '')				as 'snils',									--2.3.11
					r.Enp				as 'policy_sn',								--2.3.12
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then er.CodeEHR else null end)			as 'identify_docs/doc/doc_type',						--2.3.13.1
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocSeries else null end)			as 'identify_docs/doc/serial',					--2.3.13.2
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then r.DocNumber else null end)			as 'identify_docs/doc/num',						--2.3.13.3
					(case when er.CodeEHR is not null and r.DocNumber is not null and r.DocIssueDate is not null then convert(varchar(10), r.DocIssueDate, 20) else null end) as 'identify_docs/doc/date_issue',				--2.3.13.4
					--'??'				as 'identify_docs/doc/date_expiry',				--2.3.13.5
					(case when er.CodeEHR is not null then r.DocIssueOrg else null end)		as 'identify_docs/doc/authority',				--2.3.13.6
					@Region				as 'region'									--2.3.14
					--r.				as 'addresses/address/address_type_code',	--2.3.15.1
					--r.				as 'addresses/address/fias_address',		--2.3.15.2
					--r.				as 'addresses/address/fias_home',			--2.3.15.3
					--r.				as 'addresses/address/region',				--2.3.15.4
					--r.				as 'addresses/address/area',				--2.3.15.5
					--r.				as 'addresses/address/prefix_area',			--2.3.15.6
					--r.				as 'addresses/address/street',				--2.3.15.7
					--r.				as 'addresses/address/prefix_street',		--2.3.15.8
					--r.				as 'addresses/address/house_num',			--2.3.15.9
					--r.				as 'addresses/address/build_num',			--2.3.15.10
					--r.				as 'addresses/address/struc_num',			--2.3.15.11
					--r.				as 'addresses/address/room_num',			--2.3.15.12
					--r.				as 'addresses/address/live_start_date',		--2.3.15.13
					--r.				as 'addresses/address/live_end_date',		--2.3.15.14
				from RDS.T_SPECIAL_BENEFITS_REGISTR r
					join VCLib.T_ENUMS eSex on eSex.EnumID = r.Sex
					left join EHR.T_EHR_RUS_5O5E88VHC6 er on er.EhrRusID = r.EhrRus5o5e88vhc6 and er.IS_DUL = 1 
					where r.SpecialBenefitsRegistrID = efd.ObjId
					for xml path('citizen'),
					type
			)
			,(select
					ehrr.CodeEHR		as 'benefit/benefit_code',							--2.4.1
					--??				as 'benefit/ext_benefit_code',						--2.4.2
					Substring(r.Mkb, 1,6)				as 'benefit/diagnosis',								--2.4.3
					convert(varchar(10), r.DateOfInclusion, 20)	as 'benefit/receive_date',							--2.4.4
					convert(varchar(10), r.DateOfDeclusion, 20)as 'benefit/cancel_date'							--2.4.5
				  from RDS.T_SPECIAL_BENEFITS_REGISTR r
				  join RDs.T_BENEFITS b on b.BenefitsID = r.Benefits
				  join EHR.T_EHR_RUS_SDO8U3N5RO ehrr on ehrr.EhrRusID = b.EhrRusSdo8u3n5ro
				  join RDS.T_EXPORT_FRLLO_DATA efdB on efdB.ObjId = r.SpecialBenefitsRegistrID and efdb.ExportFrllo = efd.ExportFrllo and ex.FileNumber = efdB.FileNumber and (@WithoutError = 0 or efdB.FrlloError is null)
				  where r.SpecialBenefitsRegistrID = efd.ObjId
				  for xml path(''),
				  type
				) as 'benefits'
			--select * 
			from RDS.T_EXPORT_FRLLO_DATA efd
			join RDS.T_EXPORT_FRLLO ef on ef.ExportFrlloID = efd.ExportFrllo
			where efd.Type = 0 and ef.ExportFrlloID = @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_DATA ex
	where ex.Type = 0 and ex.ExportFrllo = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber
	end

	select val from @OutputTable
END				
				