SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_TakeSendingDnInfo]') and xtype = N'P')
drop procedure [dbo].[sp_TakeSendingDnInfo]
GO
-- =============================================
-- Author:		ЗАО Витакор
-- Create date: 2022.08.19
-- Description:	Прием пакетов ДН с ошибками ФЛК и ТК из ТФОМС
-- =============================================
CREATE PROCEDURE [dbo].[sp_TakeSendingDnInfo] 
	@FileName varchar(255)
	, @Xml xml
As
Begin
	SET NOCOUNT ON;

select 
	f.c.value('(FILENAME/text())[1]', 'varchar(255)') as [FILENAME]
,	f.c.value('(MOCOD/text())[1]', 'varchar(10)') as MOCOD
,	f.c.value('(ERR_FILE/text())[1]', 'varchar(255)') as ERR_FILE
,	dn.c.value('(GUID/text())[1]', 'uniqueidentifier') as [GUID]
,	err.c.value('(ERR_TYPE/text())[1]', 'int') as ERR_TYPE
,	err.c.value('(ERR_CODE/text())[1]', 'varchar(20)') as ERR_CODE
,	err.c.value('(ERR_NAME/text())[1]', 'varchar(4000)') as ERR_NAME
,	err.c.value('(FIELD_NAME/text())[1]', 'varchar(255)') as FIELD_NAME
,	err.c.value('(NOTE/text())[1]', 'varchar(4000)') as NOTE
INTO #sdn
from @Xml.nodes('DNLIST') f(c)
outer apply f.c.nodes('DN') as dn(c)
outer apply dn.c.nodes('ERR') as err(c)

declare @cDate datetime = getdate()

-- OK
update t
	set ErrorFileName = @FileName
	, ErrorFileDate = @cDate
	, SendStatus = 3 -- принят
from AKUZ.T_SENDING_DN as t
	join #sdn t2 on t.[FILENAME] = t2.[FILENAME]
where t2.ERR_FILE is null and t2.[GUID] is null
if @@rowcount > 0
	return;

-- флк
update t
	set ErrorFileName = @FileName
	, ErrorFileDate = @cDate
	, SendStatus = 2 -- ошибка
	, ErrorStr = 'ФЛК: '+t2.ERR_FILE
from AKUZ.T_SENDING_DN as t
	join #sdn t2 on t.[FILENAME] = t2.[FILENAME]
where t2.ERR_FILE is not null
if @@rowcount > 0
	return;

-- тк
update t
	set ErrorFileName = @FileName
	, ErrorFileDate = @cDate
	, SendStatus = case when t2.ERR_TYPE = 1 then 2 else 3 end -- ошибка/принят
	, ErrorStr = isnull('Код:'+ t2.ERR_CODE,'') + isnull(' | Наименование:'+ERR_NAME,'') + isnull(' | Поле:'+FIELD_NAME,'') + isnull(' | Описание:'+NOTE,'')
from AKUZ.T_SENDING_DN as t
	join #sdn t2 on t.[FILENAME] = t2.[FILENAME] and t.DispanserisationInit = t2.[GUID] 
where t2.ERR_FILE is null

-- тк ok (если есть те, которые не вошли в файл - проставляем ok)
update t
	set ErrorFileName = @FileName
	, ErrorFileDate = @cDate
	, SendStatus = 3 -- принят
from AKUZ.T_SENDING_DN as t
where t.[FILENAME]= (select top 1 [FILENAME] from #sdn)
	and t.DispanserisationInit not in (select [GUID] from #sdn)

return;

End