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 CommentsByDateSingle(DateTime date)
{
// 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", date, pPublished.DbType);
cmd.Parameters.Add("MaxDate", date.AddDays(1), pPublished.DbType);
return GetPostDictionary(cmd, 10);
}
public static void RemoveLogsOlderThan(int hours)
{
DateTime dt = DateTime.Now.AddHours(-1*hours);
QueryCommand command = new QueryCommand("DELETE FROM graffiti_Logs WHERE CreatedOn <= " + DataService.Provider.SqlVariable("CreatedOn"));
command.Parameters.Add(Log.FindParameter("CreatedOn")).Value = dt;
int i = DataService.ExecuteNonQuery(command);
//if(i > 0)
// Info("Log", "{0} item(s) were just removed from the logs",i);
}
public override int Insert(Table table, List<Parameter> parameters)
{
if (table.IsReadOnly)
throw new Exception("Readonly tables (views) cannot recieve inserts");
StringBuilder sb = new StringBuilder();
QueryCommand command = new QueryCommand();
sb.AppendFormat("INSERT INTO {0} (", QuoteName(table.TableName));
bool isFirst = true;
foreach (Column column in table.Columns)
{
if (column.Name != table.PrimaryKey)
{
sb.AppendFormat(" {0} {1}"
, isFirst ? string.Empty : ", "
, QuoteName(column.Name)
);
isFirst = false;
}
}
sb.Append(") VALUES (");
isFirst = true;
foreach (Column column in table.Columns)
{
if (column.Name != table.PrimaryKey)
{
sb.AppendFormat(" {0} {1}", isFirst ? string.Empty : ", ", SqlVariable(column.Name));
command.Parameters.Add( parameters.Find( delegate(Parameter p) { return (p.Name == column.Name); } ) );
isFirst = false;
}
}
sb.Append(")");
command.Sql = sb.ToString();
using(DbConnection conn = GetConnection())
{
conn.Open();
DbCommand dbCommand = GetCommand(command, conn);
dbCommand.ExecuteNonQuery();
DbCommand c2 = GetFactory().CreateCommand();
c2.CommandText = GetSelectNextId(table.TableName,table.PrimaryKey);
c2.Connection = conn;
object obj = c2.ExecuteScalar();
conn.Close();
return Int32.Parse(obj.ToString());
}
}
public static int GetNextVersionId(int postid, int currentVersion)
{
if (postid <= 0)
return 1;
else
{
QueryCommand command = new QueryCommand("Select Max(v.Version) FROM graffiti_VersionStore as v where v.Name = " + DataService.Provider.SqlVariable("Name"));
command.Parameters.Add(VersionStore.FindParameter("Name")).Value = "Post:" + postid.ToString();
object obj = DataService.ExecuteScalar(command);
if (obj == null || obj is System.DBNull)
return 2;
else
return Math.Max(((int)obj), currentVersion) + 1;
}
}
public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
{
string result = isFirst ? "" : (_useOr ? " OR " : " AND ");
result +=
DataService.Provider.QuoteName( tbl.TableName )
+ "." + DataService.Provider.QuoteName( _column.Name )
+ " "
+ DataService.GetComparisonOperator(_comp)
+ " "
+ DataService.Provider.SqlVariable(_column.Name + "_" + letter);
cmd.Parameters.Add(_column.Name + "_" + letter, _value, _column.DbType);
return result;
}
public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
{
StringBuilder sb = new StringBuilder();
sb.Append(isFirst ? "" : (_useOr ? " OR " : " AND "))
.Append(DataService.Provider.QuoteName(tbl.TableName))
.Append(".")
.Append(DataService.Provider.QuoteName(_column.Name))
.Append(" IN (");
if (int_Ids != null && int_Ids.Length > 0)
{
sb.Append(int_Ids[0]);
for (int i = 1; i < int_Ids.Length; i++)
{
sb.Append(", ")
.Append(int_Ids[i]);
}
}
else if (string_Keys != null && string_Keys.Length > 0)
{
sb.Append(DataService.Provider.SqlVariable(_column.Name + "_" + letter + "_0"));
cmd.Parameters.Add(_column.Name + "_" + letter + "_0", string_Keys[0], _column.DbType);
for (int i = 1; i < string_Keys.Length; i++)
{
sb.Append(", ")
.Append(DataService.Provider.SqlVariable(_column.Name + "_" + letter + "_" + i));
cmd.Parameters.Add(_column.Name + "_" + letter + "_" + i, string_Keys[i], _column.DbType);
}
}
sb.Append(")");
return(sb.ToString());
}
public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
{
StringBuilder sb = new StringBuilder();
sb.Append(isFirst ? "" : (_useOr ? " OR " : " AND "))
.Append( DataService.Provider.QuoteName( tbl.TableName ) )
.Append( "." )
.Append( DataService.Provider.QuoteName( _column.Name ) )
.Append( " IN (" );
if(int_Ids != null && int_Ids.Length > 0)
{
sb.Append( int_Ids[0] );
for(int i = 1; i < int_Ids.Length; i++)
{
sb.Append( ", " )
.Append( int_Ids[i] );
}
}
else if(string_Keys != null && string_Keys.Length > 0)
{
sb.Append( DataService.Provider.SqlVariable( _column.Name + "_" + letter + "_0" ) );
cmd.Parameters.Add( _column.Name + "_" + letter + "_0", string_Keys[0], _column.DbType );
for(int i = 1; i<string_Keys.Length; i++)
{
sb.Append( ", " )
.Append( DataService.Provider.SqlVariable( _column.Name + "_" + letter + "_" + i ) );
cmd.Parameters.Add( _column.Name + "_" + letter + "_" + i, string_Keys[i], _column.DbType );
}
}
sb.Append(")");
return sb.ToString();
}
public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
{
if (_wheres.Count > 1)
{
string result = isFirst ? "" : (_useOr ? " OR " : " AND ");
result += "(";
int position = 1;
foreach (WHERE where in _wheres)
{
//result += (position == 1) ? " " : (_orChildren ? " OR " : " AND ");
result += where.ToSQL(cmd, tbl, letter + "_" + position, position == 1);
position++;
}
result += ")";
return result;
}
else if (_wheres.Count == 1)
return _wheres[0].ToSQL(cmd, tbl, letter, false);
return string.Empty;
}
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;
}
public static ReportData MostPopularPosts()
{
// top 5
DataProvider dp = DataService.Provider;
QueryCommand cmd = new QueryCommand(@"
SELECT Title, Id, IdCount FROM ( SELECT
p.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 p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
group by
p.Title, p.Id) as dv
order by
IdCount desc
");
return GetPostDictionary(cmd, 5);
}
public abstract string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst);
public abstract string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst);
private static ReportData GetPostDictionary(QueryCommand command)
{
return GetPostDictionary(command, int.MaxValue);
}
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 int ExecuteNonQuery(QueryCommand cmd)
{
return(_dp.ExecuteNonQuery(cmd));
}
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 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 IDataReader ExecuteReader(QueryCommand cmd)
{
return(_dp.ExecuteReader(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 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 List<IGraffitiUser> GetUsers(string role)
//{
// throw new NotImplementedException();
//}
public string[] GetUsersInRole(string roleName)
{
QueryCommand command = new QueryCommand("SELECT u.Name FROM graffiti_Users AS u INNER JOIN graffiti_UserRoles AS ur on u.Id = ur.UserId WHERE ur.RoleName = " + DataService.Provider.SqlVariable("RoleName"));
command.Parameters.Add(UserRole.FindParameter("RoleName")).Value = roleName;
List<string> userNames = new List<string>();
using(IDataReader reader = DataService.ExecuteReader(command))
{
while(reader.Read())
{
userNames.Add(reader["Name"] as string);
}
reader.Close();
}
return userNames.ToArray();
}
public static object ExecuteScalar(QueryCommand cmd)
{
return(_dp.ExecuteScalar(cmd));
}
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")
);
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 GetDateDictionary(cmd);
}
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;
}
private static IDictionary<DateTime, int> GetDateDictionary(QueryCommand command)
{
Dictionary<DateTime, int> dates = new Dictionary<DateTime, int>();
using (IDataReader reader = DataService.ExecuteReader(command))
{
while (reader.Read())
dates.Add(
new DateTime(Int32.Parse(reader["dvYear"].ToString()), Int32.Parse(reader["dvMonth"].ToString()),
Int32.Parse(reader["dvDay"].ToString())),
Int32.Parse( reader["IdCount"].ToString()) );
reader.Close();
}
return dates;
}
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;
}
private static ReportData GetPostDictionary(QueryCommand command, int top)
{
ReportData data = new ReportData();
if ( top < 1 ) top = int.MaxValue;
int counter = 0;
using (IDataReader reader = DataService.ExecuteReader(command))
{
while (reader.Read())
{
data.Counts.Add(Int32.Parse(reader["Id"].ToString()), Int32.Parse(reader["IdCount"].ToString()));
data.Titles.Add(Int32.Parse(reader["Id"].ToString()), reader["Title"] as string);
/* if for some reason the database return more than Int32.MaxValue this would still exit. But, the IDictionaries wouldn't */
counter++;
if( counter == top ) break;
}
reader.Close();
}
return data;
}
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);
}
private static void DeleteByColumn(Column column, bool state)
{
List<string> idsToDelete = new List<string>();
List<int> postIdsChanged = new List<int>();
Query q = CreateQuery();
q.AndWhere(column,state);
q.AndWhere(Columns.Published, DateTime.Now.AddDays(-1 * Int32.Parse(ConfigurationManager.AppSettings["Graffiti::Comments::DaysToDelete"] ?? "7")),Comparison.LessOrEquals);
q.Top = "25";
q.OrderByAsc(Columns.Published);
CommentCollection cc = CommentCollection.FetchByQuery(q);
foreach(Comment c in cc)
{
idsToDelete.Add(c.Id.ToString());
if (!postIdsChanged.Contains(c.PostId))
postIdsChanged.Add(c.PostId);
}
if(idsToDelete.Count > 0)
{
QueryCommand deleteCommand =
new QueryCommand("DELETE FROM graffiti_Comments where Id in (" + string.Join(",", idsToDelete.ToArray()) + ")");
DataService.ExecuteNonQuery(deleteCommand);
foreach(int pid in postIdsChanged)
Core.Post.UpdateCommentCount(pid);
Log.Info("Deleted Comments", idsToDelete.Count + " comment(s) were removed from the database since they were older than " + (ConfigurationManager.AppSettings["Graffiti::Comments::DaysToDelete"] ?? "7") + " days and marked as " + ((column.Name == "IsDeleted") ? " deleted" : " unpublished") );
}
}
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"
);
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 (int)DataService.ExecuteScalar(cmd);
}
/// <summary>
/// Returns the number of records matching the current query.
/// </summary>
/// <returns></returns>
public int GetRecordCount()
{
QueryCommand command = new QueryCommand("");
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ")
.Append( DataService.Provider.SqlCountFunction() )
.Append(" as RecordCount FROM ")
.Append( DataService.Provider.QuoteName( _Table.TableName ) );
if (_wheres.Count > 0)
{
sb.Append(" WHERE ");
int position = 1;
foreach (WHERE where in _wheres)
{
sb.Append(where.ToSQL(command, _Table, GetNextLetter(position), position == 1));
position++;
}
}
command.Sql = sb.ToString();
object obj = DataService.ExecuteScalar(command);
int count = 0;
if (obj == null || obj is DBNull)
return count;
if ( obj is Int32 )
count = (int) obj;
else if ( obj is Int64 )
// this may cause errors in the future...if we begin to support Int64 keys
count = (long) obj > Convert.ToInt64(Int32.MaxValue) ? Int32.MaxValue : Convert.ToInt32( (long) obj );
else if ( obj is Int16 )
count = Convert.ToInt32( (short) obj );
return count;
}
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);
}
protected virtual DbCommand GetCommand(QueryCommand qryCommand, DbConnection conn)
{
return(GetCommand(DbTrace.GetMarker(), qryCommand, conn));
}
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;
}
/// <summary>
/// Returns an IDataReader based on the query results.
/// </summary>
/// <returns></returns>
public IDataReader ExecuteReader()
{
QueryCommand command = DataService.CreateQueryCommandFromQuery(this);
return(DataService.ExecuteReader(command));
}
public virtual QueryCommand CreateQueryCommandFromQuery(Query q)
{
StringBuilder columnList = new StringBuilder();
QueryCommand command = new QueryCommand("");
bool isFirst = true;
foreach (Column column in q.Table.Columns)
{
columnList.AppendFormat("{1} {0}", QuoteName(column.Name), isFirst ? string.Empty : ",");
isFirst = false;
}
string whereStatement = null;
if (q.Wheres.Count > 0)
{
StringBuilder _whereStatement = new StringBuilder();
int position = 1;
foreach (WHERE where in q.Wheres)
{
_whereStatement.Append(where.ToSQL(command, q.Table, GetNextLetter(position), position == 1));
position++;
}
whereStatement = _whereStatement.ToString();
}
string orderStatement = null;
if (q.Orders.Count > 0)
{
isFirst = true;
foreach (string order in q.Orders)
{
if (isFirst)
{
orderStatement = " Order By ";
}
orderStatement += (isFirst ? " " : " , ") + order;
isFirst = false;
}
}
if (q.PageIndex == 0 || q.PageSize == 0)
{
command.Sql = SelectSql(columnList.ToString(), q.Table.TableName, whereStatement, orderStatement, q.Top);
}
else if (q.PageIndex == 1)
{
command.Sql = SelectSql(columnList.ToString(), q.Table.TableName, whereStatement, orderStatement,
q.PageSize.ToString());
}
else
{
command.Sql =
SelectPagedSql(columnList.ToString(), q.Table.TableName, whereStatement, orderStatement,
q.Table.PrimaryKey, q.PageIndex, q.PageSize);
}
return(command);
}