// Methode de login pour le client
public List <ClientModel> GetLogin(string phone_number)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <ClientModel> _listeClient = new List <ClientModel>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
//string s = "SELECT nom AS [@nom], postnom AS [@postnom], matricule AS [@matricule]," +
// " dateAbonnement AS [@dateAbonnement], nombreAmpere AS [@nombreAmpere]"+
// " FROM enregistrement";
string s = "SELECT * FROM tClients WHERE PhoneClient = @PhoneNumber";
//SELECT * FROM tClasse
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.Parameters.AddWithValue("@PhoneNumber", phone_number);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
ClientModel objCust = new ClientModel();
objCust.IdClient = Convert.ToInt32(_Reader["IdClient"].ToString());
objCust.PhoneClient = _Reader["PhoneClient"].ToString();
objCust.PseudoClient = _Reader["PseudoClient"].ToString();
objCust.PinClient = _Reader["PinClient"].ToString();
objCust.CompteClient = _Reader["CompteClient"].ToString();
objCust.CodeClient = _Reader["CodeClient"].ToString();
try { objCust.Niveau = Convert.ToInt32(_Reader["Niveau"].ToString()); } catch { objCust.Niveau = 0; }
_listeClient.Add(objCust);
}
return(_listeClient);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public int AnnulerConversion(string CodeConversion)
{
try
{
// string dernier_EB = DernierEtatBesoin() + "EB" + InitialNomUtilisateur;
string s = " DELETE FROM tConversionPoint " +
" WHERE CodeConversion=@a ";
//Em.CodeEtatdeBesoin, change par dernier_EB
string[] r = { CodeConversion };
DateTime[] d = { DateTime.Now };
// Verification de solde
//double solde.
//Clients.ClientDataAccessLayer Dal = new Clients.ClientDataAccessLayer();
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.seteconnexion(), s, r, d);;
//EtatBesoinModel etat = new EtatBesoinModel();
//etat.CodeEtatdeBesoin = dernier_EB;
//etat.DesignationEtatDeBesion = Em.DesignationEtatDeBesion;
return(1);
}
catch
{
return(0);
throw;
}
}
public ConversionPointModel NouvelleConversion(ConversionPointModel Obj)
{
try
{
// string dernier_EB = DernierEtatBesoin() + "EB" + InitialNomUtilisateur;
string s = "INSERT INTO tConversionPoint " +
" (CodeConversion,CodeClient, PointConvertie, RefOperation,DateOperation) " +
" VALUES(@a,@b, @c,@d,@da) ";
//Em.CodeEtatdeBesoin, change par dernier_EB
string[] r = { DernierCon(), Obj.CodeClient, Obj.PointConvertie, Obj.RefOperation, };
DateTime[] d = { DateTime.Now };
// Verification de solde
//double solde.
//Clients.ClientDataAccessLayer Dal = new Clients.ClientDataAccessLayer();
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.seteconnexion(), s, r, d);;
//EtatBesoinModel etat = new EtatBesoinModel();
//etat.CodeEtatdeBesoin = dernier_EB;
//etat.DesignationEtatDeBesion = Em.DesignationEtatDeBesion;
return(Obj);
}
catch
{
return(null);
throw;
}
}
// Methode for inserting a new client in database
public int InsertNewClient(ClientModel clientModel)
{
using (SqlConnection connection = new SqlConnection(ClassVariableGlobal.SetConnexion()))
{
connection.Open();
string query = "INSERT INTO tClients" +
"(PhoneClient, PseudoClient, PinClient, CompteClient, CodeClient)" +
"VALUES(@PhoneClient, @PseudoClient, @PinClient, @CompteClient, @CodeClient)";
SqlCommand commande = new SqlCommand(query, connection);
int code_client = getDernierClient();
commande.Parameters.AddWithValue("@PhoneClient", clientModel.PhoneClient);
commande.Parameters.AddWithValue("@PseudoClient", clientModel.PseudoClient);
commande.Parameters.AddWithValue("@PinClient", clientModel.PinClient);
commande.Parameters.AddWithValue("@CompteClient", clientModel.CompteClient);
commande.Parameters.AddWithValue("@CodeClient", code_client);
//commande.Parameters.AddWithValue("@IdCategorieUt", clientModel.IdCategorieUt);
return(commande.ExecuteNonQuery());
}
}
public PointFacuration TotlalDesPointFacturePourUnePeriode(string CodeClient, DateTime date1, DateTime date2)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.seteconnexion()))
try
{
Conn.Open();
PointFacuration objCust = new PointFacuration();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
String s1 = "SommeDePointFacturePourUnePeriode";
SqlCommand objCommand = new SqlCommand(s1, Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("@CodeClient", CodeClient);
objCommand.Parameters.AddWithValue("@Date1", date1);
objCommand.Parameters.AddWithValue("@Date2", date2);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
objCust.PseudoClient = _Reader["PseudoClient"].ToString();
objCust.PhoneClient = _Reader["PhoneClient"].ToString();
objCust.CodeClient = _Reader["CodeClient"].ToString();
// objCust.MontantApayer = _Reader["NoRccm"].ToString();
try { objCust.SMontantRistourne = Convert.ToDouble(_Reader["SMontantRistourne"]); } catch { objCust.SMontantRistourne = 0; }
try { objCust.SommeFact = Convert.ToDouble(_Reader["SommeFact"]); } catch { objCust.SommeFact = 0; }
// try { objCust.SommePaye = Convert.ToDouble(_Reader["SommePaye"]); } catch { objCust.SommePaye = 0; }
// _list.Add(objCust);
}
return(objCust);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
//Methode pour get tous les factures suivants le client
public List <FactureModel> GetListeFacture(string codeClient)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <FactureModel> _listeFacture = new List <FactureModel>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT * FROM tFacturation where CodeClient=@codeClient";
//SELECT * FROM tClasse
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.Parameters.AddWithValue("@codeClient", codeClient);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
FactureModel objCust = new FactureModel();
objCust.IdFacture = Convert.ToInt32(_Reader["IdFacture"]);
objCust.RefFacture = _Reader["RefFacture"].ToString();
objCust.MontantFacture = Convert.ToDouble(_Reader["Quantite"]);
objCust.QuantiteFacture = Convert.ToDouble(_Reader["Montant"]);
objCust.CodeClientFacture = _Reader["CodeClient"].ToString();
objCust.DateFacture = Convert.ToDateTime(_Reader["DateFacture"]);
objCust.MontantRistourne = Convert.ToDouble(_Reader["MontantRistourne"]);
_listeFacture.Add(objCust);
}
return(_listeFacture);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Liste point converti par periode par client
public List <ConversionPointModel> GetListePointsConvertisTouParperiode(string codeClient, DateTime date1, DateTime date2)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <ConversionPointModel> _listePointConvertis = new List <ConversionPointModel>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "ConversionPourLaPeriode";
//SELECT * FROM tClasse
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("@CodeClient", codeClient);
objCommand.Parameters.AddWithValue("@Date1", date1);
objCommand.Parameters.AddWithValue("@Date2", date2);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
ConversionPointModel objCust = new ConversionPointModel();
// objCust.IdFacture = Convert.ToInt32(_Reader["IdFacture"]);
objCust.CodeClient = _Reader["codeClient"].ToString();
objCust.CodeConversion = (_Reader["codeConversion"].ToString());
try { objCust.DateOperation = (_Reader["dateOperation"].ToString()); } catch { objCust.DateOperation = DateTime.Now.ToString(); }
try { objCust.PointConvertie = _Reader["pointConvertie"].ToString(); } catch { objCust.PointConvertie = "0"; }
objCust.RefOperation = (_Reader["refOperation"].ToString());
_listePointConvertis.Add(objCust);
}
return(_listePointConvertis);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Methode pour liste les produits par categorie
public List <ArticleModel> GetStockPListe(string Categorie)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <ArticleModel> _listtStock = new List <ArticleModel>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
//Compte=310100
string s = "SELECT tArticle.CodeArticle AS CodeMarchandise, tArticle.DesegnationArticle AS DesignationMarchandise, tArticle.PrixAchat, tArticle.PrixVente," +
" tCompte.DesignationCompte, tCatArticle.DesCategorieA FROM tArticle INNER JOIN tCompte ON tArticle.Compte = tCompte.NumCompte INNER JOIN" +
" tCatArticle ON tArticle.CategorieArticle = tCatArticle.IdCategorieArtilcle WHERE(tCatArticle.IdCategorieArtilcle = @CategorieArticle)";
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.Parameters.AddWithValue("@CategorieArticle ", Categorie);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
ArticleModel objCust = new ArticleModel();
objCust.CodeArticle = (_Reader["CodeMarchandise"]).ToString();
objCust.DesegnationArticle = (_Reader["DesignationMarchandise"]).ToString();
objCust.PrixAchat = float.Parse((_Reader["PrixAchat"]).ToString());
objCust.PrixVente = float.Parse((_Reader["PrixVente"]).ToString());
//objCust.CategorieArticle = Convert.ToInt32((_Reader["CategorieArticle"]).ToString());
_listtStock.Add(objCust);
}
return(_listtStock);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public List <CompteModel> ListeDeComptes()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <CompteModel> _list = new List <CompteModel>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
String s1 = " SELECT ISNULL(NumCompte, 0) as NumCompte, " +
"ISNULL(DesignationCompte, '') as DesignationCompte, " +
"ISNULL(GroupeCompte, 0) as GroupeCompte, " +
"ISNULL(Unite, 0) as Unite, " +
"ISNULL(Solde, '') as Solde FROM tCompte";
SqlCommand objCommand = new SqlCommand(s1, Conn);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
CompteModel objCust = new CompteModel();
objCust.NumCompte = Convert.ToInt32(_Reader["NumCompte"]);
objCust.DesignationCompte = _Reader["DesignationCompte"].ToString();
objCust.GroupeCompte = Convert.ToInt32(_Reader["GroupeCompte"]);
objCust.Unite = Convert.ToInt32(_Reader["Unite"]);
objCust.Solde = _Reader["Solde"].ToString();
_list.Add(objCust);
}
return(_list);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
//Get la liste des etats de ristourne
public List <TypeRistourne> GetListeTypeRistourne()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <TypeRistourne> _listeEtatRistourne = new List <TypeRistourne>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
//string s = "SELECT nom AS [@nom], postnom AS [@postnom], matricule AS [@matricule]," +
// " dateAbonnement AS [@dateAbonnement], nombreAmpere AS [@nombreAmpere]"+
// " FROM enregistrement";
string s = "SELECT * FROM tTypeRistourne";
//SELECT * FROM tClasse
SqlCommand objCommand = new SqlCommand(s, Conn);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
TypeRistourne objCust = new TypeRistourne();
objCust.IdTypeRistourne = Convert.ToInt32(_Reader["IdTypeRistourne"]);
objCust.DesignationTypeRistourne = _Reader["DesignationTypeRistourne"].ToString();
objCust.EtatRistourne = _Reader["EtatRistourne"].ToString();
_listeEtatRistourne.Add(objCust);
}
return(_listeEtatRistourne);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public void AjouterOperation(OperationModel Op)
{
string s = " INSERT INTO tOperation" +
" (NumOperation, Libelle, CodeEtatdeBesoin,NomUt, DateOp, DateSysteme) " +
" VALUES(@a, @b, @c, @d, @da, @db)";
string[] r = { Op.NumOperation, Op.Libelle, Op.CodeEtatdeBesoin, Op.NomUt, };
DateTime[] d = { Op.DateOp, DateTime.Now };
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.SetConnexion(), s, r, d);
}
// Methode pour get les parametres du taux
public List <TauxRistourne> GetListeTauxRistourne()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <TauxRistourne> _listeTauxRistourne = new List <TauxRistourne>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT * FROM tParametreTaux";
SqlCommand objCommand = new SqlCommand(s, Conn);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
TauxRistourne objCust = new TauxRistourne();
objCust.IdTaux = Convert.ToInt32(_Reader["IdParametre"]);
objCust.TauxSurMontant = Convert.ToDouble(_Reader["TauxRistourneMontant"]);
objCust.TauxSurQuantite = Convert.ToDouble(_Reader["TauxRistourneQuantite"]);
objCust.CompteRistourne = Convert.ToInt32(_Reader["CompteRistourne"]);
_listeTauxRistourne.Add(objCust);
}
return(_listeTauxRistourne);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public void SupprimmeerOperation(string Op)
{
string s = " delete FROM tOperation " +
" WHERE NumOperation=@a ";
string[] r = { Op };
DateTime[] d = { DateTime.Now };
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.SetConnexion(), s, r, d);
}
public void AjouterMvtCompte(MvtCompteModel MvtC)
{
string s = " INSERT INTO tMvtCompte " +
"(NumCompte, NumOperation, Details, Qte, Entree, Sortie, CodeProject) " +
"VALUES(@a, @b, @c, @d, @e, @f, @g)";
string[] r = { MvtC.NumCompte, MvtC.NumOperation, MvtC.Details.ToString(),
MvtC.Qte.ToString(), MvtC.Entree.ToString(), MvtC.Sortie.ToString(), MvtC.CodeProject };
DateTime[] d = { };
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.SetConnexion(), s, r, d);
}
// Methode pour verifier si l utilisateur existe dans le systeme
public bool isUserExist(string isUserPhoneExist)
{
// dbConnector objConn = new dbConnector();
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT PhoneClient FROM tClients WHERE (PhoneClient = @phonenumber) ";
//SELECT * FROM tClasse
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.Parameters.AddWithValue("@phonenumber", isUserPhoneExist);;
//objCommand.Parameters.AddWithValue("@IsPaging", IsPaging);
SqlDataReader _Reader = objCommand.ExecuteReader();
if (_Reader.Read())
{
return(true);
}
else
{
return(false);
}
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Methodes pour selection les categories des articles
public List <tCatArticle> GetCatArticlePListe(string OP)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
List <tCatArticle> _listCatArticle = new List <tCatArticle>();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT IdCategorieArtilcle, DesCategorieA " +
" FROM tCatArticle ";
SqlCommand objCommand = new SqlCommand(s, Conn);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
tCatArticle objCust = new tCatArticle();
objCust.IdCategorieArtilcle = Convert.ToInt32(_Reader["IdCategorieArtilcle"]);
objCust.DesCategorieA = (_Reader["DesCategorieA"]).ToString();;
//objCust.DateDujour = Convert.ToDateTime(_Reader["DesignationClasse"]);
_listCatArticle.Add(objCust);
}
return(_listCatArticle);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Formation du code de l utilisateur
public int getDernierClient()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT MAX(IdCLient) AS maxIDClient FROM tClients ";
SqlCommand objCommand = new SqlCommand(s, Conn);
SqlDataReader _Reader = objCommand.ExecuteReader();
int dernier = 0;
if (_Reader.Read())
{
dernier = Convert.ToInt32(_Reader["maxIDClient"]);
return(dernier + 1);
}
else
{
return(1);
}
}
catch
{
return(1);
// throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Methode pour selectionner la somme des points de chaque client par rapport à son code
public double GetLesPoints(string codeClient)
{
double sommeDesPoints = 0;
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT SUM(MontantRistourne) as somme FROM tFacturation WHERE CodeClient=@codeClient";
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.Parameters.AddWithValue("@codeClient", codeClient);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
sommeDesPoints = Convert.ToDouble(_Reader["somme"]);
}
return(sommeDesPoints);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public void AjouterArticle(ArticleModel Art)
{
string s = "INSERT INTO tArticle" +
"(CodeArticle, CodeDepartement, DesegnationArticle, CategorieArticle, PrixAchat," +
" Critique, PrixVente, Compte, UniteEngro, UiniteEnDetaille, QteEnDet, Enstock," +
" Solde, CompteFournisseur) " +
"VALUES(@a, @b, @c, @d, @e, @f, @g, @h, @j, @k, @l, @m, @n, @o)";
string[] r = { Art.CodeArticle, Art.CodeDepartement, Art.DesegnationArticle,
Art.CategorieArticle.ToString(), Art.PrixAchat.ToString(), Art.Critique.ToString(),Art.PrixVente.ToString()
, Art.Compte.ToString(), Art.UniteEngro, Art.UiniteEnDetaille.ToString(),Art.QteEnDet.ToString(),
Art.Enstock.ToString(), Art.Solde.ToString(), Art.CompteFournisseur.ToString() };
DateTime[] d = { };
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.SetConnexion(), s, r, d);;
}
public double SommeDeConversion(DateTime date1, DateTime date2)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.seteconnexion()))
try
{
// Conn.Open();
double Montant = 0;
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT SUM(tConversionPoint.PointConvertie) AS SPointConvertie " +
" FROM tClients INNER JOIN " +
" tConversionPoint ON tClients.CodeClient = tConversionPoint.CodeClient " +
" WHERE(tConversionPoint.DateOperation BETWEEN @da AND @db)";
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.CommandType = CommandType.Text;
objCommand.Parameters.AddWithValue("@da", date1);
objCommand.Parameters.AddWithValue("@db", date2);
Montant = double.Parse(objCommand.ExecuteScalar().ToString());
return(Montant);
}
catch
{
return(0);
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public int ModifierParametreTaux(TauxRistourne taux)
{
using (SqlConnection connection = new SqlConnection(ClassVariableGlobal.SetConnexion()))
{
connection.Open();
string query = "UPDATE tParametreTaux " +
" SET TauxRistourneMontant = @TauxRistourneMontant, TauxRistourneQuantite = @TauxRistourneQuantite, CompteRistourne = @CompteRistourne " +
" WHERE(IdParametre = 1)";
SqlCommand commande = new SqlCommand(query, connection);
commande.Parameters.AddWithValue("@TauxRistourneMontant", taux.TauxSurMontant);
commande.Parameters.AddWithValue("@TauxRistourneQuantite", taux.TauxSurQuantite);
commande.Parameters.AddWithValue("@CompteRistourne", taux.CompteRistourne);
return(commande.ExecuteNonQuery());
}
}
public double SommeDeRestourneSolde(DateTime date2)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.seteconnexion()))
try
{
// Conn.Open();
double Montant = 0;
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT SUM(tFacturation.MontantRistourne) AS SMontantRistourne " +
" FROM tClients INNER JOIN " +
" tFacturation ON tClients.CodeClient = tFacturation.CodeClient " +
" WHERE(tFacturation.DateFacture <= @db)";
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.CommandType = CommandType.Text;
//objCommand.Parameters.AddWithValue("@da", date1);
objCommand.Parameters.AddWithValue("@db", date2);
Montant = double.Parse(objCommand.ExecuteScalar().ToString());
return(Montant);
}
catch
{
return(0);
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Methode pour enregistrer le taux sur le systeme
public int InsertTauxRistourne(TauxRistourne taux)
{
using (SqlConnection connection = new SqlConnection(ClassVariableGlobal.SetConnexion()))
{
connection.Open();
string query = "INSERT INTO tParametreTaux" +
" (TauxRistourneMontant, TauxRistourneQuantite,CompteRistourne)" +
" VALUES(@TauxRistourneMontant, @TauxRistourneQuantite,@CompteRistourne)";
SqlCommand commande = new SqlCommand(query, connection);
commande.Parameters.AddWithValue("@TauxRistourneMontant", taux.TauxSurMontant);
commande.Parameters.AddWithValue("@TauxRistourneQuantite", taux.TauxSurQuantite);
commande.Parameters.AddWithValue("@CompteRistourne", taux.CompteRistourne);
return(commande.ExecuteNonQuery());
}
}
// Enregistrement du type de ristourne
public int InsertTypeRistourne(TypeRistourne typeRistourne)
{
using (SqlConnection connection = new SqlConnection(ClassVariableGlobal.SetConnexion()))
{
connection.Open();
string query = "INSERT INTO tTypeRistourne" +
" (DesignationTypeRistourne, EtatRistourne)" +
" VALUES(@DesignationTypeRistourne, @EtatRistourne)";
SqlCommand commande = new SqlCommand(query, connection);
commande.Parameters.AddWithValue("@DesignationTypeRistourne", typeRistourne.DesignationTypeRistourne);
commande.Parameters.AddWithValue("@EtatRistourne", typeRistourne.EtatRistourne);
return(commande.ExecuteNonQuery());
}
}
public void InsertTest(tTest test)
{
using (SqlConnection connection = new SqlConnection(ClassVariableGlobal.SetConnexion()))
{
connection.Open();
string query = "INSERT INTO tTest(SavedTest) VALUES(@a)";
//SqlCommand commande = new SqlCommand(query, connection);
//commande.Parameters.AddWithValue("@SavedTest", test.ToSave);
string[] r = { test.ToSave };
DateTime[] rd = { DateTime.UtcNow };
ClassRequette req = new ClassRequette();
req.ExecuterSqlAvecDate(ClassVariableGlobal.SetConnexion(), query, r, rd);
}
}
public int DernierOperation()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
int dernier_operation = 0;
string s = "select ISNULL(max(NumOpSource), 0) as NumOpSource from tOperation";
SqlCommand objCommand = new SqlCommand(s, Conn);
//SqlDataReader _Reader = objCommand.ExecuteReader();
//while (_Reader.Read())
//{
// dernier_operation = Convert.ToInt32(_Reader["dernierOperation"]);
//}
dernier_operation = int.Parse(objCommand.ExecuteScalar().ToString());
return(dernier_operation);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public int DernierMvtCompte()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.SetConnexion()))
try
{
Conn.Open();
int dernier_mvt_compte = 0;
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "select max(IdMouvement) as IdMouvement from tMvtCompte";
SqlCommand objCommand = new SqlCommand(s, Conn);
//SqlDataReader _Reader = objCommand.ExecuteReader();
//while (_Reader.Read())
//{
// dernier_mvt_compte = Convert.ToInt32(_Reader["IdMouvement"].ToString());
//}
dernier_mvt_compte = int.Parse(objCommand.ExecuteScalar().ToString());
return(dernier_mvt_compte);
}
catch
{
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public double BalanceDePoints(string CodeClient)
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.seteconnexion()))
try
{
// Conn.Open();
double dernier_operation = 0;
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT BalancePoint FROM View_BalaceDePointClient " +
" WHERE(CodeClient = @CodeClient) ";
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.Parameters.AddWithValue("@CodeClient", CodeClient);;
objCommand.CommandType = CommandType.Text;
dernier_operation = double.Parse(objCommand.ExecuteScalar().ToString());
return(dernier_operation);
}
catch
{
return(0);
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
public string DernierCon()
{
using (SqlConnection Conn = new SqlConnection(ClassVariableGlobal.seteconnexion()))
try
{
// Conn.Open();
int dernier_operation = 0;
if (Conn.State != System.Data.ConnectionState.Open)
{
Conn.Open();
}
string s = "SELECT MAX(IdConversion) Dernier " +
" FROM tConversionPoint ";
SqlCommand objCommand = new SqlCommand(s, Conn);
objCommand.CommandType = CommandType.Text;
dernier_operation = int.Parse(objCommand.ExecuteScalar().ToString()) + 1;
return("CV" + dernier_operation.ToString());
}
catch
{
return("CV");
throw;
}
finally
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
}
}
}
// Methode pour enregistrer une facture
public int InsertFacture(FactureModel facture)
{
using (SqlConnection connection = new SqlConnection(ClassVariableGlobal.SetConnexion()))
{
connection.Open();
string query = "INSERT INTO tFacturation" +
"(RefFacture, Quantite, Montant, CodeClient," +
"DateFacture, MontantRistourne)VALUES(@RefFacture, @Quantite, @Montant, " +
" @CodeClient, @DateFacture, @MontantRistourne)";
SqlCommand commande = new SqlCommand(query, connection);
commande.Parameters.AddWithValue("@RefFacture", facture.RefFacture);
commande.Parameters.AddWithValue("@Quantite", facture.QuantiteFacture);
commande.Parameters.AddWithValue("@Montant", facture.MontantFacture);
commande.Parameters.AddWithValue("@CodeClient", facture.CodeClientFacture);
commande.Parameters.AddWithValue("@DateFacture", facture.DateFacture);
commande.Parameters.AddWithValue("@MontantRistourne", facture.MontantRistourne);
return(commande.ExecuteNonQuery());
}
}
