public ActionResult Assignatura(int Id) { Log.Info("Llistat de documents de l'assignatura " + Id); ViewBag.Id = Id; using (MySqlConnection connection = new MySqlConnection(ConnectionString)) { connection.Open(); MySqlCommand command = new MySqlCommand("SELECT Nom FROM Assignatures WHERE Id = @Id", connection); command.Parameters.AddWithValue("@Id", Id); MySqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { ViewBag.Nom = reader.GetString(reader.GetOrdinal("Nom")); reader.Close(); command = new MySqlCommand("SELECT d.Id, d.Nom, d.Tipus, d.DataAfegit, d.IdUsuari, u.Username, d.IdAssignatura, a.Nom AS NomAssignatura, c.Nom AS NomCarrera, " + "IF(EXISTS(SELECT v.IdDocument FROM Valoracions v WHERE v.IdDocument = d.Id), (SELECT AVG(v.Valoracio) FROM Valoracions v WHERE v.IdDocument = d.Id), 0) AS Valoracio " + "FROM Documents d, Usuaris u, Assignatures a, Carreres c " + "WHERE d.IdUsuari = u.Id AND d.IdAssignatura = a.Id AND a.IdCarrera = c.Id AND d.IdAssignatura = @IdAssignatura", connection); command.Parameters.AddWithValue("@IdAssignatura", Id); List<DocumentLlistat> resultats = new List<DocumentLlistat>(); reader = command.ExecuteReader(); while (reader.Read()) { DocumentLlistat d = new DocumentLlistat(); d.Id = reader.GetInt32(reader.GetOrdinal("Id")); d.Nom = reader.GetString(reader.GetOrdinal("Nom")); d.Tipus = (TipusDocument)Enum.Parse(typeof(TipusDocument), reader.GetString(reader.GetOrdinal("Tipus"))); d.DataAfegit = reader.GetDateTime(reader.GetOrdinal("DataAfegit")); d.IdUsuari = reader.GetInt32(reader.GetOrdinal("IdUsuari")); d.Username = reader.GetString(reader.GetOrdinal("Username")); d.IdAssignatura = reader.GetInt32(reader.GetOrdinal("IdAssignatura")); d.NomAssignatura = reader.GetString(reader.GetOrdinal("NomAssignatura")); d.NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera")); d.Valoracio = reader.GetDouble(reader.GetOrdinal("Valoracio")); resultats.Add(d); } Log.Info("Total de documents: " + resultats.Count); return View(resultats); } else { Log.Warn("ID de l'assignatura inexistent: " + Id); ViewBag.Error = Lang.GetString(base.lang, "Error_id_assignatura"); } return View(); } }
public ActionResult Cercar(string termesCerca) { termesCerca = termesCerca.Trim(); if (termesCerca.Length < 3) { Log.Info("Cerca amb menys de 3 caracters: " + termesCerca); ViewBag.Error = Lang.GetString(lang, "Cerca_menys_3_caracters"); return View(); } using (var connection = new MySqlConnection(ConnectionString)) { Log.Info("Cercant termes: " + termesCerca); connection.Open(); string[] termesCercaArray = termesCerca.Split(' '); var documents = new List<DocumentLlistat>(); //Documents var query = "SELECT d.Id, d.Nom, d.Tipus, d.DataAfegit, d.IdUsuari, u.Username, d.IdAssignatura, a.Nom AS NomAssignatura, c.Nom AS NomCarrera," + " IF(EXISTS(SELECT v.IdDocument FROM Valoracions v WHERE v.IdDocument = d.Id), (SELECT AVG(v.Valoracio) FROM Valoracions v WHERE v.IdDocument = d.Id), 0) AS Valoracio " + " FROM Documents d, Usuaris u, Assignatures a, Carreres c" + " WHERE d.IdUsuari = u.Id AND d.IdAssignatura = a.Id AND a.IdCarrera = c.Id"; for (var i = 0; i < termesCercaArray.Length; i++) { query += " AND d.Nom LIKE @terme" + i; //Afegim els termes parametritzats per evitar SQL injection } query += " ORDER BY DataAfegit DESC LIMIT 100"; var command = new MySqlCommand(query, connection); for (var i = 0; i < termesCercaArray.Length; i++) { command.Parameters.AddWithValue("@terme" + i, "%" + termesCercaArray[i] + "%"); } MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { var d = new DocumentLlistat { Id = reader.GetInt32(reader.GetOrdinal("Id")), Nom = reader.GetString(reader.GetOrdinal("Nom")), Tipus = (TipusDocument)Enum.Parse(typeof(TipusDocument), reader.GetString(reader.GetOrdinal("Tipus"))), DataAfegit = reader.GetDateTime(reader.GetOrdinal("DataAfegit")), IdUsuari = reader.GetInt32(reader.GetOrdinal("IdUsuari")), Username = reader.GetString(reader.GetOrdinal("Username")), IdAssignatura = reader.GetInt32(reader.GetOrdinal("IdAssignatura")), NomAssignatura = reader.GetString(reader.GetOrdinal("NomAssignatura")), NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera")), Valoracio = reader.GetDouble(reader.GetOrdinal("Valoracio")), }; documents.Add(d); } reader.Close(); //Assignatures var assignatures = new List<Assignatura>(); query = "SELECT a.Id, a.Nom, a.Curs, c.Id AS IdCarrera, c.Nom AS NomCarrera FROM Assignatures a, Carreres c" + " WHERE a.IdCarrera = c.Id"; for (var i = 0; i < termesCercaArray.Length; i++) { query += " AND a.Nom LIKE @terme" + i; //Afegim els termes parametritzats per evitar SQL injection } command = new MySqlCommand(query, connection); for (var i = 0; i < termesCercaArray.Length; i++) { command.Parameters.AddWithValue("@terme" + i, "%" + termesCercaArray[i] + "%"); } reader = command.ExecuteReader(); while (reader.Read()) { var a = new Assignatura() { Id = reader.GetInt32(reader.GetOrdinal("Id")), Nom = reader.GetString(reader.GetOrdinal("Nom")), Curs = reader.GetInt32(reader.GetOrdinal("Curs")), Carrera = new Carrera() { Id = reader.GetInt32(reader.GetOrdinal("IdCarrera")), Nom = reader.GetString(reader.GetOrdinal("NomCarrera")) } }; assignatures.Add(a); } reader.Close(); //Usuaris var usuaris = new List<Usuari>(); query = "SELECT u.Id, u.Nom, u.Cognoms, u.Username FROM Usuaris u" + " WHERE u.Activat = true"; for (var i = 0; i < termesCercaArray.Length; i++) { query += " AND (u.Nom LIKE @terme" + i + " OR u.Cognoms LIKE @terme" + i + " OR u.Username LIKE @terme" + i + ")"; //Afegim els termes parametritzats per evitar SQL injection } command = new MySqlCommand(query, connection); for (var i = 0; i < termesCercaArray.Length; i++) { command.Parameters.AddWithValue("@terme" + i, "%" + termesCercaArray[i] + "%"); } reader = command.ExecuteReader(); while (reader.Read()) { var u = new Usuari() { Id = reader.GetInt32(reader.GetOrdinal("Id")), Nom = reader.GetString(reader.GetOrdinal("Nom")), Cognoms = reader.GetString(reader.GetOrdinal("Cognoms")), Username = reader.GetString(reader.GetOrdinal("Username")) }; usuaris.Add(u); } reader.Close(); ViewBag.TermesCerca = termesCerca; return View(new Tuple<List<DocumentLlistat>, List<Assignatura>, List<Usuari>>(documents, assignatures, usuaris)); } }
public ActionResult Filtrar(TipusDocument Tipus) { using (var connection = new MySqlConnection(ConnectionString)) { Log.Info("Filtrar documents per tipus: " + Tipus.ToString()); connection.Open(); var command = new MySqlCommand("SELECT d.Id, d.Nom, d.Tipus, d.DataAfegit, d.IdUsuari, u.Username, d.IdAssignatura, a.Nom AS NomAssignatura, c.Nom AS NomCarrera," + " IF(EXISTS(SELECT v.IdDocument FROM Valoracions v WHERE v.IdDocument = d.Id), (SELECT AVG(v.Valoracio) FROM Valoracions v WHERE v.IdDocument = d.Id), 0) AS Valoracio " + " FROM Documents d, Usuaris u, Assignatures a, Carreres c" + " WHERE d.IdUsuari = u.Id AND d.IdAssignatura = a.Id AND a.IdCarrera = c.Id" + " AND d.Tipus = @TipusDocument" + " AND (IdAssignatura IN (SELECT a.Id FROM Assignatures a, Matricules m WHERE a.IdCarrera = m.IdCarrera AND a.Curs = m.Curs AND m.IdUsuari = @IdUsuari)" + " OR IdUsuari IN (SELECT Id FROM Usuaris u, Subscripcions s WHERE u.Id = s.IdUsuariSubscrit AND s.IdUsuariSubscriu = @IdUsuari))" + " ORDER BY DataAfegit DESC" + " LIMIT 100", connection); command.Parameters.AddWithValue("@IdUsuari", IdUsuari); command.Parameters.AddWithValue("@TipusDocument", Tipus.ToString()); MySqlDataReader reader = command.ExecuteReader(); var resultats = new List<DocumentLlistat>(); while (reader.Read()) { var d = new DocumentLlistat { Id = reader.GetInt32(reader.GetOrdinal("Id")), Nom = reader.GetString(reader.GetOrdinal("Nom")), Tipus = (TipusDocument)Enum.Parse(typeof(TipusDocument), reader.GetString(reader.GetOrdinal("Tipus"))), DataAfegit = reader.GetDateTime(reader.GetOrdinal("DataAfegit")), IdUsuari = reader.GetInt32(reader.GetOrdinal("IdUsuari")), Username = reader.GetString(reader.GetOrdinal("Username")), IdAssignatura = reader.GetInt32(reader.GetOrdinal("IdAssignatura")), NomAssignatura = reader.GetString(reader.GetOrdinal("NomAssignatura")), NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera")), Valoracio = reader.GetDouble(reader.GetOrdinal("Valoracio")), }; d.DataAfegitString = d.DataAfegit.ToShortDateString() + " " + d.DataAfegit.ToShortTimeString(); d.TipusString = Lang.GetString(lang, d.Tipus.ToString()); d.LinkDocument = Url.Action("Veure", "Document", new { Id = d.Id }); d.LinkUsuari = Url.Action("Perfil", "Usuari", new { Id = d.IdUsuari }); d.LinkAssignatura = Url.Action("Assignatura", "Document", new { Id = d.IdAssignatura }); resultats.Add(d); } Log.Info("Total de documents: " + resultats.Count); ViewBag.Tipus = Tipus; return View(resultats); } }
public JsonResult DocumentsPaginaPrincipal() { using (var connection = new MySqlConnection(ConnectionString)) { connection.Open(); /* Obtenir els 100 ultims documents (amb els usuaris, les assignatures a les que corresponen i les valoracions si existeixen) * de les assignatures que pertanyen a les carreres i cursos matriculats * o als usuaris que seguim, ordenats per data en que es van afegir descendentment * (els mes recents primers) */ var command = new MySqlCommand("SELECT d.Id, d.Nom, d.Tipus, d.DataAfegit, d.IdUsuari, u.Username, d.IdAssignatura, a.Nom AS NomAssignatura, c.Nom AS NomCarrera," + " IF(EXISTS(SELECT v.IdDocument FROM Valoracions v WHERE v.IdDocument = d.Id), (SELECT AVG(v.Valoracio) FROM Valoracions v WHERE v.IdDocument = d.Id), 0) AS Valoracio " + " FROM Documents d, Usuaris u, Assignatures a, Carreres c" + " WHERE d.IdUsuari = u.Id AND d.IdAssignatura = a.Id AND a.IdCarrera = c.Id" + " AND (IdAssignatura IN (SELECT a.Id FROM Assignatures a, Matricules m WHERE a.IdCarrera = m.IdCarrera AND a.Curs = m.Curs AND m.IdUsuari = @IdUsuari)" + " OR IdUsuari IN (SELECT Id FROM Usuaris u, Subscripcions s WHERE u.Id = s.IdUsuariSubscrit AND s.IdUsuariSubscriu = @IdUsuari))" + " ORDER BY DataAfegit DESC" + " LIMIT 100", connection); command.Parameters.AddWithValue("@IdUsuari", IdUsuari); MySqlDataReader reader = command.ExecuteReader(); var resultats = new List<DocumentLlistat>(); while (reader.Read()) { var d = new DocumentLlistat { Id = reader.GetInt32(reader.GetOrdinal("Id")), Nom = reader.GetString(reader.GetOrdinal("Nom")), Tipus = (TipusDocument)Enum.Parse(typeof(TipusDocument), reader.GetString(reader.GetOrdinal("Tipus"))), DataAfegit = reader.GetDateTime(reader.GetOrdinal("DataAfegit")), IdUsuari = reader.GetInt32(reader.GetOrdinal("IdUsuari")), Username = reader.GetString(reader.GetOrdinal("Username")), IdAssignatura = reader.GetInt32(reader.GetOrdinal("IdAssignatura")), NomAssignatura = reader.GetString(reader.GetOrdinal("NomAssignatura")), NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera")), Valoracio = reader.GetDouble(reader.GetOrdinal("Valoracio")), }; d.DataAfegitString = d.DataAfegit.ToShortDateString() + " " + d.DataAfegit.ToShortTimeString(); d.TipusString = Lang.GetString(lang, d.Tipus.ToString()); d.LinkDocument = Url.Action("Veure", "Document", new { Id = d.Id }); d.LinkUsuari = Url.Action("Perfil", "Usuari", new { Id = d.IdUsuari }); d.LinkAssignatura = Url.Action("Assignatura", "Document", new { Id = d.IdAssignatura }); resultats.Add(d); } Log.Info("Total de documents: " + resultats.Count); return Json(resultats, JsonRequestBehavior.AllowGet); } }
public ActionResult ModerarDocumentsAssignatura(int Id) { if (!IsAdmin) { return RedirectToAction("Login"); } Log.Info("Moderar documents de carrera amb id: " + Id); ViewBag.Action = "Moderar"; using (var connection = new MySqlConnection(ConnectionString)) { connection.Open(); var cmd = new MySqlCommand("SELECT d.Id, d.Nom, d.IdAssignatura, a.Nom AS NomAssignatura, " + "IF(EXISTS(SELECT v.IdDocument FROM Valoracions v WHERE v.IdDocument = d.Id), (SELECT AVG(v.Valoracio) FROM Valoracions v WHERE v.IdDocument = d.Id), 0) AS Valoracio, " + "a.IdCarrera, c.Nom AS NomCarrera FROM Documents d, Assignatures a, Carreres c WHERE d.IdAssignatura = @IdAssignatura AND d.IdAssignatura = a.Id AND a.IdCarrera = c.Id ORDER BY Valoracio, d.Id ASC", connection); cmd.Parameters.AddWithValue("@IdAssignatura", Id); MySqlDataReader reader = cmd.ExecuteReader(); ViewBag.IdCarrera = -1; ViewBag.NomCarrera = ""; ViewBag.NomAssignatura = ""; var r = new List<DocumentLlistat>(); while (reader.Read()) { var d = new DocumentLlistat() { Id = reader.GetInt32(reader.GetOrdinal("Id")), Nom = reader.GetString(reader.GetOrdinal("Nom")), Valoracio = reader.IsDBNull(reader.GetOrdinal("Valoracio")) ? 0.0 : reader.GetDouble(reader.GetOrdinal("Valoracio")), IdAssignatura = Id, NomAssignatura = reader.GetString(reader.GetOrdinal("NomAssignatura")) }; ViewBag.IdCarrera = reader.GetInt32(reader.GetOrdinal("IdCarrera")); ViewBag.NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera")); ViewBag.NomAssignatura = d.NomAssignatura; r.Add(d); } if (ViewBag.IdCarrera == -1) { reader.Close(); cmd = new MySqlCommand("SELECT a.Nom AS NomAssignatura, c.Id AS IdCarrera, c.Nom AS NomCarrera FROM Assignatures a, Carreres c WHERE a.IdCarrera = c.Id AND a.Id = @IdAssignatura", connection); cmd.Parameters.AddWithValue("@IdAssignatura", Id); reader = cmd.ExecuteReader(); if (reader.Read()) { ViewBag.IdCarrera = reader.GetInt32(reader.GetOrdinal("IdCarrera")); ViewBag.NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera")); ViewBag.NomAssignatura = reader.GetString(reader.GetOrdinal("NomAssignatura")); } } return View(r); } }