utdatamng.pas

代码变更管理工具。当对程序代码进行变更以后可以用改工具记录变更内容

{*******************************************************}
{  Name    : TDataMng--核心类                           }
{  Purpose : 完成对数据库的各种操作                     }
{  Author  : 刘 鹏                                      }
{  BegDate : 2008-04-30                               	}
{*******************************************************}

unit UtDataMng;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, DateUtils, 
  UtPubData;

type
  TDataMng = class
  private
    adcMdbChgLogMng  : TADOConnection;
      // 连接数据库
    adqChgLogMng : TADOQuery;
      // 执行sql语句
    arrRecBranch : ArrRecBranch;
      // 存放BranchTab表记录
    adqInfo    : TADOQuery;
      // 返回第一次界面显示数据
    arrRecDevp : ArrRecDevp;
      // 存放DevpTab表记录
    arrRecLog  : ArrRecLog;
      // 存放LogTab表记录
    temRBranch : ArrRecBranch;
      // 存放用于解析的版本分支信息
    temRDevp   : ArrRecDevp;
      // 存放用于解析的开发人员信息
    sqlStm : string;
      // 存放SQL语句
  public
    constructor Create();
      // 构造函数
    destructor Destroy(); override;
      // 析构函数
    function getDevpTabRecord(): ArrRecDevp;
      // 返回DevpTab表所有记录
    function getLogTabRecord(): ArrRecLog;
      // 返回LogTab表所有记录
    function getBranchTabRecord(): ArrRecBranch;
      // 返回LogTab表所有记录
    function getADOQuery(): TADOQuery;
      // 返回TADOQuery实例
    procedure openSqlStatement(tempAdoquery : TADOQuery; sqlStatement : string); overload;
      // 执行SQL语句，有结果集返回
    procedure openSqlStatement(tempAdoquery : TADOQuery; sqlStatement : string; fiter : Boolean); overload;
      // 执行SQL语句，有结果集返回，带过滤功能
    procedure addLogTabData(tempRecLog : RecLog);
      // 添加数据到LogTab表中
    procedure editLogTabDat(tempRecLog : RecLog);
      // 编辑LogTab表中数据
    procedure fastQuery(tempRecFast : RecFastQuery);
      // 快速查询
    procedure analysisFieldByBranch(Sender: TField; var Text: String; DisplayText: Boolean);
      // 对branchId字段进行解析
    procedure analysisFieldByDevName(Sender: TField; var Text: String; DisplayText: Boolean);
      // 对vdevpId字段进行解析
  end;
  
implementation


{ TDataMng }

//------------------------------------------------------------------------------

constructor TDataMng.Create();
begin
  adcMdbChgLogMng := TADOConnection.Create(nil);     // 生成TADOConnection对象
  adqChgLogMng    := TADOQuery.Create(nil);          // 生成TADOQuery对象
  adqInfo         := TADOQuery.Create(nil);        
  adcMdbChgLogMng.ConnectionString := ADO_STRING;    // 设置连接字符串
  adcMdbChgLogMng.LoginPrompt      := False;         // 不弹出登录数据库的输入信息框
  adqChgLogMng.Connection          := adcMdbChgLogMng; 
  adqInfo.Connection               := adcMdbChgLogMng;

  temRBranch := getBranchTabRecord();
  temRDevp   := getDevpTabRecord();
  openSqlStatement(adqInfo, SQLQUERY, True);
end;
//------------------------------------------------------------------------------

destructor TDataMng.Destroy();
begin 
  if adqChgLogMng.Active then
  begin
    adqChgLogMng.Active := False;
  end; 
  adqChgLogMng.Close();
  adqChgLogMng.Free();
  if adqInfo.Active then
  begin
    adqInfo.Active := False;
  end;  
  adqInfo.Close();
  adqInfo.Free();
  adcMdbChgLogMng.Close();
  adcMdbChgLogMng.Free();                   // 释放对象
  inherited;
end;
//------------------------------------------------------------------------------

procedure TDataMng.editLogTabDat(tempRecLog: RecLog);
begin
  try
    adqInfo.Edit();
    adqInfo.FieldByName('devpId').AsInteger   := tempRecLog.devpId;
    adqInfo.FieldByName('branchId').AsString  := tempRecLog.branchId;
    adqInfo.FieldByName('notes').AsString     := tempRecLog.notes;
    adqInfo.FieldByName('devpNotes').AsString := tempRecLog.devpNotes;
    adqInfo.FieldByName('lDateTime').AsString := DateTimeToStr(tempRecLog.lDateTime);
    adqInfo.FieldByName('modType').AsString   := tempRecLog.modType;
    adqInfo.Post();
  except
    ShowMessage('Failed to edit Log!');
  end;
  sqlStm := sqlQuery;
  openSqlStatement(adqInfo, sqlStm, True);
