RealICQClient/Controls/RealICQDBHistory.pas

{
文件名：RealICQDBHistory.pas
功  能：读取，保存历史记录。
建  立：尹进
历  史：
2006.5.10：创建文件（尹进）
}
unit RealICQDBHistory;

interface

uses
  classes,
  SysUtils,
  StrUtils,
  DateUtils,
  DB,
  ADODB,
  RealICQClient,
  WNDES,
  MD5;

type
  TMessageSearchResult = class
  private
    FID: Integer;
    FTeamID: String;
    FSender,
    FReceiver: String;
    FSendDateTime: TDateTime;
    FFont: String;
    FMessage: String;
  public
    property ID: Integer read FID;
    property TeamID: String read FTeamID;
    property Sender: String read FSender;
    property Receiver: String read FReceiver;
    property SendDateTime: TDateTime read FSendDateTime;
    property Font: String read FFont;
    property MessageStr: String read FMessage;
  end;

  TSystemMessageSearchResult = class
  private
    FID: Integer;
    FMessageID: Integer;
    FMessageType: TRealICQSystemMessageType;
    FPositionType: TRealICQSystemMessagePosition;
    FLeft: Integer;
    FTop: Integer;
    FWidth: Integer;
    FHeight: Integer;
    FTitle: String;
    FContent: String;
    FUrl: String;
    FAutoCloseTime: Integer;
    FSendDateTime: TDateTime;
  public
    property ID: Integer read FID;
    property MessageID: Integer read FMessageID;
    property MessageType: TRealICQSystemMessageType read FMessageType;
    property PositionType: TRealICQSystemMessagePosition read FPositionType;
    property Left: Integer read FLeft;
    property Top: Integer read FTop;
    property Width: Integer read FWidth;
    property Height: Integer read FHeight;
    property Title: String read FTitle;
    property Content: String read FContent;
    property Url: String read FUrl;
    property AutoCloseTime: Integer read FAutoCloseTime;
    property SendDateTime: TDateTime read FSendDateTime;
  end;

  TSMSMessageSearchResult = class
  private
    FID: Integer;
    FSender: String;
    FReceiver: String;
    FSendDateTime: TDateTime;
    FContent: String;
  public
    property ID: Integer read FID;
    property Sender: String read FSender;
    property Receiver: String read FReceiver;
    property SendDateTime: TDateTime read FSendDateTime;
    property Content: String read FContent;
  end;

  TDBHistorySearchResult = class
  private
    FRecordCount: Integer;
    FPageCount: Integer;
    FPageIndex: Integer;
    FMessages: TList;
  public
    property RecordCount: Integer read FRecordCount;
    property PageCount: Integer read FPageCount;
    property PageIndex: Integer read FPageIndex;
    property Messages: TList read FMessages;
  end;

  TRealICQDBHistory = class
    private
      FDBFileName: String;
      FDesKey: String;
      FADOConnection: TADOConnection;
      FLoginName:String;
      procedure SetDBFileName(Value: String);
      procedure SetLoginName(Value: String);
      procedure CheckFieldTeamID;
    protected
    public
      constructor Create;
      destructor Destroy; override;

      procedure SaveMessage(ATeamID: String; ASender, AReceiver: String;
        ASendDateTime: TDateTime; AFont: String; AMessage: String); 
      procedure SaveSystemMessage(AMessageID: Integer;
        AMessageType: TRealICQSystemMessageType;
        APositionType: TRealICQSystemMessagePosition;
        ALeft, ATop, AWidth, AHeight: Integer;
        ATitle, AContent, AUrl: String;
        AAutoCloseTime: Integer);
      procedure SaveSMSMessage(ASender, AReceiver: String; ASendDateTime: TDateTime; AContent: String; AMessageType: Integer);

      function GetMessage(ATeamID: String; ASender, AReceiver: String;
        ASendDateTime: TDateTime; AMaxCount: Integer = 0; Page: Integer = 1; PageSize: Integer = 20): TDBHistorySearchResult;
      function GetSystemMessage(ASendDateTime: TDateTime; Page: Integer = 1; PageSize: Integer = 20): TDBHistorySearchResult;
      function GetSMSMessage(ASendDateTime: TDateTime; AMaxCount: Integer = 0; Page: Integer = 1; PageSize: Integer = 20): TDBHistorySearchResult;


      function SearchMessage(ATeamID: String;
        ASender, AReceiver: String; SearchAllMessage: Boolean; AKeyword: String): TList;
      procedure DelMessage(AID: Integer);
      procedure DelSystemMessage(AID: Integer);
      procedure DelSMSMessage(AID: Integer);

      procedure DelMessageByLoginName(ALoginName: String);
      procedure DelMessageByTeamID(ATeamID: String);
      procedure DelAllSystemMessage;

      //获取最近联系人列表
      function GetLatests(AReceiver: String): TStringList;
      function GetContactors: TStringList;
      procedure RestoreMessageHistory(ADBFileName: String);
    published
      property DBFileName: String read FDBFileName write SetDBFileName;
      property LoginName:String read FLoginName write SetLoginName;
  end;

  function GetPaginationSelectQuery(Condition: String; TableName: String;
    RecordCount, PageCount, PageIndex: Integer; PageSize: Integer): String;
    
