void _bgWorker_DoWork(object sender, DoWorkEventArgs e) { _bgWorker.ReportProgress(1); // 取得部別、班別對照 ConfigData cd = new ConfigData(); Dictionary <string, List <ConfigDataItem> > cdDict = cd.GetConfigDataItemDict(); Dictionary <string, string> DepMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClsMappingDict = new Dictionary <string, string>(); Dictionary <string, string> DeptMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClassNoMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClassIDNameDict = new Dictionary <string, string>(); Dictionary <string, List <string> > StudTagNameDict = new Dictionary <string, List <string> >(); List <SHStudentTagRecord> SHStudentTagRecordList = SHStudentTag.SelectByStudentIDs(_StudentIDList); // 取得學生科別名稱 Dictionary <string, string> StudeDeptNameDict = Utility.GetStudDeptNameDict(_StudentIDList, _SchoolYear, _Semester); // 取得學生類別 foreach (SHStudentTagRecord TRec in SHStudentTagRecordList) { if (!StudTagNameDict.ContainsKey(TRec.RefStudentID)) { StudTagNameDict.Add(TRec.RefStudentID, new List <string>()); } StudTagNameDict[TRec.RefStudentID].Add(TRec.FullName); } _bgWorker.ReportProgress(20); foreach (SHClassRecord rec in SHClass.SelectAll()) { ClassIDNameDict.Add(rec.ID, rec.Name); } // 班級代碼對照 ClassNoMappingDict = Utility.GetLHClassCodeDict(); // 班別對照 if (cdDict.ContainsKey("班別代碼")) { foreach (ConfigDataItem cdi in cdDict["班別代碼"]) { if (!ClsMappingDict.ContainsKey(cdi.TargetName)) { ClsMappingDict.Add(cdi.TargetName, cdi.Value); } } } // 科別對照 DeptMappingDict = Utility.GetDepartmetDict(); // 取得學生基本資料 List <SHStudentRecord> StudentRecList = SHStudent.SelectByIDs(_StudentIDList); _bgWorker.ReportProgress(40); // 取得學期對照轉成大學繁星代碼 Dictionary <string, string> StudentSHClassCodDict = Utility.GetStudentClassCodeSeatNo(_StudentIDList, _SchoolYear, _Semester, false); // 取得學生定期成績資料 Dictionary <string, List <StudentSCETakeRec> > StudSCETakeDict = Utility.GetStudentSCETakeDict(_StudentIDList, _ExamID, _SchoolYear, _Semester); // 填入對應值 foreach (SHStudentRecord StudRec in StudentRecList) { if (StudSCETakeDict.ContainsKey(StudRec.ID)) { string IDNumber = StudRec.IDNumber.ToUpper(); string BirthDate = Utility.ConvertChDateString(StudRec.Birthday); // 科/班/學程別代碼 string DCLCode = "000"; if (StudeDeptNameDict.ContainsKey(StudRec.ID)) { string name = StudeDeptNameDict[StudRec.ID]; if (DeptMappingDict.ContainsKey(name)) { DCLCode = DeptMappingDict[name]; } } // 修課班別 string ClClassName = _ClassTypeCode; if (StudTagNameDict.ContainsKey(StudRec.ID)) { foreach (string str in StudTagNameDict[StudRec.ID]) { if (ClsMappingDict.ContainsKey(str)) { ClClassName = ClsMappingDict[str]; } } } // 修課班級 string ClassCode = "000"; if (StudentSHClassCodDict.ContainsKey(StudRec.ID)) { ClassCode = StudentSHClassCodDict[StudRec.ID]; } else { if (K12.Data.School.DefaultSchoolYear == _SchoolYear.ToString() && K12.Data.School.DefaultSemester == _Semester.ToString()) { if (ClassIDNameDict.ContainsKey(StudRec.RefClassID)) { string cName = ClassIDNameDict[StudRec.RefClassID]; if (ClassNoMappingDict.ContainsKey(cName)) { ClassCode = ClassNoMappingDict[cName]; } } } } foreach (StudentSCETakeRec rec in StudSCETakeDict[StudRec.ID]) { rec.IDNumber = IDNumber; rec.BirthDate = BirthDate; rec.ClassName = ClassCode; rec.ClClassName = ClClassName; rec.DCLCode = DCLCode; } } } _bgWorker.ReportProgress(70); List <StudentSCETakeRec> StudentSCETakeRecList = new List <StudentSCETakeRec>(); foreach (List <StudentSCETakeRec> recList in StudSCETakeDict.Values) { foreach (StudentSCETakeRec rec in recList) { StudentSCETakeRecList.Add(rec); } } // 排序 依身分證,科目代碼 StudentSCETakeRecList = (from data in StudentSCETakeRecList orderby data.IDNumber ascending, data.SubjectCode ascending select data).ToList(); // 填入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(_SchoolCode); wst1.Cells[1, 1].PutValue(_SchoolYear); wst1.Cells[1, 2].PutValue(_Semester); wst1.Cells[1, 3].PutValue(_ExamNo); wst1.Cells[1, 4].PutValue(_DocType); // 身分證號 0,出生日期 1,科目代碼 2,科目學分 3,修課科/班/學程別代碼 4, //修課班級 5,修課班別 6,考查成績 7,狀態代碼 8 int rowIdx = 1; foreach (StudentSCETakeRec rec in StudentSCETakeRecList) { wst2.Cells[rowIdx, 0].PutValue(rec.IDNumber); wst2.Cells[rowIdx, 1].PutValue(rec.BirthDate); wst2.Cells[rowIdx, 2].PutValue(rec.SubjectCode); wst2.Cells[rowIdx, 3].PutValue(rec.SubjectCredit); wst2.Cells[rowIdx, 4].PutValue(rec.DCLCode); wst2.Cells[rowIdx, 5].PutValue(rec.ClassName); wst2.Cells[rowIdx, 6].PutValue(rec.ClClassName); wst2.Cells[rowIdx, 7].PutValue(rec.Score); wst2.Cells[rowIdx, 8].PutValue(rec.Status); rowIdx++; } _bgWorker.ReportProgress(100); }
void _bgWorker_DoWork(object sender, DoWorkEventArgs e) { _bgWorker.ReportProgress(1); // 取得部別、班別對照 ConfigData cd = new ConfigData(); Dictionary <string, List <ConfigDataItem> > cdDict = cd.GetConfigDataItemDict(); Dictionary <string, string> DepMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClsMappingDict = new Dictionary <string, string>(); Dictionary <string, string> DeptMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClassNoMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClassIDNameDict = new Dictionary <string, string>(); Dictionary <string, List <string> > StudTagNameDict = new Dictionary <string, List <string> >(); List <SHStudentTagRecord> SHStudentTagRecordList = SHStudentTag.SelectByStudentIDs(_StudentIDList); // 取得學期對照班級座號轉成大學繁星班代 Dictionary <string, string> StudentSHClassSeatNoDict = Utility.GetStudentClassCodeSeatNo(_StudentIDList, _SchoolYear, _Semester, true); // 取得學生科別名稱 Dictionary <string, string> StudeDeptNameDict = Utility.GetStudDeptNameDict(_StudentIDList, _SchoolYear, _Semester); // 取得學生異動資料 List <SHUpdateRecordRecord> SHUpdateRecordRecordList = SHUpdateRecord.SelectByStudentIDs(_StudentIDList); Dictionary <string, List <SHUpdateRecordRecord> > SHUpdateRecordRecordDict = new Dictionary <string, List <SHUpdateRecordRecord> >(); // 沒有核准日期文號不排入,並符合所選的學年度學期 foreach (SHUpdateRecordRecord urRec in SHUpdateRecordRecordList) { if (string.IsNullOrWhiteSpace(urRec.ADDate) || string.IsNullOrWhiteSpace(urRec.ADNumber)) { continue; } if (urRec.SchoolYear.HasValue && urRec.Semester.HasValue) { if (urRec.SchoolYear <= _SchoolYear && urRec.Semester <= _Semester) { if (!SHUpdateRecordRecordDict.ContainsKey(urRec.StudentID)) { SHUpdateRecordRecordDict.Add(urRec.StudentID, new List <SHUpdateRecordRecord>()); } SHUpdateRecordRecordDict[urRec.StudentID].Add(urRec); } } } // 取得學生類別 foreach (SHStudentTagRecord TRec in SHStudentTagRecordList) { if (!StudTagNameDict.ContainsKey(TRec.RefStudentID)) { StudTagNameDict.Add(TRec.RefStudentID, new List <string>()); } StudTagNameDict[TRec.RefStudentID].Add(TRec.FullName); } _bgWorker.ReportProgress(20); foreach (SHClassRecord rec in SHClass.SelectAll()) { ClassIDNameDict.Add(rec.ID, rec.Name); } // 部別對照 if (cdDict.ContainsKey("部別代碼")) { foreach (ConfigDataItem cdi in cdDict["部別代碼"]) { if (!DepMappingDict.ContainsKey(cdi.TargetName)) { DepMappingDict.Add(cdi.TargetName, cdi.Value); } } } // 班別對照 if (cdDict.ContainsKey("班別代碼")) { foreach (ConfigDataItem cdi in cdDict["班別代碼"]) { if (!ClsMappingDict.ContainsKey(cdi.TargetName)) { ClsMappingDict.Add(cdi.TargetName, cdi.Value); } } } // 科別對照 DeptMappingDict = Utility.GetDepartmetDict(); // 班級代碼對照 ClassNoMappingDict = Utility.GetLHClassCodeDict(); _bgWorker.ReportProgress(40); // 取得學生本資料 List <SHStudentRecord> StudentRecordList = SHStudent.SelectByIDs(_StudentIDList); List <StudentBaseRec> StudentBaseRecList = new List <StudentBaseRec>(); // 整理資料 foreach (SHStudentRecord studRec in StudentRecordList) { // 身分證號,出生日期,所屬學校代碼,科/班/學程別代碼,部別,班別,班級座號代碼 StudentBaseRec sbr = new StudentBaseRec(); sbr.StudentNumber = studRec.StudentNumber; sbr.IDNumber = studRec.IDNumber.ToUpper(); sbr.BirthDate = Utility.ConvertChDateString(studRec.Birthday); sbr.SchoolCode = _SchoolCode; sbr.Name = studRec.Name; if (studRec.Gender == "男") { sbr.GenderCode = "1"; } if (studRec.Gender == "女") { sbr.GenderCode = "2"; } // 科/班/學程別代碼 sbr.DCLCode = ""; if (StudeDeptNameDict.ContainsKey(studRec.ID)) { string name = StudeDeptNameDict[studRec.ID]; if (DeptMappingDict.ContainsKey(name)) { sbr.DCLCode = DeptMappingDict[name]; } } // 部別 sbr.DepCode = _DepCode; // 班別 sbr.ClCode = _ClassCode; if (StudTagNameDict.ContainsKey(studRec.ID)) { foreach (string str in StudTagNameDict[studRec.ID]) { if (DepMappingDict.ContainsKey(str)) { sbr.DepCode = DepMappingDict[str]; } if (ClsMappingDict.ContainsKey(str)) { sbr.ClCode = ClsMappingDict[str]; } } } // 班級座號代碼 sbr.ClassSeatCode = ""; if (StudentSHClassSeatNoDict.ContainsKey(studRec.ID)) { sbr.ClassSeatCode = StudentSHClassSeatNoDict[studRec.ID]; if (sbr.ClassSeatCode.Length == 5) { sbr.ClassCode = sbr.ClassSeatCode.Substring(0, 3); } } else { //如果學年度學期和系統預設相同 if (K12.Data.School.DefaultSchoolYear == _SchoolYear.ToString() && K12.Data.School.DefaultSemester == _Semester.ToString()) { if (ClassIDNameDict.ContainsKey(studRec.RefClassID)) { string cName = ClassIDNameDict[studRec.RefClassID]; if (ClassNoMappingDict.ContainsKey(cName) && studRec.SeatNo.HasValue) { sbr.ClassSeatCode = ClassNoMappingDict[cName] + string.Format("{0:00}", studRec.SeatNo.Value); sbr.ClassCode = ClassNoMappingDict[cName]; } } } } // 處理異動相關 if (SHUpdateRecordRecordDict.ContainsKey(studRec.ID)) { // 排序 List <SHUpdateRecordRecord> UrDataList = (from data in SHUpdateRecordRecordDict[studRec.ID] orderby DateTime.Parse(data.ADDate) descending, int.Parse(data.ID) descending select data).ToList(); if (UrDataList.Count > 0) { SHUpdateRecordRecord rec = UrDataList[0]; sbr.PermrecCode = rec.UpdateCode; DateTime dt = DateTime.Parse(rec.UpdateDate); sbr.PermrecDate = Utility.ConvertChDateString(dt); sbr.OrBirthDate = "-1"; sbr.OrIDNumber = "-1"; sbr.Remak1 = rec.IDNumberComment; sbr.SpCode = rec.SpecialStatus; } foreach (SHUpdateRecordRecord rec in UrDataList) { // 再次檢查身分證與生日是否有差異 int iUcode = int.Parse(rec.UpdateCode); if (iUcode > 400 && iUcode < 411) { if (rec.IDNumber.ToUpper() != studRec.IDNumber.ToUpper()) { sbr.OrIDNumber = rec.IDNumber.ToUpper(); } if (studRec.Birthday.HasValue) { if (studRec.Birthday.Value.ToShortDateString() != rec.Birthdate) { DateTime dto = DateTime.Parse(rec.Birthdate); sbr.OrBirthDate = Utility.ConvertChDateString(dto); } } } } } StudentBaseRecList.Add(sbr); } _bgWorker.ReportProgress(80); // 排序 班級座號代碼 StudentBaseRecList = (from data in StudentBaseRecList orderby data.ClassSeatCode ascending select data).ToList(); // 統計人數 Dictionary <string, StudentBaseRecCount> StudentBaseRecCountDict = new Dictionary <string, StudentBaseRecCount>(); foreach (StudentBaseRec rec in StudentBaseRecList) { string key = rec.ClCode + rec.DCLCode + rec.DepCode + rec.ClassCode; if (!StudentBaseRecCountDict.ContainsKey(key)) { StudentBaseRecCount sbrc = new StudentBaseRecCount(); sbrc.ClassCode = rec.ClassCode; sbrc.ClCode = rec.ClCode; sbrc.DCLCode = rec.DCLCode; sbrc.DepCode = rec.DepCode; sbrc.DocType = _DocType; sbrc.SchoolCode = _SchoolCode; sbrc.SchoolYear = _SchoolYear; sbrc.Semester = _Semester; sbrc.StudentCount = 0; StudentBaseRecCountDict.Add(key, sbrc); } StudentBaseRecCountDict[key].StudentCount++; } List <StudentBaseRecCount> StudentBaseRecCountList = new List <StudentBaseRecCount>(); foreach (string Key in StudentBaseRecCountDict.Keys) { StudentBaseRecCountList.Add(StudentBaseRecCountDict[Key]); } // 排序,依班級代碼 StudentBaseRecCountList = (from data in StudentBaseRecCountList orderby data.ClassCode ascending select data).ToList(); // 填值到 Excel _wb = new Workbook(new MemoryStream(Properties.Resources.學生資料名冊樣板_非國教署主管學校_)); Worksheet wst1 = _wb.Worksheets["學生資料名冊封面"]; Worksheet wst2 = _wb.Worksheets["學生資料名冊"]; int rowIdx = 1; foreach (StudentBaseRecCount sbrc in StudentBaseRecCountList) { //學校代碼 0,學年度 1,學期 2,名冊別 3,科/班/學程別代碼 4,年級班級代碼 5,部別 6,班別 7,班級人數 8 wst1.Cells[rowIdx, 0].PutValue(sbrc.SchoolCode); wst1.Cells[rowIdx, 1].PutValue(sbrc.SchoolYear); wst1.Cells[rowIdx, 2].PutValue(sbrc.Semester); wst1.Cells[rowIdx, 3].PutValue(sbrc.DocType); wst1.Cells[rowIdx, 4].PutValue(sbrc.DCLCode); wst1.Cells[rowIdx, 5].PutValue(sbrc.ClassCode); wst1.Cells[rowIdx, 6].PutValue(sbrc.DepCode); wst1.Cells[rowIdx, 7].PutValue(sbrc.ClCode); wst1.Cells[rowIdx, 8].PutValue(sbrc.StudentCount); rowIdx++; } rowIdx = 1; foreach (StudentBaseRec sbr in StudentBaseRecList) { // 學號 0,身分證號 1,註1 2,姓名 3,性別代碼 4,出生日期 5,所屬學校代碼 6,科/班/學程別代碼 7,部別 8, //班別 9,班級座號代碼 10,特殊身分代碼 11,原身分證號 12,原出生日期 13,學籍狀態代碼 14,學籍狀態生效日期 15 wst2.Cells[rowIdx, 0].PutValue(sbr.StudentNumber); wst2.Cells[rowIdx, 1].PutValue(sbr.IDNumber); wst2.Cells[rowIdx, 2].PutValue(sbr.Remak1); wst2.Cells[rowIdx, 3].PutValue(sbr.Name); wst2.Cells[rowIdx, 4].PutValue(sbr.GenderCode); wst2.Cells[rowIdx, 5].PutValue(sbr.BirthDate); wst2.Cells[rowIdx, 6].PutValue(sbr.SchoolCode); wst2.Cells[rowIdx, 7].PutValue(sbr.DCLCode); wst2.Cells[rowIdx, 8].PutValue(sbr.DepCode); wst2.Cells[rowIdx, 9].PutValue(sbr.ClCode); wst2.Cells[rowIdx, 10].PutValue(sbr.ClassSeatCode); wst2.Cells[rowIdx, 11].PutValue(sbr.SpCode); wst2.Cells[rowIdx, 12].PutValue(sbr.OrIDNumber); wst2.Cells[rowIdx, 13].PutValue(sbr.OrBirthDate); wst2.Cells[rowIdx, 14].PutValue(sbr.PermrecCode); wst2.Cells[rowIdx, 15].PutValue(sbr.PermrecDate); rowIdx++; } _bgWorker.ReportProgress(100); }
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); }
void _bgWorker_DoWork(object sender, DoWorkEventArgs e) { _bgWorker.ReportProgress(1); // 取得部別、班別對照 ConfigData cd = new ConfigData(); Dictionary <string, List <ConfigDataItem> > cdDict = cd.GetConfigDataItemDict(); Dictionary <string, string> DepMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClsMappingDict = new Dictionary <string, string>(); Dictionary <string, string> DeptMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClassNoMappingDict = new Dictionary <string, string>(); Dictionary <string, string> ClassIDNameDict = new Dictionary <string, string>(); Dictionary <string, List <string> > StudTagNameDict = new Dictionary <string, List <string> >(); List <SHStudentTagRecord> SHStudentTagRecordList = SHStudentTag.SelectByStudentIDs(_StudentIDList); // 取得學期對照班級座號轉成大學繁星班代 Dictionary <string, string> StudentSHClassSeatNoDict = Utility.GetStudentClassCodeSeatNo(_StudentIDList, _SchoolYear, _Semester, true); // 取得學生科別名稱 Dictionary <string, string> StudeDeptNameDict = Utility.GetStudDeptNameDict(_StudentIDList, _SchoolYear, _Semester); // 取得學生類別 foreach (SHStudentTagRecord TRec in SHStudentTagRecordList) { if (!StudTagNameDict.ContainsKey(TRec.RefStudentID)) { StudTagNameDict.Add(TRec.RefStudentID, new List <string>()); } StudTagNameDict[TRec.RefStudentID].Add(TRec.FullName); } _bgWorker.ReportProgress(20); foreach (SHClassRecord rec in SHClass.SelectAll()) { ClassIDNameDict.Add(rec.ID, rec.Name); } // 部別對照 if (cdDict.ContainsKey("部別代碼")) { foreach (ConfigDataItem cdi in cdDict["部別代碼"]) { if (!DepMappingDict.ContainsKey(cdi.TargetName)) { DepMappingDict.Add(cdi.TargetName, cdi.Value); } } } // 班別對照 if (cdDict.ContainsKey("班別代碼")) { foreach (ConfigDataItem cdi in cdDict["班別代碼"]) { if (!ClsMappingDict.ContainsKey(cdi.TargetName)) { ClsMappingDict.Add(cdi.TargetName, cdi.Value); } } } // 科別對照 DeptMappingDict = Utility.GetDepartmetDict(); // 班級代碼對照 ClassNoMappingDict = Utility.GetLHClassCodeDict(); _bgWorker.ReportProgress(40); // 取得學生本資料 List <SHStudentRecord> StudentRecordList = SHStudent.SelectByIDs(_StudentIDList); List <StudentBaseRec> StudentBaseRecList = new List <StudentBaseRec>(); // 整理資料 foreach (SHStudentRecord studRec in StudentRecordList) { // 身分證號,出生日期,所屬學校代碼,科/班/學程別代碼,部別,班別,班級座號代碼 StudentBaseRec sbr = new StudentBaseRec(); sbr.IDNumber = studRec.IDNumber.ToUpper(); sbr.BirthDate = Utility.ConvertChDateString(studRec.Birthday); sbr.SchoolCode = _SchoolCode; // 科/班/學程別代碼 sbr.DCLCode = ""; if (StudeDeptNameDict.ContainsKey(studRec.ID)) { string name = StudeDeptNameDict[studRec.ID]; if (DeptMappingDict.ContainsKey(name)) { sbr.DCLCode = DeptMappingDict[name]; } } // 部別 sbr.DepCode = _DepCode; // 班別 sbr.ClCode = _ClassCode; if (StudTagNameDict.ContainsKey(studRec.ID)) { foreach (string str in StudTagNameDict[studRec.ID]) { if (DepMappingDict.ContainsKey(str)) { sbr.DepCode = DepMappingDict[str]; } if (ClsMappingDict.ContainsKey(str)) { sbr.ClCode = ClsMappingDict[str]; } } } // 班級座號代碼 sbr.ClassSeatCode = ""; if (StudentSHClassSeatNoDict.ContainsKey(studRec.ID)) { sbr.ClassSeatCode = StudentSHClassSeatNoDict[studRec.ID]; } else { //如果學年度學期和系統預設相同 if (K12.Data.School.DefaultSchoolYear == _SchoolYear.ToString() && K12.Data.School.DefaultSemester == _Semester.ToString()) { if (ClassIDNameDict.ContainsKey(studRec.RefClassID)) { string cName = ClassIDNameDict[studRec.RefClassID]; if (ClassNoMappingDict.ContainsKey(cName) && studRec.SeatNo.HasValue) { sbr.ClassSeatCode = ClassNoMappingDict[cName] + string.Format("{0:00}", studRec.SeatNo.Value); } } } } StudentBaseRecList.Add(sbr); } _bgWorker.ReportProgress(80); // 排序 班級座號代碼 StudentBaseRecList = (from data in StudentBaseRecList orderby data.ClassSeatCode ascending select data).ToList(); // 填值到 Excel _wb = new Workbook(new MemoryStream(Properties.Resources.學生資料名冊樣板_國教署主管學校_)); Worksheet wst1 = _wb.Worksheets["學生資料名冊封面"]; Worksheet wst2 = _wb.Worksheets["學生資料名冊"]; // 學校代碼,學年度,學期,名冊別 wst1.Cells[1, 0].PutValue(_SchoolCode); wst1.Cells[1, 1].PutValue(_SchoolYear); wst1.Cells[1, 2].PutValue(_Semester); wst1.Cells[1, 3].PutValue(_DocType); // 身分證號,出生日期,所屬學校代碼,科/班/學程別代碼,部別,班別,班級座號代碼 int rowIdx = 1; foreach (StudentBaseRec sbr in StudentBaseRecList) { wst2.Cells[rowIdx, 0].PutValue(sbr.IDNumber); wst2.Cells[rowIdx, 1].PutValue(sbr.BirthDate); wst2.Cells[rowIdx, 2].PutValue(sbr.SchoolCode); wst2.Cells[rowIdx, 3].PutValue(sbr.DCLCode); wst2.Cells[rowIdx, 4].PutValue(sbr.DepCode); wst2.Cells[rowIdx, 5].PutValue(sbr.ClCode); wst2.Cells[rowIdx, 6].PutValue(sbr.ClassSeatCode); rowIdx++; } _bgWorker.ReportProgress(100); }