private void BgWorkerReport_DoWork(object sender, DoWorkEventArgs e) { errorMsgList.Clear(); bgWorkerReport.ReportProgress(1); // 讀取資料 // 取得所選學生資料 List <rptStudentInfo> StudentInfoList = QueryData.GetRptStudentInfoListByIDs(StudentIDList); // 轉換各項類別對照值 Dictionary <string, string> MappingTag1 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag2 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag3 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag4 = new Dictionary <string, string>(); // 取得學生類別 Dictionary <string, List <string> > StudentTagDict = QueryData.GetStudentTagName(StudentIDList); try { // 解析對照設定 XElement elmRoot = XElement.Parse(_Configure.MappingContent); if (elmRoot != null) { foreach (XElement elm in elmRoot.Elements("Group")) { string gpName = elm.Attribute("Name").Value; if (gpName == "學生身分") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag1.ContainsKey(tagName) && tagName.Length > 0) { MappingTag1.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "身心障礙") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag2.ContainsKey(tagName) && tagName.Length > 0) { MappingTag2.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "學生報名身分設定") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag3.ContainsKey(tagName) && tagName.Length > 0) { MappingTag3.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "失業勞工子女") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag4.ContainsKey(tagName) && tagName.Length > 0) { MappingTag4.Add(tagName, elm1.Attribute("Code").Value); } } } } } } catch (Exception ex) { errorMsgList.Add(ex.Message); } // 填入身心障礙生,計算體適能會用到 foreach (rptStudentInfo si in StudentInfoList) { if (StudentTagDict.ContainsKey(si.StudentID)) { foreach (string tagName in StudentTagDict[si.StudentID]) { if (MappingTag2.ContainsKey(tagName)) { si.isSpecial = true; } } } } bgWorkerReport.ReportProgress(10); // 取得學生學期成績 StudentInfoList = QueryData.FillRptDomainScoreInfo(StudentIDList, StudentInfoList); bgWorkerReport.ReportProgress(20); // 取得獎懲紀錄 StudentInfoList = QueryData.FillRptMeritDemeritInfo(StudentIDList, StudentInfoList, _Configure.EndDate); // 取得服務學習紀錄 StudentInfoList = QueryData.FillRptServiceInfo(StudentIDList, StudentInfoList, _Configure.EndDate); // 取得體適能紀錄 StudentInfoList = QueryData.FillRptFitnessInfo(StudentIDList, StudentInfoList); // 取得低收入並填入 StudentInfoList = QueryData.FillrptIncomeType(StudentIDList, StudentInfoList); // 取得學生競賽資料並填入 StudentInfoList = QueryData.FillStudentCompetitionPerformance(StudentIDList, StudentInfoList); bgWorkerReport.ReportProgress(50); StudentDocDict.Clear(); StudentDocNameDict.Clear(); List <string> domainNameList = new List <string>(); domainNameList.Add("健康與體育"); domainNameList.Add("藝術"); domainNameList.Add("綜合活動"); //競賽統計 Dictionary <string, int> CompPerformanceCountDict = new Dictionary <string, int>(); // 整理資料,填入 DataTable foreach (rptStudentInfo si in StudentInfoList) { // 因為每位學生,所以用複製一份 Document docTemplate = _Configure.Template.Clone(); if (docTemplate == null) { docTemplate = new Document(new MemoryStream(Properties.Resources.義區成績冊樣板)); } // 每位學生一個 Word檔案 #region 產生合併欄位 DataTable dtTable = new DataTable(); dtTable.Columns.Add("學年度"); dtTable.Columns.Add("學校名稱"); dtTable.Columns.Add("班級"); dtTable.Columns.Add("座號"); dtTable.Columns.Add("姓名"); dtTable.Columns.Add("扶助弱勢_身分"); dtTable.Columns.Add("扶助弱勢_積分"); foreach (string name in domainNameList) { dtTable.Columns.Add("均衡學習_" + name + "_七上分數"); dtTable.Columns.Add("均衡學習_" + name + "_七下分數"); dtTable.Columns.Add("均衡學習_" + name + "_八上分數"); dtTable.Columns.Add("均衡學習_" + name + "_八下分數"); dtTable.Columns.Add("均衡學習_" + name + "_九上分數"); dtTable.Columns.Add("均衡學習_" + name + "_平均"); } dtTable.Columns.Add("均衡學習_積分"); dtTable.Columns.Add("品德表現_獎懲_大功統計"); dtTable.Columns.Add("品德表現_獎懲_小功統計"); dtTable.Columns.Add("品德表現_獎懲_嘉獎統計"); dtTable.Columns.Add("品德表現_獎懲_大過統計"); dtTable.Columns.Add("品德表現_獎懲_小過統計"); dtTable.Columns.Add("品德表現_獎懲_警告統計"); dtTable.Columns.Add("品德表現_獎懲_銷過統計"); dtTable.Columns.Add("品德表現_獎懲_積分"); dtTable.Columns.Add("品德表現_服務學習_積分"); dtTable.Columns.Add("品德表現_服務學習_校內時數統計"); dtTable.Columns.Add("品德表現_服務學習_校外時數統計"); dtTable.Columns.Add("品德表現_服務學習_校內外時數統計"); dtTable.Columns.Add("品德表現_體適能_積分"); for (int i = 1; i <= 100; i++) { dtTable.Columns.Add("品德表現_獎懲_獎懲日期" + i); dtTable.Columns.Add("品德表現_獎懲_學期" + i); dtTable.Columns.Add("品德表現_獎懲_獎懲事由" + i); dtTable.Columns.Add("品德表現_獎懲_大功" + i); dtTable.Columns.Add("品德表現_獎懲_小功" + i); dtTable.Columns.Add("品德表現_獎懲_嘉獎" + i); dtTable.Columns.Add("品德表現_獎懲_大過" + i); dtTable.Columns.Add("品德表現_獎懲_小過" + i); dtTable.Columns.Add("品德表現_獎懲_警告" + i); dtTable.Columns.Add("品德表現_獎懲_銷過" + i); } for (int i = 1; i <= 50; i++) { dtTable.Columns.Add("品德表現_服務學習_資料輸入日期" + i); dtTable.Columns.Add("品德表現_服務學習_校內外" + i); dtTable.Columns.Add("品德表現_服務學習_服務時數" + i); dtTable.Columns.Add("品德表現_服務學習_服務學習活動內容" + i); dtTable.Columns.Add("品德表現_服務學習_服務學習證明單位" + i); } for (int i = 1; i <= 12; i++) { dtTable.Columns.Add("品德表現_體適能_檢測日期" + i); dtTable.Columns.Add("品德表現_體適能_年齡" + i); dtTable.Columns.Add("品德表現_體適能_性別" + i); dtTable.Columns.Add("品德表現_體適能_坐姿體前彎_成績" + i); dtTable.Columns.Add("品德表現_體適能_立定跳遠_成績" + i); dtTable.Columns.Add("品德表現_體適能_仰臥起坐_成績" + i); dtTable.Columns.Add("品德表現_體適能_公尺跑走_成績" + i); dtTable.Columns.Add("品德表現_體適能_坐姿體前彎_等級" + i); dtTable.Columns.Add("品德表現_體適能_立定跳遠_等級" + i); dtTable.Columns.Add("品德表現_體適能_仰臥起坐_等級" + i); dtTable.Columns.Add("品德表現_體適能_公尺跑走_等級" + i); } #endregion // 加入競賽成績合併欄位 for (int i = 1; i <= 30; i++) { dtTable.Columns.Add("競賽成績_競賽層級" + i); dtTable.Columns.Add("競賽成績_競賽性質" + i); dtTable.Columns.Add("競賽成績_競賽名稱" + i); dtTable.Columns.Add("競賽成績_得獎名次" + i); dtTable.Columns.Add("競賽成績_證書日期" + i); dtTable.Columns.Add("競賽成績_主辦單位" + i); } CompPerformanceCountDict.Clear(); for (int i = 1; i <= 8; i++) { dtTable.Columns.Add("競賽成績_縣市個人名次" + i); dtTable.Columns.Add("競賽成績_縣市團體名次" + i); dtTable.Columns.Add("競賽成績_全國個人名次" + i); dtTable.Columns.Add("競賽成績_全國團體名次" + i); CompPerformanceCountDict.Add("競賽成績_縣市個人名次" + i, 0); CompPerformanceCountDict.Add("競賽成績_縣市團體名次" + i, 0); CompPerformanceCountDict.Add("競賽成績_全國個人名次" + i, 0); CompPerformanceCountDict.Add("競賽成績_全國團體名次" + i, 0); } dtTable.Columns.Add("競賽成績_縣市個人名次_其他"); dtTable.Columns.Add("競賽成績_縣市團體名次_其他"); dtTable.Columns.Add("競賽成績_全國個人名次_其他"); dtTable.Columns.Add("競賽成績_全國團體名次_其他"); CompPerformanceCountDict.Add("競賽成績_縣市個人名次_其他", 0); CompPerformanceCountDict.Add("競賽成績_縣市團體名次_其他", 0); CompPerformanceCountDict.Add("競賽成績_全國個人名次_其他", 0); CompPerformanceCountDict.Add("競賽成績_全國團體名次_其他", 0); dtTable.Columns.Add("競賽成績_競賽積分"); dtTable.Columns.Add("成績_合計總分"); //StreamWriter sw1 = new StreamWriter(Application.StartupPath + "\\合併欄位.txt"); //StringBuilder sb1 = new StringBuilder(); //foreach (DataColumn dc in dtTable.Columns) // sb1.AppendLine(dc.Caption); //sw1.Write(sb1.ToString()); //sw1.Close(); DataRow row = dtTable.NewRow(); row["學年度"] = si.SchoolYear; row["學校名稱"] = K12.Data.School.ChineseName; row["班級"] = si.ClassName; row["座號"] = si.SeatNo; row["姓名"] = si.Name; if (si.IncomeType1) { row["扶助弱勢_身分"] = "低收入戶"; row["扶助弱勢_積分"] = 1; } else { row["扶助弱勢_身分"] = ""; row["扶助弱勢_積分"] = 0; } // 處理領域學期成績 foreach (string name in domainNameList) { if (si.DomainScoreInfoDict.ContainsKey(name)) { row["均衡學習_" + name + "_七上分數"] = si.GetDomainSemsScore(name, "七上"); row["均衡學習_" + name + "_七下分數"] = si.GetDomainSemsScore(name, "七下"); row["均衡學習_" + name + "_八上分數"] = si.GetDomainSemsScore(name, "八上"); row["均衡學習_" + name + "_八下分數"] = si.GetDomainSemsScore(name, "八下"); row["均衡學習_" + name + "_九上分數"] = si.GetDomainSemsScore(name, "九上"); row["均衡學習_" + name + "_平均"] = si.DomainScoreInfoDict[name].AvgScore; } } row["均衡學習_積分"] = si.DomainIScore; row["品德表現_獎懲_大功統計"] = si.MASum; row["品德表現_獎懲_小功統計"] = si.MBSum; row["品德表現_獎懲_嘉獎統計"] = si.MCSum; row["品德表現_獎懲_大過統計"] = si.DASum; row["品德表現_獎懲_小過統計"] = si.DBSum; row["品德表現_獎懲_警告統計"] = si.DCSum; row["品德表現_獎懲_銷過統計"] = si.MDCleanCount; row["品德表現_獎懲_積分"] = si.MDIScore; row["品德表現_服務學習_積分"] = si.ServiceIScore; row["品德表現_服務學習_校內時數統計"] = si.ServiceInHourCount; row["品德表現_服務學習_校外時數統計"] = si.ServiceOutHourCount; row["品德表現_服務學習_校內外時數統計"] = si.ServiceInHourCount + si.ServiceOutHourCount; row["品德表現_體適能_積分"] = si.FitnessIScore; int idx = 1; // 處理獎懲明細 foreach (rptMeritDemeritInfo md in si.MeritDemeritInfoList) { row["品德表現_獎懲_獎懲日期" + idx] = md.OccurDate; row["品德表現_獎懲_學期" + idx] = md.Semester; row["品德表現_獎懲_獎懲事由" + idx] = md.Reason; row["品德表現_獎懲_大功" + idx] = md.MA; row["品德表現_獎懲_小功" + idx] = md.MB; row["品德表現_獎懲_嘉獎" + idx] = md.MC; row["品德表現_獎懲_大過" + idx] = md.DA; row["品德表現_獎懲_小過" + idx] = md.DB; row["品德表現_獎懲_警告" + idx] = md.DC; if (md.Cleand == "是") { row["品德表現_獎懲_銷過" + idx] = "是"; } else { row["品德表現_獎懲_銷過" + idx] = "--"; } idx++; } idx = 1; // 處理服務學習 foreach (rptServiceInfo sif in si.ServiceInfoList) { row["品德表現_服務學習_資料輸入日期" + idx] = sif.OccurDate; row["品德表現_服務學習_校內外" + idx] = sif.InternalOrExternal; row["品德表現_服務學習_服務時數" + idx] = sif.Hours; row["品德表現_服務學習_服務學習活動內容" + idx] = sif.Reason; row["品德表現_服務學習_服務學習證明單位" + idx] = sif.Organizers; idx++; } idx = 1; // 體適能 foreach (rptFitnessInfo fi in si.FitnessInfoList) { row["品德表現_體適能_檢測日期" + idx] = fi.TestDateStr; row["品德表現_體適能_年齡" + idx] = fi.Age; row["品德表現_體適能_性別" + idx] = si.Gender; row["品德表現_體適能_坐姿體前彎_成績" + idx] = fi.Sit_and_reach; row["品德表現_體適能_立定跳遠_成績" + idx] = fi.Standing_long_jump; row["品德表現_體適能_仰臥起坐_成績" + idx] = fi.Sit_up; row["品德表現_體適能_公尺跑走_成績" + idx] = fi.Cardiorespiratory; row["品德表現_體適能_坐姿體前彎_等級" + idx] = fi.Sit_and_reach_degree; row["品德表現_體適能_立定跳遠_等級" + idx] = fi.Standing_long_jump_degree; row["品德表現_體適能_仰臥起坐_等級" + idx] = fi.Sit_up_degree; row["品德表現_體適能_公尺跑走_等級" + idx] = fi.Cardiorespiratory_degree; idx++; } idx = 1; // 填入競賽基本資料 foreach (rptCompPerformanceInfo cpi in si.CompPerformanceInfoList) { row["競賽成績_競賽層級" + idx] = cpi.reward_level; row["競賽成績_競賽性質" + idx] = cpi.habitude; row["競賽成績_競賽名稱" + idx] = cpi.name; row["競賽成績_得獎名次" + idx] = cpi.rank_name; row["競賽成績_證書日期" + idx] = cpi.certificate_date; row["競賽成績_主辦單位" + idx] = cpi.organizer; idx++; } // 掃名次填入細項 foreach (rptCompPerformanceInfo cpi in si.CompPerformanceInfoList) { int n = 0; int.TryParse(cpi.bt_rank_int, out n); if (cpi.reward_level.Contains("國")) { // 全國 if (cpi.habitude == "個人賽") { if (n >= 1 && n <= 8) { CompPerformanceCountDict["競賽成績_全國個人名次" + n]++; } else { CompPerformanceCountDict["競賽成績_全國個人名次_其他"]++; } } if (cpi.habitude == "團體賽") { if (n >= 1 && n <= 8) { CompPerformanceCountDict["競賽成績_全國團體名次" + n]++; } else { CompPerformanceCountDict["競賽成績_全國團體名次_其他"]++; } } } else { // 市 if (cpi.habitude == "個人賽") { if (n >= 1 && n <= 8) { CompPerformanceCountDict["競賽成績_縣市個人名次" + n]++; } else { CompPerformanceCountDict["競賽成績_縣市個人名次_其他"]++; } } if (cpi.habitude == "團體賽") { if (n >= 1 && n <= 8) { CompPerformanceCountDict["競賽成績_縣市團體名次" + n]++; } else { CompPerformanceCountDict["競賽成績_縣市團體名次_其他"]++; } } } } // 填入值 foreach (string key in CompPerformanceCountDict.Keys) { row[key] = CompPerformanceCountDict[key]; } row["競賽成績_競賽積分"] = si.CompPerformanceScore; // 從報表樣板上檢查是由這幾個相加取得總積分: // 扶助弱勢_積分 // 均衡學習_積分 // 品德表現_獎懲_積分 // 品德表現_服務學習_積分 // 品德表現_體適能_積分 // 競賽成績_競賽積分 row["成績_合計總分"] = si.IncomeType1Score + si.DomainIScore + si.MDIScore + si.ServiceIScore + si.FitnessIScore + si.CompPerformanceScore; dtTable.Rows.Add(row); //// debug //dtTable.TableName = "debug"; //dtTable.WriteXml(Application.StartupPath + @"\debug" + si.StudentID + ".xml"); docTemplate.MailMerge.Execute(dtTable); docTemplate.MailMerge.RemoveEmptyParagraphs = true; docTemplate.MailMerge.DeleteFields(); if (!StudentDocDict.ContainsKey(si.StudentID)) { StudentDocDict.Add(si.StudentID, docTemplate); StudentDocNameDict.Add(si.StudentID, si.ClassName + "班" + si.SeatNo + "號"); } } bgWorkerReport.ReportProgress(90); }
private void BgWorkerExport_DoWork(object sender, DoWorkEventArgs e) { bgWorkerExport.ReportProgress(1); try { // 取得預設樣板 Workbook wb = new Workbook(new MemoryStream(Properties.Resources.Template)); Worksheet wst = wb.Worksheets[0]; // 取得學生基本資料 List <StudentInfo> StudentInfoList = QueryData.GetStudentInfoList3(); List <string> StudentIDList = new List <string>(); foreach (StudentInfo si in StudentInfoList) { StudentIDList.Add(si.StudentID); } // 取得地址資訊 Dictionary <string, JHAddressRecord> AddressDict = new Dictionary <string, JHAddressRecord>(); List <JHAddressRecord> tmpAddress = JHAddress.SelectByStudentIDs(StudentIDList); foreach (JHAddressRecord rec in tmpAddress) { if (!AddressDict.ContainsKey(rec.RefStudentID)) { AddressDict.Add(rec.RefStudentID, rec); } } // 取得電話資料 Dictionary <string, JHPhoneRecord> PhoneDict = new Dictionary <string, JHPhoneRecord>(); List <JHPhoneRecord> tmpPhone = JHPhone.SelectByStudentIDs(StudentIDList); foreach (JHPhoneRecord rec in tmpPhone) { if (!PhoneDict.ContainsKey(rec.RefStudentID)) { PhoneDict.Add(rec.RefStudentID, rec); } } bgWorkerExport.ReportProgress(20); // 取得監護人父母資訊 Dictionary <string, JHParentRecord> ParentDict = new Dictionary <string, JHParentRecord>(); List <JHParentRecord> tmpParent = JHParent.SelectByStudentIDs(StudentIDList); foreach (JHParentRecord rec in tmpParent) { if (!ParentDict.ContainsKey(rec.RefStudentID)) { ParentDict.Add(rec.RefStudentID, rec); } } // 轉換各項類別對照值 Dictionary <string, string> MappingTag1 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag2 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag3 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag4 = new Dictionary <string, string>(); // 取得學生類別 Dictionary <string, List <string> > StudentTagDict = QueryData.GetStudentTagName(StudentIDList); // 解析對照設定 XElement elmRoot = XElement.Parse(_Configure.MappingContent); if (elmRoot != null) { foreach (XElement elm in elmRoot.Elements("Group")) { string gpName = elm.Attribute("Name").Value; if (gpName == "學生身分") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag1.ContainsKey(tagName) && tagName.Length > 0) { MappingTag1.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "身心障礙") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag2.ContainsKey(tagName) && tagName.Length > 0) { MappingTag2.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "學生報名身分設定") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag3.ContainsKey(tagName) && tagName.Length > 0) { MappingTag3.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "失業勞工子女") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag4.ContainsKey(tagName) && tagName.Length > 0) { MappingTag4.Add(tagName, elm1.Attribute("Code").Value); } } } } } // 取得語言認證學生id List <string> hasLanguageCertificateIDList = QueryData.GetLanguageCertificate(StudentIDList); bgWorkerExport.ReportProgress(40); // 取得成績相關資料 Dictionary <string, List <JHSemesterScoreRecord> > SemesterScoreRecordDict = new Dictionary <string, List <JHSemesterScoreRecord> >(); List <JHSemesterScoreRecord> tmpSemsScore = JHSemesterScore.SelectByStudentIDs(StudentIDList); foreach (JHSemesterScoreRecord rec in tmpSemsScore) { if (!SemesterScoreRecordDict.ContainsKey(rec.RefStudentID)) { SemesterScoreRecordDict.Add(rec.RefStudentID, new List <JHSemesterScoreRecord>()); } SemesterScoreRecordDict[rec.RefStudentID].Add(rec); } // 取得功過紀錄 // 功過對照表 JHMeritDemeritReduceRecord DemeritReduceRecord = JHMeritDemeritReduce.Select(); // 懲 Dictionary <string, List <JHDemeritRecord> > DemeritRecordDict = new Dictionary <string, List <JHDemeritRecord> >(); List <JHDemeritRecord> tmpDemeritRecord = JHDemerit.SelectByStudentIDs(StudentIDList); foreach (JHDemeritRecord rec in tmpDemeritRecord) { if (rec.Cleared == "是") { continue; } if (rec.OccurDate > _Configure.EndDate) { continue; } else { if (!DemeritRecordDict.ContainsKey(rec.RefStudentID)) { DemeritRecordDict.Add(rec.RefStudentID, new List <JHDemeritRecord>()); } DemeritRecordDict[rec.RefStudentID].Add(rec); } } // 獎 Dictionary <string, List <JHMeritRecord> > MeritRecordDict = new Dictionary <string, List <JHMeritRecord> >(); List <JHMeritRecord> tmpMeritRecord = JHMerit.SelectByStudentIDs(StudentIDList); foreach (JHMeritRecord rec in tmpMeritRecord) { if (rec.OccurDate > _Configure.EndDate) { continue; } else { if (!MeritRecordDict.ContainsKey(rec.RefStudentID)) { MeritRecordDict.Add(rec.RefStudentID, new List <JHMeritRecord>()); } MeritRecordDict[rec.RefStudentID].Add(rec); } } // 填入幹部資料 StudentInfoList = QueryData.FillCad(StudentIDList, StudentInfoList); // 填入中低收入戶 StudentInfoList = QueryData.FillIncomeType(StudentIDList, StudentInfoList); // 填入競賽成績 StudentInfoList = QueryData.FillStudentCompetitionScore(StudentIDList, StudentInfoList, _Configure.EndDate); // 取得學生體適能資料並填入 StudentInfoList = QueryData.FillStudentFitness(StudentIDList, StudentInfoList, _Configure.EndDate); // 填入 Excel 資料 int wstRIdx = 1; bgWorkerExport.ReportProgress(70); // 幹部限制 List <string> CadreName1 = _Configure.LoadCareNames(); foreach (StudentInfo si in StudentInfoList) { // 考區代碼 0, 12/屏東考區 wst.Cells[wstRIdx, 0].PutValue(12); // 集報單位代碼 1,學校代碼 wst.Cells[wstRIdx, 1].PutValue(K12.Data.School.Code); // 序號 2 wst.Cells[wstRIdx, 2].PutValue(wstRIdx); // 學號 3 wst.Cells[wstRIdx, 3].PutValue(si.StudentNumber); // 班級 4 wst.Cells[wstRIdx, 4].PutValue(si.ClassName); // 座號 5 wst.Cells[wstRIdx, 5].PutValue(si.SeatNo); // 學生姓名 6 wst.Cells[wstRIdx, 6].PutValue(si.StudentName); // 身分證統一編號 7 wst.Cells[wstRIdx, 7].PutValue(si.IDNumber); // 性別 8 wst.Cells[wstRIdx, 8].PutValue(si.GenderCode); // 出生年(民國年) 9 wst.Cells[wstRIdx, 9].PutValue(si.BirthYear); // 出生月 10 wst.Cells[wstRIdx, 10].PutValue(si.BirthMonth); // 出生日 11 wst.Cells[wstRIdx, 11].PutValue(si.BirthDay); // 畢業學校代碼 12 wst.Cells[wstRIdx, 12].PutValue(K12.Data.School.Code); // 畢業年(民國年) 13 int gyear; if (int.TryParse(K12.Data.School.DefaultSchoolYear, out gyear)) { wst.Cells[wstRIdx, 13].PutValue(gyear + 1); } // 畢肄業 14 wst.Cells[wstRIdx, 14].PutValue(1); // 就學區 17 // 低收入戶 18 if (si.incomeType1) { wst.Cells[wstRIdx, 18].PutValue(1); } else { wst.Cells[wstRIdx, 18].PutValue(0); } // 中低收入戶 19 if (si.incomeType2) { wst.Cells[wstRIdx, 19].PutValue(1); } else { wst.Cells[wstRIdx, 19].PutValue(0); } wst.Cells[wstRIdx, 15].PutValue("0"); wst.Cells[wstRIdx, 16].PutValue("0"); wst.Cells[wstRIdx, 20].PutValue("0"); wst.Cells[wstRIdx, 28].PutValue("0"); if (StudentTagDict.ContainsKey(si.StudentID)) { foreach (string tagName in StudentTagDict[si.StudentID]) { if (MappingTag1.ContainsKey(tagName)) { // 學生身分 15 wst.Cells[wstRIdx, 15].PutValue(MappingTag1[tagName]); } if (MappingTag2.ContainsKey(tagName)) { si.isSpecial = true; // 身心障礙 16 wst.Cells[wstRIdx, 16].PutValue(MappingTag2[tagName]); } if (MappingTag3.ContainsKey(tagName)) { // 學生報名身分 28 wst.Cells[wstRIdx, 28].PutValue(MappingTag3[tagName]); } if (MappingTag4.ContainsKey(tagName)) { // 失業勞工子女 20 wst.Cells[wstRIdx, 20].PutValue(MappingTag4[tagName]); } } } // 資料授權 21 wst.Cells[wstRIdx, 21].PutValue(0); string parentName = ""; // 家長姓名 22 if (ParentDict.ContainsKey(si.StudentID)) { if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].CustodianName)) { parentName = ParentDict[si.StudentID].CustodianName; } else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].FatherName)) { parentName = ParentDict[si.StudentID].FatherName; } else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].MotherName)) { parentName = ParentDict[si.StudentID].MotherName; } else { } wst.Cells[wstRIdx, 22].PutValue(parentName); } // 市內電話 23 // 行動電話 24 if (PhoneDict.ContainsKey(si.StudentID)) { wst.Cells[wstRIdx, 23].PutValue(PhoneDict[si.StudentID].Contact.Replace("-", "").Replace(")", "").Replace("(", "")); wst.Cells[wstRIdx, 24].PutValue(PhoneDict[si.StudentID].Cell.Replace("-", "").Replace(")", "").Replace("(", "")); } // 郵遞區號 25 if (AddressDict.ContainsKey(si.StudentID)) { string zipCode = ""; if (AddressDict[si.StudentID].MailingZipCode != null) { zipCode = AddressDict[si.StudentID].MailingZipCode; } if (zipCode.Length >= 3) { zipCode = zipCode.Substring(0, 3); } wst.Cells[wstRIdx, 25].PutValue(zipCode); // 通訊地址 26 wst.Cells[wstRIdx, 26].PutValue(AddressDict[si.StudentID].MailingCounty + AddressDict[si.StudentID].MailingTown + AddressDict[si.StudentID].MailingDistrict + AddressDict[si.StudentID].MailingArea + AddressDict[si.StudentID].MailingDetail); } // 非中華民國身分證號 27 if (si.isTaiwanID) { wst.Cells[wstRIdx, 27].PutValue(""); } else { wst.Cells[wstRIdx, 27].PutValue("V"); } // 市內電話分機 29 // 均衡學習 30 // 計算分數 if (SemesterScoreRecordDict.ContainsKey(si.StudentID)) { si.CalcSemsScore5(SemesterScoreRecordDict[si.StudentID]); // 成績滿5學期才顯示 if (si.hasSemester5Score) { wst.Cells[wstRIdx, 30].PutValue(si.Semester5Score); } } // 服務表現 31 si.CalcCadreScore(CadreName1); wst.Cells[wstRIdx, 31].PutValue(si.ServiceScore); // 品德表現 32 if (DemeritRecordDict.ContainsKey(si.StudentID)) { if (MeritRecordDict.ContainsKey(si.StudentID)) { si.CalcDemeritMemeritScore(DemeritRecordDict[si.StudentID], MeritRecordDict[si.StudentID], DemeritReduceRecord); } else { si.CalcDemeritMemeritScore(DemeritRecordDict[si.StudentID], new List <JHMeritRecord>(), DemeritReduceRecord); } wst.Cells[wstRIdx, 32].PutValue(si.MeritDemeritScore); } else { // 沒有懲戒 wst.Cells[wstRIdx, 32].PutValue(10); } // 競賽表現 33 si.CalcCompetitionScore(); wst.Cells[wstRIdx, 33].PutValue(si.CompetitionScore); // 體適能 34 // 計算並填入 si.CalcFitnessScore(); wst.Cells[wstRIdx, 34].PutValue(si.FitnessScore); // 本土語言認證 35 if (hasLanguageCertificateIDList.Contains(si.StudentID)) { wst.Cells[wstRIdx, 35].PutValue(2); } else { wst.Cells[wstRIdx, 35].PutValue(0); } // 36~39 系統無法提供先空 // 適性發展_高中 36 // 適性發展_高職 37 // 適性發展_綜合高中 38 // 適性發展_五專 39 wstRIdx++; } bgWorkerExport.ReportProgress(100); e.Result = wb; } catch (Exception ex) { throw ex; } }
private void BgWorkerExport_DoWork(object sender, DoWorkEventArgs e) { bgWorkerExport.ReportProgress(1); try { // 取得預設樣板 Workbook wb = new Workbook(new MemoryStream(Properties.Resources.Template)); Worksheet wst = wb.Worksheets[0]; // 取得學生基本資料 List <StudentInfo> StudentInfoList = QueryData.GetStudentInfoList3(); List <string> StudentIDList = new List <string>(); foreach (StudentInfo si in StudentInfoList) { StudentIDList.Add(si.StudentID); } // 取得地址資訊 Dictionary <string, JHAddressRecord> AddressDict = new Dictionary <string, JHAddressRecord>(); List <JHAddressRecord> tmpAddress = JHAddress.SelectByStudentIDs(StudentIDList); foreach (JHAddressRecord rec in tmpAddress) { if (!AddressDict.ContainsKey(rec.RefStudentID)) { AddressDict.Add(rec.RefStudentID, rec); } } // 取得電話資料 Dictionary <string, JHPhoneRecord> PhoneDict = new Dictionary <string, JHPhoneRecord>(); List <JHPhoneRecord> tmpPhone = JHPhone.SelectByStudentIDs(StudentIDList); foreach (JHPhoneRecord rec in tmpPhone) { if (!PhoneDict.ContainsKey(rec.RefStudentID)) { PhoneDict.Add(rec.RefStudentID, rec); } } bgWorkerExport.ReportProgress(20); // 取得監護人父母資訊 Dictionary <string, JHParentRecord> ParentDict = new Dictionary <string, JHParentRecord>(); List <JHParentRecord> tmpParent = JHParent.SelectByStudentIDs(StudentIDList); foreach (JHParentRecord rec in tmpParent) { if (!ParentDict.ContainsKey(rec.RefStudentID)) { ParentDict.Add(rec.RefStudentID, rec); } } // 轉換各項類別對照值 Dictionary <string, string> MappingTag1 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag2 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag3 = new Dictionary <string, string>(); Dictionary <string, string> MappingTag4 = new Dictionary <string, string>(); // 取得學生類別 Dictionary <string, List <string> > StudentTagDict = QueryData.GetStudentTagName(StudentIDList); // 解析對照設定 XElement elmRoot = XElement.Parse(_Configure.MappingContent); if (elmRoot != null) { foreach (XElement elm in elmRoot.Elements("Group")) { string gpName = elm.Attribute("Name").Value; if (gpName == "學生身分") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag1.ContainsKey(tagName) && tagName.Length > 0) { MappingTag1.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "身心障礙") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag2.ContainsKey(tagName) && tagName.Length > 0) { MappingTag2.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "學生報名身分設定") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag3.ContainsKey(tagName) && tagName.Length > 0) { MappingTag3.Add(tagName, elm1.Attribute("Code").Value); } } } if (gpName == "失業勞工子女") { foreach (XElement elm1 in elm.Elements("Item")) { string tagName = elm1.Attribute("TagName").Value; if (!MappingTag4.ContainsKey(tagName) && tagName.Length > 0) { MappingTag4.Add(tagName, elm1.Attribute("Code").Value); } } } } } bgWorkerExport.ReportProgress(40); // 取得成績相關資料 Dictionary <string, List <JHSemesterScoreRecord> > SemesterScoreRecordDict = new Dictionary <string, List <JHSemesterScoreRecord> >(); List <JHSemesterScoreRecord> tmpSemsScore = JHSemesterScore.SelectByStudentIDs(StudentIDList); foreach (JHSemesterScoreRecord rec in tmpSemsScore) { if (!SemesterScoreRecordDict.ContainsKey(rec.RefStudentID)) { SemesterScoreRecordDict.Add(rec.RefStudentID, new List <JHSemesterScoreRecord>()); } SemesterScoreRecordDict[rec.RefStudentID].Add(rec); } // 取得功過紀錄 // 功過對照表 JHMeritDemeritReduceRecord DemeritReduceRecord = JHMeritDemeritReduce.Select(); // 懲 Dictionary <string, List <JHDemeritRecord> > DemeritRecordDict = new Dictionary <string, List <JHDemeritRecord> >(); List <JHDemeritRecord> tmpDemeritRecord = JHDemerit.SelectByStudentIDs(StudentIDList); foreach (JHDemeritRecord rec in tmpDemeritRecord) { if (rec.Cleared == "是") { continue; } if (rec.OccurDate > _Configure.EndDate) { continue; } else { if (!DemeritRecordDict.ContainsKey(rec.RefStudentID)) { DemeritRecordDict.Add(rec.RefStudentID, new List <JHDemeritRecord>()); } DemeritRecordDict[rec.RefStudentID].Add(rec); } } // 獎 Dictionary <string, List <JHMeritRecord> > MeritRecordDict = new Dictionary <string, List <JHMeritRecord> >(); List <JHMeritRecord> tmpMeritRecord = JHMerit.SelectByStudentIDs(StudentIDList); foreach (JHMeritRecord rec in tmpMeritRecord) { if (rec.OccurDate > _Configure.EndDate) { continue; } else { if (!MeritRecordDict.ContainsKey(rec.RefStudentID)) { MeritRecordDict.Add(rec.RefStudentID, new List <JHMeritRecord>()); } MeritRecordDict[rec.RefStudentID].Add(rec); } } // 取得服務學習時數 StudentInfoList = QueryData.FillServiceLearn(StudentIDList, StudentInfoList, _Configure.EndDate); // 填入中低收入戶 StudentInfoList = QueryData.FillIncomeType(StudentIDList, StudentInfoList); // 取得學生體適能資料並填入,嘉義版不卡日期,日期傳入不會限制 StudentInfoList = QueryData.FillStudentFitness(StudentIDList, StudentInfoList, _Configure.EndDate); // 取得競賽總積分並填入學生資料 StudentInfoList = QueryData.FillStudentCompetitionPerformanceSum(StudentIDList, StudentInfoList); // 填入 Excel 資料 int wstRIdx = 1; bgWorkerExport.ReportProgress(70); foreach (StudentInfo si in StudentInfoList) { // 考區代碼 0,嘉義區 10 wst.Cells[wstRIdx, 0].PutValue(10); // 集報單位代碼 1 wst.Cells[wstRIdx, 1].PutValue(K12.Data.School.Code); // 序號 2 wst.Cells[wstRIdx, 2].PutValue(wstRIdx); // 學號 3 wst.Cells[wstRIdx, 3].PutValue(si.StudentNumber); // 班級 4 wst.Cells[wstRIdx, 4].PutValue(si.ClassName); // 座號 5 wst.Cells[wstRIdx, 5].PutValue(si.SeatNo); // 學生姓名 6 // 2021-12-27 有些學校會為了整齊把學生的名字+空白,這裡濾掉 wst.Cells[wstRIdx, 6].PutValue(si.StudentName.Replace(" ", "").Replace(" ", "")); // 身分證統一編號 7 wst.Cells[wstRIdx, 7].PutValue(si.IDNumber); // 非中華民國身分證號 8 if (si.isTaiwanID) { wst.Cells[wstRIdx, 8].PutValue(""); } else { wst.Cells[wstRIdx, 8].PutValue("V"); } // 性別 9 wst.Cells[wstRIdx, 9].PutValue(si.GenderCode); // 出生年(民國年) 10 wst.Cells[wstRIdx, 10].PutValue(si.BirthYear); // 出生月 11 wst.Cells[wstRIdx, 11].PutValue(si.BirthMonth); // 出生日 12 wst.Cells[wstRIdx, 12].PutValue(si.BirthDay); // 畢業學校代碼 13 wst.Cells[wstRIdx, 13].PutValue(K12.Data.School.Code); // 畢業年(民國年) 14 int gyear; if (int.TryParse(K12.Data.School.DefaultSchoolYear, out gyear)) { wst.Cells[wstRIdx, 14].PutValue(gyear + 1); } // 畢肄業 15 wst.Cells[wstRIdx, 15].PutValue(1); wst.Cells[wstRIdx, 16].PutValue(0); wst.Cells[wstRIdx, 17].PutValue(0); wst.Cells[wstRIdx, 18].PutValue(0); wst.Cells[wstRIdx, 22].PutValue(0); if (StudentTagDict.ContainsKey(si.StudentID)) { foreach (string tagName in StudentTagDict[si.StudentID]) { if (MappingTag1.ContainsKey(tagName)) { // 學生身分 16 wst.Cells[wstRIdx, 16].PutValue(MappingTag1[tagName]); } if (MappingTag2.ContainsKey(tagName)) { si.isSpecial = true; // 身心障礙 18 wst.Cells[wstRIdx, 18].PutValue(MappingTag2[tagName]); } if (MappingTag3.ContainsKey(tagName)) { // 學生報名身分 17 wst.Cells[wstRIdx, 17].PutValue(MappingTag3[tagName]); } if (MappingTag4.ContainsKey(tagName)) { // 失業勞工子女 22 wst.Cells[wstRIdx, 22].PutValue(MappingTag4[tagName]); } } } // 就學區 19,不處理 // 低收入戶 20 if (si.incomeType1) { wst.Cells[wstRIdx, 20].PutValue(1); } else { wst.Cells[wstRIdx, 20].PutValue(0); } // 中低收入戶 21 if (si.incomeType2) { wst.Cells[wstRIdx, 21].PutValue(1); } else { wst.Cells[wstRIdx, 21].PutValue(0); } // 資料授權 23 wst.Cells[wstRIdx, 23].PutValue(0); string parentName = ""; // 家長姓名 24 if (ParentDict.ContainsKey(si.StudentID)) { if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].CustodianName)) { parentName = ParentDict[si.StudentID].CustodianName; } else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].FatherName)) { parentName = ParentDict[si.StudentID].FatherName; } else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].MotherName)) { parentName = ParentDict[si.StudentID].MotherName; } else { } wst.Cells[wstRIdx, 24].PutValue(parentName); } // 市內電話 25 // 市內電話分機 26 // 行動電話 27 if (PhoneDict.ContainsKey(si.StudentID)) { wst.Cells[wstRIdx, 25].PutValue(PhoneDict[si.StudentID].Contact.Replace("-", "").Replace(")", "").Replace("(", "")); wst.Cells[wstRIdx, 27].PutValue(PhoneDict[si.StudentID].Cell.Replace("-", "").Replace(")", "").Replace("(", "")); } // 郵遞區號 28 if (AddressDict.ContainsKey(si.StudentID)) { if (AddressDict[si.StudentID].MailingZipCode != null) { string zipCode = AddressDict[si.StudentID].MailingZipCode; if (zipCode.Length >= 3) { zipCode = zipCode.Substring(0, 3); } wst.Cells[wstRIdx, 28].PutValue(zipCode); } // 通訊地址 29 wst.Cells[wstRIdx, 29].PutValue(AddressDict[si.StudentID].MailingCounty + AddressDict[si.StudentID].MailingTown + AddressDict[si.StudentID].MailingDistrict + AddressDict[si.StudentID].MailingArea + AddressDict[si.StudentID].MailingDetail); } // 計算分數 if (SemesterScoreRecordDict.ContainsKey(si.StudentID)) { si.CalcSemsScore5(SemesterScoreRecordDict[si.StudentID]); } // 健康與體育 30 if (si.isDomainHelPass) { wst.Cells[wstRIdx, 30].PutValue(1); } else { wst.Cells[wstRIdx, 30].PutValue(0); } // 藝術 31 if (si.isDoaminArtPass) { wst.Cells[wstRIdx, 31].PutValue(1); } else { wst.Cells[wstRIdx, 31].PutValue(0); } // 綜合活動 32 if (si.isDomainActPass) { wst.Cells[wstRIdx, 32].PutValue(1); } else { wst.Cells[wstRIdx, 32].PutValue(0); } // 品德表現 33 List <JHDemeritRecord> recD; List <JHMeritRecord> recM; if (DemeritRecordDict.ContainsKey(si.StudentID)) { recD = DemeritRecordDict[si.StudentID]; } else { recD = new List <JHDemeritRecord>(); } if (MeritRecordDict.ContainsKey(si.StudentID)) { recM = MeritRecordDict[si.StudentID]; } else { recM = new List <JHMeritRecord>(); } si.CalcDemeritMemeritScore(recD, recM, DemeritReduceRecord); wst.Cells[wstRIdx, 33].PutValue(si.MeritDemeritScore); // 服務學習 34 wst.Cells[wstRIdx, 34].PutValue(si.ServiceLearnScore); // 體適能 35 si.CalcFitnessScore(); wst.Cells[wstRIdx, 35].PutValue(si.FitnessScore); // 競賽表現 36,使用者自行處理 if (si.CompPerfSum.HasValue) { wst.Cells[wstRIdx, 36].PutValue(si.CompPerfSum.Value); } else // 2021-12-27 嘉義協同國中 見安老師要求預設值 0 { wst.Cells[wstRIdx, 36].PutValue("0"); } // 2021-12-27 嘉義協同國中 見安老師要求預設值全部填1 // https://3.basecamp.com/4399967/buckets/15765350/todos/4475736499#__recording_4477516175 // 家長意見_高中 37 wst.Cells[wstRIdx, 37].PutValue("1"); // 家長意見_高職 38 wst.Cells[wstRIdx, 38].PutValue("1"); // 導師意見_高中 39 wst.Cells[wstRIdx, 39].PutValue("1"); // 導師意見_高職 40 wst.Cells[wstRIdx, 40].PutValue("1"); // 輔導教師意見_高中 41 wst.Cells[wstRIdx, 41].PutValue("1"); // 輔導教師意見_高職 42 wst.Cells[wstRIdx, 42].PutValue("1"); wstRIdx++; } bgWorkerExport.ReportProgress(100); e.Result = wb; } catch (Exception ex) { throw ex; } }