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