IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ConvertHospitalisation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ConvertHospitalisation]

--GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2018-12-05
---- Description:	конвертация HOSPITALISATION для Карелии по 263 приказу
---- =============================================
--CREATE PROCEDURE [dbo].[sp_ConvertHospitalisation]
--	@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]/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]/TIM_FKT[1]', 'varchar(5)') AS [TIM_FKT]
--	,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]/SMO_CODE[1]', 'varchar(5)') AS [SMO_CODE]
--	,T.c.value('./ZAP[1]/ST_OKATO[1]', 'varchar(5)') AS [ST_OKATO]
--	,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]/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]/DS[1]', 'varchar(10)') AS [DS]
--	,T.c.value('./PatientID[1]', 'varchar(36)') AS [PatientID] -- заглушка
-- into #XML_EXTR
-- FROM   @xml.nodes('/EXTR_HOSPITALISATION') T(c)

-- 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]/MCOD_NAP[1]', 'varchar(6)') AS [MCOD_NAP]--
--	,T.c.value('./ZAP[1]/MPODR_NAP[1]', 'varchar(2)') AS [MPODR_NAP]--
--	,T.c.value('./ZAP[1]/DTA_FKT[1]', 'datetime') AS [DTA_FKT]
--	,T.c.value('./ZAP[1]/TIM_FKT[1]', 'varchar(5)') AS [TIM_FKT]
--	,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]/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]/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]/DS[1]', 'varchar(10)') AS [DS]
-- into #XML_PLAN
-- FROM   @xml.nodes('/PLAN_HOSPITALISATION') T(c)


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

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

--if ((select count(*) from #XML_EXTR)>0)
--begin

--	update t1
--	set t1.[PatientID] =  (
--	  select top 1 PatientID from [RDS].T_PATIENT t2
--	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR  and t2.PolisNumber = t1.NPOLIS )   
--	 )
--	from #XML_EXTR t1
--	where t1.[PatientID] is null  and t1.DR is null
--	--step 2
--	update t1
--	set t1.[PatientID] =  (
--	  select top 1 PatientID from [RDS].T_PATIENT t2
--	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR)   
--	 )
--	from #XML_EXTR t1
--	where t1.[PatientID] is null and t1.DR is null
--	--step 3
--	update t1
--	set t1.[PatientID] =  (
--	  select top 1 PatientID from [RDS].T_PATIENT t2
--	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.PolisNumber = t1.NPOLIS)   
--	 )
--	from #XML_EXTR t1
--	where t1.[PatientID] is null and t1.DR is null
--	--step 4
--	update t1
--	set t1.[PatientID] =  (
--	  select top 1 PatientID from [RDS].T_PATIENT t2
--	  where (t2.LastName = t1.FAM and t2.Birthday = t1.DR and t2.PolisNumber = t1.NPOLIS)   
--	 )
--	from #XML_EXTR t1
--	where t1.[PatientID] is null and t1.DR is null

--	update x
--	set x.DR = p.Birthday
--	from #XML_EXTR x
--	join rds.T_PATIENT p on p.PatientID = x.PatientID
--	where x.DR is null

--	update x
--	set x.DR = '1753-01-01 00:00:00'
--	from #XML_EXTR x
--	where x.DR is null


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

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

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

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

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

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

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

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

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

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

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

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

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 13, 'Не заполнен обязательный атрибут NHISTORY'
--	from #XML_EXTR
--	where [NHISTORY] is null
	
--	insert into #ERROR ([OSHIB],[TEXT])
--	select 14, 'Не заполнен обязательный атрибут DS'
--	from #XML_EXTR
--	where [DS] is null
	
