public frmStudentHandler(string id = "")
{
InitializeComponent();
DataAccess = new clsData_Access("server=127.0.0.1;user id=root;password=;database=sms_app");
stud_id = id;
lblNotification.Text = "";
DS = DataAccess.executeQuery("select yr_details from tbl_yr where yr_active=1");
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
cboYr.Items.Add(DS.Tables[0].Rows[i][0].ToString());
}
cboYr.SelectedIndex = 0;
DS = DataAccess.executeQuery("select sec_details from tbl_sec where sec_active=1");
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
cboSec.Items.Add(DS.Tables[0].Rows[i][0].ToString());
}
cboSec.SelectedIndex = 0;
if (id != "")
{
this.Text = "Edit Student Information";
lblTitle.Text = "Edit Student Information";
DS = DataAccess.executeQuery("select * from view_students_information where stud_id='" + id + "'");
tbStudIDNumber.Text = DS.Tables[0].Rows[0][1].ToString();
tbFn.Text = DS.Tables[0].Rows[0][2].ToString();
tbMn.Text = DS.Tables[0].Rows[0][3].ToString();
tbLn.Text = DS.Tables[0].Rows[0][4].ToString();
tbPCNumber.Text = DS.Tables[0].Rows[0][5].ToString();
cboYr.SelectedItem = DS.Tables[0].Rows[0][6].ToString();
cboSec.SelectedItem = DS.Tables[0].Rows[0][7].ToString();
old_rfid = DS.Tables[0].Rows[0][8].ToString();
lblRFID.Text = DS.Tables[0].Rows[0][8].ToString();
cboRFTagID.SelectedItem = DS.Tables[0].Rows[0][8].ToString();
//cboRFTagID.Items.Add(DS.Tables[0].Rows[0][8].ToString());
try
{
img = (Byte[])(DS.Tables[0].Rows[0][12]);
ms = new System.IO.MemoryStream(img);
pic.Image = Image.FromStream(ms);
img = null;
}
catch { }
}
}
public frmUserHandler(string id = "")
{
InitializeComponent();
DataAccess = new clsData_Access("server=127.0.0.1;user id=root;password=;database=sms_app");
if (id != "")
{
user_id = id;
if (!DataAccess.dbConnected)
{
MessageBox.Show(this, "Unable to connect to database.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
DS = DataAccess.executeQuery("select * from tbl_sys_user where user_id='" + user_id + "'");
tbUn.Text = DS.Tables[0].Rows[0][1].ToString();
tbPw.Text = DS.Tables[0].Rows[0][2].ToString();
tbFn.Text = DS.Tables[0].Rows[0][3].ToString();
tbMn.Text = DS.Tables[0].Rows[0][4].ToString();
tbLn.Text = DS.Tables[0].Rows[0][5].ToString();
cboRole.SelectedItem = DS.Tables[0].Rows[0][6].ToString();
}
else
{
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
try
{
ds = dataAccess.executeQuery("select * from tbl_sys_user where user_un ='" + txtUsername.Text.Trim() + "' and user_pw='" + txtPassword.Text.Trim() + "' and user_active=1");
if (ds.Tables[0].Rows.Count > 0)
{ // row that is greater than 1 means there is an existing user in the given username and password
string id, name, role;
id = ds.Tables[0].Rows[0][0].ToString();
name = ds.Tables[0].Rows[0][3].ToString() + " " + ds.Tables[0].Rows[0][4].ToString() + " " + ds.Tables[0].Rows[0][5].ToString();
role = ds.Tables[0].Rows[0][6].ToString();
txtUsername.Clear();
txtPassword.Clear();
this.Hide();
this.WindowState = FormWindowState.Minimized;
Main = new frmMain(this, id, name, role, frm);
Main.Show();
}
else
{
MessageBox.Show(this, "Invalid username or password.", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}catch (Exception ex) {
MessageBox.Show(this, ex.Message.ToString(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
DS = DA.executeQuery("select user_pw from tbl_sys_user where user_id='" + id + "'");
if (DS.Tables[0].Rows.Count > 0)
{
if (DS.Tables[0].Rows[0][0].ToString() == tbOldPass.Text)
{
if (tbNewPass.Text == tbConfirmPass.Text)
{
xx = DA.ExecuteCommand("update tbl_sys_user set user_pw='" + tbNewPass.Text + "' where user_id='" + id + "'");
MessageBox.Show(this, "Password successfully changed." + xx.ToString(), "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show(this, "New password and confirmaiton didn't matched.", "Incorrect", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show(this, "Old password is incorrect.", "Incorrect", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
private void btnTagSave_Click(object sender, EventArgs e)
{
if (tbTagID.Text != "")
{
clsData_Access DataAccess = new clsData_Access("server=127.0.0.1;user id=root;password=;database=sms_app");
DataSet ds = new DataSet();
ds = DataAccess.executeQuery("select * from tbl_rfid_tag where rfid_tag_id ='" + tbTagID.Text + "' and rfid_tag_active=1");
if (ds.Tables[0].Rows.Count > 0)
{
MessageBox.Show(this, "Tag is currently existing and active.", "Existing", MessageBoxButtons.OK, MessageBoxIcon.Information);
tbTagID.Text = "";
tmrTagReader.Enabled = true;
return;
}
if (DataAccess.dbConnected)
{
//MessageBox.Show(this, "Connection successfully established.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (DataAccess.ExecuteProcedureCommand_Insert_Tag(tbTagID.Text))
{
MessageBox.Show(this, "Tag successfully saved", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show(this, "Tag not saved", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
tbTagID.Text = "";
//tmrTagReader.Enabled = true;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
//compare if the value is null
if (tbFn.Text == "" || tbLn.Text == "" || tbMn.Text == "" || tbPw.Text == "" || tbUn.Text == "" || cboRole.SelectedItem.ToString() == "")
{
MessageBox.Show(this, "Please fill all required fields.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
if (user_id == "") //for adding new user
{
DS = DataAccess.executeQuery("select * from tbl_sys_user where user_un ='" + tbUn.Text.Trim() + "' and user_active=1");
if (DS.Tables[0].Rows.Count > 0)
{
MessageBox.Show(this, "Usename already in used. Please change the username.", "Existing", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
if (DataAccess.ExecuteProcedureCommand_Handle_User(tbUn.Text.Trim(), tbPw.Text.Trim(), tbFn.Text.Trim(), tbMn.Text.Trim(), tbLn.Text.Trim(), cboRole.SelectedItem.ToString()))
{
MessageBox.Show(this, "User successfully added", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show(this, "Error in adding new user.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else //for editing an existing user
{
DS = DataAccess.executeQuery("select * from tbl_sys_user where user_un ='" + tbUn.Text.Trim() + "' and user_active=1");
if (DS.Tables[0].Rows.Count > 0)
{
MessageBox.Show(this, "Usename already in used. Please change the username.", "Existing", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
if (DataAccess.ExecuteProcedureCommand_Handle_User(tbUn.Text.Trim(), tbPw.Text.Trim(), tbFn.Text.Trim(), tbMn.Text.Trim(), tbLn.Text.Trim(), cboRole.SelectedItem.ToString(), user_id))
{
MessageBox.Show(this, "User successfully edited", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show(this, "Error in editing user.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void lblRFID_TextChanged(object sender, EventArgs e)
{
try
{
DS = DataAccess.executeQuery("select rfid_id from tbl_rfid_tag where rfid_tag_id='" + lblRFID.Text + "' and rfid_tag_active='1'");
if (DS.Tables[0].Rows.Count > 0)
{
lblMessage.Text = "Tag is already in used.\nSelect another";
btnOk.Enabled = false;
}
else
{
lblMessage.Text = "";
btnOk.Enabled = true;
}
}
catch {
}
}
private void bgwBroadcaster_DoWork(object sender, DoWorkEventArgs e)
{
Thread.Sleep((1000 * 60) * 10);
if (DateTime.Now.Hour < 4)
{
return;
}
dsMessage = da.executeQuery("select m_id, m_content, m_recipient from tbl_message where issent=0 and m_senddate='" + String.Format("{0:yyyy/MM/dd}", DateTime.Now.Date) + "'");
if (dsMessage.Tables.Count < 1)
{
return;
}
//MessageBox.Show(dsMessage.Tables[0].Rows.Count.ToString());
if (dsMessage.Tables[0].Rows.Count > 0)
{
DataTable dt = dsMessage.Tables[0];
for (int i = 0; i < dsMessage.Tables[0].Rows.Count; i++)
{
string[] years = dt.Rows[i][2].ToString().Split(';');
string message = dt.Rows[i][1].ToString();
string m_id = dt.Rows[i][0].ToString();
if (dt.Rows[i][2].ToString() == "All")
{
dsStudent = da.executeQuery("select stud_ln, stud_parent_number from tbl_student_info join tbl_yr on tbl_student_info.yr_id = tbl_yr.yr_id and stud_active='1'");
if (dsStudent.Tables.Count < 1)
{
return;
}
if (dsStudent.Tables[0].Rows.Count > 0)
{
//MessageBox.Show(dsStudent.Tables[0].Rows.Count.ToString());
foreach (DataRow drStud in dsStudent.Tables[0].Rows)
{
//MessageBox.Show(String.Format("{0} {1}", drStud[1].ToString(), message));
int a = AnnounceSender.sendmsg(drStud[1].ToString(), message);
//MessageBox.Show(a.ToString());
if (a < 0) // seems no internet connection
{
//Thread.Sleep(1000 * 100);
//bgwBroadcaster.CancelAsync();
return;
}
Thread.Sleep(1500);
}
}
}
else
{
foreach (string year in years)
{
if (year != "")
{
dsStudent = da.executeQuery("select stud_ln, stud_parent_number from tbl_student_info join tbl_yr on tbl_student_info.yr_id = tbl_yr.yr_id where yr_details='" + year + "' and stud_active='1'");
if (dsStudent.Tables[0].Rows.Count > 0)
{
//MessageBox.Show(dsStudent.Tables[0].Rows.Count.ToString());
foreach (DataRow drStud in dsStudent.Tables[0].Rows)
{
//MessageBox.Show(drStud[0].ToString());
int a = AnnounceSender.sendmsg(drStud[1].ToString(), message);
//MessageBox.Show(a.ToString());
if (a < 0) // seems no internet connection
{
//Thread.Sleep(1000);
//bgwBroadcaster.CancelAsync();
return;
}
Thread.Sleep(1500);
}
}
}
}
}
da.ExecuteCommand("update tbl_message set issent='1' where m_id='" + m_id + "'");
}
}
Thread.Sleep((1000 * 60) * 2);
}
private void btnSave_Click(object sender, EventArgs e)
{
DS = DataAccess.executeQuery("select stud_id from tbl_student_info where stud_id_number='" + tbStudIDNumber.Text.Trim() + "'");
if (/*cboRFTagID.SelectedIndex == 0 ||*/ cboSec.SelectedIndex == 0 || cboYr.SelectedIndex == 0)
{
lblNotification.Text = "Please select Year level or section.";
}
else if (tbStudIDNumber.Text.Trim() == "" || tbPCNumber.Text.Trim() == "" || tbLn.Text.Trim() == "" || tbFn.Text.Trim() == "")
{
lblNotification.Text = "Please fill all fields.";
}
else if (DS.Tables[0].Rows.Count > 0 && stud_id == "")
{
lblNotification.Text = "Student ID Number already existing.";
}
else if (lblRFID.Text == "")
{
lblNotification.Text = "Student should have RFID.";
}
else
{
lblNotification.Text = "";
string rfid = "", yr = "", sec = "", rfid_id = "";
//DS = DataAccess.executeQuery("select rfid_id from tbl_rfid_tag where rfid_tag_id='" + cboRFTagID.SelectedItem.ToString() + "' and rfid_tag_active=1");
//rfid = DS.Tables[0].Rows[0][0].ToString();
DS = DataAccess.executeQuery("select sec_id from tbl_sec where sec_details='" + cboSec.SelectedItem.ToString() + "' and sec_active=1");
sec = DS.Tables[0].Rows[0][0].ToString();
DS = DataAccess.executeQuery("select yr_id from tbl_yr where yr_details='" + cboYr.SelectedItem.ToString() + "' and yr_active=1");
yr = DS.Tables[0].Rows[0][0].ToString();
DataAccess.ExecuteCommand("insert tbl_rfid_tag values(null, '" + lblRFID.Text + "', '1', '1')");
DS = DataAccess.executeQuery("select rfid_id from tbl_rfid_tag where rfid_tag_id='" + lblRFID.Text + "' and rfid_tag_active='1'");
rfid_id = DS.Tables[0].Rows[0][0].ToString();
if (stud_id == "")
{
if (DataAccess.ExecuteCommand("insert into tbl_student_info values(null,'" + tbStudIDNumber.Text.Trim() + "','" + tbFn.Text.Trim() + "','" + tbMn.Text.Trim() + "','" + tbLn.Text.Trim() + "','" + tbPCNumber.Text.Trim() + "',1,'" + yr + "','" + sec + "','" + rfid_id + "',null)"))
{
DataAccess.ExecuteCommand("update tbl_rfid_tag set rfid_tag_assigned=1 where rfid_id='" + rfid + "'");
//System.IO.MemoryStream mstream = new System.IO.MemoryStream();
//pic.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Png);
//byte[] arrImg = mstream.GetBuffer();
//mstream.Close();
//mstream.Flush();
//mstream.Dispose();
//DataAccess.sp_update_img(arrImg, tbStudIDNumber.Text);
//arrImg = null;
//GC.Collect();
MessageBox.Show(this, "New student information succesfully added.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show(this, "Error in adding student information", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
if (DataAccess.ExecuteCommand("update tbl_student_info set stud_id_number='" + tbStudIDNumber.Text.Trim() + "',stud_fn='" + tbFn.Text.Trim() + "',stud_mn='" + tbMn.Text.Trim() + "',stud_ln='" + tbLn.Text.Trim() + "',stud_parent_number='" + tbPCNumber.Text.Trim() + "',stud_active=1,yr_id='" + yr + "',sec_id='" + sec + "',rfid_id='" + rfid_id + "' where stud_id='" + stud_id + "'"))
{
if (old_rfid != lblRFID.Text) //cboRFTagID.SelectedItem.ToString())
{
DataAccess.ExecuteCommand("update tbl_rfid_tag set rfid_tag_assigned=0 where rfid_tag_id='" + old_rfid + "' and rfid_tag_active=1");
DataAccess.ExecuteCommand("update tbl_rfid_tag set rfid_tag_assigned=1 where rfid_id='" + rfid + "'");
}
MessageBox.Show(this, "Student information succesfully edited.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
//if (pic.Image != null)
//{
// System.IO.MemoryStream mstream = new System.IO.MemoryStream();
// pic.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Bmp);
// byte[] arrImg = mstream.GetBuffer();
// mstream.Close();
// mstream.Flush();
// mstream.Dispose();
// DataAccess.sp_update_img(arrImg, tbStudIDNumber.Text);
// arrImg = null;
//}
this.Close();
}
else
{
MessageBox.Show(this, "Error in editing student information", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}