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
             select 
                 ex.ObjId, ex.RegNum
             INTO #REG_NUM
             from RDS.T_EXPORT_FRLLO_RECIPE_DATA ex
                 join RDS.T_EXPORT_FRLLO_RECIPE err on err.ExportFrlloRecipeID = ex.ExportFrlloRecipe
                 join RDS.T_EXPORT_FRLLO_RECIPE_DATA cur on cur.ObjId = ex.ObjId and cur.ExportFrlloRecipe = @ExportId and (@WithoutError = 0 or cur.FrlloError is null)
                 join RDS.T_EXPORT_FRLLO_RECIPE errCur on errCur.ExportFrlloRecipeID = cur.ExportFrlloRecipe
             where ex.ExportFrlloRecipe <> @ExportId and err.ActualDate < errCur.ActualDate and err.DataType = errCur.DataType and ex.RegNum is not null
             and not exists(select * 
                                from RDS.T_EXPORT_FRLLO_RECIPE_DATA exxxx
                                join RDS.T_EXPORT_FRLLO_RECIPE erOld on erOld.ExportFrlloRecipeID = exxxx.ExportFrlloRecipe
	                            where exxxx.ExportFrlloRecipe <> ex.ExportFrlloRecipe
                                    and exxxx.ObjId = ex.ObjId
                                    and erOld.ActualDate > err.ActualDate and err.DataType = erOld.DataType
                                    and exxxx.RegNum is not null
					                and exxxx.ExportFrlloRecipe <> @ExportId
                             )

	    	Insert into @OutputTable(Val)
	    	select  
	    	(


	    		select @InfoSysCode as 'info_sys_code'
	    		,(
	    			select 
	    			efd.ExportFrlloRecipeDataID	as 'document_id',
	    			ef.ActualDate				as 'doc_date_time'
	    		,(
	    			select
	    				rg.RegNum			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
	    				(case when r.FrlloId is not null then r.FrlloId else null end)				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
                        left join #REG_NUM rg on rg.ObjId = r.RecipeID
	    				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
					(case when re.FrlloId is not null then re.FrlloId else null end)			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				
