/******   Script Date: 2022-03-16 18:07:45******/

if exists(select 1 from sys.sysobjects where name='ImportData')
drop procedure ImportData--如果存在则删除存储过程
go

create procedure ImportData
--返回重复列
--增删改影响行数
@TableName Varchar(max),
@TableNamesb Varchar(max),
@TableNamecg Varchar(max),
@TableNamels Varchar(max),
@xznum int output
as
declare @errornum int --保存SQL执行后错误值的变量
declare @SQLStr nvarchar(max)--失败数据
declare @SQLStr1 nvarchar(max)--成功数据
declare @MATERIALSQL nvarchar(max)--物料
declare @MATERIALSQL1 nvarchar(max)
declare @wlLSQL nvarchar(max)--物料_L
declare @wlLSQL1 nvarchar(max)
declare @MaterialBaseSQL nvarchar(max)--基本
declare @MaterialBaseSQL1 nvarchar(max)
declare @MaterialStockSQL nvarchar(max)--库存
declare @MaterialStockSQL1 nvarchar(max)
declare @deletesql nvarchar(max)
declare @deletesql1 nvarchar(max)
declare @deletesql2 nvarchar(max)
declare @MaterialSale nvarchar(max) 
declare @MaterialSale1 nvarchar(max) 
declare @MaterialPurchaseq nvarchar(max) 
declare @MaterialPurchaseq1 nvarchar(max) 
declare @MaterialPlan nvarchar(max) 
declare @MaterialPlan1 nvarchar(max) 
declare @MaterialProduce nvarchar(max)
declare @MaterialProduce1 nvarchar(max)
declare @MaterialSubcon nvarchar(max)
declare @MaterialSubcon1 nvarchar(max)
declare @MaterialInvPty nvarchar(max)
declare @MaterialInvPty1 nvarchar(max)
declare @MaterialInvPty2 nvarchar(max)
	set @errornum=0--初始值为0
	begin transaction --开始事务
