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; } }
protected void btnView_Click(object sender, EventArgs e) { lblErrormessage.Visible = false; if (txtEId.Text.Trim() != string.Empty || txtDate.Text != string.Empty) { 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 1=1"; SqlCommand cmd = new SqlCommand(); StringBuilder sbcmd = new StringBuilder(query); if (txtEId.Text.Trim() != "") { sbcmd.Append(" AND tblAttendance.EId='" + txtEId.Text.Trim() + "'"); } if (txtDate.Text != "") { sbcmd.Append(" AND tblAttendance.Adate='" + DateTime.Parse(txtDate.Text).ToString("MM/dd/yyyy") + "'"); } cmd.CommandText = sbcmd.ToString(); cmd.Connection = connection; SqlDataReader reader = cmd.ExecuteReader(); 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"; } //attendanceData.EWorkinghours = reader.GetInt32(4); 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); } if (attendanceList.ToList().Count > 0) { gvEmployee.DataSource = attendanceList; gvEmployee.DataBind(); } else { 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; } } else { lblErrormessage.Visible = true; lblErrormessage.Text = "The employee-id / date is required to view the record(s) !"; } }