if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FreeBedLoad_karelia]') and xtype = N'P')
drop procedure [dbo].[sp_FreeBedLoad_karelia]
GO
create procedure dbo.sp_FreeBedLoad_karelia @x xml
as
begin
SELECT 
  T.c.value('./DATA[1]', 'datetime') AS [DATA]
  ,T.c.value('./TYPE[1]', 'varchar(6)') AS [TYPE]
  ,T.c.value('./ID[1]', 'varchar(36)') AS [ID]
INTO #ZGLV
FROM @x.nodes('FREE_BEDS_INFORMATION//ZGLV') T(c)

SELECT 
 T.c.value('./DTA_RAB[1]', 'datetime') AS DTA_RAB
 ,T.c.value('./MCOD_STC[1]', 'varchar(6)') AS MCOD_STC
 ,T.c.value('./MPODR_STC[1]', 'varchar(2)') AS MPODR_STC
 ,T.c.value('./USL_OK[1]', 'int') AS USL_OK
 ,T.c.value('./KOD_PFK[1]', 'int') AS KOD_PFK
 ,T.c.value('./KOL_PAC[1]', 'int') AS KOL_PAC
 ,T.c.value('./KOL_IN[1]', 'int') AS KOL_IN
 ,T.c.value('./KOL_OUT[1]', 'int') AS KOL_OUT
 ,T.c.value('./KOL_PLN[1]', 'int') AS KOL_PLN
 ,T.c.value('./KOL_PUS[1]', 'int') AS KOL_PUS
 ,T.c.value('./KOL_PUS_D[1]', 'int') AS KOL_PUS_D
 ,T.c.value('./KOL_PUS_M[1]', 'int') AS KOL_PUS_M
 ,T.c.value('./KOL_PUS_G[1]', 'int') AS KOL_PUS_G
 ,T.c.value('./ISP_V[1]', 'int') AS ISP_V --New
 ,T.c.value('./ISP_K[1]', 'int') AS ISP_K --New
 ,T.c.value('./AmountBed[1]', 'varchar(36)') as AmountBed
 ,T.c.value('./DivisionCode[1]', 'varchar(16)') as DivisionCode
 ,T.c.value('./Dubl[1]', 'varchar(36)') as Dubl
INTO #ZAP
FROM @x.nodes('FREE_BEDS_INFORMATION//ZAP') T(c)


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

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

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

update zap
set DivisionCode = (select top 1 d.CODE
                    from iszl.T_DIVISION d 
					join iszl.T_F003 f003 on f003.MCOD = d.LpuMain
					join iszl.T_BRANCH br on br.BranchID = d.Branch
					where f003.MCOD = zap.MCOD_STC and d.BR_CODE = zap.MPODR_STC 
					and exists  (select * from iszl.T_AMOUNT_BED ab 
                                 where d.DivisionID = ab.DivisionObj AND ab.V020_STRUCTURE_BED = zap.KOD_PFK))
from #ZAP zap

update zap
set AmountBed = (select top 1 AmountBedID 
                 from ISZL.T_AMOUNT_BED ab 
				 join ISZL.T_DIVISION d on d.DivisionID = ab.DivisionObj
				 join iszl.T_F003 f003 on f003.MCOD = d.LpuMain
				 where f003.MCOD = zap.MCOD_STC and d.BR_CODE = zap.MPODR_STC and d.CODE = zap.DivisionCode and ab.V020_STRUCTURE_BED = zap.KOD_PFK)
from #ZAP zap

insert into #Errors
select 1, 'Профиль койки не найден'
from #ZAP zap
where zap.AmountBed is null

update zap
set Dubl = 
		(select top 1 f.FreeBedsInformationID 
		from iszl.T_FREE_BEDS_INFORMATION f 
			where  zap.DTA_RAB = f.ACTUAL_DATE and
			       zap.AmountBed = f.AmountBed and
				   zap.KOL_PUS_D = f.CHILD_BED and
				   zap.KOL_PUS_G = f.FEMALE_BED and
				   zap.KOL_PUS_M = f.MALE_BED)
from #ZAP zap 

insert into #Errors
select 2, 'дубль информации о свободной койке'
from #ZAP zap
where zap.Dubl is not null

--если нет ошибок, то вставка
INSERT INTO [ISZL].[T_FREE_BEDS_INFORMATION]
           ([FreeBedsInformationID]
           ,[ACTUAL_DATE]
           ,[AMOUNT]
           ,[FEMALE_BED]
           ,[MALE_BED]
           ,[CHILD_BED]
           ,[BR_CODE]
           ,[DIVISION]
           ,[V020_STRUCTURE_BED]
           ,[AmountBed]
		   ,[ISP_V]
		   ,[ISP_K]
		   ,[InfoAbout])
select
           newid() --(<FreeBedsInformationID, uniqueidentifier,>
           ,zap.DTA_RAB --<ACTUAL_DATE, datetime,>
           ,zap.KOL_PUS--<AMOUNT, int,>
           ,zap.KOL_PUS_G--<FEMALE_BED, int,>
           ,zap.KOL_PUS_M--<MALE_BED, int,>
           ,zap.KOL_PUS_D --<CHILD_BED, int,>
           ,cast(zap.MPODR_STC as varchar(17)) --<BR_CODE, varchar(17),>
           ,zap.DivisionCode--<DIVISION, varchar(16),>
           ,zap.KOD_PFK --<V020_STRUCTURE_BED, int,>
           ,zap.AmountBed --<AmountBed, uniqueidentifier,>
		   ,zap.ISP_V
		   ,zap.ISP_K
		   ,0
from #ZAP zap--, #ZGLV zglv
where (select COUNT(*) from #Errors) = 0

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
