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