好久没有在网站上写的东西了,今天早上正好有这个空闲时间
公司的条码系统已经运行了快一年了,这套系统也是我刚刚进入公司,开始实施开发并导入使用的,这里我的目的不是讨论该套系统,
今天主要目的还是想说明下,条码系统导入K3所实现的过程,
K3中所相关的也是最主要的是3张表,即:ICStockBill,ICStockBillEntry,icmaxnum
尤其是表:icmaxnum,第一次导入是只是导入了前2张,后来发现数据是导入进去了,过一段时间就消失了,后来查明原因
才知道问题在于 表:icmaxnum,这个表的功能是实现最大单据号(最大序号,这里的序号其实我已经通过函数获得),
但获得后还必须将当前的序号写入到这个表中,这点很重要,
以下是导入工具的,主存储过程,里面有相关的函数、子存储过程的(这些代码如果需要可以联络我,包括每天导入数据并发送邮件的工具
该工具是VS 2008开发的,语言:C#)
///
USE [AIS20150326095508]
GO
/****** Object: StoredProcedure [dbo].[Luke_Insert_ICStockBill] Script Date: 04/14/2015 08:47:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:Luke.Lu
— Create date: 2015-3-31
— Description:增加表ICStockBill,ICStockBillEntry记录
— =============================================
ALTER PROCEDURE [dbo].[Luke_Insert_ICStockBill]
AS
begin transaction
declare @FInterID nvarchar(10)
declare @FDate datetime
declare @FTranType int
declare @FBrNo nvarchar(10)
declare @FBillNo nvarchar(50)
declare @FBillerID int –制单人
declare @FDeptID nvarchar(10) –部门
declare @FFManagerID int –验收人
declare @FSManagerID int –保管人
declare @FROB nvarchar(10)
declare @FEntryID nvarchar(10)
declare @FItemID nvarchar(10)
declare @FQty int
declare @FPrice int
declare @FUnitID nvarchar(10)
declare @FAuxQty int
declare @FDCStockID nvarchar(20) –存放仓库
declare @Icount int
declare @i int
declare @FItem nvarchar(50)
set @FPrice=0
set @FUnitID=dbo.Luke_GetUnit(‘Set’)
set @FQty=0 –应收入库数量,需要经常变动*,K3基本收货数量数字上等于@FAuxQty
— SET @FAuxQty=1—–实际入库数量,需要经常变动*
— set @FItemID=dbo.Luke_GetFitemID(‘TEST’) –料号,需要经常变动*
set @FEntryID=0 –序号,需要经常变动*
set @FROB=1 –蓝字
set @FBillerID=dbo.Luke_GetEmpID_FBillerID(‘Administrator’)
set @FDeptID=dbo.Luke_GetFSCStockID(‘电镀车间’)
set @FFManagerID=dbo.Luke_GetEmpID(‘SYSADMIN’)
set @FSManagerID=dbo.Luke_GetEmpID(‘SYSADMIN’)
set @FInterID=dbo.Luke_GetICStockBill_FInterID()
–set @FDate=getdate()
set @FTranType=dbo.Luke_GetFTranType(‘KCZH’) –传输标识
set @FBrNo=0
set @FBillNo=dbo.Luke_GetMaxFBillNo()
set @FDCStockID=dbo.Luke_GetStockID(‘一次电镀良品次数’)
SET NOCOUNT ON
–条件包括日期
select @Icount=count(*) from AIS20110901110631.dbo.CT_B_JOB_COMP_SUM_FORK3I_VIEW where Comp_date=CONVERT (varchar(10), getdate()-1, 120)
if (@Icount is not null or @Icount<>0)
print @Icount
–条件包括日期
DECLARE cur CURSOR FOR select itemcode,comp_date,sumQty from AIS20110901110631.dbo.CT_B_JOB_COMP_SUM_FORK3I_VIEW where Comp_date=CONVERT (varchar(10), getdate()-1, 120) order by itemcode desc
open cur
–FETCH from cur into @FItem,@FDate,@FAuxQty
–BEGIN TRANSACTION
while @@FETCH_STATUS=0
begin
FETCH next from cur into @FItem,@FDate,@FAuxQty
set @FItemID=dbo.Luke_GetFitemID(@FItem)
set @FEntryID=@FEntryID + 1
print @FEntryID
print @FItem
print @FDate
print @FAuxQty
if @FEntryID<=@Icount
begin
–插入到表ICStockBill
–BEGIN transaction
–插入ICStockBill
if @FEntryID=1
begin
insert into ICStockBill(FBrNo,FInterID,FTranType,FDate,FBillNo,FDeptID,FFManagerID,FSManagerID,FBillerID,FROB)values (@FBrNo,@FInterID,@FTranType,@FDate,@FBillNo,@FDeptID,@FFManagerID,@FSManagerID,@FBillerID,@FROB)
end
–插入ICStockBillEntry
if (@FItemID is not null or len(@FItemID)<1)
begin
insert into ICStockBillEntry(FBrNo,FInterID,FItemID,FQty,FUnitID,FEntryID,FAuxQty,FDCStockID)values(@FBrNo,@FInterID,@FItemID,@FAuxQty,@FUnitID,@FEntryID,@FAuxQty,@FDCStockID)
–更新单据最大号表中记录
Update icmaxnum set Fmaxnum=(select max(FInterID) from ICStockBillEntry) where Ftablename=’ICStockBill’
end
else
begin
–BEGIN transaction
EXEC dbo.Luke_CreateItem @FItem,@FAuxQty,@FDate
— if @@error>0
–begin
— rollback transaction
— retrun
— end
— else
— begin
— commit transaction
— end
end
–select @@fetch_status
–FETCH from cur into @FItem,@FDate,@FAuxQty
if @@error>0
begin
rollback transaction
retrun
end
–else
— begin
— Update icmaxnum set Fmaxnum=(select max(FInterID) from ICStockBillEntry) where Ftablename=’ICStockBill’
— end
–else
— commit transaction
end
end
commit transaction
–FETCH next from cur into @FItem,@FDate,@FAuxQty
–else
–begin
close cur
DEALLOCATE cur
SET NOCOUNT OFF
— begin
— — –插入ICStockBill
— insert into ICStockBill(FBrNo,FInterID,FTranType,FDate,FBillNo,FDeptID,FFManagerID,FSManagerID,FBillerID,FROB)
— –values (@FBrNo,@FInterID,@FTranType,@FDate,@FBillNo,@FDeptID,@FFManagerID,@FSManagerID,@FBillerID,@FROB)
— select
— @FBrNo,
— @FInterID,
— @FTranType,
— comp_date,
— @FBillNo,
— @FDeptID,
— @FFManagerID,
— @FSManagerID,
— @FBillerID,
— @FROB
— from AIS20110901110631.dbo.CT_B_JOB_COMP_SUM_FORK3I_VIEW where Comp_date=CONVERT (varchar(10), getdate()-1, 120)
— end
— begin
— — –插入ICStockBillEntry
—-insert into ICStockBillEntry(FBrNo,FInterID,FItemID,FQty,FUnitID,FEntryID,FAuxQty,FDCStockID)
—- –values(@FBrNo,@FInterID,@FItemID,@FQty,@FUnitID,@FEntryID,@FAuxQty,@FDCStockID)
—- select
—- @FBrNo,
—- @FInterID,
—- dbo.Luke_GetFitemID(itemcode),
—- @FQty,
—- @FUnitID,
—- –IDENTITY(INT,1,1) as id,
—- 1,
—- sumQty,
—- @FDCStockID
—- from AIS20110901110631.dbo.CT_B_JOB_COMP_SUM_FORK3I_VIEW where Comp_date=CONVERT (varchar(10), getdate()-1, 120)
— end
— if @@error>0
—begin
—rollback transaction
—return
—end
— commit transaction
///
—本信息来至于ACCESS技巧网 Http://www.mdbtip.com
转载于:https://blog.51cto.com/officevba/1632092
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/109486.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...