public void databaseFilePut(string varFilePath)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connec = new MySqlConnection(connString);
connec.Open();
UCHome getTokTri = new UCHome();
byte[] file;
using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = new BinaryReader(stream))
{
file = reader.ReadBytes((int)stream.Length);
}
}
using (var varConnection = connec)
using (var sqlWrite = new MySqlCommand(@"update login set pdfSigned = @File where id = '" + getTokTri.returnToken() + "'", varConnection))
{
sqlWrite.Parameters.Add("@File", MySqlDbType.VarBinary, file.Length).Value = file;
sqlWrite.ExecuteNonQuery();
}
connec.Close();
}
public static void databaseFileRead(string varID, string varPathToNewLocation)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
using (var varConnection = connection)
using (var sqlQuery = new MySqlCommand("SELECT pdfSigned FROM login WHERE id = '" + varID + "'", varConnection))
{
sqlQuery.Parameters.AddWithValue("@varID", varID);
using (var sqlQueryResult = sqlQuery.ExecuteReader())
if (sqlQueryResult != null)
{
sqlQueryResult.Read();
var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write))
fs.Write(blob, 0, blob.Length);
}
}
connection.Close();
}
private void button2_Click(object sender, EventArgs e)
{
try
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection selectRate = new MySqlConnection(connString);
selectRate.Open();
string update = "update pricing set rate ='" + textBox1.Text + "'";
MySqlCommand updateRate = new MySqlCommand(update, selectRate);
updateRate.ExecuteNonQuery();
MessageBox.Show("work");
selectRate.Close();
}
catch
{
MessageBox.Show("Le format entré doit être le suivant 0.00");
}
}
public Form1()
{
InitializeComponent();
panel1.Height = label2.Height;
ucHome1.BringToFront();
panel1.Height = label2.Height;
panel1.Top = label2.Top;
try
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
connection.Close();
}
catch
{
MessageBox.Show("Vous n'avez pas accès à internet, veuillez relancer l'application une fois connecté");
this.Close();
}
}
public static void databaseFilePut(string varFilePath)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connec = new MySqlConnection(connString);
connec.Open();
UCHome getTokTri = new UCHome();
UCTreasure getTokMember = new UCTreasure();
string requeteStatut = "select statut from login where id = '" + getTokTri.returnToken() + "'";
MySqlDataAdapter getStatut = new MySqlDataAdapter(requeteStatut, connec);
DataTable dbStatut = new DataTable();
getStatut.Fill(dbStatut);
statutGot = dbStatut.Rows[0][0].ToString();
if (statutGot == "user")
{
byte[] file;
using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = new BinaryReader(stream))
{
file = reader.ReadBytes((int)stream.Length);
}
}
using (var varConnection = connec)
using (var sqlWrite = new MySqlCommand(@"update login set pdfNotSigned = @File where id = '" + getTokTri.returnToken() + "'", varConnection))
{
sqlWrite.Parameters.Add("@File", MySqlDbType.VarBinary, file.Length).Value = file;
sqlWrite.ExecuteNonQuery();
}
connec.Close();
}
else if (statutGot == "treasure")
{
byte[] file;
using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = new BinaryReader(stream))
{
file = reader.ReadBytes((int)stream.Length);
}
}
using (var varConnection = connec)
using (var sqlWrite = new MySqlCommand(@"update login set pdfNotSigned = @File where id = '" + getTokMember.getIdMember() + "'", varConnection))
{
sqlWrite.Parameters.Add("@File", MySqlDbType.VarBinary, file.Length).Value = file;
sqlWrite.ExecuteNonQuery();
}
connec.Close();
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Ajouter")
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection coInsert = new MySqlConnection(connString);
coInsert.Open();
int insertRow = Convert.ToInt32(dataGridView1.CurrentRow.Index);
try
{
UCHome insertId = new UCHome();
int insertIdSlip = insertId.returnToken();
string insertAll = "insert into slips (date, pattern, path, kmsTraveled, pathCost, tollCost, mealCost, accomodationCost, totalCost, idMember) values ('" + dataGridView1.Rows[insertRow].Cells[0].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[1].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[2].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[3].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[4].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[5].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[6].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[7].Value.ToString() + "', '" + dataGridView1.Rows[insertRow].Cells[8].Value.ToString() + "', '" + insertIdSlip + "')";
MySqlCommand exeAll = new MySqlCommand(insertAll, coInsert);
exeAll.ExecuteNonQuery();
coInsert.Close();
slipBindingSource1.Add(new Slip {
});
comptSlip++;
}
catch
{
MessageBox.Show("Mauvaise sélection :/");
}
}
if (dataGridView1.Columns[e.ColumnIndex].Name == "Modifier")
{
UCHome getIdAgain = new UCHome();
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
int insertRow = Convert.ToInt32(dataGridView1.CurrentRow.Index);
string updateSlip = "update slips set date = '" + dataGridView1.Rows[insertRow].Cells["date"].Value.ToString() + "' , pattern = '" + dataGridView1.Rows[insertRow].Cells["pattern"].Value.ToString() + "' , path = '" + dataGridView1.Rows[insertRow].Cells["path"].Value.ToString() + "' , kmsTraveled = '" + dataGridView1.Rows[insertRow].Cells["kmsTraveled"].Value.ToString() + "' , pathCost = '" + dataGridView1.Rows[insertRow].Cells["pathCost"].Value.ToString() + "' , tollCost = '" + dataGridView1.Rows[insertRow].Cells["tollCost"].Value.ToString() + "' , mealCost = '" + dataGridView1.Rows[insertRow].Cells["mealCost"].Value.ToString() + "' , accomodationCost = '" + dataGridView1.Rows[insertRow].Cells["accommodationCost"].Value.ToString() + "' , totalCost = '" + dataGridView1.Rows[insertRow].Cells["totalCost"].Value.ToString() + "' where id = '" + dataGridView1.Rows[insertRow].Cells["Id"].Value.ToString() + "'";
MySqlCommand updateSlipDB = new MySqlCommand(updateSlip, connection);
updateSlipDB.ExecuteNonQuery();
}
if (dataGridView1.Columns[e.ColumnIndex].Name == "Supprimer")
{
slipBindingSource1.RemoveCurrent();
}
}
private void btnco_Click(object sender, EventArgs e)
{
if (Itextpwd.Text != Itextconfirm.Text)
{
MessageBox.Show("Mots de passes différents");
}
else
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection creation = new MySqlConnection(connString);
creation.Open();
MySqlDataAdapter creationtest = new MySqlDataAdapter("select count(*) from adherents where numLicence ='" + Itextlicence.Text + "' and numLigue = '" + Itextligue.Text + "'", creation);
DataTable dt = new DataTable();
creationtest.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
try
{
string inscription = "insert into login (email, password, adress) values ('" + Itextmail.Text + "', MD5('" + Itextpwd.Text + "'), '" + ItextAdress.Text + "')";
MySqlCommand crea = new MySqlCommand(inscription, creation);
crea.ExecuteNonQuery();
string ids = "select id from login where email = '" + Itextmail.Text + "'";
MySqlDataAdapter searchsearch = new MySqlDataAdapter(ids, creation);
DataTable dtsearch = new DataTable();
searchsearch.Fill(dtsearch);
idsafe = dtsearch.Rows[0][0].ToString();
int testparse = Int32.Parse(idsafe);
string majId = "update adherents set idLogin ='******' where numLicence ='" + Itextlicence.Text + "' and numLigue = '" + Itextligue.Text + "'";
MySqlCommand putLogin = new MySqlCommand(majId, creation);
putLogin.ExecuteNonQuery();
MessageBox.Show("Working");
}
catch
{
MessageBox.Show("Un compte existe déjà avec cet email.");
}
}
else
{
MessageBox.Show("rip");
}
creation.Close();
}
}
public void getDataSlip(int tokenALO)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection getSlip = new MySqlConnection(connString);
getSlip.Open();
try
{
string getInfo = "SELECT * FROM slips WHERE idMember = '" + tokenALO + "'";
MySqlDataAdapter coInfo = new MySqlDataAdapter(getInfo, getSlip);
DataTable getDt = new DataTable();
coInfo.Fill(getDt);
string ouech = getDt.Rows[0][0].ToString();
foreach (DataRow dt in getDt.Rows)
{
comptSlip++;
slipBindingSource1.Add(new Slip
{
Id = Convert.ToInt32(dt["id"]),
SlipDate = dt["date"].ToString(),
SlipPattern = dt["pattern"].ToString(),
SlipPath = dt["path"].ToString(),
SlipKilometers = Convert.ToInt32(dt["kmsTraveled"]),
PathCost = Convert.ToInt32(dt["pathCost"]),
TollCost = Convert.ToInt32(dt["tollCost"]),
MealCost = Convert.ToInt32(dt["mealCost"]),
AccommodationCost = Convert.ToInt32(dt["accomodationCost"]),
TotalCost = Convert.ToInt32(dt["totalCost"]),
});
}
slipBindingSource1.Add(new Slip {
});
}
catch
{
slipBindingSource1.Add(new Slip {
});
MessageBox.Show("Aucun bordereau n'a encore été completé");
}
getSlip.Close();
}
private void button1_Click(object sender, EventArgs e)
{
UCHome getTokenId = new UCHome();
getContent coGetInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = coGetInfo.getServer();
conn.UserID = coGetInfo.getId();
conn.Password = coGetInfo.getPassword();
conn.Database = coGetInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
string updatePwd = "update login set confirmPassword = MD5('" + textBoxMdpC.Text + "')";
MySqlCommand pwdConfirm = new MySqlCommand(updatePwd, connection);
pwdConfirm.ExecuteNonQuery();
string selectPwdConfirm = "select confirmPassword from login where id = '" + getTokenId.returnToken() + "' ";
MySqlDataAdapter verifConfirm = new MySqlDataAdapter(selectPwdConfirm, connection);
DataTable dtConfirm = new DataTable();
verifConfirm.Fill(dtConfirm);
string selectPwd = "select password from login where id = '" + getTokenId.returnToken() + "' ";
MySqlDataAdapter verifPwd = new MySqlDataAdapter(selectPwd, connection);
DataTable dtVerif = new DataTable();
verifPwd.Fill(dtVerif);
if (dtConfirm.Rows[0][0].ToString() == dtVerif.Rows[0][0].ToString())
{
string updateAdress = "update login set adress = '" + textBoxAdress.Text + "' where id = '" + getTokenId.returnToken() + "'";
MySqlCommand Adress = new MySqlCommand(updateAdress, connection);
Adress.ExecuteNonQuery();
string updateName = "update adherents set name = '" + textBoxName.Text + "' where idLogin = '******' ";
MySqlCommand updateNameCo = new MySqlCommand(updateName, connection);
updateNameCo.ExecuteNonQuery();
string updateFName = "update adherents set firstName = '" + textBoxFName.Text + "' where idLogin = '******' ";
MySqlCommand updateFNameCo = new MySqlCommand(updateFName, connection);
updateFNameCo.ExecuteNonQuery();
string updateMail = "update login set email = '" + textBoxMail.Text + "' where id = '" + getTokenId.returnToken() + "' ";
MySqlCommand updateMailCo = new MySqlCommand(updateMail, connection);
updateMailCo.ExecuteNonQuery();
MessageBox.Show("Les modifications ont été apportées à votre compte.");
}
else
{
MessageBox.Show("Veuillez confirmer le mot de passe");
}
connection.Close();
}
public void getData(int idToken)
{
try
{
getContent coGetInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = coGetInfo.getServer();
conn.UserID = coGetInfo.getId();
conn.Password = coGetInfo.getPassword();
conn.Database = coGetInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
string getUser = "******" + idToken + "'";
MySqlDataAdapter UserArray = new MySqlDataAdapter(getUser, connection);
DataTable dtUser = new DataTable();
UserArray.Fill(dtUser);
textBoxName.Text = dtUser.Rows[0]["name"].ToString();
textBoxFName.Text = dtUser.Rows[0]["firstName"].ToString();
string getLogin = "******" + idToken + "'";
MySqlDataAdapter loginArray = new MySqlDataAdapter(getLogin, connection);
DataTable dtLogin = new DataTable();
loginArray.Fill(dtLogin);
textBoxMail.Text = dtLogin.Rows[0]["email"].ToString();
NumLigue.Text = dtUser.Rows[0]["numLigue"].ToString();
NumLicence.Text = dtUser.Rows[0]["numLicence"].ToString();
textBoxAdress.Text = dtLogin.Rows[0]["adress"].ToString();
switch (dtLogin.Rows[0]["statut"].ToString())
{
case "user":
listBoxAccount.SelectedItem = "Utilisateur";
break;
case "treasure":
listBoxAccount.SelectedItem = "Trésorier";
break;
default:
listBoxAccount.SelectedItem = "Administrateur";
break;
}
connection.Close();
}
catch
{
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
string selectValidated = "SELECT id from slips where Validated = 'true' and idMember = '" + idUser + "'";
MySqlDataAdapter comSelect = new MySqlDataAdapter(selectValidated, connection);
DataTable dtSelect = new DataTable();
comSelect.Fill(dtSelect);
foreach (DataRow dtse in dtSelect.Rows)
{
comptValidate++;
}
if (comptValidate == comptSlip)
{
MessageBox.Show("Où souhaitez vous le télécharger ?");
try
{
using (FolderBrowserDialog ofd = new FolderBrowserDialog()
{
})
if (ofd.ShowDialog() == DialogResult.OK)
{
pathFile = ofd.SelectedPath;
}
string pathpath = pathFile + @"\pdfUser.pdf";
databaseFileRead(idUser, pathpath);
}
catch
{
MessageBox.Show("Aucun bordereau n'a été déposé par l'utilisateur");
}
}
else
{
MessageBox.Show("Tous les frais doivent être validés pour obtention du pdf signé.");
}
}
catch {}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
int insertRow = Convert.ToInt32(dataGridView1.CurrentRow.Index);
if (dataGridView1.Columns[e.ColumnIndex].Name == "Valider")
{
try
{
dataGridView1.Rows[insertRow].Cells["Validate"].Value = "0";
string updateValidated = "update slips set Validated = 'True' where id = '" + dataGridView1.Rows[insertRow].Cells["Id"].Value.ToString() + "'";
MySqlCommand putUpdateValid = new MySqlCommand(updateValidated, connection);
putUpdateValid.ExecuteNonQuery();
}
catch
{
MessageBox.Show("Un soucis est survenu lors de la validation");
}
}
if (dataGridView1.Columns[e.ColumnIndex].Name == "Modifier")
{
try
{
string updateSlip = "update slips set date = '" + dataGridView1.Rows[insertRow].Cells["date"].Value.ToString() + "' , pattern = '" + dataGridView1.Rows[insertRow].Cells["pattern"].Value.ToString() + "' , path = '" + dataGridView1.Rows[insertRow].Cells["path"].Value.ToString() + "' , kmsTraveled = '" + dataGridView1.Rows[insertRow].Cells["kmsTraveled"].Value.ToString() + "' , pathCost = '" + dataGridView1.Rows[insertRow].Cells["pathCost"].Value.ToString() + "' , tollCost = '" + dataGridView1.Rows[insertRow].Cells["tollCost"].Value.ToString() + "' , mealCost = '" + dataGridView1.Rows[insertRow].Cells["mealCost"].Value.ToString() + "' , accomodationCost = '" + dataGridView1.Rows[insertRow].Cells["accommodationCost"].Value.ToString() + "' , totalCost = '" + dataGridView1.Rows[insertRow].Cells["totalCost"].Value.ToString() + "' where id = '" + dataGridView1.Rows[insertRow].Cells["Id"].Value.ToString() + "'";
MySqlCommand updateSlipDB = new MySqlCommand(updateSlip, connection);
updateSlipDB.ExecuteNonQuery();
}
catch
{
MessageBox.Show("Modifications impossibles");
}
}
connection.Close();
}
private void button4_Click(object sender, EventArgs e)
{
try
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
string selectValidated = "SELECT id from slips where Validated = 'true' and idMember = '" + idUser + "'";
MySqlDataAdapter comSelect = new MySqlDataAdapter(selectValidated, connection);
DataTable dtSelect = new DataTable();
comSelect.Fill(dtSelect);
foreach (DataRow dtse in dtSelect.Rows)
{
comptValidateBis++;
}
if (comptValidateBis == comptSlip)
{
try
{
if (idUser != null)
{
CreateWordDocumentCerfa(mainPath + @"\templateCerfa.docx", mainPath + @"CerfaFill.docx");
}
}
catch
{ }
}
else
{
MessageBox.Show("Veuillez valider tous les champs");
}
connection.Close();
}
catch { }
}
private void label5_Click(object sender, EventArgs e)
{
try
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
string pswStock = generatorPwd();
getContent getIDS = new getContent();
string pwdMail = getIDS.getpwdM();
getContent getMail = new getContent();
string idMail = getMail.getIdM();
MailMessage Msg = new MailMessage();
Msg.From = new MailAddress(idMail);
Msg.To.Add(new MailAddress(textmail.Text));
Msg.Subject = "Nouveau mot de passe";
Msg.Body = "Vous avez demandé un changement de mot de passe.\n Celui ci a été modifié et remplacé par le suivant : " + pswStock + "";
SmtpClient client = new SmtpClient("smtp.gmail.com", 587);
client.EnableSsl = true;
client.Credentials = new NetworkCredential("*****@*****.**", pwdMail);
client.Send(Msg);
MessageBox.Show("Mail envoyé !");
string pdwforgot = "update login set password = MD5('" + pswStock + "') where email = '" + textmail.Text + "'";
MySqlCommand changepwd = new MySqlCommand(pdwforgot, connection);
changepwd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("Le message ne s'est pas envoyé, veuillez selectionner une adresse valide !");
}
}
private void UCAdmin_Load(object sender, EventArgs e)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection selectRate = new MySqlConnection(connString);
selectRate.Open();
string getRate = "select rate from pricing";
MySqlDataAdapter ratePricing = new MySqlDataAdapter(getRate, selectRate);
DataTable dtRate = new DataTable();
ratePricing.Fill(dtRate);
string putRate = dtRate.Rows[0][0].ToString();
textBox1.Text = putRate;
selectRate.Close();
}
private void FormPDFUser_Load(object sender, EventArgs e)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connec = new MySqlConnection(connString);
connec.Open();
UCHome getTokTri = new UCHome();
UCTreasure getTokMember = new UCTreasure();
string requeteStatut = "select statut from login where id = '" + getTokTri.returnToken() + "'";
MySqlDataAdapter getStatut = new MySqlDataAdapter(requeteStatut, connec);
DataTable dbStatut = new DataTable();
getStatut.Fill(dbStatut);
statutGot = dbStatut.Rows[0][0].ToString();
if (statutGot == "user")
{
UCHome tokos = new UCHome();
string tokenA = tokos.returnToken().ToString();
axAcroPDF1.src = pathpath + @"\bordereauUser" + tokenA + ".pdf";
databaseFilePut(pathpath + @"\bordereauUser" + tokenA + ".pdf");
}
else if (statutGot == "treasure")
{
UCTreasure tokas = new UCTreasure();
string tokenB = tokas.getIdMember();
axAcroPDF1.src = pathpath + @"\bordereauUser" + tokenB + ".pdf";
databaseFilePut(pathpath + @"\bordereauUser" + tokenB + ".pdf");
}
}
private void button1_Click(object sender, EventArgs e)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection coInsert = new MySqlConnection(connString);
coInsert.Open();
object save13 = mainPathUser + @"\bordereauFillDataGridUser.docx";
string template = mainPathUser + @"\templateBasic.docx";
object missing = Missing.Value;
word.Application wordApp = new word.Application();
wordApp.Visible = true;
word.Document document = wordApp.Documents.OpenNoRepairDialog(template);
document.Activate();
word.Table table = document.Tables[1];
for (int a = comptSlip - 1; a >= 0; a--)
{
table.Cell(1, 1).Range.Text = dataGridView1.Rows[a].Cells[1].Value.ToString();
table.Cell(1, 2).Range.Text = dataGridView1.Rows[a].Cells[2].Value.ToString();
table.Cell(1, 3).Range.Text = dataGridView1.Rows[a].Cells[3].Value.ToString();
table.Cell(1, 4).Range.Text = dataGridView1.Rows[a].Cells[4].Value.ToString() + "€";
table.Cell(1, 5).Range.Text = dataGridView1.Rows[a].Cells[5].Value.ToString() + "€";
table.Cell(1, 6).Range.Text = dataGridView1.Rows[a].Cells[6].Value.ToString() + "€";
table.Cell(1, 7).Range.Text = dataGridView1.Rows[a].Cells[7].Value.ToString() + "€";
table.Cell(1, 8).Range.Text = dataGridView1.Rows[a].Cells[8].Value.ToString() + "€";
table.Cell(1, 9).Range.Text = dataGridView1.Rows[a].Cells[9].Value.ToString() + "€";
document.Tables[1].Rows.Add(document.Tables[1].Rows[1]);
}
table.Cell(1, 1).Range.Text = "Date";
table.Cell(1, 2).Range.Text = "Motif";
table.Cell(1, 3).Range.Text = "Trajet";
table.Cell(1, 4).Range.Text = "Kms parcourus";
table.Cell(1, 5).Range.Text = "Coût trajet";
table.Cell(1, 6).Range.Text = "Péages";
table.Cell(1, 7).Range.Text = "Repas";
table.Cell(1, 8).Range.Text = "Hébergement";
table.Cell(1, 9).Range.Text = "Total";
UCHome getTok = new UCHome();
string getTotal = "select sum(totalCost) from slips where idMember = '" + getTok.returnToken() + "'";
MySqlDataAdapter sumTotal = new MySqlDataAdapter(getTotal, coInsert);
DataTable sumDt = new DataTable();
sumTotal.Fill(sumDt);
totalCostVar = sumDt.Rows[0][0].ToString();
document.SaveAs2(ref save13, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);
document.Close();
wordApp.Quit();
CreateWordDocument(mainPathUser + @"\bordereauFillDataGridUser.docx", mainPathUser + @"\bordereauFileFillUser.docx");
FormPDFUser Fc = new FormPDFUser();
Fc.ShowDialog();
}
public void CreateWordDocument(object filename, object SaveAs)
{
UCHome tok = new UCHome();
string tokenForPdf = tok.returnToken().ToString();
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection coInsert = new MySqlConnection(connString);
coInsert.Open();
UCHome getTok = new UCHome();
string getInfo = "select * from adherents where idLogin = '******'";
MySqlDataAdapter exeGet = new MySqlDataAdapter(getInfo, coInsert);
DataTable dtInfo = new DataTable();
exeGet.Fill(dtInfo);
string price = "select rate from pricing";
MySqlDataAdapter priceSql = new MySqlDataAdapter(price, coInsert);
DataTable dtPrice = new DataTable();
priceSql.Fill(dtPrice);
string getAdress = "select adress from login where id = '" + getTok.returnToken() + "'";
MySqlDataAdapter putAdress = new MySqlDataAdapter(getAdress, coInsert);
DataTable dtAdress = new DataTable();
putAdress.Fill(dtAdress);
string infoclub = "select * from club where idClub = '" + dtInfo.Rows[0]["numLigue"].ToString() + "'";
MySqlDataAdapter clubInfo = new MySqlDataAdapter(infoclub, coInsert);
DataTable dtclubAdress = new DataTable();
clubInfo.Fill(dtclubAdress);
word.Application wordApp = new word.Application();
object missing = Missing.Value;
word.Document myWordDoc = null;
if (File.Exists((string)filename))
{
object readOnly = false;
object isVisible = false;
wordApp.Visible = true;
myWordDoc = wordApp.Documents.Open(ref filename, ref missing, ref readOnly,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);
myWordDoc.Activate();
this.FindAndReplace(wordApp, "<prenom>", dtInfo.Rows[0]["firstName"].ToString());
this.FindAndReplace(wordApp, "<nom>", dtInfo.Rows[0]["name"].ToString());
this.FindAndReplace(wordApp, "<numLicence>", dtInfo.Rows[0]["numLicence"].ToString());
this.FindAndReplace(wordApp, "<total>", totalCostVar);
this.FindAndReplace(wordApp, "<adresse>", dtAdress.Rows[0][0].ToString());
this.FindAndReplace(wordApp, "<prix>", dtPrice.Rows[0][0].ToString());
try
{
this.FindAndReplace(wordApp, "<clubAdress>", dtclubAdress.Rows[0]["clubAdress"].ToString());
}
catch { }
}
else
{
MessageBox.Show("File not found");
}
myWordDoc.SaveAs2(ref SaveAs, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);
myWordDoc.ExportAsFixedFormat(mainPathUser + @"\bordereauUser" + tokenForPdf + ".pdf", word.WdExportFormat.wdExportFormatPDF);
myWordDoc.Close();
wordApp.Quit();
MessageBox.Show("Created");
coInsert.Close();
}
public void btnco_Click(object sender, EventArgs e)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
MySqlDataAdapter logon = new MySqlDataAdapter("select count(*) from login where email ='" + textmail.Text + "' and password = MD5('" + textpwd.Text + "')", connection);
DataTable dt = new DataTable();
logon.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
MySqlDataAdapter verif = new MySqlDataAdapter("select 1stConnexion from login where email ='" + textmail.Text + "' and password = MD5('" + textpwd.Text + "')", connection);
DataTable search1st = new DataTable();
verif.Fill(search1st);
if (search1st.Rows[0][0].ToString() == "never")
{
//Envoie de mon mail en cas de premiere connexion --> A améliorer pour transmettre les infos obtenues avec le token
getContent getIDS = new getContent();
string pwdMail = getIDS.getpwdM();
getContent getMail = new getContent();
string idMail = getMail.getIdM();
MailMessage Msg = new MailMessage();
Msg.From = new MailAddress(idMail);
Msg.To.Add(new MailAddress(textmail.Text));
Msg.Subject = "Coordonnées compte M2L";
Msg.Body = "Bienvenue sur l'application de la maison des ligues. Vos coordonnées sont les suivantes : -Adresse mail : '" + textmail.Text + "' -Mot de passe: '" + textpwd.Text + "'";
SmtpClient client = new SmtpClient("smtp.gmail.com", 587);
client.EnableSsl = true;
client.Credentials = new NetworkCredential("*****@*****.**", pwdMail);
client.Send(Msg);
MessageBox.Show("Mail envoyé !");
string firstCo = "update login set 1stConnexion = 'already' where email ='" + textmail.Text + "' and password = MD5('" + textpwd.Text + "')";
MySqlCommand Change1stCo = new MySqlCommand(firstCo, connection);
Change1stCo.ExecuteNonQuery();
}
//Recuperation du token pour créer ma session
string createToken = "select id from login where email ='" + textmail.Text + "' and password = MD5('" + textpwd.Text + "')";
MySqlDataAdapter token = new MySqlDataAdapter(createToken, connection);
DataTable tokendt = new DataTable();
token.Fill(tokendt);
tokenNb = tokendt.Rows[0][0].ToString();
tokeen = Int32.Parse(tokenNb);
MessageBox.Show("Vous etes maintenant connecté");
try
{
Form1 Testes = new Form1();
string getNameForm = "select name from adherents where idLogin = '******'";
MySqlDataAdapter putNameForm = new MySqlDataAdapter(getNameForm, connection);
DataTable NameTok = new DataTable();
putNameForm.Fill(NameTok);
nameFromTok = NameTok.Rows[0][0].ToString();
Testes.label1_TextChanged("Bonjour " + nameFromTok);
}
catch
{
}
try
{
string getStatutForm = "select statut from login where id = '" + tokeen + "'";
MySqlDataAdapter putStatut = new MySqlDataAdapter(getStatutForm, connection);
DataTable Statut = new DataTable();
putStatut.Fill(Statut);
string statutFromTok = Statut.Rows[0][0].ToString();
Form1 aal = new Form1();
aal.showbutton(nameFromTok, statutFromTok);
}
catch
{
}
}
else
{
MessageBox.Show("Mauvais mot de passe ou adresse mail");
}
connection.Close();
}
public void allTreasurePart()
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
UCHome getNum = new UCHome();
string getNumTreasure = "select numLigue from adherents where idLogin = '******'";
MySqlCommand recupNum = new MySqlCommand(getNumTreasure, connection);
recupNum.ExecuteNonQuery();
MySqlDataAdapter alop = new MySqlDataAdapter(getNumTreasure, connection);
DataTable stocknum = new DataTable();
alop.Fill(stocknum);
numGot = stocknum.Rows[0][0].ToString();
string sqlQuery = "select firstName from adherents where numLigue = '" + numGot + "' ";
MySqlCommand sqlcom = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = sqlcom.ExecuteReader();
AutoCompleteStringCollection autotext = new AutoCompleteStringCollection();
while (sdr.Read())
{
autotext.Add(sdr.GetString(0));
}
textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;
textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
textBox1.AutoCompleteCustomSource = autotext;
sdr.Close();
connection.Close();
connection.Open();
string sqlQuery2 = "select name from adherents where numLigue = '" + numGot + "' ";
MySqlCommand sqlcom2 = new MySqlCommand(sqlQuery2, connection);
MySqlDataReader sdr2 = sqlcom2.ExecuteReader();
AutoCompleteStringCollection autotext2 = new AutoCompleteStringCollection();
while (sdr2.Read())
{
autotext2.Add(sdr2.GetString(0));
}
textBox2.AutoCompleteMode = AutoCompleteMode.Suggest;
textBox2.AutoCompleteSource = AutoCompleteSource.CustomSource;
textBox2.AutoCompleteCustomSource = autotext2;
sdr2.Close();
connection.Close();
connection.Open();
string sqlQuery3 = "select numLicence from adherents where numLigue = '" + numGot + "' ";
MySqlCommand sqlcom3 = new MySqlCommand(sqlQuery3, connection);
MySqlDataReader sdr3 = sqlcom3.ExecuteReader();
AutoCompleteStringCollection autotext3 = new AutoCompleteStringCollection();
while (sdr3.Read())
{
autotext3.Add(sdr3.GetString(0));
}
textBox3.AutoCompleteMode = AutoCompleteMode.Suggest;
textBox3.AutoCompleteSource = AutoCompleteSource.CustomSource;
textBox3.AutoCompleteCustomSource = autotext3;
sdr3.Close();
connection.Close();
connection.Open();
string rqtSelect = "SELECT * FROM login JOIN adherents ON login.statut = 'user' WHERE adherents.numLigue = '" + numGot + "' AND login.id = adherents.idLogin";
MySqlCommand sqlCommand = new MySqlCommand(rqtSelect, connection);
MySqlDataReader dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
ListViewItem item = new ListViewItem(dr["firstName"].ToString());
item.SubItems.Add(dr["name"].ToString());
item.SubItems.Add(dr["numLicence"].ToString());
listView1.Items.Add(item);
}
connection.Close();
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.Rows.Clear();
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection connection = new MySqlConnection(connString);
connection.Open();
try
{
string select = "select idLogin from adherents where firstName = '" + textBox1.Text + "' and name = '" + textBox2.Text + "' and numLicence = '" + textBox3.Text + "'";
MySqlDataAdapter getID = new MySqlDataAdapter(select, connection);
DataTable idDt = new DataTable();
getID.Fill(idDt);
idUser = idDt.Rows[0][0].ToString();
if (idDt.Rows[0][0].ToString() != null)
{
try
{
string getInfo = "SELECT * FROM slips WHERE idMember = '" + idDt.Rows[0][0] + "'";
MySqlDataAdapter coInfo = new MySqlDataAdapter(getInfo, connection);
DataTable getDt = new DataTable();
coInfo.Fill(getDt);
string ouech = getDt.Rows[0][0].ToString();
foreach (DataRow dt in getDt.Rows)
{
comptSlip++;
slipBindingSource1.Add(new Slip
{
Id = Convert.ToInt32(dt["Id"]),
SlipDate = dt["date"].ToString(),
SlipPattern = dt["pattern"].ToString(),
SlipPath = dt["path"].ToString(),
SlipKilometers = Convert.ToInt32(dt["kmsTraveled"]),
PathCost = Convert.ToInt32(dt["pathCost"]),
TollCost = Convert.ToInt32(dt["tollCost"]),
MealCost = Convert.ToInt32(dt["mealCost"]),
AccommodationCost = Convert.ToInt32(dt["accomodationCost"]),
TotalCost = Convert.ToInt32(dt["totalCost"]),
});
}
}
catch
{
MessageBox.Show("Aucun bordereau n'a encore été completé par l'utilisateur");
}
}
else
{
MessageBox.Show("Veuillez renseigner un utilisateur valide.");
}
}
catch
{
MessageBox.Show("Veuillez renseigner un utilisateur valide.");
}
connection.Close();
}
public void CreateWordDocumentCerfa(object filename, object SaveAs)
{
getContent returnInfo = new getContent();
MySqlConnectionStringBuilder conn = new MySqlConnectionStringBuilder();
conn.Server = returnInfo.getServer();
conn.UserID = returnInfo.getId();
conn.Password = returnInfo.getPassword();
conn.Database = returnInfo.getDb();
var connString = conn.ToString();
MySqlConnection coInsert = new MySqlConnection(connString);
coInsert.Open();
UCHome getTok = new UCHome();
string getInfo = "select * from adherents where idLogin = '******'";
MySqlDataAdapter exeGet = new MySqlDataAdapter(getInfo, coInsert);
DataTable dtInfo = new DataTable();
exeGet.Fill(dtInfo);
string getTotalBis = "select sum(totalCost) from slips where idMember = '" + idUser + "'";
MySqlDataAdapter sumTotalBis = new MySqlDataAdapter(getTotalBis, coInsert);
DataTable sumDtBis = new DataTable();
sumTotalBis.Fill(sumDtBis);
totalCostVarBis = sumDtBis.Rows[0][0].ToString();
string getInfoAdress = "select adress from login where id = '" + idUser + "'";
MySqlDataAdapter adress = new MySqlDataAdapter(getInfoAdress, coInsert);
DataTable dtAdress = new DataTable();
adress.Fill(dtAdress);
string infoclub = "select * from club where idClub = '" + numGot + "'";
MySqlDataAdapter clubInfo = new MySqlDataAdapter(infoclub, coInsert);
DataTable dtclubAdress = new DataTable();
clubInfo.Fill(dtclubAdress);
word.Application wordApp = new word.Application();
object missing = Missing.Value;
word.Document myWordDoc = null;
if (File.Exists((string)filename))
{
object readOnly = false;
object isVisible = false;
wordApp.Visible = true;
myWordDoc = wordApp.Documents.Open(ref filename, ref missing, ref readOnly,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);
myWordDoc.Activate();
//this.FindAndReplace(wordApp, "<clubName>", "Nom du Club");
//this.FindAndReplace(wordApp, "<clubAdresse>", "Adresse du Club");
//this.FindAndReplace(wordApp, "<objet>", "Objet");
this.FindAndReplace(wordApp, "<adherentName>", dtInfo.Rows[0]["name"].ToString());
this.FindAndReplace(wordApp, "<adherentAdresse>", dtAdress.Rows[0][0].ToString());
this.FindAndReplace(wordApp, "<prix>", totalCostVarBis + " €");
this.FindAndReplace(wordApp, "<date>", DateTime.Now.ToShortDateString());
try{ this.FindAndReplace(wordApp, "<clubAdress>", dtclubAdress.Rows[0]["clubAdress"].ToString()); }catch { }
try{ this.FindAndReplace(wordApp, "<clubName>", dtclubAdress.Rows[0]["clubName"].ToString()); } catch { }
try { this.FindAndReplace(wordApp, "<objet>", dtclubAdress.Rows[0]["clubObject"].ToString()); } catch { }
}
else
{
MessageBox.Show("File not found");
}
myWordDoc.SaveAs2(ref SaveAs, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);
myWordDoc.ExportAsFixedFormat(mainPath + @"\Cerfa" + idUser + ".pdf", word.WdExportFormat.wdExportFormatPDF);
myWordDoc.Close();
wordApp.Quit();
MessageBox.Show("Created");
coInsert.Close();
}