Example #1
0
        public CompleteSearchContext(
            string toSearchWhat,
            string toSearchFromWho,
            SearchWhatFlags searchFromWhoMethod,
            SearchWhatFlags searchWhatMethod,
            int userID,
            int boardID,
            int maxResults,
            bool useFullText,
            bool searchDisplayName,
            int[] forumIds = null)
        {
            if (toSearchWhat == "*")
            {
                toSearchWhat = string.Empty;
            }

            this.ToSearchWhat = toSearchWhat;
            this.ToSearchFromWho = toSearchFromWho;
            this.SearchFromWhoMethod = searchFromWhoMethod;
            this.SearchWhatMethod = searchWhatMethod;
            this.UserID = userID;
            this.BoardID = boardID;
            this.MaxResults = maxResults;
            this.UseFullText = useFullText;
            this.SearchDisplayName = searchDisplayName;
            this.ForumIDs = forumIds;
        }
Example #2
0
 public CompleteSearchContext(
     string toSearchWhat,
     string toSearchFromWho,
     SearchWhatFlags searchFromWhoMethod,
     SearchWhatFlags searchWhatMethod,
     int userID,
     int boardID,
     int maxResults,
     bool useFullText,
     bool searchDisplayName,
     int startFromForumId)
     : this(
         toSearchWhat,
         toSearchFromWho,
         searchFromWhoMethod,
         searchWhatMethod,
         userID,
         boardID,
         maxResults,
         useFullText,
         searchDisplayName)
 {
     this.ForumIDs = GetForumIDsStartingWith(startFromForumId, boardID, userID);
 }
