public Report()
{
_degreeMapper = new JHSchool.Evaluation.Mapping.DegreeMapper();
InitializeTemplate();
InitializeBackgroundWorker();
}
public ScoreWriter()
{
_config = new ReportConfiguration(Global.ReportName);
_printPeriod = _config.GetBoolean("列印節數", true);
_printCredit = _config.GetBoolean("列印權數", false);
_printLearnDomain = _config.GetBoolean("列印學習領域總成績", true);
_domainsWithoutDetail = new List <string>();
_degreeMapper = new DegreeMapper();
_warned = false;
_domainText = new Dictionary <string, string>();
string printScore = _config.GetString("領域科目設定", "Domain");
if (printScore == "Domain")
{
List <string> list = Global.GetDomainList();
if (Global.Params["Mode"] == "HsinChu") //新竹市，語文跟彈性科目分列
{
if (list.Contains("語文"))
{
list.Remove("語文");
}
}
if (list.Contains("彈性課程"))
{
list.Remove("彈性課程");
}
_domainsWithoutDetail.AddRange(list);
//_domainsWithoutDetail.AddRange(new string[] { "數學", "社會", "藝術與人文", "自然與生活科技", "健康與體育", "綜合活動" });
}
}
public Report()
{
_degreeMapper = new JHSchool.Evaluation.Mapping.DegreeMapper();
InitializeTemplate();
InitializeBackgroundWorker();
}
public Report(Options _options)
{
Options = _options;
Config = new ReportConfiguration(Global.ReportName);
_degreeMapper = new DegreeMapper();
InitializeTemplate();
InitializeBackgroundWorker();
}
private string GetDegree(string p)
{
decimal d;
if (decimal.TryParse(p, out d))
{
return(DegreeMapper.GetDegreeByScore(d));
}
else
{
return(p);
}
}
/// <summary>
/// 物件初始化
/// </summary>
private static void Initialize()
{
if (_A == null)
{
_A = new AccessHelper();
}
if (_Q == null)
{
_Q = new QueryHelper();
}
if (_degreeMapper == null)
{
_degreeMapper = new DegreeMapper();
}
}
/// <summary>
/// 寫入領域 Rows
/// </summary>
private void FillDomainRows()
{
Cell cell = _cell;
Row row = _cell.ParentRow;
Table table = row.ParentTable;
int deleteIndex = table.IndexOf(row);
int rowIndex = table.IndexOf(row);
int SubjCount = 0;
foreach (DomainRow domainRow in _manager.DomainRows)
{
SubjCount = 0;
// 透過對照表查詢領域是否需要展開
bool display = DomainSubjectExpand.展開;
if (_domains.ContainsKey(domainRow.Domain))
{
display = _domains[domainRow.Domain];
}
// 不展開，列印領域
if (display == DomainSubjectExpand.展開)
{
if (domainRow.Scores.Count <= 0)
{
continue;
}
table.Rows.Add(row.Clone(true));
Row tempRow = table.LastRow;
WordHelper.MergeHorizontalCell(tempRow.Cells[0], 2);
WordHelper.Write(tempRow.Cells[0], (string.IsNullOrEmpty(domainRow.Domain) ? "彈性課程" : domainRow.Domain), _builder);
WriteDomain(tempRow.Cells[1], domainRow);
}
// 展開，列印科目
else
{
int subjectCount = 0;
foreach (string subject in domainRow.SubjectScores.Keys)
{
subjectCount++;
table.Rows.Add(row.Clone(true));
Row tempRow = table.LastRow;
WordHelper.Write(tempRow.Cells[1], subject, _builder);
WriteSubject(tempRow.Cells[1], domainRow.SubjectScores[subject]);
}
SubjCount = subjectCount;
// 當只有領域成績沒有科目成績時
if (subjectCount > 0)
{
Row startRow = table.Rows[table.Rows.Count - subjectCount];
WordHelper.Write(startRow.Cells[0], (string.IsNullOrEmpty(domainRow.Domain) ? "彈性課程" : domainRow.Domain), _builder);
if (subjectCount > 1)
{
WordHelper.MergeVerticalCell(startRow.Cells[0], subjectCount);
}
}
}
// 畢業成績
if (_StudGradScore.Domains.ContainsKey(domainRow.Domain))
{
Row tmpRow = table.Rows[table.Rows.Count - SubjCount];
// 當科目數 >=1
if (SubjCount >= 1)
{
WordHelper.MergeVerticalCell(tmpRow.Cells[20], SubjCount);
WordHelper.MergeVerticalCell(tmpRow.Cells[21], SubjCount);
}
else
{
tmpRow = table.LastRow;
}
//Row tmpRow = table.LastRow;
if (_StudGradScore.Domains[domainRow.Domain].Score.HasValue)
{
// 分數
WordHelper.Write(tmpRow.Cells[20], _StudGradScore.Domains[domainRow.Domain].Score.Value.ToString(), _builder);
// 等第
WordHelper.Write(tmpRow.Cells[21], DegreeMapper.GetDegreeByScore(_StudGradScore.Domains[domainRow.Domain].Score.Value), _builder);
}
}
}
table.Rows[deleteIndex].Remove();
// 畫底出線
foreach (Cell c in table.LastRow.Cells)
{
c.CellFormat.Borders.Bottom.LineWidth = 1.5;
}
//bool hasScore = false;
//Cell cell = _cell;
//Cell gradCell1 = GetMoveRightCell(cell, GraduateShift);
//Cell gradCell2 = GetMoveRightCell(cell, GraduateShift + 1);
//int first_row_index = _cell.ParentRow.ParentTable.IndexOf(_cell.ParentRow);
//foreach (DomainRow row in _manager.DomainRows)
//{
// // 透過對照表查詢領域是否需要展開
// bool display = DomainSubjectExpand.展開;
// if (_domains.ContainsKey(row.Domain))
// display = _domains[row.Domain];
// hasScore = true;
// //int count = row.Count;
// Write(cell, string.IsNullOrEmpty(row.Domain) ? "彈性課程" : row.Domain);
// // 不展開，列印領域
// if (display == DomainSubjectExpand.不展開)
// {
// MergeVerticalCell(cell, count);
// MergeVerticalCell(gradCell1, count);
// MergeVerticalCell(gradCell2, count);
// }
// // 展開，列印科目
// else
// {
// Cell subjectCell = GetMoveRightCell(cell, 1);
// foreach (SubjectRow subjectRow in row.SubjectRows)
// {
// WriteSubject(subjectCell, subjectRow);
// subjectCell = GetMoveDownCell(subjectCell, 1);
// if (subjectCell == null) break;
// }
// }
// cell = GetMoveDownCell(cell, count);
// gradCell1 = GetMoveDownCell(gradCell1, count);
// gradCell2 = GetMoveDownCell(gradCell2, count);
// if (cell == null) break;
//}
//if (hasScore)
//{
// do
// {
// cell.CellFormat.VerticalMerge = CellMerge.Previous;
// cell = GetMoveDownCell(cell, 1);
// gradCell1.CellFormat.VerticalMerge = CellMerge.Previous;
// gradCell1 = GetMoveDownCell(gradCell1, 1);
// gradCell2.CellFormat.VerticalMerge = CellMerge.Previous;
// gradCell2 = GetMoveDownCell(gradCell2, 1);
// }
// while (cell != null);
//}
}
private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
// 處理等第
DegreeMapper dm = new DegreeMapper();
_worker.ReportProgress(0, "開始列印 ESL報表...");
#region 取得課程成績單 設定樣板
_eslCouseList = new List <K12.Data.CourseRecord>();
_eslCouseList = K12.Data.Course.SelectByIDs(_courseIDList);
_refAssessmentSetupIDList = new List <string>();
foreach (K12.Data.CourseRecord courseRecord in _eslCouseList)
{
if (!_refAssessmentSetupIDList.Contains("'" + courseRecord.RefAssessmentSetupID + "'"))
{
_refAssessmentSetupIDList.Add("'" + courseRecord.RefAssessmentSetupID + "'");
}
if (!_courseIDPairDict.ContainsKey(courseRecord.ID))
{
_courseIDPairDict.Add(courseRecord.ID, courseRecord.RefAssessmentSetupID);
}
if (!_assessmentSetupIDPairDict.ContainsKey(courseRecord.RefAssessmentSetupID))
{
_assessmentSetupIDPairDict.Add(courseRecord.RefAssessmentSetupID, courseRecord.ID);
}
}
string assessmentSetupIDs = string.Join(",", _refAssessmentSetupIDList);
FISCA.UDT.AccessHelper _AccessHelper = new FISCA.UDT.AccessHelper();
_worker.ReportProgress(0, "取得課程成績單設定樣板...");
string qry = "ref_exam_template_id IN (" + assessmentSetupIDs + ") and schoolyear='" + K12.Data.School.DefaultSchoolYear + "' and semester ='" + K12.Data.School.DefaultSemester + "' and exam ='" + _examType + "'";
List <UDT_ReportTemplate> configures = _AccessHelper.Select <UDT_ReportTemplate>(qry);
_documentDict = new Dictionary <string, Document>();
foreach (UDT_ReportTemplate templateconfig in configures)
{
if (!_documentDict.ContainsKey(templateconfig.Ref_exam_Template_ID))
{
Document _doc = new Document();
templateconfig.Decode(); // 將 stream 轉成 Word
_doc = templateconfig.Template;
_documentDict.Add(templateconfig.Ref_exam_Template_ID, _doc);
}
}
#endregion
#region 取得修課學生、 並做整理
List <K12.Data.SCAttendRecord> scList = K12.Data.SCAttend.SelectByCourseIDs(_courseIDList);
List <string> studentIDList = new List <string>();
foreach (K12.Data.SCAttendRecord scr in scList)
{
studentIDList.Add(scr.Student.ID);
// 建立成績整理 Dict ，[studentID_courseID,[scoreKey,scoreID]]
_scoreDict.Add(scr.Student.ID + "_" + scr.Course.ID, new Dictionary <string, string>());
}
#endregion
// 建立功能變數對照
CreateFieldTemplate();
#region 取得、整理ESL成績
_worker.ReportProgress(20, "取得ESL課程成績");
int progress = 80;
decimal per = (decimal)(100 - progress) / scList.Count;
int count = 0;
string course_ids = string.Join("','", _courseIDList);
string student_ids = string.Join("','", studentIDList);
string sql = "SELECT * FROM $esl.gradebook_assessment_score WHERE ref_course_id IN ('" + course_ids + "') AND ref_student_id IN ('" + student_ids + "') ORDER BY last_update "; // 2018/6/21 通通都抓了，因為一張成績單上資訊，不只Final的
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(sql);
foreach (DataRow row in dt.Rows)
{
string termWord = "" + row["term"];
string subjectWord = "" + row["subject"];
string assessmentWord = "" + row["assessment"];
string id = "" + row["ref_student_id"] + "_" + row["ref_course_id"];
// 有教師自訂的子項目成績就跳掉 不處理
if ("" + row["custom_assessment"] != "")
{
continue;
}
// 要設計一個模式 處理 三種成績
// 項目都有，為assessment 成績
if (termWord != "" && "" + subjectWord != "" && "" + assessmentWord != "")
{
if (_scoreDict.ContainsKey(id))
{
// 指標型成績
if (_indicatorList.Contains("" + row["ref_course_id"] + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_')))
{
string key = "評量" + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + "指標";
if (_scoreDict[id].ContainsKey(key))
{
_scoreDict[id][key] = "" + row["value"]; //重覆項目，後來時間的蓋過前面
}
else
{
_scoreDict[id].Add(key, "" + row["value"]);
}
}
// 評語型成績
else if (_commentList.Contains("" + row["ref_course_id"] + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_')))
{
string key = "評量" + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + "評語";
if (_scoreDict[id].ContainsKey(key))
{
_scoreDict[id][key] = "" + row["value"]; //重覆項目，後來時間的蓋過前面
}
else
{
_scoreDict[id].Add(key, "" + row["value"]);
}
}
// 分數型成績
else
{
string key = "評量" + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + assessmentWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + "分數";
if (_scoreDict[id].ContainsKey(key))
{
_scoreDict[id][key] = "" + row["value"]; //重覆項目，後來時間的蓋過前面
}
else
{
_scoreDict[id].Add(key, "" + row["value"]);
}
}
}
}
// 沒有assessment，為subject 成績
if (termWord != "" && "" + subjectWord != "" && "" + assessmentWord == "")
{
if (_scoreDict.ContainsKey(id))
{
_scoreDict[id].Add("評量" + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "/" + subjectWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + "分數", "" + row["value"]);
}
}
// 沒有assessment、subject，為term 成績
if (termWord != "" && "" + subjectWord == "" && "" + assessmentWord == "")
{
if (_scoreDict.ContainsKey(id))
{
_scoreDict[id].Add("評量" + "_" + termWord.Trim().Replace(' ', '_').Replace('"', '_') + "_" + "分數", "" + row["value"]);
}
}
}
// 課程學期成績
string sqlSemesterCourseScore = @"SELECT
sc_attend.ref_student_id
,sc_attend.ref_course_id
,exam_template.name
,course.domain
,course.subject
,sc_attend.score
FROM sc_attend
LEFT JOIN course ON course.id = sc_attend.ref_course_id
LEFT JOIN exam_template ON exam_template.id =course.ref_exam_template_id
WHERE course.id IN ('" + course_ids + "') " +
"AND sc_attend.ref_student_id IN ('" + student_ids + "')" +
"ORDER BY ref_student_id,domain,subject";
DataTable dtSemesterCourseScore = qh.Select(sqlSemesterCourseScore);
foreach (DataRow row in dtSemesterCourseScore.Rows)
{
string id = "" + row["ref_student_id"] + "_" + row["ref_course_id"];
string templateWord = "" + row["name"];
string domainWord = "" + row["domain"];
string subjectWord = "" + row["subject"];
string score = "" + row["score"]; // 課程學期成績
if (_scoreDict.ContainsKey(id))
{
#region 跟樣板的功能變數
// 理論上一學期上 一個學生 只會有一個ESL評分樣版的課程成績 ， 不會有同一個ESL 評分樣版 有不同的課程成績
if (!_scoreDict[id].ContainsKey("課程學期成績分數"))
{
_scoreDict[id].Add("課程學期成績分數", score);
}
if (!_scoreDict[id].ContainsKey("課程學期成績等第"))
{
decimal score_d;
if (decimal.TryParse(score, out score_d))
{
_scoreDict[id].Add("課程學期成績等第", dm.GetDegreeByScore(score_d));
}
}
#endregion
}
}
#endregion
foreach (K12.Data.SCAttendRecord scar in scList)
{
string id = scar.RefStudentID + "_" + scar.RefCourseID;
string assessmentSetID = _courseIDPairDict[scar.RefCourseID];
DataTable data = _assessmentSetupDataTableDict[assessmentSetID];
DataRow row = data.NewRow();
row["電子報表辨識編號"] = "系統編號{" + scar.Student.ID + "}"; // 學生系統編號
row["學年度"] = scar.Course.SchoolYear;
row["學期"] = scar.Course.Semester;
row["學號"] = scar.Student.StudentNumber;
row["年級"] = scar.Student.Class != null ? "" + scar.Student.Class.GradeYear : "";
row["英文課程名稱"] = scar.Course.Name;
row["原班級名稱"] = scar.Student.Class != null ? "" + scar.Student.Class.Name : "";
row["學生英文姓名"] = scar.Student.EnglishName;
row["學生中文姓名"] = scar.Student.Name;
row["教師一"] = scar.Course.Teachers.Count > 0 ? scar.Course.Teachers.Find(x => x.Sequence == 1).TeacherName : ""; // 新寫法 直接找list 內教師條件
row["教師二"] = scar.Course.Teachers.Count > 1 ? scar.Course.Teachers.Find(x => x.Sequence == 2).TeacherName : "";
row["教師三"] = scar.Course.Teachers.Count > 2 ? scar.Course.Teachers.Find(x => x.Sequence == 3).TeacherName : "";
if (_itemDict.ContainsKey(scar.RefCourseID))
{
foreach (string mergeKey in _itemDict[scar.RefCourseID].Keys)
{
if (row.Table.Columns.Contains(mergeKey))
{
row[mergeKey] = _itemDict[scar.RefCourseID][mergeKey];
}
}
}
if (_scoreDict.ContainsKey(id))
{
foreach (string mergeKey in _scoreDict[id].Keys)
{
if (row.Table.Columns.Contains(mergeKey))
{
row[mergeKey] = _scoreDict[id][mergeKey];
}
}
}
data.Rows.Add(row);
count++;
progress += (int)(count * per);
_worker.ReportProgress(progress);
}
Document docFinal = new Document();
foreach (string assessmentSetupID in _assessmentSetupDataTableDict.Keys)
{
Document doc = _documentDict[assessmentSetupID];
DataTable data = _assessmentSetupDataTableDict[assessmentSetupID];
doc.MailMerge.Execute(data);
docFinal.AppendDocument(doc, ImportFormatMode.KeepSourceFormatting);
}
docFinal.Sections[0].Remove();// 把第一頁刪掉
e.Result = docFinal;
_worker.ReportProgress(100, "ESL 報表列印完成。");
}
private void _worker_DoWork(object sender, DoWorkEventArgs e)
{
double total = AllStudentID.Count;
double count = 0;
//學期歷程
Dictionary <string, HistoryUtil> utilCache = GetStudentHistories();
//學期成績
Dictionary <string, StudentScores> scoreCache = GetStudentScores(utilCache);
//聯絡資訊
Dictionary <string, ContactInfo> contactCache = GetStudentContactInfos();
//等第對照表
DegreeMapper degreeMapper = new DegreeMapper();
Workbook book = new Workbook();
book.Open(new MemoryStream(Properties.Resources.五專集體報名成績匯入檔));
Worksheet ws = book.Worksheets[0];
int rowIndex = 2;
foreach (JHStudentRecord stu in JHStudent.SelectByIDs(AllStudentID))
{
count++;
int colIndex = 3; //前面三個欄位不用管，直接跳過。
#region 基本資料
ws.Cells[rowIndex, colIndex++].PutValue((stu.Class != null) ? stu.Class.Name : "");
ws.Cells[rowIndex, colIndex++].PutValue(stu.StudentNumber);
ws.Cells[rowIndex, colIndex++].PutValue(stu.Name);
ws.Cells[rowIndex, colIndex++].PutValue(stu.IDNumber);
ws.Cells[rowIndex, colIndex++].PutValue((stu.Gender == "男") ? "1" : "2");
ws.Cells[rowIndex, colIndex++].PutValue(Global.GetFormatedBirthday(stu.Birthday));
#endregion
#region 聯絡資訊
if (contactCache.ContainsKey(stu.ID))
{
ContactInfo contact = contactCache[stu.ID];
ws.Cells[rowIndex, colIndex++].PutValue(contact.PhoneNumber);
ws.Cells[rowIndex, colIndex++].PutValue(contact.ZipCode);
ws.Cells[rowIndex, colIndex++].PutValue(contact.Address);
}
else
{
colIndex += 3; //沒有聯絡資訊直接跳過三個欄位
}
#endregion
#region 學期成績
//有學期歷程及學期成績才會印
if (utilCache.ContainsKey(stu.ID) && scoreCache.ContainsKey(stu.ID))
{
#region 領域成績
int scoreStartColIndex = 12;
HistoryUtil util = utilCache[stu.ID];
StudentScores studentScore = scoreCache[stu.ID];
SemesterData indexSem = new SemesterData(0, 0, 2);
for (int i = 0; i < FiveSemester; i++)
{
colIndex = scoreStartColIndex + i * Global.GetDomains().Count;
indexSem = indexSem.NextSemester();
if (util.ExistByGradeYear(indexSem.GradeYear, indexSem.Semester) == false)
{
continue;
}
foreach (decimal?score in studentScore.GetScoreList(util.SemesterData))
{
string value = score.HasValue ? degreeMapper.GetDegreeByScore(score.Value) : "";
if (ScoreMode) //如果是 ScoreMode，則印出分數
{
ws.Cells[rowIndex, colIndex++].PutValue("" + score);
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(value);
}
}
}
#endregion
#region 總平均
colIndex = scoreStartColIndex + FiveSemester * Global.GetDomains().Count;
decimal?averageScore = studentScore.GetAverage(util.AllSemesterData);
string averageValue = averageScore.HasValue ? degreeMapper.GetDegreeByScore(averageScore.Value) : "";
if (ScoreMode) //如果是 ScoreMode，則印出分數
{
ws.Cells[rowIndex, colIndex++].PutValue("" + averageScore);
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(averageValue);
}
#endregion
}
#endregion
_worker.ReportProgress((int)(count * 100 / total));
rowIndex++;
}
e.Result = book;
}