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

	declare @OutputTable table(Val xml)

	if(@Type = 0)
		begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'
			,(
				select 
				efd.ExportFrlloRecipeDataID	as 'document_id',
				ef.ActualDate				as 'doc_date_time'
			,(
				select
					--'??'				as 'citizen/register_id',							--2.3.1
					--r.RrPatientID		as 'citizen/ext_citizen_id',						--2.3.2
					r.FirstName			as 'citizen/name',									--2.3.3
					r.LastName			as 'citizen/surname',								--2.3.4
					r.FatherName		as 'citizen/patronymic',							--2.3.5
					r.BirthSurname		as 'citizen/firstsurname',							--2.3.6
					convert(varchar(10), r.Birthday, 20) as 'citizen/birthdate',			--2.3.7
					eSex.Code			as 'citizen/sex',									--2.3.8
					@Region				as 'citizen/region',								--2.3.9
					Replace(Replace(r.SNILS, '-',''), ' ', '')				as 'citizen/snils',	--2.3.10
					r.Enp				as 'citizen/policy_sn',								--2.3.11
					(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 'citizen/identify_docs/doc/doc_type',						--2.3.12.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 'citizen/identify_docs/doc/serial',							--2.3.12.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 'citizen/identify_docs/doc/num',								--2.3.12.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 'citizen/identify_docs/doc/date_issue',						--2.3.12.4
					--''				as 'identify_docs/doc/date_expiry',					--2.3.12.5
					(case when er.CodeEHR is not null then r.DocIssueOrg else null end)		as 'citizen/identify_docs/doc/authority',						--2.3.12.6
					case when r.Status = 1 then 'true' else 'false' end							as 'recipe/is_delete',
					r.Serial			as 'recipe/recipe_serial',							--2.4.1
					r.Number			as 'recipe/recipe_num',								--2.4.2
					(r.Serial + Cast(r.Number as varchar(max)) + Cast(DATEPART(YEAR, r.RecipeDate) as varchar(4)) + isnull(mo1.MCOD, mo2.MCOD))				as 'recipe/ext_recipe_id',							--2.4.3
					--''				as 'recipe/recipe_id',								--2.4.4
					isnull(mo1.oid, mo2.oid)												as 'recipe/med_org_oid',							--2.4.5
					--''				as 'recipe/se_num',									--2.4.6
					--''				as 'recipe/se_name',								--2.4.7
					RTRIM(r.DocLastName + ' ' + r.DocFirstName + ' ' + IsNull(r.DocFatherName, ''))			as 'recipe/doctor_name',						--2.4.8
					postEhr.IdEHR		as 'recipe/staff_position_code',					--2.4.9
					Replace(Replace(r.DoctorSnils, '-',''), ' ', '')			as 'recipe/doctor_snils',													--2.4.10
					r.AcNumber			as 'recipe/medical_card',							--2.4.11
					benEhr.CodeEHR		as 'recipe/benefit_code',							--2.4.12
					Substring(r.MKBCode, 1,6)	as 'recipe/mkb10_code',								--2.4.13
					cmnn.CodeEHR		as 'recipe/drug_smnn_code',							--2.4.14
					case when trn.EhrRusP08TSH954N is null and trn.EhrRus575VSF88OG is null then trn.NameRus	else null end				as 'recipe/drug_name',								--2.4.15
					medProd.CodeEHR		as 'recipe/med_equip_code',							--2.4.16
					medNutr.CodeEHR		as 'recipe/nutrition_code',							--2.4.17
					convert(varchar(10), r.VKDate, 20)			as 'recipe/commission_date',																--2.4.18
					r.VKNumber			as 'recipe/commission_num',							--2.4.19
					--''				as 'recipe/pay_percent',							--2.4.20
					case when trn.EhrRusP08TSH954N is not null or trn.EhrRus575VSF88OG is not null then (case when Cast(right(cast(r.UnitsCount as decimal(9,1)), 1) as int) <> 0 then Cast(cast(r.UnitsCount as decimal(9,1)) as varchar(10))  else Cast(cast(r.UnitsCount as int)as varchar(10)) end)
					else (case when Cast(right(cast((r.UnitsCount * r.ConsumerUnits) as decimal(9,1)), 1) as int) <> 0 then Cast(cast((r.UnitsCount * r.ConsumerUnits) as decimal(9,1)) as varchar(10))  else Cast(cast((r.UnitsCount * r.ConsumerUnits) as int) as varchar(10)) end) end	as 'recipe/qty',									--2.4.21
					convert(varchar(10), r.RecipeDate, 20)			as 'recipe/recipe_date',																--2.4.22
					(case when r.Cito = 1 then 1
						  when r.Statim = 1 then 2
						  else null end) as 'recipe/recipe_priority_code',						--2.4.23
					(case when r.Period = 0 then (case when medProd.CodeEHR is not null then 6 else 2 end)
						  when r.Period = 2 then (case when medProd.CodeEHR is not null then 7 else 4 end)
						  when r.Period = 5 then 1
						  when r.Period in (1, 3, 4) then null
						  end )		    as 'recipe/recipe_expiry_code',																							--2.4.24
					case when r.Period = 0 then (case when medProd.CodeEHR is not null then convert(varchar(10), DATEADD(MONTH, 1, r.RecipeDate), 20) else convert(varchar(10), DATEADD(DAY, 30, r.RecipeDate), 20) end) 
						 when r.Period = 1 then convert(varchar(10), DATEADD(DAY, 14, r.RecipeDate), 20)
						 when r.Period = 2 then (case when medProd.CodeEHR is not null then convert(varchar(10), DATEADD(MONTH, 3, r.RecipeDate), 20) else convert(varchar(10), DATEADD(DAY, 90, r.RecipeDate), 20) end)
						 when r.Period = 3 then convert(varchar(10), DATEADD(DAY, 5, r.RecipeDate), 20)
						 when r.Period = 4 then convert(varchar(10), DATEADD(DAY, 10, r.RecipeDate), 20)
						 when r.Period = 5 then convert(varchar(10), DATEADD(DAY, 15, r.RecipeDate), 20)
						 end			as 'recipe/date_expiry'																--2.4.25
				from RDS.T_RECIPE 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 
					outer apply 
					(
						select top (1) mdr308.oid, lp.MCOD
						from RDS.T_LPU_MAIN lp 
						left join EHR.T_EHR_RUS_MDR308 mdr308 on mdr308.EhrRusID = lp.EhrRusMdr308Dop
						where lp.MCOD = r.LpuCode
					) mo1
					outer apply 
					(
						select top (1) mdr308.oid, lp.MCOD
						from RDS.T_LPU_MAIN lp 
						left join EHR.T_EHR_RUS_MDR308 mdr308 on mdr308.EhrRusID = lp.EhrRusMdr308Dop
						where mdr308.oid = r.LpuCode
					) mo2
					left join EHR.T_EHR_RUS_7PJSAM6GJM postEhr on postEhr.EhrRusID = r.EhrRus7pjsam6gjm
					left join RDS.T_BENEFITS b on b.BenefitsID  = r.Benefit
				    left join EHR.T_EHR_RUS_SDO8U3N5RO benEhr on benEhr.EhrRusID = b.EhrRusSdo8u3n5ro
					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 r.RecipeID = efd.ObjId
					for xml path(''),
					type
			)
			--select * 
			from RDS.T_EXPORT_FRLLO_RECIPE_DATA efd
			join RDS.T_EXPORT_FRLLO_RECIPE ef on ef.ExportFrlloRecipeID = efd.ExportFrlloRecipe
			where ef.ExportFrlloRecipeID = @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_RECIPE_DATA ex
	where ex.ExportFrlloRecipe = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber
	end
	else if(@Type = 1)
		begin
		Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'
			,(
				select 
				efd.ExportFrlloRecipeDataID	as 'document_id',
				ef.ActualDate			as 'doc_date_time'
			,(
				select
					case when re.Deleted = 1 then 'true' else 'false' end																					as 'is_delete',
					--'??'				as 'relise_id',										--2.3.1
					re.RecipeElementID	as 'ext_relise_id',									--2.3.2
					--re.				as 'recipe_id',										--2.3.3
					(r.Serial + Cast(r.Number as varchar(max)) + Cast(DATEPART(YEAR, r.RecipeDate) as varchar(4)) + isnull(mo1.MCOD, mo2.MCOD))				as 'ext_recipe_id',		--2.3.4
					pharmEhr.oid		as 'med_org_oid',									--2.3.5
					--''				as 'se_num',										--2.3.6
					--''				as 'se_name',										--2.3.7
					r.PharmacistFio		as 'staff_name',									--2.3.8
					postEhr.IdEHR		as 'staff_position_code',							--2.3.9
					Replace(Replace(r.PharmacistSnils, '-',''), ' ', '')																					as 'staff_snils',		--2.3.10
					(case when trn.EhrRusP08TSH954N is null and trn.EhrRus575VSF88OG is null then esklp.KLP_CODE end)										as 'drug_klp_code',		--2.3.11
					medProd.CodeEHR		as 'med_equip_code',								--2.3.12
					medNutr.CodeEHR		as 'nutrition_code',								--2.3.13
					--''				as 'service_types_code',							--2.3.14
					--''				as 'refuse_reason_code',							--2.3.15
					(case when Cast(right(cast(re.Amount as decimal(9,1)), 1) as int) <> 0 then Cast(cast(re.Amount as decimal(9,1)) as varchar(10))  else Cast(cast(re.Amount as int)as varchar(10)) end)				as 'pack_qty',											--2.3.16
					(case when trn.EhrRusP08TSH954N is null and trn.EhrRus575VSF88OG is null then (case when Cast(right(cast((CAST(REPLACE(NUMBER_UNITS_2, ',','.') as decimal(15,3)) * re.Amount) as decimal(15,3)), 3) as int) <> 0 then Cast(cast((CAST(REPLACE(NUMBER_UNITS_2, ',','.') as decimal(15,3)) * re.Amount) as decimal(15,1)) as varchar(15))  else Cast(cast((CAST(REPLACE(NUMBER_UNITS_2, ',','.') as decimal(15,3)) * re.Amount) as int)as varchar(15)) end)
					else (case when Cast(right(cast(re.Amount as decimal(9,1)), 1) as int) <> 0 then Cast(cast(re.Amount as decimal(9,1)) as varchar(10))  else Cast(cast(re.Amount as int)as varchar(10)) end) end)		as 'item_qty',											--2.3.17
					cast(re.Price as decimal(16,2))																											as 'price',				--2.3.18
					convert(varchar(10), r.IssueDate, 20)																									as 'release_date'		--2.3.19
				from RDS.T_RECIPE_ELEMENT re
					left join RDS.T_RECIPE r on r.RecipeID = re.Recipe
					outer apply 
					(
						select top (1) mdr308.oid, lp.MCOD
						from RDS.T_LPU_MAIN lp 
						left join EHR.T_EHR_RUS_MDR308 mdr308 on mdr308.EhrRusID = lp.EhrRusMdr308Dop
						where lp.MCOD = r.LpuCode
					) mo1
					outer apply 
					(
						select top (1) mdr308.oid, lp.MCOD
						from RDS.T_LPU_MAIN lp 
						left join EHR.T_EHR_RUS_MDR308 mdr308 on mdr308.EhrRusID = lp.EhrRusMdr308Dop
						where mdr308.oid = r.LpuCode
					) mo2
					left join RDS.T_PHARMACY p on p.Code = re.Pharmacy
					left join EHR.T_EHR_RUS_MDR308 pharmEhr on pharmEhr.EhrRusID = p.EhrRusMdr308
					left join EHR.T_EHR_RUS_7PJSAM6GJM postEhr on postEhr.EhrRusID = r.EhrRus7pjsam6gjmPharm
					left join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = re.BillOfGoods
					left join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
					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
					left join EHR.T_EHR_RUS_2M69I03GTD esklp on esklp.EhrRusID = bg.EhrRus2m69i03gtd
					where reMain.RecipeElementID = re.RecipeElementID
					for xml path('relise'),
					type
			)
			from RDS.T_RECIPE_ELEMENT reMain
				left join RDS.T_RECIPE rMain on rMain.RecipeID = reMain.Recipe
				join RDS.T_EXPORT_FRLLO_RECIPE_DATA efd on efd.ObjId = reMain.RecipeElementID
				join RDS.T_EXPORT_FRLLO_RECIPE ef on ef.ExportFrlloRecipeID = efd.ExportFrlloRecipe
			where ef.ExportFrlloRecipeID = @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_RECIPE_DATA ex
	where ex.ExportFrlloRecipe = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber
	end


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