private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
DataSet d = null;
DataSet c = dataBase.returnDataSet("SELECT * FROM \"Client\"");
if (radioButton1.Checked)
{
d = dataBase.returnDataSet("SELECT \"Code\",\"Date_Recu\",\"Prix_Total\",\"Prix_Partiel\",\"Code_Client\",\"Etat\",\"Livree\" FROM \"Facture\" WHERE (\"Etat\"='Non Reglee' OR \"Livree\"='Non Livree') AND date(\"Date_Livraison\")=:date", ":date", DbType.Date, DateTime.Now.Date);
}
if (radioButton2.Checked)
{
d = dataBase.returnDataSet("SELECT \"Code\",\"Date_Recu\",\"Prix_Total\",\"Prix_Partiel\",\"Code_Client\",\"Etat\",\"Livree\" FROM \"Facture\" WHERE (\"Etat\"='Non Reglee' OR \"Livree\"='Non Livree') AND date(\"Date_Livraison\")<:date", ":date", DbType.Date, DateTime.Now.Date);
}
if (radioButton3.Checked)
{
d = dataBase.returnDataSet("SELECT \"Code\",\"Date_Recu\",\"Prix_Total\",\"Prix_Partiel\",\"Code_Client\",\"Etat\",\"Livree\" FROM \"Facture\" WHERE (\"Etat\"='Non Reglee' OR \"Livree\"='Non Livree') AND date(\"Date_Livraison\")>:date", ":date", DbType.Date, DateTime.Now.Date);
}
c1TrueDBGrid1.DataSource = d.Tables[0];
c1TrueDBDropdown1.DataSource = c.Tables[0];
c1TrueDBGrid1.Splits[0].DisplayColumns["Code_Client"].Button = false;
c1TrueDBGrid1.Splits[0].DisplayColumns["Code_Client"].FilterButton = true;
c1TrueDBGrid1.Columns["Code_Client"].DropDown = this.c1TrueDBDropdown1;
c1TrueDBDropdown1.DataField = "Code";
c1TrueDBDropdown1.ListField = "Nom";
c1TrueDBGrid1.Columns["Code_Client"].ValueItems.Translate = true;
c1TrueDBDropdown1.ValueTranslate = true;
}
public Int32 UpdateData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "UPDATE \"Facture\" SET \"Date_Recu\"=:dater, \"Date_Livraison\"=:datel, \"Prix_Total\"=:prixt, \"Prix_Partiel\"=:prixp, \"Code_Client\"=:codec, \"Mode_Payement\"=:modep, \"Etat\"=:etat," +
" \"Livree\"=:livree, \"Date_Sortie\"=:datesortie, \"Date_Payement\"=:datepayement" +
" WHERE \"Code\"=:code";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":code", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":dater", DbType.DateTime));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":datel", DbType.DateTime));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prixt", DbType.Double));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prixp", DbType.Double));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":codec", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":modep", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":etat", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":livree", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":datesortie", DbType.DateTime));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":datepayement", DbType.DateTime));
command.Parameters[0].Value = this.Code;
command.Parameters[1].Value = this.Date_Recu;
command.Parameters[2].Value = this.Date_livraison;
command.Parameters[3].Value = this.Prix_Total;
command.Parameters[4].Value = this.Prix_Partiel;
command.Parameters[5].Value = this.Client.Code;
command.Parameters[6].Value = this.Mode_Payement;
command.Parameters[7].Value = this.Etat;
command.Parameters[8].Value = this.Livree;
command.Parameters[9].Value = this.Date_Sortie;
command.Parameters[10].Value = this.Date_Payement;
return(dataBase.ExecuteCommand(command));
}
private void Form1_Load(object sender, EventArgs e)
{
Login();
if (GlobalVars.Utilisateur == null)
{
this.Close();
}
else
{
ShowAdmin(GlobalVars.Utilisateur.Role == 0);
labelUser.Text = GlobalVars.Utilisateur.Login;
labelDate.Text = GlobalVars.ConnexionTime;
DB_PostgreSQL dataBase = new DB_PostgreSQL();
DataSet d = dataBase.returnDataSet("SELECT \"Code\",\"Date_Recu\",\"Prix_Total\",\"Prix_Partiel\",\"Code_Client\",\"Etat\",\"Livree\" FROM \"Facture\" WHERE (\"Etat\"='Non Reglee' OR \"Livree\"='Non Livree')AND date(\"Date_Livraison\")=:date", ":date", DbType.Date, DateTime.Now.Date);
DataSet c = dataBase.returnDataSet("SELECT * FROM \"Client\"");
c1TrueDBGrid1.DataSource = d.Tables[0];
c1TrueDBDropdown1.DataSource = c.Tables[0];
c1TrueDBGrid1.Splits[0].DisplayColumns["Code_Client"].Button = false;
c1TrueDBGrid1.Splits[0].DisplayColumns["Code_Client"].FilterButton = true;
c1TrueDBGrid1.Columns["Code_Client"].DropDown = this.c1TrueDBDropdown1;
c1TrueDBDropdown1.DataField = "Code";
c1TrueDBDropdown1.ListField = "Nom";
c1TrueDBGrid1.Columns["Code_Client"].ValueItems.Translate = true;
c1TrueDBDropdown1.ValueTranslate = true;
}
}
public Int32 SaveData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "INSERT INTO \"Facture\" (\"Code\", \"Date_Recu\", \"Date_Livraison\", \"Prix_Total\", \"Prix_Partiel\", \"Code_Client\", \"Mode_Payement\", \"Etat\", \"Livree\", \"Date_Sortie\", \"Date_Payement\" )" +
"VALUES(:code,:date_recu,:date_livraison,:prix_total,:prix_partiel,:code_client,:mode_payement,:etat,:livree,:datesortie,:datepayement)";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":code", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":date_recu", DbType.DateTime));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":date_livraison", DbType.DateTime));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prix_total", DbType.Double));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prix_partiel", DbType.Double));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":code_client", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":mode_payement", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":etat", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":livree", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":datesortie", DbType.DateTime));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":datepayement", DbType.DateTime));
command.Parameters[0].Value = this.Code;
command.Parameters[1].Value = this.Date_Recu;
command.Parameters[2].Value = this.Date_livraison;
command.Parameters[3].Value = this.Prix_Total;
command.Parameters[4].Value = this.Prix_Partiel;
command.Parameters[5].Value = this.Client.Code;
command.Parameters[6].Value = this.Mode_Payement;
command.Parameters[7].Value = this.Etat;
command.Parameters[8].Value = this.Livree;
command.Parameters[9].Value = this.Date_Sortie;
command.Parameters[10].Value = this.Date_Payement;
return(dataBase.ExecuteCommand(command));
}
private void GestionFacture_Load(object sender, EventArgs e)
{
DB_PostgreSQL databse = new DB_PostgreSQL();
facture = databse.returnDataSet("SELECT * FROM \"Facture\"");
facture.Tables[0].Constraints.Add("PrimaryKey", facture.Tables[0].Columns["Code"], true);
c1TrueDBGrid1.DataSource = facture.Tables[0];
c1TrueDBGrid1.RowHeight = 0;
}
public Int32 UpdateData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "UPDATE \"Articles\" SET \"Libelle\"=:libelle," +
" \"N_Pieces\"=:npieces," +
" \"Description\"=:description," +
" \"Prix_Pressing\"=:prixpressing," +
" \"Prix_Repassage\"=:prixrepassage," +
" \"Prix_Tenture\"=:prixtenture," +
" \"Prix_Autre\"=:prixautre," +
" \"Code_Famille\"=:codef," +
" \"Photo\"=:photo," +
" \"Pressing\"=:pressing," +
" \"Repassage\"=:repassage," +
" \"Tenture\"=:tenture," +
" \"Autre\"=:autre," +
" \"Print\"=:print" +
" WHERE \"Code\"=:cde";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":cde", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":libelle", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":npieces", DbType.Decimal));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":description", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prixpressing", DbType.Decimal));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prixrepassage", DbType.Decimal));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prixtenture", DbType.Decimal));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prixautre", DbType.Decimal));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":codef", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":photo", DbType.Binary));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":pressing", DbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":repassage", DbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":tenture", DbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":autre", DbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":print", DbType.Boolean));
command.Parameters[0].Value = this.Code;
command.Parameters[1].Value = this.Libelle;
command.Parameters[2].Value = this.N_Pieces;
command.Parameters[3].Value = this.Description;
command.Parameters[4].Value = this.Prix_Pressing;
command.Parameters[5].Value = this.Prix_Repassage;
command.Parameters[6].Value = this.Prix_Tenture;
command.Parameters[7].Value = this.Prix_Autre;
command.Parameters[8].Value = this.Code_Famille;
command.Parameters[9].Value = this.Photo;
command.Parameters[10].Value = this.Pressing;
command.Parameters[11].Value = this.Repassage;
command.Parameters[12].Value = this.Tenture;
command.Parameters[13].Value = this.Autre;
command.Parameters[14].Value = this.Print;
return(dataBase.ExecuteCommand(command));
}
public InfoArticle(string Code)
{
InitializeComponent();
Save = false;
DB_PostgreSQL dataBase = new DB_PostgreSQL();
c1Combo1.DataSource = dataBase.returnDataSet("SELECT \"Code\",\"Libelle\" FROM \"Famille\"").Tables[0];
c1Combo1.DisplayMember = "Libelle";
c1Combo1.ValueMember = "Code";
currentArticle.FindByKey(Code);
SetChamp(currentArticle);
btnOK.Enabled = false;
}
public bool FindByKey(string Code)
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlDataReader reader = dataBase.returnDataReader("SELECT * FROM \"Famille\" WHERE \"Code\"='" + Code + "'");
if (reader.Read())
{
this.Code = Code;
this.Libelle = (string)reader["Libelle"];
this.Description = reader["Description"] != DBNull.Value?(string)reader["Description"]:"------------";
return(true);
}
return(false);
}
public Int32 UpdateData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "UPDATE \"Famille\" SET \"Libelle\"=:libelle, \"Description\"=:description WHERE \"Code\"=:code";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":libelle", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":description", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":code", DbType.String));
command.Parameters[0].Value = this.Libelle;
command.Parameters[1].Value = this.Description;
command.Parameters[2].Value = this.Code;
return(dataBase.ExecuteCommand(command));
}
public Int32 SaveData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "INSERT INTO \"Famille\" (\"Code\", \"Libelle\", \"Description\")" +
"VALUES(:code,:libelle,:description)";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":libelle", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":description", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":code", DbType.String));
command.Parameters[0].Value = this.Libelle;
command.Parameters[1].Value = this.Description;
command.Parameters[2].Value = this.Code;
return(dataBase.ExecuteCommand(command));
}
private void buttonOk_Click(object sender, EventArgs e)
{
if (VerifierChamo())
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "INSERT INTO \"Cheque\" (\"Banque\", \"Montant\", \"Date\", \"Num\", \"Code_Facture\") " +
"Values('" + comboBox1.Text + "','" + textBox1.Text + "',:date,'" + textBox2.Text + "','" + CodeFacture + "')";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":date", DbType.DateTime));
command.Parameters[0].Value = dateTimePicker1.Value;
dataBase.ExecuteCommand(command);
((Payement)Owner).regle = true;
this.Close();
}
}
public static List <TB_Famille> GetList()
{
List <TB_Famille> list = new List <TB_Famille>();
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlDataReader reader = dataBase.returnDataReader("SELECT * FROM \"Famille\"");
while (reader.Read())
{
TB_Famille f = new TB_Famille();
f.Code = (string)reader["Code"];
f.Libelle = (string)reader["Libelle"];
f.Description = reader["Description"] != DBNull.Value?(string)reader["Description"]:"------------";
list.Add(f);
}
return(list);
}
public bool FindByKey(string Code)
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlDataReader reader = dataBase.returnDataReader("SELECT * FROM \"Client\" WHERE \"Code\"='" + Code + "'");
if (reader.Read())
{
this.Code = Code;
this.Nom = (string)reader["Nom"];
this.Prenom = (string)reader["Prenom"];
this.NTel = (string)reader["Tel"];
this.Adresse = (string)reader["Adresse"];
return(true);
}
return(false);
}
public static List <TB_Articles> GetList(string Code_Famille, string Op)
{
List <TB_Articles> list = new List <TB_Articles>();
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlDataReader reader = dataBase.returnDataReader("SELECT * FROM \"Articles\" WHERE \"Code_Famille\"='" + Code_Famille + "'");
while (reader.Read())
{
TB_Articles a = new TB_Articles();
a.Code = (string)reader["Code"];
a.Libelle = (string)reader["Libelle"];
a.N_Pieces = (int)reader["N_Pieces"];
a.Description = (string)reader["Description"];
a.Prix_Pressing = (decimal)reader["Prix_Pressing"];
a.Prix_Repassage = (decimal)reader["Prix_Repassage"];
a.Prix_Tenture = (decimal)reader["Prix_Tenture"];
a.Prix_Autre = (decimal)reader["Prix_Autre"];
a.Code_Famille = (string)reader["Code_Famille"];
a.Print = (bool)reader["Print"];
a.Photo = reader["Photo"] != DBNull.Value ? (byte[])reader["Photo"] : null;
if (Op != string.Empty)
{
if ((Op == "Pressing") & (a.Pressing))
{
list.Add(a);
}
if ((Op == "Repassage") & (a.Repassage))
{
list.Add(a);
}
if ((Op == "Tenture") & (a.Tenture))
{
list.Add(a);
}
if ((Op == "Autre") & (a.Autre))
{
list.Add(a);
}
}
else
{
list.Add(a);
}
}
return(list);
}
public string GenerateCode()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
long count = (Int64)dataBase.executeScalar("SELECT count(*) FROM \"Famille\"");
string code = "F_" + count.ToString();
while (FindByKey(code))
{
count++;
code = "F_" + count.ToString();
}
this.Code = code;
return(code);
}
public string GenerateCode()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
string prefix = string.Empty;
long count = (Int64)dataBase.executeScalar("SELECT count(*) FROM \"Client\"");
prefix = GlobalVars.PrefixClient;
string code = prefix + count.ToString();
while (FindByKey(code))
{
count++;
code = prefix + count.ToString();
}
this.Code = code;
return(code);
}
public bool FindByKey(string Login)
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlDataReader reader = dataBase.returnDataReader("SELECT * FROM \"User\" WHERE \"Login\"='" + Login + "'");
if (reader.Read())
{
this.Login = (string)reader["Login"];
this.Password = (string)reader["Password"];
this.Tel = (string)reader["Tel"];
this.Photo = reader["Photo"] is DBNull ? null : (byte[])reader["Photo"];
this.Role = (int)reader["Role"];
_Existe = true;
return(true);
}
return(false);
}
public Int32 UpdateData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "UPDATE \"Client\" SET \"Nom\"=:nom, \"Prenom\"=:prenom, \"Tel\"=:tel, \"Adresse\"=:adresse WHERE \"Code\"=:code";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":nom", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":prenom", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":tel", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":adresse", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":code", DbType.String));
command.Parameters[0].Value = this.Nom;
command.Parameters[1].Value = this.Prenom;
command.Parameters[2].Value = this.NTel;
command.Parameters[3].Value = this.Adresse;
command.Parameters[4].Value = this.Code;
return(dataBase.ExecuteCommand(command));
}
public static List <TB_Client> GetList()
{
List <TB_Client> list = new List <TB_Client>();
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlDataReader reader = dataBase.returnDataReader("SELECT * FROM \"Client\"");
while (reader.Read())
{
TB_Client c = new TB_Client();
c.Code = (string)reader["Code"];
c.Nom = (string)reader["Nom"];
c.Prenom = (string)reader["Prenom"];
c.NTel = (string)reader["Tel"];
c.Adresse = (string)reader["Adresse"];
list.Add(c);
}
return(list);
}
public Int32 UpdateData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "UPDATE \"User\" SET \"Password\"=:password, \"Tel\"=:tel, \"Photo\"=:photo, \"Role\"=:role WHERE \"Login\"='" + this.Login + "'";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":nom", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":password", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":tel", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":photo", DbType.Binary));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":role", DbType.Int16));
command.Parameters[0].Value = this.Login;
command.Parameters[1].Value = this.Password;
command.Parameters[2].Value = this.Tel;
command.Parameters[3].Value = this.Photo;
command.Parameters[4].Value = this.Role;
return(dataBase.ExecuteCommand(command));
}
public Int32 SaveData()
{
DB_PostgreSQL dataBase = new DB_PostgreSQL();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand();
command.CommandText = "INSERT INTO \"User\" (\"Login\",\"Password\",\"Tel\",\"Photo\", \"Role\")" +
"VALUES(:nom,:password,:tel,:photo,:role)";
command.Parameters.Add(new Npgsql.NpgsqlParameter(":nom", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":password", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":tel", DbType.String));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":photo", DbType.Binary));
command.Parameters.Add(new Npgsql.NpgsqlParameter(":role", DbType.Int16));
command.Parameters[0].Value = this.Login;
command.Parameters[1].Value = this.Password;
command.Parameters[2].Value = this.Tel;
command.Parameters[3].Value = this.Photo;
command.Parameters[4].Value = this.Role;
return(dataBase.ExecuteCommand(command));
}