private void buttonX3_Click(object sender, EventArgs e)
{
// Bouton Supprimer Livraison
if (dataGridViewX1.Rows.Count != 0)
{
string sql = "select * from eentd where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "' and facture = false";
DataSet dss = met.recuperer_table(sql, "eentd");
if (dss.Tables["eentd"].Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
String req = "DELETE FROM eentd Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "'";
if (met.Execute(req))
{
String req1 = "DELETE FROM lentd Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "'";
if (met.Execute(req1))
{
MessageBox.Show("Suppression effectuée");
}
}
buttonX1_Click(sender, e);
}
}
else
{
MessageBox.Show("Devis déja Facturée", "Impossible");
}
}
}
private void buttonX3_Click(object sender, EventArgs e)
{
// Bouton Supprimer Bon Comptoir
if (dataGridViewX1.Rows.Count != 0)
{
string sql = "select * from eentcb where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["NuM"].Value + "' and facture = false";
DataSet dss = met.recuperer_table(sql, "eentcb");
if (dss.Tables["eentcb"].Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
string req1 = "Select * from lentcb where codes='" + Program.Societe + "' and codee='" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["NuM"].Value + "' ";
DataSet tmp1 = met.recuperer_table(req1, "lentcb");
foreach (DataRow dr1 in tmp1.Tables["lentcb"].Rows)
{
double xqte = 0, QTESTK = 0, QTESOR = 0, QTEMAG = 0, QTESORMAG = 0, QTESTE = 0, QTESORSTE = 0;
string xcodea = "";
xcodea = dr1.Field <string>("codea" + "");
string xcodeg = dr1.Field <string>("codeg" + "");
string xcoded = dr1.Field <string>("coded" + "");
try
{
xqte = dr1.Field <double>("qte");
}
catch { }
#region // Update stock
string sql1 = "Select qtestk,qtesor from artdep where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and coded='" + xcoded + "' and codem='" + Program.Magasin + "' and codeg='" + xcodeg + "'";
DataSet ds1 = met.recuperer_table(sql1, "artdep");
if (ds1 != null)
{
if (ds1.Tables.Count != 0)
{
if (ds1.Tables["artdep"].Rows.Count != 0)
{
Double.TryParse(ds1.Tables["artdep"].Rows[0]["qtestk"] + "", out QTESTK);
Double.TryParse(ds1.Tables["artdep"].Rows[0]["qtesor"] + "", out QTESOR);
}
}
}
string sql2 = "Select qtestk,qtesor from artmag where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and codem='" + Program.Magasin + "'";
DataSet ds2 = met.recuperer_table(sql2, "artmag");
if (ds2 != null)
{
if (ds2.Tables.Count != 0)
{
if (ds2.Tables["artmag"].Rows.Count != 0)
{
Double.TryParse(ds2.Tables["artmag"].Rows[0]["qtestk"] + "", out QTEMAG);
Double.TryParse(ds2.Tables["artmag"].Rows[0]["qtesor"] + "", out QTESORMAG);
}
}
}
string sql3 = "Select qtestk,qtesor,vstk from article where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and code ='" + xcodea + "' ";
DataSet ds3 = met.recuperer_table(sql3, "article");
if (ds3 != null)
{
if (ds3.Tables.Count != 0)
{
if (ds3.Tables["article"].Rows.Count != 0)
{
xvstk = ds3.Tables["article"].Rows[0].Field <Boolean>("vstk");
Double.TryParse(ds3.Tables["article"].Rows[0]["qtestk"] + "", out QTESTE);
Double.TryParse(ds3.Tables["article"].Rows[0]["qtesor"] + "", out QTESORSTE);
}
}
}
if (xvstk == true)
{
string sqlup = "UPDATE artdep set qtestk = '" + (QTESTK + xqte) + "',qtesor='" + (QTESOR - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and coded='" + xcoded + "' and codem='" + Program.Magasin + "' and codeg='" + xcodeg + "'";
met.Execute(sqlup);
sqlup = "UPDATE artmag set qtestk = '" + (QTEMAG + xqte) + "',qtesor='" + (QTESORMAG - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and codem='" + Program.Magasin + "'";
met.Execute(sqlup);
sqlup = "UPDATE article set qtestk = '" + (QTESTE + xqte) + "',qtesor='" + (QTESORSTE - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and code ='" + xcodea + "' ";
met.Execute(sqlup);
}
#endregion // Update stock
}
String req5 = "Select code,debit,solde From client where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["CODEC"].Value + "'";
DataSet dsc = met.recuperer_table(req5, "client");
decimal wdeb = 0, wsld = 0;
try
{
wdeb = dsc.Tables["client"].Rows[0].Field <decimal>("debit");
}
catch { }
try
{
wsld = dsc.Tables["client"].Rows[0].Field <decimal>("solde");
}
catch { }
try
{
decimal.TryParse(dataGridViewX1.SelectedRows[0].Cells[7].Value + "", out xdeb);
}
catch { }
String upcli = "UPDATE client SET debit = " + (wdeb - xdeb)
+ ", solde=" + (wsld - xdeb)
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["CODEC"].Value + "'";
met.Execute(upcli);
String req3 = "DELETE FROM eentcb Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["NuM"].Value + "'";
if (met.Execute(req3))
{
String req4 = "DELETE FROM lentcb Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["NuM"].Value + "'";
if (met.Execute(req4))
{
MessageBox.Show("Suppression effectuée");
}
}
buttonX1_Click(sender, e);
}
}
else
{
MessageBox.Show("Bon Comptoir déja Facturée", "Impossible");
}
}
}
private void buttonX2_Click(object sender, EventArgs e)
{
if (cli != null)
{
if (!modif)
{
Double xtht = 0, xtva = 0, xttc = 0, xav = 0, xgar = 0;
Double.TryParse(Tht.Text, out xtht);
Double.TryParse(Ttva.Text, out xtva);
Double.TryParse(Tttc.Text, out xttc);
Double.TryParse(Tavnc.Text, out xav);
Double.TryParse(Tgar.Text, out xgar);
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "INSERT INTO contrat (CODE,CODEC, NOMC,ADRC,DATEDEBUT, THT, TVA,TTC,VGAR,VAVANC, CODES) VALUE ("//, AVANCE,solde,reste
+ "'" + nfact.Text + "',"
+ "'" + Tcodc.Text + "',"
+ "'" + Tnomc.Text + "',"
+ "'" + Tadrc.Text + "',"
+ "'" + dat + "',"
+ "'" + xtht.ToString().Replace(Program.sep, string.Empty) + "',"
+ "'" + xtva.ToString().Replace(Program.sep, string.Empty) + "',"
+ "'" + xttc.ToString().Replace(Program.sep, string.Empty) + "',"
+ "'" + xgar.ToString().Replace(Program.sep, string.Empty) + "',"
+ "'" + xav.ToString().Replace(Program.sep, string.Empty) + "',"
+ "'" + Program.Societe + "'"
+ ")";
String sqlID = "Select Max(ID) FROM contrat";
met.Execute(sql);
DataSet dsID = met.recuperer_table(sqlID);
String ID = string.Empty;
try
{
ID = dsID.Tables[0].Rows[0].ItemArray[0] + "";
}
catch { }
//Pylones
String sqlF = "INSERT INTO lcontrat (NPYLON,TYPEPYLON,TYPEMASSIF,ORDRE,PORTEE,PORTEEMOY,PUFOND,PUMONTAGE,PUDEROULAGE,POIDS,PUPoids,VBETON,PUBETON,CODES,IDCONTRAT) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
if (!dr.IsNewRow)
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
String xport = "Null", xportmoy = "Null", xpuf = "Null", xpum = "Null", xpud = "Null", xpoid = "Null", xpup = "Null", xvbp = "Null", xpuv = "Null";
if (dr.Cells["portee"].Value != null)
{
if (!(dr.Cells["portee"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["portee"].Value + "", out d1);
xport = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["porteemoy"].Value != null)
{
if (!(dr.Cells["porteemoy"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["porteemoy"].Value + "", out d1);
xportmoy = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUHTFondation"].Value != null)
{
if (!(dr.Cells["PUHTFondation"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUHTFondation"].Value + "", out d1);
xpuf = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUHTMontage"].Value != null)
{
if (!(dr.Cells["PUHTMontage"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUHTMontage"].Value + "", out d1);
xpum = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUHTDeroulage"].Value != null)
{
if (!(dr.Cells["PUHTDeroulage"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUHTDeroulage"].Value + "", out d1);
xpud = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["Poids"].Value != null)
{
if (!(dr.Cells["Poids"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["Poids"].Value + "", out d1);
xpoid = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUP"].Value != null)
{
if (!(dr.Cells["PUP"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUP"].Value + "", out d1);
xpup = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["VBP"].Value != null)
{
if (!(dr.Cells["VBP"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["VBP"].Value + "", out d1);
xvbp = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUV"].Value != null)
{
if (!(dr.Cells["PUV"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUV"].Value + "", out d1);
xpuv = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
sqlF += "'" + dr.Cells["Npylon"].Value + "'"
+ ",'" + dr.Cells["TypePylone"].Value + "'"
+ ",'" + dr.Cells["TypeMassif"].Value + "'"
+ ",'" + (dr.Index + 1) + "'"
+ "," + xport + ""
+ "," + xportmoy + ""
+ "," + xpuf + ""
+ "," + xpum + ""
+ "," + xpud + ""
+ "," + xpoid + ""
+ "," + xpup + ""
+ "," + xvbp + ""
+ "," + xpuv + ""
+ ",'" + Program.Societe + "'"
+ "," + ID + ")";
frst = false;
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Enregistrement avec sucée.");
}
else
{
Double xtht = 0, xtva = 0, xttc = 0, xav = 0, xgar = 0;
Double.TryParse(Tht.Text, out xtht);
Double.TryParse(Ttva.Text, out xtva);
Double.TryParse(Tttc.Text, out xttc);
Double.TryParse(Tavnc.Text, out xav);
Double.TryParse(Tgar.Text, out xgar);
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "UPDATE contrat SET " // (,, ,,, , THT, TVA,TTC, AVANCE,solde,reste) VALUE ("
+ "CODE='" + nfact.Text + "',"
+ "CODEC='" + Tcodc.Text + "',"
+ "NOMC='" + Tnomc.Text + "',"
+ "ADRC='" + Tadrc.Text + "',"
+ "THT='" + xtht.ToString().Replace(Program.sep, string.Empty) + "',"
+ "TVA='" + xtva.ToString().Replace(Program.sep, string.Empty) + "',"
+ "TTC='" + xttc.ToString().Replace(Program.sep, string.Empty) + "',"
+ "VAVANC='" + xav.ToString().Replace(Program.sep, string.Empty) + "',"
+ "VGAR='" + xgar.ToString().Replace(Program.sep, string.Empty) + "',"
+ "DATEDEBUT='" + dat + "',"
+ "CODES='" + Program.Societe + "'"
+ "WHERE ID = " + IDContrat;
met.Execute(sql);
String ID = IDContrat;
//Pylones
foreach (string IDSUP in Lsupp)
{
String sqldel = "DELETE FROM lcontrat WHERE ID = " + IDSUP;
met.Execute(sqldel);
}
String sqlF = "INSERT INTO lcontrat (NPYLON,TYPEPYLON,TYPEMASSIF,ORDRE,PORTEE,PORTEEMOY,PUFOND,PUMONTAGE,PUDEROULAGE,POIDS,PUPoids,VBETON,PUBETON,CODES,IDCONTRAT) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
String xport = "Null", xportmoy = "Null", xpuf = "Null", xpum = "Null", xpud = "Null", xpoid = "Null", xpup = "Null", xvbp = "Null", xpuv = "Null";
if (dr.Cells["portee"].Value != null)
{
if (!(dr.Cells["portee"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["portee"].Value + "", out d1);
xport = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["porteemoy"].Value != null)
{
if (!(dr.Cells["porteemoy"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["porteemoy"].Value + "", out d1);
xportmoy = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUHTFondation"].Value != null)
{
if (!(dr.Cells["PUHTFondation"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUHTFondation"].Value + "", out d1);
xpuf = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUHTMontage"].Value != null)
{
if (!(dr.Cells["PUHTMontage"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUHTMontage"].Value + "", out d1);
xpum = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUHTDeroulage"].Value != null)
{
if (!(dr.Cells["PUHTDeroulage"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUHTDeroulage"].Value + "", out d1);
xpud = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["Poids"].Value != null)
{
if (!(dr.Cells["Poids"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["Poids"].Value + "", out d1);
xpoid = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUP"].Value != null)
{
if (!(dr.Cells["PUP"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUP"].Value + "", out d1);
xpup = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["VBP"].Value != null)
{
if (!(dr.Cells["VBP"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["VBP"].Value + "", out d1);
xvbp = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["PUV"].Value != null)
{
if (!(dr.Cells["PUV"].Value + "").Equals(""))
{
Double d1 = 0;
Double.TryParse(dr.Cells["PUV"].Value + "", out d1);
xpuv = "'" + d1.ToString().Replace(Program.sep, string.Empty) + "'";
}
}
if (dr.Cells["ID"].Value == null)
{
if (!dr.IsNewRow)
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
sqlF += "'" + dr.Cells["Npylon"].Value + "'"
+ ",'" + dr.Cells["TypePylone"].Value + "'"
+ ",'" + dr.Cells["TypeMassif"].Value + "'"
+ ",'" + (dr.Index + 1) + "'"
+ "," + xport + ""
+ "," + xportmoy + ""
+ "," + xpuf + ""
+ "," + xpum + ""
+ "," + xpud + ""
+ "," + xpoid + ""
+ "," + xpup + ""
+ "," + xvbp + ""
+ "," + xpuv + ""
+ ",'" + Program.Societe + "'"
+ "," + ID + ")";
frst = false;
}
}
else
{
String sqlu = "UPDATE lcontrat SET"
+ " NPYLON='" + dr.Cells["Npylon"].Value + "'"
+ ",ORDRE='" + (dr.Index + 1) + "'"
+ ",TYPEPYLON='" + dr.Cells["TypePylone"].Value + "'"
+ ",TYPEMASSIF='" + dr.Cells["TypeMassif"].Value + "'"
+ ", PORTEE=" + xport + ""
+ ", PORTEEMOY=" + xportmoy + ""
+ ",PUFOND=" + xpuf + ""
+ ",PUMONTAGE=" + xpum + ""
+ ",PUDEROULAGE=" + xpud + ""
+ ",POIDS=" + xpoid + ""
+ ",PUPoids=" + xpup + ""
+ ",VBETON=" + xvbp + ""
+ ",PUBETON=" + xpuv + ""
+ " WHERE ID = " + dr.Cells["ID"].Value;
met.Execute(sqlu);
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Enregistrement avec sucée.");
}
}
else
{
MessageBox.Show("Verifier Le Code Client.");
}
}
private void buttonX3_Click(object sender, EventArgs e)
{
// Bouton Supprimer Avoir
if (dataGridViewX1.Rows.Count != 0)
{
string sql = "select * from eentvc where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "'";
DataSet dss = met.recuperer_table(sql, "eentvc");
if (dss.Tables["eentvc"].Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
String req = "DELETE FROM eentvc Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "'";
if (met.Execute(req))
{
String sqlqte = "Select * From lentvc Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "'";
DataSet dstavr = met.recuperer_table(sqlqte);
if (dstavr != null)
{
if (dstavr.Tables.Count != 0)
{
foreach (DataRow dr1 in dstavr.Tables[0].Rows)
{
double xqte = 0, QTESTK = 0, QTESOR = 0, QTEMAG = 0, QTESORMAG = 0, QTESTE = 0, QTESORSTE = 0;
string xcodea = "";
xcodea = dr1.Field <string>("codea" + "");
string xcodeg = dr1.Field <string>("codeg" + "");
string xcoded = dr1.Field <string>("coded" + "");
try
{
xqte = dr1.Field <double>("qte");
}
catch { }
#region // Update stock
string sql1 = "Select qtestk,qteavrc from artdep where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and coded='" + xcoded + "' and codem='" + Program.Magasin + "' and codeg='" + xcodeg + "'";
DataSet ds1 = met.recuperer_table(sql1, "artdep");
if (ds1 != null)
{
if (ds1.Tables.Count != 0)
{
if (ds1.Tables["artdep"].Rows.Count != 0)
{
Double.TryParse(ds1.Tables["artdep"].Rows[0]["qtestk"] + "", out QTESTK);
Double.TryParse(ds1.Tables["artdep"].Rows[0]["qteavrc"] + "", out QTESOR);
}
}
}
string sql2 = "Select qtestk,qteavrc from artmag where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and codem='" + Program.Magasin + "'";
DataSet ds2 = met.recuperer_table(sql2, "artmag");
if (ds2 != null)
{
if (ds2.Tables.Count != 0)
{
if (ds2.Tables["artmag"].Rows.Count != 0)
{
Double.TryParse(ds2.Tables["artmag"].Rows[0]["qtestk"] + "", out QTEMAG);
Double.TryParse(ds2.Tables["artmag"].Rows[0]["qteavrc"] + "", out QTESORMAG);
}
}
}
string sql3 = "Select qtestk,qteavrc,vstk from article where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and code ='" + xcodea + "' ";
DataSet ds3 = met.recuperer_table(sql3, "article");
if (ds3 != null)
{
if (ds3.Tables.Count != 0)
{
if (ds3.Tables["article"].Rows.Count != 0)
{
xvstk = ds3.Tables["article"].Rows[0].Field <Boolean>("vstk");
Double.TryParse(ds3.Tables["article"].Rows[0]["qtestk"] + "", out QTESTE);
Double.TryParse(ds3.Tables["article"].Rows[0]["qteavrc"] + "", out QTESORSTE);
}
}
}
if (xvstk == true)
{
string sqlup = "UPDATE artdep set qtestk = '" + (QTESTK - xqte) + "',qteavrc='" + (QTESOR - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and coded='" + xcoded + "' and codem='" + Program.Magasin + "' and codeg='" + xcodeg + "'";
met.Execute(sqlup);
sqlup = "UPDATE artmag set qtestk = '" + (QTEMAG - xqte) + "',qteavrc='" + (QTESORMAG - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and codem='" + Program.Magasin + "'";
met.Execute(sqlup);
sqlup = "UPDATE article set qtestk = '" + (QTESTE - xqte) + "',qteavrc='" + (QTESORSTE - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and code ='" + xcodea + "' ";
met.Execute(sqlup);
}
#endregion // Update stock
#region Update Client
Decimal xdeb = 0, xven = 0;
String req5 = "Select code,avoir,solde From client where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["codec"].Value + "'";
DataSet dsc = met.recuperer_table(req5, "client");
decimal wdeb = 0, wsld = 0;
try
{
wdeb = dsc.Tables["client"].Rows[0].Field <decimal>("avoir");
}
catch { }
try
{
wsld = dsc.Tables["client"].Rows[0].Field <decimal>("solde");
}
catch { }
try
{
decimal.TryParse(dataGridViewX1.SelectedRows[0].Cells["TOTALTTC"].Value + "", out xdeb);
}
catch { }
String upcli = "UPDATE client SET avoir = " + (wdeb - xdeb)
+ ", solde=" + (wsld + xdeb)
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["codec"].Value + "'";
met.Execute(upcli);
/*String req6 = "Select code,vente,taux,com From rep where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["coder"].Value + "'";
* DataSet dsr = met.recuperer_table(req6, "rep");
* decimal wven = 0, nven = 0;
* double wcom = 0, wtaux = 0;
* try
* {
* wven = dsr.Tables["rep"].Rows[0].Field<decimal>("vente");
*
* }
* catch { }
* try
* {
* wtaux = dsr.Tables["rep"].Rows[0].Field<double>("taux");
*
* }
* catch { }
*
* try
* {
* decimal.TryParse(dataGridViewX1.SelectedRows[0].Cells["TOTALHT"].Value + "", out xven);
* }
* catch { }
*
* nven = wven + xven;
* wcom = (double)nven * (wtaux / 100);
* String uprep = "UPDATE rep SET vente = " + nven
+ ", com=" + wcom
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["coder"].Value + "'";
+ met.Execute(uprep);
*/
#endregion
}
}
}
String req1 = "DELETE FROM lentvc Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells[0].Value + "'";
if (met.Execute(req1))
{
MessageBox.Show("Suppression effectuée");
}
}
buttonX1_Click(sender, e);
}
}
}
}
private void buttonX2_Click(object sender, EventArgs e)
{
if (cli != null)
{
if (!modif)
{
string err = test_sauve();
if (err.Equals(""))
{
SaveIncrementationNumero();
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "INSERT INTO Facture (NUMF,REF,NREF,VREF,CODEC, NOMC,ADRC,DATEF, CODES,CODEE, THT,RAVANCE,RGARANTIE,NETHT, TVA,TIMBRE,TTC,RS50,RS15,NET,MODEP,type,VGARANTIE,VAVANCE,prorata) VALUE ("
+ "'" + met.CString(nfact.Text) + "',"
+ "'" + met.CString(tref.Text) + "',"
+ "'" + met.CString(tnref.Text) + "',"
+ "'" + met.CString(tvref.Text) + "',"
+ "'" + Tcodc.Text + "',"
+ "'" + Tnomc.Text + "',"
+ "'" + met.CString(Tadrc.Text) + "',"
+ "'" + dat + "',"
+ "'" + Program.Societe + "',"
+ "'" + Program.Exercice + "',"
+ "'" + Ttht.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tav.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tret.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tnetht.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Ttva.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Ttimbre.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tttc.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + rs50.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + rs15.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + net.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Modep.SelectedValue + "',"
+ "'T',"
+ "'" + Tpret.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + tpav.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + tprorata.Text.Replace(Program.sep, string.Empty) + "'"
+ ")";
met.Execute(sql);
//Montage
String sqlF = "insert INTO ltravaux (DESIGNATION,QTE,U,PUHT,PTHT,TVA,CODES,CODEE,NUMF) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
if (!dr.IsNewRow)
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
sqlF += "'" + met.CString(dr.Cells["Npylon"].Value) + "'"
+ ",'" + (dr.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + met.CString(dr.Cells["U"].Value) + "'"
+ ",'" + (dr.Cells["PUHTF"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PTHT"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["TVAFF"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + Program.Societe + "'"
+ ",'" + Program.Exercice + "'"
+ ",'" + nfact.Text + "')";
frst = false;
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Sauvgard avec sucée.");
buttonX2.Visible = false;
buttonX3.Visible = true;
}
else
{
MessageBox.Show(err);
}
}
else
{
string err = test_sauve();
if (err.Equals(""))
{
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "UPDATE Facture SET REF='" + met.CString(tref.Text)
+ "',NREF='" + met.CString(tnref.Text)
+ "',VREF='" + met.CString(tvref.Text)
+ "',CODEC='" + met.CString(Tcodc.Text)
+ "', NOMC='" + met.CString(Tnomc.Text)
+ "',ADRC='" + met.CString(Tadrc.Text)
+ "',DATEF='" + dat
+ "', CODES='" + Program.Societe
+ "', CODEE='" + Program.Exercice
+ "', THT='" + Ttht.Text.Replace(Program.sep, string.Empty)
+ "',RAVANCE='" + Tav.Text.Replace(Program.sep, string.Empty)
+ "',RGARANTIE='" + Tret.Text.Replace(Program.sep, string.Empty)
+ "',NETHT='" + Tnetht.Text.Replace(Program.sep, string.Empty)
+ "', TVA='" + Ttva.Text.Replace(Program.sep, string.Empty)
+ "',TIMBRE='" + Ttimbre.Text.Replace(Program.sep, string.Empty)
+ "',TTC='" + Tttc.Text.Replace(Program.sep, string.Empty)
+ "'RS50='" + rs50.Text.Replace(Program.sep, string.Empty) + "',"
+ "'RS15='" + rs15.Text.Replace(Program.sep, string.Empty) + "',"
+ "'NET='" + net.Text.Replace(Program.sep, string.Empty) + "',"
+ "',MODEP='" + Modep.SelectedValue
+ "',type='T',VGARANTIE='" + Tpret.Text.Replace(Program.sep, string.Empty)
+ "',VAVANCE='" + tpav.Text.Replace(Program.sep, string.Empty)
+ "',prorata='" + tprorata.Text.Replace(Program.sep, string.Empty)
+ "'WHERE codes='" + Program.Societe
+ "' and numf = '" + NumFact + "'";
met.Execute(sql);
//Fondation
met.Execute("DELETE FROM ltravaux WHERE codes='" + Program.Societe + "' and codee='" + Program.Exercice + "' and numf='" + NumFact + "'");
String sqlF = "INSERT INTO ltravaux (DESIGNATION,QTE,U,PUHT,PTHT,TVA,CODES,CODEE,NUMF) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
if (!dr.IsNewRow)
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
sqlF += "'" + met.CString(dr.Cells["Npylon"].Value) + "'"
+ ",'" + (dr.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + met.CString(dr.Cells["U"].Value) + "'"
+ ",'" + (dr.Cells["PUHTF"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PTHT"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["TVAFF"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + Program.Societe + "'"
+ ",'" + Program.Exercice + "'"
+ ",'" + nfact.Text + "')";
frst = false;
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Sauvgard avec sucée.");
buttonX2.Visible = false;
buttonX3.Visible = true;
}
else
{
MessageBox.Show(err);
}
}
}
else
{
MessageBox.Show("Verifier Le Code Client.");
}
}
private void buttonX2_Click(object sender, EventArgs e)
{
if (cli != null)
{
if (!modif)
{
string err = test_sauve();
if (err.Equals(""))
{
SaveIncrementationNumero();
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "INSERT INTO Facture (NUMF,REF,NREF,VREF,CODEC, NOMC,ADRC,DATEF, CODES,CODEE, THT,RAVANCE,RGARANTIE,NETHT, TVA,TIMBRE,TTC,RS50,RS15,NET,MODEP,type,VGARANTIE,VAVANCE,prorata,Contrat) VALUE ("
+ "'" + nfact.Text + "',"
+ "'" + met.CString(tref.Text) + "',"
+ "'" + met.CString(tnref.Text) + "',"
+ "'" + met.CString(tvref.Text) + "',"
+ "'" + Tcodc.Text + "',"
+ "'" + met.CString(Tnomc.Text) + "',"
+ "'" + met.CString(Tadrc.Text) + "',"
+ "'" + dat + "',"
+ "'" + Program.Societe + "',"
+ "'" + Program.Exercice + "',"
+ "'" + Ttht.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tav.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tret.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tnetht.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Ttva.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Ttimbre.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tttc.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + rs50.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + rs15.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + net.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Modep.SelectedValue + "',"
+ "'MD',"
+ "'" + Tpret.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + tpav.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + tprorata.Text.Replace(Program.sep, string.Empty) + "',"
+ "" + IDContrat + ""
+ ")";
met.Execute(sql);
//Montage
String sqlF = "insert INTO lmontage (NPYLONE,TYPEPYLONE,POIDS,U,PUHT,PTHT,TVA,CODES,CODEE,NUMF,IDLC) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
if (!dr.IsNewRow)
{
if (!dr.Cells["ID"].Value.Equals(-1))
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
sqlF += "'" + met.CString(dr.Cells["Npylon"].Value) + "'"
+ ",'" + met.CString(dr.Cells["TypePylone"].Value) + "'"
+ ",'" + (dr.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + met.CString(dr.Cells["U"].Value) + "'"
+ ",'" + (dr.Cells["PUHTF"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PTHT"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["TVAFF"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + Program.Societe + "'"
+ ",'" + Program.Exercice + "'"
+ ",'" + nfact.Text + "'"
+ ",'" + dr.Cells["ID"].Value + "')";
MAJProrat(dr.Cells["ID"].Value + "");
frst = false;
}
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Sauvgard avec sucée.");
buttonX2.Visible = false;
buttonX3.Visible = true;
}
else
{
MessageBox.Show(err);
}
}
}
else
{
MessageBox.Show("Verifier Le Code Client.");
}
}
private void buttonX3_Click(object sender, EventArgs e)
{
// Bouton Supprimer Facture
if (dataGridViewX1.Rows.Count != 0)
{
if (dataGridViewX1.SelectedRows[0].Cells["mode"].Value + "" == "D")
{
string sql = "select * from eentcs where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "' and regle = false";
DataSet dss = met.recuperer_table(sql, "eentcs");
if (dss.Tables["eentcs"].Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
string req1 = "Select * from lentcs where codes='" + Program.Societe + "' and codee='" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "' ";
DataSet tmp1 = met.recuperer_table(req1, "lentcs");
foreach (DataRow dr1 in tmp1.Tables["lentcs"].Rows)
{
double xqte = 0, QTESTK = 0, QTESOR = 0, QTEMAG = 0, QTESORMAG = 0, QTESTE = 0, QTESORSTE = 0;
string xcodea = "";
xcodea = dr1.Field <string>("codea" + "");
string xcodeg = dr1.Field <string>("codeg" + "");
string xcoded = dr1.Field <string>("coded" + "");
xqte = dr1.Field <double>("qte");
try
{
xqte = dr1.Field <double>("qte");
}
catch { }
#region // Update stock
string sql1 = "Select qtestk,qtesor from artdep where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and coded='" + xcoded + "' and codem='" + Program.Magasin + "' and codeg='" + xcodeg + "'";
DataSet ds1 = met.recuperer_table(sql1, "artdep");
if (ds1 != null)
{
if (ds1.Tables.Count != 0)
{
if (ds1.Tables["artdep"].Rows.Count != 0)
{
Double.TryParse(ds1.Tables["artdep"].Rows[0]["qtestk"] + "", out QTESTK);
Double.TryParse(ds1.Tables["artdep"].Rows[0]["qtesor"] + "", out QTESOR);
}
}
}
string sql2 = "Select qtestk,qtesor from artmag where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and codem='" + Program.Magasin + "'";
DataSet ds2 = met.recuperer_table(sql2, "artmag");
if (ds2 != null)
{
if (ds2.Tables.Count != 0)
{
if (ds2.Tables["artmag"].Rows.Count != 0)
{
Double.TryParse(ds2.Tables["artmag"].Rows[0]["qtestk"] + "", out QTEMAG);
Double.TryParse(ds2.Tables["artmag"].Rows[0]["qtesor"] + "", out QTESORMAG);
}
}
}
string sql3 = "Select qtestk,qtesor,vstk from article where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and code ='" + xcodea + "' ";
DataSet ds3 = met.recuperer_table(sql3, "article");
if (ds3 != null)
{
if (ds3.Tables.Count != 0)
{
if (ds3.Tables["article"].Rows.Count != 0)
{
xvstk = ds3.Tables["article"].Rows[0].Field <Boolean>("vstk");
Double.TryParse(ds3.Tables["article"].Rows[0]["qtestk"] + "", out QTESTE);
Double.TryParse(ds3.Tables["article"].Rows[0]["qtesor"] + "", out QTESORSTE);
}
}
}
if (xvstk == true)
{
string sqlup = "UPDATE artdep set qtestk = '" + (QTESTK + xqte) + "',qtesor='" + (QTESOR - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and coded='" + xcoded + "' and codem='" + Program.Magasin + "' and codeg='" + xcodeg + "'";
met.Execute(sqlup);
sqlup = "UPDATE artmag set qtestk = '" + (QTEMAG + xqte) + "',qtesor='" + (QTESORMAG - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codea ='" + xcodea + "' and codem='" + Program.Magasin + "'";
met.Execute(sqlup);
sqlup = "UPDATE article set qtestk = '" + (QTESTE + xqte) + "',qtesor='" + (QTESORSTE - xqte) + "' where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and code ='" + xcodea + "' ";
met.Execute(sqlup);
}
#endregion // Update stock
}
String req5 = "Select code,debit,solde From client where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["codec"].Value + "'";
DataSet dsc = met.recuperer_table(req5, "client");
decimal wdeb = 0, wsld = 0;
try
{
wdeb = dsc.Tables["client"].Rows[0].Field <decimal>("debit");
}
catch { }
try
{
wsld = dsc.Tables["client"].Rows[0].Field <decimal>("solde");
}
catch { }
try
{
decimal.TryParse(dataGridViewX1.SelectedRows[0].Cells["TOTALTTC"].Value + "", out xdeb);
}
catch { }
String upcli = "UPDATE client SET debit = " + (wdeb - xdeb)
+ ", solde=" + (wsld - xdeb)
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["codec"].Value + "'";
met.Execute(upcli);
String req6 = "Select code,vente,taux,com From rep where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["coder"].Value + "'";
DataSet dsr = met.recuperer_table(req6, "rep");
decimal wven = 0, nven = 0;
double wcom = 0, wtaux = 0;
try
{
wven = dsr.Tables["rep"].Rows[0].Field <decimal>("vente");
}
catch { }
try
{
wtaux = dsr.Tables["rep"].Rows[0].Field <double>("taux");
}
catch { }
try
{
decimal.TryParse(dataGridViewX1.SelectedRows[0].Cells["TOTALHT"].Value + "", out xven);
}
catch { }
nven = wven - xven;
wcom = (double)nven * (wtaux / 100);
String uprep = "UPDATE rep SET vente = " + nven
+ ", com=" + wcom
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND Code = '" + dataGridViewX1.SelectedRows[0].Cells["coder"].Value + "'";
met.Execute(uprep);
String req3 = "DELETE FROM eentcs Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
if (met.Execute(req3))
{
String req4 = "DELETE FROM lentcs Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
if (met.Execute(req4))
{
MessageBox.Show("Suppression effectuée");
}
}
buttonX1_Click(sender, e);
}
}
else
{
MessageBox.Show("Facture Reglée");
}
}
else if (dataGridViewX1.SelectedRows[0].Cells["mode"].Value + "" == "L")
{
string sql = "select * from eentcs where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "' and regle = false";
DataSet dss = met.recuperer_table(sql, "eentcs");
if (dss.Tables["eentcs"].Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
string sqln = "select * from nblfacture where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and numf = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
DataSet dsn = met.recuperer_table(sqln, "nblfacture");
if (dsn != null)
{
foreach (DataRow dr in dsn.Tables["nblfacture"].Rows)
{
string xnbl = dr.Field <string>("numl");
String upbl = "UPDATE eentl SET facture = " + false
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND codem ='" + Program.Magasin + "' AND num = '" + xnbl + "'";
met.Execute(upbl);
}
}
String req3 = "DELETE FROM eentcs Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
met.Execute(req3);
String req4 = "DELETE FROM lentcs Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
met.Execute(req4);
String req5 = "DELETE FROM nblfacture Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and numf = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
met.Execute(req5);
MessageBox.Show("Suppression effectuée");
buttonX1_Click(sender, e);
}
}
else
{
MessageBox.Show("Facture Reglée");
}
}
else if (dataGridViewX1.SelectedRows[0].Cells["mode"].Value + "" == "B")
{
string sql = "select * from eentcs where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
DataSet dss = met.recuperer_table(sql, "eentcs");
if (dss.Tables["eentcs"].Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
string sqln = "select * from nblfacture where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and numf = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
DataSet dsn = met.recuperer_table(sqln, "nblfacture");
if (dsn != null)
{
foreach (DataRow dr in dsn.Tables["nblfacture"].Rows)
{
string xnbl = dr.Field <string>("numl");
String upbl = "UPDATE eentcb SET facture = " + false
+ " where codes = '" + Program.Societe + "' AND codee ='" + Program.Exercice + "' AND codem ='" + Program.Magasin + "' AND num = '" + xnbl + "'";
met.Execute(upbl);
}
}
String req3 = "DELETE FROM eentcs Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
met.Execute(req3);
String req4 = "DELETE FROM lentcs Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and num = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
met.Execute(req4);
String req5 = "DELETE FROM nblfacture Where codes = '" + Program.Societe + "' and codee = '" + Program.Exercice + "' and codem='" + Program.Magasin + "' and numf = '" + dataGridViewX1.SelectedRows[0].Cells["num"].Value + "'";
met.Execute(req5);
MessageBox.Show("Suppression effectuée");
buttonX1_Click(sender, e);
}
}
}
}
}
private void buttonX2_Click(object sender, EventArgs e)
{
if (cli != null)
{
if (!modif)
{
string err = "";
if (err.Equals(""))
{
SaveIncrementationNumero();
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "INSERT INTO Facture (NUMF,REF,NREF,VREF,CODEC, NOMC,ADRC,DATEF, CODES,CODEE, THT,RAVANCE,RGARANTIE,NETHT, TVA,TIMBRE,TTC,RS50,RS15,NET,MODEP,type,VGARANTIE,VAVANCE,prorata,Contrat) VALUE ("
+ "'" + nfact.Text + "',"
+ "'" + met.CString(tref.Text) + "',"
+ "'" + met.CString(tnref.Text) + "',"
+ "'" + met.CString(tvref.Text) + "',"
+ "'" + Tcodc.Text + "',"
+ "'" + met.CString(Tnomc.Text) + "',"
+ "'" + met.CString(Tadrc.Text) + "',"
+ "'" + dat + "',"
+ "'" + Program.Societe + "',"
+ "'" + Program.Exercice + "',"
+ "'" + Ttht.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tav.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tret.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tnetht.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Ttva.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Ttimbre.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Tttc.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + rs50.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + rs15.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + net.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + Modep.SelectedValue + "',"
+ "'DD',"
+ "'" + Tpret.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + tpav.Text.Replace(Program.sep, string.Empty) + "',"
+ "'" + tprorata.Text.Replace(Program.sep, string.Empty) + "',"
+ "" + IDContrat + ""
+ ")";
met.Execute(sql);
//Montage
String sqlF = "insert INTO lderoulage (NPYLONE,TYPEPYLONE,QTE,U,PUHT,PTHT,TVA,CODES,CODEE,NUMF,IDLC,IDLC1) values";
Boolean frst = true;
int lasord = 0;
string lastid = "";
for (int cpt = 0; cpt < mygrid1.Rows.Count - 1; cpt += 2)
{
DataGridViewRow dr = mygrid1.Rows[cpt];
DataGridViewRow dr1 = mygrid1.Rows[cpt + 1];
if (!dr.IsNewRow)
{
if (!dr.Cells["ID"].Value.Equals(-1))
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
string xqte = "0", xpu = "0", xptht = "0", xtva = "0", xid1 = "0";
if (cpt != mygrid1.Rows.Count - 2)
{
xqte = (dr1.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty);
xpu = (dr1.Cells["PUHTF"].Value + "").Replace(Program.sep, string.Empty);
xptht = (dr1.Cells["PTHT"].Value + "").Replace(Program.sep, string.Empty);
xtva = (dr1.Cells["TVAFF"].Value + "").Replace(Program.sep, string.Empty);
xid1 = dr1.Cells["ID"].Value + "";
MAJProrat(dr.Cells["ID"].Value + "");
int.TryParse(dr1.Cells["ordre"].Value + "", out lasord);
}
else
{
}
lastid = dr1.Cells["ID"].Value + "";
sqlF += "'" + met.CString(dr.Cells["Npylon"].Value) + "'"
+ ",'" + met.CString(dr.Cells["TypePylone"].Value) + "'"
+ ",'" + xqte + "'"
+ ",'" + met.CString(dr1.Cells["U"].Value) + "'"
+ ",'" + xpu + "'"
+ ",'" + xptht + "'"
+ ",'" + xtva + "'"
+ ",'" + Program.Societe + "'"
+ ",'" + Program.Exercice + "'"
+ ",'" + nfact.Text + "'"
+ ",'" + dr.Cells["ID"].Value + "','" + xid1 + "')";
frst = false;
}
}
}
lastid = mygrid1.Rows[mygrid1.Rows.Count - 2].Cells["ID"].Value + "";
string sqllastid = "SELECT MAX(ordre) FROM lcontrat WHERE IDCONTRAT = " + IDContrat;
DataSet dsid = met.recuperer_table(sqllastid);
try {
int idtest = 0;
int.TryParse(dsid.Tables[0].Rows[0][0] + "", out idtest);
if (lasord == idtest)
{
MAJProrat(lastid + "");
}
}
catch { }
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Sauvgard avec sucée.");
buttonX2.Visible = false;
buttonX3.Visible = true;
}
else
{
MessageBox.Show(err);
}
}
}
else
{
MessageBox.Show("Verifier Le Code Client.");
}
}
private void buttonX3_Click(object sender, EventArgs e)
{
// Bouton Supprimer Facture
if (dataGridViewX1.Rows.Count != 0)
{
String msg = MessageBox.Show("Ete-vous sur", "Important", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
if (msg.Equals("Yes"))
{
string sqlupdcontrat = "";
String del1 = "delete from facture where codes='" + Program.Societe + "' and numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "'";
string table = "";
if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("F") || dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("FD"))
{
table = "Lfondation";
sqlupdcontrat = "update lcontrat join lfondation on (lcontrat.ID = lfondation.IDLC) join facture on (facture.numf = lfondation.numf and facture.codes = lfondation.codes) set lcontrat.prorataf=lcontrat.prorataf-facture.prorata where facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "' and facture.codes='" + Program.Societe + "'";
//sqlupdcontrat = "update lcontrat set prorataf=0 where ID in(select IDLC from lfondation where numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "')";
}
else if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("M") || dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("MD"))
{
table = "lmontage";
sqlupdcontrat = "update lcontrat join lmontage on (lcontrat.ID = lmontage.IDLC) join facture on (facture.numf = lmontage.numf and facture.codes = lmontage.codes) set lcontrat.proratam=lcontrat.proratam-facture.prorata where facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "' and facture.codes='" + Program.Societe + "'";
//sqlupdcontrat = "update lcontrat set proratam=0 where ID in(select IDLC from lmontage where numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "')";
}
else if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("DD") || dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("D"))
{
table = "lderoulage";
sqlupdcontrat = "update lcontrat join lderoulage on (lcontrat.ID = lderoulage.IDLC) join facture on (facture.numf = lderoulage.numf and facture.codes = lderoulage.codes) set lcontrat.proratad=if(lcontrat.proratad >0, lcontrat.proratad-facture.prorata,0) where facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "' and facture.codes='" + Program.Societe + "'";
//sqlupdcontrat = "update lcontrat set proratad=0 where ID in(select IDLC from lderoulage where numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "')";
string sqll = "select r1.max1,r2.max2,r1.prorata from (select max(lcontrat.id) AS max1,facture.prorata AS prorata from lcontrat join lderoulage on (lcontrat.ID = lderoulage.IDLC) join facture on (facture.numf = lderoulage.numf) where ( facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "') and facture.codes='" + Program.Societe + "') r1,(select max(id) as max2 from lcontrat WHERE lcontrat.idcontrat = (select distinct(lcontrat.idcontrat) AS ID1 from lcontrat join lderoulage on (lcontrat.ID = lderoulage.IDLC) join facture on (facture.numf = lderoulage.numf) where ( facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "' ) and facture.codes='" + Program.Societe + "')) r2;";
DataSet dsll = met.recuperer_table(sqll);
int max1 = 0, max2 = 0; Decimal prorat = 0;
try {
int.TryParse(dsll.Tables[0].Rows[0]["max1"] + "", out max1);
int.TryParse(dsll.Tables[0].Rows[0]["max2"] + "", out max2);
Decimal.TryParse(dsll.Tables[0].Rows[0]["prorata"] + "", out prorat);
if (max2 == max1 + 1)
{
sqll = "update lcontrat set proratad = proratad - " + prorat + " WHERE ID = " + max2;
met.Execute(sqll);
}
}
catch { }
}
else if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("AV") || dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("GR") || dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("T"))
{
table = "ltravaux";
if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("AV"))
{
sqlupdcontrat = "update CONTRAT join facture on (facture.numf = CONTRAT.NUMFAvance and facture.codes = CONTRAT.codes) set CONTRAT.NUMFAvance='',CONTRAT.Favance=0 where facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "' and facture.codes='" + Program.Societe + "' ";
}
if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("GR"))
{
sqlupdcontrat = "update CONTRAT join facture on (facture.numf = CONTRAT.NUMFgarantie and facture.codes = CONTRAT.codes) set CONTRAT.NUMFgarantie='',CONTRAT.Fgarantie=0 where facture.numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "' and facture.codes='" + Program.Societe + "' ";
}
}
else if (dataGridViewX1.CurrentRow.Cells["Mode"].Value.Equals("TD"))
{
table = "ldivers";
}
String del2 = "delete from " + table + " where codes='" + Program.Societe + "' and numf = '" + dataGridViewX1.CurrentRow.Cells["NuM"].Value + "'";
try
{
met.Execute(sqlupdcontrat);
}
catch { }
met.Execute(del2);
met.Execute(del1);
buttonX1_Click(sender, e);
}
}
}
private void buttonX2_Click(object sender, EventArgs e)
{
if (cli != null)
{
if (!modif)
{
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "INSERT INTO contrat (CODE,CODEC, NOMC,ADRC,DATEDEBUT,VGAR,VAVANC, CODES) VALUE ("//, THT, TVA,TTC, AVANCE,solde,reste
+ "'" + nfact.Text + "',"
+ "'" + Tcodc.Text + "',"
+ "'" + Tnomc.Text + "',"
+ "'" + Tadrc.Text + "',"
+ "'" + dat + "',"
+ "'" + Tgar.Text + "',"
+ "'" + Tavnc.Text + "',"
+ "'" + Program.Societe + "'"
+ ")";
String sqlID = "Select Max(ID) FROM contrat";
met.Execute(sql);
DataSet dsID = met.recuperer_table(sqlID);
String ID = string.Empty;
try
{
ID = dsID.Tables[0].Rows[0].ItemArray[0] + "";
}
catch { }
//Pylones
String sqlF = "INSERT INTO lcontrat (NPYLON,TYPEPYLON,TYPEMASSIF,POIDS,PUPoids,VBETON,PUBETON,DISTANCE,PUDISTANCE,CODES,IDCONTRAT) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
if (!dr.IsNewRow)
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
sqlF += "'" + dr.Cells["Npylon"].Value + "'"
+ ",'" + dr.Cells["TypePylone"].Value + "'"
+ ",'" + dr.Cells["TypeMassif"].Value + "'"
+ ",'" + (dr.Cells["Poids"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PUP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PUV"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["distance"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PUD"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + Program.Societe + "'"
+ "," + ID + ")";
frst = false;
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Enregistrement avec sucée.");
}
else
{
String dat = datefact.Value.ToString("yyyy-MM-dd");
string sql = "UPDATE contrat SET " // (,, ,,, , THT, TVA,TTC, AVANCE,solde,reste) VALUE ("
+ "CODE='" + nfact.Text + "',"
+ "CODEC='" + Tcodc.Text + "',"
+ "NOMC='" + Tnomc.Text + "',"
+ "ADRC='" + Tadrc.Text + "',"
+ "VGAR='" + Tgar.Text + "',"
+ "VAVANC='" + Tavnc.Text + "',"
+ "DATEDEBUT='" + dat + "',"
+ "CODES='" + Program.Societe + "'"
+ "WHERE ID = " + IDContrat;
met.Execute(sql);
String ID = IDContrat;
//Pylones
foreach (string IDSUP in Lsupp)
{
String sqldel = "DELETE FROM lcontrat WHERE ID = " + IDSUP;
met.Execute(sqldel);
}
String sqlF = "INSERT INTO lcontrat (NPYLON,TYPEPYLON,TYPEMASSIF,POIDS,PUPoids,VBETON,PUBETON,DISTANCE,PUDISTANCE,CODES,IDCONTRAT) values";
Boolean frst = true;
foreach (DataGridViewRow dr in mygrid1.Rows)
{
if (dr.Cells["ID"].Value == null)
{
if (!dr.IsNewRow)
{
if (!frst)
{
sqlF += ",(";
}
else
{
sqlF += "(";
}
sqlF += "'" + dr.Cells["Npylon"].Value + "'"
+ ",'" + dr.Cells["TypePylone"].Value + "'"
+ ",'" + dr.Cells["TypeMassif"].Value + "'"
+ ",'" + (dr.Cells["Poids"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PUP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PUV"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["distance"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + (dr.Cells["PUD"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",'" + Program.Societe + "'"
+ "," + ID + ")";
frst = false;
}
}
else
{
String sqlu = "UPDATE lcontrat SET"
+ " NPYLON='" + dr.Cells["Npylon"].Value + "'"
+ ",TYPEPYLON='" + dr.Cells["TypePylone"].Value + "'"
+ ",TYPEMASSIF='" + dr.Cells["TypeMassif"].Value + "'"
+ ",POIDS='" + (dr.Cells["Poids"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",PUPoids='" + (dr.Cells["PUP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",VBETON='" + (dr.Cells["VBP"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",PUBETON='" + (dr.Cells["PUV"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",DISTANCE='" + (dr.Cells["distance"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ ",PUDISTANCE='" + (dr.Cells["PUD"].Value + "").Replace(Program.sep, string.Empty) + "'"
+ " WHERE ID = " + dr.Cells["ID"].Value;
met.Execute(sqlu);
}
}
if (!frst)
{
met.Execute(sqlF);
}
MessageBox.Show("Enregistrement avec sucée.");
}
}
else
{
MessageBox.Show("Verifier Le Code Client.");
}
}
