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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

--на всякий 
  IF OBJECT_ID('dbo.T_F039_BANK_MO_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_F039_BANK_MO_BACKUP FROM ies.T_F039_BANK_MO
		end


  SELECT 
   T.c.value('./MCOD[1]', 'VARCHAR(6)') AS MCOD
  ,T.c.value('./UIDMO[1]', 'VARCHAR(17)') AS UIDMO
  ,T.c.value('./ACCOUNTTYPE[1]', 'int') AS ACCOUNTTYPE
  ,T.c.value('./ACCOUNTNUM[1]', 'VARCHAR(255)') AS ACCOUNTNUM
  ,T.c.value('./BANKBIK[1]', 'VARCHAR(255)') AS BANKBIK
  ,T.c.value('./CORRACCOUNT[1]', 'VARCHAR(255)') AS CORRACCOUNT
  ,T.c.value('./BANKNAME[1]', 'VARCHAR(1000)') AS BANKNAME
  ,T.c.value('./INN[1]', 'VARCHAR(12)') AS INN
  ,T.c.value('./KPP[1]', 'VARCHAR(9)') AS KPP
  ,T.c.value('./STA_CHARACTER[1]', 'VARCHAR(255)') AS STA_CHARACTER
  ,T.c.value('./TOFK_BIK[1]', 'VARCHAR(255)') AS TOFK_BIK
  ,T.c.value('./TOFK_CODE[1]', 'VARCHAR(255)') AS TOFK_CODE
  ,T.c.value('./TOFK_NAME[1]', 'VARCHAR(1000)') AS TOFK_NAME
  ,T.c.value('./PERSONAL_ACCOUNT[1]', 'VARCHAR(255)') AS PERSONAL_ACCOUNT
  ,T.c.value('./FUNCTION_PERSONAL_ACCOUNT[1]', 'VARCHAR(1000)') AS FUNCTION_PERSONAL_ACCOUNT
  ,T.c.value('./NAME_AGENCY[1]', 'VARCHAR(1000)') AS NAME_AGENCY
  ,T.c.value('./PERSONAL_ACCOUNT_INCOME_ADMIN[1]', 'VARCHAR(255)') AS PERSONAL_ACCOUNT_INCOME_ADMIN
  ,T.c.value('./INN_INCOME_ADMIN[1]', 'VARCHAR(12)') AS INN_INCOME_ADMIN
  ,T.c.value('./NAME_INCOME_ADMIN[1]', 'VARCHAR(1000)') AS NAME_INCOME_ADMIN
  ,T.c.value('./CODE_TOFK[1]', 'VARCHAR(50)') AS CODE_TOFK
  ,T.c.value('./KPP_INCOME_ADMIN[1]', 'VARCHAR(9)') AS KPP_INCOME_ADMIN
  ,T.c.value('./TREASURE_ACC_DEP_NAME[1]', 'VARCHAR(255)') AS TREASURE_ACC_DEP_NAME
  ,T.c.value('./TREASURE_ACC_DEP_INN[1]', 'VARCHAR(12)') AS TREASURE_ACC_DEP_INN
  ,T.c.value('./TREASURE_ACC_DEP_KPP[1]', 'VARCHAR(9)') AS TREASURE_ACC_DEP_KPP
  ,T.c.value('./DOG_NUM[1]', 'VARCHAR(500)') AS DOG_NUM 
  ,case when T.c.value('./DOG_DATE[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DOG_DATE[1]', 'varchar(20)'),103) end AS DOG_DATE
  ,T.c.value('./DOG_TERM[1]', 'VARCHAR(2)') AS DOG_TERM
  ,case when T.c.value('./DOG_TERM_DATE[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DOG_TERM_DATE[1]', 'varchar(20)'),103) end AS DOG_TERM_DATE
  ,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),103) end AS DATEBEG
  ,case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEEND[1]', 'varchar(20)'),103) end AS DATEEND
  into #temp_f039
  FROM   @Xml.nodes('/packet/zap') T(c)
  

  		-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update t1 
		set Actual = 0 
	from IES.T_F039_BANK_MO t1
	where not exists(select 1 from #temp_f039 t2 
					cross apply(select top 1 * from ies.T_F032_TRMO f032 where t2.UIDMO = f032.UIDMO and f032.Actual =1) f032
					cross apply(select top 1 * from ies.T_F003_MO f003 where t2.MCOD = f003.MCOD) f003
	where f032.F032TrmoID = t1.F032Trmo and   t1.DATEBEG = t2.DATEBEG and t2.DOG_NUM = t1.DOG_NUM )
	and t1.Actual = 1

			-- обновляем актуальность у записей которые есть в Фед. справочнике
	update t1 
		set Actual = 1 
	from IES.T_F039_BANK_MO t1
	where exists(select 1 from #temp_f039 t2 
					cross apply(select top 1 * from ies.T_F032_TRMO f032 where t2.UIDMO = f032.UIDMO and f032.Actual =1) f032
					cross apply(select top 1 * from ies.T_F003_MO f003 where t2.MCOD = f003.MCOD) f003
	where f032.F032TrmoID = t1.F032Trmo and   t1.DATEBEG = t2.DATEBEG and t2.DOG_NUM = t1.DOG_NUM)
	and t1.Actual = 0


	INSERT INTO [IES].[T_F039_BANK_MO]
				(F039BankMoID, F031Ermo, F003Mo, F032Trmo, ACCOUNTTYPE, ACCOUNTNUM, BANKBIK, CORRACCOUNT, BANKNAME, INN, KPP, STA_CHARACTER, TOFK_BIK, TOFK_CODE, TOFK_NAME
				, PERSONAL_ACCOUNT, FUNCTION_PERSONAL_ACCOUNT, NAME_AGENCY, PERSONAL_ACCOUNT_INCOME_ADMIN, INN_INCOME_ADMIN, NAME_INCOME_ADMIN, CODE_TOFK, KPP_INCOME_ADMIN
				, TREASURE_ACC_DEP_NAME, TREASURE_ACC_DEP_INN, TREASURE_ACC_DEP_KPP, DATEBEG, DATEEND ,DOG_NUM, DOG_DATE, DOG_TERM, DOG_TERM_DATE,Actual)
		select
				NEWID(), null, t1.MCOD, f032.F032TrmoID, t1.ACCOUNTTYPE, t1.ACCOUNTNUM, t1.BANKBIK, t1.CORRACCOUNT, t1.BANKNAME, t1.INN, t1.KPP, t1.STA_CHARACTER, t1.TOFK_BIK, t1.TOFK_CODE, t1.TOFK_NAME
				, t1.PERSONAL_ACCOUNT, t1.FUNCTION_PERSONAL_ACCOUNT, t1.NAME_AGENCY, case when t1.PERSONAL_ACCOUNT_INCOME_ADMIN = 'false' then 0 else 1 end, t1.INN_INCOME_ADMIN, t1.NAME_INCOME_ADMIN, t1.CODE_TOFK, t1.KPP_INCOME_ADMIN
				, t1.TREASURE_ACC_DEP_NAME, t1.TREASURE_ACC_DEP_INN, t1.TREASURE_ACC_DEP_KPP, t1.DATEBEG, t1.DATEEND, t1.DOG_NUM, t1.DOG_DATE, t1.DOG_TERM, t1.DOG_TERM_DATE,1
	FROM #temp_f039 t1
	cross apply(select top 1 * from ies.T_F032_TRMO f032 where t1.UIDMO = f032.UIDMO and f032.Actual =1) f032
	cross apply(select top 1 * from ies.T_F003_MO f003 where t1.MCOD = f003.MCOD) f003
	left join [IES].[T_F039_BANK_MO] t2 on t2.F032Trmo = f032.F032TrmoID  and t2.DATEBEG = t1.DATEBEG and t2.DOG_NUM = t1.DOG_NUM and t2.Actual = 1 
	where t2.F039BankMoID is null

drop table #temp_f039
 --select * from [IES].[T_F039_BANK_MO]
END
