C# 统计员工上下班情况

点击打开链接public void CommuertReportExcel()

{

DataTable dtshow = new DataTable();

DataTable dt = new DataTable();

string sql = "select LogTime,AttendantWorkNo,ActionName from AttendantLogInfo where ActionName in('登录','下班') " + search_condition() + " order by LogTime";

dt = share.rdtable(sql);

if (dt.Rows.Count == 0)

{

dgVidicon.Visible = false;

return;

}

else

dgVidicon.Visible = true;

ArrayList al = new ArrayList();

string showcolumns = "";

for (int j = 0; j < dt.Rows.Count; j++)

{

if (j == 0)

{

dtshow.Columns.Add("工号", Type.GetType("System.String"));

}

DateTime logtime = Convert.ToDateTime(dt.Rows[j][0]);

//string nyr = logtime.GetDateTimeFormats('D')[0].ToString();

string nyr = string.Format("{0:d}", logtime);

//string sf = logtime.GetDateTimeFormats('t')[0].ToString();

string workno = dt.Rows[j][1].ToString();

if (showcolumns != nyr)

{

dtshow.Columns.Add(nyr, Type.GetType("System.String"));

showcolumns = nyr;

}

if (!al.Contains(workno))

{

al.Add(workno);

DataRow dr = dtshow.NewRow();

dr[dtshow.Columns[0].ColumnName] = workno;

dtshow.Rows.Add(dr);

dtshow.AcceptChanges();

}

}

for (int k = 0; k < dtshow.Rows.Count; k++)

{

for (int m = 1; m < dtshow.Columns.Count; m++)

{

string workno = dtshow.Rows[k][0].ToString();

string day = dtshow.Columns[m].ColumnName;

dtshow.Rows[k][m] = getValue(dt, workno, day);

}

}

dgVidicon.DataSource = dtshow;

dgVidicon.DataBind();

}

public string getValue(DataTable dt, string workno, string day)

{

object obj1 = dt.Compute("min(LogTime)", "AttendantWorkNo='" + workno + "' and Logtime>='" + day + " 00:00:00' and Logtime<='" + day + " 23:59:59' and ActionName='登录'");

if (obj1 == null || obj1.ToString().Length == 0)

obj1 = "0";

else

{

DateTime logtime = Convert.ToDateTime(obj1);

obj1 = logtime.GetDateTimeFormats('t')[0].ToString();

}

object obj2 = dt.Compute("max(LogTime)", "AttendantWorkNo='" + workno + "' and Logtime>='" + day + " 00:00:00' and Logtime<='" + day + " 23:59:59' and ActionName='下班'");

if (obj2 == null || obj2.ToString().Length == 0)

obj2 = "0";

else

{

DateTime logtime = Convert.ToDateTime(obj2);

obj2 = logtime.GetDateTimeFormats('t')[0].ToString();

}

string value = "";

if (obj1 == "0" && obj2 == "0")

{

value = "0";

}

else

value = "上班[" + obj1 + "]<br />" + "下班[" + obj2 + "]";

return value;

}