/* Nombre de couettes étant actuellement dans le pressing
* @Param plage date :
* 1 : par jour
* 2 : par semaine
* 3 : par mois
* 4 : par année
*/
public static int couetteByDate(int plageDate)
{
try
{
int retour = 0;
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.couetteByDate, 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();
while (msdr.Read())
{
retour = Int32.Parse(msdr["nbArticles"].ToString());
}
msdr.Dispose();
Bdd.deconnexion();
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : Impossible de récupérer le nombre de couettes");
Bdd.deconnexion();
return(0);
}
}
//Liste des paiement effectués dans la journée, rassemblé par type
public static List <Payement> listSommePaiementToday(int plageDate)
{
try
{
List <Payement> retour = new List <Payement>();
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.listSommePaiementToday, Bdd.connexion());
//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;
}
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
Payement payement;
while (msdr.Read())
{
payement = new Payement(DateTime.Now, float.Parse(msdr["pai_montant"].ToString()), msdr["pai_type"].ToString(), -1);
retour.Add(payement);
}
msdr.Dispose();
Bdd.deconnexion();
return(retour);
}
catch (Exception Ex)
{
//LogDAO.insertLog(new Log(DateTime.Now, "ERREUR BDD : Erreur dans la selection d'une liste de types dans la base de données."));
Bdd.deconnexion();
return(null);
}
}
/* Nombre de clients ayants repris un ou plusieurs articles selon la plage de dates
* @Param plageDate :
* 1 : par jour
* 2 : par semaine
* 3 : par mois
* 4 : par année
*/
public static int nbClientRecup(int plageDate)
{
try
{
Client client = null;
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.nbClientRecup, Bdd.connexion());
//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;
}
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
int totalClients = 0;
int nBCommandes;
while (msdr.Read())
{
client = new Client();
client.id = Int32.Parse(msdr["cmd_clt_id"].ToString());
nBCommandes = Int32.Parse(msdr["nbCommandes"].ToString());
if (client.id != 0 && nBCommandes != 0)
{
totalClients++;
}
}
msdr.Dispose();
Bdd.deconnexion();
return(totalClients);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : Impossible de déterminer le nombre de clients ayant récupéré des articles");
Bdd.deconnexion();
return(0);
}
}
/* Give the amount of today opened commands
* @Param plage date :
* 1 : par jour
* 2 : par semaine
* 3 : par mois
* 4 : par année
*/
public static List <Client> listClientAddToday(int plageDate)
{
try
{
//paramètres
List <Client> retour = new List <Client>();
List <int> cltList = new List <int>();
Client client;
bool contactmail = false;
bool clt_contactsms;
int clt_type;
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.listClientAddToday, Bdd.connexion());
//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;
}
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
while (msdr.Read())
{
contactmail = ((msdr["clt_contactmail"].ToString()).Equals("False")) ? false : true;
clt_contactsms = ((msdr["clt_contactsms"].ToString()).Equals("False")) ? false : true;
clt_type = ((msdr["clt_type"].ToString()).Equals("False")) ? 0 : 1;
client = new Client(
Int32.Parse(msdr["clt_id"].ToString()),
msdr["clt_nom"].ToString(),
msdr["clt_prenom"].ToString(),
msdr["clt_fix"].ToString(),
msdr["clt_mob"].ToString(),
Adresse.Parse(msdr["clt_adresse"].ToString()),
DateTime.Parse(msdr["clt_dateNaissance"].ToString()),
msdr["clt_email"].ToString(),
DateTime.Parse(msdr["clt_dateInscription"].ToString()),
Int32.Parse(msdr["clt_idCleanway"].ToString()),
contactmail,
clt_contactsms,
clt_type);
retour.Add(client);
}
msdr.Dispose();
Bdd.deconnexion();
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : listClientAddToday");
Bdd.deconnexion();
return(null);
}
}
/*Give the amount command open today
* @Param plage date :
* 1 : par jour
* 2 : par semaine
* 3 : par mois
* 4 : par année
*/
public static List <Commande> listCommandeRecuToday(int plageDate)
{
try
{
List <Commande> retour = new List <Commande>();
List <int> cltList = new List <int>();
//connection à la base de données
MySqlCommand cmd = new MySqlCommand(Bdd.listCommandeRecuToday, Bdd.connexion());
//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;
}
//Execute la commande
MySqlDataReader msdr = cmd.ExecuteReader();
Commande commande;
int id_clt;
while (msdr.Read())
{
commande = new Commande(
Int32.Parse(msdr["cmd_id"].ToString()),
DateTime.Parse(msdr["cmd_date"].ToString()),
Boolean.Parse(msdr["cmd_payee"].ToString()),
float.Parse(msdr["cmd_remise"].ToString()));
if (!msdr["cmd_date_rendu"].ToString().Equals(""))
{
commande.date_rendu = DateTime.Parse(msdr["cmd_date_rendu"].ToString());
}
id_clt = Int32.Parse(msdr["cmd_clt_id"].ToString());
retour.Add(commande);
cltList.Add(id_clt);
}
msdr.Dispose();
Bdd.deconnexion();
#region ajout article
foreach (Commande comm in retour)
{
comm.listArticles = ArticleDAO.selectArticleByIdCmd(comm.id);
}
#endregion
#region ajout client
for (int i = 0; i < retour.Count; i++)
{
//parametres en false afin de ne pas boucler
retour[i].client = ClientDAO.selectClientById(cltList[i], false, false, false);
}
#endregion
return(retour);
}
catch (Exception Ex)
{
MessageBox.Show("ERREUR BDD : listCommandeRecuToday");
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 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);
}
}