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; }