public List <Post> GetPosts(List <Guid> ids, bool withContent, bool withTags)
{
var postIds = ids.ConvertAll(id => id.ToString());
var select = Query("blogs_posts")
.Select("id", "title", "created_by", "created_when", "blog_id", "post_id")
.Where(Exp.In("id", postIds));
if (withContent)
{
select.Select("content");
}
var posts = Db.ExecuteList <Post>(select, r => RowMappers.ToPost(r, withContent));
if (posts.Count > 0)
{
if (withTags)
{
var tagQuery = Query("blogs_tags").Where(Exp.In("post_id", postIds)).Select("name", "post_id");
var tlist = Db.ExecuteList <Tag>(tagQuery, RowMappers.ToTag);
foreach (var post in posts)
{
post.TagList = tlist.FindAll(t => t.PostId == post.ID);
}
}
}
return(posts);
}
public IEnumerable <Post> GetPostsByDate(DateTime @from, DateTime to)
{
var select = Query("blogs_posts")
.Select("id", "title", "created_by", "created_when", "blog_id", "LastModified")
.Where(Exp.Between("created_when", from, to) | Exp.Between("LastModified", from, to));
return(Db.ExecuteList <Post>(select, (x) => { var post = RowMappers.ToPost(x);
post.Updated = x.Get <DateTime>("LastModified");
return post; }));
}
public List <Post> Select(Guid?id, long?blogId, Guid?userId, string tag, bool withContent, bool asc, int?from, int?count, bool fillTags, bool withCommentsCount)
{
var where = GetWhere(id, blogId, userId, tag);
var query = Query("blogs_posts")
.Select("id", "title", "created_by", "created_when", "blog_id", "post_id")
.Where(where);
if (withContent)
{
query.Select("content");
}
if (count.HasValue)
{
query.SetMaxResults(count.Value);
}
if (from.HasValue)
{
query.SetFirstResult(from.Value);
}
query.OrderBy("created_when", asc);
var posts = Db.ExecuteList <Post>(query, r => RowMappers.ToPost(r, withContent));
if (posts.Count > 0)
{
var pids = posts.ConvertAll(p => p.ID.ToString());
var postQ = Query("blogs_posts").Select("id").Where(where).OrderBy("created_when", asc);
if (count.HasValue)
{
postQ.SetMaxResults(count.Value);
}
if (from.HasValue)
{
postQ.SetFirstResult(from.Value);
}
var postsFilter = Exp.In("post_id", pids);
if (fillTags)
{
var tagQuery = Query("blogs_tags").Where(postsFilter).Select("name", "post_id");
var tlist = Db.ExecuteList <Tag>(tagQuery, RowMappers.ToTag);
foreach (var post in posts)
{
post.TagList = tlist.FindAll(t => t.PostId == post.ID);
}
}
}
return(posts);
}
public IEnumerable <Comment> GetCommentsByDate(DateTime @from, DateTime to, Guid forUser)
{
var query = new SqlQuery("blogs_comments t1")
.Select("t1.id", "t1.post_id", "t1.parent_id", "t1.content", "t1.created_by", "t1.created_when", "t1.inactive", "t2.reviewed_by")
.LeftOuterJoin("blogs_reviewposts t2", Exp.EqColumns("t1.post_id", "t2.post_id") & Exp.Sql("t1.created_when>t2.timestamp") & Exp.Eq("t2.reviewed_by", forUser.ToString()) & Exp.Eq("t2.Tenant", Tenant))
.Where(Exp.Between("t1.created_when", from, to))
.OrderBy("t1.created_when", true);
return(Db.ExecuteList(query, (x) =>
{
var comment = RowMappers.ToComment(x);
comment.IsReaded = x.Get <string>("reviewed_by") == forUser.ToString();
return comment;
}));
}