implementation
  
function GetPaginationSelectQuery(Condition: String; TableName: String;
    RecordCount, PageCount, PageIndex: Integer; PageSize: Integer): String;
var
  SelectQuery: String;
  MiddlePage: Integer;
begin
      //计算中间的页码
      MiddlePage := PageCount div 2;

      //根据页码的范围构造不同的SQL语句
      if (PageIndex = 1) then
      begin
        SelectQuery := 'SELECT TOP ' + IntToStr(PageSize) + ' * '
                    + ' FROM ' + TableName + ' WHERE ' + Condition
                    + ' ORDER BY ID ASC';
      end
      else if (PageIndex <= MiddlePage) then
      begin
        SelectQuery := 'SELECT TOP ' + IntToStr(PageSize) + ' * '
                    + ' FROM ' + TableName + ' '
                    + ' WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP ' + IntToStr(PageSize * (PageIndex - 1))
                    + ' ID FROM ' + TableName + ' WHERE ' + Condition
                    + ' ORDER BY ID ASC) DERIVEDTB )'
                    + ' AND ' + Condition
                    + ' ORDER BY ID ASC';
      end
      else if (PageIndex = PageCount) then
      begin
        SelectQuery := 'SELECT TOP ' + IntToStr(RecordCount - (PageIndex - 1) * PageSize) + ' * '
                    + ' FROM ' + TableName + ' '
                    + ' WHERE ' + condition
                    + ' ORDER BY ID DESC';
        SelectQuery := 'SELECT * FROM (' + SelectQuery + ') DERIVEDTB ORDER BY ID ASC';
      end
      else if (PageIndex < PageCount) then
      begin
        SelectQuery := 'SELECT TOP ' + IntToStr(PageSize) + ' * '
                    + ' FROM ' + TableName + ' '
                    + ' WHERE ID<(SELECT MIN(ID) FROM (SELECT TOP ' + IntToStr(RecordCount - PageSize * PageIndex)
                    + ' ID FROM ' + TableName + ' WHERE ' + Condition
                    + ' ORDER BY ID DESC) DERIVEDTB ) AND ' + Condition
                    + ' ORDER BY ID DESC';
        SelectQuery := 'SELECT * FROM (' + SelectQuery + ') DERIVEDTB ORDER BY ID ASC';
      end;

      Result := SelectQuery;
end;
   
