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


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

declare @mindate datetime
declare @nextid int
set @mindate = CONVERT(datetime, '19000101')

select [TF_OKATO]
      ,[SMOCOD]
      ,[NAM_SMOP]
      ,[NAM_SMOK]
      ,[INN]
      ,[OGRN]
      ,[KPP]
      ,[INDEX_J]
      ,[ADDR_J]
      ,tt1.O005OkopfID as [OKOPF]
      ,[ORG]
      ,[FAM_RUK]
      ,[IM_RUK]
      ,[OT_RUK]
      ,[PHONE]
      ,[FAX]
      ,[E_MAIL]
      ,[N_DOC]
      ,[D_START]
      ,case when[DATA_E] IS null then @mindate else [DATA_E] end as [DATA_E]
      ,[WWW]
      ,case when[D_BEGIN] IS null then @mindate else [D_BEGIN] end as [D_BEGIN]
      ,[D_END]
      ,[NAME_E]
      ,[DUVED]
      ,case when[D_EDIT] IS null then @mindate else [D_EDIT] end as [D_EDIT]
      ,[INDEX_F]
      ,[ADDR_F]
      ,[NAL_P]
      ,[KOL_ZL]
       into #temp_f002
 from 
 (
    select tt.* from
    (
		SELECT 
		T.c.value('./TF_OKATO[1]', 'varchar(5)') AS TF_OKATO
		,T.c.value('./smocod[1]', 'varchar(6)') AS SMOCOD
		,T.c.value('./nam_smop[1]', 'varchar(250)') AS NAM_SMOP
		,T.c.value('./nam_smok[1]', 'varchar(250)') AS NAM_SMOK
		,T.c.value('./INN[1]', 'varchar(12)') AS INN
		,T.c.value('./Ogrn[1]', 'varchar(15)') AS OGRN
		,T.c.value('./KPP[1]', 'varchar(9)') AS KPP
		,T.c.value('./jurAddress[1]/INDEX_J[1]', 'varchar(6)') AS INDEX_J
		,T.c.value('./jurAddress[1]/ADDR_J[1]', 'varchar(254)') AS ADDR_J
		,T.c.value('./OKOPF[1]', 'varchar(10)') AS OKOPF		
		,T.c.value('./ORG[1]', 'int') AS ORG
		,T.c.value('./fam_ruk[1]', 'varchar(40)') AS FAM_RUK
		,T.c.value('./im_ruk[1]', 'varchar(40)') AS IM_RUK
		,T.c.value('./ot_ruk[1]', 'varchar(40)') AS OT_RUK
		,T.c.value('./Phone[1]', 'varchar(40)') AS PHONE
		,T.c.value('./Fax[1]', 'varchar(40)') AS FAX
		,T.c.value('./e_mail[1]', 'varchar(50)') AS E_MAIL
		,T.c.value('./licenziy[1]/N_DOC[1]', 'varchar(20)') AS N_DOC
		,CONVERT(datetime,T.c.value('./licenziy[1]/D_START[1]', 'varchar(30)'),103) AS D_START
		,case when LEN( T.c.value('./licenziy[1]/DATE_E[1]', 'varchar(30)')) < 10  then null else CONVERT(datetime,T.c.value('./licenziy[1]/DATE_E[1]', 'varchar(30)'),103) end AS DATA_E
		,T.c.value('./WWW[1]', 'varchar(100)') as WWW
		,case when LEN( T.c.value('./insInclude[1]/d_begin[1]', 'varchar(30)')) < 10  then null else CONVERT(datetime,T.c.value('./insInclude[1]/d_begin[1]', 'varchar(30)'),103) end AS D_BEGIN
		,case when LEN( T.c.value('./insInclude[1]/d_end[1]', 'varchar(30)')) < 10  then null else CONVERT(datetime,T.c.value('./insInclude[1]/d_end[1]', 'varchar(30)'),103) end AS D_END
		,T.c.value('./insInclude[1]/NAME_E[1]', 'varchar(10)') as NAME_E
		,T.c.value('./insInclude[1]/NAL_P[1]', 'varchar(1)') AS NAL_P
		,case when LEN( T.c.value('./D_EDIT[1]', 'varchar(30)')) < 10  then null else CONVERT(datetime,T.c.value('./D_EDIT[1]', 'varchar(30)'),103) end AS D_EDIT
		,T.c.value('./pstAddress[1]/INDEX_F[1]', 'varchar(6)') AS INDEX_F
		,T.c.value('./pstAddress[1]/addr_f[1]', 'varchar(254)') AS ADDR_F
		,case when T.c.value('./insAdvice[1]/KOL_ZL[1]','int') is null then 0 else T.c.value('./insAdvice[1]/KOL_ZL[1]','int') end AS KOL_ZL
		,case when LEN( T.c.value('./insAdvice[1]/DUVED[1]', 'varchar(30)')) < 10  then null else CONVERT(datetime,T.c.value('./insAdvice[1]/DUVED[1]', 'varchar(30)'),103) end as [DUVED]
		FROM   @Xml.nodes('/PACKET/insCompany') T(c)
	) tt
	) tt2
	left join [IES].T_O005_OKOPF tt1 on tt1.KOD = tt2.OKOPF


	SELECT
		T.c.value('../smocod[1]','varchar(6)') as [SMOCOD]
		,T.c.value('./N_DOC[1]','varchar(32)') as [N_DOC]
		,case when len(T.c.value('./D_START[1]','varchar(30)')) < 10 then null else convert(datetime,T.c.value('./D_START[1]','varchar(30)'),103) end AS [D_START]
		,case when len(T.c.value('./DATE_E[1]','varchar(30)')) < 10 then null else convert(datetime,T.c.value('./DATE_E[1]','varchar(30)'),103) end AS [data_e]
		,case when len(T.c.value('./D_TERM[1]','varchar(30)')) < 10 then null else convert(datetime,T.c.value('./D_TERM[1]','varchar(30)'),103) end AS [d_term]
		into #SMO_License
	from @Xml.nodes('/PACKET/insCompany/licenziy') T(c)
	
	
