if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FreeBedsInformationLoad_folder]') and xtype = N'P')
drop procedure [dbo].[sp_FreeBedsInformationLoad_folder]
GO
create procedure dbo.sp_FreeBedsInformationLoad_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('./ACTUAL_DATE[1]', 'datetime') AS ACTUAL_DATE
 ,T.c.value('./AMOUNT[1]', 'int') AS AMOUNT
 ,T.c.value('./FEMALE_BED[1]', 'int') AS FEMALE_BED
 ,T.c.value('./MALE_BED[1]', 'int') AS MALE_BED
 ,T.c.value('./CHILD_BED[1]', 'int') AS CHILD_BED
 ,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('./FreeBedsInformationID[1]', 'uniqueidentifier') AS FreeBedsInformationID--Заглушка
 ,T.c.value('./AmountBedID[1]', 'uniqueidentifier') AS AmountBedID--Заглушка
INTO #ZAP
FROM @x.nodes('FREE_BEDS_INFORMATION//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)

insert into #Errors
select 2, 'Классификатор профиля койки ('+cast(zap.V020_STRUCTURE_BED as varchar)+') не найден. N_ZAP('+cast(N_ZAP as varchar)+')'
from #ZAP zap
where not exists(select* from iszl.T_V020_STRUCTURE_BED t where t.IDK_PR = zap.V020_STRUCTURE_BED)


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

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

--DROP TABLE #ZAP
--DROP TABLE #Errors
insert into #Errors
select 3, 'Профиль койки не найден. N_ZAP('+cast(N_ZAP as varchar)+')'
from #ZAP
where AmountBedID is null


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

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

	-- удаляем, если есть
	delete t from [ISZL].[T_FREE_BEDS_INFORMATION] t
	join #ZAP zap on t.FreeBedsInformationID = zap.FreeBedsInformationID
	where [TYPE]='Insert'

	-- вставка, если нет ошибок
	INSERT INTO [ISZL].[T_FREE_BEDS_INFORMATION]
			   ([FreeBedsInformationID]
			   ,[ACTUAL_DATE]
			   ,[AMOUNT]
			   ,[FEMALE_BED]
			   ,[MALE_BED]
			   ,[CHILD_BED]
			   ,[BR_CODE]
			   ,[DIVISION]
			   ,[V020_STRUCTURE_BED]
			   ,[AmountBed])
	select
			   ID--(<FreeBedsInformationID, uniqueidentifier,>
			   ,zap.ACTUAL_DATE--, datetime,>
			   ,zap.AMOUNT--, int,>
			   ,zap.FEMALE_BED--, int,>
			   ,zap.MALE_BED--, int,>
			   ,zap.CHILD_BED--, int,>
			   ,zap.BR_CODE--, varchar(17),>
			   ,zap.DIVISION--, varchar(16),>
			   ,zap.V020_STRUCTURE_BED--, int,>
			   ,zap.AmountBedID--, uniqueidentifier,>)

	from #ZAP zap
	where [TYPE]='Insert'

	IF COL_LENGTH('ISZL.T_FREE_BEDS_INFORMATION', 'FILENAME') IS not NULL
	BEGIN
		update t1
		set t1.[FILENAME] = z.[FILENAME]
		from ISZL.T_FREE_BEDS_INFORMATION t1
		join #ZAP z on z.[ID] = t1.[FreeBedsInformationID]
		where [TYPE]='Insert'
	END
end


--Update
if((select COUNT(*) from #Errors) = 0)
begin
	insert into #Errors
	select 4, 'Запись не найдена в ранее загруженных списках'
	from #ZAP zap
	where zap.FreeBedsInformationID is null and [TYPE]='Update'

	UPDATE t
	   SET [ACTUAL_DATE] = zap.ACTUAL_DATE--, datetime,>
		  ,[AMOUNT] = zap.AMOUNT--, int,>
		  ,[FEMALE_BED] = zap.FEMALE_BED--, int,>
		  ,[MALE_BED] = zap.MALE_BED--, int,>
		  ,[CHILD_BED] = zap.CHILD_BED--, int,>
		  ,[BR_CODE] = zap.BR_CODE--, varchar(17),>
		  ,[DIVISION] = zap.DIVISION--, varchar(16),>
		  ,[V020_STRUCTURE_BED] = zap.V020_STRUCTURE_BED--, int,>
		  ,[AmountBed] = zap.AmountBedID--, uniqueidentifier,>
	from [ISZL].[T_FREE_BEDS_INFORMATION] t, #ZAP zap
	WHERE (select COUNT(*) from #Errors) = 0 and t.FreeBedsInformationID = zap.FreeBedsInformationID and [TYPE]='Update'

	IF COL_LENGTH('ISZL.T_FREE_BEDS_INFORMATION', 'FILENAME') IS not NULL
	BEGIN
		update t1
		set t1.[FILENAME] = z.[FILENAME]
		from ISZL.T_FREE_BEDS_INFORMATION t1
		join #ZAP z on z.FreeBedsInformationID = t1.[FreeBedsInformationID]
		where [TYPE]='Update'
	END
end


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

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

	delete t
	from [ISZL].[T_FREE_BEDS_INFORMATION] t, #ZAP zap
	WHERE (select COUNT(*) from #Errors) = 0 and t.FreeBedsInformationID = zap.FreeBedsInformationID 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
