msgdatabasemysql.pas

Delphi MsgCommunicator 2-10 component.I ve used, really good job. can be Server-Client message appl

        end;
       mscmpContains:
        begin
         if (FirstNameComparison.CaseInsensitive) then
          condition_FirstName := '(UPPER(FirstName) LIKE '+
                               AnsiQuotedStr('%'+AnsiUpperCase(FirstName)+'%','''')+')'
         else
          condition_FirstName := '(FirstName LIKE '+
                               AnsiQuotedStr('%'+FirstName+'%','''')+')';
        end;
      end; // FirstName
    if (LastName <> '') then
      case LastNameComparison.Comparison of
       mscmpExact:
        begin
         if (LastNameComparison.CaseInsensitive) then
          condition_LastName := '(UPPER(LastName) = '+
                               AnsiQuotedStr(AnsiUpperCase(LastName),'''')+')'
         else
          condition_LastName := '(LastName = '+
                               AnsiQuotedStr(LastName,'''')+')';
        end;
       mscmpStarts:
        begin
         if (LastNameComparison.CaseInsensitive) then
          condition_LastName := '(UPPER(LastName) LIKE '+
                               AnsiQuotedStr(AnsiUpperCase(LastName)+'%','''')+')'
         else
          condition_LastName := '(LastName LIKE '+
                               AnsiQuotedStr(LastName+'%','''')+')';
        end;
       mscmpContains:
        begin
         if (LastNameComparison.CaseInsensitive) then
          condition_LastName := '(UPPER(LastName) LIKE '+
                               AnsiQuotedStr('%'+AnsiUpperCase(LastName)+'%','''')+')'
         else
          condition_LastName := '(LastName LIKE '+
                               AnsiQuotedStr('%'+LastName+'%','''')+')';
        end;
      end; // LastName
    if (Organization <> '') then
      case OrganizationComparison.Comparison of
       mscmpExact:
        begin
         if (OrganizationComparison.CaseInsensitive) then
          condition_Organization := '(UPPER(Organization) = '+
                               AnsiQuotedStr(AnsiUpperCase(Organization),'''')+')'
         else
          condition_Organization := '(Organization = '+
                               AnsiQuotedStr(Organization,'''')+')';
        end;
       mscmpStarts:
        begin
         if (OrganizationComparison.CaseInsensitive) then
          condition_Organization := '(UPPER(Organization) LIKE '+
                               AnsiQuotedStr(AnsiUpperCase(Organization)+'%','''')+')'
         else
          condition_Organization := '(Organization LIKE '+
                               AnsiQuotedStr(Organization+'%','''')+')';
        end;
       mscmpContains:
        begin
         if (OrganizationComparison.CaseInsensitive) then
          condition_Organization := '(UPPER(Organization) LIKE '+
                               AnsiQuotedStr('%'+AnsiUpperCase(Organization)+'%','''')+')'
         else
          condition_Organization := '(Organization LIKE '+
                               AnsiQuotedStr('%'+Organization+'%','''')+')';
        end;
      end; // Organization
    if (Department <> '') then
      case DepartmentComparison.Comparison of
       mscmpExact:
        begin
         if (DepartmentComparison.CaseInsensitive) then
          condition_Department := '(UPPER(Department) = '+
                               AnsiQuotedStr(AnsiUpperCase(Department),'''')+')'
         else
          condition_Department := '(Department = '+
                               AnsiQuotedStr(Department,'''')+')';
        end;
       mscmpStarts:
        begin
         if (DepartmentComparison.CaseInsensitive) then
          condition_Department := '(UPPER(Department) LIKE '+
                               AnsiQuotedStr(AnsiUpperCase(Department)+'%','''')+')'
         else
          condition_Department := '(Department LIKE '+
                               AnsiQuotedStr(Department+'%','''')+')';
        end;
       mscmpContains:
        begin
         if (DepartmentComparison.CaseInsensitive) then
          condition_Department := '(UPPER(Department) LIKE '+
                               AnsiQuotedStr('%'+AnsiUpperCase(Department)+'%','''')+')'
         else
          condition_Department := '(Department LIKE '+
                               AnsiQuotedStr('%'+Department+'%','''')+')';
        end;
      end; // Department
    if (Host <> '') then
      case HostComparison.Comparison of
       mscmpExact:
        begin
         if (HostComparison.CaseInsensitive) then
          condition_Host := '(UPPER(Host) = '+
                               AnsiQuotedStr(AnsiUpperCase(Host),'''')+')'
         else
          condition_Host := '(Host = '+
                               AnsiQuotedStr(Host,'''')+')';
        end;
       mscmpStarts:
        begin
         if (HostComparison.CaseInsensitive) then
          condition_Host := '(UPPER(Host) LIKE '+
                               AnsiQuotedStr(AnsiUpperCase(Host)+'%','''')+')'
         else
          condition_Host := '(Host LIKE '+
                               AnsiQuotedStr(Host+'%','''')+')';
        end;
       mscmpContains:
        begin
         if (HostComparison.CaseInsensitive) then
          condition_Host := '(UPPER(Host) LIKE '+
                               AnsiQuotedStr('%'+AnsiUpperCase(Host)+'%','''')+')'
         else
          condition_Host := '(Host LIKE '+
                               AnsiQuotedStr('%'+Host+'%','''')+')';
        end;
      end; // Host
    if (Application <> '') then
      case ApplicationComparison.Comparison of
       mscmpExact:
        begin
         if (ApplicationComparison.CaseInsensitive) then
          condition_Application := '(UPPER(Application) = '+
                               AnsiQuotedStr(AnsiUpperCase(Application),'''')+')'
         else
          condition_Application := '(Application = '+
                               AnsiQuotedStr(Application,'''')+')';
        end;
       mscmpStarts:
        begin
         if (ApplicationComparison.CaseInsensitive) then
          condition_Application := '(UPPER(Application) LIKE '+
                               AnsiQuotedStr(AnsiUpperCase(Application)+'%','''')+')'
         else
          condition_Application := '(Application LIKE '+
                               AnsiQuotedStr(Application+'%','''')+')';
        end;
       mscmpContains:
        begin
         if (ApplicationComparison.CaseInsensitive) then
          condition_Application := '(UPPER(Application) LIKE '+
                               AnsiQuotedStr('%'+AnsiUpperCase(Application)+'%','''')+')'
         else
          condition_Application := '(Application LIKE '+
                               AnsiQuotedStr('%'+Application+'%','''')+')';
        end;
      end; // Application
    if (UserID <> MSG_INVALID_USER_ID) then
     condition_UserID := '(ID = '+IntToStr(Integer(UserID))+')';
    if (Status <> msgNone) then
     condition_Status := '(Status = '+IntToStr(Byte(Status))+')';
    if (PortComparison.Comparison1 <> mcmpopNone) then
      begin
       condition_Port := '(Port ';
       case PortComparison.Comparison1 of
        mcmpopEqual:  condition_Port := condition_Port + ' = ';
        mcmpopGreater:  condition_Port := condition_Port + ' > ';
        mcmpopLower:  condition_Port := condition_Port + ' < ';
        mcmpopNotEqual:  condition_Port := condition_Port + ' <> ';
        mcmpopGreaterEqual:  condition_Port := condition_Port + ' >= ';
        mcmpopLowerEqual:  condition_Port := condition_Port + ' <= ';
       end;
       condition_Port := condition_Port+IntToStr(PortComparison.Value1)+')';
      end;
    if (PortComparison.Comparison2 <> mcmpopNone) then
      begin
       if (condition_Port = '') then
        condition_Port := '(Port '
       else
        condition_Port := condition_Port + ' AND (Port ';
       case PortComparison.Comparison2 of
        mcmpopEqual:  condition_Port := condition_Port + ' = ';
        mcmpopGreater:  condition_Port := condition_Port + ' > ';
        mcmpopLower:  condition_Port := condition_Port + ' < ';
        mcmpopNotEqual:  condition_Port := condition_Port + ' <> ';
        mcmpopGreaterEqual:  condition_Port := condition_Port + ' >= ';
        mcmpopLowerEqual:  condition_Port := condition_Port + ' <= ';
       end;
       condition_Port := condition_Port+IntToStr(PortComparison.Value2)+')';
      end;
    Result := AddCondition(Result,condition_UserName);
    Result := AddCondition(Result,condition_FirstName);
    Result := AddCondition(Result,condition_LastName);
    Result := AddCondition(Result,condition_Organization);
    Result := AddCondition(Result,condition_Department);
    Result := AddCondition(Result,condition_Host);
    Result := AddCondition(Result,condition_Port);
    Result := AddCondition(Result,condition_Application);
    Result := AddCondition(Result,condition_UserID);
    Result := AddCondition(Result,condition_Status);
   end; // SearchCondition is empty - check all conditions
  if (Result <> '') then
   Result := 'WHERE '+Result;
 end; // GetCondition

