public ActionResult EditUser() { if (Session["UserId"] == null) { return(RedirectToAction("Login")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { var usuario = db.Usuarios.Where(u => u.Id == usuarioId).FirstOrDefault(); if (usuario != null) { return(View(usuario)); } else { return(View("Error")); } } }
/// <summary> /// Lấy thông tin biến thể màu /// </summary> /// <param name="productID"></param> /// <param name="productVariationID"></param> /// <returns></returns> public string getColor(int productID, int productVariationID) { using (var con = new SQLServerContext()) { var variations = con.tbl_ProductVariable .Where(x => x.ProductID == productID) .Where(x => x.ID == productVariationID) .FirstOrDefault(); if (variations == null) { return(String.Empty); } var color = con.tbl_ProductVariableValue .Where(x => x.ProductVariableID == variations.ID) .Join( con.tbl_VariableValue.Where(x => x.VariableID == (int)VariableType.Color), p => p.VariableValueID, c => c.ID, (p, c) => c ) .FirstOrDefault(); return(color == null ? String.Empty : color.VariableValue); } }
/// <summary> /// Thực thi đệ quy để lấy tất cả category theo nhánh parent /// </summary> /// <param name="con"></param> /// <param name="parent"></param> /// <returns></returns> private List <ProductCategoryModel> getCategoryChild(SQLServerContext con, ProductCategoryModel parent) { var result = new List <ProductCategoryModel>(); result.Add(parent); var child = con.tbl_Category .Where(x => x.ParentID.Value == parent.id) .Select(x => new ProductCategoryModel() { id = x.ID, name = x.CategoryName, description = x.CategoryDescription, slug = x.Slug }) .ToList(); if (child.Count > 0) { foreach (var id in child) { result.AddRange(getCategoryChild(con, id)); } } return(result); }
public PostClone Update(PostClone data) { using (var con = new SQLServerContext()) { var post = con.PostClone.Where(o => o.ID == data.ID).FirstOrDefault(); if (post != null) { post.PostPublicID = data.PostPublicID; post.Web = data.Web; post.PostWebID = data.PostWebID; post.CategoryID = data.CategoryID; post.CategoryName = data.CategoryName; post.Title = data.Title; post.Summary = data.Summary; post.Content = data.Content; post.Thumbnail = data.Thumbnail; post.CreatedBy = data.CreatedBy; post.CreatedDate = data.CreatedDate; post.ModifiedBy = data.ModifiedBy; post.ModifiedDate = data.ModifiedDate; con.SaveChanges(); return(post); } return(null); } }
public ActionResult PerfilUsuario(int usuarioId) { if (usuarioId == 1) { return(RedirectToAction("Index")); } using (var db = new SQLServerContext()) { var usuario = db.Usuarios.Include("UsuarioCalificacion").SingleOrDefault(u => u.Id == usuarioId); var calificaciones = db.UsuariosXCalificaciones.Where(uc => uc.Usuario.Id == usuario.Id).Select(x => x.Puntaje); if (usuario == null) { return(RedirectToAction("Index")); } Double calificacionPromedio = 0; if (calificaciones.Count() > 0) { calificacionPromedio = calificaciones.Average(); } ViewBag.CalificacionPromedio = Math.Round(calificacionPromedio, 2); return(View(usuario)); } }
public ActionResult PagarContratacion(int contratacionId) { if (Session["UserId"] == null) { return(RedirectToAction("Login", "Account")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { Contratacion contratacion = db.Contrataciones .Include("Publicacion") .Include("FechaContratacion") .FirstOrDefault(c => c.Id == contratacionId); Usuario usuario = db.Usuarios.Find(usuarioId); MP mp = new MP(); String url = mp.PagarContratacion(usuario, contratacion); return(Redirect(url)); } }
/// <summary> /// Tìm các category thuộc nhánh slug /// </summary> /// <param name="slug"></param> /// <returns></returns> public List <ProductCategoryModel> getCategoryChild(string slug) { using (var con = new SQLServerContext()) { var parent = con.tbl_Category .Where(x => (!String.IsNullOrEmpty(slug) && x.Slug == slug) || (String.IsNullOrEmpty(slug) && x.CategoryLevel == 0) ) .Select(x => new ProductCategoryModel() { id = x.ID, name = x.CategoryName, description = x.CategoryDescription, slug = x.Slug }) .FirstOrDefault(); if (parent != null) { return(getCategoryChild(con, parent)); } else { return(null); } } }
public ActionResult DisablePublication(int publicacionId) { if (Session["UserId"] == null) { return(RedirectToAction("Login", "Account")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { Publicacion publicacion = db.Publicaciones.Find(publicacionId); if (publicacion.Usuario.Id != usuarioId || publicacion.Estado == "Desactivada") { return(View("Error")); } publicacion.Estado = "Desactivada"; publicacion.Visible = false; publicacion.FechaDeModificacion = Convert.ToDateTime(DateTime.Now); db.SaveChanges(); return(RedirectToAction("Publicaciones", "Account")); } }
public ActionResult CrearConsulta(Consulta consulta, FormCollection form) { if (Session["UserId"] == null) { return(Json("NotAllowed", JsonRequestBehavior.AllowGet)); } else { int usuarioId = Int32.Parse(Session["UserId"].ToString()); var publicacionId = Int32.Parse(form["publiId"]); using (var db = new SQLServerContext()) { var publicacion = db.Publicaciones.SingleOrDefault(p => p.Id == publicacionId); var usuario = db.Usuarios.SingleOrDefault(u => u.Id == usuarioId); consulta.Usuario = usuario; consulta.Publicacion = publicacion; consulta.Visible = true; db.Consultas.Add(consulta); db.SaveChanges(); return(Json("guardado", JsonRequestBehavior.AllowGet)); } } }
// MIS CONTRATACIONES public ActionResult GetContratacionesDelUsuario() { if (Session["UserId"] == null) { return(RedirectToAction("Login")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { var contrataciones = db.Contrataciones .Include("Publicacion") .Include("Usuario") .Include("Usuario.UsuarioCalificacion") .Include("FechaContratacion") .Include("Pago") .Where(c => c.Publicacion.Usuario.Id == usuarioId && (c.Estado == "Contratada" || c.Estado == "Pendiente" || c.Estado == "Cancelada" || c.Estado == "Finalizada")) .OrderByDescending(c => c.Id) .ToList(); return(View(contrataciones)); } }
/// <summary> /// Lấy danh sách hình ảnh của sản phẩm /// </summary> /// <param name="productID"></param> /// <returns></returns> public List <string> getImageListByProduct(int productID) { using (var con = new SQLServerContext()) { // Lấy hình ảnh của sản phẩm cha //var imageProduct = con.tbl_Product // .Where(x => x.ID == productID) // .Where(x => !String.IsNullOrEmpty(x.ProductImage)) // .Select(x => new { image = x.ProductImage }) // .ToList(); // Lấy hình anh trong bảng image var images = con.tbl_ProductImage.Where(x => x.ProductID == productID) .Select(x => new { image = x.ProductImage }) .ToList(); //var images = imageSource // .Select(x => x.image) // .Distinct() // .ToList(); if (images.Count == 0) { return(new List <string>()); } else { return(images.Select(x => x.image).ToList()); } } }
public ActionResult GetConsultasDelUsuario() { if (Session["UserId"] == null) { return(RedirectToAction("Login")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { var consultas = db.Consultas .Include("Publicacion") .Include("Usuario") .Where(c => c.Publicacion.Usuario.Id == usuarioId && c.Respuesta == null && c.Visible == true) .OrderBy(c => c.Id) .ToList(); return(View(consultas)); } }
/// <summary> /// Lấy thông tin biến size /// </summary> /// <param name="productID"></param> /// <param name="productVariationID"></param> /// <returns></returns> public string getSize(int productID, int productVariationID) { using (var con = new SQLServerContext()) { var variations = con.tbl_ProductVariable .Where(x => x.ProductID == productID) .Where(x => x.ID == productVariationID) .FirstOrDefault(); if (variations == null) { return(String.Empty); } var size = con.tbl_ProductVariableValue .Where(x => x.ProductVariableID == variations.ID) .Join( con.tbl_VariableValue.Where(x => x.VariableID == (int)VariableType.Size), p => p.VariableValueID, s => s.ID, (p, s) => s ) .FirstOrDefault(); return(size == null ? String.Empty : size.VariableValue); } }
public ActionResult Pagar(Publicacion publicacion) { if (Session["UserId"] == null) { return(RedirectToAction("Login", "Account")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { Usuario usuario = db.Usuarios.Find(usuarioId); MP mp = new MP(); String url = mp.PagarPromocion(usuario, publicacion); return(Redirect(url)); } }
public ActionResult Login(Usuario usuario) { using (var db = new SQLServerContext()) { var rUsuario = db.Roles.SingleOrDefault(r => r.Nombre == "USER"); var rAdmin = db.Roles.SingleOrDefault(r => r.Nombre == "ADMIN"); var usuarioAEncontrar = db.Usuarios.FirstOrDefault(u => u.Email == usuario.Email); if (usuarioAEncontrar != null && Crypto.VerifyHashedPassword(usuarioAEncontrar.Password, usuario.Password)) { if (usuarioAEncontrar.Roles.Contains(rUsuario)) { Session["UserId"] = usuarioAEncontrar.Id; Session["Email"] = usuario.Email; return(RedirectToAction("Index", "Home")); } else if (usuarioAEncontrar.Roles.Contains(rAdmin)) { Session["UserId"] = usuarioAEncontrar.Id; Session["Email"] = usuario.Email; Session["isAdmin"] = "true"; return(RedirectToAction("Index", "Admin")); } } else { ModelState.AddModelError("", "Los datos ingresados son incorrectos."); } } return(View()); }
public ActionResult Publicaciones() { if (Session["UserId"] == null) { return(RedirectToAction("Login")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { var publicaciones = db.Publicaciones.Include("Categoria").Where(p => p.Estado != "Desactivada" && p.Usuario.Id == usuarioId).ToList(); if (publicaciones != null) { return(View(publicaciones)); } else { return(View("Error")); } } }
public ActionResult PromocionarPublicacion(int publicacionId) { if (Session["UserId"] == null) { return(RedirectToAction("Login", "Account")); } if (Session["isAdmin"] != null) { return(View("NotAuthorized")); } int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { Publicacion publicacion = db.Publicaciones.Find(publicacionId); if (publicacion == null || publicacion.Usuario.Id != usuarioId || publicacion.Estado == "Desactivada" || publicacion.Promocionada == true) { return(View("Error")); } else { return(RedirectToAction("Pagar", "MercadoPago", publicacion)); } } }
public ActionResult CancelarContratacion(FormCollection form) { if (Session["UserId"] == null) { return(Json("NotAllowed", JsonRequestBehavior.AllowGet)); } else { int contratacionId = Int32.Parse(form["id"].ToString()); using (var db = new SQLServerContext()) { var contratacion = db.Contrataciones.Include("FechaContratacion").SingleOrDefault(c => c.Id == contratacionId); foreach (FechaContratacion fecha in contratacion.FechaContratacion) { if (!FechaMayorA96Horas(fecha)) { return(Json("Error")); } } LiberarDisponibilidad(contratacion); contratacion.Estado = "Cancelada"; db.SaveChanges(); return(Json("cancelada", JsonRequestBehavior.AllowGet)); } } }
public ActionResult BuscarPublicaciones(String termino, Double precioMinimo = 0, Double precioMaximo = 0) { using (var db = new SQLServerContext()) { // ÚNICAMENTE FILTRA POR PRECIO MÍNIMO if (precioMinimo > 0 && precioMaximo == 0) { var publicacionesPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == true && p.Titulo.ToLower().Contains(termino.ToLower()) && p.Precio >= precioMinimo) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicacionesNoPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == false && p.Titulo.ToLower().Contains(termino.ToLower()) && p.Precio >= precioMinimo) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicaciones = publicacionesPromocionadas.Concat(publicacionesNoPromocionadas).ToList(); ViewBag.Termino = termino; return(View("BuscadorPublicaciones", publicaciones)); } // ÚNICAMENTE FILTRA POR PRECIO MÁXIMO else if (precioMaximo > 0 && precioMinimo == 0) { var publicacionesPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == true && p.Titulo.ToLower().Contains(termino.ToLower()) && precioMaximo <= p.Precio) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicacionesNoPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == false && p.Titulo.ToLower().Contains(termino.ToLower()) && precioMaximo <= p.Precio) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicaciones = publicacionesPromocionadas.Concat(publicacionesNoPromocionadas).ToList(); ViewBag.Termino = termino; return(View("BuscadorPublicaciones", publicaciones)); } // FILTRA POR PRECIO MÍNIMO Y PRECIO MÁXIMO else if (precioMaximo > 0 && precioMinimo > 0) { var publicacionesPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == true && p.Titulo.ToLower().Contains(termino.ToLower()) && p.Precio >= precioMinimo && precioMaximo <= p.Precio) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicacionesNoPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == false && p.Titulo.ToLower().Contains(termino.ToLower()) && p.Precio >= precioMinimo && precioMaximo <= p.Precio) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicaciones = publicacionesPromocionadas.Concat(publicacionesNoPromocionadas).ToList(); ViewBag.Termino = termino; return(View("BuscadorPublicaciones", publicaciones)); } // SIN FILTRO DE PRECIO else { var publicacionesPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == true && p.Titulo.ToLower().Contains(termino.ToLower())) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicacionesNoPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == false && p.Titulo.ToLower().Contains(termino.ToLower())) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicaciones = publicacionesPromocionadas.Concat(publicacionesNoPromocionadas).ToList(); ViewBag.Termino = termino; return(View("BuscadorPublicaciones", publicaciones)); } } }
public ActionResult EditUser(Usuario usuario, FormCollection form) { ModelState.Remove("TipoDocumento"); ModelState.Remove("Password"); ModelState.Remove("ConfirmPassword"); if (ModelState.IsValid) { String tipoDeIdentificacion = form["TipoDeIdentificacion"]; int usuarioId = Int32.Parse(Session["UserId"].ToString()); using (var db = new SQLServerContext()) { // E-MAIL DE LA BASE var usuarioAEditar = db.Usuarios.SingleOrDefault(u => u.Id == usuarioId); // E-MAILS A COMPARAR var emailAbuscar = db.Usuarios.Where(u => u.Email == usuario.Email).FirstOrDefault(); // VALIDA QUE EL E-MAIL INGRESADO NO EXISTA EN LA BASE DE DATOS if (emailAbuscar != null && usuario.Email != usuarioAEditar.Email) { ViewBag.Message = "El E-Mail ingresado ya existe."; return(View(usuario)); } // VALIDA QUE EL USUARIO SEA MAYOR DE EDAD if (usuario.FechaDeNacimiento.Value.AddYears(18) > DateTime.Today) { ViewBag.Message = "La edad ingresada debe ser mayor o igual a 18 años."; return(View(usuario)); } usuarioAEditar.Nombre = usuario.Nombre; usuarioAEditar.Apellido = usuario.Apellido; usuarioAEditar.TipoDocumento = tipoDeIdentificacion; usuarioAEditar.Documento = usuario.Documento; usuarioAEditar.FechaDeNacimiento = usuario.FechaDeNacimiento; usuarioAEditar.Telefono = usuario.Telefono; if (usuario.Email != usuarioAEditar.Email) { usuarioAEditar.Email = usuario.Email; } usuarioAEditar.ConfirmPassword = usuarioAEditar.Password; db.SaveChanges(); ModelState.Clear(); ViewBag.Message = "Usted ha modificado sus datos con éxito."; return(RedirectToAction("UserInfo")); } } return(View(usuario)); }
public ActionResult CalificarPrestatarioAPrestadorYAPublicacion(FormCollection formulario) { if (Session["UserId"] == null) { return(Json("NotAuthorized")); } int calificacionPrestador = Int32.Parse(formulario["calificacionPrestador"]); String comentarioPrestador = formulario["comentarioPrestador"]; int calificacionPublicacion = Int32.Parse(formulario["calificacionPublicacion"]); String comentarioPublicacion = formulario["comentarioPublicacion"]; int usuarioIdACalificar = Int32.Parse(formulario["usuarioACalificar"]); int publicacionId = Int32.Parse(formulario["publicacionId"]); int contratacionId = Int32.Parse(formulario["contratacionId"]); if (calificacionPublicacion > 5 || calificacionPublicacion < 1) { return(Json("Error")); } if (calificacionPrestador > 5 || calificacionPrestador < 1) { return(Json("Error")); } using (var db = new SQLServerContext()) { var usuario = db.Usuarios.Find(usuarioIdACalificar); var publicacion = db.Publicaciones.Find(publicacionId); var contratatacion = db.Contrataciones.Find(contratacionId); var usuarioCalificacion = new UsuarioCalificacion { Puntaje = calificacionPrestador, Comentario = comentarioPrestador, Usuario = usuario, Contratacion = contratatacion }; var publicacionCalificacion = new PublicacionCalificacion { Puntaje = calificacionPublicacion, Comentario = comentarioPublicacion, Publicacion = publicacion, Contratacion = contratatacion }; db.UsuariosXCalificaciones.Add(usuarioCalificacion); db.PublicacionesXCalificaciones.Add(publicacionCalificacion); db.SaveChanges(); return(Json("OK")); } }
public IActionResult SelectionVideo() { List <Video> listVideos = new List <Video>(); using (SQLServerContext db = new SQLServerContext()) { listVideos = db.Videos.ToList <Video>(); } return(View(listVideos)); }
public IActionResult Video(int id) { Video model; using (SQLServerContext db = new SQLServerContext()) { model = db.Videos.Where(x => x.Id == id).FirstOrDefault(); } return(View(model)); }
public IActionResult SelectionModule() { List <Module> listModules = new List <Module>(); using (SQLServerContext db = new SQLServerContext()) { listModules = db.Modules.ToList(); } return(View(listModules)); return(View()); }
// Example for one report. Need to implements DTOs private IReportData DriversVehiclesInfo() { using (var dbContext = new SQLServerContext()) { var driversVehicle = dbContext.Vehicles.Select(x => new { VehicleType = x.Type.Name, DriverName = x.Driver.FirstName + " " + x.Driver.LastName }).ToArray(); } return(null); }
public ActionResult Index() { using (var db = new SQLServerContext()) { var publicacionesPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == true) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicacionesNoPromocionadas = db.Publicaciones.Where(p => p.Visible == true && p.Estado == "Aprobada" && p.Promocionada == false) .OrderByDescending(p => p.FechaDeModificacion).ToList(); var publicaciones = publicacionesPromocionadas.Concat(publicacionesNoPromocionadas).ToList(); return(View(publicaciones)); } }
public ActionResult Register(Usuario usuario, FormCollection form) { ModelState.Remove("TipoDocumento"); if (ModelState.IsValid) { using (var db = new SQLServerContext()) { var usuarioAEncontrar = db.Usuarios.SingleOrDefault(u => u.Email == usuario.Email); String tipoDeIdentificacion = form["TipoDeIdentificacion"]; // VALIDA QUE EL E-MAIL INGRESADO NO EXISTA EN LA BASE DE DATOS if (usuarioAEncontrar != null) { ViewBag.Message = "El E-Mail que quiere registrar ya existe."; return(View()); } // VALIDA QUE EL USUARIO SEA MAYOR DE EDAD if (usuario.FechaDeNacimiento.Value.AddYears(18) > DateTime.Today) { ViewBag.Message = "Debe tener más de 18 años para poder registrarse en la plataforma."; return(View()); } usuario.TipoDocumento = tipoDeIdentificacion; usuario.Activo = true; usuario.Password = Crypto.HashPassword(usuario.Password); if (Crypto.VerifyHashedPassword(usuario.Password, usuario.ConfirmPassword)) { usuario.ConfirmPassword = usuario.Password; } db.Usuarios.Add(usuario); Rol rol = db.Roles.SingleOrDefault(r => r.Nombre == "USER"); usuario.Roles.Add(rol); db.SaveChanges(); ModelState.Clear(); ViewBag.Message = "Usted se ha registrado correctamente."; return(View()); } } return(View()); }
public List <PostPublicImage> getPostImageByPostID(int postPublicID) { using (var con = new SQLServerContext()) { var data = con.PostPublicImage.Where(x => x.PostID == postPublicID).OrderByDescending(x => x.ID).ToList(); if (data == null) { return(null); } return(data); } }
public List <ProductVideo> getVideoByProductID(int productID) { using (var con = new SQLServerContext()) { var data = con.ProductVideo.Where(x => x.ProductId == productID).ToList(); if (data == null) { return(null); } return(data); } }
/// <summary> /// Lấy thông tin sản phẩm theo SKU /// </summary> /// <param name="SKU"></param> /// <returns></returns> public ProductModel getProductBySKU(string SKU) { using (var con = new SQLServerContext()) { // Kiểm tra có sản phẩm không var product = con.tbl_Product.Where(x => x.ProductSKU == SKU).FirstOrDefault(); if (product == null) { return(null); } return(getProductBySlug(product.Slug)); } }
static void Main() { Database.SetInitializer( new MigrateDatabaseToLatestVersion<SQLServerContext, Configuration>()); var context = new SQLServerContext(); // SeedDatabase(context); - Initial Seed // 1. Lists all students and their homework submissions. // Select only their names and for each homework - content and content-type. var students = context.Students.Select(s => new { Name = s.Name, Homeworks = s.Homeworks.Select(h => new { Content = h.Content, ContentType = h.ContentType }) }); foreach (var student in students) { Console.WriteLine("--- " + student.Name); foreach (var homework in student.Homeworks) { Console.WriteLine(" - " + homework.Content + " - " + homework.ContentType); } } // 2. List all courses with their corresponding resources. // Select the course name and description and everything for each resource. // Order the courses by start date (ascending), then by end date (descending). var courses = context.Courses .OrderBy(c => c.StartDate) .ThenByDescending(c => c.EndDate) .Select(c => new { Name = c.Name, Description = c.Description, Resources = c.Resources }); Console.WriteLine(); foreach (var course in courses) { Console.WriteLine(" --- " + course.Name + " -- " + course.Description); foreach (var resource in course.Resources) { Console.WriteLine(" - " + resource.Name + " - " + resource.URL + " - " + resource.ResourceType); } } // 3. List all courses with more than 5 resources. // Order them by resources count (descending), then by start date (descending). // Select only the course name and the resource count. var coursesWithMoreThan5Resources = context.Courses .Where(c => c.Resources.Count > 5) .OrderByDescending(c => c.Resources.Count) .ThenByDescending(c => c.StartDate) .Select(c => new { Name = c.Name, ResourcesCount = c.Resources.Count }); Console.WriteLine(); foreach (var course in coursesWithMoreThan5Resources) { Console.WriteLine(" --- " + course.Name + " - " + course.ResourcesCount); } // 4. List all courses which were active on a given date, // and for each course count the number of students enrolled. // Select the course name, start and end date, course duration // and number of students enrolled. // Order the results by the number of students enrolled (in descending order), // then by duration (descending). DateTime activeDate = new DateTime(2015, 07, 20); var activeCourses = context.Courses .Where(c => c.StartDate < activeDate && c.EndDate > activeDate) .OrderByDescending(c => c.Students.Count) .ThenByDescending(c => EntityFunctions.DiffDays(c.StartDate, c.EndDate)) .Select(c => new { Name = c.Name, StartDate = c.StartDate, EndDate = c.EndDate, Duration = EntityFunctions.DiffDays(c.StartDate, c.EndDate), StudentsEnrolledCount = c.Students.Count }); Console.WriteLine(); foreach (var activeCourse in activeCourses) { Console.WriteLine(" --- {0} - {1} - {2} - Duration: {3} ---", activeCourse.Name, activeCourse.StartDate.ToString("dd/MM/yyyy"), activeCourse.EndDate.ToString("dd/MM/yyyy"), activeCourse.Duration); } // 5. For each student, calculate the number of courses she’s enrolled in, // the total price of these courses and the average price per course // for the student. Select the student name, number of courses, // total price and average price. // Order the results by total price (descending), // then by number of courses (descending) // and then by the student’s name (ascending). var studentStatistics = context.Students .Select(s => new { Name = s.Name, CoursesCount = s.Courses.Count, CoursesTotalPrice = s.Courses.Sum(c => c.Price), CoursesAveragePrice = s.Courses.Average(c => c.Price) }) .OrderByDescending(s => s.CoursesTotalPrice) .ThenByDescending(s => s.CoursesCount) .ThenBy(s => s.Name); Console.WriteLine(); foreach (var studentStatistic in studentStatistics) { Console.WriteLine(" --- {0} - Courses Count: {1}", studentStatistic.Name, studentStatistic.CoursesCount); Console.WriteLine(" -- Total Price: {0} - Average Price: {1}", studentStatistic.CoursesTotalPrice, studentStatistic.CoursesAveragePrice); } }
private static void SeedDatabase(SQLServerContext context) { var pesho = new Student { Birthday = new DateTime(1994, 06, 5), PhoneNumber = "0892244992", RegistrationDate = new DateTime(2014, 06, 11), Name = "Pesho" }; var gosho = new Student { Birthday = new DateTime(1992, 07, 1), PhoneNumber = "0892244993", RegistrationDate = new DateTime(2013, 06, 11), Name = "Gosho" }; var minka = new Student { Birthday = new DateTime(1984, 06, 5), PhoneNumber = "0882244992", RegistrationDate = new DateTime(2015, 06, 11), Name = "Minka" }; var penka = new Student { Birthday = new DateTime(1993, 06, 5), PhoneNumber = "0893244992", RegistrationDate = new DateTime(2013, 06, 11), Name = "Minka" }; var menka = new Student { Birthday = new DateTime(1991, 06, 5), PhoneNumber = "0892245992", RegistrationDate = new DateTime(2012, 06, 11), Name = "Menka" }; var databases = new Course() { Description = "really cool", EndDate = new DateTime(2015, 07, 30), Name = "Databases", Price = 50.55m, StartDate = new DateTime(2015, 06, 15) }; var databaseApps = new Course() { Description = "really cool course", EndDate = new DateTime(2015, 08, 30), Name = "Database Apps", Price = 100.55m, StartDate = new DateTime(2015, 07, 15) }; var systemAdministration = new Course() { Description = "really hard course", EndDate = new DateTime(2014, 08, 30), Name = "System Administration", Price = 200.55m, StartDate = new DateTime(2015, 07, 15) }; databases.Students.Add(pesho); databases.Students.Add(gosho); databases.Students.Add(minka); databaseApps.Students.Add(menka); databaseApps.Students.Add(penka); databaseApps.Students.Add(minka); systemAdministration.Students.Add(pesho); systemAdministration.Students.Add(penka); systemAdministration.Students.Add(gosho); systemAdministration.Students.Add(menka); var peshoHomework = new Homework() { Content = "no homework y'all", ContentType = ContentType.Other, Course = systemAdministration, Student = pesho, SubmissionDate = new DateTime(2015, 02, 20) }; var goshoHomework = new Homework() { Content = "I did it", ContentType = ContentType.PDF, Course = databases, Student = gosho, SubmissionDate = new DateTime(2015, 07, 20) }; var minkaHomework = new Homework() { Content = "Ain't nobody got time fo' this", ContentType = ContentType.ZIP, Course = databaseApps, Student = minka, SubmissionDate = new DateTime(2015, 08, 20) }; var menkaHomework = new Homework() { Content = "Ain't nobody got time fo' this yeah", ContentType = ContentType.ZIP, Course = systemAdministration, Student = menka, SubmissionDate = new DateTime(2015, 07, 25) }; var penkaHomework = new Homework() { Content = "Ain't nobody got time fo' this yeah", ContentType = ContentType.ZIP, Course = databaseApps, Student = penka, SubmissionDate = new DateTime(2015, 07, 25) }; var databasesPresentation = new Resource() { Course = databases, Name = "Presentation Databases", URL = "http://example.com", ResourceType = ResourceType.Presentation }; var databasesHomework = new Resource() { Course = databases, Name = "Homework Databases", URL = "http://example.com", ResourceType = ResourceType.Document }; var databaseAppsPresentation = new Resource() { Course = databaseApps, Name = "Presentation Database Apps", URL = "http://example.com", ResourceType = ResourceType.Presentation }; var databaseAppsHomework = new Resource() { Course = databaseApps, Name = "Homework Database Apps", URL = "http://example.com", ResourceType = ResourceType.Document }; var systemAdministrationPresentation = new Resource() { Course = systemAdministration, Name = "Presentation System Administration", URL = "http://example.com", ResourceType = ResourceType.Presentation }; var systemAdministrationHomework = new Resource() { Course = systemAdministration, Name = "Homework System Administration", URL = "http://example.com", ResourceType = ResourceType.Document }; context.Students .AddOrUpdate(pesho, gosho, minka, penka, menka); context.Courses .AddOrUpdate(databases, databaseApps, systemAdministration); context.Homeworks .AddOrUpdate( peshoHomework, goshoHomework, minkaHomework, menkaHomework, penkaHomework); context.Resources.AddOrUpdate( databasesPresentation, databasesHomework, databaseAppsPresentation, databaseAppsHomework, systemAdministrationPresentation, systemAdministrationHomework ); }