public ListeModuleParDevis(string idDevis)
{
InitializeComponent();
monDevis = BDDExterne.GetDevis(idDevis);
labelNomClient.Text = monDevis.devClient.cliNom + " " + monDevis.devClient.cliPrenom;
labelDevisDateCreation.Text = monDevis.devDateCreation.ToShortDateString();
foreach (Module item in BDDExterne.GetAllModules())
{
data.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDisponible.DisplayMember = "Text";
listBoxModuleDisponible.ValueMember = "Value";
listBoxModuleDisponible.DataSource = data;
foreach (Module item in BDDExterne.GetModulesByDevis(idDevis))
{
dataModuleDevis.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDevis.DisplayMember = "Text";
listBoxModuleDevis.ValueMember = "Value";
listBoxModuleDevis.DataSource = dataModuleDevis;
}
public void refresh()
{
monDevis = BDDExterne.GetDevis(monDevis.devId.ToString());
labelNomClient.Text = monDevis.devClient.cliNom + " " + monDevis.devClient.cliPrenom;
labelDevisDateCreation.Text = monDevis.devDateCreation.ToShortDateString();
foreach (Module item in BDDExterne.GetAllModules())
{
data.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDisponible.DisplayMember = "Text";
listBoxModuleDisponible.ValueMember = "Value";
listBoxModuleDisponible.DataSource = data;
foreach (Module item in BDDExterne.GetModulesByDevis(monDevis.devId.ToString()))
{
dataModuleDevis.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDevis.DisplayMember = "Text";
listBoxModuleDevis.ValueMember = "Value";
listBoxModuleDevis.DataSource = dataModuleDevis;
}
private void button1_Click(object sender, EventArgs e)
{
Module monMod = BDDExterne.GetModule(listBoxModuleDisponible.SelectedValue.ToString());
int newNumMod = BDDExterne.getMaxNumModuleByDevis(monDevis.devId.ToString());
newNumMod++;
foreach (Parametre item in monMod.modParametres)
{
string query = @"INSERT INTO public.precise(id, id_parametre, id_devis, valeur, num_module) VALUES ('" + Guid.NewGuid().ToString() + "' , '" + item.parId.ToString() + "', '" + monDevis.devId.ToString() + "',0, " + newNumMod + ")";
if (BDDExterne.Insert(query) == false)
{
MessageBox.Show("echec insert");
}
}
dataModuleDevis.Clear();
foreach (Module item in BDDExterne.GetModulesByDevis(monDevis.devId.ToString()))
{
dataModuleDevis.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDevis.DataSource = null;
listBoxModuleDevis.Items.Clear();
listBoxModuleDevis.DisplayMember = "Text";
listBoxModuleDevis.ValueMember = "Value";
listBoxModuleDevis.DataSource = dataModuleDevis;
// ----
monDevis = BDDExterne.GetDevis(monDevis.devId.ToString());
labelNomClient.Text = monDevis.devClient.cliNom + " " + monDevis.devClient.cliPrenom;
labelDevisDateCreation.Text = monDevis.devDateCreation.ToShortDateString();
foreach (Module item in BDDExterne.GetAllModules())
{
data.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDisponible.DisplayMember = "Text";
listBoxModuleDisponible.ValueMember = "Value";
listBoxModuleDisponible.DataSource = data;
foreach (Module item in BDDExterne.GetModulesByDevis(monDevis.devId.ToString()))
{
dataModuleDevis.Add(new ComboxItem()
{
Value = item.modId, Text = item.modLibele
});
}
listBoxModuleDevis.DisplayMember = "Text";
listBoxModuleDevis.ValueMember = "Value";
listBoxModuleDevis.DataSource = dataModuleDevis;
}
void BtnSupprimer_Click(object sender, EventArgs e)
{
ComboxItem item = (ComboxItem)ComboBoxSupprimerFournisseur.SelectedItem;
//Fournisseur fou = (Fournisseur) item.Value;
BDDExterne.SupprimerFournisseur(item.Value.ToString());
//Fournisseur fournisseur = Fournisseur.afficher((Guid) item.Value);
ReloadFourn();
}
private void BtnSupprimer_Click(object sender, EventArgs e)
{
listBoxModuleDevis.Enabled = false;
// listBoxParam.Items
string numBox = listBoxParam.SelectedValue.ToString();
if (BDDExterne.DeletePreciseByDevisAndNumModule(monDevis.devId.ToString(), BDDExterne.getNumModuleFromIDPrecise(numBox).ToString()) == false)
{
MessageBox.Show("echec supression ");
}
refresh();
listBoxModuleDevis.Enabled = true;
}
private void BtnAjouter_Click(object sender, EventArgs e)
{
if (!textBox1.Text.Any())
{
MessageBox.Show("Vous devez renseigner le nom de la gamme !");
}
else
{
// ajout de la gamme
BDDExterne.AjouterGamme(new Gamme(Guid.NewGuid(), textBox1.Text));
MessageBox.Show("La gamme a été créée !");
this.textBox1.Text = "";
reloadGammes();
}
}
void BtnAjouter_Click(object sender, EventArgs e)
{
if (!CheckControls())
{
MessageBox.Show("Renseignez tout les champs !");
}
else
{
try {
BDDExterne.AjouterFounisseur(new Fournisseur(Guid.NewGuid(), TextBoxNom.Text, TextBoxTelephone.Text, Int32.Parse(TextBoxNumRue.Text), TextBoxRue.Text, TextBoxCodePostal.Text, TextBoxVille.Text, (string)ComboBoxPays.SelectedItem, TextBoxEmail.Text));
} catch (FormatException ex) {
Debug.WriteLine(ex.Message);
MessageBox.Show("Le champ numéro de rue n'est pas correctement rempli !");
}
ResetControls();
ReloadFourn();
}
}
public void ReloadFourn()
{
// vidage des combox
ComboBoxModifierFournisseur.Items.Clear();
ComboBoxSupprimerFournisseur.Items.Clear();
ComboBoxModifierFournisseur.Text = "";
ComboBoxSupprimerFournisseur.Text = "";
// ajout des valeurs
ComboxItem item;
foreach (Fournisseur f in BDDExterne.GetAllFournisseur())
{
item = new ComboxItem(f.fouNom, f.fouId);
ComboBoxModifierFournisseur.Items.Add(item);
ComboBoxSupprimerFournisseur.Items.Add(item);
}
}
private void BtnSupprimer_Click(object sender, EventArgs e)
{
//if (Gamme.listGamme.Count <= 0) {
// MessageBox.Show("Il n'y a pas de gammes !");
//} else {
// ComboxItem item = (ComboxItem) comboBox2.SelectedItem;
// if (Gamme.supprimeGamme((Guid) item.Value)) {
// MessageBox.Show("La gamme a été supprimée !");
// reloadGammes();
// }
//}
ComboxItem item = (ComboxItem)comboBox2.SelectedItem;
//Fournisseur fou = (Fournisseur) item.Value;
BDDExterne.SupprimerGamme(item.Value.ToString());
//Fournisseur fournisseur = Fournisseur.afficher((Guid) item.Value);
reloadGammes();
}
public static Boolean SupprimerSalarie(string id)
{
foreach (Salarie item in BDDExterne.GetAllSalarie())
{
if (item.salId.ToString() == id)
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
NpgsqlCommand MyCmd = null;
// id, nom ,tel,numrue,codepostal,ville,pays,mail,nom rue
string query = @"DELETE FROM ""Salarie"" WHERE id == '" + id;
Debug.WriteLine(query);
MyCmd = new NpgsqlCommand(query, conn);
MyCmd.ExecuteNonQuery(); //Exécution
conn.Close();
return(true);
}
}
return(false);
}
private void listBoxParam_DoubleClick(object sender, EventArgs e)
{
if (BDDExterne.ModifierValeurParam(listBoxParam.SelectedValue.ToString(), Int32.Parse(Microsoft.VisualBasic.Interaction.InputBox("entrer la nouvelle valeur", "Title", "").ToString())) == false)
{
MessageBox.Show("Erreur modification de la valeur");
}
listBoxParam.DataSource = null;
listBoxParam.Items.Clear();
List <ComboxItem> listComboxItemParam = new List <ComboxItem>();
monDevis = BDDExterne.GetDevis(monDevis.devId.ToString());
foreach (Parametre monParam in monDevis.modules[listBoxModuleDevis.SelectedIndex].modParametres)
{
listComboxItemParam.Add(new ComboxItem()
{
Value = monParam.parIdValeur, Text = monParam.parNom + " " + monParam.parValeur + " " + monDevis.modules[listBoxModuleDevis.SelectedIndex].uniteUsage
});
}
listBoxParam.DisplayMember = "Text";
listBoxParam.ValueMember = "Value";
listBoxParam.DataSource = listComboxItemParam;
}
public static Boolean SupprimerMatiere(string id)
{
foreach (Matiere item in BDDExterne.GetAllMatiere())
{
if (item.matId.ToString() == id)
{
Debug.WriteLine("on passe là");
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
NpgsqlCommand MyCmd = null;
// id, nom ,tel,numrue,codepostal,ville,pays,mail,nom rue
string query = @"DELETE FROM ""Matiere"" CASCADE WHERE id = '" + id + "'";
Debug.WriteLine(query);
MyCmd = new NpgsqlCommand(query, conn);
MyCmd.ExecuteNonQuery(); //Exécution
conn.Close();
return(true);
}
}
return(false);
}
public void reloadGammes()
{
// vidage des combobox
comboBox1.Items.Clear();
comboBox2.Items.Clear();
comboBox1.Text = "";
comboBox2.Text = "";
// ajout des valeurs
ComboxItem item;
foreach (Gamme g in BDDExterne.GetAllGammes())
{
item = new ComboxItem(g.gamLibelle, g.gamId);
comboBox1.Items.Add(item);
comboBox2.Items.Add(item);
}
// sélection du 1er élément
//comboBox1.SelectedIndex = 0;
//comboBox2.SelectedIndex = 0;
}
public void generePDF()
{
StringWriter stringWriter = new StringWriter();
using (HtmlTextWriter writer = new HtmlTextWriter(stringWriter))
{
writer.Write(
"<!DOCTYPE html><html><head>" +
"<style>" +
"#calc {" +
"top: 350px;" +
"position: relative;" +
"}" +
"aside {" +
"border: 1px solid;" +
"padding: 0 10px 10px;" +
"position: absolute;" +
"}" +
"aside ul {" +
"list-style-type: none;" +
"margin: 0;" +
"padding: 0;" +
"}" +
"table {" +
"width: 100%;" +
"}" +
"table td[colspan] {" +
"text-align: right;" +
"padding: 0 20px;" +
"}" +
"table tfoot tr td:last-child {" +
"font-weight: bold;" +
"}" +
"#client {" +
"min-width: 150px;" +
"max-width: 500px;" +
"top: 10px;" +
"left: 10px;" +
"}" +
"#devis {" +
"min-width: 300px;" +
"max-width: 500px;" +
"top: 150px;" +
"right: 10px;" +
"}" +
"#sign-client {" +
"width: 200px;" +
"height: 150px;" +
"top: 800px;" +
"left: 10px;" +
"}" +
"#sign-commer {" +
"width: 200px;" +
"height: 150px;" +
"top: 800px;" +
"right: 10px;" +
"}" +
"#logo {" +
"position: absolute;" +
"top: 0;" +
"right: 0;" +
"}" +
"#logo img { width: 250px; }" +
".right {" +
"display: inline-block;" +
"position: absolute;" +
"right: 1em;" +
"}" +
"/* bordure pour le tableau générale */" +
"table {" +
"border-collapse: collapse;" +
"}" +
"table tr {" +
"border-bottom: 1px solid #8a8787;" +
"}" +
"table td:last-child {" +
"text-align: right;" +
"border-left: 1px solid #8a8787;" +
"}" +
"/* bordure des totaux */" +
"tfoot::before {" +
"content: '';" +
"display: block;" +
"height: 18px;" +
"}" +
"table tfoot {" +
"margin-top: 20px;" +
"}" +
"table tfoot td {" +
"/*border-bottom: none;*/" +
"border: 1px solid #8a8787;" +
"}" +
"table tfoot tr, table tfoot td[colspan] {" +
"border: none;" +
"}" +
"</style>" +
"</head>" +
"<body>" +
"<section id=\"info\">" +
"<aside id=\"client\">" +
"<h2> Client</h2>" +
"<ul>" +
"<li><b>" + this.devClient.cliNom + "</b> " + this.devClient.cliPrenom + "</li>" +
"<li>" + this.devClient.cliNumRue + " " + this.devClient.cliRue + "</li>" +
"<li>" + this.devClient.cliCp + " <span class=\"right\">" + this.devClient.cliVille + "</span> </li>" +
"</ul>" +
"</aside>" +
"<aside id=\"devis\">" +
"<h2> Devis </h2>" +
"<ul>" +
"<li>N° de devis<span class=\"right\">5</span> </li>" +
"<li>Date de création : <span class=\"right\">" + this.devDateCreation.ToShortDateString() + "</span></li>" +
"<li> validité<span class=\"right\">25/07/2019</span> </li>" +
"</ul>" +
"</aside>" +
"<figure id=\"logo\">" +
"<img src='http://madera-construction.fr/images/site/logo.png'/>" +
"</figure>" +
"</section>" +
"<section id=\"calc\">" +
"<table>" +
"<thead>" +
"<tr>" +
"<th> N° </th>" +
"<th> Description</th>" +
"<th> Unité</th>" +
"<th> Prix</th>" +
"</tr>" +
"</thead>" +
"<tbody>");
double total = 0;
foreach (Module module in BDDExterne.GetModulesByDevis(this.devId.ToString()))
{
writer.Write("<tr><td>" + module.num_module + "</td>");
writer.Write("<td>" + module.modLibele + "</td>");
writer.Write("<td>" + module.uniteUsage + "</td>");
writer.Write("<td>" + module.prixBase + " €</td></tr>");
total += module.prixBase;
}
writer.Write("<tr></tr></tbody>" +
"<tfoot>" +
"<tr>" +
"<td colspan=\"2\"></td>" +
"<td>Hors taxes</td>" +
"<td>" + total + " €</td>" +
"</tr>" +
"<tr>" +
"<td colspan=\"2\"></td>" +
"<td>Taxe valeur ajoutée</td>" +
"<td>20%</td>" +
"</tr>" +
"<tr>" +
"<td colspan=\"2\"></td>" +
"<td>Toutes taxes comprises</td>" +
"<td>" + total * 1.2 + " €</td>" +
"</tr>" +
"</tfoot>" +
"</table>" +
"</section>" +
"<aside id=\"sign-client\"><h2>Bon pour accord</h2>Signature du Client</aside>" +
"<aside id=\"sign-commer\"><h2>Bon pour accord</h2>Signature du Commercial</aside>" +
"</body></html>");
//writer.AddAttribute(HtmlTextWriterAttribute.Src, @"C:\Users\maxna\source\repos\PFR-Madera\Madera\Resources\logo.png");
//writer.AddAttribute(HtmlTextWriterAttribute.Alt, "Madera");
//writer.RenderBeginTag(HtmlTextWriterTag.Img); // Begin #3
//writer.RenderEndTag(); // End #3
/*writer.Write(
* "Client : " + this.devClient.cliNom + " " + this.devClient.cliPrenom + "<br>" +
* "<br>" + this.devDateCreation.ToString() +
* "<br><br> Modules associés au projet : <br>"
* );
* foreach (Module module in BDDExterne.GetModulesByDevis(this.devId.ToString()))
* {
* writer.Write(module.modLibele + " " + module.uniteUsage + "<br>");
* foreach (Parametre param in module.modParametres)
* {
* writer.Write(param.parNom + " " + param.parValeur + "<br>");
* }
* writer.Write("<br><br>");
* }*/
}
System.IO.File.WriteAllText(@"C:\Users\Public\WriteText.html", stringWriter.ToString());
var Renderer = new IronPdf.HtmlToPdf();
var PDF = Renderer.RenderHTMLFileAsPdf(@"C:\Users\Public\WriteText.html");
string Outpath = @"C:\Users\Public\";
string OutpathView = "C:/Users/Public";
PDF.SaveAs(Outpath + "Devis_du_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + "_" + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".pdf");
System.Windows.Forms.MessageBox.Show("Votre PDF a été généré dans le dossier " + OutpathView);
}
public static List <Devis> GetAllDevis()
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
List <Devis> listeDevis = new List <Devis>();
string query = @"SELECT id,status,date_creation,date_signature,date_facture,montant_facture,""id_Client"",""id_Salarie"" FROM ""devis""";
Debug.WriteLine(query);
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
listeDevis.Add(new Devis(new Guid(dr[0].ToString()), Int32.Parse(dr[1].ToString()), DateTime.Parse(dr[2].ToString()), DateTime.Parse(dr[3].ToString()), DateTime.Parse(dr[4].ToString()), double.Parse(dr[5].ToString()), BDDExterne.GetClient(dr[6].ToString()), BDDExterne.GetSalarie(dr[7].ToString()), BDDExterne.GetModulesByDevis(dr[0].ToString())));
}
conn.Close();
return(listeDevis);
}
public FormProjet()
{
InitializeComponent();
BDDExterne.Open();
}
public static List <Matiere> GetAllMatiere()
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
List <Matiere> ListeMatiere = new List <Matiere>();
string query = @"SELECT id, nom, ""Fournisseur""FROM ""Matiere""";
Debug.WriteLine(query);
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
ListeMatiere.Add(new Matiere(new Guid(dr[0].ToString()), dr[1].ToString(), BDDExterne.GetFournisseur(dr[2].ToString())));
}
conn.Close();
return(ListeMatiere);
}
public static Matiere GetMatiere(string id)
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
string query = @"SELECT id, nom, ""Fournisseur"" FROM ""Matiere"" where id = '" + id + "'";
Debug.WriteLine(query);
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Matiere OneMatiere = new Matiere(new Guid(dr[0].ToString()), dr[1].ToString(), BDDExterne.GetFournisseur(dr[2].ToString()));
conn.Close();
return(OneMatiere);
}
conn.Close();
return(null);
}
public static List <Module> GetAllModules()
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
List <Module> ListeModules = new List <Module>();
string query = @"SELECT mod_id, mod_libelle, mod_prix_base, ""uniteUsage"", matiere, gamme FROM module";
Debug.WriteLine(query);
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
ListeModules.Add(new Module(Guid.Parse(dr[0].ToString()), dr[1].ToString(), BDDExterne.GetGamme(dr[5].ToString()), BDDExterne.GetMatiere(dr[4].ToString()), double.Parse(dr[2].ToString()), BDDExterne.GetAllParametreByModule(dr[0].ToString()), dr[3].ToString()));
}
conn.Close();
return(ListeModules);
}
public static Module GetModule(string id)
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
string query = @"SELECT mod_id, mod_libelle, mod_prix_base, ""uniteUsage"",""matiere"",""gamme"",""uniteUsage"" FROM module where mod_id = '" + id + "'";
Debug.WriteLine(query);
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Module OneModule = new Module(Guid.Parse(dr[0].ToString()), dr[1].ToString(), BDDExterne.GetGamme(dr[5].ToString()), BDDExterne.GetMatiere(dr[4].ToString()), double.Parse(dr[2].ToString()), BDDExterne.GetAllParametreByModule(dr[0].ToString()), dr[6].ToString());
conn.Close();
return(OneModule);
}
conn.Close();
return(null);
}
public static List <Module> GetModulesByDevis(string id)
{
NpgsqlConnection conn;
conn = new NpgsqlConnection(chaineConnection);
conn.Open();
List <Module> ListeModules = new List <Module>();
string query = @"SELECT distinct num_module, module.mod_id FROM precise inner join parametre on precise.id_parametre = parametre.par_id inner join module on module.mod_id = parametre.mod_id where id_devis = '" + id + "'";
Debug.WriteLine(query);
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Module module = BDDExterne.GetModule(dr[1].ToString());
module.num_module = Int32.Parse(dr[0].ToString());
ListeModules.Add(module);
}
conn.Close();
conn.Open();
List <string> ListeId = new List <string>();
query = @"select distinct num_module from precise where id_devis = '" + id + "'";
command = new NpgsqlCommand(query, conn);
dr = command.ExecuteReader();
while (dr.Read())
{
ListeId.Add(dr[0].ToString());
}
conn.Close();
foreach (string item in ListeId)
{
//ListeModules[i]
NpgsqlConnection conn2;
conn2 = new NpgsqlConnection(chaineConnection);
conn2.Open();
List <Module> ListeModules2 = new List <Module>();
string query2 = @"SELECT par_id, valeur,num_module,id FROM precise inner join parametre on precise.id_parametre = parametre.par_id inner join module on module.mod_id = parametre.mod_id where id_devis = '" + id + "' and num_module = " + item;
NpgsqlCommand command2 = new NpgsqlCommand(query2, conn2);
NpgsqlDataReader dr2 = command2.ExecuteReader();
while (dr2.Read())
{
foreach (Module item2 in ListeModules)
{
foreach (Parametre monParametre in item2.modParametres)
{
if (monParametre.parId == Guid.Parse(dr2[0].ToString()))
{
if (item2.num_module == Int32.Parse(dr2[2].ToString()))
{
monParametre.parValeur = dr2[1].ToString();
monParametre.parIdValeur = Guid.Parse(dr2[3].ToString());
}
}
}
}
}
conn2.Close();
}
return(ListeModules);
}