{ 文件名：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.