private void BGW_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
BgwArgument arg = (BgwArgument)e.Result;
if (arg.Success)
{
MsgBox.Show(arg.Message);
ReloadDataGridView(arg.SchoolYear, arg.Semester, arg.Type);
}
else
{
MsgBox.Show(arg.Message);
}
#region Button
{
cbxSchoolYear.Enabled = true;
cbxSemester.Enabled = true;
cbxCoursePeriod.Enabled = true;
btnUnOpen.Enabled = true;
btnSelected.Enabled = true;
btnBlock.Enabled = true;
btnRecover.Enabled = true;
btnLeave.Enabled = true;
btnSave.Enabled = true;
progressBarX1.Visible = false;
}
#endregion
}
private void btnSave_Click(object sender, EventArgs e)
{
#region Button
{
cbxSchoolYear.Enabled = false;
cbxSemester.Enabled = false;
cbxCoursePeriod.Enabled = false;
btnUnOpen.Enabled = false;
btnSelected.Enabled = false;
btnBlock.Enabled = false;
btnRecover.Enabled = false;
btnLeave.Enabled = false;
btnSave.Enabled = false;
progressBarX1.Visible = true;
}
#endregion
BgwArgument arg = new BgwArgument();
arg.SchoolYear = cbxSchoolYear.SelectedItem.ToString();
arg.Semester = cbxSemester.SelectedItem.ToString();
arg.Type = cbxCoursePeriod.SelectedItem.ToString();
this._bgw.RunWorkerAsync(arg);
}
private void BGW_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bgw = sender as BackgroundWorker;
BgwArgument arg = (BgwArgument)e.Argument;
List <string> listDataRow = new List <string>();
#region 資料整理
{
foreach (DataGridViewRow dgvrow in dataGridViewX1.Rows)
{
Attend attend = dgvrow.Tag as Attend;
for (int i = 3; i <= 12; i++)
{
Wish wish = dgvrow.Cells[i].Tag as Wish;
// 如果有志願
if (!string.IsNullOrEmpty(wish.RefSubjectID))
{
string data = string.Format(@"
SELECT
{0}::BIGINT AS ref_student_id
, {1}::BIGINT AS ref_subject_id
, {2}::BOOLEAN AS is_cancel
, '{3}'::TEXT AS cancel_reason
, '{4}'::TEXT AS cancel_by
, now() AS cancel_time
", attend.RefStudentID, wish.RefSubjectID, wish.IsCancel, wish.CancelReason, this._userAccount);
listDataRow.Add(data);
}
}
}
}
#endregion
#region SQL
string sql = string.Format(@"
WITH data_row AS(
{0}
), origin_data AS(
SELECT
ss_wish.*
FROM
$ischool.course_selection.ss_wish AS ss_wish
INNER JOIN data_row
ON data_row.ref_student_id = ss_wish.ref_student_id
AND data_row.ref_subject_id = ss_wish.ref_subject_id
), update_data AS(
UPDATE $ischool.course_selection.ss_wish SET
is_cancel = data_row.is_cancel
, cancel_reason = data_row.cancel_reason
, cancel_by = data_row.cancel_by
, cancel_time = data_row.cancel_time
FROM
data_row
WHERE
$ischool.course_selection.ss_wish.ref_student_id = data_row.ref_student_id
AND $ischool.course_selection.ss_wish.ref_subject_id = data_row.ref_subject_id
RETURNING $ischool.course_selection.ss_wish.*
)
SELECT
student.id
, class.class_name
, student.seat_no
, student.name
, subject.subject_name
, origin_data.sequence
, origin_data.is_cancel AS origin_is_cancel
, origin_data.cancel_reason AS origin_cancel_reason
, update_data.is_cancel AS update_is_cancel
, update_data.cancel_reason AS update_cancel_reason
FROM
origin_data
LEFT OUTER JOIN $ischool.course_selection.subject AS subject
ON subject.uid = origin_data.ref_subject_id
LEFT OUTER JOIN update_data
ON update_data.ref_student_id = origin_data.ref_student_id
AND update_data.ref_subject_id = origin_data.ref_subject_id
LEFT OUTER JOIN student
ON student.id = origin_data.ref_student_id
LEFT OUTER JOIN class
ON class.id = student.ref_class_id
ORDER BY
class.class_name
, student.seat_no
, origin_data.sequence
", string.Join(" UNION ALL ", listDataRow), this._userAccount, this._clientInfo, arg.SchoolYear, arg.Semester);
#endregion
try
{
bgw.ReportProgress(10);
// 取回更新資料
DataTable dt = this._qh.Select(sql);
bgw.ReportProgress(50);
Dictionary <string, Log> dicLogByStudentID = new Dictionary <string, Log>();
#region 整理LOG紀錄
{
foreach (DataRow row in dt.Rows)
{
// 已學生為單位收集LOG資料
if (!dicLogByStudentID.ContainsKey("" + row["id"]))
{
dicLogByStudentID.Add("" + row["id"], new Log());
dicLogByStudentID["" + row["id"]].ListWishLog = new List <DataRow>();
}
dicLogByStudentID["" + row["id"]].ClassName = "" + row["class_name"];
dicLogByStudentID["" + row["id"]].SeatNo = "" + row["seat_no"];
dicLogByStudentID["" + row["id"]].StudentName = "" + row["name"];
dicLogByStudentID["" + row["id"]].ListWishLog.Add(row);
}
}
#endregion
bgw.ReportProgress(60);
StringBuilder description = new StringBuilder();
description.AppendLine(string.Format("學年度{0} 學期{1} 「學生選課調整」: "
, arg.SchoolYear
, arg.Semester));
foreach (string studentID in dicLogByStudentID.Keys)
{
Log log = dicLogByStudentID[studentID];
description.AppendLine(string.Format("班級「{0}」座號「{1}」姓名「{2}」: ", log.ClassName, log.SeatNo, log.StudentName));
foreach (DataRow row in dicLogByStudentID[studentID].ListWishLog)
{
description.AppendLine(string.Format("志願「{0}」 科目名稱「{1}」 取消「{2}」變更為「{3}」 取消原因「{4}」變更為「{5}」",
"" + row["sequence"], "" + row["subject_name"], "" + row["origin_is_cancel"], "" + row["update_is_cancel"]
, "" + row["origin_cancel_reason"], "" + row["update_cancel_reason"]));
}
description.AppendLine("");
}
#region SQL
sql = string.Format(@"
INSERT INTO log(
actor
, action_type
, action
, target_category
, server_time
, client_info
, action_by
, description
)
VALUES(
'{0}'
, 'Record'
, '選課志願調整'
, 'ss_wish'
, now()
, '{1}'
, '選課志願調整'
, '{2}'
) ", this._userAccount, this._clientInfo, description.ToString());
#endregion
bgw.ReportProgress(70);
this._up.Execute(sql);
bgw.ReportProgress(100);
arg.Success = true;
arg.Message = "儲存成功!";
e.Result = arg;
}
catch (Exception ex)
{
arg.Success = false;
arg.Message = "儲存失敗:" + ex.Message;
e.Result = arg;
}
}
