/// <summary>
        /// Récupère une catégorie
        /// </summary>
        /// <param name="id">id de la catégorie</param>
        /// <returns>La catégorie</returns>
        public Categorie Get(int id)
        {
            string query = "SELECT id,name FROM category WHERE id=" + id;
            Categorie cat = new Categorie();
            using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query))
            {
                // Check if the reader returned any rows
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        cat.CategorieId = reader.GetInt16(0);
                        cat.Name = reader.GetString(1);
                    }
                }
            }

            return cat;
        }
        /// <summary>
        /// Récupère toutes les catégories
        /// </summary>
        /// <returns>Retourne toutes les catégories</returns>
        public IEnumerable<Categorie> GetAll()
        {
            string query = "SELECT id,name FROM category";
            List<Categorie> cats = new List<Categorie>();
            using (MySqlDataReader reader = MySqlHelper.ExecuteReader(connexion, query))
            {
                // Check if the reader returned any rows
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Categorie cat = new Categorie();
                        cat.CategorieId = reader.GetInt16(0);
                        cat.Name = reader.GetString(1);
                        cats.Add(cat);
                    }
                }
            }

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