public void AddReport(Report report)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Save(report);
}
}
public void UpdateComment(Comment comment)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Save(comment);
}
}
public void SaveUser(User user)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Save(user);
}
}
public IList <User> GetUsers()
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <User>().ToList());
}
}
public void UpdatePost(Post post)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Save(post);
}
}
// mozliwe ze trzeba bedzie popracowac nad wydajnoscia, jak sie pojawią problemy
public List <Comment> GetManyComments(int postId, int lastId, int count)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
var masterComments = db.Query <Comment>()
.Include(c => c.User)
.Include(p => p.Post)
.OrderByDescending(c => c.CreationDate)
.Where(c => c.Post.Id == postId)
.Where(c => c.Id < lastId || lastId == 0)
.Where(c => c.MasterComment == null)
.Where(c => c.DeletionDate == null)
.Limit(count)
.ToList();
foreach (var masterComment in masterComments)
{
var answers = db.Query <Comment>()
.Include(c => c.User)
.Include(c => c.MasterComment)
.OrderBy(c => c.CreationDate)
.Where(c => c.MasterComment.Id == masterComment.Id)
.Where(c => c.DeletionDate == null)
.ToList();
masterComment.Answers = answers;
}
return(masterComments);
}
}
public Role GetDefaultRole()
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Role>().SingleOrDefault(r => r.Name == "Member"));
}
}
public void UpdateLike(Like like)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Update(like);
}
}
public void AddLike(Like like)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Save(like);
}
}
protected void Application_Start()
{
CustomDatabaseFactory.Setup();
GlobalConfiguration.Configure(WebApiConfig.Register);
MigrationRunner.RunMigrations();
AutofacConfig.Configure();
}
public User GetUserById(int id)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <User>()
.SingleOrDefault(u => u.Id == id));
}
}
public User GetUserByCredentials(string username)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <User>()
.SingleOrDefault(u => u.Username == username));
}
}
public void SaveUserRole(UserRole userRole)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
db.Execute(
$"INSERT INTO [dbo].[UserRoles] ([UserId],[RoleId]) VALUES ({userRole.User.Id} , {userRole.Role.Id})");
}
}
public IList <ReportType> GetReportTypes()
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <ReportType>()
.ToList());
}
}
public Following GetFollowing(string authorName, int userId)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Following>()
.Include(p => p.Followed)
.Include(p => p.Follower)
.SingleOrDefault(f => f.Followed.Username == authorName && f.Follower.Id == userId));
}
}
public Post GetBestUserPost(string username)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Post>()
.Include(p => p.Op)
.OrderByDescending(p => p.Rating)
.FirstOrDefault(p => p.Op.Username.Equals(username)));
}
}
public Like GetLikeByCommentId(int commentId, int userId)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Like>()
.Include(l => l.Comment)
.Include(l => l.User)
.SingleOrDefault(l => l.Comment.Id == commentId && l.User.Id == userId));
}
}
public bool DidUserReportPost(int userId, int postId)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Report>()
.Include(r => r.Reporter)
.Include(r => r.Post)
.Any(r => r.Reporter.Id == userId && r.Post.Id == postId));
}
}
public Post GetPostById(int id)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
var result = db.Query <Post>()
.Include(p => p.Op)
.SingleOrDefault(p => p.Id == id);
return(result);
}
}
public int GetNumberOfDeletedByUserId(int userId)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Comment>()
.Include(c => c.User)
.Where(c => c.User.Id == userId || userId == 0)
.Where(c => c.DeletionDate != null)
.Count());
}
}
public List <Post> GetUserPosts(string username)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Post>()
.Include(p => p.Op)
.Where(p => p.Op.Username.Equals(username))
.Where(p => p.DeletionDate == null)
.ToList());
}
}
public Role GetUserRoleByUserId(int userId)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <UserRole>()
.Include(ur => ur.User)
.Include(ur => ur.Role)
.Where(ur => ur.User.Id == userId)
.FirstOrDefault()?
.Role);
}
}
public Report GetReportById(int id)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Report>()
.Include(r => r.Comment)
.Include(r => r.Post)
.Include(r => r.Reporter)
.Include(r => r.ReportType)
.SingleOrDefault(r => r.Id == id));
}
}
public List <Post> GetTopPosts(int lastId, int count)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Post>()
.Include(p => p.Op)
.OrderByDescending(p => p.Rating)
.Where(p => p.Id < lastId || lastId == 0)
.Where(p => p.DeletionDate == null)
.Limit(count)
.ToList());
}
}
public List <User> StepaniakToKutas(string userName)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
var result = db.Query <Following>()
.Include(p => p.Followed)
.Include(p => p.Follower)
.Where(f => f.Follower.Username == userName && f.Active)
.ToList();
return(result.Select(r => r.Followed).ToList());
}
}
public List <Post> GetUserPosts(int lastId, int count, string authorName)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Post>()
.Include(p => p.Op)
.OrderByDescending(p => p.CreationDate)
.Where(p => p.Op.Username.Equals(authorName))
.Where(p => p.Id < lastId || lastId == 0)
.Where(p => p.DeletionDate == null)
.Limit(count)
.ToList());
}
}
public void UpsertFollowing(Following following)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
var result =
db.Query <Following>()
.Include(p => p.Followed)
.Include(p => p.Follower)
.Where(f => f.Followed.Id == following.Followed.Id && f.Follower.Id == following.Follower.Id).FirstOrDefault();
db.Execute(
result != null
? $"UPDATE [Followings] SET [Active] = '{following.Active}' WHERE [FollowerId] = {following.Follower.Id} AND [FollowedId] = {following.Followed.Id}"
: $"INSERT INTO [Followings] VALUES ({following.Follower.Id}, {following.Followed.Id}, '{following.Active}')");
//db.Save(following);
}
}
public List <Comment> GetBestComments(int postId, int count)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
var result = db.Query <Comment>()
.Include(c => c.User)
.Include(p => p.Post)
.OrderByDescending(c => c.Rating)
.Where(c => c.Post.Id == postId)
.Where(c => c.DeletionDate == null)
.Limit(count)
.ToList();
return(result);
}
}
public List <User> GetUsersForReport(string username, string sex, int status)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
//return db.Query<User>()
// .Where(u =>
// u.Username.StartsWith(username) && u.Sex == GetGender(sex) && u.BannedDate.HasValue == IsBanned(status))
// .ToList();
var query = db.Query <User>()
.Where(u => string.IsNullOrEmpty(username) || u.Username.StartsWith(username))
.Where(u => sex.Equals("0") || u.Sex == GetGender(sex))
.Where(u => status == 0 || u.BannedDate.HasValue == IsBanned(status));
//var paramUsername = 1;
//var paramSex = 1;
//var paramBanned = 1;
//var notStr = "";
if (!string.IsNullOrEmpty(username))
{
query = query.Where(u => u.Username.StartsWith(username));
}
if (!sex.Equals("0"))
{
query = query.Where(u => u.Sex == GetGender(sex));
}
if (status != 0)
{
query = query.Where(u => u.BannedDate.HasValue == IsBanned(status));
}
//if (status == 0)
// paramBanned = 0;
//if (status == 2)
// notStr = "NOT";
return(query.ToList());
//return db.Fetch<User>(
// $"SELECT * FROM [Users] U WHERE ({paramUsername} = 0 OR U.Username LIKE '{username}%') AND ({paramSex} = 0 OR U.Sex LIKE '{GetGender(sex)}') AND ({paramBanned} = 0 OR ([U].[BannedDate] is {notStr} null))");
}
}
public List <Report> GetReportedComments(int lastId)
{
using (var db = CustomDatabaseFactory.GetConnection())
{
return(db.Query <Report>()
.Include(r => r.Post)
.Include(r => r.Reporter)
.Include(r => r.Comment.User)
.Include(r => r.ReportType)
.Where(r => r.Post == null)
.Where(r => r.Comment != null)
.Where(r => r.Comment.DeletionDate == null)
.Where(r => r.Comment.Id > lastId || lastId == 0)
.OrderBy(r => r.Comment.Id)
.ToList());
}
}
