/// <summary> /// 透過班級編號 取得 班級學生一般狀態 /// </summary> /// <param name="ClassIDList"></param> /// <returns></returns> public static Dictionary <string, List <StudentInfo> > GetClassStudentDict(List <string> ClassIDList) { Dictionary <string, List <StudentInfo> > value = new Dictionary <string, List <StudentInfo> >(); try { string classIDs = string.Join(",", ClassIDList.ToArray()); string qryStud = @" SELECT student.ref_class_id AS class_id ,student.id AS student_id ,seat_no,student.name AS student_name ,sems_history FROM student WHERE student.status=1 AND student.ref_class_id IN(" + classIDs + @") ORDER BY student.ref_class_id,seat_no"; QueryHelper qh = new QueryHelper(); DataTable dtStud = qh.Select(qryStud); // 取得班級異動資料 string qryUpdateRec = @" SELECT student.id AS student_id ,update_record.school_year ,update_record.semester ,CASE WHEN update_code = '2' THEN '畢業' WHEN update_code = '3' THEN '轉入' WHEN update_code = '4' THEN '轉出' WHEN update_code = '5' THEN '休學' WHEN update_code = '6' THEN '復學' WHEN update_code = '7' THEN '中輟' WHEN update_code = '8' THEN '續讀' WHEN update_code = '9' THEN '更正學籍' WHEN update_code = '10' THEN '延長修業年限' WHEN update_code = '11' THEN '死亡' ELSE '' END AS update_desc FROM update_record INNER JOIN student ON update_record.ref_student_id = student.id WHERE update_code<>'1' AND student.status=1 AND student.ref_class_id IN(" + classIDs + @") ORDER BY student.id,update_date DESC "; DataTable dtUpdRec = qh.Select(qryUpdateRec); Dictionary <string, List <string> > studUpdaeRecDict = new Dictionary <string, List <string> >(); if (dtUpdRec != null) { foreach (DataRow dr in dtUpdRec.Rows) { string sid = dr["student_id"].ToString(); if (!studUpdaeRecDict.ContainsKey(sid)) { studUpdaeRecDict.Add(sid, new List <string>()); } string msg = dr["school_year"].ToString() + " " + dr["semester"].ToString() + " " + dr["update_desc"].ToString(); studUpdaeRecDict[sid].Add(msg); } } // 取得幹部資料 Dictionary <string, List <CadreInfo> > studCadDict = new Dictionary <string, List <CadreInfo> >(); string qryCadre = @" SELECT student.id AS student_id ,schoolyear AS school_year ,semester ,referencetype ,cadrename ,text FROM student INNER JOIN $behavior.thecadre ON student.id = CAST($behavior.thecadre.studentid AS INTEGER) WHERE student.status=1 AND student.ref_class_id IN(" + classIDs + @") ORDER BY student.id,$behavior.thecadre.schoolyear,semester "; DataTable dtCadre = qh.Select(qryCadre); if (dtCadre != null) { foreach (DataRow dr in dtCadre.Rows) { string sid = dr["student_id"].ToString(); if (!studCadDict.ContainsKey(sid)) { studCadDict.Add(sid, new List <CadreInfo>()); } CadreInfo ci = new CadreInfo(); ci.StudentID = sid; ci.SchoolYear = dr["school_year"].ToString(); ci.Semester = dr["semester"].ToString(); ci.ReferenceType = dr["referencetype"].ToString(); ci.CadreName = dr["cadrename"].ToString(); ci.Text = dr["text"].ToString(); studCadDict[sid].Add(ci); } } Configure _Configure = null; AccessHelper _accessHelper = new AccessHelper(); List <Configure> confList = _accessHelper.Select <Configure>(); if (confList != null && confList.Count > 0) { _Configure = confList[0]; } else { _Configure = new Configure(); _Configure.Name = "屏東免試入學-班級服務表現"; } // 班級幹部限制 List <string> CadreName1 = _Configure.LoadCareNames(); // 學生資料取得與整理需要相關資料 if (dtStud != null) { foreach (DataRow dr in dtStud.Rows) { StudentInfo si = new StudentInfo(); if (dr["seat_no"] != null) { si.SeatNo = dr["seat_no"].ToString(); } else { si.SeatNo = ""; } si.StudentID = dr["student_id"].ToString(); si.ClassID = dr["class_id"].ToString(); si.StudentName = dr["student_name"].ToString(); // 處理學期歷程 if (dr["sems_history"] != null) { try { XElement elms = XElement.Parse("<root>" + dr["sems_history"].ToString() + "</root>"); foreach (XElement elm in elms.Elements("History")) { SemsHistoryInfo shi = new SemsHistoryInfo(); if (elm.Attribute("SchoolYear") != null) { shi.SchoolYear = elm.Attribute("SchoolYear").Value; } if (elm.Attribute("Semester") != null) { shi.Semester = elm.Attribute("Semester").Value; } if (elm.Attribute("GradeYear") != null) { shi.GradeYear = elm.Attribute("GradeYear").Value; } si.SemsHistoryInfoList.Add(shi); } } catch (Exception ex) { } } // 放入異動資料 if (studUpdaeRecDict.ContainsKey(si.StudentID)) { si.ServiceMemo = studUpdaeRecDict[si.StudentID]; } // 放入幹部資料 if (studCadDict.ContainsKey(si.StudentID)) { si.CadreInfoList = studCadDict[si.StudentID]; } // 計算幹部積分 si.CalcCadreScore(CadreName1); if (!value.ContainsKey(si.ClassID)) { value.Add(si.ClassID, new List <StudentInfo>()); } value[si.ClassID].Add(si); } } } catch (Exception ex) { throw ex; } return(value); }
/// <summary> /// 取得三年級一般狀態學生資料 /// </summary> /// <returns></returns> public static List <StudentInfo> GetStudentInfoList3() { List <StudentInfo> value = new List <StudentInfo>(); QueryHelper qh = new QueryHelper(); string qry = @" SELECT student.id AS student_id ,class.id AS class_id ,student_number ,class.class_name ,seat_no ,student.name AS student_name ,id_number ,birthdate ,CASE gender WHEN '0' THEN '2' WHEN '1' THEN '1' ELSE '' END AS gender ,sems_history FROM student INNER JOIN class ON student.ref_class_id = class.id WHERE student.status = 1 AND class.grade_year IN(3,9) ORDER BY class.grade_year,class.display_order,class.class_name,seat_no "; DataTable dt = qh.Select(qry); if (dt != null) { foreach (DataRow dr in dt.Rows) { StudentInfo si = new StudentInfo(); si.StudentID = dr["student_id"].ToString(); si.ClassID = dr["class_id"].ToString(); si.StudentNumber = dr["student_number"].ToString(); if (K12.Data.School.Code.Length >= 6) { si.SchoolCode = K12.Data.School.Code.Substring(0, 6); } else { si.SchoolCode = K12.Data.School.Code; } string className = dr["class_name"].ToString(); // 班名取2位 if (className.Length >= 2) { si.ClassName = className.Substring(className.Length - 2, 2); } else { si.ClassName = className; } // 座號補0 int no; if (int.TryParse(dr["seat_no"].ToString(), out no)) { if (no < 10) { si.SeatNo = "0" + no; } else { si.SeatNo = no + ""; } } else { si.SeatNo = ""; } si.StudentName = dr["student_name"].ToString(); si.IDNumber = dr["id_number"].ToString().Trim(); si.isTaiwanID = false; // 檢查是否台灣身分證 if (si.IDNumber.Length > 1) { string ii = si.IDNumber.Substring(1, 1); if (ii == "1" || ii == "2") { si.isTaiwanID = true; } else { si.isTaiwanID = false; } } si.GenderCode = dr["gender"].ToString(); DateTime dt1; if (DateTime.TryParse(dr["birthdate"].ToString(), out dt1)) { si.BirthYear = (dt1.Year - 1911) + ""; si.BirthMonth = dt1.Month + ""; si.BirthDay = dt1.Day + ""; } // 處理學期歷程 if (dr["sems_history"] != null) { try { XElement elms = XElement.Parse("<root>" + dr["sems_history"].ToString() + "</root>"); foreach (XElement elm in elms.Elements("History")) { SemsHistoryInfo shi = new SemsHistoryInfo(); if (elm.Attribute("SchoolYear") != null) { shi.SchoolYear = elm.Attribute("SchoolYear").Value; } if (elm.Attribute("Semester") != null) { shi.Semester = elm.Attribute("Semester").Value; } if (elm.Attribute("GradeYear") != null) { shi.GradeYear = elm.Attribute("GradeYear").Value; } si.SemsHistoryInfoList.Add(shi); } } catch (Exception ex) { } } value.Add(si); } } return(value); }