/// <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);
}
