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

SELECT
  newid() as [CANHOSP_ID]
 ,T.c.value('./DATE[1]', 'datetime') AS [DATE]
 ,T.c.value('./MED_CARD_NUMBER[1]', 'varchar(50)') AS [MED_CARD_NUMBER]
 ,T.c.value('./HOSPITALISATION_DIVISION[1]', 'varchar(16)') AS [HOSPITALISATION_DIVISION]
 ,T.c.value('./BRANCH[1]', 'varchar(17)') AS [BRANCH]
 ,T.c.value('./REASON[1]', 'int') AS [REASON]
 ,T.c.value('./CANCEL_SOURSE[1]', 'int') AS [CANCEL_SOURSE]
 ,T.c.value('./DATE_CANCEL[1]', 'datetime') AS [DATE_CANCEL]
 ,T.c.value('./CODE_ORG[1]', 'varchar(6)') AS [CODE_ORG]
 ,T.c.value('./CANCEL_DESCRIPTION[1]', 'varchar(4096)') AS [CANCEL_DESCRIPTION]
 ,T.c.value('./HospIdFromDB[1]', 'varchar(36)') AS [HospIdFromDB] --заглушка
 ,T.c.value('../header[1]/MO[1]', 'varchar(6)') AS [MO]
INTO #ZAP
FROM @x.nodes('CANCEL_HOSPITALISATION//body') T(c)

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

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


if(@type = 'Insert')
begin
	update t1
	set t1.[HospIdFromDB] =  (
	select hosp.HospitalizationID
	from ISZL.T_HOSPITALIZATION hosp
	where hosp.HOSPITALIZATION_DATE = t1.DATE 
	and hosp.MED_CARD_NUMBER = t1.MED_CARD_NUMBER 
	and hosp.DIVISION = t1.HOSPITALISATION_DIVISION
	)
	from #ZAP t1

	
	insert into #Errors (CODE,COMMENT)
	select 1, 'Не найдена соответствующая госпитализация.'
	from #ZAP
	where HospIdFromDB is null

	insert into #Errors (CODE,COMMENT)
	select 2, 'Отмена госпитализации уже существует.'
	from #ZAP zap
	join ISZL.T_CANCEL_HOSPITALIZATION cancel on cancel.Hospitalization = zap.HospIdFromDB
	where zap.HospIdFromDB is not null

	if((select COUNT(*) from #Errors) = 0)
	begin
		insert into ISZL.T_CANCEL_HOSPITALIZATION
		(
			 [CancelHospitalizationID]
			,[DATE]
			,[MED_CARD_NUMBER]
			,[HOSPITALIZATION_DIVISION]
			,[BRANCH]
			,[REASON]
			,[CANCEL_SOURCE]
			,[DATE_CANCEL]
			,[CODE_ORG]
			,[CANCEL_DESCRIPTION]
			,[DivisionObj]
			,[BranchObj]
			,[CreatePlace]
			,[Hospitalization]
		)
		select
			 zap.CANHOSP_ID
			,zap.DATE
			,zap.MED_CARD_NUMBER
			,zap.HOSPITALISATION_DIVISION
			,zap.BRANCH
			,zap.REASON
			,zap.CANCEL_SOURSE
			,zap.DATE_CANCEL
			,zap.CODE_ORG
			,zap.CANCEL_DESCRIPTION
			,div.DivisionID
			,br.BranchID
			,2--rabbit
			,zap.HospIdFromDB
		from #ZAP zap
		left join ISZL.T_BRANCH br on br.BR_CODE = zap.BRANCH and br.f003mo = zap.MO
		join ISZL.T_DIVISION div on div.CODE = zap.HOSPITALISATION_DIVISION and (div.lpumain = zap.MO or br.branchid  = div.branch)

	end
end
else
begin
	insert into #Errors (CODE,COMMENT)
	select 3, 'Неверный тип операции.'
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