Exemplo n.º 1
0
        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));
            }
        }
Exemplo n.º 2
0
        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();
                }
            }
        }
Exemplo n.º 3
0
        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>()));
        }
Exemplo n.º 4
0
        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();
            }
        }
Exemplo n.º 5
0
        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();
            }
        }