if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CancelHospitalisationReferralLoad]') and xtype = N'P')
drop procedure [dbo].[sp_CancelHospitalisationReferralLoad]
GO
create procedure dbo.sp_CancelHospitalisationReferralLoad @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]
  ,T.c.value('./MO_TO[1]', 'varchar(6)') as [MO_TO]
INTO #ZGLV
FROM @x.nodes('CANCEL_HOSPITALISATION_REFERRAL//header') T(c)

SELECT
  newid() as [CANCELHOSP_ID]
 ,T.c.value('./REFERRAL_NUMBER[1]', 'varchar(21)') AS [REFERRAL_NUMBER]
 ,T.c.value('./DATE[1]', 'datetime') AS [DATE]
 ,T.c.value('./REFERRAL_MO[1]', 'varchar(6)') AS [REFERRAL_MO]
 ,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('./CODE_ORG[1]', 'varchar(6)') AS [CODE_ORG]
 ,T.c.value('./DATE_CANCEL[1]', 'date') AS [DATE_CANCEL]
 ,T.c.value('./CANCEL_DESCRIPTION[1]', 'varchar(4096)') AS [CANCEL_DESCRIPTION]
 ,T.c.value('./HospRefIdFromDB[1]', 'varchar(36)') AS [HospRefIdFromDB] --заглушка
INTO #ZAP
FROM @x.nodes('CANCEL_HOSPITALISATION_REFERRAL//body') T(c)

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

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

update t1
set t1.[HospRefIdFromDB] = (
select hosp.HospitalizationReferralID
from ISZL.T_HOSPITALIZATION_REFERRAL hosp
where hosp.REFERRAL_DATE = t1.DATE 
and hosp.REFERRAL_NUMBER = t1.REFERRAL_NUMBER 
and hosp.LpuMainFrom = t1.REFERRAL_MO)
from #ZAP t1

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

if(@type = 'Insert')
begin

	insert into #Errors (CODE,COMMENT)
	select 1, 'Не найдено соответствующее направление на госпитализацию.'
	from #ZAP zap
	where zap.HospRefIdFromDB is null
	
	insert into #Errors (CODE,COMMENT)
	select 2, 'Отмена госпитализации уже существует.'
	from #ZAP zap
		join ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL cancel on cancel.HospitalizationReferral = zap.HospRefIdFromDB
	where zap.HospRefIdFromDB is not null

	if((select COUNT(*) from #Errors) = 0)
	begin
		insert into ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL
		(
			 [CancelHospitalizationOrReferralID]
			,[REFERRAL_NUMBER]
			,[DATE]
			,[REFERRAL_MO]
			,[REASON]
			,[CANCEL_SOURCE]
			,[CODE_ORG]
			,[DATE_CANCEL]
			,[CreatePlace]
			--,[Organization]
			,[BRANCH]
			,[CANCEL_DESCRIPTION]
			,[BranchObj]
			,[HospitalizationReferral]
		)
		select
			 zap.CANCELHOSP_ID
			,zap.REFERRAL_NUMBER
			,zap.DATE
			,zap.REFERRAL_MO
			,zap.REASON
			,zap.CANCEL_SOURSE
			,zap.CODE_ORG
			,zap.DATE_CANCEL
			,2
			--,org.OrganizationID
			,zap.BRANCH
			,zap.CANCEL_DESCRIPTION
			,branch.BranchID
			,zap.HospRefIdFromDB
		from #ZAP zap
		--join ISZL.T_ORGANIZATION org on org.Code = zap.CODE_ORG
		left join ISZL.T_BRANCH branch on branch.BR_CODE = zap.BRANCH
	end
end

if(@type = 'Delete')
begin
	insert into #Errors (CODE,COMMENT)
	select 3, 'Не найдено соответствующее направление на госпитализацию.'
	from #ZAP zap
	where zap.HospRefIdFromDB is null

	if((select COUNT(*) from #Errors) = 0)
	begin
		delete t1
		from ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL t1
		join #ZAP zap on t1.HospitalizationReferral = zap.HospRefIdFromDB
		where t1.REFERRAL_NUMBER = zap.REFERRAL_NUMBER and
		t1.[DATE] = zap.[DATE] and
		t1.REFERRAL_MO = zap.REFERRAL_MO and
		t1.DATE_CANCEL = zap.DATE_CANCEL
	end
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