private List<Assignatura> GetLlistaAssignatures()
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand("SELECT A.Id, A.Nom, A.Curs, C.Id AS IdCarrera, C.Nom AS NomCarrera FROM Assignatures A, Carreres C, Matricules M WHERE M.IdUsuari = @IdUsuari AND M.IdCarrera = A.IdCarrera AND A.IdCarrera = C.Id AND M.Curs = A.Curs ORDER BY A.IdCarrera, A.Curs, A.Nom", connection);
cmd.Parameters.AddWithValue("@IdUsuari", IdUsuari);
MySqlDataReader reader = cmd.ExecuteReader();
List<Assignatura> l = new List<Assignatura>();
while (reader.Read())
{
Assignatura a = new Assignatura();
a.Id = reader.GetInt32(reader.GetOrdinal("Id"));
a.Nom = reader.GetString(reader.GetOrdinal("Nom"));
a.Curs = reader.GetInt32(reader.GetOrdinal("Curs"));
a.Carrera = new Carrera();
a.Carrera.Id = reader.GetInt32(reader.GetOrdinal("IdCarrera"));
a.Carrera.Nom = reader.GetString(reader.GetOrdinal("NomCarrera"));
l.Add(a);
}
return l;
}
}
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 ModerarDocumentsCarrera(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 a.Id, a.Nom, a.Curs, a.IdCarrera, c.Nom AS NomCarrera, (SELECT COUNT(Id) FROM Documents WHERE IdAssignatura = a.Id) AS NumDocs FROM Assignatures a, Carreres c WHERE a.IdCarrera = @IdCarrera AND a.IdCarrera = c.Id ORDER BY Curs, Id ASC", connection);
cmd.Parameters.AddWithValue("@IdCarrera", Id);
MySqlDataReader reader = cmd.ExecuteReader();
ViewBag.NomCarrera = "";
var r = new List<Assignatura>();
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")),
NumDocs = reader.GetInt32(reader.GetOrdinal("NumDocs")),
Carrera = new Carrera()
{
Id = Id,
Nom = reader.GetString(reader.GetOrdinal("NomCarrera"))
}
};
ViewBag.NomCarrera = a.Carrera.Nom;
r.Add(a);
}
if (string.IsNullOrEmpty(ViewBag.NomCarrera))
{
reader.Close();
cmd = new MySqlCommand("SELECT Nom FROM Carreres WHERE Id = @Id", connection);
cmd.Parameters.AddWithValue("@Id", Id);
reader = cmd.ExecuteReader();
if (reader.Read())
{
ViewBag.NomCarrera = reader.GetString(reader.GetOrdinal("Nom"));
}
}
return View(r);
}
}
