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