/// <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;
}
