if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BranchLoad]') and xtype = N'P')
drop procedure [dbo].[sp_BranchLoad]
GO
create procedure dbo.sp_BranchLoad @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
as
begin
SELECT 
   T.c.value('./MO[1]', 'varchar(6)') AS MO
  ,T.c.value('./TYPE[1]', 'varchar(6)') AS [TYPE]
INTO #ZGLV
FROM @x.nodes('BRANCH//header') T(c)

SELECT 
  T.c.value('./BR_CODE[1]', 'varchar(17)') AS BR_CODE
 ,T.c.value('./NAME[1]', 'varchar(266)') AS NAME
 ,T.c.value('./ADDRESS[1]', 'varchar(255)') AS [ADDRESS]
 ,T.c.value('./PHONE[1]', 'varchar(255)') AS PHONE
 ,T.c.value('./RUCV[1]', 'varchar(255)') AS RUCV
 ,T.c.value('./ACTIVE[1]', 'int') AS ACTIVE

INTO #ZAP
FROM @x.nodes('BRANCH//body') T(c)

create table #Errors
(
	CODE int,
	COMMENT varchar(250)
)

alter table #ZAP add BranchID uniqueidentifier

declare @type varchar(6)
set @type = (select zglv.[TYPE] from #ZGLV zglv)

if (@type <> 'Insert' and @type <> 'Update' and @type <> 'Delete')
begin
	insert into #Errors (CODE,COMMENT)
	select 4, 'Неверный тип операции.'
end


update zap
set BranchID = br.BranchID
from #ZAP zap
outer apply #ZGLV zglv
join ISZL.T_F003 lm on lm.MCOD = zglv.MO
join ISZL.T_BRANCH br on br.BR_CODE = zap.BR_CODE



insert into #Errors (CODE,COMMENT)
select 1, 'Обязательное поле BR_CODE' from #ZAP
where BR_CODE = '' or BR_CODE is null

---- DEBUG
--select* from #ZGLV
--select * from #ZAP zap
--select* from #Errors

--DROP TABLE #ZGLV
--DROP TABLE #ZAP
--DROP TABLE #Errors
---- end DEBUG



if(@type = 'Insert')
begin
	-- нельзя удалять, тк могут быть отделения в подразделении
	-- удаляем, если есть
	--delete br from [ISZL].[T_BRANCH] br
	--join #ZAP zap on br.BranchID = zap.BranchID
	insert into #Errors (CODE,COMMENT)
	select 2, 'Подразделение было добавлено ранее' 
	from #ZAP
	where BranchID is not null


	-- вставка, если нет ошибок
	INSERT INTO [ISZL].[T_BRANCH]([BranchID],[BR_CODE],[NAME],[PHONE],[ADDRESS],[RUCV],[ACTIVE],[F003Mo])
	select NEWID()
		,zap.BR_CODE
		,zap.NAME
		,zap.PHONE
		,zap.[ADDRESS]
		,zap.RUCV
		,(case when zap.ACTIVE = 0 then 0 else 1 end)
		,zglv.MO
	from #ZAP zap, #ZGLV zglv
	where (select COUNT(*) from #Errors) = 0
end


if(@type = 'Update')
begin
	UPDATE t
	   SET [NAME] = zap.Name
		  ,[PHONE] = zap.PHONE
		  ,[ADDRESS] = zap.[ADDRESS]
		  ,[RUCV] = zap.RUCV
		  ,[ACTIVE] = (case when zap.ACTIVE = 0 then 0 else 1 end)
		  ,[F003Mo] = zglv.MO
	from [ISZL].[T_BRANCH] t, #ZAP zap, #ZGLV zglv
	WHERE (select COUNT(*) from #Errors) = 0 and t.BranchID = zap.BranchID
	
	-- вставка, если не найдена
	INSERT INTO [ISZL].[T_BRANCH]([BranchID],[BR_CODE],[NAME],[PHONE],[ADDRESS],[RUCV],[ACTIVE],[F003Mo])
	select NEWID()
		,zap.BR_CODE
		,zap.NAME
		,zap.PHONE
		,zap.[ADDRESS]
		,zap.RUCV
		,(case when zap.ACTIVE = 0 then 0 else 1 end)
		,zglv.MO
	from #ZAP zap, #ZGLV zglv
	where (select COUNT(*) from #Errors) = 0 and zap.BranchID is null
end


if(@type = 'Delete')
begin

	insert into #Errors
	select 3, 'Запись не найдена в ранее загруженных списках'
	from #ZAP zap
	where zap.BranchID is null

	delete t
	from [ISZL].[T_BRANCH] t, #ZAP zap
	WHERE (select COUNT(*) from #Errors) = 0 and t.BranchID = zap.BranchID

end

select (case 
	   when (select COUNT(*) from #Errors) = 0 
	   then cast(1 as bit)
	   else case
			when (select COUNT(*) from #Errors) > 0
			then cast(0 as bit)
			end
	   end),
	   '<?xml version="1.0" encoding="Windows-1251"?>' 
		+ 
		(
			SELECT 
				CODE as 'CODE'
				,COMMENT as 'COMMENT'
			FROM #Errors 
			FOR XML PATH('ERR'),ROOT('ROOT')
		)
DROP TABLE #ZGLV
DROP TABLE #ZAP
DROP TABLE #Errors
end
