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