public static IDictionary <DateTime, int> CommentsByDate(DateTime min, DateTime max) { DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" select " + dp.SqlYearFunction("c.Published") + " as dvYear, " + dp.SqlMonthFunction("c.Published") + " as dvMonth, " + dp.SqlDayFunction("c.Published") + " as dvDay, " + dp.SqlCountFunction("c.Id") + @" as IdCount from graffiti_Comments AS c left outer join graffiti_Posts AS p on p.Id = c.PostId where c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @" and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @" and c.IsDeleted = 0 group by " + dp.SqlYearFunction("c.Published") + ", " + dp.SqlMonthFunction("c.Published") + ", " + dp.SqlDayFunction("c.Published") ); Parameter pPublished = Comment.FindParameter("Published"); cmd.Parameters.Add("MinDate", min, pPublished.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType); return(GetDateDictionary(cmd)); }
public static ReportData CommentsByPost(DateTime min, DateTime max) { // top 10 DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" SELECT Title, Id, IdCount from ( SELECT max(p.Title) as Title, p.Id, " + dp.SqlCountFunction("c.Id") + @" as IdCount from graffiti_Comments AS c left outer join graffiti_Posts AS p on p.Id = c.PostId where c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @" and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @" and c.IsDeleted = 0 group by p.Id) as dv order by IdCount desc "); Parameter pPublished = Comment.FindParameter("Published"); cmd.Parameters.Add("MinDate", min, pPublished.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType); return(GetPostDictionary(cmd, 10)); }
public static IDictionary <DateTime, int> CommentsByPostSingle(int postId, DateTime min, DateTime max) { DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" select " + dp.SqlYearFunction("c.Published") + " as dvYear, " + dp.SqlMonthFunction("c.Published") + " as dvMonth, " + dp.SqlDayFunction("c.Published") + " as dvDay, " + dp.SqlCountFunction("c.Id") + @" as IdCount from graffiti_Comments AS c where c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @" and c.PostId = " + dp.SqlVariable("PostId") + @" and c.IsDeleted = 0 group by " + dp.SqlYearFunction("c.Published") + ", " + dp.SqlMonthFunction("c.Published") + ", " + dp.SqlDayFunction("c.Published") ); List <Parameter> parameters = Comment.GenerateParameters(); Parameter pPublished = Comment.FindParameter(parameters, "Published"); cmd.Parameters.Add("MinDate", min, pPublished.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType); cmd.Parameters.Add(Comment.FindParameter(parameters, "PostId")).Value = postId; return(GetDateDictionary(cmd)); }
public static int CommentsByPostSingleCount(int postId, DateTime min, DateTime max) { DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" select " + dp.SqlCountFunction("c.Id") + @" as IdCount from graffiti_Comments c inner join graffiti_Posts p on p.Id = c.PostId where c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @" and c.PostId = " + dp.SqlVariable("PostId") + @" and c.IsDeleted = 0" ); var parameters = Comment.GenerateParameters(); Parameter pPublished = Comment.FindParameter(parameters, "Published"); cmd.Parameters.Add("MinDate", min, pPublished.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType); cmd.Parameters.Add(Comment.FindParameter(parameters, "PostId")).Value = postId; return((int)DataService.ExecuteScalar(cmd)); }