public static int GetPublishedCommentCount(string user)
{
int count = 0;
QueryCommand cmd;
cmd = new QueryCommand(
@"select " + DataService.Provider.SqlCountFunction("a.PostId") + @" from graffiti_Comments a
inner join graffiti_Posts b on a.PostId = b.Id
where b.IsPublished <> 0 and b.IsDeleted = 0
and a.IsPublished <> 0 and a.IsDeleted = 0 and b.Status = 1");
if (!String.IsNullOrEmpty(user))
{
cmd.Sql += " and b.CreatedBy = " + DataService.Provider.SqlVariable("CreatedBy");
cmd.Parameters.Add(Post.FindParameter("CreatedBy")).Value = user;
}
try
{
count = Convert.ToInt32(DataService.ExecuteScalar(cmd));
}
catch (Exception) { }
return(count);
}
public static PostCollection FetchPostsByTagAndCategory(string tagName, int categoryId)
{
QueryCommand command = new QueryCommand("SELECT p.* FROM graffiti_Posts AS p INNER JOIN graffiti_Tags AS t ON p.Id = t.PostId WHERE p.CategoryId = " + categoryId.ToString() + " and p.IsPublished <> 0 and p.IsDeleted = 0 and p.Published <= " + DataService.Provider.SqlVariable("Published") + " and t.Name = " + DataService.Provider.SqlVariable("Name") + " ORDER BY p.Published DESC");
command.Parameters.Add(Post.FindParameter("Published")).Value = SiteSettings.CurrentUserTime;
command.Parameters.Add(Tag.FindParameter("Name")).Value = tagName;
PostCollection pc = new PostCollection();
pc.LoadAndCloseReader(DataService.ExecuteReader(command));
return(pc);
}
public static void UpdateViewCount(int postid)
{
QueryCommand command = new QueryCommand("UPDATE graffiti_Posts Set Views = Views + 1 WHERE Id = " + DataService.Provider.SqlVariable("Id"));
command.Parameters.Add(Post.FindParameter("Id")).Value = postid;
DataService.ExecuteNonQuery(command);
PostStatistic ps = new PostStatistic();
ps.PostId = postid;
ps.DateViewed = DateTime.Now;
ps.Save();
}
public static void UpdatePostStatus(int id, PostStatus status)
{
//UpdateVersionCount(id);
QueryCommand command = new QueryCommand("Update graffiti_Posts Set Status = " + DataService.Provider.SqlVariable("Status") + " Where Id = " + DataService.Provider.SqlVariable("Id"));
List <Parameter> parameters = Post.GenerateParameters();
command.Parameters.Add(Post.FindParameter(parameters, "Status")).Value = (int)status;
command.Parameters.Add(Post.FindParameter(parameters, "Id")).Value = id;
DataService.ExecuteNonQuery(command);
ZCache.RemoveByPattern("Posts-");
ZCache.RemoveCache("Post-" + id);
}
public static void UpdateCommentCount(int postid)
{
QueryCommand command = null;
DataProvider dp = DataService.Provider;
if (Util.IsAccess)
{
Query q1 = Comment.CreateQuery();
q1.AndWhere(Comment.Columns.PostId, postid);
q1.AndWhere(Comment.Columns.IsPublished, true);
q1.AndWhere(Comment.Columns.IsDeleted, false);
int Comment_Count = q1.GetRecordCount();
Query q2 = Comment.CreateQuery();
q2.AndWhere(Comment.Columns.PostId, postid);
q2.AndWhere(Comment.Columns.IsPublished, false);
q2.AndWhere(Comment.Columns.IsDeleted, false);
int Pending_Comment_Count = q2.GetRecordCount();
command = new QueryCommand("UPDATE graffiti_Posts Set Comment_Count = "
+ dp.SqlVariable("Comment_Count")
+ ", Pending_Comment_Count = " + dp.SqlVariable("Pending_Comment_Count")
+ " WHERE Id = " + dp.SqlVariable("Id"));
List <Parameter> parameters = Post.GenerateParameters();
command.Parameters.Add(Post.FindParameter(parameters, "Comment_Count")).Value = Comment_Count;
command.Parameters.Add(Post.FindParameter(parameters, "Pending_Comment_Count")).Value = Pending_Comment_Count;
command.Parameters.Add(Post.FindParameter(parameters, "Id")).Value = postid;
}
else
{
string sql =
@"Update graffiti_Posts
Set
Comment_Count = (Select " + dp.SqlCountFunction() + @" FROM graffiti_Comments AS c where c.PostId = " + dp.SqlVariable("Id") + @" and c.IsPublished = 1 and c.IsDeleted = 0),
Pending_Comment_Count = (Select " + dp.SqlCountFunction() + @" FROM graffiti_Comments AS c where c.PostId = " + dp.SqlVariable("Id") + @" and c.IsPublished = 0 and c.IsDeleted = 0)
Where Id = " + dp.SqlVariable("Id");
command = new QueryCommand(sql);
command.Parameters.Add(Post.FindParameter("Id")).Value = postid;
}
DataService.ExecuteNonQuery(command);
}
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);
}
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 int GetPostIdByName(string name)
{
object id = ZCache.Get <object>("PostIdByName-" + name);
if (id == null)
{
string postName;
string categoryName = null;
if (name.Contains("/"))
{
string[] parts = name.Split('/');
for (int i = 0; i < parts.Length; i++)
{
parts[i] = Util.CleanForUrl(parts[i]);
}
switch (parts.Length)
{
case 2:
categoryName = parts[0];
postName = parts[1];
break;
case 3:
categoryName = parts[0] + "/" + parts[1];
postName = parts[2];
break;
default:
return(-1);
}
}
else
{
postName = Util.CleanForUrl(name);
}
int categoryId = -1;
if (categoryName != null)
{
CategoryCollection the_categories = categories.GetCachedCategories();
foreach (Category category in the_categories)
{
if (category.LinkName == categoryName)
{
categoryId = category.Id;
}
}
if (categoryId == -1)
{
return(-1);
}
}
List <Parameter> parameters = Post.GenerateParameters();
/* this is supposed to be TOP 1, but the ExecuteScalar will pull only the first one */
QueryCommand cmd = new QueryCommand("Select Id FROM graffiti_Posts Where Name = " + DataService.Provider.SqlVariable("Name") + " and IsDeleted = 0");
cmd.Parameters.Add(Post.FindParameter(parameters, "Name")).Value = postName;
if (categoryId > -1)
{
cmd.Sql += " and CategoryId = " + DataService.Provider.SqlVariable("CategoryId");
cmd.Parameters.Add(Post.FindParameter(parameters, "CategoryId")).Value = categoryId;
}
cmd.Sql += " order by CategoryId asc";
object postobj = DataService.ExecuteScalar(cmd);
if (postobj != null)
{
id = postobj;
ZCache.InsertCache("PostIdByName-" + name, (int)id, 60);
}
else
{
id = -1;
}
}
return((int)id);
}
