global.~pas

在打沙场用的最简单

unit Global;

interface

uses Classes, Windows, Sysutils, Variants, Globals, ComObj, DBclient, DB, 
      Execsqlclass, SelectsqlClass;

type
  TGlobal = Class(TinterfacedObject,IGlobals)
  private
    aselectsql:Tselectsql;
    aexecsql:Texecsql;
    function vartosql(value:variant):string;
  protected
  public
    constructor create();
    destructor destroy();override;
    function login(const usercode, password: WideString): Integer; safecall;
    function getusername(const usercode, password: WideString): WideString;
      safecall;
    function getadmin(const usercode, password: WideString): WordBool;
      safecall;
    function getapptitle: WideString; safecall;
    function getgroupid(const usercode, username: WideString): Integer;
      safecall;
    function syslog(const fform, fevent, fuser, fpcname: WideString): Integer;
      safecall;
    function execsql(const cmdstr: WideString): WordBool; safecall;
    function getlargedata(const psql: WideString;
      precCount: Integer): OleVariant; safecall;
    function applyupdata(pdelta: OleVariant; const ptablename,
      pkeyfield: WideString): WordBool; safecall;
    function getmaxid(const ptablename, pkeyfield: WideString): Integer;
      safecall;
    function isunique(const ptablename, pkeyfield,
      pkeyvalue: WideString): WordBool; safecall;
    function getnumber(pBilltypeid: Integer): WideString; safecall;
    function getinnunber(pbilltypeid: Integer): Integer; safecall;
    function purchasedetail(pitemid: Integer): OleVariant; safecall;    
end;

implementation

uses Datamodule, Servers;

{ TGlobal }

function TGlobal.applyupdata(pdelta: OleVariant; const ptablename,
  pkeyfield: WideString): WordBool;
var
i:integer;
s1,s2:String;
Cmdstr:string;
FieldList:TstringList;
Cdsupdate:Tclientdataset;
begin
if VarisNull(pdelta) then Exit;
  try
    with Data.ADOConnection do
      begin
        FieldList:=Tstringlist.Create;
        GetFieldNames(pTablename,FieldList);
      end;
    Cdsupdate:=Tclientdataset.Create(nil);
    Cdsupdate.Close;
    Cdsupdate.Data:=pdelta;
    if not Cdsupdate.Active then Cdsupdate.Open;
    for i:=1 to fieldList.Count do
      if Cdsupdate.FindField(FieldList[i-1])<>nil then
        cdsupdate.FindField(FieldList[i-1]).tag:=1;
    FieldList.Free;

    if CDSupdate.RecordCount>0 then
      begin
        CDSupdate.First;
        s1:='';
        s2:='';
        while not CDSupdate.Eof do
          begin
            CmdStr:='';
            case Cdsupdate.UpdateStatus of
              usUnmodified:
                begin
                  S2:=VartoSql(cdsupdate[pkeyField]);
                end;
              usModified:
                begin
                  s1:='';
                  for i:=1 to CDSupdate.FieldCount do
                    if (not cdsupdate.Fields[i-1].isNull) and (Cdsupdate.Fields[i-1].tag=1) then
                      begin
                        if s1='' then
                          s1:=Trim(CDSupdate.Fields[i-1].FieldName)+' = '+vartosql(Cdsupdate.Fields[i-1].value)
                          else
                          s1:=s1+','+Trim(CDSupdate.Fields[i-1].FieldName)+' = '+vartosql(Cdsupdate.Fields[i-1].value);
                      end;
                    if s1<>'' then
                      begin
                        CmdStr:=' update '+ptablename+' set '+s1+' where '+pkeyField+' = '+s2;
                      end;
                end;
              usInserted:
                begin
                  s1:='';
                  s2:='';
                  for i:=1 to Cdsupdate.FieldCount do
                    if (not Cdsupdate.Fields[i-1].isnull) and (cdsupdate.Fields[i-1].tag=1) then
                      begin
                        if s1='' then
                          begin
                            s1:=Trim(Cdsupdate.Fields[i-1].FieldName);
                            s2:=Vartosql(Cdsupdate.Fields[i-1].value);
                          end
                          else
                          begin
                            s1:=s1+','+Trim(Cdsupdate.Fields[i-1].FieldName);
                            s2:=s2+','+Vartosql(Cdsupdate.Fields[i-1].value);
                          end;
                      end;
                    if s1<>'' then
                      begin
                        CmdStr:=' Insert into '+pTablename+'('+s1+') values('+s2+')';
                      end;
                end;
              usDeleted:
                begin
                  s2:=Vartosql(Cdsupdate[pkeyField]);
                  CmdStr:='Delete '+ptablename+' where '+pkeyField+'='+s2;
                end;
            end;
            if CmdStr<>'' then
              if Execsql(Cmdstr) then
                Result:=True
                else
                Result:=False;

            Cdsupdate.Next;
          end;
      end;
  finally
    Cdsupdate.Close;
    Cdsupdate.Free();
  end;
