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

        #region Lien
        public List<LienInfo> getLienAll()
        {
            try
            {
                List<LienInfo> listLien = new List<LienInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Liens.getLiensAll]", listParams);

                while (reader.Read())
                {
                    LienInfo lien = new LienInfo();
                    lien.IdLien = Convert.ToInt16(reader["id_lien"].ToString());
                    lien.Adresse = reader["lien_adresse"].ToString();
                    lien.Nom = reader["lien_nom"].ToString();
                    lien.Description = reader["lien_description"].ToString();

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

                    listLien.Add(lien);
                }

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

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

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

                SqlDataReader reader = connex.procStockQuery("[Liens.getLiensById]", listParams);

                while (reader.Read())
                {
                    lien.IdLien = Convert.ToInt16(reader["id_lien"].ToString());
                    lien.Adresse = reader["lien_adresse"].ToString();
                    lien.Nom = reader["lien_nom"].ToString();
                    lien.Description = reader["lien_description"].ToString();

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

                }

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

                return lien;
            }
            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 Lieu
        public List<LieuInfo> getLieuAll()
        {
            try
            {
                List<LieuInfo> listLieux = new List<LieuInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Lieux.getLieuAll]", listParams);

                while (reader.Read())
                {
                    LieuInfo lieu = new LieuInfo();
                    lieu.IdLieu = Convert.ToInt16(reader["id_lieu"].ToString());
                    lieu.Adresse = reader["lieu_adresse"].ToString();
                    lieu.Cp = reader["lieu_cp"].ToString();
                    lieu.Web = reader["lieu_web"].ToString();
                    lieu.Name = reader["lieu_name"].ToString();
                    lieu.Description = reader["lieu_description"].ToString();
                    lieu.Type = this.getTypeById(Convert.ToInt16(reader["id_type"].ToString()));
                    lieu.Ville = reader["lieu_ville"].ToString();
                    lieu.Creation = Convert.ToDateTime(reader["lieu_creation"].ToString());

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

                    listLieux.Add(lieu);
                }

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

                List<GenreInfo> lstGenre = this.getGenreAll();
                foreach (LieuInfo lieu in listLieux)
                {
                    connex.connect();

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

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idLieu", DbType = System.Data.SqlDbType.BigInt, Value = lieu.IdLieu.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Lieux.getGenreForLieu]", listParams2);

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

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

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

                return listLieux;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public List<LieuInfo> getLieuByGenre(GenreInfo p_genre)
        {
            try
            {
                List<LieuInfo> listLieux = new List<LieuInfo>();
                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("[Lieux.getLieuByGenre]", listParams);

                while (reader.Read())
                {
                    LieuInfo lieu = new LieuInfo();
                    lieu.IdLieu = Convert.ToInt16(reader["id_lieu"].ToString());
                    lieu.Adresse = reader["lieu_adresse"].ToString();
                    lieu.Cp = reader["lieu_cp"].ToString();
                    lieu.Web = reader["lieu_web"].ToString();
                    lieu.Name = reader["lieu_name"].ToString();
                    lieu.Description = reader["lieu_description"].ToString();
                    lieu.Type = this.getTypeById(Convert.ToInt16(reader["id_type"].ToString()));
                    lieu.Ville = reader["lieu_ville"].ToString();
                    lieu.Creation = Convert.ToDateTime(reader["lieu_creation"].ToString());

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

                    listLieux.Add(lieu);
                }

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

                List<GenreInfo> lstGenre = this.getGenreAll();
                foreach (LieuInfo lieu in listLieux)
                {
                    connex.connect();

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

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idLieu", DbType = System.Data.SqlDbType.BigInt, Value = lieu.IdLieu.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Lieux.getGenreForLieu]", listParams2);

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

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

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

                return listLieux;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public List<LieuInfo> getLieuByType(TypeInfo p_type)
        {
            try
            {
                List<LieuInfo> listLieux = new List<LieuInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

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

                SqlDataReader reader = connex.procStockQuery("[Lieux.getLieuByType]", listParams);

                while (reader.Read())
                {
                    LieuInfo lieu = new LieuInfo();
                    lieu.IdLieu = Convert.ToInt16(reader["id_lieu"].ToString());
                    lieu.Adresse = reader["lieu_adresse"].ToString();
                    lieu.Cp = reader["lieu_cp"].ToString();
                    lieu.Web = reader["lieu_web"].ToString();
                    lieu.Name = reader["lieu_name"].ToString();
                    lieu.Description = reader["lieu_description"].ToString();
                    lieu.Type = p_type;
                    lieu.Ville = reader["lieu_ville"].ToString();
                    lieu.Creation = Convert.ToDateTime(reader["lieu_creation"].ToString());

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

                    listLieux.Add(lieu);
                }

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

                List<GenreInfo> lstGenre = this.getGenreAll();
                foreach (LieuInfo lieu in listLieux)
                {
                    connex.connect();

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

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@idLieu", DbType = System.Data.SqlDbType.BigInt, Value = lieu.IdLieu.ToString() });
                    SqlDataReader reader2 = connex.procStockQuery("[Lieux.getGenreForLieu]", listParams2);

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

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

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

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

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

                SqlDataReader reader = connex.procStockQuery("[Lieux.getLieuById]", listParams);

                while (reader.Read())
                {
                    lieu.IdLieu = Convert.ToInt16(reader["id_lieu"].ToString());
                    lieu.Adresse = reader["lieu_adresse"].ToString();
                    lieu.Cp = reader["lieu_cp"].ToString();
                    lieu.Web = reader["lieu_web"].ToString();
                    lieu.Name = reader["lieu_name"].ToString();
                    lieu.Description = reader["lieu_description"].ToString();
                    lieu.Type = this.getTypeById(Convert.ToInt16(reader["id_type"].ToString()));
                    lieu.Ville = reader["lieu_ville"].ToString();
                    lieu.Creation = Convert.ToDateTime(reader["lieu_creation"].ToString());

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

                }

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

                List<GenreInfo> lstGenre = this.getGenreAll();
                
                connex.connect();

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

                List<Parameters> listParams2 = new List<Parameters>();
                listParams2.Add(new Parameters { Param = "@idLieu", DbType = System.Data.SqlDbType.BigInt, Value = lieu.IdLieu.ToString() });
                SqlDataReader reader2 = connex.procStockQuery("[Lieux.getGenreForLieu]", listParams2);

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

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

                reader2.Close();
                connex.disconnect();
                
                return lieu;
            }
            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 Liste
        public List<ListeInfo> getListeAll()
        {
            try
            {
                List<ListeInfo> listListe = new List<ListeInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Liste.getListeAll]", listParams);

                while (reader.Read())
                {
                    ListeInfo liste = new ListeInfo();
                    liste.IdListe = Convert.ToInt16(reader["id_liste"].ToString());
                    liste.Name = reader["liste_name"].ToString();
                    liste.Description = reader["liste_description"].ToString();

                    Connection connex2 = new Connection();
                    connex2.connect();

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@id", DbType = System.Data.SqlDbType.BigInt, Value = liste.IdListe.ToString() });
                    SqlDataReader reader2 = connex2.procStockQuery("[Liste.getContactForListe]", listParams2);

                    liste.Contacts = new List<ContactInfo>();

                    while (reader2.Read())
                    {
                        ContactInfo contact = this.getContactById(Convert.ToInt16(reader2["id_contact"].ToString()));
                        liste.Contacts.Add(contact);
                    }

                    reader2.Close();


                    listListe.Add(liste);
                }

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

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

                SqlDataReader reader = connex.procStockQuery("[Liste.getListeById]", listParams);

                while (reader.Read())
                {
                    liste.IdListe = Convert.ToInt16(reader["id_liste"].ToString());
                    liste.Name = reader["liste_name"].ToString();
                    liste.Description = reader["liste_description"].ToString();

                    Connection connex2 = new Connection();
                    connex2.connect();

                    List<Parameters> listParams2 = new List<Parameters>();
                    listParams2.Add(new Parameters { Param = "@id", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });
                    SqlDataReader reader2 = connex2.procStockQuery("[Liste.getContactForListe]", listParams2);

                    liste.Contacts = new List<ContactInfo>();

                    while (reader2.Read())
                    {
                        ContactInfo contact = this.getContactById(Convert.ToInt16(reader2["id_contact"].ToString()));
                        liste.Contacts.Add(contact);
                    }

                    reader2.Close();


                }

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

                return liste;
            }
            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 News
        public List<NewsInfo> getNewsAll()
        {
            try
            {
                List<NewsInfo> listNews = new List<NewsInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[News.getNewsAll]", listParams);

                while (reader.Read())
                {
                    NewsInfo news = new NewsInfo();
                    news.IdNews = Convert.ToInt16(reader["id_news"].ToString());
                    news.Categorie = this.getCategorieById(Convert.ToInt16(reader["id_categorie"].ToString()));
                    news.Contenu = reader["news_contenu"].ToString();
                    news.Creation = Convert.ToDateTime(reader["news_creation"].ToString());
                    news.Fin = Convert.ToDateTime(reader["news_fin"].ToString());
                    news.Publication = Convert.ToDateTime(reader["news_parution"].ToString());
                    news.Publie = (reader["news_publie"].ToString().Equals("1")) ? true : false;
                    news.Titre = reader["news_titre"].ToString();

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


                    listNews.Add(news);
                }

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

                return listNews;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public List<NewsInfo> getNewsByCategorie(CategorieInfo p_categorie)
        {
            try
            {
                List<NewsInfo> listNews = new List<NewsInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@idCategorie", DbType = System.Data.SqlDbType.BigInt, Value = p_categorie.IdCategorie.ToString() });
                  
                SqlDataReader reader = connex.procStockQuery("[News.getNewsByCategorie]", listParams);

                while (reader.Read())
                {
                    NewsInfo news = new NewsInfo();
                    news.IdNews = Convert.ToInt16(reader["id_news"].ToString());
                    news.Categorie = p_categorie;
                    news.Contenu = reader["news_contenu"].ToString();
                    news.Creation = Convert.ToDateTime(reader["news_creation"].ToString());
                    news.Fin = Convert.ToDateTime(reader["news_fin"].ToString());
                    news.Publication = Convert.ToDateTime(reader["news_parution"].ToString());
                    news.Publie = (reader["news_publie"].ToString().Equals("1")) ? true : false;
                    news.Titre = reader["news_titre"].ToString();

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


                    listNews.Add(news);
                }

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

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

                listParams.Add(new Parameters { Param = "@idNews", DbType = System.Data.SqlDbType.BigInt, Value = p_id.ToString() });
                 
                SqlDataReader reader = connex.procStockQuery("[News.getNewsById]", listParams);

                while (reader.Read())
                {
                    news.IdNews = Convert.ToInt16(reader["id_news"].ToString());
                    news.Categorie = this.getCategorieById(Convert.ToInt16(reader["id_categorie"].ToString()));
                    news.Contenu = reader["news_contenu"].ToString();
                    news.Creation = Convert.ToDateTime(reader["news_creation"].ToString());
                    news.Fin = Convert.ToDateTime(reader["news_fin"].ToString());
                    news.Publication = Convert.ToDateTime(reader["news_parution"].ToString());
                    news.Publie = (reader["news_publie"].ToString().Equals("1")) ? true : false;
                    news.Titre = reader["news_titre"].ToString();

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

                }

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

                return news;
            }
            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 Type de lieu
        public List<TypeInfo> getTypeAll()
        {
            try
            {
                List<TypeInfo> listType = new List<TypeInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Type.getTypeAll]", listParams);

                while (reader.Read())
                {
                    TypeInfo type = new TypeInfo();
                    type.IdType = Convert.ToInt16(reader["id_type"].ToString());
                    type.Name = reader["type_name"].ToString();
                    type.Description = reader["type_description"].ToString();

                    listType.Add(type);
                }

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

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

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

                SqlDataReader reader = connex.procStockQuery("[Type.getTypeById]", listParams);

                while (reader.Read())
                {
                    type.IdType = Convert.ToInt16(reader["id_type"].ToString());
                    type.Name = reader["type_name"].ToString();
                    type.Description = reader["type_description"].ToString();
                }

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

                return type;
            }
            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 User
        public List<UserInfo> getUserAll()
        {
            try
            {
                List<UserInfo> listUser = new List<UserInfo>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[User.getUserAll]", listParams);

                while (reader.Read())
                {
                    UserInfo user = new UserInfo();

                    user.IdUser = Convert.ToInt16(reader["id_user"].ToString());
                    user.Inscription = Convert.ToDateTime(reader["user_inscription"].ToString());
                    user.Droit = this.getDroitById(Convert.ToInt16(reader["id_droit"].ToString()));
                    user.LastVisit = Convert.ToDateTime(reader["user_lastVist"].ToString());
                    user.Login = reader["user_login"].ToString();
                    user.Mail = reader["user_mail"].ToString();

                    user.Valid = (reader["user_valid"].ToString().Equals("1")) ? true : false;

                    listUser.Add(user);
                }

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

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

                listParams.Add(new Parameters { Param = "@idDroit", DbType = System.Data.SqlDbType.BigInt, Value = p_droit.IdDroit.ToString() });
                 
                SqlDataReader reader = connex.procStockQuery("[User.getUserByDroit]", listParams);

                while (reader.Read())
                {
                    UserInfo user = new UserInfo();

                    user.IdUser = Convert.ToInt16(reader["id_user"].ToString());
                    user.Inscription = Convert.ToDateTime(reader["user_inscription"].ToString());
                    user.Droit = p_droit;
                    user.LastVisit = Convert.ToDateTime(reader["user_lastVist"].ToString());
                    user.Login = reader["user_login"].ToString();
                    user.Mail = reader["user_mail"].ToString();

                    user.Valid = (reader["user_valid"].ToString().Equals("1")) ? true : false;

                    listUser.Add(user);
                }

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

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

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

                SqlDataReader reader = connex.procStockQuery("[User.getUserById]", listParams);

                while (reader.Read())
                {
                    
                    user.IdUser = Convert.ToInt16(reader["id_user"].ToString());
                    user.Inscription = Convert.ToDateTime(reader["user_inscription"].ToString());
                    user.Droit = this.getDroitById(Convert.ToInt16(reader["id_droit"].ToString()));
                    user.LastVisit = Convert.ToDateTime(reader["user_lastVist"].ToString());
                    user.Login = reader["user_login"].ToString();
                    user.Mail = reader["user_mail"].ToString();

                    user.Valid = (reader["user_valid"].ToString().Equals("1")) ? true : false;

                }

                if (!reader.HasRows)
                {
                    reader.Close();
                    connex.disconnect();
                    return null;
                }

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

                return user;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return null;
            }
        }
        public UserInfo getUserByName(String p_name)
        {
            try
            {
                UserInfo user = new UserInfo();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@name", DbType = System.Data.SqlDbType.VarChar, Value = p_name });

                SqlDataReader reader = connex.procStockQuery("[User.getUserByName]", listParams);

                while (reader.Read())
                {

                    user.IdUser = Convert.ToInt16(reader["id_user"].ToString());
                    user.Inscription = Convert.ToDateTime(reader["user_inscription"].ToString());
                    user.Droit = this.getDroitById(Convert.ToInt16(reader["id_droit"].ToString()));
                    user.LastVisit = Convert.ToDateTime(reader["user_lastVist"].ToString());
                    user.Login = reader["user_login"].ToString();
                    user.Mail = reader["user_mail"].ToString();

                    user.Valid = (reader["user_valid"].ToString().Equals("1")) ? true : false;

                }

                if (!reader.HasRows)
                {
                    reader.Close();
                    connex.disconnect();
                    return null;
                }

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

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

        public String getPasswordByUserName(String p_name)
        {
            try
            {
                String pwd = String.Empty;
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@name", DbType = System.Data.SqlDbType.VarChar, Value = p_name });

                SqlDataReader reader = connex.procStockQuery("[User.getPasswordByUserName]", listParams);

                while (reader.Read())
                {

                    pwd = reader[0].ToString();

                }

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

                return pwd;
            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return String.Empty;
            }
        }
        public Boolean ifUserNameExist(String p_name)
        {
            try
            {
                Boolean ifExist = false;
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                listParams.Add(new Parameters { Param = "@name", DbType = System.Data.SqlDbType.VarChar, Value = p_name });

                SqlDataReader reader = connex.procStockQuery("[User.ifUserNameExist]", listParams);

                while (reader.Read())
                {
                    String response = reader[0].ToString();
                    if (response.Equals("1"))
                        ifExist = true;

                }

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

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

        public void updateLastVisit(UserInfo p_user)
        {
            try
            {;
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

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

                SqlDataReader reader = connex.procStockQuery("[User.UpdateLastVisit]", listParams);

                while (reader.Read())
                {
                    
                }

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

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

        #region Autres
        public void setStatistics(Statistics stats)
        {
            try
            {
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();
                listParams.Add(new Parameters { Param = "@srid", DbType = SqlDbType.VarChar, Value = stats.SRID });
                listParams.Add(new Parameters { Param = "@ip", DbType = SqlDbType.VarChar, Value = stats.IpClient });
                listParams.Add(new Parameters { Param = "@browser", DbType = SqlDbType.VarChar, Value = stats.Navigateur });
                listParams.Add(new Parameters { Param = "@browser_version", DbType = SqlDbType.VarChar, Value = stats.Nav_Version });
                listParams.Add(new Parameters { Param = "@os", DbType = SqlDbType.VarChar, Value = stats.OS });
                listParams.Add(new Parameters { Param = "@referer", DbType = SqlDbType.VarChar, Value = (String.IsNullOrEmpty(stats.AdresseAppelante))? "" : stats.AdresseAppelante });
                listParams.Add(new Parameters { Param = "@language", DbType = SqlDbType.VarChar, Value = stats.Langue });

                SqlDataReader reader = connex.procStockQuery("[Stat.AjoutStat]", listParams);
                int i = 0;

                while (reader.Read())
                {
                    i = Convert.ToInt16(reader[0].ToString());
                }

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

            }
            catch (Exception ex)
            {
                connex.disconnect();
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
            }
        }
        public List<Statistics> getStatAll()
        {
            try
            {
                List<Statistics> listStat = new List<Statistics>();
                connex.connect();
                List<Parameters> listParams = new List<Parameters>();

                SqlDataReader reader = connex.procStockQuery("[Stat.getAllStat]", listParams);

                while (reader.Read())
                {
                    Statistics stat = new Statistics();

                    stat.SRID = reader["stat_srid"].ToString();
                    stat.IpClient = reader["stat_ip"].ToString();
                    stat.Navigateur = reader["stat_browser"].ToString();
                    stat.Nav_Version = reader["stat_browser_version"].ToString();
                    stat.OS = reader["stat_os"].ToString();
                    stat.AdresseAppelante = reader["stat_referer"].ToString();
                    stat.Langue = reader["stat_language"].ToString();
                    stat.date = DateTime.Parse(reader["stat_date"].ToString());


                    listStat.Add(stat);
                }

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

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

        public Boolean sendMail(String p_destinataire, String objet, String message, Boolean isHTML)
        {
            return sendMail("admin@musicatoulouse.com", p_destinataire, objet, message, isHTML);
        }
        public Boolean sendMail(String p_expediteur, String p_destinataire, String objet, String message, Boolean isHTML)
        {            
            MailMessage email = new MailMessage();

            
            email.From = new MailAddress(p_expediteur, "Music@Toulouse");
            email.To.Add(new MailAddress(p_destinataire));
            email.IsBodyHtml = isHTML;
            email.Subject = objet;
            email.Body = message;
            SmtpClient smtp = new SmtpClient();
           
            try
            {
                smtp = new SmtpClient("smtp.gmail.com", 587);
                smtp.Credentials = new System.Net.NetworkCredential("musicatoulouse@gmail.com", "aze021ail");
                smtp.EnableSsl = true;
                smtp.Send(email);
            }
            catch (Exception ex)
            {
                Log log = new Log("metier.txt");
                log.write(DateTime.Now, ex.Message, ex.Source, ex.StackTrace);
                return false;
            }


            return true;
        }
        
        public string GeneratePassword()
        {
            String caracteres = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
            char[] tab = caracteres.ToCharArray();
            StringBuilder password = new StringBuilder();
            Random rd = new Random();
            for (int i = 0; i < 8; i++)
            {
                password.Append(tab[rd.Next(tab.Length)]);
            }
            return password.ToString();
        }
        public string EncodePassword(string originalPassword)
        {
            // First we need to convert the string into bytes, which
            // means using a text encoder.
            Encoder enc = System.Text.Encoding.Unicode.GetEncoder();

            // Create a buffer large enough to hold the string
            byte[] unicodeText = new byte[originalPassword.Length * 2];
            enc.GetBytes(originalPassword.ToCharArray(), 0, originalPassword.Length, unicodeText, 0, true);

            // Now that we have a byte array we can ask the CSP to hash it
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);

            // Build the final string by converting each byte
            // into hex and appending it to a StringBuilder
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < result.Length; i++)
            {
                sb.Append(result[i].ToString("X2"));
            }

            // And return it
            return sb.ToString();

        }

        #endregion

        #endregion

        private void log(String message)
        {
            Log logg = new Log("log.txt");
            logg.write(DateTime.Now.ToString("dd/mm/yyyy - hh:mm:ss.ffff") + " - " + message);
        }

    }
    /// <summary>
    /// 
    /// </summary>
    public class Statistics
    {
        public String SRID
        { get; set; }
        public String Navigateur
        { get; set; }
        public String Nav_Version
        { get; set; }
        public String OS
        { get; set; }
        public String Langue
        { get; set; }
        public String AdresseAppelante
        { get; set; }
        public String IpClient
        { get; set; }
        public DateTime date
        { get; set; }
    }