UPDATE t1 SET t1.TF_OKATO = t2.TF_OKATO
, t1.[NAM_SMOP] = t2.[NAM_SMOP], t1.[NAM_SMOK] = t2.[NAM_SMOK], t1.INN = t2.INN
, t1.[OGRN] = t2.[OGRN], t1.KPP = t2.KPP, t1.[INDEX_J] = t2.[INDEX_J], t1.[ADDR_J] = t2.[ADDR_J]
, t1.[OKOPF] = t2.[OKOPF]
,t1.[ORG] = t2.[ORG]
      ,t1.[FAM_RUK] =t2.[FAM_RUK] 
      ,t1.[IM_RUK] = t2.[IM_RUK]
      ,t1.[OT_RUK] = t2.[OT_RUK]
      ,t1.[PHONE] =t2.[PHONE]
      ,t1.[FAX] = t2.[FAX]
      ,t1.[E_MAIL] = t2.[E_MAIL]
      ,t1.[N_DOC] = t2.[N_DOC]
      ,t1.[D_START] = t2.[D_START]
      ,t1.[DATA_E] = t2.[DATA_E]
      ,t1.[WWW] = t2.[WWW]
      ,t1.[D_BEGIN] = t2.[D_BEGIN]
      ,t1.[D_END] = t2.[D_END]
      ,t1.[NAME_E] =t2.[NAME_E]
      ,t1.[DUVED] =t2.[DUVED]
      ,t1.[D_EDIT] =t2.[D_EDIT]
      ,t1.[INDEX_F] = t2.[INDEX_F]
      ,t1.[ADDR_F] = isnull(t2.[ADDR_F], '')
      ,t1.[NAL_P] = t2.[NAL_P]
      ,t1.[KOL_ZL] = isnull(t2.[KOL_ZL], 0)
FROM [IES].[T_F002_SMO] t1
join #temp_f002 t2 on t2.SMOCOD = t1.SMOCOD

INSERT INTO  [IES].[T_F002_SMO]
           (
       [TF_OKATO]
      ,[SMOCOD]
      ,[NAM_SMOP]
      ,[NAM_SMOK]
      ,[INN]
      ,[OGRN]
      ,[KPP]
      ,[INDEX_J]
      ,[ADDR_J]
      ,[OKOPF]
      ,[ORG]
      ,[FAM_RUK]
      ,[IM_RUK]
      ,[OT_RUK]
      ,[PHONE]
      ,[FAX]
      ,[E_MAIL]
      ,[N_DOC]
      ,[D_START]
      ,[DATA_E]
      ,[WWW]
      ,[D_BEGIN]
      ,[D_END]
      ,[NAME_E]
      ,[DUVED]
      ,[D_EDIT]
      ,[INDEX_F]
      ,[ADDR_F]
      ,[NAL_P]
      ,[KOL_ZL]
	  ,IsActive)
select 
       [TF_OKATO]
      ,[SMOCOD]
      ,[NAM_SMOP]
      ,[NAM_SMOK]
      ,[INN]
      ,[OGRN]
      ,[KPP]
      ,[INDEX_J]
      ,[ADDR_J]
      ,[OKOPF]
      ,[ORG]
      ,[FAM_RUK]
      ,[IM_RUK]
      ,[OT_RUK]
      ,[PHONE]
      ,[FAX]
      ,[E_MAIL]
      ,[N_DOC]
      ,[D_START]
      ,[DATA_E]
      ,[WWW]
      ,[D_BEGIN]
      ,[D_END]
      ,[NAME_E]
      ,[DUVED]
      ,[D_EDIT]
      ,[INDEX_F]
      ,isnull([ADDR_F], '')
      ,[NAL_P]
      ,[KOL_ZL]
	  ,0 
	  from  #temp_f002 f002 where not exists(select * from [IES].T_F002_SMO smo where smo.SMOCOD = f002.SMOCOD)
	  and N_DOC is not null and D_START is not null

      UPDATE t1 SET t1.D_END = CONVERT (date, GETDATE())
      FROM [IES].[T_F002_SMO] t1
      left join #temp_f002 t2 on t2.SMOCOD = t1.SMOCOD
      where t2.SMOCOD is null and t1.D_END is null 

	  update t1
	  set t1.date_e=t0.data_e
		  ,t1.d_start = t0.D_START
	  from #SMO_License t0
	  join [IES].T_SMO_LICENSE t1 on (t1.F002Smo=t0.SMOCOD and t1.n_doc=t0.N_DOC)

	  insert into [IES].T_SMO_LICENSE(SmoLicenseID,F002Smo,n_doc,d_start,date_e)
	  select newid(),t3.SMOCOD,t1.N_DOC,t1.D_START,t1.data_e
	  from #smo_license t1
	  left join [IES].T_SMO_LICENSE t2 on (t2.F002SMO = t1.SMOCOD and t2.n_doc=t1.N_DOC)
	  join IES.T_F002_SMO t3 on (t1.SMOCOD = t3.SMOCOD)
	  where t2.F002Smo is null and t1.N_DOC is not null and t1.D_START is not null

drop table #smo_license
drop table #temp_f002

END