begin
begin
	 --与数据库中的数据重复，文档内重复的数据，字段为空的数据
	 set @deletesql=N'IF Object_id('''+@TableNamesb+''') IS NOT NULL DROP TABLE  '+@TableNamesb
	 Execute sp_executesql @deletesql

 set @SQLStr=N'select * into '+@TableNamesb+' from(
select ''物料中已存在此货品代号'' as 失败原因 ,t1.* from '+@TableName+'  as t1 left join ( select distinct FNUMBER from T_BD_MATERIAL) as t2
 on t1.货品代号=t2.FNUMBER  where  t2.FNUMBER is not null 
 union all
 select  ''物料中已存在此名称规格'' as 失败原因,[创建人],[货品代号],[名称],[规格],[主单位],[大类],[中类],[预设仓库],[品牌],[12NC],[SKU],[CK编码], [接头数量],[接头型号]
,[线缆长度(米)],[材质],[长],[宽],[高],[拼板数],[开模图号],[每模穴数],[单模日产],[使用范围],[调价物料类型],[发料是否取整],[物料分组] from (
select t.*,t2.mcgg  from (select ('+@TableName+'.[名称]+'+@TableName+'.[规格]) as mcgg1 ,'+@TableName+'.*
from '+@TableName+'  ) as t left join 
(select (l.FNAME+t.F_QAZ_REMARK) as mcgg from T_BD_MATERIAL t 
left join T_BD_MATERIAL_L l on t.FMATERIALID=l.FMATERIALID where FLOCALEID=2052)
as t2 on t2.mcgg=t.mcgg1
)  ttt where mcgg is not null
 union all
  select ''物料中已存在此12NC'' as [失败原因] ,t1.*  from '+@TableName+' as t1 
 LEFT JOIN ( select distinct F_QAZ_TEXT2 from T_BD_MATERIAL) T5 on T1.[12NC]=T5.F_QAZ_TEXT2
 where  T5.F_QAZ_TEXT2 IS not NULL  
 union all
 select  ''文档中，12NC重复'' as [失败原因],'+@TableName+'.*  from '+@TableName+'
where [12NC] in (select  [12NC]  from  '+@TableName+'  group  by  [12NC]  having  count([12NC]) > 1)
union all
select  ''文档中，货品代号重复'' as [失败原因],'+@TableName+'.*  from '+@TableName+'
where [货品代号] in (select  [货品代号]  from  '+@TableName+'  group  by  [货品代号]  having  count([货品代号]) > 1)
 union all
 select  ''文档中，名称规格重复'' as [失败原因],'+@TableName+'.*  from '+@TableName+'
 where [名称]+[规格] in (select  [名称]+[规格]  from  '+@TableName+'  group  by  [名称]+[规格]  having  count([名称]+[规格]) > 1) 
  union all
	 select  ''预设仓库不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
	 [预设仓库] is null or [预设仓库]=''  '' 
	   union all
	  select  ''中类不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
	 [中类] is null or [中类]=''  ''
	union all
	 select  ''大类不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
  [大类] is null or [大类]=''  '' 
  union all
   select  ''主单位不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
  [主单位]  is  null  or  [主单位] =''  '' 
  union all
  select  ''创建人不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
 [创建人] is null or [创建人]=''  '' 
 union all
  select  ''物料分组不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
[物料分组] is  null or [物料分组]=''   ''
union all
 select  ''货品代号不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
 [货品代号] is null or [货品代号]=''  ''
 union all
 select  ''规格不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
 [规格] is null or [规格]=''  ''
  union all
 select  ''名称不能为空'' as [失败原因],'+@TableName+'.*  from '+@TableName+' where 
  [名称] is null or [名称]=''  ''
) as t1'

Execute sp_executesql @SQLStr

  -----------------------------------------------------
 -----------------------------------------------------
--如果存在临时表则删除   创建临时表，将不重复的数据添加进去
  	--临时表数据添加
	set @deletesql1=N'IF Object_id('''+@TableNamecg+''') IS NOT NULL DROP TABLE  '+@TableNamecg
	 Execute sp_executesql @deletesql1
set @SQLStr1=N'select * into '+@TableNamecg+' from(
  select t2.FNUMBER,T5.F_QAZ_TEXT2,t1.*,row_number() over(order by t1.货品代号 asc) as rownumber,t4.mcgg  from '+@TableName+' as t1 left join T_BD_MATERIAL as t2
 on t1.货品代号=t2.FNUMBER  
 left join T_BD_MATERIAL T5 ON T1.[12NC]=T5.F_QAZ_TEXT2
 left join (select t.[货品代号] ,t2.mcgg from (select ('+@TableName+'.[名称]+'+@TableName+'.[规格]) as mcgg1 ,'+@TableName+'.*
from '+@TableName+'  ) as t left join 
(select (l.FNAME+t.F_QAZ_REMARK) as mcgg from T_BD_MATERIAL t 
left join T_BD_MATERIAL_L l on t.FMATERIALID=l.FMATERIALID where FLOCALEID=2052)
as t2 on t2.mcgg=t.mcgg1)  as t4 on t4.[货品代号]=t1.[货品代号] ) as t1
 where FNUMBER is  null and F_QAZ_TEXT2 IS  NULL and mcgg is null and
 [预设仓库] is not null and [预设仓库]!=''   ''and [中类] is not null and [中类]!=''   ''
 and[大类] is not null and [大类]!=''   '' and [主单位] is not null and [主单位]!=''   '' and [规格] is not null and [规格]!=''   ''
 and[名称] is not null and [名称]!=''   '' and [创建人] is not null and [创建人]!=''   '' and t1.[货品代号] is not null and t1.[货品代号]!=''   '' 
 and [物料分组] is not null and [物料分组]!=''   ''
 and [名称]+[规格] in (select  [名称]+[规格]  from  '+@TableName+'  group  by  [名称]+[规格]  having  count([名称]+[规格]) = 1)
and exists (select [12NC] from   '+@TableName+' B 
 group  by  [12NC]  having  count([12NC]) = 1
 union all
select [12NC] from  '+@TableName+' B where
 [12NC] IS NULL  or [12NC] =''   '')
and t1.[货品代号] in (select  [货品代号]  from  '+@TableName+'  group  by  [货品代号]  having  count([货品代号]) = 1)'
	Execute sp_executesql @SQLStr1
	end
	begin
 -----------------------------------------------------
 -----------------------------------------------------
 --执行数据导入

	--生成ID，新增<物料>数据
 set @MATERIALSQL=N' insert into Z_BAS_ITEM (Column1) select rownumber from '+@TableNamecg
 Execute sp_executesql @MATERIALSQL
--select * from Z_BAS_ITEM
set @MATERIALSQL1=N'
insert into T_BD_MATERIAL(FMATERIALID,FMASTERID, FCREATEORGID,FUSEORGID,FCREATEDATE,FMODIFYDATE,F_KTEN_CHECKBOX,
FNUMBER,FCREATORID,FDOCUMENTSTATUS,FFORBIDSTATUS,F_QAZ_REMARK,
F_QAZ_TEXT,F_QAZ_INTEGER,F_QAZ_TEXT1,F_QAZ_INTEGER1,F_QAZ_COMBO,
F_QAZ_TEXT2,F_QAZ_TEXT3,F_QAZ_TEXT6,F_QAZ_COMBO2,F_QAZ_ASSISTANT,
F_QAZ_ASSISTANT1,F_QAZ_INTEGER2,F_QAZ_QTY,F_QAZ_INTEGER3,FMATERIALGROUP
) select T2.Id,T2.Id,1,1,GETDATE(),GETDATE(),case when [货品代号]  collate Chinese_PRC_CS_AI like ''ZJ%'' then ''1'' else ''0'' end as tc,
 货品代号,convert(int,创建人)as 创建人,''A'',''A'',规格,ISNULL([品牌], ''  '') as 品牌,ISNULL( [单模日产],0) as 单模日产,  ISNULL([开模图号], ''  '') as 开模图号,
  ISNULL( [每模穴数],0) as 每模穴数, ISNULL([使用范围], ''  '') as 使用范围,
  ISNULL([12NC], ''  '') as [12NC], ISNULL([SKU], ''  '') as SKU, ISNULL([CK编码], ''  '') as CK编码,ISNULL([调价物料类型], ''  '') as 调价物料类型,ISNULL([材质], ''  '') as 材质,
 ISNULL([接头型号], ''  '') as 接头型号,ISNULL( [接头数量],0) as 接头数量,ISNULL([线缆长度(米)], 0) as [线缆长度(米)],
 ISNULL( [拼板数],0) as 拼板数,convert(int,[物料分组])as [物料分组]
 from '+@TableNamecg+' left join Z_BAS_ITEM T2 on  '+@TableNamecg+'.rownumber=T2.Column1'
 Execute sp_executesql @MATERIALSQL1
 set @errornum=@errornum+@@ERROR--累加错误值
 -----------------------------------------------------
 -----------------------------------------------------

 --新增<T_BD_MATERIAL_L>数据
  set @wlLSQL=N'insert into z_BD_MATERIAL_L (Column1) select rownumber from '+@TableNamecg
  Execute sp_executesql @wlLSQL
--select * from z_BD_MATERIAL_L
  SET @wlLSQL1=N' insert into T_BD_MATERIAL_L (FPKID,FMATERIALID,FLOCALEID,FNAME) select T3.Id,t2.Id,''2052'',名称 from  '+@TableNamecg+'  
 left join Z_BAS_ITEM T2 on  '+@TableNamecg+'.rownumber=T2.Column1 
 left join z_BD_MATERIAL_L T3 on  '+@TableNamecg+'.rownumber=T3.Column1'
 Execute sp_executesql @wlLSQL1
 set @errornum=@errornum+@@ERROR--累加错误值
 -----------------------------------------------------
 -----------------------------------------------------
 --新增<基本>数据
 SET @MaterialBaseSQL=N'insert into Z_BD_MaterialBase (Column1) select rownumber from '+@TableNamecg
 Execute sp_executesql @MaterialBaseSQL
--select * from Z_BD_MaterialBase
 SET @MaterialBaseSQL1=N'insert into t_BD_MaterialBase (FENTRYID,FMATERIALID,FLENGTH,FWIDTH,FHEIGHT,FCATEGORYID,FERPCLSID,FBASEUNITID,
 FISPURCHASE,FISINVENTORY,FISSALE,FFEATUREITEM,FTAXRATEID,
FSUITE,FTAXTYPE,FWEIGHTUNITID,FVOLUMEUNITID) 
 select T3.Id,t2.Id,
 ISNULL([长],0) as 长,
ISNULL([宽],0) as 宽,
ISNULL([高],0) as 高,中类,大类,主单位,1,1,1,1,234,0,''63489f1e86944c989c58d97893a2aec2'',10095,10087 from  '+@TableNamecg+'
 left join Z_BAS_ITEM T2 on '+@TableNamecg+'.rownumber=T2.Column1 
 left join Z_BD_MaterialBase T3 on '+@TableNamecg+'.rownumber=T3.Column1'
 Execute sp_executesql @MaterialBaseSQL1
 set @errornum=@errornum+@@ERROR--累加错误值
  -----------------------------------------------------
 -----------------------------------------------------
 --新增<库存>数据
    SET @MaterialStockSQL=N'insert into z_BD_MaterialStock (Column1) select rownumber from '+@TableNamecg
	Execute sp_executesql @MaterialStockSQL
--select * from z_BD_MaterialStock
SET @MaterialStockSQL1=N' insert into t_BD_MaterialStock(FENTRYID,FMATERIALID,FSTOCKID,
FSTOREUNITID,FUNITCONVERTDIR,FCURRENCYID,FISLOCKSTOCK
)select  T3.Id,t2.Id,预设仓库,10101,1,1,1  from '+@TableNamecg+'
  left join Z_BAS_ITEM T2 on '+@TableNamecg+'.rownumber=T2.Column1 
 left join z_BD_MaterialStock T3 on '+@TableNamecg+'.rownumber=T3.Column1'
 Execute sp_executesql @MaterialStockSQL1
 set @errornum=@errornum+@@ERROR--累加错误值
  -----------------------------------------------------
 -----------------------------------------------------


 --新增<销售>t_BD_MaterialSale
set @MaterialSale=N'insert into z_BD_MaterialSale(Column1) select rownumber from '+@TableNamecg
 Execute sp_executesql @MaterialSale

set @MaterialSale1=N'insert into t_BD_MaterialSale(FENTRYID,FMATERIALID,
FSALEUNITID,FSALEPRICEUNITID,FOUTLMTUNIT,FISRETURN,FISAFTERSALE,FISPRODUCTFILES ,FWARRANTYUNITID
) select  T3.Id,t2.Id,10101,10101,''SAL'',1,1,1,''D'' from '+@TableNamecg+'
 left join Z_BAS_ITEM T2 on  '+@TableNamecg+'.rownumber=T2.Column1 
left join z_BD_MaterialSale T3 on '+@TableNamecg+'.rownumber=T3.Column1  '
 Execute sp_executesql @MaterialSale1
  set @errornum=@errornum+@@ERROR

 --新增<采购>t_bd_MaterialPurchase
 set @MaterialPurchaseq=N'insert into z_bd_MaterialPurchase(Column1) select rownumber from '+@TableNamecg
  Execute sp_executesql @MaterialPurchaseq

set @MaterialPurchaseq1=N'insert into t_bd_MaterialPurchase(FENTRYID,FMATERIALID,
FPURCHASEUNITID,FPURCHASEPRICEUNITID,FPURCHASEORGID,FISRETURNMATERIAL ,FQUOTATYPE,FPRINTCOUNT,FPOBILLTYPEID
) select  T3.Id,t2.Id,10101,10101,1,1,1,1,''93591469feb54ca2b08eb635f8b79de3'' from '+@TableNamecg+'
 left join Z_BAS_ITEM T2 on  '+@TableNamecg+'.rownumber=T2.Column1 
left join z_bd_MaterialPurchase T3 on '+@TableNamecg+'.rownumber=T3.Column1  '
 Execute sp_executesql @MaterialPurchaseq1
  set @errornum=@errornum+@@ERROR

 --新增<计划>t_BD_MaterialPlan
 set @MaterialPlan=N'insert into z_BD_MaterialPlan(Column1) select rownumber from '+@TableNamecg
  Execute sp_executesql @MaterialPlan

set @MaterialPlan1=N'insert into t_BD_MaterialPlan(FENTRYID,FMATERIALID,FPLANOFFSETTIMETYPE,
FPLANNINGSTRATEGY,FMFGPOLICYID,FORDERPOLICY,FORDERINTERVALTIMETYPE,FWRITEOFFQTY,FISMRPCOMBILL,FEOQ
) select  T3.Id,t2.Id,1,1,''40042'',0,3,''1.0000000000'',1,1 from '+@TableNamecg+'
 left join Z_BAS_ITEM T2 on  '+@TableNamecg+'.rownumber=T2.Column1 
left join z_BD_MaterialPlan T3 on '+@TableNamecg+'.rownumber=T3.Column1 '
 Execute sp_executesql @MaterialPlan1
  set @errornum=@errornum+@@ERROR

 --新增<生产>数据
    SET @MaterialProduce=N'insert into z_BD_MaterialProduce (Column1) select rownumber from '+@TableNamecg
	Execute sp_executesql @MaterialProduce
--select * from z_BD_MaterialStock
SET @MaterialProduce1=N' insert into t_BD_MaterialProduce(FENTRYID,FMATERIALID,FMINISSUEUNITID,FISMAINPRD,
FPRODUCEUNITID,FPRODUCEBILLTYPE,FORGTRUSTBILLTYPE,FBACKFLUSHTYPE,FBOMUNITID,
FISSUETYPE,FOVERCONTROLMODE,FMINISSUEQTY
)select  T3.Id,t2.Id,10101,case [大类] when 2 then 1 when 3 then 1 else 0 end as zcp,10101,''281783bb73244d178d41ce550d877af0'',
''00505694499eb16411e3cd16dc62e287'' ,1, 10101,1,1,''1.0000000000'' from '+@TableNamecg+'
  left join Z_BAS_ITEM T2 on '+@TableNamecg+'.rownumber=T2.Column1 
 left join z_BD_MaterialProduce T3 on '+@TableNamecg+'.rownumber=T3.Column1'
 Execute sp_executesql @MaterialProduce1
 set @errornum=@errornum+@@ERROR--累加错误值

 --委外z_bd_MaterialSubcon
 set @MaterialSubcon=N'insert into z_bd_MaterialSubcon (Column1) select rownumber from '+@TableNamecg
 Execute sp_executesql @MaterialSubcon
 set @MaterialSubcon1=N'insert into t_bd_MaterialSubcon(FENTRYID,FMATERIALID,FSUBCONUNITID,FSUBCONPRICEUNITID,FSUBBILLTYPE
)select  T3.Id,t2.Id,10101,10101,''93904bf745d84ae0ad08da30949754e0'' from '+@TableNamecg+'
  left join Z_BAS_ITEM T2 on '+@TableNamecg+'.rownumber=T2.Column1 
 left join z_bd_MaterialSubcon T3 on '+@TableNamecg+'.rownumber=T3.Column1'
 Execute sp_executesql @MaterialSubcon1

 --库存属性z_BD_MaterialInvPty
 --创建临时表，添加数据，以及默认数据
 set @deletesql2=N'IF Object_id('''+@TableNamels+''') IS NOT NULL DROP TABLE  '+@TableNamels
	 Execute sp_executesql @deletesql2
 set @MaterialInvPty=N'select * into '+@TableNamels+' from(
select *,row_number() over(order by 货品代号 asc) as rownumber1  from(
select 货品代号,1 as num ,10001 as tyid,1 as able ,0 as rice ,0 as ctplan ,0 as cost ,1 rgid,rownumber as pnum from '+@TableNamecg+' 
union all
select 货品代号,2 as num,10002 as tyid,1 as able ,0 as rice ,0 as ctplan ,0 as cost ,1 rgid,rownumber as pnum from '+@TableNamecg+'
union all
select 货品代号,3 as num ,10003 as tyid,0 as able ,0 as rice ,0 as ctplan ,0 as cost ,1 rgid,rownumber as pnum from '+@TableNamecg+' 
union all
select 货品代号,4 as num,10004 as tyid,0 as able ,0 as rice ,0 as ctplan ,0 as cost ,1 rgid,rownumber as pnum from '+@TableNamecg+' 
union all
select 货品代号,5 as num,10006 as tyid,0 as able ,0 as rice ,0 as ctplan ,0 as cost ,1 rgid,rownumber as pnum from '+@TableNamecg+' ) as t0
) as t1 '
 Execute sp_executesql @MaterialInvPty
 --生成ID
 set @MaterialInvPty1=N'insert into z_BD_MaterialInvPty (Column1) select rownumber1 from '+@TableNamels
  Execute sp_executesql @MaterialInvPty1
  --新增数据
  set @MaterialInvPty2=N'insert into t_BD_MaterialInvPty(FENTRYID,FMATERIALID,FINVPTYID,FISENABLE,FISAFFECTPRICE,FISAFFECTPLAN,FISAFFECTCOST,FUSEORGID) 
select t2.Id,t3.Id,t1.tyid,t1.able,t1.rice,t1.ctplan,t1.cost,t1.rgid from '+@TableNamels+' as t1 
left join z_BD_MaterialInvPty t2 on t1.rownumber1=t2.Column1 left join Z_BAS_ITEM t3
on t3.Column1=t1.pnum'
 Execute sp_executesql @MaterialInvPty2
    --删除生成ID的数据表
 delete from Z_BAS_ITEM  
 delete from z_BD_MATERIAL_L 
 delete from Z_BD_MaterialBase 
 delete from z_BD_MaterialStock 
 delete from z_bd_MaterialPurchase  
 delete from z_BD_MaterialPlan  
 delete from z_BD_MaterialProduce  
 delete from z_BD_MaterialSale  
 delete from z_bd_MaterialSubcon 
 delete from z_BD_MaterialInvPty

 set @errornum=@errornum+@@ERROR--累加错误值

 --判断过程是否有错误
	if(@errornum=0)--没有错误
		begin
			commit transaction--提交事务
			--set @xznum='成功';
			--print'成功'
			
		end
	else--发生错误
		begin
			rollback transaction--回滚事务
			--set @xznum='失败';
			--print'失败'
		end
 if @@Error<>0
set @xznum = -1
else
set @xznum = 1
	end
end


