ufrmexportbusinessdata.pas

抽象三层访问数据库示例

unit ufrmExportBusinessData;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Registry, IniFiles, typinfo, Buttons, Grids,
  DBGrids, DB, DBClient, uIDataAccess, uDataAccessHelper, uXLSReadWriteAgent,
  uIExcelAgent, uExcelAgentHelper, uCommon, uDataCopy,
  BIFFRecsII2, CellFormats2, XLSUtils2, XLSFonts2, ExtCtrls;

type
  TAddGrantType = (agtBusiness, agtProcess, agtRole, agtMenu, agtModule, agtBlock);

  TfrmExportBusinessData = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    rbtnEpfGJPlatformDB: TRadioButton;
    rbtnOtherDB: TRadioButton;
    lblDataBaseType: TLabel;
    cboDataBaseType: TComboBox;
    lblServerName: TLabel;
    cboDataBaseName: TComboBox;
    lblDataBaseName: TLabel;
    edtServerName: TEdit;
    lblUserName: TLabel;
    edtUserName: TEdit;
    lblPassword: TLabel;
    edtPassword: TEdit;
    cdsBusinessGrant: TClientDataSet;
    cdsBusinessConfig: TClientDataSet;
    Label1: TLabel;
    Label2: TLabel;
    edtExportFile: TEdit;
    btnExecute: TBitBtn;
    Button1: TButton;
    Bevel1: TBevel;
    Label3: TLabel;
    Bevel2: TBevel;
    Label4: TLabel;
    Bevel3: TBevel;
    Label5: TLabel;
    lblInfo: TLabel;
    cdsProcessConfig: TClientDataSet;
    cdsProcessOperates: TClientDataSet;
    mmBusinessNameOrID: TMemo;
    sbtnSelEmp: TSpeedButton;
    SaveDialog1: TSaveDialog;
    procedure rbtnEpfGJPlatformDBClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cboDataBaseTypeChange(Sender: TObject);
    procedure cboDataBaseNameChange(Sender: TObject);
    procedure cdsBusinessGrantNewRecord(DataSet: TDataSet);
    procedure btnExecuteClick(Sender: TObject);
    procedure cdsBusinessGrantAfterOpen(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
    procedure sbtnSelEmpClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
    FDataAccess: IDataAccess;
    FXLSReadWriteAgent: TXLSReadWriteAgent;
    function GetDataAccess: IDataAccess;
    function GetExcelAgent: IExcelAgent;
    function GetXLSReadWriteAgent: TXLSReadWriteAgent;
    property DataAccess: IDataAccess read GetDataAccess;
    property ExcelAgent: IExcelAgent read GetExcelAgent;
    property XLSReadWriteAgent: TXLSReadWriteAgent read GetXLSReadWriteAgent;
    procedure GetRegOracleServer;//获取本地注册的Oracle服务名列表
    procedure CreateBusinessGrantDataSet;//产生业务权限数据集
    procedure GetExportBusinessData(ABusinessNameOrIDs: string);//获取业务导出数据
    procedure ExportBusinessData(ABusinessNameOrIDs, AFileName: string);//导出业务数据
    procedure ShowExecuteInfo(AInfo: string);
  public
    { Public declarations }
  end;

var
  frmExportBusinessData: TfrmExportBusinessData;

implementation

{$R *.dfm}

procedure TfrmExportBusinessData.rbtnEpfGJPlatformDBClick(Sender: TObject);
begin
  cboDataBaseType.Enabled := rbtnOtherDB.Checked;
  lblDataBaseType.Enabled := cboDataBaseType.Enabled;
  lblServerName.Enabled := cboDataBaseType.Enabled;
  cboDataBaseName.Enabled := cboDataBaseType.Enabled;
  lblDataBaseName.Enabled := cboDataBaseType.Enabled;
  edtServerName.Enabled := cboDataBaseType.Enabled;
  lblUserName.Enabled := cboDataBaseType.Enabled;
  edtUserName.Enabled := cboDataBaseType.Enabled;
  lblPassword.Enabled := cboDataBaseType.Enabled;
  edtPassword.Enabled := cboDataBaseType.Enabled;
  FDataAccess := nil;
end;

procedure TfrmExportBusinessData.FormCreate(Sender: TObject);
begin
  rbtnEpfGJPlatformDB.Checked := true;
  rbtnEpfGJPlatformDBClick(rbtnEpfGJPlatformDB);
  cboDataBaseType.Items.Delimiter := ';';
  cboDataBaseType.Items.DelimitedText := 'Oracle;MSSQL';
  cboDataBaseType.ItemIndex := 0;
  cboDataBaseType.Style := csDropDownList;
  edtServerName.Text := '';
  cboDataBaseName.Items.Clear;
  cboDataBaseName.Text := '';
  GetRegOracleServer;
  edtUserName.Text := '';
  edtPassword.Text := '';
  mmBusinessNameOrID.Text := '';
  edtExportFile.Text := '';
  lblInfo.Caption := '';
  FDataAccess := nil;
end;

function TfrmExportBusinessData.GetDataAccess: IDataAccess;
  procedure GetPlatformDB;
  var
    tmpIni: TIniFile;
    strDataBaseSection: string;
  begin
    tmpIni := TIniFile.Create(GetSysDirectory + 'ServerDefine.ini');
    try
      strDataBaseSection := tmpIni.ReadString('SYSTEM', 'DataConnType', 'Oracle');
      if CompareText(strDataBaseSection, 'Oracle') = 0 then
      begin
        FDataAccess := TDataAccessHelper.GetDOADataAccess;
        FDataAccess.SetDBType('Oracle');
      end
      else
      begin
        FDataAccess := TDataAccessHelper.GetADODataAccess;
        FDataAccess.SetDBType('MSSQL');
      end;
      FDataAccess.SetDBHost(tmpIni.ReadString(strDataBaseSection, 'ServerName', ''));
      FDataAccess.SetDBName(tmpIni.ReadString(strDataBaseSection, 'DataName', ''));
      FDataAccess.SetDBUserName(tmpIni.ReadString(strDataBaseSection, 'UserID', ''));
      FDataAccess.SetDBPassword(tmpIni.ReadString(strDataBaseSection, 'Password', ''));
    finally
      tmpIni.Free;
    end;
  end;
begin
  if FDataAccess = nil then
  begin
    if rbtnEpfGJPlatformDB.Checked then
      GetPlatformDB
    else
    begin
      if cboDataBaseType.ItemIndex = 0 then
        FDataAccess := TDataAccessHelper.GetDOADataAccess
      else
        FDataAccess := TDataAccessHelper.GetADODataAccess;
      FDataAccess.SetDBType(cboDataBaseType.Text);
      FDataAccess.SetDBHost(edtServerName.Text);
      FDataAccess.SetDBName(cboDataBaseName.Text);
      FDataAccess.SetDBUserName(edtUserName.Text);
      FDataAccess.SetDBPassword(edtPassword.Text);
    end;
  end;
  Result := FDataAccess;
end;

procedure TfrmExportBusinessData.GetRegOracleServer;
var
  strOracleHome: string;

  function GetOracleHome: string;
  var
    tmpReg: TRegistry;
    strTemp: string;
  begin
    result := '';
    tmpReg := TRegistry.Create;
    try
      tmpReg.RootKey := HKEY_LOCAL_MACHINE;
      if tmpReg.OpenKey('SOFTWARE\ORACLE\ALL_HOMES', False) then
      begin
        strTemp := tmpReg.ReadString('LAST_HOME');
        if (strTemp <> '') and tmpReg.OpenKey('ID' + strTemp, false) then
        begin
          strTemp := tmpReg.ReadString('PATH');
          if strTemp <> '' then
          begin
            if strTemp[Length(strTemp)] = '\' then
              Delete(strTemp, Length(strTemp), 1);
            Result := strTemp;
          end;
        end;
        tmpReg.CloseKey;
      end;
    finally
      tmpReg.Free;
    end;
  end;

  procedure GetOracleList(ARegFileName: string);
  var
    tmpList: TStringList;
    i: integer;
    strRegName: string;
  begin
    //这里读取的方法暂时比较简单，该文件写法就要合理，否则读取不到。
    //以后再看有无更好的方法
    if not FileExists(ARegFileName) then
      exit;
    tmpList := TStringList.Create;
    try
      tmpList.LoadFromFile(ARegFileName);
      for i := 0 to tmpList.Count - 1 do
        if (Pos('(', tmpList[i]) = 0) and (Pos(')', tmpList[i]) = 0) then
        begin
          strRegName := Trim(tmpList[i]);
          if strRegName <> '' then
          begin
            if strRegName[1] = '#' then
              Continue;
            while Pos('=', strRegName) > 0 do
              Delete(strRegName, Pos('=', strRegName), MaxInt);
            strRegName := Trim(strRegName);
            if strRegName <> '' then
              cboDatabaseName.Items.Add(strRegName);
          end;
        end;
    finally
      tmpList.Free;
    end;
  end;
begin
  cboDatabaseName.Items.Clear;
  strOracleHome := GetOracleHome;
  if strOracleHome <> '' then
    GetOracleList(strOracleHome + '\network\admin\tnsnames.ora');
end;

procedure TfrmExportBusinessData.cboDataBaseTypeChange(Sender: TObject);
begin
  if cboDataBaseType.ItemIndex = 1 then
  begin
    cboDataBaseName.Items.Clear;
    cboDataBaseName.Style := csSimple;
  end
  else
  begin
    cboDataBaseName.Style := csDropDownList;
    GetRegOracleServer;
  end;
  FDataAccess := nil;
end;

procedure TfrmExportBusinessData.cboDataBaseNameChange(Sender: TObject);
begin
  FDataAccess := nil;
end;