if exists ( select * from sys.procedures p where p.name = 'sp_F042TREKMPLoad' ) DROP PROCEDURE [dbo].[sp_F042TREKMPLoad]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_F042TREKMPLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

	select 
		 T.c.value('./UIDEKMP[1]', 'varchar(11)') AS UIDEKMP
		,T.c.value('./IDEKMP[1]', 'varchar(8)') AS IDEKMP
		,T.c.value('./N_EXPERT[1]', 'varchar(7)') AS N_EXPERT
		,T.c.value('./OKTMO_P[1]', 'varchar(11)') AS OKTMO_P
		,T.c.value('./SUBJ[1]', 'varchar(150)') AS SUBJ
		,case when T.c.value('./D_BEGIN[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./D_BEGIN[1]', 'varchar(20)'),104) end AS D_BEGIN
		,case when T.c.value('./D_END[1]', 'varchar(20)')='' then null else CONVERT(date,T.c.value('./D_END[1]', 'varchar(20)'),104) end AS D_END
		,T.c.value('./NAME_E[1]', 'varchar(2)') AS NAME_E
		,T.c.value('./EKS[1]', 'int') AS EKS
		,T.c.value('./ZSL[1]', 'int') AS ZSL
		,T.c.value('./KMO[1]', 'int') AS KMO
		,T.c.value('./ZAK[1]', 'int') AS ZAK
		,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),104) end AS DATEBEG
		,case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(date,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	into #F042
	from @xml.nodes('/packet/zap') T(c)
	
	update #F042
	set D_END = null
	where D_END < '19000101'
	
	update #F042
	set DATEEND = null
	where DATEEND < '19000101'

	update t2 
	set  t2.SUBJ=t1.SUBJ
		,t2.NAME_E=t1.NAME_E
		,t2.DATEEND=t1.DATEEND
		,t2.D_END=t1.D_END
		,t2.EKS = t1.EKS
		,t2.ZSL = t1.ZSL
		,t2.KMO = t1.KMO
		,t2.ZAK = t1.ZAK
	from #F042 t1
	join [IES].T_F042_TREKMP t2 on (t1.UIDEKMP = t2.UIDEKMP and t1.DATEBEG = t2.DATEBEG)

	insert into [IES].T_F042_TREKMP(F042TrekmpID, UIDEKMP, IDEKMP, N_EXPERT, OKTMO_P, SUBJ, D_BEGIN, D_END, NAME_E, EKS, ZSL, KMO, ZAK, DATEBEG, DATEEND)
	select newid(), t1.UIDEKMP, t1.IDEKMP, t1.N_EXPERT, t1.OKTMO_P, t1.SUBJ, t1.D_BEGIN, t1.D_END, t1.NAME_E, t1.EKS, t1.ZSL, t1.KMO, t1.ZAK, t1.DATEBEG, t1.DATEEND
	from #F042 t1
	left join [IES].T_F042_TREKMP t2 on (t1.UIDEKMP = t2.UIDEKMP and t1.DATEBEG = t2.DATEBEG)
	where t2.F042TrekmpID is null

	drop table #F042

END

