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