end;

constructor TGlobal.create;
begin
{do nothing};
end;

destructor TGlobal.destroy;
begin
{do nothing};
  inherited;
end;

function TGlobal.execsql(const cmdstr: WideString): WordBool;
begin
try
  try
    aexecsql:=Texecsql.Create(data.ADOConnection,data.Qcommon,frmservers.sc,cmdstr);
    aexecsql.Execute;
    Result:=True;
  except;
    Result:=False;
  end;
finally
  data.Qcommon.Close;
  data.Qcommon.SQL.Clear;
  aexecsql.Free;
end;
end;

function TGlobal.getadmin(const usercode, password: WideString): WordBool;
var
  cmdStr:string;
begin
  try
    cmdstr:='Select fadmin from tuser where fusercode='+Quotedstr(usercode)+
            ' and fpassword='+Quotedstr(password);
    aselectsql:=Tselectsql.create(data.ADOConnection,data.Qcommon,frmServers.sc,cmdStr);
    aselectsql.Execute;
    if data.Qcommon.RecordCount > 0 then
      Result:=data.Qcommon.fieldbyname('fadmin').AsBoolean
      else
      Result:=False;

  finally
    data.Qcommon.Close;
    data.Qcommon.SQL.Clear;
    aselectsql.Free;
  end;
end;

function TGlobal.getapptitle: WideString;
begin
  if Trim(frmServers.Systemname)<>'' then
    Result:=frmServers.Systemname
    else
    Result:='NULL';  
end;

function TGlobal.getgroupid(const usercode, username: WideString): Integer;
var
  cmdStr:string;
begin
  try
    cmdstr:='Select fgroupid from tuser where fusercode='+Quotedstr(usercode)+
            ' and fpassword='+Quotedstr(username);
    aselectsql:=Tselectsql.create(data.ADOConnection,data.Qcommon,frmServers.sc,cmdStr);
    aselectsql.Execute;
    if data.Qcommon.RecordCount > 0 then
      Result:=data.Qcommon.fieldbyname('fgroupid').AsInteger
      else
      Result:=0;

  finally
    data.Qcommon.Close;
    data.Qcommon.SQL.Clear;
    aselectsql.Free;
  end;
end;

function TGlobal.getinnunber(pbilltypeid: Integer): Integer;
begin
  Result:=0;
  try
    with Data.Sgetinnumber do
      begin
        parameters.ParamValues['@billtype']:=pBilltypeid;
        Execproc;
      end;
  Except
    abort;
  end;
  Result:=Data.Sgetinnumber.Parameters.ParamValues['@InNumBer'];
end;

function TGlobal.getlargedata(const psql: WideString;
  precCount: Integer): OleVariant;
begin
  try
    Data.QGetdata.Close;
    Data.QGetdata.SQL.Clear;
    Data.QGetdata.SQL.Text:=psql;
    Data.QGetdata.MaxRecords:=precCount;
    Data.CDSgetdata.Active:=False;
    Data.CDSgetdata.FetchOnDemand:=False;
    Data.CDSgetdata.PacketRecords:=20;
    Data.CDSgetdata.Active:=True;
    Result:=Data.CDSgetdata.Data;
  finally
    Data.QGetdata.Close;
    Data.QGetdata.SQL.Clear;
    Data.QGetdata.MaxRecords:=0;
    Data.CDSgetdata.Active:=False;
    Data.CDSgetdata.PacketRecords:=-1;
  end;
end;

function TGlobal.getmaxid(const ptablename,
  pkeyfield: WideString): Integer;
var
  cmdStr:string;
begin
  try
    cmdStr:='Select '+pkeyfield+' from '+ptablename+
            ' Where '+pkeyfield+'=(Select max('+pkeyfield+
            ') from '+ptablename+')';
    aselectsql:=Tselectsql.create(data.ADOConnection,data.Qcommon,frmservers.sc,cmdStr);
    aselectsql.Execute;
    if data.Qcommon.RecordCount > 0 then
      Result:=data.Qcommon.fieldbyname(pkeyfield).AsInteger
    else
      Result:=0;
  finally
    data.Qcommon.Close;
    data.Qcommon.SQL.Clear;
    aselectsql.Free;
  end;
