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