--	insert into #ERROR ([OSHIB],[TEXT])
--	select 15, 'Не заполнен обязательный атрибут DR'
--	from #XML_EXTR
--	where [DR] 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'
--			,[MPODR_STC]	AS 'header/LPU_TO'
--			,[ID]			AS 'header/ID'
--			--body
--			,[NOM_NAP]		as 'body/REFERRAL_NUMBER'
--			,FORMAT([DTA_NAP],'yyyy-MM-dd')		as 'body/REFERRAL_DATE'
--			--,[MCOD_NAP]		as 'body/REFERRAL_MO'--??
--			,[MCOD_STC]		as 'body/MO'
--			,isnull([MPODR_STC],1)	as 'body/BRANCH'
--			,[KOD_PFO]		as 'body/DIVISION'
--			,1				as 'body/FORM_MEDICAL_CARE'--EXTR
--			,FORMAT([DTA_FKT],'yyyy-MM-dd')		as 'body/HOSPITALISATION_DATE'
--			,FORMAT([DTA_FKT],'yyyy-MM-dd') + ' ' + REPLACE([TIM_FKT],'-',':')+':00'	as 'body/HOSPITALISATION_TIME'
--			,[VPOLIS]		as 'body/POLICY_TYPE'
--			,[SPOLIS]		as 'body/POLIS_SERIAL'
--			,[NPOLIS]		as 'body/POLIS_NUMBER'
--			,[SMO_CODE]		as 'body/SMO'
--			,[IM]			as 'body/FIRST_NAME'
--			,[FAM]			as 'body/LAST_NAME'
--			,[OT]			as 'body/FATHER_NAME'
--			,case when [W] = 1 then 10301 else 10302 end as 'body/SEX'
--			,FORMAT([DR],'yyyy-MM-dd')			as 'body/BIRTHDAY'
--			,[KOD_PFK]		as 'body/STRUCTURE_BED'
--			,[NHISTORY]		as 'body/MED_CARD_NUMBER'
--			,[DS]			as 'body/MKB'
--		from #XML_EXTR
--		FOR XML PATH (''), ROOT('HOSPITALISATION')
--		)
--		,null
--		end
		
--	drop table #XML_EXTR
--end

--if ((select count(*) from #XML_PLAN)>0)
--begin

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--	insert into #ERROR ([OSHIB],[TEXT])
--	select 22, 'Не заполнен обязательный атрибут DS'
--	from #XML_PLAN
--	where DS 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'
--		,[MPODR_STC]	AS 'header/LPU_TO'
--		,[ID]			AS 'header/ID'
--		--body
--		,[NOM_NAP]		as 'body/REFERRAL_NUMBER'
--		,FORMAT([DTA_NAP],'yyyy-MM-dd')		as 'body/REFERRAL_DATE'
--		,[MCOD_NAP]		as 'body/REFERRAL_MO'--??
--		,[MCOD_STC]		as 'body/MO'
--		,isnull([MPODR_STC],1)	as 'body/BRANCH'
--		,[KOD_PFO]		as 'body/DIVISION'
--		,case when [FRM_MP] = 1 then 0 else 1 end as 'body/FORM_MEDICAL_CARE'
--		,FORMAT([DTA_FKT],'yyyy-MM-dd')		as 'body/HOSPITALISATION_DATE'
--		,FORMAT([DTA_FKT],'yyyy-MM-dd') + ' ' + REPLACE([TIM_FKT],'-',':')+':00'	as 'body/HOSPITALISATION_TIME'
--		,[VPOLIS]		as 'body/POLICY_TYPE'
--		,[SPOLIS]		as 'body/POLIS_SERIAL'
--		,[NPOLIS]		as 'body/POLIS_NUMBER'
--		,[SMO_CODE]		as 'body/SMO'
--		,[IM]			as 'body/FIRST_NAME'
--		,[FAM]			as 'body/LAST_NAME'
--		,[OT]			as 'body/FATHER_NAME'
--		,case when [W] = 1 then 10301 else 10302 end as 'body/SEX'
--		,FORMAT([DR],'yyyy-MM-dd')			as 'body/BIRTHDAY'
--		,[KOD_PFK]		as 'body/STRUCTURE_BED'
--		,[NHISTORY]		as 'body/MED_CARD_NUMBER'
--		,[DS]			as 'body/MKB'
--	from #XML_PLAN
--	FOR XML PATH (''), ROOT('HOSPITALISATION')
--	)
--	,null
--	end
--	drop table #XML_PLAN
--end

--	drop table #ERROR
--	select * from @t1
--end