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();
}
