using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    using MusicaToulouse.Connection;
    using MusicaToulouse.LogError;
    using System.Data.SqlClient;
    using System.Net.Mail;
    using System.Net;
    using System.Security.Cryptography;
    using System.Data;
    /// <summary>
    /// La classe Interface permet d'avoir accès aux données.
    /// </summary>
    public class Interface
    {
        private Connection connex;
        #region Constructors
        /// <summary>
        /// Contructeur de la classe Interface
        /// </summary>
        public Interface()
        {
            connex = new Connection();            
        }

        #endregion Constructors

        #region Methods

        #region Artistes
        /// <summary>
        /// Récupère la liste complète des artistes enregistrés.
        /// </summary>
        /// <returns></returns>
        public List<ArtisteInfo> getArtisteAll()
        {
            try
            {
                List<ArtisteInfo> listArtiste = new List<ArtisteInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Artiste.getArtisteAll]", listParams);
                
                while (reader.Read())
                {
                    ArtisteInfo artiste = new ArtisteInfo();
                    artiste.IdArtiste = Convert.ToInt16(reader["id_artiste"].ToString());
                    artiste.Name = reader["artiste_name"].ToString();
                    artiste.Web = reader["artiste_web"].ToString();
                    artiste.Text = reader["artiste_text"].ToString();
                    artiste.Photo = reader["artiste_photo"].ToString();
                    artiste.Region = (reader["artiste_region"].ToString().Equals("1")) ? true : false;
                    artiste.Ville = reader["artiste_ville"].ToString();
                    artiste.Creation = Convert.ToDateTime(reader["artiste_creation"].ToString());
                    artiste.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    artiste.Valid = (reader["artiste_valid"].ToString().Equals("1")) ? true : false;
                    artiste.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));

                    artiste.Genres = new List<GenreInfo>();

                    listArtiste.Add(artiste);
                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                foreach (ArtisteInfo artiste in listArtiste)
                {
                    connex.connect();
                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idArtiste", DbType = System.Data.SqlDbType.BigInt, Value = artiste.IdArtiste.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Artiste.getGenresForArtiste]", listParams2);

                    while (reader2.Read())
                    {
                        var genre = from o in lstGenre
                                    where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                    select o;
                        
                        artiste.Genres.Add(genre.First());
                    }

                    reader2.Close();
                    connex.disconnect();
                }

                return listArtiste;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// Récupère la liste des artistes pour un genre.
        /// </summary>
        /// <param name="p_genre">Genre défini pour la recherche.</param>
        /// <returns></returns>
        public List<ArtisteInfo> getArtisteByGenre(GenreInfo p_genre)
        {
            try
            {
                var Artiste = from o in getArtisteAll()
                              where (from a in o.Genres where a.Name == p_genre.Name select a).Count() != 0
                              select o;
                
                return Artiste.ToList();
                
            }
            catch (Exception ex)
            {
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// Récupère un artiste en fonction de son identifiant.
        /// </summary>
        /// <param name="p_id">Identifiant de l'artiste</param>
        /// <returns></returns>
        public ArtisteInfo getArtisteById(int p_id)
        {
            try
            {
                ArtisteInfo artiste = new ArtisteInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();
                listParams.Add(new Parameters { Param = "@idArtiste", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Artiste.getArtisteByID]", listParams);

                while (reader.Read())
                {                    
                    artiste.IdArtiste = Convert.ToInt16(reader["id_artiste"].ToString());
                    artiste.Name = reader["artiste_name"].ToString();
                    artiste.Web = reader["artiste_web"].ToString();
                    artiste.Text = reader["artiste_text"].ToString();
                    artiste.Photo = reader["artiste_photo"].ToString();
                    artiste.Region = (reader["artiste_region"].ToString().Equals("1")) ? true : false;
                    artiste.Ville = reader["artiste_ville"].ToString();
                    artiste.Creation = Convert.ToDateTime(reader["artiste_creation"].ToString());
                    artiste.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    artiste.Valid = (reader["artiste_valid"].ToString().Equals("1")) ? true : false;
                    artiste.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));

                    artiste.Genres = new List<GenreInfo>();

                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                
                connex.connect();
                List<Parameters> listParams2 = new List<Parameters>();
                listParams2.Add(new Parameters { Param = "@idArtiste", DbType = System.Data.SqlDbType.BigInt, Value = artiste.IdArtiste.ToString() });
                SqlDataReader reader2 = connex.procStockQuery("[Artiste.getGenresForArtiste]", listParams2);

                while (reader2.Read())
                {
                    var genre = from o in lstGenre
                                where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                select o;

                    artiste.Genres.Add(genre.First());
                }

                reader2.Close();
                connex.disconnect();
                

                return artiste;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// Récupère la liste des artistes de la région Toulousaine.
        /// </summary>
        /// <returns></returns>
        public List<ArtisteInfo> getArtisteFromRegion()
        {
            try
            {
                List<ArtisteInfo> listArtiste = new List<ArtisteInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Artiste.getArtisteFromRegion]", listParams);

                while (reader.Read())
                {
                    ArtisteInfo artiste = new ArtisteInfo();
                    artiste.IdArtiste = Convert.ToInt16(reader["id_artiste"].ToString());
                    artiste.Name = reader["artiste_name"].ToString();
                    artiste.Web = reader["artiste_web"].ToString();
                    artiste.Text = reader["artiste_text"].ToString();
                    artiste.Photo = reader["artiste_photo"].ToString();
                    artiste.Region = (reader["artiste_region"].ToString().Equals("1")) ? true : false;
                    artiste.Ville = reader["artiste_ville"].ToString();
                    artiste.Creation = Convert.ToDateTime(reader["artiste_creation"].ToString());
                    artiste.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    artiste.Valid = (reader["artiste_valid"].ToString().Equals("1")) ? true : false;
                    artiste.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));

                    artiste.Genres = new List<GenreInfo>();

                    listArtiste.Add(artiste);
                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                foreach (ArtisteInfo artiste in listArtiste)
                {
                    connex.connect();
                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idArtiste", DbType = System.Data.SqlDbType.BigInt, Value = artiste.IdArtiste.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Artiste.getGenresForArtiste]", listParams2);

                    while (reader2.Read())
                    {
                        var genre = from o in lstGenre
                                    where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                    select o;

                        artiste.Genres.Add(genre.First());
                    }

                    reader2.Close();
                    connex.disconnect();
                }

                return listArtiste;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        #endregion

        #region Categorie
        /// <summary>
        /// Recupère la liste des catégories
        /// </summary>
        /// <returns></returns>
        public List<CategorieInfo> getCategorieAll()
        {
            try
            {
                List<CategorieInfo> listCategorie = new List<CategorieInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Categorie.getCategorieAll]", listParams);

                while (reader.Read())
                {
                    CategorieInfo categorie = new CategorieInfo();
                    categorie.IdCategorie = Convert.ToInt16(reader["id_categorie"].ToString());
                    categorie.Nom = reader["categorie_name"].ToString();
                    categorie.Description = reader["categorie_description"].ToString();

                    listCategorie.Add(categorie);
                }

                reader.Close();
                connex.disconnect();

                return listCategorie;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// Récupère une catégorie en fonction de son identifiant
        /// </summary>
        /// <param name="p_id">Identifiant de la catégorie</param>
        /// <returns></returns>
        public CategorieInfo getCategorieById(int p_id)
        {
            try
            {
                CategorieInfo categorie = new CategorieInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();
                listParams.Add(new Parameters { Param = "@idCategorie", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Categorie.getCategorieById]", listParams);

                while (reader.Read())
                {
                    categorie.IdCategorie = Convert.ToInt16(reader["id_categorie"].ToString());
                    categorie.Nom = reader["categorie_name"].ToString();
                    categorie.Description = reader["categorie_description"].ToString();
                    
                }

                reader.Close();
                connex.disconnect();

                return categorie;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        #endregion

        #region Concert
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<ConcertInfo> getConcertAll()
        {
            try
            {
                List<ConcertInfo> listConcert = new List<ConcertInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();
                
                SqlDataReader reader = connex.procStockQuery("[Concert.getConcertAll]", listParams);

                while (reader.Read())
                {
                    ConcertInfo concert = new ConcertInfo();
                    concert.IdConcert = Convert.ToInt16(reader["id_concert"].ToString());
                    concert.Date = Convert.ToDateTime(reader["concert_date"].ToString());
                    concert.Description = reader["concert_description"].ToString();
                    concert.Lieu = this.getLieuById(Convert.ToInt16(reader["id_lieu"].ToString()));
                    concert.Prix = Convert.ToInt16(reader["concert_prix"].ToString());
                    concert.Soustitre = reader["concert_subtitle"].ToString();
                    concert.Titre = reader["concert_title"].ToString();
                    concert.Photo = reader["concert_photo"].ToString();
                    concert.Creation = Convert.ToDateTime(reader["concert_creation"].ToString());

                    concert.Valid = (reader["concert_valid"].ToString().Equals("1")) ? true : false;
                    concert.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));
                    concert.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    concert.Genres = new List<GenreInfo>();
                    concert.Artiste = new List<ArtisteInfo>();

                    listConcert.Add(concert);
                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                List<ArtisteInfo> lstArtiste = this.getArtisteAll();
                foreach (ConcertInfo concert in listConcert)
                {

                    connex.connect();

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Concert.getGenreForConcert]", listParams2);

                    while (reader2.Read())
                    {
                        var genre = from o in lstGenre
                                    where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                    select o;
                        
                        concert.Genres.Add(genre.First());
                    }

                    reader2.Close();
                    connex.disconnect();
                    connex.connect();

                    List<Parameters> listParams3 = new List<Parameters>();
                    listParams3.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader3 = connex.procStockQuery("[Concert.getArtisteForConcert]", listParams2);

                    while (reader3.Read())
                    {
                        var artiste = from o in lstArtiste
                                      where o.IdArtiste.Equals(Convert.ToInt16(reader3["id_artiste"].ToString()))
                                      select o;

                        concert.Artiste.Add(artiste.First());
                    }

                    reader3.Close();
                    connex.disconnect();
                }

                return listConcert;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_genre"></param>
        /// <returns></returns>
        public List<ConcertInfo> getConcertByGenre(GenreInfo p_genre)
        {
            try
            {
                List<ConcertInfo> listConcert = new List<ConcertInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idGenre", DbType = System.Data.SqlDbType.BigInt, Value = p_genre.IdGenre.ToString() });
                
                SqlDataReader reader = connex.procStockQuery("[Concert.getConcertByGenre]", listParams);

                while (reader.Read())
                {
                    ConcertInfo concert = new ConcertInfo();
                    concert.IdConcert = Convert.ToInt16(reader["id_concert"].ToString());
                    concert.Date = Convert.ToDateTime(reader["concert_date"].ToString());
                    concert.Description = reader["concert_description"].ToString();
                    concert.Lieu = this.getLieuById(Convert.ToInt16(reader["id_lieu"].ToString()));
                    concert.Prix = Convert.ToInt16(reader["concert_prix"].ToString());
                    concert.Soustitre = reader["concert_subtitle"].ToString();
                    concert.Titre = reader["concert_title"].ToString();
                    concert.Photo = reader["concert_photo"].ToString();
                    concert.Creation = Convert.ToDateTime(reader["concert_creation"].ToString());

                    concert.Valid = (reader["concert_valid"].ToString().Equals("1")) ? true : false;
                    concert.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));
                    concert.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    concert.Genres = new List<GenreInfo>();
                    concert.Artiste = new List<ArtisteInfo>();

                    listConcert.Add(concert);
                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                List<ArtisteInfo> lstArtiste = this.getArtisteAll();
                foreach (ConcertInfo concert in listConcert)
                {

                    connex.connect();

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Concert.getGenreForConcert]", listParams2);

                    while (reader2.Read())
                    {
                        var genre = from o in lstGenre
                                    where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                    select o;

                        concert.Genres.Add(genre.First());
                    }

                    reader2.Close();
                    connex.disconnect();
                    connex.connect();

                    List<Parameters> listParams3 = new List<Parameters>();
                    listParams3.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader3 = connex.procStockQuery("[Concert.getArtisteForConcert]", listParams2);

                    while (reader3.Read())
                    {
                        var artiste = from o in lstArtiste
                                      where o.IdArtiste.Equals(Convert.ToInt16(reader3["id_artiste"].ToString()))
                                      select o;

                        concert.Artiste.Add(artiste.First());
                    }

                    reader3.Close();
                    connex.disconnect();
                }

                return listConcert;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_artiste"></param>
        /// <returns></returns>
        public List<ConcertInfo> getConcertByArtiste(ArtisteInfo p_artiste)
        {
            try
            {
                List<ConcertInfo> listConcert = new List<ConcertInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idArtiste", DbType = System.Data.SqlDbType.BigInt, Value = p_artiste.IdArtiste.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Concert.getConcertByArtiste]", listParams);

                while (reader.Read())
                {
                    ConcertInfo concert = new ConcertInfo();
                    concert.IdConcert = Convert.ToInt16(reader["id_concert"].ToString());
                    concert.Date = Convert.ToDateTime(reader["concert_date"].ToString());
                    concert.Description = reader["concert_description"].ToString();
                    concert.Lieu = this.getLieuById(Convert.ToInt16(reader["id_lieu"].ToString()));
                    concert.Prix = Convert.ToInt16(reader["concert_prix"].ToString());
                    concert.Soustitre = reader["concert_subtitle"].ToString();
                    concert.Titre = reader["concert_title"].ToString();
                    concert.Photo = reader["concert_photo"].ToString();
                    concert.Creation = Convert.ToDateTime(reader["concert_creation"].ToString());

                    concert.Valid = (reader["concert_valid"].ToString().Equals("1")) ? true : false;
                    concert.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));
                    concert.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    concert.Genres = new List<GenreInfo>();
                    concert.Artiste = new List<ArtisteInfo>();

                    listConcert.Add(concert);
                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                List<ArtisteInfo> lstArtiste = this.getArtisteAll();
                foreach (ConcertInfo concert in listConcert)
                {

                    connex.connect();

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Concert.getGenreForConcert]", listParams2);

                    while (reader2.Read())
                    {
                        var genre = from o in lstGenre
                                    where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                    select o;

                        concert.Genres.Add(genre.First());
                    }

                    reader2.Close();
                    connex.disconnect();
                    connex.connect();

                    List<Parameters> listParams3 = new List<Parameters>();
                    listParams3.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader3 = connex.procStockQuery("[Concert.getArtisteForConcert]", listParams2);

                    while (reader3.Read())
                    {
                        var artiste = from o in lstArtiste
                                      where o.IdArtiste.Equals(Convert.ToInt16(reader3["id_artiste"].ToString()))
                                      select o;

                        concert.Artiste.Add(artiste.First());
                    }

                    reader3.Close();
                    connex.disconnect();
                }

                return listConcert;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_id"></param>
        /// <returns></returns>
        public ConcertInfo getConcertById(int p_id)
        {
            try
            {
                ConcertInfo concert = new ConcertInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Concert.getConcertById]", listParams);

                while (reader.Read())
                {
                    concert.IdConcert = Convert.ToInt16(reader["id_concert"].ToString());
                    concert.Date = Convert.ToDateTime(reader["concert_date"].ToString());
                    concert.Description = reader["concert_description"].ToString();
                    concert.Lieu = this.getLieuById(Convert.ToInt16(reader["id_lieu"].ToString()));
                    concert.Prix = Convert.ToInt16(reader["concert_prix"].ToString());
                    concert.Soustitre = reader["concert_subtitle"].ToString();
                    concert.Titre = reader["concert_title"].ToString();
                    concert.Photo = reader["concert_photo"].ToString();
                    concert.Creation = Convert.ToDateTime(reader["concert_creation"].ToString());

                    concert.Valid = (reader["concert_valid"].ToString().Equals("1")) ? true : false;
                    concert.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));
                    concert.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    concert.Genres = new List<GenreInfo>();
                    concert.Artiste = new List<ArtisteInfo>();

                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                List<ArtisteInfo> lstArtiste = this.getArtisteAll();
                

                connex.connect();

                List<Parameters> listParams2 = new List<Parameters>();
                listParams2.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                SqlDataReader reader2 = connex.procStockQuery("[Concert.getGenreForConcert]", listParams2);

                while (reader2.Read())
                {
                    var genre = from o in lstGenre
                                where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                select o;

                    concert.Genres.Add(genre.First());
                }

                reader2.Close();
                connex.disconnect();
                connex.connect();

                List<Parameters> listParams3 = new List<Parameters>();
                listParams3.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                SqlDataReader reader3 = connex.procStockQuery("[Concert.getArtisteForConcert]", listParams2);

                while (reader3.Read())
                {
                    var artiste = from o in lstArtiste
                                  where o.IdArtiste.Equals(Convert.ToInt16(reader3["id_artiste"].ToString()))
                                  select o;

                    concert.Artiste.Add(artiste.First());
                }

                reader3.Close();
                connex.disconnect();
                
                
                return concert;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_lieu"></param>
        /// <returns></returns>
        public List<ConcertInfo> getConcertByLieu(LieuInfo p_lieu)
        {
            try
            {
                List<ConcertInfo> listConcert = new List<ConcertInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idLieu", DbType = System.Data.SqlDbType.BigInt, Value = p_lieu.IdLieu.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Concert.getConcertByLieu]", listParams);

                while (reader.Read())
                {
                    ConcertInfo concert = new ConcertInfo();
                    concert.IdConcert = Convert.ToInt16(reader["id_concert"].ToString());
                    concert.Date = Convert.ToDateTime(reader["concert_date"].ToString());
                    concert.Description = reader["concert_description"].ToString();
                    concert.Lieu = p_lieu;
                    concert.Prix = Convert.ToInt16(reader["concert_prix"].ToString());
                    concert.Soustitre = reader["concert_subtitle"].ToString();
                    concert.Titre = reader["concert_title"].ToString();
                    concert.Photo = reader["concert_photo"].ToString();
                    concert.Creation = Convert.ToDateTime(reader["concert_creation"].ToString());

                    concert.Valid = (reader["concert_valid"].ToString().Equals("1")) ? true : false;
                    concert.Validater = this.getUserById(Convert.ToInt16(reader["id_validater"].ToString()));
                    concert.Createur = this.getUserById(Convert.ToInt16(reader["id_createur"].ToString()));

                    concert.Genres = new List<GenreInfo>();
                    concert.Artiste = new List<ArtisteInfo>();

                    listConcert.Add(concert);
                }

                reader.Close();
                connex.disconnect();

                List<GenreInfo> lstGenre = this.getGenreAll();
                List<ArtisteInfo> lstArtiste = this.getArtisteAll();
                foreach (ConcertInfo concert in listConcert)
                {

                    connex.connect();

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Concert.getGenreForConcert]", listParams2);

                    while (reader2.Read())
                    {
                        var genre = from o in lstGenre
                                    where o.IdGenre.Equals(Convert.ToInt16(reader2["id_genre"].ToString()))
                                    select o;

                        concert.Genres.Add(genre.First());
                    }

                    reader2.Close();
                    connex.disconnect();
                    connex.connect();

                    List<Parameters> listParams3 = new List<Parameters>();
                    listParams3.Add(new Parameters { Param = "@idConcert", DbType = System.Data.SqlDbType.BigInt, Value = concert.IdConcert.ToString() });
                    SqlDataReader reader3 = connex.procStockQuery("[Concert.getArtisteForConcert]", listParams2);

                    while (reader3.Read())
                    {
                        var artiste = from o in lstArtiste
                                      where o.IdArtiste.Equals(Convert.ToInt16(reader3["id_artiste"].ToString()))
                                      select o;

                        concert.Artiste.Add(artiste.First());
                    }

                    reader3.Close();
                    connex.disconnect();
                }

                return listConcert;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        #endregion
        
        #region Contact
        public List<ContactInfo> getContactAll()
        {
            try
            {
                List<ContactInfo> listContact = new List<ContactInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Contact.getContactAll]", listParams);

                while (reader.Read())
                {
                    ContactInfo contact = new ContactInfo();
                    contact.IdContact = Convert.ToInt16(reader["id_contact"].ToString());
                    contact.Mail = reader["contact_mail"].ToString();

                    listContact.Add(contact);
                }

                reader.Close();
                connex.disconnect();

                return listContact;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public ContactInfo getContactById(int p_contact)
        {
            try
            {
                ContactInfo contact = new ContactInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();
                listParams.Add(new Parameters { Param = "@id", DbType = System.Data.SqlDbType.BigInt, Value = p_contact.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Contact.getContactById]", listParams);

                while (reader.Read())
                {
                    contact.IdContact = Convert.ToInt16(reader["id_contact"].ToString());
                    contact.Mail = reader["contact_mail"].ToString();
                }

                reader.Close();
                connex.disconnect();

                return contact;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        #endregion
        
        #region Droit
        public List<DroitInfo> getDroitAll()
        {
            try
            {
                List<DroitInfo> listDroit = new List<DroitInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Droit.getDroitAll]", listParams);

                while (reader.Read())
                {
                    DroitInfo droit = new DroitInfo();
                    droit.IdDroit = Convert.ToInt16(reader["id_droit"].ToString());
                    droit.Nom = reader["droit_name"].ToString();

                    listDroit.Add(droit);
                }

                reader.Close();
                connex.disconnect();

                return listDroit;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public DroitInfo getDroitById(int p_id)
        {
            try
            {
                DroitInfo droit = new DroitInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idDroit", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Droit.getDroitById]", listParams);

                while (reader.Read())
                {
                    droit.IdDroit = Convert.ToInt16(reader["id_droit"].ToString());
                    droit.Nom = reader["droit_name"].ToString();

                }

                reader.Close();
                connex.disconnect();

                return droit;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        #endregion

        #region Genre
        public List<GenreInfo> getGenreAll()
        {
            try
            {
                List<GenreInfo> listGenre = new List<GenreInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Genre.getGenreAll]", listParams);

                while (reader.Read())
                {
                    GenreInfo genre = new GenreInfo();
                    genre.IdGenre = Convert.ToInt16(reader["id_genre"].ToString());
                    genre.Name = reader["genre_name"].ToString();

                    listGenre.Add(genre);
                }

                reader.Close();
                connex.disconnect();

                return listGenre;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public GenreInfo getGenreById(int p_id)
        {
            try
            {
                GenreInfo genre = new GenreInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idGenre", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });

                SqlDataReader reader = connex.procStockQuery("[Genre.getGenreById]", listParams);

                while (reader.Read())
                {
                    genre.IdGenre = Convert.ToInt16(reader["id_genre"].ToString());
                    genre.Name = reader["genre_name"].ToString();

                }

                reader.Close();
                connex.disconnect();

                return genre;
            }
            catch (Exception ex)
            {