public void Add(Produit m)
{
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("insert into tproduit values(@id, @nom,@description,@cat, @prix,@img)", cnx);
cmd.Parameters.Add("@id", m.Id);//
cmd.Parameters.Add("@nom", m.Nom);
cmd.Parameters.Add("@description", m.Description);
cmd.Parameters.Add("@cat", m.IdCat.IdCat);
cmd.Parameters.Add("@prix", m.Prix);
cmd.Parameters.Add("@img", m.Img);
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Produit ajouter avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Pb de la requete de l'ajout\n " + ex.Message, "Attention");
}
}
public bool Conadmin(string s1, string s2)
{
// Employer m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tcompte where type=@type and nomUtilisateur=@ut and motPasse=@mPa", cnx);
cmd.Parameters.Add("@type", "admin");
cmd.Parameters.Add("@ut", s1);
cmd.Parameters.Add("@mPa", s2);// cmd.Parameters.Add("@id", id);
MySqlDataReader myReader = cmd.ExecuteReader();
// private int a;
while (myReader.Read())
{
a = a + 1;
}
if (a == 1)
{
bl = true;
}
else
{
bl = false;
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete cnx : Find \n " + ex.Message, "Attention");
}
return(bl);
}
public List <Produit> FindToCat(int cat)
{
CategorieDAO bdCat = new CategorieDAO();
List <Categorie> categories = bdCat.FindAll();
Categorie c = null;
List <Produit> res = new List <Produit>();
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tproduit where idCateg=@idCateg", cnx);
cmd.Parameters.Add("@idCateg", cat);//
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (Categorie med in categories)
{
if (med.IdCat.Equals(reader.GetInt32(3)))
{
c = med; break;
}
}
Produit m = new Produit(reader.GetInt32("id"), reader.GetString(1), reader.GetString(2), c, reader.GetDecimal(4), (byte[])reader[5]);
res.Add(m);
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Pb d'ouverture de la connexion \n " + ex.Message, "Attention");
}
return(res);
}
public void Update(int id, string n, string p, string se, DateTime dn, string tel, string email, string type, string ut, string mp, string adr, byte[] img, DateTime datIns)
{
try
{
cnx = ConnexionCoffe.GetInstance();//ouvrir la connexion
MySqlCommand cmd = new MySqlCommand("update tcompte set id=@id, nom=@nom, prenom=@prenom, sexe=@sexe,dateNaissance=@dateNaissance,telephone=@telephone,email=@email,type=@type,nomUtilisateur=@nomUtilisateur,motPasse=@motPasse,adresse=@adresse,image=@image,dateInscription=@dateInscription where id=@id", cnx);
cmd.Parameters.Add("@id", id); //
cmd.Parameters.Add("@nom", n); //
cmd.Parameters.Add("@prenom", p); //
cmd.Parameters.Add("@sexe", se); //
cmd.Parameters.Add("@dateNaissance", dn); //
cmd.Parameters.Add("@telephone", tel); //
cmd.Parameters.Add("@email", email); //
cmd.Parameters.Add("@type", type); //
cmd.Parameters.Add("@nomUtilisateur", ut); //
cmd.Parameters.Add("@motPasse", mp);
cmd.Parameters.Add("@adresse", adr); //
cmd.Parameters.Add("@image", img); //
cmd.Parameters.Add("@dateInscription", datIns); //
int x = cmd.ExecuteNonQuery();
if (x != 0)
{
MessageBox.Show("Employer est modifié avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Employer : Update\n " + ex.Message, "Attention");
}
}
public void Add(Employer m)
{
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("insert into tcompte values(@id, @nom, @prenom,@sexe,@dateNaissance,@telephone,@email,@type,@nomUtilisateur,@motPasse,@adresse,@image,@dateInscription)", cnx);
cmd.Parameters.Add("@id", m.Id);//
cmd.Parameters.Add("@nom", m.Nom);
cmd.Parameters.Add("@prenom", m.Prenom);
cmd.Parameters.Add("@sexe", m.Sexe);
cmd.Parameters.Add("@dateNaissance", m.DateNaiss);
cmd.Parameters.Add("@telephone", m.Tel);
cmd.Parameters.Add("@email", m.Email);
cmd.Parameters.Add("@type", m.Type);
cmd.Parameters.Add("@nomUtilisateur", m.NomUtilisateur);
cmd.Parameters.Add("@motPasse", m.Password);
cmd.Parameters.Add("@adresse", m.Adresse);
cmd.Parameters.Add("@image", m.Img);
cmd.Parameters.Add("@dateInscription", m.DateInscription);
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Utilisateur ajouter avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Pb de la requete de l'ajout\n " + ex.Message, "Attention");
}
}
public bool UpdateProduct(int ProductID, string ProductName, decimal ProductPrice, int ProductCategoryID, string ProductDescription, byte[] ProductPicture)
{
try
{
cnx = ConnexionCoffe.GetInstance();//ouvrir la connexion
MySqlCommand cmd = new MySqlCommand("update tproduit set id=@id, nom=@nom, description=@description, idcateg=@idcateg,prix=@prix,image=@image where id=@id", cnx);
cmd.Parameters.Add("@id", ProductID); //
cmd.Parameters.Add("@nom", ProductName); //
cmd.Parameters.Add("@description", ProductDescription); //
cmd.Parameters.Add("@idcateg", ProductCategoryID); //
cmd.Parameters.Add("@prix", ProductPrice); //
cmd.Parameters.Add("@image", ProductPicture); //
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Produit est modifié avec succée \n ", "Attention");
}
cnx.Close();
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Produit : Update\n " + ex.Message, "Attention");
return(false);
}
}
public int FindbyName(string user)
{
int userId = 0;
//Employer m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tcompte where nomUtilisateur=@user", cnx);
cmd.Parameters.Add("@user", user);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// m = new Employer(reader.GetInt32("id"), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetDateTime(10), reader.GetDateTime(11), (byte[])reader[12]);
userId = reader.GetInt32(0);
}
//reader.Close();
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche employerparnom : Find \n " + ex.Message, "Attention");
}
return(userId);
}
public void Update(Produit s)
{
try
{
cnx = ConnexionCoffe.GetInstance();//ouvrir la connexion
MySqlCommand cmd = new MySqlCommand("update tproduit set id=@id, nom=@nom, description=@description, idcateg=@idcateg,prix=@prix,image=@image where id=@id", cnx);
cmd.Parameters.Add("@id", s.Id); //
cmd.Parameters.Add("@nom", s.Nom); //
cmd.Parameters.Add("@description", s.Description); //
cmd.Parameters.Add("@idcateg", s.IdCat.IdCat); //
cmd.Parameters.Add("@prix", s.Prix); //
cmd.Parameters.Add("@image", s.Img); //
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Produit est modifié avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Produit : Update\n " + ex.Message, "Attention");
}
}
public Ticket Find(int id)
{
EmployerDAO bdCat = new EmployerDAO();
List <Employer> emps = bdCat.FindAll();
Employer c = null;
Ticket m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tticket where id=@id", cnx);
cmd.Parameters.Add("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (Employer emp in emps)
{
if (emp.Id.Equals(reader.GetInt32(3)))
{
c = emp; break;
}
}
m = new Ticket(reader.GetInt32("id"), c, reader.GetDecimal(2), reader.GetDecimal(3), reader.GetDecimal(4), reader.GetDateTime(5));
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche ticket : Find \n " + ex.Message, "Attention");
}
return(m);
}
public void Add2(Ticket m)
{
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("insert into tticket values(@id,@idemp,@total,@especes, @rendue,@dateCreation)", cnx);
cmd.Parameters.Add("@id", null);
cmd.Parameters.Add("@idemp", m.IdEmploye.Id);
cmd.Parameters.Add("@total", m.Total);
cmd.Parameters.Add("@especes", m.Especes);
cmd.Parameters.Add("@rendue", m.Rendu);
cmd.Parameters.Add("@dateCreation", m.DateTicket);
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Ticket ajouter avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Pb de la requete de l'ajout\n " + ex.Message, "Attention");
}
}
public List <Categorie> FindAll()
{
List <Categorie> res = new List <Categorie>();
ArrayList CategoriesList = new ArrayList();
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tcategorie", cnx);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Categorie m = new Categorie(reader.GetInt32("id"), reader.GetString(1), reader.GetString(2), (byte[])reader[3]);
res.Add(m);
}
// reader.Close();
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Categorie : Pb de FindAll \n " + ex.Message, "Attention");
}
return(res);
}
public void Add(LigneTicket m)
{
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("insert into tligneticket values(@id, @idprod,@idticket,@nomprod, @qteprod,@prixprod,@totalprod)", cnx);
cmd.Parameters.Add("@id", m.Id);//
cmd.Parameters.Add("@idprod", m.Idprod.Id);
cmd.Parameters.Add("@idticket", m.Idticket.Id);
cmd.Parameters.Add("@nomprod", m.NomProd);
cmd.Parameters.Add("@qteprod", m.QteProd);
cmd.Parameters.Add("@prixprod", m.PrixProd);
cmd.Parameters.Add("@totalprod", m.TotalProd);
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("LigneTicket ajouter avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Pb de la requete de l'ajout\n " + ex.Message, "Attention");
}
}
public Produit Find(int id)
{
CategorieDAO bdCat = new CategorieDAO();
List <Categorie> categories = bdCat.FindAll();
Categorie c = null;
Produit m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tproduit where id=@id", cnx);
cmd.Parameters.Add("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (Categorie med in categories)
{
if (med.IdCat.Equals(reader.GetInt32(3)))
{
c = med; break;
}
}
m = new Produit(reader.GetInt32("id"), reader.GetString(1), reader.GetString(2), c, reader.GetDecimal(4), (byte[])reader[5]);
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche Produit : Find \n " + ex.Message, "Attention");
}
return(m);
}
public List <Employer> FindAll()
{
List <Employer> res = new List <Employer>();
ArrayList EmployerList = new ArrayList();
Employer m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tcompte", cnx);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
m = new Employer(reader.GetInt32("id"), reader.GetString("nom"), reader.GetString("prenom"), reader.GetString("sexe"), reader.GetString("telephone"), reader.GetString("email"), reader.GetString("adresse"), reader.GetString("type"), reader.GetString("nomUtilisateur"), reader.GetString("motPasse"), reader.GetDateTime("dateNaissance"), reader.GetDateTime("dateInscription"), (byte[])reader["image"]);
res.Add(m);
}
// reader.Close();
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Employe : Pb de FindAll \n " + ex.Message, "Attention");
}
return(res);
}
public bool RemoveEmpbyId(int id)
{
try
{
cnx = ConnexionCoffe.GetInstance(); //ouvrir la connexion
MySqlCommand cmd = new MySqlCommand("delete from tcompte where id=@id", cnx);
cmd.Parameters.Add("@id", id); //
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Employer supprimer avec succée \n ", "Attention");
cnx.Close();
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Employer : RemoveByid \n " + ex.Message, "Attension");
return(false);
}
}
public List <LigneTicket> FindAllbyTicket(int idTick)
{
ProduitDAO bdProd = new ProduitDAO();
List <Produit> produits = bdProd.FindAll();
Produit p = null;
TicketDAO bdtick = new TicketDAO();
List <Ticket> tickets = bdtick.FindAll();
Ticket t = null;
List <LigneTicket> res = new List <LigneTicket>();
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tligneticket where idticket=@idtick ", cnx);
cmd.Parameters.Add("@idtick", idTick);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (Produit prod in produits)
{
if (prod.Id.Equals(reader.GetInt32(1)))
{
p = prod; break;
}
}
foreach (Ticket tick in tickets)
{
if (tick.Id.Equals(reader.GetInt32(2)))
{
t = tick; break;
}
}
LigneTicket m = new LigneTicket(reader.GetInt32("id"), p, t, reader.GetString(3), reader.GetInt32(4), reader.GetDecimal(5), reader.GetDecimal(6));
res.Add(m);
}
reader.Close();
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Ligne ticket : Pb de FindAll \n " + ex.Message, "Attention");
}
return(res);
}
public int ReturnCategorieID(string CategorieName)
{
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("SELECT id FROM tcategorie where nom= '" + CategorieName + "';", cnx);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
m = reader.GetInt32(0);
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche idCat: Find \n " + ex.Message, "Attention");
}
return(m);
}
public void Remove(Produit s)
{
try
{
cnx = ConnexionCoffe.GetInstance(); //ouvrir la connexion
MySqlCommand cmd = new MySqlCommand("delete from tproduit where id=@id", cnx);
cmd.Parameters.Add("@id", s.Id); //
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
MessageBox.Show("Produit supprimer avec succée \n ", "Attention");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Produit : Remove \n " + ex.Message, "Attension");
}
}
public int lastVal()
{
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("SELECT MAX(id) FROM tticket", cnx);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
m = reader.GetInt32(0);
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche lastVal: Find \n " + ex.Message, "Attention");
}
return(m);
}
public string FindNameByid(int id)
{
string m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tcompte where id=@id", cnx);
cmd.Parameters.Add("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
m = reader.GetString("nomUtilisateur");
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche employeparid : Find \n " + ex.Message, "Attention");
}
return(m);
}
public Employer Find(int id)
{
Employer m = null;
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tcompte where id=@id", cnx);
cmd.Parameters.Add("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
m = new Employer(reader.GetInt32("id"), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10), reader.GetDateTime(4), reader.GetDateTime(12), (byte[])reader[11]);
}
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Requete recherche employeparid : Find \n " + ex.Message, "Attention");
}
return(m);
}
public List <Ticket> FindAll()
{
EmployerDAO bdCat = new EmployerDAO();
List <Employer> categories = bdCat.FindAll();
Employer c = null;
List <Ticket> res = new List <Ticket>();
// ArrayList ProduitList = new ArrayList();
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tticket", cnx);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (Employer emp in categories)
{
if (emp.Id.Equals(reader.GetInt32(1)))
{
c = emp; break;
}
}
Ticket m = new Ticket(reader.GetInt32("id"), c, reader.GetDecimal(2), reader.GetDecimal(3), reader.GetDecimal(4), reader.GetDateTime(5));
res.Add(m);
}
// reader.Close();
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Ticket : Pb de FindAll \n " + ex.Message, "Attention");
}
return(res);
}
public List <Produit> FindAll()
{
CategorieDAO bdCat = new CategorieDAO();
List <Categorie> categories = bdCat.FindAll();
Categorie c = null;
List <Produit> res = new List <Produit>();
ArrayList ProduitList = new ArrayList();
try
{
cnx = ConnexionCoffe.GetInstance();
MySqlCommand cmd = new MySqlCommand("select * from tproduit", cnx);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (Categorie med in categories)
{
if (med.IdCat.Equals(reader.GetInt32(3)))
{
c = med; break;
}
}
Produit m = new Produit(reader.GetInt32("id"), reader.GetString(1), reader.GetString(2), c, reader.GetDecimal(4), (byte[])reader[5]);
res.Add(m);
}
// reader.Close();
cnx.Close();
}
catch (Exception ex)
{
MessageBox.Show("Produit : Pb de FindAll \n " + ex.Message, "Attention");
}
return(res);
}