Example #3
0
        /// <summary>
        /// The build search sql.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="toSearchWhat">
        /// The to search what.
        /// </param>
        /// <param name="toSearchFromWho">
        /// The to search from who.
        /// </param>
        /// <param name="searchFromWhoMethod">
        /// The search from who method.
        /// </param>
        /// <param name="searchWhatMethod">
        /// The search what method.
        /// </param>
        /// <param name="userId">
        /// The user id.
        /// </param>
        /// <param name="searchDisplayName">
        /// The search display name.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <param name="maxResults">
        /// The max results.
        /// </param>
        /// <param name="useFullText">
        /// The use full text.
        /// </param>
        /// <param name="categoriesIds">
        /// The categoriesIds.
        /// </param>
        /// <param name="ids">
        /// The forum ids.
        /// </param>
        /// <param name="forumIdToStartAt">
        /// The forum Id To Start At.
        /// </param>
        /// <param name="culture">
        /// The culture.
        /// </param>
        /// <returns>
        /// The build search sql.
        /// </returns>
        public string BuildSearchSql(
            int? mid,
            [NotNull] string toSearchWhat,
            [NotNull] string toSearchFromWho,
            SearchWhatFlags searchFromWhoMethod,
            SearchWhatFlags searchWhatMethod,
            int userId,
            bool searchDisplayName,
            int boardId,
            int maxResults,
            bool useFullText,
            string categoriesIds,
            string ids,
            [NotNull] IEnumerable<int> forumIdToStartAt,
            string culture)
        {
            CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
            CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");

            string limitString = string.Empty;
            string orderString = string.Empty;
            var searchSql = new StringBuilder();
            string forumIDs = ids;
            string ftsLanguage = "simple";
            culture = culture.Substring(0, 2);
            foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.SpecificCultures))
            {
                if (ci.Parent.Name == culture)
                {
                    switch (culture)
                    {
                        case "en":
                            ftsLanguage = "english";
                            break;
                        case "ru":
                            ftsLanguage = "russian";
                            break;
                        case "sp":
                            ftsLanguage = "spanish";
                            break;
                        case "fr":
                            ftsLanguage = "french";
                            break;
                        case "de":
                            ftsLanguage = "german";
                            break;
                        case "da":
                            ftsLanguage = "danish";
                            break;
                        case "nl":
                            ftsLanguage = "dutch";
                            break;
                        case "fi":
                            ftsLanguage = "finnish";
                            break;
                        case "hu":
                            ftsLanguage = "hungarian";
                            break;
                        case "it":
                            ftsLanguage = "italian";
                            break;
                        case "nn":
                            ftsLanguage = "norwegian";
                            break;
                        case "pt":
                            ftsLanguage = "portugues";
                            break;
                        case "ro":
                            ftsLanguage = "romanian";
                            break;
                        case "se":
                            ftsLanguage = "swedish";
                            break;
                        case "tr":
                            ftsLanguage = "turkish";
                            break;
                    }
                }

                break;
            }
           
            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
            searchSql.Append(maxResults == 0 ? "SELECT " : "SELECT DISTINCT ");

            if (maxResults > 0)
            {
                limitString += string.Format(" LIMIT {0} ", maxResults);
            }

            searchSql.Append(
                " a.forumid, a.topicid, a.topic, b.userid, COALESCE(c.username, b.name) as Name, c.messageid as \"MessageID\", c.posted, c.message as \"Message\", c.flags FROM ");
            searchSql.Append(SqlDbAccess.GetVzfObjectName("topic", mid));
            searchSql.Append(" a JOIN ");
            searchSql.Append(SqlDbAccess.GetVzfObjectName("forum", mid));
            searchSql.Append(" f ON f.forumid = a.forumid LEFT JOIN ");
            searchSql.Append(SqlDbAccess.GetVzfObjectName("message", mid));
            searchSql.Append(" c ON a.topicid = c.topicid LEFT JOIN ");
            searchSql.Append(SqlDbAccess.GetVzfObjectName("user", mid));
            searchSql.Append(" b ON c.userid = b.userid join ");
            searchSql.Append(SqlDbAccess.GetVzfObjectName("vaccess", mid));
            searchSql.Append(" x ON x.forumid=a.forumid ");
            searchSql.Append(
                "WHERE x.readaccess<>0 AND x.userid={0} AND c.isapproved IS TRUE AND a.topicmovedid IS NULL AND a.isdeleted IS FALSE AND c.isdeleted IS FALSE "
                    .FormatWith(userId));


            orderString += "ORDER BY a.forumid ";

            string[] words;

            bool bFirst = false;

            if (!string.IsNullOrEmpty(toSearchFromWho) || !string.IsNullOrEmpty(toSearchWhat))
            {
                searchSql.Append("AND (");
                bFirst = true;
            }

            if (!string.IsNullOrEmpty(toSearchFromWho))
            {
                // generate user search sql...
                switch (searchFromWhoMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql.Append(" AND ");
                            }
                            else
                            {
                                bFirst = false;
                            }

                            // searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
                            if (!int.TryParse(word, out userId))
                            {
                                if (searchDisplayName)
                                {
                                    searchSql.Append(
                                        string.Format(
                                            " ((c.userdisplayname IS NULL AND b.displayname  ~* '.*{0}.*') OR (c.userdisplayname ~* '.*{0}.*'))",
                                            word));
                                }
                                else
                                {
                                    searchSql.Append(
                                        string.Format(
                                            " ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))",
                                            word));
                                }
                            }
                            else
                            {
                                searchSql.Append(string.Format(" (c.userid IN ({0}))", userId));
                            }
                        }

                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql.Append(" OR ");
                            }
                            else
                            {
                                bFirst = false;
                            }

                            if (searchDisplayName)
                            {
                                searchSql.Append(
                                    string.Format(
                                        " ((c.userdisplayname IS NULL AND b.displayname  ~* '.*{0}.*') OR (c.userdisplayname ~* '.*{0}.*'))",
                                        word));
                            }
                            else
                            {
                                searchSql.Append(
                                string.Format(
                                    " ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))",
                                    word));
                            }
                        }

                        break;
                    case SearchWhatFlags.ExactMatch:

                        // searchSql += string.Format(" ((c.username IS NULL AND b.name = '{0}' ) OR (c.username = '{0}' ))", toSearchFromWho);
                        if (!int.TryParse(toSearchFromWho, out userId))
                        {
                            if (searchDisplayName)
                            {
                                searchSql.Append(
                                    string.Format(
                                        " ((c.userdisplayname IS NULL AND b.displayname = '{0}') OR (c.userdisplayname = '{0}'))",
                                        toSearchFromWho));
                            }
                            else
                            {
                                searchSql.Append(
                                    string.Format(
                                        " ((c.username IS NULL AND b.name = '{0}') OR (c.username = '{0}'))",
                                        toSearchFromWho));
                            }
                        }
                        else
                        {
                            searchSql.Append(string.Format(" (c.userid IN ({0})) ", userId));
                        }

                        break;
                }

                searchSql.Append(") ");
            }

            if (!string.IsNullOrEmpty(toSearchWhat))
            {
                // generate message and topic search sql...
                switch (searchWhatMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchWhat.RemoveDoubleWhiteSpaces().Split(' ');
                        if (useFullText)
                        {
                            string ftInner = string.Empty;

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst)
                                {
                                    ftInner += " & ";
                                }
                                else
                                {
                                    bFirst = false;
                                }

                                ftInner += string.Format("{0}", word);
                            }

                            // make final string...
                            searchSql.Append(
                                string.Format(
                                    "select to_tsvector('{0}',c.message) @@ to_tsquery('{0}','{1}') OR to_tsvector('{0}',a.topic) @@ to_tsquery('{0}','{1}') ",
                                     ftsLanguage, ftInner.Trim().Trim('&').Trim()));
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst)
                                {
                                    searchSql.Append(" AND ");
                                }
                                else
                                {
                                    bFirst = false;
                                }


                                searchSql.Append(
                                    string.Format("(c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' )", word));
                            }
                        }

                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchWhat.RemoveDoubleWhiteSpaces().Split(' ');

                        if (useFullText)
                        {
                            string ftInner = string.Empty;
                            string ftInnerBody = string.Empty;

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst)
                                {
                                  //  ftInner += " OR ";
                                    ftInnerBody += " | ";
                                }
                                else
                                {
                                    bFirst = false;
                                }

                               // ftInner += string.Format(@"""{0}""", word);
                                ftInnerBody += string.Format("{0}", word);

                                if (int.TryParse(word, out userId))
                                {
                                   // searchSql.Append(string.Format(" (c.userid IN ({0}))", userId));
                                }
                                else
                                {
                                    /* searchSql.Append(
                                        string.Format(
                                            " ((c.username IS NULL AND b.name LIKE '%{0}%') OR (c.username like '{0}%'))",
                                            word)); */
                                }
                            }

                            // make final string...
                            searchSql.Append(
                                string.Format(
                                    "SELECT to_tsvector('{0}',c.message) @@ to_tsquery('{0}','{1}') OR to_tsvector('{0}',a.topic) @@ to_tsquery('{0}','{1}') ",
                                    ftsLanguage,
                                    ftInnerBody.Trim().Trim('|').Trim()));
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql.Append(" OR ");
                                else bFirst = false;
                                searchSql.Append(
                                    string.Format("c.message ~* '.*{0}.*'  OR a.topic ~* '.*{0}.*' ", word));
                            }
                        }

                        break;
                    case SearchWhatFlags.ExactMatch:
                         words = toSearchWhat.Split(' ');
                        if (useFullText)
                        {
                            string ftInner = string.Empty;

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst)
                                {
                                    ftInner += " & ";
                                }
                                else
                                {
                                    bFirst = false;
                                }

                                ftInner += string.Format("{0}", word);
                            }

                            // make final string...
                            searchSql.Append(
                                string.Format(
                                    "select to_tsvector('{0}',c.message) @@ to_tsquery('{0}','{1}') OR to_tsvector('{0}',a.topic) @@ to_tsquery('{0}','{1}') ",
                                     ftsLanguage, ftInner.Trim().Trim('&').Trim()));
                        }
                        else
                        {
                            searchSql.Append(
                                string.Format("c.message ~* '.*{0}.*'  OR a.topic ~* '.*{0}.*'  ", toSearchWhat));
                        }

                        break;
                }

                searchSql.Append(") ");
            }

            // vzrus
            if (categoriesIds.IsSet())
            {
                searchSql.Append(string.Format(" AND f.categoryid IN ({0})", categoriesIds));
            }

            if (forumIDs.IsSet())
            {
                searchSql.Append(string.Format(" AND a.forumid IN ({0})", forumIDs));
            }

            if (orderString != string.Empty)
            {
                orderString += ", ";
            }

            if (!orderString.Contains("ORDER BY"))
            {
                searchSql.Append("ORDER BY ");
            }

            searchSql.Append(orderString + "c.posted DESC ");

            if (!orderString.Contains("LIMIT"))
            {
                searchSql.Append(limitString);
            }

            return searchSql.ToString();
        }
    /// <summary>
    /// The build search sql.
    /// </summary>
    /// <param name="toSearchWhat">
    /// The to search what.
    /// </param>
    /// <param name="toSearchFromWho">
    /// The to search from who.
    /// </param>
    /// <param name="searchFromWhoMethod">
    /// The search from who method.
    /// </param>
    /// <param name="searchWhatMethod">
    /// The search what method.
    /// </param>
    /// <param name="userID">
    /// The user id.
    /// </param>
    /// <param name="searchDisplayName">
    /// The search display name.
    /// </param>
    /// <param name="boardId">
    /// The board id.
    /// </param>
    /// <param name="maxResults">
    /// The max results.
    /// </param>
    /// <param name="useFullText">
    /// The use full text.
    /// </param>
    /// <param name="forumIds">
    /// The forum ids.
    /// </param>
    /// <returns>
    /// The build search sql.
    /// </returns>
    public string BuildSearchSql(
      [NotNull] string toSearchWhat, 
      [NotNull] string toSearchFromWho, 
      SearchWhatFlags searchFromWhoMethod, 
      SearchWhatFlags searchWhatMethod, 
      int userID, 
      bool searchDisplayName, 
      int boardId, 
      int maxResults, 
      bool useFullText, 
      [NotNull] IEnumerable<int> forumIds)
    {
      CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
      CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
      CodeContracts.ArgumentNotNull(forumIds, "forumIds");

      var builtStatements = new List<string>();

      if (maxResults > 0)
      {
        builtStatements.Add("SET ROWCOUNT {0};".FormatWith(maxResults));
      }

      string searchSql =
        "SELECT a.ForumID, a.TopicID, a.Topic, b.UserID, IsNull(c.Username, b.Name) as Name, c.MessageID, c.Posted, [Message] = '', c.Flags ";
      searchSql +=
        "\r\nfrom {databaseOwner}.{objectQualifier}topic a left join {databaseOwner}.{objectQualifier}message c on a.TopicID = c.TopicID left join {databaseOwner}.{objectQualifier}user b on c.UserID = b.UserID join {databaseOwner}.{objectQualifier}vaccess x on x.ForumID=a.ForumID ";
      searchSql +=
        "\r\nwhere x.ReadAccess<>0 AND x.UserID={0} AND c.IsApproved = 1 AND a.TopicMovedID IS NULL AND a.IsDeleted = 0 AND c.IsDeleted = 0"
          .FormatWith(userID);

      if (forumIds.Any())
      {
        searchSql += " AND a.ForumID IN ({0})".FormatWith(forumIds.ToDelimitedString(","));
      }

      if (toSearchFromWho.IsSet())
      {
        searchSql +=
          "\r\nAND ({0})".FormatWith(
            this.BuildWhoConditions(toSearchFromWho, searchFromWhoMethod, searchDisplayName).BuildSql(true));
      }

      if (toSearchWhat.IsSet())
      {
        builtStatements.Add(searchSql);

        builtStatements.Add(
          "AND ({0})".FormatWith(
            this.BuildWhatConditions(toSearchWhat, searchWhatMethod, "c.Message", useFullText).BuildSql(true)));

        builtStatements.Add("UNION");

        builtStatements.Add(searchSql);

        builtStatements.Add(
          "AND ({0})".FormatWith(
            this.BuildWhatConditions(toSearchWhat, searchWhatMethod, "a.Topic", useFullText).BuildSql(true)));
      }
      else
      {
        builtStatements.Add(searchSql);
      }

      builtStatements.Add("ORDER BY c.Posted DESC");

      string builtSql = builtStatements.ToDelimitedString("\r\n");

      Debug.WriteLine("Build Sql: [{0}]".FormatWith(builtSql));

      return builtSql;
    }
    protected IEnumerable<SearchCondition> BuildWhoConditions(
      [NotNull] string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, bool searchDisplayName)
    {
      CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");

      toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

      var conditions = new List<SearchCondition>();
      string conditionSql = string.Empty;

      SearchConditionType conditionType = SearchConditionType.AND;

      if (searchFromWhoMethod == SearchWhatFlags.AnyWords)
      {
        conditionType = SearchConditionType.OR;
      }

      var wordList = new List<string> { toSearchFromWho };

      if (searchFromWhoMethod == SearchWhatFlags.AllWords || searchFromWhoMethod == SearchWhatFlags.AnyWords)
      {
        wordList =
          toSearchFromWho.Replace(@"""", string.Empty).Split(' ').Where(x => x.IsSet()).Select(x => x.Trim()).ToList();
      }

      foreach (string word in wordList)
      {
        int userId;

        if (int.TryParse(word, out userId))
        {
          conditionSql = "c.UserID IN ({0})".FormatWith(userId);
        }
        else
        {
          if (searchFromWhoMethod == SearchWhatFlags.ExactMatch)
          {
            conditionSql = "(c.Username IS NULL AND b.{1} = N'{0}') OR (c.Username = N'{0}')".FormatWith(
              word, searchDisplayName ? "DisplayName" : "Name");
          }
          else
          {
            conditionSql = "(c.Username IS NULL AND b.{1} LIKE N'%{0}%') OR (c.Username LIKE N'%{0}%')".FormatWith(
              word, searchDisplayName ? "DisplayName" : "Name");
          }
        }

        conditions.Add(new SearchCondition { Condition = conditionSql, ConditionType = conditionType });
      }

      return conditions;
    }
    protected IEnumerable<SearchCondition> BuildWhatConditions(
      [NotNull] string toSearchWhat, SearchWhatFlags searchWhatMethod, [NotNull] string dbField, bool useFullText)
    {
      CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
      CodeContracts.ArgumentNotNull(dbField, "dbField");

      toSearchWhat = toSearchWhat.Replace("'", "''").Trim();

      var conditions = new List<SearchCondition>();
      string conditionSql = string.Empty;

      SearchConditionType conditionType = SearchConditionType.AND;

      if (searchWhatMethod == SearchWhatFlags.AnyWords)
      {
        conditionType = SearchConditionType.OR;
      }

      var wordList = new List<string> { toSearchWhat };

      if (searchWhatMethod == SearchWhatFlags.AllWords || searchWhatMethod == SearchWhatFlags.AnyWords)
      {
        wordList =
          toSearchWhat.Replace(@"""", string.Empty).Split(' ').Where(x => x.IsSet()).Select(x => x.Trim()).ToList();
      }

      if (useFullText)
      {
        var list = new List<SearchCondition>();

        list.AddRange(
          wordList.Select(
            word => new SearchCondition { Condition = @"""{0}""".FormatWith(word), ConditionType = conditionType }));

        conditions.Add(
          new SearchCondition
            {
              Condition = "CONTAINS ({1}, N' {0} ')".FormatWith(list.BuildSql(false), dbField), 
              ConditionType = conditionType
            });
      }
      else
      {
        conditions.AddRange(
          wordList.Select(
            word =>
            new SearchCondition
              {
                 Condition = "{1} LIKE N'%{0}%'".FormatWith(word, dbField), ConditionType = conditionType 
              }));
      }

      return conditions;
    }
Example #7
0
        /// <summary>
        /// Sets up highlighting of the search keywords.
        /// </summary>
        /// <param name="searchFlags">
        /// The search Flags.
        /// </param>
        private void SetupHighlightWords(SearchWhatFlags searchFlags)
        {
            this.HighlightSearchWords.Clear();

            switch (searchFlags)
            {
                case SearchWhatFlags.ExactMatch:
                    this.HighlightSearchWords.Add(this.SearchWhatCleaned);
                    break;
                case SearchWhatFlags.AllWords:
                case SearchWhatFlags.AnyWords:
                    this.HighlightSearchWords.AddRange(this.SearchWhatCleaned.Split(' ').ToList().Where(x => x.IsSet()));
                    break;
            }
        }
Example #8
0
        /// <summary>
        /// The build search sql.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="toSearchWhat">
        /// The to search what.
        /// </param>
        /// <param name="toSearchFromWho">
        /// The to search from who.
        /// </param>
        /// <param name="searchFromWhoMethod">
        /// The search from who method.
        /// </param>
        /// <param name="searchWhatMethod">
        /// The search what method.
        /// </param>
        /// <param name="userId">
        /// The user Id.
        /// </param>
        /// <param name="searchDisplayName">
        /// The search display name.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <param name="maxResults">
        /// The max results.
        /// </param>
        /// <param name="useFullText">
        /// The use full text.
        /// </param>
        /// <param name="ids">
        /// The ids.
        /// </param>
        /// <param name="forumIdToStartAt">
        /// The forum Id To Start At.
        /// </param>
        /// <returns>
        /// The build search sql.
        /// </returns>
        public string BuildSearchSql(
            int? mid,
            [NotNull] string toSearchWhat,
            [NotNull] string toSearchFromWho,
            SearchWhatFlags searchFromWhoMethod,
            SearchWhatFlags searchWhatMethod,
            int userId,
            bool searchDisplayName,
            int boardId,
            int maxResults,
            bool useFullText,
            string categoriesIds,
            string ids,
            [NotNull] IEnumerable<int> forumIdToStartAt)
        {
            CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
            CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");

            string limitString = string.Empty;
            string orderString = string.Empty;
            IEnumerable<int> forumIds = null;
            if (!string.IsNullOrEmpty(ids))
            {
                forumIds = new[] { ids.ToType<int>() };
            }

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

            string searchSql = "SELECT ";
            if (maxResults > 0)
            {
                limitString += string.Format(" FIRST {0} ", maxResults);
                searchSql += limitString;
            }

            searchSql +=
                @" a.FORUMID AS ""ForumID"", a.TOPICID AS ""TopicID"", a.TOPIC AS ""Topic"", b.USERID AS ""UserID"" , COALESCE(c.USERNAME, b.NAME) AS ""Name"", c.MESSAGEID AS ""MessageID"", c.POSTED AS ""Posted"", '' AS ""Message"", c.FLAGS AS ""Flags""";
            searchSql += " FROM " + SqlDbAccess.GetVzfObjectName("TOPIC", mid) + " a LEFT JOIN "
                         + SqlDbAccess.GetVzfObjectName("MESSAGE", mid) + @" c ON a.TOPICID = c.TOPICID LEFT JOIN "
                         + SqlDbAccess.GetVzfObjectName("USER", mid) + @" b ON c.USERID = b.USERID join "
                         + SqlDbAccess.GetVzfObjectName("VACCESS", mid) + @" x ON x.FORUMID=a.FORUMID ";
            searchSql +=
                string.Format(
                    @"WHERE x.READACCESS<>0 AND x.USERID={0} AND c.ISAPPROVED <> 0 AND a.TOPICMOVEDID IS NULL AND a.ISDELETED = 0 AND c.ISDELETED = 0 ",
                    userId);
            orderString += @" ORDER BY a.FORUMID ";


            
            bool bFirst;

            if (!string.IsNullOrEmpty(toSearchFromWho))
            {
                searchSql += "AND (";

                searchSql = new SearchFromWho().Build(searchSql, searchFromWhoMethod, toSearchFromWho, useFullText, searchDisplayName);

                searchSql += ") ";
            }

            if (!string.IsNullOrEmpty(toSearchWhat))
            {
                searchSql += "AND (";

                searchSql = new SearchWhat().Build(searchSql, searchWhatMethod, toSearchWhat, useFullText); 

                searchSql += ") ";
            }

            if (categoriesIds.IsSet())
            {
                searchSql +=string.Format(" AND f.categoryid IN ({0})", categoriesIds);
            }

            // Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
            if (forumIdToStartAt.Any())
            {
                searchSql += string.Format(@"AND a.FORUMID IN ({0})", forumIds);
            }

            if (orderString != string.Empty)
            {
                orderString += ", ";
            }

            if (!orderString.Contains("ORDER BY"))
            {
                searchSql += " ORDER BY ";
            }

            searchSql += orderString + @"c.POSTED DESC ";
            return searchSql;
        }
Example #9
0
        public string Build(string searchSql, SearchWhatFlags searchWhatMethod, string toSearchWhat, bool useFullText)
        {
            bool bFirst = true;
            // generate message and topic search sql...
            switch (searchWhatMethod)
            {
                case SearchWhatFlags.AllWords:
                    string[] words = toSearchWhat.Split(' ');
                    if (useFullText)
                    {
                        string ftInner = string.Empty;

                        // make the inner FULLTEXT search
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                ftInner += " AND ";
                            }
                            else
                            {
                                bFirst = false;
                            }

                            ftInner += string.Format(@"""{0}""", word);
                        }

                        // make final string...
                        searchSql +=
                            string.Format(
                                @"( CONTAINS (c.MESSAGE, ' {0} ') OR CONTAINS (a.TOPIC, ' {0} ' ) )",
                                ftInner);
                    }
                    else
                    {
                        foreach (string word in words)
                        {
                            if (!bFirst) searchSql += " AND ";
                            else bFirst = false;
                            searchSql += string.Format(@"(c.MESSAGE like '%{0}%' OR a.TOPIC LIKE '%{0}%' )", word);
                        }
                    }

                    break;
                case SearchWhatFlags.AnyWords:
                    words = toSearchWhat.Split(' ');

                    if (useFullText)
                    {
                        string ftInner = string.Empty;

                        // make the inner FULLTEXT search
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                ftInner += " OR ";
                            }
                            else
                            {
                                bFirst = false;
                            }

                            ftInner += string.Format(@"""{0}""", word);
                        }

                        // make final string...
                        searchSql +=
                            string.Format(
                                @"( CONTAINS (c.MESSAGE, ' {0} ' ) OR CONTAINS (a.TOPIC, ' {0} ' ) )",
                                ftInner);
                    }
                    else
                    {
                        foreach (string word in words)
                        {
                            if (!bFirst) searchSql += " OR ";
                            else bFirst = false;
                            searchSql += string.Format(@"c.MESSAGE LIKE '%{0}%'  OR a.TOPIC LIKE '%{0}%' ", word);
                        }
                    }

                    break;
                case SearchWhatFlags.ExactMatch:
                    if (useFullText)
                    {
                        // searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' \"{0}\" ' ) OR CONTAINS (a.Topic, ' \"{0}\" '  )", toSearchWhat);
                    }
                    else
                    {
                        searchSql += string.Format(
                            @"c.MESSAGE LIKE '%{0}%'  OR a.TOPIC LIKE '%{0}%'  ",
                            toSearchWhat);
                    }

                    break;
            }

            return searchSql;
        }
