private void InsertProduit_Load(object sender, EventArgs e)
{
lescategories = Categorie.getAllCategories();
cbCategorie.DataSource = lescategories;
cbCategorie.DisplayMember = "Nom";
cbCategorie.ValueMember = "Id";
string sql = "SELECT * from image";
try
{
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
lesimages = new List <Image>();
while (rdr.Read())
{
int id = int.Parse(rdr[0].ToString());
string nom = rdr[1].ToString();
Image img = new Image(id, nom);
lesimages.Add(img);
}
rdr.Close();
cbImage.DataSource = lesimages;
cbImage.DisplayMember = "Nom";
cbImage.ValueMember = "Id";
}
catch
{
MessageBox.Show("Erreur de chargement de la liste des Images");
}
}
public static List <Categorie> getAllCategories()
{
List <Categorie> lescategories = new List <Categorie>();
string sql = "SELECT * from categorie";
try
{
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
lescategories = new List <Categorie>();
while (rdr.Read())
{
int id = int.Parse(rdr[0].ToString());
string nom = rdr[1].ToString();
Categorie cat = new Categorie(id, nom);
lescategories.Add(cat);
}
rdr.Close();
return(lescategories);
}
catch
{
return(null);
}
}
private void btnsubmit_Click(object sender, EventArgs e)
{
string username = txtusername.Text;
string email = txtemail.Text;
string password = SHA.petitsha(txtpass.Text);
User.RoleType role = User.RoleType.EMPLOYE;
if (cbrole.Text == "Employe")
{
role = User.RoleType.EMPLOYE;
}
else if (cbrole.Text == "Admin")
{
role = User.RoleType.ADMIN;
}
string sql = $"insert into admin (username,email ,pass, Role) Values ('{username}', '{email}','{password}', {User.roleTypeToInt(role)})";
if (DataBaseUtil.executeNonQuery(sql) > 0)
{
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("User " + username + " exists already");
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
labelVerif.Visible = false;
string Libelle = txtLibelle.Text;
string MotsCles = txtMots.Text;
string Description = txtdesc.Text;
decimal Quantite = nudQuantite.Value;
float Prix = float.Parse(txtPrix.Text);
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
string strprix = Prix.ToString(nfi);
int idcategorie = (cbCategorie.SelectedItem as Categorie).Id;
int idimage = (cbImage.SelectedItem as Image).Id;
string sql = $"insert into produit (nom_produit, p_motscles, description, qteProduit, prix, id_categorie, id_image) Values ('{Libelle}', '{MotsCles}', '{Description}', {Quantite}, {strprix}, {idcategorie}, {idimage}) ";
if (DataBaseUtil.executeNonQuery(sql) > 0)
{
labelVerif.ForeColor = Color.Green;
labelVerif.Visible = true;
labelVerif.Text = " Les informations ont bien été enregistrées ";
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("Echec d'ajout du produit");
}
}
private void AjouterJournalConnexion(int id_admin, DateTime t, bool etat)
{
string dt = t.ToString("yyyy-MM-dd HH:mm:ss");
string sql = $"INSERT INTO journal (dateconnect, etat, PersonID) VALUES ('{dt}', {etat}, {id_admin})";
if (DataBaseUtil.executeNonQuery(sql) == -1)
{
MessageBox.Show("Erreur lors de l'insertion dans le journal");
}
}
private void load_produit()
{
lesproduits.Clear();
string sql = "SELECT produit.*, categorie.nom_categorie from produit, categorie WHERE produit.id_categorie = categorie.id_categorie";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
Produit ProduitView = new Produit(int.Parse(rdr[0].ToString()), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString(), int.Parse(rdr[4].ToString()), float.Parse(rdr[5].ToString()), rdr[8].ToString());
lesproduits.Add(ProduitView);
}
rdr.Close();
DGVProduit.DataSource = null;
DGVProduit.DataSource = lesproduits;
}
private void load_client()
{
lesclients.Clear();
string sql = "Select * from users ";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
Client ClientView = new Client(int.Parse(rdr[0].ToString()), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString(), rdr[4].ToString());
lesclients.Add(ClientView);
}
rdr.Close();
DGVClient.DataSource = null;
DGVClient.DataSource = lesclients;
}
private void btnDeleteParticipant_Click(object sender, EventArgs e)
{
List <Client> selected = new List <Client>();
foreach (DataGridViewRow row in DGVClient.SelectedRows)
{
selected.Add(lesclients[row.Index]);
//DGVParticipant.Rows.RemoveAt(row.Index);
}
foreach (Client p in selected)
{
string sql = "DELETE FROM users WHERE id = '" + p.Id + "'";
DataBaseUtil.executeNonQuery(sql);
}
load_client();
}
private void btnDeleteSalon_Click(object sender, EventArgs e)
{
List <Produit> selected = new List <Produit>();
foreach (DataGridViewRow row in DGVProduit.SelectedRows)
{
selected.Add(lesproduits[row.Index]);
//DGVParticipant.Rows.RemoveAt(row.Index);
}
foreach (Produit s in selected)
{
string sql = "DELETE FROM produit WHERE id_produit = " + s.Id;
DataBaseUtil.executeNonQuery(sql);
}
load_produit();
}
private void btndeleteAdmin_Click(object sender, EventArgs e)
{
List <User> selected = new List <User>();
foreach (DataGridViewRow row in DGVAdmin.SelectedRows)
{
selected.Add(lesadmins[row.Index]);
//DGVParticipant.Rows.RemoveAt(row.Index);
}
foreach (User s in selected)
{
string sql = "DELETE FROM admin WHERE id = " + s.Id;
DataBaseUtil.executeNonQuery(sql);
}
load_admin();
}
private void load_admin()
{
lesadmins.Clear();
string sql = "Select * from admin";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
int roleid = int.Parse(rdr[4].ToString());
User.RoleType role = User.intToRoleType(roleid);
User AdminViews = new User(int.Parse(rdr[0].ToString()), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString(), role, bool.Parse(rdr[5].ToString()));
lesadmins.Add(AdminViews);
}
rdr.Close();
//DGVAdmin.DataSource = null;
DGVAdmin.DataSource = lesadmins;
}
private void load_commande()
{
lescommandes.Clear();
string sql = "SELECT * from commande ";
MySqlCommand cmd = new MySqlCommand(sql);
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
Commande CommandeView = new Commande(int.Parse(rdr[0].ToString()), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString());
int ref_com = int.Parse(rdr[0].ToString());
string date_commande = (rdr[1].ToString());
string id_u = (rdr[2].ToString());
string total = (rdr[3].ToString());
lescommandes.Add(CommandeView);
}
rdr.Close();
DGVCommande.DataSource = null;
DGVCommande.DataSource = lescommandes;
}
private void load_journal()
{
lesjournaux.Clear();
DateTime dt = DateTime.Now.Subtract(new TimeSpan(3, 0, 0, 0, 0));
string strdate = Journal.dateTimeToSQLString(dt);
string sql = $"SELECT username, dateconnect, role, etat from journal, admin WHERE journal.PersonID = admin.id AND dateconnect > '{strdate}' ORDER BY dateconnect DESC";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
dt = DateTime.Parse(rdr[1].ToString());
int r = int.Parse(rdr[2].ToString());
bool etat = Boolean.Parse(rdr[3].ToString());
Journal JournalView = new Journal(dt, rdr[0].ToString(), User.intToRoleType(r), etat);
lesjournaux.Add(JournalView);
}
rdr.Close();
DGVJournal.DataSource = null;
DGVJournal.DataSource = lesjournaux;
}
private void btnSubmit_Click(object sender, EventArgs e)
{
labelVerif.Visible = false;
string nom = txtusername.Text;
string tel = txttel.Text;
string departement = txtadresse.Text;
string email = txtemail.Text;
string sql = $"insert into users (username, tel, adresse, email) Values ('{nom}', '{tel}', '{departement}', '{email}') ";
if (DataBaseUtil.executeNonQuery(sql) > 0)
{
labelVerif.ForeColor = Color.Green;
labelVerif.Visible = true;
labelVerif.Text = " Les informations ont bien été enregistrées ";
}
else
{
MessageBox.Show("Echec de l'ajout d'un client");
}
}
private void DGVParticipant_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (0 <= e.RowIndex && e.RowIndex < lesclients.Count)
{
Client client = lesclients[e.RowIndex];
string modifiedColumn = null;
if (e.ColumnIndex == 1)
{
modifiedColumn = "username";
client.Pseudo = DGVClient.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else if (e.ColumnIndex == 2)
{
modifiedColumn = "tel";
client.Tel = DGVClient.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else if (e.ColumnIndex == 3)
{
modifiedColumn = "adresse";
client.Adresse = DGVClient.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else if (e.ColumnIndex == 4)
{
modifiedColumn = "email";
client.Email = DGVClient.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
if (modifiedColumn != null)
{
string sql = $"UPDATE users SET {modifiedColumn} = '{DGVClient.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString()}' WHERE id = {client.Id} ";
if (DataBaseUtil.executeNonQuery(sql) == -1)
{
MessageBox.Show("Failed to Update User");
}
}
}
}
public static bool checkUserLocked(string username)
{
string sql = $"Select locked FROM admin WHERE admin.username = '******' ";
try
{
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
bool locked = bool.Parse(rdr[0].ToString());
if (locked)
{
rdr.Close();
return(true);
}
}
rdr.Close();
return(false);
}
catch
{
}
return(false);
}
private void load_Produits()
{
lesproduits.Clear();
try
{
string sql = "SELECT * FROM produit";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
Produit ProduitView = new Produit(int.Parse(rdr[0].ToString()), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString(), int.Parse(rdr[4].ToString()), float.Parse(rdr[5].ToString()), null);
lesproduits.Add(ProduitView);
}
rdr.Close();
cbProduit.DataSource = null;
cbProduit.DataSource = lesproduits;
cbProduit.DisplayMember = "Nom";
cbProduit.ValueMember = "Id";
}
catch
{
MessageBox.Show("Impossible de charger les produits");
}
}
private void cbProduit_SelectedIndexChanged(object sender, EventArgs e)
{
Produit produit = cbProduit.SelectedItem as Produit;
try
{
byuser.Clear();
string sql = $"SELECT users.username, panier.qte, commande.date_commande FROM panier, commande, users WHERE panier.ref_com = commande.ref_com AND panier.id_produit = {produit.Id} AND commande.id_u = users.id";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
while (rdr.Read())
{
Buy b = new Buy(rdr[0].ToString(), int.Parse(rdr[1].ToString()), DateTime.Parse(rdr[2].ToString()));
byuser.Add(b);
}
rdr.Close();
DGVBuy.DataSource = null;
DGVBuy.DataSource = byuser;
}
catch
{
MessageBox.Show("La Liste n'a pas pu etre récuperer");
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
if (txtusername.Text == "" || txtpassword.Text == "")
{
MessageBox.Show("Remplissez tous les champs");
return;
}
try
{
string username = txtusername.Text;
string pass = txtpassword.Text;
pass = SHA.petitsha(pass);
if (User.checkUserLocked(username))
{
MessageBox.Show("Votre compte a été verouillé suite à 5 tentatives échouées");
this.DialogResult = DialogResult.Cancel;
return;
}
string sql = $"Select id, username, pass, Role from admin where username='******'";
MySqlDataReader rdr = DataBaseUtil.executeSelect(sql);
if (rdr.Read())
{
labelError.Visible = false;
int id = int.Parse(rdr[0].ToString());
string password = rdr[2].ToString();
bool etat = password.Equals(pass.ToLower());
role = int.Parse(rdr[3].ToString());
nomoperateur = rdr[1].ToString();
if (etat)
{
this.DialogResult = DialogResult.OK;
}
else
{
int nbr = -1;
if (!dictUserConnexion.TryGetValue(username, out nbr))
{
dictUserConnexion.Add(username, 0);
}
dictUserConnexion[username]++;
nbr = dictUserConnexion[username];
if (dictUserConnexion[username] == 5)
{
User.lockUnlockUser(username, true);
}
labelError.Visible = true;
}
rdr.Close();
AjouterJournalConnexion(id, DateTime.Now, etat);
}
else
{
labelError.Visible = true;
}
}
catch
{
}
///this.StrLevel = "Administrateur";
}
private void DGVSalon_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (0 <= e.RowIndex && e.RowIndex < lesproduits.Count)
{
float prix = -1;
Produit produit = lesproduits[e.RowIndex];
string strvalue = null;
int intvalue = 0;
string modifiedColumn = null;
if (e.ColumnIndex == 1)
{
modifiedColumn = "titre_produit";
strvalue = DGVProduit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else if (e.ColumnIndex == 2)
{
modifiedColumn = "description_courte";
strvalue = DGVProduit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else if (e.ColumnIndex == 3)
{
modifiedColumn = "description_longue";
strvalue = DGVProduit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else if (e.ColumnIndex == 4)
{
modifiedColumn = "qteProduit";
intvalue = int.Parse(DGVProduit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());
}
else if (e.ColumnIndex == 5)
{
modifiedColumn = "prix";
prix = float.Parse(DGVProduit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());
}
else if (e.ColumnIndex == 6)
{
modifiedColumn = "id_categorie";
string tmp = DGVProduit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
intvalue = Categorie.stringToId(tmp);
if (intvalue == 0)
{
MessageBox.Show("Catégorie introuvable");
return;
}
}
if (modifiedColumn != null)
{
string sql = null;
if (strvalue != null)
{
sql = $"UPDATE produit SET {modifiedColumn} = '{strvalue}' WHERE id_produit = {produit.Id} ";
}
else if (prix >= 0)
{
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
string strprix = prix.ToString(nfi);
sql = $"UPDATE produit SET {modifiedColumn} = {strprix} WHERE id_produit = {produit.Id} ";
}
else
{
sql = $"UPDATE produit SET {modifiedColumn} = {intvalue} WHERE id_produit = {produit.Id} ";
}
if (DataBaseUtil.executeNonQuery(sql) == -1)
{
MessageBox.Show("Failed to Update User");
}
}
}
}
public static bool lockUnlockUser(string username, bool locked)
{
string sql = $"UPDATE admin SET locked = {locked} WHERE admin.username = '******' AND admin.Role <> 1 ";
return(DataBaseUtil.executeNonQuery(sql) > 0);
}
