} // end of GetAllStudentScore
/// <summary>
/// 取得所有班級的計算規則
/// </summary>
/// <param name="ClassList"></param>
/// <param name="ClassIdList"></param>
public static void GetAllClassCalRule(Dictionary <string, ClassVO> ClassList, List <string> ClassIdList)
{
StringBuilder sb = new StringBuilder();
sb.Append("select class.id, score_calc_rule.content");
sb.Append(" from class");
sb.Append(" left join score_calc_rule");
sb.Append(" on class.ref_score_calc_rule_id=score_calc_rule.id");
sb.Append(" where class.id in ('" + string.Join("','", ClassIdList.ToArray()) + "')");
if (Global.IsDebug)
{
Console.WriteLine("[GetClassCalRule] sql: [" + sb.ToString() + "]");
}
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sb.ToString());
foreach (DataRow row in dt.Rows)
{
string ClassId = ("" + row["id"]).Trim();
if (string.IsNullOrEmpty(ClassId))
{
continue;
}
if (ClassList.Keys.Contains(ClassId))
{
ClassVO ClassObj = ClassList[ClassId];
string ClassCalRule = ("" + row["content"]).Trim();
foreach (StudentVO Student in ClassObj.StudentListDic.Values)
{
// 假如學生身上沒有及格標準, 就改成班級的及格標準
if (string.IsNullOrEmpty(Student.StudentCalRule))
{
Student.StudentCalRule = ClassCalRule;
}
}
}
}
} // end of GetAllClassCalRule
} // end of GetDistincExamList
/// <summary>
/// 取得學生所有修課的成績, 包括學生自己的特殊計算規則假如有的話
/// </summary>
/// <param name="ClassIdList"></param>
/// <param name="exam"></param>
/// <returns></returns>
public static Dictionary<string, ClassVO> GetAllStudentScore(List<string> ClassIdList, ExamVO exam)
{
Dictionary<string, ClassVO> result = new Dictionary<string,ClassVO>();
StringBuilder sb = new StringBuilder();
sb.Append("select student.id as student_id,");
sb.Append("student.ref_class_id,");
sb.Append("class.class_name,");
sb.Append("class.grade_year,");
sb.Append("class.display_order,");
sb.Append("student.seat_no,");
sb.Append("student.name as student_name,");
sb.Append("student.student_number,");
sb.Append("course.id as course_id,");
sb.Append("course.credit,");
sb.Append("course.course_name,");
sb.Append("sce_take.score,");
sb.Append("score_calc_rule.content as student_calc_rule");
sb.Append(" from student");
sb.Append(" left join class");
sb.Append(" on student.ref_class_id = class.id");
sb.Append(" left join sc_attend");
sb.Append(" on sc_attend.ref_student_id = student.id");
sb.Append(" left join course");
sb.Append(" on course.id = sc_attend.ref_course_id");
sb.Append(" left join te_include");
sb.Append(" on te_include.ref_exam_template_id = course.ref_exam_template_id");
sb.Append(" left join sce_take");
sb.Append(" on (sce_take.ref_sc_attend_id=sc_attend.id and sce_take.ref_exam_id = te_include.ref_exam_id)");
sb.Append(" left join score_calc_rule");
sb.Append(" on score_calc_rule.id = student.ref_score_calc_rule_id");
sb.Append(" where class.id in ('" + string.Join("','", ClassIdList.ToArray()) + "')");
sb.Append(" and student.status = '" + _StudentSatus + "'");
sb.Append(" and course.school_year = '" + exam.SchoolYear + "'");
sb.Append(" and course.semester = '" + exam.Semester + "'");
sb.Append(" and te_include.ref_exam_id = '" + exam.ExamId + "'");
if (Global.IsDebug) Console.WriteLine("[GetAllStudentScore] sql: [" + sb.ToString() + "]");
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sb.ToString());
foreach (DataRow row in dt.Rows)
{
string ClassId = ("" + row["ref_class_id"]).Trim();
if (string.IsNullOrEmpty(ClassId)) continue;
ClassVO ClassObj;
if(result.Keys.Contains(ClassId))
{
ClassObj = result[ClassId];
}
else
{
ClassObj = new ClassVO(row);
result.Add(ClassId, ClassObj);
}
ClassObj.AddStudent(row);
}
return result;
} // end of GetAllStudentScore
} // end of GetDistincExamList
/// <summary>
/// 取得學生所有修課的成績, 包括學生自己的特殊計算規則假如有的話
/// </summary>
/// <param name="ClassIdList"></param>
/// <param name="exam"></param>
/// <returns></returns>
public static Dictionary <string, ClassVO> GetAllStudentScore(List <string> ClassIdList, ExamVO exam)
{
Dictionary <string, ClassVO> result = new Dictionary <string, ClassVO>();
StringBuilder sb = new StringBuilder();
sb.Append("select student.id as student_id,");
sb.Append("student.ref_class_id,");
sb.Append("class.class_name,");
sb.Append("class.grade_year,");
sb.Append("class.display_order,");
sb.Append("student.seat_no,");
sb.Append("student.name as student_name,");
sb.Append("student.student_number,");
sb.Append("course.id as course_id,");
sb.Append("course.credit,");
sb.Append("course.course_name,");
sb.Append("sce_take.score,");
sb.Append("score_calc_rule.content as student_calc_rule");
sb.Append(" from student");
sb.Append(" left join class");
sb.Append(" on student.ref_class_id = class.id");
sb.Append(" left join sc_attend");
sb.Append(" on sc_attend.ref_student_id = student.id");
sb.Append(" left join course");
sb.Append(" on course.id = sc_attend.ref_course_id");
sb.Append(" left join te_include");
sb.Append(" on te_include.ref_exam_template_id = course.ref_exam_template_id");
sb.Append(" left join sce_take");
sb.Append(" on (sce_take.ref_sc_attend_id=sc_attend.id and sce_take.ref_exam_id = te_include.ref_exam_id)");
sb.Append(" left join score_calc_rule");
sb.Append(" on score_calc_rule.id = student.ref_score_calc_rule_id");
sb.Append(" where class.id in ('" + string.Join("','", ClassIdList.ToArray()) + "')");
sb.Append(" and student.status = '" + _StudentSatus + "'");
sb.Append(" and course.school_year = '" + exam.SchoolYear + "'");
sb.Append(" and course.semester = '" + exam.Semester + "'");
sb.Append(" and te_include.ref_exam_id = '" + exam.ExamId + "'");
if (Global.IsDebug)
{
Console.WriteLine("[GetAllStudentScore] sql: [" + sb.ToString() + "]");
}
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sb.ToString());
foreach (DataRow row in dt.Rows)
{
string ClassId = ("" + row["ref_class_id"]).Trim();
if (string.IsNullOrEmpty(ClassId))
{
continue;
}
ClassVO ClassObj;
if (result.Keys.Contains(ClassId))
{
ClassObj = result[ClassId];
}
else
{
ClassObj = new ClassVO(row);
result.Add(ClassId, ClassObj);
}
ClassObj.AddStudent(row);
}
return(result);
} // end of GetAllStudentScore