public unsafe void SetRenderer(Renderer renderer, uint numParams, Parameter[] parameters)
{
if (disposed)
{
throw new ObjectDisposedException("Library", "Cannot access a disposed object.");
}
if (renderer == null)
{
throw new ArgumentNullException("renderer");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
ParameterRec[] paramRecs = new ParameterRec[parameters.Length]; //parameters.Select(x => x.Record).ToArray();
for (int i = 0; i < paramRecs.Length; i++)
paramRecs[i] = parameters[i].Record;
fixed(void *ptr = paramRecs)
{
Error err = FT.FT_Set_Renderer(Reference, renderer.Reference, numParams, (IntPtr)ptr);
if (err != Error.Ok)
{
throw new FreeTypeException(err);
}
}
}
private void btnExport_Click(object sender, EventArgs e)
{
btnExport.Enabled = false;
if (!bgWorker.IsBusy)
{
ParameterRec data = new ParameterRec();
data.ClassIDs = string.Join(",", listClassID);
bgWorker.RunWorkerAsync(data);
}
}
private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
ParameterRec data = (ParameterRec)e.Argument;
Document doc = new Document();
doc.RemoveAllChildren();
int progress = 0;
// 取得學年度學期班級學生領域成績
DataTable dt = GetClassStudentDomainScore(data);
bgWorker.ReportProgress(progress += 10);
// 資料解析
ParseData(dt);
bgWorker.ReportProgress(progress += 10);
// 資料填寫
DataTable table = FillMergeFiledData(data);
bgWorker.ReportProgress(progress += 10);
int p = 0;
if (table.Rows.Count > 0)
{
p = 70 / table.Rows.Count;
}
foreach (DataRow row in table.Rows)
{
Document eachDoc = new Document();
eachDoc.Sections.Clear();
Section section = (Section)eachDoc.ImportNode(docTemplate.FirstSection, true);
eachDoc.AppendChild(section);
eachDoc.MailMerge.CleanupOptions = MailMergeCleanupOptions.RemoveEmptyParagraphs;
eachDoc.MailMerge.Execute(row);
doc.Sections.Add(doc.ImportNode(eachDoc.Sections[0], true));
bgWorker.ReportProgress(progress += p);
}
string path = $"{Application.StartupPath}\\Reports\\班級成績預警通知單.docx";
int i = 1;
while (File.Exists(path))
{
string docName = Path.GetFileNameWithoutExtension(path);
string newPath = $"{Path.GetDirectoryName(path)}\\成績預警通知單{i++}{Path.GetExtension(path)}";
path = newPath;
}
doc.Save(path, SaveFormat.Docx);
e.Result = path;
}
private void btnExport_Click(object sender, EventArgs e)
{
if (!bgWorker.IsBusy)
{
ParameterRec data = new ParameterRec();
data.SchoolYear = cbxSchoolYear.SelectedItem.ToString();
data.Semester = cbxSemester.SelectedItem.ToString();
data.Range = cbxRange.SelectedItem.ToString();
bgWorker.RunWorkerAsync(data);
}
}
private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
ParameterRec data = (ParameterRec)e.Argument;
int progress = 0;
// 取得各年級領域成績
DataTable dt = GetDomainScore(data.SchoolYear, data.Semester);
bgWorker.ReportProgress(progress += 15);
// 資料解析
ParseData(dt, data.Range);
bgWorker.ReportProgress(progress += 15);
if (listDomainFromData.Count > 0)
{
Workbook wb = FillWorkBookData(data);
bgWorker.ReportProgress(progress += 25);
string path = $"{Application.StartupPath}\\Reports\\領域不及格人數統計表.xlsx";
int i = 1;
while (File.Exists(path))
{
string docName = Path.GetFileNameWithoutExtension(path);
string newPath = $"{Path.GetDirectoryName(path)}\\領域不及格人數統計表{i++}{Path.GetExtension(path)}";
path = newPath;
}
wb.Save(path, SaveFormat.Xlsx);
bgWorker.ReportProgress(100);
e.Result = path;
}
else
{
bgWorker.ReportProgress(100);
e.Result = "";
}
}
private DataTable FillMergeFiledData(ParameterRec data)
{
DataTable dt = CreateMergeFiledTable();
foreach (string classID in dicClassNameByID.Keys)
{
DataRow row = dt.NewRow();
row["school_name"] = School.ChineseName;
row["class_name"] = dicClassNameByID[classID];
// 班級領域清單
List <string> listDomain = dicDomainNameByClassID[classID];
// 根據領域對照表做排序
listDomain.Sort(delegate(string a, string b)
{
int aIndex = listDomainName.FindIndex(domain => domain == a);
int bIndex = listDomainName.FindIndex(domain => domain == b);
if (aIndex > bIndex)
{
return(1);
}
else if (aIndex == bIndex)
{
return(0);
}
else
{
return(-1);
}
});
// 目前樣板最多支援8個領域 多的不顯示
if (listDomain.Count > 8)
{
listDomain.RemoveRange(8, listDomain.Count - 8);
}
// 領域
{
int d = 1;
foreach (string domain in listDomain)
{
row[$"domain_{d}"] = domain;
d++;
}
}
// 學生
Dictionary <string, Dictionary <string, ScoreRec> > dicStuDomainScore = dicClassStuDomainScore[classID];
int s = 1;
foreach (string stuID in dicStuDomainScore.Keys)
{
StudentRec stuRec = dicStuRecByID[stuID];
row[$"seat_no_{s}"] = stuRec.SeatNo;
row[$"name_{s}"] = stuRec.Name;
int d = 1;
int sc = 0;
int passCount = 0;
decimal totalPower = 0;
decimal totalScore = 0;
// 領域成績
foreach (string domain in listDomain)
{
if (dicStuDomainScore[stuID].ContainsKey(domain))
{
string score = dicStuDomainScore[stuID][domain].Score;
string originScore = dicStuDomainScore[stuID][domain].OriginScore;
string power = dicStuDomainScore[stuID][domain].Power;
row[$"stu_{s}_domain_{d}"] = score == originScore ? score : $"{score}";
//totalScore += FloatParser(score) * FloatParser(power);
//totalPower += FloatParser(power);
// 最後平均使用算術平均不使用加權平均
totalScore += DecimalParser(score);
totalPower += 1;
if (DecimalParser(score) >= 60)
{
passCount++;
}
sc++;
}
d++;
}
if (sc > 0)
{
// 沒有權重就不幫你算
if (totalPower > 0)
{
// 成績計算規則
ScoreCalculator defaultScoreCalculator = new ScoreCalculator(null);
ScoreCalculator studentCalculator = defaultScoreCalculator;
if (calcIDCache.ContainsKey(stuID) && calcCache.ContainsKey(calcIDCache[stuID]))
{
studentCalculator = calcCache[calcIDCache[stuID]];
}
//// 平均成績
//row[$"stu_{s}_avg_score"] = studentCalculator.ParseGraduateScore(totalScore / totalPower); // Math.Round(totalScore / totalPower, 2);
List <decimal> scoreList = new List <decimal>();
if (StudentSemsDomainScoreSumDict.ContainsKey(stuID) && StudentSemsCreditSumDict.ContainsKey(stuID))
{
foreach (string sms in StudentSemsDomainScoreSumDict[stuID].Keys)
{
// 各學期做加權平均
if (StudentSemsCreditSumDict[stuID].ContainsKey(sms))
{
decimal ss = StudentSemsDomainScoreSumDict[stuID][sms];
decimal cc = StudentSemsCreditSumDict[stuID][sms];
if (cc > 0)
{
// 使用領域計算規則進位
scoreList.Add(studentCalculator.ParseDomainScore(ss / cc));
}
}
}
}
// 最後再做算術平均在四捨五入
row[$"stu_{s}_avg_score"] = studentCalculator.ParseGraduateScore(scoreList.Average()); // Math.Round(totalScore / totalPower, 2);
}
// 領域及格數
row[$"stu_{s}_pass_count"] = passCount;
}
s++;
}
dt.Rows.Add(row);
}
return(dt);
}
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);
}
private Workbook FillWorkBookData(ParameterRec data)
{
Workbook prototype = new Workbook();
prototype.Copy(wbTemplate);
Worksheet ps = prototype.Worksheets[0];
Workbook wb = new Workbook();
wb.Copy(prototype);
Worksheet ws = wb.Worksheets[0];
Range colRange = ps.Cells.CreateRange(2, 1, 1, 1);
Range rowRange = ps.Cells.CreateRange(3, 1, false);
int rowIndex = 0;
int colIndex = 0;
int domainCount = listDomainFromData.Count;
string title = cbxIsSchoolYear.Checked ? $"{data.SchoolYear}學年度 領域不及格人數統計表"
: $"{data.SchoolYear}學年度 {data.Semester}學期 領域不及格人數統計表";
ws.Cells.Merge(rowIndex, 0, 1, domainCount * 2);
ws.Cells[rowIndex++, 0].PutValue(title);
// 各學習領域學生成績評量情形
{
ws.Cells.Merge(1, 1, 1, domainCount);
Range range = ws.Cells.CreateRange(1, 1, 1, domainCount);
range.SetOutlineBorders(CellBorderType.Thin, Color.Black);
range.PutValue("各學習領域學生成績評量情形", false, false);
Cell cell = ws.Cells.GetCell(1, 1);
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
}
// 學生成績評量不及格領域數情形
{
ws.Cells.Merge(1, domainCount + 1, 1, domainCount);
Range range = ws.Cells.CreateRange(1, domainCount + 1, 1, domainCount);
range.SetOutlineBorders(CellBorderType.Thin, Color.Black);
range.PutValue("學生成績評量不及格領域數情形", false, false);
Cell cell = ws.Cells.GetCell(1, domainCount + 1);
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
}
rowIndex++;
colIndex = 1;
// 領域
foreach (string domain in listDomainFromData)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
range.ColumnWidth = colRange.ColumnWidth;
ws.Cells[rowIndex, colIndex++].PutValue(domain);
}
// 不及格數
for (int i = 1; i <= listDomainFromData.Count; i++)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
range.ColumnWidth = colRange.ColumnWidth;
ws.Cells[rowIndex, colIndex++].PutValue($"{i}個學習領域不及格人數");
}
rowIndex++;
// 年級
foreach (string gradeYear in listGradeYear)
{
colIndex = 0;
if (rowIndex > 3)
{
ws.Cells.InsertRow(rowIndex);
}
ws.Cells.CreateRange(rowIndex, 1, false).CopyStyle(rowRange);
ws.Cells[rowIndex, colIndex++].PutValue($"{gradeYear}年級");
// 領域不及格人數
foreach (string domain in listDomainFromData)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
if (dicUnPassCountByDomainGradeYear[gradeYear].ContainsKey(domain))
{
int unPassCount = dicUnPassCountByDomainGradeYear[gradeYear][domain];
ws.Cells[rowIndex, colIndex++].PutValue(unPassCount);
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(0);
}
}
// 不及格領域人數
for (int i = 1; i <= listDomainFromData.Count; i++)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
if (dicUnPassCountByUnPassGradeYear.ContainsKey(gradeYear))
{
if (dicUnPassCountByUnPassGradeYear[gradeYear].ContainsKey(i))
{
int unPassCount = dicUnPassCountByUnPassGradeYear[gradeYear][i];
ws.Cells[rowIndex, colIndex++].PutValue(unPassCount);
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(0);
}
}
else
{
ws.Cells[rowIndex, colIndex++].PutValue(0);
}
}
rowIndex++;
}
colIndex = 1;
// 總計 領域
foreach (string domain in listDomainFromData)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
ws.Cells[rowIndex, colIndex++].PutValue(dicTotalCountByDomain[domain]);
}
// 總計 不及格領域數
foreach (int i in dicTotalCountByUnPass.Keys)
{
Range range = ws.Cells.CreateRange(rowIndex, colIndex, 1, 1);
range.CopyStyle(colRange);
ws.Cells[rowIndex, colIndex++].PutValue(dicTotalCountByUnPass[i]);
}
rowIndex++;
int year = DateTime.Now.Year - 1911;
int month = DateTime.Now.Month;
int day = DateTime.Now.Day;
ws.Cells[rowIndex, 0].PutValue($"列印日期: {year}年{month}月{day}日");
return(wb);
}
