//统计此系每周缺勤人数，放到一张表，每周一行
/// <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="startWeek"></param>
/// <param name="WeekEnd"></param>
/// <returns></returns>
public static DataTable GetDataAndCreateChartBySum(int startWeek, int WeekEnd)
{
string strSql = "select * from TabDepartment";//每个系部总人数
DataTable dtCount = AddSQLStringToDAL.GetDtBySQL(strSql);
string[] AllCount = new string[dtCount.Rows.Count];//保存每个系部总人数
for (int i = 0; i < dtCount.Rows.Count; i++)
{
AllCount[i] = dtCount.Rows[i]["sum"].ToString();
}
string[] AllDepartment = { "会计系", "信息工程系", "经济管理系", "食品工程系", "机械工程系", "商务外语系", "建筑工程系" };
string[] AllData = new string[AllDepartment.Length];
string[] AllLate = new string[AllDepartment.Length];
string[] AllAttendance = new string[AllDepartment.Length];
string[] AllEarly = new string[AllDepartment.Length];
string[] AllLeave = new string[AllDepartment.Length];
//储存每个系合计后的考勤情况
for (int i = 0; i < AllDepartment.Length; i++)
{
string[] allWeek = AllWeekInitial(AllDepartment[i], startWeek, WeekEnd);
AllData[i] = allWeek[4]; //各种缺勤合计
AllLeave[i] = allWeek[3]; //请假合计
AllAttendance[i] = allWeek[2]; //旷课
AllEarly[i] = allWeek[1]; //早退
AllLate[i] = allWeek[0]; //迟到
}
//储存所有系考勤合计和缺勤率
DataTable gridViewDt = CreateDataTableReplaceChart(AllDepartment, AllCount, AllLate, AllEarly, AllAttendance, AllLeave, AllData);
return(gridViewDt);
}
private static void FillParentNode(TreeNode node, string menuName)
{
string sqlQuery = "Select DISTINCT Parent_Node From " + menuName + "";
DataTable dt = AddSQLStringToDAL.GetDtBySQL(sqlQuery);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
TreeNode NewNode = new
TreeNode(row["Parent_Node"].ToString(), "ddad");
NewNode.PopulateOnDemand = true;
NewNode.SelectAction = TreeNodeSelectAction.Expand;
node.ChildNodes.Add(NewNode);
}
}
}
public static void FillChildNode(TreeNode node, string menuName)
{
string sqlQuery = "Select * From " + menuName + "" +
" Where Parent_Node = '" + node.Text + "'";
DataTable dt = AddSQLStringToDAL.GetDtBySQL(sqlQuery);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
TreeNode NewNode = new
TreeNode(row["Child_Node"].ToString(), "", "", row["Navigate_Url"].ToString(), "");
NewNode.PopulateOnDemand = false;
node.ChildNodes.Add(NewNode);
}
}
}
/// <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);
}