public static void SaveSubscriptionChanges(int courseId, int accountId, string comment, byte?rating)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
// If you have composite primary key, then pass key values in the order they defined in model:
var subscriptionInDB = dbContext.Subscriptions.Find(courseId, accountId);
if (!String.IsNullOrEmpty(comment))
{
subscriptionInDB.Comment = comment;
}
if (rating != null)
{
subscriptionInDB.Rate = rating;
}
try
{
dbContext.SaveChanges();
}
catch (Exception)
{
throw new Exception("could not reach the Database");
}
}
}
public static Course GetQuiz(int id)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
Course course = dbContext.Courses.Where(c => c.Id == id).Include(c => c.Questions).FirstOrDefault();
return(course);
}
}
public static List <Teacher> GetTeachers()
{
using (ElearnerContext dbContext = new ElearnerContext())
{
List <Teacher> teacherList = dbContext.Teachers.Include(a => a.Account).ToList();
return(teacherList);
}
}
public static void UpdateGradeToDb(byte?grade, int studentid, int courseid)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
var query = dbContext.Subscriptions.Where(x => x.StudentId == studentid && x.CourseId == courseid).FirstOrDefault();
query.Grade = grade;
dbContext.SaveChanges();
}
}
public static IList <Subscription> GetStudentSubscriptions(int studentId)
{
IList <Subscription> subscriptions;
using (ElearnerContext dbContext = new ElearnerContext())
{
subscriptions = dbContext.Subscriptions.Where(x => x.StudentId == studentId).Include(x => x.Course).ToList();
}
return(subscriptions);
}
public static Course GetFullCourseDetails(int courseId)
{
Course results;
using (ElearnerContext dbContext = new ElearnerContext())
{
results = dbContext.Courses.Where(c => c.Id == courseId).Include(c => c.Content).Include(q => q.Questions).FirstOrDefault();
}
return(results);
}
public static IList <Course> GetCourseByTeacher(int teacherId)
{
IList <Course> results;
using (ElearnerContext dbContext = new ElearnerContext())
{
results = dbContext.Courses.Where(c => c.TeacherId == teacherId).ToList();
}
return(results);
}
public static decimal UpdateUserDeposit(int accountId)
{
decimal deposit;
using (ElearnerContext dbContext = new ElearnerContext())
{
deposit = dbContext.BankAccounts.Where(b => b.AccountId == accountId).Select(d => d.Deposit).FirstOrDefault();
}
return(deposit);
}
public static double?GetCourseRating(int courseId)
{
double?avg;
using (ElearnerContext dbContext = new ElearnerContext())
{
avg = dbContext.Subscriptions.Where(s => s.CourseId == courseId && s.Rate != null).Average(s => s.Rate);
}
return(avg);
}
public static List <Course> GetMostPopular()
{
using (ElearnerContext dbContext = new ElearnerContext())
{
var popularCourses = dbContext.Courses.SqlQuery("SELECT TOP 3 Id,Name,Duration,Price,TeacherId,Description " +
"FROM Courses INNER JOIN Subscriptions ON Courses.Id = Subscriptions.CourseId " +
"GROUP BY Id,Name,Duration,Price,TeacherId,Description " +
"ORDER BY COUNT(Subscriptions.StudentId) DESC").ToList();
return(popularCourses);
}
}
public static List <Course> GetFreeCourses()
{
Random r = new Random();
using (ElearnerContext dbContext = new ElearnerContext())
{
var query = dbContext.Courses.Where(x => x.Price == 0).ToList();
List <Course> freeCourses = query.OrderBy(x => r.Next()).Take(3).ToList();
return(freeCourses);
}
}
public static Account SignUp(String name, string lastname, DateTime birthdate, string email, string password, Decimal deposit)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
if (String.IsNullOrWhiteSpace(name) || String.IsNullOrWhiteSpace(lastname) ||
String.IsNullOrWhiteSpace(email) || String.IsNullOrWhiteSpace(password) || birthdate == null || deposit < 0)
{
return(null);
}
else if (dbContext.Accounts.FirstOrDefault(a => a.Email == email) != null)
{
return(null);
}
Account accountRecord = new Account()
{
Email = email,
Password = password,
};
dbContext.Accounts.Add(accountRecord);
dbContext.SaveChanges();
int lastInsertedId = dbContext.Accounts.OrderByDescending(a => a.Id).Select(a => a.Id).First();
Student studentRecord = new Student()
{
Name = name,
Lastname = lastname,
Birthdate = birthdate,
AccountId = lastInsertedId
};
dbContext.Students.Add(studentRecord);
dbContext.SaveChanges();
BankAccount bankAccount = new BankAccount()
{
AccountId = lastInsertedId,
Deposit = deposit
};
dbContext.BankAccounts.Add(bankAccount);
dbContext.SaveChanges();
Account result = new Account();
result = accountRecord;
result.Student = studentRecord;
result.BankAccount = bankAccount;
return(result);
}
}
public static void UpdateStudentToDb(Student current, Student changes, ElearnerContext dbContext)
{
if (current == null && changes == null)
{
throw new ArgumentException("Accounts cannot be null.");
}
current.Name = changes.Name;
current.Lastname = changes.Lastname;
current.Birthdate = changes.Birthdate;
dbContext.SaveChanges();
}
public static decimal AddMoneyToUser(int accountId, decimal amount)
{
BankAccount bankAccount;
using (ElearnerContext dbContext = new ElearnerContext())
{
bankAccount = dbContext.BankAccounts.Where(b => b.AccountId == accountId).FirstOrDefault();
bankAccount.Deposit += amount;
dbContext.SaveChanges();
}
return(bankAccount.Deposit);
}
public static bool HasStudentThisCourse(int studentId, int CourseId)
{
bool result;
using (ElearnerContext dbContent = new ElearnerContext())
{
result = dbContent.Subscriptions
.Where(x => x.StudentId == studentId)
.Where(x => x.CourseId == CourseId)
.FirstOrDefault() != null ? true : false;
}
return(result);
}
public static void UpdateAccountToDb(Account current, Account changed)
{
if (current == null && changed == null)
{
throw new ArgumentException("Accounts cannot be null.");
}
using (ElearnerContext dbContext = new ElearnerContext())
{
current.Email = changed.Email;
current.Password = changed.Password;
dbContext.SaveChanges();
}
}
public static void UpdateCourse(Course changedCourse)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
Course oldCourse = dbContext.Courses.Where(c => c.Id == changedCourse.Id).Include(c => c.Content).FirstOrDefault();
oldCourse.Name = changedCourse.Name;
oldCourse.Price = changedCourse.Price;
oldCourse.Description = changedCourse.Description;
oldCourse.Duration = changedCourse.Duration;
oldCourse.Content.TextContent = changedCourse.Content.TextContent;
oldCourse.Content.VideoUrl = changedCourse.Content.VideoUrl;
dbContext.SaveChanges();
}
}
public static Course GetCourseFromDb(int?courseId, string name)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
if (courseId != null && courseId > 0)
{
return(dbContext.Courses.Where(c => c.Id == courseId).Include(t => t.Teacher).Include(c => c.Content).FirstOrDefault());
}
else if (!string.IsNullOrWhiteSpace(name))
{
return(dbContext.Courses.Where(c => c.Name == name).Include(t => t.Teacher).Include(c => c.Content).FirstOrDefault());
}
else
{
throw new ArgumentException("Wrong Arguments");
}
}
}
public static void AddCourseToDb(Account account, AddCourseViewModel addCourseViewModel)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
dbContext.Courses.Add(new Course()
{
Name = addCourseViewModel.TeachingCourse.Name,
Duration = addCourseViewModel.TeachingCourse.Duration,
Price = addCourseViewModel.TeachingCourse.Price,
Description = addCourseViewModel.TeachingCourse.Description,
TeacherId = account.Id,
Content = addCourseViewModel.TeachingCourse.Content,
Questions = addCourseViewModel.TeachingCourse.Questions
});
dbContext.SaveChanges();
}
}
public static string PurchaseCourse(int courseId, int accountid, decimal courseCost)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
Subscription dbRecord = dbContext.Subscriptions
.Where(c => c.CourseId == courseId)
.Where(s => s.StudentId == accountid)
.FirstOrDefault();
if (dbRecord != null && dbRecord.Course.Price == 0.0m)
{
return("Course is free");
}
else if (dbRecord != null)
{
return("Course already has purchased!");
}
BankAccount currentUserDeposit = dbContext.BankAccounts.Where(b => b.AccountId == accountid).FirstOrDefault();
if (currentUserDeposit.Deposit < courseCost)
{
return("You dont have enough money!");
}
currentUserDeposit.Deposit -= courseCost;
dbContext.SaveChanges();
BankAccount teacherDeposit = dbContext.BankAccounts
.Where(b => b.AccountId == (dbContext.Courses.Where(c => c.Id == courseId).Select(t => t.TeacherId).FirstOrDefault()))
.FirstOrDefault();
teacherDeposit.Deposit += courseCost;
dbContext.SaveChanges();
dbContext.Subscriptions.Add(new Subscription {
CourseId = courseId, StudentId = accountid
});
dbContext.SaveChanges();
return("Course is successfully purchased!");
}
}
public static Account SignUpTeacher(SignUpTeacherViewModel teacherViewModel)
{
Account createdTeacher;
if (teacherViewModel == null || string.IsNullOrWhiteSpace(teacherViewModel.TeacherAccount.Email) || string.IsNullOrWhiteSpace(teacherViewModel.TeacherAccount.Password) || teacherViewModel.TeacherAccount.BankAccount.Deposit < 0 ||
string.IsNullOrWhiteSpace(teacherViewModel.TeachingCourse.Name) || string.IsNullOrWhiteSpace(teacherViewModel.TeachingCourse.Description) || teacherViewModel.TeachingCourse.Duration < 0)
{
return(null);
}
using (ElearnerContext dbContext = new ElearnerContext())
{
createdTeacher = dbContext.Accounts.Add(teacherViewModel.TeacherAccount);
dbContext.Courses.Add(teacherViewModel.TeachingCourse);
dbContext.SaveChanges();
}
return(createdTeacher);
}
public static void RemoveObjectToDb <T> (T obj) where T : class
{
if (obj == null)
{
throw new ArgumentException("Object cannot be null.");
}
using (ElearnerContext dbContext = new ElearnerContext())
{
try
{
dbContext.Set <T>().Remove(obj);
dbContext.SaveChanges();
}
catch (Exception e)
{
throw e;
}
}
}
public ActionResult Quiz(int Id)
{
if (Session[UserType.LoggedInUser.ToString()] == null)
{
return(RedirectToAction("LogIn", "Authedication"));
}
using (ElearnerContext dbContext = new ElearnerContext())
{
var course = dbContext.Courses.Where(c => c.Id == Id).First();
var questions = dbContext.Questions.Where(q => q.CourseId == course.Id).ToList();
var viewModel = new QuizViewModel
{
Course = course,
Questions = questions,
};
return(View(viewModel));
}
}
public static Account HasAccount(string email, string password)
{
using (ElearnerContext dbContext = new ElearnerContext())
{
Account result = dbContext.Accounts.Include(s => s.Student).FirstOrDefault(a => a.Email == email);
if (result != null)
{
if (result.Email == email && result.Password == password)
{
if (dbContext.Students.Where(s => s.AccountId == result.Id).FirstOrDefault() != null)
{
return(dbContext.Accounts.Include(s => s.Student).Include(b => b.BankAccount).FirstOrDefault(a => a.Email == email));
}
else
{
return(dbContext.Accounts.Include(t => t.Teacher).Include(b => b.BankAccount).FirstOrDefault(a => a.Email == email));
}
}
}
return(null);
}
}
public static List <CommentViewModel> GetCourseComments(int courseId)
{
List <CommentViewModel> comments = new List <CommentViewModel>();
using (ElearnerContext dbContent = new ElearnerContext())
{
var results = dbContent.Subscriptions
.Where(c => c.CourseId == courseId)
.Where(c => c.Comment != null && c.Comment.Trim() != "")
.Include(s => s.Student)
.Select(x => new { Comment = x.Comment, StudentName = x.Student.Name + " " + x.Student.Lastname, Grade = x.Grade })
.ToList();
foreach (var item in results)
{
comments.Add(new CommentViewModel()
{
Comment = item.Comment, StudentName = item.StudentName, Grade = item.Grade
});
}
}
return(comments);
}
public List <Course> Search()
{
if (Order == null)
{
Order = "Name";
}
using (ElearnerContext dbContext = new ElearnerContext())
{
if (LowerBoundPrice == null && UpperBoundPrice == null &&
LowerBoundDuration == null && UpperBoundDuration == null && Query != null)
{
if (Order == "Name")
{
var q1 = dbContext.Courses.Where(x => x.Name.Contains(Query)).OrderBy(x => x.Name);
return((Query.Length == 1) ? q1.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q1.ToList());
}
if (Order == "Price")
{
var q1 = dbContext.Courses.Where(x => x.Name.Contains(Query)).OrderBy(x => x.Price);
return((Query.Length == 1) ? q1.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q1.ToList());
}
if (Order == "Duration")
{
var q1 = dbContext.Courses.Where(x => x.Name.Contains(Query)).OrderBy(x => x.Duration);
return((Query.Length == 1) ? q1.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q1.ToList());
}
return(null);
}
if (LowerBoundPrice != null && UpperBoundPrice != null &&
LowerBoundDuration == null && UpperBoundDuration == null && Query != null)
{
if (Order == "Name")
{
var q2 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice).OrderBy(x => x.Name);
return((Query.Length == 1) ? q2.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q2.ToList());
}
if (Order == "Price")
{
var q2 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice).OrderBy(x => x.Price);
return((Query.Length == 1) ? q2.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q2.ToList());
}
if (Order == "Duration")
{
var q2 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice).OrderBy(x => x.Duration);
return((Query.Length == 1) ? q2.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q2.ToList());
}
return(null);
}
if (LowerBoundPrice != null && UpperBoundPrice != null &&
LowerBoundDuration != null && UpperBoundDuration != null && Query != null)
{
if (Order == "Name")
{
var q3 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Name);
return((Query.Length == 1) ? q3.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q3.ToList());
}
if (Order == "Price")
{
var q3 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Price);
return((Query.Length == 1) ? q3.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q3.ToList());
}
if (Order == "Duration")
{
var q3 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Duration);
return((Query.Length == 1) ? q3.ToList().Where(x => x.Name[0].ToString().ToUpper() == Query[0].ToString().ToUpper()).ToList() : q3.ToList());
}
return(null);
}
if (LowerBoundPrice == null && UpperBoundPrice == null &&
LowerBoundDuration != null && UpperBoundDuration != null && Query == null)
{
if (Order == "Name")
{
var q4 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Name);
return(q4.ToList());
}
if (Order == "Price")
{
var q4 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Price);
return(q4.ToList());
}
if (Order == "Duration")
{
var q4 = dbContext.Courses.Where(x => x.Name.Contains(Query) && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Duration);
return(q4.ToList());
}
return(null);
}
if (LowerBoundPrice != null && UpperBoundPrice != null &&
LowerBoundDuration == null && UpperBoundDuration == null && Query == null)
{
if (Order == "Name")
{
var q5 = dbContext.Courses.Where(x => x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice).OrderBy(x => x.Name);
return(q5.ToList());
}
if (Order == "Price")
{
var q5 = dbContext.Courses.Where(x => x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice).OrderBy(x => x.Price);
return(q5.ToList());
}
if (Order == "Duration")
{
var q5 = dbContext.Courses.Where(x => x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice).OrderBy(x => x.Duration);
return(q5.ToList());
}
return(null);
}
if (LowerBoundPrice != null && UpperBoundPrice != null &&
LowerBoundDuration != null && UpperBoundDuration != null && Query == null)
{
if (Order == "Name")
{
var q6 = dbContext.Courses.Where(x => x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Name);
return(q6.ToList());
}
if (Order == "Price")
{
var q6 = dbContext.Courses.Where(x => x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Price);
return(q6.ToList());
}
if (Order == "Duration")
{
var q6 = dbContext.Courses.Where(x => x.Price >= LowerBoundPrice && x.Price <= UpperBoundPrice && x.Duration >= LowerBoundDuration && x.Duration <= UpperBoundDuration).OrderBy(x => x.Duration);
return(q6.ToList());
}
return(null);
}
else
{
return(null);
}
}
}