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