//旷课 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_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}人次"; } }