if exists ( select * from sys.procedures p where p.name = 'sp_LoadMtrSchetZapErrors' ) DROP PROCEDURE [dbo].[sp_LoadMtrSchetZapErrors]
GO
CREATE PROCEDURE [dbo].[sp_LoadMtrSchetZapErrors]
	@Xml xml
AS

IF OBJECT_ID('tempdb.dbo.#mtrError', 'U') IS NOT NULL
  DROP TABLE #mtrError;

declare @fileName varchar(64);
select @fileName = @Xml.query('/FLK_P/FNAME_I[1]').value('.', 'varchar(64)');

declare @fileNameWithExt varchar(64);
set @fileNameWithExt = @fileName;

set @fileName = SUBSTRING(@fileName, 1, len(@fileName) - CHARINDEX('.', reverse(@fileName)));

create table #mtrError(OSHIB varchar(20) not null, IM_POL varchar(512) not null, BAS_EL varchar(512) not null, N_ZAP int null, COMMENT varchar(512) not null);

insert into #mtrError(OSHIB, IM_POL, BAS_EL, N_ZAP, COMMENT)
select 
	 T.c.query('OSHIB').value('.', 'varchar(20)')
	,T.c.query('IM_POL').value('.', 'varchar(512)')
	,T.c.query('BAS_EL').value('.', 'varchar(512)')
	,T.c.query('N_ZAP').value('number(.)', 'int')
	,T.c.query('COMMENT').value('.', 'varchar(512)')
from @Xml.nodes('/FLK_P/PR') T(c);

--проверка на существование файла
declare @fileCount int;
select @fileCount = (
	select count(*)
	from ies.T_SCHET_FILE sf
	inner join VCLib.T_FILE f on sf.FileID = f.FileID
	inner join ies.T_SCHET s on sf.Schet = s.SchetID
	where s.IsDelete = 0 and s.type_ = 539 
		and SUBSTRING(f.Name, 1, len(f.Name) - CHARINDEX('.', reverse(f.Name))) = @fileName);

if @fileCount = 0
begin
	RAISERROR ('Файл %s не найден.', 16, 1, @fileNameWithExt);
	return;
end;

--проверка на N_ZAP
declare @Nzaps varchar(max) = '';
select @Nzaps = @Nzaps + convert(varchar, N_ZAP) + ', '
from #mtrError
	where N_ZAP not in (
		select sz.N_ZAP from ies.T_SCHET_ZAP sz
		inner join ies.T_SCHET_FILE sf on sz.Schet = sf.Schet
		inner join vclib.T_FILE f on sf.FileID = f.FileID
		inner join ies.T_SCHET s on sf.Schet = s.SchetID

		where s.IsDelete = 0 and s.type_ = 539 
			and SUBSTRING(f.Name, 1, len(f.Name) - CHARINDEX('.', reverse(f.Name))) = @fileName
	);

if @Nzaps != ''
begin
	SET @Nzaps = SUBSTRING(@Nzaps, 1, len(@Nzaps)-1) + '.';
	RAISERROR ('Не удалось найти номера счетов N_ZAP: %s', 16, 1, @Nzaps);
end;

--записываем ошибки
insert into ies.T_MTR_SCHET_ZAP_ERROR(MtrSchetZapErrorID, OSHIB, IM_POL, BAS_EL, Comment, MtrSchetZap)
select NEWID(), OSHIB, IM_POL, BAS_EL, COMMENT, zap.SchetZapID
from #mtrError me
inner join ies.T_SCHET_ZAP zap
on me.N_ZAP = zap.N_ZAP
inner join ies.T_SCHET_FILE sf
on sf.Schet = zap.Schet
inner join VCLib.T_FILE f
on f.FileID = sf.FileID
inner join ies.T_SCHET s on sf.Schet = s.SchetID
where s.IsDelete = 0 and s.type_ = 539 
	and SUBSTRING(f.Name, 1, len(f.Name) - CHARINDEX('.', reverse(f.Name))) = @fileName;

--если N_ZAP в xml = null, тогда просто выводим предупреждение
declare @nullNzapCount int;
set @nullNzapCount = (select count(*) from #mtrError where N_ZAP is null);
if @nullNzapCount != 0
begin
	RAISERROR ('Записей было пропущено: %d (не указан N_ZAP).', 15, 1, @nullNzapCount);
end;