unitmain.pas

数据仓库维表及参照表维护系统

unit Unitmain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, jpeg, DB, ADODB, DBXpress, Grids, DBGrids,
  DBClient, SimpleDS, SqlExpr, FMTBcd, DBCtrls;

type
  Tmain = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Label1: TLabel;
    Button1: TButton;
    Button2: TButton;
    Panel4: TPanel;
    Button3: TButton;
    Panel5: TPanel;
    Panel6: TPanel;
    Image1: TImage;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Image2: TImage;
    Button8: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    DataSource3: TDataSource;
    DataSource4: TDataSource;
    StaticText2: TStaticText;
    Panel9: TPanel;
    Note: TStaticText;
    Panel7: TPanel;
    StaticText1: TStaticText;
    DBGrid3: TDBGrid;
    DBGrid4: TDBGrid;
    Panel8: TPanel;
    DBNavigator1: TDBNavigator;
    DBNavigator2: TDBNavigator;
    Panel10: TPanel;
    Button9: TButton;
    Button10: TButton;
    Button11: TButton;
    Button12: TButton;
    Button13: TButton;
    Button14: TButton;
    Memo1: TMemo;

    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button13Click(Sender: TObject);
    procedure Button14Click(Sender: TObject);
  private
    { Private declarations }

  public
    { Public declarations }

    connection: array[1..10] of TSQLConnection;

    procedure auto_Initialize();
    procedure main();
    procedure Code_bak();
    procedure Get_Code();
    procedure Get_Code_Change();
    procedure Dispose_Code_Delete();
    procedure Dispose_Code_Add();
    procedure dispose_Dim_layer();
    procedure Auto_finish();
  end;

var
  main: Tmain;
  firstLogin: Boolean = true;
  logid: Integer;
  TmpStr: String;
  number: Integer;


implementation

uses Unitleave, Unitlogin, DataModule;

{$R *.dfm}

//function MySQLCallBack(CallType: TRACECat; CBInfo: Pointer): CBRType; stdcall;
//var
//  CBI: pSQLTRACEDesc;
//begin
//  Result := cbrUSEDEF;
//  if CBIn

procedure Tmain.Button1Click(Sender: TObject);
//var
//  number: Integer;
begin
  DataModule.DataModule1.SimpleDataSet1.Active:=true;
  Panel3.Visible:=false;
  Panel4.Visible:=true;
  Panel5.Visible:=false;
  Panel6.Visible:=false;
  Panel7.Visible:=false;
  Panel8.Visible:=false;
  Panel9.Visible:=false;
//    number:= DataModule.DataModule1.SimpleDataSet1.RecordCount;
//    showmessage(inttostr(number));

end;

procedure Tmain.Button2Click(Sender: TObject);
begin
  DataModule.DataModule1.SimpleDataSet2.Active:=true;
  Panel3.Visible:=false;
  Panel4.Visible:=false;
  Panel5.Visible:=true;
  Panel6.Visible:=false;
  Panel7.Visible:=false;
  Panel8.Visible:=false;
  Panel9.Visible:=false;

end;

procedure Tmain.Button3Click(Sender: TObject);
begin
  DataModule.DataModule1.SimpleDataSet3.Active:=true;
  DataModule.DataModule1.SimpleDataSet4.Active:=true;
  Panel3.Visible:=false;
  Panel4.Visible:=false;
  Panel5.Visible:=false;
  Panel6.Visible:=true;
  Panel7.Visible:=true;
  Panel8.Visible:=true;
  Panel9.Visible:=false;

end;



procedure Tmain.Button8Click(Sender: TObject);
begin
  Unitleave.leave.Show;
end;

procedure Tmain.Button4Click(Sender: TObject);
begin
  Panel3.Visible:=false;
  Panel4.Visible:=false;
  Panel5.Visible:=false;
  Panel6.Visible:=false;
  Panel7.Visible:=false;
  Panel8.Visible:=false;
  Panel9.Visible:=true;
  //  Unitnote.Note.Show;
  //  showmessage('开始自动维护！');
end;

procedure Tmain.main();
begin
  auto_Initialize();
  Code_bak();
  Get_Code();
  Get_Code_Change();
  Dispose_Code_Delete();
  Dispose_Code_Add();
  dispose_Dim_layer();
  Auto_finish();
end;

procedure Tmain.auto_Initialize();
var
  i: Integer;
  ConnectionName: String;
  DriverName: String;
  GetDriverFunc: String;
  LibraryName: String;
  VendorLib: String;
  DataBase: String;
  User_Name: String;
  Password: String;
const
  Finish: String = 'Finish';
  System: String = 'System';
  Lock: String = 'Lock';
