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