public List <SubjectViewModel> GetSubjectViewModelList()
{
List <SubjectViewModel> subjectViewModelList;
using (_context = new ExaminationContext())
{
subjectViewModelList = _context.Subject.OrderBy(s => s.SubjectId).Include(x => x.Exam)
.Select(s => new SubjectViewModel
{
SubjectId = s.SubjectId,
SubjectName = s.SubjectName,
Description = s.Description,
InExam = s.Exam.Count() > 0
})
.ToList();
// SUBJECTVIEWMODEL!
//var ss = _context.Subject.Include(x => x.Exam).OrderBy(x => x.SubjectId)
// .Select(x => new { SubjectName = x.SubjectName,
// SubjectId = x.SubjectId,
// Description = x.Description,
// ExamCount = x.Exam.Count()
// }).ToList();
//foreach (var z in ss)
//{
// Console.WriteLine(z.SubjectName + " => " + z.ExamCount);
//}
}
return(subjectViewModelList);
}
public List <ExamineeReport> GetExamineeList(DateTime startDate, DateTime?endDate = null)
{
IQueryable <Examinee> examineeList;
using (_context = new ExaminationContext())
{
if (endDate == null)
{
examineeList = _context.Examinee.Where(e => DbFunctions.TruncateTime(e.DateTimeAdded) == DbFunctions.TruncateTime(startDate));
}
else
{
examineeList = _context.Examinee.Where(e => DbFunctions.TruncateTime(e.DateTimeAdded) >= DbFunctions.TruncateTime(startDate) &&
DbFunctions.TruncateTime(e.DateTimeAdded) <= DbFunctions.TruncateTime(endDate));
}
return(examineeList.Select(s => new ExamineeReport
{
FullName = s.LastName + ", " + s.FirstName + " " + s.MiddleName,
Email = s.Email,
ContactNo = s.ContactNo,
City = s.City,
DateTimeRegisteredOrAdded = s.DateTimeAdded
})
.ToList());
}
}
public List <ExamViewModel> GetExamViewModelList(bool IsArchived)
{
List <ExamViewModel> examViewModelList;
using (_context = new ExaminationContext())
{
examViewModelList = _context.Exam.Include(u => u.SystemUser)
.Include(s => s.Subject)
.Include(q => q.QuestionBank)
.Where(e => e.Archived == IsArchived)
.OrderBy(e => e.ExamId)
.Select(e => new ExamViewModel
{
UserId = e.UserId,
LastName = e.SystemUser.LastName,
FirstName = e.SystemUser.FirstName,
MiddleName = e.SystemUser.MiddleName,
Username = e.SystemUser.Username,
ExamId = e.ExamId,
SubjectId = e.SubjectId,
SubjectName = e.Subject.SubjectName,
ExaminationType = e.ExaminationType,
ItemCount = e.ItemCount,
TimeLimit = e.TimeLimit,
DateTimeAdded = e.DateTimeAdded,
IncompleteQuestionCount = e.QuestionBank.Where(q => q.Question != null && q.Question != string.Empty).Count()
})
.ToList();
}
return(examViewModelList);
}
public bool HasIncompleteExam()
{
using (_context = new ExaminationContext())
{
return(_context.Exam.Where(e => e.Archived == false && e.QuestionBank.Any(q => q.Question == null)).Count() > 0);;
}
}
public List <ExamineeTakeStatusViewModel> GetExamineeTakeStatusViewModel(int currentDaysToWait, string nameOrEmail)
{
List <ExamineeTakeStatusViewModel> examineeTakeStatus;
using (_context = new ExaminationContext())
{
DateTime currentDateTime = GetServerDateTime(_context);
examineeTakeStatus = _context.Examinee.Include(e => e.ExamineeTake)
.Where(e => (e.LastName.Contains(nameOrEmail) ||
e.FirstName.Contains(nameOrEmail) ||
e.MiddleName.Contains(nameOrEmail)) ||
e.Email.Contains(nameOrEmail))
.GroupBy(g => new
{
g.ExamineeId,
CurrentDateTime = currentDateTime,
FullName = string.Concat(g.LastName + ", " + g.FirstName + " " + (string.IsNullOrEmpty(g.MiddleName) ? "" : g.MiddleName)),
g.Email,
g.ExamineeTake.OrderByDescending(o => o.CodeDateTimeIssued).FirstOrDefault().CodeDateTimeIssued
})
.Select(e => new ExamineeTakeStatusViewModel
{
ExamineeId = e.Key.ExamineeId,
FullName = e.Key.FullName,
Email = e.Key.Email,
CurrentDateTime = e.Key.CurrentDateTime,
CodeIssuedDateTime = e.Key.CodeDateTimeIssued,
AllowExamAfterDateTime = e.Key.CodeDateTimeIssued == null ? null : DbFunctions.AddDays(e.Key.CodeDateTimeIssued, currentDaysToWait)
})
.ToList();
}
return(examineeTakeStatus);
}
//public void DeleteUser(SystemUser user)
//{
// using (_context = new ExaminationContext())
// {
// _context.Entry(user).State = EntityState.Deleted;
// _context.SaveChanges();
// }
//}
public List <SystemUserViewModel> GetUserListByNameViewModel(string namePart)
{
List <SystemUserViewModel> userListViewModel;
using (_context = new ExaminationContext())
{
userListViewModel = _context.SystemUser
.Where(u => u.LastName.Contains(namePart) ||
u.FirstName.Contains(namePart) ||
u.MiddleName.Contains(namePart))
.Select(u => new SystemUserViewModel
{
UserId = u.UserId,
LastName = u.LastName,
FirstName = u.FirstName,
MiddleName = u.MiddleName,
Username = u.Username,
UserLevel = u.UserLevel,
AccountStatus = u.AccountStatus
})
.ToList();
}
return(userListViewModel);
}
public DateTime GetDateTime()
{
using (_context = new ExaminationContext())
{
return(_context.Database.SqlQuery <DateTime>("Select GETDATE();").FirstOrDefault());
}
}
//public void DeleteQuestion(QuestionBank question)
//{
// using (_context = new ExaminationContext())
// {
// //QuestionBank question = new QuestionBank { QuestionId = questionId };
// //_context.Entry(question).State = EntityState.Deleted;
// //_context.QuestionBank.Remove(question);
// _context.Entry(question).State = EntityState.Deleted;
// Exam exam = _context.Exam.Where(e => e.ExamId == question.ExamId).Single();
// exam.ItemCount = exam.ItemCount - 1;
// _context.SaveChanges();
// }
//}
public List <QuestionBank> GetQuestionBankList(int examId)
{
List <QuestionBank> questionBankList;
using (_context = new ExaminationContext())
{
questionBankList = _context.QuestionBank
.Where(q => q.ExamId == examId)
.OrderBy(q => q.QuestionNumber)
//.Select(q => new QuestionBankViewModel
//{
// ExamId = q.ExamId,
// QuestionId = q.QuestionId,
// Question = q.Question,
// CorrectAnswer = q.CorrectAnswer,
// WrongAnswer1 = q.WrongAnswer1,
// WrongAnswer2 = q.WrongAnswer2,
// WrongAnswer3 = q.WrongAnswer3,
// DateTimeAdded = q.DateTimeAdded
//})
.ToList();
}
return(questionBankList);
}
public bool CheckLogIn(LoginViewModel loginUser)
{
SystemUserViewModel userViewModel;
using (_context = new ExaminationContext())
{
userViewModel = _context.SystemUser.Where(u => u.Username == loginUser.Username &&
u.Pword == loginUser.Password &&
u.AccountStatus == true)
.Select(u => new SystemUserViewModel
{
UserId = u.UserId,
LastName = u.LastName,
FirstName = u.FirstName,
MiddleName = u.MiddleName,
UserLevel = u.UserLevel
})
.SingleOrDefault();
}
if (userViewModel != null) // user exists
{
UserInfo.UserId = userViewModel.UserId;
UserInfo.CurrentUser = string.Format("{0} {1}", userViewModel.FirstName, userViewModel.LastName);
UserInfo.UserLevel = userViewModel.UserLevel;
return(true);
}
UserInfo.UserId = 0;
UserInfo.CurrentUser = string.Empty;
UserInfo.UserLevel = string.Empty;
return(false);
}
public PagedResult <ExamineeViewModel> GetExamineeListViewModel(int pageNumber, int pageSize)
{
PagedResult <ExamineeViewModel> examineeListViewModel;
using (_context = new ExaminationContext())
{
examineeListViewModel = _context.Examinee.OrderBy(e => e.ExamineeId)
.Select(e => new ExamineeViewModel
{
ExamineeId = e.ExamineeId,
LastName = e.LastName,
FirstName = e.FirstName,
MiddleName = e.MiddleName,
Address = e.Address,
City = e.City,
ContactNo = e.ContactNo,
Email = e.Email,
LastSchoolAttended = e.LastSchoolAttended,
YearGraduated = e.YearGraduated,
ExamTakes = e.ExamTakes,
DateTimeAdded = e.DateTimeAdded
})
.GetPaged(pageNumber, pageSize);
}
return(examineeListViewModel);
}
public void InsertSubject(Subject subject)
{
using (_context = new ExaminationContext())
{
_context.Subject.Add(subject); //_context.Entry(subject).State = EntityState.Added;
_context.SaveChanges();
}
}
public void SendExamToArchieve(int examId, bool toArchive)
{
using (_context = new ExaminationContext())
{
_context.Exam.Where(e => e.ExamId == examId).Single().Archived = toArchive;
_context.SaveChanges();
}
}
public void UpdateSubject(Subject subject)
{
using (_context = new ExaminationContext())
{
_context.Entry(subject).State = EntityState.Modified;
_context.SaveChanges();
}
}
public void InsertUser(SystemUser user)
{
using (_context = new ExaminationContext())
{
_context.SystemUser.Add(user); //_context.Entry(user).State = EntityState.Added;
_context.SaveChanges();
}
}
public void InsertQuestion(QuestionBank question)
{
using (_context = new ExaminationContext())
{
_context.QuestionBank.Add(question); //_context.Entry(question).State = EntityState.Added;
_context.SaveChanges();
}
}
public DateTime GetServerDateTime()
{
//DateTime dbServerDateTime = _context.Database.SqlQuery<DateTime>("Select GETDATE();").FirstOrDefault();
//return dbServerDateTime;
using (_context = new ExaminationContext())
{
return(_context.Database.SqlQuery <DateTime>("Select GETDATE();").FirstOrDefault());
}
}
public void InsertExam(Exam exam)
{
using (_context = new ExaminationContext())
{
exam.DateTimeAdded = GetServerDateTime(_context);
_context.Exam.Add(exam);
_context.SaveChanges();
}
}
public void UpdateUser(SystemUser user)
{
using (_context = new ExaminationContext())
{
_context.Entry(user).State = EntityState.Modified;
_context.Entry(user).Property(u => u.Pword).IsModified = false;
_context.SaveChanges();
}
}
public void InsertExaminee(Examinee examinee)
{
using (_context = new ExaminationContext())
{
examinee.DateTimeAdded = GetServerDateTime(_context);
_context.Examinee.Add(examinee); //_context.Entry(examinee).State = EntityState.Added;
_context.SaveChanges();
}
}
public void IncreaseExamineeExamTaken(int examineeId)
{
using (_context = new ExaminationContext())
{
Examinee examinee = _context.Examinee.Find(examineeId);
examinee.ExamTakes += 1;
_context.SaveChanges();
}
}
public void UpdateExaminee(Examinee examinee)
{
using (_context = new ExaminationContext())
{
_context.Entry(examinee).State = EntityState.Modified;
//_context.Entry(examinee).Property(p => p.DateTimeAdded).IsModified = false; // DateTimeAdded should not be modified
_context.SaveChanges();
}
}
public void SetPassingRate(PassingRate passingRate)
{
using (_context = new ExaminationContext())
{
passingRate.DateTimeModified = GetServerDateTime(_context);
_context.PassingRate.Add(passingRate);
_context.SaveChanges();
}
}
public void SetWaitDays(ExamineeFailure examineeFailure)
{
using (_context = new ExaminationContext())
{
examineeFailure.DateTimeModified = GetServerDateTime(_context);
_context.ExamineeFailure.Add(examineeFailure);
_context.SaveChanges();
}
}
public void DeleteExam(int examId)
{
using (_context = new ExaminationContext())
{
//Examinee examinee = new Examinee { ExamineeId = examineeId };
//_context.Entry(examinee).State = EntityState.Deleted;
_context.Exam.Remove(_context.Exam.SingleOrDefault(e => e.ExamId == examId));
_context.SaveChanges();
}
}
public string[] GetExamineeCodesByExamineeId(int examineeId)
{
using (_context = new ExaminationContext())
{
return(_context.ExamineeTake.Where(e => e.ExamineeId == examineeId)
.OrderBy(e => e.CodeDateTimeIssued)
.Select(e => e.ExamCode)
.ToArray());
}
}
public void DeleteSubject(int subjectId)
{
using (_context = new ExaminationContext())
{
//Subject subject = new Subject { SubjectId = subjectId };
//_context.Entry(subject).State = EntityState.Deleted;
_context.Subject.Remove(_context.Subject.Single(s => s.SubjectId == subjectId));
_context.SaveChanges();
}
}
public void UpdateOne(ExamineeTake examineeTake)
{
using (_context = new ExaminationContext())
{
//examineeTake.ExamDateTimeTaken = DateTime.Now.Date;
_context.ExamineeExam.AddRange(examineeTake.ExamineeExam);
//_context.ExamineeTake.Attach(examineeTake);
_context.Entry(examineeTake).State = EntityState.Modified;
_context.SaveChanges();
}
}
public int GetExamineeCount()
{
int examineeCount;
using (_context = new ExaminationContext())
{
examineeCount = _context.Examinee.Count();
}
return(examineeCount);
}
public int GetUserCount()
{
int userCount;
using (_context = new ExaminationContext())
{
userCount = _context.SystemUser.Count();
}
return(userCount);
}
public string InsertExamineeTake(ExamineeTake examineeTake)
{
using (_context = new ExaminationContext())
{
examineeTake.CodeDateTimeIssued = GetServerDateTime(_context);
examineeTake.UserId = UserInfo.UserId;
_context.ExamineeTake.Add(examineeTake);
_context.SaveChanges();
}
return(examineeTake.ExamCode);
}