private void dataGridViewX1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.Button != MouseButtons.Left)
{
return;
}
if (e.ColumnIndex < 0)
{
return;
}
if (e.RowIndex < 0)
{
return;
}
DataGridViewCell cell = dataGridViewX1.Rows[e.RowIndex].Cells[e.ColumnIndex];
_targetGroupID = "" + cell.OwningRow.Tag; // targetTermName
UDT_MakeUpGroup selectedGroup = _makeUpGroupList.Find(g => g.UID == _targetGroupID);
// 管理補考成績
InsertUpdateMakeUpGroupForm iumgf = new InsertUpdateMakeUpGroupForm(_schoolYear, _semester, "管理補考成績", selectedGroup);
iumgf.ShowDialog();
RefreshListView(); // 更改完成績後，重整畫面
}
// 刪除補考群組
private void MenuItemDeleteGroup_Click(Object sender, System.EventArgs e)
{
if (_selectedGroupList.Find(g => g.MakeUp_Group == "未分群組") != null)
{
FISCA.Presentation.Controls.MsgBox.Show("【未分群組】 不得刪除。");
return;
}
UDT_MakeUpGroup non_group = _groupList.Find(g => g.MakeUp_Group == "未分群組");
foreach (UDT_MakeUpGroup selectGroup in _selectedGroupList)
{
// 把 刪除的學生人數 都數到未分群組
if (non_group != null)
{
non_group.StudentCount = "" + (int.Parse(non_group.StudentCount) + int.Parse(selectGroup.StudentCount));
}
selectGroup.IsDirty = true;
selectGroup.Action = "刪除";
}
RefreshUIGroupListView();
}
private void dataGridViewX1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.Button != MouseButtons.Left)
{
return;
}
if (e.ColumnIndex < 0)
{
return;
}
if (e.RowIndex < 0)
{
return;
}
DataGridViewCell cell = dataGridViewX1.Rows[e.RowIndex].Cells[e.ColumnIndex];
if (lblIsDirty.Visible)
{
FISCA.Presentation.Controls.MsgBox.Show("編輯群組資料前，請先儲存。");
return;
}
// 紀錄目前 UID
GroupUIDList.Clear();
foreach (DataGridViewRow drv in dataGridViewX1.Rows)
{
if (drv.IsNewRow)
{
continue;
}
string uid = drv.Tag.ToString();
GroupUIDList.Add(uid);
}
//// 找到選取到的 梯次
_selectedGroup = _selectedGroupList.Find(x => x.UID == "" + cell.OwningRow.Tag);
if (_action == "管理補考成績")
{
// 管理補考成績
InsertUpdateMakeUpGroupForm iumgf = new InsertUpdateMakeUpGroupForm(_schoolYear, _semester, "管理補考成績", _selectedGroup);
if (iumgf.ShowDialog() == DialogResult.Yes)
{
RefreshListView(); //重整畫面
}
}
else
{
// 修改模式
InsertUpdateMakeUpGroupForm iumgf = new InsertUpdateMakeUpGroupForm(_schoolYear, _semester, "修改群組", _selectedGroup);
if (iumgf.ShowDialog() == DialogResult.Yes)
{
RefreshListView(); //重整畫面
}
}
//RefreshListView(); //重整畫面
}
// 合併補考群組
private void MenuItemMergeGroup_Click(Object sender, System.EventArgs e)
{
if (_selectedGroupList.Find(g => g.MakeUp_Group == "未分群組") != null)
{
FISCA.Presentation.Controls.MsgBox.Show("【未分群組】 不得合併。");
return;
}
if (_selectedGroupList.Count < 2)
{
FISCA.Presentation.Controls.MsgBox.Show("合併功能需選擇 大於1個群組。");
return;
}
// 合併後的 群組id
string new_merge_group_id = "";
// 合併後的 群組名稱
string new_merge_group_name = "";
// 第一個群組(併別人的)
UDT_MakeUpGroup firstGroup = _selectedGroupList.Find(g => g.IsFirstSelectedRow);
new_merge_group_id = firstGroup.UID;
new_merge_group_name = firstGroup.MakeUp_Group;
foreach (UDT_MakeUpGroup selectGroup in _selectedGroupList)
{
// 非第一個選的 就是要被併的
if (!selectGroup.IsFirstSelectedRow)
{
// 把從其他群組併過來的 人數 加上
firstGroup.StudentCount = "" + (int.Parse(firstGroup.StudentCount) + int.Parse(selectGroup.StudentCount));
selectGroup.New_Merge_Group_ID = new_merge_group_id;
selectGroup.New_Merge_Group_Name = new_merge_group_name;
selectGroup.IsDirty = true;
selectGroup.Action = "合併";
}
}
RefreshUIGroupListView();
}
public SwapMakeUpGroupForm(UDT_MakeUpGroup oldGroup, List <UDT_MakeUpData> makeup_data_list)
{
_oldGroup = oldGroup;
_makeup_data_list = makeup_data_list;
_makeupDict = new Dictionary <string, string>();
InitializeComponent();
_groupWorker = new BackgroundWorker();
_groupWorker.DoWork += new DoWorkEventHandler(GroupWorker_DoWork);
_groupWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(GroupWorker_RunWorkerCompleted);
_groupWorker.ProgressChanged += new ProgressChangedEventHandler(GroupWorker_ProgressChanged);
_groupWorker.WorkerReportsProgress = true;
_groupWorker.RunWorkerAsync();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (dataGridViewX1.Rows.Count == 0)
{
FISCA.Presentation.Controls.MsgBox.Show("目前無任何補考群組可以儲存。");
return;
}
picLoadingDgvXMakeUpGroup.Visible = true;
// LOG 資訊
string _actor = DSAServices.UserAccount;;
string _client_info = ClientInfo.GetCurrentClientInfo().OutputResult().OuterXml;
//拚SQL
// 兜資料
List <string> dataList = new List <string>();
string sql = "";
// 更新的
foreach (DataGridViewRow row in dataGridViewX1.Rows)
{
UDT_MakeUpGroup groupRecord = _groupList.Find(g => g.UID == "" + row.Tag);
// 再原有補考群組清單找不到， 就是 新增的
if (groupRecord == null)
{
}
else
{
// 沒有改變的項目 也不必儲存
if (!groupRecord.IsDirty)
{
continue;
}
}
// row 的 Tag 為 補考群組的系統編號
groupRecord.UID = "" + row.Tag;
groupRecord.MakeUp_Group = "" + row.Cells[0].Value;
groupRecord.Description = "" + row.Cells[3].Value;
string logDetail = @" 高中補考 學年度「" + _schoolYear +
@"」，學期「" + _semester + @"」， 補考梯次「 " + _selectedBatch.MakeUp_Batch + @"」， 補考群組「 " + groupRecord.MakeUp_Group + @"」
";
string old_teacher_name = "";
string new_teacher_name = "";
if (groupRecord.New_Ref_Teacher_ID != null)
{
K12.Data.TeacherRecord old_tr = _teacherList.Find(t => t.ID == groupRecord.Ref_Teacher_ID);
K12.Data.TeacherRecord new_tr = _teacherList.Find(t => t.ID == groupRecord.New_Ref_Teacher_ID);
if (old_tr != null)
{
if (!string.IsNullOrEmpty(old_tr.Nickname))
{
old_teacher_name = old_tr.Name + "(" + old_tr.Nickname + ")";
}
else
{
old_teacher_name = old_tr.Name;
}
}
else
{
old_teacher_name = "";
}
if (new_tr != null)
{
if (!string.IsNullOrEmpty(new_tr.Nickname))
{
new_teacher_name = new_tr.Name + "(" + new_tr.Nickname + ")";
}
else
{
new_teacher_name = new_tr.Name;
}
}
else
{
new_teacher_name = "";
}
}
if (groupRecord.Ref_Teacher_ID != groupRecord.New_Ref_Teacher_ID)
{
logDetail += "閱卷老師由「 " + old_teacher_name + @"」， 更改為「 " + new_teacher_name + @"」";
// 更新統一用Ref_Teacher_ID
groupRecord.Ref_Teacher_ID = groupRecord.New_Ref_Teacher_ID;
}
string data = string.Format(@"
SELECT
'{0}'::TEXT AS makeup_group
,'{1}'::TEXT AS school_year
,'{2}'::TEXT AS semester
,'{3}'::TEXT AS description
,'{4}'::TEXT AS log_detail
,'{5}'::TEXT AS ref_makeup_batch_id
,'{6}'::TEXT AS ref_teacher_id
,'{7}'::BIGINT AS uid
,'{8}'::TEXT AS action
,'{9}'::TEXT AS new_merge_group_id
", groupRecord.MakeUp_Group, _schoolYear, _semester, groupRecord.Description, logDetail, _selectedBatch.UID, groupRecord.Ref_Teacher_ID != null ? groupRecord.Ref_Teacher_ID : "", groupRecord.UID, groupRecord.Action, groupRecord.New_Merge_Group_ID);
dataList.Add(data);
}
foreach (UDT_MakeUpGroup group in _groupList)
{
// 刪除的
if (group.Action == "刪除")
{
string logDetail = @" 高中補考 學年度「" + _schoolYear +
@"」，學期「" + _semester + @"」， 補考梯次「 " + _selectedBatch.MakeUp_Batch + @"」， 補考群組「 " + group.MakeUp_Group + @"」
";
logDetail += " 刪除， 其下的補考學生將分派至【未分群組】";
string data = string.Format(@"
SELECT
'{0}'::TEXT AS makeup_group
,'{1}'::TEXT AS school_year
,'{2}'::TEXT AS semester
,'{3}'::TEXT AS description
,'{4}'::TEXT AS log_detail
,'{5}'::TEXT AS ref_makeup_batch_id
,'{6}'::TEXT AS ref_teacher_id
,'{7}'::BIGINT AS uid
,'{8}'::TEXT AS action
,'{9}'::TEXT AS new_merge_group_id
", group.MakeUp_Group, _schoolYear, _semester, group.Description, logDetail, _selectedBatch.UID, group.Ref_Teacher_ID != null ? group.Ref_Teacher_ID : "", group.UID, group.Action, group.New_Merge_Group_ID);
dataList.Add(data);
}
// 合併的
if (group.Action == "合併")
{
string logDetail = @" 高中補考 學年度「" + _schoolYear +
@"」，學期「" + _semester + @"」， 補考梯次「 " + _selectedBatch.MakeUp_Batch + @"」， 補考群組「 " + group.MakeUp_Group + @"」
";
logDetail += " 合併 至【" + group.New_Merge_Group_Name + "】群組。";
string data = string.Format(@"
SELECT
'{0}'::TEXT AS makeup_group
,'{1}'::TEXT AS school_year
,'{2}'::TEXT AS semester
,'{3}'::TEXT AS description
,'{4}'::TEXT AS log_detail
,'{5}'::TEXT AS ref_makeup_batch_id
,'{6}'::TEXT AS ref_teacher_id
,'{7}'::BIGINT AS uid
,'{8}'::TEXT AS action
,'{9}'::TEXT AS new_merge_group_id
", group.MakeUp_Group, _schoolYear, _semester, group.Description, logDetail, _selectedBatch.UID, group.Ref_Teacher_ID != null ? group.Ref_Teacher_ID : "", group.UID, group.Action, group.New_Merge_Group_ID);
dataList.Add(data);
}
}
string dataString = string.Join(" UNION ALL", dataList);
sql = string.Format(@"
WITH data_row AS(
{0}
)
,update_group_data AS (
Update $make.up.group
SET
makeup_group = data_row.makeup_group
,description = data_row.description
,ref_teacher_id = data_row.ref_teacher_id
FROM data_row
WHERE $make.up.group.uid = data_row.uid
AND data_row.action ='更新'
)
,delete_group_data AS (
DELETE
FROM $make.up.group
WHERE $make.up.group.uid IN
(
SELECT data_row.uid
FROM data_row
WHERE data_row.action ='刪除' OR data_row.action ='合併'
)
),check_non_group_id AS
(
--確認【未分群組】 的id 如果沒有 就幫他建立新的
INSERT INTO $make.up.group
(Ref_MakeUp_Batch_ID, MakeUp_Group)
SELECT '{3}', '未分群組'
WHERE
NOT EXISTS (
SELECT
*
FROM $make.up.group
WHERE
Ref_MakeUp_Batch_ID = '{3}'
AND MakeUp_Group ='未分群組'
)
RETURNING *
),non_group_id AS
(
SELECT
*
FROM check_non_group_id
UNION ALL
SELECT
*
FROM $make.up.group
WHERE
Ref_MakeUp_Batch_ID = '{3}'
AND MakeUp_Group ='未分群組'
),update_delete_data_data AS (
Update $make.up.data
SET
Ref_MakeUp_Group_ID = non_group_id.uid
FROM data_row
LEFT JOIN non_group_id ON data_row.action ='刪除'
WHERE $make.up.data.Ref_MakeUp_Group_ID ::BIGINT = data_row.uid
AND data_row.action ='刪除'
)
,update_merge_data_data AS (
Update $make.up.data
SET
Ref_MakeUp_Group_ID = data_row.new_merge_group_id
FROM data_row
WHERE $make.up.data.Ref_MakeUp_Group_ID ::BIGINT = data_row.uid
AND data_row.action ='合併'
)
INSERT INTO log(
actor
, action_type
, action
, target_category
, target_id
, server_time
, client_info
, action_by
, description
)
SELECT
'{1}'::TEXT AS actor
, 'Record' AS action_type
, '編輯高中補考群組' AS action
, ''::TEXT AS target_category
, '' AS target_id
, now() AS server_time
, '{2}' AS client_info
, '編輯高中補考群組'AS action_by
,data_row.log_detail AS description
FROM
data_row
", dataString, _actor, _client_info, _selectedBatch.UID);
//沒有資料不處理
if (!string.IsNullOrWhiteSpace(dataString))
{
try
{
K12.Data.UpdateHelper uh = new UpdateHelper();
//執行sql
uh.Execute(sql);
FISCA.Presentation.Controls.MsgBox.Show("儲存成功。");
// 儲存完畢 重新整理 介面
RefreshListView();
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show("儲存失敗," + ex.Message);
picLoadingDgvXMakeUpGroup.Visible = false;
}
}
else
{
picLoadingDgvXMakeUpGroup.Visible = false;
}
}
private void dataGridViewX1_SelectionChanged(object sender, EventArgs e)
{
DevComponents.DotNetBar.Controls.DataGridViewX drvx = (DevComponents.DotNetBar.Controls.DataGridViewX)sender;
int selectRowsTotalCount = 0;
// 先數 這次使用者 全部一共選擇幾個項目
foreach (DataGridViewRow row in drvx.Rows)
{
if (row.Selected)
{
selectRowsTotalCount++;
}
}
// 假如 選擇 Row的總數 為 1 代表 這是第一個選的 Row，
// 之後，要做補考群組合併 的補考群組，都會合到第一個補考群組
if (selectRowsTotalCount == 1)
{
// 把之前 選的群組Group 都清空
_selectedGroupList.Clear();
// 先將所有的Row 都視為非第一個選 row
foreach (UDT_MakeUpGroup group in _groupList)
{
group.IsFirstSelectedRow = false;
}
foreach (DataGridViewRow row in drvx.Rows)
{
if (row.Selected)
{
UDT_MakeUpGroup firstSelectedGroup = _groupList.Find(g => g.UID == "" + row.Tag);
// 用uid 找的到的 是原本就有的 補考群組
if (firstSelectedGroup != null)
{
firstSelectedGroup.IsFirstSelectedRow = true;
_selectedGroupList.Add(firstSelectedGroup);
}
}
}
}
// 假如 選擇的 Row 總數 大於 1，代表使用者再選擇完第一個Row後
// 還有再選擇其他的 Row，之前第一個Row(要合併其他人的， IsFirstSelectedRow =true) 就不必再加入List
// 後面選的 Row(被合併的, IsFirstSelectedRow =false ) 再加List 即可
if (selectRowsTotalCount > 1)
{
foreach (DataGridViewRow row in drvx.Rows)
{
if (row.Selected)
{
UDT_MakeUpGroup selectedGroup = _groupList.Find(g => g.UID == "" + row.Tag);
// 非第一個選的 row ，且不在_selectedGroupList 中 就加
if (!selectedGroup.IsFirstSelectedRow && !_selectedGroupList.Contains(selectedGroup))
{
_selectedGroupList.Add(selectedGroup);
}
}
}
}
}
private void GetMakeUpGroup()
{
_groupList.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 = '" + _selectedBatch.UID + @"'
GROUP BY $make.up.group.uid ORDER BY makeup_group ASC";
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"];
group.IsDirty = false;
// 把 補考梯次 配對
group.MakeUpBatch = _selectedBatch;
_groupList.Add(group);
}
}
// 如果已有 UID List 排序
if (GroupUIDList.Count > 0)
{
_groupList = _groupList.OrderBy(d => GroupUIDList.IndexOf(d.UID)).ToList();
}
//另存份 原本的list 作為比較
_groupListOri = new List <UDT_MakeUpGroup>(_groupList.ToArray());
#endregion
}
// 修正模式 傳補考群組物件
public InsertUpdateMakeUpGroupForm(string school_year, string semester, string action, UDT_MakeUpGroup group)
{
InitializeComponent();
_school_year = school_year;
_semester = semester;
_dataList = new List <UDT_MakeUpData>();
_selected_dataList = new List <UDT_MakeUpData>();
_groupNameList = new List <string>();
var tList = K12.Data.Teacher.SelectAll();
_teacherList = new List <TeacherRecord>();
foreach (K12.Data.TeacherRecord rec in tList)
{
if (rec.Status == TeacherRecord.TeacherStatus.刪除)
{
continue;
}
_teacherList.Add(rec);
}
//_teacherList = K12.Data.Teacher.SelectAll();
// 老師 依教師姓名排序
_teacherList.Sort((x, y) => { return(x.Name.CompareTo(y.Name)); });
// 加入空白教師，提供使用者可以取消
cboTeacher.Items.Add("");
//將教師加入清單
foreach (K12.Data.TeacherRecord teacher in _teacherList)
{
// 老師全名
if (!string.IsNullOrEmpty(teacher.Nickname))
{
cboTeacher.Items.Add(teacher.Name + "(" + teacher.Nickname + ")");
}
else
{
cboTeacher.Items.Add(teacher.Name);
}
}
K12.Data.TeacherRecord groupTeacher = _teacherList.Find(t => t.ID == group.Ref_Teacher_ID);
// 預設為群組的閱卷老師
if (groupTeacher != null)
{
if (!string.IsNullOrEmpty(groupTeacher.Nickname))
{
cboTeacher.Text = groupTeacher.Name + "(" + groupTeacher.Nickname + ")";
}
else
{
cboTeacher.Text = groupTeacher.Name;
}
}
else
{
cboTeacher.Text = "";
}
_dataWorker = new BackgroundWorker();
_dataWorker.DoWork += new DoWorkEventHandler(DataWorker_DoWork);
_dataWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(DataWorker_RunWorkerCompleted);
_dataWorker.ProgressChanged += new ProgressChangedEventHandler(DataWorker_ProgressChanged);
_dataWorker.WorkerReportsProgress = true;
_action = action;
_group = group;
// 只有新增 模式 才可以讓使用者 編輯
if (_action == "新增群組")
{
}
// 修改模式 使用者只能修改
if (_action == "修改群組")
{
this.Text = "管理補考群組";
txtGroupName.Text = _group.MakeUp_Group;
txtDescription.Text = _group.Description;
txtDate.Text = _group.MakeUp_Date;
txtTime.Text = _group.MakeUp_Time;
txtPlace.Text = _group.MakeUp_Place;
txtGroupName.Enabled = false;
txtDescription.Enabled = false;
cboTeacher.Enabled = false;
btnSave.Enabled = false;
btnClose.Enabled = false;
picLoading.Visible = true;
_dataWorker.RunWorkerAsync();
}
// 修改模式 使用者只能修改
if (_action == "管理補考成績")
{
this.Text = "管理補考成績";
txtGroupName.Text = _group.MakeUp_Group;
txtDescription.Text = _group.Description;
btnSwap.Enabled = false;
txtGroupName.Enabled = false;
txtDescription.Enabled = false;
cboTeacher.Enabled = false;
btnSave.Enabled = false;
btnClose.Enabled = false;
txtDate.Enabled = false;
txtTime.Enabled = false;
txtPlace.Enabled = false;
picLoading.Visible = true;
_dataWorker.RunWorkerAsync();
}
}
/// <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
}
}