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

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

	select 
		 T.c.value('./KOD_TF[1]', 'varchar(2)') AS KOD_TF
		,T.c.value('./KOD_OKATO[1]', 'varchar(5)') AS KOD_OKATO
		,T.c.value('./OKRUG[1]', 'int') AS OKRUG
		,T.c.value('./SUBNAME[1]', 'varchar(254)') AS SUBNAME
		,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(datetime,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	into #F010
	from @xml.nodes('/packet/zap') T(c)

	update t2 set 
		 t2.SUBNAME=t1.SUBNAME
		,t2.DATEEND=t1.DATEEND
	from #F010 t1
	join [IES].[T_F010_SUBJECT] t2 on (t1.KOD_TF=t2.KOD_TF and t1.DATEBEG = t2.DATEBEG)

	declare @row_count int
	declare @start_id int

	select @row_count = count(1)
	from #F010 t1
	left join [IES].[T_F010_SUBJECT] t2 on (t1.KOD_TF=t2.KOD_TF and t1.DATEBEG = t2.DATEBEG)
	where t2.SubjectID is null

	exec @start_id = dbo.sp_GenerateIdForEntity @EntName = 'IES.F010_SUBJECT', @FieldName = 'PrimaryKey', @count = @row_count

	insert into [IES].[T_F010_SUBJECT](SubjectID, KOD_TF, KOD_OKATO, SUBNAME, OKRUG, DATEBEG, DATEEND)
	select @start_id + ROW_NUMBER() OVER (ORDER BY t1.KOD_TF) - 1, t1.KOD_TF, t1.KOD_OKATO, t1.SUBNAME, t1.OKRUG, t1.DATEBEG, t1.DATEEND
	from #F010 t1
	left join [IES].[T_F010_SUBJECT] t2 on (t1.KOD_TF=t2.KOD_TF and t1.DATEBEG = t2.DATEBEG)
	where t2.SubjectID is null

	drop table #F010

END


