金蝶K3--采购单价---导入

use aiserp2019
go
if exists(select 1 from tempdb..sysobjects where name like '#poorder_price_insert%')
begin	--先删除已经存在的缓存表
	drop table #poorder_price_insert
end
go
select * into #poorder_price_insert from hrhost.dbo.poorder_price
alter table #poorder_price_insert add fsupid int,fitemid int,funitid int,fptype int,fuserid int,id int identity(1000,1) not null,im_result varchar(100)
go

declare @fsupid int,@fitemid int,@funitid int,@fptype int,@fuserid int,@fentryid int,@fprice decimal(18,10),@fprint_info varchar(max)
if exists(select 1 from icmaxnum where ftablename='t_supply')
begin
	select @fentryid=fmaxnum+1 from icmaxnum where ftablename='t_supply'
end
else
begin
	set @fentryid=1001
	insert into icmaxnum (ftablename,fmaxnum)values('t_supply',@fentryid)
end
declare @供应商名称 varchar(100),@物料代码 varchar(100),@计量单位 varchar(20),@单价类型 varchar(20),@最新维护人 varchar(50),@id int,@备注 varchar(max)
declare my_select cursor for select 供应商名称,物料代码,计量单位,单价类型,最新维护人,id,报价,备注 from #poorder_price_insert
open my_select
fetch next from my_select into @供应商名称,@物料代码,@计量单位,@单价类型,@最新维护人,@id,@fprice,@备注
while @@fetch_status=0
begin
	set @fprint_info = null
	select @fsupid=fitemid from t_supplier where fname like '%'+ltrim(rtrim(@供应商名称))+'%'
	if isnull(@fsupid,0)=0 begin set @fprint_info='供应商名称'+ltrim(rtrim(@供应商名称))+'找不到对应记录' end
	select @fitemid=fitemid from t_icitem where fnumber like '%'+ltrim(rtrim(@物料代码))+'%'
	if isnull(@fitemid,0)=0 begin set @fprint_info=case when len(@fprint_info)>0 then '|' else '' end+'物料代码'+ltrim(rtrim(@物料代码))+'找不到对应记录' end
	select @funitid=fmeasureunitid from t_measureunit where fname like '%'+ltrim(rtrim(@计量单位))+'%'
	if isnull(@funitid,0)=0 begin set @fprint_info=case when len(@fprint_info)>0 then '|' else '' end+'计量单位'+ltrim(rtrim(@计量单位))+'找到不对应记录' end
	select @fuserid=fuserid from t_user where fname=ltrim(rtrim(@最新维护人))
	if isnull(@fuserid,0)=0 begin set @fprint_info=case when len(@fprint_info)>0 then '|' else '' end+'最新维护人'+ltrim(rtrim(@最新维护人))+'找不到对应记录' end
	set @fptype = case when ltrim(rtrim(@单价类型))='采购单价' then 1 else 0 end
	if isnull(@fprint_info,'x')<>'x' or len(@fprint_info)>0
	begin
		print (cast(@id as varchar(4))+'行:'+@fprint_info)
	end
	else
	begin
		update #poorder_price_insert set fsupid=@fsupid,fitemid=@fitemid,funitid=@funitid,fptype=@fptype,fuserid=@fuserid where id=@id
		if not exists(select 1 from t_supply where fsupid=@fsupid and fitemid=@fitemid and fptype=@fptype)
		begin
			insert into t_supply (fbrno,fsupid,fitemid,fcurrencyid,fpohighprice,fptype)values(0,@fsupid,@fitemid,1,0.0,@fptype)
		end
		if not exists(select 1 from t_supplyentry where fsupid=@fsupid and fitemid=@fitemid and fptype=@fptype and fprice=@fprice)
		begin
			insert into t_supplyentry (fbrno,fused,fentryid,fsupid,fitemid,funitid,fstartqty,fendqty,fptype,fprice,fcyid,fdiscount,fleadtime,fquotetime,fdisabledate,fremark,flastmodifiedby,flastmodifieddate) values ('0',0,@fentryid,@fsupid,@fitemid,@funitid,0,0,@fptype,@fprice,1,0,0,'2019-01-01','2100-01-01',@备注,@fuserid,convert(varchar(10),getdate(),120))
			update #poorder_price_insert set im_result='导入成功' where id=@id
			set @fentryid = @fentryid + 1
		end
	end
	fetch next from my_select into @供应商名称,@物料代码,@计量单位,@单价类型,@最新维护人,@id,@fprice,@备注
end
close my_select
deallocate my_select
update icmaxnum set fmaxnum=@fentryid where ftablename='t_supply'
go
select * from #poorder_price_insert
drop table #poorder_price_insert

 

转载于:https://my.oschina.net/ansenchina/blog/3021161