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 Seguint(int Id)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
connection.Open();
MySqlCommand command = new MySqlCommand("SELECT Id, Username FROM Usuaris WHERE Id = @Id", connection);
command.Parameters.AddWithValue("@Id", Id);
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
Usuari usuariPrincipal = new Usuari();
usuariPrincipal.Username = reader.GetString(reader.GetOrdinal("Username"));
reader.Close();
command = new MySqlCommand("SELECT s.IdUsuariSubscrit, u.Nom, u.Cognoms FROM Subscripcions s, Usuaris u WHERE s.IdUsuariSubscriu = @IdUsuariSubscriu AND s.IdUsuariSubscrit = u.Id ORDER BY u.Nom ASC", connection);
command.Parameters.AddWithValue("@IdUsuariSubscriu", Id);
reader = command.ExecuteReader();
List<Usuari> resultat = new List<Usuari>();
while (reader.Read())
{
Usuari u = new Usuari();
u.Id = reader.GetInt32(reader.GetOrdinal("IdUsuariSubscrit"));
u.Nom = reader.GetString(reader.GetOrdinal("Nom"));
u.Cognoms = reader.GetString(reader.GetOrdinal("Cognoms"));
resultat.Add(u);
}
return View(new Tuple<Usuari, List<Usuari>>(usuariPrincipal, resultat));
}
else
{
Log.Warn("ID d'usuari inexistent: " + Id);
ViewBag.Error = Lang.GetString(lang, "Error_id_usuari");
return View();
}
}
}
public ActionResult Configuracio(string PasswordEnc, string Email, string Nom, string Cognoms, DateTime DataNaixement, char Sexe)
{
Log.Info("Guardar configuracio de l'usuari " + IdUsuari);
Usuari u = new Usuari()
{
Id = IdUsuari,
Password = PasswordEnc,
Email = Email,
Nom = Nom,
Cognoms = Cognoms,
DataNaixement = DataNaixement,
Sexe = Sexe,
Activat = true
};
using (var connection = new MySqlConnection(ConnectionString))
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand("SELECT Email FROM Usuaris WHERE Email = @Email AND Id != @Id", connection);
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@Id", IdUsuari);
cmd.Transaction = transaction;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
reader.Close();
transaction.Rollback();
Log.Warn("Email " + Email + " ja esta registrat per un altre usuari");
ViewBag.Error = Lang.GetString(base.lang, "Email_ja_existent");
}
else
{
reader.Close();
cmd = new MySqlCommand("SELECT Email FROM Usuaris WHERE Id = @Id", connection);
cmd.Parameters.AddWithValue("@Id", IdUsuari);
cmd.Transaction = transaction;
reader = cmd.ExecuteReader();
if (reader.Read())
{
string emailAntic = reader.GetString(reader.GetOrdinal("Email"));
bool emailModificat = emailAntic != Email;
reader.Close();
object SexeSQL = Sexe.ToString();
if (Sexe == '-')
{
SexeSQL = DBNull.Value;
}
object CodiActivacio = DBNull.Value;
bool Activat = true;
if (emailModificat)
{
Log.Info("Email modificat. Enviar correu nou de confirmacio i desactivar el compte");
Guid g = Guid.NewGuid();
string CodiActivacioString = Convert.ToBase64String(g.ToByteArray());
CodiActivacioString = CodiActivacioString.Replace("=", "");
CodiActivacioString = CodiActivacioString.Replace("+", "");
CodiActivacioString = CodiActivacioString.Replace("/", "");
CodiActivacio = CodiActivacioString;
Activat = false;
}
string passwordSQL = "";
if (PasswordEnc != "")
{
passwordSQL = ", Password = @Password";
}
cmd = new MySqlCommand("UPDATE Usuaris SET Email = @Email" + passwordSQL + ", Nom = @Nom, Cognoms = @Cognoms, DataNaixement = @DataNaixement, Sexe = @Sexe, Activat = @Activat, CodiActivacio = @CodiActivacio WHERE Id = @Id", connection);
if (PasswordEnc != "")
{
cmd.Parameters.AddWithValue("@Password", PasswordEnc);
}
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@Nom", Nom);
cmd.Parameters.AddWithValue("@Cognoms", Cognoms);
cmd.Parameters.AddWithValue("@DataNaixement", DataNaixement);
cmd.Parameters.AddWithValue("@Sexe", SexeSQL);
cmd.Parameters.AddWithValue("@Activat", Activat);
cmd.Parameters.AddWithValue("@CodiActivacio", CodiActivacio);
cmd.Parameters.AddWithValue("@Id", IdUsuari);
cmd.Transaction = transaction;
try
{
reader = cmd.ExecuteReader();
reader.Close();
transaction.Commit();
if (emailModificat)
{
var urlBuilder = new System.UriBuilder(Request.Url.AbsoluteUri)
{
Path = Url.Action("Activate", "Usuari", new RouteValueDictionary(new { id = CodiActivacio }))
};
string url = urlBuilder.ToString();
MailMessage msg = new MailMessage();
msg.To.Add(Email);
msg.Subject = Lang.GetString(lang, "Completa_el_registre");
msg.From = new MailAddress("*****@*****.**", "HotNotes Admin");
msg.Body = Lang.GetString(base.lang, "Email_registre").Replace("[[NOM]]", Nom).Replace("[[LINK]]", url);
msg.IsBodyHtml = true;
NetworkCredential nwCredential = new NetworkCredential("webmasterhotnotes", "thehotnotespassword");
SmtpClient smtp = new SmtpClient("smtp.gmail.com");
smtp.UseDefaultCredentials = false;
smtp.Credentials = nwCredential;
smtp.EnableSsl = true;
smtp.Send(msg);
FormsAuthentication.SignOut();
ViewBag.Accio = Lang.GetString(base.lang, "Dades_actualitzades");
ViewBag.Message = Lang.GetString(base.lang, "Email_modificat");
Log.Info("Email de confirmacio enviat");
return View("Register_Complete");
}
else
{
Log.Info("Dades actualitzades");
ViewBag.Message = Lang.GetString(base.lang, "Dades_actualitzades");
}
return RedirectToAction("Index", "Home");
}
catch (MySqlException e)
{
reader.Close();
transaction.Rollback();
Log.Error("Error actualitzant dades", e);
ViewBag.Error = Lang.GetString(base.lang, "Error_registre");
}
catch (SmtpException e)
{
reader.Close();
transaction.Rollback();
Log.Error("Error enviant email de confirmacio", e);
ViewBag.Error = Lang.GetString(base.lang, "Error_registre");
}
}
else
{
//Usuari no existent previament!
reader.Close();
transaction.Rollback();
Log.Warn("ID d'usuari inexistent");
ViewBag.Error = Lang.GetString(base.lang, "Error_registre");
}
}
}
return View(new Tuple<Usuari, List<Matricula>>(u, new List<Matricula>()));
}
public ActionResult Perfil(int Id)
{
//Veure perfil d'un usuari
Log.Info("Veure perfil de l'usuari " + Id);
if (Id == base.IdUsuari)
{
return RedirectToAction("Configuracio");
}
using (var connection = new MySqlConnection(ConnectionString))
{
connection.Open();
var cmd = new MySqlCommand("SELECT u.Username, u.Nom, u.Cognoms, u.Sexe, COUNT(d.Id) AS NumDocumentsPujats, EXISTS(SELECT * FROM Subscripcions WHERE IdUsuariSubscriu = @IdUsuari AND IdUsuariSubscrit = @IdUsuariSubscrit) AS EmSegueix, EXISTS(SELECT * FROM Subscripcions WHERE IdUsuariSubscriu = @IdUsuariSubscrit AND IdUsuariSubscrit = @IdUsuari) AS ElSegueixo FROM Usuaris u, Documents d WHERE IdUsuari = @IdUsuari AND d.IdUsuari = u.Id", connection);
cmd.Parameters.AddWithValue("@IdUsuari", Id);
cmd.Parameters.AddWithValue("@IdUsuariSubscrit", base.IdUsuari);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() && !reader.IsDBNull(reader.GetOrdinal("Username")))
{
Usuari u = new Usuari();
u.Id = Id;
u.Username = reader.GetString(reader.GetOrdinal("Username"));
u.Nom = reader.GetString(reader.GetOrdinal("Nom"));
u.Cognoms = reader.GetString(reader.GetOrdinal("Cognoms"));
if (!reader.IsDBNull(reader.GetOrdinal("Sexe")))
{
u.Sexe = reader.GetChar(reader.GetOrdinal("Sexe"));
}
else
{
u.Sexe = '-';
}
u.NumDocumentsPujats = reader.GetInt32(reader.GetOrdinal("NumDocumentsPujats"));
u.EmSegueix = reader.GetBoolean(reader.GetOrdinal("EmSegueix"));
u.ElSegueixo = reader.GetBoolean(reader.GetOrdinal("ElSegueixo"));
reader.Close();
cmd = new MySqlCommand("SELECT COUNT(IdUsuariSubscrit) AS NumSeguint FROM Subscripcions WHERE IdUsuariSubscriu = @IdUsuari", connection);
cmd.Parameters.AddWithValue("@IdUsuari", Id);
reader = cmd.ExecuteReader();
reader.Read();
u.NumSeguint = reader.GetInt32(reader.GetOrdinal("NumSeguint"));
reader.Close();
cmd = new MySqlCommand("SELECT COUNT(IdUsuariSubscriu) AS NumSeguidors FROM Subscripcions WHERE IdUsuariSubscrit = @IdUsuari", connection);
cmd.Parameters.AddWithValue("@IdUsuari", Id);
reader = cmd.ExecuteReader();
reader.Read();
u.NumSeguidors = reader.GetInt32(reader.GetOrdinal("NumSeguidors"));
reader.Close();
cmd = new MySqlCommand("SELECT m.IdCarrera, m.Curs, c.Nom AS NomCarrera, f.Nom AS NomFacultat, u.Nom AS NomUniversitat " +
"FROM Matricules m, Carreres c, Facultats f, Universitats u " +
"WHERE m.IdUsuari = @IdUsuari AND m.IdCarrera = c.Id AND c.IdFacultat = f.Id AND f.IdUniversitat = u.Id " +
"ORDER BY c.Nom ASC, m.Curs ASC", connection);
cmd.Parameters.AddWithValue("@IdUsuari", Id);
reader = cmd.ExecuteReader();
List<Matricula> matricules = new List<Matricula>();
while (reader.Read())
{
Matricula m = new Matricula();
m.IdUsuari = IdUsuari;
m.IdCarrera = reader.GetInt32(reader.GetOrdinal("IdCarrera"));
m.Curs = reader.GetInt32(reader.GetOrdinal("Curs"));
m.NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera"));
m.NomFacultat = reader.GetString(reader.GetOrdinal("NomFacultat"));
m.NomUniversitat = reader.GetString(reader.GetOrdinal("NomUniversitat"));
matricules.Add(m);
}
return View(new Tuple<Usuari, List<Matricula>>(u, matricules));
}
else
{
Log.Warn("ID d'usuari inexistent: " + Id);
ViewBag.Error = Lang.GetString(lang, "Error_id_usuari");
}
return View();
}
}
public ActionResult Configuracio()
{
Log.Info("Carregar configuracio de l'usuari " + IdUsuari);
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand("SELECT Id, Username, Password, Email, Nom, Cognoms, DataNaixement, Sexe, Activat FROM Usuaris WHERE Id = @Id", connection);
cmd.Parameters.AddWithValue("@Id", IdUsuari);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
char sexe;
if (reader.IsDBNull(reader.GetOrdinal("Sexe")))
{
sexe = '-';
}
else
{
sexe = reader.GetString(reader.GetOrdinal("Sexe"))[0];
}
Usuari u = new Usuari()
{
Id = IdUsuari,
Username = reader.GetString(reader.GetOrdinal("Username")),
Password = reader.GetString(reader.GetOrdinal("Password")),
Email = reader.GetString(reader.GetOrdinal("Email")),
Nom = reader.GetString(reader.GetOrdinal("Nom")),
Cognoms = reader.GetString(reader.GetOrdinal("Cognoms")),
DataNaixement = reader.GetDateTime(reader.GetOrdinal("DataNaixement")),
Sexe = sexe,
Activat = reader.GetBoolean(reader.GetOrdinal("Activat"))
};
reader.Close();
cmd = new MySqlCommand("SELECT m.IdCarrera, m.Curs, c.Nom AS NomCarrera, f.Nom AS NomFacultat, u.Nom AS NomUniversitat " +
"FROM Matricules m, Carreres c, Facultats f, Universitats u " +
"WHERE m.IdUsuari = @IdUsuari AND m.IdCarrera = c.Id AND c.IdFacultat = f.Id AND f.IdUniversitat = u.Id " +
"ORDER BY c.Nom ASC, m.Curs ASC", connection);
cmd.Parameters.AddWithValue("@IdUsuari", IdUsuari);
reader = cmd.ExecuteReader();
List<Matricula> matricules = new List<Matricula>();
while (reader.Read())
{
Matricula m = new Matricula();
m.IdUsuari = IdUsuari;
m.IdCarrera = reader.GetInt32(reader.GetOrdinal("IdCarrera"));
m.Curs = reader.GetInt32(reader.GetOrdinal("Curs"));
m.NomCarrera = reader.GetString(reader.GetOrdinal("NomCarrera"));
m.NomFacultat = reader.GetString(reader.GetOrdinal("NomFacultat"));
m.NomUniversitat = reader.GetString(reader.GetOrdinal("NomUniversitat"));
matricules.Add(m);
}
return View(new Tuple<Usuari, List<Matricula>>(u, matricules));
}
else
{
Log.Warn("ID d'usuari inexistent");
ViewBag.Error = Lang.GetString(base.lang, "Usuari_no_existeix");
}
return View();
}
}
