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