//méthode qui permet d'obtenir les informations sur l'état des stocks sur des types d'article particulier // retourner la liste des objets qui correspondent à un certain critère public List <EtatStockBE> etatStockSuivantDateOperation(DateTime dateOp) { CultureInfo ci = CultureInfo.CreateSpecificCulture(CultureInfo.CurrentCulture.Name); ci.DateTimeFormat.ShortDatePattern = "yyyy-MM-dd"; Thread.CurrentThread.CurrentCulture = ci; List <EtatStockBE> list = new List <EtatStockBE>(); int numero; string codeMagasin; string codeArticle; Int16 stockDebut; Int16 quantiteAchetee; Int16 quantiteVendue; DateTime dateOperation; int annee; Int16 puArticle; Int16 stockRestant; string designation; EtatStockBE c; try { // Création d'une commande SQL MySqlCommand cmd = con.connexion.CreateCommand(); cmd.CommandText = "SELECT NUMERO, dateoperation, annee, codemagasin, s.codearticle, a.designation as designation, stockdebut, SUM(quantiteachetee) as quantiteachetee, " + "SUM(quantitevendue) as quantitevendue, " + "(SELECT PUARTICLE " + "FROM stocker " + "WHERE NUMERO = (SELECT MAX(NUMERO) FROM stocker WHERE codearticle = s.codearticle AND codeMAgasin = s.codeMagasin)) as PUARTICLE, " + "(SELECT stockrestant " + "FROM stocker " + "WHERE NUMERO = (SELECT MAX(NUMERO) FROM stocker WHERE codearticle = s.codearticle AND codeMAgasin = s.codeMagasin AND dateOperation = s.dateOperation AND annee = s.annee GROUP BY codearticle, codemagasin, dateOperation, annee)) as stockrestant " + "FROM stocker s, article a " + "WHERE a.codearticle = s.codearticle AND dateoperation = '" + dateOp + "' " + "GROUP BY s.codearticle, codemagasin, dateOperation, annee"; using (MySqlDataReader dataReader = cmd.ExecuteReader()) { //fabriquer l'objet à retourner while (dataReader.Read()) { codeMagasin = Convert.ToString(dataReader["CODEMAGASIN"]); codeArticle = Convert.ToString(dataReader["CODEARTICLE"]); stockDebut = Convert.ToInt16(dataReader["STOCKDEBUT"]); quantiteAchetee = Convert.ToInt16(dataReader["QUANTITEACHETEE"]); quantiteVendue = Convert.ToInt16(dataReader["QUANTITEVENDUE"]); dateOperation = Convert.ToDateTime(dataReader["DATEOPERATION"]); annee = Convert.ToInt16(dataReader["ANNEE"]); puArticle = Convert.ToInt16(dataReader["PUARTICLE"]); stockRestant = Convert.ToInt16(dataReader["STOCKRESTANT"]); designation = Convert.ToString(dataReader["designation"]); c = new EtatStockBE(); c.numero = Convert.ToInt16(dataReader["NUMERO"]); c.codeMagasin = codeMagasin; c.codeArticle = codeArticle; c.stockDebut = stockDebut; c.quantiteAchetee = quantiteAchetee; c.quantiteVendue = quantiteVendue; c.dateOperation = dateOperation; c.dateOperationString = dateOperation.ToShortDateString(); c.annee = annee; c.puArticle = puArticle; c.stockRestant = stockRestant; c.designationArticle = designation; list.Add(c); } dataReader.Close(); return(list); } } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } }
// Fonction permettant de remplir le DataGrid avec les informations de la base de données // @param : - listObjet : la liste des objets à afficher dans le DataGrid public void RemplirDataGrid(List <EtatStockBE> listObjet) { // Ajout de données dans la DataTable : var table = new DataTable(); table.Columns.Add(new DataColumn("numero", typeof(string))); table.Columns.Add(new DataColumn("codeArticle", typeof(string))); table.Columns.Add(new DataColumn("codeMagasin", typeof(string))); table.Columns.Add(new DataColumn("stockDebut", typeof(string))); table.Columns.Add(new DataColumn("quantiteAchetee", typeof(string))); table.Columns.Add(new DataColumn("quantiteVendue", typeof(string))); table.Columns.Add(new DataColumn("dateOperation", typeof(DateTime))); table.Columns.Add(new DataColumn("dateOperationString", typeof(string))); table.Columns.Add(new DataColumn("annee", typeof(string))); table.Columns.Add(new DataColumn("puArticle", typeof(string))); table.Columns.Add(new DataColumn("stockRestant", typeof(string))); table.Columns.Add(new DataColumn("designationArticle", typeof(string))); if (listObjet != null) { for (int i = 0; i < listObjet.Count; i++) { DataRow dr = table.NewRow(); dr["numero"] = listObjet.ElementAt(i).numero; dr["codeArticle"] = listObjet.ElementAt(i).codeArticle; dr["codeMagasin"] = listObjet.ElementAt(i).codeMagasin; dr["stockDebut"] = listObjet.ElementAt(i).stockDebut; dr["quantiteAchetee"] = listObjet.ElementAt(i).quantiteAchetee; dr["quantiteVendue"] = listObjet.ElementAt(i).quantiteVendue; dr["dateOperation"] = listObjet.ElementAt(i).dateOperation; dr["dateOperationString"] = listObjet.ElementAt(i).dateOperation.ToShortDateString(); dr["annee"] = listObjet.ElementAt(i).annee; dr["puArticle"] = listObjet.ElementAt(i).puArticle; dr["stockRestant"] = listObjet.ElementAt(i).stockRestant; /*ArticleBE article = new ArticleBE(); * article.codeArticle = listObjet.ElementAt(i).codeArticle; * article = consulterEtatStockBL.rechercherArticle(article); * dr["designationArticle"] = article.designation;*/ dr["designationArticle"] = listObjet.ElementAt(i).designationArticle; table.Rows.Add(dr); } } string vNumero = ""; string vcodeArticle = ""; string vcodeMagasin = ""; int vStockDebut = 0; int vQuantiteAchetee = 0; int vQuantiteVendue = 0; DateTime vDateOperation = new DateTime(); string vDateOperationString = ""; int vAnnee = 0; int vPuarticle = 0; int vStockRestant = 0; string vDesignationArticle = ""; ListeEtatStocks.Clear(); //Personnes_Table = LoadDataTable(); foreach (DataRow row in table.Rows) { vNumero = Convert.ToString(row["numero"]); vcodeArticle = Convert.ToString(row["codeArticle"]); vcodeMagasin = Convert.ToString(row["codeMagasin"]); vStockDebut = Convert.ToInt16(row["stockDebut"]); vQuantiteAchetee = Convert.ToInt16(row["quantiteAchetee"]); vQuantiteVendue = Convert.ToInt16(row["quantiteVendue"]); vDateOperation = Convert.ToDateTime(row["dateOperation"]); vDateOperationString = Convert.ToString(row["dateOperationString"]); vAnnee = Convert.ToInt16(row["annee"]); vPuarticle = Convert.ToInt16(row["puArticle"]); vStockRestant = Convert.ToInt16(row["stockRestant"]); vDesignationArticle = Convert.ToString(row["designationArticle"]); EtatStockBE etatStock = new EtatStockBE(); etatStock.numero = Convert.ToInt16(vNumero); etatStock.codeMagasin = vcodeMagasin; etatStock.codeArticle = vcodeArticle; etatStock.stockDebut = Convert.ToInt16(vStockDebut); etatStock.quantiteAchetee = Convert.ToInt16(vQuantiteAchetee); etatStock.quantiteVendue = Convert.ToInt16(vQuantiteVendue); etatStock.dateOperation = vDateOperation; etatStock.dateOperationString = vDateOperationString; etatStock.annee = vAnnee; etatStock.puArticle = Convert.ToInt16(vPuarticle); etatStock.stockRestant = Convert.ToInt16(vStockRestant); etatStock.designationArticle = vDesignationArticle; ListeEtatStocks.Add(etatStock); } }