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