摘要：declare @pTypeId varchar(100), @pParId varchar(100), @tParentID varchar(100), @tBasePath varchar(100)
ALTER PROCEDURE [dbo].[pSys_ChatSJ]
( @Mode varchar(100)
)
AS
declare
@Rec int,
@jls int,
@ParPath varchar(1000),
@ParRec int,
@BasePath varchar(1000),
@prince1 decimal(18,4),
@prince2 decimal(18,4),
@prince3 decimal(18,4),
@prince4 decimal(18,4),
@prince5 decimal(18,4),
@relation1 decimal(18,4),
@relation2 decimal(18,4),
@relation3 decimal(18,4),
@ltem varchar(10),
@unit varchar(10),
@PyNo varchar(50),
@tem_price decimal(18,4),
@BaseNO integer ,
@num integer ,
@bz integer,
@sql varchar(1000)
declare @pTypeId varchar(100), @pParId varchar(100), @tParentID varchar(100), @tBasePath varchar(100)
declare @pleveal int, @pSonnum int
declare @pUserCode varchar(100), @pFullname varchar(100), @pName varchar(100), @pNamePy varchar(100)
declare @pTelandAddress varchar(100), @ppostcode varchar(100), @pperson varchar(100), @ptaxnumber varchar(100)
declare @pbankandacount varchar(100), @pcomment varchar(100), @partotal00 varchar(100), @paptotal00 varchar(100),@pArea varchar(100)
declare @pSoncount int
declare @Standards varchar(100)
declare @Type varchar(100), @Area varchar(100), @Unit1 varchar(100),@Unit2 varchar(100),@unitrate1 float,@unitrate2 float,
@Comment varchar(100)
declare @pBaseNo int
declare @Ktypeid varchar(100)
declare @jobnumber varchar(20), @outFactoryDate varchar(10)
declare @qty numeric(24,10), @Price numeric(24,10), @total numeric(24,10)
declare @goodsOrder int, @goodsOrderid int
declare @tGoodsid int, @tStid int
if @Mode='LST'
begin
insert into BasPTypeinner(
BaseNo ,
ParentNo ,
BaseLevel ,
BaseCode ,
Name ,
FullName ,
Standards,
Model ,
ProArea ,
BasicUnit ,
AssistUnitOne ,
AssistUnitTwo ,
AssistUnitThree ,
SalePriceBasic,
SalePriceOne ,
SalePriceTwo ,
SalePriceThree ,
PercentBasic ,
PercentOne,
PercentTwo ,
PercentThree ,
UseFullLifeDay ,
CostType ,
RecentPrice ,
RecentPrice1,
Deleted ,
PyNo ,
RowIndex ,
isSerial ,
PChage ,
Memo,
DeductType ,
DeductValue ,
MemberPrice ,
MinPrice ,
Preprice1 ,
Stoped ,
basePath
)
select
ID + 1 ,
Par_Id + 1,
1,
left(code, 30),
isnull(name,fullname) ,
fullname,
standard,
xh,
addr ,
isnull(unit1,'无') ,
unit2,
0,
0,
price,
0,
0,
0,
1,
unitrelation2, --辅助单位1
unitrelation3, --辅助单位2
0 ,
bzdateNum,
costmode,
0,
0,
0,
pym,
0 ,
serialtype,
0,
comment,
0,
0,
0,
0,
0,
0,
''
from aaa..t_base_goods
update BasBasicID set PTypeID=(select max(BaseNo)+1 from BasPTypeinner)
第一次置路径
declare upCur cursor for select Baseno from BasPTypeinner order by BaseLevel
open upCur
fetch next from upCur into @Rec
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasPTypeinner where Parentno=@Rec and deleted = 0
update BasPTypeinner set SonCount=@jls,SonNum=@jls where BaseNo = @Rec
IF @Rec1
begin
select @ParRec=Parentno from BasPTypeinner where BaseNo=@Rec --查询父亲ID
select @ParPath=Basepath from BasPTypeinner where BaseNo=@ParRec --查询父亲Path
update BasPTypeinner set BasePath=@ParPath+'-'+CONVERT(nvarchar(20), BaseNo) where BaseNo=@Rec
end
fetch next from upCur into @Rec
end
CLOSE upCur
DEALLOCATE upCur
第二次置路径
declare upCur2 cursor for select Baseno from BasPTypeinner order by BaseLevel
open upCur2
fetch next from upCur2 into @Rec
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasPTypeinner where Parentno=@Rec and deleted = 0
update BasPTypeinner set SonCount=@jls,SonNum=@jls where BaseNo = @Rec
IF @Rec1
begin
select @ParRec=Parentno from BasPTypeinner where BaseNo=@Rec --查询父亲ID
select @ParPath=Basepath from BasPTypeinner where BaseNo=@ParRec --查询父亲Path
update BasPTypeinner set BasePath=@ParPath+'-'+CONVERT(nvarchar(20), BaseNo) where BaseNo=@Rec
end
fetch next from upCur2 into @Rec
end
CLOSE upCur2
DEALLOCATE upCur2
truncate table BasPTypePriceMx
declare upCur cursor for
select a.baseNO, price1,price2,price3,price4,price5,PyNo ,BasicUnit
from basptype a,aaa..t_base_goods b where a.basecode=b.code and a.fullname=b.fullname
open upCur
fetch next from upCur into @baseNO,@prince1,@prince2,@prince3,@prince4,@prince5,@PyNo,@unit
while @@FETCH_STATUS=0
begin
set @num=0
if @prince1 > 0
begin
set @ltem='预设售价'
set @num=@num + 1
print @num
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@prince1, @ltem, @PyNo)
end
if @prince2 > 0
begin
set @ltem='预设售价'
set @num=@num + 1
print @num
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@prince2, @ltem, @PyNo)
end
if @prince3 > 0
begin
set @ltem='预设售价'
set @num=@num + 1
print @num
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@prince3, @ltem, @PyNo)
end
if @prince4 > 0
begin
set @ltem='预设售价'
set @num=@num + 1
print @num
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@prince4, @ltem, @PyNo)
end
if @prince5 > 0
begin
set @ltem='预设售价'
set @num=@num + 1
print @num
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@prince5, @ltem, @PyNo)
end
@relation1
if @relation1 > 0 unitrelation2
begin
if @prince1 *@relation1 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince1 *@relation1
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@tem_price, @ltem, @PyNo)
end
if @prince2 *@relation1 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince2 *@relation1
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num) ;
end
if @prince3 *@relation1 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince3 *@relation1
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num);
end
if @prince4 *@relation1 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince4 *@relation1
set @num=@num + 1
end
if @prince5 *@relation1 > 0
begin
set @ltem='预设售价'
set @tem_price=@relation1
set @num=@num + 1
end
end
@relation2
if @relation2 > 0 unitrelation2
begin
if @prince1 *@relation2 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince1 *@relation2
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@tem_price, @ltem, @PyNo)
end
if @prince2 *@relation2 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince2 *@relation2
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num) ;
end
if @prince3 *@relation2 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince3 *@relation2
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num);
end
if @prince4 *@relation2 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince4 *@relation2
set @num=@num + 1
end
if @prince5 *@relation2 > 0
begin
set @ltem='预设售价'
set @tem_price=@relation2
set @num=@num + 1
end
end
@relation3//////////////////////////////////
if @relation3 > 0 unitrelation2
begin
if @prince1 *@relation3 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince1 *@relation3
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@tem_price, @ltem, @PyNo)
end
if @prince2 *@relation3 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince2 *@relation3
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num) ;
end
if @prince3 *@relation3 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince3 *@relation3
set @num=@num + 1
set @ltem=@ltem + convert(varchar,@num);
end
if @prince4 *@relation3 > 0
begin
set @ltem='预设售价'
set @tem_price=@prince4 *@relation3
set @num=@num + 1
end
if @prince5 *@relation3 > 0
begin
set @ltem='预设售价'
set @tem_price=@relation3
set @num=@num + 1
end
end
fetch next from upCur into @baseNO,@prince1,@prince2,@prince3,@prince4,@prince5,@PyNo,@unit
end
CLOSE upCur
DEALLOCATE upCur
(导入单位信息)
delete from basunittypeinner where baseno>1
update aaa..t_base_wldw set par_id = 1 where par_id
insert into basunittypeinner (baseno, parentno, basecode, name, IniArTotal,IniAPTotal,IniPrepayARTotal,IniPrepayAPTotal,
fullname, unitphone,faxnum , pyno,address,PostCode,linkman )
select id,par_id,code,name,ystotal00, yftotal00,yushoutotal00,yufutotal00,
fullname, tel,'' ,pym,addr,postcode,person
from aaa..t_base_wldw where id > 4
--declare @Rec int,@jls int,@ParPath varchar(1000),@ParRec int,@BasePath varchar(1000)
declare upCur cursor for select Baseno from basunittypeinner order by BaseLevel
open upCur
fetch next from upCur into @Rec
while @@FETCH_STATUS=0
begin
select @jls=count(*) from basunittypeinner where Parentno=@Rec and deleted = 0
update basunittypeinner set SonNum=@jls where BaseNo = @Rec
IF @Rec1
begin
select @ParRec=Parentno from basunittypeinner where BaseNo=@Rec --查询父亲ID
select @ParPath=Basepath from basunittypeinner where BaseNo=@ParRec --查询父亲Path
update basunittypeinner set BasePath=@ParPath+'-'+CONVERT(nvarchar(20), BaseNo) where BaseNo=@Rec
end
fetch next from upCur into @Rec
end
CLOSE upCur
DEALLOCATE upCur
update basunittypeinner set IniARTotal=0 where SonNum!=0
update basunittypeinner set IniAPTotal=0 where SonNum!=0
update basunittypeinner set IniPrepayARTotal=0 where SonNum!=0
update basunittypeinner set IniPrepayAPTotal=0 where SonNum!=0
update BasBasicID set UnitID=(select max(baseNO)+1 from basunittypeinner)
--导入仓库
insert into BasStockinner( baseno,parentNo,baselevel,basepath,fullname,name,basecode,deleted,pyno,rowindex,sttype)
select id+1,par_id+1,0, '' ,fullname,name,code, 0, pym,0 , 0
from aaa..t_base_storage
declare upCur cursor for select Baseno from BasStockinner order by BaseLevel
open upCur
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasStockinner where Parentno=@Rec and deleted = 0
update BasStockinner set SonNum=@jls where BaseNo = @Rec
IF @Rec1
begin
select @ParRec=Parentno from BasStockinner where BaseNo=@Rec --查询父亲ID
select @ParPath=Basepath from BasStockinner where BaseNo=@ParRec --查询父亲Path
update BasStockinner set BasePath=@ParPath+'-'+CONVERT(nvarchar(20), BaseNo) where BaseNo=@Rec
end
fetch next from upCur into @Rec
end
CLOSE upCur
DEALLOCATE upCur
update BasBasicID set stockid=(select max(baseNO)+1 from BasStockinner)
------导入职员
delete from basperson where baseno>1
insert into basperson (baseno ,parentno,baselevel,basecode,name,fullname,pyno,sex,
deptid,linktel,handtel,address,deleted,email,injobdate,birthday,sonnum,soncount,basepath)
select id+1,par_id+1, 1, code, name, fullname,pym, '',
depid, tel, mobileno, addr, 0, '', '', birthday ,0,0,''
from aaa..t_base_employees
declare upCur cursor for select Baseno from BasPerson order by BaseLevel
open upCur
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasPerson where Parentno=@Rec and deleted = 0
update BasPerson set SonCount=@jls,SonNum=@jls where BaseNo = @Rec
IF @Rec1
begin
select @ParRec=Parentno from BasPerson where BaseNo=@Rec --查询父亲ID
select @ParPath=Basepath from BasPerson where BaseNo=@ParRec --查询父亲Path
update BasPerson set BasePath=@ParPath+'-'+CONVERT(nvarchar(20), BaseNo) where BaseNo=@Rec
--处理其它关联表
end
fetch next from upCur into @Rec
end
CLOSE upCur
DEALLOCATE upCur
update BasBasicID set personid=(select max(baseNO)+1 from BasPerson)
导入部门
insert into basdept(baseno,parentno,baselevel,sonnum,basepath,basecode,name,fullname,deleted,pyno,rowindex)
select id+1,par_id+1,leveal, 0, '', code,name ,fullname,0, pym,0
from aaa..t_base_department
declare upCur cursor for select Baseno from basdept order by BaseLevel
open upCur
while @@FETCH_STATUS=0
begin
select @jls=count(*) from basdept where Parentno=@Rec and deleted = 0
update basdept set SonNum=@jls where BaseNo = @Rec
IF @Rec1
begin
select @ParRec=Parentno from basdept where BaseNo=@Rec --查询父亲ID
select @ParPath=Basepath from basdept where BaseNo=@ParRec --查询父亲Path
update basdept set BasePath=@ParPath+'-'+CONVERT(nvarchar(20), BaseNo) where BaseNo=@Rec
end
fetch next from upCur into @Rec
end
CLOSE upCur
DEALLOCATE upCur
update BasBasicID set deptid=(select max(baseNO)+1 from basdept)
----导入库存信息
insert into StorageIniGoods (ptypeid,ktypeid,jobnumber,outfactorydate,qty,
price,total,goodsorder,basepath)
select goodsid+1,stid+1,jobnumber,'' ,goodsqty,
price,total,goodspc,''
from aaa..t_prime_goodsstock
update StorageIniGoods set basepath =b.basepath
from StorageIniGoods,basptypeinner b where ptypeid=b.baseno
update basptypeinner set name = fullname where name='' or name is null
update basunittypeinner set name = fullname where name='' or name is null
update basptypeinner set basicunit = '无' where basicunit = ''
update BasPTypeBarMx set subunit = '无' where subunit = ''
update basptypepricemx set subunit = '无' where subunit = ''
end
else if @mode='GST'
begin
--导入往来单位基本信息
select @pBaseNo = 2
declare upCur cursor for
select typeId, parid, leveal, Sonnum, UserCode, Fullname, Name, NamePy, TelandAddress, postcode, person, taxnumber, bankandacount, comment, artotal00, aptotal00,area
from aaa..btype
where typeId'00000' and deleted=0
order by typeId
open upCur
fetch next from upCur into
@ptypeId, @pparid, @pleveal, @pSonnum, @pUserCode, @pFullname, @pName, @pNamePy, @pTelandAddress, @ppostcode, @pperson, @ptaxnumber,
@pbankandacount, @pcomment, @partotal00, @paptotal00 ,@parea
while @@FETCH_STATUS=0
begin
if @pParId = '00000'
begin
select @tParentId=1
insert into BasUnitTypeInner(BaseNo, ParentNo, BaseLevel, SonNum, BasePath,
BaseCode, FullName, Name, PYNo, UnitPhone, postcode, LinkMan, taxnum, bankandacount, Memo, iniartotal, iniaptotal,address)
values(@pBaseNo, @tParentId, @pleveal, @pSonnum, '',
@pUserCode, @pFullname, @pName, @pNamePy, @pTelandAddress, @ppostcode, @pperson, @ptaxnumber,
@pbankandacount, @pcomment, @partotal00, @paptotal00,@parea )
update BasUnitTypeInner set BasePath='1-'+rtrim(convert(char,@pBaseNo)) where BaseNo=@pBaseNo
end
else
begin
select @tParentId=(select BaseNo from BasUnitTypeInner
where fullname=(select fullname from aaa..btype where typeid=@pParId) and
Basecode=(select userCode from aaa..btype where typeid=@pParId))
select @tBasePath=(select BasePath from BasUnitTypeInner where BaseNo=@tParentId)
update BasUnitTypeInner set BasePath=@tBasePath+'-'+rtrim(convert(char,@pBaseNo)) where BaseNo=@pBaseNo
end
select @pBaseNo = @pBaseNo + 1
fetch next from upCur into @ptypeId, @pparid, @pleveal, @pSonnum, @pUserCode, @pFullname, @pName, @pNamePy, @pTelandAddress, @ppostcode, @pperson, @ptaxnumber,
@pbankandacount, @pcomment, @partotal00, @paptotal00 ,@parea
end
CLOSE upCur
DEALLOCATE upCur
declare upCur cursor for select Baseno from BasUnitTypeInner order by Baseno
open upCur
fetch next from upCur into @Rec
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasUnitTypeInner where Parentno=@Rec
update BasUnitTypeInner set SonNum=@jls where BaseNo = @Rec
end
CLOSE upCur
DEALLOCATE upCur
update BasBasicID set UnitID=(select max(baseno)+1 from BasUnitTypeInner)
--导入商品基本信息
select @pBaseNo = 2
declare upCur cursor for
select typeId, parid, leveal, Sonnum, SonCount, UserCode, Fullname, Name, NamePy, Standard, Type, Area, Unit1,unit2,unitrate1,unitrate2, Comment
from aaa..ptype
where typeId'00000' and deleted=0
order by typeId
open upCur
fetch next from upCur into
@ptypeId, @pparid, @pleveal, @pSonnum, @pSonCount, @pUserCode, @pFullname, @pName, @pNamePy, @Standards, @Type, @Area, @Unit1,@Unit2,@unitrate1,@unitrate2, @Comment
while @@FETCH_STATUS=0
begin
if @pParId = '00000'
begin
select @tParentId=1
insert into BasPtypeInner(BaseNo, ParentNo, BaseLevel, SonNum, SonCount, BasePath,
DeductType, isSerial, PChage,
BaseCode, FullName, Name, PYNo, Standards, Model, ProArea, BasicUnit, AssistUnitOne, AssistUnitTwo, AssistUnitThree, memo,percentbasic,percentone)
values(@pBaseNo, @tParentId, @pleveal, @pSonnum, @pSonCount, '',
0, 0, 0,
@pUserCode, @pFullname, @pName, @pNamePy, @Standards, @Type, @Area, @Unit1, @Unit2, '', '', @Comment,@unitrate1,@unitrate2)
update BasPtypeInner set BasePath='1-'+rtrim(convert(char,@pBaseNo)) where BaseNo=@pBaseNo
end
else
begin
select @tParentId=(select BaseNo from BasPtypeInner
where fullname=(select fullname from aaa..ptype where typeid=@pParId) and
Basecode=(select userCode from aaa..ptype where typeid=@pParId))
select @tBasePath=(select BasePath from BasPtypeInner where BaseNo=@tParentId)
update BasPtypeInner set BasePath=@tBasePath+'-'+rtrim(convert(char,@pBaseNo)) where BaseNo=@pBaseNo
end
select @pBaseNo = @pBaseNo + 1
fetch next from upCur into @ptypeId, @pparid, @pleveal, @pSonnum, @pSonCount, @pUserCode, @pFullname, @pName, @pNamePy, @Standards, @Type, @Area,@Unit1,@Unit2,@unitrate1,@unitrate2, @Comment
end
CLOSE upCur
DEALLOCATE upCur
declare upCur cursor for select Baseno from BasPtypeInner order by Baseno
open upCur
fetch next from upCur into @Rec
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasPtypeInner where Parentno=@Rec
update BasPtypeInner set SonCount=@jls,SonNum=@jls where BaseNo = @Rec
end
CLOSE upCur
DEALLOCATE upCur
update BasBasicID set ptypeid=(select max(baseno)+1 from BasPtypeInner)
--导入仓库基本信息
select @pBaseNo = 2
declare upCur cursor for
select typeId, parid, leveal, Sonnum, UserCode, Fullname, Name, NamePy
from aaa..stock
where typeId'00000' and deleted=0
order by typeId
open upCur
fetch next from upCur into
@ptypeId, @pparid, @pleveal, @pSonnum, @pUserCode, @pFullname, @pName, @pNamePy
begin
if @pParId = '00000'
begin
select @tParentId=1
insert into BasStockInner(BaseNo, ParentNo, BaseLevel, SonNum, BasePath, BaseCode, FullName, Name, PYNo)
values(@pBaseNo, @tParentId, @pleveal, @pSonnum, '', @pUserCode, @pFullname, @pName, @pNamePy)
update BasStockInner set BasePath='1-'+rtrim(convert(char,@pBaseNo)) where BaseNo=@pBaseNo
end
else
begin
select @tParentId=(select BaseNo from BasStockInner
where fullname=(select fullname from aaa..stock where typeid=@pParId) and
Basecode=(select userCode from aaa..stock where typeid=@pParId))
select @tBasePath=(select BasePath from BasStockInner where BaseNo=@tParentId)
update BasStockInner set BasePath=@tBasePath+'-'+rtrim(convert(char,@pBaseNo)) where BaseNo=@pBaseNo
end
select @pBaseNo = @pBaseNo + 1
fetch next from upCur into @ptypeId, @pparid, @pleveal, @pSonnum, @pUserCode, @pFullname, @pName, @pNamePy
end
CLOSE upCur
DEALLOCATE upCur
declare upCur cursor for select Baseno from BasStockInner order by Baseno
open upCur
fetch next from upCur into @Rec
while @@FETCH_STATUS=0
begin
select @jls=count(*) from BasStockInner where Parentno=@Rec
update BasStockInner set SonNum=@jls where BaseNo = @Rec
end
CLOSE upCur
DEALLOCATE upCur
update BasBasicID set stockid=(select max(baseno)+1 from BasStockInner)
declare @no1 int, @no2 int, @no3 int
select @no1 = max(baseno)+1 from dbo.BasPtypeInner
select @no2 = max(baseno)+1 from dbo.BasUnitTypeInner
select @no3 = max(baseno)+1 from dbo.BasStockInner
update BasBasicID set ptypeid = @no1, UnitID = @no2, StockID = @no3
--导入期初库存表
declare upCur cursor for
select ptypeid, Ktypeid, jobnumber, outFactoryDate, qty, Price, total, goodsOrder
from aaa..inigoodsstocks
order by goodsorder
open upCur
fetch next from upCur into @ptypeid, @Ktypeid, @jobnumber, @outFactoryDate, @qty, @Price, @total, @goodsOrder
begin
select @tGoodsid=(select BaseNo from BasPtypeInner
where fullname=(select fullname from aaa..ptype where typeid=@ptypeid) and
BaseCode=(select usercode from aaa..ptype where typeid=@ptypeid) )
select @tStid=(select BaseNo from BasStockInner
where fullname=(select fullname from aaa..stock where typeid=@Ktypeid) and
BaseCode=(select usercode from aaa..stock where typeid=@Ktypeid) )
insert into StorageIniGoods (ptypeid, ktypeid, jobnumber, outFactorydate, qty, price, total, goodsorder)
values(@tGoodsid, @tStid, @jobnumber, @outFactoryDate, @qty, @Price, @total, @goodsOrder)
fetch next from upCur into @ptypeid, @Ktypeid, @jobnumber, @outFactoryDate, @qty, @Price, @total, @goodsOrder
end
CLOSE upCur
DEALLOCATE upCur
update basptypeinner set percentbasic=1
(2)
declare
@uname varchar(100),
@urate varchar(100)
declare upCur cursor for select ptypeid,uname,urate from aaa..UnitPrice
open upCur
fetch next from upCur into @baseno,@uname,@urate
while @@FETCH_STATUS=0
begin
update aaa..ptype set unit2=@uname,unitrate2=@urate where typeid=@baseno
end
CLOSE upCur
DEALLOCATE upCur
(3)
truncate table BasPTypePriceMx
declare upCur cursor for
select a.baseNO, b.preprice1,b.preprice2,b.preprice3,b.preprice4,b.unitrate1,b.unitrate2,unit1,Unit2
from basptypeinner a,aaa..ptype b where a.basecode=b.usercode and a.fullname=b.fullname
open upCur
fetch next from upCur into @baseNO,@prince1,@prince2,@prince3,@prince4,@relation1,@relation2,@unit,@unit2
begin
set @num=0
print @unit
print @unit2
-- if @prince1 > 0
--begin
set @ltem='预设售价'
set @num=@num + 1
print @num
set @ltem=@ltem + convert(varchar,@num)
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
values(@baseNO,@unit,@num,@prince1, @ltem, '')
-- end
--if @prince2 > 0
-- begin
set @ltem='预设售价'
set @num=@num + 1
print @num
values(@baseNO,@unit,@num,@prince2, @ltem, '')
-- end
-- if @prince3 > 0
-- begin
set @ltem='预设售价'
set @num=@num + 1
print @num
values(@baseNO,@unit,@num,@prince3, @ltem, '')
-- end
---
-- if @prince4 > 0
--begin
set @ltem='预设售价'
set @num=@num + 1
print @num
values(@baseNO,@unit,@num,@prince4, @ltem, '')
-- end
end
CLOSE upCur
DEALLOCATE upCur
(4)
delete from BasPTypePriceMx where subunit=''
insert into BasPTypePriceMx(Ptypeid,subUnit,IndexID,AdvPrice,displayname,PyNo)
select a.baseno,a.assistunitone, b.indexid, percentone*advprice ,b.displayname,b.PyNo
from basptypeinner a ,BasPTypePriceMx b
where a.baseno=b.ptypeid
update basptypeinner set name=fullname where name='' or name is null
(导条码)
insert into basPtypeBarmx(Ptypeid,SubUnit,Indexid,BarName,BarCode,IsDefault)
select a.baseNO,a.BasicUnit,1,'条码1',b.barcode,1
from basptypeinner a,aaa..ptype b
where a.basecode=b.usercode and a.fullname=b.fullname
end
来源：小月科技论