//------------------------------------------------------------------------------
procedure TRealICQDBHistory.DelMessage(AID: Integer);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('DELETE FROM Messages WHERE ID = ' + IntToStr(AID));
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.DelSystemMessage(AID: Integer);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('DELETE FROM SystemMessages WHERE ID = ' + IntToStr(AID));
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.DelSMSMessage(AID: Integer);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    if AID = -1 then
      ADOQuery.SQL.Add('DELETE FROM Messages (TeamID =''-3'')')
    else
      ADOQuery.SQL.Add('DELETE FROM Messages WHERE ID = ' + IntToStr(AID));
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.DelMessageByLoginName(ALoginName: String);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Text := 'DELETE FROM Messages WHERE (TeamID = ''-1'')';
    ADOQuery.SQL.Text := ADOQuery.SQL.Text + ' AND (Sender=''' + ALoginName + ''' OR Receiver=''' + ALoginName + ''')';
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.DelMessageByTeamID(ATeamID: String);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('DELETE FROM Messages WHERE TeamID = ''' + ATeamID + '''');
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.DelAllSystemMessage;
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('DELETE FROM SystemMessages');
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
function TRealICQDBHistory.SearchMessage(ATeamID: String;
  ASender, AReceiver: String; SearchAllMessage: Boolean; AKeyword: String): TList;
var
  SearchResult: TMessageSearchResult;
  ADODataSet: TADODataSet;
  AMessageStr: String;
  ASendDateTime: TDateTime;
begin
  ASender := AnsiReplaceStr(ASender, '''', '''''');
  AReceiver := AnsiReplaceStr(AReceiver, '''', '''''');
  Result := TList.Create;
  ADODataSet := TADODataSet.Create(nil);
  try
    ADODataSet.Connection := FADOConnection;
    ADODataSet.CommandText := 'SELECT * FROM Messages';
    if not SearchAllMessage then
    begin
      try
        if StrToInt(ATeamID) <= 0 then
        begin
          ADODataSet.CommandText := ADODataSet.CommandText + ' WHERE (TeamID=''' + ATeamID + ''' ';
          ADODataSet.CommandText := ADODataSet.CommandText + ' OR TeamID = ''-5'') AND ((Sender=''' + ASender + ''' AND Receiver=''' + AReceiver + ''')';
          ADODataSet.CommandText := ADODataSet.CommandText + ' OR (Receiver = ''' + ASender + ''' AND Sender=''' + AReceiver + '''))';
        end
        else
        begin
          ADODataSet.CommandText := ADODataSet.CommandText + ' WHERE TeamID=''' + ATeamID + '''';
        end;
      except
        ADODataSet.CommandText := ADODataSet.CommandText + ' WHERE TeamID=''' + ATeamID + '''';
      end;
    end;
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      ASendDateTime := ADODataSet.FieldByName('SendDateTime').AsFloat;
      FDesKey := Copy(MD5En(FLoginName), 25, 8);
      AMessageStr := DESryStrHex(ADODataSet.FieldByName('Message').AsString, FDesKey);
      
      if AnsiPos(AKeyword, AMessageStr) > 0 then
      begin
        SearchResult := TMessageSearchResult.Create;
        SearchResult.FTeamID := Trim(ADODataSet.FieldByName('TeamID').AsString);
        SearchResult.FSender := ADODataSet.FieldByName('Sender').AsString;
        SearchResult.FReceiver := ADODataSet.FieldByName('Receiver').AsString;
        SearchResult.FSendDateTime := ASendDateTime;
        SearchResult.FFont := ADODataSet.FieldByName('Font').AsString;
        SearchResult.FMessage := AMessageStr;
        Result.Add(SearchResult);
      end;      
      ADODataSet.Next;
    end;

  finally
    FDesKey := Copy(MD5En(FLoginName), 25, 8);
    FreeAndNil(ADODataSet);
  end;
end;

//------------------------------------------------------------------------------
function TRealICQDBHistory.GetSystemMessage(ASendDateTime: TDateTime; Page: Integer = 1; PageSize: Integer = 20): TDBHistorySearchResult;
var
  SearchResult: TSystemMessageSearchResult;
  ADODataSet: TADODataSet;
  ADODataSetGetRecordCount: TADODataSet;
  Condition: String;
begin
  Result := TDBHistorySearchResult.Create;
  Result.FMessages := TList.Create;
  Result.FRecordCount := 0;
  Result.FPageCount := 0;
  Result.FPageIndex := 0;
  ADODataSet := TADODataSet.Create(nil);
  ADODataSetGetRecordCount := TADODataSet.Create(nil);
  try
    ADODataSet.Connection := FADOConnection;
    ADODataSetGetRecordCount.Connection := FADOConnection;
    Condition := 'SendDateTime >= ' + FloatToStr(ASendDateTime);

    //-------------------------------------------------
    //获取符合条件的记录总数
    ADODataSetGetRecordCount.CommandText := 'SELECT COUNT(*) FROM SystemMessages WHERE ' + Condition;
    ADODataSetGetRecordCount.Open;
    Result.FRecordCount := ADODataSetGetRecordCount.Fields[0].AsInteger;
    ADODataSetGetRecordCount.Close;
    if (Result.FRecordCount = 0) then Exit;
    //-------------------------------------------------

    //-------------------------------------------------
    //计算总的页数
    if (Result.FRecordCount mod PageSize = 0) then
      Result.FPageCount := Result.FRecordCount div PageSize
    else
      Result.FPageCount := (Result.FRecordCount div PageSize) + 1;
    //-------------------------------------------------
      
    //-------------------------------------------------
    //设置PageIndex的值
    if (Page < 1) then Page := 1;
    if (Page > Result.FPageCount) then Page := Result.FPageCount;
    Result.FPageIndex := page;
    //-------------------------------------------------

    ADODataSet.CommandText := GetPaginationSelectQuery(Condition, 'SystemMessages',
      Result.FRecordCount, Result.FPageCount, Result.FPageIndex, PageSize);
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      SearchResult := TSystemMessageSearchResult.Create;
      SearchResult.FID := ADODataSet.FieldByName('ID').AsInteger; 
      SearchResult.FMessageID := ADODataSet.FieldByName('MessageID').AsInteger;
      SearchResult.FMessageType := TRealICQSystemMessageType(ADODataSet.FieldByName('MessageType').AsInteger);
      SearchResult.FPositionType := TRealICQSystemMessagePosition(ADODataSet.FieldByName('PositionType').AsInteger);
      SearchResult.FLeft := ADODataSet.FieldByName('Left').AsInteger;
      SearchResult.FTop := ADODataSet.FieldByName('Top').AsInteger;
      SearchResult.FWidth := ADODataSet.FieldByName('Width').AsInteger;
      SearchResult.FHeight := ADODataSet.FieldByName('Height').AsInteger;
      SearchResult.FAutoCloseTime := ADODataSet.FieldByName('AutoCloseTime').AsInteger;
      SearchResult.FSendDateTime := ADODataSet.FieldByName('SendDateTime').AsFloat;
      FDesKey := Copy(MD5En(FLoginName), 25, 8);

      SearchResult.FTitle := DESryStrHex(ADODataSet.FieldByName('Title').AsString, FDesKey);
      SearchResult.FContent := DESryStrHex(ADODataSet.FieldByName('Content').AsString, FDesKey);
      SearchResult.FUrl := DESryStrHex(ADODataSet.FieldByName('Url').AsString, FDesKey);
      
      Result.Messages.Add(SearchResult);
      
      ADODataSet.Next;
    end;

  finally
    FDesKey := Copy(MD5En(FLoginName), 25, 8);
    FreeAndNil(ADODataSet);
  end;
end;


function TRealICQDBHistory.GetContactors: TStringList;
var
  ADODataSet: TADODataSet;
begin
  Result := TStringList.Create;
  
  ADODataSet := TADODataSet.Create(nil);
  try
    ADODataSet.Connection := FADOConnection;
    ADODataSet.CommandText := 'SELECT DISTINCT Sender FROM Messages WHERE TeamID = ''-1''';
    
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      if Result.IndexOf(ADODataSet.FieldByName('Sender').AsString) = -1 then
        Result.Add(ADODataSet.FieldByName('Sender').AsString);

      if Result.Count >= 100 then Break;
      ADODataSet.Next;
    end;

    ADODataSet.Close;
    ADODataSet.CommandText := 'SELECT DISTINCT Receiver FROM Messages WHERE TeamID = ''-1''';
    
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      if Result.IndexOf(ADODataSet.FieldByName('Receiver').AsString) = -1 then
        Result.Add(ADODataSet.FieldByName('Receiver').AsString);

      if Result.Count >= 100 then Break;
      ADODataSet.Next;
    end;
  finally
    FreeAndNil(ADODataSet);
  end;
