//统计此系每周缺勤人数,放到一张表,每周一行 /// <summary> /// /// </summary> /// <param name="Department">当前系名</param> /// <param name="week">当前周次</param> /// <param name="weekEnd">从几周开始</param> /// <returns>返回一个系所有考勤统计</returns> private static DataTable InitialDataTable(string Department, int week, int weekEnd) { DataTable dt = CreateDateTable();//储存每周人数情况 StringBuilder sbl = new StringBuilder(); //当前周次 for (int i = week; i > weekEnd - 1; i--) { sbl.Remove(0, sbl.Length); DataRow drs = dt.NewRow(); drs[0] = i;//周次 drs[1] = Department; sbl.Append("SELECT COUNT(*) AS LateMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek = " + i + " and AttendanceType = '迟到' "); sbl.Append("SELECT COUNT(*) AS EarlyMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek = " + i + " and AttendanceType = '早退' "); sbl.Append("SELECT COUNT(*) AS EarlyMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek = " + i + " and AttendanceType = '旷课' "); sbl.Append("SELECT COUNT(*) AS EarlyMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek = " + i + " and AttendanceType = '请假' "); DataSet ds = AddSQLStringToDAL.GetDsBySql(sbl.ToString()); int Late = Convert.ToInt32(ds.Tables[0].Rows[0][0]); //获取此系此周迟到的人数 drs[2] = Late; int Early = Convert.ToInt32(ds.Tables[1].Rows[0][0]); //获取此系此周早退的人数 drs[3] = Early; int Attendance = Convert.ToInt32(ds.Tables[2].Rows[0][0]); //获取此系此周旷课的人数 drs[4] = Attendance; int Leave = Convert.ToInt32(ds.Tables[3].Rows[0][0]); //获取此系此周请假的人数 drs[5] = Leave; drs[6] = Late + Early + Attendance + Leave; //缺勤的总人数 dt.Rows.Add(drs); } return(dt); }
/// <summary> /// /// </summary> /// <param name="Department"></param> /// <param name="startWeek"></param> /// <param name="EndWeek"></param> private static string[] AllWeekInitial(string Department, int startWeek, int EndWeek) { StringBuilder sbder = new StringBuilder(); sbder.Remove(0, sbder.Length); sbder.Append("SELECT COUNT(*) AS LateMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek in" + SqlList(startWeek, EndWeek) + " and AttendanceType = '迟到' "); sbder.Append("SELECT COUNT(*) AS LateMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek in" + SqlList(startWeek, EndWeek) + " and AttendanceType = '早退' "); sbder.Append("SELECT COUNT(*) AS LateMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek in" + SqlList(startWeek, EndWeek) + " and AttendanceType = '旷课' "); sbder.Append("SELECT COUNT(*) AS LateMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek in" + SqlList(startWeek, EndWeek) + " and AttendanceType = '请假' "); sbder.Append("SELECT COUNT(*) AS LateMount FROM TabStudentAttendance where StudentDepartment = '" + Department + "' and CourseAllWeek in" + SqlList(startWeek, EndWeek) + " ");//所有 DataSet ds = AddSQLStringToDAL.GetDsBySql(sbder.ToString()); //所有周缺勤人数 string[] departmentInfo = { ds.Tables[0].Rows[0][0].ToString(), ds.Tables[1].Rows[0][0].ToString(), ds.Tables[2].Rows[0][0].ToString(), ds.Tables[3].Rows[0][0].ToString(), ds.Tables[4].Rows[0][0].ToString() }; return(departmentInfo); }