end;
//------------------------------------------------------------------------------

procedure TDataMng.addLogTabData(tempRecLog: RecLog);
begin
  try
    adqInfo.Append();
    adqInfo.FieldByName('devpId').AsInteger   := tempRecLog.devpId;
    adqInfo.FieldByName('branchId').AsString  := tempRecLog.branchId;
    adqInfo.FieldByName('notes').AsString     := tempRecLog.notes;
    adqInfo.FieldByName('devpNotes').AsString := tempRecLog.devpNotes;
    adqInfo.FieldByName('lDateTime').AsString := DateTimeToStr(tempRecLog.lDateTime);
    adqInfo.FieldByName('modType').AsString   := tempRecLog.modType;
    adqInfo.Post();
  except
    ShowMessage('Failed to insert Log!');
  end;
  sqlStm := sqlQuery;
  openSqlStatement(adqInfo, sqlStm, True);
end;
//------------------------------------------------------------------------------

function TDataMng.getADOQuery(): TADOQuery;
begin
  Result := adqInfo;         // 返回TADOQuery实例
end;
//------------------------------------------------------------------------------

function TDataMng.getBranchTabRecord(): ArrRecBranch;
var
  i: Integer;
begin
  openSqlStatement(adqChgLogMng, 'select * from BranchTab order by BranchTab.name '); // 返回BranchTab表中所有数据
  if not adqChgLogMng.Eof then
  begin
    SetLength(arrRecBranch, adqChgLogMng.RecordCount); // 设置数组大小
    for i := 0 to adqChgLogMng.RecordCount - 1 do      // Iterate
    begin
      arrRecBranch[i].id        := adqChgLogMng.FieldByName('id').AsInteger;
      arrRecBranch[i].name      := adqChgLogMng.FieldByName('name').AsString;
      arrRecBranch[i].active    := adqChgLogMng.FieldByName('active').AsInteger;
      arrRecBranch[i].bDateTime := adqChgLogMng.FieldByName('bDateTime').AsDateTime;
      adqChgLogMng.Next();      
    end;    // end for
  end;      // end if not adqChgLogMng.Eof then
  Result := arrRecBranch;    
end;
//------------------------------------------------------------------------------

function TDataMng.getDevpTabRecord(): ArrRecDevp;
var
  i: Integer;
begin
  openSqlStatement(adqChgLogMng, 'select * from DevpTab order by DevpTab.name');  // 返回DevpTab表中所有数据
  if not adqChgLogMng.Eof then
  begin
    SetLength(arrRecDevp, adqChgLogMng.RecordCount); // 设置数组大小
    for i := 0 to adqChgLogMng.RecordCount - 1 do    // Iterate
    begin
      arrRecDevp[i].id     := adqChgLogMng.FieldByName('id').AsInteger;
      arrRecDevp[i].name   := adqChgLogMng.FieldByName('name').AsString;
      arrRecDevp[i].active := adqChgLogMng.FieldByName('active').AsInteger;
      adqChgLogMng.Next();
    end;    // end for
  end;      // end if not adqChgLogMng.Eof then
  Result := arrRecDevp;    
end;
//------------------------------------------------------------------------------

function TDataMng.getLogTabRecord(): ArrRecLog;
var
  i: Integer;
begin
  openSqlStatement(adqChgLogMng, 'select * from LogTab');       // 返回LogTab表中所有数据
  if not adqChgLogMng.Eof then
  begin
    SetLength(arrRecLog, adqChgLogMng.RecordCount);  // 设置数组大小
    for i := 0 to adqChgLogMng.RecordCount - 1 do    // Iterate
    begin
      arrRecLog[i].id        := adqChgLogMng.FieldByName('id').AsInteger;
      arrRecLog[i].branchId  := adqChgLogMng.FieldByName('branchId').AsString;
      arrRecLog[i].devpId    := adqChgLogMng.FieldByName('devpId').AsInteger;
      arrRecLog[i].notes     := adqChgLogMng.FieldByName('notes').AsString;
      arrRecLog[i].devpNotes := adqChgLogMng.FieldByName('devpNotes').AsString;
      arrRecLog[i].modType   := adqChgLogMng.FieldByName('modType').AsString;
      arrRecLog[i].lDateTime := adqChgLogMng.FieldByName('lDateTime').AsDateTime;
      adqChgLogMng.Next();
    end;    // end for
  end;      // end if not adqChgLogMng.Eof then
  Result := arrRecLog;    
end;
//------------------------------------------------------------------------------

