private void btnUpdate_Click(object sender, EventArgs e)
{
if (lbMOStaff.SelectedItems.Count > 0)
{
Regex regShift = new Regex(@"([\w\s]+)-([\w\s._]+)-([\w\s]+)");
int affectedRows = 0;
for (int i = 0; i < lbMOStaff.SelectedItems.Count; i++)
{
if (mssql != null && mssql.isConnected())
{
if (getShiftTime(regShift.Match(lbMOStaff.SelectedItems[i].ToString()).Groups[3].Value) != "")
{
if (mssql.executeNonQuery(string.Format("INSERT INTO movement VALUES({0},'{1}','{2}')", regShift.Match(lbMOStaff.SelectedItems[i].ToString()).Groups[1].Value, getShiftTime(regShift.Match(lbMOStaff.SelectedItems[i].ToString()).Groups[3].Value), regShift.Match(lbMOStaff.SelectedItems[i].ToString()).Groups[3].Value + "-OUT")) == 1)
{
affectedRows++;
}
}
}
}
if (affectedRows > 0)
{
LoadDBValues();
MessageBox.Show(this, string.Format("Successfully Updated {0} OUT Punch/es.", affectedRows), "Successfully Updated", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show(this, "Kindly select at-least one staff to Update!", "Staff?", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private bool saveDetailsToDatabase(string staffno)
{
try
{
if (mssql.executeNonQuery(string.Format("INSERT INTO movement VALUES({0},CURRENT_TIMESTAMP,'{1}')", staffno, getDBShiftCode(lblShiftSelected.Text))) != 1)
{
return(false);
}
}
catch (Exception e)
{
MessageBox.Show(this, e.Message, "Exception@saveDetailsToDatabase", MessageBoxButtons.OK, MessageBoxIcon.Error);
Logger.log("Exception(saveDetailsToDatabase):" + e.Message);
}
//To avoid NIGHT Shift Double attendance 00-05 Hours Not Considered for
//SQLiteCommand cmd = new SQLiteCommand(string.Format("SELECT * from attendance where staffno='{0}' and attendeddate=CURRENT_DATE;",staffno), sqlite.getConnection());
//SQLiteDataReader reader = cmd.ExecuteReader();
//cmd = new SQLiteCommand(string.Format("SELECT * from attendance where staffno='{0}' and attendeddate=CURRENT_DATE;", staffno), sqlite.getConnection());
//reader = cmd.ExecuteReader();
//if (!reader.Read())
//{
// if (sqlite.executeNonQuery(string.Format("INSERT INTO attendance VALUES('{0}',CURRENT_DATE)", staffno)) != 1)
// return false;
//}
return(true);
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
if (cbPlant.Text != null && cbPlant.Text.Trim() != "")
{
if (mssql != null && mssql.isConnected())
{
SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM plant where plantname='{0}';", cbPlant.Text.ToString().Trim().ToUpper()), mssql.getConnection());
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
reader.Close();
if (mssql.executeNonQuery(string.Format("INSERT INTO plant VALUES('{0}','{1}')", cbPlant.Text.ToString().Trim().ToUpper(), tbPlantTitle.Text.Trim().ToUpper())) == 1)
{
MessageBox.Show(this, "Successfully Added a new Plant", "Added Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information);
cbPlant.Text = "";
LoadDBValues();
}
else
{
MessageBox.Show(this, "Failed to Add a New Item", "Failed to Add", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "This Plant is already Exists", "Already Exists", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
reader.Close();
}
else
{
MessageBox.Show(this, "Unable to reach Database Server. Ensure Network Connectivity and Double-check Database Parameters under Settings.", "Unable to Connect Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Empty Plant Name...!!\nKindly Provide a non-empty plant name to be added...", "Empty Plant", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Logger.log("Exception(Conf_btnAdd_Click):" + ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string InTime = "", OutTime = "";
Regex regShift = new Regex(@"([\w\s]+)-([\w\s._]+)");
if (isMandatoryDataProvided())
{
if (isValidDate())
{
if (!isDuplicateRecordExists())
{
InTime = getINTime();
OutTime = getOUTTime();
if (InTime != "" && OutTime != "")
{
if (MessageBox.Show(this, String.Format("Following Record will be inserted:\nStaffNo: {0} Name: {1}\n IN: {2} OUT: {3}\nAre you sure to proceed?", regShift.Match(lbIRStaff.SelectedItem.ToString()).Groups[1].Value, regShift.Match(lbIRStaff.SelectedItem.ToString()).Groups[2].Value, dtpSelectDate.Value.ToString("dd-MM-yyyy") + " " + InTime, (OutTime == "06:30:00") ? (dtpSelectDate.Value.AddDays(1).ToString("dd-MM-yyyy") + " " + OutTime) : (dtpSelectDate.Value.ToString("dd-MM-yyyy") + " " + OutTime)), "Are You Sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (mssql.executeNonQuery(string.Format("INSERT INTO movement VALUES({0},'{1}','{2}')", regShift.Match(lbIRStaff.SelectedItem.ToString()).Groups[1].Value, dtpSelectDate.Value.ToString("yyyy-MM-dd") + " " + InTime, "U" + cbShiftCode.Text + "-IN")) == 1 &&
mssql.executeNonQuery(string.Format("INSERT INTO movement VALUES({0},'{1}','{2}')", regShift.Match(lbIRStaff.SelectedItem.ToString()).Groups[1].Value, (OutTime == "06:30:00") ? (dtpSelectDate.Value.AddDays(1).ToString("yyyy-MM-dd") + " " + OutTime) : (dtpSelectDate.Value.ToString("yyyy-MM-dd") + " " + OutTime), "U" + cbShiftCode.Text + "-OUT")) == 1)
{
MessageBox.Show(this, "Successfully Updated the New Record.", "Update Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
else
{
MessageBox.Show(this, "Shift-IN Record already exists!", "Shift-IN Record exists!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show(this, "You Can only Update Missing-IN and Missing-OUT records, one day prior to Current Date.", "Same Day Update Not Allowed", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show(this, "Some required data is Missing!\nKindly provide data for all the 3 fields", "Missing Data?", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (btnState == "UPDATE")
{
//Only Save Updated Fingerprint Templates
if (isValidName(tbName.Text.Trim().ToUpper(), tbStaffNumber.Text.Trim()))
{
if (ANSITemplate1 != null && ANSITemplate1.Length > 0 && ANSITemplate2 != null && ANSITemplate2.Length > 0)
{
if (mssql != null && mssql.isConnected())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = mssql.getConnection();
cmd.CommandText = string.Format("Update staff set finger1=@Finger1, finger2=@Finger2 where staffno={0}", tbStaffNumber.Text.Trim().ToUpper());
cmd.Parameters.Add("@Finger1", SqlDbType.VarBinary, 512).Value = ANSITemplate1;
cmd.Parameters.Add("@Finger2", SqlDbType.VarBinary, 512).Value = ANSITemplate2;
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show(this, "Staff Fingerprint Templates are Successfully Updated", "Updated Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information);
windowDefaultState();
clearBioValues();
}
else
{
MessageBox.Show(this, "Failed to update the Fingerprint Templates", "Failed to Update", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Unable to reach Database Server. Ensure Network Connectivity and Double-check Database Parameters under Settings.", "Unable to Connect Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Kindly Capture Two Fingerprints", "Fingerprints are missing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Kindly provide the valid Name(letters, Space, & Dot is Allowed) and Staff Number (0-9)", "Invalid Name or Staff Number", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (tbName.Text.Trim() != "" && tbStaffNumber.Text.Trim() != "" && cbPlant.SelectedIndex != -1 && cbGender.SelectedIndex != -1 && cbSkill.SelectedIndex != -1 && cbSamiti.SelectedIndex != -1)
{
if (isValidName(tbName.Text.Trim().ToUpper(), tbStaffNumber.Text.Trim()))
{
if (btnState == "ADD")
{
if (mssql != null && mssql.isConnected())
{
if (ANSITemplate1 != null && ANSITemplate1.Length > 0 && ANSITemplate2 != null && ANSITemplate2.Length > 0)
{
SqlCommand cmd = new SqlCommand(string.Format("SELECT staffno FROM staff where staffno={0};", tbStaffNumber.Text.Trim().ToUpper()), mssql.getConnection());
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
reader.Close();
cmd.CommandText = string.Format("INSERT INTO staff VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,@Finger1,@Finger2)", tbStaffNumber.Text.Trim().ToUpper(), tbName.Text.Trim().ToUpper(), cbPlant.Text.ToUpper(), cbGender.Text.ToUpper(), cbSkill.Text.ToUpper(), cbSamiti.Text.ToUpper(), "ENABLED");
cmd.Parameters.Add("@Finger1", SqlDbType.VarBinary, 512).Value = ANSITemplate1;
cmd.Parameters.Add("@Finger2", SqlDbType.VarBinary, 512).Value = ANSITemplate2;
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show(this, "Successfully Added a new Staff", "Added Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information);
windowDefaultState();
LoadDBValues();
clearBioValues();
}
else
{
MessageBox.Show(this, "Failed to Add a New Staff", "Failed to Add", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "This Staff Number is already Exists", "Already Exists", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
reader.Close();
}
else
{
MessageBox.Show(this, "Kindly Capture Two Fingerprints", "Fingerprints are missing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Unable to reach Database Server. Ensure Network Connectivity and Double-check Database Parameters under Settings.", "Unable to Connect Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (btnState == "MODIFY")
{
SqlCommand cmd = new SqlCommand(string.Format("SELECT staffno FROM staff where staffno={0};", dgvAttendance.Rows[selectedRow].Cells[1].Value.ToString()), mssql.getConnection());
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
reader.Close();
if (mssql.executeNonQuery(string.Format("UPDATE staff set staffno={0},staffname='{1}',plantname='{2}',gender='{4}',skilllevel='{5}',samitiname='{6}',lastmodifiedon=CURRENT_TIMESTAMP where staffno={3}", tbStaffNumber.Text.Trim().ToUpper(), tbName.Text.Trim().ToUpper(), cbPlant.Text.ToString().ToUpper(), dgvAttendance.Rows[selectedRow].Cells[1].Value.ToString(), cbGender.Text.ToUpper(), cbSkill.Text.ToUpper(), cbSamiti.Text.ToUpper())) == 1)
{
MessageBox.Show(this, "Successfully modified the Staff Details", "Successfully Modified", MessageBoxButtons.OK, MessageBoxIcon.Information);
windowDefaultState();
LoadDBValues();
}
else
{
MessageBox.Show(this, "Failed to Modify Staff details", "Failed to Modify", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "This Staff Number Does not Exists", "Record Missing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
reader.Close();
}
}
else
{
MessageBox.Show(this, "Kindly provide the valid Name(letters, Space, & Dot is Allowed) and Staff Number (0-9)", "Invalid Name or Staff Number", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(this, "Some of the required fields are empty", "Required Fields are Missing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Exception@btnSave_Click", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}