void emptab()
{
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT * FROM [dbo].[tblEmployee] WHERE [EId]='" + txtEid.Text + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
btnUpdate.Text = "Update";
while (reader.Read())
{
txtName.Text = reader.GetString(1);
txtDepartment.Text = reader.GetString(2);
txtDesignation.Text = reader.GetString(3);
DateTime dt1 = Convert.ToDateTime(reader["EJoiningDate"].ToString());
txtJoindate.Text = String.Format("{0:yyyy-MM-dd}", dt1);
txtGender.Text = reader.GetString(6);
DateTime dt2 = Convert.ToDateTime(reader["EDob"].ToString());
txtDob.Text = String.Format("{0:yyyy-MM-dd}", dt2);
txtAddress.Text = reader.GetString(8);
txtPhoneno.Text = reader.GetString(9);
txtEmail.Text = reader.GetString(10);
}
}
else
{
btnUpdate.Text = "Add";
// btnClear_Click(new object(), new EventArgs());
}
}
}
void paytab()
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT [BasicPayPD],[EAccno] FROM [dbo].[tblPayroll] WHERE [EId]='" + txtEid.Text + "' AND [Month]='" + month + "' AND [Year]='" + year + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
btnUpdate.Text = "Update";
while (reader.Read())
{
txtAn.Text = reader["EAccno"].ToString();
txtBp.Text = reader["BasicPayPD"].ToString();
}
}
else
{
btnUpdate.Text = "Add";
// btnClear_Click(new object(), new EventArgs());
}
}
}
protected void btnTimein_Click(object sender, EventArgs e)
{
var connection = SqlConnectionManager.GetConnection();
string eid = ((string)Session["id"]);
string intime = DateTime.Now.ToString("HH:mm");
string adate = DateTime.Today.ToString("MM/dd/yyyy");
string month = DateTime.Now.Month.ToString();
string year = DateTime.Now.Year.ToString();
string query = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
string it = "10:00";
while (reader.Read())
{
it = reader["Intime"].ToString();
}
if (it == "10:00")
{
lblError.Visible = false;
string query2 = "UPDATE tblAttendance SET Intime='" + intime + "', Outtime='19:30', Status='present' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(query2, connection);
adapter.InsertCommand.ExecuteNonQuery();
Response.Write("<script>alert('In-Time Registered Successfully.');</script>");
}
else
{
lblError.Visible = true;
lblError.Text = "In-Time already registered!";
}
}
else
{
string query2 = "SELECT * FROM tblEmployee";
var cmd2 = SqlConnectionManager.GetCommand(query2, connection);
using (SqlDataReader reader2 = cmd2.ExecuteReader())
{
if (reader2.HasRows)
{
SqlDataAdapter sa = new SqlDataAdapter();
while (reader2.Read())
{
int empid = reader2.GetInt32(0);
string insertQuery = "INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + empid + "', '10:00', '19:30', '" + adate + "', 'absent', '" + month + "', '" + year + "')";
sa.InsertCommand = new SqlCommand(insertQuery, connection);
sa.InsertCommand.ExecuteNonQuery();
}
}
}
Response.Write("<script>alert('Unable to register In-time, try again.');window.location = 'eAttendance.aspx';</script>");
}
}
}
protected void btnTimeout_Click(object sender, EventArgs e)
{
var connection = SqlConnectionManager.GetConnection();
string eid = ((string)Session["id"]);
string outtime = DateTime.Now.ToString("HH:mm");
string adate = DateTime.Today.ToString("MM/dd/yyyy");
string query = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
string it = "10:00";
string ot = "19:30";
while (reader.Read())
{
it = reader["Intime"].ToString();
ot = reader["Outtime"].ToString();
}
if (it != "10:00")
{
if (ot == "19:30")
{
lblError.Visible = false;
string query2 = "UPDATE tblAttendance SET Outtime='" + outtime + "' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand(query2, connection);
adapter.UpdateCommand.ExecuteNonQuery();
Response.Write("<script>alert('Out-Time Registered Successfully.');</script>");
}
else
{
lblError.Visible = true;
lblError.Text = "Out-Time already registered!";
}
}
else
{
lblError.Visible = true;
lblError.Text = "In-Time not registered, register In-Time first!";
}
}
}
}
public string getDepartmentData()
{
string data = "";
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT [EDepartment],COUNT(*) as count FROM [dbo].[tblEmployee] GROUP BY [EDepartment] ORDER BY count DESC";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
string deptname = reader.GetString(0);
int employeetotal = reader.GetInt32(1);
data += "<tr><td>" + deptname + "</td><td>" + employeetotal + "</td></tr>";
}
}
}
return(data);
}
void attendanceData()
{
var connection = SqlConnectionManager.GetConnection();
connection.Open();
string adate = DateTime.Today.ToString("MM/dd/yyyy");
List <AttendanceDao> attendanceList = new List <AttendanceDao>();
string query = "SELECT tblAttendance.*,tblEmployee.EName FROM tblAttendance INNER JOIN tblEmployee ON tblAttendance.EId=tblEmployee.EId WHERE tblAttendance.[Adate]='" + adate + "'";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Connection = connection;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
AttendanceDao attendanceData = new AttendanceDao();
attendanceData.AId = reader.GetInt32(0);
attendanceData.EId = reader.GetInt32(1);
attendanceData.Intime = reader.IsDBNull(2) ? "" : reader.GetString(2);
attendanceData.Outtime = reader.IsDBNull(3) ? "" : reader.GetString(3);
if (attendanceData.Intime == "10:00")
{
attendanceData.Intime = "not available";
}
if (attendanceData.Outtime == "19:30")
{
attendanceData.Outtime = "not available";
}
int wh = 0, wm = 0, ithr, othr, itmin, otmin;
if (attendanceData.Intime != "10:00" && attendanceData.Outtime != "19:30")
{
Int32.TryParse(attendanceData.Intime.Substring(0, 2), out ithr);
Int32.TryParse(attendanceData.Outtime.Substring(0, 2), out othr);
Int32.TryParse(attendanceData.Intime.Substring(3), out itmin);
Int32.TryParse(attendanceData.Outtime.Substring(3), out otmin);
wh = othr - ithr;
wm = otmin - itmin;
wm = Math.Abs(wm);
}
attendanceData.EWorkinghours = wh.ToString() + "hrs " + wm.ToString() + "mins";
attendanceData.Adate = reader.GetDateTime(4).ToString("dd/MM/yyyy");
attendanceData.Status = reader.GetString(5);
attendanceData.Month = reader.GetInt32(6);
attendanceData.Year = reader.GetInt32(7);
attendanceData.EName = reader.GetString(8);
attendanceList.Add(attendanceData);
}
}
else
{
string query2 = "SELECT * FROM tblEmployee";
var cmd2 = SqlConnectionManager.GetCommand(query2, connection);
using (SqlDataReader reader2 = cmd2.ExecuteReader())
{
if (reader2.HasRows)
{
string month = DateTime.Now.Month.ToString();
string year = DateTime.Now.Year.ToString();
SqlDataAdapter sa = new SqlDataAdapter();
while (reader2.Read())
{
int empid = reader2.GetInt32(0);
string insertQuery = "INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + empid + "', '10:00', '19:30', '" + adate + "', 'absent', '" + month + "', '" + year + "')";
sa.InsertCommand = new SqlCommand(insertQuery, connection);
sa.InsertCommand.ExecuteNonQuery();
}
}
}
Response.Write("<script>window.location = 'frmAttendance.aspx';</script>");
}
if (attendanceList.ToList().Count > 0)
{
gvEmployee.DataSource = attendanceList;
gvEmployee.DataBind();
}
else
{
AttendanceDao attendanceData = new AttendanceDao();
attendanceData.EName = "dummy";
attendanceList.Add(attendanceData);
gvEmployee.DataSource = attendanceList;
gvEmployee.DataBind();
for (int i = 0; i < gvEmployee.Rows.Count; i++)
{
gvEmployee.Rows[i].Cells.Clear();
gvEmployee.Rows[i].Visible = false;
}
gvEmployee.Rows[0].Visible = true;
gvEmployee.BottomPagerRow.Visible = false;
gvEmployee.Rows[0].Cells.Add(new TableCell());
gvEmployee.Rows[0].Cells[0].ColumnSpan = gvEmployee.Columns.Count;
gvEmployee.Rows[0].Cells[0].Text = "No Data Found!";
gvEmployee.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
}
}