if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AmountbedLoad]') and xtype = N'P')
drop procedure [dbo].[sp_AmountbedLoad]
GO
create procedure dbo.sp_AmountbedLoad @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('AMOUNT_BED//header') T(c)

SELECT 
 T.c.value('./DATE_BEGIN[1]', 'datetime') AS DATE_BEGIN
 ,T.c.value('./DATE_END[1]', 'datetime') AS DATE_END
 ,T.c.value('./BR_CODE[1]', 'varchar(17)') AS BR_CODE
 ,T.c.value('./DIVISION[1]', 'varchar(16)') AS DIVISION
 ,T.c.value('./V020_STRUCTURE_BED[1]', 'int') AS V020_STRUCTURE_BED
 ,T.c.value('../header[1]/MO[1]', 'varchar(6)') AS MO

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

alter table #ZAP add AmountBedID uniqueidentifier
alter table #ZAP add DivisionID uniqueidentifier
alter table #ZAP add BranchID uniqueidentifier


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

insert into #Errors
select 1, 'МО не найдена'
from #ZGLV zglv
where not exists(select* from iszl.t_f003 lm where lm.mcod = zglv.MO)

--insert into #Errors
--select 2, 'Профиль койки не найден'
--from #ZAP zap, #ZGLV zglv
--where not exists(select* from iszl.T_V020_STRUCTURE_BED t where t.IDK_PR = zap.V020_STRUCTURE_BED)


-- поиск отделения и филиала
update zap
set AmountBedID = ( select top 1 ab.AmountBedID 
					from iszl.T_AMOUNT_BED ab
						join iszl.T_DIVISION div on div.DivisionID = ab.DivisionObj
						left join iszl.T_BRANCH br on div.Branch = br.BranchID 
					where ab.V020_STRUCTURE_BED = zap.V020_STRUCTURE_BED and div.CODE = zap.DIVISION 
						and (div.LpuMain=zglv.MO OR br.F003Mo=zglv.MO) )
	,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.DIVISION
						and (div.LpuMain=zglv.MO OR br.F003Mo=zglv.MO) )
	,BranchID = (select top 1 br.BranchID from iszl.T_BRANCH br where br.BR_CODE=zap.BR_CODE and br.F003Mo=zglv.MO)
	
from #ZAP zap
	outer apply #ZGLV zglv

--select* from #ZGLV
--select * from #ZAP zap
--select* from #Errors
insert into #Errors
select 2, 'Не найдено подразделение ('+BR_CODE+') Для МО ('+ MO +')'
from #ZAP
where BranchID is null and BR_CODE is not null

insert into #Errors
select 3, 'Не найдено отделение ('+DIVISION+'). Для подразделение ('+ BR_CODE +'). Для МО ('+ MO +')'
from #ZAP
where DivisionID is null and BranchID is not null



--insert into #Errors
--select 4, 'Не найден профиль койки ('+cast(V020_STRUCTURE_BED as varchar(10))+'). Для отделения ('+DIVISION+'). Для подразделения ('+ BR_CODE +'). Для МО ('+ MO +')'
--from #ZAP
--where AmountBedID is null and BranchID is not null and DivisionID is not null

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


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 6, 'Неверный тип операции.'
end

if(@type = 'Insert')
begin

	-- удаляем, если есть
	delete t from iszl.T_AMOUNT_BED t
	join #ZAP zap on t.AmountBedID = zap.AmountBedID

	-- вставка, если нет ошибок
	INSERT INTO [ISZL].[T_AMOUNT_BED]
			   ([AmountBedID]
			   ,[DATE_BEGIN]
			   ,[DATE_END]
			   ,[BR_CODE]
			   ,[DIVISION]
			   ,[V020_STRUCTURE_BED]
			   ,[DivisionObj])
	select
			   NEWID()--(<AmountBedID, uniqueidentifier,>
			   ,zap.DATE_BEGIN--, datetime,>
			   ,zap.DATE_END--, datetime,>
			   ,zap.BR_CODE--, varchar(17),>
			   ,zap.DIVISION--, varchar(16),>
			   ,zap.V020_STRUCTURE_BED--, int,>
			   ,zap.DivisionID--, uniqueidentifier,>)
	from #ZAP zap, #ZGLV zglv
	where (select COUNT(*) from #Errors) = 0

end

if(@type = 'Update')
begin

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

	UPDATE t
	   SET [DATE_BEGIN] = zap.DATE_BEGIN--, datetime,>
		  ,[DATE_END] = zap.DATE_END--, datetime,>
		  ,[BR_CODE] = zap.BR_CODE--, varchar(17),>
		  ,[DIVISION] = zap.DIVISION--, varchar(16),>
		  ,[V020_STRUCTURE_BED] = zap.V020_STRUCTURE_BED--, int,>
		  ,[DivisionObj] = zap.DivisionID--<DivisionObj, uniqueidentifier,>

	from [ISZL].[T_AMOUNT_BED] t, #ZAP zap, #ZGLV zglv
	WHERE (select COUNT(*) from #Errors) = 0 and t.AmountBedID = zap.AmountBedID

end

if(@type = 'Delete')
begin

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

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

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