Example #10
0
        /// <summary>
        /// Returns Search results
        /// </summary>
        /// <param name="toSearchWhat">
        /// The to Search What.
        /// </param>
        /// <param name="toSearchFromWho">
        /// The to Search From Who.
        /// </param>
        /// <param name="searchFromWhoMethod">
        /// The search From Who Method.
        /// </param>
        /// <param name="searchWhatMethod">
        /// The search What Method.
        /// </param>
        /// <param name="forumIDToStartAt">
        /// The forum ID To Start At.
        /// </param>
        /// <param name="userID">
        /// The user ID.
        /// </param>
        /// <param name="boardId">
        /// The board Id.
        /// </param>
        /// <param name="maxResults">
        /// The max Results.
        /// </param>
        /// <param name="useFullText">
        /// The use Full Text.
        /// </param>
        /// <param name="searchDisplayName">
        /// The search Display Name.
        /// </param>
        /// <returns>
        /// Results
        /// </returns>
        public static DataTable GetSearchResult(
            [NotNull] string toSearchWhat,
            [NotNull] string toSearchFromWho,
            SearchWhatFlags searchFromWhoMethod,
            SearchWhatFlags searchWhatMethod,
          int forumIDToStartAt,
          int userID,
          int boardId,
          int maxResults,
          bool useFullText,
          bool searchDisplayName)
        {
            if (toSearchWhat == "*")
            {
                toSearchWhat = string.Empty;
            }

            IEnumerable<int> forumIds = new List<int>();

            if (forumIDToStartAt != 0)
            {
                forumIds = ForumListAll(boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
            }

            string searchSql = new SearchBuilder().BuildSearchSql(
              toSearchWhat,
              toSearchFromWho,
              searchFromWhoMethod,
              searchWhatMethod,
              userID,
              searchDisplayName,
              boardId,
              maxResults,
              useFullText,
              forumIds);

            using (var cmd = MsSqlDbAccess.GetCommand(searchSql, true))
            {
                return MsSqlDbAccess.Current.GetData(cmd);
            }
        }
Example #11
0
        /// <summary>
        /// The get search result.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="toSearchWhat">
        /// The to search what.
        /// </param>
        /// <param name="toSearchFromWho">
        /// The to search from who.
        /// </param>
        /// <param name="searchFromWhoMethod">
        /// The search from who method.
        /// </param>
        /// <param name="searchWhatMethod">
        /// The search what method.
        /// </param>
        /// <param name="categoryId">
        /// The category id.
        /// </param>
        /// <param name="forumIdToStartAt">
        /// The forum id to start at.
        /// </param>
        /// <param name="userId">
        /// The user id.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <param name="maxResults">
        /// The max results.
        /// </param>
        /// <param name="useFullText">
        /// The use full text.
        /// </param>
        /// <param name="searchDisplayName">
        /// The search display name.
        /// </param>
        /// <param name="includeChildren">
        /// The include children.
        /// </param>
        /// <param name="culture">
        /// The culture.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns>
        /// <exception cref="ArgumentOutOfRangeException">
        /// </exception>
        public static DataTable GetSearchResult(
            int? mid,
            string toSearchWhat,
            string toSearchFromWho,
            SearchWhatFlags searchFromWhoMethod,
            SearchWhatFlags searchWhatMethod,
            List<int> categoryId,
            List<int> forumIdToStartAt,
            int userId,
            int boardId,
            int maxResults,
            bool useFullText,
            bool searchDisplayName,
            bool includeChildren,
            string culture)
        {
            string dataEngine;
            string connectionString;
            string namePattern = string.Empty;
            SqlDbAccess.GetConnectionData(mid, namePattern, out dataEngine, out connectionString);

            // New access
            /*  if (toSearchWhat == "*")
              {
                  toSearchWhat = string.Empty;
              }

              IEnumerable<int> forumIds = new List<int>();

              if (forumIdToStartAt != 0)
              {
                  forumIds = ForumListAll(boardId, userID, forumIdToStartAt).Select(f => f.ForumID ?? 0).Distinct();
              }

              string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);

              using (SqlCommand cmd = PostgreDbAccess.GetCommand(searchSql, true))
              {
                  return PostgreDbAccess.Current.GetData(cmd);
              } */

            if (toSearchWhat == "*")
            {
                toSearchWhat = string.Empty;
            }

            string forumIDs = string.Empty;
            string categoriesIds = string.Empty;
            DataTable dt = null;
            if ((categoryId.Any() || forumIdToStartAt.Any()) && !Config.LargeForumTree)
            {
                dt = CommonDb.forum_listall_sorted(mid, boardId, userId, forumIdToStartAt.ToArray<int>());
            }

            if (categoryId.Any())
            {
                if (Config.LargeForumTree)
                {
                    DataTable dt1 = CommonDb.forum_cataccess_actuser(mid, boardId, userId);
                    foreach (DataRow c in dt1.Rows)
                    {
                        foreach (int c1 in categoryId)
                        {
                            if (Convert.ToInt32(c["CategoryID"]) == c1)
                            {
                                categoriesIds = categoriesIds + "," + c1;
                            }
                        }
                    }

                    categoriesIds = categoriesIds.Trim(',');
                }
                else
                {
                    if (dt != null)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            // if not a category
                            if (Convert.ToInt32(dr["ForumID"]) == -1)
                            {
                                categoriesIds = categoriesIds + Convert.ToInt32(dr["ForumID"]) + ",";
                            }
                        }

                        categoriesIds = categoriesIds.Substring(0, forumIDs.Length - 1);

                        categoriesIds = categoriesIds.Trim(',');
                    }
                }
            }

            if (forumIdToStartAt.Any())
            {
                if (!Config.LargeForumTree)
                {
                    if (dt != null)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            // if not a category
                            if (Convert.ToInt32(dr["ForumID"]) != -1)
                            {
                                forumIDs = forumIDs + Convert.ToInt32(dr["ForumID"]) + ",";
                            }
                        }

                        forumIDs = forumIDs.Substring(0, forumIDs.Length - 1);
                    }
                }
                else
                {
                    foreach (int frms in forumIdToStartAt)
                    {
                        var d1 = CommonDb.forum_ns_getch_actuser(
                            mid,
                            boardId,
                            0,
                            frms,
                            userId,
                            false,
                            false,
                            "-");

                        foreach (DataRow r in d1.Rows)
                        {
                            forumIDs += "," + r["ForumID"];
                        }

                        // Parent forum only.
                        if (!includeChildren)
                        {
                            break;
                        }
                    }

                    forumIDs = forumIDs.Trim(',');
                }
            }

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
            string sql;
            switch (dataEngine)
            {
                case SqlDbAccess.MsSql:
                    sql = new VZF.Data.MsSql.Search.SearchBuilder().BuildSearchSql(
                        mid,
                        toSearchWhat,
                        toSearchFromWho,
                        searchFromWhoMethod,
                        searchWhatMethod,
                        userId,
                        searchDisplayName,
                        boardId,
                        maxResults,
                        useFullText,
                        forumIDs,
                        forumIdToStartAt);
                    break;
                case SqlDbAccess.Npgsql:
                    sql = new VZF.Data.Postgre.Search.SearchBuilder().BuildSearchSql(
                        mid,
                        toSearchWhat,
                        toSearchFromWho,
                        searchFromWhoMethod,
                        searchWhatMethod,
                        userId,
                        searchDisplayName,
                        boardId,
                        maxResults,
                        useFullText,
                        categoriesIds,
                        forumIDs,
                        forumIdToStartAt, 
                        culture);
                    break;
                case SqlDbAccess.MySql:
                    sql = new VZF.Data.MySql.Search.SearchBuilder().BuildSearchSql(
                        mid,
                        toSearchWhat,
                        toSearchFromWho,
                        searchFromWhoMethod,
                        searchWhatMethod,
                        userId,
                        searchDisplayName,
                        boardId,
                        maxResults,
                        useFullText,
                        categoriesIds,
                        forumIDs,
                        forumIdToStartAt);
                    break;
                case SqlDbAccess.Firebird:
                    sql = new VZF.Data.Firebird.Search.SearchBuilder().BuildSearchSql(
                        mid,
                        toSearchWhat,
                        toSearchFromWho,
                        searchFromWhoMethod,
                        searchWhatMethod,
                        userId,
                        searchDisplayName,
                        boardId,
                        maxResults,
                        useFullText,
                        categoriesIds,
                        forumIDs,
                        forumIdToStartAt);
                    break;
                    // case SqlDbAccess.Oracle:  return VZF.Data.Oracle.Db.GetSearchResult(connectionString, toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, categoryId, forumIdToStartAt, userId, boardId, maxResults, useFullText, searchDisplayName, includeChildren);
                    // case SqlDbAccess.Db2:  return VZF.Data.Db2.Db.GetSearchResult(connectionString, toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, categoryId, forumIdToStartAt, userId, boardId, maxResults, useFullText, searchDisplayName, includeChildren);
                    // case SqlDbAccess.Other:  return VZF.Data.Other.Db.GetSearchResult(connectionString, toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, categoryId, forumIdToStartAt, userId, boardId, maxResults, useFullText, searchDisplayName, includeChildren);
                default:
                    throw new ArgumentOutOfRangeException(dataEngine);
            }
            DataTable dtResult;
            using (var sc = new VzfSqlCommand(mid))
            {
                if (dataEngine == SqlDbAccess.MySql)
                {
                    sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@i_Limit", maxResults.ToString(CultureInfo.InvariantCulture)));
                }

                sc.CommandText.AppendQuery(sql);
                dtResult = sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.Text, true);
            }

            if (dataEngine == SqlDbAccess.MySql)
            {
                string oldUid = null;
                string oldFid = null;
                foreach (DataRow dr in dtResult.Rows)
                {
                    if (oldUid == dr["UserID"].ToString() || oldFid == dr["ForumID"].ToString())
                    {
                        continue;
                    }

                    using (var sc = new VzfSqlCommand(mid))
                    {
                        sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@i_UserID", dr["UserID"]));
                        sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@i_ForumID", dr["ForumID"]));
                        sc.CommandText.AppendQuery(
                            string.Format(
                                "SELECT {0}(@i_UserID,@i_ForumID);",
                                SqlDbAccess.GetVzfObjectName("vaccess_s_readaccess_combo", mid)));

                        if (Convert.ToInt32(sc.ExecuteScalar(CommandType.Text)) == 0)
                        {
                            dr.Delete();
                        }

                    }

                    oldUid = dr["UserID"].ToString();
                    oldFid = dr["ForumID"].ToString();
                }

                dtResult.AcceptChanges();
            }

            return dtResult;
        }
        /// <summary>
        /// Returns Search results
        /// </summary>
        /// <param name="ToSearch"></param>
        /// <param name="sf">Field to search</param>
        /// <param name="sw">Search what</param>
        /// <param name="fid"></param>
        /// <param name="UserID">ID of user</param>
        /// <returns>Results</returns>
        public static DataTable GetSearchResult( string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userId, int boardId, int maxResults, bool useFullText, bool searchDisplayName)
        {
            // New access
              /*  if (toSearchWhat == "*")
            {
                toSearchWhat = string.Empty;
            }

            IEnumerable<int> forumIds = new List<int>();

            if (forumIDToStartAt != 0)
            {
                forumIds = ForumListAll(boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
            }

            string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);

            using (SqlCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
            {
                return MsSqlDbAccess.Current.GetData(cmd);
            } */

            if (toSearchWhat == "*")
                toSearchWhat = "";
            string forumIDs = "";
            string limitString = "";
            string orderString = "";

            if (forumIDToStartAt != 0)
            {
                DataTable dt = forum_listall_sorted(boardId, userId, null, false, forumIDToStartAt);
                foreach (DataRow dr in dt.Rows)
                    forumIDs = forumIDs + Convert.ToInt32(dr["ForumID"]).ToString() + ",";
                forumIDs = forumIDs.Substring(0, forumIDs.Length - 1);
            }

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

            string searchSql = (maxResults == 0) ? "SELECT" : ("SELECT DISTINCT ");
            if (maxResults > 0)
            { limitString += String.Format(" LIMIT {0} ", maxResults.ToString());  }

            searchSql += " a.forumid, a.topicid, a.topic, b.userid, COALESCE(c.username, b.name) as Name, c.messageid as \"MessageID\", c.posted, c.message as \"Message\", c.flags ";
            searchSql += "FROM " + (Config.SchemaName.IsSet() ? Config.SchemaName : "public") +
                "." + Config.DatabaseObjectQualifier + "topic a LEFT JOIN " +
                (Config.SchemaName.IsSet() ? Config.SchemaName : "public") + "." + Config.DatabaseObjectQualifier +
                "message c ON a.topicid = c.topicid LEFT JOIN " + (Config.SchemaName.IsSet() ? Config.SchemaName : "public") + "." + Config.DatabaseObjectQualifier +
                "user b ON c.userid = b.userid join " + (Config.SchemaName.IsSet() ? Config.SchemaName : "public") + "." + Config.DatabaseObjectQualifier
                + "vaccess x ON x.forumid=a.forumid ";
            searchSql += String.Format(@"WHERE x.readaccess<>0 AND x.userid={0} AND c.isapproved IS TRUE AND a.topicmovedid IS NULL AND a.isdeleted IS FALSE AND c.isdeleted IS FALSE ", userId);
            orderString += "ORDER BY a.forumid ";

            string[] words;
            bool bFirst;

            if (!String.IsNullOrEmpty(toSearchFromWho))
            {
                searchSql += "AND (";
                bFirst = true;

                // generate user search sql...
                switch (searchFromWhoMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst) searchSql += " AND "; else bFirst = false;
                           // searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);

                            if (!int.TryParse(word, out userId))
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                  string.Format(" ((c.userdisplayname IS NULL AND b.displayname  ~* '.*{0}.*') OR (c.userdisplayname ~* '.*{0}.*'))", word);
                                }
                                else
                                {
                                    searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
                                }

                            }
                            else
                            {
                                searchSql +=
                              string.Format(" (c.userid IN ({0}))", userId);
                            }
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst) searchSql += " OR "; else bFirst = false;
                            searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        // searchSql += string.Format(" ((c.username IS NULL AND b.name = '{0}' ) OR (c.username = '{0}' ))", toSearchFromWho);

                        if (!int.TryParse(toSearchFromWho, out userId))
                        {
                            if (searchDisplayName)
                            {
                                searchSql += string.Format(
                                                          " ((c.userdisplayname IS NULL AND b.displayname = '{0}') OR (c.userdisplayname = '{0}'))", toSearchFromWho);
                            }
                            else
                            {
                                searchSql += string.Format(
                           " ((c.username IS NULL AND b.name = '{0}') OR (c.username = '{0}'))", toSearchFromWho);
                            }

                        }
                        else
                        {

                            searchSql +=
                              string.Format(" (c.userid IN ({0})) ", userId);

                        }
                        break;
                }
                searchSql += ") ";
            }

            if (!String.IsNullOrEmpty(toSearchWhat))
            {
                searchSql += "AND (";
                bFirst = true;

                // generate message and topic search sql...
                switch (searchWhatMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchWhat.Split(' ');
                        if (useFullText)
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst) ftInner += " AND "; else bFirst = false;
                                ftInner += String.Format(@"""{0}""", word);
                            }
                            // make final string...
                            searchSql += string.Format("( CONTAINS (c.message, ' {0} ') OR CONTAINS (a.topic, ' {0} ' ) )", ftInner);
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " AND "; else bFirst = false;
                                searchSql += String.Format("(c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' )", word);
                            }
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchWhat.Split(' ');

                        if (useFullText)
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst) ftInner += " OR "; else bFirst = false;
                                ftInner += String.Format(@"""{0}""", word);

                                if (int.TryParse(word, out userId))
                                {
                                    searchSql +=
                                      string.Format(" (c.userid IN ({0}))", userId);
                                }
                                else
                                {
                                    searchSql +=
                                      string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
                                }
                            }
                            // make final string...
                            searchSql += string.Format("( CONTAINS (c.message, ' {0} ' ) OR CONTAINS (a.topic, ' {0} ' ) )", ftInner);
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " OR "; else bFirst = false;
                                searchSql += String.Format("c.message ~* '.*{0}.*'  OR a.topic ~* '.*{0}.*' ", word);
                            }
                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (useFullText)
                        {
                            searchSql += string.Format("( CONTAINS (c.message, ' \"{0}\" ' ) OR CONTAINS (a.topic, ' \"{0}\" '  )", toSearchWhat);
                        }
                        else
                        {
                            searchSql += string.Format("c.message ~* '.*{0}.*'  OR a.topic ~* '.*{0}.*'  ", toSearchWhat);
                        }
                        break;
                }
                searchSql += ") ";
            }

            // Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
            if (forumIDToStartAt > 0)
            {
                searchSql += string.Format("AND a.forumid IN ({0})", forumIDs);
            }

            if (orderString != "") { orderString += ", "; }
            if (!orderString.Contains("ORDER BY"))
            {
                searchSql += " ORDER BY ";
            }

            searchSql += orderString + "c.posted DESC ";

            if (!orderString.Contains("LIMIT"))
            {
                searchSql += limitString;
            }

            using (NpgsqlCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
            {
                return MsSqlDbAccess.Current.GetData(cmd);
            }
        }
        /// <summary>
        /// Returns Search results
        /// </summary>
        /// <param name="ToSearch"></param>
        /// <param name="sf">Field to search</param>
        /// <param name="sw">Search what</param>
        /// <param name="fid"></param>
        /// <param name="UserID">ID of user</param>
        /// <returns>Results</returns>
        public static DataTable GetSearchResult(string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userID, int boardId, int maxResults, bool useFullText, 
		 bool searchDisplayName)
        {
            bool bFirst = true;
            string forumIds = string.Empty;

            DataTable dt_result = null;
            // if ( ToSearch.Length == 0 )
            //	return new DataTable();

            if ( toSearchWhat == "*" )
                toSearchWhat = "";
            string forumIDs = "";
            string limitString = "";
            string orderString = "";
            String paramsString = "";

            //Search not in all forums
            if ( forumIDToStartAt != 0 )
            {
                DataTable dt = LegacyDb.forum_listall_sorted( boardId, userID, null, false, forumIDToStartAt);

                foreach ( DataRow dr in dt.Rows )
                forumIDs = forumIDs + Convert.ToInt32( dr ["ForumID"] ).ToString() + ",";

                forumIDs = forumIDs.Substring( 0, forumIDs.Length - 1 );
            }

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace( "'", "''" ).Trim();
            toSearchFromWho = toSearchFromWho.Replace( "'", "''" ).Trim();

            string searchSql = ( maxResults == 0 ) ? "SELECT" : ( "SELECT DISTINCTROW ");

            searchSql += " a.ForumID, a.TopicID, a.Topic, b.UserID, IFNULL(c.Username, b.Name) as Name, c.MessageID, c.Posted, '' AS Message, c.Flags";
            searchSql += " from "
                + MsSqlDbAccess.GetObjectName("Topic")
                 + " a left join " +
                MsSqlDbAccess.GetObjectName("Message") +
                " c on a.TopicID = c.TopicID left join "
                + MsSqlDbAccess.GetObjectName("User") +
                " b on c.UserID = b.UserID where ";
            searchSql += " IFNULL(CAST(SIGN(c.Flags & 16) AS SIGNED),0) = 1 AND a.TopicMovedID IS NULL AND IFNULL(CAST(SIGN(a.Flags & 8) AS SIGNED),0) = 0 AND IFNULL(CAST(SIGN(c.Flags & 8) AS SIGNED),0) = 0 ";
            orderString += " ORDER BY a.ForumID ";
            limitString += " LIMIT @i_Limit ";
            string [] words;

            if ( !String.IsNullOrEmpty( toSearchFromWho ) )
            {
                searchSql += "AND (";
                bFirst = true;
                int userId;
                // generate user search sql...
                switch ( searchFromWhoMethod )
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchFromWho.Split( ' ' );
                        foreach ( string word in words )
                        {
                            if ( !bFirst ) searchSql += " AND "; else bFirst = false;

                            if (int.TryParse(word, out userId))
                            {
                                searchSql +=
                                    searchSql += string.Format(" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                string.Format(" ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                                }
                                else
                                {
                                    searchSql +=
                                string.Format(" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                                }
                            }
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split( ' ' );
                        foreach ( string word in words )
                        {
                            if ( !bFirst ) searchSql += " OR "; else bFirst = false;

                            if (int.TryParse(word, out userId))
                            {
                                searchSql +=
                                  string.Format(" (c.UserID IN ({0}))", userId);
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                string.Format(" ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                                }
                                else
                                {
                                    searchSql +=
                                 string.Format(" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                                }

                            }
                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (int.TryParse(toSearchFromWho, out userId))
                        {
                            searchSql +=
                              string.Format(" (c.UserID IN ({0}))", userId);
                        }
                        else
                        {
                            if (searchDisplayName)
                            {
                                searchSql += string.Format(" ((c.Username IS NULL AND b.DisplayName = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))", toSearchFromWho, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                            }
                            else
                            {
                                searchSql += string.Format(" ((c.Username IS NULL AND b.Name = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))", toSearchFromWho, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                            }

                        }

                        break;
                }
                searchSql += ") ";
            }

            if ( !String.IsNullOrEmpty( toSearchWhat ) )
            {

                searchSql += "AND (";
                bFirst = true;

                // generate message and topic search sql...
                switch ( searchWhatMethod )
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchWhat.Split( ' ' );
                        if ( useFullText )
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach ( string word in words )
                            {
                                if ( !bFirst ) ftInner += " AND "; else bFirst = false;
                                ftInner += String.Format( @"""{0}""", word );
                            }
                            // make final string...
                            searchSql += string.Format("( CONTAINS (c.Message, CONVERT(' {0} ' USING {1}) OR CONTAINS (a.Topic, CONVERT(' {0} ' USING {1})) )", ftInner, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                        }
                        else
                        {
                            foreach ( string word in words )
                            {
                                if ( !bFirst ) searchSql += " AND "; else bFirst = false;
                                searchSql += String.Format("(c.Message like CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                            }
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchWhat.Split( ' ' );

                        if ( useFullText )
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach ( string word in words )
                            {
                                if ( !bFirst ) ftInner += " OR "; else bFirst = false;
                                ftInner += String.Format( @"""{0}""", word );
                            }
                            // make final string...
                            searchSql += string.Format("( CONTAINS (c.Message, CONVERT(' {0} ' USING {1})) OR CONTAINS (a.Topic, CONVERT(' {0} ' USING {1})) )", ftInner, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                        }
                        else
                        {
                            foreach ( string word in words )
                            {
                                if ( !bFirst ) searchSql += " OR "; else bFirst = false;
                                searchSql += String.Format("c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1})", word, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                            }
                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        if ( useFullText )
                        {
                            searchSql += string.Format("( CONTAINS (c.Message, CONVERT(' \"{0}\" ' USING {1})) OR CONTAINS (a.Topic, CONVERT(' \"{0}\" ' USING {1}) )", toSearchWhat, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                        }
                        else
                        {
                            searchSql += string.Format("c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}) ", toSearchWhat, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
                        }
                        break;
                }
                searchSql += ") ";
            }

            // Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
            if ( forumIDToStartAt > 0 )
            {
                searchSql += string.Format( "AND a.ForumID IN ( SELECT {0})", forumIDs );
            }
            if (orderString!="") { orderString += ", "; }
            if (!orderString.Contains("ORDER BY"))
            {
                searchSql += " ORDER BY ";
            }

            searchSql += orderString + "c.Posted DESC ";

            if (!orderString.Contains("LIMIT"))
            {
                searchSql += limitString;
            }

            using ( MySqlCommand cmd = MsSqlDbAccess.GetCommand( searchSql, true ) )
            {
               // string toSearchWhat, string toSearchFromWho int forumIDToStartAt, int userID, int boardId, int maxResults
              //  cmd.Parameters.Add( "i_UserID", MySqlDbType.Int32 ).Value = userID;
              //  cmd.Parameters.Add( "i_Word", MySqlDbType.VarChar ).Value = word;
              //  cmd.Parameters.Add("i_toSearchFromWho", MySqlDbType.VarChar).Value = toSearchFromWho;
               // cmd.Parameters.Add("i_forumIDs", MySqlDbType.VarChar).Value = forumIDs;
              //  cmd.Parameters.Add("i_ftInner", MySqlDbType.VarChar).Value = ftInner;

             /*   words = toSearchFromWho.Split(' ');
                int ii1 = 0;
                foreach (string word in words)
                {
                    cmd.Parameters.Add( "i_word"+ii1.ToString(), MySqlDbType.VarChar ).Value = userID;
                    ii1++;
                }*/

             cmd.Parameters.Add("@i_Limit", MySqlDbType.Int32).Value = maxResults.ToString(); ;

                dt_result = MsSqlDbAccess.Current.GetData(cmd);

            }
            string old_uid = null;
            string old_fid = null;
            foreach (DataRow dr in dt_result.Rows)
            {
                if (old_uid != dr["UserID"].ToString() && old_fid != dr["ForumID"].ToString())
                {
                    using (MySqlCommand cmd1 = MsSqlDbAccess.GetCommand(String.Format("SELECT {0}(@i_UserID,@i_ForumID);", MsSqlDbAccess.GetObjectName("vaccess_s_readaccess_combo")), true))
                    {
                        cmd1.Parameters.Add("@i_UserID", MySqlDbType.Int32).Value = dr["UserID"];
                        cmd1.Parameters.Add("@i_ForumID", MySqlDbType.Int32).Value = dr["ForumID"]; ;

                        if ( Convert.ToInt32(MsSqlDbAccess.Current.ExecuteScalar(cmd1)) == 0 )
                        {
                            dr.Delete();
                        }

                    }
                    old_uid = dr["UserID"].ToString();
                    old_fid = dr["ForumID"].ToString();
                }

            }

            dt_result.AcceptChanges();
            return dt_result;
        }
Example #14
0
        public string Build(string searchSql, SearchWhatFlags searchFromWhoMethod, string toSearchFromWho, bool useFullText, bool searchDisplayName)
        {
            bool bFirst = true;
            int userId;
         // generate user search sql...
                switch (searchFromWhoMethod)
                {
                    case SearchWhatFlags.AllWords:
                      string[] words  = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql += " AND ";
                            }
                            else
                            {
                                bFirst = false;
                            }

                            searchSql +=
                                string.Format(
                                    @" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))",
                                    word);
                            if (int.TryParse(word, out userId))
                            {
                                searchSql += string.Format(" (c.UserID IN ({0}))", userId);
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.Username IS NULL AND b.DisplayName LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))",
                                            word);
                                }
                                else
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.Username IS NULL AND b.Name LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))",
                                            word);
                                }
                            }
                        }

                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql += " OR ";
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.USERNAME IS NULL AND b.DISPLAYNAME = '{0}') OR (c.Username = '{0}')",
                                            toSearchFromWho);
                                }
                                else
                                {
                                    searchSql +=
                                        string.Format(
                                            @" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))",
                                            word);
                                }
                            }
                        }

                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (int.TryParse(toSearchFromWho, out userId))
                        {
                            searchSql += string.Format(" (c.UserID IN ({0}))", userId);
                        }
                        else
                        {
                            searchSql +=
                                string.Format(
                                    @" ((c.USERNAME IS NULL AND b.NAME = '{0}' ) OR (c.USERNAME = '{0}' ))",
                                    toSearchFromWho);
                        }

                        break;
                }
                return searchSql;
        }
