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