public List <Post> getPostQ(int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var query =
(from u in db.posts
where u.type == 1 //Makes sure it's only questions and not answers - A good solution imo would be to include a boolean (withAnswers or something)
select new Post
{
id = u.id,
type = u.type,
parent_id = u.parent_id,
answer_id = u.answer_id,
creationDate = u.creationDate,
score = u.score,
text = u.text,
closedDate = u.closedDate,
title = u.title
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
return(query);
}
}
public List <Post> getPost(int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var query =
(from u in db.Posts
select new Post
{
id = u.id,
type = u.type,
parent_id = u.parent_id,
answer_id = u.answer_id,
creationDate = u.creationDate,
score = u.score,
text = u.text,
closedDate = u.closedDate,
title = u.title
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
return(query);
}
}
public List <Word> wordCloud(string search)
{
List <Word> words = new List <Word>();
using (var db = new stackOverflowContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Parameters.Add("@1", DbType.String);
cmd.Parameters["@1"].Value = search;
cmd.CommandText = "call subpj3_b6(@1)";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Word word = new Word
{
Name = reader.GetString(0),
weight = reader.GetDouble(1)
};
words.Add(word);
}
}
return(words);
}
public bool updateMarking(int userid, int postid, string note)
{
using (var db = new stackOverflowContext())
{
var update = new Marking
{
postId = postid,
userID = userid,
note = note
};
try
{
db.Marking.Update(update);
db.SaveChanges();
Console.WriteLine("Updated");
return(true);
}
catch (Exception e)
{
Console.WriteLine("Couldn't update");
return(false);
}
}
}
public int amountWeightPosts(string search)
{
List <WeightedPost> posts = new List <WeightedPost>();
using (var db = new stackOverflowContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Parameters.Add("@1", DbType.String);
cmd.Parameters["@1"].Value = search;
cmd.CommandText = "call subpj3_b4(@1)";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
WeightedPost post = new WeightedPost
{
postId = reader.GetInt32(0),
};
posts.Add(post);
}
}
return(posts.Count());
}
public List <Post> getPostWord(string postword, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var query =
(from p in db.Posts
where p.title.Contains(postword)
select new Post
{
id = p.id,
type = p.type,
parent_id = p.parent_id,
answer_id = p.answer_id,
creationDate = p.creationDate,
score = p.score,
text = p.text,
closedDate = p.closedDate,
title = p.title
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
return(query);
}
}
public List <Post> getPostByUser(int postuserid, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var query =
(from u in db.Posts
join us in db.User
on u.user.id equals us.id
where us.id == postuserid
select new Post
{
type = u.type,
parent_id = u.parent_id,
answer_id = u.answer_id,
creationDate = u.creationDate,
score = u.score,
text = u.text,
closedDate = u.closedDate,
title = u.title
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
Console.WriteLine(query.Count());
return(query);
}
}
public void Select_post_bywordandtag(string a, string b)
{
using (var db = new stackOverflowContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Parameters.Add("@1", DbType.String);
cmd.Parameters.Add("@2", DbType.String);
cmd.Parameters["@1"].Value = a;
cmd.Parameters["@2"].Value = b;
cmd.CommandText = "call select_posts(@1, @2)";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine((reader.GetInt32((int)0), reader.GetInt32(5), reader.GetString(6)));
}
}
}
public bool deleteFavourites(int userid, int postid)
{
using (var db = new stackOverflowContext())
{
var delete = new Marking
{
userID = userid,
postId = postid
};
try
{
db.Marking.Remove(delete);
db.SaveChanges();
Console.WriteLine("Deletion complete");
return(true);
}
catch (Exception e)
{
Console.WriteLine("Couldn't delete");
return(false);
throw;
}
}
}
public Post getCommments(int postid, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var query1 =
(from c in db.Comments
where c.postId == postid
select new Comment
{
score = c.score,
text = c.text,
creationDate = c.creationDate,
}).ToList();
var query2 =
(from p in db.Posts
where p.id == postid
select new Post
{
score = p.score,
text = p.text,
creationDate = p.creationDate,
comments = query1
});
return(query2.FirstOrDefault());
}
}
public List <Post> getPostByTag(string tag, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var query =
(from t in db.Tags
join c in db.Combinations
on t.id equals c.tags_id
join p in db.Posts
on c.post_id equals p.id
where t.name == tag
select new Post
{
id = p.id,
type = p.type,
parent_id = p.parent_id,
answer_id = p.answer_id,
creationDate = p.creationDate,
score = p.score,
text = p.text,
closedDate = p.closedDate,
title = p.title
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
Console.WriteLine(query.FirstOrDefault().title);
return(query);
}
}
public int amountPost()
{
using (var db = new stackOverflowContext())
{
var q =
(from p in db.Posts
select new Post
{
id = p.id
}).ToList();
return(q.Count());
}
}
public int amountPostA(int id)
{
using (var db = new stackOverflowContext())
{
var q =
(from p in db.posts
where p.type == 2 && p.parent_id == id
select new Post
{
id = p.id
}).ToList();
return(q.Count());
}
}
public int userAmount()
{
using (var db = new stackOverflowContext())
{
var query = (
from u in db.User
select new User
{
id = u.id
}).ToList();
return(query.Count());
}
}
public int markingAmount(int userid)
{
using (var db = new stackOverflowContext())
{
var query = (
from m in db.Marking
where m.userID == userid
select new Marking
{
postId = m.postId
}).ToList();
return(query.Count());
}
}
public int historyAmount(int userid)
{
using (var db = new stackOverflowContext())
{
var query = (
from h in db.History
where h.userId == userid
select new History
{
userId = h.userId
}).ToList();
return(query.Count());
}
}
public int amountComments(int postid)
{
using (var db = new stackOverflowContext())
{
var q =
(from c in db.Comments
where c.postId == postid
select new Post
{
score = c.score
}).ToList();
return(q.Count());
}
}
public int amountPostWord(string postWord)
{
using (var db = new stackOverflowContext())
{
var query =
(from p in db.Posts
where p.title.Contains(postWord)
select new Post
{
id = p.id,
}).ToList();
return(query.Count());
}
}
public int userNameAmount(string s)
{
using (var db = new stackOverflowContext())
{
var query = (
from u in db.User
where u.name.Contains(s)
select new User
{
name = u.name
}).ToList();
return(query.Count());
}
}
public Marking createMarking(int userid, int postid, string note)
{
using (var db = new stackOverflowContext())
{
var query = new Marking
{
postId = postid,
userID = userid,
note = note
};
db.Marking.Add(query);
db.SaveChanges();
return(query);
}
}
public History createHistory(int id, string search)
{
using (var db = new stackOverflowContext())
{
var query = new History
{
userId = id,
searchWord = search
};
db.History.Add(query);
db.SaveChanges();
Console.WriteLine("Succesfully created");
return(query);
}
}
public int amountPostByUser(int postuserid)
{
using (var db = new stackOverflowContext())
{
var query =
(from u in db.Posts
join us in db.User
on u.user.id equals us.id
where us.id == postuserid
select new Post
{
id = u.id
}).ToList();
return(query.Count());
}
}
public List <WeightedPost> getWeightedPosts(string search, int page, int pageSize)
{
List <WeightedPost> posts = new List <WeightedPost>();
using (var db = new stackOverflowContext())
{
var conn = (MySqlConnection)db.Database.GetDbConnection();
conn.Open();
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Parameters.Add("@1", DbType.String);
cmd.Parameters["@1"].Value = search;
cmd.CommandText = "call subpj3_b4(@1)";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
WeightedPost post = new WeightedPost
{
postId = reader.GetInt32(0),
title = reader.GetString(1),
weight = reader.GetDouble(3)
};
posts.Add(post);
}
}
List <WeightedPost> trimmedPostList = new List <WeightedPost>();
for (int i = page * pageSize; i < (page * pageSize) + pageSize; i++)
{
if (i == posts.Count())
{
break;
}
trimmedPostList.Add(posts[i]);
}
return(trimmedPostList);
}
public List <User> getUserById(int id)
{
using (var db = new stackOverflowContext())
{
var query =
(from u in db.user
where u.id.Equals(id) //Makes sure it's only questions and not answers - A good solution imo would be to include a boolean (withAnswers or something)
select new User
{
id = u.id,
name = u.name,
location = u.location,
age = u.age,
creationDate = u.creationDate,
})
.ToList();
return(query);
}
}
public List <Marking> getFavourites(int id, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var q =
(from m in db.Marking
where m.userID == id
select new Marking
{
postId = m.postId,
userID = m.userID,
note = m.note
}).OrderBy(u => u.userID)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
return(q);
}
}
public int amountPostByTag(string tag)
{
using (var db = new stackOverflowContext())
{
var query =
(from t in db.Tags
join c in db.Combinations
on t.id equals c.tags_id
join p in db.Posts
on c.post_id equals p.id
where t.name == tag
select new Post
{
id = p.id
}).ToList();
return(query.Count());
}
}
public List <User> getUsername(string s, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var users =
(from n in db.User
where n.name.Contains(s)
select new User
{
age = n.age,
creationDate = n.creationDate,
location = n.location,
name = n.name
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
return(users);
}
}
public List <History> getHistory(int id, int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var q =
(from h in db.History
where h.userId == id
select new History
{
id = h.id,
userId = h.userId,
searchWord = h.searchWord
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
return(q);
}
}
public List <User> getUser(int page, int pageSize)
{
using (var db = new stackOverflowContext())
{
var q =
(from u in db.User
select new User
{
age = u.age,
creationDate = u.creationDate,
location = u.location,
name = u.name
}).OrderBy(u => u.id)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
Console.WriteLine(q.FirstOrDefault());
return(q);
}
}
public bool deleteHistory(int id)
{
using (var db = new stackOverflowContext())
{
var delete = new History
{
id = id
};
try
{
db.History.Remove(delete);
db.SaveChanges();
Console.WriteLine("Succesfully deleted");
return(true);
}
catch (Exception e)
{
Console.WriteLine("Couldn't delete");
return(false);
}
}
}