我也做進銷存,下面是我寫的代碼: procedure Tqmsin300.Qyqms300AfterInsert(DataSet: TDataSet); var year,month,day:string; i,j,q:integer; s1,s:string; begin inherited; s1:=datetostr(date); year :=copy(s1,1,4); case length(s1) of 8:begin month :='0'+copy(s1 ,6,1); day :='0'+copy(s1 ,8,1); end; 9:begin if copy(s1 ,7,1)='/' then begin month :='0'+copy(s1 ,6,1); day :=copy(s1 ,8,2); end else begin month :=copy(s1 ,6,2); day :='0'+copy(s1 ,9,1); end; end; 10:begin month :=copy(s1,6,2); day :=copy(s1,9,2); end; end; ///////////////// Qyqms300max.Close ; Qyqms300max.SQL.Clear ; Qyqms300max.SQL.Add('select max(checkno) from qms300'); Qyqms300max.Open ; if Qyqms300max.fields[0].AsString<>'' then begin s:=copy(Qyqms300max.fields[0].AsString,11,3); q:=strtoint(s)+1; i:=2-length(inttostr(q)); s:=inttostr(q); for j:=1 to i do begin s:='0'+s; end; Qyqms300.FieldByName('checkno').AsString :='DB_'+year+month+day++'_'+s; end else begin Qyqms300.FieldByName('checkno').AsString :='DB_'+year+month+day+'_'+'001'; end; end;
放置号码的表说明: 有三个字:日期字段;fnbh 用来标志是哪一类单据的号(如入库单据为1,出库单据为2);当前号 下面存储过程的参数@BH 就是单据的类别编号CREATE PROCEDURE getcfh @bh int AS declare @t integer,@c smalldatetime select @t=当前号,@c=getdate() from 放置号码的表 where fnbh=@bh and (datediff(day,号码表的日期字段,getdate())=0) if not(@t is null)--既是存在记录值 begin update 放置号码的表 set 当前号=当前号+1 where fnbh=@bh and (datediff(day,号码表的日期字段,@c)=0) select convert(varchar,getdate(),12)+convert(varchar,@t+1) as 新号码 end else begin if exists(select 当前号 from 放置号码的表 where fnbh=@bh) update 放置号码的表 set 当前号='1',号码表的日期字段=getdate() where fnbh=@bh else begin select @c=getdate() insert into 放置号码的表(fcdjmc,当前号,fnbh,号码表的日期字段) values(@leirong,'1',@bh,@c) end select convert(varchar,getdate(),12)+'1' as 新号码 end
以下语句添加时得到自增的序号 select 'DB-'+datename(year,getdate())+datename(month,getdate())+datename(day,getdateselect 'DB-'+datename(year,getdate())+datename(month,getdate())+datename(day,getdate())+'_'+substring(convert(char(4),convert(int,isnull(max(substring(xh,13,3)),0))+1+1000),2,3) as xh from 表1 where substring(xh,4,8)=datename(year,getdate())+datename(month,getdate())+datename(day,getdate())
procedure Tqmsin300.Qyqms300AfterInsert(DataSet: TDataSet);
var year,month,day:string;
i,j,q:integer;
s1,s:string;
begin
inherited;
s1:=datetostr(date);
year :=copy(s1,1,4);
case length(s1) of
8:begin
month :='0'+copy(s1 ,6,1);
day :='0'+copy(s1 ,8,1);
end;
9:begin
if copy(s1 ,7,1)='/' then
begin
month :='0'+copy(s1 ,6,1);
day :=copy(s1 ,8,2);
end
else
begin
month :=copy(s1 ,6,2);
day :='0'+copy(s1 ,9,1);
end;
end;
10:begin
month :=copy(s1,6,2);
day :=copy(s1,9,2);
end;
end;
/////////////////
Qyqms300max.Close ;
Qyqms300max.SQL.Clear ;
Qyqms300max.SQL.Add('select max(checkno) from qms300');
Qyqms300max.Open ;
if Qyqms300max.fields[0].AsString<>'' then
begin
s:=copy(Qyqms300max.fields[0].AsString,11,3);
q:=strtoint(s)+1;
i:=2-length(inttostr(q));
s:=inttostr(q);
for j:=1 to i do
begin
s:='0'+s;
end;
Qyqms300.FieldByName('checkno').AsString :='DB_'+year+month+day++'_'+s;
end
else
begin
Qyqms300.FieldByName('checkno').AsString :='DB_'+year+month+day+'_'+'001';
end;
end;
有三个字:日期字段;fnbh 用来标志是哪一类单据的号(如入库单据为1,出库单据为2);当前号
下面存储过程的参数@BH 就是单据的类别编号CREATE PROCEDURE getcfh
@bh int
AS
declare @t integer,@c smalldatetime
select @t=当前号,@c=getdate() from 放置号码的表 where fnbh=@bh and (datediff(day,号码表的日期字段,getdate())=0)
if not(@t is null)--既是存在记录值
begin
update 放置号码的表 set 当前号=当前号+1 where fnbh=@bh and (datediff(day,号码表的日期字段,@c)=0)
select convert(varchar,getdate(),12)+convert(varchar,@t+1) as 新号码
end
else
begin
if exists(select 当前号 from 放置号码的表 where fnbh=@bh)
update 放置号码的表 set 当前号='1',号码表的日期字段=getdate() where fnbh=@bh
else
begin
select @c=getdate()
insert into 放置号码的表(fcdjmc,当前号,fnbh,号码表的日期字段) values(@leirong,'1',@bh,@c)
end
select convert(varchar,getdate(),12)+'1' as 新号码
end
select
'DB-'+datename(year,getdate())+datename(month,getdate())+datename(day,getdateselect 'DB-'+datename(year,getdate())+datename(month,getdate())+datename(day,getdate())+'_'+substring(convert(char(4),convert(int,isnull(max(substring(xh,13,3)),0))+1+1000),2,3) as xh from 表1 where substring(xh,4,8)=datename(year,getdate())+datename(month,getdate())+datename(day,getdate())