/// <summary> /// Ajout d'un utilisateur /// </summary> /// <param name="user">Utilisateur à ajouter</param> /// <returns>Vrai si tous c'est bien passé faux sinon</returns> public bool Add(User user) { if(user == null || mailExist(user.Email)) { return false; } string query = "INSERT INTO user (first_name,last_name,gender,birth_date,email,phone,pwd,image,role) VALUES (@first_name,@last_name,@gender,@birth,@email,@phone,@pwd,@image,@role)"; List<MySqlParameter> parms = new List<MySqlParameter>(); parms.Add(new MySqlParameter("first_name", user.FirstName)); parms.Add(new MySqlParameter("last_name", user.LastName)); parms.Add(new MySqlParameter("gender", user.Gender)); parms.Add(new MySqlParameter("birth", user.BirthDate)); parms.Add(new MySqlParameter("email", user.Email)); parms.Add(new MySqlParameter("phone", user.Phone)); parms.Add(new MySqlParameter("pwd", CreateSHAHash(user.Password))); parms.Add(new MySqlParameter("image", user.Image)); parms.Add(new MySqlParameter("role", user.Role)); MySqlHelper.ExecuteNonQuery(connexion, query, parms.ToArray()); Mail send = new Mail(); send.SendSimpleMessage(user.Email,"Inscription","Vous êtes maintenant inscrit à dance coach"); return true; }
/// <summary> /// Récupère la liste de tous les utilisateurs /// </summary> /// <returns>Liste de tous les utilisateurs</returns> public IEnumerable<User> GetAll() { List<User> users = new List<User>(); string query = "Select id,first_name,last_name,gender,birth_date,email,phone,image,role from user"; using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { User user = new User(); user.UserId = reader.GetInt16(0); user.FirstName = reader.GetString(1); user.LastName = reader.GetString(2); user.Gender = reader.GetBoolean(3); user.Email = reader.GetString(5); user.Phone = reader.GetValue(6).ToString(); user.Image = reader.GetValue(7).ToString(); user.Role = reader.GetInt16(8); users.Add(user); } } } return users; }
/// <summary> /// Réupère un message /// </summary> /// <param name="id">Id du message à récupérer</param> /// <returns>Message</returns> public Messenger Get(int id) { string query = "select id,subject,message,lesson_id,u.id,last_name,first_name,image FROM messenger as m JOIN user as u ON u.id = m.user_id WHERE m.id = "+id; Messenger message = new Messenger(); User author = new User(); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { message.MessengerId = reader.GetInt16(0); message.Subject = reader.GetString(1); message.Message = reader.GetString(2); message.lessonid = reader.GetInt16(3); author.UserId = reader.GetInt16(4); author.LastName = reader.GetString(5); author.FirstName = reader.GetString(6); author.Image = reader.GetString(7); message.Author = author; } } } return message; }
/// <summary> /// Mets à jour l'utilisateur /// </summary> /// <param name="user">Utilisateur à mettre à jour</param> /// <returns>Vrai si tous ce passe bien faux sinon</returns> public bool Update(User user) { if(user == null) { return false; } string query = "UPDATE user SET first_name=@firstname, last_name=@lastname,gender=@gender,birth_date=@birth,email=@email,phone=@phone,pwd=@pwd,image=@image WHERE id=@userid"; List<MySqlParameter> parms = new List<MySqlParameter>(); parms.Add(new MySqlParameter("firstname", user.FirstName)); parms.Add(new MySqlParameter("lastname", user.LastName)); parms.Add(new MySqlParameter("gender", user.Gender)); parms.Add(new MySqlParameter("birth", user.BirthDate)); parms.Add(new MySqlParameter("email", user.Email)); parms.Add(new MySqlParameter("phone", user.Phone)); parms.Add(new MySqlParameter("pwd", CreateSHAHash(user.Password))); parms.Add(new MySqlParameter("image", user.Image)); parms.Add(new MySqlParameter("userid", user.UserId)); MySqlHelper.ExecuteNonQuery(connexion, query, parms.ToArray()); return true; }
public User GetUserByMail(string mail) { User user = new User(); string query = "Select id,first_name,last_name,email,gender,birth_date,phone,image,role from user where email = @mail"; List<MySqlParameter> parms = new List<MySqlParameter>(); parms.Add(new MySqlParameter("mail", mail)); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query,parms.ToArray())) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { user.UserId = reader.GetInt16(0); user.FirstName = reader.GetString(1); user.LastName = reader.GetString(2); user.Email = reader.GetString(3); user.Gender = reader.GetBoolean(4); user.Phone = reader.GetValue(6).ToString(); user.Image = reader.GetValue(7).ToString(); user.Role = reader.GetInt16(8); } } } return user; }
/// <summary> /// Récupère le profil public d'un utilisateur /// </summary> /// <param name="id">id de l'utilisateur</param> /// <returns>Profil public de l'utilisateur</returns> public User GetPublic(int id) { User user = new User(); string query = "Select id,first_name,last_name,gender,phone,image,role from user where id ="+id; using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { user.UserId = reader.GetInt16(0); user.FirstName = reader.GetString(1); user.LastName = reader.GetString(2); user.Gender = reader.GetBoolean(3); user.Phone = reader.GetValue(4).ToString(); user.Image = reader.GetValue(5).ToString(); user.Role = reader.GetInt16(6); } } } return user; }
/// <summary> /// Retourne tous les messages des lessons /// </summary> /// <param name="lessonId">L'id de la lesson</param> /// <returns>Liste des messages</returns> public IEnumerable<Messenger> GetAllByLesson(int lessonId) { List<Messenger> messages = new List<Messenger>(); string query = "Select id,subject,message,u.id,first_name,last_name,image from messenger as m JOIN user as u ON u.id = m.user_id WHERE lesson_id ="+lessonId; using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { Messenger message = new Messenger(); User author = new User(); message.MessengerId = reader.GetInt16(0); message.Subject = reader.GetString(1); message.Message = reader.GetString(2); author.UserId = reader.GetInt16(3); author.LastName = reader.GetString(4); author.FirstName = reader.GetString(5); author.Image = reader.GetString(6); messages.Add(message); } } } return messages; }
/// <summary> /// Récupère toutes les lessons à venir d'un utilisateur /// </summary> /// <param name="userId">l'id de l'utilisateur</param> /// <returns>Les lessons à venir de l'utilisateur</returns> public IEnumerable<Lesson> GetLessonByUserInFewTime(int userId) { UserRepository _repositoryUser = new UserRepository(); List<Lesson> lessons = new List<Lesson>(); if (_repositoryUser.GetPublic(userId).FirstName != "") { string query = "SELECT id,description,price,title,c.name,start_date,end_date,u.id as userid,first_name,last_name,image FROM lesson as l JOIN category as c ON c.id = l.category_id JOIN user as u ON u.id = l.user_id WHERE user_id=" + userId+" start_date>="+DateTime.Now; using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { Lesson lesson = new Lesson(); User author = new User(); Categorie cat = new Categorie(); lesson.LessonId = reader.GetInt16(0); lesson.description = reader.GetString(1); lesson.Price = reader.GetFloat(2); lesson.Title = reader.GetString(3); cat.Name = reader.GetString(4); lesson.DateStart = reader.GetDateTime(5).ToString("MM/dd/yyyy HH:mm"); lesson.DateEnd = reader.GetDateTime(6).ToString("MM/dd/yyyy HH:mm"); author.UserId = reader.GetInt16(7); author.FirstName = reader.GetString(8); author.LastName = reader.GetString(9); author.Image = reader.GetValue(10).ToString(); lesson.Author = author; lesson.Categorie = cat; lessons.Add(lesson); } } } } return lessons; }
/// <summary> /// /// </summary> /// <param name="start"></param> /// <param name="end"></param> /// <param name="idcat"></param> /// <returns></returns> public IEnumerable<Lesson> GetFilterByCat(DateTime start, DateTime end, int idcat) { string query = "select l.id as id_lesson, description,nb_free,nb_booked,price,title,zip_code,address,start_date,end_date,u.id as user_id, first_name,last_name,gender,birth_date,email,phone,image,c.name FROM lesson as l JOIN user as u ON u.id = l.user_id JOIN category as c ON c.id = l.category_id WHERE start_date >= @start AND end_date <= @end AND l.category_id = @idcat"; List<MySqlParameter> parms = new List<MySqlParameter>(); parms.Add(new MySqlParameter("start", start)); parms.Add(new MySqlParameter("end", end)); parms.Add(new MySqlParameter("idcat", idcat)); List<Lesson> lessons = new List<Lesson>(); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query, parms.ToArray())) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { Lesson lesson = new Lesson(); User author = new User(); Categorie cat = new Categorie(); lesson.LessonId = reader.GetInt16(0); lesson.description = reader.GetString(1); lesson.NumberFree = reader.GetInt16(2); lesson.NumberBooked = reader.GetInt16(3); lesson.Price = reader.GetFloat(4); lesson.Title = reader.GetString(5); lesson.ZipCode = reader.GetString(6); lesson.Adresse = reader.GetString(7); lesson.DateStart = reader.GetDateTime(8).ToString("MM/dd/yyyy HH:mm"); lesson.DateEnd = reader.GetDateTime(9).ToString("MM/dd/yyyy HH:mm"); author.UserId = reader.GetInt16(10); author.FirstName = reader.GetString(11); author.LastName = reader.GetString(12); author.Gender = reader.GetBoolean(13); author.Email = reader.GetString(15); author.Phone = reader.GetValue(16).ToString(); author.Image = reader.GetValue(17).ToString(); cat.Name = reader.GetString(18); lesson.Author = author; lesson.Categorie = cat; lessons.Add(lesson); } } } return lessons; }
/// <summary> /// Retourne la liste d'utilisateur inscrit à la lesson /// </summary> /// <param name="id">id lesson</param> /// <returns>Liste d'utilisateur</returns> public IEnumerable<User> GetAllBookByLesson(int id) { string query = "SELECT u.id,email,image,last_name,first_name from user as u JOIN booking as b ON b.user_id = u.id JOIN lesson as l ON b.lesson_id = l.id WHERE l.id = "+id; List<User> users = new List<User>(); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { User user = new User(); user.UserId = reader.GetInt16(0); user.Email = reader.GetString(1); user.Image = reader.GetValue(2).ToString(); user.LastName = reader.GetString(3); user.FirstName = reader.GetString(4); users.Add(user); } } } return users; }
/// <summary> /// Récupération d'une lesson /// </summary> /// <param name="id">Id de la lesson</param> /// <returns>Une lesson</returns> public Lesson Get(int id) { string query = "select l.id as id_lesson, description,nb_free,nb_booked,price,title,zip_code,address,start_date,end_date,u.id as user_id, first_name,last_name,gender,birth_date,email,phone,pwd,image,c.name FROM lesson as l JOIN user as u ON u.id = l.user_id JOIN category as c ON c.id = l.category_id WHERE l.id = "+id; Lesson lesson = new Lesson(); User author = new User(); Categorie cat = new Categorie(); using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query)) { // Check if the reader returned any rows if (reader.HasRows) { while (reader.Read()) { lesson.LessonId = reader.GetInt16(0); lesson.description = reader.GetString(1); lesson.NumberFree = reader.GetInt16(2); lesson.NumberBooked = reader.GetInt16(3); lesson.Price = reader.GetFloat(4); lesson.Title = reader.GetString(5); lesson.ZipCode = reader.GetString(6); lesson.Adresse = reader.GetString(7); lesson.DateStart = reader.GetDateTime(8).ToString("MM/dd/yyyy HH:mm"); lesson.DateEnd = reader.GetDateTime(9).ToString("MM/dd/yyyy HH:mm"); author.UserId = reader.GetInt16(10); author.FirstName = reader.GetString(11); author.LastName = reader.GetString(12); author.Gender = reader.GetBoolean(13); author.Email = reader.GetString(15); author.Phone = reader.GetValue(16).ToString(); author.Password = reader.GetString(17); author.Image = reader.GetValue(18).ToString(); cat.Name = reader.GetString(19); lesson.Author = author; lesson.Categorie = cat; } } } return lesson; }