// 將補考資料 移至其他群組
private void btnSwap_Click(object sender, EventArgs e)
{
int selectRows = 0;
// 將舊資料清除
_selected_dataList.Clear();
// 計算 所有被選取的 補考資料 項目數
foreach (DataGridViewRow row in dataGridViewX1.Rows)
{
if (row.Selected)
{
selectRows++;
UDT_MakeUpData data = _dataList.Find(d => d.UID == "" + row.Tag);
if (data != null)
{
_selected_dataList.Add(data);
}
}
}
if (selectRows < 1)
{
FISCA.Presentation.Controls.MsgBox.Show("轉移群組功能需選擇 大於1個資料。");
return;
}
// 傳進目前 的 補考群組、 選擇 欲轉其他群組的補考資料
SwapMakeUpGroupForm smuf = new SwapMakeUpGroupForm(_group, _selected_dataList);
if (DialogResult.OK == smuf.ShowDialog())
{
_dataWorker.RunWorkerAsync();
}
}
private void dataGridViewX1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
// 只驗 分數
if (e.ColumnIndex != 11)
{
return;
}
DataGridViewCell cell = dataGridViewX1.Rows[e.RowIndex].Cells[e.ColumnIndex];
// 找到對應的成績資料 以便對出 小數位數計算設定
UDT_MakeUpData inputData = _dataList.Find(data => data.UID == "" + cell.OwningRow.Tag);
cell.ErrorText = String.Empty;
decimal d = 0;
if (!decimal.TryParse("" + e.FormattedValue, out d))
{
if ("" + e.FormattedValue != "缺" && "" + e.FormattedValue != "")
{
cell.ErrorText = "缺考請輸入『缺』，本欄無法輸入其他文字。";
inputData.HasNewMakeUpScore = false;
}
if ("" + e.FormattedValue == "缺")
{
cell.ErrorText = String.Empty;
inputData.New_MakeUp_Score = "缺";
inputData.HasNewMakeUpScore = true;
}
// 原本有值 填成空白 也要更新
if ("" + e.FormattedValue == "" && inputData.MakeUp_Score != "")
{
cell.ErrorText = String.Empty;
inputData.New_MakeUp_Score = "";
inputData.HasNewMakeUpScore = true;
}
}
else
{
int index = ("" + e.FormattedValue).IndexOf('.');
// 輸入整數 (Ex:66， 因為沒有 「.」，所以會回傳-1)
if (index == -1)
{
// 輸入整數，就不必管任何驗證了。
inputData.New_MakeUp_Score = "" + e.FormattedValue;
inputData.HasNewMakeUpScore = true;
}
else
{
decimal dn = decimal.Parse(inputData.DecimalNumber);
if (("" + e.FormattedValue).Substring(index + 1).Length > dn)
{
cell.ErrorText = "補考成績小數位數輸入超過計算規則設定值『" + inputData.DecimalNumber + "』。";
inputData.HasNewMakeUpScore = false;
}
else
{
// 驗過了 改變值
inputData.New_MakeUp_Score = "" + e.FormattedValue;
inputData.HasNewMakeUpScore = true;
}
}
}
}
private void DataWorker_DoWork(object sender, DoWorkEventArgs e)
{
// 清空 舊資料
_dataList.Clear();
#region 取得本學期得其他補考群組資料 驗證重覆使用
string query_make_up_group = @"
SELECT
$make.up.batch.makeup_batch
,$make.up.group.makeup_group
FROM $make.up.group
LEFT JOIN $make.up.batch ON $make.up.batch.uid = $make.up.group.ref_makeup_batch_id :: BIGINT
WHERE $make.up.batch.uid = " + _group.Ref_MakeUp_Batch_ID + " AND $make.up.group.uid !=" + _group.UID;
QueryHelper qh_make_up_group = new QueryHelper();
DataTable dt_make_up_group = qh_make_up_group.Select(query_make_up_group);
if (dt_make_up_group.Rows.Count > 0)
{
foreach (DataRow row in dt_make_up_group.Rows)
{
_makeup_batch = "" + row["makeup_batch"];
if (!_groupNameList.Contains("" + row["makeup_group"]))
{
_groupNameList.Add("" + row["makeup_group"]);
}
}
}
#endregion
#region 取得補考資料
tmpGroupIDList.Clear();
tmpGroupIDList.Add("'" + _group.UID + "'");
//透過 group_id 取得學生修課及格與補考標準
Utility uti = new Utility();
Dictionary <string, DataRow> studPassScoreDict = uti.GetStudentMakeupPassScoreByGroupIDs(tmpGroupIDList);
string query = @"
SELECT
$make.up.data.uid
,$make.up.data.ref_student_id
,student.name AS student_name
,dept.name AS department
,class.class_name
,student.seat_no
,student.student_number
,$make.up.data.subject
,$make.up.data.level
,$make.up.data.credit
,$make.up.data.c_is_required_by
,$make.up.data.c_is_required
,$make.up.data.score
,$make.up.data.makeup_score
,$make.up.data.pass_standard
,$make.up.data.makeup_standard
,$make.up.data.decimalnumber
,$make.up.data.calrole
FROM $make.up.data
LEFT JOIN student ON student.id = $make.up.data.ref_student_id :: BIGINT
LEFT JOIN class ON class.id = student.ref_class_id
LEFT JOIN dept ON dept.id = student.ref_dept_id OR dept.id = class.ref_dept_id
WHERE
$make.up.data.Ref_MakeUp_Group_ID = '" + _group.UID + "'" +
" ORDER BY class.grade_year DESC, class.display_order, class_name, seat_no";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
//整理目前的補考資料
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpData data = new UDT_MakeUpData();
data.UID = "" + row["uid"];
//學生科別
data.Department = "" + row["department"];
//學生姓名
data.StudentName = "" + row["student_name"];
//學生班級
data.ClassName = "" + row["class_name"];
//學生座號
data.Seat_no = "" + row["seat_no"];
//學生學號
data.StudentNumber = "" + row["student_number"];
//科目
data.Subject = "" + row["subject"];
//科目
data.Level = "" + row["level"];
//學分
data.Credit = "" + row["credit"];
//校部定
data.C_Is_Required_By = "" + row["c_is_required_by"];
//必選修
data.C_Is_Required = "" + row["c_is_required"];
//成績分數
data.Score = "" + row["score"];
//補考分數
data.MakeUp_Score = "" + row["makeup_score"];
string key = _group.UID + "_" + row["ref_student_id"] + "_" + data.Subject + "_" + data.Level;
//if (studPassScoreDict.ContainsKey(key))
//{
// //及格標準
// if (studPassScoreDict[key]["passing_standard"] != null)
// data.Pass_Standard = studPassScoreDict[key]["passing_standard"].ToString();
// //補考標準
// if (studPassScoreDict[key]["makeup_standard"] != null)
// data.MakeUp_Standard = studPassScoreDict[key]["makeup_standard"].ToString();
//}
//及格標準
data.Pass_Standard = "" + row["pass_standard"];
//補考標準
data.MakeUp_Standard = "" + row["makeup_standard"];
// 取得本學生 成績的輸入小數位數規則
// 只有 管理補考成績的情境用得到， 目前會要用比較慢一筆一筆學生查詢，而不直接寫在SQL內的原因
// 是因為要跟 產生補考清單 時 使用的API 邏輯一致
if (_action == "管理補考成績")
{
data.DecimalNumber = "" + row["decimalnumber"];
data.HasNewMakeUpScore = false;
// 成績身分
data.CalRole = "" + row["calrole"];
}
_dataList.Add(data);
}
}
#endregion
}
/// <summary>
/// 依補考梯次 取得所有 補考群組 以及 其下 所有的補考資料
/// </summary>
/// <param name="targetBatchID"></param>
private void LoadMakeUpGroup(string targetBatchID, DoWorkEventArgs e)
{
_worker.ReportProgress(0, "取得補考群組、補考資料...");
_scoreGroupDict.Clear();
;
#region 取得補考群組
string query = @"
SELECT
$make.up.group.uid
,$make.up.group.ref_makeup_batch_id
,$make.up.group.makeup_group
,$make.up.group.ref_teacher_id
,$make.up.group.description
,$make.up.group.makeup_date
,$make.up.group.makeup_time
,$make.up.group.makeup_place
,COUNT($make.up.data.uid) AS studentCount
FROM $make.up.group
LEFT JOIN $make.up.data ON $make.up.data.ref_makeup_group_id :: BIGINT = $make.up.group.uid
WHERE $make.up.group.ref_makeup_batch_id = '" + targetBatchID + @"'
GROUP BY $make.up.group.uid
ORDER BY $make.up.group.makeup_group";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
//整理目前的補考群組 資料
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpGroup group = new UDT_MakeUpGroup();
group.UID = "" + row["uid"];
//補考群組
group.MakeUp_Group = "" + row["makeup_group"];
//補考群組 參考梯次uid
group.Ref_MakeUp_Batch_ID = "" + row["ref_makeup_batch_id"];
//閱卷老師 ID
group.Ref_Teacher_ID = "" + row["ref_teacher_id"];
//補考人數
group.StudentCount = "" + row["studentCount"];;
// 描述
group.Description = "" + row["description"];
// 補考日期
group.MakeUp_Date = "" + row["makeup_date"];
// 補考時間
group.MakeUp_Time = "" + row["makeup_time"];
// 補考地點
group.MakeUp_Place = "" + row["makeup_place"];
if (!_scoreGroupDict.ContainsKey(group.UID))
{
_scoreGroupDict.Add(group.UID, new List <UDT_MakeUpData>());
}
// 所屬補考梯次
group.MakeUpBatch = _batchList.Find(b => b.UID == "" + row["ref_makeup_batch_id"]);
_makeUpGroupList.Add(group);
}
}
#endregion
#region 取得補考資料
List <string> groupIDList = new List <string>();
foreach (UDT_MakeUpGroup group in _makeUpGroupList)
{
groupIDList.Add("'" + group.UID + "'");
}
string groupIDs = string.Join(",", groupIDList);
query = @"
SELECT
$make.up.data.uid
,$make.up.data.ref_student_id
,$make.up.data.ref_makeup_group_id
,student.name AS student_name
,dept.name AS department
,class.class_name
,class.grade_year
,student.seat_no
,student.student_number
,$make.up.data.subject
,$make.up.data.level
,$make.up.data.credit
,$make.up.data.c_is_required_by
,$make.up.data.c_is_required
,$make.up.data.score
,$make.up.data.makeup_score
,$make.up.data.pass_standard
,$make.up.data.makeup_standard
FROM $make.up.data
LEFT JOIN student ON student.id = $make.up.data.ref_student_id :: BIGINT
LEFT JOIN class ON class.id = student.ref_class_id
LEFT JOIN dept ON dept.id = student.ref_dept_id OR dept.id = class.ref_dept_id
WHERE
$make.up.data.Ref_MakeUp_Group_ID IN (" + groupIDs + ")" +
"ORDER BY ref_makeup_group_id,grade_year DESC, class.display_order, class_name, seat_no"
;
qh = new QueryHelper();
dt = qh.Select(query);
//整理目前的補考資料
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpData data = new UDT_MakeUpData();
data.UID = "" + row["uid"];
// 參考 補考群組 id
data.Ref_MakeUp_Group_ID = "" + row["ref_makeup_group_id"];
//學生ID
data.Ref_Student_ID = "" + row["ref_student_id"];
//年級
data.GradeYear = "" + row["grade_year"];
//學生科別
data.Department = "" + row["department"];
//學生姓名
data.StudentName = "" + row["student_name"];
//學生班級
data.ClassName = "" + row["class_name"];
//學生座號
data.Seat_no = "" + row["seat_no"];
//學生學號
data.StudentNumber = "" + row["student_number"];
//科目
data.Subject = "" + row["subject"];
//科目
data.Level = "" + row["level"];
//學分
data.Credit = "" + row["credit"];
//校部定
data.C_Is_Required_By = "" + row["c_is_required_by"];
//必選修
data.C_Is_Required = "" + row["c_is_required"];
//成績分數(原始成績)
data.Score = "" + row["score"];
//補考分數
data.MakeUp_Score = "" + row["makeup_score"];
//及格標準
data.Pass_Standard = "" + row["pass_standard"];
//補考標準
data.MakeUp_Standard = "" + row["makeup_standard"];
if (_printMode == "依群組" && _scoreGroupDict.ContainsKey(data.Ref_MakeUp_Group_ID))
{
_scoreGroupDict[data.Ref_MakeUp_Group_ID].Add(data);
}
if (_printMode == "依學生")
{
data.MakeUpGroup = _makeUpGroupList.Find(g => g.UID == data.Ref_MakeUp_Group_ID); // 所屬補考群組
if (!_scoreStudentDict.ContainsKey(data.Ref_Student_ID))
{
_scoreStudentDict.Add(data.Ref_Student_ID, new List <UDT_MakeUpData>());
_scoreStudentDict[data.Ref_Student_ID].Add(data);
}
else
{
_scoreStudentDict[data.Ref_Student_ID].Add(data);
}
}
}
}
#endregion
#region 排序
//2022/5/26 Cynthia 客服反應會造成排序亂掉，先註解
//foreach (string groupID in _scoreGroupDict.Keys)
//{
// _scoreGroupDict[groupID].Sort((x, y) => { return x.ClassName.CompareTo(y.ClassName); });
//}
#endregion
#region 列印
_dataTable = new DataTable();
Aspose.Words.Document doc = _configure.Template;
if (_printMode == "依群組")
{
#region 基本資訊
_dataTable.Columns.Add("學年度");
_dataTable.Columns.Add("學期");
_dataTable.Columns.Add("補考梯次別");
_dataTable.Columns.Add("補考群組名稱");
_dataTable.Columns.Add("補考日期");
_dataTable.Columns.Add("補考時間");
_dataTable.Columns.Add("補考地點");
_dataTable.Columns.Add("補考描述");
_dataTable.Columns.Add("補考人次");
#endregion
#region 補考學生資訊
for (int i = 1; i <= 350; i++)
{
_dataTable.Columns.Add("科別" + i);
_dataTable.Columns.Add("年級" + i);
_dataTable.Columns.Add("班級名稱" + i);
_dataTable.Columns.Add("座號" + i);
_dataTable.Columns.Add("學號" + i);
_dataTable.Columns.Add("姓名" + i);
_dataTable.Columns.Add("科目名稱" + i);
_dataTable.Columns.Add("科目級別" + i);
}
#endregion
#region 合併列印
foreach (string groupID in _scoreGroupDict.Keys)
{
// 所屬補考群組
UDT_MakeUpGroup group = _makeUpGroupList.Find(g => g.UID == groupID);
DataRow row = _dataTable.NewRow();
row["學年度"] = group.MakeUpBatch.School_Year;
row["學期"] = group.MakeUpBatch.Semester;
row["補考梯次別"] = group.MakeUpBatch.MakeUp_Batch;
row["補考群組名稱"] = group.MakeUp_Group;
row["補考日期"] = group.MakeUp_Date;
row["補考時間"] = group.MakeUp_Time;
row["補考地點"] = group.MakeUp_Place;
row["補考描述"] = group.Description;
int i = 1;
foreach (UDT_MakeUpData score in _scoreGroupDict[groupID])
{
row["科別" + i] = score.Department;
row["年級" + i] = score.GradeYear;
row["班級名稱" + i] = score.ClassName;
row["座號" + i] = score.Seat_no;
row["學號" + i] = score.StudentNumber;
row["姓名" + i] = score.StudentName;
row["科目名稱" + i] = score.Subject;
row["科目級別" + i] = score.Level;
i++;
}
// 看本群組有多少筆補考資料人次
row["補考人次"] = i - 1;
_dataTable.Rows.Add(row);
}
#endregion
}
if (_printMode == "依學生")
{
#region 基本資訊
_dataTable.Columns.Add("學年度");
_dataTable.Columns.Add("學期");
_dataTable.Columns.Add("科別");
_dataTable.Columns.Add("年級");
_dataTable.Columns.Add("班級名稱");
_dataTable.Columns.Add("座號");
_dataTable.Columns.Add("學號");
_dataTable.Columns.Add("姓名");
_dataTable.Columns.Add("電子報表辨識編號");
_dataTable.Columns.Add("補考梯次別");
_dataTable.Columns.Add("補考群組數");
#endregion
#region 補考群組資訊
for (int i = 1; i <= 30; i++)
{
_dataTable.Columns.Add("補考群組名稱" + i);
_dataTable.Columns.Add("補考日期" + i);
_dataTable.Columns.Add("補考時間" + i);
_dataTable.Columns.Add("補考地點" + i);
_dataTable.Columns.Add("補考群組描述" + i);
_dataTable.Columns.Add("科目名稱" + i);
_dataTable.Columns.Add("科目級別" + i);
}
#endregion
#region 合併列印
foreach (string studentID in _scoreStudentDict.Keys)
{
DataRow row = _dataTable.NewRow();
row["學年度"] = _batchList[0].School_Year;
row["學期"] = _batchList[0].Semester;
row["補考梯次別"] = _batchList[0].MakeUp_Batch;
// 抓第一筆資料
row["科別"] = _scoreStudentDict[studentID][0].Department;
row["年級"] = _scoreStudentDict[studentID][0].GradeYear;
row["班級名稱"] = _scoreStudentDict[studentID][0].ClassName;
row["座號"] = _scoreStudentDict[studentID][0].Seat_no;
row["學號"] = _scoreStudentDict[studentID][0].StudentNumber;
row["姓名"] = _scoreStudentDict[studentID][0].StudentName;
row["電子報表辨識編號"] = studentID;
int i = 1;
foreach (UDT_MakeUpData score in _scoreStudentDict[studentID])
{
row["補考群組名稱" + i] = score.MakeUpGroup.MakeUp_Group;
row["補考日期" + i] = score.MakeUpGroup.MakeUp_Date;
row["補考時間" + i] = score.MakeUpGroup.MakeUp_Time;
row["補考地點" + i] = score.MakeUpGroup.MakeUp_Place;
row["補考群組描述" + i] = score.MakeUpGroup.Description;
row["科目名稱" + i] = score.Subject;
row["科目級別" + i] = score.Level;
i++;
}
// 看此學生 有多少個要參加的補考群組數
row["補考群組數"] = i - 1;
_dataTable.Rows.Add(row);
}
#endregion
}
#endregion
doc.MailMerge.Execute(_dataTable);
e.Result = doc;
}
private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
_worker.ReportProgress(0);
// 以下補考邏輯 仿 原高中成績系統 SmartSchool.Evaluation 處理方式
#region 取得所有學生以及補考資訊
AccessHelper helper = new AccessHelper();
List <StudentRecord> allStudents = new List <StudentRecord>();
List <ClassRecord> allClasses = helper.ClassHelper.GetClass(_classIDList);
//WearyDogComputer computer = new WearyDogComputer();
Utility utility = new Utility();
double currentClass = 1;
double totalClasses = allClasses.Count;
foreach (ClassRecord aClass in allClasses)
{
List <StudentRecord> classStudents = aClass.Students;
//computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
utility.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
allStudents.AddRange(classStudents);
// 填上學生的修課紀錄
helper.StudentHelper.FillAttendCourse(int.Parse(_schoolYear), int.Parse(_semester), classStudents);
_worker.ReportProgress((int)(currentClass++ *60.0 / totalClasses));
}
double currentStudent = 1;
double totalStudents = allStudents.Count;
#endregion
#region 取得課程資訊 (授課教師)
List <K12.Data.CourseRecord> courseList = K12.Data.Course.SelectBySchoolYearAndSemester(int.Parse(_schoolYear), int.Parse(_semester));
#endregion
Dictionary <string, Dictionary <string, string> > subjectInfo = new Dictionary <string, Dictionary <string, string> >();
Dictionary <string, List <Dictionary <string, string> > > subjectStudentList = new Dictionary <string, List <Dictionary <string, string> > >();
// 補考群組Dict <課程名稱 +"_"+ 科目() +"_"+ 級別()+"_"+ 學分() +"_"+ "課程系統編號()" , UDT_MakeUpGroup>
Dictionary <string, UDT_MakeUpGroup> makeUpGroupDict = new Dictionary <string, UDT_MakeUpGroup>();
// 補考資料 List
List <UDT_MakeUpData> UDT_MakeUpDataList = new List <UDT_MakeUpData>();
// 整理 補考群組 補考資料
foreach (StudentRecord aStudent in allStudents)
{
List <StudentAttendCourseRecord> scattendList = aStudent.AttendCourseList;
string className = aStudent.RefClass.ClassName;
string seatNo = aStudent.SeatNo;
string studentName = aStudent.StudentName;
string studentNumber = aStudent.StudentNumber;
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if ("" + info.SchoolYear == _schoolYear && "" + info.Semester == _semester && !info.Pass)
{
StudentAttendCourseRecord scaRecord = scattendList.Find(sc => sc.Subject == info.Subject && sc.SubjectLevel == info.Level && sc.Credit == info.CreditDec());
string makeUpGroupKey = "";
// 是否為課程群組分類， 若其分數未取得學分(!info.Pass)、且補考標準， isCourseGroup 為 True
bool isCourseGroup = false;
if (info.Detail.GetAttribute("達補考標準") == "是" && scaRecord != null)
{
isCourseGroup = true;
//string makeUpGroupKey = scaRecord.CourseName + "_科目(" + scaRecord.Subject + ")_級別(" + scaRecord.SubjectLevel + ")_學分(" + scaRecord.Credit + ")_課程系統編號(" + scaRecord.CourseID + ")";
//makeUpGroupKey = scaRecord.CourseName + "_課程系統編號(" + scaRecord.CourseID + ")";
// 2019/04/26 穎驊與恩正確認， 同一學期的 課程名稱 在系統中是唯一值， 因此不必擔心重覆的問題，捨棄上面舊的補考群組名稱。
makeUpGroupKey = scaRecord.CourseName;
}
else if (info.Detail.GetAttribute("達補考標準") == "否" && scaRecord != null)
{
// 2019/04/26 穎驊與恩正確認， 不同課程的未達標準分開建立
makeUpGroupKey = scaRecord.CourseName + "_" + "未達補考標準群組";
}
else
{
// 不論有沒有達補考標準 該學期找不到 修課紀錄 通通分到未分群組
makeUpGroupKey = "未分群組";
}
if (!makeUpGroupDict.ContainsKey(makeUpGroupKey))
{
UDT_MakeUpGroup makeUpGroupRecord = new UDT_MakeUpGroup();
makeUpGroupRecord.MakeUp_Group = makeUpGroupKey;
makeUpGroupRecord.Description = "";
makeUpGroupRecord.MakeUp_Date = "";
makeUpGroupRecord.MakeUp_Time = "";
makeUpGroupRecord.MakeUp_Place = "";
makeUpGroupRecord.Ref_MakeUp_Batch_ID = _batch.UID;
// 只有課程群組 才會有 預設的 閱卷老師
// 授課教師 要另外對照出來(原本 smart school API 沒支援)，目前這邊會先抓教師一
if (isCourseGroup)
{
K12.Data.CourseRecord courseRecord = courseList.Find(cr => cr.ID == "" + scaRecord.CourseID);
makeUpGroupRecord.Ref_Teacher_ID = courseRecord != null ? courseRecord.MajorTeacherID : "";
}
else
{
makeUpGroupRecord.Ref_Teacher_ID = "";
}
makeUpGroupDict.Add(makeUpGroupKey, makeUpGroupRecord);
}
UDT_MakeUpData makeUpData = new UDT_MakeUpData();
// 參考補考梯次ID
makeUpData.Ref_MakeUp_Batch_ID = _batch.UID;
// 參考補考群組ID (這個時候還不會有 因為補考群組也正要新增)
//makeUpData.Ref_MakeUp_Group_ID = "";
makeUpData.Ref_MakeUp_Group_Name = makeUpGroupKey;
// 參考學生系統編號
makeUpData.Ref_Student_ID = aStudent.StudentID;
// 科別
makeUpData.Department = "";
// 科目
makeUpData.Subject = info.Subject;
// 級別
makeUpData.Level = info.Level;
// 學分
makeUpData.Credit = "" + info.CreditDec();
// 校部定
makeUpData.C_Is_Required_By = info.Detail.HasAttribute("修課校部訂") ? info.Detail.GetAttribute("修課校部訂") : "";
// 必選修
makeUpData.C_Is_Required = info.Detail.HasAttribute("修課必選修") ? info.Detail.GetAttribute("修課必選修") : "";
// 原始成績
makeUpData.Score = info.Detail.HasAttribute("原始成績") ? info.Detail.GetAttribute("原始成績") : "";
// 補考成績
makeUpData.MakeUp_Score = info.Detail.HasAttribute("補考成績") ? info.Detail.GetAttribute("補考成績") : "";
// 及格標準
makeUpData.Pass_Standard = info.Detail.HasAttribute("及格標準") ? info.Detail.GetAttribute("及格標準") : "";
// 補考標準
makeUpData.MakeUp_Standard = info.Detail.HasAttribute("補考標準") ? info.Detail.GetAttribute("補考標準") : "";
// 輸入小數位數限制
makeUpData.DecimalNumber = info.Detail.HasAttribute("位數限制") ? info.Detail.GetAttribute("位數限制") : "";
// 成績身分
makeUpData.CalRole = info.Detail.HasAttribute("成績身分") ? info.Detail.GetAttribute("成績身分") : "";
UDT_MakeUpDataList.Add(makeUpData);
}
}
_worker.ReportProgress(60 + (int)(currentStudent++ *20.0 / totalStudents));
}
// LOG 資訊
string _actor = DSAServices.UserAccount;;
string _client_info = ClientInfo.GetCurrentClientInfo().OutputResult().OuterXml;
// 拚 SQL
List <string> makeUpGroupDataList = new List <string>();
foreach (string groupKey in makeUpGroupDict.Keys)
{
UDT_MakeUpGroup makeUpGroupRecord = makeUpGroupDict[groupKey];
string data = string.Format(@"
SELECT
'{0}'::TEXT AS ref_makeup_batch_id
,'{1}'::TEXT AS makeup_group
,'{2}'::TEXT AS ref_teacher_id
,'{3}'::TEXT AS description
,'{4}'::TEXT AS makeup_date
,'{5}'::TEXT AS makeup_time
,'{6}'::TEXT AS makeup_place
", makeUpGroupRecord.Ref_MakeUp_Batch_ID, makeUpGroupRecord.MakeUp_Group, makeUpGroupRecord.Ref_Teacher_ID, makeUpGroupRecord.Description, makeUpGroupRecord.MakeUp_Date, makeUpGroupRecord.MakeUp_Time, makeUpGroupRecord.MakeUp_Place);
makeUpGroupDataList.Add(data);
}
string makeUpGroupDataString = string.Join(" UNION ALL", makeUpGroupDataList);
List <string> UDT_MakeUpDataDataList = new List <string>();
foreach (UDT_MakeUpData makeUpData in UDT_MakeUpDataList)
{
string data = string.Format(@"
SELECT
'{0}'::TEXT AS ref_makeup_batch_id
,'{1}'::TEXT AS ref_makeup_group_name
,'{2}'::TEXT AS ref_student_id
,'{3}'::TEXT AS subject
,'{4}'::TEXT AS level
,'{5}'::TEXT AS credit
,'{6}'::TEXT AS c_is_required_by
,'{7}'::TEXT AS c_is_required
,'{8}'::TEXT AS score
,'{9}'::TEXT AS makeup_score
,'{10}'::TEXT AS pass_standard
,'{11}'::TEXT AS makeup_standard
,'{12}'::TEXT AS decimalnumber
,'{13}'::TEXT AS calrole
", makeUpData.Ref_MakeUp_Batch_ID, makeUpData.Ref_MakeUp_Group_Name, makeUpData.Ref_Student_ID, makeUpData.Subject, makeUpData.Level, makeUpData.Credit, makeUpData.C_Is_Required_By, makeUpData.C_Is_Required, makeUpData.Score, makeUpData.MakeUp_Score, makeUpData.Pass_Standard, makeUpData.MakeUp_Standard, makeUpData.DecimalNumber, makeUpData.CalRole);
UDT_MakeUpDataDataList.Add(data);
}
string makeUpDataDataString = string.Join(" UNION ALL", UDT_MakeUpDataDataList);
string sql = string.Format(@"
WITH makeUpGroupData_row AS(
{0}
)
,makeUpDataData_row AS(
{1}
)
,insert_makeUpGroupData AS (
INSERT INTO $make.up.group(
ref_makeup_batch_id
,makeup_group
,ref_teacher_id
,description
,makeup_date
,makeup_time
,makeup_place
)
SELECT
makeUpGroupData_row.ref_makeup_batch_id::TEXT AS ref_makeup_batch_id
,makeUpGroupData_row.makeup_group::TEXT AS makeup_group
,makeUpGroupData_row.ref_teacher_id::TEXT AS ref_teacher_id
,makeUpGroupData_row.description::TEXT AS description
,makeUpGroupData_row.makeup_date::TEXT AS makeup_date
,makeUpGroupData_row.makeup_time::TEXT AS makeup_time
,makeUpGroupData_row.makeup_place::TEXT AS makeup_place
FROM
makeUpGroupData_row
RETURNING $make.up.group.*
)
,insert_makeUpDataData AS(
INSERT INTO $make.up.data(
ref_makeup_batch_id
,ref_makeup_group_id
,ref_student_id
,subject
,level
,credit
,c_is_required_by
,c_is_required
,score
,makeup_score
,pass_standard
,makeup_standard
,decimalnumber
,calrole
)
SELECT
makeUpDataData_row.ref_makeup_batch_id::TEXT AS ref_makeup_batch_id
,insert_makeUpGroupData.uid::TEXT AS ref_makeup_group_id
,makeUpDataData_row.ref_student_id::TEXT AS ref_student_id
,makeUpDataData_row.subject::TEXT AS subject
,makeUpDataData_row.level::TEXT AS level
,makeUpDataData_row.credit::TEXT AS credit
,makeUpDataData_row.c_is_required_by::TEXT AS c_is_required_by
,makeUpDataData_row.c_is_required::TEXT AS c_is_required
,makeUpDataData_row.score::TEXT AS score
,makeUpDataData_row.makeup_score::TEXT AS makeup_score
,makeUpDataData_row.pass_standard::TEXT AS pass_standard
,makeUpDataData_row.makeup_standard::TEXT AS makeup_standard
,makeUpDataData_row.decimalnumber::TEXT AS decimalnumber
,makeUpDataData_row.calrole::TEXT AS calrole
FROM
makeUpDataData_row
LEFT JOIN insert_makeUpGroupData ON insert_makeUpGroupData.makeup_group = makeUpDataData_row.ref_makeup_group_name
)
,insert_makeUpGroupData_Log AS (
INSERT INTO log(
actor
, action_type
, action
, target_category
, target_id
, server_time
, client_info
, action_by
, description
)
SELECT
'{2}'::TEXT AS actor
, 'Record' AS action_type
, '高中補考群組新增' AS action
, ''::TEXT AS target_category
, '' AS target_id
, now() AS server_time
, '{3}' AS client_info
, '高中補考群組新增'AS action_by
, ' 高中補考 學年度「'|| makeUpGroupData_row_Log.school_year||'」，學期「'|| makeUpGroupData_row_Log.semester||'」， 補考梯次「'|| makeUpGroupData_row_Log.makeup_batch||'」，新增補考群組 「'|| makeUpGroupData_row_Log.makeup_group ||'」，閱卷老師 「'|| COALESCE(makeUpGroupData_row_Log.teacher_name,'') ||'」。' AS description
FROM
(
SELECT
$make.up.batch.school_year AS school_year
,$make.up.batch.semester AS semester
,$make.up.batch.makeup_batch AS makeup_batch
,makeUpGroupData_row.makeup_group AS makeup_group
,teacher.teacher_name AS teacher_name
FROM makeUpGroupData_row
LEFT JOIN $make.up.batch ON $make.up.batch.uid :: TEXT = makeUpGroupData_row.ref_makeup_batch_id
LEFT JOIN teacher ON teacher.id :: TEXT = makeUpGroupData_row.ref_teacher_id
) AS makeUpGroupData_row_Log
)
,insert_makeUpDataData_Log AS (
INSERT INTO log(
actor
, action_type
, action
, target_category
, target_id
, server_time
, client_info
, action_by
, description
)
SELECT
'{2}'::TEXT AS actor
, 'Record' AS action_type
, '高中補考資料新增' AS action
, ''::TEXT AS target_category
, '' AS target_id
, now() AS server_time
, '{3}' AS client_info
, '高中補考資料新增'AS action_by
, ' 高中補考 學年度「'|| makeUpDataData_row_Log.school_year||'」，學期「'|| makeUpDataData_row_Log.semester||'」， 補考梯次「'|| makeUpDataData_row_Log.makeup_batch||'」，補考群組 「'|| makeUpDataData_row_Log.makeup_group ||'」，閱卷老師 「'|| COALESCE(makeUpDataData_row_Log.teacher_name,'') ||'」
新增 補考資料 學生系統編號 「'|| makeUpDataData_row_Log.ref_student_id||'」 ，學號 「'|| makeUpDataData_row_Log.student_number||'」，學生姓名 「'|| makeUpDataData_row_Log.student_name||'」
，科目 「'|| makeUpDataData_row_Log.subject||'」，級別 「'|| makeUpDataData_row_Log.level||'」，學分 「'|| makeUpDataData_row_Log.credit||'」，校部定 「'|| makeUpDataData_row_Log.c_is_required_by||'」，必選修 「'|| makeUpDataData_row_Log.c_is_required||'」
，分數 「'|| makeUpDataData_row_Log.score||'」 ，補考分數 「'|| makeUpDataData_row_Log.makeup_score||'」 ，及格標準 「'|| makeUpDataData_row_Log.pass_standard||'」，補考標準 「'|| makeUpDataData_row_Log.makeup_standard||'」' AS description
FROM
(
SELECT
$make.up.batch.school_year AS school_year
,$make.up.batch.semester AS semester
,$make.up.batch.makeup_batch AS makeup_batch
,insert_makeUpGroupData.makeup_group AS makeup_group
,teacher.teacher_name AS teacher_name
,student.id AS ref_student_id
,student.student_number AS student_number
,student.name AS student_name
,makeUpDataData_row.subject AS subject
,makeUpDataData_row.level AS level
,makeUpDataData_row.credit AS credit
,makeUpDataData_row.credit AS c_is_required_by
,makeUpDataData_row.credit AS c_is_required
,makeUpDataData_row.score AS score
,makeUpDataData_row.makeup_score AS makeup_score
,makeUpDataData_row.pass_standard AS pass_standard
,makeUpDataData_row.makeup_standard AS makeup_standard
FROM makeUpDataData_row
LEFT JOIN insert_makeUpGroupData ON insert_makeUpGroupData.makeup_group = makeUpDataData_row.ref_makeup_group_name
LEFT JOIN $make.up.batch ON $make.up.batch.uid :: TEXT = insert_makeUpGroupData.ref_makeup_batch_id
LEFT JOIN teacher ON teacher.id :: TEXT = insert_makeUpGroupData.ref_teacher_id
LEFT JOIN student ON student.id :: TEXT = makeUpDataData_row.ref_student_id
) AS makeUpDataData_row_Log
)
SELECT 0
", makeUpGroupDataString, makeUpDataDataString, _actor, _client_info);
//K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
////執行sql
//uh.Execute(sql);
FISCA.Data.QueryHelper qh = new FISCA.Data.QueryHelper();
//執行sql
// 有補考群組才執行SQL
if (makeUpGroupDataList.Count > 0)
{
qh.Select(sql);
}
else
{
MsgBox.Show("本梯次班級沒有任何學生需要補考，故本梯次沒有任何補考群組。", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
/// <summary>
/// 依補考梯次 取得所有 補考群組 以及 其下 所有的補考資料
/// </summary>
/// <param name="targetBatchID"></param>
private void LoadMakeUpGroup(string targetBatchID)
{
_worker.ReportProgress(0, "取得補考群組、補考資料...");
_teacherList.Clear();
_scoreDict.Clear();
// 取得所有教師資料 之後可以對照出 閱卷老師
List <TeacherRecord> trList = K12.Data.Teacher.SelectAll();
foreach (TeacherRecord tr in trList)
{
if (tr.Status == TeacherRecord.TeacherStatus.刪除)
{
continue;
}
_teacherList.Add(tr);
}
string query = @"
SELECT
$make.up.group.uid
,$make.up.group.ref_makeup_batch_id
,$make.up.group.makeup_group
,$make.up.group.ref_teacher_id
,$make.up.group.description
,COUNT($make.up.data.uid) AS studentCount
FROM $make.up.group
LEFT JOIN $make.up.data ON $make.up.data.ref_makeup_group_id :: BIGINT = $make.up.group.uid
LEFT JOIN $make.up.batch ON $make.up.batch.uid = $make.up.group.ref_makeup_batch_id :: BIGINT
WHERE $make.up.group.ref_makeup_batch_id = '" + targetBatchID + @"'
AND ($make.up.batch.is_archive is null OR $make.up.batch.is_archive != '是' )
GROUP BY $make.up.group.uid
ORDER BY $make.up.group.makeup_group";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
//整理目前的補考梯次 資料
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpGroup group = new UDT_MakeUpGroup();
group.UID = "" + row["uid"];
//補考群組
group.MakeUp_Group = "" + row["makeup_group"];
//補考群組 參考梯次uid
group.Ref_MakeUp_Batch_ID = "" + row["ref_makeup_batch_id"];
//閱卷老師 ID
group.Ref_Teacher_ID = "" + row["ref_teacher_id"];
//由於老師暱稱為空值時
//不要顯示( )
//By 俊威 2019/8/26
// group.TeacherName = _teacherList.Find(t => t.ID == "" + row["ref_teacher_id"]) != null ?
//_teacherList.Find(t => t.ID == "" + row["ref_teacher_id"]).Name + "(" +
//_teacherList.Find(t => t.ID == "" + row["ref_teacher_id"]).Nickname + ")" : "";
// 閱卷老師全名 老師名字(綽號)
foreach (TeacherRecord t in _teacherList)
{
if ("" + row["ref_teacher_id"] == t.ID)
{
if (!string.IsNullOrEmpty(t.Nickname))
{
group.TeacherName = t.Name + "(" + t.Nickname + ")";
}
else
{
group.TeacherName = t.Name;
}
continue;
}
}
//補考人數
group.StudentCount = "" + row["studentCount"];;
// 描述
group.Description = "" + row["description"];
if (!_scoreDict.ContainsKey(group.UID))
{
_scoreDict.Add(group.UID, new List <UDT_MakeUpData>());
}
// 所屬補考梯次
group.MakeUpBatch = _batchList.Find(b => b.UID == "" + row["ref_makeup_batch_id"]);
_makeUpGroupList.Add(group);
}
}
#region 取得補考資料
List <string> groupIDList = new List <string>();
if (_makeUpGroupList.Count > 0)
{
foreach (UDT_MakeUpGroup group in _makeUpGroupList)
{
groupIDList.Add("'" + group.UID + "'");
}
string groupIDs = string.Join(",", groupIDList);
query = @"
SELECT
$make.up.data.uid
,$make.up.data.ref_student_id
,$make.up.data.ref_makeup_group_id
,student.name AS student_name
,dept.name AS department
,class.class_name
,student.seat_no
,student.student_number
,$make.up.data.subject
,$make.up.data.level
,$make.up.data.credit
,$make.up.data.c_is_required_by
,$make.up.data.c_is_required
,$make.up.data.score
,$make.up.data.makeup_score
,$make.up.data.pass_standard
,$make.up.data.makeup_standard
FROM $make.up.data
LEFT JOIN student ON student.id = $make.up.data.ref_student_id :: BIGINT
LEFT JOIN class ON class.id = student.ref_class_id
LEFT JOIN dept ON dept.id = student.ref_dept_id OR dept.id = class.ref_dept_id
WHERE
$make.up.data.Ref_MakeUp_Group_ID IN (" + groupIDs + ")";
qh = new QueryHelper();
dt = qh.Select(query);
//整理目前的補考資料
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpData data = new UDT_MakeUpData();
data.UID = "" + row["uid"];
// 參考 補考群組 id
data.Ref_MakeUp_Group_ID = "" + row["ref_makeup_group_id"];
//學生科別
data.Department = "" + row["department"];
//學生姓名
data.StudentName = "" + row["student_name"];
//學生班級
data.ClassName = "" + row["class_name"];
//學生座號
data.Seat_no = "" + row["seat_no"];
//學生學號
data.StudentNumber = "" + row["student_number"];
//科目
data.Subject = "" + row["subject"];
//科目
data.Level = "" + row["level"];
//學分
data.Credit = "" + row["credit"];
//校部定
data.C_Is_Required_By = "" + row["c_is_required_by"];
//必選修
data.C_Is_Required = "" + row["c_is_required"];
//成績分數
data.Score = "" + row["score"];
//補考分數
data.MakeUp_Score = "" + row["makeup_score"];
//及格標準
data.Pass_Standard = "" + row["pass_standard"];
//補考標準
data.MakeUp_Standard = "" + row["makeup_standard"];
if (_scoreDict.ContainsKey(data.Ref_MakeUp_Group_ID))
{
_scoreDict[data.Ref_MakeUp_Group_ID].Add(data);
}
}
}
#endregion
}
int scoreCount = 0;
// 填 DataGridView
_groupDataGridViewRowList = new List <GroupDataGridViewRow>();
foreach (string groupID in _scoreDict.Keys)
{
_worker.ReportProgress(60 + 30 * (scoreCount++ / _scoreDict.Keys.Count), "取得補考成績資料...");
GroupDataGridViewRow row = new GroupDataGridViewRow(groupID, _scoreDict[groupID], _makeUpGroupList);
row.Tag = groupID; // 用補考群組 ID 當作 Tag
_groupDataGridViewRowList.Add(row);
}
}
/// <summary>
/// 依補考梯次 取得所有 補考群組 以及 其下 所有的補考資料
/// </summary>
/// <param name="targetBatchID"></param>
private void LoadMakeUpGroup(string targetBatchID)
{
_worker.ReportProgress(0, "取得補考群組、補考資料...");
_scoreDict.Clear();
;
if (targetBatchID != "")
{
#region 取得補考群組
string query = @"
SELECT
$make.up.group.uid
,$make.up.group.ref_makeup_batch_id
,$make.up.group.makeup_group
,$make.up.group.ref_teacher_id
,$make.up.group.description
,COUNT($make.up.data.uid) AS studentCount
FROM $make.up.group
LEFT JOIN $make.up.data ON $make.up.data.ref_makeup_group_id :: BIGINT = $make.up.group.uid
WHERE $make.up.group.ref_makeup_batch_id = '" + targetBatchID + @"'
GROUP BY $make.up.group.uid
ORDER BY $make.up.group.makeup_group";
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
//整理目前的補考梯次 資料
if (dt.Rows.Count > 0)
{
_makeUpGroupList.Clear();
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpGroup group = new UDT_MakeUpGroup();
group.UID = "" + row["uid"];
//補考群組
group.MakeUp_Group = "" + row["makeup_group"];
//補考群組 參考梯次uid
group.Ref_MakeUp_Batch_ID = "" + row["ref_makeup_batch_id"];
//閱卷老師 ID
group.Ref_Teacher_ID = "" + row["ref_teacher_id"];
//補考人數
group.StudentCount = "" + row["studentCount"];;
// 描述
group.Description = "" + row["description"];
if (!_scoreDict.ContainsKey(group.UID))
{
_scoreDict.Add(group.UID, new List <UDT_MakeUpData>());
}
// 所屬補考梯次
group.MakeUpBatch = _batchList.Find(b => b.UID == "" + row["ref_makeup_batch_id"]);
_makeUpGroupList.Add(group);
}
}
#endregion
#region 取得補考資料
List <string> groupIDList = new List <string>();
foreach (UDT_MakeUpGroup group in _makeUpGroupList)
{
groupIDList.Add("'" + group.UID + "'");
}
string groupIDs = string.Join(",", groupIDList);
if (groupIDs != "")
{
//透過 group_id 取得學生修課及格與補考標準
Utility uti = new Utility();
Dictionary <string, DataRow> studPassScoreDict = uti.GetStudentMakeupPassScoreByGroupIDs(groupIDList);
query = @"
SELECT
$make.up.data.uid
,$make.up.data.ref_student_id
,$make.up.data.ref_makeup_group_id
,student.name AS student_name
,dept.name AS department
,class.class_name
,student.seat_no
,student.student_number
,$make.up.data.subject
,$make.up.data.level
,$make.up.data.credit
,$make.up.data.c_is_required_by
,$make.up.data.c_is_required
,$make.up.data.score
,$make.up.data.makeup_score
,$make.up.data.pass_standard
,$make.up.data.makeup_standard
FROM $make.up.data
LEFT JOIN student ON student.id = $make.up.data.ref_student_id :: BIGINT
LEFT JOIN class ON class.id = student.ref_class_id
LEFT JOIN dept ON dept.id = student.ref_dept_id OR dept.id = class.ref_dept_id
WHERE
$make.up.data.ref_makeup_group_id IN (" + groupIDs + ")";
qh = new QueryHelper();
dt = qh.Select(query);
// 取得學生學年度學期修課標準， 如果沒有使用預設，比對後填入
//整理目前的補考資料
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
UDT_MakeUpData data = new UDT_MakeUpData();
data.UID = "" + row["uid"];
// 參考 補考群組 id
data.Ref_MakeUp_Group_ID = "" + row["ref_makeup_group_id"];
//學生ID
data.Ref_Student_ID = "" + row["ref_student_id"];
//學生科別
data.Department = "" + row["department"];
//學生姓名
data.StudentName = "" + row["student_name"];
//學生班級
data.ClassName = "" + row["class_name"];
//學生座號
data.Seat_no = "" + row["seat_no"];
//學生學號
data.StudentNumber = "" + row["student_number"];
//科目
data.Subject = "" + row["subject"];
//科目
data.Level = "" + row["level"];
//學分
data.Credit = "" + row["credit"];
//校部定
data.C_Is_Required_By = "" + row["c_is_required_by"];
//必選修
data.C_Is_Required = "" + row["c_is_required"];
//成績分數(原始成績)
data.Score = "" + row["score"];
//補考分數
data.MakeUp_Score = "" + row["makeup_score"];
string key = data.Ref_MakeUp_Group_ID + "_" + data.Ref_Student_ID + "_" + data.Subject + "_" + data.Level;
if (studPassScoreDict.ContainsKey(key))
{
//及格標準
if (studPassScoreDict[key]["passing_standard"] != null)
{
data.Pass_Standard = studPassScoreDict[key]["passing_standard"].ToString();
}
//補考標準
if (studPassScoreDict[key]["makeup_standard"] != null)
{
data.MakeUp_Standard = studPassScoreDict[key]["makeup_standard"].ToString();
}
}
////及格標準
//data.Pass_Standard = "" + row["pass_standard"];
////補考標準
//data.MakeUp_Standard = "" + row["makeup_standard"];
if (_scoreDict.ContainsKey(data.Ref_MakeUp_Group_ID))
{
_scoreDict[data.Ref_MakeUp_Group_ID].Add(data);
}
}
}
}
#endregion
}
}
