private void button2_Click(object sender, EventArgs e)
{
panel1.Height = button2.Height;
panel1.Top = button2.Top;
UCHome getToken = new UCHome();
if (button2.Text == "Gestion des bordereaux")
{
if (comptGetdataslip == 0)
{
ucUser1.getDataSlip(getToken.returnToken());
comptGetdataslip++;
}
ucUser1.BringToFront();
}
else if (button2.Text == "Adhérents")
{
if (comptGetdataslip == 0)
{
ucTreasure2.allTreasurePart();
comptGetdataslip++;
}
ucTreasure2.BringToFront();
}
else if (button2.Text == "Administration")
{
ucAdmin1.BringToFront();
}
}
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 button1_Click(object sender, EventArgs e)
{
panel1.Height = button1.Height;
panel1.Top = button1.Top;
UCHome getToken = new UCHome();
ucAccount1.getData(getToken.returnToken());
ucAccount1.BringToFront();
}
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();
}
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");
}
}
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();
}
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 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();
}
}
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();
}