public List <string> GetListeIdentifiant(string identifiant) { List <String> reponse = new List <String>(); Connexion connexion = new Connexion(); string sql = "select identifiants from utilisateur where identifiants like '" + identifiant + "%'"; MySqlCommand command = new MySqlCommand(sql, connexion.GetConnection()); MySqlDataReader dataReader; connexion.GetConnection().Open(); dataReader = command.ExecuteReader(); PosteDAO posteDAO = new PosteDAO(); try { while (dataReader.Read()) { reponse.Add(dataReader["IDENTIFIANTS"].ToString()); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public Utilisateur GetUtilisateurByIdentifiant(string identifiants) { Utilisateur reponse = new Utilisateur(); Connexion connexion = new Connexion(); string sql = "select * from utilisateur where IDENTIFIANTS='" + identifiants + "'"; MySqlCommand command = new MySqlCommand(sql, connexion.GetConnection()); MySqlDataReader dataReader; connexion.GetConnection().Open(); dataReader = command.ExecuteReader(); PosteDAO posteDAO = new PosteDAO(); try { if (dataReader.Read().ToString() == "True") { Poste poste = posteDAO.GetPosteById(dataReader["ID_POSTE"].ToString()); reponse = new Utilisateur(dataReader["ID_UTILISATEUR"].ToString(), poste, dataReader["NOM_UTILISATEUR"].ToString(), dataReader["PRENOMS"].ToString(), dataReader["IDENTIFIANTS"].ToString(), dataReader["MDP"].ToString()); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public Utilisateur GetMagasinier(string mdp) { Utilisateur reponse = new Utilisateur(); Connexion connexion = new Connexion(); string sql = "select * from utilisateur where MDP = '" + mdp + "' and id_poste='2'"; MySqlCommand command = new MySqlCommand(sql, connexion.GetConnection()); MySqlDataReader dataReader; connexion.GetConnection().Open(); dataReader = command.ExecuteReader(); PosteDAO posteDAO = new PosteDAO(); try { while (dataReader.Read()) { Poste poste = posteDAO.GetPosteById(dataReader["ID_POSTE"].ToString()); reponse = new Utilisateur(dataReader["ID_UTILISATEUR"].ToString(), poste, dataReader["NOM_UTILISATEUR"].ToString(), dataReader["PRENOMS"].ToString(), dataReader["IDENTIFIANTS"].ToString(), dataReader["MDP"].ToString()); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public List <string> GetNomBinome(string nom) { List <string> reponse = new List <string>(); Connexion connexion = new Connexion(); string sql = "select distinct prenoms from utilisateur where prenoms like '" + nom + "%' and id_poste='3'"; MySqlCommand command = new MySqlCommand(sql, connexion.GetConnection()); MySqlDataReader dataReader; connexion.GetConnection().Open(); dataReader = command.ExecuteReader(); try { while (dataReader.Read()) { reponse.Add(dataReader["prenoms"].ToString()); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public void CreateUtilisateur(Utilisateur utilisateur) { Connexion connexion = new Connexion(); MySqlConnection connection = connexion.GetConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction transaction; transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; string sql = "INSERT INTO UTILISATEUR (ID_POSTE,NOM_UTILISATEUR,PRENOMS,IDENTIFIANTS,MDP,ETAT,etatmdp) values ('" + utilisateur.Poste.IdPoste + "','" + utilisateur.NomUtilisateur + "','" + utilisateur.Prenoms + "','" + utilisateur.Identifiants + "','" + utilisateur.Mdp + "','" + utilisateur.Etat + "','1')"; try { this.ExcecuteCommande(command, sql); if (transaction != null) { transaction.Commit(); } } catch (Exception exception) { transaction.Rollback(); throw exception; } finally { connexion.CloseAll(command, transaction, connection); } }
public void UpdateMdp(Utilisateur utilisateur) { Connexion connexion = new Connexion(); MySqlConnection connection = connexion.GetConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction transaction; transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; string sql = "UPDATE UTILISATEUR SET MDP ='" + utilisateur.Mdp + "', etatmdp='1' where ID_UTILISATEUR='" + utilisateur.IdUtilisateur + "'"; try { this.ExcecuteCommande(command, sql); if (transaction != null) { transaction.Commit(); } } catch (Exception exception) { transaction.Rollback(); throw exception; } finally { connexion.CloseAll(command, transaction, connection); } }
public List <Utilisateur> GetUtilisateurValidation(string etat) { List <Utilisateur> reponse = new List <Utilisateur>(); Connexion connexion = new Connexion(); string sql = "select * from utilisateur where etat='" + etat + "'"; MySqlCommand command = new MySqlCommand(sql, connexion.GetConnection()); MySqlDataReader dataReader; connexion.GetConnection().Open(); dataReader = command.ExecuteReader(); PosteDAO posteDAO = new PosteDAO(); try { while (dataReader.Read()) { Poste poste = posteDAO.GetPosteById(dataReader["ID_POSTE"].ToString()); reponse.Add(new Utilisateur(dataReader["ID_UTILISATEUR"].ToString(), poste, dataReader["NOM_UTILISATEUR"].ToString(), dataReader["PRENOMS"].ToString(), dataReader["IDENTIFIANTS"].ToString(), dataReader["MDP"].ToString())); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public Utilisateur GetUtilisateurByLogin(LoginUsers loginUsers) { Utilisateur reponse = new Utilisateur(); Connexion connexion = new Connexion(); string sql = "select * from utilisateur where IDENTIFIANTS='" + loginUsers.Identifiant + "' and MDP = '" + loginUsers.Mdp + "' and ETAT='1'"; MySqlCommand command = new MySqlCommand(sql, connexion.GetConnection()); MySqlDataReader dataReader; connexion.GetConnection().Open(); dataReader = command.ExecuteReader(); PosteDAO posteDAO = new PosteDAO(); try { if (dataReader.Read().ToString() == "True") { Poste poste = posteDAO.GetPosteById(dataReader["ID_POSTE"].ToString()); reponse = new Utilisateur(dataReader["ID_UTILISATEUR"].ToString(), poste, dataReader["NOM_UTILISATEUR"].ToString(), dataReader["PRENOMS"].ToString(), dataReader["IDENTIFIANTS"].ToString(), dataReader["MDP"].ToString()); reponse.EtatMdp = dataReader["etatmdp"].ToString(); } else { throw new Exception("Identifiants / mot de passe invalide"); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public void createCommande(Commande commande) { Connexion connexion = new Connexion(); MySqlConnection connection = connexion.GetConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction transaction; transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; try { this.AjoutCommande(command, commande); if (transaction != null) { transaction.Commit(); } } catch (Exception exception) { transaction.Rollback(); throw exception; } finally { connexion.CloseAll(command, transaction, connection); } }
public void ConfirmUser(string id) { Connexion connexion = new Connexion(); MySqlConnection connection = connexion.GetConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction transaction; transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; string sql = "UPDATE UTILISATEUR SET ETAT ='1' where ID_UTILISATEUR='" + id + "'"; try { this.ExcecuteCommande(command, sql); if (transaction != null) { transaction.Commit(); } } catch (Exception exception) { transaction.Rollback(); throw exception; } finally { connexion.CloseAll(command, transaction, connection); } }
public List <Commande> GetListeCommandeAnnule(string dateDebut, string dateFin) { Connexion connexion = new Connexion(); string query = ""; if (dateDebut == "" && dateFin == "") { query = "select * from commande where ETAT = '000' and CLIENT='1'"; } else { query = "select * from commande where ETAT = '000' and CLIENT='1' and date_commande<='" + dateFin + "' and date_commande>='" + dateDebut + "'"; } MySqlCommand command = new MySqlCommand(query, connexion.GetConnection()); AccesSageDAO accesSageDAO = new AccesSageDAO(); MySqlDataReader dataReader; //Creation d'une liste List <Commande> reponse = new List <Commande>(); try { //Ouverture connexion if (connexion.OpenConnection() == true) { //Excecution de la commande dataReader = command.ExecuteReader(); //Lecture des donnees et stockage dans la liste while (dataReader.Read()) { Comptoir comptoir = accesSageDAO.GetComptoirById(dataReader["ID_COMPTOIR"].ToString()); Commande commande = new Commande(Int32.Parse(dataReader["id_commande"].ToString()), dataReader["DATE_COMMANDE"].ToString(), dataReader["NUMERO"].ToString(), comptoir, Int32.Parse(dataReader["CLIENT"].ToString()), dataReader["ETAT"].ToString()); List <DetailCommande> detailCommandes = GetArticlesCommandes(commande.Numero); for (int i = 0; i < detailCommandes.Count; i++) { int quantite = detailCommandes[i].Quantite; detailCommandes[i].Article = accesSageDAO.GetArticleByReferences(detailCommandes[i].Article.References); detailCommandes[i].Quantite = quantite; } commande.ListeDetailCommande = detailCommandes; reponse.Add(commande); } dataReader.Close(); } //return return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public List <StatDuree> GetStatDurees(string dateDebut, string dateFin) { Connexion connexion = new Connexion(); string query = ""; if (dateFin == "" && dateDebut == "") { query = "select commande.id_commande,TIMESTAMPDIFF(SECOND,heure_commande,heure_livraison) as duree from duree join commande on commande.id_commande=duree.id_commande where ETAT='111' and client='1'"; } else { query = "select commande.id_commande as id_commande,TIMESTAMPDIFF(SECOND,heure_commande,heure_livraison) as duree from duree join commande on commande.id_commande=duree.id_commande where date_commande<='" + dateFin + "' and date_commande>='" + dateDebut + "' and ETAT='111' and client='1'"; } MySqlCommand command = new MySqlCommand(query, connexion.GetConnection()); MySqlDataReader dataReader; //Creation d'une liste List <StatDuree> reponse = new List <StatDuree>(); try { //Ouverture connexion if (connexion.OpenConnection() == true) { //Excecution de la commande dataReader = command.ExecuteReader(); //Lecture des donnees et stockage dans la liste while (dataReader.Read()) { StatDuree stat = new StatDuree(); Commande commande = new Commande(); commande.IdCommande = Int32.Parse(dataReader["id_commande"].ToString()); stat.Commande = commande; stat.Minutes = Int32.Parse(dataReader["duree"].ToString()); reponse.Add(stat); } dataReader.Close(); } //return return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public StatCommande GetStatCommandesAnnule(string dateDebut, string dateFin) { Connexion connexion = new Connexion(); string query = ""; if (dateDebut == "" && dateFin == "") { query = "SELECT count(*) as total FROM commande where ETAT='000' and client='1'"; } else { query = "SELECT count(*) as total FROM commande where ETAT='000' and date_commande<='" + dateFin + "' and date_commande>='" + dateDebut + "' and client='1'"; } MySqlCommand command = new MySqlCommand(query, connexion.GetConnection()); AccesSageDAO accesSageDAO = new AccesSageDAO(); MySqlDataReader dataReader; //Creation d'une liste StatCommande reponse = new StatCommande(); try { //Ouverture connexion if (connexion.OpenConnection() == true) { //Excecution de la commande dataReader = command.ExecuteReader(); //Lecture des donnees et stockage dans la liste while (dataReader.Read()) { StatCommande stat = new StatCommande(); stat.Nombre = Int32.Parse(dataReader["total"].ToString()); reponse = stat; } dataReader.Close(); } //return return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public void Sortie(string id_commande, string id_magasinier, string id_binome) { Connexion connexion = new Connexion(); MySqlConnection connection = connexion.GetConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction transaction; transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; Duree duree = new Duree(); Commande commande = new Commande(); commande.IdCommande = Int32.Parse(id_commande); duree.Commande = commande; duree.HeureSortie = DateTime.Now; DureeDAO dureeDAO = new DureeDAO(); try { this.SortieCommande(command, commande); this.SortieStock(command, id_magasinier, id_binome, commande); dureeDAO.UpdateDuree(command, duree, "SORTIE"); if (transaction != null) { transaction.Commit(); } } catch (Exception exception) { transaction.Rollback(); throw exception; } finally { connexion.CloseAll(command, transaction, connection); } }
public void Valider(string id_commande) { Connexion connexion = new Connexion(); MySqlConnection connection = connexion.GetConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); MySqlTransaction transaction; transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; Duree duree = new Duree(); Commande commande = new Commande(); commande.IdCommande = Int32.Parse(id_commande); duree.Commande = commande; duree.HeureLivraison = DateTime.Now; DureeDAO dureeDAO = new DureeDAO(); try { this.ValiderCommande(command, id_commande); dureeDAO.UpdateDuree(command, duree, "LIVRAISON"); if (transaction != null) { transaction.Commit(); } } catch (Exception exception) { transaction.Rollback(); throw exception; } finally { connexion.CloseAll(command, transaction, connection); } }
public List <Commande> GetListeToutCommandeEnCours(string etat) { Connexion connexion = new Connexion(); string query = "SELECT * FROM commande where ETAT='" + etat + "'"; MySqlCommand command = new MySqlCommand(query, connexion.GetConnection()); AccesSageDAO accesSageDAO = new AccesSageDAO(); MySqlDataReader dataReader; //Creation d'une liste List <Commande> reponse = new List <Commande>(); try { //Ouverture connexion if (connexion.OpenConnection() == true) { //Excecution de la commande dataReader = command.ExecuteReader(); //Lecture des donnees et stockage dans la liste while (dataReader.Read()) { Comptoir comptoir = accesSageDAO.GetComptoirByNumTicket(dataReader["NUMERO"].ToString()); Commande commande = new Commande(Int32.Parse(dataReader["id_commande"].ToString()), dataReader["DATE_COMMANDE"].ToString(), dataReader["NUMERO"].ToString(), comptoir, Int32.Parse(dataReader["CLIENT"].ToString()), dataReader["ETAT"].ToString()); reponse.Add(commande); } dataReader.Close(); } //return return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public List <Poste> GetPostes() { Connexion connexion = new Connexion(); string query = "select * from poste where id_poste!='4'"; MySqlCommand command = new MySqlCommand(query, connexion.GetConnection()); MySqlDataReader dataReader; //Creation d'une liste List <Poste> reponse = new List <Poste>(); try { //Ouverture connexion if (connexion.OpenConnection() == true) { //Excecution de la commande dataReader = command.ExecuteReader(); //Lecture des donnees et stockage dans la liste while (dataReader.Read()) { Poste poste = new Poste(dataReader["ID_POSTE"].ToString(), dataReader["NOM_POSTE"].ToString()); reponse.Add(poste); } dataReader.Close(); } //return return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }
public List <Commande> GetListeCommande(string dateDebut, string dateFin) { Connexion connexion = new Connexion(); string query = ""; if (dateDebut == "" && dateFin == "") { query = "select * from commande join duree on duree.id_commande =commande.id_commande join sortie on sortie.id_commande=commande.id_commande where commande.ETAT = '111' and commande.CLIENT='1' group by sortie.id_commande"; } else { query = "select * from commande join duree on duree.id_commande =commande.id_commande join sortie on sortie.id_commande=commande.id_commande where commande.ETAT = '111' and commande.CLIENT='1' and commande.date_commande<='" + dateFin + "' and commande.date_commande>='" + dateDebut + "' group by sortie.id_commande"; } MySqlCommand command = new MySqlCommand(query, connexion.GetConnection()); AccesSageDAO accesSageDAO = new AccesSageDAO(); UtilisateurDAO utilisateurDAO = new UtilisateurDAO(); MySqlDataReader dataReader; //Creation d'une liste List <Commande> reponse = new List <Commande>(); try { //Ouverture connexion if (connexion.OpenConnection() == true) { //Excecution de la commande dataReader = command.ExecuteReader(); //Lecture des donnees et stockage dans la liste while (dataReader.Read()) { Comptoir comptoir = accesSageDAO.GetComptoirById(dataReader["ID_COMPTOIR"].ToString()); Commande commande = new Commande(Int32.Parse(dataReader["id_commande"].ToString()), dataReader["DATE_COMMANDE"].ToString(), dataReader["NUMERO"].ToString(), comptoir, Int32.Parse(dataReader["CLIENT"].ToString()), dataReader["ETAT"].ToString()); List <DetailCommande> detailCommandes = GetArticlesCommandes(commande.Numero); for (int i = 0; i < detailCommandes.Count; i++) { int quantite = detailCommandes[i].Quantite; detailCommandes[i].Article = accesSageDAO.GetArticleByReferences(detailCommandes[i].Article.References); detailCommandes[i].Quantite = quantite; } commande.ListeDetailCommande = detailCommandes; Duree duree = new Duree(); duree.HeureCommandeString = dataReader["HEURE_COMMANDE"].ToString(); duree.HeureSortieString = dataReader["HEURE_LIVRAISON"].ToString(); commande.Duree = duree; Utilisateur binome = utilisateurDAO.GetUtilisateurById(dataReader["ID_BINOME"].ToString()); Utilisateur magasinier = utilisateurDAO.GetUtilisateurById(dataReader["ID_MAGASINIER"].ToString()); Commande test = new Commande(); test.IdCommande = Int32.Parse(dataReader["id_commande"].ToString()); SortieCommande sortie = new SortieCommande(dataReader["ID_SORTIE"].ToString(), test, binome, magasinier); commande.SortieCommande = sortie; reponse.Add(commande); } dataReader.Close(); } return(reponse); } catch (Exception exception) { throw exception; } finally { connexion.CloseAll(command, null, connexion.GetConnection()); } }