/// <summary>
/// 取得不重複的試別清單
/// </summary>
/// <param name="ClassIdList"></param>
/// <returns></returns>
public static List <ExamVO> GetDistincExamList(List <string> ClassIdList)
{
List <ExamVO> result = new List <ExamVO>();
StringBuilder sb = new StringBuilder();
sb.Append("select te_include.ref_exam_id, exam.exam_name, course.school_year, course.semester, exam.display_order");
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 exam");
sb.Append(" on te_include.ref_exam_id = exam.id");
sb.Append(" where class.id in ('" + string.Join("','", ClassIdList.ToArray()) + "')");
sb.Append(" and course.ref_exam_template_id is not NULL");
sb.Append(" and course.school_year is not NULL");
sb.Append(" and course.semester is not NULL");
sb.Append(" and te_include.ref_exam_id is not NULL");
sb.Append(" and student.status = '" + _StudentSatus + "'");
sb.Append(" group by ref_exam_id, exam.exam_name, course.school_year, course.semester, exam.display_order");
sb.Append(" order by course.school_year, course.semester, exam.display_order");
if (Global.IsDebug)
{
Console.WriteLine("[GetDistincExamList] sql: [" + sb.ToString() + "]");
}
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sb.ToString());
foreach (DataRow row in dt.Rows)
{
ExamVO ExamObj = new ExamVO("" + row["ref_exam_id"], "" + row["exam_name"], "" + row["school_year"], "" + row["semester"]);
result.Add(ExamObj);
}
return(result);
} // end of GetDistincExamList
/// <summary>
/// 取得不重複的試別清單
/// </summary>
/// <param name="ClassIdList"></param>
/// <returns></returns>
public static List<ExamVO> GetDistincExamList(List<string> ClassIdList)
{
List<ExamVO> result = new List<ExamVO>();
StringBuilder sb = new StringBuilder();
sb.Append("select te_include.ref_exam_id, exam.exam_name, course.school_year, course.semester, exam.display_order");
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 exam");
sb.Append(" on te_include.ref_exam_id = exam.id");
sb.Append(" where class.id in ('"+string.Join("','", ClassIdList.ToArray())+"')");
sb.Append(" and course.ref_exam_template_id is not NULL");
sb.Append(" and course.school_year is not NULL");
sb.Append(" and course.semester is not NULL");
sb.Append(" and te_include.ref_exam_id is not NULL");
sb.Append(" and student.status = '" + _StudentSatus + "'");
sb.Append(" group by ref_exam_id, exam.exam_name, course.school_year, course.semester, exam.display_order");
sb.Append(" order by course.school_year, course.semester, exam.display_order");
if (Global.IsDebug) Console.WriteLine("[GetDistincExamList] sql: [" + sb.ToString() + "]");
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sb.ToString());
foreach (DataRow row in dt.Rows)
{
ExamVO ExamObj = new ExamVO("" + row["ref_exam_id"], "" + row["exam_name"], "" + row["school_year"], "" + row["semester"]);
result.Add(ExamObj);
}
return result;
} // end of GetDistincExamList
} // 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