udata.pas

人事档案管理

unit uData;

interface

uses
  SysUtils, Classes, DB, ADODB, Variants;

type
  TdmPer = class(TDataModule)
    acPer: TADOConnection;
    aqSta: TADOQuery;
    dsSta: TDataSource;
    aqDept: TADOQuery;
    dsDept: TDataSource;
    aqExper: TADOQuery;
    dsExper: TDataSource;
    aqFami: TADOQuery;
    dsFami: TDataSource;
    aqAP: TADOQuery;
    dsAP: TDataSource;
    aqTrain: TADOQuery;
    dsTrain: TDataSource;
    aqStaf: TADOQuery;
    aqExperf: TADOQuery;
    aqAPf: TADOQuery;
    aqFamif: TADOQuery;
    aqTrainf: TADOQuery;
    aqStat: TADOQuery;
    dsFrt: TDataSource;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure OpenQuery(AADOQuery: TADOQuery; ASQlStr: string);
    procedure ExecSQL(ASQlStr: string);
    procedure OpenDept;
    procedure OpenSta;
    procedure OpenExper;
    procedure OpenFami;
    procedure OpenAP;
    procedure OpenTrain;
    procedure OpenStat(AWhr, AGrpStr: string);

    procedure OpenStaf(AIds: string);
    procedure OpenExperf;
    procedure OpenFamif;
    procedure OpenAPf;
    procedure OpenTrainf;
    
    procedure GetDeptText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure SetDeptText(Sender: TField; const Text: String);
    procedure GetOtherText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure SetOtherText(Sender: TField; const Text: String);
  end;

var
  dmPer: TdmPer;

implementation

{$R *.dfm}

{ TdmPer }

procedure TdmPer.DataModuleCreate(Sender: TObject);
begin
  if acPer.Connected = False then acPer.Open;
  if not aqSta.Active then OpenSta;
  aqSta.FieldByName('depid').OnGetText := GetDeptText;
  aqSta.FieldByName('depid').OnSetText := SetDeptText;
  if not aqAP.Active then OpenAP;
  aqAP.FieldByName('dep').OnGetText := GetDeptText;
  aqAP.FieldByName('dep').OnSetText := SetDeptText;
end;

procedure TdmPer.DataModuleDestroy(Sender: TObject);
begin
  if acPer.Connected then acPer.Close;
end;

procedure TdmPer.OpenQuery(AADOQuery: TADOQuery; ASqlStr: string);
begin
  with AADOQuery do
  begin
    if Active then Close;
    Connection := acPer;
    LockType := ltBatchOptimistic;
    SQL.Clear;
    SQL.Text := ASqlStr;
    Open;
  end;
end;

procedure TdmPer.ExecSQL(ASQlStr: string);
var
  aqExec: TADOQuery;
begin
  aqExec := TADOQuery.Create(self);
  with aqExec do
  begin
    Connection := acPer;
    SQL.Clear;
    SQL.Text := ASqlStr;
    ExecSQL;
  end;
  aqExec.Free;
end;

procedure TdmPer.OpenDept;
begin
  OpenQuery(aqDept, 'select id, dep from dept');
end;

procedure TdmPer.OpenSta;
begin
  OpenQuery(aqSta, 'select * from staInfo order by id');
end;

procedure TdmPer.OpenExper;
begin
  OpenQuery(aqExper, 'select * from wexper');
end;

procedure TdmPer.OpenFami;
begin
  OpenQuery(aqFami, 'select * from family');
end;

procedure TdmPer.OpenAP;
begin
  OpenQuery(aqAP, 'select * from aandp');
end;

procedure TdmPer.OpenTrain;
begin
  OpenQuery(aqTrain, 'select * from train');
end;

{统计所需过程 Access不支持Group By All查询语句}
procedure TdmPer.OpenStat(AWhr, AGrpStr: string);
begin
  OpenQuery(aqStat, 'select '+ aGrpStr +', count(id) as sCount from stainfo where 0=0 '
    + AWhr + ' group by '+ AGrpStr);
end;

{报表开始}
procedure TdmPer.OpenStaf(AIds: string);
begin
  OpenQuery(aqStaf, 'select * from staInfo where id in (' + AIds + ')');
end;

procedure TdmPer.OpenExperf;
begin
  OpenQuery(aqExperf, 'select * from wexper where sid=:id');
end;

procedure TdmPer.OpenFamif;
begin
  OpenQuery(aqFamif, 'select * from family where sid=:id');
end;

procedure TdmPer.OpenAPf;
begin
  OpenQuery(aqAPf, 'select * from aandp where sid=:id');
end;

procedure TdmPer.OpenTrainf;
begin
  OpenQuery(aqTrainf, 'select * from train where sid=:id');
end;
{报表结束}

procedure TdmPer.GetDeptText(Sender: TField; var Text: String;
  DisplayText: Boolean);
var
  aqGetDep: TADOQuery;
begin
  if not aqDept.Active then dmPer.OpenDept;
  aqGetDep := TADOQuery.Create(Self);
  aqGetDep.Clone(aqDept, ltReadOnly);
  Text := VarToStr(aqGetDep.Lookup('id', Sender.AsInteger, 'dep'));
  aqGetDep.Free;
end;

procedure TdmPer.SetDeptText(Sender: TField; const Text: String);
var
  aqSetDep: TADOQuery;
begin
  if not aqDept.Active then dmPer.OpenDept;
  aqSetDep := TADOQuery.Create(Self);
  aqSetDep.Clone(aqDept, ltReadOnly);
  Sender.AsString := aqSetDep.Lookup('dep', Text, 'id');
  aqSetDep.Free;
end;

procedure TdmPer.GetOtherText(Sender: TField; var Text: String;
  DisplayText: Boolean);
var
  aqGetOther: TADOQuery;
begin
  aqGetOther := TADOQuery.Create(Self);
  OpenQuery(aqGetOther, 'select * from oinfo');
  Text := VarToStr(aqGetOther.Lookup('id', Sender.AsInteger, 'sname'));
  aqGetOther.Free;
end;

procedure TdmPer.SetOtherText(Sender: TField; const Text: String);
var
  aqSetOther: TADOQuery;
begin
  aqSetOther := TADOQuery.Create(Self);
  OpenQuery(aqSetOther, 'select * from oinfo');
  Sender.AsString := aqSetOther.Lookup('sname', Text, 'id');
  aqSetOther.Free;
end;

end.