public int GetUnreadCommentsCount(int userId)
{
using (var db = new BlogContext())
{
return GetUnreadCommenstQuery(db, userId).Count();
}
}
public List<Comment> GetUnreadComments(int userId)
{
using (var db = new BlogContext())
{
return GetUnreadCommenstQuery(db,userId).ToList();
}
}
public void AddArticle(Article article)
{
using (var db = new BlogContext())
{
db.Articles.Add(article);
db.SaveChanges();
}
}
public List<Role> GetRoles()
{
using (var db = new BlogContext())
{
var roles = (from p in db.Roles select p);
return roles.ToList();
}
}
public Role GetRoleByType(RoleType type)
{
using (var db = new BlogContext())
{
return (from p in db.Roles
where p.Type == type
select p).FirstOrDefault();
}
}
public void MarkAsRead(int commentId)
{
using (var db = new BlogContext())
{
var comment = db.Comments.Find(commentId);
comment.Read = true;
db.SaveChanges();
}
}
public void AddComment(Comment comment)
{
using (var db = new BlogContext())
{
comment.CreationDate = DateTime.UtcNow;
db.Comments.Add(comment);
db.SaveChanges();
}
}
private IQueryable<Comment> GetUnreadCommenstQuery(BlogContext db,int userId)
{
return
from commnet in db.Comments.Include(c => c.Article).Include(x => x.Author)
where commnet.Article.AuthorId == userId
where commnet.AuthorId != userId
where !commnet.ParentId.HasValue
where !commnet.Read
select commnet;
}
public List<Article> GetArticles(int userId)
{
using (var db = new BlogContext())
{
var articles = (from a in db.Articles.Include(a => a.Author)
where a.AuthorId == userId
select a).ToList();
return articles;
}
}
public List<Comment> GetArticleComments(int articleId)
{
using (var db = new BlogContext())
{
var query = (from c in db.Comments.Include(x => x.Author)
where (c.ArticleId == articleId && c.Parent == null)
select c).ToList();
return query;
}
}
public void AddUser(User user)
{
using (var db = new BlogContext())
{
foreach (Role r in user.Roles)
{
db.Roles.Attach(r);
}
db.Users.Add(user);
db.SaveChanges();
}
}
public Article GetArticleById(int id)
{
using (var db = new BlogContext())
{
var query = (from a in db.Articles.Include(a => a.Author)
where a.Id == id
select a)
.OrderBy(a => a.ModificationdDate)
.ThenBy(a => a.CreationDate)
.FirstOrDefault();
return query;
}
}
public Comment RetriveComments(Comment comment)
{
using (var db = new BlogContext())
{
List<Comment> comments = new List<Comment>();
var dbComment = db.Comments.Include(x => x.Author).Include(c => c.Comments).Where(c => c.Id == comment.Id).FirstOrDefault();
foreach (var item in dbComment.Comments)
{
comments.Add(RetriveComments(item));
}
dbComment.Comments = comments;
return dbComment;
}
}
public int CountAdmin()
{
using (var db = new BlogContext())
{
var query = (from u in db.Users.Include(r => r.Roles)
where u.IsActive == true
select u).ToList();
int count = 0;
foreach (var u in query)
{
foreach (var r in u.Roles)
{
if (r.Type.Equals(RoleType.Administrator))
count++;
}
}
return count;
}
}
public List<int> GetArticlesPerMonth(int year)
{
using (var db = new BlogContext())
{
var allMonths = Enumerable.Range(1, 12);
var query = db.Articles.Where(a => a.CreationDate.Year == year)
.GroupBy((o => new
{
Month = o.CreationDate.Month,
Year = o.CreationDate.Year
}))
.Select(g => new
{
Month = g.Key.Month,
Year = g.Key.Year,
Total = g.Count()
})
.OrderByDescending(a => a.Year)
.ThenByDescending(a => a.Month)
.ToList();
var value = (from months in allMonths
join date in query on months equals date.Month into d
from date in d.DefaultIfEmpty()
select new
{
Date = months,
Count = (date == null) ? 0 : date.Total
})
.OrderBy(a => a.Date)
.ToList().Select(p => p.Count).ToList();
return value;
}
}
public List<Tuple<User, int>> GetMostActiveUsers(DateTime fromDate, DateTime toDate)
{
using (var db = new BlogContext())
{
var lstArticles = (from u in db.Users.Include("Articles").Include("Comments")
select u).ToList<User>();
List<Tuple<User, int>> lstMostActive = new List<Tuple<User, int>>();
foreach (User u in lstArticles)
{
int counter = 0;
foreach (var article in u.Articles)
{
if (article.CreationDate >= fromDate && article.CreationDate <= toDate)
counter++;
}
foreach (var comment in u.Comments)
{
if (comment.CreationDate >= fromDate && comment.CreationDate <= toDate)
counter++;
}
if (counter > 0)
{
lstMostActive.Add(new Tuple<User, int>(u, counter));
}
}
return lstMostActive;
}
}
public List<Article> SearchArticles(string searchText)
{
using (var db = new BlogContext())
{
var query = (from a in db.Articles.Include(x=>x.Author)
where (a.Name.Contains(searchText) || a.Text.Contains(searchText)) && a.Type == ArticleType.Public
select a).ToList();
return query;
}
}
public void ModifyArticle(Article article)
{
using (var db = new BlogContext())
{
var query = (from a in db.Articles
where a.Id == article.Id
select a).First();
query.Layout = article.Layout;
query.ModificationdDate = DateTime.Now;
query.Name = article.Name;
query.PicturePath = article.PicturePath;
query.Text = article.Text;
query.Type = article.Type;
db.SaveChanges();
}
}
public List<Article> GetPublicArticles(int id)
{
using (var db = new BlogContext())
{
var query = (from a in db.Articles.Include(x => x.Author)
where a.AuthorId == id && a.Type == ArticleType.Public
select a).OrderBy(y=>y.ModificationdDate).ToList();
return query;
}
}
public User GetUserByUsername(string username)
{
using (var db = new BlogContext())
{
var query = from u in db.Users.Include(u => u.Roles)
where u.IsActive == true && u.Username.Trim() == username.Trim()
select u;
return query.FirstOrDefault();
}
}
public User GetUserById(int userId)
{
using (var db = new BlogContext())
{
var query = from u in db.Users.Include(u => u.Roles)
where u.IsActive == true && u.Id == userId
select u;
return query.FirstOrDefault();
}
}
public void RemoveUser(User user)
{
using (var db = new BlogContext())
{
var query = (from u in db.Users
where u.IsActive == true && u.Id == user.Id
select u).FirstOrDefault<User>();
query.IsActive = false;
db.SaveChanges();
}
}
public void ModifyUser(User user)
{
using (var db = new BlogContext())
{
var query = (from u in db.Users.Include(r=>r.Roles)
where u.IsActive == true && u.Id == user.Id
select u).FirstOrDefault<User>();
var types = user.Roles.Select(r => r.Type);
var newRoles = db.Roles
.Where(r => types.Contains(r.Type)).ToList();
query.Email = user.Email;
query.Name = user.Name;
query.Surname = user.Surname;
query.Password = user.Password;
query.PicturePath = user.PicturePath;
query.Roles.Clear();
query.Roles.AddRange(newRoles);
db.SaveChanges();
}
}
public List<User> GetUsers()
{
using (var db = new BlogContext())
{
var query = from u in db.Users.Include(u => u.Roles)
where u.IsActive == true
select u;
return query.ToList<User>();
}
}
public List<Article> GetLatest(int count)
{
using (var db = new BlogContext())
{
var articles = (from a in db.Articles.Include(a => a.Author)
where a.Type == ArticleType.Public
select a)
.OrderBy(a => a.ModificationdDate)
.ThenBy(a => a.CreationDate)
.Take(count);
return articles.ToList();
}
}
public void UpdateUserComments(int userId, Comment comment)
{
using (var db = new BlogContext())
{
db.Comments.Attach(comment);
var query = (from u in db.Users.Include(u => u.Comments)
where u.IsActive == true && u.Id == userId
select u).FirstOrDefault<User>();
query.Comments.Add(comment);
db.SaveChanges();
}
}
