SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

---- =============================================
---- Author:		Marat Zigangarayev
---- Create date: 2009/10/14
---- Description:	Импорт информации о персоне
---- =============================================
--CREATE PROCEDURE [dbo].[sp_ImportPersonInfo] 
--	@BeginDate datetime -- дата начала
--	, @XmlDocument text	-- xml
--As
--Begin
--	SET NOCOUNT ON;

--	DECLARE @DocHandle int

--	EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

--	declare @t table
--	(
--		[pid] Varchar(128)
--		, [Number] int
--		, [Lpu] varchar(4)
--		, [Reg] bit
--		, [RD] varchar(10)
--		, [uid] varchar(10)
--		, [cn] varchar(10)
--		, [cl] varchar(2)
--		, [Patient] uniqueidentifier
--	)
	
--	-- выявляем пациентов-учащихся
--	Insert Into @t([pid],[Number],[Lpu],[Reg],[RD],[uid],[cn],[cl], [Patient])
--	Select 
--		[pd].[pid]
--		, ac.Number Number
--		, lpu.Code Lpu
--		, IsNull(r.[Status], 0) Reg
--		, Case When r.RegistrationDate Is Null Then Cast(Cast(Cast(ac.CreationDateTime As Float) As Int) As varchar(10)) Else Cast(Cast(r.RegistrationDate As float) As varchar(10)) End RD
--		, [pd].[uid]
--		, [pd].[cn]
--		, [pd].[cl]
--		, ac.[Patient]
--	From 
--		[AKUZ].T_AMBULANCE_CARD ac
--		Join [AKUZ].T_PATIENT p On ac.Patient = p.PatientID
--		Join [AKUZ].T_LPU_MAIN lpu On ac.CardLpu = lpu.LpuID
--		-- информация о прикреплении
--		Left Join 
--			(Select r.Patient, r.[Status], r.RegistrationDate, r.LpuMain
--			 From [AKUZ].T_REGISTRATION r
--			 Where r.RegistrationDate = 
--				(Select Top 1 rr.RegistrationDate 
--				 From [AKUZ].T_REGISTRATION rr 
--				 Where r.Patient = rr.Patient
--				 Order by rr.RegistrationDate Desc, rr.[Status])
--			) r On p.PatientID = r.Patient And ac.CardLpu = r.LpuMain
--		-- информация из xml
--		Join
--		(
--		Select 
--			[pid] [pid]
--			, NULLIF([ln],'') [ln]
--			, NULLIF([fn],'') [fn]
--			, NULLIF([mn],'') [mn]
--			, NULLIF([bd],'') [bd]
--			, NULLIF([uid],'') [uid]
--			, NULLIF([cn],'') [cn]
--			, NULLIF([cl],'') [cl]
--		From OPENXML (@DocHandle, '/root/ps/p',1)
--		  WITH ([pid]  varchar(128),
--				[ln]  varchar(128),
--				[fn] varchar(128),
--				[mn] varchar(128),
--				[bd] varchar(32),
--				[uid] varchar(10),
--				[cn] varchar(10),
--				[cl] varchar(2)
--				)
--		) [pd] On 
--			(p.LastName = [pd].ln 
--				And p.FirstName = [pd].fn 
--				And (p.FatherName = [pd].mn) 
--				And (
--					len([pd].[bd]) = 0 
--					OR p.Birthday = cast(cast([pd].[bd] As float) As datetime)
--					)
--			)

--	-- освобождаем документ xml
--	Exec sp_xml_removedocument @DocHandle

--	-- создаем, для тех, у кого нет такой инфы
--	Insert Into [AKUZ].[T_PATIENT_SCHOOL]([PatientSchoolID],[BeginDate],[EndDate],[Patient],[ClassNumber],[ClassLetter],[School])
--	Select NEWID(), @BeginDate, NULL, t.Patient, t.cn, t.cl, s.SchoolID
--	From @t t
--		Join [AKUZ].T_SCHOOL s On s.[UID] = t.[uid]
--	Where Not t.Patient in 
--		(
--			-- у кого уже есть точно такая же инф-я о персоне
--			Select distinct ps.Patient
--			From [AKUZ].T_PATIENT_SCHOOL ps
--				Join @t t2 On ps.Patient = t2.Patient
--				Join [AKUZ].T_SCHOOL s2 On ps.School = s2.SchoolID
--			Where ps.ClassNumber = t2.cn
--			 And ps.ClassLetter = t2.cl
--			 And s2.[UID] = t2.[uid]
--			 And ps.EndDate Is Null
--		)	

--	-- результат
--	Select [pid], Cast([Number] as varchar(10)) Num, [Lpu], cast([Reg] As varchar(1)) [Reg], [RD]
--	From @t
	
--End
--GO