//旷课
protected void Button_Kuanggong_Click(object sender, EventArgs e)
{
var where = "";
if (!string.IsNullOrWhiteSpace(TextBox_Kaishishijian.Text))
{
where += $" and y.考勤时间 >= '{ TextBox_Kaishishijian.Text }'";
}
if (!string.IsNullOrWhiteSpace(TextBox_Jieshushijian.Text))
{
where += $" and y.考勤时间 <='{ TextBox_Jieshushijian.Text + " 23:59:59" }'";
}
if (!string.IsNullOrWhiteSpace(TextBox_Xuehao.Text))
{
where += $" and u.pass_word like '%{TextBox_Xuehao.Text}%'";
}
if (!string.IsNullOrWhiteSpace(TextBox_banji.Text))
{
where += $" and u.class_grade='{TextBox_banji.Text}'";
}
var sql = " SELECT any_value(a.名字) as 名字, CASE WHEN any_value(a.时间) > DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 12:00') THEN DATE_FORMAT(any_value(a.时间), " +
"'%Y-%m-%d 下午') " +
" ELSE DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 上午') END as 旷课时间 from( SELECT DATE_FORMAT( any_value(y.`考勤时间`), '%Y-%m-%d %H:%i' ) AS 时间," +
" any_value(u.user_name) AS 名字,CASE WHEN any_value(y.`考勤时间`) > DATE_FORMAT(any_value(y.`考勤时间`), '%Y-%m-%d 13:00') THEN DATE_FORMAT(any_value(y.`考勤时间`), '%Y-%m-%d 18:00') ELSE DATE_FORMAT(any_value(y.`考勤时间`), '%Y-%m-%d 12:00' ) END AS 日期 from yuanshijilus as y " +
$"left join users as u on u.user_id = y.user_id where any_value(y.打卡结果='未打卡') {where} AND y.id not in (SELECT y.id from yuanshijilus " +
$"as y left join users as u on u.user_id = y.user_id " +
$"LEFT join qingjias as q on u.user_id=y.user_id where any_value(y.打卡结果='未打卡') {where} AND " +
$" y.`打卡时间` BETWEEN q.`开始时间` and q.`结束时间`) " + $" GROUP BY y.id) as a GROUP BY a.名字,a.日期 HAVING count(a.日期) > 1 ORDER BY a.时间 desc";
var countSql = "select count(1) from (SELECT any_value(a.名字), CASE WHEN any_value(a.时间) > DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 12:00') THEN DATE_FORMAT(any_value(a.时间), " +
"'%Y-%m-%d 下午') " +
" ELSE DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 上午') END as 旷课时间 from( SELECT DATE_FORMAT( any_value(y.`考勤时间`), '%Y-%m-%d %H:%i' ) AS 时间," +
" any_value(u.user_name) AS 名字,CASE WHEN any_value(y.`考勤时间`) > DATE_FORMAT(any_value(y.`考勤时间`), '%Y-%m-%d 13:00') THEN DATE_FORMAT(any_value(y.`考勤时间`), '%Y-%m-%d 18:00') ELSE DATE_FORMAT(any_value(y.`考勤时间`), '%Y-%m-%d 12:00' ) END AS 日期 from yuanshijilus as y " +
$"left join users as u on u.user_id = y.user_id where any_value(y.打卡结果='未打卡') {where} AND y.id not in (SELECT y.id from yuanshijilus " +
$"as y left join users as u on u.user_id = y.user_id " +
$"LEFT join qingjias as q on u.user_id=y.user_id where any_value(y.打卡结果='未打卡') {where} AND " +
$" y.`打卡时间` BETWEEN q.`开始时间` and q.`结束时间`) " + $" GROUP BY y.id) as a GROUP BY a.名字,a.日期 HAVING count(a.日期) > 1 ORDER BY a.时间 desc) as d ";
var count = SqlHelper.ExecuteScalar(countSql);
using (MySqlConnection sqlConnection = new MySqlConnection(SqlHelper.connStr))
using (MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sql, sqlConnection))
{
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "yuanshijilus");
GridView_Shijiantongji.DataSource = ds;
GridView_Shijiantongji.DataBind();
tongji.Text = $"缺课人数一共{count}人次";
}
}
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_Shijiantongji.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
//早退
protected void Button_Zaotui_Click(object sender, EventArgs e)
{
var where = "";
if (!string.IsNullOrWhiteSpace(TextBox_Kaishishijian.Text))
{
where += $" and y.考勤时间 >='{TextBox_Kaishishijian.Text } '";
}
if (!string.IsNullOrWhiteSpace(TextBox_Jieshushijian.Text))
{
where += $" and y.考勤时间 <='{ TextBox_Jieshushijian.Text + " 23:59:59"}'";
}
if (!string.IsNullOrWhiteSpace(TextBox_Xuehao.Text))
{
where += $" and u.pass_word like '%{TextBox_Xuehao.Text}%'";
}
if (!string.IsNullOrWhiteSpace(TextBox_banji.Text))
{
where += $" and u.class_grade='{TextBox_banji.Text}'";
}
var sql = $"SELECT u.user_name as 名字,y.考勤时间,y.打卡时间,y.打卡结果 from yuanshijilus as y " +
$"left join users as u on u.user_id = y.user_id where " +
$" y.打卡结果='早退' {where} ORDER BY y.打卡时间 desc";
var countSql = $"SELECT count(1) from yuanshijilus as y " +
$"left join users as u on u.user_id = y.user_id where " +
$" y.打卡结果='早退' {where}";
var count = SqlHelper.ExecuteScalar(countSql);
using (MySqlConnection sqlConnection = new MySqlConnection(SqlHelper.connStr))
using (MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sql, sqlConnection))
{
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "yuanshijilu");
GridView_Shijiantongji.DataSource = ds;
GridView_Shijiantongji.DataBind();
tongji.Text = $"早退人数一共{count}人次";
}
}
//请假
protected void Button_Qingjia_Click(object sender, EventArgs e)
{
var where = "";
if (!string.IsNullOrWhiteSpace(TextBox_Kaishishijian.Text))
{
where += $" and q.开始时间 >= '{ TextBox_Kaishishijian.Text }'";
}
if (!string.IsNullOrWhiteSpace(TextBox_Jieshushijian.Text))
{
where += $" and q.开始时间 <='{ TextBox_Jieshushijian.Text + " 23:59:59" }'";
}
if (!string.IsNullOrWhiteSpace(TextBox_Xuehao.Text))
{
where += $" and u.pass_word like '%{TextBox_Xuehao.Text}%'";
}
if (!string.IsNullOrWhiteSpace(TextBox_banji.Text))
{
where += $" and u.class_grade='{TextBox_banji.Text}'";
}
var sql = $" SELECT u.user_name as 名字, q.`开始时间`, q.`结束时间`,q.`请假类型` from qingjias as q left join users u" +
$" on q.user_id = u.user_id where 1=1 {where} ORDER BY q.`开始时间` desc";
var countSql = $" SELECT count(1) from qingjias as q left join users u" +
$" on q.user_id = u.user_id where 1=1 {where}";
var count = SqlHelper.ExecuteScalar(countSql);
using (MySqlConnection sqlConnection = new MySqlConnection(SqlHelper.connStr))
using (MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sql, sqlConnection))
{
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "yuanshijilu");
GridView_Shijiantongji.DataSource = ds;
GridView_Shijiantongji.DataBind();
tongji.Text = $"请假人数一共{count}人次";
}
}
