k3获取单据编号

alter Procedure txkj_GetBillNo
--单据编号获取存储过程
@ClassType Int,--单据类型id
@isSave int,--是否更新
@BillNo Varchar(200) OutPut,--输出编号
@DeptID Int = 0,
@EmpID Int = 0
As
--如果没有对应的单据编码规则，则返回空白
If Not Exists(Select 1 From t_BillCodeRule Where FBillTypeID = @ClassType)
Begin
Set @BillNo = ''
Goto H_End
End
Declare @ProjectID Int
Declare @ProjectVal varchar(100)
Declare @Length Int
Declare @table Varchar(100)
Declare @field Varchar(100)
Declare @Pre Varchar(100)
Declare @Pre2 Varchar(100)
Declare @Suf Varchar(100)
Declare @Suf2 Varchar(100)
Declare @Cur Varchar(100)
Declare @Next Varchar(100)
Declare @Len Int
Declare @Now Varchar(10)
Declare @year Varchar(4)
Declare @Month Varchar(2)
Declare @Day Varchar(2)
Declare @I Int
Declare @iLoc3 Int
Declare @ItemID Int
Declare @Item Varchar(100)
Declare @SSQL NVarchar(1000)
Select @Pre = '',@Pre2 = '',@Suf = '',@Suf2 = ''
--获得当前编号、下一个编号和编号长度
Select @Cur = FProjectVal,@Next = 1 + FProjectVal,@Len = FLength From t_BillCodeRule Where FBillTypeID = @ClassType And FProjectID = 3
--调整编号长度，并用0格式化当前编号和下一个编号，获得流水号（截止2008-11-12，K3编码规则中流水号一项必有且唯一）
If Len(@Cur) > @Len Set @Len = Len(@Cur)
Set @Cur = Replicate('0',@Len - Len(@Cur)) + @Cur
If Len(@Next) > @Len Set @Len = Len(@Next)
Set @Next = Replicate('0',@Len - Len(@Next)) + @Next
--获得编号规则
Select Identity(Int,1,1) FID, A.FClassIndex,A.FProjectID,A.FProjectVal,A.FLength,
IsNull(B.FTable,'') FTable,IsNull(E.FFieldName,'') FFieldName
Into #T
From t_BillCodeRule A
left Join t_Option E On A.FProjectID = E.FProjectID And A.FFormatIndex = E.FID
Left Join t_CheckProject B On A.FBillType = B.FBillTypeID And A.FProjectVal = B.FField
Where A.FBillTypeID = @ClassType Order By A.FClassIndex
Select @i = 1,@iLoc3 = FID From #T Where FProjectID = 3
--依次处理各规则拼接单据编号，在流水号之前的为前缀，在流水号之后的为后缀，@ProjectID为3的不处理，已经在开头获得流水号
While @i <= (Select Max(FID) From #T)
Begin
Select
@ProjectID = FProjectID,@ProjectVal = FProjectVal,@Length = FLength,
@Table = FTable,@Field = FFieldName
From #T Where FID = @i
--自定义，也就是字符串
If @ProjectID = 1
Begin
If @i < @iLoc3
Select @Pre = @Pre + @ProjectVal,@Pre2 = @Pre2 + @ProjectVal + '+'
Else
Select @Suf = @Suf + @ProjectVal,@Suf2 = @Suf2 + '+' + @ProjectVal
Goto H_Next
End
--日期
If @ProjectID = 2
Begin
Set @Now = Convert(Varchar(10),GetDate(),21)
Select @Year = Left(@Now,4),@Month = SubString(@Now,6,2),@Day = Right(@Now,2)
--处理年
if CharIndex('yyyy',@ProjectVal) <> 0 Begin
Set @ProjectVal = Replace(@ProjectVal,'yyyy',@Year)
End Else Begin
if CharIndex('yy',@ProjectVal) <> 0 Begin
Set @ProjectVal = Replace(@ProjectVal,'yy',Right(@Year,2))
End
End
--处理月
if CharIndex('mm',@ProjectVal) <> 0 Begin
Set @ProjectVal = Replace(@ProjectVal,'mm',@Month)
End Else Begin
if CharIndex('m',@ProjectVal) <> 0 Begin
Set @ProjectVal = Replace(@ProjectVal,'m',Cast(Cast(@Month As Int) As Varchar(2)))
End
End
--处理日
if CharIndex('dd',@ProjectVal) <> 0 Begin
Set @ProjectVal = Replace(@ProjectVal,'dd',@Day)
End Else Begin
if CharIndex('d',@ProjectVal) <> 0 Begin
Set @ProjectVal = Replace(@ProjectVal,'d',Cast(Cast(@Day As Int) As Varchar(2)))
End
End
If @i < @iLoc3
Select @Pre = @Pre + @ProjectVal,@Pre2 = @Pre2 + '日期' + '+'
Else
Select @Suf = @Suf + @ProjectVal,@Suf2 = @Suf2 + '+' + '日期'
Goto H_Next
End
--部门和业务员
If @ProjectID = 9
Begin
Set @ItemID = IsNull((Case When @ProjectVal = 'FDeptID' Then @DeptID Else @EmpID End),0)
Set @sSql = 'Set @Item = IsNull((Select ' + @Field + ' From ' + @Table + ' Where FItemID = ' + Cast(@ItemID As Varchar(20)) + '),'''')'
Exec sp_executesql @sSql,N'@Item varchar(100) Out',@Item Out
If @i < @iLoc3
Select @Pre = @Pre + @Item,@Pre2 = @Pre2 + Case When @ProjectVal = 'FDeptID' Then '部门' Else '业务员' End + '+'
Else
Select @Suf = @Suf + @Item,@Suf2 = @Suf2 + '+' + Case When @ProjectVal = 'FDeptID' Then '部门' Else '业务员' End
Goto H_Next
End
H_Next:
Set @i = @i + 1
End
--拼接单据号
Set @BillNo = IsNull(@Pre,'') + @Cur + IsNull(@Suf,'')
if @isSave=1
begin
--更新参数表
Begin Tran
Update ICBillNo Set FCurNo = Cast(@Next As Int),
FDesc = IsNull(@Pre2,'') + @Next + IsNull(@Suf2,'')
Where FBillID = @ClassType
If @@Error <> 0 Begin
RollBack Tran
Set @BillNo = ''
Goto H_End
End

Update t_BillCodeRule Set FProjectVal = Cast(Cast(@Next As Int) As Varchar(100)),FLength = @Len Where FBillTypeID = @ClassType And FProjectID = 3
If @@Error <> 0 Begin
RollBack Tran
Set @BillNo = ''
Goto H_End
End
Commit Tran
end
H_End:
Return
GO