/// <summary>
/// 取得幹部資料
/// </summary>
/// <param name="StudentIDList"></param>
/// <param name="StudentInfoList"></param>
/// <returns></returns>
public static List <StudentInfo> FillCad(List <string> StudentIDList, List <StudentInfo> StudentInfoList)
{
try
{
// 取得中低收入資料
QueryHelper qh = new QueryHelper();
Dictionary <string, List <CadreInfo> > studCadDict = new Dictionary <string, List <CadreInfo> >();
string qry = @"
SELECT
student.id AS student_id
,schoolyear AS school_year
,semester
,referencetype
,cadrename
FROM
student INNER JOIN $behavior.thecadre
ON student.id = CAST($behavior.thecadre.studentid AS INTEGER)
WHERE student.status=1 AND student.id IN(" + string.Join(",", StudentIDList.ToArray()) + @")
ORDER BY student.id,$behavior.thecadre.schoolyear,semester
";
DataTable dt = qh.Select(qry);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
string sid = dr["student_id"].ToString();
if (!studCadDict.ContainsKey(sid))
{
studCadDict.Add(sid, new List <CadreInfo>());
}
CadreInfo ci = new CadreInfo();
ci.SchoolYear = dr["school_year"].ToString();
ci.Semester = dr["semester"].ToString();
ci.StudentID = dr["student_id"].ToString();
ci.CadreName = dr["cadrename"].ToString();
ci.ReferenceType = dr["referencetype"].ToString();
studCadDict[sid].Add(ci);
}
}
// 填入資料
foreach (StudentInfo si in StudentInfoList)
{
if (studCadDict.ContainsKey(si.StudentID))
{
si.CadreInfoList = studCadDict[si.StudentID];
}
}
}
catch (Exception ex)
{
// throw ex;
}
return(StudentInfoList);
}
/// <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);
}