procedure TDataMng.openSqlStatement(tempAdoquery : TADOQuery; sqlStatement: string);
begin
  tempAdoquery.Close();                   // 对象关闭，防止是一次使用后没有关闭
  tempAdoquery.SQL.Clear();               // 把SQL中的语句清空
  tempAdoquery.SQL.Add(sqlStatement);     // 添加SQL语句
  try   
    tempAdoquery.Open();                  // 执行SQL语句
  except
    ShowMessage('执行数据库操作失败');   
  end;
end;
//------------------------------------------------------------------------------

procedure TDataMng.fastQuery(tempRecFast: RecFastQuery);
var
  flag : Boolean;
begin
  flag := False;
  sqlStm := 'select * from LogTab where ';
  if (tempRecFast.devpId =0) and (tempRecFast.branchId = '') and (tempRecFast.notes = '') 
    and (DateTimeToStr(tempRecFast.startTime) = '1899-12-30') and (DateTimeToStr(tempRecFast.endTime) 
    = '1899-12-30') then
  begin
    openSqlStatement(adqInfo, SQLQUERY, True);
  end
  else
  begin
// 判断是否添加查询条件：devpId  
    if tempRecFast.devpId > 0 then
    begin
      if flag then
      begin
        sqlStm := sqlStm + ' and ';
      end;  
      sqlStm := sqlStm + ' devpId=' + IntToStr(tempRecFast.devpId);
      flag := True;
    end;
// 判断是否添加查询条件：branchName  
    if tempRecFast.branchId <> '' then
    begin
      if flag then
      begin
        sqlStm := sqlStm + ' and ';
      end; 
      sqlStm := sqlStm + ' branchId like + ' + '''%;' + tempRecFast.branchId + ';%''';
      flag := True;
    end;
// 判断是否添加查询条件：开始和结束时间
    if (DateTimeToStr(tempRecFast.startTime) <> '1899-12-30') and  
       (DateTimeToStr(tempRecFast.endTime) <> '1899-12-30') then
    begin
      if flag then
      begin
        sqlStm := sqlStm + ' and ';
      end; 
      sqlStm := sqlStm + ' lDateTime  between ' + '#' + DateToStr(tempRecFast.startTime) 
      + '#' + ' and ' + '#' + DateToStr(tempRecFast.endTime) + '#';
      flag := True;
    end;  
// 判断是否添加查询条件：备注内容
    if tempRecFast.notes <> '' then
    begin
      if flag then
      begin
        sqlStm := sqlStm + ' and ';
      end; 
      sqlStm := sqlStm + ' notes like + ' + '''%' + tempRecFast.notes + '%''';
      flag := True;
    end;
    sqlStm := sqlStm + ' order by LogTab.lDateTime desc';
    if  not flag then
    begin
      ;
    end;
    openSqlStatement(adqInfo, sqlStm, True);
  end;  
end;
//------------------------------------------------------------------------------
 
procedure TDataMng.analysisFieldByBranch(Sender: TField; var Text: string; DisplayText: Boolean);
var
  i, j : Integer;
  tempBranchIDStr : string;
  temStrList : TStringList;
begin
  temStrList := TStringList.Create();
  tempBranchIDStr := Sender.Value; 
  temStrList.Delimiter := SPLITER_CHAR;
  temStrList.DelimitedText := tempBranchIDStr;
  for i := 1 to temStrList.Count - 2 do    // Iterate
  begin
    for j := 0 to Length(temRBranch) - 1 do    // Iterate
    begin
      if temStrList[i] = IntToStr(temRBranch[j].id) then
      begin
        Text := Text + temRBranch[j].name + ' ';
      end;
    end;    // for
  end;    // for
  temStrList.Free();
end;
//------------------------------------------------------------------------------

procedure TDataMng.openSqlStatement(tempAdoquery: TADOQuery;sqlStatement: string; 
  fiter: Boolean);
begin
  tempAdoquery.Close();                   // 对象关闭，防止是一次使用后没有关闭
  tempAdoquery.SQL.Clear();               // 把SQL中的语句清空
  tempAdoquery.SQL.Add(sqlStatement);     // 添加SQL语句
  try   
    tempAdoquery.Open();                  // 执行SQL语句
  except
    ShowMessage('执行数据库操作失败');   
  end;
  if tempAdoquery.RecordCount > 0 then
  begin
    tempAdoquery.Fields[2].OnGetText := analysisFieldByBranch;
    tempAdoquery.Fields[1].OnGetText := analysisFieldByDevName;
  end;
  
end;
//------------------------------------------------------------------------------

procedure TDataMng.analysisFieldByDevName(Sender: TField; var Text: String;
  DisplayText: Boolean);
var
  i: Integer;    
begin
  for i := 0 to Length(temRDevp) - 1 do    // Iterate
  begin
    if Sender.Value = IntToStr(temRDevp[i].id) then
    begin
      Text := temRDevp[i].name;
      Break;
    end;
  end;    // for   
end;     

end.