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);
}
}
