/* Selectionner un article à partir de son id
* @param art_id : id de l'article à selectionner
*/
public static Article selectArticleById(int art_id)
{
try
{
Article retour = new Article();
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.selectArticleById, Bdd.connexion());
//ajout des parametres
cmd.Parameters.AddWithValue("id", art_id);
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
while (msdr.Read())
{
new PlaceConvoyeur(Int32.Parse(msdr["art_conv_id"].ToString()), 0, 0);
Int32.Parse(msdr["art_cmd_id"].ToString());
retour = new Article(
Int32.Parse(msdr["art_id"].ToString()),
msdr["art_photo"].ToString(),
msdr["art_commentaire"].ToString(),
bool.Parse(msdr["art_rendu"].ToString()),
float.Parse(msdr["art_TVA"].ToString()),
float.Parse(msdr["art_TTC"].ToString()),
new TypeArticle(Int32.Parse(msdr["art_typ_id"].ToString()), null, 0, 0, 0, null),
new PlaceConvoyeur(Int32.Parse(msdr["art_conv_id"].ToString()), 0, 0),
Int32.Parse(msdr["art_cmd_id"].ToString()));
if (!msdr["art_date_rendu"].ToString().Equals(""))
{
retour.date_rendu = DateTime.Parse(msdr["art_date_rendu"].ToString());
}
}
msdr.Dispose();
Bdd.deconnexion();
#region ajout du type et du departement
retour.type = TypeArticleDAO.selectTypesById(retour.type.id);
#endregion
#region ajout de la place convoyeur
retour.convoyeur = PlaceConvoyeurDAO.selectConvoyeurById(retour.convoyeur.id);
#endregion
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : SelectArticleById");
Bdd.deconnexion();
return(null);
}
}
/* Selectionner les articles rendu en fonction de la date
* @Param plage date :
* 1 : par jour
* 2 : par semaine
* 3 : par mois
* 4 : par année
*/
public static List <Article> selectArticleRenduByDate(int plageDate)
{
try
{
List <Article> retour = new List <Article>();
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.selectArticleRenduByDate, Bdd.connexion());
#region ajout des parametres
switch (plageDate)
{
//par jour
case 1:
cmd.Parameters.AddWithValue("startTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
//par semaine
case 2:
cmd.Parameters.AddWithValue("startTime", new DateTime(SecondaryDateTime.GetMonday(DateTime.Now).Year, SecondaryDateTime.GetMonday(DateTime.Now).Month, SecondaryDateTime.GetMonday(DateTime.Now).Day, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
//par mois
case 3:
cmd.Parameters.AddWithValue("startTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
//par année
case 4:
cmd.Parameters.AddWithValue("startTime", new DateTime(DateTime.Now.Year, 1, 1, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
}
#endregion
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
//Article article;
while (msdr.Read())
{
//SELECT art_id, art_photo, art_commentaire, art_rendu, art_TVA, art_TTC, art_conv_id, art_typ_id, art_cmd_id FROM article WHERE art_date_rendu BETWEEN ? AND ?
Article article = new Article();
article.id = Int32.Parse(msdr["art_id"].ToString());
article.photo = msdr["art_photo"].ToString();
article.commentaire = msdr["art_commentaire"].ToString();
article.ifRendu = bool.Parse(msdr["art_rendu"].ToString());
article.TVA = float.Parse(msdr["art_TVA"].ToString());
article.TTC = float.Parse(msdr["art_TTC"].ToString());
article.type = new TypeArticle(Int32.Parse(msdr["art_typ_id"].ToString()), null, 0, 0, 0, null);
if (msdr["art_conv_id"].ToString().Equals("") || msdr["art_conv_id"].ToString() == null)
{
article.convoyeur = new PlaceConvoyeur(0, 0, 0);
}
else
{
article.convoyeur = new PlaceConvoyeur(Int32.Parse(msdr["art_conv_id"].ToString()), 0, 0);
}
article.fk_commande = Int32.Parse(msdr["art_cmd_id"].ToString());
retour.Add(article);
}
msdr.Dispose();
Bdd.deconnexion();
#region ajout des types, des departements et des places convoyeurs
foreach (Article art in retour)
{
art.type = TypeArticleDAO.selectTypesById(art.type.id);
art.convoyeur = PlaceConvoyeurDAO.selectConvoyeurById(art.convoyeur.id);
}
#endregion
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : SelectArticleRenduByDate : " + Ex);
Bdd.deconnexion();
return(null);
}
}
/* Selectionner les articles rendu en fonction de la date
* @Param plage date :
* 1 : par jour
* 2 : par semaine
* 3 : par mois
* 4 : par année
*/
public static List <Article> selectArticlePayeeByDateNoCleanWay(int plageDate)
{
int test = 0;
try
{
List <Article> requestResult = new List <Article>();
List <Article> retour = new List <Article>();
List <int> ifpayee = new List <int>();
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.selectArticlePayeeByDate, Bdd.connexion());
#region ajout des parametres
switch (plageDate)
{
//par jour
case 1:
cmd.Parameters.AddWithValue("startTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
//par semaine
case 2:
cmd.Parameters.AddWithValue("startTime", new DateTime(SecondaryDateTime.GetMonday(DateTime.Now).Year, SecondaryDateTime.GetMonday(DateTime.Now).Month, SecondaryDateTime.GetMonday(DateTime.Now).Day, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
//par mois
case 3:
cmd.Parameters.AddWithValue("startTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
//par année
case 4:
cmd.Parameters.AddWithValue("startTime", new DateTime(DateTime.Now.Year, 1, 1, 0, 0, 0));
cmd.Parameters.AddWithValue("endTime", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59));
break;
}
#endregion
#region récupération depuis la BDD des articles
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
//Article article;
while (msdr.Read())
{
Article article = new Article();
article.id = Int32.Parse(msdr["art_id"].ToString());
article.photo = msdr["art_photo"].ToString();
article.commentaire = msdr["art_commentaire"].ToString();
article.ifRendu = bool.Parse(msdr["art_rendu"].ToString());
article.TVA = float.Parse(msdr["art_TVA"].ToString());
article.TTC = float.Parse(msdr["art_TTC"].ToString());
article.type = new TypeArticle(Int32.Parse(msdr["art_typ_id"].ToString()), null, 0, 0, 0, null);
if (msdr["art_conv_id"].ToString().Equals("") || msdr["art_conv_id"].ToString() == null)
{
article.convoyeur = new PlaceConvoyeur(0, 0, 0);
}
else
{
article.convoyeur = new PlaceConvoyeur(Int32.Parse(msdr["art_conv_id"].ToString()), 0, 0);
}
article.fk_commande = Int32.Parse(msdr["art_cmd_id"].ToString());
if (!msdr["art_date_payee"].ToString().Equals(""))
{
article.date_payee = DateTime.Parse(msdr["art_date_payee"].ToString());
}
requestResult.Add(article);
}
msdr.Dispose();
Bdd.deconnexion();
#endregion
Commande commande;
foreach (Article art in requestResult)
{
if (art.id == 564)
{
test = art.id;
}
commande = CommandeDAO.selectCommandeById(art.fk_commande, true, false, false);
if (commande.listPayements.Count != 0)
{
if (commande.listPayements[0] != null)
{
//si l'article n'a pas été payée par cleanWay
if (!(commande.listPayements[0].typePaiement.Equals("CleanWay")))
{
retour.Add(art);
}
}
}
}
#region ajout des types, des departements et des places convoyeurs
foreach (Article art in retour)
{
art.type = TypeArticleDAO.selectTypesById(art.type.id);
art.convoyeur = PlaceConvoyeurDAO.selectConvoyeurById(art.convoyeur.id);
}
#endregion
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : selectArticlePayeeByDateNoCleanWay : " + Ex);
Bdd.deconnexion();
return(null);
}
}
/* Selectionner l'ensemble des articles d'une commande dans la base de données
* @param cmd_id : id d'une commande
*/
public static List <Article> selectArticleByIdCmd(int cmd_id)
{
try
{
List <Article> retour = new List <Article>();
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.selectArticleByIdCmd, Bdd.connexion());
//ajout des parametres
cmd.Parameters.AddWithValue("id", cmd_id);
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
int art_id;
String art_photo;
String art_commentaire;
bool art_rendu;
float art_TVA;
float art_TTC;
TypeArticle typeArticle;
PlaceConvoyeur placeConv;
int art_cmd_id;
int conv_id;
while (msdr.Read())
{
art_id = Int32.Parse(msdr["art_id"].ToString());
art_photo = msdr["art_photo"].ToString();
art_commentaire = msdr["art_commentaire"].ToString();
art_rendu = bool.Parse(msdr["art_rendu"].ToString());
art_TVA = float.Parse(msdr["art_TVA"].ToString());
art_TTC = float.Parse(msdr["art_TTC"].ToString());
typeArticle = new TypeArticle(Int32.Parse(msdr["art_typ_id"].ToString()), null, 0, 0, 0, null);
//conv_id = Int32.Parse(msdr["art_conv_id"].ToString());
String test = msdr["art_conv_id"].ToString();
if (test == null || test == "")
{
conv_id = 0;
}
else
{
conv_id = Int32.Parse(msdr["art_conv_id"].ToString());
}
placeConv = new PlaceConvoyeur(conv_id, 0, 0);
art_cmd_id = Int32.Parse(msdr["art_cmd_id"].ToString());
Article article = new Article(
art_id,
art_photo,
art_commentaire,
art_rendu,
art_TVA,
art_TTC,
typeArticle,
placeConv,
art_cmd_id);
if (msdr["art_date_rendu"].ToString().Equals(null))
{
article.date_rendu = DateTime.Parse(msdr["art_date_rendu"].ToString());
}
retour.Add(article);
}
msdr.Dispose();
Bdd.deconnexion();
#region ajout des types, des departements et des places convoyeurs
foreach (Article art in retour)
{
art.type = TypeArticleDAO.selectTypesById(art.type.id);
art.convoyeur = PlaceConvoyeurDAO.selectConvoyeurById(art.convoyeur.id);
}
#endregion
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : SelectArticleByIdCmd");
Bdd.deconnexion();
return(null);
}
}