public static ReportData CommentsByPost(DateTime min, DateTime max)
{
// top 10
DataProvider dp = DataService.Provider;
QueryCommand cmd = new QueryCommand(@"
SELECT Title, Id, IdCount from (
SELECT max(p.Title) as Title, p.Id, " + dp.SqlCountFunction("c.Id") + @" as IdCount
from
graffiti_Comments AS c
left outer join
graffiti_Posts AS p on p.Id = c.PostId
where
c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @"
and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
and c.IsDeleted = 0
group by
p.Id) as dv
order by
IdCount desc
");
Parameter pPublished = Comment.FindParameter("Published");
cmd.Parameters.Add("MinDate", min, pPublished.DbType);
cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType);
return(GetPostDictionary(cmd, 10));
}
public static IDictionary <DateTime, int> CommentsByDate(DateTime min, DateTime max)
{
DataProvider dp = DataService.Provider;
QueryCommand cmd = new QueryCommand(@"
select " +
dp.SqlYearFunction("c.Published") + " as dvYear, " +
dp.SqlMonthFunction("c.Published") + " as dvMonth, " +
dp.SqlDayFunction("c.Published") + " as dvDay, " +
dp.SqlCountFunction("c.Id") + @" as IdCount
from
graffiti_Comments AS c
left outer join
graffiti_Posts AS p on p.Id = c.PostId
where
c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @"
and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
and c.IsDeleted = 0
group by " +
dp.SqlYearFunction("c.Published") + ", " +
dp.SqlMonthFunction("c.Published") + ", " +
dp.SqlDayFunction("c.Published")
);
Parameter pPublished = Comment.FindParameter("Published");
cmd.Parameters.Add("MinDate", min, pPublished.DbType);
cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType);
return(GetDateDictionary(cmd));
}
protected PostCollection GetTaggedPosts(string tagName)
{
PostCollection pc = ZCache.Get <PostCollection>("Tags-ForRSS-" + tagName);
if (pc == null)
{
pc = Post.FetchPostsByTag(TagName);
PostCollection permissionsFiltered = new PostCollection();
foreach (Post post in pc)
{
permissionsFiltered.Add(post);
}
permissionsFiltered.AddRange(pc);
foreach (Post p in pc)
{
if (!RolePermissionManager.GetPermissions(p.Category.Id, GraffitiUsers.Current).Read)
{
permissionsFiltered.Remove(p);
}
}
pc.Clear();
int ctr = 0;
foreach (Post post in permissionsFiltered)
{
if (ctr < Util.PageSize)
{
pc.Add(post);
ctr++;
}
}
ZCache.InsertCache("Tags-ForRSS-" + tagName, pc, 120);
}
return(pc);
}
public override string RenderData()
{
StringBuilder sb = new StringBuilder("<ul>");
Urls urls = new Urls();
HttpContext context = HttpContext.Current;
if (context != null)
{
TemplatedThemePage ttp = context.Handler as TemplatedThemePage;
if (ttp != null && ttp.PostId > 0)
{
Post p = new Post(ttp.PostId);
if (RolePermissionManager.GetPermissions(p.CategoryId, GraffitiUsers.Current).Edit)
{
sb.AppendFormat("<li><a href=\"{0}\">{1}</a></li>\n", urls.Edit(ttp.PostId), "Edit this Post");
}
}
}
if (RolePermissionManager.CanViewControlPanel(GraffitiUsers.Current))
{
sb.AppendFormat("<li><a href=\"{0}\">{1}</a></li>\n", urls.Write, "Write a new Post");
sb.AppendFormat("<li><a href=\"{0}\">{1}</a></li>\n", urls.Admin, "Control Panel");
}
sb.AppendFormat("<li><a href=\"{0}\">{1}</a></li>\n", urls.Logout, "Logout");
sb.Append("</ul>\n");
return(sb.ToString());
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
Initialize();
SiteSettings settings = SiteSettings.Get();
if (!settings.InitialSetupCompleted)
{
Response.Redirect(ResolveUrl("~/graffiti-setup/"));
}
if (string.IsNullOrEmpty(RedirectUrl))
{
GraffitiContext graffitiContext = GraffitiContext.Current;
SetContextDefault(graffitiContext, ViewName);
LoadContent(graffitiContext);
if (
!RolePermissionManager.GetPermissions(CategoryID, GraffitiUsers.Current,
graffitiContext["where"].ToString() == "home" ||
graffitiContext["where"].ToString() == "search").Read)
{
Response.Redirect(ResolveUrl("~/access-denied/"));
}
ViewManager.Render(Context, graffitiContext, ThemeName);
}
else
{
RedirectTo(VirtualPathUtility.ToAbsolute(RedirectUrl));
}
}
public static ReportData GetViewsByPost(DateTime min, DateTime max)
{
// top 10
DataProvider dp = DataService.Provider;
QueryCommand cmd = new QueryCommand(@"
select Title, Id, IdCount FROM ( SELECT
max(p.Title) as Title, p.Id, " + dp.SqlCountFunction("p.Id") + @" as IdCount
from
graffiti_Post_Statistics AS ps
left outer join
graffiti_Posts AS p on p.Id = ps.PostId
where
ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @"
and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
group by
p.Id) as dv
order by
IdCount desc
");
Parameter pDateViewed = PostStatistic.FindParameter("DateViewed");
cmd.Parameters.Add("MinDate", min, pDateViewed.DbType);
cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType);
return(GetPostDictionary(cmd, 10));
}
/// <summary>
/// Returns all the users for a given role
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
public static List <IGraffitiUser> GetUsers(string role)
{
var userList = ZCache.Get <string[]>("usersByRole-" + role);
if (userList == null)
{
if (role != "*")
{
userList = controller.GetUsersInRole(role);
}
else
{
StringCollection sc = new StringCollection();
foreach (RolePermissions rp in RolePermissionManager.GetRolePermissions())
{
var users = controller.GetUsersInRole(rp.RoleName);
foreach (string u in users)
{
if (!sc.Contains(u))
{
sc.Add(u.ToLower());
}
}
}
var admimUsers = controller.GetUsersInRole(AdminRole);
foreach (string u in admimUsers)
{
if (!sc.Contains(u))
{
sc.Add(u.ToLower());
}
}
userList = new string[sc.Count];
sc.CopyTo(userList, 0);
}
ZCache.InsertCache("usersByRole-" + role, userList, 180);
}
var the_users = new List <IGraffitiUser>();
foreach (string username in userList)
{
the_users.Add(GetUser(username));
}
the_users.Sort(delegate(IGraffitiUser u1, IGraffitiUser u2)
{
return
(Comparer <string> .Default.Compare(
u1.ProperName, u2.ProperName));
});
return(the_users);
}
public static int CommitPost(Post p, IGraffitiUser user, bool isFeaturedPost, bool isFeaturedCategory)
{
Permission perm = RolePermissionManager.GetPermissions(p.CategoryId, user);
bool isMan = perm.Publish;
bool isEdit = GraffitiUsers.IsAdmin(user);
if (isMan || isEdit)
{
p.IsPublished = (p.PostStatus == PostStatus.Publish);
}
else
{
p.IsPublished = false;
if (p.PostStatus != PostStatus.Draft && p.PostStatus != PostStatus.PendingApproval)
{
p.PostStatus = PostStatus.Draft;
}
}
p.ModifiedBy = user.Name;
if (p.IsNew) //No VERSION WORK, just save it.
{
p.Version = 1;
p.Save(user.Name, SiteSettings.CurrentUserTime);
}
else if (p.IsPublished) //Make a copy of the current post, then save this one.
{
Post old_Post = new Post(p.Id);
//if(old_Post.PostStatus == PostStatus.Publish)
VersionPost(old_Post);
p.Version = GetNextVersionId(p.Id, p.Version);
p.Save(user.Name);
}
else
{
p.Version = GetNextVersionId(p.Id, p.Version);
VersionPost(p);
Post.UpdatePostStatus(p.Id, p.PostStatus);
}
ProcessFeaturedPosts(p, user, isFeaturedPost, isFeaturedCategory);
if (p.PostStatus == PostStatus.PendingApproval)
{
SendPReqiresApprovalMessage(p, user);
}
else if (p.PostStatus == PostStatus.RequiresChanges)
{
SendRequestedChangesMessage(p, user);
}
return(p.Id);
}
public static void SendPReqiresApprovalMessage(Post p, IGraffitiUser user)
{
var users = new List <IGraffitiUser>();
foreach (IGraffitiUser u in GraffitiUsers.GetUsers("*"))
{
if (GraffitiUsers.IsAdmin(u) || RolePermissionManager.GetPermissions(p.CategoryId, u).Publish)
{
users.Add(u);
}
}
Macros m = new Macros();
EmailTemplateToolboxContext pttc = new EmailTemplateToolboxContext();
pttc.Put("sitesettings", SiteSettings.Get());
pttc.Put("post", p);
pttc.Put("user", user);
pttc.Put("macros", m);
pttc.Put("home", m.FullUrl(new Urls().Home));
pttc.Put("adminUrl",
m.FullUrl(VirtualPathUtility.ToAbsolute("~/graffiti-admin/posts/write/")) + "?id=" + p.Id + "&v=" +
p.Version);
string adminApprovalUrl = m.FullUrl(VirtualPathUtility.ToAbsolute("~/api/approve.ashx")) +
"?key={0}&u={1}&id={2}&v={3}";
EmailTemplate template = new EmailTemplate();
template.Context = pttc;
template.Subject = "You have content to approve: " + p.Title;
template.TemplateName = "QueuedPost.view";
foreach (IGraffitiUser admin in users)
{
template.Context.Put("adminApprovalUrl",
string.Format(adminApprovalUrl, admin.UniqueId, admin.Name, p.Id, p.Version));
try
{
template.To = admin.Email;
Emailer.Send(template);
//Emailer.Send("QueuedPost.view", admin.Email, "You have content to approve: " + p.Title, pttc);
}
catch (Exception ex)
{
Log.Error("Email Error", ex.Message);
}
}
Log.Info("Post approval email", "{0} user(s) were sent an email to approve the post \"{1}\" (id: {2}).", users.Count,
p.Title, p.Id);
}
protected override void Authenticate()
{
if (GraffitiUsers.Current == null)
{
Response.Redirect("~/login/");
}
if (!RolePermissionManager.CanViewControlPanel(GraffitiUsers.Current) && !GraffitiUsers.IsAdmin(GraffitiUsers.Current))
{
Response.Redirect("~/");
}
}
public void ProcessRequest(HttpContext context)
{
if (!RolePermissionManager.CanViewControlPanel(GraffitiUsers.Current))
{
context.Response.End();
}
string report;
report = context.Request.QueryString["report"];
this.context = context;
switch (report)
{
case "ViewsByDate":
ViewsByDateReport();
break;
case "ViewsByDate_Single":
ViewsByDateReport_Single();
break;
case "ViewsByPost":
ViewsByPostReport();
break;
case "MostPopularPosts":
MostPopularPostReport();
break;
case "ViewsByPost_Single":
ViewsByPostReport_Single();
break;
case "CommentsByDate":
CommentsByDateReport();
break;
case "CommentsByDate_Single":
CommentsByDateReport_Single();
break;
case "CommentsByPost":
CommentsByPostReport();
break;
case "CommentsByPost_Single":
CommentsByPostReport_Single();
break;
}
}
public static void AddUpdateRole(string roleName, bool hasRead, bool hasEdit, bool hasPublish)
{
RolePermissions permissions = new RolePermissions();
permissions.RoleName = roleName;
permissions.HasRead = hasRead;
permissions.HasEdit = hasEdit;
permissions.HasPublish = hasPublish;
permissions.Save();
RolePermissionManager.MarkDirty();
// Add role to other membership databases
controller.AddRole(roleName);
}
public static void AddUpdateRole(string roleName, int categoryID, bool hasRead, bool hasEdit, bool hasPublish)
{
RoleCategoryPermissions rcp = new RoleCategoryPermissions();
rcp.RoleName = roleName;
rcp.HasRead = hasRead;
rcp.HasEdit = hasEdit;
rcp.HasPublish = hasPublish;
rcp.CategoryId = categoryID;
rcp.Save();
RolePermissionManager.MarkDirty();
// Add role to other membership databases
controller.AddRole(roleName);
}
public static bool IsEveryoneAContentPublisher()
{
RolePermissionsCollection rpc = RolePermissionManager.GetRolePermissions();
foreach (RolePermissions rp in rpc)
{
if (rp.RoleName == GraffitiUsers.EveryoneRole)
{
if (rp.HasEdit || rp.HasPublish)
{
return(true);
}
}
}
return(false);
}
public static void SendRequestedChangesMessage(Post p, IGraffitiUser user)
{
List <IGraffitiUser> users = new List <IGraffitiUser>();
foreach (IGraffitiUser u in GraffitiUsers.GetUsers("*"))
{
if (GraffitiUsers.IsAdmin(u) || RolePermissionManager.GetPermissions(p.CategoryId, u).Publish)
{
users.Add(u);
}
}
Macros m = new Macros();
EmailTemplateToolboxContext pttc = new EmailTemplateToolboxContext();
pttc.Put("sitesettings", SiteSettings.Get());
pttc.Put("post", p);
pttc.Put("user", user);
pttc.Put("macros", m);
pttc.Put("home", m.FullUrl(new Urls().Home));
pttc.Put("adminUrl",
m.FullUrl(VirtualPathUtility.ToAbsolute("~/graffiti-admin/posts/write/")) + "?id=" + p.Id + "&v=" +
p.Version);
EmailTemplate template = new EmailTemplate();
template.Context = pttc;
template.To = p.User.Email;
template.Subject = "Changes Requested: " + p.Title;
template.TemplateName = "RequestChanges.view";
try
{
Emailer.Send(template);
//Emailer.Send("RequestChanges.view", p.User.Email, "Changes Requested: " + p.Title, pttc);
Log.Info("Post Changes Email", p.User.Email + " was sent an email requesting changes");
}
catch (Exception ex)
{
Log.Error("Email Requested Changes Error", ex.Message);
}
}
public static ReportData MostPopularPosts()
{
// top 5
DataProvider dp = DataService.Provider;
QueryCommand cmd = new QueryCommand(@"
SELECT Title, Id, IdCount FROM ( SELECT
p.Title, p.Id, " + dp.SqlCountFunction("p.Id") + @" as IdCount
from
graffiti_Post_Statistics AS ps
left outer join
graffiti_Posts AS p on p.Id = ps.PostId
where p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
group by
p.Title, p.Id) as dv
order by
IdCount desc
");
return(GetPostDictionary(cmd, 5));
}
public override string RenderData()
{
StringBuilder sb = new StringBuilder("<ul>");
Data data = new Data();
PostCollection pc = CategoryId > 0
? data.PostsByCategory(
new CategoryController().GetCachedCategory(CategoryId, true), NumberOfPosts)
: data.RecentPosts(NumberOfPosts);
foreach (Post p in pc)
{
if (RolePermissionManager.GetPermissions(p.CategoryId, GraffitiUsers.Current).Read)
{
sb.AppendFormat("<li><a href=\"{0}\">{1}</a>{2}</li>\n", p.Url, p.Title, ShowExcerpt ? "<br />" + p.CustomExcerpt(100) : null);
}
}
sb.Append("</ul>\n");
return(sb.ToString());
}
public static void DeleteRole(string roleName)
{
// Remove users from role
List <IGraffitiUser> roleUsers = GraffitiUsers.GetUsers(roleName);
if (roleUsers != null && roleUsers.Count > 0)
{
foreach (IGraffitiUser user in roleUsers)
{
GraffitiUsers.RemoveUserFromRole(user.Name, roleName);
}
}
RolePermissionsCollection rp = RolePermissionManager.GetRolePermissions();
RoleCategoryPermissionsCollection rpc = RolePermissionManager.GetRoleCategoryPermissions();
foreach (RolePermissions rperm in rp)
{
if (String.Compare(rperm.RoleName, roleName, StringComparison.InvariantCultureIgnoreCase) == 0)
{
RolePermissions.Destroy(RolePermissions.Columns.RoleName, rperm.RoleName);
break;
}
}
foreach (RoleCategoryPermissions rcatperm in rpc)
{
if (String.Compare(rcatperm.RoleName, roleName, StringComparison.InvariantCultureIgnoreCase) == 0)
{
RoleCategoryPermissions.Destroy(RolePermissions.Columns.RoleName, rcatperm.RoleName);
break;
}
}
RolePermissionManager.MarkDirty();
// Remove role from other membership databases
controller.DeleteRole(roleName);
}
protected override void OnLoad(EventArgs e)
{
Initialize();
SiteSettings settings = SiteSettings.Get();
string baseUrl = SiteSettings.BaseUrl;
if (string.IsNullOrEmpty(TagName))
{
Category category = null;
if (CategoryID > -1)
{
category = new CategoryController().GetCachedCategory(CategoryID, false);
}
if (category == null)
{
if (!string.IsNullOrEmpty(settings.ExternalFeedUrl) &&
Request.UserAgent.IndexOf("FeedBurner", StringComparison.InvariantCultureIgnoreCase) == -1)
{
Context.Response.RedirectLocation = settings.ExternalFeedUrl;
Context.Response.StatusCode = 301;
Context.Response.End();
}
}
else if (!string.IsNullOrEmpty(category.FeedUrlOverride) &&
Request.UserAgent.IndexOf("FeedBurner", StringComparison.InvariantCultureIgnoreCase) == -1)
{
Context.Response.RedirectLocation = category.FeedUrlOverride;
Context.Response.StatusCode = 301;
Context.Response.End();
}
else if (CategoryName != null && !Util.AreEqualIgnoreCase(CategoryName, category.LinkName))
{
Context.Response.RedirectLocation = new Uri(Context.Request.Url, category.Url).ToString();
Context.Response.StatusCode = 301;
Context.Response.End();
}
string cacheKey = CategoryID > -1
? "Posts-Index-" + Util.PageSize + "-" + CategoryID.ToString()
: string.Format("Posts-Categories-P:{0}-C:{1}-T:{2}-PS:{3}", 1, CategoryID,
SortOrderType.Descending, Util.PageSize);
PostCollection pc = ZCache.Get <PostCollection>(cacheKey);
if (pc == null)
{
Query q = PostCollection.DefaultQuery();
q.Top = Util.PageSize.ToString();
if (SiteSettings.Get().IncludeChildPosts&& macros.IsNotNull(category))
{
if (category.ParentId > 0)
{
q.AndWhere(Post.Columns.CategoryId, CategoryID);
}
else
{
var ids = new List <int>(category.Children.Count + 1);
foreach (Category child in category.Children)
{
ids.Add(child.Id);
}
ids.Add(category.Id);
q.AndInWhere(Post.Columns.CategoryId, ids.ToArray());
}
}
else
{
if (CategoryID > 0)
{
q.AndWhere(Post.Columns.CategoryId, CategoryID);
}
}
pc = new PostCollection();
pc.LoadAndCloseReader(q.ExecuteReader());
PostCollection permissionsFiltered = new PostCollection();
permissionsFiltered.AddRange(pc);
foreach (Post p in pc)
{
if (!RolePermissionManager.GetPermissions(p.CategoryId, GraffitiUsers.Current).Read)
{
permissionsFiltered.Remove(p);
}
}
ZCache.InsertCache(cacheKey, permissionsFiltered, 90);
pc = permissionsFiltered;
}
ValidateAndSetHeaders(pc, settings, Context);
StringWriter sw = new StringWriter();
sw.WriteLine("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
XmlTextWriter writer = new XmlTextWriter(sw);
writer.WriteStartElement("rss");
writer.WriteAttributeString("version", "2.0");
writer.WriteAttributeString("xmlns:dc", "http://purl.org/dc/elements/1.1/");
writer.WriteAttributeString("xmlns:slash", "http://purl.org/rss/1.0/modules/slash/");
// Allow plugins to add additional xml namespaces
Core.Events.Instance().ExecuteRssNamespace(writer);
writer.WriteStartElement("channel");
WriteChannel(writer, category, settings);
// Allow plugins to add additional xml to the <channel>
Core.Events.Instance().ExecuteRssChannel(writer);
foreach (Post p in pc)
{
writer.WriteStartElement("item");
WriteItem(writer, p, settings, baseUrl);
// Allow plugins to add additional xml to the <item>
Core.Events.Instance().ExecuteRssItem(writer, p);
writer.WriteEndElement(); // End Item
}
writer.WriteEndElement(); // End Channel
writer.WriteEndElement(); // End Document
// save XML into response
Context.Response.ContentEncoding = Encoding.UTF8;
Context.Response.ContentType = "application/rss+xml";
Context.Response.Write(sw.ToString());
}
else
{
PostCollection pc = GetTaggedPosts(TagName);
ValidateAndSetHeaders(pc, settings, Context);
StringWriter sw = new StringWriter();
sw.WriteLine("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
XmlTextWriter writer = new XmlTextWriter(sw);
writer.WriteStartElement("rss");
writer.WriteAttributeString("version", "2.0");
writer.WriteAttributeString("xmlns:dc", "http://purl.org/dc/elements/1.1/");
writer.WriteAttributeString("xmlns:slash", "http://purl.org/rss/1.0/modules/slash/");
Core.Events.Instance().ExecuteRssNamespace(writer);
writer.WriteStartElement("channel");
WriteChannel(writer, TagName, settings);
// Allow plugins to add additional xml to the <channel>
Core.Events.Instance().ExecuteRssChannel(writer);
foreach (Post p in pc)
{
writer.WriteStartElement("item");
WriteItem(writer, p, settings, baseUrl);
Core.Events.Instance().ExecuteRssItem(writer, p);
writer.WriteEndElement(); // End Item
}
writer.WriteEndElement(); // End Channel
writer.WriteEndElement(); // End Document
Context.Response.ContentEncoding = Encoding.UTF8;
Context.Response.ContentType = "application/rss+xml";
Context.Response.Write(sw.ToString());
}
}
public static List <CategoryCount> GetCategoryCountForStatus(PostStatus status, string authorID)
{
List <CategoryCount> catCounts = new List <CategoryCount>();
List <CategoryCount> final = new List <CategoryCount>();
DataProvider dp = DataService.Provider;
QueryCommand cmd = new QueryCommand(String.Empty);
if (String.IsNullOrEmpty(authorID))
{
cmd.Sql = @"select c.Id, " + dp.SqlCountFunction("c.Name") + @" as IdCount, p.CategoryId from graffiti_Posts AS p
inner join graffiti_Categories AS c on p.CategoryId = c.Id
where p.Status = " + dp.SqlVariable("Status") + @" and p.IsDeleted = 0
group by c.Id, p.CategoryId";
}
else
{
cmd.Sql = @"select c.Id, " + dp.SqlCountFunction("c.Name") + @" as IdCount, p.CategoryId from ((graffiti_Posts AS p
inner join graffiti_Categories AS c on p.CategoryId = c.Id)
inner join graffiti_Users AS u on p.CreatedBy = u.Name)
where p.Status = " + dp.SqlVariable("Status") + @" and p.IsDeleted = 0 and u.Id = " + dp.SqlVariable("AuthorId") +
@" group by c.Id, p.CategoryId";
}
cmd.Parameters.Add(Post.FindParameter("Status")).Value = (int)status;
if (!String.IsNullOrEmpty(authorID))
{
cmd.Parameters.Add("AuthorId", Convert.ToInt32(authorID), Graffiti.Core.User.FindParameter("Id").DbType);
}
using (IDataReader reader = DataService.ExecuteReader(cmd))
{
while (reader.Read())
{
CategoryCount catCount = new CategoryCount();
catCount.ID = Int32.Parse(reader["Id"].ToString());
catCount.Count = Int32.Parse(reader["IdCount"].ToString());
catCount.CategoryId = Int32.Parse(reader["CategoryId"].ToString());
catCounts.Add(catCount);
}
reader.Close();
}
// populate the category name
CategoryCollection cats = new CategoryController().GetAllCachedCategories();
List <CategoryCount> tempParentList = new List <CategoryCount>();
foreach (CategoryCount cc in catCounts)
{
Category temp = cats.Find(
delegate(Category c)
{
return(c.Id == cc.ID);
});
if (temp != null)
{
cc.Name = temp.Name;
cc.ParentId = temp.ParentId;
}
if (cc.Count > 0 && cc.ParentId >= 1)
{
// if it's not already in the list, add it
CategoryCount parent = catCounts.Find(
delegate(CategoryCount cac)
{
return(cac.ID == cc.ParentId);
});
if (parent == null)
{
parent = tempParentList.Find(
delegate(CategoryCount cac)
{
return(cac.ID == cc.ParentId);
});
if (parent == null)
{
Category tempParent = cats.Find(
delegate(Category cttemp)
{
return(cttemp.Id == cc.ParentId);
});
parent = new CategoryCount();
parent.ID = tempParent.Id;
parent.ParentId = tempParent.ParentId;
parent.Name = tempParent.Name;
parent.Count = 0;
tempParentList.Add(parent);
}
}
}
}
catCounts.AddRange(tempParentList);
List <CategoryCount> filteredPermissions = new List <CategoryCount>();
filteredPermissions.AddRange(catCounts);
foreach (CategoryCount ac in catCounts)
{
if (!RolePermissionManager.GetPermissions(ac.CategoryId, GraffitiUsers.Current).Read)
{
filteredPermissions.Remove(ac);
}
}
foreach (CategoryCount ac in filteredPermissions)
{
CategoryCount existing = final.Find(
delegate(CategoryCount catcount)
{
return(catcount.ID == ac.ID);
});
if (existing == null)
{
final.Add(ac);
}
else
{
existing.Count += ac.Count;
}
}
return(final);
}
public static List <PostCount> GetPostCounts(int catID, string user)
{
List <PostCount> postCounts = new List <PostCount>();
List <PostCount> final = new List <PostCount>();
List <Parameter> parameters = Post.GenerateParameters();
QueryCommand cmd = new QueryCommand("Select Status, CategoryId, " + DataService.Provider.SqlCountFunction("Id") + " as StatusCount FROM graffiti_Posts Where IsDeleted = 0");
if (catID > 0)
{
cmd.Sql += " and CategoryId = " + DataService.Provider.SqlVariable("CategoryId");
cmd.Parameters.Add(Post.FindParameter(parameters, "CategoryId")).Value = catID;
}
if (!String.IsNullOrEmpty(user))
{
cmd.Sql += " and CreatedBy = " + DataService.Provider.SqlVariable("CreatedBy");
cmd.Parameters.Add(Post.FindParameter(parameters, "CreatedBy")).Value = user;
}
cmd.Sql += " group by Status, CategoryId";
using (IDataReader reader = DataService.ExecuteReader(cmd))
{
while (reader.Read())
{
PostCount postCount = new PostCount();
postCount.PostStatus = (PostStatus)Int32.Parse(reader["Status"].ToString());
postCount.Count = Int32.Parse(reader["StatusCount"].ToString());
postCount.CategoryId = Int32.Parse(reader["CategoryId"].ToString());
postCounts.Add(postCount);
}
reader.Close();
}
List <PostCount> filteredPermissions = new List <PostCount>();
filteredPermissions.AddRange(postCounts);
foreach (PostCount ac in postCounts)
{
if (!RolePermissionManager.GetPermissions(ac.CategoryId, GraffitiUsers.Current).Read)
{
filteredPermissions.Remove(ac);
}
}
foreach (PostCount ac in filteredPermissions)
{
PostCount existing = final.Find(
delegate(PostCount postcount)
{
return(postcount.PostStatus == ac.PostStatus);
});
if (existing == null)
{
final.Add(ac);
}
else
{
existing.Count += ac.Count;
}
}
return(final);
}
public static List <AuthorCount> GetAuthorCountForStatus(PostStatus status, string categoryID)
{
List <AuthorCount> autCounts = new List <AuthorCount>();
List <AuthorCount> final = new List <AuthorCount>();
QueryCommand cmd = new QueryCommand(
@"select u.Id, " + DataService.Provider.SqlCountFunction("u.Id") + @" as IdCount, u.ProperName, p.CategoryId from graffiti_Posts AS p
inner join graffiti_Users as u on p.CreatedBy = u.Name
where p.Status = " + DataService.Provider.SqlVariable("Status") + @" and p.IsDeleted = 0");
if (!String.IsNullOrEmpty(categoryID))
{
cmd.Sql += " and p.CategoryId = " + DataService.Provider.SqlVariable("CategoryId");
}
cmd.Sql += " group by u.Id, u.ProperName, p.CategoryId";
List <Parameter> parameters = Post.GenerateParameters();
cmd.Parameters.Add(Post.FindParameter(parameters, "Status")).Value = (int)status;
if (!String.IsNullOrEmpty(categoryID))
{
cmd.Parameters.Add(Post.FindParameter(parameters, "CategoryId")).Value = Convert.ToInt32(categoryID);
}
using (IDataReader reader = DataService.ExecuteReader(cmd))
{
while (reader.Read())
{
AuthorCount autCount = new AuthorCount();
autCount.ID = Int32.Parse(reader["Id"].ToString());
autCount.Count = Int32.Parse(reader["IdCount"].ToString());
autCount.Name = reader["ProperName"].ToString();
autCount.CategoryId = Int32.Parse(reader["CategoryId"].ToString());
autCounts.Add(autCount);
}
List <AuthorCount> filteredPermissions = new List <AuthorCount>();
filteredPermissions.AddRange(autCounts);
foreach (AuthorCount ac in autCounts)
{
if (!RolePermissionManager.GetPermissions(ac.CategoryId, GraffitiUsers.Current).Read)
{
filteredPermissions.Remove(ac);
}
}
foreach (AuthorCount ac in filteredPermissions)
{
AuthorCount existing = final.Find(
delegate(AuthorCount authcount)
{
return(authcount.Name == ac.Name);
});
if (existing == null)
{
final.Add(ac);
}
else
{
existing.Count += ac.Count;
}
}
reader.Close();
}
return(final);
}