end;


//获取最近联系人列表
function TRealICQDBHistory.GetLatests(AReceiver: String): TStringList;
var
  ADODataSet: TADODataSet;
begin
  AReceiver := AnsiReplaceStr(AReceiver, '''', '''''');
  Result := TStringList.Create;
  
  ADODataSet := TADODataSet.Create(nil);
  try
    ADODataSet.Connection := FADOConnection;
    ADODataSet.CommandText := 'SELECT Top 1000 Sender FROM Messages WHERE Receiver = ''' + AReceiver + ''' AND TeamID = ''-1'' ORDER BY SendDateTime DESC';
    
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      if Result.IndexOf(ADODataSet.FieldByName('Sender').AsString) = -1 then
        Result.Add(ADODataSet.FieldByName('Sender').AsString);

      if Result.Count >= 20 then Break;
      ADODataSet.Next;
    end;
  finally
    FreeAndNil(ADODataSet);
  end;
end;

//------------------------------------------------------------------------------
function TRealICQDBHistory.GetSMSMessage(ASendDateTime: TDateTime; AMaxCount: Integer = 0; Page: Integer = 1; PageSize: Integer = 20): TDBHistorySearchResult;
var
  SearchResult: TSMSMessageSearchResult;
  ADODataSet: TADODataSet;
  ADODataSetGetRecordCount: TADODataSet;
  Condition: String;
begin 
  Result := TDBHistorySearchResult.Create;
  Result.FMessages := TList.Create;
  Result.FRecordCount := 0;
  Result.FPageCount := 0;
  Result.FPageIndex := 0;
  ADODataSet := TADODataSet.Create(nil);
  ADODataSetGetRecordCount := TADODataSet.Create(nil);
  try
    ADODataSet.Connection := FADOConnection;
    ADODataSetGetRecordCount.Connection := FADOConnection;
    Condition := 'SendDateTime >= ' + FloatToStr(ASendDateTime);

    if AMaxCount > 0 then
    begin
      ADODataSet.CommandText := 'SELECT TOP ' + IntToStr(AMaxCount) + ' * FROM Messages WHERE (TeamID =''-2'' or  TeamID =''-3'') and (' + Condition + ')';
      ADODataSet.CommandText := ADODataSet.CommandText + ' ORDER BY SendDateTime DESC';
    end
    else
    begin
      //-------------------------------------------------
      //获取符合条件的记录总数
      ADODataSetGetRecordCount.CommandText := 'SELECT COUNT(*) FROM Messages WHERE (TeamID =''-2'' or  TeamID =''-3'') and (' + Condition + ')';
      ADODataSetGetRecordCount.Open;
      Result.FRecordCount := ADODataSetGetRecordCount.Fields[0].AsInteger;
      ADODataSetGetRecordCount.Close;
      if (Result.FRecordCount = 0) then Exit;
      //-------------------------------------------------

      //-------------------------------------------------
      //计算总的页数
      if (Result.FRecordCount mod PageSize = 0) then
        Result.FPageCount := Result.FRecordCount div PageSize
      else
        Result.FPageCount := (Result.FRecordCount div PageSize) + 1;
      //-------------------------------------------------

      //-------------------------------------------------
      //设置PageIndex的值
      if (Page < 1) then Page := 1;
      if (Page > Result.FPageCount) then Page := Result.FPageCount;
      Result.FPageIndex := page;
      //-------------------------------------------------

      ADODataSet.CommandText := GetPaginationSelectQuery(Condition, 'Messages',
        Result.FRecordCount, Result.FPageCount, Result.FPageIndex, PageSize);
    end;
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      SearchResult := TSMSMessageSearchResult.Create;
      SearchResult.FID := ADODataSet.FieldByName('ID').AsInteger;
      SearchResult.FSender := ADODataSet.FieldByName('Sender').AsString;
      SearchResult.FReceiver := ADODataSet.FieldByName('Receiver').AsString;
      SearchResult.FSendDateTime := ADODataSet.FieldByName('SendDateTime').AsDateTime;
      try
        SearchResult.FContent := DESryStrHex(ADODataSet.FieldByName('Content').AsString, FDesKey);
      except
        FreeAndNil(SearchResult);
        Continue;
      end;
      Result.Messages.Add(SearchResult);

      ADODataSet.Next;
    end;

  finally
    FreeAndNil(ADODataSet);
  end;
end;

//------------------------------------------------------------------------------
function TRealICQDBHistory.GetMessage(ATeamID: String; ASender, AReceiver: String;
  ASendDateTime: TDateTime; AMaxCount: Integer = 0; Page: Integer = 1; PageSize: Integer = 20): TDBHistorySearchResult;
var
  SearchResult: TMessageSearchResult;
  ADODataSet: TADODataSet;
  ADODataSetGetRecordCount: TADODataSet;
  Condition: String;
begin
  ASender := AnsiReplaceStr(ASender, '''', '''''');
  AReceiver := AnsiReplaceStr(AReceiver, '''', '''''');
  Result := TDBHistorySearchResult.Create;
  Result.FMessages := TList.Create;
  Result.FRecordCount := 0;
  Result.FPageCount := 0;
  Result.FPageIndex := 0;
  
  ADODataSet := TADODataSet.Create(nil);
  ADODataSetGetRecordCount := TADODataSet.Create(nil);
  try
    ADODataSet.Connection := FADOConnection;
    ADODataSetGetRecordCount.Connection := FADOConnection;

    if ATeamID = '-1' then
      Condition := '(TeamID = ''-5'' or TeamID = ''-1'') AND SendDateTime >= ' + FloatToStr(ASendDateTime)
    else if ATeamID = '-2' then
      Condition := '(TeamID = ''-2'' or TeamID = ''-1'' or TeamID = ''-5'') AND SendDateTime >= ' + FloatToStr(ASendDateTime)
    else
      Condition := 'TeamID=''' + ATeamID + ''' AND SendDateTime >= ' + FloatToStr(ASendDateTime);

    try
      if (StrToInt(ATeamID) <= 0) and (StrToInt(ATeamID) <> -3) then
      begin
        Condition := Condition + ' AND ((Sender=''' + ASender + ''' AND Receiver=''' + AReceiver + ''')';
        Condition := Condition + ' OR (Receiver=''' + ASender + ''' AND Sender=''' + AReceiver + '''))';
      end;
    except
    end;
    
    if AMaxCount > 0 then
    begin
      ADODataSet.CommandText := 'SELECT TOP ' + IntToStr(AMaxCount) + ' * FROM Messages WHERE ' + Condition;
      ADODataSet.CommandText := ADODataSet.CommandText + ' ORDER BY ID DESC';
    end
    else
    begin
      //-------------------------------------------------
      //获取符合条件的记录总数
      ADODataSetGetRecordCount.CommandText := 'SELECT COUNT(*) FROM Messages WHERE ' + Condition;  
      ADODataSetGetRecordCount.Open;
      Result.FRecordCount := ADODataSetGetRecordCount.Fields[0].AsInteger;
      ADODataSetGetRecordCount.Close;
      if (Result.FRecordCount = 0) then Exit;
      //-------------------------------------------------

      //-------------------------------------------------
      //计算总的页数
      if (Result.FRecordCount mod PageSize = 0) then
        Result.FPageCount := Result.FRecordCount div PageSize
      else
        Result.FPageCount := (Result.FRecordCount div PageSize) + 1;
      //-------------------------------------------------
      
      //-------------------------------------------------
      //设置PageIndex的值
      if (Page < 1) then Page := 1;
      if (Page > Result.FPageCount) then Page := Result.FPageCount;
      Result.FPageIndex := page;
      //-------------------------------------------------

      ADODataSet.CommandText := GetPaginationSelectQuery(Condition, 'Messages',
        Result.FRecordCount, Result.FPageCount, Result.FPageIndex, PageSize);
    end;
    
    ADODataSet.Open;
    while not ADODataSet.Eof do
    begin
      SearchResult := TMessageSearchResult.Create;
      SearchResult.FID := ADODataSet.FieldByName('ID').AsInteger; 
      SearchResult.FTeamID := Trim(ADODataSet.FieldByName('TeamID').AsString); 
      SearchResult.FSender := ADODataSet.FieldByName('Sender').AsString;
      SearchResult.FReceiver := ADODataSet.FieldByName('Receiver').AsString;
      SearchResult.FSendDateTime := ADODataSet.FieldByName('SendDateTime').AsFloat;

      FDesKey := Copy(MD5En(FLoginName), 25, 8);
   
      SearchResult.FFont := ADODataSet.FieldByName('Font').AsString;

      try
        SearchResult.FMessage := DESryStrHex(ADODataSet.FieldByName('Message').AsString, FDesKey);
      except
        FreeAndNil(SearchResult);
        Continue;
      end;
   
      Result.FMessages.Add(SearchResult);
      
      ADODataSet.Next;
    end;

  finally
    FDesKey := Copy(MD5En(FLoginName), 25, 8);
    FreeAndNil(ADODataSetGetRecordCount);
    FreeAndNil(ADODataSet);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.SaveSMSMessage(ASender, AReceiver: String;
  ASendDateTime: TDateTime; AContent: String; AMessageType: Integer);
var
  ADOQuery: TADOQuery;
begin
  
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('INSERT INTO Messages ('
                     + 'TeamID,'
                     + '[Sender],'
                     + '[Receiver],'
                     + 'SendDateTime,'
                     + 'Font,'
                     + 'Message) VALUES ('
                     + '''' + IntToStr(AMessageType) + ''','
                     + '''' + AnsiReplaceStr(ASender, '''', '''''') + ''','
                     + '''' + AnsiReplaceStr(AReceiver, '''', '''''') + ''','
                     + FloatToStr(ASendDateTime) + ','
                     + ''''','
                     + '''' + EncryStrHex(AContent, FDesKey) + ''')');
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.SaveMessage(ATeamID: String; ASender, AReceiver: String;
        ASendDateTime: TDateTime; AFont: String; AMessage: String);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('INSERT INTO Messages ('
                     + 'TeamID,'
                     + '[Sender],'
                     + '[Receiver],'
                     + 'SendDateTime,'
                     + 'Font,'
                     + 'Message) VALUES ('
                     + '''' + ATeamID + ''','
                     + '''' + AnsiReplaceStr(ASender, '''', '''''') + ''','
                     + '''' + AnsiReplaceStr(AReceiver, '''', '''''') + ''','
                     + FloatToStr(ASendDateTime) + ','
                     + '''' + AnsiReplaceStr(AFont, '''', '''''') + ''','
                     + '''' + EncryStrHex(AMessage, FDesKey) + ''')');
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.SaveSystemMessage(AMessageID: Integer;
        AMessageType: TRealICQSystemMessageType;
        APositionType: TRealICQSystemMessagePosition;
        ALeft, ATop, AWidth, AHeight: Integer;
        ATitle, AContent, AUrl: String;
        AAutoCloseTime: Integer);
