/// <summary> /// 取得AB表用學生擔任幹部 /// </summary> /// <param name="StudentIDList"></param> /// <returns></returns> public static Dictionary<string, List<DAO.AB_StudText>> GetABCard_StudentTheCadreDict(List<string> StudentIDList) { Dictionary<string, List<DAO.AB_StudText>> retVal = new Dictionary<string, List<DAO.AB_StudText>>(); if (StudentIDList.Count > 0) { // 取得幹部資料 QueryHelper qh = new QueryHelper(); string strSQL = "select studentid,schoolyear,semester,referencetype,cadrename from $behavior.thecadre where studentid in('" + string.Join("','", StudentIDList.ToArray()) + "')"; DataTable dt = qh.Select(strSQL); // 讀取學期歷程 List<SemesterHistoryRecord> SemesterHistoryRecordList = SemesterHistory.SelectByStudentIDs(StudentIDList); List<DAO.AB_StudText> dataList = new List<DAO.AB_StudText>(); foreach (DataRow dr in dt.Rows) { DAO.AB_StudText ast = new DAO.AB_StudText(); ast.StudentID = dr[0].ToString(); ast.SchoolYear = int.Parse(dr[1].ToString()); ast.Semester = int.Parse(dr[2].ToString()); ast.Text1 = dr[3].ToString() + ":" + dr[4].ToString(); // 比對學期歷程填入年級 foreach (SemesterHistoryRecord shr in SemesterHistoryRecordList.Where(x => x.RefStudentID == ast.StudentID)) foreach (SemesterHistoryItem shi in (from data in shr.SemesterHistoryItems where data.SchoolYear == ast.SchoolYear && data.Semester == ast.Semester select data)) ast.GradeYear = shi.GradeYear; dataList.Add(ast); } foreach (string str in StudentIDList) retVal.Add(str, (from data in dataList where data.StudentID == str orderby data.GradeYear, data.Semester select data).ToList()); } return retVal; }
/// <summary> /// 取得AB表用學生選社團 /// </summary> /// <param name="StudentIDList"></param> /// <returns></returns> public static Dictionary<string, List<DAO.AB_StudText>> GetABCard_StudentSpecCourseDict(List<string> StudentIDList) { Dictionary<string, List<DAO.AB_StudText>> retVal = new Dictionary<string, List<DAO.AB_StudText>>(); if (StudentIDList.Count > 0) { // 取得學生選社 QueryHelper qh = new QueryHelper(); string strSQL = "select ref_student_id,course.school_year,course.semester,course.course_name from sc_attend inner join course on sc_attend.ref_course_id=course.id inner join tag_course on tag_course.ref_course_id=sc_attend.ref_course_id and tag_course.ref_tag_id in(select id from Tag where name in('社團','聯課活動') and prefix in('社團','聯課活動')) where ref_student_id in(" + string.Join(",", StudentIDList.ToArray()) + ")"; DataTable dt = qh.Select(strSQL); // 讀取學期歷程 List<SemesterHistoryRecord> SemesterHistoryRecordList = SemesterHistory.SelectByStudentIDs(StudentIDList); List<DAO.AB_StudText> dataList = new List<DAO.AB_StudText>(); foreach (DataRow dr in dt.Rows) { DAO.AB_StudText ast = new DAO.AB_StudText(); ast.StudentID = dr[0].ToString(); ast.SchoolYear = int.Parse(dr[1].ToString()); ast.Semester = int.Parse(dr[2].ToString()); ast.Text1 = dr[3].ToString(); // 比對學期歷程填入年級 foreach (SemesterHistoryRecord shr in SemesterHistoryRecordList.Where(x => x.RefStudentID == ast.StudentID)) foreach (SemesterHistoryItem shi in (from data in shr.SemesterHistoryItems where data.SchoolYear == ast.SchoolYear && data.Semester == ast.Semester select data)) ast.GradeYear = shi.GradeYear; dataList.Add(ast); } foreach(string str in StudentIDList) retVal.Add(str,(from data in dataList where data.StudentID== str orderby data.GradeYear,data.Semester select data).ToList()); } return retVal; }