/// <summary>
/// 取得學生學期成績
/// </summary>
/// <param name="utilCache"></param>
/// <returns></returns>
private Dictionary <string, StudentScores> GetStudentScores(Dictionary <string, HistoryUtil> utilCache)
{
Dictionary <string, StudentScores> scores = new Dictionary <string, StudentScores>();
foreach (JHSemesterScoreRecord scoreRecord in JHSemesterScore.SelectByStudentIDs(AllStudentID))
{
if (!scores.ContainsKey(scoreRecord.RefStudentID))
{
StudentScores studentScores = new StudentScores(utilCache[scoreRecord.RefStudentID]);
scores.Add(scoreRecord.RefStudentID, studentScores);
}
scores[scoreRecord.RefStudentID].Add(scoreRecord);
}
return(scores);
}
/// <summary>
/// 取得領域成績並判斷是否及格
/// </summary>
/// <param name="studDataList"></param>
/// <param name="SchoolYear"></param>
/// <param name="Semester"></param>
/// <param name="passScore"></param>
/// <returns></returns>
public static List <StudentData> CalcStudDomainScorePass(List <StudentData> studDataList, int SchoolYear, int Semester, decimal passScore)
{
// 取得學期成績
Dictionary <string, JHSemesterScoreRecord> studSemsScoreDict = new Dictionary <string, JHSemesterScoreRecord>();
List <string> studIDList = (from data in studDataList select data.StudentID).ToList();
List <JHSemesterScoreRecord> semsScoreList = JHSemesterScore.SelectByStudentIDs(studIDList);
foreach (JHSemesterScoreRecord rec in semsScoreList)
{
if (rec.SchoolYear == SchoolYear && rec.Semester == Semester)
{
if (!studSemsScoreDict.ContainsKey(rec.RefStudentID))
{
studSemsScoreDict.Add(rec.RefStudentID, rec);
}
}
}
// 判斷學生領域成績是否及格
foreach (StudentData sd in studDataList)
{
if (studSemsScoreDict.ContainsKey(sd.StudentID))
{
sd.StudSemesterScoreRecord = studSemsScoreDict[sd.StudentID];
foreach (DomainScore ds in studSemsScoreDict[sd.StudentID].Domains.Values)
{
if (!sd.DomainScorePassDict.ContainsKey(ds.Domain))
{
sd.DomainScorePassDict.Add(ds.Domain, false);
sd.DomainScoreDict.Add(ds.Domain, 0);
}
if (ds.Score.HasValue)
{
sd.DomainScoreDict[ds.Domain] = ds.Score.Value;
if (ds.Score.Value >= passScore)
{
sd.DomainScorePassDict[ds.Domain] = true;
}
}
}
}
}
return(studDataList);
}
public override void InitializeExport(SmartSchool.API.PlugIn.Export.ExportWizard wizard)
{
SmartSchool.API.PlugIn.VirtualCheckBox filterRepeat = new SmartSchool.API.PlugIn.VirtualCheckBox("自動略過重讀成績", true);
wizard.Options.Add(filterRepeat);
//2017/6/16 穎驊新增，因應[02-02][06] 計算學期科目成績新增清空原成績模式 項目， 新增 "刪除"欄位，使使用者能匯入 刪除成績資料
wizard.ExportableFields.AddRange("領域", "學年度", "學期", "權數", "節數", "成績", "原始成績", "補考成績", "努力程度", "文字描述", "註記", "刪除");
wizard.ExportPackage += delegate(object sender, SmartSchool.API.PlugIn.Export.ExportPackageEventArgs e)
{
#region ExportPackage
List <JHStudentRecord> students = JHStudent.SelectByIDs(e.List);
Dictionary <string, List <JHSemesterScoreRecord> > semsDict = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (JHSemesterScoreRecord record in JHSemesterScore.SelectByStudentIDs(e.List))
{
if (!semsDict.ContainsKey(record.RefStudentID))
{
semsDict.Add(record.RefStudentID, new List <JHSemesterScoreRecord>());
}
semsDict[record.RefStudentID].Add(record);
}
foreach (JHStudentRecord stu in students)
{
if (!semsDict.ContainsKey(stu.ID))
{
continue;
}
foreach (JHSemesterScoreRecord record in semsDict[stu.ID])
{
foreach (K12.Data.DomainScore domain in record.Domains.Values)
{
RowData row = new RowData();
row.ID = stu.ID;
foreach (string field in e.ExportFields)
{
if (wizard.ExportableFields.Contains(field))
{
switch (field)
{
case "領域": row.Add(field, "" + domain.Domain); break;
case "學年度": row.Add(field, "" + record.SchoolYear); break;
case "學期": row.Add(field, "" + record.Semester); break;
case "權數": row.Add(field, "" + domain.Credit); break;
case "節數": row.Add(field, "" + domain.Period); break;
case "成績": row.Add(field, "" + domain.Score); break;
case "原始成績": row.Add(field, "" + domain.ScoreOrigin); break;
case "補考成績": row.Add(field, "" + domain.ScoreMakeup); break;
case "努力程度": row.Add(field, "" + domain.Effort); break;
case "文字描述": row.Add(field, domain.Text); break;
case "註記": row.Add(field, domain.Comment); break;
case "刪除": row.Add(field, ""); break;
}
}
}
e.Items.Add(row);
}
}
}
#endregion
FISCA.LogAgent.ApplicationLog.Log("成績系統.匯入匯出", "匯出學期領域成績", "總共匯出" + e.Items.Count + "筆學期領域成績。");
};
}
public override void InitializeImport(SmartSchool.API.PlugIn.Import.ImportWizard wizard)
{
Dictionary <string, int> _ID_SchoolYear_Semester_GradeYear = new Dictionary <string, int>();
Dictionary <string, List <string> > _ID_SchoolYear_Semester_Subject = new Dictionary <string, List <string> >();
Dictionary <string, JHStudentRecord> _StudentCollection = new Dictionary <string, JHStudentRecord>();
Dictionary <JHStudentRecord, Dictionary <int, decimal> > _StudentPassScore = new Dictionary <JHStudentRecord, Dictionary <int, decimal> >();
Dictionary <string, List <JHSemesterScoreRecord> > semsDict = new Dictionary <string, List <JHSemesterScoreRecord> >();
wizard.PackageLimit = 3000;
//wizard.ImportableFields.AddRange("領域", "科目", "學年度", "學期", "權數", "節數", "分數評量", "努力程度", "文字描述", "註記");
//wizard.ImportableFields.AddRange("領域", "科目", "學年度", "學期", "權數", "節數", "分數評量", "文字描述", "註記");
//2015.1.27 Cloud新增
wizard.ImportableFields.AddRange("領域", "科目", "學年度", "學期", "權數", "節數", "成績", "原始成績", "補考成績", "努力程度", "文字描述", "註記");
wizard.RequiredFields.AddRange("領域", "科目", "學年度", "學期");
wizard.ValidateStart += delegate(object sender, SmartSchool.API.PlugIn.Import.ValidateStartEventArgs e)
{
#region ValidateStart
_ID_SchoolYear_Semester_GradeYear.Clear();
_ID_SchoolYear_Semester_Subject.Clear();
_StudentCollection.Clear();
List <JHStudentRecord> list = JHStudent.SelectByIDs(e.List);
MultiThreadWorker <JHStudentRecord> loader = new MultiThreadWorker <JHStudentRecord>();
loader.MaxThreads = 3;
loader.PackageSize = 250;
loader.PackageWorker += delegate(object sender1, PackageWorkEventArgs <JHStudentRecord> e1)
{
foreach (var item in JHSemesterScore.SelectByStudents(e1.List))
{
if (!semsDict.ContainsKey(item.RefStudentID))
{
semsDict.Add(item.RefStudentID, new List <JHSchool.Data.JHSemesterScoreRecord>());
}
semsDict[item.RefStudentID].Add(item);
}
};
loader.Run(list);
foreach (JHStudentRecord stu in list)
{
if (!_StudentCollection.ContainsKey(stu.ID))
{
_StudentCollection.Add(stu.ID, stu);
}
}
#endregion
};
wizard.ValidateRow += delegate(object sender, SmartSchool.API.PlugIn.Import.ValidateRowEventArgs e)
{
#region ValidateRow
int t;
decimal d;
JHStudentRecord student;
if (_StudentCollection.ContainsKey(e.Data.ID))
{
student = _StudentCollection[e.Data.ID];
}
else
{
e.ErrorMessage = "壓根就沒有這個學生" + e.Data.ID;
return;
}
bool inputFormatPass = true;
#region 驗各欄位填寫格式
foreach (string field in e.SelectFields)
{
string value = e.Data[field];
switch (field)
{
default:
break;
case "領域":
//if (value == "")
//{
// inputFormatPass &= false;
// e.ErrorFields.Add(field, "必須填寫");
//}
//else if (!Domains.Contains(value))
//{
// inputFormatPass &= false;
// e.ErrorFields.Add(field, "必須為七大領域");
//}
break;
case "科目":
if (value == "")
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填寫");
}
break;
case "學年度":
if (value == "" || !int.TryParse(value, out t))
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填入學年度");
}
break;
case "權數":
case "節數":
if (value == "" || !decimal.TryParse(value, out d))
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填入數值");
}
break;
//case "成績年級":
// if (value == "" || !int.TryParse(value, out t))
// {
// inputFormatPass &= false;
// e.ErrorFields.Add(field, "必須填入整數");
// }
// break;
case "學期":
if (value == "" || !int.TryParse(value, out t) || t > 2 || t < 1)
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填入1或2");
}
break;
//case "分數評量":
// if (value != "" && !decimal.TryParse(value, out d))
// {
// inputFormatPass &= false;
// e.ErrorFields.Add(field, "必須填入空白或數值");
// }
// break;
case "成績":
if (value != "" && !decimal.TryParse(value, out d))
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填入空白或數值");
}
break;
case "原始成績":
if (value != "" && !decimal.TryParse(value, out d))
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填入空白或數值");
}
break;
case "補考成績":
if (value != "" && !decimal.TryParse(value, out d))
{
inputFormatPass &= false;
e.ErrorFields.Add(field, "必須填入空白或數值");
}
break;
//case "努力程度":
// if (value != "" && !int.TryParse(value, out t))
// {
// inputFormatPass &= false;
// e.ErrorFields.Add(field, "必須填入空白或數值");
// }
// break;
}
}
#endregion
//輸入格式正確才會針對情節做檢驗
if (inputFormatPass)
{
string errorMessage = "";
string subject = e.Data["科目"];
string schoolYear = e.Data["學年度"];
string semester = e.Data["學期"];
int? sy = null;
int? se = null;
if (int.TryParse(schoolYear, out t))
{
sy = t;
}
if (int.TryParse(semester, out t))
{
se = t;
}
if (sy != null && se != null)
{
string key = e.Data.ID + "_" + sy + "_" + se;
#region 驗證新增科目成績
List <JHSemesterScoreRecord> semsList;
if (semsDict.ContainsKey(student.ID))
{
semsList = semsDict[student.ID];
}
else
{
semsList = new List <JHSemesterScoreRecord>();
}
foreach (JHSemesterScoreRecord record in semsList)
{
if (record.SchoolYear != sy)
{
continue;
}
if (record.Semester != se)
{
continue;
}
bool isNewSubjectInfo = true;
string message = "";
foreach (K12.Data.SubjectScore s in record.Subjects.Values)
{
if (s.Subject == subject)
{
isNewSubjectInfo = false;
}
}
if (isNewSubjectInfo)
{
if (!e.WarningFields.ContainsKey("查無此科目"))
{
e.WarningFields.Add("查無此科目", "學生在此學期並無此筆科目成績資訊，將會新增此科目成績");
}
foreach (string field in new string[] { "領域", "科目", "學年度", "學期", "權數", "節數" })
{
if (!e.SelectFields.Contains(field))
{
message += (message == "" ? "發現此學期無此科目，\n將會新增成績\n缺少成績必要欄位" : "、") + field;
}
}
if (message != "")
{
errorMessage += (errorMessage == "" ? "" : "\n") + message;
}
}
}
#endregion
#region 驗證重複科目資料
//string skey = subject + "_" + le;
string skey = subject;
if (!_ID_SchoolYear_Semester_Subject.ContainsKey(key))
{
_ID_SchoolYear_Semester_Subject.Add(key, new List <string>());
}
if (_ID_SchoolYear_Semester_Subject[key].Contains(skey))
{
errorMessage += (errorMessage == "" ? "" : "\n") + "同一學期不允許多筆相同科目的資料";
}
else
{
_ID_SchoolYear_Semester_Subject[key].Add(skey);
}
#endregion
}
e.ErrorMessage = errorMessage;
}
#endregion
};
wizard.ImportPackage += delegate(object sender, SmartSchool.API.PlugIn.Import.ImportPackageEventArgs e)
{
#region ImportPackage
Dictionary <string, List <RowData> > id_Rows = new Dictionary <string, List <RowData> >();
#region 分包裝
foreach (RowData data in e.Items)
{
if (!id_Rows.ContainsKey(data.ID))
{
id_Rows.Add(data.ID, new List <RowData>());
}
id_Rows[data.ID].Add(data);
}
#endregion
//List<SmartSchool.Feature.Score.AddScore.InsertInfo> insertList = new List<SmartSchool.Feature.Score.AddScore.InsertInfo>();
//List<SmartSchool.Feature.Score.EditScore.UpdateInfo> updateList = new List<SmartSchool.Feature.Score.EditScore.UpdateInfo>();
List <JHSemesterScoreRecord> insertList = new List <JHSemesterScoreRecord>();
List <JHSemesterScoreRecord> updateList = new List <JHSemesterScoreRecord>();
//交叉比對各學生資料
#region 交叉比對各學生資料
foreach (string id in id_Rows.Keys)
{
XmlDocument doc = new XmlDocument();
JHStudentRecord studentRec = _StudentCollection[id];
//該學生的學期科目成績
Dictionary <SemesterInfo, JHSemesterScoreRecord> semesterScoreDictionary = new Dictionary <SemesterInfo, JHSemesterScoreRecord>();
#region 整理現有的成績資料
List <JHSchool.Data.JHSemesterScoreRecord> semsList;
if (semsDict.ContainsKey(studentRec.ID))
{
semsList = semsDict[studentRec.ID];
}
else
{
semsList = new List <JHSchool.Data.JHSemesterScoreRecord>();
}
foreach (JHSemesterScoreRecord var in semsList)
{
SemesterInfo info = new SemesterInfo();
info.SchoolYear = var.SchoolYear;
info.Semester = var.Semester;
if (!semesterScoreDictionary.ContainsKey(info))
{
semesterScoreDictionary.Add(info, var);
}
//string key = var.Subject + "_" + var.Level;
//if (!semesterScoreDictionary.ContainsKey(var.SchoolYear))
// semesterScoreDictionary.Add(var.SchoolYear, new Dictionary<int, Dictionary<string, SemesterSubjectScoreInfo>>());
//if (!semesterScoreDictionary[var.SchoolYear].ContainsKey(var.Semester))
// semesterScoreDictionary[var.SchoolYear].Add(var.Semester, new Dictionary<string, SemesterSubjectScoreInfo>());
//if (!semesterScoreDictionary[var.SchoolYear][var.Semester].ContainsKey(key))
// semesterScoreDictionary[var.SchoolYear][var.Semester].Add(key, var);
}
#endregion
//要匯入的學期科目成績
Dictionary <SemesterInfo, Dictionary <string, RowData> > semesterImportScoreDictionary = new Dictionary <SemesterInfo, Dictionary <string, RowData> >();
#region 整理要匯入的資料
foreach (RowData row in id_Rows[id])
{
int t;
string subject = row["科目"];
string schoolYear = row["學年度"];
string semester = row["學期"];
int sy = int.Parse(schoolYear);
int se = int.Parse(semester);
SemesterInfo info = new SemesterInfo();
info.SchoolYear = sy;
info.Semester = se;
if (!semesterImportScoreDictionary.ContainsKey(info))
{
semesterImportScoreDictionary.Add(info, new Dictionary <string, RowData>());
}
if (!semesterImportScoreDictionary[info].ContainsKey(subject))
{
semesterImportScoreDictionary[info].Add(subject, row);
}
}
#endregion
//學期年級重整
//Dictionary<SemesterInfo, int> semesterGradeYear = new Dictionary<SemesterInfo, int>();
//要變更成績的學期
List <SemesterInfo> updatedSemester = new List <SemesterInfo>();
//在變更學期中新增加的成績資料
Dictionary <SemesterInfo, List <RowData> > updatedNewSemesterScore = new Dictionary <SemesterInfo, List <RowData> >();
//要增加成績的學期
Dictionary <SemesterInfo, List <RowData> > insertNewSemesterScore = new Dictionary <SemesterInfo, List <RowData> >();
//開始處理ImportScore
#region 開始處理ImportScore
foreach (SemesterInfo info in semesterImportScoreDictionary.Keys)
{
foreach (string subject in semesterImportScoreDictionary[info].Keys)
{
RowData data = semesterImportScoreDictionary[info][subject];
//如果是本來沒有這筆學期的成績就加到insertNewSemesterScore
if (!semesterScoreDictionary.ContainsKey(info))
{
if (!insertNewSemesterScore.ContainsKey(info))
{
insertNewSemesterScore.Add(info, new List <RowData>());
}
insertNewSemesterScore[info].Add(data);
}
else
{
bool hasChanged = false;
//修改已存在的資料
if (semesterScoreDictionary[info].Subjects.ContainsKey(subject))
{
JHSemesterScoreRecord record = semesterScoreDictionary[info];
#region 直接修改已存在的成績資料的Detail
foreach (string field in e.ImportFields)
{
K12.Data.SubjectScore score = record.Subjects[subject];
string value = data[field];
//"分數評量", "努力程度", "文字描述", "註記"
switch (field)
{
default:
break;
case "領域":
if (score.Domain != value)
{
score.Domain = value;
hasChanged = true;
}
break;
case "權數":
if ("" + score.Credit != value)
{
score.Credit = decimal.Parse(value);
hasChanged = true;
}
break;
case "節數":
if ("" + score.Period != value)
{
score.Period = decimal.Parse(value);
hasChanged = true;
}
break;
//case "成績年級":
// int gy = int.Parse(data["成績年級"]);
// if (record.GradeYear != gy)
// {
// semesterGradeYear[info] = gy;
// hasChanged = true;
// }
// break;
//case "分數評量":
// if ("" + score.Score != value)
// {
// decimal d;
// if (decimal.TryParse(value, out d))
// score.Score = d;
// else
// score.Score = null;
// hasChanged = true;
// }
// break;
case "成績":
if ("" + score.Score != value)
{
decimal d;
if (decimal.TryParse(value, out d))
{
score.Score = d;
}
else
{
score.Score = null;
}
hasChanged = true;
}
break;
case "原始成績":
if ("" + score.ScoreOrigin != value)
{
decimal d;
if (decimal.TryParse(value, out d))
{
score.ScoreOrigin = d;
}
else
{
score.ScoreOrigin = null;
}
hasChanged = true;
}
break;
case "補考成績":
if ("" + score.ScoreMakeup != value)
{
decimal d;
if (decimal.TryParse(value, out d))
{
score.ScoreMakeup = d;
}
else
{
score.ScoreMakeup = null;
}
hasChanged = true;
}
break;
//case "努力程度":
// if ("" + score.Effort != value)
// {
// int i;
// if (int.TryParse(value, out i))
// score.Effort = i;
// else
// score.Effort = null;
// hasChanged = true;
// }
// break;
case "文字描述":
if ("" + score.Text != value)
{
score.Text = value;
hasChanged = true;
}
break;
case "註記":
if (score.Comment != value)
{
score.Comment = value;
hasChanged = true;
}
break;
}
}
#endregion
}
else//加入新成績至已存在的學期
{
if (!updatedNewSemesterScore.ContainsKey(info))
{
updatedNewSemesterScore.Add(info, new List <RowData>());
}
updatedNewSemesterScore[info].Add(data);
hasChanged = true;
}
//真的有變更
if (hasChanged)
{
#region 登錄有變更的學期
if (!updatedSemester.Contains(info))
{
updatedSemester.Add(info);
}
#endregion
}
}
}
}
#endregion
//處理已登錄要更新的學期成績
#region 處理已登錄要更新的學期成績
foreach (SemesterInfo info in updatedSemester)
{
//Dictionary<int, Dictionary<int, string>> semeScoreID = (Dictionary<int, Dictionary<int, string>>)studentRec.Fields["SemesterSubjectScoreID"];
//string semesterScoreID = semeScoreID[sy][se];//從學期抓ID
//int gradeyear = semesterGradeYear[info];//抓年級
//XmlElement subjectScoreInfo = doc.CreateElement("SemesterSubjectScoreInfo");
#region 產生該學期科目成績的XML
//foreach (SemesterSubjectScoreInfo scoreInfo in semesterScoreDictionary[sy][se].Values)
//{
// subjectScoreInfo.AppendChild(doc.ImportNode(scoreInfo.Detail, true));
//}
updateList.Add(semesterScoreDictionary[info]);
//if (updatedNewSemesterScore.ContainsKey(sy) && updatedNewSemesterScore[sy].ContainsKey(se))
if (updatedNewSemesterScore.ContainsKey(info))
{
foreach (RowData row in updatedNewSemesterScore[info])
{
//XmlElement newScore = doc.CreateElement("Subject");
K12.Data.SubjectScore subjectScore = new K12.Data.SubjectScore();
#region 建立newScore
//foreach (string field in new string[] { "領域", "科目", "權數", "節數", "分數評量", "努力程度", "文字描述", "註記" })
//foreach (string field in new string[] { "領域", "科目", "權數", "節數", "分數評量", "文字描述", "註記" })
foreach (string field in new string[] { "領域", "科目", "權數", "節數", "成績", "原始成績", "補考成績", "文字描述", "註記" })
{
if (e.ImportFields.Contains(field))
{
decimal d;
#region 填入科目資訊
string value = row[field];
switch (field)
{
default:
break;
case "領域":
subjectScore.Domain = value;
break;
case "科目":
subjectScore.Subject = value;
break;
case "權數":
subjectScore.Credit = decimal.Parse(value);
break;
case "節數":
subjectScore.Period = decimal.Parse(value);
break;
//case "分數評量":
// decimal d;
// if (decimal.TryParse(value, out d))
// subjectScore.Score = d;
// else
// subjectScore.Score = null;
// break;
case "成績":
if (decimal.TryParse(value, out d))
{
subjectScore.Score = d;
}
else
{
subjectScore.Score = null;
}
break;
case "原始成績":
if (decimal.TryParse(value, out d))
{
subjectScore.ScoreOrigin = d;
}
else
{
subjectScore.ScoreOrigin = null;
}
break;
case "補考成績":
if (decimal.TryParse(value, out d))
{
subjectScore.ScoreMakeup = d;
}
else
{
subjectScore.ScoreMakeup = null;
}
break;
//case "努力程度":
// int i;
// if (int.TryParse(value, out i))
// subjectScore.Effort = i;
// else
// subjectScore.Effort = null;
// break;
case "文字描述":
subjectScore.Text = value;
break;
case "註記":
subjectScore.Comment = value;
break;
}
#endregion
}
}
#endregion
//subjectScoreInfo.AppendChild(newScore);
JHSemesterScoreRecord record = semesterScoreDictionary[info];
if (!record.Subjects.ContainsKey(subjectScore.Subject))
{
record.Subjects.Add(subjectScore.Subject, subjectScore);
}
else
{
record.Subjects[subjectScore.Subject] = subjectScore;
}
updateList.Add(record);
}
}
#endregion
//updateList.Add(new SmartSchool.Feature.Score.EditScore.UpdateInfo(semesterScoreID, gradeyear, subjectScoreInfo));
}
#endregion
//處理新增成績學期
#region 處理新增成績學期
foreach (SemesterInfo info in insertNewSemesterScore.Keys)
{
//int gradeyear = semesterGradeYear[info];//抓年級
foreach (RowData row in insertNewSemesterScore[info])
{
K12.Data.SubjectScore subjectScore = new K12.Data.SubjectScore();
//foreach (string field in new string[] { "領域", "科目", "權數", "節數", "分數評量", "努力程度", "文字描述", "註記" })
//foreach (string field in new string[] { "領域", "科目", "權數", "節數", "分數評量", "文字描述", "註記" })
foreach (string field in new string[] { "領域", "科目", "權數", "節數", "成績", "原始成績", "補考成績", "文字描述", "註記" })
{
if (e.ImportFields.Contains(field))
{
decimal d;
string value = row[field];
switch (field)
{
default: break;
case "領域":
subjectScore.Domain = value;
break;
case "科目":
subjectScore.Subject = value;
break;
case "權數":
subjectScore.Credit = decimal.Parse(value);
break;
case "節數":
subjectScore.Period = decimal.Parse(value);
break;
//case "分數評量":
// decimal d;
// if (decimal.TryParse(value, out d))
// subjectScore.Score = d;
// else
// subjectScore.Score = null;
// break;
case "成績":
if (decimal.TryParse(value, out d))
{
subjectScore.Score = d;
}
else
{
subjectScore.Score = null;
}
break;
case "原始成績":
if (decimal.TryParse(value, out d))
{
subjectScore.ScoreOrigin = d;
}
else
{
subjectScore.ScoreOrigin = null;
}
break;
case "補考成績":
if (decimal.TryParse(value, out d))
{
subjectScore.ScoreMakeup = d;
}
else
{
subjectScore.ScoreMakeup = null;
}
break;
//case "努力程度":
// int i;
// if (int.TryParse(value, out i))
// subjectScore.Effort = i;
// else
// subjectScore.Effort = null;
// break;
case "文字描述":
subjectScore.Text = value;
break;
case "註記":
subjectScore.Comment = value;
break;
}
}
}
//subjectScoreInfo.AppendChild(newScore);
JHSemesterScoreRecord record = new JHSemesterScoreRecord();
record.SchoolYear = info.SchoolYear;
record.Semester = info.Semester;
record.RefStudentID = studentRec.ID;
//record.GradeYear = gradeyear;
if (!record.Subjects.ContainsKey(subjectScore.Subject))
{
record.Subjects.Add(subjectScore.Subject, subjectScore);
}
else
{
record.Subjects[subjectScore.Subject] = subjectScore;
}
insertList.Add(record);
}
//insertList.Add(new SmartSchool.Feature.Score.AddScore.InsertInfo(studentRec.StudentID, "" + sy, "" + se, gradeyear, "", subjectScoreInfo));
}
#endregion
}
#endregion
Dictionary <string, JHSemesterScoreRecord> iList = new Dictionary <string, JHSemesterScoreRecord>();
Dictionary <string, JHSemesterScoreRecord> uList = new Dictionary <string, JHSemesterScoreRecord>();
foreach (var record in insertList)
{
string key = record.RefStudentID + "_" + record.SchoolYear + "_" + record.Semester;
if (!iList.ContainsKey(key))
{
iList.Add(key, new JHSemesterScoreRecord());
}
JHSemesterScoreRecord newRecord = iList[key];
newRecord.RefStudentID = record.RefStudentID;
newRecord.SchoolYear = record.SchoolYear;
newRecord.Semester = record.Semester;
foreach (var subject in record.Subjects.Keys)
{
if (!newRecord.Subjects.ContainsKey(subject))
{
newRecord.Subjects.Add(subject, record.Subjects[subject]);
}
}
}
foreach (var record in updateList)
{
string key = record.RefStudentID + "_" + record.SchoolYear + "_" + record.Semester;
if (!uList.ContainsKey(key))
{
uList.Add(key, record);
}
JHSemesterScoreRecord newRecord = uList[key];
newRecord.RefStudentID = record.RefStudentID;
newRecord.SchoolYear = record.SchoolYear;
newRecord.Semester = record.Semester;
newRecord.ID = record.ID;
foreach (var subject in record.Subjects.Keys)
{
if (!newRecord.Subjects.ContainsKey(subject))
{
newRecord.Subjects.Add(subject, record.Subjects[subject]);
}
}
}
List <string> ids = new List <string>(id_Rows.Keys);
Dictionary <string, JHSemesterScoreRecord> origs = new Dictionary <string, JHSemesterScoreRecord>();
foreach (var record in JHSemesterScore.SelectByStudentIDs(ids))
{
if (!origs.ContainsKey(record.ID))
{
origs.Add(record.ID, record);
}
}
foreach (var record in uList.Values)
{
if (origs.ContainsKey(record.ID))
{
foreach (var domain in origs[record.ID].Domains.Keys)
{
if (!record.Domains.ContainsKey(domain))
{
record.Domains.Add(domain, origs[record.ID].Domains[domain]);
}
}
}
}
JHSemesterScore.Insert(new List <JHSemesterScoreRecord>(iList.Values));
JHSemesterScore.Update(new List <JHSemesterScoreRecord>(uList.Values));
FISCA.LogAgent.ApplicationLog.Log("成績系統.匯入匯出", "匯入學期科目成績", "總共匯入" + (insertList.Count + updateList.Count) + "筆學期科目成績。");
#endregion
};
wizard.ImportComplete += delegate
{
MsgBox.Show("匯入完成");
};
}
private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
List <JHPeriodMappingInfo> periodList = JHPeriodMapping.SelectAll();
List <JHAbsenceMappingInfo> absenceList = JHAbsenceMapping.SelectAll();
double total = _config.Students.Count;
double count = 0;
List <string> student_ids = new List <string>();
foreach (JHStudentRecord item in _config.Students)
{
student_ids.Add(item.ID);
}
#region 快取資料
Dictionary <string, List <AutoSummaryRecord> > autoSummaryCache = new Dictionary <string, List <AutoSummaryRecord> >();
foreach (AutoSummaryRecord record in AutoSummary.Select(student_ids, null))
{
if (!autoSummaryCache.ContainsKey(record.RefStudentID))
{
autoSummaryCache.Add(record.RefStudentID, new List <AutoSummaryRecord>());
}
autoSummaryCache[record.RefStudentID].Add(record);
}
Dictionary <string, List <JHUpdateRecordRecord> > updateRecordCache = new Dictionary <string, List <JHUpdateRecordRecord> >();
foreach (var record in JHUpdateRecord.SelectByStudentIDs(student_ids))
{
if (!updateRecordCache.ContainsKey(record.StudentID))
{
updateRecordCache.Add(record.StudentID, new List <JHUpdateRecordRecord>());
}
updateRecordCache[record.StudentID].Add(record);
}
Dictionary <string, List <JHSemesterScoreRecord> > semesterScoreCache = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (var record in JHSemesterScore.SelectByStudentIDs(student_ids))
{
if (!semesterScoreCache.ContainsKey(record.RefStudentID))
{
semesterScoreCache.Add(record.RefStudentID, new List <JHSemesterScoreRecord>());
}
semesterScoreCache[record.RefStudentID].Add(record);
}
Dictionary <string, JHSemesterHistoryRecord> semesterHistoryCache = new Dictionary <string, JHSemesterHistoryRecord>();
foreach (var record in JHSemesterHistory.SelectByStudentIDs(student_ids))
{
if (!semesterHistoryCache.ContainsKey(record.RefStudentID))
{
semesterHistoryCache.Add(record.RefStudentID, record);
}
}
#endregion
#region 取得所有科目
Dictionary <string, SubjectScore> subjectDict = new Dictionary <string, SubjectScore>();
foreach (JHSemesterScoreRecord record in JHSemesterScore.SelectByStudentIDs(student_ids))
{
foreach (SubjectScore subject in record.Subjects.Values)
{
string key = Bind(subject.Domain, subject.Subject);
if (!subjectDict.ContainsKey(key))
{
subjectDict.Add(key, subject);
}
}
}
List <SubjectScore> subjectList = new List <SubjectScore>(subjectDict.Values);
subjectList.Sort(delegate(SubjectScore x, SubjectScore y)
{
List <string> list = new List <string>(new string[] { "國語文", "國文", "英文", "英語", "數學", "歷史", "地理", "公民", "理化", "生物" });
int ix = list.IndexOf(x.Subject);
int iy = list.IndexOf(y.Subject);
if (ix >= 0 && iy >= 0)
{
return(ix.CompareTo(iy));
}
else if (ix >= 0)
{
return(-1);
}
else if (iy >= 0)
{
return(1);
}
else
{
return(x.Subject.CompareTo(y.Subject));
}
});
#endregion
#region 判斷要列印的領域科目
Dictionary <string, bool> domains = new Dictionary <string, bool>();
Dictionary <string, List <string> > subjects = new Dictionary <string, List <string> >();
if (_config.DomainSubjectSetup == "Domain")
{
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
domains.Add(domain, true);
}
if (domains.ContainsKey("語文"))
{
domains["語文"] = false;
}
if (domains.ContainsKey("彈性課程"))
{
domains["彈性課程"] = false;
}
if (!domains.ContainsKey(""))
{
domains.Add("", false);
}
}
else if (_config.DomainSubjectSetup == "Subject")
{
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
domains.Add(domain, false);
}
if (!domains.ContainsKey(""))
{
domains.Add("", false);
}
}
else
{
throw new Exception("請重新儲存列印設定");
}
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
subjects.Add(domain, new List <string>());
}
if (!subjects.ContainsKey(""))
{
subjects.Add("", new List <string>());
}
foreach (SubjectScore ss in subjectList)
{
if (!subjects.ContainsKey(ss.Domain))
{
subjects.Add(ss.Domain, new List <string>());
}
//很怪
if (domains.ContainsKey(ss.Domain) && domains[ss.Domain] == true)
{
continue;
}
if (!subjects[ss.Domain].Contains(ss.Subject))
{
subjects[ss.Domain].Add(ss.Subject);
}
}
_config.SetPrintDomains(domains);
_config.SetPrintSubjects(subjects);
#endregion
#region 依節權數設定，在畫面上顯示
DocumentBuilder templateBuilder = new DocumentBuilder(_template);
string pcDisplay = string.Empty;
if (_config.PrintPeriod && _config.PrintCredit)
{
pcDisplay = "節/權數";
}
else if (_config.PrintPeriod)
{
pcDisplay = "節數";
}
else if (_config.PrintCredit)
{
pcDisplay = "權數";
}
while (templateBuilder.MoveToMergeField("節權數"))
{
templateBuilder.Write(pcDisplay);
}
#endregion
#region 務學習時數
Config._SLRDict.Clear();
Config._SLRDict = Utility.GetServiceLearningDetail(student_ids);
#endregion
#region 產生
foreach (JHStudentRecord student in _config.Students)
{
count++;
DocumentBuilder builder = null;
#region 建立學期歷程對照
List <SemesterHistoryItem> semesterHistoryList = null;
if (semesterHistoryCache.ContainsKey(student.ID))
{
semesterHistoryList = semesterHistoryCache[student.ID].SemesterHistoryItems;
}
else
{
semesterHistoryList = new List <SemesterHistoryItem>();
}
SemesterMap map = new SemesterMap();
map.SetData(semesterHistoryList);
#endregion
Document each = (Document)_template.Clone(true);
builder = new DocumentBuilder(each);
#region 基本資料
StudentBasicInfo basic = new StudentBasicInfo();
basic.SetStudent(student, semesterHistoryList);
each.MailMerge.MergeField += delegate(object sender1, MergeFieldEventArgs e1)
{
#region 處理照片
if (e1.FieldName == "照片粘貼處")
{
DocumentBuilder builder1 = new DocumentBuilder(e1.Document);
builder1.MoveToField(e1.Field, true);
byte[] photoBytes = null;
try
{
photoBytes = Convert.FromBase64String("" + e1.FieldValue);
}
catch (Exception ex)
{
builder1.Write("照片粘貼處");
e1.Field.Remove();
return;
}
if (photoBytes == null || photoBytes.Length == 0)
{
builder1.Write("照片粘貼處");
e1.Field.Remove();
return;
}
e1.Field.Remove();
Shape photoShape = new Shape(e1.Document, ShapeType.Image);
photoShape.ImageData.SetImage(photoBytes);
photoShape.WrapType = WrapType.Inline;
#region AutoResize
double origHWRate = photoShape.ImageData.ImageSize.HeightPoints / photoShape.ImageData.ImageSize.WidthPoints;
double shapeHeight = (builder1.CurrentParagraph.ParentNode.ParentNode as Row).RowFormat.Height * 6;
double shapeWidth = (builder1.CurrentParagraph.ParentNode as Cell).CellFormat.Width;
if ((shapeHeight / shapeWidth) < origHWRate)
{
shapeWidth = shapeHeight / origHWRate;
}
else
{
shapeHeight = shapeWidth * origHWRate;
}
#endregion
photoShape.Height = shapeHeight * 0.9;
photoShape.Width = shapeWidth * 0.9;
builder1.InsertNode(photoShape);
}
#endregion
};
//each.MailMerge.FieldMergingCallback = new InsertDocumentAtMailMergeHandler();
List <string> fieldName = new List <string>();
fieldName.AddRange(basic.GetFieldName());
List <string> fieldValue = new List <string>();
fieldValue.AddRange(basic.GetFieldValue());
each.MailMerge.Execute(fieldName.ToArray(), fieldValue.ToArray());
#endregion
#region 異動資料
List <JHUpdateRecordRecord> updateRecordList = null;
if (updateRecordCache.ContainsKey(student.ID))
{
updateRecordList = updateRecordCache[student.ID];
}
else
{
updateRecordList = new List <JHUpdateRecordRecord>();
}
StudentUpdateRecordProcessor updateRecordProcessor = new StudentUpdateRecordProcessor(each);
updateRecordProcessor.SetData(updateRecordList);
#endregion
#region 成績資料
List <JHSemesterScoreRecord> semesterScoreList = null;
if (semesterScoreCache.ContainsKey(student.ID))
{
semesterScoreList = semesterScoreCache[student.ID];
}
else
{
semesterScoreList = new List <JHSemesterScoreRecord>();
}
StudentSemesterScoreProcessor semesterScoreProcessor = new StudentSemesterScoreProcessor(builder, map, _config);
semesterScoreProcessor.SetData(semesterScoreList);
#endregion
List <AutoSummaryRecord> autoSummaryList = null;
if (autoSummaryCache.ContainsKey(student.ID))
{
autoSummaryList = autoSummaryCache[student.ID];
}
else
{
autoSummaryList = new List <AutoSummaryRecord>();
}
#region 獎懲資料
StudentDisciplineProcessor disciplineProcessor = new StudentDisciplineProcessor(builder, map);
disciplineProcessor.SetData(autoSummaryList);
#endregion
#region 缺曠資料
StudentAttendanceProcessor attendanceProcessor = new StudentAttendanceProcessor(builder, map);
attendanceProcessor.SetData(autoSummaryList);
#endregion
#region 日常行為
StudentTextScoreProcessor textScoreProcessor = new StudentTextScoreProcessor(builder, map);
textScoreProcessor.SetData(autoSummaryList);
#endregion
SemesterHistoryProcessor semesterHistoryProcessor = new SemesterHistoryProcessor(builder, map);
foreach (Section sec in each.Sections)
{
_doc.Sections.Add(_doc.ImportNode(sec, true));
}
//回報進度
_worker.ReportProgress((int)(count * 100.0 / total));
}
List <string> globalFieldName = new List <string>();
List <object> globalFieldValue = new List <object>();
globalFieldName.Add("學校名稱");
globalFieldValue.Add(School.ChineseName);
globalFieldName.Add("列印日期");
globalFieldValue.Add(Common.CDate(DateTime.Now.ToString("yyyy/MM/dd")) + " " + DateTime.Now.ToString("HH:mm:ss"));
string chancellor, eduDirector, stuDirector;
chancellor = eduDirector = stuDirector = string.Empty;
XmlElement info = School.Configuration["學校資訊"].PreviousData;
XmlElement chancellorElement = (XmlElement)info.SelectSingleNode("ChancellorChineseName");
XmlElement eduDirectorElement = (XmlElement)info.SelectSingleNode("EduDirectorName");
XmlElement stuDirectorElement = (XmlElement)info.SelectSingleNode("StuDirectorName");
if (chancellorElement != null)
{
chancellor = chancellorElement.InnerText;
}
if (eduDirectorElement != null)
{
eduDirector = eduDirectorElement.InnerText;
}
if (stuDirectorElement != null)
{
stuDirector = stuDirectorElement.InnerText;
}
globalFieldName.Add("教務主任");
globalFieldValue.Add(eduDirector);
globalFieldName.Add("學務主任");
globalFieldValue.Add(stuDirector);
globalFieldName.Add("校長");
globalFieldValue.Add(chancellor);
_doc.MailMerge.Execute(globalFieldName.ToArray(), globalFieldValue.ToArray());
#endregion
}
public Dictionary <string, bool> Evaluate(IEnumerable <StudentRecord> list)
{
_result.Clear();
Dictionary <string, bool> passList = new Dictionary <string, bool>();
Dictionary <string, List <JHSemesterScoreRecord> > studentSemesterScoreCache = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (JHSemesterScoreRecord record in JHSemesterScore.SelectByStudentIDs(list.AsKeyList()))
{
if (!studentSemesterScoreCache.ContainsKey(record.RefStudentID))
{
studentSemesterScoreCache.Add(record.RefStudentID, new List <JHSemesterScoreRecord>());
}
studentSemesterScoreCache[record.RefStudentID].Add(record);
}
//學生能被承認的學年度學期對照
Dictionary <string, List <string> > studentSYSM = new Dictionary <string, List <string> >();
#region 學期歷程
foreach (K12.Data.SemesterHistoryRecord shr in K12.Data.SemesterHistory.SelectByStudentIDs(list.Select(x => x.ID)))
{
if (!studentSYSM.ContainsKey(shr.RefStudentID))
{
studentSYSM.Add(shr.RefStudentID, new List <string>());
}
Dictionary <string, K12.Data.SemesterHistoryItem> check = new Dictionary <string, K12.Data.SemesterHistoryItem>()
{
{ "1a", null },
{ "1b", null },
{ "2a", null },
{ "2b", null },
{ "3a", null },
{ "3b", null }
};
foreach (K12.Data.SemesterHistoryItem item in shr.SemesterHistoryItems)
{
string grade = item.GradeYear + "";
if (grade == "7")
{
grade = "1";
}
if (grade == "8")
{
grade = "2";
}
if (grade == "9")
{
grade = "3";
}
if (grade == "1" || grade == "2" || grade == "3")
{
string key = "";
if (item.Semester == 1)
{
key = grade + "a";
}
else if (item.Semester == 2)
{
key = grade + "b";
}
else
{
continue;
}
//相同年級取較新的學年度
if (check[key] == null)
{
check[key] = item;
}
else if (item.SchoolYear > check[key].SchoolYear)
{
check[key] = item;
}
}
}
foreach (string key in check.Keys)
{
if (check[key] == null)
{
continue;
}
K12.Data.SemesterHistoryItem item = check[key];
studentSYSM[shr.RefStudentID].Add(item.SchoolYear + "_" + item.Semester);
}
}
#endregion
foreach (StudentRecord each in list)
{
List <ResultDetail> resultList = new List <ResultDetail>();
// 有學期成績
if (studentSemesterScoreCache.ContainsKey(each.ID))
{
// 存放符合標準畢業領域成績
List <decimal> passScoreList = new List <decimal>();
//每個學期整理後的成績
List <List <K12.Data.DomainScore> > GradeScoreList = new List <List <K12.Data.DomainScore> >();
// 取得學生學生領域成績填入計算畢業成績用
foreach (JHSemesterScoreRecord record in studentSemesterScoreCache[each.ID])
{
string key = record.SchoolYear + "_" + record.Semester;
//只處理承認的學年度學期
if (!studentSYSM.ContainsKey(each.ID) || !studentSYSM[each.ID].Contains(key))
{
continue;
}
//整理後的領域成績
List <K12.Data.DomainScore> domainScoreList = new List <K12.Data.DomainScore>();
K12.Data.DomainScore 語文 = new K12.Data.DomainScore();
語文.Domain = "語文";
decimal sum = 0;
decimal credit = 0;
// 2017/5/25 穎驊新增， 因應 高雄客服 高雄小組 [05-01][--] 項目調整，
// 舊有邏輯無論該學期是否已經有教務作業期末結算完產生 "語文領域" 的領域成績，
// 皆會再額外再幫它算一次並且加入語文領域總分、語文領域權重，此行為容易造成資料的組成比重不對，產生錯誤無法解釋的語文領域分數，
// 現在加入新判斷，如果該學期已經有 語文領域成績， 則不會再另外計算、加入該學期的語文領域成績，
// 反之，如果該學期 沒有語文領域成績， 會再計算一次 補上，以作為畢業預警判斷使用。
bool hasLanguageDomain = false;
//跑一遍領域成績
foreach (K12.Data.DomainScore domain in record.Domains.Values)
{
if (domain.Domain == "語文")
{
hasLanguageDomain = true;
}
//這三種挑出來處理
if (domain.Domain == "國語文" || domain.Domain == "英語")
{
if (domain.Score.HasValue && domain.Credit.HasValue)
{
sum += domain.Score.Value * domain.Credit.Value;
credit += domain.Credit.Value;
//處理高雄語文顯示
// 加權總分
if (!TempData.tmpStudDomainScoreDict.ContainsKey(record.RefStudentID))
{
TempData.tmpStudDomainScoreDict.Add(record.RefStudentID, new Dictionary <string, decimal>());
}
if (!TempData.tmpStudDomainCreditDict.ContainsKey(record.RefStudentID))
{
TempData.tmpStudDomainCreditDict.Add(record.RefStudentID, new Dictionary <string, decimal>());
}
if (!TempData.tmpStudDomainScoreDict[record.RefStudentID].ContainsKey(domain.Domain))
{
TempData.tmpStudDomainScoreDict[record.RefStudentID].Add(domain.Domain, 0);
}
// 學分數
if (!TempData.tmpStudDomainCreditDict[record.RefStudentID].ContainsKey(domain.Domain))
{
TempData.tmpStudDomainCreditDict[record.RefStudentID].Add(domain.Domain, 0);
}
TempData.tmpStudDomainScoreDict[record.RefStudentID][domain.Domain] += (domain.Score.Value * domain.Credit.Value);
TempData.tmpStudDomainCreditDict[record.RefStudentID][domain.Domain] += domain.Credit.Value;
}
}
else
{
domainScoreList.Add(domain);
}
}
if (!hasLanguageDomain && credit > 0)
{
語文.Score = Math.Round(sum / credit, 2, MidpointRounding.AwayFromZero);
語文.Credit = credit;
domainScoreList.Add(語文);
}
//會被加入就代表承認了
GradeScoreList.Add(domainScoreList);
}
Dictionary <string, decimal> domainScoreSum = new Dictionary <string, decimal>();
Dictionary <string, decimal> domainScoreCount = new Dictionary <string, decimal>();
foreach (List <K12.Data.DomainScore> scoreList in GradeScoreList)
{
foreach (K12.Data.DomainScore ds in scoreList)
{
string domainName = ds.Domain;
if (!domainScoreSum.ContainsKey(domainName))
{
domainScoreSum.Add(domainName, 0);
}
if (!domainScoreCount.ContainsKey(domainName))
{
domainScoreCount.Add(domainName, 0);
}
if (ds.Score.HasValue)
{
domainScoreSum[domainName] += ds.Score.Value;
//同一學期不會有相同領域名稱,可直接作++
domainScoreCount[domainName]++;
}
}
}
//2017/5/9 穎驊修正 ，因應 高雄 [08-05][03] 畢業資格判斷成績及格標準調整 項目，
// 領域 分數超過60分 ，以 四捨五入取到小數第二位 ， 低於60分 採用 無條件進位至整數 (EX : 59.01 =60)
// (只有高雄版有如此機制，新竹版照舊不管分數高低都是四捨五入)
foreach (string domainName in domainScoreCount.Keys)
{
if (domainScoreCount[domainName] > 0)
{
decimal grScore = 0;
if (JHEvaluation.ScoreCalculation.Program.Mode == JHEvaluation.ScoreCalculation.ModuleMode.HsinChu)
{
grScore = Math.Round(domainScoreSum[domainName] / domainScoreCount[domainName], 2, MidpointRounding.AwayFromZero);
}
if (JHEvaluation.ScoreCalculation.Program.Mode == JHEvaluation.ScoreCalculation.ModuleMode.KaoHsiung)
{
if (domainScoreSum[domainName] / domainScoreCount[domainName] >= 60)
{
grScore = Math.Round(domainScoreSum[domainName] / domainScoreCount[domainName], 2, MidpointRounding.AwayFromZero);
}
else
{
grScore = Math.Ceiling(domainScoreSum[domainName] / domainScoreCount[domainName]);
}
}
if (grScore >= _score)
{
passScoreList.Add(grScore);
}
StudentDomainResult.AddDomain(each.ID, domainName, grScore, grScore >= _score);
}
}
// 當及格數小於標準數，標示不符格畢業規範
if (passScoreList.Count < _domain_count)
{
ResultDetail rd = new ResultDetail(each.ID, "0", "0");
rd.AddMessage("領域畢業加權總平均成績不符合畢業規範");
rd.AddDetail("領域畢業加權總平均成績不符合畢業規範");
resultList.Add(rd);
}
}
if (resultList.Count > 0)
{
_result.Add(each.ID, resultList);
passList.Add(each.ID, false);
}
else
{
passList.Add(each.ID, true);
}
}
return(passList);
}
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;
}
}
public DataRationalityMessage Execute()
{
QueryHelper Helper = new QueryHelper();
List <string> StudentIDs = new List <string>();
DataTable StudentIDTable = Helper.Select("select ref_student_id from sems_subj_score where score_info like'%<SemesterSubjectScoreInfo/>%' and score_info like'%<Domains/>%' and score_info like'%<LearnDomainScore/>%'");
for (int i = 0; i < StudentIDTable.Rows.Count; i++)
{
StudentIDs.Add("" + StudentIDTable.Rows[i][0]);
}
List <JHSemesterScoreRecord> SemsScoreList = JHSemesterScore.SelectByStudentIDs(StudentIDs);
CorrectableRecs.Clear();
RATRecs.Clear();
DataRationalityMessage retMsg = new DataRationalityMessage();
try
{
foreach (JHSemesterScoreRecord SmesRec in SemsScoreList)
{
if (SmesRec.Subjects.Count == 0 && SmesRec.Domains.Count == 0)
{
EmptySemesterScoreRATRec rec = new EmptySemesterScoreRATRec();
rec.學期成績系統編號 = SmesRec.ID;
rec.學生系統編號 = SmesRec.RefStudentID;
rec.身分證號 = SmesRec.Student.IDNumber;
rec.姓名 = SmesRec.Student.Name;
rec.狀態 = SmesRec.Student.StatusStr;
rec.座號 = K12.Data.Int.GetString(SmesRec.Student.SeatNo);
if (SmesRec.Student.Class != null)
{
rec.班級 = SmesRec.Student.Class.Name;
}
rec.學年度 = SmesRec.SchoolYear.ToString();
rec.學期 = SmesRec.Semester.ToString();
rec.學號 = SmesRec.Student.StudentNumber;
RATRecs.Add(rec);
CorrectableRecs.Add(SmesRec);
}
}
}
catch (Exception ex)
{
retMsg.Message = ex.Message;
return(retMsg);
}
StringBuilder strBuilder = new StringBuilder();
strBuilder.AppendLine("檢查學期成績筆數：" + SemsScoreList.Count);
strBuilder.AppendLine("學期科目與領域成績空值筆數：" + RATRecs.Count);
var SortedRATRecords = from RATRecord in RATRecs orderby RATRecord.狀態, RATRecord.班級, K12.Data.Int.ParseAllowNull(RATRecord.座號), RATRecord.學年度, RATRecord.學期 select RATRecord;
retMsg.Message = strBuilder.ToString();
retMsg.Data = SortedRATRecords.ToList();
return(retMsg);
}
private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
List <string> globalFieldName = new List <string>();
List <object> globalFieldValue = new List <object>();
globalFieldName.Add("學校名稱");
globalFieldValue.Add(K12.Data.School.ChineseName);
globalFieldName.Add("列印日期");
globalFieldValue.Add(DateConvert.CDate(DateTime.Now.ToString("yyyy/MM/dd")));
globalFieldName.Add("列印時間");
globalFieldValue.Add(DateTime.Now.ToString("HH:mm:ss"));
ReportConfiguration _Dylanconfig = new ReportConfiguration(Global.OneFileSave);
OneFileSave = _Dylanconfig.GetBoolean("單檔儲存", false);
StudentDoc = new Dictionary <string, Document>();
double total = _students.Count;
double count = 0;
List <string> student_ids = new List <string>();
foreach (JHStudentRecord item in _students)
{
student_ids.Add(item.ID);
}
#region 快取資料
//取得異動資料
Dictionary <string, List <JHUpdateRecordRecord> > updateRecordCache = new Dictionary <string, List <JHUpdateRecordRecord> >();
foreach (var record in JHUpdateRecord.SelectByStudentIDs(student_ids))
{
if (!updateRecordCache.ContainsKey(record.StudentID))
{
updateRecordCache.Add(record.StudentID, new List <JHUpdateRecordRecord>());
}
updateRecordCache[record.StudentID].Add(record);
}
//取得缺曠獎懲資料
Dictionary <string, List <AutoSummaryRecord> > autoSummaryCache = new Dictionary <string, List <AutoSummaryRecord> >();
foreach (AutoSummaryRecord record in AutoSummary.Select(student_ids, null))
{
if (!autoSummaryCache.ContainsKey(record.RefStudentID))
{
autoSummaryCache.Add(record.RefStudentID, new List <AutoSummaryRecord>());
}
autoSummaryCache[record.RefStudentID].Add(record);
}
//取得學期歷程
Dictionary <string, JHSemesterHistoryRecord> semesterHistoryCache = new Dictionary <string, JHSemesterHistoryRecord>();
foreach (var record in JHSemesterHistory.SelectByStudentIDs(student_ids))
{
if (!semesterHistoryCache.ContainsKey(record.RefStudentID))
{
semesterHistoryCache.Add(record.RefStudentID, record);
}
}
//取得學期成績
Dictionary <string, List <JHSemesterScoreRecord> > semesterScoreCache = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (var record in JHSemesterScore.SelectByStudentIDs(student_ids))
{
if (!semesterScoreCache.ContainsKey(record.RefStudentID))
{
semesterScoreCache.Add(record.RefStudentID, new List <JHSemesterScoreRecord>());
}
semesterScoreCache[record.RefStudentID].Add(record);
}
// 取得畢業成績
Dictionary <string, K12.Data.GradScoreRecord> StudGradScoreDic = new Dictionary <string, GradScoreRecord>();
foreach (GradScoreRecord score in GradScore.SelectByIDs <GradScoreRecord>(student_ids))
{
StudGradScoreDic.Add(score.RefStudentID, score);
}
////課程
//JHCourse.RemoveAll();
//Dictionary<string, JHCourseRecord> courseCache = new Dictionary<string, JHCourseRecord>();
//foreach (JHCourseRecord record in JHCourse.SelectAll())
//{
// if (!courseCache.ContainsKey(record.ID))
// courseCache.Add(record.ID, record);
//}
#endregion
#region 判斷要列印的領域科目
Dictionary <string, bool> domains = new Dictionary <string, bool>();
//Dictionary<string, List<string>> subjects = new Dictionary<string, List<string>>();
//List<JHCourseRecord> courseList = new List<JHCourseRecord>(courseCache.Values);
//courseList.Sort(delegate(JHCourseRecord x, JHCourseRecord y)
//{
// return JHSchool.Evaluation.Subject.CompareSubjectOrdinal(x.Subject, y.Subject);
//});
string domainSubjectSetup = Config.GetString("領域科目設定", "Domain");
if (domainSubjectSetup == "Domain")
{
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
domains.Add(domain, DomainSubjectExpand.展開);
}
if (!domains.ContainsKey(""))
{
domains.Add("", DomainSubjectExpand.展開);
}
}
else if (domainSubjectSetup == "Subject")
{
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
domains.Add(domain, DomainSubjectExpand.展開);
}
if (!domains.ContainsKey(""))
{
domains.Add("", DomainSubjectExpand.展開);
}
}
else
{
throw new Exception("請重新儲存一次列印設定");
}
//foreach (var domain in JHSchool.Evaluation.Subject.Domains)
// subjects.Add(domain, new List<string>());
//if (!subjects.ContainsKey("")) subjects.Add("", new List<string>());
//foreach (var course in courseList)
//{
// if (!subjects.ContainsKey(course.Domain))
// subjects.Add(course.Domain, new List<string>());
// if (!subjects[course.Domain].Contains(course.Subject) &&
// domains.ContainsKey(course.Domain) &&
// domains[course.Domain] == false)
// {
// subjects[course.Domain].Add(course.Subject);
// }
//}
#endregion
DocumentBuilder templateBuilder = new DocumentBuilder(_template);
#region 節權數顯示
string pcDisplay = string.Empty;
bool printPeriod = Config.GetBoolean("列印節數", true);
bool printCredit = Config.GetBoolean("列印權數", false);
if (printPeriod && printCredit)
{
pcDisplay = "節" + Environment.NewLine + "權" + Environment.NewLine + "數";
}
else if (printPeriod)
{
pcDisplay = "節" + Environment.NewLine + "數";
}
else if (printCredit)
{
pcDisplay = "權" + Environment.NewLine + "數";
}
while (templateBuilder.MoveToMergeField("節權數"))
{
templateBuilder.Write(pcDisplay);
}
#endregion
#region 文字評語是否列印
bool printText = Config.GetBoolean("列印文字評語", true);
if (printText == false)
{
templateBuilder.MoveToMergeField("學習領域評量");
(templateBuilder.CurrentParagraph.ParentNode as Cell).ParentRow.ParentTable.Remove();
}
#endregion
#region 務學習時數
Global._SLRDict.Clear();
Global._SLRDict = Utility.GetServiceLearningDetail(student_ids);
#endregion
#region 產生
foreach (JHStudentRecord student in _students)
{
count++;
Document each = (Document)_template.Clone(true);
DocumentBuilder builder = new DocumentBuilder(each);
#region 建立學期歷程對照
List <SemesterHistoryItem> semesterHistoryList = null;
if (semesterHistoryCache.ContainsKey(student.ID))
{
semesterHistoryList = semesterHistoryCache[student.ID].SemesterHistoryItems;
}
else
{
semesterHistoryList = new List <SemesterHistoryItem>();
}
SemesterMap map = new SemesterMap();
map.SetData(semesterHistoryList);
#endregion
#region 學生基本資料
StudentBasicInfo basicInfo = new StudentBasicInfo(builder);
basicInfo.SetStudent(student, semesterHistoryList);
#endregion
#region 異動資料
List <JHUpdateRecordRecord> updateRecordList = null;
if (updateRecordCache.ContainsKey(student.ID))
{
updateRecordList = updateRecordCache[student.ID];
}
else
{
updateRecordList = new List <JHUpdateRecordRecord>();
}
StudentUpdateRecordProcessor updateRecordProcessor = new StudentUpdateRecordProcessor(builder);
updateRecordProcessor.SetData(updateRecordList);
#endregion
#region 日常表現
List <AutoSummaryRecord> autoSummaryList = null;
if (autoSummaryCache.ContainsKey(student.ID))
{
autoSummaryList = autoSummaryCache[student.ID];
}
else
{
autoSummaryList = new List <AutoSummaryRecord>();
}
StudentMoralProcessor moralProcessor = new StudentMoralProcessor(builder, map);
moralProcessor.SetData(autoSummaryList);
#endregion
#region 學期歷程
StudentHistoryProcessor history = new StudentHistoryProcessor(builder, map, (semesterHistoryCache.ContainsKey(student.ID) ? semesterHistoryCache[student.ID] : null));
#endregion
#region 學期成績
List <JHSemesterScoreRecord> semesterScoreList = null;
if (semesterScoreCache.ContainsKey(student.ID))
{
semesterScoreList = semesterScoreCache[student.ID];
}
else
{
semesterScoreList = new List <JHSemesterScoreRecord>();
}
// 畢業成績
K12.Data.GradScoreRecord StudGradScore = new GradScoreRecord();
if (StudGradScoreDic.ContainsKey(student.ID))
{
StudGradScore = StudGradScoreDic[student.ID];
}
StudentSemesterScoreProcessor semesterScoreProcessor = new StudentSemesterScoreProcessor(builder, map, domainSubjectSetup, domains, StudGradScore);
semesterScoreProcessor.DegreeMapper = _degreeMapper;
semesterScoreProcessor.PrintPeriod = printPeriod;
semesterScoreProcessor.PrintCredit = printCredit;
semesterScoreProcessor.SetData(semesterScoreList);
#endregion
#region 學習領域評量
if (printText == true)
{
StudentTextProcessor text = new StudentTextProcessor(builder, map);
text.SetData(semesterScoreList);
}
#endregion
if (OneFileSave)
{
each.MailMerge.Execute(globalFieldName.ToArray(), globalFieldValue.ToArray());
string fileName = "";
fileName = student.StudentNumber;
fileName += "_" + student.IDNumber;
if (!string.IsNullOrEmpty(student.RefClassID))
{
fileName += "_" + student.Class.Name;
}
else
{
fileName += "_";
}
fileName += "_" + (student.SeatNo.HasValue ? student.SeatNo.Value.ToString() : "");
fileName += "_" + student.Name;
if (!StudentDoc.ContainsKey(fileName))
{
StudentDoc.Add(fileName, each);
}
}
else
{
foreach (Section sec in each.Sections)
{
_doc.Sections.Add(_doc.ImportNode(sec, true));
}
}
//回報進度
_worker.ReportProgress((int)(count * 100.0 / total));
}
if (!OneFileSave)
{
_doc.MailMerge.Execute(globalFieldName.ToArray(), globalFieldValue.ToArray());
}
#endregion
}
public static void Calculate(List <StudentRecord> students)
{
const string LearnDomain = "學習領域";
const string CourseLearn = "課程學習";
ScoreCalcRule.Instance.SyncAllBackground();
//SemesterScore.Instance.SyncAllBackground();
Dictionary <string, List <JHSemesterScoreRecord> > studentSemesterScoreRecordCache = new Dictionary <string, List <JHSchool.Data.JHSemesterScoreRecord> >();
foreach (JHSemesterScoreRecord record in JHSemesterScore.SelectByStudentIDs(students.AsKeyList()))
{
if (!studentSemesterScoreRecordCache.ContainsKey(record.RefStudentID))
{
studentSemesterScoreRecordCache.Add(record.RefStudentID, new List <JHSchool.Data.JHSemesterScoreRecord>());
}
studentSemesterScoreRecordCache[record.RefStudentID].Add(record);
}
List <GradScoreRecordEditor> editors = new List <GradScoreRecordEditor>();
BackgroundWorker worker = new BackgroundWorker();
worker.WorkerReportsProgress = true;
MultiThreadBackgroundWorker <GradScoreRecordEditor> multiWorker = new MultiThreadBackgroundWorker <GradScoreRecordEditor>();
multiWorker.WorkerReportsProgress = true;
multiWorker.AutoReportsProgress = true;
multiWorker.PackageSize = 50;
multiWorker.Loading = MultiThreadLoading.Light;
worker.DoWork += delegate
{
Dictionary <string, ScoreCalculator> calc = new Dictionary <string, ScoreCalculator>();
double studentTotal = students.Count;
double studentCount = 0;
foreach (StudentRecord student in students)
{
studentCount++;
#region 取得成績計算規則
string calcID = string.Empty;
ScoreCalcRuleRecord calcRecord = student.GetScoreCalcRuleRecord();
if (calcRecord != null)
{
calcID = calcRecord.ID;
if (!calc.ContainsKey(calcID))
{
JHScoreCalcRuleRecord record = null;
List <JHScoreCalcRuleRecord> list = JHScoreCalcRule.SelectByIDs(new string[] { calcRecord.ID });
if (list.Count > 0)
{
record = list[0];
}
calc.Add(calcID, new ScoreCalculator(record));
}
}
else
{
if (!calc.ContainsKey(string.Empty))
{
calc.Add(string.Empty, new ScoreCalculator(null));
}
}
#endregion
#region 取得各學期成績
List <JHSemesterScoreRecord> semesterScoreRecordList;
if (studentSemesterScoreRecordCache.ContainsKey(student.ID))
{
semesterScoreRecordList = studentSemesterScoreRecordCache[student.ID];
}
else
{
semesterScoreRecordList = new List <JHSemesterScoreRecord>();
}
Dictionary <string, List <decimal> > domainScores = new Dictionary <string, List <decimal> >();
foreach (JHSemesterScoreRecord record in semesterScoreRecordList)
{
foreach (K12.Data.DomainScore domain in record.Domains.Values)
{
if (!domainScores.ContainsKey(domain.Domain))
{
domainScores.Add(domain.Domain, new List <decimal>());
}
if (domain.Score.HasValue)
{
domainScores[domain.Domain].Add(domain.Score.Value);
}
}
if (!domainScores.ContainsKey(LearnDomain))
{
domainScores.Add(LearnDomain, new List <decimal>());
}
if (record.LearnDomainScore.HasValue)
{
domainScores[LearnDomain].Add(record.LearnDomainScore.Value);
}
if (!domainScores.ContainsKey(CourseLearn))
{
domainScores.Add(CourseLearn, new List <decimal>());
}
if (record.CourseLearnScore.HasValue)
{
domainScores[CourseLearn].Add(record.CourseLearnScore.Value);
}
}
#endregion
#region 產生畢業成績資料
GradScoreRecordEditor editor;
GradScoreRecord gradScoreRecord = GradScore.Instance.Items[student.ID];
if (gradScoreRecord != null)
{
editor = gradScoreRecord.GetEditor();
}
else
{
editor = new GradScoreRecordEditor(student);
}
editor.LearnDomainScore = null;
editor.CourseLearnScore = null;
editor.Domains.Clear();
foreach (string domain in domainScores.Keys)
{
decimal total = 0;
decimal count = domainScores[domain].Count;
if (count <= 0)
{
continue;
}
foreach (decimal score in domainScores[domain])
{
total += score;
}
total = total / count;
total = calc[calcID].ParseGraduateScore(total);
if (domain == LearnDomain)
{
editor.LearnDomainScore = total;
}
else if (domain == CourseLearn)
{
editor.CourseLearnScore = total;
}
else
{
if (!editor.Domains.ContainsKey(domain))
{
editor.Domains.Add(domain, new GradDomainScore(domain));
}
editor.Domains[domain].Score = total;
}
}
editors.Add(editor);
#endregion
worker.ReportProgress((int)(studentCount * 100 / studentTotal));
}
};
worker.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e)
{
FISCA.Presentation.MotherForm.SetStatusBarMessage("計算畢業成績中", e.ProgressPercentage);
};
worker.RunWorkerCompleted += delegate(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Error != null)
{
MsgBox.Show("計算畢業成績時發生錯誤。" + e.Error.Message);
editors.Clear();
}
else
{
multiWorker.RunWorkerAsync(editors);
}
};
multiWorker.DoWork += delegate(object sender, PackageDoWorkEventArgs <GradScoreRecordEditor> e)
{
IEnumerable <GradScoreRecordEditor> list = e.Items;
list.SaveAllEditors();
};
multiWorker.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e)
{
FISCA.Presentation.MotherForm.SetStatusBarMessage("上傳畢業成績中", e.ProgressPercentage);
};
multiWorker.RunWorkerCompleted += delegate(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Error != null)
{
MsgBox.Show("上傳畢業成績時發生錯誤。" + e.Error.Message);
}
else
{
FISCA.Presentation.MotherForm.SetStatusBarMessage("上傳畢業成績完成");
}
};
worker.RunWorkerAsync();
}
/// <summary>
/// 取得領域成績並判斷是否及格
/// </summary>
/// <param name="studDataList"></param>
/// <param name="SchoolYear"></param>
/// <param name="Semester"></param>
/// <param name="passScore"></param>
/// <returns></returns>
public static List <StudentData> CalcStudDomainScorePass(List <StudentData> studDataList, int SchoolYear, int Semester, decimal passScore)
{
// 取得學期成績
Dictionary <string, JHSemesterScoreRecord> studSemsScoreDict = new Dictionary <string, JHSemesterScoreRecord>();
List <string> studIDList = (from data in studDataList select data.StudentID).ToList();
List <JHSemesterScoreRecord> semsScoreList = JHSemesterScore.SelectByStudentIDs(studIDList);
foreach (JHSemesterScoreRecord rec in semsScoreList)
{
if (rec.SchoolYear == SchoolYear && rec.Semester == Semester)
{
if (!studSemsScoreDict.ContainsKey(rec.RefStudentID))
{
studSemsScoreDict.Add(rec.RefStudentID, rec);
}
}
}
// 判斷學生領域成績是否及格
foreach (StudentData sd in studDataList)
{
if (studSemsScoreDict.ContainsKey(sd.StudentID))
{
sd.StudSemesterScoreRecord = studSemsScoreDict[sd.StudentID];
foreach (DomainScore ds in studSemsScoreDict[sd.StudentID].Domains.Values)
{
if (!sd.DomainScorePassDict.ContainsKey(ds.Domain))
{
sd.DomainScorePassDict.Add(ds.Domain, false);
sd.DomainScoreDict.Add(ds.Domain, 0);
}
if (ds.Score.HasValue)
{
sd.DomainScoreDict[ds.Domain] = ds.Score.Value;
if (ds.Score.Value >= passScore)
{
sd.DomainScorePassDict[ds.Domain] = true;
}
///Cynthia 2021/12/8 新增需求: 若語文及格，則不單獨呈現國語文/英語
///https://3.basecamp.com/4399967/buckets/15852426/todos/4422425947#__recording_4426175131
if (sd.DomainScorePassDict.ContainsKey("語文"))
{
if (sd.DomainScorePassDict["語文"] == true) //如果語文及格
{
if (sd.DomainScorePassDict.ContainsKey("國語文"))
{
sd.DomainScorePassDict["國語文"] = true; //國語文也當成及格，就不會被印出來
}
if (sd.DomainScorePassDict.ContainsKey("英語"))
{
sd.DomainScorePassDict["英語"] = true; //英語也當成及格，就不會被印出來
}
}
}
}
}
}
}
return(studDataList);
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
List <StudentScore> Students = StudentIDs.ToStudentScore();
List <StudentScore> noValid = Students.ReadCalculationRule(this); //沒有計算規則的學生。
if (noValid.Count > 0)
{
throw new CalculationException(noValid, "下列學生沒有計算規則，無法計算畢業成績。");
}
Students.ReadSemesterScore(this); //讀取所有學期的成績。
Students.ReadSemesterHistory(this); //讀取學期歷程。
noValid = Students.VaidSixSemesterHistory(); //驗證學期歷程是否完整。
if (noValid.Count > 0)
{
throw new CalculationException(noValid, "下列學生學期歷程不完整，無法計算畢業成績。");
}
noValid = Students.ValidSixSemesterDomainScore(); //驗證學期成績是否完整。
if (noValid.Count > 0)
{
throw new CalculationException(noValid, "下列學生並沒有完整的十二學期成績，無法計算畢業成績。");
}
Students.ReadGraduateScore(this); //讀取畢業成績。
Students.CalculateGraduateScore(); //計算畢業成績。
// 取得學生 ID
List <string> StudIDs = (from Stud in Students select Stud.Id).ToList();
// 學生科目成績(彈性課程)
Dictionary <string, List <K12.Data.SubjectScore> > StudSubjScore = new Dictionary <string, List <K12.Data.SubjectScore> >();
Dictionary <string, decimal> StudDomainNullScore = new Dictionary <string, decimal>();
foreach (JHSemesterScoreRecord Scr in JHSemesterScore.SelectByStudentIDs(StudentIDs))
{
// 取得領域名稱空白
List <K12.Data.SubjectScore> ss = (from ss1 in Scr.Subjects where ss1.Value.Domain == "" select ss1.Value).ToList();
if (ss.Count > 0)
{
if (StudSubjScore.ContainsKey(Scr.RefStudentID))
{
StudSubjScore[Scr.RefStudentID].AddRange(ss);
}
else
{
StudSubjScore.Add(Scr.RefStudentID, ss);
}
}
}
// 計算彈性課程成績
foreach (KeyValuePair <string, List <K12.Data.SubjectScore> > data in StudSubjScore)
{
decimal sum = 0, Credit = 0;
foreach (K12.Data.SubjectScore ss in data.Value)
{
if (ss.Credit.HasValue)
{
Credit += ss.Credit.Value;
}
if (ss.Score.HasValue)
{
sum += ss.Score.Value;
}
}
if (!StudDomainNullScore.ContainsKey(data.Key))
{
if (Credit > 0)
{
decimal score = sum / Credit;
StudDomainNullScore.Add(data.Key, score);
}
}
}
foreach (StudentScore ss in Students)
{
if (StudDomainNullScore.ContainsKey(ss.Id))
{
//2017/5/9 穎驊修正 ，因應 高雄 [08-05][03] 畢業資格判斷成績及格標準調整 項目，
// 領域 分數超過60分 ，以 四捨五入取到小數第二位 ， 低於60分 採用 無條件進位至整數 (EX : 59.01 =60)
//decimal score = ss.CalculationRule.ParseDomainScore(StudDomainNullScore[ss.Id]);
decimal score = 0;
if (JHEvaluation.ScoreCalculation.Program.Mode == JHEvaluation.ScoreCalculation.ModuleMode.HsinChu)
{
score = ss.CalculationRule.ParseDomainScore(StudDomainNullScore[ss.Id]);
}
if (JHEvaluation.ScoreCalculation.Program.Mode == JHEvaluation.ScoreCalculation.ModuleMode.KaoHsiung)
{
if (StudDomainNullScore[ss.Id] >= 60)
{
score = ss.CalculationRule.ParseDomainScore(StudDomainNullScore[ss.Id]);
}
else
{
score = Math.Ceiling(StudDomainNullScore[ss.Id]);
}
}
if (ss.GraduateScore.Contains("彈性課程"))
{
ss.GraduateScore["彈性課程"].Value = score;
}
else
{
GraduateScore gs = new GraduateScore();
gs.Value = score;
ss.GraduateScore.Add("彈性課程", gs);
}
}
}
Students.SaveGraduateScore(this);//儲存畢業成績。
//儲存 Log。
try
{
LogSaver logger = FISCA.LogAgent.ApplicationLog.CreateLogSaverInstance();
logger.Log("學生畢業成績計算(學生)", "計算學生畢業成績", "計算學生數：" + Students.Count);
//logger.BatchLogCompleted += delegate(object sender1, EventArgs e1) { };
//logger.BatchLogFailure += delegate(object sender1, LogErrorEventArgs e1) { };
foreach (StudentScore each in Students)
{
StringBuilder description = new StringBuilder();
description.AppendLine(string.Format("學生：{0}({1})", each.Name, each.StudentNumber));
foreach (LogData log in each.GraduateScore.Log)
{
description.Append(log.ToString());
}
description.Append(each.GraduateScore.LearningLog.ToString());
description.Append(each.GraduateScore.CourseLog.ToString());
logger.AddBatch("學生畢業成績計算", "計算學生畢業成績", "student", each.Id, description.ToString());
}
logger.LogBatch();
}
catch { }
Feedback("", 0);
}
/// <summary>
/// 取得領域資料並填入成績冊用
/// </summary>
/// <param name="StudentIDList"></param>
/// <param name="StudentInfoList"></param>
/// <returns></returns>
public static List <rptStudentInfo> FillRptDomainScoreInfo(List <string> StudentIDList, List <rptStudentInfo> StudentInfoList)
{
// 取得學生學期成績
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);
}
// 填入學期成績
List <string> tmpDomainNameList = new List <string>();
tmpDomainNameList.Add("健康體育");
tmpDomainNameList.Add("健康與體育");
//tmpDomainNameList.Add("藝術人文");
//tmpDomainNameList.Add("藝術與人文");
tmpDomainNameList.Add("藝術");
tmpDomainNameList.Add("綜合活動");
foreach (rptStudentInfo si in StudentInfoList)
{
if (SemesterScoreRecordDict.ContainsKey(si.StudentID))
{
foreach (JHSemesterScoreRecord semsRec in SemesterScoreRecordDict[si.StudentID])
{
string key = semsRec.SchoolYear + "_" + semsRec.Semester;
// 學期歷程有
if (si.SemsHistoryDict.ContainsKey(key))
{
foreach (string dname in semsRec.Domains.Keys)
{
if (tmpDomainNameList.Contains(dname))
{
if (semsRec.Domains[dname].Score.HasValue)
{
if (!si.DomainScoreInfoDict.ContainsKey(dname))
{
rptDomainScoreInfo ds = new rptDomainScoreInfo();
ds.StudentID = si.StudentID;
ds.Name = dname;
si.DomainScoreInfoDict.Add(dname, ds);
}
if (!si.DomainScoreInfoDict[dname].ScoreDict.ContainsKey(si.SemsHistoryDict[key]))
{
si.DomainScoreInfoDict[dname].ScoreDict.Add(si.SemsHistoryDict[key], semsRec.Domains[dname].Score.Value);
}
}
}
}
}
}
}
// 計算成績
si.CalcDomainScoreInfoList();
}
return(StudentInfoList);
}
private void CalcWorker_DoWork(object sender, DoWorkEventArgs e)
{
bool error = false;
List <StudentRecord> students = _students;
List <string> student_ids = new List <string>();
foreach (StudentRecord each in students)
{
student_ids.Add(each.ID);
}
double total = students.Count;
double count = 0;
#region 寫入學期歷程
if (_studentHistoryDict != null && _studentHistoryDict.Count > 0)
{
try
{
List <JHSchool.Data.JHSemesterHistoryRecord> list = new List <JHSchool.Data.JHSemesterHistoryRecord>();
int size = 50;
double count2 = 0;
double total2 = _studentHistoryDict.Count;
foreach (JHSchool.Data.JHSemesterHistoryRecord record in _studentHistoryDict.Values)
{
if (_calc_worker.CancellationPending)
{
return;
}
list.Add(record);
count2++;
if (list.Count == size)
{
JHSchool.Data.JHSemesterHistory.Update(list);
list.Clear();
_calc_worker.ReportProgress((int)(count2 * 100 / total2), "寫入學期歷程中…");
}
}
if (list.Count > 0)
{
JHSchool.Data.JHSemesterHistory.Update(list);
list.Clear();
_calc_worker.ReportProgress(100, "學期歷程寫入完成");
}
}
catch (Exception ex)
{
MsgBox.Show("寫入學期歷程失敗。" + ex.Message);
e.Result = null;
return;
}
}
#endregion
#region 計算課程成績
_viewer.Clear();
_viewer.SetHeader("課程");
_calc_worker.ReportProgress(0, "計算課程成績…");
if (_calc_worker.CancellationPending)
{
return;
}
_raw_data = new CourseDataLoader();
_raw_data.LoadCalculationData(this, _students, intSchoolYear.Value, intSemester.Value);
foreach (CW.Course course in _raw_data.Courses.Values)
{
if (string.IsNullOrEmpty(course.ExamTemplateId) && course.ExamRequired)
{
_viewer.SetMessage(course.CourseName, new List <string>(new string[] { "缺少評量設定" }));
error = true;
}
}
if (error)
{
e.Result = error;
return;
}
_viewer.Clear();
if (_calc_worker.CancellationPending)
{
return;
}
CourseScoreCalculate calculate = new CourseScoreCalculate(_raw_data.Courses);
calculate.Calculate();
foreach (CW.Course course in _raw_data.Courses.Values)
{
Dictionary <string, string> examLacks = new Dictionary <string, string>();
Dictionary <string, int> examScoreLacks = new Dictionary <string, int>();
Dictionary <string, int> examEffortLacks = new Dictionary <string, int>();
List <CW.SCAttend> scattends = new List <CW.SCAttend>();
List <CW.SCAttend> no_exam_scattends = new List <CW.SCAttend>();
foreach (CW.SCAttend scattend in course.SCAttends.Values)
{
if (student_ids.Contains(scattend.StudentIdentity))
{
if (scattend.ContainsLack)
{
scattends.Add(scattend);
}
if (scattend.NoExam)
{
no_exam_scattends.Add(scattend);
}
}
}
foreach (CW.SCAttend scattend in scattends)
{
foreach (string exam in scattend.ScoreLack)
{
if (!examLacks.ContainsKey(exam))
{
examLacks.Add(exam, "");
}
if (!examScoreLacks.ContainsKey(exam))
{
examScoreLacks.Add(exam, 0);
}
examScoreLacks[exam]++;
}
foreach (string exam in scattend.EffortLack)
{
if (!examLacks.ContainsKey(exam))
{
examLacks.Add(exam, "");
}
if (!examEffortLacks.ContainsKey(exam))
{
examEffortLacks.Add(exam, 0);
}
examEffortLacks[exam]++;
}
}
if (scattends.Count > 0)
{
List <string> msgs = new List <string>();
foreach (string exam in new List <string>(examLacks.Keys))
{
if (examScoreLacks.ContainsKey(exam))
{
examLacks[exam] += "有" + examScoreLacks[exam] + "位學生缺少分數評量,";
}
if (examEffortLacks.ContainsKey(exam))
{
examLacks[exam] += "有" + examEffortLacks[exam] + "位學生缺少努力程度,";
}
if (!string.IsNullOrEmpty(examLacks[exam]))
{
examLacks[exam] = examLacks[exam].Substring(0, examLacks[exam].Length - 1);
}
msgs.Add(exam + ":" + examLacks[exam]);
}
_viewer.SetMessage(course.CourseName, msgs);
error = true;
}
if (no_exam_scattends.Count > 0)
{
_viewer.SetMessage(course.CourseName, new List <string>(new string[] { "沒有設定各次評量" }));
error = true;
}
}
if (error)
{
e.Result = true;
return;
}
if (_calc_worker.CancellationPending)
{
return;
}
CourseScoreUpdater updater = new CourseScoreUpdater(_raw_data.Courses, this, false);
updater.UpdateToServer();
#endregion
#region 計算學期成績
//List<SemesterScoreRecordEditor> editors = new List<SemesterScoreRecordEditor>();
List <JHSemesterScoreRecord> semesterScoreRecordList = new List <JHSchool.Data.JHSemesterScoreRecord>();
JHSchool.Evaluation.SCAttend.Instance.SyncAllBackground();
Dictionary <string, List <JHSemesterScoreRecord> > studentSemesterScoreCache = new Dictionary <string, List <JHSchool.Data.JHSemesterScoreRecord> >();
foreach (JHSemesterScoreRecord record in JHSemesterScore.SelectByStudentIDs(students.AsKeyList()))
{
if (!studentSemesterScoreCache.ContainsKey(record.RefStudentID))
{
studentSemesterScoreCache.Add(record.RefStudentID, new List <JHSchool.Data.JHSemesterScoreRecord>());
}
studentSemesterScoreCache[record.RefStudentID].Add(record);
}
count = 0;
foreach (StudentRecord each in students)
{
count++;
ScoreCalcRuleRecord old = GetScoreCalcRuleRecord(each);
JHScoreCalcRuleRecord dalrecord = null;
if (old != null)
{
List <JHScoreCalcRuleRecord> list = JHScoreCalcRule.SelectByIDs(new string[] { old.ID });
if (list.Count > 0)
{
dalrecord = list[0];
}
}
ScoreCalculator calculator = new ScoreCalculator(dalrecord);
List <SCAttendRecord> scattends = new List <SCAttendRecord>();
foreach (SCAttendRecord scattend in JHSchool.Evaluation.SCAttend.Instance.GetStudentAttend(each.ID))
{
CourseRecord course = scattend.Course;
if (course.SchoolYear == intSchoolYear.Value &&
course.Semester == intSemester.Value &&
!string.IsNullOrEmpty(course.RefAssessmentSetupID) &&
course.CalculationFlag == "1"
)
{
scattends.Add(scattend);
}
}
if (scattends.Count > 0)
{
List <K12.Data.SubjectScore> subjectScores = _inner_calculator.CalculateSubjectScore(scattends);
foreach (K12.Data.SubjectScore subject in subjectScores)
{
subject.Score = calculator.ParseSubjectScore((decimal)subject.Score);
}
List <K12.Data.DomainScore> domainScores = _inner_calculator.CalculateDomainScore(subjectScores);
foreach (K12.Data.DomainScore domain in domainScores)
{
domain.Score = calculator.ParseDomainScore((decimal)domain.Score);
}
List <K12.Data.DomainScore> domainListWithoutElastic = new List <K12.Data.DomainScore>();
bool hasElasticCourse = false;
foreach (K12.Data.DomainScore domain in domainScores)
{
if (domain.Domain == "彈性課程")
{
hasElasticCourse = true;
}
else
{
domainListWithoutElastic.Add(domain);
}
}
decimal?learnDomainScore = calculator.ParseLearnDomainScore(_inner_calculator.CalculateTotalDomainScore(domainListWithoutElastic));
decimal?courseLearnScore = null;
if (hasElasticCourse)
{
courseLearnScore = calculator.ParseLearnDomainScore(_inner_calculator.CalculateTotalDomainScore(domainScores));
}
JHSemesterScoreRecord current = null;
if (studentSemesterScoreCache.ContainsKey(each.ID))
{
foreach (JHSemesterScoreRecord record in studentSemesterScoreCache[each.ID])
{
if (record.SchoolYear == intSchoolYear.Value && record.Semester == intSemester.Value)
{
current = record;
}
}
}
if (current != null)
{
//editor = current.GetEditor();
current.Subjects = new Dictionary <string, K12.Data.SubjectScore>();
current.Domains = new Dictionary <string, K12.Data.DomainScore>();
}
else
{
//current = new SemesterScoreRecordEditor(each, intSchoolYear.Value, intSemester.Value, _gradeYears[each.ID]);
current = new JHSchool.Data.JHSemesterScoreRecord();
current.RefStudentID = each.ID;
current.SchoolYear = intSchoolYear.Value;
current.Semester = intSemester.Value;
}
foreach (K12.Data.SubjectScore subject in subjectScores)
{
current.Subjects.Add(subject.Subject, subject);
}
foreach (K12.Data.DomainScore domain in domainScores)
{
current.Domains.Add(domain.Domain, domain);
}
current.LearnDomainScore = learnDomainScore;
current.CourseLearnScore = courseLearnScore;
//editors.Add(editor);
semesterScoreRecordList.Add(current);
}
_calc_worker.ReportProgress((int)((double)count * 100 / (double)total), "計算學期成績…");
}
e.Result = semesterScoreRecordList;
#endregion
}
private DataTable GetClassStudentDomainScore(ParameterRec data)
{
// string sql = string.Format(@"
//WITH data_row AS (
// SELECT
// {0}::INT AS school_year
// , {1}::INT AS semester
//), target_student AS(
// SELECT
// *
// FROM
// student
// WHERE
// ref_class_id IN ({2})
// AND status IN(1, 2)
//)
//SELECT
// student.id
// , student.name
// , student.seat_no
// , class.id AS class_id
// , class.class_name
// , sems_subj_score_ext.semester
// , sems_subj_score_ext.school_year
// , array_to_string(xpath('/Domain/@原始成績', subj_score_ele), '')::text AS 原始成績
// , array_to_string(xpath('/Domain/@成績', subj_score_ele), '')::text AS 成績
// , array_to_string(xpath('/Domain/@領域', subj_score_ele), '')::text AS 領域
// , array_to_string(xpath('/Domain/@權數', subj_score_ele), '')::text AS 權數
//FROM (
// SELECT
// sems_subj_score.*
// , unnest(xpath('/root/Domains/Domain', xmlparse(content '<root>' || score_info || '</root>'))) as subj_score_ele
// FROM
// sems_subj_score
// INNER JOIN target_student
// ON target_student.id = sems_subj_score.ref_student_id
// ) as sems_subj_score_ext
// LEFT OUTER JOIN student
// ON student.id = sems_subj_score_ext.ref_student_id
// LEFT OUTER JOIN class
// ON class.id = student.ref_class_id
// INNER JOIN data_row
// ON data_row.school_year = sems_subj_score_ext.school_year
// AND data_row.semester = sems_subj_score_ext.semester
//ORDER BY
// sems_subj_score_ext.grade_year
// , class.display_order
// , student.seat_no
// ", data.SchoolYear, data.Semester, data.ClassIDs);
//return qh.Select(sql);
// 取得班級學生含研修
QueryHelper qh1 = new QueryHelper();
string qry = @"SELECT student.id,student.name,student.seat_no,class.id AS class_id,class.class_name FROM student INNER JOIN class on student.ref_class_id = class.id WHERE student.status IN(1,2) AND class.id IN(" + data.ClassIDs + @") ORDER BY class.grade_year,class.display_order,class.class_name,student.seat_no";
DataTable dtStudent = qh1.Select(qry);
List <string> StudentIDList = new List <string>();
foreach (DataRow dr in dtStudent.Rows)
{
StudentIDList.Add(dr["id"].ToString());
}
// 取得學生學期成績，即時計算使用
List <JHSemesterScoreRecord> StudentSemesterScoreList = JHSemesterScore.SelectByStudentIDs(StudentIDList);
// 建立成績資料索引
Dictionary <string, List <JHSemesterScoreRecord> > StudentSemesterScoreDict = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (JHSemesterScoreRecord rec in StudentSemesterScoreList)
{
if (!StudentSemesterScoreDict.ContainsKey(rec.RefStudentID))
{
StudentSemesterScoreDict.Add(rec.RefStudentID, new List <JHSemesterScoreRecord>());
}
StudentSemesterScoreDict[rec.RefStudentID].Add(rec);
}
DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("name");
dt.Columns.Add("seat_no");
dt.Columns.Add("class_id");
dt.Columns.Add("class_name");
dt.Columns.Add("semester");
dt.Columns.Add("school_year");
dt.Columns.Add("原始成績");
dt.Columns.Add("成績");
dt.Columns.Add("領域");
dt.Columns.Add("權數");
// 各領域學期成績
Dictionary <string, List <decimal> > domainnScoreDict = new Dictionary <string, List <decimal> >();
// 各領域學期成績原始
Dictionary <string, List <decimal> > domainnScoreOrignDict = new Dictionary <string, List <decimal> >();
// 各領域成績(算術平均)
Dictionary <string, decimal> domainnScoreAvgDict = new Dictionary <string, decimal>();
// 各領域成績(算術平均)(原始)
Dictionary <string, decimal> domainnScoreAvgOrignDict = new Dictionary <string, decimal>();
// 各領域學分
Dictionary <string, List <decimal> > domainCreditDict = new Dictionary <string, List <decimal> >();
// 各領域學分平均
Dictionary <string, decimal> domainAvgCreditDict = new Dictionary <string, decimal>();
#region 取得學生成績計算規則
ScoreCalculator defaultScoreCalculator = new ScoreCalculator(null);
//key: ScoreCalcRuleID
calcCache.Clear();
//key: StudentID, val: ScoreCalcRuleID
calcIDCache.Clear();
List <string> scoreCalcRuleIDList = new List <string>();
List <StudentRecord> StudentRecList = K12.Data.Student.SelectByIDs(StudentIDList);
foreach (StudentRecord student in StudentRecList)
{
//calcCache.Add(student.ID, new ScoreCalculator(student.ScoreCalcRule));
string calcID = string.Empty;
if (!string.IsNullOrEmpty(student.OverrideScoreCalcRuleID))
{
calcID = student.OverrideScoreCalcRuleID;
}
else if (student.Class != null && !string.IsNullOrEmpty(student.Class.RefScoreCalcRuleID))
{
calcID = student.Class.RefScoreCalcRuleID;
}
if (!string.IsNullOrEmpty(calcID))
{
calcIDCache.Add(student.ID, calcID);
}
}
foreach (JHScoreCalcRuleRecord record in JHScoreCalcRule.SelectByIDs(calcIDCache.Values))
{
if (!calcCache.ContainsKey(record.ID))
{
calcCache.Add(record.ID, new ScoreCalculator(record));
}
}
#endregion
// 計算畢業成績使用
StudentSemsDomainScoreSumDict.Clear();
StudentSemsCreditSumDict.Clear();
// 開始填資料
foreach (DataRow dr in dtStudent.Rows)
{
string sid = dr["id"].ToString();
//DataRow newRow = dt.NewRow();
//newRow["id"] = sid;
//newRow["name"] = dr["name"].ToString();
//newRow["seat_no"] = dr["seat_no"].ToString();
//newRow["class_id"] = dr["class_id"].ToString();
//newRow["class_name"] = dr["class_name"].ToString();
//newRow["semester"] = "";
//newRow["school_year"] = "";
//newRow["原始成績"] = "0";
//newRow["成績"] = "0";
//newRow["領域"] = "0";
//newRow["權數"] = "0";
// 即時計算領域成績
if (StudentSemesterScoreDict.ContainsKey(sid))
{
// 成績計算規則
ScoreCalculator studentCalculator = defaultScoreCalculator;
if (calcIDCache.ContainsKey(sid) && calcCache.ContainsKey(calcIDCache[sid]))
{
studentCalculator = calcCache[calcIDCache[sid]];
}
domainnScoreDict.Clear();
domainnScoreOrignDict.Clear();
domainnScoreAvgDict.Clear();
domainnScoreAvgOrignDict.Clear();
domainCreditDict.Clear();
if (!StudentSemsDomainScoreSumDict.ContainsKey(sid))
{
StudentSemsDomainScoreSumDict.Add(sid, new Dictionary <string, decimal>());
}
if (!StudentSemsCreditSumDict.ContainsKey(sid))
{
StudentSemsCreditSumDict.Add(sid, new Dictionary <string, decimal>());
}
// 整理各學期領域成績
foreach (JHSemesterScoreRecord rec in StudentSemesterScoreDict[sid])
{
string key = rec.SchoolYear + "_" + rec.Semester;
if (!StudentSemsDomainScoreSumDict[sid].ContainsKey(key))
{
StudentSemsDomainScoreSumDict[sid].Add(key, 0);
}
if (!StudentSemsCreditSumDict[sid].ContainsKey(key))
{
StudentSemsCreditSumDict[sid].Add(key, 0);
}
// 讀取成績
foreach (string dName in rec.Domains.Keys)
{
// 加入領域成績
if (rec.Domains[dName].Score.HasValue)
{
if (!domainnScoreDict.ContainsKey(dName))
{
domainnScoreDict.Add(dName, new List <decimal>());
}
domainnScoreDict[dName].Add(rec.Domains[dName].Score.Value);
if (rec.Domains[dName].Credit.HasValue)
{
StudentSemsDomainScoreSumDict[sid][key] += (rec.Domains[dName].Score.Value * rec.Domains[dName].Credit.Value);
}
}
// 加入領域原始
if (rec.Domains[dName].ScoreMakeup.HasValue)
{
if (!domainnScoreOrignDict.ContainsKey(dName))
{
domainnScoreOrignDict.Add(dName, new List <decimal>());
}
domainnScoreOrignDict[dName].Add(rec.Domains[dName].ScoreMakeup.Value);
}
// 加入權數
if (rec.Domains[dName].Credit.HasValue)
{
if (!domainCreditDict.ContainsKey(dName))
{
domainCreditDict.Add(dName, new List <decimal>());
}
domainCreditDict[dName].Add(rec.Domains[dName].Credit.Value);
StudentSemsCreditSumDict[sid][key] += rec.Domains[dName].Credit.Value;
}
}
}
// 計算成績
foreach (string dName in domainnScoreDict.Keys)
{
// 使用畢業成績計算規則四捨五入方式，即時計算平均
decimal avgScore = studentCalculator.ParseGraduateScore(domainnScoreDict[dName].Average());
if (!domainnScoreAvgDict.ContainsKey(dName))
{
domainnScoreAvgDict.Add(dName, avgScore);
}
}
foreach (string dName in domainnScoreOrignDict.Keys)
{
// 使用畢業成績計算規則四捨五入方式，即時計算平均(原始)
decimal avgScore = studentCalculator.ParseGraduateScore(domainnScoreOrignDict[dName].Average());
if (!domainnScoreAvgOrignDict.ContainsKey(dName))
{
domainnScoreAvgOrignDict.Add(dName, avgScore);
}
}
// 處理學分
foreach (string dName in domainCreditDict.Keys)
{
// 使用畢業成績計算規則四捨五入方式，即時計算平均(原始)
decimal avgScore = studentCalculator.ParseGraduateScore(domainCreditDict[dName].Average());
if (!domainAvgCreditDict.ContainsKey(dName))
{
domainAvgCreditDict.Add(dName, avgScore);
}
}
foreach (string domainName in domainnScoreAvgDict.Keys)
{
// 填資料
DataRow newRow = dt.NewRow();
newRow["id"] = sid;
newRow["name"] = dr["name"].ToString();
newRow["seat_no"] = dr["seat_no"].ToString();
newRow["class_id"] = dr["class_id"].ToString();
newRow["class_name"] = dr["class_name"].ToString();
newRow["semester"] = "";
newRow["school_year"] = "";
newRow["原始成績"] = "";
if (domainnScoreAvgOrignDict.ContainsKey(domainName))
{
newRow["原始成績"] = domainnScoreAvgOrignDict[domainName];
}
newRow["成績"] = domainnScoreAvgDict[domainName];
newRow["領域"] = domainName;
newRow["權數"] = "";
if (domainAvgCreditDict.ContainsKey(domainName))
{
newRow["權數"] = domainAvgCreditDict[domainName];
}
dt.Rows.Add(newRow);
}
}
}
return(dt);
}
public DataCache(Options options)
{
var student_ids = from student in options.Students select student.ID;
#region 取得 AutoSummary
_autoSummaryCache = new Dictionary <string, AutoSummaryRecord>();
foreach (AutoSummaryRecord record in AutoSummary.Select(student_ids.ToList <string>(), null))
{
if (record.SchoolYear == options.SchoolYear &&
record.Semester == options.Semester)
{
if (!_autoSummaryCache.ContainsKey(record.RefStudentID))
{
_autoSummaryCache.Add(record.RefStudentID, record);
}
}
}
#endregion
#region 取得 SemesterScore
_semesterScoreCache = new Dictionary <string, JHSemesterScoreRecord>();
foreach (var record in JHSemesterScore.SelectByStudentIDs(student_ids.ToList <string>()))
{
if (record.SchoolYear == options.SchoolYear &&
record.Semester == options.Semester)
{
if (!_semesterScoreCache.ContainsKey(record.RefStudentID))
{
_semesterScoreCache.Add(record.RefStudentID, record);
}
}
}
#endregion
#region 取得 SemesterHistoryItem
_historyItemCache = new Dictionary <string, K12.Data.SemesterHistoryItem>();
foreach (var record in JHSemesterHistory.SelectByStudentIDs(student_ids))
{
foreach (var item in record.SemesterHistoryItems)
{
if (item.SchoolYear == options.SchoolYear &&
item.Semester == options.Semester)
{
if (!_historyItemCache.ContainsKey(item.RefStudentID))
{
_historyItemCache.Add(item.RefStudentID, item);
}
}
}
}
#endregion
if (Global.Params["Mode"] == "KaoHsiung")
{
#region 取得社團成績
_assnScoreCache = new Dictionary <string, AssnScore>();
FISCA.UDT.AccessHelper ah = new FISCA.UDT.AccessHelper();
string condition = string.Format("SchoolYear='{0}' and Semester='{1}'", options.SchoolYear, options.Semester);
List <AssnCode> list = ah.Select <AssnCode>(condition);
foreach (AssnCode record in list)
{
if (!_assnScoreCache.ContainsKey(record.StudentID))
{
XmlElement scores = K12.Data.XmlHelper.LoadXml(record.Scores);
XmlElement itemElement = (XmlElement)scores.SelectSingleNode("Item");
if (itemElement != null)
{
AssnScore assnScore = new AssnScore()
{
Score = itemElement.GetAttribute("Score"),
Effort = itemElement.GetAttribute("Effort"),
Text = itemElement.GetAttribute("Text")
};
_assnScoreCache.Add(record.StudentID, assnScore);
}
}
}
//<Content>
//<Item AssociationName="籃球社" Score="" Effort="" Text=""></Item>
//</Content>
#endregion
}
}
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 Worker_DoWork(object sender, DoWorkEventArgs e)
{
List <string> globalFieldName = new List <string>();
List <object> globalFieldValue = new List <object>();
globalFieldName.Add("學校名稱");
globalFieldValue.Add(School.ChineseName);
globalFieldName.Add("列印時間");
globalFieldValue.Add(Global.CDate(DateTime.Now.ToString("yyyy/MM/dd")) + " " + DateTime.Now.ToString("HH:mm:ss"));
ReportConfiguration _Dylanconfig = new ReportConfiguration(Global.OneFileSave);
OneFileSave = _Dylanconfig.GetBoolean("單檔儲存", false);
StudentDoc = new Dictionary <string, Document>();
List <JHPeriodMappingInfo> periodList = JHPeriodMapping.SelectAll();
List <JHAbsenceMappingInfo> absenceList = JHAbsenceMapping.SelectAll();
double total = Options.Students.Count;
double count = 0;
List <string> student_ids = new List <string>();
foreach (JHStudentRecord item in Options.Students)
{
student_ids.Add(item.ID);
}
DocumentBuilder templateBuilder = new DocumentBuilder(_template);
#region 變更節權數顯示
string pcDisplay = string.Empty;
bool printPeriod = Config.GetBoolean("列印節數", false);
bool printCredit = Config.GetBoolean("列印權數", false);
if (printPeriod && printCredit)
{
pcDisplay = "節/權數";
}
else if (printPeriod)
{
pcDisplay = "節數";
}
else if (printCredit)
{
pcDisplay = "權數";
}
while (templateBuilder.MoveToMergeField("節權數"))
{
templateBuilder.Write(pcDisplay);
}
#endregion
#region 快取資料
// 取得學生缺曠
Dictionary <string, List <AutoSummaryRecord> > autoSummaryCache = new Dictionary <string, List <AutoSummaryRecord> >();
foreach (AutoSummaryRecord record in AutoSummary.Select(student_ids, null))
{
if (!autoSummaryCache.ContainsKey(record.RefStudentID))
{
autoSummaryCache.Add(record.RefStudentID, new List <AutoSummaryRecord>());
}
autoSummaryCache[record.RefStudentID].Add(record);
}
// 取得學生異動
Dictionary <string, List <JHUpdateRecordRecord> > updateRecordCache = new Dictionary <string, List <JHUpdateRecordRecord> >();
foreach (var record in JHUpdateRecord.SelectByStudentIDs(student_ids))
{
if (!updateRecordCache.ContainsKey(record.StudentID))
{
updateRecordCache.Add(record.StudentID, new List <JHUpdateRecordRecord>());
}
updateRecordCache[record.StudentID].Add(record);
}
// 取得學生學期成績
Dictionary <string, List <JHSemesterScoreRecord> > semesterScoreCache = new Dictionary <string, List <JHSemesterScoreRecord> >();
foreach (var record in JHSemesterScore.SelectByStudentIDs(student_ids))
{
if (!semesterScoreCache.ContainsKey(record.RefStudentID))
{
semesterScoreCache.Add(record.RefStudentID, new List <JHSemesterScoreRecord>());
}
semesterScoreCache[record.RefStudentID].Add(record);
}
// 取得學生學期歷程
Dictionary <string, JHSemesterHistoryRecord> semesterHistoryCache = new Dictionary <string, JHSemesterHistoryRecord>();
foreach (var record in JHSemesterHistory.SelectByStudentIDs(student_ids))
{
if (!semesterHistoryCache.ContainsKey(record.RefStudentID))
{
semesterHistoryCache.Add(record.RefStudentID, record);
}
}
// 取得學生畢業成績
Dictionary <string, K12.Data.GradScoreRecord> StudGradScoreDic = new Dictionary <string, GradScoreRecord>();
foreach (GradScoreRecord score in GradScore.SelectByIDs <GradScoreRecord>(student_ids))
{
StudGradScoreDic.Add(score.RefStudentID, score);
}
#endregion
#region 判斷要列印的領域科目
Dictionary <string, bool> domains = new Dictionary <string, bool>();
string domainSubjectSetup = Config.GetString("領域科目設定", "Domain");
if (domainSubjectSetup == "Domain")
{
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
domains.Add(domain, true);
}
if (domains.ContainsKey("語文"))
{
domains["語文"] = false;
}
if (domains.ContainsKey("彈性課程"))
{
domains["彈性課程"] = false;
}
if (!domains.ContainsKey(""))
{
domains.Add("", false);
}
}
else if (domainSubjectSetup == "Subject")
{
foreach (var domain in JHSchool.Evaluation.Subject.Domains)
{
domains.Add(domain, false);
}
if (!domains.ContainsKey(""))
{
domains.Add("", false);
}
}
else
{
throw new Exception("請重新儲存列印設定");
}
//string domainSubjectSetup = Config.GetString("領域科目設定", "Domain");
//if (domainSubjectSetup == "Domain")
//{
// foreach (var domain in JHSchool.Evaluation.Subject.Domains)
// domains.Add(domain, DomainSubjectExpand.不展開);
// if (!domains.ContainsKey("")) domains.Add("", DomainSubjectExpand.展開);
//}
//else if (domainSubjectSetup == "Subject")
//{
// foreach (var domain in JHSchool.Evaluation.Subject.Domains)
// domains.Add(domain, DomainSubjectExpand.展開);
// if (!domains.ContainsKey("")) domains.Add("", DomainSubjectExpand.展開);
//}
//else
// throw new Exception("請重新儲存一次列印設定");
#endregion
#region 務學習時數
Global._SLRDict.Clear();
Global._SLRDict = Utility.GetServiceLearningDetail(student_ids);
#endregion
#region 產生
foreach (JHStudentRecord student in Options.Students)
{
count++;
DocumentBuilder builder = null;
Document each = (Document)_template.Clone(true);
#region 建立學期歷程對照
List <SemesterHistoryItem> semesterHistoryList = null;
if (semesterHistoryCache.ContainsKey(student.ID))
{
semesterHistoryList = semesterHistoryCache[student.ID].SemesterHistoryItems;
}
else
{
semesterHistoryList = new List <SemesterHistoryItem>();
}
SemesterMap map = new SemesterMap();
map.SetData(semesterHistoryList);
#endregion
#region Part1
builder = new DocumentBuilder(each);
StudentBasicInfo basic = new StudentBasicInfo();
basic.SetStudent(student, semesterHistoryList);
StudentSemesterHistory history = new StudentSemesterHistory();
history.SetData(semesterHistoryList);
each.MailMerge.MergeField += delegate(object sender1, MergeFieldEventArgs e1)
{
#region 處理照片
if (e1.FieldName == "照片")
{
byte[] photoBytes = null;
try
{
photoBytes = Convert.FromBase64String("" + e1.FieldValue);
}
catch (Exception ex)
{
e1.Field.Remove();
return;
}
if (photoBytes == null || photoBytes.Length == 0)
{
e1.Field.Remove();
return;
}
DocumentBuilder builder1 = new DocumentBuilder(e1.Document);
builder1.MoveToField(e1.Field, true);
e1.Field.Remove();
Shape photoShape = new Shape(e1.Document, ShapeType.Image);
photoShape.ImageData.SetImage(photoBytes);
photoShape.WrapType = WrapType.Inline;
#region AutoResize
double origHWRate = photoShape.ImageData.ImageSize.HeightPoints / photoShape.ImageData.ImageSize.WidthPoints;
double shapeHeight = (builder1.CurrentParagraph.ParentNode.ParentNode as Row).RowFormat.Height * 6;
double shapeWidth = (builder1.CurrentParagraph.ParentNode as Cell).CellFormat.Width;
if ((shapeHeight / shapeWidth) < origHWRate)
{
shapeWidth = shapeHeight / origHWRate;
}
else
{
shapeHeight = shapeWidth * origHWRate;
}
#endregion
photoShape.Height = shapeHeight;
photoShape.Width = shapeWidth;
builder1.InsertNode(photoShape);
}
#endregion
};
List <string> fieldName = new List <string>();
fieldName.AddRange(basic.GetFieldName());
fieldName.AddRange(history.GetFieldName());
List <string> fieldValue = new List <string>();
fieldValue.AddRange(basic.GetFieldValue());
fieldValue.AddRange(history.GetFieldValue());
each.MailMerge.Execute(fieldName.ToArray(), fieldValue.ToArray());
builder.MoveToMergeField("異動");
List <JHUpdateRecordRecord> updateRecordList = null;
if (updateRecordCache.ContainsKey(student.ID))
{
updateRecordList = updateRecordCache[student.ID];
}
else
{
updateRecordList = new List <JHUpdateRecordRecord>();
}
StudentUpdateRecordProcessor updateRecordProcessor = new StudentUpdateRecordProcessor(builder);
updateRecordProcessor.SetData(updateRecordList);
builder.MoveToMergeField("成績");
List <JHSemesterScoreRecord> semesterScoreList = null;
if (semesterScoreCache.ContainsKey(student.ID))
{
semesterScoreList = semesterScoreCache[student.ID];
}
else
{
semesterScoreList = new List <JHSemesterScoreRecord>();
}
// 學生畢業成績
K12.Data.GradScoreRecord studGradScore = new GradScoreRecord();
if (StudGradScoreDic.ContainsKey(student.ID))
{
studGradScore = StudGradScoreDic[student.ID];
}
StudentSemesterScoreProcessor semesterScoreProcessor = new StudentSemesterScoreProcessor(builder, map, domainSubjectSetup, domains, studGradScore);
semesterScoreProcessor.DegreeMapper = _degreeMapper;
semesterScoreProcessor.PrintPeriod = printPeriod;
semesterScoreProcessor.PrintCredit = printCredit;
semesterScoreProcessor.SetData(semesterScoreList);
#endregion
#region Part2
//builder = new DocumentBuilder(each);
builder.MoveToMergeField("領域文字描述");
if (Config.GetBoolean("列印文字評語", true))
{
StudentDomainTextProcessor domainTextProcessor = new StudentDomainTextProcessor(builder, map);
domainTextProcessor.SetData(semesterScoreList);
}
else
{
Section deleteSection = builder.CurrentSection;
each.Sections.Remove(deleteSection);
}
#endregion
#region Part3
//Document part3 = (Document)_template_part3.Clone(true);
//builder = new DocumentBuilder(each);
builder.MoveToMergeField("日常行為");
StudentMoralProcessor moralProcessor = new StudentMoralProcessor(builder, map, periodList, absenceList);
List <AutoSummaryRecord> autoSummaryList = null;
if (autoSummaryCache.ContainsKey(student.ID))
{
autoSummaryList = autoSummaryCache[student.ID];
}
else
{
autoSummaryList = new List <AutoSummaryRecord>();
}
moralProcessor.SetData(autoSummaryList);
// 處理多出來 table row
Cell cel = moralProcessor.GetCurrentCell();
Table table = cel.ParentRow.ParentTable;
for (int i = 47; i >= 20; i--)
{
bool rm = true;
foreach (Aspose.Words.Cell cell in table.Rows[i].Cells)
{
if (cell.GetText() != "\a")
{
rm = false;
break;
}
}
if (rm)
{
table.Rows[i].Remove();
}
}
#endregion
if (OneFileSave)
{
each.MailMerge.Execute(globalFieldName.ToArray(), globalFieldValue.ToArray());
string fileName = "";
fileName = student.StudentNumber;
fileName += "_" + student.IDNumber;
if (!string.IsNullOrEmpty(student.RefClassID))
{
fileName += "_" + student.Class.Name;
}
else
{
fileName += "_";
}
fileName += "_" + (student.SeatNo.HasValue ? student.SeatNo.Value.ToString() : "");
fileName += "_" + student.Name;
if (!StudentDoc.ContainsKey(fileName))
{
StudentDoc.Add(fileName, each);
}
}
else
{
foreach (Section sec in each.Sections)
{
_doc.Sections.Add(_doc.ImportNode(sec, true));
}
}
//回報進度
_worker.ReportProgress((int)(count * 100.0 / total));
}
if (!OneFileSave)
{
_doc.MailMerge.Execute(globalFieldName.ToArray(), globalFieldValue.ToArray());
}
#endregion
}
/// <summary>
/// (Network Access)將目前學期的資料填入到 SemesterScores 變數中。
/// </summary>
private void FillSemestersData()
{
SemesterScores = JHSemesterScore.SelectByStudentIDs(Students.ToKeys());
}
private void DataBuilding(object sender, DoWorkEventArgs e)
{
_BW.ReportProgress(0);
//取得結束時間 並轉成 像是 2018/05/10 格式
String endDate = dateTimeInput1.Value.ToString("yyyy/MM/dd");
SaveSetting();
//MappingData
_MappingData = new Dictionary <string, List <string> >();
foreach (DataGridViewRow row in dataGridViewX1.Rows)
{
if (row.Cells[0].Value != null)
{
string tagName = row.Cells[0].Value.ToString();
if (!_MappingData.ContainsKey(tagName))
{
_MappingData.Add(tagName, new List <string>());
}
if (row.Cells[1].Value != null)
{
string tagText = row.Cells[1].Value.ToString();
if (_Column2Items.ContainsKey(tagText))
{
string tagId = _Column2Items[tagText];
if (!_MappingData[tagName].Contains(tagId))
{
_MappingData[tagName].Add(tagId);
}
}
}
}
}
Dictionary <string, StudentObj> studentDic = new Dictionary <string, StudentObj>();
List <string> students = K12.Presentation.NLDPanels.Student.SelectedSource;
string ids = string.Join("','", students);
_BW.ReportProgress(10);
//基本資料
DataTable dt = _Q.Select("SELECT student.id,student.name,student.id_number,class.class_name,student.seat_no,student.student_number,student.birthdate,student.contact_phone,student.sms_phone,student.mailing_address,student.permanent_address,class.grade_year FROM student LEFT JOIN class ON ref_class_id = class.id WHERE student.id IN ('" + ids + "')");
foreach (DataRow row in dt.Rows)
{
StudentObj obj = new StudentObj(row);
if (!studentDic.ContainsKey(obj.Id))
{
studentDic.Add(obj.Id, obj);
}
}
_BW.ReportProgress(15);
//基本資料-TagId
dt = _Q.Select("SELECT ref_student_id,ref_tag_id FROM tag_student WHERE ref_student_id IN ('" + ids + "')");
foreach (DataRow row in dt.Rows)
{
string id = row["ref_student_id"].ToString();
string tagid = row["ref_tag_id"].ToString();
if (studentDic.ContainsKey(id))
{
if (!studentDic[id].TagIds.Contains(tagid))
{
studentDic[id].TagIds.Add(tagid);
}
}
}
_BW.ReportProgress(20);
//服務學習紀錄
dt = _Q.Select("SELECT ref_student_id,hours FROM $k12.service.learning.record WHERE ref_student_id IN ('" + ids + "') AND occur_date <= '" + endDate + "' ::timestamp");
foreach (DataRow row in dt.Rows)
{
string id = row["ref_student_id"].ToString();
if (studentDic.ContainsKey(id))
{
studentDic[id].ServiceHours += decimal.Parse(row["hours"].ToString());
}
}
_BW.ReportProgress(30);
//幹部紀錄
dt = _Q.Select("SELECT studentid,schoolyear,semester,cadrename FROM $behavior.thecadre WHERE studentid IN ('" + ids + "')");
List <string> checkList = new List <string>();
foreach (DataRow row in dt.Rows)
{
string id = row["studentid"].ToString();
string schoolyear = row["schoolyear"].ToString();
string semester = row["semester"].ToString();
string cadrename = row["cadrename"].ToString();
string key = id + "_" + schoolyear + "_" + semester;
if (!checkList.Contains(key))
{
if (studentDic.ContainsKey(id))
{
if (!cadrename.Contains("副"))
{
studentDic[id].CadreTimes++;
checkList.Add(key);
continue;
}
if (cadrename.Contains("副班") || cadrename.Contains("副社"))
{
studentDic[id].CadreTimes++;
checkList.Add(key);
continue;
}
}
}
}
_BW.ReportProgress(40);
////獎懲紀錄
//List<AutoSummaryRecord> records = AutoSummary.Select(students, null);
//foreach (AutoSummaryRecord record in records)
//{
// string id = record.RefStudentID;
// if (studentDic.ContainsKey(id))
// {
// studentDic[id].MeritA += record.MeritA;
// studentDic[id].MeritB += record.MeritB;
// studentDic[id].MeritC += record.MeritC;
// studentDic[id].DemeritA += record.DemeritA;
// studentDic[id].DemeritB += record.DemeritB;
// studentDic[id].DemeritC += record.DemeritC;
// }
//}
// 2018/5/15 穎驊新增，自羿均那邊拿到他調整好 高中職免試入學抓取資料的SQL
// 提出其中 獎懲的部分稍作調整，作為新的五專免試入學學生獎懲資料抓取方式
// 其最大的特色是，可以設定截止時間、過濾銷過紀錄、自動加總非明細資料(轉學生適用)、
// 且無論該學期有無學習歷程，只要有計獎懲一律計算，不會因為該學期休學而不計算
List <string> sidList = new List <string>();
foreach (string sid in studentDic.Keys)
{
sidList.Add("SELECT " + sid + "::BIGINT AS id ");
}
string target_student_s = String.Join(" UNION ALL ", sidList);
string sql = string.Format(@"WITH target_datetime AS(
SELECT
'{0}'::TIMESTAMP AS end_date
) ,target_student AS(
{1}
) ,target_sems_demerit AS(
SELECT
target_student.id
,CASE
WHEN SUM(大過) IS NULL
THEN 0
ELSE SUM(大過)
END AS 大過支數
,CASE
WHEN SUM(小過) IS NULL
THEN 0
ELSE SUM(小過)
END AS 小過支數
,CASE
WHEN SUM(警告) IS NULL
THEN 0
ELSE SUM(警告)
END AS 警告支數
FROM
target_student
LEFT OUTER JOIN (
SELECT
sems_moral_score.ref_student_id
, CAST( regexp_replace( xpath_string(sems_moral_score.initial_summary,'/InitialSummary/DisciplineStatistics/Demerit/@A'), '^$', '0') AS INTEGER) AS 大過
, CAST( regexp_replace( xpath_string(sems_moral_score.initial_summary,'/InitialSummary/DisciplineStatistics/Demerit/@B'), '^$', '0') AS INTEGER) AS 小過
, CAST( regexp_replace( xpath_string(sems_moral_score.initial_summary,'/InitialSummary/DisciplineStatistics/Demerit/@C'), '^$', '0') AS INTEGER) AS 警告
FROM
sems_moral_score
) AS sems_demerit ON target_student.id = sems_demerit.ref_student_id
GROUP BY
target_student.id
) ,target_demerit AS(
SELECT
target_student.id
,CASE
WHEN SUM(大過) IS NULL
THEN 0
ELSE SUM(大過)
END AS 大過支數
,CASE
WHEN SUM(小過) IS NULL
THEN 0
ELSE SUM(小過)
END AS 小過支數
,CASE
WHEN SUM(警告) IS NULL
THEN 0
ELSE SUM(警告)
END AS 警告支數
FROM
target_student
LEFT OUTER JOIN(
SELECT
discipline.ref_student_id
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Demerit/@A'), '^$', '0') AS INTEGER) AS 大過
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Demerit/@B'), '^$', '0') AS INTEGER) AS 小過
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Demerit/@C'), '^$', '0') AS INTEGER) AS 警告
FROM
target_datetime
LEFT OUTER JOIN discipline
ON discipline.occur_date <= target_datetime.end_date
WHERE
merit_flag = 0
AND xpath_string(discipline.detail,'/Discipline/Demerit/@Cleared') <> '是'
AND ref_student_id IN(SELECT * FROM target_student)
UNION ALL
SELECT
discipline.ref_student_id
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Demerit/@A'), '^$', '0') AS INTEGER) AS 大過
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Demerit/@B'), '^$', '0') AS INTEGER) AS 小過
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Demerit/@C'), '^$', '0') AS INTEGER) AS 警告
FROM
target_datetime
LEFT OUTER JOIN(
SELECT
*
, CASE
WHEN xpath_string(discipline.detail,'/Discipline/Demerit/@ClearDate') = ''
THEN '1970/1/1'::TIMESTAMP
ELSE xpath_string(discipline.detail,'/Discipline/Demerit/@ClearDate')::TIMESTAMP
END AS cleardate
FROM
discipline
) discipline ON discipline.occur_date <= target_datetime.end_date
WHERE
merit_flag = 0
AND xpath_string(discipline.detail,'/Discipline/Demerit/@Cleared') = '是'
AND discipline.cleardate > (SELECT end_date FROM target_datetime)
AND ref_student_id IN(SELECT id FROM target_student)
) AS target_discipline ON target_student.id = target_discipline.ref_student_id
GROUP BY target_student.id
) ,total_demerit AS(
SELECT
total.id
, CASE WHEN SUM(大過支數) IS NULL THEN 0 ELSE SUM(大過支數) END AS 大過支數
, CASE WHEN SUM(小過支數) IS NULL THEN 0 ELSE SUM(小過支數) END AS 小過支數
, CASE WHEN SUM(警告支數) IS NULL THEN 0 ELSE SUM(警告支數) END AS 警告支數
FROM(
SELECT * FROM target_demerit
UNION ALL
SELECT * FROM target_sems_demerit
) AS total
GROUP BY
total.id
) ,target_sems_merit AS(
SELECT
target_student.id
,CASE
WHEN SUM(大功) IS NULL
THEN 0
ELSE SUM(大功)
END AS 大功支數
,CASE
WHEN SUM(小功) IS NULL
THEN 0
ELSE SUM(小功)
END AS 小功支數
,CASE
WHEN SUM(嘉獎) IS NULL
THEN 0
ELSE SUM(嘉獎)
END AS 嘉獎支數
FROM
target_student
LEFT OUTER JOIN (
SELECT
sems_moral_score.ref_student_id
, CAST( regexp_replace( xpath_string(sems_moral_score.initial_summary,'/InitialSummary/DisciplineStatistics/Merit/@A'), '^$', '0') AS INTEGER) AS 大功
, CAST( regexp_replace( xpath_string(sems_moral_score.initial_summary,'/InitialSummary/DisciplineStatistics/Merit/@B'), '^$', '0') AS INTEGER) AS 小功
, CAST( regexp_replace( xpath_string(sems_moral_score.initial_summary,'/InitialSummary/DisciplineStatistics/Merit/@C'), '^$', '0') AS INTEGER) AS 嘉獎
FROM
sems_moral_score
) AS sems_merit ON target_student.id = sems_merit.ref_student_id
GROUP BY
target_student.id
) ,target_merit AS(
SELECT
ref_student_id AS id
,CASE
WHEN SUM(大功) IS NULL
THEN 0
ELSE SUM(大功)
END AS 大功支數
,CASE
WHEN SUM(小功) IS NULL
THEN 0
ELSE SUM(小功)
END AS 小功支數
,CASE
WHEN SUM(嘉獎) IS NULL
THEN 0
ELSE SUM(嘉獎)
END AS 嘉獎支數
FROM(
SELECT
discipline.ref_student_id
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Merit/@A'), '^$', '0') AS INTEGER) AS 大功
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Merit/@B'), '^$', '0') AS INTEGER) AS 小功
, CAST( regexp_replace( xpath_string(discipline.detail,'/Discipline/Merit/@C'), '^$', '0') AS INTEGER) AS 嘉獎
FROM
target_datetime
LEFT OUTER JOIN discipline
ON discipline.occur_date <= target_datetime.end_date
WHERE
merit_flag = 1
AND ref_student_id IN(SELECT * FROM target_student)
) AS target_discipline
GROUP BY ref_student_id
) ,total_merit AS (
SELECT
total.id
, CASE WHEN SUM(大功支數) IS NULL THEN 0 ELSE SUM(大功支數) END AS 大功支數
, CASE WHEN SUM(小功支數) IS NULL THEN 0 ELSE SUM(小功支數) END AS 小功支數
, CASE WHEN SUM(嘉獎支數) IS NULL THEN 0 ELSE SUM(嘉獎支數) END AS 嘉獎支數
FROM(
SELECT * FROM target_merit
UNION ALL
SELECT * FROM target_sems_merit
) AS total
GROUP BY
total.id
)
SELECT
target_student.id
,CASE WHEN total_merit.大功支數 is null THEN 0 ELSE total_merit.大功支數 END as 大功支數
,CASE WHEN total_merit.小功支數 is null THEN 0 ELSE total_merit.小功支數 END as 小功支數
,CASE WHEN total_merit.嘉獎支數 is null THEN 0 ELSE total_merit.嘉獎支數 END as 嘉獎支數
,CASE WHEN total_demerit.大過支數 is null THEN 0 ELSE total_demerit.大過支數 END as 大過支數
,CASE WHEN total_demerit.小過支數 is null THEN 0 ELSE total_demerit.小過支數 END as 小過支數
,CASE WHEN total_demerit.警告支數 is null THEN 0 ELSE total_demerit.警告支數 END as 警告支數
FROM
target_student
LEFT OUTER JOIN total_demerit
ON total_demerit.id = target_student.id
LEFT OUTER JOIN total_merit
ON total_merit.id = target_student.id
" , endDate, target_student_s);
QueryHelper qh = new QueryHelper();
DataTable dt_discipline = qh.Select(sql);
foreach (DataRow row in dt_discipline.Rows)
{
string id = "" + row["id"];
if (studentDic.ContainsKey(id))
{
studentDic[id].MeritA = int.Parse("" + row["大功支數"]);
studentDic[id].MeritB = int.Parse("" + row["小功支數"]);
studentDic[id].MeritC = int.Parse("" + row["嘉獎支數"]);
studentDic[id].DemeritA = int.Parse("" + row["大過支數"]);
studentDic[id].DemeritB = int.Parse("" + row["小過支數"]);
studentDic[id].DemeritC = int.Parse("" + row["警告支數"]);
}
}
_BW.ReportProgress(45);
_BW.ReportProgress(50);
//獎懲紀錄功過相抵
foreach (StudentObj obj in studentDic.Values)
{
obj.MeritDemeritTransfer();
}
_BW.ReportProgress(60);
//體適能
//先確認UDT存在
dt = _Q.Select("SELECT name FROM _udt_table where name='ischool_student_fitness'");
if (dt.Rows.Count > 0)
{
dt = _Q.Select("SELECT ref_student_id,sit_and_reach_degree, standing_long_jump_degree, sit_up_degree, cardiorespiratory_degree FROM $ischool_student_fitness WHERE ref_student_id IN ('" + ids + "')");
foreach (DataRow row in dt.Rows)
{
string id = row["ref_student_id"].ToString();
if (studentDic.ContainsKey(id))
{
//擇優判斷
if (GetScore(row) > studentDic[id].SportFitnessScore)
{
studentDic[id].sit_and_reach_degree = row["sit_and_reach_degree"].ToString();
studentDic[id].sit_up_degree = row["sit_up_degree"].ToString();
studentDic[id].standing_long_jump_degree = row["standing_long_jump_degree"].ToString();
studentDic[id].cardiorespiratory_degree = row["cardiorespiratory_degree"].ToString();
}
}
}
}
_BW.ReportProgress(65);
//均衡學習-年級對照
Dictionary <string, Dictionary <string, string> > SchoolyearSemesteerToGrade = new Dictionary <string, Dictionary <string, string> >();
foreach (JHSemesterHistoryRecord record in JHSemesterHistory.SelectByStudentIDs(students))
{
foreach (SemesterHistoryItem item in record.SemesterHistoryItems)
{
if (!SchoolyearSemesteerToGrade.ContainsKey(item.RefStudentID))
{
SchoolyearSemesteerToGrade.Add(item.RefStudentID, new Dictionary <string, string>());
}
string key = item.SchoolYear + "_" + item.Semester;
if (!SchoolyearSemesteerToGrade[item.RefStudentID].ContainsKey(key))
{
if (item.Semester == 1)
{
SchoolyearSemesteerToGrade[item.RefStudentID].Add(key, item.GradeYear + "上");
}
else if (item.Semester == 2)
{
SchoolyearSemesteerToGrade[item.RefStudentID].Add(key, item.GradeYear + "下");
}
}
}
}
_BW.ReportProgress(70);
//均衡學習-領域分數
List <JHSemesterScoreRecord> recs = JHSemesterScore.SelectByStudentIDs(students);
foreach (JHSemesterScoreRecord rec in recs)
{
foreach (DomainScore score in rec.Domains.Values)
{
string id = score.RefStudentID;
string key = score.SchoolYear + "_" + score.Semester;
string grade = "";
if (SchoolyearSemesteerToGrade.ContainsKey(id))
{
if (SchoolyearSemesteerToGrade[id].ContainsKey(key))
{
grade = SchoolyearSemesteerToGrade[id][key];
}
}
if (studentDic.ContainsKey(id))
{
string domain = score.Domain;
if ((domain == "健康與體育" || domain == "藝術與人文" || domain == "綜合活動") && !string.IsNullOrWhiteSpace(grade))
{
if (!studentDic[id].DomainScores.ContainsKey(domain))
{
studentDic[id].DomainScores.Add(domain, new Dictionary <string, decimal>());
}
if (!studentDic[id].DomainScores[domain].ContainsKey(grade))
{
decimal value = score.Score.HasValue ? score.Score.Value : 0;
studentDic[id].DomainScores[domain].Add(grade, value);
}
}
}
}
}
_BW.ReportProgress(80);
//排序
List <StudentObj> list = studentDic.Values.ToList();
list.Sort(SortStudent);
int progress = 80;
decimal per = (decimal)(100 - progress) / studentDic.Count;
int count = 0;
//Objects轉Table
Dictionary <string, int> CloumnIndex = new Dictionary <string, int>();
CloumnIndex.Add("身分證字統一編號", 0);
CloumnIndex.Add("學生姓名", 1);
CloumnIndex.Add("出生年(民國年)", 2);
CloumnIndex.Add("出生月", 3);
CloumnIndex.Add("出生日", 4);
CloumnIndex.Add("年級", 5);
CloumnIndex.Add("班級", 6);
CloumnIndex.Add("座號", 7);
CloumnIndex.Add("報名資格", 8);
CloumnIndex.Add("郵遞區號", 9);
CloumnIndex.Add("地址", 10);
CloumnIndex.Add("市內電話", 11);
CloumnIndex.Add("行動電話", 12);
CloumnIndex.Add("特種生加分類別", 13);
CloumnIndex.Add("報名費減免身分", 14);
CloumnIndex.Add("競賽", 15);
CloumnIndex.Add("擔任幹部", 16);
CloumnIndex.Add("服務時數", 17);
CloumnIndex.Add("服務學習", 18);
CloumnIndex.Add("累計嘉獎", 19);
CloumnIndex.Add("累計小功", 20);
CloumnIndex.Add("累計大功", 21);
CloumnIndex.Add("累計警告", 22);
CloumnIndex.Add("累計小過", 23);
CloumnIndex.Add("累計大過", 24);
CloumnIndex.Add("日常生活表現評量", 25);
CloumnIndex.Add("肌耐力", 26);
CloumnIndex.Add("柔軟度", 27);
CloumnIndex.Add("瞬發力", 28);
CloumnIndex.Add("心肺耐力", 29);
CloumnIndex.Add("體適能", 30);
CloumnIndex.Add("多元學習表現", 31);
CloumnIndex.Add("技藝教育成績", 32);
CloumnIndex.Add("技藝優良", 33);
CloumnIndex.Add("弱勢身分", 34);
CloumnIndex.Add("弱勢積分", 35);
CloumnIndex.Add("健康與體育", 36);
CloumnIndex.Add("藝術與人文", 37);
CloumnIndex.Add("綜合活動", 38);
CloumnIndex.Add("均衡學習", 39);
CloumnIndex.Add("家長意見", 40);
CloumnIndex.Add("導師意見", 41);
CloumnIndex.Add("輔導教師意見", 42);
CloumnIndex.Add("適性輔導", 43);
CloumnIndex.Add("其他比序項目_全民英檢", 44);
CloumnIndex.Add("合計", 45);
CloumnIndex.Add("報名「北區」五專學校代碼", 46);
CloumnIndex.Add("報名「中區」五專學校代碼", 47);
CloumnIndex.Add("報名「南區」五專學校代碼", 48);
CloumnIndex.Add("競賽名稱", 49);
//CloumnIndex.Add("其他比序項目_多益測驗", 50);
int index = 1;
Workbook wb = new Workbook(new MemoryStream(Properties.Resources.Template));
Cells cs = wb.Worksheets[0].Cells;
foreach (StudentObj obj in list)
{
cs[index, CloumnIndex["身分證字統一編號"]].PutValue(obj.IdNumber);
cs[index, CloumnIndex["學生姓名"]].PutValue(obj.Name);
cs[index, CloumnIndex["出生年(民國年)"]].PutValue(obj.Birth_Year.ToString().PadLeft(3, '0'));
cs[index, CloumnIndex["出生月"]].PutValue(obj.Birth_Month.ToString().PadLeft(2, '0'));
cs[index, CloumnIndex["出生日"]].PutValue(obj.Birth_Day.ToString().PadLeft(2, '0'));
cs[index, CloumnIndex["年級"]].PutValue(obj.GradeYear);
cs[index, CloumnIndex["班級"]].PutValue(obj.ClassName);
cs[index, CloumnIndex["座號"]].PutValue(obj.SeatNo);
cs[index, CloumnIndex["報名資格"]].PutValue(CheckTagId(obj.TagIds, 報名資格));
cs[index, CloumnIndex["郵遞區號"]].PutValue(obj.ZipCode);
cs[index, CloumnIndex["地址"]].PutValue(obj.Address);
cs[index, CloumnIndex["市內電話"]].PutValue(obj.Contact_Phone);
cs[index, CloumnIndex["行動電話"]].PutValue(obj.SMS_Phone);
cs[index, CloumnIndex["特種生加分類別"]].PutValue(CheckTagId(obj.TagIds, 特種生加分類別));
cs[index, CloumnIndex["報名費減免身分"]].PutValue(CheckTagId(obj.TagIds, 報名費減免身分));
cs[index, CloumnIndex["擔任幹部"]].PutValue(obj.CadreTimesScore);
cs[index, CloumnIndex["服務時數"]].PutValue(obj.ServiceHours);
cs[index, CloumnIndex["服務學習"]].PutValue(obj.ServiceLearningScore);
cs[index, CloumnIndex["累計嘉獎"]].PutValue(obj.MeritC);
cs[index, CloumnIndex["累計小功"]].PutValue(obj.MeritB);
cs[index, CloumnIndex["累計大功"]].PutValue(obj.MeritA);
cs[index, CloumnIndex["累計警告"]].PutValue(obj.DemeritC);
cs[index, CloumnIndex["累計小過"]].PutValue(obj.DemeritB);
cs[index, CloumnIndex["累計大過"]].PutValue(obj.DemeritA);
cs[index, CloumnIndex["日常生活表現評量"]].PutValue(obj.MeritDemeritScore);
cs[index, CloumnIndex["肌耐力"]].PutValue(obj.CheckScore("仰臥起坐"));
cs[index, CloumnIndex["柔軟度"]].PutValue(obj.CheckScore("坐姿體前彎"));
cs[index, CloumnIndex["瞬發力"]].PutValue(obj.CheckScore("立定跳遠"));
cs[index, CloumnIndex["心肺耐力"]].PutValue(obj.CheckScore("心肺適能"));
cs[index, CloumnIndex["體適能"]].PutValue(obj.SportFitnessScore);
int x = index + 1;
string formula = "=IF(P" + x + "+S" + x + "+Z" + x + "+AE" + x + ">16,16,P" + x + "+S" + x + "+Z" + x + "+AE" + x + ")";
cs[index, CloumnIndex["多元學習表現"]].Formula = formula;
string[] tag = CheckTagId(obj.TagIds);
cs[index, CloumnIndex["弱勢身分"]].PutValue(tag[0]);
cs[index, CloumnIndex["弱勢積分"]].PutValue(tag[1]);
Dictionary <string, decimal> dic = obj.GetDomainScores();
cs[index, CloumnIndex["健康與體育"]].PutValue(dic.ContainsKey("健康與體育") ? dic["健康與體育"] : 0);
cs[index, CloumnIndex["藝術與人文"]].PutValue(dic.ContainsKey("藝術與人文") ? dic["藝術與人文"] : 0);
cs[index, CloumnIndex["綜合活動"]].PutValue(dic.ContainsKey("綜合活動") ? dic["綜合活動"] : 0);
cs[index, CloumnIndex["均衡學習"]].PutValue(obj.DomainItemScore);
cs[index, CloumnIndex["其他比序項目_全民英檢"]].PutValue(CheckTagId(obj.TagIds, 其他比序項目_全民英檢));
//cs[index, CloumnIndex["其他比序項目_多益測驗"]].PutValue(CheckTagId(obj.TagIds, 其他比序項目_多益測驗));
formula = "=IF(AF" + x + "+AH" + x + "+AJ" + x + "+AN" + x + "+AR" + x + ">30,30,AF" + x + "+AH" + x + "+AJ" + x + "+AN" + x + "+AR" + x + ")";
cs[index, CloumnIndex["合計"]].Formula = formula;
index++;
count++;
progress += (int)(count * per);
_BW.ReportProgress(progress);
}
//wb.Worksheets[0].AutoFitColumns();
e.Result = wb;
}
