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