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