var
  ADOQuery: TADOQuery;
begin
  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.Connection := FADOConnection;
    ADOQuery.SQL.Add('INSERT INTO SystemMessages ('
                     + 'MessageID,'
                     + 'MessageType,'
                     + 'PositionType,'
                     + '[Left],'
                     + '[Top],'
                     + '[Width],'
                     + '[Height],'
                     + '[Title],'
                     + '[Content],'
                     + '[Url],'
                     + 'AutoCloseTime,'
                     + 'SendDateTime) VALUES ('
                     + IntToStr(AMessageID) + ','
                     + IntToStr(Byte(AMessageType)) + ','
                     + IntToStr(Byte(APositionType)) + ','
                     + IntToStr(ALeft) + ','
                     + IntToStr(ATop) + ','
                     + IntToStr(AWidth) + ','
                     + IntToStr(AHeight) + ','
                     + '''' + EncryStrHex(ATitle, FDesKey) + ''','
                     + '''' + EncryStrHex(AContent, FDesKey) + ''','
                     + '''' + EncryStrHex(AUrl, FDesKey) + ''','
                     + IntToStr(AAutoCloseTime) + ','
                     + FloatToStr(Now) + ')');
    ADOQuery.ExecSQL;
  finally
    FreeAndNil(ADOQuery);
  end;
