///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
private void LoadDBValues()
{
try
{
if (mssql != null && mssql.isConnected())
{
cbPlant.Items.Clear();
SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM plant order by plantname;"), mssql.getConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cbPlant.Items.Add(reader.GetString(0));
}
reader.Close();
cbPlant.Text = DefaultPlant;
cbSamiti.Items.Clear();
cmd.CommandText = string.Format("SELECT * FROM samiti order by samitiname;");
reader = cmd.ExecuteReader();
while (reader.Read())
{
cbSamiti.Items.Add(reader.GetString(0));
}
reader.Close();
cbContractorName.Items.Clear();
cmd.CommandText = string.Format("SELECT * FROM contractor order by contractorname;");
reader = cmd.ExecuteReader();
while (reader.Read())
{
cbContractorName.Items.Add(reader.GetString(0));
}
reader.Close();
cmd = new SqlCommand(string.Format("SELECT staffno,staffname,fathername,gender,contractorname,plantname,skilllevel,samitiname FROM staff where plantname='{0}' order by staffno;", DefaultPlant), mssql.getConnection());
reader = cmd.ExecuteReader();
dgvAttendance.Rows.Clear();
while (reader.Read())
{
dgvAttendance.Rows.Add();
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[0].Value = dgvAttendance.Rows.Count;
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[1].Value = reader.GetString(0); //staffno
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[2].Value = reader.GetString(1); //staffname
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[3].Value = reader.GetString(2); //fathername
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[4].Value = reader.GetString(3); //gender
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[5].Value = reader.GetString(4); //contractorname
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[6].Value = reader.GetString(5); //plantname
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[7].Value = reader.GetString(6); //skilllevel
dgvAttendance.Rows[dgvAttendance.Rows.Count - 1].Cells[8].Value = reader.GetString(7); //samitiname
}
reader.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Exception@LoadDBValues", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
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 void updateStaffCount()
{
try
{
if (mssql != null && mssql.isConnected())
{
SqlCommand cmd = new SqlCommand(string.Format("SELECT m.shiftcode, Count(m.shiftcode) FROM movement as m, staff as s where m.staffno=s.staffno and s.plantname='{0}' and CAST(m.moveon as date)=CAST(CURRENT_TIMESTAMP as date) GROUP BY shiftcode;", DefaultPlant), mssql.getConnection());
SqlDataReader reader = cmd.ExecuteReader();
lbl1stShiftCount.Text = "1stShift: 0";
lbl2ndShiftCount.Text = "2ndShift: 0";
lbl3rdShiftCount.Text = "3rdShift: 0";
lblGSCount.Text = "GS: 0";
while (reader.Read())
{
if (reader.GetString(0) == "1stShift-IN")
{
lbl1stShiftCount.Text = "1stShift: " + reader.GetInt32(1).ToString();
}
else if (reader.GetString(0) == "2ndShift-IN")
{
lbl2ndShiftCount.Text = "2ndShift: " + reader.GetInt32(1).ToString();
}
else if (reader.GetString(0) == "3rdShift-IN")
{
lbl3rdShiftCount.Text = "3rdShift: " + reader.GetInt32(1).ToString();
}
else if (reader.GetString(0) == "GS-IN")
{
lblGSCount.Text = "GS: " + reader.GetInt32(1).ToString();
}
}
reader.Close();
lbl1stShiftCount.Refresh();
lbl2ndShiftCount.Refresh();
lbl3rdShiftCount.Refresh();
lblGSCount.Refresh();
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Exception@updateStaffCount", MessageBoxButtons.OK, MessageBoxIcon.Error);
Logger.log("Exception(updateStaffCount):" + ex.Message);
}
}
private void LoadDBValues()
{
try
{
if (mssql != null && mssql.isConnected())
{
cbPlant.Items.Clear();
cbSelectPlant.Items.Clear();
SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM plant order by plantname;"), mssql.getConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cbPlant.Items.Add(reader.GetString(0));
cbSelectPlant.Items.Add(reader.GetString(0));
}
reader.Close();
if (DefaultPlant != "")
{
//Select Default Plant
cbSelectPlant.Text = DefaultPlant;
}
cbSamiti.Items.Clear();
cmd.CommandText = string.Format("SELECT * FROM samiti order by samitiname;");
reader = cmd.ExecuteReader();
while (reader.Read())
{
cbSamiti.Items.Add(reader.GetString(0));
}
reader.Close();
cbContractor.Items.Clear();
cmd.CommandText = string.Format("SELECT * FROM contractor order by contractorname;");
reader = cmd.ExecuteReader();
while (reader.Read())
{
cbContractor.Items.Add(reader.GetString(0));
}
reader.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Logger.log("Exception(Conf_LoadDBValues):" + ex.Message);
}
}
