private void BindGroupsList() { Cn.Open(); ddlGroups.DataSource = Cn.ExecuteReader("SELECT * FROM ForumUserGroups ORDER BY Title"); ddlGroups.DataBind(); Cn.Close(); }
private void BindRepeater() { DataSet ds = new DataSet(); ds.Tables.Add("Messages"); ds.Tables.Add("CompainUsers"); string sql; if (this.IsAdministrator) { sql = @"SELECT ForumComplaints.UserID AS ComplainUserID, ForumMessages.MessageID, ForumMessages.Body, ForumMessages.CreationDate, ForumTopics.TopicID, ForumTopics.Subject, ForumUsers.UserName, ForumMessages.UserID, ForumUsers.PostsCount, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName FROM ((ForumMessages LEFT JOIN ForumUsers ON ForumMessages.UserID=ForumUsers.UserID) INNER JOIN ForumTopics ON ForumMessages.TopicID=ForumTopics.TopicID) INNER JOIN ForumComplaints ON ForumMessages.MessageID=ForumComplaints.MessageID ORDER BY ForumMessages.MessageID DESC" ; } else { sql = @"SELECT ForumComplaints.UserID AS ComplainUserID, ForumMessages.MessageID, ForumMessages.Body, ForumMessages.CreationDate, ForumTopics.TopicID, ForumTopics.Subject, ForumUsers.UserName, ForumMessages.UserID, ForumUsers.PostsCount, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName FROM ((ForumMessages LEFT JOIN ForumUsers ON ForumMessages.UserID=ForumUsers.UserID) INNER JOIN ForumTopics ON ForumMessages.TopicID=ForumTopics.TopicID) INNER JOIN ForumComplaints ON ForumMessages.MessageID=ForumComplaints.MessageID WHERE ForumTopics.ForumID IN (SELECT DISTINCT ForumID FROM ForumModerators WHERE UserID=" + CurrentUserID + @") ORDER BY ForumMessages.MessageID DESC" ; } DbDataReader dr = Cn.ExecuteReader(sql, false); ds.Tables[0].Load(dr); dr.Close(); //now get complainers dr = Cn.ExecuteReader("SELECT UserID, UserName FROM ForumUsers WHERE UserID IN (SELECT UserID FROM ForumComplaints)"); ds.Tables[1].Load(dr); dr.Close(); ds.Relations.Add(new DataRelation("MessagesUsers", ds.Tables[0].Columns["ComplainUserID"], ds.Tables[1].Columns["UserID"], false)); PagedDataSource pagedSrc = new PagedDataSource(); pagedSrc.DataSource = ds.Tables[0].DefaultView; pagedSrc.AllowPaging = true; pagedSrc.PageSize = this.PageSize; int curPage = 0; if (Request.QueryString["page"] != null) { int.TryParse(Request.QueryString["page"], out curPage); } pagedSrc.CurrentPageIndex = curPage; //prepare a string for the "pager" at the bottom pagerString = Utils.Various.GetPaginationString(curPage, pagedSrc.PageCount, "complaints.aspx"); this.rptMessagesList.DataSource = pagedSrc; this.rptMessagesList.DataBind(); rptMessagesList.Visible = (rptMessagesList.Items.Count > 0); divNothingFound.Visible = !rptMessagesList.Visible; }
private void BindMemberGroups() { var groups = Utils.User.GetGroupIdsForUser(_editedUserID); Cn.Open(); if (groups.Any()) { var drMember = Cn.ExecuteReader( @"SELECT ForumUserGroups.GroupID, ForumUserGroups.Title FROM ForumUserGroups WHERE GroupID IN (" + groups.Select(x => x.ToString()).Aggregate((x, y) => x + "," + y) + @") ORDER BY Title" ); rptMember.DataSource = drMember; rptMember.DataBind(); drMember.Close(); } var drNotmember = Cn.ExecuteReader( @"SELECT ForumUserGroups.GroupID, ForumUserGroups.Title FROM ForumUserGroups " + (groups.Any() ? @"WHERE GroupID NOT IN (" + groups.Select(x => x.ToString()).Aggregate((x, y) => x + "," + y) + ") " : "") + "ORDER BY Title"); rptNotMember.DataSource = drNotmember; rptNotMember.DataBind(); Cn.Close(); }
protected void btnPurge_Click(object sender, EventArgs e) { List <int> topicIds = new List <int>(); Cn.Open(); var dr = Cn.ExecuteReader( @"SELECT ForumTopics.TopicID FROM ForumTopics INNER JOIN ForumMessages ON ForumTopics.LastMessageID=ForumMessages.MessageID WHERE ForumMessages.CreationDate<?" , DateTime.Parse(tbDateFrom.Text)); while (dr.Read()) { topicIds.Add(Convert.ToInt32(dr[0])); } dr.Close(); foreach (int topicId in topicIds) { Topic.DeleteTopic(topicId, Cn); } Cn.Close(); lblRes.Text = "OK!"; }
private void BindForumProperties() { Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT * FROM ForumGroups ORDER BY OrderByNumber"); ddlForumGroup.DataSource = dr; ddlForumGroup.DataBind(); dr.Close(); dr = Cn.ExecuteReader("SELECT * FROM Forums WHERE ForumID=" + _forumId); if (dr.Read()) { tbTitle.Text = dr["Title"].ToString(); tbDescr.Text = dr["Description"].ToString(); cbPremoderated.Checked = Convert.ToBoolean(dr["Premoderated"]); cbMembersOnly.Checked = Convert.ToBoolean(dr["MembersOnly"]); cbRestrictTopicCreation.Checked = Convert.ToBoolean(dr["RestrictTopicCreation"]); ddlForumGroup.SelectedValue = dr["GroupID"].ToString(); imgForumIcon.ImageUrl = forums.GetForumIcon(dr["IconFile"].ToString()); } dr.Close(); Cn.Close(); }
private string GetRssXML() { if (Cache["RecentRSS"] != null) { return(Cache["RecentRSS"] as string); } string retval = ""; retval += "<?xml version=\"1.0\"?>\r\n"; retval += "<rss version=\"2.0\">\r\n"; retval += "<channel>\r\n"; retval += "<title>" + Utils.Settings.ForumTitle.Replace("&", "&") + " - Recent Posts</title>\r\n"; retval += "<link>" + Utils.Various.ForumURL + "recent.aspx</link>\r\n"; retval += "<description>" + Utils.Settings.ForumTitle.Replace("&", "&") + " - Recent Posts</description>\r\n"; retval += "<language>en-us</language>\r\n"; retval += "<docs>http://blogs.law.harvard.edu/tech/rss</docs>\r\n"; retval += "<generator>Jitbit AspNetForum</generator>\r\n"; Cn.Open(); DbDataReader dr = Cn.ExecuteReader(@"SELECT TOP 30 ForumMessages.Body, ForumMessages.CreationDate, ForumTopics.TopicID, ForumTopics.Subject, ForumUsers.UserName, ForumUsers.FirstName, ForumUsers.LastName, ForumMessages.UserID, ForumUsers.PostsCount FROM (ForumMessages INNER JOIN ForumTopics ON ForumMessages.TopicID=ForumTopics.TopicID) LEFT JOIN ForumUsers ON ForumMessages.UserID=ForumUsers.UserID WHERE ForumTopics.ForumID NOT IN (SELECT ForumID FROM ForumGroupPermissions WHERE AllowReading=?) AND ForumTopics.ForumID NOT IN (SELECT ForumID FROM Forums WHERE MembersOnly=?) ORDER BY ForumMessages.MessageID DESC" , true, true); if (dr.HasRows) { int i = 0; while (dr.Read()) { if (i == 0) //first record { retval += string.Format("<pubDate>{0}</pubDate>\r\n", ((DateTime)dr["CreationDate"]).ToString("r")); retval += string.Format("<lastBuildDate>{0}</lastBuildDate>\r\n", ((DateTime)dr["CreationDate"]).ToString("r")); } i++; //items retval += "<item>\r\n"; retval += string.Format("<link>{0}</link>\r\n", Utils.Various.ForumURL + Utils.Various.GetTopicURL(dr["TopicID"], dr["Subject"])); retval += "<title>Topic "" + dr["Subject"].ToString().Replace("&", "&") + "" a message from " + Utils.User.GetUserDisplayName(dr["UserName"], dr["FirstName"], dr["LastName"]).Replace("&", "&") + "</title>\r\n"; retval += string.Format("<description><![CDATA[{0}]]></description>\r\n", Utils.Formatting.FormatMessageHTML(dr["Body"].ToString())); retval += string.Format("<pubDate>{0}</pubDate>\r\n", ((DateTime)dr["CreationDate"]).ToString("r")); retval += "</item>\r\n"; } } dr.Close(); Cn.Close(); retval += "</channel>\r\n"; retval += "</rss>\r\n"; Cache.Add("RecentRSS", retval.ToString(), null, DateTime.Now.AddMinutes(15), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); return(retval); }
private void BindModeratorsGrid() { Cn.Open(); gridModerators.DataSource = Cn.ExecuteReader(@"SELECT ForumModerators.UserID, ForumUsers.UserName FROM ForumModerators INNER JOIN ForumUsers ON ForumModerators.UserID = ForumUsers.UserID WHERE ForumModerators.ForumID=" + _forumId); gridModerators.DataBind(); Cn.Close(); lblNoModerators.Visible = (gridModerators.Items.Count == 0); }
private void BindForumsDropDown() { DbDataReader dr = Cn.ExecuteReader("SELECT ForumID, Title FROM Forums"); DataTable dt = new DataTable(); dt.Load(dr); dr.Close(); ddlForumsTop.DataSource = dt; ddlForumsTop.DataBind(); }
private void BindSettings() { Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT * FROM ForumConfig ORDER BY CfgKey"); gridSettings.DataSource = dr; gridSettings.DataBind(); dr.Close(); Cn.Close(); }
private void BindRecentUsers() { DbDataReader dr = Cn.ExecuteReader(@"SELECT top 15 UserID, UserName, AvatarFileName, FirstName, LastName FROM ForumUsers WHERE Disabled=0 AND HidePresence=0 ORDER BY UserID DESC" ); DataTable dt = new DataTable(); dt.Load(dr); dt.DefaultView.Sort = "UserName"; //resort by username rptRecent.DataSource = dt.DefaultView; rptRecent.DataBind(); }
private void BindForums() { this.Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT * FROM Forums ORDER BY OrderByNumber"); this.gridForums.DataSource = dr; this.gridForums.DataBind(); dr.Close(); this.Cn.Close(); lblNoForums.Visible = (gridForums.Items.Count == 0); }
private void BindPermissionsGrid() { Cn.Open(); gridGroups.DataSource = Cn.ExecuteReader(@"SELECT ForumUserGroups.GroupID, ForumUserGroups.Title, ForumGroupPermissions.AllowReading, ForumGroupPermissions.AllowPosting FROM ForumUserGroups INNER JOIN ForumGroupPermissions ON ForumGroupPermissions.GroupID = ForumUserGroups.GroupID WHERE ForumGroupPermissions.ForumID=" + _forumId); gridGroups.DataBind(); Cn.Close(); lblFFA.Visible = (gridGroups.Items.Count == 0); gridGroups.Visible = (gridGroups.Items.Count != 0); }
private void BindGroups() { this.Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT * FROM ForumUserGroups"); this.gridGroups.DataSource = dr; this.gridGroups.DataBind(); dr.Close(); this.Cn.Close(); lblNoGroups.Visible = (gridGroups.Items.Count == 0); }
private string GetRssXML() { //Cn.Open(); the connection should be already open!!!!!! StringBuilder retval = new StringBuilder(); retval.Append("<?xml version=\"1.0\"?>\r\n"); retval.Append("<rss version=\"2.0\">\r\n"); retval.Append("<channel>\r\n"); retval.Append("<title>" + Utils.Settings.ForumTitle.Replace("&", "&") + " - " + _forumTitle.Replace("&", "&") + " - " + _topicSubject.Replace("&", "&") + " - Messages</title>\r\n"); retval.Append("<link>" + Utils.Various.ForumURL + Utils.Various.GetTopicURL(_topicID, _topicSubject) + "</link>\r\n"); retval.Append("<description>" + Utils.Settings.ForumTitle.Replace("&", "&") + " - " + _forumTitle.Replace("&", "&") + " - " + _topicSubject.Replace("&", "&") + " - Messages</description>\r\n"); retval.Append("<language>en-us</language>\r\n"); retval.Append("<docs>http://blogs.law.harvard.edu/tech/rss</docs>\r\n"); retval.Append("<generator>Jitbit AspNetForum</generator>\r\n"); DbDataReader dr = Cn.ExecuteReader( @"SELECT ForumMessages.TopicID, ForumMessages.MessageID, ForumUsers.UserName, ForumUsers.FirstName, ForumUsers.LastName, ForumMessages.CreationDate, ForumMessages.Body, ForumMessages.Visible, ForumMessages.UserID, ForumUsers.PostsCount FROM ForumMessages LEFT JOIN ForumUsers ON ForumMessages.UserID=ForumUsers.UserID WHERE ForumMessages.Visible=? AND ForumMessages.TopicID=" + _topicID + " ORDER BY ForumMessages.CreationDate DESC", true); if (dr.HasRows) { bool firstRecord = true; while (dr.Read()) { if (firstRecord) //first record { retval.Append(string.Format("<pubDate>{0}</pubDate>\r\n", ((DateTime)dr["CreationDate"]).ToString("r"))); retval.Append(string.Format("<lastBuildDate>{0}</lastBuildDate>\r\n", ((DateTime)dr["CreationDate"]).ToString("r"))); firstRecord = false; } //items retval.Append("<item>\r\n"); retval.Append(string.Format("<link>{0}</link>\r\n", Utils.Various.ForumURL + Utils.Various.GetTopicURL(_topicID, _topicSubject))); retval.Append("<title>Message from " + Utils.User.GetUserDisplayName(dr["UserName"], dr["FirstName"], dr["LastName"]).Replace("&", "&") + "</title>\r\n"); retval.Append(string.Format("<description><![CDATA[{0}]]></description>\r\n", Utils.Formatting.FormatMessageHTML(dr["Body"].ToString()))); retval.Append(string.Format("<pubDate>{0}</pubDate>\r\n", ((DateTime)dr["CreationDate"]).ToString("r"))); retval.Append("</item>\r\n"); } } dr.Close(); retval.Append("</channel>\r\n"); retval.Append("</rss>\r\n"); //cache the rss content Cache.Add("MessagesRSS" + _topicID, retval.ToString(), null, DateTime.Now.AddHours(1), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null); return(retval.ToString()); }
private void BindActiveUsers() { DbDataReader dr = Cn.ExecuteReader(@"SELECT TOP 15 ForumUsers.UserID, ForumUsers.UserName, COUNT(ForumMessages.MessageID) AS MsgCount, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName FROM ForumUsers INNER JOIN ForumMessages ON ForumUsers.UserID=ForumMessages.UserID WHERE Disabled=0 AND HidePresence=0 GROUP BY ForumUsers.UserID, ForumUsers.UserName, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName ORDER BY COUNT(ForumMessages.MessageID) DESC" ); rptMostActive.DataSource = dr; rptMostActive.DataBind(); dr.Close(); }
private void BindRecentlyActiveUsers() { DbDataReader dr = Cn.ExecuteReader(@"SELECT TOP 15 ForumUsers.UserID, ForumUsers.UserName, COUNT(ForumMessages.MessageID) AS MsgCount, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName FROM ForumUsers INNER JOIN ForumMessages ON ForumUsers.UserID=ForumMessages.UserID WHERE ForumMessages.CreationDate>? AND Disabled=0 AND HidePresence=0 GROUP BY ForumUsers.UserID, ForumUsers.UserName, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName ORDER BY COUNT(ForumMessages.MessageID) DESC" , Various.GetCurrTime().AddDays(-14)); rptRecentlyActive.DataSource = dr; rptRecentlyActive.DataBind(); dr.Close(); }
private void BindUserGroups() { var groups = Utils.User.GetGroupIdsForUser(_userId); Cn.Open(); DbDataReader dr = Cn.ExecuteReader(@"SELECT ForumUserGroups.GroupID, ForumUserGroups.Title FROM ForumUserGroups WHERE GroupID IN (" + groups.Select(x => x.ToString()).Aggregate((x, y) => x + "," + y) + @")"); gridGroups.DataSource = dr; gridGroups.DataBind(); dr.Close(); Cn.Close(); }
private void GetUserByOpenId(string openId, out int userId, out string userName) { Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT UserID, UserName FROM ForumUsers WHERE OpenIdUserName=?", openId); userId = 0; userName = null; if (dr.Read()) { userId = Convert.ToInt32(dr["UserID"]); userName = dr["UserName"].ToString(); } dr.Close(); Cn.Close(); }
private void BindRepeater() { DbDataReader dr; if (this.IsAdministrator) { dr = Cn.ExecuteReader(@"SELECT ForumMessages.MessageID, ForumMessages.Body, ForumMessages.CreationDate, ForumTopics.TopicID, ForumTopics.Subject, ForumUsers.UserName, ForumMessages.UserID, ForumUsers.PostsCount, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName FROM (ForumMessages LEFT JOIN ForumUsers ON ForumMessages.UserID=ForumUsers.UserID) INNER JOIN ForumTopics ON ForumMessages.TopicID=ForumTopics.TopicID WHERE ForumMessages.Visible=? ORDER BY ForumMessages.MessageID DESC" , false); } else { dr = Cn.ExecuteReader(@"SELECT ForumMessages.MessageID, ForumMessages.Body, ForumMessages.CreationDate, ForumTopics.TopicID, ForumTopics.Subject, ForumUsers.UserName, ForumMessages.UserID, ForumUsers.PostsCount, ForumUsers.AvatarFileName, ForumUsers.FirstName, ForumUsers.LastName FROM (ForumMessages LEFT JOIN ForumUsers ON ForumMessages.UserID=ForumUsers.UserID) INNER JOIN ForumTopics ON ForumMessages.TopicID=ForumTopics.TopicID WHERE ForumMessages.Visible=? AND ForumTopics.ForumID IN (SELECT DISTINCT ForumID FROM ForumModerators WHERE UserID=" + CurrentUserID + @") ORDER BY ForumMessages.MessageID DESC" , false); } DataTable dt = new DataTable(); dt.Load(dr); dr.Close(); PagedDataSource pagedSrc = new PagedDataSource(); pagedSrc.DataSource = dt.DefaultView; pagedSrc.AllowPaging = true; pagedSrc.PageSize = this.PageSize; int curPage = 0; if (Request.QueryString["page"] != null) { int.TryParse(Request.QueryString["page"], out curPage); } pagedSrc.CurrentPageIndex = curPage; //prepare a string for the "pager" at the bottom pagerString = Utils.Various.GetPaginationString(curPage, pagedSrc.PageCount, "unapprovedposts.aspx"); this.rptMessagesList.DataSource = pagedSrc; this.rptMessagesList.DataBind(); rptMessagesList.Visible = (rptMessagesList.Items.Count > 0); divNothingFound.Visible = !rptMessagesList.Visible; }
private void BindRepeater() { List <object> parameters = new List <object>(); string sql = @"SELECT ForumMessages.Body, ForumMessages.CreationDate, ForumTopics.TopicID, ForumTopics.Subject FROM (ForumMessages INNER JOIN ForumTopics ON ForumMessages.TopicID=ForumTopics.TopicID)"; if (CurrentUserID == 0) { sql += " INNER JOIN Forums ON ForumTopics.ForumID = Forums.ForumID "; } sql += @" WHERE ForumTopics.ForumID NOT IN (SELECT ForumID FROM ForumGroupPermissions) AND ForumMessages.UserID=" + userID; if (CurrentUserID == 0) { sql += " AND Forums.MembersOnly=?"; parameters.Add(false); } sql += " ORDER BY ForumMessages.CreationDate"; DbDataReader dr = Cn.ExecuteReader(sql, parameters.ToArray()); DataTable dt = new DataTable(); dt.Load(dr); PagedDataSource pagedSrc = new PagedDataSource { DataSource = dt.DefaultView, AllowPaging = true, PageSize = this.PageSize }; int curPage = 0; if (Request.QueryString["page"] != null) { int.TryParse(Request.QueryString["page"], out curPage); } pagedSrc.CurrentPageIndex = curPage; //prepare a string for the "pager" at the bottom pagerString = Utils.Various.GetPaginationString(curPage, pagedSrc.PageCount, "viewpostsbyuser.aspx?UserID=" + userID); this.rptMessagesList.DataSource = pagedSrc; this.rptMessagesList.DataBind(); }
private void BindForumsListRecursive(int parentId, int lvl) { List <object> parameters = new List <object>(); string commandText = "SELECT Forums.ForumID, Forums.Title FROM Forums "; if (parentId == 0) //not a subforum { commandText += "WHERE Forums.ForumID NOT IN (SELECT SubForumID FROM ForumSubforums)"; //not a subforum } else //a subforum of a specified parent { commandText += @"INNER JOIN ForumSubforums ON ForumSubforums.SubForumID=Forums.ForumID WHERE ForumSubforums.ParentForumID=" + parentId; //subforum for parentID } if (CurrentUserID == 0) { //not a restricted forum commandText += @" AND Forums.MembersOnly=0 AND Forums.ForumID NOT IN (SELECT ForumID FROM ForumGroupPermissions WHERE ForumGroupPermissions.AllowReading=?) " ; parameters.Add(true); } else { string strSQLAllowedForums = Utils.Forum.GetReadableForumsForUserString(Utils.User.CurrentUserID); //not a restricted forum or - a forum with permissions commandText += @" AND Forums.ForumID IN ( " + strSQLAllowedForums + ")"; } DataTable dt = new DataTable(); DbDataReader dr = Cn.ExecuteReader(commandText, parameters.ToArray()); if (dr.HasRows) { dt.Load(dr); } dr.Close(); foreach (DataRow row in dt.Rows) { string indent = new string('-', lvl); ListItem lstItm = new ListItem(indent + row["Title"].ToString(), row["ForumID"].ToString()); ddlForum.Items.Add(lstItm); BindForumsListRecursive(Convert.ToInt32(row["ForumID"]), lvl + 1); } }
private void BindDropDownLists() { Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT ForumID, Title FROM Forums"); ddlParentForum.DataSource = dr; ddlParentForum.DataBind(); dr.Close(); dr = Cn.ExecuteReader("SELECT ForumID, Title FROM Forums WHERE ForumID NOT IN (SELECT SubForumID FROM ForumSubforums)"); ddlSubForum.DataSource = dr; ddlSubForum.DataBind(); dr.Close(); Cn.Close(); divAddSubforum.Visible = (ddlParentForum.Items.Count != 0); }
private void BindBodiesRepeater() { DataSet ds = new DataSet(); ds.Tables.Add("Messages"); ds.Tables.Add("UploadedFiles"); DbDataReader dr = Cn.ExecuteReader(@"SELECT ForumPersonalMessages.MessageID, ForumUsers.UserName, ForumUsers.AvatarFileName, ForumUsers.Signature, ForumPersonalMessages.CreationDate, ForumPersonalMessages.Body, ForumUsers.UserID, ForumPersonalMessages.New, ForumUsers.FirstName, ForumUsers.LastName FROM ForumPersonalMessages INNER JOIN ForumUsers ON ForumPersonalMessages.FromUserID=ForumUsers.UserID WHERE (ForumPersonalMessages.ToUserID=? and ForumPersonalMessages.FromUserID=? and ForumPersonalMessages.HiddenByRecipient=?) OR (ForumPersonalMessages.ToUserID=? and ForumPersonalMessages.FromUserID=? and ForumPersonalMessages.HiddenBySender=?) ORDER BY ForumPersonalMessages.MessageID" , CurrentUserID, _userId, false, _userId, CurrentUserID, false); ds.Tables[0].Load(dr); dr.Close(); //now get files uploaded dr = Cn.ExecuteReader("SELECT FileID, FileName, MessageID, UserID FROM ForumUploadedPersonalFiles WHERE MessageID IN (SELECT MessageID FROM ForumPersonalMessages WHERE ToUserID=" + CurrentUserID + ")"); ds.Tables[1].Load(dr); ds.Relations.Add(new DataRelation("MessagesFiles", ds.Tables[0].Columns["MessageID"], ds.Tables[1].Columns["MessageID"], false)); PagedDataSource pagedSrc = new PagedDataSource(); pagedSrc.DataSource = ds.Tables[0].DefaultView; pagedSrc.AllowPaging = true; pagedSrc.PageSize = this.PageSize; int curPage = 0; if (Request.QueryString["page"] != null) { int.TryParse(Request.QueryString["page"], out curPage); } else if (Request.QueryString["lastpage"] != null) { curPage = pagedSrc.PageCount - 1; } pagedSrc.CurrentPageIndex = curPage; //prepare a string for the "pager" at the bottom pagerString = Utils.Various.GetPaginationString(curPage, pagedSrc.PageCount, "privateinbox.aspx?UserID=" + _userId); this.rptMessagesList.DataSource = pagedSrc; this.rptMessagesList.DataBind(); this.rptMessagesList.Visible = rptMessagesList.Items.Count > 0; }
protected void Page_Load(object sender, System.EventArgs e) { if (!Utils.Settings.EnablePrivateMessaging) { Response.End(); return; } try { toUserID = int.Parse(Request.QueryString["ToUserID"]); if (CurrentUserID == 0) { throw new Exception("not logged in"); } } catch { divMain.Style["display"] = "none"; lblError.Visible = true; return; } btnSave.DataBind(); mailNotificationsEnabled = Utils.Settings.MailNotificationsEnabled; //if quoting if (Request.QueryString["Quote"] != null && !IsPostBack) { int quotedMsgId = int.Parse(Request.QueryString["Quote"]); Cn.Open(); var dr = Cn.ExecuteReader( @"SELECT ForumPersonalMessages.Body, ForumUsers.UserName FROM ForumUsers INNER JOIN ForumPersonalMessages ON ForumUsers.UserID=ForumPersonalMessages.FromUserID WHERE ForumPersonalMessages.MessageID=?" , quotedMsgId); if (dr.Read()) { string body = dr["Body"].ToString().Replace("<br>", "\r\n"); body = System.Text.RegularExpressions.Regex.Replace(body, @"<\S[^>]*>", ""); tbMsg.Text = "[quote=" + dr["UserName"].ToString() + "]" + body + "[/quote]"; } dr.Close(); Cn.Close(); } }
private void BindConversationsRepeater() { DbDataReader dr = Cn.ExecuteReader(@" SELECT m.UserID, ForumUsers.UserName, ForumUsers.FirstName, ForumUsers.LastName, COUNT(m.MessageID) as Posts, MAX(m.CreationDate) as LastMessageDate, MAX(m.NewFlag) as New, ForumUsers.AvatarFileName, ForumUsers.UseGravatar, ForumUsers.Email FROM (SELECT MessageID, FromUserID AS UserID, CreationDate, 2 AS NewFlag FROM ForumPersonalMessages WHERE ToUserID=? AND New=? AND HiddenByRecipient<>? UNION SELECT MessageID, FromUserID AS UserID, CreationDate, 1 AS NewFlag FROM ForumPersonalMessages WHERE ToUserID=? AND New=? AND HiddenByRecipient<>? UNION SELECT MessageID, ToUserID AS UserID, CreationDate, 0 as NewFlag FROM ForumPersonalMessages WHERE FromUserID=? AND HiddenBySender<>?) as m INNER JOIN ForumUsers ON ForumUsers.UserID = m.UserID GROUP BY m.UserID, ForumUsers.UserName, ForumUsers.FirstName, ForumUsers.LastName, ForumUsers.AvatarFileName, ForumUsers.UseGravatar, ForumUsers.Email ORDER BY MAX(m.CreationDate) DESC" , CurrentUserID, true, true, CurrentUserID, false, true, CurrentUserID, true); rptConversationsList.DataSource = dr; rptConversationsList.DataBind(); rptConversationsList.Visible = rptConversationsList.Items.Count > 0; }
private void ShowUserInfo() { Cn.Open(); DbDataReader dr = Cn.ExecuteReader("SELECT * FROM ForumUsers WHERE UserID=" + _editedUserID); if (dr.Read()) { UseGravatar = Convert.ToBoolean(dr["UseGravatar"]); tbUsername.Text = dr["Username"].ToString(); string email = dr["Email"].ToString(); tbEmail.Text = email; tbHomepage.Text = dr["Homepage"].ToString(); tbInterests.Text = dr["Interests"].ToString(); tbSignature.Text = dr["Signature"].ToString(); tbFirstName.Text = dr["FirstName"].ToString(); tbLastName.Text = dr["LastName"].ToString(); cbHidePresence.Checked = dr["HidePresence"] is DBNull ? false : Convert.ToBoolean(dr["HidePresence"]); //avatar string avatarPic = dr["AvatarFileName"].ToString(); imgAvatar.Visible = _bAvatarsEnabled; imgAvatar.Src = Utils.User.GetAvatarFileName(avatarPic, UseGravatar, email); if (avatarPic == "http://") { tbAvatarURL.Text = ""; //old version just saved "http://" to db as the deaful value } else if (avatarPic.StartsWith("http://") || avatarPic.StartsWith("https://")) { tbAvatarURL.Text = avatarPic; } else { tbAvatarURL.Text = ""; //default empty value } } dr.Close(); Cn.Close(); }
private void BindRepeater(string username) { Cn.Open(); string sql = "SELECT * FROM ForumUsers WHERE Disabled=? "; if (!IsAdministrator) { sql += " AND HidePresence=0"; } if (Request.QueryString["Admin"] != null) { sql += " AND UserID IN (SELECT UserID FROM ForumAdministrators)"; } if (username != null && username.Trim() != "") { username = username.Replace("'", ""); //injection protection sql += string.Format(" AND (UserName LIKE '{0}%' OR Email LIKE '{0}%') ", username); } string order = Request.QueryString["order"]; string disabled = Request.QueryString["Disabled"]; if (order == "regdate") { sql += " ORDER BY RegistrationDate"; } else if (order == "email") { sql += " ORDER BY Email"; } else if (order == "posts") { sql += " ORDER BY PostsCount"; } else if (order == "logondate") { sql += " ORDER BY LastLogonDate"; } else { sql += " ORDER BY UserName"; } DataTable dt = new DataTable(); DbDataReader dr = Cn.ExecuteReader(sql, (Request.QueryString["Disabled"] == "1")); dt.Load(dr); dr.Close(); Cn.Close(); PagedDataSource pagedSrc = new PagedDataSource(); pagedSrc.DataSource = dt.DefaultView; pagedSrc.AllowPaging = true; pagedSrc.PageSize = this.PageSize * 5; int curPage = 0; if (Request.QueryString["page"] != null) { int.TryParse(Request.QueryString["page"], out curPage); } pagedSrc.CurrentPageIndex = curPage; pagerString = Utils.Various.GetPaginationString(curPage, pagedSrc.PageCount, "allusers.aspx?order=" + order + "&Disabled=" + disabled + "&q=" + Server.UrlEncode(username == null ? "" : username)); this.rptUsersList.DataSource = pagedSrc; this.rptUsersList.DataBind(); }
private void ShowPollIfAny() { object res = Cn.ExecuteScalar("SELECT PollID FROM ForumPolls WHERE TopicID=" + _topicID); if (res == null) //it is NOT a poll { return; } _pollID = Convert.ToInt32(res); divPoll.Visible = true; bool bShowResults; DbDataReader dr; //get poll name dr = Cn.ExecuteReader("SELECT * FROM ForumPolls WHERE PollID=" + _pollID); dr.Read(); lblPollName.Text = dr["Question"].ToString(); dr.Close(); if (CurrentUserID != 0) //check if current user already voted { res = Cn.ExecuteScalar("SELECT UserID FROM ForumPollAnswers WHERE OptionID IN (SELECT OptionID FROM ForumPollOptions WHERE PollID=" + _pollID + ") AND UserID=" + CurrentUserID); bShowResults = (res != null); //user has voted } else { bShowResults = true; } rblOptions.Visible = !bShowResults; rptVoteResults.Visible = bShowResults; btnVote.Visible = !bShowResults; if (bShowResults) //showing poll results { DataTable dt = new DataTable(); dr = Cn.ExecuteReader( @"SELECT COUNT(ForumPollAnswers.UserID) as VoteCount, ForumPollOptions.OptionID, ForumPollOptions.OptionText FROM ForumPollAnswers RIGHT OUTER JOIN ForumPollOptions ON ForumPollOptions.OptionID = ForumPollAnswers.OptionID WHERE ForumPollOptions.PollID=" + _pollID + @" GROUP BY ForumPollOptions.OptionID, ForumPollOptions.OptionText" ); dt.Load(dr); dr.Close(); //now let's fin max vote count\ _maxvotecount = 0; foreach (DataRow row in dt.Rows) { if (_maxvotecount <= Convert.ToInt32(row["VoteCount"])) { _maxvotecount = Convert.ToInt32(row["VoteCount"]); } } rptVoteResults.DataSource = dt; rptVoteResults.DataBind(); } else if (!IsNonLoginPostBack) //bind poll voting controls { dr = Cn.ExecuteReader("SELECT OptionID, OptionText FROM ForumPollOptions WHERE PollID=" + _pollID); rblOptions.DataSource = dr; rblOptions.DataBind(); dr.Close(); } }
protected void Page_Load(object sender, System.EventArgs e) { //firefox html3.2 rendering fix cboWhoAmI.Style.Add("width", "30%"); tbSubj.Style.Add("width", "100%"); tbMsg.Style.Add("width", "100%"); tbSubj.Attributes["placeholder"] = Resources.various.Subject; btnSave.Text = Resources.various.AddMessage; cbSubscribe.Text = Resources.various.NotifyMeOnReply; _allowFileUploads = Utils.Settings.EnableFileUploads; divFiles.Visible = _allowFileUploads && (CurrentUserID != 0) && !_isIPhoneOrAndroid; _allowGuestPosts = Utils.Settings.AllowGuestPosts; if (Request.QueryString["TopicID"] != null) { _topicID = int.Parse(Request.QueryString["TopicID"]); } if (Request.QueryString["ForumID"] != null) { _forumID = int.Parse(Request.QueryString["ForumID"]); } if (_forumID == 0 && _topicID == 0) { Response.Write("Either Topic or Forum must be specified"); Response.End(); } //if we have an unauthorized user if (CurrentUserID == 0 && !_allowGuestPosts) { Response.Write("Sorry, posting and editing is allowed only for authenticated users"); Response.End(); } cbSubscribe.Visible = Utils.Settings.MailNotificationsEnabled && (CurrentUserID != 0) && !_isIPhoneOrAndroid; btnSmilies.Visible = Utils.Settings.AllowSmilies && !_isIPhoneOrAndroid; spanUtils.Visible = divEditbar.Visible = btnPreview.Visible = !_isIPhoneOrAndroid; Cn.Open(); // Figure out if we're editing or quoting a message, and extract the ID. _messageId = 0; if (Request.QueryString["Edit"] != null) { _messageId = int.Parse(Request.QueryString["Edit"]); _isEditing = true; btnSave.Text = "update message"; //check if it's the first msg in a topic - to see if we should allow changing the topic text object res = Cn.ExecuteScalar("SELECT MIN(MessageID) FROM ForumMessages WHERE TopicID=" + _topicID); _changeTopic = (Convert.ToInt32(res) == _messageId); } if (Request.QueryString["Quote"] != null) { _messageId = int.Parse(Request.QueryString["Quote"]); _isEditing = false; } if (_forumID == 0) //we're NOT adding a new topic to a forum, we're adding msg to an existing { _addTopic = false; bool isTopicClosed = false; string tWhoAmI = ""; DbDataReader dr = Cn.ExecuteReader("SELECT Forums.ForumID, Forums.Title, Forums.Premoderated, ForumTopics.IsClosed, ForumTopics.Subject, ForumTopics.WhoAmI FROM Forums INNER JOIN ForumTopics ON Forums.ForumID=ForumTopics.ForumID WHERE ForumTopics.TopicID=" + _topicID); if (dr.Read()) { _forumID = Convert.ToInt32(dr["ForumID"]); _premoderated = Convert.ToBoolean(dr["Premoderated"]); isTopicClosed = Convert.ToBoolean(dr["IsClosed"]); ForumTitle = dr["Title"].ToString(); cboWhoAmI.SelectedValue = dr["WhoAmI"].ToString(); if (_changeTopic) { if (!IsPostBack) { tbSubj.Text = dr["Subject"].ToString(); } } else { lblSubjectText.Text = dr["Subject"].ToString(); //let's hsow the subj when replying } } dr.Close(); if (isTopicClosed && !_isEditing) { Cn.Close(); Response.End(); return; } } else //we're adding a NEW TOPIC to a forum { _addTopic = true; DbDataReader dr = Cn.ExecuteReader("SELECT Forums.ForumID, Forums.Title, Forums.Premoderated FROM Forums WHERE Forums.ForumID=" + _forumID); if (dr.Read()) { _premoderated = Convert.ToBoolean(dr["Premoderated"]); ForumTitle = dr["Title"].ToString(); } dr.Close(); } divPolls.Visible = _addTopic && !_isIPhoneOrAndroid; if (!Utils.Forum.CheckForumPostPermissions(_forumID, CurrentUserID)) { lblDenied.Visible = true; divMain.Visible = false; } if (_addTopic || _changeTopic) { tbSubj.Visible = true; reqSubject.Enabled = true; } if (!_addTopic) { if (!IsPostBack) { //set the "subscribe me" checkbox if (cbSubscribe.Visible) { var res = Cn.ExecuteScalar("SELECT UserID FROM ForumSubscriptions WHERE UserID=" + CurrentUserID + " AND TopicID=" + _topicID); cbSubscribe.Checked = (res != null); } if (!_isIPhoneOrAndroid) { //display previous messages in a topic var dr = Cn.ExecuteReader( @"SELECT ForumMessages.Body, ForumUsers.UserName, ForumMessages.CreationDate FROM ForumMessages LEFT JOIN ForumUsers ON ForumUsers.UserID=ForumMessages.UserID WHERE ForumMessages.TopicID=" + _topicID + " and ForumMessages.Visible=? ORDER BY ForumMessages.CreationDate DESC", true); rptMessages.DataSource = dr; rptMessages.DataBind(); dr.Close(); } else { rptMessages.Visible = false; } } } //if we-re quoting or editing if (_messageId != 0) { //get the author of the edited message object res = Cn.ExecuteScalar("SELECT UserID FROM ForumMessages WHERE MessageID=" + _messageId); _messageAuthorID = (res == null ? -1 : Convert.ToInt32(res)); //IF not PostBack - lets pre-fill the body field with the message text and show attachments if (!IsPostBack) { DbDataReader dr; //show attachments if (_isEditing) { dr = Cn.ExecuteReader("SELECT FileID, FileName FROM ForumUploadedFiles WHERE MessageID=" + _messageId); rptExistingFiles.DataSource = dr; rptExistingFiles.DataBind(); rptExistingFiles.Visible = (rptExistingFiles.Items.Count > 0); dr.Close(); } dr = Cn.ExecuteReader("SELECT ForumMessages.Body, ForumUsers.UserName, ForumUsers.FirstName, ForumUsers.LastName, ForumMessages.UserID FROM ForumMessages LEFT OUTER JOIN ForumUsers ON ForumUsers.UserID=ForumMessages.UserID WHERE ForumMessages.MessageID=" + _messageId); if (dr.Read()) { string body = dr["Body"].ToString().Replace("<br>", "\r\n").Replace("<br/>", "\r\n").Replace("<br />", "\r\n"); body = System.Text.RegularExpressions.Regex.Replace(body, @"<\S[^>]*>", ""); //if its quoting if (!_isEditing) { //remove domain from username (in case its windows auth) string uname = Utils.User.GetUserDisplayName(dr["UserName"].ToString(), dr["FirstName"].ToString(), dr["LastName"].ToString()); tbMsg.Text = "[quote=" + uname + "]" + body + "[/quote]\r\n\r\n"; } else //if its editing { tbMsg.Text = body; } } dr.Close(); } } Cn.Close(); }
protected void btnSearch_Click(object sender, System.EventArgs e) { if (!Page.IsValid) { return; } string searchStr = tbWords.Text.Trim().Replace("'", ""); //injection protection string[] words = searchStr.Split(new[] { ' ', ',' }); string commandText = ""; List <object> parameters = new List <object>(); #if TRIAL commandText = "SELECT DISTINCT TOP 5 "; #else commandText = "SELECT DISTINCT "; #endif commandText += @" ForumTopics.TopicID, ForumTopics.Subject, ForumTopics.LastMessageID, ForumTopics.RepliesCount FROM ForumTopics INNER JOIN ForumMessages ON ForumMessages.TopicID = ForumTopics.TopicID" ; if (CurrentUserID == 0) //guest user - search in public forums only { commandText += " WHERE ForumTopics.ForumID NOT IN (SELECT ForumID FROM ForumGroupPermissions WHERE AllowReading=?) "; parameters.Add(true); } else //search in allowed forums only { string strSQLAllowedForums = Utils.Forum.GetReadableForumsForUserString(Utils.User.CurrentUserID); commandText += " WHERE ForumTopics.ForumID IN (" + strSQLAllowedForums + " ) "; } if (tbDateTo.Text != "") { commandText += " AND ForumMessages.CreationDate<?"; parameters.Add(DateTime.Parse(tbDateTo.Text)); } if (tbDateFrom.Text != "") { commandText += " AND ForumMessages.CreationDate>?"; parameters.Add(DateTime.Parse(tbDateFrom.Text)); } if (ddlForum.SelectedValue != "") { commandText += string.Format(" AND (ForumTopics.ForumID = {0} OR ForumTopics.ForumID IN (SELECT SubForumID FROM ForumSubforums WHERE ParentForumID={0})) ", int.Parse(ddlForum.SelectedValue)); } if (rbAll.Checked) { string criteria = ""; foreach (string word in words) { criteria += "(ForumTopics.Subject LIKE '%" + word + "%' "; if (!cbSearchTtitleOnly.Checked) { criteria += "OR ForumMessages.Body LIKE '%" + word + "%' "; } criteria += ") AND "; } criteria = " AND (" + criteria.Substring(0, criteria.Length - 5) + ")"; commandText += criteria; } else if (rbExact.Checked) { commandText += " AND (ForumTopics.Subject LIKE '%" + searchStr + "%' "; if (!cbSearchTtitleOnly.Checked) { commandText += "OR ForumMessages.Body LIKE '%" + searchStr + "%' "; } commandText += ")"; } else if (rbAny.Checked) { string criteria = ""; foreach (string word in words) { criteria += "ForumTopics.Subject LIKE '%" + word + "%' "; if (!cbSearchTtitleOnly.Checked) { criteria += "OR ForumMessages.Body LIKE '%" + word + "%' "; } criteria += " OR "; } criteria = " AND (" + criteria.Substring(0, criteria.Length - 4) + ")"; commandText += criteria; } this.Cn.Open(); DbDataReader dr = Cn.ExecuteReader(commandText, parameters.ToArray()); lblNothingFound.Visible = !dr.HasRows; #if TRIAL if (!lblNothingFound.Visible) //something found { lblNothingFound.Visible = true; lblNothingFound.Text = "The free version returns the first 5 results only"; lblNothingFound.ForeColor = Color.Red; } #endif DataTable dt = new DataTable(); dt.Load(dr); dr.Close(); this.rptTopicsList.DataSource = dt; this.rptTopicsList.DataBind(); this.Cn.Close(); }