public decimal getPayementByCmdId(int id)
{
Decimal tot = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand sqlCmd = new SqlCommand("select prix from Payement where idCmd=@id", cnx);
sqlCmd.Parameters.AddWithValue("id", id);
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
tot += reader.GetDecimal(0);
}
}
else
{
tot = 0;
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(tot);
}
public int update(Facture fact)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("UPDATE Facture SET totalTTC=@ttc,totalHT=@ht,totalTVA=@tva,IdPayement=@idpay where IDFact=@id", cnx);
cmd.Parameters.AddWithValue("ttx", fact.totalTTC);
cmd.Parameters.AddWithValue("ht", fact.totalHT);
cmd.Parameters.AddWithValue("tva", fact.totalTVA);
cmd.Parameters.AddWithValue("idpay", fact.Idpayement);
int done = (int)cmd.ExecuteNonQuery();
if (done > 0)
{
res = 1;
}
}
catch (SqlException)
{
throw;
}
finally { Connexion.closeConnection(); }
return(res);
}
public int add(Facture f)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
CommandeDAO cmddao = new CommandeDAO();
SqlCommand sqlCmd = new SqlCommand("insert into Facture ( totalTTC,totalHT,totalTVA) values (@ttc,@ht,@tva)", cnx);
sqlCmd.Parameters.AddWithValue("ttc", f.totalTTC);
sqlCmd.Parameters.AddWithValue("ht", f.totalHT);
sqlCmd.Parameters.AddWithValue("tva", f.totalTVA);
res = sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public int update(Categorie obj)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("Select * from Categorie where IdCat=@id", cnx);
cmd.Parameters.AddWithValue("id", obj.idcatg);
SqlDataReader rd = cmd.ExecuteReader();
}
catch (SqlException ex) { throw ex; }
finally { Connexion.closeConnection(); }
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("UPDATE Serveur SET libelleCat=@lib", cnx);
cmd.Parameters.AddWithValue("lib", obj.libCatg);
int done = (int)cmd.ExecuteNonQuery();
if (done > 0)
{
res = 1;
}
}
catch (SqlException)
{
throw;
}
finally { Connexion.closeConnection(); }
return(res);
}
public List <Ticket> getAll()
{
List <Ticket> listeT = new List <Ticket>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Ticket", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
listeT.Add(new Ticket(reader.GetDecimal(0), reader.GetDateTime(1), reader.GetString(2)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(listeT);
}
public int add(Crediteur c)
{
int res = 0;
decimal sum;
try
{
SqlConnection cnx = Connexion.GetConnection();
CommandeDAO cmddao = new CommandeDAO();
Commande cmd = cmddao.getById(c.Idcmd);
sum = cmd.prixtotal;
SqlCommand sqlCmd = new SqlCommand("insert into Client_Crediteurs ( nomprenom, cin,totalCmdTTC , tel,idCommande) values (@nomprenom,@cin,@prix,@tel,@idcmd)", cnx);
// sqlCmd.Parameters.AddWithValue("idc", c.IdCrediteur);
sqlCmd.Parameters.AddWithValue("nomprenom", c.nomprenom);
sqlCmd.Parameters.AddWithValue("cin", c.cin);
sqlCmd.Parameters.AddWithValue("prix", c.MontantCredit);
sqlCmd.Parameters.AddWithValue("tel", c.tel);
sqlCmd.Parameters.AddWithValue("idcmd", c.Idcmd);
res = sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public List <Categorie> getAll()
{
List <Categorie> list = new List <Categorie>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Categorie", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(new Categorie(reader.GetInt32(0), reader.GetString(1)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(list);
}
public bool find(Commande e)
{
//TODO
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
bool res = false;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Commande where IdCommande=@id", cnx);
sqlCmd.Parameters.AddWithValue("id", e.IdCommande);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
res = true;
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
return(res);
}
public List <Commande> getAll()
{
//TODO
List <Commande> list = new List <Commande>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Commande where etatCmd='False'", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(new Commande(reader.GetInt32(0), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4), reader.GetDateTime(6)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(list);
}
// table occupee = true
// table vide = false
public int add(Commande e)
{
//SqlCommand insertJointure = null;
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand sqlCmd = new SqlCommand("insert into Commande (prixTotal,NumTable,IdServeur,IdUSer,idFacture,datecommande) values (0,@numt,@idserveur,@iduser,@date)", cnx);
SqlCommand UpdateTable = new SqlCommand("UPDATE Tables SET Etat='True' where NumTable=@num and Etat='False'", cnx);
sqlCmd.Parameters.AddWithValue("numt", e.NumTable);
sqlCmd.Parameters.AddWithValue("date", e.datecommande);
sqlCmd.Parameters.AddWithValue("idserveur", e.idserveur);
sqlCmd.Parameters.AddWithValue("iduser", e.Id);
UpdateTable.Parameters.AddWithValue("num", e.NumTable);
int i = (int)UpdateTable.ExecuteNonQuery();
int j = (int)sqlCmd.ExecuteNonQuery();
if (i > 0 && j > 0)
{
res = 1;
}
SumCommande(e.IdCommande);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public void SumCommande(int cmd)
{
int d = 0;
SqlCommand insertJointure = null;
SqlConnection cnx = Connexion.GetConnection();
try
{
SqlCommand sqlCmd = new SqlCommand("Select SUM(Prix) from Article,lignecmd,Commande where (Article.IdArticle=lignecmd.numArticle)and(Commande.IdCommande=lignecmd.numcmd)and(numcmd=@id)", cnx);
sqlCmd.Parameters.AddWithValue("@id", cmd);
Decimal res = (decimal)sqlCmd.ExecuteScalar();
insertJointure = new SqlCommand("update Commande set prixTotal=@prix where IdCommande=@cmd", cnx);
insertJointure.Parameters.AddWithValue("prix", res);
insertJointure.Parameters.AddWithValue("cmd", cmd);
d = (int)insertJointure.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
}
public int updateEtat(int id)
{
int res = 0;
int numtable = 0;
SqlConnection cnx = Connexion.GetConnection();
try
{
SqlCommand Updateetat = new SqlCommand("UPDATE Commande SET etatCmd=@etat where IdCommande=@id", cnx);
Updateetat.Parameters.AddWithValue("id", id);
Updateetat.Parameters.AddWithValue("NumTable", numtable);
Updateetat.Parameters.AddWithValue("etat", true);
int res1 = (int)Updateetat.ExecuteNonQuery();
if (res1 > 0)
{
res = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public static List <User> getAllUsers()
{
List <User> lstUsers = new List <User>();
User u = null;
try
{
cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[USER]", cnx);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
int id = rd.GetInt32(0);
string nom = rd.GetString(1);
string mdp = rd.GetString(2);
string qs = rd.GetString(3);
string choixqs = rd.GetString(4);
u = new User(id, nom, mdp, qs, choixqs);
lstUsers.Add(u);
}
rd.Close();
}
catch (SqlException e)
{
throw e;
}
finally { Connexion.closeConnection(); }
return(lstUsers);
}
public static User getUserById(int id)
{
User u = null;
try
{
cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("select * from [USER] where IdUser =@uname", cnx);
cmd.Parameters.AddWithValue("uname", id);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
u = new User(rd.GetInt32(0), rd.GetString(1), rd.GetString(2), rd.GetString(3), rd.GetString(4));
}
rd.Close();
} catch (SqlException e)
{
throw e;
}
finally
{
Connexion.closeConnection();
}
return(u);
}
public int add(Cheque c)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand sqlCmd = new SqlCommand("insert into Cheque (somme,dateCheque,nom_prenomCli,CINcli,numCheque) values (@som,@datec,@np,@cin,@numc)", cnx);
sqlCmd.Parameters.AddWithValue("som", c.somme);
sqlCmd.Parameters.AddWithValue("datec", c.dateCheque);
sqlCmd.Parameters.AddWithValue("np", c.nom_prenomCli);
sqlCmd.Parameters.AddWithValue("cin", c.CINcli);
sqlCmd.Parameters.AddWithValue("numc", c.numCheque);
sqlCmd.ExecuteNonQuery();
res = 1;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public int updatefct(Commande obj, int fct)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
TableDAO daot = new TableDAO();
if (daot.checkIfEmpty(obj.NumTable))
{
SqlCommand cmd = new SqlCommand("UPDATE Commande SET idFacture=@fct where IdCommande=@idc", cnx);
cmd.Parameters.AddWithValue("fct", fct);
cmd.Parameters.AddWithValue("idc", obj.IdCommande);
int done = (int)cmd.ExecuteNonQuery();
if (done > 0)
{
res = 1;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(res);
}
public List <Crediteur> getAll()
{
List <Crediteur> listc = new List <Crediteur>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Client_Crediteurs", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
listc.Add(new Crediteur(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetDecimal(3), reader.GetString(4), reader.GetInt32(5)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(listc);
}
public int updateprix(int id, decimal prixnv)
{
int res = 0;
SqlConnection cnx = Connexion.GetConnection();
try
{
SqlCommand Updateetat = new SqlCommand("UPDATE Commande SET prixTotal=@prix where IdCommande=@id", cnx);
Updateetat.Parameters.AddWithValue("id", id);
Updateetat.Parameters.AddWithValue("prix", prixnv);
int res1 = (int)Updateetat.ExecuteNonQuery();
if (res1 > 0)
{
res = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public int update(Crediteur crd)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("UPDATE Client_Crediteurs SET nom_prenomCred=@nomprenom,cin=@cin,totalCmdTTC=@montC,tel=@numt,idCommande=@idcmd where IdCrediteur=@id", cnx);
cmd.Parameters.AddWithValue("nomprenom", crd.nomprenom);
cmd.Parameters.AddWithValue("cin", crd.cin);
cmd.Parameters.AddWithValue("numt", crd.tel);
cmd.Parameters.AddWithValue("MontC", crd.MontantCredit);
cmd.Parameters.AddWithValue("idcmd", crd.Idcmd);
int done = (int)cmd.ExecuteNonQuery();
if (done > 0)
{
res = 1;
}
}
catch (SqlException)
{
throw;
}
finally { Connexion.closeConnection(); }
return(res);
}
public List <Article> listArticle(int id)
{
List <int> lstNumA = new List <int>();
List <int> lstQte = new List <int>();
List <Article> lstArticle = new List <Article>();
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd2 = new SqlCommand("SELECT Article.* FROM Article,lignecmd WHERE lignecmd.numcmd=@id AND lignecmd.numArticle=Article.IdArticle", cnx);
cmd2.Parameters.AddWithValue("id", id);
SqlDataReader rd2 = cmd2.ExecuteReader();
if (rd2.HasRows)
{
while (rd2.Read())
{
lstArticle.Add(new Article(rd2.GetInt32(0), rd2.GetString(1), rd2.GetDecimal(2), rd2.GetInt32(3)));
}
}
rd2.Close();
}
catch (SqlException) { throw; }
finally { Connexion.closeConnection(); }
return(lstArticle);
}
public int getIdByLib(String lib)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand sqlCmd = new SqlCommand("select IdCat from Categorie where libelleCat=@lib", cnx);
sqlCmd.Parameters.AddWithValue("lib", lib);
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
res = reader.GetInt32(0);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(res);
}
public int add(Article a)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand sqlCmd = new SqlCommand("insert into Article (Libelle, Prix, IdCategorie, platJour) values (@lib,@prix,@cat,@plat)", cnx);
sqlCmd.Parameters.AddWithValue("lib", a.Libelle);
sqlCmd.Parameters.AddWithValue("prix", a.Prix);
sqlCmd.Parameters.AddWithValue("cat", a.IdCategorie);
sqlCmd.Parameters.AddWithValue("plat", false);
res = (int)sqlCmd.ExecuteNonQuery();
if (res > 0)
{
return(res);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public List <Table> getAllTableDispo()
{
List <Table> list = new List <Table>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Tables where Etat=false", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(new Table(reader.GetInt32(0), reader.GetBoolean(1)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(list);
}
public int deleteArticle(int idArt, int id)
{
int res = 0;
SqlConnection cnx = Connexion.GetConnection();
try
{
SqlCommand sqlCmd = new SqlCommand("DELETE FROM [lignecmd] WHERE numArticle = @idArt AND numcmd = @id", cnx);
sqlCmd.Parameters.AddWithValue("idArt", idArt);
sqlCmd.Parameters.AddWithValue("id", id);
res = (int)sqlCmd.ExecuteNonQuery();
if (res > 0)
{
res = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public int add(Ticket t)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand sqlCmd = new SqlCommand("insert into Ticket (somme,date,nomSociete) values (@som,@dat,@ns)", cnx);
sqlCmd.Parameters.AddWithValue("som", t.somme);
sqlCmd.Parameters.AddWithValue("dat", t.date);
sqlCmd.Parameters.AddWithValue("ns", t.nomSociete);
sqlCmd.ExecuteNonQuery();
res = 1;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public int addArticleOnCommande(List <Article> lst, int id)
{
int res = 0;
SqlCommand insertJointure = null;
int d = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
for (int i = 0; i < lst.Count; i++)
{
insertJointure = new SqlCommand("insert into lignecmd(numcmd,numArticle) VALUES (@numcd,@numar)", cnx);
insertJointure.Parameters.AddWithValue("numcd", id);
insertJointure.Parameters.AddWithValue("numar", lst[i].IdArticle);
d = (int)insertJointure.ExecuteNonQuery();
}
if (d > 0)
{
res = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public List <Facture> getAll()
{
List <Facture> listc = new List <Facture>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Facture", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
listc.Add(new Facture(reader.GetInt32(0), reader.GetDecimal(1), reader.GetDecimal(2), reader.GetDecimal(3), reader.GetInt32(4)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(listc);
}
public List <Cheque> getAll()
{
List <Cheque> listeCh = new List <Cheque>();
SqlConnection cnx = Connexion.GetConnection();
SqlDataReader reader;
try
{
SqlCommand sqlCmd = new SqlCommand("select * from Cheque", cnx);
reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
listeCh.Add(new Cheque(reader.GetInt32(0), reader.GetDecimal(1), reader.GetDateTime(2), reader.GetString(3), reader.GetString(4), reader.GetString(5)));
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { Connexion.closeConnection(); }
return(listeCh);
}
public int delete(int id)
{
int res = 0;
SqlConnection cnx = Connexion.GetConnection();
try
{
SqlCommand sqlCmd = new SqlCommand("delete from Facture where IdFact=@id", cnx);
sqlCmd.Parameters.AddWithValue("id", id);
res = sqlCmd.ExecuteNonQuery();
if (res > 0)
{
res = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connexion.closeConnection();
}
return(res);
}
public int update(Payement obj)
{
int res = 0;
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("Select * from Payement where IdPayement=@id", cnx);
cmd.Parameters.AddWithValue("id", obj.id);
SqlDataReader rd = cmd.ExecuteReader();
}
catch (SqlException ex) { throw ex; }
finally { Connexion.closeConnection(); }
try
{
SqlConnection cnx = Connexion.GetConnection();
SqlCommand cmd = new SqlCommand("UPDATE Payement SET idTicket=@tk,idCheque=@chq,idCmd=@cmd", cnx);
cmd.Parameters.AddWithValue("tk", obj.idticket);
cmd.Parameters.AddWithValue("chq", obj.idcheque);
cmd.Parameters.AddWithValue("cmd", obj.idcmd);
int done = (int)cmd.ExecuteNonQuery();
if (done > 0)
{
res = 1;
}
}
catch (SqlException)
{
throw;
}
finally { Connexion.closeConnection(); }
return(res);
}
