private void GetDefaultToDG()
{
dgClassName.Rows.Clear();
List <SHClassRecord> ClassRecList = SHClass.SelectAll();
ClassRecList = (from data in ClassRecList orderby data.Name select data).ToList();
foreach (SHClassRecord rec in ClassRecList)
{
int rowIdx = dgClassName.Rows.Add();
dgClassName.Rows[rowIdx].Cells[colClassName.Index].Value = rec.Name;
dgClassName.Rows[rowIdx].Cells[colSpecClassName.Index].Value = "";
}
}
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 _bgWorkerLoad_DoWork(object sender, DoWorkEventArgs e)
{
// 取得就學生畢業異動資料
_StudHasGraduateRecDict.Clear();
List <SHUpdateRecordRecord> recList = SHUpdateRecord.SelectByStudentIDs(_StudentIDList);
foreach (SHUpdateRecordRecord rec in recList)
{
// 不是畢業異動跳過
if (rec.UpdateCode != "501")
{
continue;
}
if (!_StudHasGraduateRecDict.ContainsKey(rec.StudentID))
{
_StudHasGraduateRecDict.Add(rec.StudentID, rec);
}
}
// 取得最後一筆異動
_StudLastupdateRecDict.Clear();
foreach (string sid in _StudentIDList)
{
List <SHUpdateRecordRecord> recL = (from data in recList where data.StudentID == sid && data.ADDate.Trim() != "" orderby DateTime.Parse(data.ADDate) descending, int.Parse(data.ID) descending select data).ToList();
if (recL.Count > 0)
{
SHUpdateRecordRecord r1 = recL[0];
if (!_StudLastupdateRecDict.ContainsKey(r1.StudentID))
{
_StudLastupdateRecDict.Add(r1.StudentID, r1);
}
}
}
// 學生資料
_StudentDict.Clear();
foreach (SHStudentRecord rec in SHStudent.SelectByIDs(_StudentIDList))
{
_StudentDict.Add(rec.ID, rec);
}
// 畢業證書
_LeaveInfoRecordDict.Clear();
foreach (SHLeaveInfoRecord rec in SHLeaveInfo.SelectByStudentIDs(_StudentIDList))
{
if (!_LeaveInfoRecordDict.ContainsKey(rec.RefStudentID))
{
_LeaveInfoRecordDict.Add(rec.RefStudentID, rec);
}
}
// 班級
_ClassRecDict.Clear();
foreach (SHClassRecord rec in SHClass.SelectAll())
{
if (!_ClassRecDict.ContainsKey(rec.ID))
{
_ClassRecDict.Add(rec.ID, rec);
}
}
// 科別
_DeptRecDict.Clear();
foreach (SHDepartmentRecord rec in SHDepartment.SelectAll())
{
if (!_DeptRecDict.ContainsKey(rec.ID))
{
_DeptRecDict.Add(rec.ID, rec);
}
}
}
void _bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
_DataTable = new DataTable();
// 新增 DataTable Columns
AddTableColumns();
_bgWorker.ReportProgress(1);
// word 資料合併
Document doc = new Document();
doc.Sections.Clear();
// 取得資料
_StudentRecDict.Clear();
foreach (SHStudentRecord rec in SHStudent.SelectByIDs(_StudentIDList))
{
_StudentRecDict.Add(rec.ID, rec);
}
_PhoneRecDict.Clear();
foreach (SHPhoneRecord rec in SHPhone.SelectByStudentIDs(_StudentIDList))
{
_PhoneRecDict.Add(rec.RefStudentID, rec);
}
// 入學照片
_PhotoPDict.Clear();
_PhotoPDict = K12.Data.Photo.SelectFreshmanPhoto(_StudentIDList);
// 畢業照片
_PhotoGDict.Clear();
_PhotoGDict = K12.Data.Photo.SelectGraduatePhoto(_StudentIDList);
_ParentRecDict.Clear();
foreach (SHParentRecord rec in SHParent.SelectByStudentIDs(_StudentIDList))
{
_ParentRecDict.Add(rec.RefStudentID, rec);
}
_AddressRecDict.Clear();
foreach (SHAddressRecord rec in SHAddress.SelectByStudentIDs(_StudentIDList))
{
_AddressRecDict.Add(rec.RefStudentID, rec);
}
_ClassRecDict.Clear();
foreach (SHClassRecord rec in SHClass.SelectAll())
{
_ClassRecDict.Add(rec.ID, rec);
}
// 學校名稱
string SchoolName = K12.Data.School.ChineseName;
_bgWorker.ReportProgress(30);
// 開始填入資料
foreach (string StudID in _StudentIDList)
{
DataRow row = _DataTable.NewRow();
row["學校名稱"] = SchoolName;
if (_StudentRecDict.ContainsKey(StudID))
{
if (_StudentRecDict[StudID].Department != null)
{
row["科別"] = _StudentRecDict[StudID].Department.FullName;
}
else
{
row["科別"] = "";
}
row["學號"] = _StudentRecDict[StudID].StudentNumber;
// 處理班級
if (_ClassRecDict.ContainsKey(_StudentRecDict[StudID].RefClassID))
{
row["班級"] = _ClassRecDict[_StudentRecDict[StudID].RefClassID].Name;
}
row["條碼"] = _StudentRecDict[StudID].StudentNumber;
row["姓名"] = _StudentRecDict[StudID].Name;
row["英文姓名"] = _StudentRecDict[StudID].EnglishName;
row["性別"] = _StudentRecDict[StudID].Gender;
row["身分證字號"] = _StudentRecDict[StudID].IDNumber;
if (_StudentRecDict[StudID].Birthday.HasValue)
{
DateTime dtb = _StudentRecDict[StudID].Birthday.Value;
row["生日"] = dtb.Year + "/" + dtb.Month + "/" + dtb.Day;
row["生日2"] = (dtb.Year - 1911) + "/" + dtb.Month + "/" + dtb.Day;
}
else
{
row["生日"] = "";
row["生日2"] = "";
}
}
if (_PhotoPDict.ContainsKey(StudID))
{
row["照片"] = _PhotoPDict[StudID];
row["照片2"] = _PhotoPDict[StudID];
}
if (_PhotoGDict.ContainsKey(StudID))
{
row["畢業照片"] = _PhotoGDict[StudID];
row["畢業照片2"] = _PhotoGDict[StudID];
}
if (_PhoneRecDict.ContainsKey(StudID))
{
row["戶籍電話"] = _PhoneRecDict[StudID].Permanent;
row["聯絡電話"] = _PhoneRecDict[StudID].Contact;
}
if (_ParentRecDict.ContainsKey(StudID))
{
row["監護人電話"] = _ParentRecDict[StudID].CustodianPhone;
row["監護人姓名"] = _ParentRecDict[StudID].CustodianName;
row["父親電話"] = _ParentRecDict[StudID].FatherPhone;
row["父親姓名"] = _ParentRecDict[StudID].FatherName;
row["母親電話"] = _ParentRecDict[StudID].MotherPhone;
row["母親姓名"] = _ParentRecDict[StudID].MotherName;
}
if (_AddressRecDict.ContainsKey(StudID))
{
row["戶籍地址"] = _AddressRecDict[StudID].PermanentAddress;
row["聯絡地址"] = _AddressRecDict[StudID].MailingAddress;
}
_DataTable.Rows.Add(row);
}
_bgWorker.ReportProgress(70);
int page = 1;
count = 0;
List <string> mapNameList = _Config.Template.ToDocument().MailMerge.GetFieldNames().ToList();
foreach (string str in mapNameList)
{
if (str == "姓名")
{
count++;
}
}
for (int i = 1; i <= _StudentIDList.Count; i++)
{
if (i % count == 0 && i >= count)
{
page++;
}
}
// 當人數與一頁個數相同,只有一頁
if (count == _StudentIDList.Count)
{
page = 1;
}
for (int i = 1; i <= page; i++)
{
Document document = new Document();
document = _Config.Template.ToDocument();
doc.Sections.Add(doc.ImportNode(document.Sections[0], true));
}
doc.MailMerge.MergeField += new Aspose.Words.Reporting.MergeFieldEventHandler(MailMerge_MergeField);
doc.MailMerge.Execute(_DataTable);
doc.MailMerge.RemoveEmptyParagraphs = true;
doc.MailMerge.DeleteFields();
//doc.Sections.Add(doc.ImportNode(document.Sections[0], true));
_bgWorker.ReportProgress(95);
e.Result = doc;
}
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);
// 取得部別、班別對照
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);
}