end;


//------------------------------------------------------------------------------
procedure TRealICQDBHistory.SetLoginName(Value: String);
begin
  FLoginName := Value;
  if Pos('+', FLoginName) > 0 then FLoginName := Copy(FLoginName, Pos('+', FLoginName) + 1, Length(FLoginName)); 
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.SetDBFileName(Value: String);
begin
  FDBFileName := Value;
  try
    if FADOConnection.Connected then FADOConnection.Close;
  except
  end;
  if FDBFileName = '' then Exit;
  
  FADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; ' +
                                     'Data Source=' + FDBFileName + ';';
  FADOConnection.Open;
  FDesKey := Copy(MD5En(FLoginName), 25, 8);
  //CheckFieldTeamID;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.CheckFieldTeamID;
var
  ADOCommand: TADOCommand;
begin
  try
    ADOCommand := TADOCommand.Create(nil);
    try
      ADOCommand.Connection := FADOConnection;
      ADOCommand.CommandText := 'ALTER TABLE Messages ALTER COLUMN TeamID Char';
      ADOCommand.Execute;
    finally
      ADOCommand.Free;
    end;
  except
  end;
end;

//------------------------------------------------------------------------------
procedure TRealICQDBHistory.RestoreMessageHistory(ADBFileName: String);
var
  AADOConnection: TADOConnection;
  ADODataSet: TADODataSet;
  ADODataSet1: TADODataSet;
  ADOQuery: TADOQuery;
  
  ATeamID: String;
  ASender: String;
  AReceiver: String;
  ASendDateTime: TDateTime;
  AFont: String;
  AMessage: String;
  SearchResult: TSystemMessageSearchResult;