begin
  TmpStr := 'select ID, CONNECTIONNAME, DATABASE, "User", PASSWORD from ALLCONNECTIONNEW';
  DataModule.DataModule1.SimpleDataSet5.Close;
  DataModule.DataModule1.SimpleDataSet5.DataSet.CommandText:=TmpStr;
  try
    DataModule.DataModule1.SimpleDataSet5.Open;

    number := DataModule.DataModule1.SimpleDataSet5.RecordCount;
//    showmessage(intTostr(number));
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  if(number = 0) then
    memo1.Lines.add('数据源不存在！')
  else
  begin
    DataModule.DataModule1.SimpleDataSet5.Next;
    for i:=1 to number do
    begin
      ConnectionName := DataModule.DataModule1.SimpleDataSet5.FieldList.Fields[1].AsString;
      DataBase := DataModule.DataModule1.SimpleDataSet5.FieldList.Fields[2].AsString;
      User_Name := DataModule.DataModule1.SimpleDataSet5.FieldList.Fields[3].AsString;
      Password := DataModule.DataModule1.SimpleDataSet5.FieldList.Fields[4].AsString;

      if ConnectionName = 'OracleConnection' then
      begin
        DriverName := 'Oracle';
        GetDriverFunc:= 'getSQLDriverORACLE';
        LibraryName:= 'dbexpora.dll';
        VendorLib:= 'oci.dll';
      end
      else if ConnectionName = 'DB2Connection' then
      begin
        DriverName := 'DB2';
        GetDriverFunc:= 'getSQLDriverDB2';
        LibraryName:= 'dbexpdb2.dll';
        VendorLib:= 'db2cli.dll';
      end;

      try
        connection[i] := TSQLConnection.Create(self);
        connection[i].ConnectionName := ConnectionName;
        connection[i].DriverName := DriverName;
        connection[i].GetDriverFunc := GetDriverFunc;
        connection[i].LibraryName := LibraryName;
        connection[i].VendorLib := VendorLib;
        connection[i].Params.Values['DataBase'] := DataBase;
        connection[i].Params.Values['User_Name'] := User_Name;
        connection[i].Params.Values['Password'] := Password;
        connection[i].LoginPrompt := false;
        connection[i].Connected := true;
      except
      on e:Exception do
        messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
      end;

      DataModule.DataModule1.SimpleDataSet5.Next;
    end;

    TmpStr := 'Update log set Result='''+Finish+'''where id = (select max(id) from log) and not exists (select 1 from LogCode where clbz is null)';
    DataModule.DataModule1.SQLQuery1.Close;
    DataModule.DataModule1.SQLQuery1.SQL.Clear;
    DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);

    try
      DataModule.DataModule1.SQLQuery1.ExecSQL(false);
    except
    on e:Exception do
      messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
    end;

    TmpStr := 'select max(id) from log where Result='''+Finish+'''';
    DataModule.DataModule1.SimpleDataSet5.Close;
    DataModule.DataModule1.SimpleDataSet5.DataSet.CommandText:=TmpStr;
    try
      DataModule.DataModule1.SimpleDataSet5.Open;
//      number:= DataModule.DataModule1.SimpleDataSet5.RecordCount;

      if(DataModule.DataModule1.SimpleDataSet5.FieldList.Fields[0].AsInteger <> 0) then  //而且log的第一条记录的oprator项不能为空
      begin
//        showmessage(intTostr(logid));
        logid := DataModule.DataModule1.SimpleDataSet5.FieldList.Fields[0].AsInteger + 1;
      end
      else
      begin
        logid := 1;
//        showmessage(intTostr(logid));
      end;
    except
    on e:Exception do
      messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
    end;

    TmpStr := 'insert into log (ID, Operator, Result) values ('''+intTostr(logid)+''','''+System+''', '''+Lock+''')';
    DataModule.DataModule1.SQLQuery1.Close;
    DataModule.DataModule1.SQLQuery1.SQL.Clear;
    DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
    try
      DataModule.DataModule1.SQLQuery1.ExecSQL(false);
    except
    on e:Exception do
      showmessage('手工维护还没有完成，请完成手工维护后再进行自动维护');//如果执行异常则报错
    end;
  end;
end;

procedure Tmain.Code_bak();
var
  TmpStr: String;
begin
  TmpStr := 'delete from AllCodeDataBak';
  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  TmpStr := 'insert into AllCodeDataBak Select * from AllCodeData';
  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  TmpStr := 'delete from AllCodeData' ;
  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

end;

procedure Tmain.Get_Code();
begin
end;

procedure Tmain.Get_Code_Change();
var
  Tmpint: Integer;

const
  newAdd: String = '新增';
  delete: String = '删除';
  describeChange: String = '描述变化';
  layerChange: String = '分层变化';
begin
  memo1.Lines.Add('开始获取代码表变化...');
  TmpStr := 'insert into logcode(logid,CodeTable,Id,Remark,LogRemark) select ''' + intTostr(logid) + ''',CodeTable, Id,Remark, '''+newAdd+''' from AllCodeData where AllCodeData.Id not in (select Id from AllCodeDataBak where AllCodeData.CodeTable=AllCodeDataBak.CodeTable and AllCodeData.Id=AllCodeDataBak.Id )' ;
  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  TmpStr := 'insert into logcode(logid,CodeTable,Id,Remark,LogRemark) select ''' + intTostr(logid) + ''',CodeTable, Id,Remark, '''+delete+''' from AllCodeDataBak where AllCodeDataBak.Id not in (select Id from AllCodeData where AllCodeData.CodeTable=AllCodeDataBak.CodeTable and AllCodeData.Id=AllCodeDataBak.Id )';
  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  TmpStr := 'insert into logcode(logid,CodeTable,Id,Remark,LogRemark) select ''' + intTostr(logid) + ''',CodeTable, Id,Remark, '''+describeChange+''' from AllCodeData where  exists (select 1 from AllCodeDataBak where AllCodeData.CodeTable=AllCodeDataBak.CodeTable and AllCodeData.Id=AllCodeDataBak.Id  and AllCodeData.remark<>AllCodeDataBak.remark)';
  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);
  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  TmpStr := 'insert into logcode(logid,CodeTable,Id,Remark,LogRemark) select ''' + intTostr(logid) + ''',CodeTable, Id,Remark, '''+layerChange+''' from AllCodeData where  exists (select 1 from AllCodeDataBak where AllCodeData.CodeTable=AllCodeDataBak.CodeTable and AllCodeData.Id=AllCodeDataBak.Id  and AllCodeData.remark=AllCodeDataBak.remark and ((AllCodeData.layer1<>AllCodeDataBak.layer1) ';
  for Tmpint := 1 to 9 do
  begin
    TmpStr := TmpStr + 'or (AllCodeData.layer' + intTostr(Tmpint) + '<>AllCodeDataBak.layer' + intTostr(Tmpint) + ')' ;
  end;
  TmpStr := TmpStr +'))';

  DataModule.DataModule1.SQLQuery1.Close;
  DataModule.DataModule1.SQLQuery1.SQL.Clear;
  DataModule.DataModule1.SQLQuery1.SQL.add(TmpStr);

  try
    DataModule.DataModule1.SQLQuery1.ExecSQL(false);
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  memo1.Lines.Add('代码表变化获取完毕');
end;

procedure Tmain.Dispose_Code_Delete();
begin
  TmpStr := 'SELECT * FROM AllDim';
  DataModule.DataModule1.SimpleDataSet5.Close;
  DataModule.DataModule1.SimpleDataSet5.DataSet.CommandText:=TmpStr;
  try
    DataModule.DataModule1.SimpleDataSet5.Open;
  except
  on e:Exception do
    messagedlg(e.message,mtinformation,[mbok],0);//如果执行异常则报错
  end;

  memo1.Lines.Add('开始处理代码表删除');

end;

procedure Tmain.Dispose_Code_Add();
begin
end;

procedure Tmain.dispose_Dim_layer();
begin
end;

procedure Tmain.Auto_finish();
begin

end;

procedure Tmain.FormShow(Sender: TObject);
begin
  if (firstLogin) then
  begin
    login.ShowModal;
    firstLogin := false;
  end;  
end;
{
function MySQLCallBack(CallType: TRACECat; CBInfo: Pointer): CBRType; stdcall;
var
  CBI: pSQLTRACEDesc;
begin
  Result := cbrUSEDEF;
  if CBInfo <> nil then
  begin
    CBI := pSQLTRACEDesc(CBInfo);
//    frmMain.
  end;
end;

}



procedure Tmain.Button13Click(Sender: TObject);
begin
  note.Caption := '自动维护程序正在运行，请稍候...';
  DataModule.DataModule1.SimpleDataSet5.Active:=true;
  DataModule.DataModule1.SQLConnection2.SetTraceCallbackEvent(nil, 0);
  DataModule.DataModule1.sqlmonitor1.Active := true;
  main();
  showmessage('自动维护结束！');
  note.Caption := '自动维护程序运行期间，将不能做其他的维护工作!';

end;

procedure Tmain.Button14Click(Sender: TObject);
begin
  memo1.Lines.Clear;
end;

end.