public static ObservableCollection <OrdreAchatDAO> selectOrdreAchats()
{
ObservableCollection <OrdreAchatDAO> l = new ObservableCollection <OrdreAchatDAO>();
string query = "SELECT * FROM ordreachat;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
OrdreAchatDAO p = new OrdreAchatDAO(reader.GetInt32(0), reader.GetDouble(1), reader.GetDateTime(2), reader.GetInt32(3), reader.GetInt32(4));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table OrdreAchat : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static ObservableCollection <UtilisateurDAO> selectUtilisateurs()
{
ObservableCollection <UtilisateurDAO> l = new ObservableCollection <UtilisateurDAO>();
string query =
"SELECT fk_idPersonne, nomPersonne, prenomPersonne, dateNaissance, Email, password, Telephone, verifId,modePaiement,verifSolvable,estFrancais FROM utilisateur INNER JOIN personne ON utilisateur.fk_idPersonne = personne.idPersonne;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
UtilisateurDAO p = new UtilisateurDAO(reader.GetInt32(0), reader.GetString(1),
reader.GetString(2), reader.GetDateTime(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetString(8),
reader.GetInt32(9), reader.GetInt32(10));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table Utilisateur : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static ObservableCollection <PersonneDAO> selectPersonnes()
{
ObservableCollection <PersonneDAO> l = new ObservableCollection <PersonneDAO>();
string query = "SELECT * FROM personne;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
PersonneDAO p = new PersonneDAO(reader.GetInt32(0), reader.GetString(1),
reader.GetString(2), reader.GetDateTime(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetInt32(7));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table personne : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static ObservableCollection <CommissairePriseurDAO> selectCommissairePriseurs()
{
ObservableCollection <CommissairePriseurDAO> l = new ObservableCollection <CommissairePriseurDAO>();
string query = "SELECT fk_idPersonne, nomPersonne, prenomPersonne, dateNaissance, Email, password, Telephone, verifId, EstVolontaire,Formation,verifFormation FROM commissairepriseur JOIN personne ON commissairepriseur.fk_idPersonne = personne.idPersonne;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
CommissairePriseurDAO p = new CommissairePriseurDAO(reader.GetInt32(0), reader.GetString(1),
reader.GetString(2), (DateTime)reader.GetDateTime(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8),
reader.GetString(9), reader.GetInt32(10));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table CommissairePriseur : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static int getMaxIdEstimation()
{
int maxIdEstimation = 0;
string query = "SELECT MAX(idProduitEstimation) AND MAX(idCommissairePriseurEstimation) FROM estimation;";
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))
{
maxIdEstimation = reader.GetInt32(0);
}
else
{
maxIdEstimation = 0;
}
}
reader.Close();
return(maxIdEstimation);
}
public static ObservableCollection <CategorieProduitDAO> selectCategorieProduits()
{
ObservableCollection <CategorieProduitDAO> l = new ObservableCollection <CategorieProduitDAO>();
string query = "SELECT * FROM CategorieProduit;";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataReader reader = null;
try
{
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
while (reader.Read())
{
CategorieProduitDAO p = new CategorieProduitDAO(reader.GetInt32(0), reader.GetInt32(1));
l.Add(p);
}
}
catch (Exception e)
{
MessageBox.Show("Il y a un problème dans la table CommissairePriseur : {0}", e.StackTrace);
}
reader.Close();
return(l);
}
public static int getMaxIdPersonne()
{
int maxIdPersonne = 0;
string query = "SELECT MAX(idPersonne) FROM personne;";
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))
{
maxIdPersonne = reader.GetInt32(0);
}
else
{
maxIdPersonne = 0;
}
}
reader.Close();
return(maxIdPersonne);
}
public static void supprimerProduitVente(int idProduit, int idVente)
{
string query = "DELETE FROM ProduitVente WHERE idProduitPV AND idVentePV = \"" + idProduit + "\",\"" + idVente + "\";";
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 nomLot=\"" + p.nomLotDAO + "\", descriptionLot=\"" + p.descriptionLotDAO + "\" where idLot=" + p.idLotDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateEvenementVente(EvenementVenteDAO p)
{
string query = "UPDATE EvenementVente set estVolontaireEvenementVente=\"" + p.estVolontaireEvenementVenteDAO + "\", nomEvenementVente=\"" + p.nomEvenementVenteDAO + "\", idCommissairePriseurEvenementVente=\"" + p.idCommissairePriseurEvenementVenteDAO + "\", idAdresseEvenementVenteDAO=\"" + p.idAdresseEvenementVenteDAO + "\" where idEvenementVente=" + p.idEvenementVenteDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateStockage(StockageDAO p)
{
string query = "UPDATE stockage set idAdresseStockage=\"" + p.idAdresseStockageDAO + "\" where idStockage=" + p.idStockageDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateEstimation(EstimationDAO p)
{
string query = "UPDATE estimation set dateEstimation=\"" + p.DateEstimationDAO + "\", prixEstime=\"" + p.prixEstimeDAO + "\" where idProduitEstimation=" + p.idProduitEstimationDAO + "\" and where idCommissairePriseurEstimation=" + p.idCommissairePriseurEstimationDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateAdresse(AdresseDAO p)
{
string query = "UPDATE adresse set numero=\"" + p.numeroDAO + "\", rue=\"" + p.rueDAO + "\", ville=\"" + p.villeDAO + "\", codePostal=\"" + p.codePostalDAO + "\", pays=\"" + p.paysDAO + "\" where idAdresse=" + p.idAdresseDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updatePhoto(PhotoDAO p)
{
string query = "UPDATE photo set nomPhoto=\"" + p.nomPhotoDAO + "\", lienPhoto=\"" + p.lienPhotoDAO + "\", idProduit=\"" + p.idProduitDAO + "\" where idPhoto=" + p.idPhotoDAO + ";";
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 nomCategorie=\"" + p.nomCategorieDAO + "\", descriptionCategorie=\"" + p.descriptionCategorieDAO + "\" where idCategorie=" + p.idCategorieDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerCategorieProduit(int idCategorie, int idProduit)
{
string query = "DELETE FROM CategorieProduit WHERE idCategorieCP AND idProduitCP = \"" + idCategorie + "\",\"" + idProduit + "\";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateOrdreAchat(OrdreAchatDAO p)
{
string query = "UPDATE ordreachat set prixMax=\"" + p.prixMaxDAO + "\", dateOrdre=\"" + p.DateOrdreDAO + "\", idUtilisateur=\"" + p.idUtilisateurOrdreDAO + "\", idProduit=\"" + p.idProduitOrdreDAO + "\" where idOrdreAchat=" + p.idOrdreAchatDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerEstimation(int idP, int idCP)
{
string query = "DELETE FROM Estimation WHERE idProduitEstimation AND idCommissairePriseurEstimation = \"" + idP + "\",\"" + idCP + "\";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void supprimerPersonne(int id)
{
string query = "DELETE FROM personne WHERE idPersonne = \"" + id + "\";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertAdresse(AdresseDAO p)
{
int id = getMaxIdAdresse() + 1;
string query = "INSERT INTO adresse VALUES (\"" + id + "\",\"" + p.numeroDAO + "\",\"" + p.rueDAO + "\",\"" + p.villeDAO + "\",\"" + p.codePostalDAO + "\",\"" + p.paysDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void updateUtilisateur(UtilisateurDAO p)
{
string query = "UPDATE utilisateur set modePaiement=\"" + p._ModePaiementDAO + "\", verifSolv=\"" + p.verifSolvableDAO + "\", estFrancais=\"" + p.estFrancaisDAO + "\" where idPersonne=" + p.idPersonneDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertOrdreAchat(OrdreAchatDAO p)
{
int id = getMaxIdOrdreAchat() + 1;
string query = "INSERT INTO ordreachat VALUES (\"" + id + "\",\"" + p.prixMaxDAO + "\",\"" + p.DateOrdreDAO + "\",\"" + p.idUtilisateurOrdreDAO + "\",\"" + p.idProduitOrdreDAO + "\");";
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 + "\",\"" + p.descriptionCategorieDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void updateCommissairePriseur(CommissairePriseurDAO p)
{
string query = "UPDATE commissairepriseur set EstVolontaire=\"" + p.estVolontaireDAO + "\", Formation=\"" + p.formationDAO + "\", verifFormation=\"" + p.formationDAO
+ "\" where fk_idPersonne=" + p.idPersonneCommissairePriseurDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertPhoto(PhotoDAO p)
{
int id = getMaxIdPhoto() + 1;
string query = "INSERT INTO photo VALUES (\"" + id + "\",\"" + p.nomPhotoDAO + "\",\"" + p.lienPhotoDAO + "\",\"" + p.idProduitDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void insertStockage(StockageDAO p)
{
int id = getMaxIdStockage() + 1;
string query = "INSERT INTO stockage VALUES (\"" + id + "\",\"" + p.idAdresseStockageDAO + "\");";
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.nomProduitDAO + "\",\"" + p.descriptionProduitDAO + "\", \"" + p.prixReserveDAO + "\", \"" + p.prixDepartDAO + "\",\"" + p.estVenduDAO + "\",\"" + p.enStockDAO + "\",\"" + p.prixVenteDAO + "\",\"" + p.nbInvenduDAO + "\",\"" + p.idUtilisateurProduitDAO + "\",\"" + p.idLotProduitDAO + "\",\"" + p.idStockageProduitDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
public static void updateProduitVente(ProduitVenteDAO p)
{
string query = "UPDATE ProduitVente set idVentePV=\"" + p.idVenteDAOPV +
"\" where idProduitCP=" + p.idProduitPVDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void updateCategorieProduit(CategorieProduitDAO p)
{
string query = "UPDATE CategorieProduit set idCategorieCP=\"" + p._idCategorieCPDAO +
"\" where idProduitCP=" + p._idProduitCPDAO + ";";
MySqlCommand cmd = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
}
public static void insertEvenementVente(EvenementVenteDAO p)
{
int id = getMaxIdEvenementVente() + 1;
string query = "INSERT INTO EvenementVente VALUES (\"" + id + "\",\"" + p.estVolontaireEvenementVenteDAO + "\",\"" + p.nomEvenementVenteDAO + "\",\"" + p.idCommissairePriseurEvenementVenteDAO + "\",\"" + p.idAdresseEvenementVenteDAO + "\");";
MySqlCommand cmd2 = new MySqlCommand(query, DALConnection.OpenConnection());
MySqlDataAdapter sqlDataAdap = new MySqlDataAdapter(cmd2);
cmd2.ExecuteNonQuery();
}