begin
  try
    AADOConnection := TADOConnection.Create(nil);
    ADODataSet := TADODataSet.Create(nil);
    ADODataSet1 := TADODataSet.Create(nil);
    ADOQuery := TADOQuery.Create(nil);

    AADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; ' +
                                     'Data Source=' + ADBFileName + ';';
    AADOConnection.Open;
    

    ADOQuery.Connection := FADOConnection;
    ADODataSet1.Connection := FADOConnection;
    ADODataSet.Connection := AADOConnection;

    ADODataSet.CommandText := 'SELECT * FROM Messages';    
    ADODataSet.Open;
    
    while not ADODataSet.Eof do
    begin
      ATeamID := ADODataSet.FieldByName('TeamID').AsString;
      ASender:= ADODataSet.FieldByName('Sender').AsString;
      AReceiver := ADODataSet.FieldByName('Receiver').AsString;
      ASendDateTime := ADODataSet.FieldByName('SendDateTime').AsFloat;
      AFont := ADODataSet.FieldByName('Font').AsString;
      AMessage := ADODataSet.FieldByName('Message').AsString;

      ADODataSet1.CommandText := 'Select ID From Messages Where '
                       + 'SendDateTime = ' + FloatToStr(ASendDateTime)
                       + ' and Message = ''' + AMessage + '''';
      ADODataSet1.Open;
                       
      if ADODataSet1.Eof then
      begin
        ADOQuery.SQL.Clear;
        ADOQuery.SQL.Add('INSERT INTO Messages ('
                         + 'TeamID,'
                         + '[Sender],'
                         + '[Receiver],'
                         + 'SendDateTime,'
                         + 'Font,'
                         + 'Message) VALUES ('
                         + '''' + ATeamID + ''','
                         + '''' + AnsiReplaceStr(ASender, '''', '''''') + ''','
                         + '''' + AnsiReplaceStr(AReceiver, '''', '''''') + ''','
                         + FloatToStr(ASendDateTime) + ','
                         + '''' + AnsiReplaceStr(AFont, '''', '''''') + ''','
                         + '''' + AMessage + ''')');
        try
          ADOQuery.ExecSQL;
        except
        end;
      end;
      ADODataSet1.Close;
      ADODataSet.Next;
    end;
    ADODataSet.Close;

     
    ADODataSet.CommandText := 'SELECT * FROM SystemMessages';    
    ADODataSet.Open;
    
    while not ADODataSet.Eof do
    begin
      SearchResult := TSystemMessageSearchResult.Create;
      SearchResult.FID := ADODataSet.FieldByName('ID').AsInteger; 
      SearchResult.FMessageID := ADODataSet.FieldByName('MessageID').AsInteger;
      SearchResult.FMessageType := TRealICQSystemMessageType(ADODataSet.FieldByName('MessageType').AsInteger);
      SearchResult.FPositionType := TRealICQSystemMessagePosition(ADODataSet.FieldByName('PositionType').AsInteger);
      SearchResult.FLeft := ADODataSet.FieldByName('Left').AsInteger;
      SearchResult.FTop := ADODataSet.FieldByName('Top').AsInteger;
      SearchResult.FWidth := ADODataSet.FieldByName('Width').AsInteger;
      SearchResult.FHeight := ADODataSet.FieldByName('Height').AsInteger;
      SearchResult.FAutoCloseTime := ADODataSet.FieldByName('AutoCloseTime').AsInteger;
      SearchResult.FSendDateTime := ADODataSet.FieldByName('SendDateTime').AsFloat;
      SearchResult.FTitle := ADODataSet.FieldByName('Title').AsString;
      SearchResult.FContent := ADODataSet.FieldByName('Content').AsString;
      SearchResult.FUrl := ADODataSet.FieldByName('Url').AsString;

      ADODataSet1.CommandText := 'Select ID From SystemMessages Where '
                       + 'SendDateTime = ' + FloatToStr(SearchResult.FSendDateTime)
                       + ' and MessageID = ' + IntToStr(Integer(SearchResult.FMessageID));
      ADODataSet1.Open;
                       
      if ADODataSet1.Eof then
      begin
        ADOQuery.SQL.Clear;
        ADOQuery.SQL.Add('INSERT INTO SystemMessages ('
                         + 'MessageID,'
                         + 'MessageType,'
                         + 'PositionType,'
                         + '[Left],'
                         + '[Top],'
                         + '[Width],'
                         + '[Height],'
                         + '[Title],'
                         + '[Content],'
                         + '[Url],'
                         + 'AutoCloseTime,'
                         + 'SendDateTime) VALUES ('
                         + IntToStr(SearchResult.FMessageID) + ','
                         + IntToStr(Byte(SearchResult.FMessageType)) + ','
                         + IntToStr(Byte(SearchResult.FPositionType)) + ','
                         + IntToStr(SearchResult.FLeft) + ','
                         + IntToStr(SearchResult.FTop) + ','
                         + IntToStr(SearchResult.FWidth) + ','
                         + IntToStr(SearchResult.FHeight) + ','
                         + '''' + SearchResult.FTitle + ''','
                         + '''' + SearchResult.FContent + ''','
                         + '''' + SearchResult.FUrl + ''','
                         + IntToStr(SearchResult.FAutoCloseTime) + ','
                         + FloatToStr(SearchResult.FSendDateTime) + ')');
        FreeAndNil(SearchResult);
        try
          ADOQuery.ExecSQL;
        except
        end;
      end;
      ADODataSet1.Close;
      ADODataSet.Next;
    end;
    ADODataSet.Close;   
  finally
    FreeAndNil(ADOQuery);
    FreeAndNil(ADODataSet);
    FreeAndNil(ADODataSet1);
    if AADOConnection.Connected then AADOConnection.Close;
    FreeAndNil(AADOConnection);
  end;
end;

//------------------------------------------------------------------------------
constructor TRealICQDBHistory.Create;
begin
  FADOConnection := TADOConnection.Create(nil);
end;

//------------------------------------------------------------------------------
destructor TRealICQDBHistory.Destroy;
begin
  try
    try
      if FADOConnection.Connected then FADOConnection.Close;
    except
    end;
    FreeAndNil(FADOConnection);
  finally
    inherited Destroy;
  end;
end;

end.