public static int getAuth(string mail, string mdp)
{
int counter = 0;
string query = "SELECT * FROM admin WHERE mail='" + mail + "' AND motDePasse='" + mdp + "';";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
//reader.Read();
//string mdp = reader.GetString(0);
if (reader.HasRows)
{
reader.Read();
if (!reader.IsDBNull(0))
{
counter = reader.GetInt32(0);
}
else
{
counter = 0;
}
}
reader.Close();
return(counter);
}
public static ProduitDAO getProduit(int id)
{
string query = "SELECT * FROM produit WHERE id=" + id + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
float prixVente;
if (Convert.IsDBNull(reader[2]))
{
prixVente = 0;
}
else
{
prixVente = reader.GetFloat(2);
}
int idLot; // = ( int ) reader["idLot"];
if (Convert.IsDBNull(reader[7]))
{
idLot = 0;
}
else
{
idLot = reader.GetInt32(7);
}
int idPhoto;
if (Convert.IsDBNull(reader[8]))
{
idPhoto = 0;
}
else
{
idPhoto = reader.GetInt32(8);
}
int idAcheteur;
if (Convert.IsDBNull(reader[9]))
{
idAcheteur = 0;
}
else
{
idAcheteur = reader.GetInt32(9);
}
ProduitDAO cat = new ProduitDAO(reader.GetInt32(0), reader.GetFloat(1), prixVente,
reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6),
idLot, idPhoto, idAcheteur, reader.GetInt32(10));
reader.Close();
return(cat);
}
public static ObservableCollection <AcheteurDAO> selectAcheteur()
{
ObservableCollection <AcheteurDAO> l = new ObservableCollection <AcheteurDAO>();
string query = "SELECT * FROM acheteur LEFT JOIN personne ON acheteur.idPersonne = personne.id;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
AcheteurDAO p = new AcheteurDAO(reader.GetInt32(0), reader.GetFloat(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetInt32(5), reader.GetInt32(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetInt32(13), reader.GetInt32(14));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table Acheteur : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static int getMaxIdAdmin()
{
int maxIdCategorie = 0;
string query = "SELECT MAX(id) FROM admin;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
int cnt = cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
DataTable schemaTable = reader.GetSchemaTable();
if (reader.HasRows)
{
reader.Read();
if (!reader.IsDBNull(0))
{
maxIdCategorie = reader.GetInt32(0);
}
else
{
maxIdCategorie = 0;
}
}
reader.Close();
return(maxIdCategorie);
}
public static ObservableCollection <AdminDAO> selectAdmin()
{
ObservableCollection <AdminDAO> l = new ObservableCollection <AdminDAO>();
string query = "SELECT * FROM admin;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
AdminDAO p = new AdminDAO(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetInt32(6), reader.GetInt32(7));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table Categorie : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static ObservableCollection <ProduitCategorieDAO> getProduitCategorie(int id)
{
ObservableCollection <ProduitCategorieDAO> liste = new ObservableCollection <ProduitCategorieDAO>();
string query = "SELECT DISTINCT categorie.nom, categorie.id FROM categorie JOIN produitcategorie ON categorie.id = produitcategorie.idCategorie JOIN produit on produitcategorie.idProduit = produit.id WHERE produit.id =" + id + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
ProduitCategorieDAO cat = new ProduitCategorieDAO(reader.GetString(0), reader.GetInt32(1));
liste.Add(cat);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table produitcategorie : {0}", e.StackTrace);
}
reader.Close();
return(liste);
}
public static ObservableCollection <OrdreachatDAO> getOrdreachat(int id)
{
ObservableCollection <OrdreachatDAO> l = new ObservableCollection <OrdreachatDAO>();
string query = "SELECT produit.id , produit.nom , enchere.id , enchere.nom, ordreachat.montantMax , ordreachat.adresseDepot FROM ordreachat JOIN produit ON produit.id = ordreachat.idProduit JOIN enchere ON enchere.id = ordreachat.idEnchere JOIN acheteur ON acheteur.id = ordreachat.idAcheteur WHERE acheteur.id =" + id + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
OrdreachatDAO cat = new OrdreachatDAO(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2),
reader.GetString(3), reader.GetInt32(4), reader.GetString(5));
l.Add(cat);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table Ordreachat : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static void updateVendeur(VendeurDAO p)
{
string query = "UPDATE vendeur LEFT JOIN personne ON vendeur.idPersonne = personne.id set idPersonne=\"" + p.idPersonneDAO + "\", nom=\"" + p.nomVendeurDAO + "\", prenom=\"" + p.prenomVendeurDAO + "\", mail=\"" + p.mailVendeurDAO + "\", numeroTel=\"" + p.numeroTelVendeurDAO + "\", motDePasse=\"" + p.motDePasseVendeurDAO + "\", adresse=\"" + p.adresseVendeurDAO + "\", codePostal=\"" + p.codePostalVendeurDAO + "\", age=\"" + p.ageVendeurDAO + "\" where vendeur.id=" + p.idVendeurDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateLieu(LieuDAO p)
{
string query = "UPDATE lieu set ville=\"" + p.villeLieuDAO + "\", adresse=\"" + p.adresseLieuDAO + "\", codePostal=\"" + p.codePostalLieuDAO + "\", departement=\"" + p.departementLieuDAO + "\" where id=" + p.idLieuDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updatePersonne(PersonneDAO p)
{
string query = "UPDATE personne set nom=\"" + p.nomPersonneDAO + "\", prenom=\"" + p.prenomPersonneDAO + "\", mail=\"" + p.mailPersonneDAO + "\", numeroTel=\"" + p.numeroTelPersonneDAO + "\" , motDePasse=\"" + p.motDePassePersonneDAO + "\" , adresse=\"" + p.adressePersonneDAO + "\" , codePostal=\"" + p.codePostalPersonneDAO + "\", age=\"" + p.agePersonneDAO + "\" where id=" + p.idPersonneDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateCategorie(CategorieDAO p)
{
string query = "UPDATE categorie set nom=\"" + p.nomCategorieDAO + "\" where id=" + p.idCategorieDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerProduitCategorie(int idProduit, int idCategorie)
{
string query = "DELETE FROM produitcategorie WHERE idProduit = \"" + idProduit + "\" and idCategorie=" + idCategorie + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerOrdreachat(int idAcheteur, int idEnchere, int idProduit)
{
string query = "DELETE from ordreachat WHERE idEnchere = \"" + idEnchere + "\" and idAcheteur=\"" + idAcheteur + "\" and idProduit=" + idProduit + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerAdmin(int id)
{
string query = "DELETE FROM admin WHERE id = \"" + id + "\";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateAdmin(AdminDAO p)
{
string query = "UPDATE admin set nom=\"" + p.nomAdminDAO + "\", prenom=\"" + p.prenomAdminDAO + "\", mail=\"" + p.mailAdminDAO + "\", numeroTel=\"" + p.numeroTelAdminDAO + "\", motDePasse=\"" + p.motDePasseAdminDAO + "\", age=\"" + p.ageAdminDAO + "\", idLieu=\"" + p.idLieuAdminDAO + "\" where id=" + p.idAdminDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateEnchere(EnchereDAO p)
{
string query = "UPDATE enchere set nom=\"" + p.nomEnchereDAO + "\", heure=\"" + p.heureEnchereDAO + "\", dateVente=\"" + p.dateVenteEnchereDAO + "\", idLieu=\"" + p.idLieuEnchereDAO + "\", idAdmin=\"" + p.idAdminEnchereDAO + "\" where id=" + p.idEnchereDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateLot(LotDAO p)
{
string query = "UPDATE lot set nom=\"" + p.nomLotDAO + "\", description=\"" + p.descriptionLotDAO + "\", idEnchere=\"" + p.idEnchereLotDAO + "\" where id=" + p.idLotDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateAcheteur(AcheteurDAO p)
{
string query = "UPDATE acheteur LEFT JOIN personne ON acheteur.idPersonne = personne.id set solde=\"" + p.soldeAcheteurDAO + "\", isSolvable=\"" + p.isSolvableAcheteurDAO + "\", identite=\"" + p.identiteAcheteurDAO + "\",moyenPaiement=\"" + p.moyenPaiementAcheteurDAO + "\", idPersonne=\"" + p.idPersonneAcheteurDAO + "\", idPersonne=\"" + p.idPersonneDAO + "\", nom=\"" + p.nomAcheteurDAO + "\", prenom=\"" + p.prenomAcheteurDAO + "\", mail=\"" + p.mailAcheteurDAO + "\", numeroTel=\"" + p.numeroTelAcheteurDAO + "\", motDePasse=\"" + p.motDePasseAcheteurDAO + "\", adresse=\"" + p.adresseAcheteurDAO + "\", codePostal=\"" + p.codePostalAcheteurDAO + "\", age=\"" + p.ageAcheteurDAO + "\" where Acheteur.id=" + p.idAcheteurDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertAdmin(AdminDAO p)
{
int id = getMaxIdAdmin() + 1;
string query = "INSERT INTO Admin VALUES (\"" + id + "\",\"" + p.nomAdminDAO + "\",\"" + p.prenomAdminDAO + "\",\"" + p.mailAdminDAO + "\",\"" + p.numeroTelAdminDAO + "\",\"" + p.motDePasseAdminDAO + "\",\"" + p.ageAdminDAO + "\",\"" + p.idLieuAdminDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertProduitCategorie(ProduitCategorieDAO p)
{
/*int id = getMaxIdproduitcategorie() + 1;*/
string query = "INSERT INTO produitcategorie VALUES (\"" + p.idProduitDAO + "\",\"" + p.idCategorieDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertCategorie(CategorieDAO p)
{
int id = getMaxIdCategorie() + 1;
string query = "INSERT INTO categorie VALUES (\"" + id + "\",\"" + p.nomCategorieDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertPersonne(PersonneDAO p)
{
int id = getMaxIdPersonne() + 1;
string query = "INSERT INTO Personne VALUES (\"" + id + "\",\"" + p.nomPersonneDAO + "\",\"" + p.prenomPersonneDAO + "\",\"" + p.mailPersonneDAO + "\",\"" + p.numeroTelPersonneDAO + "\",\"" + p.motDePassePersonneDAO + "\",\"" + p.adressePersonneDAO + "\",\"" + p.codePostalPersonneDAO + "\",\"" + p.agePersonneDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertLot(LotDAO p)
{
int id = getMaxIdLot() + 1;
string query = "INSERT INTO lot VALUES (\"" + id + "\",\"" + p.nomLotDAO + "\",\"" + p.descriptionLotDAO + "\",\"" + p.idEnchereLotDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertEstimation(EstimationDAO p)
{
/*int id = getMaxIdEstimation() + 1;*/
string query = "INSERT INTO Estimation VALUES (\"" + p.estimationDAO + "\",\"" + p.dateEstimationDAO + "\",\"" + p.idProduitDAO + "\",\"" + p.idAdminDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertEnchere(EnchereDAO p)
{
int id = getMaxIdEnchere() + 1;
string query = "INSERT INTO enchere VALUES (\"" + id + "\",\"" + p.nomEnchereDAO + "\",\"" + p.heureEnchereDAO + "\",\"" + p.dateVenteEnchereDAO + "\",\"" + p.idLieuEnchereDAO + "\",\"" + p.idAdminEnchereDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertAcheteur(AcheteurDAO p)
{
int id = getMaxIdAcheteur() + 1;
string query = "INSERT INTO acheteur VALUES (\"" + id + "\",\"" + p.soldeAcheteurDAO + "\",\"" + p.isSolvableAcheteurDAO + "\",\"" + p.identiteAcheteurDAO + "\",\"" + p.moyenPaiementAcheteurDAO + "\",\"" + p.idPersonneAcheteurDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertLieu(LieuDAO p)
{
int id = getMaxIdLieu() + 1;
string query = "INSERT INTO lieu VALUES (\"" + id + "\",\"" + p.villeLieuDAO + "\",\"" + p.adresseLieuDAO + "\",\"" + p.codePostalLieuDAO + "\",\"" + p.departementLieuDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertVendeur(VendeurDAO p)
{
int id = getMaxIdVendeur() + 1;
string query = "INSERT INTO vendeur VALUES (\"" + id + "\",\"" + p.idPersonneDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertOrdreachat(OrdreachatDAO p)
{
/*int id = getMaxIdOrdreachat() + 1;*/
string query = "INSERT INTO Ordreachat VALUES (\"" + p.idProduitDAO + "\",\"" + p.idAcheteurDAO + "\",\"" + p.idEnchereDAO + "\",\"" + p.montantMaxDAO + "\",\"" + p.adresseDepotDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertProduit(ProduitDAO p)
{
int id = getMaxIdProduit() + 1;
string query = "INSERT INTO produit VALUES (\"" + id + "\",\"" + p.estimationProduitDAO + "\",\"" +
p.prixVenteProduitDAO + "\",\"" + p.nomProduitDAO + "\"" +
",\"" + p.descriptionProduitDAO + "\",\"" + p.artisteProduitDAO + "\",\"" +
p.styleProduitDAO + "\",\"" + p.idLotProduitDAO + "\",\"" + p.idPhotoProduitDAO + "\"" +
",\"" + p.idAcheteurProduitDAO + "\",\"" + p.idVendeurProduitDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
