public static Clientel getClientById(String id)
{
String sql = "SELECT * FROM clientel WHERE NO_CLIENT='" + id + "'";
DataTable dt;
Serreurs er = new Serreurs("Erreur sur recherche d'une commande.", "Commandes.RechercheUneCommande()");
try
{
dt = DBInterface.Lecture(sql, er);
System.Console.WriteLine(sql);
if (dt.IsInitialized && dt.Rows.Count > 0)
{
Clientel leClient = new Clientel();
DataRow dataRow = dt.Rows[0];
leClient.NoClient = id;
leClient.Societe = dataRow[1].ToString();
leClient.NomCl = dataRow[2].ToString();
leClient.PrenomCl = dataRow[3].ToString();
leClient.AdresseCl = dataRow[4].ToString();
leClient.VilleCl = dataRow[5].ToString();
leClient.CodePostCl = dataRow[6].ToString();
return(leClient);
}
else
{
return(null);
}
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
public static void addClient(Clientel unCli)
{
Serreurs er = new Serreurs("Erreur sur l'écriture d'un client.", "Client.update()");
String requete = "INSERT INTO Clientel (SOCIETE, NOM_CL, PRENOM_CL, ADRESSE_CL, VILLE_CL, CODE_POST_CL, NO_CLIENT) " +
"VALUES (" +
"'" + unCli.Societe + "' , '"
+ unCli.NomCl + "' , '"
+ unCli.PrenomCl + "' , '"
+ unCli.AdresseCl + "' , '"
+ unCli.VilleCl + "' , '"
+ unCli.CodePostCl + "' , '"
+ unCli.NoClient + "') ";
try
{
DBInterface.Insertion_Donnees(requete);
}
catch (MonException erreur)
{
throw erreur;
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
/// <summary>
/// mise à jour d'un client sur son ID
/// </summary>
/// <param name="numCli">N° de l'utilisateur à lire</param>
public static void updateClient(Clientel unCli)
{
Serreurs er = new Serreurs("Erreur sur l'écriture d'un client.", "Client.update()");
String requete = "UPDATE Clientel SET " +
"SOCIETE = '" + unCli.Societe + "'" +
", NOM_CL = '" + unCli.NomCl + "'" +
", PRENOM_CL = '" + unCli.PrenomCl + "'" +
", ADRESSE_CL = '" + unCli.AdresseCl + "'" +
", VILLE_CL = '" + unCli.VilleCl + "'" +
", CODE_POST_CL = '" + unCli.CodePostCl + "'" +
" WHERE NO_CLIENT LIKE '" + unCli.NoClient + "'";
try
{
DBInterface.Insertion_Donnees(requete);
}
catch (MonException erreur)
{
throw erreur;
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
public static void insertClient(Clientel unCli)
{
Serreurs er = new Serreurs("Erreur sur la création d'un client.", "Client.insert()");
String requete = "INSERT INTO clientel (no_client, societe, nom_cl, prenom_cl, adresse_cl, ville_cl, code_post_cl) VALUES " +
"('" + unCli.NoClient + "'" +
",'" + unCli.Societe + "'" +
",'" + unCli.NomCl + "'" +
",'" + unCli.PrenomCl + "'" +
",'" + unCli.AdresseCl + "'" +
",'" + unCli.VilleCl + "'" +
",'" + unCli.CodePostCl + "')";
try
{
DBInterface.Insertion_Donnees(requete);
}
catch (MonException erreur)
{
throw erreur;
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
/// <summary>
/// Initialisation avec les paramètres
/// </summary>
public Commandes(string noCo, Vendeur noV, Clientel noCl, string dC, string f, string nbA)
{
noCommande = noCo;
noVendeur = noV;
noClient = noCl;
dateCde = dC;
facture = f;
nbArticle = nbA;
}
/// <summary>
/// Initialisation
/// </summary>
public Commandes()
{
noCommande = "";
noVendeur = null;
noClient = null;
dateCde = "";
facture = "";
nbArticle = "";
}
public static ComVendCli getComVendCli()
{
ComVendCli cvc = new ComVendCli();
Serreurs er = new Serreurs("Erreur sur recherche d'un vendeur.", "vendeur.RechercheDesVendeurs()");
try
{
cvc.commande = new Commandes();
cvc.listeClients = Clientel.getClients();
cvc.listeVendeurs = Vendeur.getVendeurs();
return(cvc);
}
catch (Exception e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
public static IEnumerable <Commandes> getCommandes()
{
IEnumerable <Commandes> commandes = new List <Commandes>();
DataTable dt;
Commandes commande;
Serreurs er = new Serreurs("Erreur sur lecture des commandes.", "CommandesList.getCommandes()");
try
{
string mysql = "SELECT c.NO_COMMAND, c.NO_VENDEUR, c.NO_CLIENT, c.DATE_CDE, ";
mysql += "c.FACTURE, count(d.NO_COMMAND) NB_ARTICLES ";
mysql += "FROM commandes c LEFT OUTER JOIN detail_cde d ";
mysql += "ON c.NO_COMMAND=d.NO_COMMAND GROUP BY c.NO_COMMAND ORDER BY c.NO_COMMAND";
dt = DBInterface.Lecture(mysql, er);
foreach (DataRow dataRow in dt.Rows)
{
commande = new Commandes();
commande.NoCommande = dataRow[0].ToString();
String noVendeur = dataRow[1].ToString();
commande.NoVendeur = Vendeur.getVendeurById(noVendeur);
String noClient = dataRow[2].ToString();
commande.NoClient = Clientel.getClientById(noClient);
Match match = Regex.Match(dataRow[3].ToString(), pattern);
String temp = match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value;
commande.DateCde = temp;
commande.Facture = dataRow[4].ToString();
commande.NbArticle = dataRow[5].ToString();
((List <Commandes>)commandes).Add(commande);
}
return(commandes);
}
catch (MonException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
public static void Supprimer(Clientel unCli)
{
SuppCom(unCli.NoClient);
Serreurs er = new Serreurs("Erreur sur la suppression d'un client.", "Clientel.Supprimer()");
String requete = "DELETE FROM Clientel WHERE NO_CLIENT='" + unCli.NoClient + "'";
try
{
DBInterface.Insertion_Donnees(requete);
}
catch (MonException erreur)
{
throw erreur;
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
/// <summary>
/// Lire une commande sur son ID
/// </summary>
/// <param name="numCom">N° de la commande à lire</param>
public static Commandes getCommande(String numCom)
{
String mysql;
DataTable dt;
Serreurs er = new Serreurs("Erreur sur recherche d'une commande.", "Commandes.RechercheUneCommande()");
try
{
mysql = "SELECT c.NO_VENDEUR, c.NO_CLIENT, c.DATE_CDE, ";
mysql += "c.FACTURE, count(*) NB_ARTICLES ";
mysql += "FROM commandes c LEFT OUTER JOIN detail_cde d ON c.NO_COMMAND=d.NO_COMMAND ";
mysql += "WHERE c.NO_COMMAND='" + numCom + "' GROUP BY c.NO_COMMAND";
dt = DBInterface.Lecture(mysql, er);
System.Console.WriteLine(mysql);
if (dt.IsInitialized && dt.Rows.Count > 0)
{
Commandes commande = new Commandes();
DataRow dataRow = dt.Rows[0];
commande.NoCommande = numCom;
String noVendeur = dataRow[0].ToString();
commande.NoVendeur = Vendeur.getVendeurById(noVendeur);
String noClient = dataRow[1].ToString();
commande.NoClient = Clientel.getClientById(noClient);
Match match = Regex.Match(dataRow[2].ToString(), pattern);
String temp = match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value;
commande.DateCde = temp;
commande.Facture = dataRow[3].ToString();
commande.NbArticle = dataRow[4].ToString();
return(commande);
}
else
{
return(null);
}
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
public static IEnumerable <Clientel> getClients()
{
IEnumerable <Clientel> clients = new List <Clientel>();
DataTable dt;
Clientel client;
Serreurs er = new Serreurs("Erreur sur lecture des clients.", "ClientsList.getClients()");
try
{
String mysql = "SELECT SOCIETE, NOM_CL, PRENOM_CL, ADRESSE_CL, VILLE_CL, CODE_POST_CL, " +
"NO_CLIENT FROM clientel ORDER BY NO_CLIENT";
dt = DBInterface.Lecture(mysql, er);
foreach (DataRow dataRow in dt.Rows)
{
client = new Clientel();
client.NoClient = dataRow[6].ToString();
client.NomCl = dataRow[1].ToString();
client.Societe = dataRow[0].ToString();
client.PrenomCl = dataRow[2].ToString();
client.AdresseCl = dataRow[3].ToString();
client.VilleCl = dataRow[4].ToString();
client.CodePostCl = dataRow[5].ToString();
((List <Clientel>)clients).Add(client);
}
return(clients);
}
catch (MonException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}
/// <summary>
/// Lire un utilisateur sur son ID
/// </summary>
/// <param name="numCli">N° de l'utilisateur à lire</param>
public static Clientel getClient(String numCli)
{
String mysql;
DataTable dt;
Serreurs er = new Serreurs("Erreur sur recherche d'un client.", "Client.RechercheUnClient()");
try
{
mysql = "SELECT SOCIETE, NOM_CL, PRENOM_CL,";
mysql += "ADRESSE_CL, VILLE_CL, CODE_POST_CL ";
mysql += "FROM clientel WHERE NO_CLIENT='" + numCli + "'";
dt = DBInterface.Lecture(mysql, er);
if (dt.IsInitialized && dt.Rows.Count > 0)
{
Clientel client = new Clientel();
DataRow dataRow = dt.Rows[0];
client.NoClient = numCli;
client.NomCl = dataRow[1].ToString();
client.Societe = dataRow[0].ToString();
client.PrenomCl = dataRow[2].ToString();
client.AdresseCl = dataRow[3].ToString();
client.VilleCl = dataRow[4].ToString();
client.CodePostCl = dataRow[5].ToString();
return(client);
}
else
{
return(null);
}
}
catch (MySqlException e)
{
throw new MonException(er.MessageUtilisateur(), er.MessageApplication(), e.Message);
}
}