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());
}
}
