[PHP知识库]mssql-记录-cp_sysconfig_dh_php 自动生成单号

CREATE procedure cp_sysconfig_dh_php         
/*                                        
参数：@guid_dll 单号类型；@machinecode 机器号，非POS赋值为''；                                                   
返回值：@outdh 生成的单号       
调用：需要生成单号的模块和存储过程                                                                                
实例：                    
declare @outdh varchar(50)                                         
exec cp_sysconfig_dh 'xskh','BCEF85B9-7B0F-4543-81CC-6AD54C5DA8DB','',@outdh out                                   
select @outdh      
drop procedure cp_sysconfig_dh                       
*/                                                                                  
@guid_dll varchar(255),      
@guid_bm varchar(50),        
@machinecode varchar(2)         
as        
begin        
  SET NOCOUNT ON
  declare @bmcode varchar(4),@ywdate DATETIME,
  @outdh varchar(50)     
  if LEN(@guid_bm)>40   --根据凯韵要求带业务日期过来      
  begin      
    select @ywdate=LEFT(@guid_bm,10)      
    select @guid_bm=SUBSTRING(@guid_bm,11,50)      
  end else      
  begin      
    select @ywdate=GETDATE()      
  end      
  select @bmcode=''          
  if exists(select * from pubbm where aguid=@guid_bm)        
  begin        
    select @bmcode=left(acode,4) from pubbm where aguid=@guid_bm      
  end else        
  begin        
    raiserror 88888 '部门选择错误'      
    return -1        
  end          
  select @bmcode=replicate('0',4-len(@bmcode))+@bmcode        
  --取得起作用的序号生成规则,前导        
  declare @guid_dllxh int,@guid_dllpre varchar(50)        
  select @guid_dllxh=typexh,@guid_dllpre=pre from sysconfig_dh where guid_dll=@guid_dll         
  if @guid_dllxh is null  --如果以单据类型无法查出，则再以 前导 查询        
    select @guid_dllxh=typexh,@guid_dllpre=pre from sysconfig_dh where pre=@guid_dll         
      
  if @guid_dllxh is null  --如果以 前导 也查不出来，则插入一条记录到单号规则表        
    insert into sysconfig_dh(aguid,guid_dll,typexh,pre,remark) values(newid(),@guid_dll,8,'','自动调用生成')        
      
  --print @guid_dllxh        
      
  --print @guid_dllpre        
      
  if isnull(@machinecode,'')=''        
    select @guid_dllxh=isnull(@guid_dllxh,8)  --没有定义规则的其他单据类型统一用第六类规则：前导(2)+年月号(4)+序(6)        
  else        
    select @guid_dllxh=isnull(@guid_dllxh,1)  --没有定义规则的零售单据类型统一用第一类规则：yymmdd+部门序号(4)+机号(2)+序号(6)        
  select @guid_dllpre=isnull(@guid_dllpre,'')        
  declare @dhleft varchar(50),@dhright varchar(50),@itemtmp varchar(50),@maxxh varchar(50)         
  --print @guid_dllxh      
  --yymmdd+部门序号(4)+机号(2)+序号(6)        
  if @guid_dllxh=1        
  begin        
    select @dhleft=convert(varchar(10),@ywdate,12)+@bmcode+@machinecode,@dhright=''        
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',6-len(@maxxh))+@maxxh+@dhright        
  end        
      
  --年号(2)+部门序号(4)+机号(2)+序号(8)        
  if @guid_dllxh=2        
  begin        
    select @dhleft=right(datename(yy,@ywdate),2)+@bmcode+@machinecode,@dhright=''         
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',8-len(@maxxh))+@maxxh+@dhright         
  end        
      
  --年月号(4)+部门序号(4)+机号(2)+序号(6)        
  if @guid_dllxh=3        
  begin        
    select @dhleft=left(convert(varchar(10),@ywdate,12),4)+@bmcode+@machinecode,@dhright=''        
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',6-len(@maxxh))+@maxxh+@dhright          
  end        
      
  --前导(2)+yymmdd+部门序号(4)+序号(6)        
  if @guid_dllxh=4        
  begin        
    select @dhleft=@guid_dllpre+convert(varchar(10),@ywdate,12)+@bmcode,@dhright=''        
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',6-len(@maxxh))+@maxxh+@dhright        
  end        
      
  --前导(2)+年号(2)+部门序号(4)+序号(8)        
  if @guid_dllxh=5        
  begin        
    select @dhleft=@guid_dllpre+right(datename(yy,@ywdate),2)+@bmcode,@dhright=''         
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',8-len(@maxxh))+@maxxh+@dhright         
  end        
      
  --前导(2)+年月号(4)+部门序号(4)+序号(6)        
  if @guid_dllxh=6        
  begin        
    select @dhleft=@guid_dllpre+left(convert(varchar(10),@ywdate,12),4)+@bmcode,@dhright=''        
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',6-len(@maxxh))+@maxxh+@dhright          
  end        
      
  --部门序号(4)+序号(7)        
  if @guid_dllxh=7        
  begin        
    select @dhleft=@bmcode,@dhright=''        
    select @itemtmp=@dhleft+@dhright        
    exec cp_sysconfig_dhxh @guid_dll,@itemtmp,@maxxh out        
    select @outdh=@dhleft+replicate('0',5-len(@maxxh))+@maxxh+@dhright          
  end        
      
  --前导(2)+年月号(4)+序号(6)        
  if @guid_dllxh=8       
  begin          
    select @dhleft=@guid_dllpre+right(convert(varchar(10),@ywdate,112),4),@dhright=''        
    select @itemtmp=@dhleft+@dhright        
      
    --print 'a'      
    --print @guid_dll      
    --print 'b'        
    ----print @dhleft      
    --print @itemtmp      
    --print 'c'        
    --replicate('0',5-len(@maxxh)) 这句可能引起null 如果len(@maxxh)>5      
      
    exec cp_sysconfig_dhxh_3 @guid_dll,@itemtmp,@maxxh out       
    select @outdh=@dhleft+replicate('0',3-len(@maxxh))+@maxxh+@dhright        
      
    --if @outdh is null      
    --print 'e'       
  end       
        
  --前导(2)+序号(6)        
  if @guid_dllxh=9       
  begin          
    select @dhleft=@guid_dllpre,@dhright=''        
    select @itemtmp=@dhleft+@dhright      
    exec cp_sysconfig_dhxh_jlf @guid_dll,@itemtmp,@maxxh out       
    select @outdh=@dhleft+replicate('0',8-len(@maxxh))+@maxxh+@dhright      
  end   
  
  SELECT  @outdh AS acode     
end