/// <summary>
/// 取得學期對照內班級座號，轉成大學繁星代碼與相對學習歷程資料庫需要班座
/// </summary>
/// <param name="StudentIDList"></param>
/// <param name="SchoolYear"></param>
/// <param name="Semester"></param>
/// <param name="ReturnSeatNo"></param>
/// <returns></returns>
public static Dictionary <string, string> GetStudentClassCodeSeatNo(List <string> StudentIDList, int SchoolYear, int Semester, bool ReturnSeatNo)
{
Dictionary <string, string> value = new Dictionary <string, string>();
Dictionary <string, string> ClassCodeDict = new Dictionary <string, string>();
Dictionary <string, string> codeDict = new Dictionary <string, string>();
Dictionary <string, string> SeatNoDict = new Dictionary <string, string>();
// 取得學生學期對照
List <SHSemesterHistoryRecord> SHSemesterHistoryRecordList = SHSemesterHistory.SelectByStudentIDs(StudentIDList);
foreach (SHSemesterHistoryRecord rec in SHSemesterHistoryRecordList)
{
foreach (K12.Data.SemesterHistoryItem item in rec.SemesterHistoryItems)
{
if (item.SchoolYear == SchoolYear && item.Semester == Semester)
{
if (!ClassCodeDict.ContainsKey(rec.RefStudentID))
{
ClassCodeDict.Add(rec.RefStudentID, item.ClassName);
if (item.SeatNo.HasValue)
{
SeatNoDict.Add(rec.RefStudentID, string.Format("{0:00}", item.SeatNo.Value));
}
}
}
}
}
// 取得學習歷程班級代碼
try
{
codeDict = GetLHClassCodeDict();
}
catch (Exception ex) { }
// 比對資料
foreach (string sid in ClassCodeDict.Keys)
{
string cName = ClassCodeDict[sid];
if (codeDict.ContainsKey(cName))
{
// +回傳座號
if (ReturnSeatNo)
{
if (SeatNoDict.ContainsKey(sid))
{
value.Add(sid, codeDict[cName] + SeatNoDict[sid]);
}
}
else
{
// 只有班代
value.Add(sid, codeDict[cName]);
}
}
}
return(value);
}
void _bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
_bgWorker.ReportProgress(1);
List <SubjectScoreRec> SubjectScoreRecList = new List <SubjectScoreRec>();
Dictionary <string, string> DeptMappingDict = new Dictionary <string, string>();
Dictionary <string, List <string> > StudTagNameDict = new Dictionary <string, List <string> >();
// 科別對照
DeptMappingDict = Utility.GetDepartmetDict();
Dictionary <string, List <ConfigDataItem> > cdDict = _cd.GetConfigDataItemDict();
Dictionary <string, string> ClsMappingDict = new Dictionary <string, string>();
Dictionary <string, string> ClassNoMappingDict = new Dictionary <string, string>();
List <SHStudentTagRecord> SHStudentTagRecordList = SHStudentTag.SelectByStudentIDs(_StudentIDList);
// 取得學期對照轉成大學繁星代碼
Dictionary <string, string> StudentSHClassCodDict = Utility.GetStudentClassCodeSeatNo(_StudentIDList, _SchoolYear, _Semester, false);
// 班別對照
if (cdDict.ContainsKey("班別代碼"))
{
foreach (ConfigDataItem cdi in cdDict["班別代碼"])
{
if (!ClsMappingDict.ContainsKey(cdi.TargetName))
{
ClsMappingDict.Add(cdi.TargetName, cdi.Value);
}
}
}
// 取得學生類別
foreach (SHStudentTagRecord TRec in SHStudentTagRecordList)
{
if (!StudTagNameDict.ContainsKey(TRec.RefStudentID))
{
StudTagNameDict.Add(TRec.RefStudentID, new List <string>());
}
StudTagNameDict[TRec.RefStudentID].Add(TRec.FullName);
}
// 班級代碼對照
ClassNoMappingDict = Utility.GetLHClassCodeDict();
_bgWorker.ReportProgress(10);
// 取得學生科別名稱
Dictionary <string, string> StudeDeptNameDict = Utility.GetStudDeptNameDict(_StudentIDList, _SchoolYear, _Semester);
SmartSchool.Customization.Data.AccessHelper accHelper = new SmartSchool.Customization.Data.AccessHelper();
// 取得學生學期科目成績
List <SmartSchool.Customization.Data.StudentRecord> StudentRecList = accHelper.StudentHelper.GetStudents(_StudentIDList);
accHelper.StudentHelper.FillSemesterSubjectScore(true, StudentRecList);
// 取得及格標準
Dictionary <string, Dictionary <string, decimal> > passScoreDict = Utility.GetStudentApplyLimitDict(StudentRecList);
Dictionary <string, string> StudGradYearDict = new Dictionary <string, string>();
foreach (SmartSchool.Customization.Data.StudentRecord rec in StudentRecList)
{
if (!StudGradYearDict.ContainsKey(rec.StudentID))
{
if (rec.RefClass != null)
{
StudGradYearDict.Add(rec.StudentID, rec.RefClass.GradeYear);
}
}
}
// 取得學期對照年為主
List <SHSemesterHistoryRecord> SemsH = SHSemesterHistory.SelectByStudentIDs(_StudentIDList);
foreach (SHSemesterHistoryRecord rec in SemsH)
{
foreach (K12.Data.SemesterHistoryItem item in rec.SemesterHistoryItems)
{
if (item.SchoolYear == _SchoolYear && item.Semester == _Semester)
{
if (!StudGradYearDict.ContainsKey(item.RefStudentID))
{
StudGradYearDict.Add(item.RefStudentID, item.GradeYear.ToString());
}
else
{
StudGradYearDict[item.RefStudentID] = item.GradeYear.ToString();
}
}
}
}
_bgWorker.ReportProgress(30);
foreach (SmartSchool.Customization.Data.StudentRecord studRec in StudentRecList)
{
string IDNumber = studRec.IDNumber.ToUpper();
string BirthDate = "";
DateTime dt;
if (DateTime.TryParse(studRec.Birthday, out dt))
{
BirthDate = Utility.ConvertChDateString(dt);
}
// 科/班/學程別代碼
string DCLCode = "";
if (StudeDeptNameDict.ContainsKey(studRec.StudentID))
{
string name = StudeDeptNameDict[studRec.StudentID];
if (DeptMappingDict.ContainsKey(name))
{
DCLCode = DeptMappingDict[name];
}
}
// 修課班別
string ClClassName = _ClassTypeCode;
if (StudTagNameDict.ContainsKey(studRec.StudentID))
{
foreach (string str in StudTagNameDict[studRec.StudentID])
{
if (ClsMappingDict.ContainsKey(str))
{
ClClassName = ClsMappingDict[str];
}
}
}
// 修課班級
string ClassCode = "";
if (StudentSHClassCodDict.ContainsKey(studRec.StudentID))
{
ClassCode = StudentSHClassCodDict[studRec.StudentID];
}
else
{
if (K12.Data.School.DefaultSchoolYear == _SchoolYear.ToString() && K12.Data.School.DefaultSemester == _Semester.ToString())
{
if (ClassNoMappingDict.ContainsKey(studRec.RefClass.ClassName))
{
ClassCode = ClassNoMappingDict[studRec.RefClass.ClassName];
}
}
}
#region 一般與補修
foreach (SmartSchool.Customization.Data.StudentExtension.SemesterSubjectScoreInfo sssi in studRec.SemesterSubjectScoreList)
{
if (sssi.SchoolYear == _SchoolYear && sssi.Semester == _Semester)
{
SubjectScoreRec ssr = new SubjectScoreRec();
ssr.IDNumber = IDNumber;
ssr.StudentID = studRec.StudentID;
ssr.BirthDate = BirthDate;
ssr.SubjectCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
ssr.ReSubjectCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
ssr.ScSubjectCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
ssr.SubjectCredit = sssi.Detail.GetAttribute("開課學分數");
// 預設值 -1
ssr.Score = ssr.ScScore = ssr.ReScore = ssr.MuScore = "-1";
string GrStr = "";
if (StudGradYearDict.ContainsKey(studRec.StudentID))
{
GrStr = StudGradYearDict[studRec.StudentID] + "_及";
}
decimal ds, dsre, dsmu, dssc, passScore = 60;
// 及格標準
if (passScoreDict[studRec.StudentID].ContainsKey(GrStr))
{
passScore = passScoreDict[studRec.StudentID][GrStr];
}
if (decimal.TryParse(sssi.Detail.GetAttribute("原始成績"), out ds))
{
if (ds < passScore)
{
ssr.Score = "*" + string.Format("{0:##0.00}", ds);
}
else
{
ssr.Score = string.Format("{0:##0.00}", ds);
}
}
if (decimal.TryParse(sssi.Detail.GetAttribute("重修成績"), out dsre))
{
if (dsre < passScore)
{
ssr.ReScore = "*" + string.Format("{0:##0.00}", dsre);
}
else
{
ssr.ReScore = string.Format("{0:##0.00}", dsre);
}
}
if (decimal.TryParse(sssi.Detail.GetAttribute("補考成績"), out dsmu))
{
if (dsmu < passScore)
{
ssr.MuScore = "*" + string.Format("{0:##0.00}", dsmu);
}
else
{
ssr.MuScore = string.Format("{0:##0.00}", dsmu);
}
}
if (decimal.TryParse(sssi.Detail.GetAttribute("原始成績"), out dssc))
{
if (dssc < passScore)
{
ssr.ScScore = "*" + string.Format("{0:##0.00}", dssc);
}
else
{
ssr.ScScore = string.Format("{0:##0.00}", dssc);
}
}
ssr.isScScore = ssr.isReScore = false;
if (sssi.Detail.GetAttribute("是否補修成績") == "是")
{
ssr.isScScore = true;
ssr.ScSubjectGradeYearSemester = sssi.GradeYear.ToString() + sssi.Semester.ToString();
}
ssr.DCLCode = DCLCode;
ssr.ClassName = ClassCode;
ssr.ClCode = ClClassName;
SubjectScoreRecList.Add(ssr);
}
}
#endregion
#region 重修處理
foreach (SmartSchool.Customization.Data.StudentExtension.SemesterSubjectScoreInfo sssi in studRec.SemesterSubjectScoreList)
{
int chkSy = 0, chkSS = 0;
if (sssi.Detail.GetAttribute("重修學年度") != "" && sssi.Detail.GetAttribute("重修學期") != "")
{
int sy, ss;
if (int.TryParse(sssi.Detail.GetAttribute("重修學年度"), out sy))
{
chkSy = sy;
}
if (int.TryParse(sssi.Detail.GetAttribute("重修學期"), out ss))
{
chkSS = ss;
}
}
// 以有填寫重修學年度學期為主
if (chkSy == _SchoolYear && chkSS == _Semester)
{
SubjectScoreRec ssr = new SubjectScoreRec();
ssr.IDNumber = IDNumber;
ssr.StudentID = studRec.StudentID;
ssr.BirthDate = BirthDate;
ssr.SubjectCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
ssr.ReSubjectCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
ssr.ScSubjectCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
ssr.SubjectCredit = sssi.Detail.GetAttribute("開課學分數");
// 預設值 -1
ssr.Score = ssr.ScScore = ssr.ReScore = ssr.MuScore = "-1";
string GrStr = "";
if (StudGradYearDict.ContainsKey(studRec.StudentID))
{
GrStr = StudGradYearDict[studRec.StudentID] + "_及";
}
decimal ds, dsre, dsmu, dssc, passScore = 60;
// 及格標準
if (passScoreDict[studRec.StudentID].ContainsKey(GrStr))
{
passScore = passScoreDict[studRec.StudentID][GrStr];
}
if (decimal.TryParse(sssi.Detail.GetAttribute("原始成績"), out ds))
{
if (ds < passScore)
{
ssr.Score = "*" + string.Format("{0:##0.00}", ds);
}
else
{
ssr.Score = string.Format("{0:##0.00}", ds);
}
}
if (decimal.TryParse(sssi.Detail.GetAttribute("重修成績"), out dsre))
{
if (dsre < passScore)
{
ssr.ReScore = "*" + string.Format("{0:##0.00}", dsre);
}
else
{
ssr.ReScore = string.Format("{0:##0.00}", dsre);
}
}
if (decimal.TryParse(sssi.Detail.GetAttribute("補考成績"), out dsmu))
{
if (dsmu < passScore)
{
ssr.MuScore = "*" + string.Format("{0:##0.00}", dsmu);
}
else
{
ssr.MuScore = string.Format("{0:##0.00}", dsmu);
}
}
if (decimal.TryParse(sssi.Detail.GetAttribute("原始成績"), out dssc))
{
if (dssc < passScore)
{
ssr.ScScore = "*" + string.Format("{0:##0.00}", dssc);
}
else
{
ssr.ScScore = string.Format("{0:##0.00}", dssc);
}
}
ssr.isScScore = ssr.isReScore = false;
// 使用學年度為該學年度學期
ssr.ReSubjectSchoolYearSemester = string.Format("{0:000}", sssi.SchoolYear) + sssi.Semester;
ssr.isReScore = true;
ssr.DCLCode = DCLCode;
ssr.ClassName = ClassCode;
ssr.ClCode = ClClassName;
SubjectScoreRecList.Add(ssr);
}
}
#endregion
}
_bgWorker.ReportProgress(80);
// 讀取樣版並寫入 Excel
_wb = new Workbook(new MemoryStream(Properties.Resources.成績名冊樣版));
Worksheet wst1 = _wb.Worksheets["成績名冊封面"];
Worksheet wst2 = _wb.Worksheets["成績名冊"];
Worksheet wst3 = _wb.Worksheets["補修成績名冊"];
Worksheet wst4 = _wb.Worksheets["重修成績名冊"];
// wst1:學校代碼 0,學年度 1,學期 2,名冊別 3
wst1.Cells[1, 0].PutValue(_SchoolCode);
wst1.Cells[1, 1].PutValue(_SchoolYear);
wst1.Cells[1, 2].PutValue(_Semester);
wst1.Cells[1, 3].PutValue(_DocType);
// wst2:身分證號 0,出生日期 1,科目代碼 2,科目學分 3,修課科/班/學程別代碼 4,修課班級 5,修課班別 6,原始成績 7,補考成績 8
int rowIdx = 1;
foreach (SubjectScoreRec ssr in SubjectScoreRecList)
{
// 跳過補修
if (ssr.isScScore)
{
continue;
}
wst2.Cells[rowIdx, 0].PutValue(ssr.IDNumber);
wst2.Cells[rowIdx, 1].PutValue(ssr.BirthDate);
wst2.Cells[rowIdx, 2].PutValue(ssr.SubjectCode);
wst2.Cells[rowIdx, 3].PutValue(ssr.SubjectCredit);
wst2.Cells[rowIdx, 4].PutValue(ssr.DCLCode);
wst2.Cells[rowIdx, 5].PutValue(ssr.ClassName);
wst2.Cells[rowIdx, 6].PutValue(ssr.ClCode);
wst2.Cells[rowIdx, 7].PutValue(ssr.Score);
wst2.Cells[rowIdx, 8].PutValue(ssr.MuScore);
rowIdx++;
}
// wst3:身分證號 0,出生日期 1,補修科目代碼 2,補修科目開設年級及學期(選填) 3,科目學分 4,修課科/班/學程別代碼 5,修課班級 6,修課班別 7,補修成績 8,補考成績 9
rowIdx = 1;
foreach (SubjectScoreRec ssr in SubjectScoreRecList)
{
// 補修成績
if (ssr.isScScore)
{
wst3.Cells[rowIdx, 0].PutValue(ssr.IDNumber);
wst3.Cells[rowIdx, 1].PutValue(ssr.BirthDate);
wst3.Cells[rowIdx, 2].PutValue(ssr.ScSubjectCode);
wst3.Cells[rowIdx, 3].PutValue(ssr.ScSubjectGradeYearSemester);
wst3.Cells[rowIdx, 4].PutValue(ssr.SubjectCredit);
wst3.Cells[rowIdx, 5].PutValue(ssr.DCLCode);
wst3.Cells[rowIdx, 6].PutValue(ssr.ClassName);
wst3.Cells[rowIdx, 7].PutValue(ssr.ClCode);
wst3.Cells[rowIdx, 8].PutValue(ssr.ScScore);
wst3.Cells[rowIdx, 9].PutValue(ssr.MuScore);
rowIdx++;
}
}
// wst4:身分證號 0,出生日期 1,重修科目代碼 2,重修科目開設年級及學期 3,科目學分 4,修課科/班/學程別代碼 5,修課班級 6,修課班別 7,重修成績 8
rowIdx = 1;
foreach (SubjectScoreRec ssr in SubjectScoreRecList)
{
// 有重修成績學年度學期
if (ssr.isReScore)
{
wst4.Cells[rowIdx, 0].PutValue(ssr.IDNumber);
wst4.Cells[rowIdx, 1].PutValue(ssr.BirthDate);
wst4.Cells[rowIdx, 2].PutValue(ssr.ReSubjectCode);
wst4.Cells[rowIdx, 3].PutValue(ssr.ReSubjectSchoolYearSemester);
wst4.Cells[rowIdx, 4].PutValue(ssr.SubjectCredit);
wst4.Cells[rowIdx, 5].PutValue(ssr.DCLCode);
wst4.Cells[rowIdx, 6].PutValue(ssr.ClassName);
wst4.Cells[rowIdx, 7].PutValue(ssr.ClCode);
wst4.Cells[rowIdx, 8].PutValue(ssr.ReScore);
rowIdx++;
}
}
_bgWorker.ReportProgress(100);
}
/// <summary>
/// 取得學生科別名稱
/// </summary>
/// <param name="StudentIDList"></param>
/// <returns></returns>
public static Dictionary <string, string> GetStudDeptNameDict(List <string> StudentIDList, int SchoolYear, int Semester)
{
Dictionary <string, string> value = new Dictionary <string, string>();
// 如果是系統預設學年度學期,不是讀取學期對照
if (SchoolYear.ToString() == K12.Data.School.DefaultSchoolYear && Semester.ToString() == K12.Data.School.DefaultSemester)
{
if (StudentIDList.Count > 0)
{
QueryHelper qhC1 = new QueryHelper();
// 取的學生所屬班級科別
string strC1 = "select student.id as sid,dept.name as deptName from student inner join class on student.ref_class_id=class.id inner join dept on class.ref_dept_id=dept.id where student.id in(" + string.Join(",", StudentIDList.ToArray()) + ")";
DataTable dtC1 = qhC1.Select(strC1);
foreach (DataRow dr in dtC1.Rows)
{
string sid = dr["sid"].ToString();
string deptName = dr["deptName"].ToString();
if (!value.ContainsKey(sid))
{
value.Add(sid, deptName);
}
else
{
value[sid] = deptName;
}
}
QueryHelper qhS1 = new QueryHelper();
string strS1 = "select student.id as sid,dept.name as deptName from student inner join dept on student.ref_dept_id=dept.id where student.id in(" + string.Join(",", StudentIDList.ToArray()) + ")";
DataTable dtS1 = qhS1.Select(strS1);
foreach (DataRow dr in dtS1.Rows)
{
string sid = dr["sid"].ToString();
string deptName = dr["deptName"].ToString();
if (!value.ContainsKey(sid))
{
value.Add(sid, deptName);
}
else
{
value[sid] = deptName;
}
}
}
}
else
{
List <SHSemesterHistoryRecord> SHSemesterHistoryRecordList = SHSemesterHistory.SelectByStudentIDs(StudentIDList);
foreach (SHSemesterHistoryRecord shrec in SHSemesterHistoryRecordList)
{
foreach (K12.Data.SemesterHistoryItem item in shrec.SemesterHistoryItems)
{
if (item.SchoolYear == SchoolYear && item.Semester == Semester)
{
if (!value.ContainsKey(item.RefStudentID))
{
value.Add(item.RefStudentID, item.DeptName);
}
}
}
}
}
return(value);
}
public static Dictionary <string, List <StudentSCETakeRec> > GetStudentSCETakeDict(List <string> StudentIDList, string ExamID, int SchoolYear, int Semester)
{
Dictionary <string, List <StudentSCETakeRec> > value = new Dictionary <string, List <StudentSCETakeRec> >();
if (StudentIDList.Count > 0 && ExamID != "")
{
SmartSchool.Customization.Data.AccessHelper acc = new SmartSchool.Customization.Data.AccessHelper();
// 取得及格標準
List <SmartSchool.Customization.Data.StudentRecord> StudentRecList = acc.StudentHelper.GetStudents(StudentIDList);
Dictionary <string, Dictionary <string, decimal> > passScoreDict = GetStudentApplyLimitDict(StudentRecList);
Dictionary <string, string> StudGradYearDict = new Dictionary <string, string>();
foreach (SmartSchool.Customization.Data.StudentRecord rec in StudentRecList)
{
if (!StudGradYearDict.ContainsKey(rec.StudentID))
{
if (rec.RefClass != null)
{
StudGradYearDict.Add(rec.StudentID, rec.RefClass.GradeYear);
}
}
}
// 取得學期對照年為主
List <SHSemesterHistoryRecord> SemsH = SHSemesterHistory.SelectByStudentIDs(StudentIDList);
foreach (SHSemesterHistoryRecord rec in SemsH)
{
foreach (K12.Data.SemesterHistoryItem item in rec.SemesterHistoryItems)
{
if (item.SchoolYear == SchoolYear && item.Semester == Semester)
{
if (!StudGradYearDict.ContainsKey(item.RefStudentID))
{
StudGradYearDict.Add(item.RefStudentID, item.GradeYear.ToString());
}
else
{
StudGradYearDict[item.RefStudentID] = item.GradeYear.ToString();
}
}
}
}
List <SHCourseRecord> CourseRecList = SHCourse.SelectBySchoolYearAndSemester(SchoolYear, Semester);
Dictionary <string, SHCourseRecord> CourseRecDict = new Dictionary <string, SHCourseRecord>();
foreach (SHCourseRecord rec in CourseRecList)
{
if (!CourseRecDict.ContainsKey(rec.ID))
{
CourseRecDict.Add(rec.ID, rec);
}
}
QueryHelper qh = new QueryHelper();
string query = "select sc_attend.ref_student_id as sid,course.id as cid,course.course_name,course.subject,sce_take.score as sce_score,course.credit as credit from sc_attend inner join course on sc_attend.ref_course_id=course.id inner join sce_take on sc_attend.id=sce_take.ref_sc_attend_id where sc_attend.ref_student_id in(" + string.Join(",", StudentIDList.ToArray()) + ") and course.school_year=" + SchoolYear + " and course.semester=" + Semester + " and sce_take.ref_exam_id in(" + ExamID + ")";
DataTable dt = qh.Select(query);
foreach (DataRow dr in dt.Rows)
{
string SubjCode = "";
string sid = dr["sid"].ToString();
if (!value.ContainsKey(sid))
{
value.Add(sid, new List <StudentSCETakeRec>());
}
string cid = dr["cid"].ToString();
StudentSCETakeRec scetRec = new StudentSCETakeRec();
scetRec.StudentID = sid;
if (CourseRecDict.ContainsKey(cid))
{
string Req = "", notNq = "";
if (CourseRecDict[cid].Required)
{
Req = "必修";
}
else
{
Req = "選修";
}
if (CourseRecDict[cid].NotIncludedInCredit)
{
notNq = "是";
}
else
{
notNq = "否";
}
SubjCode = CourseRecDict[cid].Subject + "_" + CourseRecDict[cid].Credit.Value + "_" + Req + "_" + CourseRecDict[cid].RequiredBy + "_" + CourseRecDict[cid].Entry + "_" + notNq;
}
scetRec.SubjectCode = SubjCode;
string GrStr = "";
if (StudGradYearDict.ContainsKey(sid))
{
GrStr = StudGradYearDict[sid] + "_及";
}
scetRec.Score = "-1";
decimal dd, passScore = 0;
if (decimal.TryParse(dr["sce_score"].ToString(), out dd))
{
// 取得及格標準
if (passScoreDict.ContainsKey(sid))
{
if (passScoreDict[sid].ContainsKey(GrStr))
{
passScore = passScoreDict[sid][GrStr];
}
}
// 不及格標 *
if (dd < passScore)
{
scetRec.Score = "*" + string.Format("{0:##0.00}", dd);
}
else
{
scetRec.Score = string.Format("{0:##0.00}", dd);
}
scetRec.Status = "1";
}
else
{
// 缺考
scetRec.Status = "4";
}
scetRec.SubjectCredit = dr["credit"].ToString();
value[sid].Add(scetRec);
}
}
return(value);
}
void _bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
_bgWorker.ReportProgress(1);
Dictionary <string, string> DeptMappingDict = new Dictionary <string, string>();
// 取得科群對照
Dictionary <string, string> GroupIDDict = Utility.GetGroupDeptIDDict();
// 科別對照
DeptMappingDict = Utility.GetDepartmetDict();
// 取得學生學期對照班級
Dictionary <string, string> classNameDict = new Dictionary <string, string>();
List <SHSemesterHistoryRecord> SHSemesterHistoryRecordList = SHSemesterHistory.SelectByStudentIDs(_StudentIDList);
foreach (SHSemesterHistoryRecord rec in SHSemesterHistoryRecordList)
{
foreach (K12.Data.SemesterHistoryItem item in rec.SemesterHistoryItems)
{
if (item.SchoolYear == _SchoolYear && item.Semester == _Semester)
{
if (!classNameDict.ContainsKey(rec.RefStudentID))
{
classNameDict.Add(rec.RefStudentID, item.ClassName);
}
}
}
}
// 取得特色班對照
Dictionary <string, string> SpecClassNameDict = _cd.GetKeyValueItem(_ConfigClassName);
// 取得學生科別名稱
Dictionary <string, string> StudeDeptNameDict = Utility.GetStudDeptNameDict(_StudentIDList, _SchoolYear, _Semester);
Dictionary <string, SubjectRec> SubjectRecDict = new Dictionary <string, SubjectRec>();
SmartSchool.Customization.Data.AccessHelper accHelper = new SmartSchool.Customization.Data.AccessHelper();
// 取得學生學期科目成績
List <SmartSchool.Customization.Data.StudentRecord> StudentRecList = accHelper.StudentHelper.GetStudents(_StudentIDList);
accHelper.StudentHelper.FillSemesterSubjectScore(true, StudentRecList);
foreach (SmartSchool.Customization.Data.StudentRecord studRec in StudentRecList)
{
string studClassName = "";
if (studRec.RefClass != null)
{
studClassName = studRec.RefClass.ClassName;
}
if (classNameDict.ContainsKey(studRec.StudentID))
{
studClassName = classNameDict[studRec.StudentID];
}
// 科別
string DeptName = "";
if (StudeDeptNameDict.ContainsKey(studRec.StudentID))
{
DeptName = StudeDeptNameDict[studRec.StudentID];
}
foreach (SmartSchool.Customization.Data.StudentExtension.SemesterSubjectScoreInfo sssi in studRec.SemesterSubjectScoreList)
{
if (sssi.SchoolYear == _SchoolYear && sssi.Semester == _Semester)
{
// 需要加入科別，上傳測試過程科別也是key
string SubjCode = sssi.Detail.GetAttribute("科目") + "_" + sssi.Detail.GetAttribute("開課學分數") + "_" + sssi.Detail.GetAttribute("修課必選修") + "_" + sssi.Detail.GetAttribute("修課校部訂") + "_" + sssi.Detail.GetAttribute("開課分項類別") + "_" + sssi.Detail.GetAttribute("不計學分");
string key = DeptName + SubjCode;
if (!SubjectRecDict.ContainsKey(key))
{
SubjectRec sr = new SubjectRec();
// 科/班/學程別代碼
string DCLCode = "";
string GroupID = "";
if (StudeDeptNameDict.ContainsKey(studRec.StudentID))
{
string name = StudeDeptNameDict[studRec.StudentID];
if (DeptMappingDict.ContainsKey(name))
{
DCLCode = DeptMappingDict[name];
}
if (GroupIDDict.ContainsKey(DCLCode))
{
GroupID = GroupIDDict[DCLCode];
}
}
sr.Code = SubjCode;
sr.Name = sssi.Detail.GetAttribute("科目");
sr.CourseType = sssi.Detail.GetAttribute("修課校部訂") + sssi.Detail.GetAttribute("開課分項類別") + sssi.Detail.GetAttribute("修課必選修");
sr.DLCCode = DCLCode;
sr.DomainType = "";
sr.Group = GroupID;
sr.isCalc = sssi.Detail.GetAttribute("不計學分");
sr.Required = sssi.Detail.GetAttribute("修課必選修");
sr.SpcType = "";
if (SpecClassNameDict.ContainsKey(studClassName))
{
sr.SpcType = SpecClassNameDict[studClassName];
}
SubjectRecDict.Add(key, sr);
}
if (sssi.GradeYear == 1)
{
SubjectRecDict[key].CreditG1 = sssi.Detail.GetAttribute("開課學分數");
}
if (sssi.GradeYear == 2)
{
SubjectRecDict[key].CreditG2 = sssi.Detail.GetAttribute("開課學分數");
}
if (sssi.GradeYear == 3)
{
SubjectRecDict[key].CreditG3 = sssi.Detail.GetAttribute("開課學分數");
}
}
}
}
_bgWorker.ReportProgress(70);
Dictionary <string, string> ClassTypeDict = GetClassTypeDict();
List <SubjectRec> SubjectRecList = new List <SubjectRec>();
foreach (SubjectRec rec in SubjectRecDict.Values)
{
// 資料轉換
if (rec.Required == "必修")
{
rec.Required = "1";
}
else if (rec.Required == "選修")
{
rec.Required = "2";
}
else
{
rec.Required = "3";
}
if (rec.isCalc == "否")
{
rec.isCalc = "1";
}
else if (rec.isCalc == "是")
{
rec.isCalc = "2";
}
else
{
rec.isCalc = "3";
}
if (!string.IsNullOrEmpty(rec.CourseType))
{
if (ClassTypeDict.ContainsKey(rec.CourseType))
{
rec.CourseType = ClassTypeDict[rec.CourseType];
}
else
{
rec.CourseType = "13";
}
}
SubjectRecList.Add(rec);
}
// 寫入 Excel
_wb = new Workbook(new MemoryStream(Properties.Resources.科目名冊樣板));
Worksheet wst1 = _wb.Worksheets["科目名冊封面"];
Worksheet wst2 = _wb.Worksheets["科目名冊"];
// 學年度 0,學期 1,學校代碼 2,學校種類 3,名冊別 4
wst1.Cells[1, 0].PutValue(_SchoolYear);
wst1.Cells[1, 1].PutValue(_Semester);
wst1.Cells[1, 2].PutValue(_SchoolCode);
wst1.Cells[1, 3].PutValue(_SchoolType);
wst1.Cells[1, 4].PutValue(_DocType);
// 修課別 0,類別種類 1,領域分類 2,群別 3,科別 4,特色班/實驗班名稱 5,科目名稱 6,科目代碼 7,
// 一年級學分 8,二年級學分 9,三年級學分 10,是否計算學分 11
int rowIdx = 1;
foreach (SubjectRec rec in SubjectRecList)
{
wst2.Cells[rowIdx, 0].PutValue(rec.Required);
wst2.Cells[rowIdx, 1].PutValue(rec.CourseType);
wst2.Cells[rowIdx, 2].PutValue(rec.DomainType);
wst2.Cells[rowIdx, 3].PutValue(rec.Group);
wst2.Cells[rowIdx, 4].PutValue(rec.DLCCode);
wst2.Cells[rowIdx, 5].PutValue(rec.SpcType);
wst2.Cells[rowIdx, 6].PutValue(rec.Name);
wst2.Cells[rowIdx, 7].PutValue(rec.Code);
wst2.Cells[rowIdx, 8].PutValue(rec.CreditG1);
wst2.Cells[rowIdx, 9].PutValue(rec.CreditG2);
wst2.Cells[rowIdx, 10].PutValue(rec.CreditG3);
wst2.Cells[rowIdx, 11].PutValue(rec.isCalc);
rowIdx++;
}
}
