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