private void dgWeeklyReport_SelectionChanged(object sender, EventArgs e)
{
txtGeneralComment.Text = "";
SelectedWeeklyReportUID = "";
SelectdBeginDate = SelectdEndDate = "";
if (dgWeeklyReport.SelectedRows.Count > 0)
{
WeeklyReportLogRecord rec = dgWeeklyReport.SelectedRows[0].Tag as WeeklyReportLogRecord;
if (rec != null)
{
txtGeneralComment.Text = rec.GeneralComment;
SelectedWeeklyReportUID = rec.UID;
oldGeneralComment = rec.GeneralComment;
SelectdBeginDate = rec.BeginDate;
SelectdEndDate = rec.EndDate;
LoadLoadWeeklyDataByReportID(rec);
}
}
}
// 儲存
private void buttonX1_Click(object sender, EventArgs e)
{
if (dateTimeInput1.Text == "" || dateTimeInput2.Text == "")
{
MessageBox.Show("請輸入完整日期區間!");
return;
}
if (dateTimeInput1.Value > dateTimeInput2.Value)
{
MessageBox.Show("結束日期必須大於開始日期!");
return;
}
dataGridViewX1.Rows.Clear();
WeeklyReportLogDict.Clear();
// 選出套用ESL樣板的課程 檢查時間區段裡 老師是否有建立weekly_report
string query = string.Format(@"SELECT
course.id AS course_id
,course.course_name
,exam_template.description
,teacher.id AS teacher_id
,teacher.teacher_name
,tc_instruct.sequence
,exam_template.id AS templateID
,exam_template.name AS templateName
,$esl.weekly_report.begin_date
,$esl.weekly_report.end_date
FROM course
LEFT JOIN exam_template ON course.ref_exam_template_id =exam_template.id
LEFT JOIN tc_instruct ON tc_instruct.ref_course_id =course.id
LEFT JOIN teacher ON teacher.id = tc_instruct.ref_teacher_id
LEFT JOIN $esl.weekly_report ON $esl.weekly_report.ref_course_id =course.id AND $esl.weekly_report.ref_teacher_id = teacher.id
WHERE exam_template.description IS NOT NULL
AND course.school_year = '{0}'
AND semester = '{1}'
AND (end_date >= TIMESTAMP '{2}' AND end_date<= TIMESTAMP '{3}' OR end_date IS NULL)
ORDER BY course_name,sequence", comboBoxEx1.Text, comboBoxEx2.Text, dateTimeInput1.Value.Date.ToShortDateString(), dateTimeInput2.Value.Date.ToShortDateString());
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(query);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (!WeeklyReportLogDict.ContainsKey("" + dr["course_id"] + "_" + dr["teacher_id"]))
{
WeeklyReportLogRecord lr = new WeeklyReportLogRecord();
lr.CourseID = "" + dr["course_id"];
lr.CourseName = "" + dr["course_name"];
lr.BeginDate = "" + dr["begin_date"] != "" ? DateTime.Parse("" + dr["begin_date"]).ToString("yyyy/MM/dd"):"";
lr.EndDate = "" + dr["end_date"] != "" ? DateTime.Parse("" + dr["end_date"]).ToString("yyyy/MM/dd") :"";
lr.TeacherID = "" + dr["teacher_id"];
lr.TeacherName = "" + dr["teacher_name"];
// 沒有時間 代表 本周沒有建立WeeklyReport
if ("" + dr["end_date"] != "")
{
lr.WeeklyReportCount = 1;
}
else
{
lr.WeeklyReportCount = 0;
}
WeeklyReportLogDict.Add("" + dr["course_id"] + "_" + dr["teacher_id"], lr);
}
else
{
WeeklyReportLogDict["" + dr["course_id"] + "_" + dr["teacher_id"]].WeeklyReportCount++;
}
}
foreach (string key in WeeklyReportLogDict.Keys)
{
DataGridViewRow row = new DataGridViewRow();
row.CreateCells(dataGridViewX1);
row.Cells[0].Value = WeeklyReportLogDict[key].CourseName;
row.Cells[1].Value = WeeklyReportLogDict[key].TeacherName;
row.Cells[2].Value = WeeklyReportLogDict[key].BeginDate;
row.Cells[3].Value = WeeklyReportLogDict[key].EndDate;
row.Cells[4].Value = WeeklyReportLogDict[key].WeeklyReportCount == 0 ? "本周尚未建立" : "本周已建立" + WeeklyReportLogDict[key].WeeklyReportCount + "筆";
// 如果沒有建立 標紅色
if (WeeklyReportLogDict[key].WeeklyReportCount == 0)
{
DataGridViewCellStyle s = new DataGridViewCellStyle();
s.ForeColor = Color.Red;
row.Cells[4].Style = s;
}
dataGridViewX1.Rows.Add(row);
}
}
}
/// <summary>
/// 透過 ReportID 取得相關 Weekly Data
/// </summary>
/// <param name="rptID"></param>
private void LoadLoadWeeklyDataByReportID(WeeklyReportLogRecord rec)
{
if (!string.IsNullOrWhiteSpace(rec.UID))
{
// 取得這份 Weekly Report 關聯的學生與 Personal Comment
string SQL = "SELECT " +
"$esl.weekly_data.uid" +
",student.id AS student_id" +
",class.class_name" +
",student.seat_no" +
",student.name AS student_name" +
",personal_comment " +
",student.student_number AS student_number" +
",student.english_name AS english_name" +
" FROM $esl.weekly_data " +
"INNER JOIN student " +
" ON student.id = $esl.weekly_data.ref_student_id " +
" LEFT JOIN class ON student.ref_class_id = class.id " +
" WHERE $esl.weekly_data.ref_weekly_report_uid =" + rec.UID +
" ORDER BY student_number,class.display_order,class_name,seat_no,student.name;";
WeeklyDataInfoList.Clear();
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(SQL);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
WeeklyDataInfo wdi = new WeeklyDataInfo();
wdi.UID = "" + dr["uid"];
wdi.ClassName = "" + dr["class_name"];
wdi.CourseID = rec.CourseID;
wdi.TeacherID = rec.TeacherID;
wdi.StudentID = "" + dr["student_id"];
wdi.PersonalComment = "" + dr["personal_comment"];
wdi.SeatNo = 0;
int seatNo;
if (int.TryParse("" + dr["seat_no"], out seatNo))
{
wdi.SeatNo = seatNo;
}
wdi.StudentName = "" + dr["student_name"];
wdi.StudentNumber = "" + dr["student_number"];
wdi.EnglishName = "" + dr["english_name"];
WeeklyDataInfoList.Add(wdi);
}
}
// Load To DataGrid
dgStudentData.Rows.Clear();
txtTeacherComment.Text = "";
foreach (WeeklyDataInfo data in WeeklyDataInfoList)
{
int rowIdx = dgStudentData.Rows.Add();
dgStudentData.Rows[rowIdx].Tag = data;
dgStudentData.Rows[rowIdx].Cells[colClassName.Index].Value = data.ClassName;
dgStudentData.Rows[rowIdx].Cells[colSeatNo.Index].Value = data.SeatNo;
dgStudentData.Rows[rowIdx].Cells[colName.Index].Value = data.StudentName;
dgStudentData.Rows[rowIdx].Cells[colStudentNumber.Index].Value = data.StudentNumber;
dgStudentData.Rows[rowIdx].Cells[colEnglishName.Index].Value = data.EnglishName;
}
if (WeeklyDataInfoList.Count > 0)
{
dgStudentData.Rows[0].Selected = true;
txtTeacherComment.Text = WeeklyDataInfoList[0].PersonalComment;
SelectedWeeklyDataUID = WeeklyDataInfoList[0].UID;
oldTeacherComment = txtTeacherComment.Text;
LoadBehaviorData(WeeklyDataInfoList[0].StudentID, WeeklyDataInfoList[0].CourseID, WeeklyDataInfoList[0].TeacherID);
}
}
}
private void btnDel_Click(object sender, EventArgs e)
{
// 刪除
btnDel.Enabled = false;
if (dataGridViewX1.Rows.Count > 0)
{
if (dataGridViewX1.SelectedRows.Count == 1)
{
List <string> delUID = new List <string>();
WeeklyReportLogRecord ddR = null;
if (dataGridViewX1.SelectedRows[0].Tag != null)
{
string rKey = dataGridViewX1.SelectedRows[0].Tag.ToString();
if (WeeklyReportDataDict.ContainsKey(rKey))
{
foreach (WeeklyReportLogRecord rec in WeeklyReportDataDict[rKey])
{
ddR = rec;
delUID.Add(rec.UID);
}
}
if (delUID.Count > 0)
{
if (MsgBox.Show("按「是」將刪除" + delUID.Count + " 筆資料，請問確認刪除?", "刪除資料", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
try
{
// del SQL，先刪除子資料在刪除主資料
//DELETE FROM $esl.weekly_data WHERE ref_weekly_report_uid IN(111);
//DELETE FROM $esl.weekly_report WHERE UID IN(111);
string uid = string.Join(",", delUID.ToArray());
List <string> sqlList = new List <string>();
sqlList.Add("DELETE FROM $esl.weekly_data WHERE ref_weekly_report_uid IN(" + uid + ");");
sqlList.Add("DELETE FROM $esl.weekly_report WHERE UID IN(" + uid + ");");
// update
UpdateHelper uh = new UpdateHelper();
uh.Execute(sqlList);
if (ddR != null)
{
// 寫 Log
FISCA.LogAgent.ApplicationLog.Log("ESL 檢視週報表", "刪除資料", "course", ddR.CourseID, "刪除 Weekly Report 資料： CourseName:" + ddR.CourseName + ", TeacherName:" + ddR.TeacherName + ", CourseID:" + ddR.CourseID + ", TeacherID:" + ddR.TeacherID + "。");
}
MsgBox.Show("刪除完成");
LoadSearchData();
}
catch (Exception ex)
{
MsgBox.Show("刪除資料發生錯誤", ex.Message);
}
}
}
}
}
}
btnDel.Enabled = true;
}
