public void modif_pobox(CpoBox pb, string old_nfacture, string old_fourni, string user)
{
string sql = "";
//@dt_recp,@direc,@emet,@desti,@fourn,@dt_fac,@ttc,@dev,@objfac,@nfac,@ncom,@dt_env_fed,@restit,@env_dfi,@remar,@pole,@dt_env_dfi
if (check_for_double(pb.fournisseur, pb.Nfacture) == true)
{
sql = "update Tpo_box set date_reception=@dt_recp,direction=@direc,emetteur=@emet,destinataire=@desti,fournisseur=@fourn,date_facture=@dt_fac,montant_ttc=@ttc,devise=@dev,objet_facture=@objfac,Nfacture=@nfac,Ncommande=@ncom ,date_envoi_fedaso=@dt_env_fed,restitution_fedosa=@restit,envoie_dfi=@env_dfi,remarque=@remar,pole=@pole,date_envoi_dfi=@dt_env_dfi where Nfacture= @old_nfact and fournisseur = @old_fourni ";
cnn.Open();
commande = new SqlCommand(sql, cnn);
//set the pb
commande.Parameters.AddWithValue("@dt_recp", pb.date_reception);
commande.Parameters.AddWithValue("@direc", pb.direction);
commande.Parameters.AddWithValue("@emet", pb.emetteur);
commande.Parameters.AddWithValue("@desti", pb.destinataire);
commande.Parameters.AddWithValue("@fourn", pb.fournisseur);
commande.Parameters.AddWithValue("@dt_fac", pb.date_facture);
commande.Parameters.AddWithValue("@ttc", pb.montant);
commande.Parameters.AddWithValue("@dev", pb.devise);
commande.Parameters.AddWithValue("@objfac", pb.objet_facture);
commande.Parameters.AddWithValue("@nfac", pb.Nfacture);
commande.Parameters.AddWithValue("@ncom", pb.Ncommande);
commande.Parameters.AddWithValue("@dt_env_fed", pb.date_envoi_fedosa);
commande.Parameters.AddWithValue("@restit", pb.restitution_fedosa);
commande.Parameters.AddWithValue("@env_dfi", pb.envoi_dfi);
commande.Parameters.AddWithValue("@remar", pb.remarque);
commande.Parameters.AddWithValue("@pole", pb.pole);
commande.Parameters.AddWithValue("@dt_env_dfi", pb.date_envoi_dfi);
//old values
commande.Parameters.AddWithValue("@old_nfact", old_nfacture);
commande.Parameters.AddWithValue("@old_fourni", old_fourni);
commande.ExecuteNonQuery();
commande.Dispose();
cnn.Close();
archiv(user, 0, pb.fournisseur, pb.Nfacture);//archiv
MessageBox.Show("updated");
}
else
{
MessageBox.Show("facture n existe pas ", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool ajout_pobox(CpoBox pb, string user)
{//(date_reception,direction,emetteur,destinataire,fournisseur,date_facture,montant_ttc,devise,objet_facture,Nfacture,Ncommande,date_envoi_fedaso,restitution_fedosa,envoie_dfi,remarque,pole,date_envoi_dfi )
string sql = "";
if (check_for_double(pb.fournisseur, pb.Nfacture) == false)
{
// sql = "insert into Tpo_box values('" +pb.date_reception + "','" + pb.date_reception + "','" + pb.emetteur + "','" + pb.destinataire + "','" + pb.fournisseur + "','" + pb.date_facture + "','" + pb.montant + "','" + pb.devise + "','" + pb.objet_facture + "','" + pb.Nfacture + "','" + pb.Ncommande + "','" + pb.date_envoi_fedosa + "','" + pb.restitution_fedosa + "','" + pb.envoi_dfi + "','" + pb.remarque + "','" + pb.pole + "','" + pb.date_envoi_dfi + "')";
sql = "insert into Tpo_box (date_reception,direction,emetteur,destinataire,fournisseur,date_facture,montant_ttc,devise,objet_facture,Nfacture,Ncommande,date_envoi_fedaso,restitution_fedosa,envoie_dfi,remarque,pole,date_envoi_dfi) values (@dt_recp,@direc,@emet,@desti,@fourn,@dt_fac,@ttc,@dev,@objfac,@nfac,@ncom,@dt_env_fed,@restit,@env_dfi,@remar,@pole,@dt_env_dfi)";
cnn.Open();
commande = new SqlCommand(sql, cnn);
//@dt_recp,@direc,@emet,@desti,@fourn,@dt_fac,@ttc,@dev,@objfac,@nfac,@ncom,@dt_env_fed,@restit,@env_dfi,@remar,@pole,@dt_env_dfi
commande.Parameters.AddWithValue("@dt_recp", pb.date_reception);
commande.Parameters.AddWithValue("@direc", pb.direction);
commande.Parameters.AddWithValue("@emet", pb.emetteur);
commande.Parameters.AddWithValue("@desti", pb.destinataire);
commande.Parameters.AddWithValue("@fourn", pb.fournisseur);
commande.Parameters.AddWithValue("@dt_fac", pb.date_facture);
commande.Parameters.AddWithValue("@ttc", pb.montant);
commande.Parameters.AddWithValue("@dev", pb.devise);
commande.Parameters.AddWithValue("@objfac", pb.objet_facture);
commande.Parameters.AddWithValue("@nfac", pb.Nfacture);
commande.Parameters.AddWithValue("@ncom", pb.Ncommande);
commande.Parameters.AddWithValue("@dt_env_fed", pb.date_envoi_fedosa);
commande.Parameters.AddWithValue("@restit", pb.restitution_fedosa);
commande.Parameters.AddWithValue("@env_dfi", pb.envoi_dfi);
commande.Parameters.AddWithValue("@remar", pb.remarque);
commande.Parameters.AddWithValue("@pole", pb.pole);
commande.Parameters.AddWithValue("@dt_env_dfi", pb.date_envoi_dfi);
commande.ExecuteNonQuery();
/*
* adapter = new SqlDataAdapter();
* adapter.InsertCommand = new SqlCommand(sql, cnn);
* adapter.InsertCommand.ExecuteNonQuery();*/
commande.Dispose();
cnn.Close();
archiv(user, 1, pb.fournisseur, pb.Nfacture);//archiv
return(true);
}
else
{
MessageBox.Show("facture déjà existant ", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
public List <CpoBox> get_pobox_by_nfact_or_fourniss(string nfacture, string fournisseur)
{
List <CpoBox> llpb = new List <CpoBox>();//declaration de la list pour stockage des pobox,puis return de liste
string sql = "";
if (nfacture == "" && fournisseur != "")
{
sql = "select * from Tpo_box where fournisseur ='" + fournisseur + "' ";
} //sinfacture est vide
if (fournisseur == "" && nfacture != "")
{
sql = "select * from Tpo_box where Nfacture='" + nfacture + "' ";
} //si fournisseur est vide
if (fournisseur != "" && nfacture != "")
{
sql = "select * from Tpo_box where fournisseur ='" + fournisseur + "' and Nfacture='" + nfacture + "' "; /*recherche par nfacture ou fournisseur*/
}
cnn.Open();
commande = new SqlCommand(sql, cnn);
//string bb="";
dr = commande.ExecuteReader();
while (dr.Read())
{
//int i = 0;
CpoBox pb = new CpoBox();
//stock what we got from database to the class pobox
long l = Convert.ToInt64(dr.GetValue(10).ToString()); //get as string then turn it to long
double mt = Convert.ToDouble(dr.GetValue(6).ToString()); //get as string then turn it to double
pb.SetPobox(dr.GetValue(0).ToString(), dr.GetValue(1).ToString(), dr.GetValue(2).ToString(), dr.GetValue(3).ToString(), dr.GetValue(4).ToString(), dr.GetValue(5).ToString(), mt, dr.GetValue(7).ToString(), dr.GetValue(8).ToString(), dr.GetValue(9).ToString(), l, dr.GetValue(11).ToString(), dr.GetValue(12).ToString(), dr.GetValue(13).ToString(), dr.GetValue(14).ToString(), dr.GetValue(15).ToString(), dr.GetValue(16).ToString());
llpb.Add(pb);//add the pobox to the list
//bb += llpb[i].direction.ToString() + "#";
}
//MessageBox.Show(output);
cnn.Close();
//MessageBox.Show(bb);
return(llpb);//return the list
}
public void ajout_pobox(CpoBox pb)
{//(date_reception,direction,emetteur,destinataire,fournisseur,date_facture,montant_ttc,devise,objet_facture,Nfacture,Ncommande,date_envoi_fedaso,restitution_fedosa,envoie_dfi,remarque,pole,date_envoi_dfi )
string sql = "";
// sql = "insert into Tpo_box values('" +pb.date_reception + "','" + pb.date_reception + "','" + pb.emetteur + "','" + pb.destinataire + "','" + pb.fournisseur + "','" + pb.date_facture + "','" + pb.montant + "','" + pb.devise + "','" + pb.objet_facture + "','" + pb.Nfacture + "','" + pb.Ncommande + "','" + pb.date_envoi_fedosa + "','" + pb.restitution_fedosa + "','" + pb.envoi_dfi + "','" + pb.remarque + "','" + pb.pole + "','" + pb.date_envoi_dfi + "')";
sql = "insert into Tpo_box (date_reception,direction,emetteur,destinataire,fournisseur,date_facture,montant_ttc,devise,objet_facture,Nfacture,Ncommande,date_envoi_fedaso,restitution_fedosa,envoie_dfi,remarque,pole,date_envoi_dfi) values (@dt_recp,@direc,@emet,@desti,@fourn,@dt_fac,@ttc,@dev,@objfac,@nfac,@ncom,@dt_env_fed,@restit,@env_dfi,@remar,@pole,@dt_env_dfi)";
cnn.Open();
commande = new SqlCommand(sql, cnn);
//@dt_recp,@direc,@emet,@desti,@fourn,@dt_fac,@ttc,@dev,@objfac,@nfac,@ncom,@dt_env_fed,@restit,@env_dfi,@remar,@pole,@dt_env_dfi
commande.Parameters.AddWithValue("@dt_recp", pb.date_reception);
commande.Parameters.AddWithValue("@direc", pb.direction);
commande.Parameters.AddWithValue("@emet", pb.emetteur);
commande.Parameters.AddWithValue("@desti", pb.destinataire);
commande.Parameters.AddWithValue("@fourn", pb.fournisseur);
commande.Parameters.AddWithValue("@dt_fac", pb.date_facture);
commande.Parameters.AddWithValue("@ttc", pb.montant);
commande.Parameters.AddWithValue("@dev", pb.devise);
commande.Parameters.AddWithValue("@objfac", pb.objet_facture);
commande.Parameters.AddWithValue("@nfac", pb.Nfacture);
commande.Parameters.AddWithValue("@ncom", pb.Ncommande);
commande.Parameters.AddWithValue("@dt_env_fed", pb.date_envoi_fedosa);
commande.Parameters.AddWithValue("@restit", pb.restitution_fedosa);
commande.Parameters.AddWithValue("@env_dfi", pb.envoi_dfi);
commande.Parameters.AddWithValue("@remar", pb.remarque);
commande.Parameters.AddWithValue("@pole", pb.pole);
commande.Parameters.AddWithValue("@dt_env_dfi", pb.date_envoi_dfi);
commande.ExecuteNonQuery();
/*
* adapter = new SqlDataAdapter();
* adapter.InsertCommand = new SqlCommand(sql, cnn);
* adapter.InsertCommand.ExecuteNonQuery();*/
commande.Dispose();
cnn.Close();
//MessageBox.Show(fname);
}