国庆节连一天假都没得放,很不爽,散分!
ilang 2002-09-27 05:38:20 TMD的那个王八蛋地中海居然还说有项目奖加班费就免了,想马上走人了,散分
同时也把经常会碰到的一些问题贴出来和大家交流一下,这些方法感觉不大理想,我想了解一下大家通常是怎么处理的
{~~~~~~~~~~~~~~~~~~~ 取得下一个单号~~~~~~~~~~~~~~~~~~~~~~~~~ }
{ parameters:iFlag := 0 只读出单号 iFlag := 1 更新下一个单号 }
function TFrmEdit.GetSerialNo(cds: TClientDataSet; iFlag: Integer): OleVariant;
var
sTemp, sTableName: string;
cdsTemp: TClientDataSet;
begin
sTableName := GetTableNameByCds(cds);
sTemp := 'Declare @No varchar(20) ' + #13 + 'Exec GetNo @No output , %s ,%d '
+ #13 + 'select @No as SerialNo';
sTemp := Format(sTemp, [sTableName, iFlag]);
cdsTemp := TClientDataSet.Create(Self);
with cdsTemp do
begin
RemoteServer := (Self.FindComponent('cdsX') as TClientDataSet).RemoteServer;
ProviderName := (Self.FindComponent('cdsX') as TClientDataSet).ProviderName;
SetCdsCmdTxt(cdsTemp, sTemp, 2);
Result := cdsTemp.FieldByName('SerialNo').AsString;
Free;
end;
end;
{ 设置ClientDataSet组件CommandText属性 }
procedure TFrmEdit.SetcdsCmdTxt(cds: TClientDataSet; StrSQL: string;
iFlag: Integer);
begin
with cds do
begin
if Active then Close;
CommandText := StrSQL;
if iFlag = 1 then Execute
else if iFlag = 2 then Open
else
begin
Execute;
Open;
end;
end;
end;
{ 从简单的SQL语句取得数据表名称 }
function TFrmEdit.GetTableNameFromSQLText(SQLTxt: string): string;
var
I: Integer;
sTemp: string;
begin
I := Pos('from', LowerCase(SQLTxt)) + 4;
sTemp := TrimLeft(Copy(SQLTxt, I, StrLen(PChar(SQLTxt))));
I := Pos(' ', sTemp);
if I = 0 then
Result := sTemp
else
Result := Copy(sTemp, 0, I);
end;
{ 从ClientDataSet组件CommandText属性取得连接的数据表名称 }
function TFrmEdit.GetTableNameByCds(cds: TClientDataSet): string;
var
StrSQL: string;
begin
StrSQL := cds.CommandText;
Result := GetTableNameFromSQLText(StrSQL);
end;
{说明:建立附加数据表PutNo,根据数据表名称读取下一个单号
SQL脚本
Create Table PutNo(sNo Varchar(12), sTableName Varchar(20), ab Varchar(20), sExplain Varchar(20))
--参数说明 sNo:日期码 sTableName:数据表名称 ab:区分码 sExplain:中文说明
--例:在数据表写入一条记录:200209010001, EmployeeData,YGZLB,员工资料表
执行存储过程传入表名称参数’EmployeeData’,则返回单号’ YGZLB200209010001’
因考虑到用户处理数据的过程中该表单的最新单号有可能已更改,所以在用户提交数据之前需重新读取一次,若新表单保存完毕,则更新下一个单号,例:
procedure TFrmEdit.GetSerialNoEx(iFlag: Integer);
begin
cdsX.FieldByName('sSerialNo').AsString := GetSerialNo(cdsX, iFlag);
end;
procedure TFrmEdit.cdsXBeforePost(DataSet: TDataSet);
begin
if cdsX.State = dsInsert then GetSerialNoEx(0);
end;
procedure TFrmEdit.cdsXAfterInsert(DataSet: TDataSet);
begin
GetSerialNoEx(0);
end;
procedure TFrmEdit.cdsXAfterApplyUpdates(Sender: TObject;
var OwnerData: OleVariant);
begin
GetSerialNo(cdsX, 1);
end;}
{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}
创建存储过程GetNo SQL脚本
CREATE PROCEDURE GetNo
@No varchar(20) output ,
@TableName varchar(50),
@Flag int
AS
declare @Year Int,
@Month int,
-- @Day int,
@Temp_No varchar(10),
@RandNo varchar(4),
@No1 varchar(10),
@ab Varchar(30)
BEGIN
select @year=Year(GetDate())
select @Month=Month(GetDate())
select @No=Str(@Year,4,0)+
(select
case
when @Month>=10 then Str(@Month,2,0)
when @Month<10 Then '0'+Str(@Month,1,0)
end
)
select @tablename = Rtrim(@tablename)
select @tablename = Ltrim(@tablename)
SELECT @Temp_No = sNo FROM Number where sTableName= @TableName
SELECT @ab = ab FROM Number where sTableName= @TableName
IF @No <> SUBSTRING( @Temp_No,1,6)
begin
SELECT @No1 = @No+'0001'
SELECT @No = @ab+@No+'0001'
end
ELSE
BEGIN
SELECT @RandNo = STR( CONVERT(int,(SUBSTRING( @Temp_No ,7,4) )+1) , 4, 0 )
SELECT @RandNo = REPLACE( @RandNo,' ','0')
SELECT @No = @No + @RandNo
SELECT @No1 = @No
SELECT @No =@ab + @No
END
if @Flag=1
UPDATE Number SET sNo = @No1 where sTableName = @TableName
END
GO
{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}