Esempio n. 1
0
        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");
                }
            }
        }
Esempio n. 2
0
        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);
            }
        }
Esempio n. 3
0
        public static List <Teacher> GetTeachers()
        {
            using (ElearnerContext dbContext = new ElearnerContext())
            {
                List <Teacher> teacherList = dbContext.Teachers.Include(a => a.Account).ToList();

                return(teacherList);
            }
        }
Esempio n. 4
0
 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();
     }
 }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        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);
        }
Esempio n. 7
0
        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);
        }
Esempio n. 8
0
        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);
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
 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);
     }
 }
Esempio n. 11
0
        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);
            }
        }
Esempio n. 12
0
        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);
            }
        }
Esempio n. 13
0
        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();
        }
Esempio n. 14
0
        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);
        }
Esempio n. 15
0
        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);
        }
Esempio n. 16
0
        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();
            }
        }
Esempio n. 17
0
        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();
            }
        }
Esempio n. 18
0
 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");
         }
     }
 }
Esempio n. 19
0
        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();
            }
        }
Esempio n. 20
0
        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!");
            }
        }
Esempio n. 21
0
        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);
        }
Esempio n. 22
0
        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;
                }
            }
        }
Esempio n. 23
0
        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));
            }
        }
Esempio n. 24
0
        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);
            }
        }
Esempio n. 25
0
        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);
        }
Esempio n. 26
0
        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);
                }
            }
        }