public static IDictionary <DateTime, int> ViewsByDateReport(DateTime min, DateTime max) { DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" select " + dp.SqlYearFunction("ps.DateViewed") + " as dvYear, " + dp.SqlMonthFunction("ps.DateViewed") + " as dvMonth, " + dp.SqlDayFunction("ps.DateViewed") + " as dvDay, " + dp.SqlCountFunction("ps.Id") + @" as IdCount from graffiti_Post_Statistics AS ps left outer join graffiti_Posts AS p on p.Id = ps.PostId where ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @" and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @" group by " + dp.SqlYearFunction("ps.DateViewed") + ", " + dp.SqlMonthFunction("ps.DateViewed") + ", " + dp.SqlDayFunction("ps.DateViewed") ); Parameter pDateViewed = PostStatistic.FindParameter("DateViewed"); cmd.Parameters.Add("MinDate", min, pDateViewed.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType); return(GetDateDictionary(cmd)); }
public static ReportData GetViewsByPost(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("p.Id") + @" as IdCount from graffiti_Post_Statistics AS ps left outer join graffiti_Posts AS p on p.Id = ps.PostId where ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @" and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @" group by p.Id) as dv order by IdCount desc "); Parameter pDateViewed = PostStatistic.FindParameter("DateViewed"); cmd.Parameters.Add("MinDate", min, pDateViewed.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType); return(GetPostDictionary(cmd, 10)); }
public static IDictionary <DateTime, int> ViewsByPostSingle(int postId, DateTime min, DateTime max) { DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" select " + dp.SqlYearFunction("ps.DateViewed") + " as dvYear, " + dp.SqlMonthFunction("ps.DateViewed") + " as dvMonth, " + dp.SqlDayFunction("ps.DateViewed") + " as dvDay, " + dp.SqlCountFunction("ps.Id") + @" as IdCount from graffiti_Post_Statistics AS ps where ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @" and ps.PostId = " + dp.SqlVariable("PostId") + @" group by " + dp.SqlYearFunction("ps.DateViewed") + ", " + dp.SqlMonthFunction("ps.DateViewed") + ", " + dp.SqlDayFunction("ps.DateViewed") ); var parameters = PostStatistic.GenerateParameters(); Parameter pDateViewed = PostStatistic.FindParameter(parameters, "DateViewed"); cmd.Parameters.Add("MinDate", min, pDateViewed.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType); cmd.Parameters.Add(PostStatistic.FindParameter(parameters, "PostId")).Value = postId; return(GetDateDictionary(cmd)); }
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 int ViewsByPostSingleCount(int postId, DateTime min, DateTime max) { DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" select " + dp.SqlCountFunction("ps.Id") + @" as IdCount from graffiti_Post_Statistics AS ps where ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @" and ps.PostId = " + dp.SqlVariable("PostId") ); List <Parameter> parameters = PostStatistic.GenerateParameters(); Parameter pDateViewed = PostStatistic.FindParameter(parameters, "DateViewed"); cmd.Parameters.Add("MinDate", min, pDateViewed.DbType); cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType); cmd.Parameters.Add(PostStatistic.FindParameter(parameters, "PostId")).Value = postId; return((int)DataService.ExecuteScalar(cmd)); }