/// <summary>
/// Retrieve an Article by its num
/// </summary>
/// <param name="numArticle"></param>
/// <returns>Article</returns>
public static Article Get(int numArticle)
{
Article article = new Article();
using (SqlConnection db = DbConnexion.connect())
{
string sql = "SELECT Description1Article, Description2Article, PrixArticle,QteStockArticle,NumSousCategorie,NomMarque FROM Article a WHERE a.NumArticle = @NumArticle";
using (SqlCommand req = new SqlCommand(sql, db))
{
req.Parameters.Add(new SqlParameter("NumArticle", numArticle));
SqlDataReader row = req.ExecuteReader();
if (row.Read())
{
article.Num = numArticle;
article.Description1 = row["Description1Article"].ToString();
article.Description2 = row["Description2Article"].ToString();
article.Prix = float.Parse(row["PrixArticle"].ToString());
article.QuantiteStock = int.Parse(row["QteStockArticle"].ToString());
article.SousCategorie = SousCategorieDAO.Get(int.Parse(row["NumSousCategorie"].ToString()));
article.Marque = row["NomMarque"].ToString();
}
}
}
return article;
}
/// <summary>
/// Delete an Article
/// </summary>
/// <param name="article"></param>
/// <returns>bool</returns>
public static bool Delete(Article article)
{
int res = 0;
using (SqlConnection db = DbConnexion.connect())
{
string sql = "DELETE FROM Article WHERE NumArticle = @NumArticle"; ;
using (SqlCommand req = new SqlCommand(sql, db))
{
req.Parameters.Add(new SqlParameter("NumArticle", article.Num));
res = req.ExecuteNonQuery();
}
}
return res != 0 ? true : false;
}
/// <summary>
/// Retrieve all Article
/// </summary>
/// <returns></returns>
public static List<Article> GetAll()
{
List<Article> listArticles = new List<Article>();
using (SqlConnection db = DbConnexion.connect())
{
string sql = "SELECT Description1Article, Description2Article, PrixArticle,QteStockArticle,NumSousCategorie,NomMarque, NumArticle FROM Article";
using (SqlCommand req = new SqlCommand(sql, db))
{
SqlDataReader row = req.ExecuteReader();
while (row.Read())
{
Article article = new Article();
article.Num = int.Parse(row["NumArticle"].ToString());
article.Description1 = row["Description1Article"].ToString();
article.Description2 = row["Description2Article"].ToString();
article.Prix = float.Parse(row["PrixArticle"].ToString());
article.QuantiteStock = int.Parse(row["QteStockArticle"].ToString());
article.SousCategorie = SousCategorieDAO.Get(int.Parse(row["NumSousCategorie"].ToString()));
article.Marque = row["NomMarque"].ToString();
listArticles.Add(article);
}
}
}
return listArticles;
}
/// <summary>
/// Update an Article
/// </summary>
/// <param name="article"></param>
/// <returns>bool</returns>
public static bool Update(Article article)
{
int res = 0;
using (SqlConnection db = DbConnexion.connect())
{
string sql = "UPDATE article SET Description1Article = @Description1Article, Description2Article = @Description2Article, PrixArticle = @PrixArticle, QteStockArticle = @QteStockArticle, NumSousCategorie = @NumSousCategorie, NomMarque = @NomMarque WHERE NumArticle = @NumArticle"; ;
using (SqlCommand req = new SqlCommand(sql, db))
{
req.Parameters.Add(new SqlParameter("NumArticle", article.Num));
req.Parameters.Add(new SqlParameter("Description1Article", article.Description1));
req.Parameters.Add(new SqlParameter("Description2Article", article.Description2));
req.Parameters.Add(new SqlParameter("PrixArticle", article.Prix));
req.Parameters.Add(new SqlParameter("QteStockArticle", article.QuantiteStock));
req.Parameters.Add(new SqlParameter("NumSousCategorie", article.SousCategorie.Num));
req.Parameters.Add(new SqlParameter("NomMarque", article.Marque));
res = req.ExecuteNonQuery();
}
}
return res != 0 ? true : false;
}
/// <summary>
/// Insert a new Article
/// </summary>
/// <param name="article"></param>
/// <returns>bool</returns>
public static bool Insert(Article article)
{
int res = 0;
int lastId;
using (SqlConnection db = DbConnexion.connect())
{
string sqlLastId = "SELECT MAX(NumArticle) FROM Article"; ;
using (SqlCommand req = new SqlCommand(sqlLastId, db))
{
lastId = (req.ExecuteScalar() != null) ? int.Parse(req.ExecuteScalar().ToString()) + 1 : 1;
}
string sql = "INSERT INTO article (NumArticle, Description1Article, Description2Article, PrixArticle, QteStockArticle, NumSousCategorie, NomMarque, ImageArticle) VALUES(@NumArticle, @Description1Article, @Description2Article, @PrixArticle, @QteStockArticle, @NumSousCategorie, @NomMarque, @ImageArticle)"; ;
using (SqlCommand req = new SqlCommand(sql, db))
{
req.Parameters.Add(new SqlParameter("NumArticle", lastId));
req.Parameters.Add(new SqlParameter("Description1Article", article.Description1));
req.Parameters.Add(new SqlParameter("Description2Article", article.Description2));
req.Parameters.Add(new SqlParameter("PrixArticle", article.Prix));
req.Parameters.Add(new SqlParameter("QteStockArticle", article.QuantiteStock));
req.Parameters.Add(new SqlParameter("NumSousCategorie", article.SousCategorie.Num));
req.Parameters.Add(new SqlParameter("NomMarque", article.Marque));
req.Parameters.Add(new SqlParameter("ImageArticle", "image"));
res = req.ExecuteNonQuery();
}
}
return res != 0 ? true : false;
}
/// <summary>
/// Count how many Tickets the Article has been used
/// </summary>
/// <param name="article"></param>
/// <returns></returns>
public static int GetNbTickets(Article article)
{
int nb = 0;
using (SqlConnection db = DbConnexion.connect())
{
string sql = "SELECT COUNT(*) FROM ticket_contenu tc WHERE tc.NumArticle = @NumArticle GROUP BY tc.NumArticle";
using (SqlCommand req = new SqlCommand(sql, db))
{
req.Parameters.Add(new SqlParameter("NumArticle", article.Num));
nb = (req.ExecuteScalar() != null) ? int.Parse(req.ExecuteScalar().ToString()) : 0;
}
}
return nb;
}
/// <summary>
/// Retrieve an Article by its SousCategorie
/// </summary>
/// <param name="sousCategorie"></param>
/// <returns></returns>
public static List<Article> GetBySousCategorie(SousCategorie sousCategorie)
{
List<Article> listArticles = new List<Article>();
using (SqlConnection db = DbConnexion.connect())
{
string sql = "SELECT Description1Article, Description2Article, PrixArticle,QteStockArticle,NumSousCategorie,NomMarque FROM Article a WHERE a.NumSousCategorie = @NumSousCategorie";
using (SqlCommand req = new SqlCommand(sql, db))
{
req.Parameters.Add(new SqlParameter("NumSousCategorie", sousCategorie.Num));
SqlDataReader row = req.ExecuteReader();
while (row.Read())
{
Article article = new Article();
article.Description1 = row["Description1Article"].ToString();
article.Description2 = row["Description2Article"].ToString();
article.Prix = row.GetFloat(row.GetOrdinal("PrixArticle"));
article.QuantiteStock = int.Parse(row["QteStockArticle"].ToString());
article.SousCategorie = sousCategorie;
article.Marque = row.GetValue(5).ToString();
listArticles.Add(article);
}
}
}
return listArticles;
}
