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); } } }