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