//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);
}
}
