protected void Button_Shujudaochu_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=抽查（按时间统计）.xls");
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
GridView_Shijianjieguo.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
/// <summary>
/// 时间抽查
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GridView_Shijianchoucha_OnRowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "SELECT")
{
int index = Convert.ToInt32(e.CommandArgument);
string a = GridView_Shijianchoucha.Rows[index].Cells[1].Text;
string b = GridView_Shijianchoucha.Rows[index].Cells[2].Text;
var startTime = Convert.ToDateTime(a).ToString("yyyy-MM-dd HH:mm:ss");
var endTime = Convert.ToDateTime(b).ToString("yyyy-MM-dd HH:mm:ss");
var cmdString = "select u.user_name as 名字,y.user_id as `学号`,y.`考勤时间` ,y.`考勤日期`,y.`打卡时间`,y.`打卡结果` from yuanshijilus as y LEFT join users as u " +
$"on u.user_id = y.user_id where `考勤时间` >='{a}' and `考勤时间` <='{b}'";
var sql = $"SELECT * from ( select '{a}' as 开始时间 ,'{b}' as 结束时间 ,u.user_id,u.user_name as 名字," +
$"CASE " +
$"when(SELECT count(*) from qingjias as q where DATE_FORMAT(q.`开始时间`, '%Y-%m-%d %H:%i:%s') <= '{startTime}'and " +
$"DATE_FORMAT(q.`结束时间`, '%Y-%m-%d %H:%i:%s')>= '{endTime}' and q.user_id = u.user_id)>0 then '请假'" +
$"WHEN(SELECT Min(打卡时间) from dakaxiangqings where 打卡时间 >= '{startTime}' and 打卡时间 < '{Convert.ToDateTime(b).AddMinutes(1).AddMilliseconds(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and userId = " + "u.user_id) != ' ' THEN '正常'" +
$"WHEN(SELECT Min(打卡时间) from dakaxiangqings where 打卡时间 <= '{Convert.ToDateTime(b).AddMinutes(30).AddMilliseconds(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and" +
$" 打卡时间 >= '{Convert.ToDateTime(b).AddMinutes(30).AddMilliseconds(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and userId = u.user_id) != ' ' then '迟到'" +
$" ELSE '未打卡' END as 打卡状态, (SELECT Min(打卡时间) from dakaxiangqings where 打卡时间 >= '{startTime}' and 打卡时间<= '{endTime}' and userId = u.user_id) as '最早打卡时间' ," +
$" ( SELECT msg FROM remark WHERE user_id = u.user_id and start_time='{a}' and end_time='{b}' ) AS 备注 " +
$" from users as u where u.getdata = 1) as d where d.打卡状态!='正常' ";
MySqlConnection MySqlConnection = new MySqlConnection(SqlHelper.connStr);
MySqlDataAdapter sqlDataAdapter1 = new MySqlDataAdapter(sql, MySqlConnection);
MySqlConnection.Open();
DataSet dss = new DataSet();
sqlDataAdapter1.Fill(dss, "yuanshijilus");
GridView_Shijianjieguo.DataSource = dss;
// GridView_Shijianjieguo.DataKeyNames= new string[] { "id" };//主键
GridView_Shijianjieguo.DataBind();
}
}
