public int CountCoOccurrencesByWord(string word)
{
using (var db = new SovaContext())
{
return(db.CoOccurrence.Where(x => x.Word == word).Count());
}
}
public int CountUserCustomeFields()
{
using (var db = new SovaContext())
{
return(db.UserCustomeField.Count());
}
}
//public List<Notes> ReadAll(int userid, PagingAttributes pagingAttributes)
//{
// SovaContext db = new SovaContext();
// return db.Notes
// .Where(x => x.Userid == userid)
// .Skip(pagingAttributes.Page * pagingAttributes.PageSize)
// .Take(pagingAttributes.PageSize).ToList();
//}
public void Update(Notes updateNote)
{
SovaContext db = new SovaContext();
db.Update(updateNote);
db.SaveChanges();
}
public int CountCoOccurrences()
{
using (var db = new SovaContext())
{
return(db.CoOccurrence.Count());
}
}
public void Remove_sovausers_from_database()
{
DateTime now = DateTime.Now;
var sovaUser = new SovaUser {
SovaUserCreationDate = now
};
var options = new DbContextOptionsBuilder <SovaContext>()
.UseInMemoryDatabase(databaseName: "RemoveSovausersFromDatabase")
.Options;
// Run the test against one instance of the context
using (var context = new SovaContext(options))
{
var service = new SovaUserDataService(context);
service.Add(sovaUser);
service.Add(sovaUser);
service.Delete(1);
}
// Use a separate instance of the context to verify correct data was saved to database
using (var context = new SovaContext(options))
{
Assert.Equal(2, context.SovaUsers.Single().SovaUserId);
Assert.Equal(1, context.SovaUsers.Count());
}
}
public void UpdateSovausers()
{
DateTime now = DateTime.Now;
var sovaUser = new SovaUser {
SovaUserCreationDate = now
};
var options = new DbContextOptionsBuilder <SovaContext>()
.UseInMemoryDatabase(databaseName: "UpdateSovausers")
.Options;
using (var context = new SovaContext(options))
{
var service = new SovaUserDataService(context);
service.Add(sovaUser);
now = DateTime.Now;
sovaUser.SovaUserCreationDate = DateTime.Now;
service.Update(sovaUser);
}
// Use a separate instance of the context to verify correct data was saved to database
using (var context = new SovaContext(options))
{
Assert.Equal(now, context.SovaUsers.Single().SovaUserCreationDate);
}
}
public int CountMarkings(int Uid)
{
using (var db = new SovaContext())
{
return(db.Markings.Where(u => u.UserId == Uid).Count());
}
}
public int CountCommentsByUserId(int id)
{
using (var db = new SovaContext())
{
return(db.Comments.Where(i => i.OwnerUserId == id).Count());
}
}
public User GetById(int id)
{
using (var db = new SovaContext())
{
return(db.Users.Find(id));
}
}
public User CreateSovaUser(User user, string password)
{
using (var db = new SovaContext())
{
// validation
if (string.IsNullOrWhiteSpace(password))
{
throw new AppException("Password is required");
}
if (db.Users.Any(x => x.Username == user.Username))
{
throw new AppException("Username " + user.Username + " is already taken");
}
byte[] passwordHash, passwordSalt;
CreatePasswordHash(password, out passwordHash, out passwordSalt);
user.PasswordHash = passwordHash;
user.PasswordSalt = passwordSalt;
db.Users.Add(user);
db.SaveChanges();
return(user);
}
}
public ICollection <User> GetAllSovaUsers()
{
using (var db = new SovaContext())
{
return(db.Users.ToList());
}
}
///////////////////SOVA APP Users
public User Authenticate(string username, string password)
{
using (var db = new SovaContext())
{
if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password))
{
return(null);
}
var user = db.Users.SingleOrDefault(x => x.Username == username);
// check if username exists
if (user == null)
{
return(null);
}
// check if password is correct
if (!VerifyPasswordHash(password, user.PasswordHash, user.PasswordSalt))
{
return(null);
}
// authentication successful
return(user);
}
}
public IEnumerable <Question> GetFirstFiveQuestions()
{
var db = new SovaContext();
var fiveQuestions = db.Questions.Take(5);
return(fiveQuestions);
}
public Question GetQuestionsWithAnswersAndCommentsById(int id)
{
var db = new SovaContext();
var fetchedQuestion = db.Questions.Include(q => q.Answers).ThenInclude(a => a.AnswerComments).Include(q => q.QuestionComments).Where(q => q.Id == id).First();
return(fetchedQuestion);
}
public void GetSingleSovauser()
{
DateTime now = DateTime.Now;
SovaUser retrievedSovaUser;
SovaUser sovaUser = new SovaUser {
SovaUserCreationDate = now
};
var options = new DbContextOptionsBuilder <SovaContext>()
.UseInMemoryDatabase(databaseName: "GetSingleSovauser")
.Options;
using (var context = new SovaContext(options))
{
var service = new SovaUserDataService(context);
service.Add(sovaUser);
retrievedSovaUser = service.Get(1);
}
// Use a separate instance of the context to verify correct data was saved to database
using (var context = new SovaContext(options))
{
Assert.NotNull(retrievedSovaUser);
Assert.Equal(sovaUser.SovaUserCreationDate, context.SovaUsers.Single().SovaUserCreationDate);
}
}
public int CountQuestions()
{
using (var db = new SovaContext())
{
return(db.Posts.Where(i => i.PostTypeId == 1).Count());
}
}
///////////Term_As_Result
public int CountTermsAsResult(string text)
{
using (var db = new SovaContext())
{
return(db.TermAsResult.FromSql("call bestmatch_terms({0})", text).Count());
}
}
public int CountFavoriteTags()
{
using (var db = new SovaContext())
{
return(db.FavoriteTags.Count());
}
}
public void UpdateMarking(Marking marking)
{
SovaContext db = new SovaContext();
db.Update(marking);
db.SaveChanges();
}
public int CountAnnotations()
{
using (var db = new SovaContext())
{
return(db.Annotations.Count());
}
}
////////////////Searching
public ICollection <SearchResultDTO> Search(string keywords, int page, int pageSize)
{
using (var db = new SovaContext())
{
// var result = db.Posts.FromSql("call keysearch({0})", keywords);
var result = db.SearchResult.FromSql("call SentenceSearch({0})", keywords);
var result2 = result.OrderByDescending(x => x.Rank)
.Skip(page * pageSize)
.Take(pageSize)
.ToList();
List <SearchResultDTO> ResultsDTO = new List <SearchResultDTO>();
var totalResults = result.Count();
foreach (var FoundItem in result2)
{
var posttags = GetPostTagsByPostId(FoundItem.Id);
var newItemDTO = new SearchResultDTO(FoundItem.Id, FoundItem.Title, FoundItem.Rank, posttags);
newItemDTO.totalResults = totalResults;
ResultsDTO.Add(newItemDTO);
}
return(ResultsDTO);
}
}
public int CountMarkings()
{
using (var db = new SovaContext())
{
return(db.Markings.Count());
}
}
public int CountPosts()
{
using (var db = new SovaContext())
{
return(db.Posts.Count());
}
}
public bool UserHasQuestion(int id)
{
using (var db = new SovaContext())
{
return(db.Posts.Where(x => x.PostTypeId == 1).Where(u => u.OwnerUserId == id).ToList().Count > 0);
}
}
public int CountUsers()
{
using (var db = new SovaContext())
{
return(db.UserInfo.Count());
}
}
public int GetNumberOfSearches()
{
using (var db = new SovaContext())
{
var number = db.SearchHistory.Count();
return(number);
}
}
public SearchHistoryDTO GetSearchHistoryById(int id)
{
using (var db = new SovaContext())
{
var s = db.SearchHistory.Where(i => i.Id == id).FirstOrDefault();
return(new SearchHistoryDTO(s.Id, s.SearchContent, s.SearchDate));
}
}
public TagsDTO GetTagByID(int id)
{
using (var db = new SovaContext())
{
var tag = db.Tags.Where(i => i.Id == id).FirstOrDefault();
return(new TagsDTO(tag.Id, tag.Tag));
}
}
public List <History> ReadAll(int userId, PagingAttributes pagingAttributes)
{
SovaContext db = new SovaContext();
return(db.Histories.Skip(pagingAttributes.Page * pagingAttributes.PageSize).Take(pagingAttributes.PageSize).Where(x => x.userid == userId).ToList());
//return db.Histories.Where(x => x.userid == userId).Select(x => x).ToList();
}
public int CountQuestionsByUserId(int id)
{
using (var db = new SovaContext())
{
var answers = db.Posts.Where(i => i.PostTypeId == 1).ToList();
return(answers.Where(u => u.OwnerUserId == id).Count());
}
}
