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