/****** Object:  StoredProcedure [dbo].[sp_Export7NosologyXml]    Script Date: 16.05.2017 12:42:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Export7NosologyXml]') and xtype = N'P')
drop procedure [dbo].[sp_Export7NosologyXml]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[APS].[sp_Export7NosologyXml]') and xtype = N'P')
drop procedure [APS].[sp_Export7NosologyXml]
GO


CREATE PROCEDURE [APS].[sp_Export7NosologyXml]
	@DateBegin datetime
	,@DateEnd datetime
	,@Result varbinary(max) OUTPUT
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @xml varchar(max)

	declare @Errors table
	(
		[ID] uniqueidentifier,
		[errorText] varchar(256),
		[name] varchar(256)
	)

	SELECT
		 newid() as 'ID' 
		,nslg.[UniqueNumber] as 'u_numb_regist_record_04' 
		,nslg.[SNILS] as 'Snils'
		,null as 'u_numb_regist_record'
		,lm.[OGRN] as 'gos_numb_okop_uz'
		,rec.[PostCode] as 'pres_doctor' 
		,rec.[Serial] + ' ' + convert(varchar(100), rec.[Number]) as 'pres_ser_num'
		,convert(varchar(30), isnull(rec.[RecipeDate], ''), 104) as 'pres_date'
		,'734010000' as 'okato'
		,mnn.[ExportCode] as 'prepar_name' 
		,mnn.[NameRus] as 'MnnNameRus'
		,convert(int, rec.[UnitsCount]) as 'doza_count'
		,left(lm.TF_OKATO, 2) as 'terr_code'
		,ph.[Code] as 'pharmacy_reg_no'
		,convert(varchar(30), rec.[IssueDate], 104) as 'pharmacy_date_out1'
		,bg.[ExportCode] as 'lekarstv_name'
		,trn.[NameRus] as 'TrnNameRus'
		,rec.[Mkb] as 'Mkb'
		,me.[ExportCode] as 'mkb_code'
		,mf.RegCode as 'lekarstv_forma'
		,bg.[Active] as 'lekarstv_doza_pres'
		,rec.[Dose] as 'lekarstv_doza_pack'
		,convert(int, rec.[UnitsCount]) as 'lekarstv_packs_out'
		,'2' as 'operation_code' 
		,nslg.[Head] as 'head_person'
		,nslg.[ExecutiveAuthority] as 'organ_ispoln_vlast_sf' 
		,'1' as 'id_istochnik_finans'
		,nslg.[Comment] as 'fcomment'
	INTO #tempdb
	FROM [APS].[T_RECIPE] rec 
		JOIN [APS].[T_NOSOLOGY] nslg on nslg.[SNILS] = rec.[Snils]
		JOIN [APS].[T_BENEFITS] b on b.BenefitsID = rec.[Benefits] and b.BenefitsID = nslg.[Benefits]
		LEFT JOIN [APS].[T_LPU_MAIN] lm on lm.[MCOD] = rec.[LpuMain]
		JOIN [PHARM].[T_C_MNN] mnn on mnn.[CMnnID] = rec.[CMnn]
		JOIN [APS].[T_RECIPE_INVOICE] ri on ri.[Recipe] = rec.[RecipeID]
		JOIN [STOR].[T_STORAGE_INVOICE] si on si.[InvoiceID] = ri.[InvoiceID]
		JOIN [STOR].[T_INVOICE_ITEM] ii on ii.[StorageInvoice] = si.[InvoiceID]
		JOIN [PHARM].[T_BILL_OF_GOODS] bg on bg.[BillOfGoodsID] = ii.[BillOfGoods]
		JOIN [PHARM].[T_MEDICATION_FORMS] mf on mf.MedicationFormsID = bg.MedicationForms
		JOIN [PHARM].[T_C_TRN] trn on trn.[CTrnID] = bg.[CTrn]
		JOIN [APS].[T_MKB_EXPORT] me on me.[MkbCode] = rec.[Mkb]
		LEFT JOIN [APS].[T_PHARMACY] ph on ph.[Code] = rec.[Pharmacy]
	WHERE rec.[Status] = 1 and b.BenefitViews = 19006 
		and rec.[IssueDate] >= @DateBegin and rec.[IssueDate] <= @DateEnd 

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (u_numb_regist_record_04) - уникальный номер в записях регистра по 12 нозологиям. СНИЛС пациента в регистре - ' + t.[Snils]
	from #tempdb t
	where t.[u_numb_regist_record_04] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (gos_numb_okop_uz) - основной государственный регистрационный номер (по ОКПО) учреждения здравоохранения, выдавшего рецепт на лекарственное средство'
	from #tempdb t
	where t.[gos_numb_okop_uz] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (pres_doctor) - серия и номер рецепта'
	from #tempdb t
	where t.[pres_doctor] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (pres_ser_num) - идентификационный номер врача, выписавшего рецепт'
	from #tempdb t
	where t.[pres_ser_num] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (pres_date) - дата выписки рецепта'
	from #tempdb t
	where t.[pres_date] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (doza_count) - выписананое количество доз лекарственного средства'
	from #tempdb t
	where t.[doza_count] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (terr_code) - код территории отпуска лекарственного средства по Общероссийскому классификатору административно-территориальных образований'
	from #tempdb t
	where t.[terr_code] is null

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (organ_ispoln_vlast_sf) - орган исполнительной власти субъекта Российской Федерации (ФМБА России)'
	from #tempdb t
	where t.[organ_ispoln_vlast_sf] is null

	insert into @Errors (ID, errorText)
	select 	t.[ID], 'Не указано обязательное поле (MnnNameRus) - код мнн выписанного лс'
	from #tempdb t
	where t.[MnnNameRus] is null

	insert into @Errors (errorText, name)
	select 	'Не найдено соответсвие (prepar_name) - код мнн выписанного лс и ExportID: ' + t.[MnnNameRus], t.[MnnNameRus]
	from #tempdb t
	where t.[prepar_name] is null
	group by t.[MnnNameRus]

	update e
	set e.[ID] = tt.[ID]
	from @Errors e
	cross apply (select top 1 * from #tempdb t where t.[MnnNameRus] = e.[name]) tt

	insert into @Errors (errorText, name)
	select 	'Не найдено соответсвие (lekarstv_name) - трн отпущенного лс и ExportID : ' + t.[TrnNameRus], t.[TrnNameRus]
	from #tempdb t
	where t.[lekarstv_name] is null
	group by t.[TrnNameRus]

	update e
	set e.[ID] = tt.[ID]
	from @Errors e
	cross apply (select top 1 * from #tempdb t where t.[TrnNameRus] = e.[name]) tt

	insert into @Errors (ID, errorText)
	select t.[ID], 'Не указано обязательное поле (mkb_code) - код заболевания по MKB-10: ' + t.[Mkb]
	from #tempdb t
	where t.[Mkb] is null

	insert into @Errors (errorText, name)
	select 	'Не найдено соответсвие в таблице MKB_EXPORT кода МКБ и ExportID: ' + t.[Mkb], t.[Mkb]
	from #tempdb t
	where t.[mkb_code] is null
	group by t.[Mkb]

	update e
	set e.[ID] = tt.[ID]
	from @Errors e
	cross apply (select top 1 * from #tempdb t where t.[Mkb] = e.[name]) tt

	-- -- -- -- -- -- -- -- -- 
	
	insert into @Errors (errorText, name)
	select 	'Не найдено соответсвие (lekarstv_forma) - форма выпуска лс и FormExportCode : '  + t.[TrnNameRus], t.[TrnNameRus]
	from #tempdb t
	where t.[lekarstv_forma] is null
	group by t.[TrnNameRus]

	update e
	set e.[ID] = tt.[ID]
	from @Errors e
	cross apply (select top 1 * from #tempdb t where t.[TrnNameRus] = e.[name]) tt

	-- -- -- -- -- -- -- -- -- 

	SET @xml = '<?xml version="1.0"?>'

	if (select count(*) from @Errors) > 0
	begin 
		SET @xml = 
		(
			select 
				(
					select e.[errorText] + '. Серия и номер рецепта: ' + t.[pres_ser_num] as 'error'
					from @Errors e 
					where e.[ID] = t.[ID]
					FOR XML PATH(''), root('Errors'), type
				)
				,t.[u_numb_regist_record_04]
				,'<u_numb_regist_record></u_numb_regist_record>'
				,t.[gos_numb_okop_uz]
				,t.[pres_doctor]
				,t.[pres_ser_num]
			    ,case when t.[pres_date] is null then '<press_date></press_date>' else t.[pres_date] end
				,t.[okato]
				,t.[prepar_name]
				,t.[MnnNameRus]
				,t.[doza_count]
				,t.[terr_code]
				,case when t.[pharmacy_reg_no] is null then '<pharmacy_reg_no></pharmacy_reg_no>' else t.[pharmacy_reg_no] end
				,t.[pharmacy_date_out1]
				,t.[lekarstv_name]
				,t.[TrnNameRus]
				,t.[mkb_code]
				,t.[Mkb]
				,t.[lekarstv_forma] --'<lekarstv_forma></lekarstv_forma>'
			    ,t.[lekarstv_doza_pres] --'<lekarstv_doza_pres></lekarstv_doza_pres>'
			    ,case when t.[lekarstv_doza_pack] is null then '<lekarstv_doza_pack></lekarstv_doza_pack>' else '<lekarstv_doza_pack>' + t.[lekarstv_doza_pack] + '</lekarstv_doza_pack>' end
				,t.[lekarstv_packs_out]
				,t.[operation_code]
			    ,case when t.[head_person] is null then  '<head_person></head_person>' else '<head_person>' + t.[head_person] + '</head_person>' end
				,t.[organ_ispoln_vlast_sf]
				,t.[id_istochnik_finans]
			    ,case when t.[fcomment] is null then '<fcomment></fcomment>' else '<fcomment>' + t.[fcomment] + '</fcomment>' end
			from #tempdb t
				join @Errors e on e.[ID] = t.[ID]
			FOR XML path('row'), root('errorlist'), ELEMENTS
		)
	end
	else
	begin
		SET @xml = 
		(	   
			SELECT
				t.[u_numb_regist_record_04]
			   ,'<u_numb_regist_record></u_numb_regist_record>'
			   ,t.[gos_numb_okop_uz]
			   ,t.[pres_doctor]
			   ,t.[pres_ser_num]
			   ,case when t.[pres_date] is null then '<pres_date></pres_date>' else '<pres_date>' + t.[pres_date] + '</pres_date>' end
			   ,t.[okato]
			   ,t.[prepar_name]
			   ,t.[doza_count]
			   ,t.[terr_code]
			   ,case when t.[pharmacy_reg_no] is null then '<pharmacy_reg_no></pharmacy_reg_no>' else '<pharmacy_reg_no>' + convert(varchar, t.[pharmacy_reg_no]) + '</pharmacy_reg_no>' end
			   ,t.[pharmacy_date_out1]
			   ,t.[lekarstv_name]
			   ,t.[mkb_code]
			   ,t.[lekarstv_forma] -- '<lekarstv_forma></lekarstv_forma>'
			   ,t.[lekarstv_doza_pres] --'<lekarstv_doza_pres></lekarstv_doza_pres>'
			   ,case when t.[lekarstv_doza_pack] is null then '<lekarstv_doza_pack></lekarstv_doza_pack>' else '<lekarstv_doza_pack>' + t.[lekarstv_doza_pack] + '</lekarstv_doza_pack>' end
			   ,t.[lekarstv_packs_out]
			   ,t.[operation_code]
			   ,case when t.[head_person] is null then  '<head_person></head_person>' else '<head_person>' + t.[head_person] + '</head_person>' end
			   ,t.[organ_ispoln_vlast_sf]
			   ,t.[id_istochnik_finans]
			   ,case when t.[fcomment] is null then '<fcomment></fcomment>' else '<fcomment>' + t.[fcomment] + '</fcomment>' end
			FROM #tempdb t
			FOR XML PATH ('row'), ROOT('root'), ELEMENTS
		)
	end

	SET @xml = REPLACE(REPLACE(REPLACE(@xml, '&lt;', '<'), '&lt;/','</'), '&gt;', '>')

	drop table #tempdb
	
	set @Result = convert(varbinary(max), @xml)

	select @Result 
END