begin
  Query := TMsgMySQLQuery.Create(Self,False);
  try
    if (Ascending) then
     s1 := ' ASC'
    else
     s1 := ' DESC';
    case SortBy of
    msgusbUserID: s := 'ORDER BY ID'+s1;
    msgusbUserName: s := 'ORDER BY UserName'+s1;
    msgusbFirstName: s := 'ORDER BY FirstName'+s1;
    msgusbLastName: s := 'ORDER BY LastName'+s1;
    msgusbStatus: s := 'ORDER BY Status'+s1;
    msgusbHost: s := 'ORDER BY Host'+s1;
    msgusbPort: s := 'ORDER BY Port'+s1;
    msgusbApplication: s := 'ORDER BY Application'+s1;
    else
     s := '';
    end;
    if (OrderByClause <> '') then
     s := OrderByClause;
    condition := GetCondition;

//    Query.RequestLive := True;
    Query.SQL.Text := 'SELECT * FROM '+UsersTableName+' '+condition+' '+s;
    Query.Dataset.Open;
    i := 0;
    SetLength(Users,i);
    Query.Dataset.First;
    while (not Query.Dataset.Eof) do
     begin
      Inc(i);
      SetLength(Users,i);
      Users[i-1] := ExtractUserInfo(Query.Dataset);
      Query.Dataset.Next;
     end;
  finally
    Query.Free;
  end;
