if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DivisionLoad_folder]') and xtype = N'P')
drop procedure [dbo].[sp_DivisionLoad_folder]
GO
create procedure dbo.sp_DivisionLoad_folder @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
as
begin


SELECT 
  newid() as [ID]
 ,T.c.value('../HEADER[1]/FILENAME[1]', 'varchar(26)') AS [FILENAME]
 ,T.c.value('../HEADER[1]/MO[1]', 'varchar(6)') AS [MO]
 ,T.c.value('./N_ZAP[1]', 'int') AS [N_ZAP]
 ,T.c.value('./TYPE[1]', 'varchar(6)') AS [TYPE]
 ,T.c.value('./BR_CODE[1]', 'varchar(17)') AS BR_CODE
 ,T.c.value('./CODE[1]', 'varchar(16)') AS CODE
 ,T.c.value('./NAME[1]', 'varchar(128)') AS NAME
 ,T.c.value('./FULLNAME[1]', 'varchar(255)') AS FULLNAME
 ,T.c.value('./ADDRESS[1]', 'varchar(255)') AS [ADDRESS]
 ,T.c.value('./DIVISION_TYPE[1]', 'int') AS DIVISION_TYPE
 ,T.c.value('./ACTIVE[1]', 'int') AS ACTIVE
 ,T.c.value('./DivisionID[1]', 'uniqueidentifier') AS DivisionID--
 ,T.c.value('./BranchID[1]', 'uniqueidentifier') AS BranchID--

INTO #ZAP
FROM @x.nodes('DIVISION//BODY') T(c)


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

insert into #Errors
select 1, 'МО не найдена. N_ZAP('+cast(N_ZAP as varchar)+')'
from #ZAP z
where not exists(select * from iszl.t_f003 lm where lm.mcod = z.MO)

-- поиск отделения и филиала
update zap
set DivisionID = ( select top 1 div.DivisionID 
					from iszl.T_DIVISION div 
						left join iszl.T_BRANCH br on div.Branch = br.BranchID 
					where div.CODE = zap.CODE
						and (div.LpuMain=zap.MO OR br.F003Mo=zap.MO) )
	,BranchID = (select top 1 br.BranchID from iszl.T_BRANCH br where br.BR_CODE=zap.BR_CODE and br.F003Mo=zap.MO)
from #ZAP zap

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

--DROP TABLE #ZAP
--DROP TABLE #Errors

insert into #Errors (CODE,COMMENT)
select 4, 'Неверный тип операции.'
from #ZAP 
where [TYPE] <> 'Insert' and [TYPE] <> 'Update' and [TYPE] <> 'Delete'

--Insert
if((select COUNT(*) from #Errors) = 0)
begin
	-- нельзя удалять, тк могут быть профили коек в отделении
	-- удаляем, если есть
	--delete t from [ISZL].[T_DIVISION] t
	--join #ZAP zap on t.DivisionID = zap.DivisionID
	insert into #Errors (CODE,COMMENT)
	select 2, 'Отделение было добавлено ранее. N_ZAP('+cast(N_ZAP as varchar)+')'
	from #ZAP
	where DivisionID is not null and [TYPE] = 'Insert'

	-- вставка, если нет ошибок
	INSERT INTO [ISZL].[T_DIVISION]([DivisionID],[NAME],[CODE],[FULLNAME],[ADDRESS],[ACTIVE],[Branch],[V006MedicalTerms],[BR_CODE],[LpuMain])
	select [ID]
		,zap.NAME
		,zap.CODE
		,zap.FULLNAME
		,zap.[ADDRESS]
		,zap.ACTIVE
		,zap.BranchID
		,zap.DIVISION_TYPE
		,zap.BR_CODE
		,case when zap.BranchID is null then zap.MO else null end
	from #ZAP zap
	where (select COUNT(*) from #Errors) = 0 and [TYPE] = 'Insert'

	IF COL_LENGTH('ISZL.T_DIVISION', 'FILENAME') IS not NULL
	BEGIN
		update t1
		set t1.[FILENAME] = z.[FILENAME]
		from ISZL.T_DIVISION t1
		join #ZAP z on [ID] = t1.[DivisionID]
		where((select COUNT(*) from #Errors) = 0) and [TYPE] = 'Insert'
	END
end

--Update
if((select COUNT(*) from #Errors) = 0)
begin
	UPDATE t
	   SET [NAME] = zap.NAME
		  ,[CODE] = zap.CODE
		  ,[FULLNAME] = zap.FULLNAME
		  ,[ADDRESS] = zap.[ADDRESS]
		  ,[ACTIVE] = (case when zap.ACTIVE = 0 then 0 else 1 end)
		  ,[Branch] = zap.BranchID
		  ,[V006MedicalTerms] = zap.DIVISION_TYPE
		  ,[BR_CODE] = zap.BR_CODE
		  ,[LpuMain] = case when zap.BranchID is null then zap.MO else null end
	from [ISZL].[T_DIVISION] t, #ZAP zap
	WHERE (select COUNT(*) from #Errors) = 0 and zap.DivisionID is not null and t.DivisionID = zap.DivisionID and [TYPE] = 'Update'

	-- вставка, если не найдена
	INSERT INTO [ISZL].[T_DIVISION]([DivisionID],[NAME],[CODE],[FULLNAME],[ADDRESS],[ACTIVE],[Branch],[V006MedicalTerms],[BR_CODE],[LpuMain])
	select [ID]
		,zap.NAME
		,zap.CODE
		,zap.FULLNAME
		,zap.[ADDRESS]
		,zap.ACTIVE
		,zap.BranchID
		,zap.DIVISION_TYPE
		,zap.BR_CODE
		,case when zap.BranchID is null then zap.MO else null end
	from #ZAP zap
	where (select COUNT(*) from #Errors) = 0 and zap.DivisionID is null and [TYPE] = 'Update'

	IF COL_LENGTH('ISZL.T_DIVISION', 'FILENAME') IS not NULL
	BEGIN
		update t1
		set t1.[FILENAME] = z.[FILENAME]
		from ISZL.T_DIVISION t1
		join #ZAP z on [ID] = t1.[DivisionID]
		where((select COUNT(*) from #Errors) = 0) and [TYPE] = 'Update'
	END
end

--Delete
if((select COUNT(*) from #Errors) = 0)
begin

	insert into #Errors
	select 3, 'Запись не найдена в ранее загруженных списках. N_ZAP('+cast(N_ZAP as varchar)+')'
	from #ZAP zap
	where zap.DivisionID is null and [TYPE] = 'Delete'

	delete t
	from [ISZL].[T_DIVISION] t, #ZAP zap
	WHERE (select COUNT(*) from #Errors) = 0 and t.DivisionID = zap.DivisionID and [TYPE] = 'Delete'

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 #ZAP
DROP TABLE #Errors
end
