private void MenuItem4_Click(object sender, RoutedEventArgs e)
{
CalcuateSalary go = new CalcuateSalary();
go.Show();
}
private void InsertSalary_Click(object sender, RoutedEventArgs e)
{
dataGrid.SelectedIndex = 0;
con.Open();
string readersql1 = "select sMonth from salary WHERE sMonth='" + Convert.ToDateTime(datePick2.Text).ToString("yyyy-MM-dd") + "'";
MySqlCommand commandnaja = new MySqlCommand(readersql1, con);
MySqlDataReader readdata = commandnaja.ExecuteReader();
while (readdata.Read())
{
datecheck = Convert.ToDateTime(readdata["sMonth"]).ToString("dd/MM/yyyy");
var myDate = DateTime.Parse(datecheck);
var newDate = myDate.AddYears(-543);
datecheck = newDate.ToShortDateString();
}
con.Close();
if (datePick2.Text == datecheck)
{
MessageBoxResult result1 = MessageBox.Show("คุณมีข้อมูลนี้อยู่แล้วต้องการแก้ไขหรือไม่?", "รอคำยืนยัน", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (result1 != MessageBoxResult.Yes)
{
return;
}
else
{
for (int i = 0; i < dataGrid.Items.Count; i++)
{
object item = dataGrid.Items[i];
showid = (dataGrid.SelectedCells[1].Column.GetCellContent(item) as TextBlock).Text;
showname = (dataGrid.SelectedCells[2].Column.GetCellContent(item) as TextBlock).Text;
showlastname = (dataGrid.SelectedCells[3].Column.GetCellContent(item) as TextBlock).Text;
con.Open();
string readersql2 = "select * from checkwork WHERE (checkDate BETWEEN '" + Convert.ToDateTime(datePick1.Text).ToString("yyyy-MM-dd") + "' AND '" + Convert.ToDateTime(datePick2.Text).ToString("yyyy-MM-dd") + "') AND (checkID='" + showid + "')";
MySqlCommand commandna = new MySqlCommand(readersql2, con);
MySqlDataReader readerdata = commandna.ExecuteReader();
while (readerdata.Read())
{
value += Convert.ToInt32(readerdata["checkPay"]);
getvalue = value;
}
value = 0;
con.Close();
con.Open();
MySqlCommand cmd2 = new MySqlCommand();
cmd2.Connection = con;
cmd2.CommandText = @"UPDATE salary SET sMonth='" + Convert.ToDateTime(datePick2.Text).ToString("yyyy-MM-dd") + "',empID='" + showid + "',sName='" + showname + "',sLastname='" + showlastname + "',sSalary='" + getvalue + "',sSave=now() WHERE (sMonth='" + Convert.ToDateTime(datePick2.Text).ToString("yyyy-MM-dd") + "') AND (empID='" + showid + "')";
cmd2.ExecuteNonQuery();
con.Close();
}
MessageBox.Show("แก้ไขเรียบร้อยแล้ว!!!", "เรียบร้อย");
CalcuateSalary go = new CalcuateSalary();
this.Close();
go.Show();
}
}
else
{
MessageBoxResult result = MessageBox.Show("คุณต้องการบันทึกใช่ไหม?", "รอคำยืนยัน", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (result != MessageBoxResult.Yes)
{
return;
}
else
{
using (MySqlCommand cmd1 = new MySqlCommand("SELECT COUNT(*) FROM employee", con))
{
con.Open();
countrow = Convert.ToInt32(cmd1.ExecuteScalar());
con.Close();
}
for (int i = 0; i < dataGrid.Items.Count; i++)
{
object item = dataGrid.Items[i];
showid = (dataGrid.SelectedCells[1].Column.GetCellContent(item) as TextBlock).Text;
showname = (dataGrid.SelectedCells[2].Column.GetCellContent(item) as TextBlock).Text;
showlastname = (dataGrid.SelectedCells[3].Column.GetCellContent(item) as TextBlock).Text;
con.Open();
string readersql2 = "select * from checkwork WHERE (checkDate BETWEEN '" + Convert.ToDateTime(datePick1.Text).ToString("yyyy-MM-dd") + "' AND '" + Convert.ToDateTime(datePick2.Text).ToString("yyyy-MM-dd") + "') AND (checkID='" + showid + "')";
MySqlCommand commandna = new MySqlCommand(readersql2, con);
MySqlDataReader readerdata = commandna.ExecuteReader();
while (readerdata.Read())
{
value += Convert.ToInt32(readerdata["checkPay"]);
getvalue = value;
}
value = 0;
con.Close();
countloop += 1;
if (countloop == countrow)
{
break;
}
con.Open();
MySqlCommand cmd2 = new MySqlCommand();
cmd2.Connection = con;
cmd2.CommandText = @"INSERT INTO salary (sMonth,empID,sName,sLastname,sSalary,sSave) VALUES (@Date,@ID,@NAME,@LASTNAME,@SALARY,now())";
cmd2.Parameters.AddWithValue("@Date", Convert.ToDateTime(datePick2.Text).ToString("yyyy-MM-dd"));
cmd2.Parameters.AddWithValue("@ID", showid);
cmd2.Parameters.AddWithValue("@NAME", showname);
cmd2.Parameters.AddWithValue("@LASTNAME", showlastname);
cmd2.Parameters.AddWithValue("@SALARY", getvalue);
cmd2.ExecuteNonQuery();
cmd2.Parameters.Clear();
con.Close();
}
MessageBox.Show("บันทึกเรียบร้อยแล้ว");
CalcuateSalary go = new CalcuateSalary();
this.Close();
go.Show();
}
}
}