public Dictionary <String, decimal> historiqueArticle() { Dictionary <String, decimal> dictHist = new Dictionary <String, decimal>(); List <int> nbArticle = new List <int>(); List <String> lblArticle = new List <String>(); List <decimal> PrixArticle = new List <decimal>(); try { SqlConnection cnx = Connexion.GetConnection(); SqlCommand cmd1 = new SqlCommand("SELECT IdArticle FROM Article ", cnx); SqlDataReader reader = cmd1.ExecuteReader(); while (reader.Read()) { nbArticle.Add(reader.GetInt32(0)); } reader.Close(); } catch (SqlException) { throw; } finally { Connexion.closeConnection(); } try { SqlConnection cnx = Connexion.GetConnection(); SqlCommand cmd2 = new SqlCommand("SELECT Libelle FROM Article", cnx); SqlDataReader reader = cmd2.ExecuteReader(); while (reader.Read()) { lblArticle.Add(reader.GetString(0)); } reader.Close(); } catch (SqlException) { throw; } finally { Connexion.closeConnection(); } try { SqlConnection cnx = Connexion.GetConnection(); SqlCommand cmd3 = new SqlCommand("SELECT Prix FROM Article", cnx); SqlDataReader reader = cmd3.ExecuteReader(); while (reader.Read()) { PrixArticle.Add(reader.GetDecimal(0)); } reader.Close(); } catch (SqlException) { throw; } finally { Connexion.closeConnection(); } try { SqlConnection cnx = Connexion.GetConnection(); for (int i = 0; i < nbArticle.Count; i++) { SqlCommand cmd = new SqlCommand("SELECT COUNT(numArticle) AS nb_Articles FROM lignecmd WHERE numArticle=@art GROUP BY numArticle HAVING COUNT(numArticle)>=1", cnx); cmd.Parameters.AddWithValue("art", nbArticle[i]); SqlDataReader reader1 = cmd.ExecuteReader(); while (reader1.Read()) { decimal totPrix = reader1.GetInt32(0) * PrixArticle[i]; Properties.Settings.Default.nombreArticle += reader1.GetInt32(0); Properties.Settings.Default.totalPrix += totPrix; Properties.Settings.Default.Save(); String article = reader1.GetInt32(0) + " x " + lblArticle[i]; dictHist.Add(article, totPrix); } reader1.Close(); } } catch (SqlException) { throw; } finally { Connexion.closeConnection(); } return(dictHist); }
public int addMultipleArticlesInOneC(List <Article> lst, Commande e) { int res = 0; try { SqlConnection cnx = Connexion.GetConnection(); SqlCommand UpdateTable = null; SqlCommand sqlCmd = null; SqlCommand insertJointure = null; SqlCommand findLastInsertedID = null; int b = 0; int j = 0; int d = 0; sqlCmd = new SqlCommand("insert into Commande (NumTable,idServeur,idUser,datecommande,etatCmd) values (@numt,@idserveur,@iduser,@date,'False')", cnx); UpdateTable = new SqlCommand("UPDATE Tables SET Etat='True' where NumTable=@numt AND Etat='False'", cnx); //sqlCmd.Parameters.AddWithValue("idCom", e.IdCommande); //sqlCmd.Parameters.AddWithValue("prix",e.prixtotal); sqlCmd.Parameters.AddWithValue("numt", e.NumTable); sqlCmd.Parameters.AddWithValue("date", e.datecommande); sqlCmd.Parameters.AddWithValue("idserveur", e.idserveur); sqlCmd.Parameters.AddWithValue("iduser", e.Id); UpdateTable.Parameters.AddWithValue("numt", e.NumTable); b = (int)UpdateTable.ExecuteNonQuery(); j = (int)sqlCmd.ExecuteNonQuery(); int idCommande = 0; for (int i = 0; i < lst.Count; i++) { findLastInsertedID = new SqlCommand("SELECT IdCommande from Commande", cnx); SqlDataReader rd = findLastInsertedID.ExecuteReader(); if (rd.HasRows) { while (rd.Read()) { idCommande = rd.GetInt32(0); } } rd.Close(); insertJointure = new SqlCommand("insert into lignecmd(numcmd,numArticle) VALUES (@numcd,@numar)", cnx); insertJointure.Parameters.AddWithValue("numcd", idCommande); insertJointure.Parameters.AddWithValue("numar", lst[i].IdArticle); d = (int)insertJointure.ExecuteNonQuery(); } if (b > 0 && j > 0 && d > 0 && idCommande > 0) { res = 1; } SumCommande(idCommande); } catch (Exception ex) { throw ex; } finally { Connexion.closeConnection(); } return(res); }