Example #15
0
        /// <summary>
        /// The build search sql.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="toSearchWhat">
        /// The to search what.
        /// </param>
        /// <param name="toSearchFromWho">
        /// The to search from who.
        /// </param>
        /// <param name="searchFromWhoMethod">
        /// The search from who method.
        /// </param>
        /// <param name="searchWhatMethod">
        /// The search what method.
        /// </param>
        /// <param name="pageUserId">
        /// The page User Id.
        /// </param>
        /// <param name="searchDisplayName">
        /// The search display name.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <param name="maxResults">
        /// The max results.
        /// </param>
        /// <param name="useFullText">
        /// The use full text.
        /// </param>
        /// <param name="ids">
        /// The ids.
        /// </param>
        /// <param name="forumIdToStartAt">
        /// The forum Id To Start At.
        /// </param>
        /// <returns>
        /// The build search sql.
        /// </returns>
        public string BuildSearchSql(
            int? mid,
            [NotNull] string toSearchWhat,
            [NotNull] string toSearchFromWho,
            SearchWhatFlags searchFromWhoMethod,
            SearchWhatFlags searchWhatMethod,
            int pageUserId,
            bool searchDisplayName,
            int boardId,
            int maxResults,
            bool useFullText,
            string categoriesIds,
            string ids,
            [NotNull] IEnumerable<int> forumIdToStartAt)
        {
            CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
            CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");

            string limitString = string.Empty;
            string orderString = string.Empty;

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

            bool bFirst = true;

            // if ( ToSearch.Length == 0 )
            //	return new DataTable();
            if (toSearchWhat == "*")
            {
                toSearchWhat = string.Empty;
            }
            string forumIDs = ids;

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

            string searchSql = (maxResults == 0) ? "SELECT" : "SELECT DISTINCTROW ";

            searchSql +=
                " a.ForumID, a.TopicID, a.Topic, b.UserID, IFNULL(c.Username, b.Name) as Name, c.MessageID, c.Posted, '' AS Message, c.Flags";
            searchSql += "{FTS} from " + SqlDbAccess.GetVzfObjectName("Topic", mid) + " a left join "
                         + SqlDbAccess.GetVzfObjectName("Message", mid) + " c on a.TopicID = c.TopicID left join "
                         + SqlDbAccess.GetVzfObjectName("User", mid) + " b on c.UserID = b.UserID join "
                         + SqlDbAccess.GetVzfObjectName("vaccess", mid) + " x ON x.ForumID=a.ForumID ";
            searchSql += string.Format(@"WHERE x.ReadAccess<>0 AND x.UserID={0} ", pageUserId);

            searchSql +=
                " and IFNULL(CAST(SIGN(c.Flags & 16) AS SIGNED),0) = 1 AND a.TopicMovedID IS NULL AND IFNULL(CAST(SIGN(a.Flags & 8) AS SIGNED),0) = 0 AND IFNULL(CAST(SIGN(c.Flags & 8) AS SIGNED),0) = 0 ";
            orderString += " ORDER BY a.ForumID ";
            limitString += " LIMIT @i_Limit ";
            string[] words;

            if (!string.IsNullOrEmpty(toSearchFromWho))
            {
                searchSql += "AND (";

                // generate user search sql...
                int userId;
                switch (searchFromWhoMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql += " AND ";
                            }
                            else
                            {
                                bFirst = false;
                            }

                            if (int.TryParse(word, out userId))
                            {
                                searchSql +=
                                    string.Format(
                                        " ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
                                        word,
                                        !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                            ? Config.DatabaseEncoding
                                            : "utf8");
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
                                            word,
                                            !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                                ? Config.DatabaseEncoding
                                                : "utf8");
                                }
                                else
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
                                            word,
                                            !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                                ? Config.DatabaseEncoding
                                                : "utf8");
                                }
                            }
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql += " OR ";
                            }
                            else
                            {
                                bFirst = false;
                            }

                            if (int.TryParse(word, out userId))
                            {
                                searchSql += string.Format(" (c.UserID IN ({0}))", userId);
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
                                            word,
                                            !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                                ? Config.DatabaseEncoding
                                                : "utf8");
                                }
                                else
                                {
                                    searchSql +=
                                        string.Format(
                                            " ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
                                            word,
                                            !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                                ? Config.DatabaseEncoding
                                                : "utf8");
                                }
                            }
                        }

                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (int.TryParse(toSearchFromWho, out userId))
                        {
                            searchSql += string.Format(" (c.UserID IN ({0}))", userId);
                        }
                        else
                        {
                            if (searchDisplayName)
                            {
                                searchSql +=
                                    string.Format(
                                        " ((c.Username IS NULL AND b.DisplayName = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))",
                                        toSearchFromWho,
                                        !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                            ? Config.DatabaseEncoding
                                            : "utf8");
                            }
                            else
                            {
                                searchSql +=
                                    string.Format(
                                        " ((c.Username IS NULL AND b.Name = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))",
                                        toSearchFromWho,
                                        !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                            ? Config.DatabaseEncoding
                                            : "utf8");
                            }
                        }

                        break;
                }

                searchSql += ") ";
            }


            if (!string.IsNullOrEmpty(toSearchWhat))
            {
                searchSql += "AND (";
                bFirst = true;

                // generate message and topic search sql...
                switch (searchWhatMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchWhat.Split(' ');
                        if (useFullText)
                        {
                            string ftInner = string.Empty;

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst)
                                {
                                    ftInner += " ";
                                }
                                else
                                {
                                    bFirst = false;
                                }

                                ftInner += string.Format(@"{0}", word);
                            }

                            // make final string...
                            searchSql = searchSql.Replace(
                                "{FTS}",
                                string.Format(
                                    ", MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE), MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) ",
                                    ftInner,
                                    !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8"));
                            searchSql += string.Format(
                                "(MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0 OR MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0) ",
                                ftInner,
                                !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " AND ";
                                else bFirst = false;
                                searchSql +=
                                    string.Format(
                                        "(c.Message like CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}))",
                                        word,
                                        !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                            ? Config.DatabaseEncoding
                                            : "utf8");
                            }
                        }

                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchWhat.Split(' ');

                        if (useFullText)
                        {
                            string ftInner = string.Empty;

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst)
                                {
                                    ftInner += " ";
                                }
                                else
                                {
                                    bFirst = false;
                                }

                                ftInner += string.Format(@"{0}", word);
                            }

                            // make final string...
                            searchSql = searchSql.Replace(
                                "{FTS}",
                                string.Format(            
                                    ", MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE), MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) ",
                                    ftInner,
                                    !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8"));
                            searchSql += string.Format(
                                "(MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0 OR MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0) ",
                                ftInner,
                                !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " OR ";
                                else bFirst = false;
                                searchSql +=
                                    string.Format(
                                        "c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1})",
                                        word,
                                        !string.IsNullOrEmpty(Config.DatabaseEncoding)
                                            ? Config.DatabaseEncoding
                                            : "utf8");
                            }
                        }

                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (useFullText)
                        {
                            // make final string...
                            searchSql = searchSql.Replace(
                                "{FTS}",
                                string.Format(
                                    ", MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE), MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) ",
                                    toSearchWhat,
                                    !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8"));
                            searchSql += string.Format(
                                "(MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0 OR MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0) ",
                                toSearchWhat,
                                !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                        }
                        else
                        {
                            searchSql +=
                                string.Format(
                                    "c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}) ",
                                    toSearchWhat,
                                    !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
                        }

                        break;
                }

                searchSql += ") ";
            }

            if (categoriesIds.IsSet())
            {
                searchSql += string.Format(" AND f.categoryid IN ({0})", categoriesIds);
            }

            // Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
            if (forumIdToStartAt.Any())
            {
                searchSql += string.Format("AND a.ForumID IN (SELECT {0})", forumIDs);
            }

            if (orderString != string.Empty)
            {
                orderString += ", ";
            }

            if (!orderString.Contains("ORDER BY"))
            {
                searchSql += " ORDER BY ";
            }

            searchSql += orderString + "c.Posted DESC ";

            if (!orderString.Contains("LIMIT"))
            {
                searchSql += limitString;
            }

            return searchSql.Replace("{FTS}", string.Empty);
        }
        /// <summary>
        /// Returns Search results
        /// </summary>
        /// <param name="ToSearch"></param>
        /// <param name="sf">Field to search</param>
        /// <param name="sw">Search what</param>
        /// <param name="fid"></param>
        /// <param name="UserID">ID of user</param>
        /// <returns>Results</returns>
        public static DataTable GetSearchResult( string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userID, int boardId, int maxResults, bool useFullText, bool searchDisplayName)
        {
            /*     if (toSearchWhat == "*")
                   {
                       toSearchWhat = string.Empty;
                   }

                   IEnumerable<int> forumIds = new List<int>();

                   if (forumIDToStartAt != 0)
                   {
                       forumIds = ForumListAll(boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
                   }
             string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);

                   using (FbCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
                   {
                       return MsSqlDbAccess.Current.GetData(cmd);
                   }

            */
            // TODO: to move it to a class
            if (toSearchWhat == "*")
            {
                toSearchWhat = string.Empty;
            }

            IEnumerable<int> forumIds = new List<int>();

            if (forumIDToStartAt != 0)
            {
                forumIds = ForumListAll( boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
            }

              /*  string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);

            using (FbCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
            {
                return MsSqlDbAccess.Current.GetData(cmd);
            } */

            // if ( ToSearch.Length == 0 )
            //	return new DataTable();

            if (toSearchWhat == "*")
                toSearchWhat = "";
            string forumIDs = "";
            string limitString = "";
            string orderString = "";

            if (forumIDToStartAt != 0)
            {
                DataTable dt = forum_listall_sorted( boardId, userID, null, false, forumIDToStartAt);
                foreach (DataRow dr in dt.Rows)
                    forumIDs = forumIDs + Convert.ToInt32(dr["ForumID"]).ToString() + ",";
                forumIDs = forumIDs.Substring(0, forumIDs.Length - 1);
            }

            // fix quotes for SQL insertion...
            toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
            toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();

            string searchSql = (maxResults == 0) ? "SELECT" : ("SELECT ");
            if (maxResults > 0)
            {
            limitString += String.Format(" FIRST {0} ", maxResults.ToString());
            searchSql += limitString;
            }

            searchSql += @" a.FORUMID AS ""ForumID"", a.TOPICID AS ""TopicID"", a.TOPIC AS ""Topic"", b.USERID AS ""UserID"" , COALESCE(c.USERNAME, b.NAME) AS ""Name"", c.MESSAGEID AS ""MessageID"", c.POSTED AS ""Posted"", '' AS ""Message"", c.FLAGS AS ""Flags""";
            searchSql += " FROM "  +
                MsSqlDbAccess.GetObjectName("TOPIC") + " a LEFT JOIN "
                + MsSqlDbAccess.GetObjectName("MESSAGE") + @" c ON a.TOPICID = c.TOPICID LEFT JOIN "
                +  MsSqlDbAccess.GetObjectName("USER") + @" b ON c.USERID = b.USERID join "
                + MsSqlDbAccess.GetObjectName("VACCESS") + @" x ON x.FORUMID=a.FORUMID ";
            searchSql += String.Format(@"WHERE x.READACCESS<>0 AND x.USERID={0} AND c.ISAPPROVED <> 0 AND a.TOPICMOVEDID IS NULL AND a.ISDELETED = 0 AND c.ISDELETED = 0 ", userID);
            orderString += @" ORDER BY a.FORUMID ";

            string[] words;
            bool bFirst;

            if (!String.IsNullOrEmpty(toSearchFromWho))
            {
                searchSql += "AND (";
                bFirst = true;
                int userId;

                // generate user search sql...
                switch (searchFromWhoMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql += " AND ";
                            }
                            else
                            {
                                bFirst = false;
                            }
                            searchSql += string.Format(@" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))", word);
                            if (int.TryParse(word, out userId))
                            {
                                searchSql +=
                                  string.Format(" (c.UserID IN ({0}))", userId);
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql +=
                                       string.Format(" ((c.Username IS NULL AND b.DisplayName LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))", word);
                                }
                                else
                                {
                                    searchSql +=
                                     string.Format(" ((c.Username IS NULL AND b.Name LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))", word);
                                }

                            }

                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchFromWho.Split(' ');
                        foreach (string word in words)
                        {
                            if (!bFirst)
                            {
                                searchSql += " OR ";
                            }
                            else
                            {
                                if (searchDisplayName)
                                {
                                    searchSql += string.Format(
                                      " ((c.USERNAME IS NULL AND b.DISPLAYNAME = '{0}') OR (c.Username = '{0}')", toSearchFromWho);
                                }
                                else
                                {
                                    searchSql += string.Format(@" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))", word);
                                }

                            }

                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (int.TryParse(toSearchFromWho, out userId))
                        {
                            searchSql +=
                              string.Format(" (c.UserID IN ({0}))", userId);
                        }
                        else
                        {
                            searchSql += string.Format(@" ((c.USERNAME IS NULL AND b.NAME = '{0}' ) OR (c.USERNAME = '{0}' ))", toSearchFromWho);
                        }

                        break;
                }
                searchSql += ") ";
            }

            if (!String.IsNullOrEmpty(toSearchWhat))
            {
                searchSql += "AND (";
                bFirst = true;

                // generate message and topic search sql...
                switch (searchWhatMethod)
                {
                    case SearchWhatFlags.AllWords:
                        words = toSearchWhat.Split(' ');
                        if (useFullText)
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst) ftInner += " AND "; else bFirst = false;
                                ftInner += String.Format(@"""{0}""", word);
                            }
                            // make final string...
                            searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' {0} ') OR CONTAINS (a.TOPIC, ' {0} ' ) )", ftInner);
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " AND "; else bFirst = false;
                                searchSql += String.Format(@"(c.MESSAGE like '%{0}%' OR a.TOPIC LIKE '%{0}%' )", word);
                            }
                        }
                        break;
                    case SearchWhatFlags.AnyWords:
                        words = toSearchWhat.Split(' ');

                        if (useFullText)
                        {
                            string ftInner = "";

                            // make the inner FULLTEXT search
                            foreach (string word in words)
                            {
                                if (!bFirst) ftInner += " OR "; else bFirst = false;
                                ftInner += String.Format(@"""{0}""", word);
                            }
                            // make final string...
                            searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' {0} ' ) OR CONTAINS (a.TOPIC, ' {0} ' ) )", ftInner);
                        }
                        else
                        {
                            foreach (string word in words)
                            {
                                if (!bFirst) searchSql += " OR "; else bFirst = false;
                                searchSql += String.Format(@"c.MESSAGE LIKE '%{0}%'  OR a.TOPIC LIKE '%{0}%' ", word);
                            }
                        }
                        break;
                    case SearchWhatFlags.ExactMatch:
                        if (useFullText)
                        {
                           // searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' \"{0}\" ' ) OR CONTAINS (a.Topic, ' \"{0}\" '  )", toSearchWhat);
                        }
                        else
                        {
                            searchSql += string.Format(@"c.MESSAGE LIKE '%{0}%'  OR a.TOPIC LIKE '%{0}%'  ", toSearchWhat);
                        }
                        break;
                }
                searchSql += ") ";
            }

            // Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
            if (forumIDToStartAt > 0)
            {
                searchSql += string.Format(@"AND a.FORUMID IN ({0})", forumIDs);
            }

            if (orderString != "") { orderString += ", "; }
            if (!orderString.Contains("ORDER BY"))
            {
                searchSql += " ORDER BY ";
            }

            searchSql += orderString + @"c.POSTED DESC ";

            using (FbCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
            {
                return MsSqlDbAccess.Current.GetData(cmd);
            }
        }