public PostCollection UpcomingEvents(string tag, int count)
{
CategoryController cc = new CategoryController();
Category eventCategory = cc.GetCachedCategory("Events", true);
DataBuddy.Table table = new DataBuddy.Table("graffiti_posts", "PostCollection");
Query query = new Query(table);
query.Top = "100 PERCENT *";
Column categoryColumn = new Column("CategoryId", DbType.Int32, typeof(Int32), "CategoryId", false, false);
query.AndWhere(categoryColumn, eventCategory.Id, Comparison.Equals);
PostCollection posts = PostCollection.FetchByQuery(query);
List <Post> eventPosts = posts
.Where(p => p.IsInFuture())
.Where(p => String.IsNullOrEmpty(tag) || p.TagList.Contains(tag)).ToList();
if (String.IsNullOrEmpty(tag))
{
eventPosts.AddRange(Utility.LoadFeedEvents(DateTime.Today, DateTime.MaxValue).ToPosts());
}
eventPosts = eventPosts.OrderBy(p => p.GetEffectiveDate()).ToList();
count = count > eventPosts.Count || count == -1 ? eventPosts.Count : count;
eventPosts = eventPosts.GetRange(0, count);
PostCollection ret = new PostCollection();
ret.AddRange(eventPosts);
return(ret);
}
public PostCollection GetNewsItems(int count)
{
CategoryController cc = new CategoryController();
// TODO: Need to get category name from plugin
Category newsCategory = cc.GetCachedCategory("News", true);
DataBuddy.Table table = new DataBuddy.Table("graffiti_posts", "PostCollection");
Query query = new Query(table);
query.Top = "100 PERCENT *";
Column categoryColumn = new Column("CategoryId", DbType.Int32, typeof(Int32), "CategoryId", false, false);
query.AndWhere(categoryColumn, newsCategory.Id, Comparison.Equals);
PostCollection posts = PostCollection.FetchByQuery(query);
List <Post> newsPosts = posts.Where(p => p.GetStartDate() <= DateTime.Today)
.Where(p => p.GetEndDate() >= DateTime.Today)
.Where(p => !p.IsDeleted)
.OrderByDescending(p => p.GetPriority())
.ThenByDescending(p => p.Published).ToList();
count = count > newsPosts.Count ? newsPosts.Count : count;
newsPosts = newsPosts.GetRange(0, count);
PostCollection ret = new PostCollection();
ret.AddRange(newsPosts);
return(ret);
}
private static List <Post> GetPosts(int year, int month)
{
CategoryController cc = new CategoryController();
Category eventCategory = cc.GetCachedCategory("Events", true);
DataBuddy.Table table = new DataBuddy.Table("graffiti_posts", "PostCollection");
Query query = new Query(table);
query.Top = "100 PERCENT *";
Column categoryColumn = new Column("CategoryId", DbType.Int32, typeof(Int32), "CategoryId", false, false);
query.AndWhere(categoryColumn, eventCategory.Id, Comparison.Equals);
PostCollection posts = PostCollection.FetchByQuery(query);
DateTime firstOfMonth = new DateTime(year, month, 1);
DateTime lastOfMonth = firstOfMonth.AddMonths(1).AddDays(-1);
List <Post> postList = posts.FindAll(delegate(Post post)
{
return(!post.IsDeleted && post.IsInRange(firstOfMonth, lastOfMonth));
});
postList.AddRange(Utility.LoadFeedEvents(firstOfMonth, lastOfMonth).ToPosts());
return(postList);
}
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 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)
{
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;
}
/// <summary>
/// Queries are built off of a single table.
/// </summary>
/// <param name="tbl"></param>
public Query(Table tbl)
{
_Table = tbl;
}
public abstract string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst);