end;

function TGlobal.getnumber(pBilltypeid: Integer): WideString;
begin
  Result:='';
  try
    with Data.Sgetoutnumber do
      begin
        parameters.ParamValues['@billtype']:=pBilltypeid;
        Execproc;
      end;
  Except
    abort;
  end;
  Result:=Trim(Data.Sgetoutnumber.Parameters.ParamValues['@outNumber']);
end;

function TGlobal.getusername(const usercode,
  password: WideString): WideString;
var
  cmdStr:string;
begin
  try
    cmdstr:='Select fusername from tuser where fusercode='+Quotedstr(usercode)+
            ' and fpassword='+Quotedstr(password);
    aselectsql:=Tselectsql.create(data.ADOConnection,data.Qcommon,frmServers.sc,cmdStr);
    aselectsql.Execute;
    if data.Qcommon.RecordCount > 0 then
      Result:=data.Qcommon.fieldbyname('fusername').AsString
      else
      Result:='NULL';

  finally
    data.Qcommon.Close;
    data.Qcommon.SQL.Clear;
    aselectsql.Free;
  end;
end;

function TGlobal.isunique(const ptablename, pkeyfield,
  pkeyvalue: WideString): WordBool;
var
  cmdStr:string;
begin
  try
  cmdstr:='Select '+pkeyfield+' from '+ptablename+' where '+
                     pkeyfield+'='+vartosql(pkeyValue);
  aselectsql:=Tselectsql.create(data.ADOConnection,data.Qcommon,frmServers.sc,cmdStr);
  aselectsql.Execute;
  if Data.Qcommon.RecordCount>0 then
    Result:=True
    else
    Result:=False;
  finally
    data.Qcommon.Close;
    data.Qcommon.SQL.Clear;
    aselectsql.Free;
  end;
end;

function TGlobal.login(const usercode, password: WideString): Integer;
var
  cmdStr:string;
begin
  try
    cmdstr:='Select fid from tuser where fusercode='+Quotedstr(usercode)+
            ' and fpassword='+Quotedstr(password);
    aselectsql:=Tselectsql.create(data.ADOConnection,data.Qcommon,frmServers.sc,cmdStr);
    aselectsql.Execute;
    if data.Qcommon.RecordCount > 0 then
      Result:=data.Qcommon.fieldbyname('fid').AsInteger
      else
      Result:=0;

  finally
    data.Qcommon.Close;
    data.Qcommon.SQL.Clear;
    aselectsql.Free;
  end;
end;

function TGlobal.purchasedetail(pitemid: Integer): OleVariant;
begin
try
  try
    with Data.spurchasedetail do
      begin
        parameters.ParamValues['@totalitemid']:=pitemid;
        Execproc;
      end;
    Data.CDSpurchaseDetail.Active:=True;
    Result:=Data.CDSpurchasedetail.Data;
  Except
    Result:=null;
    abort;
  end;
finally
  Data.CDSpurchasedetail.FetchOnDemand:=True;
  Data.CDSpurchasedetail.PacketRecords:=-1;
  Data.CDSpurchasedetail.Active:=False;
  Data.Spurchasedetail.MaxRecords:=0;
  Data.Spurchasedetail.Close;  
end;
end;

function TGlobal.syslog(const fform, fevent, fuser,
  fpcname: WideString): Integer;
var
  cmdstr:string;
begin
  cmdstr:='Insert into tsyslog(ftype,ftime,fform,fevent,fuser,fpcname) values('
          +vartosql('Normal')+','
          +vartosql(now())+','
          +vartosql(fform)+','
          +vartosql(fevent)+','
          +vartosql(fuser)+','
          +vartosql(fpcname)+')';
  Execsql(cmdstr);            
end;

function TGlobal.vartosql(value: variant): string;
begin
  if varisnull(Value) then
    Result:='NULL'
    else
    case Vartype(value) of
      varDate:
        Result:=Quotedstr(Datetimetostr(VartoDatetime(Value)));
      varString,varOlestr:
        Result:=Quotedstr(Trim(Vartostr(Value)));
      varboolean:
        begin
          if Value then
            Result:='1'
            else
            Result:='0';
        end;
      else
        Result:=Quotedstr(Trim(Vartostr(Value)));
      end;
end;

end.