private void Add_Button_Click(object sender, RoutedEventArgs e)
{
string nameInput = name.Text;
string pinInput = pin.Text;
string dateInput = date.Text;
string timeInput = time.Text;
string inoutInput = inout.Text;
string inoutNum = null;
string statusBefore = null;
string dateBefore = null;
if (nameInput.Length != 0 && dateInput.Length != 0 && timeInput != null && inoutInput.Length != 0)
{
int spaceIndex = nameInput.IndexOf(" ");
Console.WriteLine("-->" + nameInput);
nameInput = nameInput.Substring(0, 1) + nameInput.Substring(spaceIndex);
//convert inout to 1 or 0
if (inoutInput == "In")
{
inoutNum = "1";
}
else
{
inoutNum = "0";
}
//combine date and time string and format for MySQL
string dt = Convert.ToDateTime(dateInput + " " + timeInput).ToString("yyyy-MM-dd HH:mm:ss");
string dt2 = Convert.ToDateTime(dt).AddMinutes(1).ToString("yyyy-MM-dd HH:mm:ss");
Console.WriteLine(dt + " --> " + dt2);
if (this.openConn())
{
MySqlDataReader dr1 = null;
//query to check if date and time for user in/out already exists
string query1 = "Use crst_dash; \n"
+ "SELECT name, time, status FROM a_staff_time WHERE pin = \"" + pinInput
+ "\" AND time < \"" + dt2 + "\" ORDER BY time DESC LIMIT 1;";
MySqlCommand cmd1 = new MySqlCommand(query1, myconn);
try
{
dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
statusBefore = dr1[2].ToString();
dateBefore = dr1[1].ToString();
}
dr1.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
DateTime d = Convert.ToDateTime(dateInput);
if (dateBefore != null && statusBefore != null)
{
int i = dateBefore.IndexOf(" ");
DateTime dBefore = Convert.ToDateTime(dateBefore.Substring(0, i));
if (statusBefore == inoutNum && DateTime.Compare(dBefore, d) == 0)
{
snackbar.MessageQueue.Enqueue("Cannot add punch with same status as previous.");
return;
}
}
// query to insert new punch
string query2 = "USE crst_dash; \n"
+ "INSERT INTO a_staff_time (name, pin, time, status) VALUES (\""
+ nameInput + "\", \"" + pinInput + "\", \"" + dt + "\", \"" + inoutNum + "\");";
Console.WriteLine(query2);
MySqlCommand cmd2 = new MySqlCommand(query2, myconn);
try
{
cmd2.ExecuteNonQuery();
// check if following punch is different or different day
string query3 = "USE crst_dash; \n"
+ "SELECT time, status FROM a_staff_time WHERE pin = \""
+ pinInput + "\" AND time >= \"" + dt2 + "\" ORDER BY time ASC LIMIT 1;";
Console.WriteLine(query3);
MySqlDataReader dr3 = null;
MySqlCommand cmd3 = new MySqlCommand(query3, myconn);
Boolean sameStatusAfter = true;
Boolean sameDayAfter = true;
try
{
dr3 = cmd3.ExecuteReader();
while (dr3.Read())
{
if (dr3[1].ToString() != inoutNum)
{
sameStatusAfter = false;
}
Console.WriteLine("-->" + Convert.ToDateTime(dr3[0]).Date);
Console.WriteLine("-->" + d.Date);
if (DateTime.Compare(Convert.ToDateTime(dr3[0]).Date, d.Date) != 0)
{
sameDayAfter = false;
}
}
dr3.Close();
Console.WriteLine(sameStatusAfter + "\n" + sameDayAfter);
if (sameStatusAfter && sameDayAfter)
{
string dt3 = Convert.ToDateTime(dt2).AddDays(1).Date.ToString("yyyy-MM-dd HH-mm-ss");
// query to change following punches, if the status after the added status are the same
string query4 = "USE crst_dash; \n"
+ "UPDATE a_staff_time SET status = IF(status = 1, 0, 1) WHERE pin = \""
+ pinInput + "\" AND time >= \"" + dt2 + "\" AND time < \"" + dt3 + "\";";
Console.WriteLine("in 4\n" + query4);
MySqlCommand cmd4 = new MySqlCommand(query4, myconn);
try
{
cmd4.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
}
else
{
snackbar.MessageQueue.Enqueue("ya good");
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
tmeclk.FillDatagrid();
tmeclk.CheckForMissedPunches();
tmeclk.snackbar.MessageQueue.Enqueue("Punch Added");
tmeclk.grid2.Children.Remove(this);
tmeclk.initializeAddPunch();
this.closeConn();
}
}
else
{
snackbar.MessageQueue.Enqueue("Please enter all fields.");
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
string pinInput = selectedPunch.pin;
string dateInput = date.Text;
string timeInput = time.Text;
string inoutInput = inout.Text;
string inoutNum = null;
string dt = null;
Boolean statusChanged = true;
Boolean dateChanged = false;
Console.WriteLine(inoutInput.Length);
//convert inout to 1 or 0
if (inoutInput.Length != 0)
{
Console.WriteLine("in1");
if (inoutInput == "In")
{
inoutNum = "1";
}
else
{
inoutNum = "0";
}
if (selectedPunch.status == inoutNum)
{
statusChanged = false;
}
}
else
{
Console.WriteLine("in2");
statusChanged = false;
if (selectedPunch.status.Equals("OUT"))
{
inoutNum = "0";
}
else
{
inoutNum = "1";
}
}
if (dateInput.Length != 0)
{
dateChanged = true;
}
string dteSel = selectedPunch.date;
string timeSel = selectedPunch.time;
//int i = dteSel.IndexOf(" ");
//combine date and time string and format for MySQL
if (dateInput.Length != 0 && timeInput != null) //if date and input is given
{
dt = Convert.ToDateTime(dateInput + " " + timeInput).ToString("yyyy-MM-dd HH:mm:ss");
}
else if (dateInput.Length != 0 && timeInput == null) //if only date is given, get old time
{
dt = Convert.ToDateTime(dateInput + " " + timeSel).ToString("yyyy-MM-dd HH:mm:ss");
}
else if (dateInput.Length == 0 && timeInput != null) // if only time is give, get old date
{
//string date = dteSel.Substring(0, i);
Console.WriteLine(dteSel);
Console.WriteLine(timeInput);
dt = Convert.ToDateTime(dteSel + " " + timeInput).ToString("yyyy-MM-dd HH:mm:ss");
}
else // if neither date or time is given, get old date and time
{
dt = Convert.ToDateTime(dteSel + " " + timeSel).ToString("yyyy-MM-dd HH:mm:ss");
}
// minute after
string dt2 = Convert.ToDateTime(dt).AddMinutes(1).ToString("yyyy-MM-dd HH:mm:ss");
if (this.openConn())
{
// if date is being changed first change the statuses of the punches after in the current date
if (dateChanged)
{
string dteSel2 = Convert.ToDateTime(dteSel + " " + timeSel).AddMinutes(1).ToString("yyyy-MM-dd HH:mm:ss");
string dteSel3 = Convert.ToDateTime(dteSel).AddDays(1).Date.ToString("yyyy-MM-dd HH:mm:ss");
string query0 = "USE crst_dash; \n"
+ "UPDATE a_staff_time SET status = IF(status = 1, 0, 1) WHERE pin = \""
+ pinInput + "\" AND time >= \"" + dteSel2 + "\" AND time < \"" + dteSel3 + "\" ;";
Console.WriteLine(query0);
MySqlCommand cmd0 = new MySqlCommand(query0, myconn);
try
{
cmd0.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
}
// query to update the existing punch
string query = "USE crst_dash; \n"
+ "UPDATE a_staff_time SET time =\"" + dt + "\", status = \"" + inoutNum
+ "\" WHERE id = \"" + selectedPunch.id + "\";";
Console.WriteLine(query);
MySqlCommand cmd = new MySqlCommand(query, myconn);
try
{
cmd.ExecuteNonQuery();
if (statusChanged || dateChanged)
{
// check status after
string query2 = "USE crst_dash; \n"
+ "SELECT time, status FROM a_staff_time WHERE pin = \""
+ pinInput + "\" AND time >= \"" + dt2 + "\" ORDER BY time ASC LIMIT 1;";
Console.WriteLine(query2);
MySqlDataReader dr2 = null;
MySqlCommand cmd2 = new MySqlCommand(query2, myconn);
Boolean sameStatusAfter = true;
Boolean sameDayAfter = true;
DateTime d = Convert.ToDateTime(dt);
try
{
dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
Console.WriteLine(dr2[0] + " " + dr2[1]);
if (dr2[1].ToString() != inoutNum)
{
Console.WriteLine(dr2[1].ToString());
Console.WriteLine(inoutNum);
sameStatusAfter = false;
}
if (DateTime.Compare(Convert.ToDateTime(dr2[0]).Date, d.Date) != 0)
{
sameDayAfter = false;
}
}
dr2.Close();
Console.WriteLine(sameStatusAfter);
Console.WriteLine(sameDayAfter);
if (sameStatusAfter && sameDayAfter)
{
// day after
string dt3 = Convert.ToDateTime(dt2).AddDays(1).Date.ToString("yyyy-MM-dd HH-mm-ss");
// change statuses after or check for statuses before
string query3 = "USE crst_dash; \n"
+ "UPDATE a_staff_time SET status = IF(status = 1, 0, 1) WHERE pin = \""
+ pinInput + "\" AND time >= \"" + dt2 + "\" AND time < \"" + dt3 + "\" ;";
Console.WriteLine(query3);
MySqlCommand cmd3 = new MySqlCommand(query3, myconn);
try
{
cmd3.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
}
tmeclk.FillDatagrid();
tmeclk.CheckForMissedPunches();
tmeclk.snackbar.MessageQueue.Enqueue("Punch Updated");
tmeclk.grid2.Children.Remove(this);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
}
}
this.closeConn();
}