u8:应付单据录入生成编号

use UFDATA_012_2014
go
if OBJECT_ID('p_get_number','P') >0 
   DROP PROCEDURE [dbo].[P_Get_Number]
GO

declare 
@cardNum1 nvarchar(60) ,
@dDate1 datetime ,
@create_user_id1 nvarchar(40),
@number1 nvarchar(100),
@SQL NVARCHAR(2000),
@PARA NVARCHAR(500);
begin
  set @dDate1 = GETDATE();
  SET @SQL =N'[dbo].[P_Get_Number] ';
  --set @PARA = N'[dbo].[P_Get_Number]  @CardNum  Nvarchar(60), @ddate datetime, @create_user_id nvarchar(40), @number nvarchar(100) output';
  --exec SP_EXECUTESQL   @para, @CardNum1 =@CardNum, @ddate=@DDATE1 ,@create_user_id='demo',@number= @number1 output;
  EXEC [dbo].[P_Get_Number] 'P0',@dDate1,'dmeo',@number1 output;
  select @number1; 
end;
go

create PROCEDURE [dbo].[P_Get_Number]
@cardNum nvarchar(60) ,
@dDate datetime ,
@create_user_id nvarchar(40),
@number nvarchar(100)  output
as
begin
/*
* Author: samrv,
* Create Date: 2023-07-25
* 生成单据编号
*/
declare @user_id nvarchar(40)
declare @number1 nvarchar(100)
declare @cContent nvarchar(50);
set @user_id = (select cCode from VoucherContrapose a  left join   UserHrPersonContro b  on cPsn_Num = @create_user_id where a.cSeed = b.cUser_Id)

SELECT  @number1 =
    case 
    
    
    when  Prefix1Len=4 AND Prefix1Rule ='年'  then  CONVERT(varchar(4), @dDate, 112)
    
    when  Prefix1Len=4 AND Prefix1Rule ='年月' then  substring(CONVERT(varchar(6),@dDate,112),3,4)
    
    when   Prefix1Len=6 AND Prefix1Rule ='年月' then  CONVERT(varchar(6), @dDate, 112)
    
    when   Prefix1Len=6 AND Prefix1Rule ='年月日' then  substring(CONVERT(varchar(8), @dDate, 112),3,6)
    
    when   Prefix1Len=8 AND Prefix3Rule ='年月日' then  CONVERT(varchar(8), @dDate, 112)
        
    else '' 
    end,
    @cContent= Glide 
from  VoucherNumber where CardNumber = @cardNum;

declare @number2 nvarchar(100)

SELECT  @number2 =
    case 
    
    
    when  Prefix2Len=4 AND Prefix2Rule ='年' then  CONVERT(varchar(4), @dDate, 112)
    
    when Prefix2Len=4 AND Prefix2Rule ='年月' then  substring(CONVERT(varchar(6),@dDate,112),3,4)
    
    when  Prefix2Len=6 AND Prefix2Rule ='年月' then  CONVERT(varchar(6), @dDate, 112)
    
    when  Prefix2Len=6 AND Prefix2Rule ='年月日' then  substring(CONVERT(varchar(8), @dDate, 112),3,6)
    
    when  Prefix2Len=8 AND Prefix3Rule ='年月日' then  CONVERT(varchar(8), @dDate, 112)

    else '' 
    end,
    @cContent= Glide 
    
from  VoucherNumber where CardNumber = @cardNum

declare @number3 nvarchar(100)

SELECT  @number3 =
    case 
    
    
    when  Prefix3Len=4 AND Prefix3Rule ='年' then  CONVERT(varchar(4), @dDate, 112)
    
    when  Prefix3Len=4 AND Prefix3Rule ='年月' then  substring(CONVERT(varchar(6),@dDate,112),3,4)
    
    when  Prefix3Len=6 AND Prefix3Rule ='年月' then  CONVERT(varchar(6), @dDate, 112)
    
    when  Prefix3Len=6 AND Prefix3Rule ='年月日' then  substring(CONVERT(varchar(8), @dDate, 112),3,6)
    
    when  Prefix3Len=8 AND Prefix3Rule ='年月日' then  CONVERT(varchar(8), @dDate, 112)
    

    else '' 
    end ,
    @cContent= Glide 
     
from  VoucherNumber where CardNumber = @cardNum

	declare @num int
	declare @len int
	declare @glide nvarchar(100)
	declare @cSeed nvarchar(100)
	declare @maxn int 

	set @glide= (select GlideRule from VoucherNumber where CardNumber = @cardNum) 
	set @len = (Select GlideLen From VoucherNumber Where CardNumber=@cardNum ) 
	if(@glide is not null and @glide <>'')
	begin
		if(@glide = '年')
			set @cSeed = (CONVERT(varchar(4), @dDate, 112))
		if(@glide = '月')	
			set @cSeed = (CONVERT(varchar(6), @dDate, 112))
		if(@glide = '日' and @len <=6)
			set @cSeed = substring(CONVERT(varchar(8), @dDate, 112),3,6)  
		if(@glide = '日' and @len >6)
			set @cSeed = CONVERT(varchar(8), @dDate, 112) 
		select @maxn=max(cNumber) from VoucherHistory with (NOLOCK) Where  CardNumber=@cardNum and cContent=@cContent and cContentRule=@glide and cSeed=@cSeed
		if(@maxn is not null)
			set @num= 1+ @maxn
		else
		begin
			set @num=1
		end	
		exec P_InsetVoucheHistory @cardNum,@glide,@cSeed,@cContent
	end
	else
		set @num = 1+ (select cNumber as Maxnumber From VoucherHistory  with (NOLOCK) Where  CardNumber=@cardNum and cContent is NULL)

	SET @number = @number1+@number2+@number3+dbo.fn_FillNumberWithZero(@num,@len)

end

-- 生成录入日期 的单据编号 
create  PROCEDURE [dbo].[P_InsetVoucheHistory]    
@cardNum nvarchar(60),        
@glide nvarchar(100), 
@cSeed nvarchar(100),
@cContent nvarchar(100)         
AS  
/*
* Author: samrv,
* Create Date: 2023-07-25
* 生成单据编号
*/
if not exists (select * from VoucherHistory where CardNumber=@cardNum and cContent=@cContent and cContentRule=@glide)
begin   
   insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty)
   values (@cardNum,@cContent,@glide,@cSeed,0,0)
end
else
BEGIN
	update VoucherHistory set cNumber=cNumber+1 where CardNumber=@cardNum and cContent=@cContent and cContentRule=@glide and cSeed=@cSeed
END