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); }
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 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 List <IconBKInfo> QueryIcon(int pageIndex, int pageSize, ref int totalRow) { List <IconBKInfo> iconList = null; string sqlStr = @"select * from sadDTICON"; string orderStr = @"order by updt desc"; SearchParamInfo parInfo = new SearchParamInfo(null, sqlStr, null, orderStr, pageIndex, pageSize); int pageCount = 0; try { iconList = _dal.GetItems <IconBKInfo>(ref pageCount, ref totalRow, parInfo.GetPars()); } catch (Exception ex) { _log.Fatal(ex); } return(iconList); }
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); }
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); }