public List <StatArticles> GetStatArticles(string dateDebut, string dateFin, string numeroEmplacement)
{
Connexion connexion = new Connexion();
string query = "";
if (dateFin == "" && dateDebut == "")
{
query = "select reference_article, count(*) as total from detail_commande join commande on commande.id_commande=detail_commande.id_commande where emplacement ='" + numeroEmplacement + "' group by reference_article";
}
else
{
query = "select reference_article, count(*) as total from detail_commande join commande on commande.id_commande=detail_commande.id_commande where emplacement ='" + numeroEmplacement + "' and date_commande<='" + dateFin + "' and date_commande>='" + dateDebut + "' group by reference_article";
}
MySqlCommand command = new MySqlCommand(query, connexion.GetConnection());
MySqlDataReader dataReader;
//Creation d'une liste
List <StatArticles> reponse = new List <StatArticles>();
AccesSageDAO accesSageDAO = new AccesSageDAO();
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())
{
StatArticles stat = new StatArticles();
Article article = accesSageDAO.GetArticleByReferences(dataReader["reference_article"].ToString());
stat.Article = article;
stat.Quantite = Int32.Parse(dataReader["total"].ToString());
reponse.Add(stat);
}
dataReader.Close();
}
//return
return(reponse);
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (command != null)
{
command.Dispose();
}
if (connexion.GetConnection() != null)
{
connexion.GetConnection().Close();
}
}
}
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 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 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 <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());
}
}