private void RetrieveInfo_Click(object sender, EventArgs e)
{
clear();
String contactno = null;
String section_id = null;
String remarks = null;
String statuss = null;
String aaaa = null;
String last_name = "";
String first_name = "";
String middle_name = "";
MySqlConnection con = new MySqlConnection(Connection.GetConnectionStr());
#region Get Account Status
String query333 = "select account_id, account_status from rfid where rfid_code = " + textBox1.Text + "";
con.Open();
MySqlCommand cmd3 = new MySqlCommand(query333, con);
cmd3.CommandTimeout = 500;
MySqlDataReader dr2 = cmd3.ExecuteReader();
if (dr2.Read())
{
GlobalVar.account_status = (dr2["account_status"].ToString());
GlobalVar.account_id = (dr2["account_id"].ToString());
}
dr2.Close();
con.Close();
#endregion
#region Get Week Day
String query222 = "SELECT WEEKDAY(CURDATE())";
con.Open();
MySqlCommand cmd4 = new MySqlCommand(query222, con);
cmd4.CommandTimeout = 500;
MySqlDataReader dr4 = cmd4.ExecuteReader();
if (dr4.Read())
{
GlobalVar.weekday = (dr4["WEEKDAY(CURDATE())"].ToString());
}
dr4.Close();
con.Close();
#endregion
#region if already time in
string query555 = "select * from attendance where timein = curdate() and account_id = " + GlobalVar.account_id + "";
con.Open();
MySqlCommand cmd555 = new MySqlCommand(query555, con);
cmd555.CommandTimeout = 500;
try
{
MySqlDataReader dr222 = cmd555.ExecuteReader();
if (dr2.Read())
{
aaaa = (dr222["account_id"].ToString());
}
dr222.Close();
con.Close();
statuss = "Already";
}
catch
{
statuss = "";
}
#endregion
if (GlobalVar.account_status == "Student")
{
String timein = "";
String currenttime = "";
#region compare weekday
String queryweek = "select '" + GlobalVar.timein + "' from timein where account_id = '" + GlobalVar.account_id + "' and '" + GlobalVar.weekday + "' = '" + GlobalVar.weekday + "'";
con.Open();
MySqlCommand cmd5 = new MySqlCommand(queryweek, con);
cmd5.CommandTimeout = 500;
MySqlDataReader dr6 = cmd5.ExecuteReader();
if (dr6.Read())
{
timein = (dr6[GlobalVar.timein].ToString());
currenttime = DateTime.Now.ToString("HH");
GlobalVar.timein = Int32.Parse(timein);
GlobalVar.currenttime = Int32.Parse(currenttime);
}
dr6.Close();
con.Close();
#endregion
FindImage();
#region query first
String query = "select rfid.account_id, rfid.account_status, student.lrn, student.last_name, student.first_name,student.middle_name, student.section_id, student.semester, student.remarks, student.contactno from rfid inner join student on rfid.account_id = student.account_id where rfid_code = " + textBox1.Text + "";
MySqlCommand cmd1 = new MySqlCommand(query, con);
cmd1.CommandTimeout = 500;
con.Open();
MySqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
last_name = (dr1["last_name"].ToString());
first_name = (dr1["first_name"].ToString());
middle_name = (dr1["middle_name"].ToString());
lbllastname.Text = last_name + ", " + first_name + " " + middle_name;
lbllrn.Text = (dr1["lrn"].ToString());
account_status = (dr1["account_status"].ToString());
lblsemester.Text = (dr1["semester"].ToString()) + " Semester";
remarks = (dr1["remarks"].ToString());
lblstatus.Text = remarks;
section_id = (dr1["section_id"].ToString());
account_id = (dr1["account_id"].ToString());
contactno = (dr1["contactno"].ToString());
}
dr1.Close();
con.Close();
#endregion
#region query 2
if (section_id == "")
{
}
else
{
String query1 = "select * from section where section_id = " + section_id + "";
con.Open();
MySqlCommand cmd2 = new MySqlCommand(query1, con);
MySqlDataReader dr5 = cmd2.ExecuteReader();
if (dr5.Read())
{
lblgrade.Text = (dr5["grade"].ToString());
lblsection.Text = (dr5["section"].ToString());
lbltrack.Text = (dr5["track"].ToString());
lblstrand.Text = (dr5["strand"].ToString());
lbladviser.Text = (dr5["adviser"].ToString());
}
dr5.Close();
lblcurdate.Text = DateTime.Now.ToString("hh:mm tt");
}
#endregion
}
else if (GlobalVar.account_status == "Teacher")
{
String timein = "";
String currenttime = "";
#region compare weekday
String queryweek = "select '" + GlobalVar.timein + "' from timein where account_id = '" + GlobalVar.account_id + "' and '" + GlobalVar.weekday + "' = '" + GlobalVar.weekday + "'";
con.Open();
MySqlCommand cmd5 = new MySqlCommand(queryweek, con);
cmd5.CommandTimeout = 500;
MySqlDataReader dr6 = cmd5.ExecuteReader();
if (dr6.Read())
{
timein = (dr6[GlobalVar.timein].ToString());
currenttime = DateTime.Now.ToString("HH");
GlobalVar.timein = Int32.Parse(timein);
GlobalVar.currenttime = Int32.Parse(currenttime);
}
dr6.Close();
con.Close();
#endregion
FindImageTeacher();
#region query first
String queryteacher = "select rfid.account_id, rfid.account_status, concat(teacher.first_name, teacher.middle_name, teacher.last_name) as full_name, teacher.track, teacher.strand, teacher.employee_number, teacher.position, teacher.ancillary_assignment, teacher.contactno from rfid inner join teacher on rfid.account_id = teacher.account_id where rfid_code = " + textBox1.Text + "";
MySqlCommand cmd1 = new MySqlCommand(queryteacher, con);
cmd1.CommandTimeout = 500;
con.Open();
MySqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
account_id = (dr1["account_id"].ToString());
last_name = (dr1["last_name"].ToString());
first_name = (dr1["first_name"].ToString());
middle_name = (dr1["middle_name"].ToString());
lbllastname.Text = last_name + ", " + first_name + " " + middle_name;
lbllrn.Text = (dr1["employee_number"].ToString());
contactno = (dr1["contactno"].ToString());
lbltrack.Text = (dr1["track"].ToString());
lblstrand.Text = (dr1["strand"].ToString());
lblcurdate.Text = DateTime.Now.ToString("hh:mm tt");
lbladviser.Text = (dr1["ancillary_assignment"].ToString());
lblgrade.Text = (dr1["position"].ToString());
}
dr1.Close();
con.Close();
#endregion
}
if (statuss == null)
{
if (GlobalVar.account_status == "Student")
{
if (GlobalVar.timein <= GlobalVar.currenttime)
{
String status = "on time";
this.ActiveControl = textBox1;
InsertData(account_id, status, first_name, middle_name, last_name);
lblontime.Text = "On Time";
lblontime.ForeColor = System.Drawing.Color.Green;
}
else
{
String status = "late";
this.ActiveControl = textBox1;
InsertData(account_id, status, first_name, middle_name, last_name);
lblontime.Text = "Late";
lblontime.ForeColor = System.Drawing.Color.Red;
}
PresentStudent();
viewAttendance a = new viewAttendance();
MemoryStream ms = new MemoryStream();
PictureStudent.Image.Save(ms, PictureStudent.Image.RawFormat);
byte[] aaa = ms.GetBuffer();
ms.Close();
MemoryStream msa = new MemoryStream(aaa);
a.PictureStudent.Image = Image.FromStream(msa);
a.lbllrn.Text = lbllrn.Text;
a.lblstrand.Text = lblstrand.Text;
a.lbltrack.Text = lbltrack.Text;
a.lblcurdate.Text = DateTime.Now.ToString();
String time = lblontime.Text;
if (time == "On Time")
{
a.lblontime.Text = "On Time";
a.lblontime.ForeColor = System.Drawing.Color.Green;
}
else if (time == "Late")
{
a.lblontime.Text = "Late";
a.lblontime.ForeColor = System.Drawing.Color.Red;
}
flowLayoutPanel1.Controls.Add(a);
}
else if (GlobalVar.account_status == "Teacher")
{
if (GlobalVar.timein <= GlobalVar.currenttime)
{
String status = "on time";
this.ActiveControl = textBox1;
InsertDataTeacher(account_id, status, first_name, middle_name, last_name);
lblontime.Text = "On Time";
lblontime.ForeColor = System.Drawing.Color.Green;
}
else
{
String status = "late";
this.ActiveControl = textBox1;
InsertDataTeacher(account_id, status, first_name, middle_name, last_name);
lblontime.Text = "Late";
lblontime.ForeColor = System.Drawing.Color.Red;
}
PresentTeacher();
viewAttendance a = new viewAttendance();
MemoryStream ms = new MemoryStream();
PictureStudent.Image.Save(ms, PictureStudent.Image.RawFormat);
byte[] aaa = ms.GetBuffer();
ms.Close();
MemoryStream msa = new MemoryStream(aaa);
a.PictureStudent.Image = Image.FromStream(msa);
a.lbllrn.Text = lbllrn.Text;
a.lblstrand.Text = lblstrand.Text;
a.lbltrack.Text = lbltrack.Text;
a.lblcurdate.Text = DateTime.Now.ToString();
String time = lblontime.Text;
if (time == "Present")
{
a.lblontime.Text = "Time In";
a.lblontime.ForeColor = System.Drawing.Color.Green;
}
else if (time == "Time Out")
{
a.lblontime.Text = "Time Out";
a.lblontime.ForeColor = System.Drawing.Color.Red;
}
flowLayoutPanel1.Controls.Add(a);
}
}
}
void Retrieve()
{
String last_name = null;
String first_name = null;
String middle_name = null;
flowLayoutPanel1.Controls.Clear();
MySqlConnection con = new MySqlConnection(Connection.GetConnectionStr());
con.Open();
String query = "select * from attendance where timein = curdate() order by attendance_id DESC";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
viewAttendance a = new viewAttendance();
try
{
byte[] img = (byte[])(dt.Rows[i]["img"]);
MemoryStream ms = new MemoryStream(img);
a.PictureStudent.Image = Image.FromStream(ms);
last_name = dt.Rows[i]["last_name"].ToString();
first_name = dt.Rows[i]["first_name"].ToString();
middle_name = dt.Rows[i]["middle_name"].ToString();
a.lbllastname.Text = last_name + ", " + first_name + " " + middle_name;
a.lbllrn.Text = dt.Rows[i]["number"].ToString();
a.lblstrand.Text = dt.Rows[i]["strand"].ToString();
a.lbltrack.Text = dt.Rows[i]["track"].ToString();
a.lblcurdate.Text = dt.Rows[i]["timein"].ToString();
String time = dt.Rows[i]["status"].ToString();
if (time == "Present")
{
a.lblontime.Text = "On Time";
a.lblontime.ForeColor = System.Drawing.Color.Green;
}
else if (time == "Time Out")
{
a.lblontime.Text = "Time Out";
a.lblontime.ForeColor = System.Drawing.Color.Red;
}
flowLayoutPanel1.Controls.Add(a);
}
catch
{
last_name = dt.Rows[i]["last_name"].ToString();
first_name = dt.Rows[i]["first_name"].ToString();
middle_name = dt.Rows[i]["middle_name"].ToString();
a.lbllastname.Text = last_name + ", " + first_name + " " + middle_name;
a.lbllrn.Text = dt.Rows[i]["number"].ToString();
a.lblstrand.Text = dt.Rows[i]["strand"].ToString();
a.lbltrack.Text = dt.Rows[i]["track"].ToString();
a.lblstatus.Text = dt.Rows[i]["remarks"].ToString();
a.lblcurdate.Text = dt.Rows[i]["timein"].ToString();
String time = dt.Rows[i]["status"].ToString();
if (time == "Late")
{
a.lblontime.Text = "Late";
a.lblontime.ForeColor = System.Drawing.Color.Red;
}
else if (time == "Present")
{
a.lblontime.Text = "On Time";
a.lblontime.ForeColor = System.Drawing.Color.Green;
}
else if (time == "Time Out")
{
a.lblontime.Text = "Time Out";
a.lblontime.ForeColor = System.Drawing.Color.Red;
}
flowLayoutPanel1.Controls.Add(a);
}
}
}