SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ImportPersonLpuInfo]') and xtype = N'P')
drop procedure [dbo].[sp_ImportPersonLpuInfo]
GO

---- =============================================
---- Author:		Marat Zigangarayev
---- Create date: 2009/10/15
---- Description:	Импорт информации о привязке персоны к ЛПУ
---- =============================================
--CREATE PROCEDURE [dbo].[sp_ImportPersonLpuInfo] 
--	@XmlDocument text	-- xml
--As
--Begin
--	SET NOCOUNT ON;

--	DECLARE @DocHandle int

--	EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

--	declare @t table
--	(
--		[pid] varchar(128)
--		,[num] varchar(12)
--		,[lpu] varchar(4)
--		,[reg] varchar(1)
--		,[rd] datetime
--		,PersonLinkID uniqueidentifier
--	)
	
--	-- те, кого нашли в удаленном ЛПУ
--	Insert Into @t([pid],[num],[lpu],[reg],[rd],PersonLinkID)
--	Select
--		[pd].[pid]
--		, [pd].[num] 
--		, [pd].[lpu] 
--		, [pd].[reg]
--		, Case When [pd].[rd] = '' Then NULL Else cast(cast([pd].[rd] As float) As datetime)  End
--		,newid()
--	From 
--		-- информация из xml
--		--Join
--		(
--		Select 
--			[pid] [pid]
--			, [num] [num]
--			, [lpu] [lpu]
--			, [reg] [reg]
--			, [rd] [rd]
--		From OPENXML (@DocHandle, '/root/ps/p',1)
--		  WITH ([pid]  varchar(128),
--				[num]  varchar(12),
--				[lpu] varchar(4),
--				[reg] varchar(1),
--				[rd] varchar(10)
--				)
--		) [pd]

--	-- освобождаем документ xml
--	Exec sp_xml_removedocument @DocHandle
	
--	-- собираем тех, по кому нет инфы
--	declare @pl table 
--	(
--		PersonLinkID uniqueidentifier
--		,[BeginDate] datetime
--		,[Person] uniqueidentifier
--		,[num] varchar(12)
--		,[lpu] varchar(4)
--		,[reg] varchar(1)
--	)
--	Insert Into @pl (PersonLinkID,[BeginDate],[Person], [num], [lpu], [reg])
--	Select NEWID(), t.rd, cast(t.pid As uniqueidentifier), t.num, t.lpu, t.reg
--	From @t t
--		left Join [AKUZ].T_PERSON_LINK pl On pl.Person = t.pid
--	Where Not t.[pid] in 
--		(Select Person 
--		 From [AKUZ].T_PERSON_LINK pl
--			Join [AKUZ].T_PERSON_LPU plpu on pl.PersonLinkID = plpu.PersonLinkID
--		 Where pl.EndDate Is Null 
--		  And plpu.CardNumber = t.num
--		  And plpu.Registration = t.reg)

--	-- добавляем в связку
--	Insert Into [AKUZ].[T_PERSON_LINK]([PersonLinkID],[BeginDate],[EndDate],[Person],[type_])
--	Select pl.PersonLinkID, pl.BeginDate, NULL, pl.Person, 803
--	From @pl pl
--		Join [AKUZ].T_BRANCH lm On lm.BranchCode = pl.lpu
--	-- добавляем в наследуемую
--	Insert Into [AKUZ].[T_PERSON_LPU]([CardNumber],[LpuMain],[Registration],[PersonLinkID])
--	Select pl.num, lm.LpuID, pl.reg, pl.PersonLinkID
--	From @pl pl
--		Join [AKUZ].T_LPU_MAIN lm On lm.Code = pl.lpu

--End
--GO