/// <summary> /// 取得成績冊用學生基本資料 /// </summary> /// <param name="StudentIDList"></param> /// <returns></returns> public static List <rptStudentInfo> GetRptStudentInfoListByIDs(List <string> StudentIDList) { List <rptStudentInfo> StudentInfoList = new List <rptStudentInfo>(); if (StudentIDList.Count > 0) { QueryHelper qh = new QueryHelper(); string qry = @" SELECT student.id AS student_id ,class.class_name ,student.seat_no ,CASE student.gender WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '' END AS gender ,student.name AS student_name ,sems_history ,birthdate FROM student LEFT JOIN class ON student.ref_class_id = class.id WHERE student.status = 1 AND student.id IN(" + string.Join(",", StudentIDList.ToArray()) + @") ORDER BY class.grade_year DESC,class.display_order,class.class_name,seat_no "; DataTable dt = qh.Select(qry); if (dt != null) { foreach (DataRow dr in dt.Rows) { rptStudentInfo si = new rptStudentInfo(); si.StudentID = dr["student_id"].ToString(); si.ClassName = dr["class_name"].ToString(); si.SeatNo = dr["seat_no"].ToString(); si.Gender = dr["gender"].ToString(); si.SchoolYear = K12.Data.School.DefaultSchoolYear; si.SchoolName = K12.Data.School.ChineseName; si.Name = dr["student_name"].ToString(); DateTime dt1; if (DateTime.TryParse(dr["birthdate"].ToString(), out dt1)) { si.Birthday = dt1; } // 處理學期歷程 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; } string key = shi.SchoolYear + "_" + shi.Semester; string value = ""; if (shi.GradeYear == "1" || shi.GradeYear == "7") { if (shi.Semester == "1") { value = "七上"; } if (shi.Semester == "2") { value = "七下"; } } if (shi.GradeYear == "2" || shi.GradeYear == "8") { if (shi.Semester == "1") { value = "八上"; } if (shi.Semester == "2") { value = "八下"; } } if (shi.GradeYear == "3" || shi.GradeYear == "9") { if (shi.Semester == "1") { value = "九上"; } } if (value != "") { if (!si.SemsHistoryDict.ContainsKey(key)) { si.SemsHistoryDict.Add(key, value); } } si.SemsHistoryInfoList.Add(shi); } } catch (Exception ex) { } } StudentInfoList.Add(si); } } } return(StudentInfoList); }
/// <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 = true; // 檢查是否台灣身分證 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); }