end; // FindUsers


//------------------------------------------------------------------------------
// Get user contacts (array of UserID)
//------------------------------------------------------------------------------
procedure TMsgDatabaseMySQL.GetUserContacts(const UserID: Cardinal; var Contacts: TMsgContactInfoArray);
var DB:     TDatabase;
    Query:  TMsgMySQLQuery;
    i:      Integer;
begin
  Query := TMsgMySQLQuery.Create(Self,False);
  try
    Query.RequestLive := False;
    Query.SQL.Text := 'SELECT u.*, c.ContactNameSource,c.ContactCustomName FROM '+
                      UsersTableName+' AS u, '+
                      ContactsTableName+' AS c'+
                      ' WHERE (u.ID = c.ContactID) '
                      +' AND (c.OwnerID = '+
                      IntToStr(Integer(UserID))+')';
    Query.Dataset.Open;
    i := 0;
    SetLength(Contacts,i);
    Query.Dataset.First;
    while (not Query.Dataset.Eof) do
     begin
      Inc(i);
      SetLength(Contacts,i);
      Contacts[i-1].UserInfo := ExtractUserInfo(Query.Dataset);
      Contacts[i-1].ContactNameSource := TMsgContactNameSource(Query.Dataset.FieldByName('ContactNameSource').AsInteger);
      Contacts[i-1].ContactCustomName := Query.Dataset.FieldByName('ContactCustomName').AsString;
      Query.Dataset.Next;
     end;
  finally
    Query.Free;
  end;
end; // GetUserContacts


//------------------------------------------------------------------------------
// Add user to contact list of another user
//------------------------------------------------------------------------------
procedure TMsgDatabaseMySQL.AddUserToContacts(
                            const OwnerUserID:        Cardinal;
                            const ContactUserID:      Cardinal;
                            const ContactNameSource:  TMsgContactNameSource = mcnsUserName;
                            const ContactCustomName:  ShortString = ''
                                                  );
var DB:     TDatabase;
    Query:  TMsgMySQLQuery;
    bOK:    Boolean;
    cnt:    Integer;
begin
  Query := TMsgMySQLQuery.Create(Self,True);
  try
    Query.SQL.Text := 'INSERT INTO '+ContactsTableName+
                      ' (OwnerID,ContactID,ContactNameSource,ContactCustomName) VALUES ('+crlf+
                      IntToStr(Integer(OwnerUserID))+','+crlf+
                      IntToStr(Integer(ContactUserID))+','+crlf+
                      IntToStr(Byte(ContactNameSource))+','+crlf+
                      AnsiQuotedStr(ContactCustomName,'''')+crlf+
                      ');'+crlf;
    bOK := False;
    cnt := FRetryCount;
    while (not bOK) and (cnt >= 0) do
     begin
      Query.StartTransaction;
      if (not UserExists(OwnerUserID)) then
       begin
         Query.Rollback;
         raise EMsgException.Create(11368,ErrorLUserDoesNotExist,[OwnerUserID]);
       end;
      if (not UserExists(ContactUserID)) then
       begin
         Query.Rollback;
         raise EMsgException.Create(11369,ErrorLUserDoesNotExist,[ContactUserID]);
       end;
      try
       Query.ExecSQL;
       Query.Commit;
       bOK := True;
      except
       Query.Rollback;
       Dec(cnt);
      end;
     end;
    if (cnt < 0) then
     raise EMsgException.Create(11370,ErrorLAddContactTransactionFailed,
       [OwnerUserID,ContactUserID,FRetryCount,FDelay]);
  finally
    Query.Free;
  end;
end; // AddUserToContacts


//------------------------------------------------------------------------------
// update user in contact list of another user
//------------------------------------------------------------------------------
procedure TMsgDatabaseMySQL.UpdateUserInContacts(
                            const OwnerUserID:        Cardinal;
                            const ContactUserID:      Cardinal;
                            const ContactNameSource:  TMsgContactNameSource = mcnsUserName;
                            const ContactCustomName:  ShortString = ''
                                                  );
var DB:     TDatabase;
    Query:  TMsgMySQLQuery;
    bOK:    Boolean;
    cnt:    Integer;
begin
  Query := TMsgM