private void FindPosts()
{
string orderby = ddlSortBy.SelectedValue;
var sparams = new SearchParamInfo
{
ForumId = Convert.ToInt32(ddlForum.SelectedValue),
Match = ddlMatch.SelectedValue,
AuthorPostType = ddlUserPosts.SelectedValue,
Author = tbxUserName.Text.Trim(),
SearchFor = searchFor.Text.Trim(),
MessageAndSubject = !cbxSubjectOnly.Checked,
SubjectOnly = cbxSubjectOnly.Checked,
PageSize = Convert.ToInt32(ddlPageSize.SelectedValue),
Archived = cbxArchive.Checked
};
if (sparams.ForumId == -99)
{
//no forum selected
return;
}
if (!string.IsNullOrEmpty(tbxDate.Text.Trim()))
{
if (ddlSince.SelectedValue == "before")
sparams.BeforeDate = Convert.ToDateTime(tbxDate.Text);
else
sparams.SinceDate = Convert.ToDateTime(tbxDate.Text);
}
int rowcount = 0;
CurrentPage = _replyPager.CurrentIndex;
var searchResults = Topics.FindTopics(sparams, CurrentPage, orderby, ref rowcount);
RowCount = rowcount;
SearchResults.DataSource = searchResults;
SearchResults.PageSize = sparams.PageSize;
SearchResults.DataBind();
if(SearchResults.BottomPagerRow != null)
SearchResults.BottomPagerRow.Visible = true;
SearchResults.Visible = true;
HideSearchForm();
}
public static IEnumerable<SearchResult> FindTopics(SearchParamInfo sparams, int currentPage, string orderby, ref int rowcount)
{
ITopic dal = Factory<ITopic>.Create("Topic");
List<SearchResult> topics = new List<SearchResult>(dal.FindTopics(sparams, currentPage,orderby, ref rowcount));
return topics;
}
public IEnumerable<SearchResult> FindTopics(SearchParamInfo sparams, int currentPage, string orderby, ref int rowcount)
{
string whereClause = "";
string orderfield = "";
string whereOperator = "WHERE";
string topictable = sparams.Archived ? "FORUM_A_TOPICS" : "FORUM_TOPICS";
string replytable = sparams.Archived ? "FORUM_A_REPLY" : "FORUM_REPLY";
List<OleDbParameter> param = new List<OleDbParameter>();
switch (orderby)
{
case "Subject":
orderfield = "TT.T_SUBJECT";
break;
case "Replies":
orderfield = "TT.T_REPLIES";
break;
case "Views":
orderfield = "TT.T_VIEW_COUNT";
break;
case "Date":
orderfield = "TT.T_DATE";
break;
case "Author":
orderfield = "TM.M_NAME";
break;
case "ForumOrder":
orderfield = "F.F_SUBJECT";
break;
default:
orderfield = "TT.T_LAST_POST, TOPIC_ID";
break;
}
#region Build WHERE clause
if (sparams.ForumId > 0)
{
param.Add(new OleDbParameter("@ForumId", OleDbType.Integer) { Value = sparams.ForumId });
whereClause = whereClause + whereOperator + " TT.FORUM_ID=@ForumId ";
whereOperator = "AND";
}
if (sparams.SinceDate != DateTime.MinValue)
{
param.Add(new OleDbParameter("@LastVisit", OleDbType.VarChar) { Value = sparams.SinceDate });
whereClause = whereClause + whereOperator + " IIF(TT.T_LAST_POST IS NULL,T.T_DATE,TT.T_LAST_POST) > @LastVisit ";
whereOperator = "AND";
}
if (sparams.BeforeDate != DateTime.MinValue)
{
param.Add(new OleDbParameter("@BeforeDate", OleDbType.VarChar) { Value = sparams.SinceDate });
whereClause = whereClause + whereOperator + " IIF(TT.T_LAST_POST IS NULL,T.T_DATE,TT.T_LAST_POST) < @BeforeDate ";
whereOperator = "AND";
}
if (!String.IsNullOrEmpty(sparams.Author))
{
if (sparams.AuthorPostType == "topic")
{
param.Add(new OleDbParameter("@Author", OleDbType.VarChar) { Value = sparams.Author });
whereClause = whereClause + whereOperator + " TM.M_NAME=@Author ";
}
else if (sparams.AuthorPostType == "any")
{
param.Add(new OleDbParameter("@Author", OleDbType.VarChar) { Value = sparams.Author });
whereClause = whereClause + whereOperator + " (TM.M_NAME=@Author OR TRA.M_NAME=@Author) ";
}
whereOperator = "AND";
}
if (!String.IsNullOrEmpty(sparams.SearchFor))
{
whereClause = whereClause + whereOperator + " (";
string[] searchTerms;
switch (sparams.Match)
{
case "any":
whereOperator = "";
searchTerms = sparams.SearchFor.Split(new[] { ' ' });
if (sparams.MessageAndSubject)
{
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new OleDbParameter("@SearchFor" + i, OleDbType.VarChar) { Value = "%" + searchTerms[i] + "%" });
whereClause = whereClause + whereOperator + "(TT.T_SUBJECT LIKE @SearchFor" + i + " OR TT.T_MESSAGE LIKE @SearchFor" + i + ")";
whereOperator = "OR";
}
}
else
{
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new OleDbParameter("@SearchFor" + i, OleDbType.VarChar) { Value = "%" + searchTerms[i] + "%" });
whereClause = whereClause + whereOperator + "TT.T_SUBJECT LIKE @SearchFor" + i + " ";
whereOperator = "OR";
}
}
break;
case "all":
whereOperator = " (";
searchTerms = sparams.SearchFor.Split(new[] { ' ' });
if (sparams.MessageAndSubject)
{
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new OleDbParameter("@SearchFor" + i, OleDbType.VarChar) { Value = "%" + searchTerms[i] + "%" });
whereClause = whereClause + whereOperator + " TT.T_SUBJECT LIKE @SearchFor" + i + " ";
whereOperator = "AND";
}
whereOperator = ") OR (";
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new OleDbParameter("@SearchFor" + (i + searchTerms.Length), OleDbType.VarChar) { Value = "%" + searchTerms[i] + "%" });
whereClause = whereClause + whereOperator + " TT.T_MESSAGE LIKE @SearchFor" + (i + searchTerms.Length) + " ";
whereOperator = "AND";
}
whereClause = whereClause + ") ";
}
else
{
whereOperator = " (";
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new OleDbParameter("@SearchFor" + i, OleDbType.VarChar) { Value = "%" + searchTerms[i] + "%" });
whereClause = whereClause + whereOperator + " TT.T_SUBJECT LIKE @SearchFor" + i + " ";
whereOperator = "AND";
}
whereClause = whereClause + ") ";
}
break;
default:
if (sparams.MessageAndSubject)
{
param.Add(new OleDbParameter("@SearchFor", OleDbType.VarChar) { Value = "%" + sparams.SearchFor + "%" });
whereClause = whereClause + " TT.T_SUBJECT LIKE @SearchFor OR TT.T_MESSAGE LIKE @SearchFor";
}
else
{
param.Add(new OleDbParameter("@SearchFor", OleDbType.VarChar) { Value = "%" + sparams.SearchFor + "%" });
whereClause = whereClause + " TT.T_SUBJECT LIKE @SearchFor";
}
break;
}
whereClause = whereClause + ") ";
}
#endregion
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("SELECT DISTINCT ");
strSql.AppendLine("C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME,");
strSql.AppendLine("F.F_SUBJECT, F.F_SUBSCRIPTION, F.F_STATUS, F.F_PRIVATEFORUMS, F.F_PASSWORD_NEW,");
strSql.AppendLine("T.CAT_ID, T.FORUM_ID, T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST,");
strSql.AppendLine("T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT,");
strSql.AppendLine("TA.MEMBER_ID, TA.M_NAME, LRA.M_NAME AS LAST_POST_AUTHOR_NAME ");
strSql.AppendLine("FROM");
strSql.AppendLine("((((((");
strSql.AppendFormat("(SELECT TOP {0} TOPIC_ID, T_LAST_POST FROM ", sparams.PageSize).AppendLine();
strSql.AppendFormat("(SELECT TOP {0} TOPIC_ID, T_LAST_POST FROM {1} TT ", Math.Max((rowcount - (currentPage * sparams.PageSize)), sparams.PageSize), topictable).AppendLine();
strSql.Append(whereClause).AppendLine();
strSql.AppendLine("ORDER BY TT.T_LAST_POST DESC, TT.TOPIC_ID DESC) AS foo ");
strSql.AppendLine("ORDER BY T_LAST_POST ASC, TOPIC_ID ASC) as bar ");
strSql.AppendFormat("INNER JOIN {0} T ON bar.TOPIC_ID = T.TOPIC_ID) ", topictable).AppendLine();
strSql.AppendFormat("LEFT JOIN {0} R ON T.TOPIC_ID = R.TOPIC_ID) ", replytable).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}CATEGORY C ON T.CAT_ID = C.CAT_ID) ",Config.ForumTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}FORUM F ON T.FORUM_ID = F.FORUM_ID) ", Config.ForumTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS TA ON T.T_AUTHOR = TA.MEMBER_ID) ", Config.MemberTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS RA ON R.R_AUTHOR = RA.MEMBER_ID) ", Config.MemberTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS LRA ON T.T_LAST_POST_AUTHOR = LRA.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql = strSql.Replace("[WHERECLAUSE]", whereClause);
List<SearchResult> topics = new List<SearchResult>();
using (OleDbDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), param.ToArray()))
{
while (rdr.Read())
{
topics.Add(BoHelper.CopySearchResultToBO(rdr, ref rowcount));
}
}
return topics;
}
public static List<SearchResult> GetTopicsBySubject(string topicSubject)
{
int rowcount = 0;
ITopic dal = Factory<ITopic>.Create("Topic");
SearchParamInfo bysubject = new SearchParamInfo
{
MessageAndSubject = false,
SearchFor = topicSubject,
PageSize = 10,
Match = null
};
return new List<SearchResult>(dal.FindTopics(bysubject, 0, "", ref rowcount));
}
public IEnumerable<SearchResult> FindTopics(SearchParamInfo sparams, int currentPage, string orderby, ref int rowcount)
{
//string whereClause = "";
string orderfield = "";
string whereOperator = "WHERE";
string topictable = sparams.Archived ? Config.ForumTablePrefix + "A_TOPICS" : Config.ForumTablePrefix + "TOPICS";
string replytable = sparams.Archived ? Config.ForumTablePrefix + "A_REPLY" : Config.ForumTablePrefix + "REPLY";
List<SqlParameter> param = new List<SqlParameter>();
switch (orderby)
{
case "Subject" :
orderfield = "TT.T_SUBJECT";
break;
case "Replies" :
orderfield = "TT.T_REPLIES DESC";
break;
case "Views" :
orderfield = "TT.T_VIEW_COUNT DESC";
break;
case "Date" :
orderfield = "TT.T_DATE DESC";
break;
case "Author" :
orderfield = "TM.M_NAME";
break;
case "ForumOrder" :
break;
default :
orderfield = "TT.T_LAST_POST DESC";
break;
}
#region Build WHERE clause
StringBuilder whereStr = new StringBuilder();
if (sparams.ForumId > 0)
{
param.Add(new SqlParameter("@ForumId", SqlDbType.Int) { Value = sparams.ForumId });
whereStr.AppendFormat("{0} TT.FORUM_ID=@ForumId ", whereOperator).AppendLine();
whereOperator = "AND";
}
if (sparams.SinceDate != DateTime.MinValue)
{
param.Add(new SqlParameter("@LastVisit", SqlDbType.VarChar) { Value = sparams.SinceDate });
whereStr.AppendFormat("{0} COALESCE(TT.T_LAST_POST,T.T_DATE) > @LastVisit ", whereOperator).AppendLine();
whereOperator = "AND";
}
if (sparams.BeforeDate != DateTime.MinValue)
{
param.Add(new SqlParameter("@BeforeDate", SqlDbType.VarChar) { Value = sparams.SinceDate });
whereStr.AppendFormat("{0} COALESCE(TT.T_LAST_POST,T.T_DATE) < @BeforeDate ",whereOperator).AppendLine();
whereOperator = "AND";
}
if (!String.IsNullOrEmpty(sparams.Author))
{
if (sparams.AuthorPostType == "topic")
{
param.Add(new SqlParameter("@Author", SqlDbType.VarChar) {Value = sparams.Author});
whereStr.AppendFormat("{0} TM.M_NAME=@Author ", whereOperator).AppendLine();
}
else if (sparams.AuthorPostType == "any")
{
param.Add(new SqlParameter("@Author", SqlDbType.VarChar) { Value = sparams.Author });
whereStr.AppendFormat("{0} (TM.M_NAME=@Author OR TRA.M_NAME=@Author) ", whereOperator).AppendLine();
}
whereOperator = "AND";
}
if (!String.IsNullOrEmpty(sparams.SearchFor))
{
whereStr.AppendFormat("{0} (", whereOperator).AppendLine();
string[] searchTerms;
switch (sparams.Match)
{
case "any":
whereOperator = "";
searchTerms = sparams.SearchFor.Split(new[] { ' ' });
if (sparams.MessageAndSubject)
{
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new SqlParameter("@SearchFor" + i, SqlDbType.NVarChar) { Value = "%" + searchTerms[i] + "%" });
whereStr.AppendFormat("{0} (TT.T_SUBJECT LIKE @SearchFor{1} OR TT.T_MESSAGE LIKE @SearchFor{1} OR TR.R_MESSAGE LIKE @SearchFor{1})", whereOperator, i).AppendLine();
whereOperator = "OR";
}
}
else
{
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new SqlParameter("@SearchFor" + i, SqlDbType.NVarChar) { Value = "%" + searchTerms[i] + "%" });
whereStr.AppendFormat("{0} TT.T_SUBJECT LIKE @SearchFor{1} ", whereOperator, i).AppendLine();
whereOperator = "OR";
}
}
break;
case "all":
whereOperator = " (";
searchTerms = sparams.SearchFor.Split(new[] { ' ' });
if (sparams.MessageAndSubject)
{
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new SqlParameter("@SearchFor" + i, SqlDbType.NVarChar) { Value = "%" + searchTerms[i] + "%" });
whereStr.AppendFormat("{0} TT.T_SUBJECT LIKE @SearchFor{1} ", whereOperator, i).AppendLine();
whereOperator = "AND";
}
whereOperator = ") OR (";
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new SqlParameter("@SearchFor" + (i + searchTerms.Length), SqlDbType.NVarChar) { Value = "%" + searchTerms[i] + "%" });
whereStr.AppendFormat("{0} TT.T_MESSAGE LIKE @SearchFor{1} ", whereOperator, i).AppendLine();
whereOperator = "AND";
}
whereOperator = ") OR (";
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new SqlParameter("@SearchFor" + (i + searchTerms.Length), SqlDbType.NVarChar) { Value = "%" + searchTerms[i] + "%" });
whereStr.AppendFormat("{0} TR.R_MESSAGE LIKE @SearchFor{1} ", whereOperator, i).AppendLine();
whereOperator = "AND";
}
whereStr.AppendLine(")");
}
else
{
whereOperator = " (";
for (int i = 0; i < searchTerms.Length; i++)
{
param.Add(new SqlParameter("@SearchFor" + i, SqlDbType.NVarChar) { Value = "%" + searchTerms[i] + "%" });
whereStr.AppendFormat("{0} TT.T_SUBJECT LIKE @SearchFor{1} ", whereOperator, i).AppendLine();
whereOperator = "AND";
}
whereStr.AppendLine(")");
}
break;
default:
if (sparams.MessageAndSubject)
{
param.Add(new SqlParameter("@SearchFor", SqlDbType.NVarChar) { Value = "%" + sparams.SearchFor + "%" });
whereStr.AppendLine(" TT.T_SUBJECT LIKE @SearchFor OR TT.T_MESSAGE LIKE @SearchFor OR TR.R_MESSAGE LIKE @SearchFor");
}
else
{
param.Add(new SqlParameter("@SearchFor", SqlDbType.NVarChar) { Value = "%" + sparams.SearchFor + "%" });
whereStr.AppendLine(" TT.T_SUBJECT LIKE @SearchFor");
}
break;
}
whereStr.AppendLine(")");
}
#endregion
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("WITH TopicEntities AS (");
strSql.AppendFormat("SELECT TT.TOPIC_ID, DENSE_RANK() OVER (ORDER BY {0}) AS RowNo", orderfield).AppendLine();
strSql.AppendFormat("FROM {0} TT ", topictable).AppendLine();
strSql.AppendFormat("LEFT JOIN {0} TR ON TT.TOPIC_ID = TR.TOPIC_ID ", replytable).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}CATEGORY TC ON TT.CAT_ID = TC.CAT_ID",Config.ForumTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}FORUM TF ON TT.FORUM_ID = TF.FORUM_ID ", Config.ForumTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS TM ON TT.T_AUTHOR = TM.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS TRA ON TR.R_AUTHOR = TRA.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS MEMBERS_1 ON TT.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql.Append(whereStr).AppendLine();
strSql.AppendLine(")");
strSql.AppendLine("SELECT DISTINCT TE.RowNo, C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME, ");
strSql.AppendLine("F.F_SUBJECT, F.F_SUBSCRIPTION, F.F_STATUS, F.F_PRIVATEFORUMS, F.F_PASSWORD_NEW, ");
strSql.AppendLine("T.CAT_ID, T.FORUM_ID, T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST, ");
strSql.AppendLine("T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT, ");
strSql.AppendLine("TA.MEMBER_ID, TA.M_NAME, LRA.M_NAME AS LAST_POST_AUTHOR_NAME,(select count(DISTINCT RowNo) from TopicEntities) as TotalRows ");
strSql.AppendLine("FROM TopicEntities TE ");
strSql.AppendFormat("INNER JOIN {0} T on TE.TOPIC_ID = T.TOPIC_ID ", topictable).AppendLine();
strSql.AppendFormat("LEFT JOIN {0} R ON T.TOPIC_ID = R.TOPIC_ID ", replytable).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}CATEGORY C ON T.CAT_ID = C.CAT_ID ", Config.ForumTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}FORUM F ON T.FORUM_ID = F.FORUM_ID ", Config.ForumTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS TA ON T.T_AUTHOR = TA.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS RA ON R.R_AUTHOR = RA.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql.AppendFormat("LEFT JOIN {0}MEMBERS LRA ON T.T_LAST_POST_AUTHOR = LRA.MEMBER_ID ", Config.MemberTablePrefix).AppendLine();
strSql.AppendLine("WHERE TE.RowNo Between @Start AND @MaxRows ORDER BY TE.RowNo ");
List<SearchResult> topics = new List<SearchResult>();
param.Add(new SqlParameter("@Start", SqlDbType.Int) { Value = currentPage*sparams.PageSize });
param.Add(new SqlParameter("@MaxRows", SqlDbType.VarChar) { Value = currentPage * sparams.PageSize + sparams.PageSize });
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSql.ToString(), param.ToArray()))
{
while (rdr.Read())
{
topics.Add(BoHelper.CopySearchResultToBO(rdr,ref rowcount));
}
}
return topics;
}
