IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ConvertMotionInHospital]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ConvertMotionInHospital]
--GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2018-12-05
---- Description:	конвертация MOTION_IN_HOSPITAL для Карелии по 263 приказу
---- =============================================
--CREATE PROCEDURE [dbo].[sp_ConvertMotionInHospital]
--	@Xml xml
--AS
--BEGIN
----------------------------------------------
--SELECT
--	 T.c.value('./ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
--	,T.c.value('./ZGLV[1]/TYPE[1]', 'varchar(6)') AS [TYPE]
--	,T.c.value('./ZGLV[1]/ID[1]', 'uniqueidentifier') AS [ID]
--	,T.c.value('./ZAP[1]/NOM_NAP[1]', 'varchar(16)') AS [NOM_NAP]
--	,T.c.value('./ZAP[1]/DTA_NAP[1]', 'datetime') AS [DTA_NAP]
--	,T.c.value('./ZAP[1]/FRM_MP[1]', 'int') AS [FRM_MP]
--	,T.c.value('./ZAP[1]/MCOD_STC[1]', 'varchar(6)') AS [MCOD_STC]
--	,T.c.value('./ZAP[1]/MPODR_STC[1]', 'varchar(2)') AS [MPODR_STC]
--	,T.c.value('./ZAP[1]/DTA_FKT[1]', 'datetime') AS [DTA_FKT]
--	,T.c.value('./ZAP[1]/DTA_END[1]', 'datetime') AS [DTA_END]
--	,T.c.value('./ZAP[1]/SMO_CODE[1]', 'varchar(5)') AS [SMO_CODE]
--	,T.c.value('./ZAP[1]/FAM[1]', 'varchar(40)') AS [FAM]
--	,T.c.value('./ZAP[1]/IM[1]', 'varchar(40)') AS [IM]
--	,T.c.value('./ZAP[1]/OT[1]', 'varchar(40)') AS [OT]
--	,T.c.value('./ZAP[1]/W[1]', 'int') AS [W]
--	,T.c.value('./ZAP[1]/DR[1]', 'datetime') AS [DR]
--	,T.c.value('./ZAP[1]/VPOLIS[1]', 'int') AS [VPOLIS]
--	,T.c.value('./ZAP[1]/SPOLIS[1]', 'varchar(10)') AS [SPOLIS]
--	,T.c.value('./ZAP[1]/NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
--	,T.c.value('./ZAP[1]/USL_OK[1]', 'int') AS [USL_OK]
--	,T.c.value('./ZAP[1]/KOD_PFO[1]', 'varchar(3)') AS [KOD_PFO]
--	,T.c.value('./ZAP[1]/KOD_PFK[1]', 'varchar(3)') AS [KOD_PFK]
--	,T.c.value('./ZAP[1]/NHISTORY[1]', 'varchar(50)') AS [NHISTORY]
--	,T.c.value('./ZAP[1]/HOSPITALISATION_ID[1]', 'varchar(36)') AS [HOSPITALISATION_ID]

-- into #XML
-- FROM   @xml.nodes('/MOTION_IN_HOSPITAL') T(c)



-- DECLARE @t1 table
--(OuterXML varchar(max), ERR varchar(max))

--create table #ERROR
--(
--	[OSHIB] int,
--	[TEXT] varchar(max)
--)
 

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 1, 'Не заполнен обязательный атрибут MCOD_STC'
--	from #XML
--	where MCOD_STC is null
	
--	insert into #ERROR ([OSHIB],[TEXT])
--	select 2, 'Не заполнен обязательный атрибут TYPE'
--	from #XML
--	where [TYPE] is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 3, 'Не заполнен обязательный атрибут ID'
--	from #XML
--	where ID is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 4, 'Не заполнен обязательный атрибут KOD_PFO'
--	from #XML
--	where KOD_PFO is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 5, 'Не заполнен обязательный атрибут KOD_PFK'
--	from #XML
--	where KOD_PFK is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 6, 'Не заполнен обязательный атрибут DTA_FKT'
--	from #XML
--	where DTA_FKT is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 7, 'Не заполнен обязательный атрибут DTA_END'
--	from #XML
--	where DTA_END is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 8, 'Не заполнен обязательный атрибут NHISTORY'
--	from #XML
--	where NHISTORY is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 9, 'Не заполнен обязательный атрибут FAM'
--	from #XML
--	where FAM is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 10, 'Не заполнен обязательный атрибут IM'
--	from #XML
--	where IM is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 11, 'Не заполнен обязательный атрибут DR'
--	from #XML
--	where DR is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 11, 'Не заполнен обязательный атрибут NPOLIS'
--	from #XML
--	where NPOLIS is null

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 12, 'Не заполнен обязательный атрибут USL_OK'
--	from #XML
--	where USL_OK is null



-- update  #XML
-- set [HOSPITALISATION_ID] = (
--			select top 1 ID 
--			from RDS.T_HOSPITALIZATION 
--			where LastName = [FAM] 
--				and FirstName = IM 
--				and ISNULL(FatherName,0) = ISNULL(OT,0) 
--				and Birthday = DR
--				and PolisNumber = [NPOLIS]
--				and HospitalizationDate = [DTA_FKT]
--				and ISNULL(ReferralNumber,0) = ISNULL([NOM_NAP],0) 
--		)	

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 13, 'Не найдена запись госпитализации по номеру направления, фио, др, номер полиса, дата госпитализации'
--	from #XML
--	where [HOSPITALISATION_ID] is null

	
--if (Select COUNT(*) from #ERROR) <> 0--Если есть ошибки, то дальше нет смысла делать проверки
--begin
--INSERT @t1 (OuterXML, ERR)
--select null,
--	(select 
--	'<?xml version="1.0" encoding="Windows-1251"?>
--	' + (
--	select 
--		OSHIB AS 'OSHIB',
--		[TEXT] as 'TEXT'
--	FROM #ERROR
--	FOR XML PATH('PR'), 
--	ROOT('FLK_P')
--	)	)
--end
--else
--begin

--INSERT @t1 (OuterXML, ERR)
--select
--	(select
--		--header
--		[MCOD_STC]		AS 'header/LPU' 
--		,[TYPE]			AS 'header/TYPE'
--		,[ID]			AS 'header/ID'
--		--body
--		,[HOSPITALISATION_ID]	as 'body/HOSPITALISATION_ID'
--		,isnull([MPODR_STC],1)	as 'body/BRANCH'
--		,[KOD_PFO]		as 'body/DIVISION'
--		,[KOD_PFK]		as 'body/STRUCTURE_BED'
--		,FORMAT([DTA_FKT],'yyyy-MM-dd')		as 'body/DATE_IN'
--		,FORMAT([DTA_END],'yyyy-MM-dd')		as 'body/DATE_OUT'
--		,[NHISTORY]		as 'body/MED_CARD_NUMBER'
--		,FORMAT([DTA_FKT],'yyyy-MM-dd')		as 'body/HOSPITALISATION_DATE'
--		,USL_OK			as 'body/USL_OK'
--	from #XML
--	FOR XML PATH (''), ROOT('MOTION_IN_HOSPITAL')
--)
--,null
--end

--select * from @t1

--drop table #ERROR	
--drop table #XML
--end

