// 改用 Mandrill Webhook
private void UpdateSendMailStatus()
{
string MandrillAPIKey = string.Empty;
List<UDT.MandrillAPIKey> MandrillAPIKeys = Access.Select<UDT.MandrillAPIKey>();
if (MandrillAPIKeys.Count > 0)
MandrillAPIKey = MandrillAPIKeys.ElementAt(0).APIKey;
else
return;
MandrillApi mandrill = new MandrillApi(MandrillAPIKey, false);
string pong = string.Empty;
try
{
pong = mandrill.Ping();
}
catch (Exception ex)
{
return;
}
if (!pong.ToUpper().Contains("PONG!"))
{
return;
}
string SQL = "select * from $ischool.emba.mail_log where (status ilike 'Sent' or status ilike 'Queued') and ((time_stamp + '7'<update_status_time) or update_status_time is null)";
DataTable dataTable = Query.Select(SQL);
foreach (DataRow x in dataTable.Rows)
{
string Result = x["result"] + "";
if (Result.StartsWith("![CDATA["))
Result = Result.Replace("![CDATA[", "");
if (Result.EndsWith("]]"))
Result = Result.Replace("]]", "");
XElement xElement = XElement.Parse(Result, LoadOptions.None);
if (xElement.Descendants("ResultID") != null)
{
string ResultID = xElement.Descendants("ResultID").ElementAt(0).Value;
Info info = new Info();
info.id = ResultID;
dynamic result = mandrill.SendInfoMessageSync(info);
List<UDT.MailLog> MailLogs = Access.Select<UDT.MailLog>(new List<string>() { x["uid"] + "" });
MailLogs.ElementAt(0).UpdateStatusTime = DateTime.Now;
if ((x["status"] + "").ToLower() != ((string)result.state).ToLower())
{
MailLogs.ElementAt(0).Status = result.state + "";
}
MailLogs.SaveAll();
}
}
}
private void SendEMail(MailLogBase MailLogBase, int AttendNo)
{
dynamic course = (this.cboCourse.Items[this.cboCourse.SelectedIndex] as ComboItem).Tag;
List<string> StudentIDs = new List<string>();
Dictionary<string, Dictionary<int, string>> dicSections = new Dictionary<string, Dictionary<int, string>>();
Dictionary<string, string> dicAttendNos = new Dictionary<string, string>();
List<string> ErrorMessages = new List<string>();
foreach (DataGridViewRow row in this.dg.Rows)
{
if (row.IsNewRow)
continue;
bool is_cancel = false;
if (bool.TryParse(row.Cells[3].Value + "", out is_cancel) && is_cancel)
continue;
string StudentID = row.Tag + "";
bool bChecked = false;
bool.TryParse(row.Cells[4].Value + "", out bChecked);
int attend_no = 0;
int.TryParse(row.Cells[2].Value + "", out attend_no);
if ((attend_no < AttendNo) && bChecked)
ErrorMessages.Add(string.Format("學號「{0}」，姓名「{1}」，缺課次數「{2}」", row.Cells[0].Value + "", row.Cells[1].Value + "", row.Cells[2].Value + ""));
if (!dicAttendNos.ContainsKey(StudentID))
dicAttendNos.Add(StudentID, attend_no.ToString());
if (bChecked && this.dg.Columns.Count > 6)
{
StudentIDs.Add(StudentID);
if (!dicSections.ContainsKey(StudentID))
dicSections.Add(StudentID, new Dictionary<int, string>());
for (int j = 7; j < this.dg.Columns.Count; j++)
{
if (row.Cells[j].Value + "" != "缺")
continue;
object[] sections = this.dg.Columns[j].Tag as object[];
DateTime begin_time = DateTime.Parse(sections[0] + "");
DateTime end_time = DateTime.Parse(sections[1] + "");
string sDate = begin_time.Year.ToString().Length == 4 ? begin_time.ToString("yyyy/MM/dd") : (begin_time.Year + 1911) + "/" + begin_time.Month.ToString("00") + "/" + begin_time.Day.ToString("00");
string bTime =begin_time.Hour.ToString("00") + ":" + begin_time.Minute.ToString("00");
string eTime = end_time.Hour.ToString("00") + ":" + end_time.Minute.ToString("00");
string section_time = sDate + " " + bTime + "~" + eTime;
dicSections[StudentID].Add(j, section_time);
}
}
}
if (ErrorMessages.Count > 0)
throw new Exception(string.Format("待寄出之缺課通知為缺課{0}次通知，但下列學生缺課未達寄發之缺課次數：\n\n" + string.Join("\n", ErrorMessages), AttendNo));
MandrillApi mandrill = new MandrillApi(this.MandrillAPIKey, false);
string pong = string.Empty;
try
{
pong = mandrill.Ping();
}
catch (Exception ex)
{
MessageBox.Show("未正確設定「MandrillAPIKey」, 錯誤訊息：" + ex.Message);
this.btnSend.Enabled = true;
this.btnSend2.Enabled = true;
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
return;
}
if (!pong.ToUpper().Contains("PONG!"))
{
MessageBox.Show("未正確設定「MandrillAPIKey」。");
this.btnSend.Enabled = true;
this.btnSend2.Enabled = true;
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
return;
}
Task<List<UDT.MailLog>> task = Task < List<UDT.MailLog>>.Factory.StartNew(() =>
{
List<UDT.MailLog> MandrillSendLogs = new List<UDT.MailLog>();
this.SendEmails(StudentIDs, course, dicSections, dicAttendNos, MailLogBase, mandrill, MandrillSendLogs);
return MandrillSendLogs;
});
task.ContinueWith((x) =>
{
this.btnSend.Enabled = true;
this.btnSend2.Enabled = true;
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
if (x.Exception != null)
MessageBox.Show(x.Exception.InnerException.Message);
else
{
x.Result.SaveAll();
this.UpdateLogShow(x.Result);
MessageBox.Show("已寄出缺課通知。");
}
}, TaskScheduler.FromCurrentSynchronizationContext());
}
private void SendEmail_Click(int no)
{
if (this.dgvData.SelectedRows.Count < 1)
{
MessageBox.Show("請選擇要發送 Email 通知的紀錄。", "提示");
return;
}
if (string.IsNullOrWhiteSpace(this.txtSenderEMail.Text) || string.IsNullOrWhiteSpace(this.txtSenderName.Text))
{
MessageBox.Show("請輸入寄件人 Email 及名稱。");
return;
}
MandrillApi mandrill = new MandrillApi(this.MandrillAPIKey, false);
string pong = string.Empty;
try
{
pong = mandrill.Ping();
}
catch (Exception ex)
{
MessageBox.Show("未正確設定「MandrillAPIKey」, 錯誤訊息：" + ex.Message);
return;
}
if (!pong.ToUpper().Contains("PONG!"))
{
MessageBox.Show("請先設定「MandrillAPIKey」。");
return;
}
////彈出畫面指定寄件者
//if (string.IsNullOrWhiteSpace(this.SenderEmail))
//{
// Forms.Email_Credential frmCred = new Email_Credential();
// frmCred.AfterInputCredential += new Email_Credential.InputCredentialHandler(frmCred_AfterInputCredential);
// frmCred.ShowDialog();
//}
////如果還是沒有指定寄件者，則離開
//if (string.IsNullOrWhiteSpace(this.SenderEmail))
//{
// MessageBox.Show("未指定寄件者。", "提示");
// return;
//}
this.guid = Guid.NewGuid().ToString();
List<UDT.AssignedSurvey> AssignedSurveys = new List<UDT.AssignedSurvey>();
Dictionary<UDT.AssignedSurvey, dynamic> dicEmailInfos = new Dictionary<UDT.AssignedSurvey, dynamic>();
//string error_message = string.Empty;
int SchoolYear = int.Parse(this.nudSchoolYear.Value.ToString());
int Semester = int.Parse(this.cboSemester.SelectedValue + "");
foreach (DataGridViewRow row in this.dgvData.SelectedRows)
{
UDT.AssignedSurvey AssignedSurvey = row.Tag as UDT.AssignedSurvey;
if (AssignedSurvey == null)
continue;
//if (AssignedSurvey == null)
// error_message += string.Format("開課「{0}」，授課教師「{1}」。\n", row.Cells[0].Value + "", row.Cells[1].Value + "");
AssignedSurveys.Add(AssignedSurvey);
dynamic o = new ExpandoObject();
o.CourseID = AssignedSurvey.CourseID;
o.CourseName = row.Cells[3].Value + "";
o.TeacherID = AssignedSurvey.TeacherID;
o.TeacherName = row.Cells[4].Value + "";
o.SubjectID = row.Cells[0].Tag + "";
o.SubjectName = row.Cells[0].Value + "";
o.SchoolYear = SchoolYear;
o.Semester = TeachingEvaluation.DataItems.SemesterItem.GetSemesterByCode(Semester.ToString()).Name;
o.SurveyBeginTime = row.Cells[5].Value + "";
o.SurveyEndTime = row.Cells[6].Value + "";
o.EmailList = new Dictionary<StudentRecord, IEnumerable<string>>();
dicEmailInfos.Add(AssignedSurvey, o);
}
//if (!string.IsNullOrEmpty(error_message))
//{
// MessageBox.Show("下列授課教師尚未設定評鑑組態：\n\n" + error_message, "錯誤");
// return;
//}
this.from_email = this.txtSenderEMail.Text.Trim();
this.from_name = this.txtSenderName.Text.Trim();
List<string> email_lists = new List<string>();
Dictionary<string, List<string>> error_email_lists = new Dictionary<string, List<string>>();
this.circularProgress.Visible = true;
this.circularProgress.IsRunning = true;
this.btnSendEmail.Enabled = false;
this.btnSendSecondEmail.Enabled = false;
Task task = Task.Factory.StartNew(() =>
{
// 評鑑開課
List<int> CourseIDs = new List<int>();
// 修課學生
Dictionary<string, List<string>> dicSCAttends = new Dictionary<string, List<string>>();
// 待寄通知學生
Dictionary<UDT.AssignedSurvey, IEnumerable<string>> dicStudentIDs = new Dictionary<UDT.AssignedSurvey, IEnumerable<string>>();
List<StudentRecord> Students = Student.SelectAll();
Dictionary<string, StudentRecord> dicStudents = new Dictionary<string, StudentRecord>();
if (Students.Count > 0)
dicStudents = Students.ToDictionary(x => x.ID);
foreach (UDT.AssignedSurvey AssignedSurvey in AssignedSurveys)
{
CourseIDs.Add(AssignedSurvey.CourseID);
}
// 取得修課學生
DataTable dataTable = Query.Select(string.Format(@"select student.id as student_id, course.id as course_id from student join $ischool.emba.scattend_ext as se on se.ref_student_id=student.id join course on course.id=se.ref_course_id where se.is_cancel=false and course.id in ({0})", string.Join(",", CourseIDs)));
foreach (DataRow row in dataTable.Rows)
{
if (!dicSCAttends.ContainsKey(row["course_id"] + ""))
dicSCAttends.Add(row["course_id"] + "", new List<string>());
dicSCAttends[row["course_id"] + ""].Add(row["student_id"] + "");
}
// 取得評鑑回覆學生清單(已回覆者不需Email通知)
List<UDT.Reply> Replys = Access.Select<UDT.Reply>("status=1");
foreach (UDT.AssignedSurvey AssignedSurvey in dicEmailInfos.Keys)
{
List<string> reply_student_ids = new List<string>();
reply_student_ids = Replys.Where(x => (x.CourseID == AssignedSurvey.CourseID && x.TeacherID == AssignedSurvey.TeacherID)).Select(x => x.StudentID.ToString()).ToList();
if (!dicSCAttends.ContainsKey(AssignedSurvey.CourseID.ToString()))
continue;
IEnumerable<string> need_mail_student_ids = new List<string>();
need_mail_student_ids = dicSCAttends[AssignedSurvey.CourseID.ToString()].Except(reply_student_ids);
dicStudentIDs.Add(AssignedSurvey, need_mail_student_ids);
}
// 取得需Email通知之學生的Email
List<string> all_need_mail_student_ids = new List<string>();
dicStudentIDs.Values.ToList().ForEach((x => all_need_mail_student_ids.AddRange(x)));
if (all_need_mail_student_ids.Count == 0)
throw new Exception("所有學生皆已回覆，不需 Email 通知。");
// 每個學生的電子郵件
Dictionary<string, IEnumerable<string>> dicStudentEmails = new Dictionary<string, IEnumerable<string>>();
dataTable = Query.Select(string.Format(@"Select student.id as 學生系統編號, student.student_number as 學號, student.name as 學生姓名, student.sa_login_name as 登入帳號, (xpath_string('<root>' || sb2.email_list || '</root>','email1')) as 電子郵件一, (xpath_string('<root>' || sb2.email_list || '</root>','email2')) as 電子郵件二, (xpath_string('<root>' || sb2.email_list || '</root>','email3')) as 電子郵件三, (xpath_string('<root>' || sb2.email_list || '</root>','email4')) as 電子郵件四,
(xpath_string('<root>' || sb2.email_list || '</root>','email5')) as 電子郵件五 From student Left join $ischool.emba.student_brief2 as sb2 on sb2.ref_student_id = student.id Where student.id in ({0})", string.Join(",", all_need_mail_student_ids)));
string error_email_alert_message = string.Empty;
foreach (DataRow row in dataTable.Rows)
{
List<string> student_emails = new List<string>();
if (!dicStudentEmails.ContainsKey(row["學生系統編號"] + ""))
dicStudentEmails.Add(row["學生系統編號"] + "", new List<string>());
if (!string.IsNullOrWhiteSpace(row["登入帳號"] + ""))
{
if (isValidEmail(row["登入帳號"] + ""))
student_emails.Add((row["登入帳號"] + "").Trim());
else
error_email_alert_message += string.Format("學號「{0}」，姓名「{1}」，電子郵件「{2}」\n", row["學號"] + "", row["學生姓名"] + "", row["登入帳號"] + "");
}
if (!string.IsNullOrWhiteSpace(row["電子郵件一"] + ""))
{
if (isValidEmail(row["電子郵件一"] + ""))
student_emails.Add((row["電子郵件一"] + "").Trim());
else
error_email_alert_message += string.Format("學號「{0}」，姓名「{1}」，電子郵件「{2}」\n", row["學號"] + "", row["學生姓名"] + "", row["電子郵件一"] + "");
}
if (!string.IsNullOrWhiteSpace(row["電子郵件二"] + ""))
{
if (isValidEmail(row["電子郵件二"] + ""))
student_emails.Add((row["電子郵件二"] + "").Trim());
else
error_email_alert_message += string.Format("學號「{0}」，姓名「{1}」，電子郵件「{2}」\n", row["學號"] + "", row["學生姓名"] + "", row["電子郵件二"] + "");
}
if (!string.IsNullOrWhiteSpace(row["電子郵件三"] + ""))
{
if (isValidEmail(row["電子郵件三"] + ""))
student_emails.Add((row["電子郵件三"] + "").Trim());
else
error_email_alert_message += string.Format("學號「{0}」，姓名「{1}」，電子郵件「{2}」\n", row["學號"] + "", row["學生姓名"] + "", row["電子郵件三"] + "");
}
if (!string.IsNullOrWhiteSpace(row["電子郵件四"] + ""))
{
if (isValidEmail(row["電子郵件四"] + ""))
student_emails.Add((row["電子郵件四"] + "").Trim());
else
error_email_alert_message += string.Format("學號「{0}」，姓名「{1}」，電子郵件「{2}」\n", row["學號"] + "", row["學生姓名"] + "", row["電子郵件四"] + "");
}
if (!string.IsNullOrWhiteSpace(row["電子郵件五"] + ""))
{
if (isValidEmail(row["電子郵件五"] + ""))
student_emails.Add((row["電子郵件五"] + "").Trim());
else
error_email_alert_message += string.Format("學號「{0}」，姓名「{1}」，電子郵件「{2}」\n", row["學號"] + "", row["學生姓名"] + "", row["電子郵件五"] + "");
}
dicStudentEmails[row["學生系統編號"] + ""] = student_emails.Distinct();
}
if (!string.IsNullOrEmpty(error_email_alert_message))
throw new Exception("下列學生及其電子郵件格式有誤，請先修正：\n\n" + error_email_alert_message);
foreach (UDT.AssignedSurvey AssignedSurvey in dicEmailInfos.Keys)
{
dynamic o = dicEmailInfos[AssignedSurvey];
Dictionary<StudentRecord, IEnumerable<string>> dicEmailLists = new Dictionary<StudentRecord, IEnumerable<string>>();
IEnumerable<string> need_email_student_ids = dicStudentIDs[AssignedSurvey];
foreach (string student_id in need_email_student_ids)
{
if (dicStudentEmails.ContainsKey(student_id))
dicEmailLists.Add(dicStudents[student_id], dicStudentEmails[student_id]);
}
o.EmailList = dicEmailLists;
}
this.SendMail(dicEmailInfos, no, mandrill);
});
task.ContinueWith((x) =>
{
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
this.btnSendEmail.Enabled = true;
this.btnSendSecondEmail.Enabled = true;
if (x.Exception != null)
{
MessageBox.Show(x.Exception.InnerException.Message);
return;
}
this.UpdateEmailingTimes(no);
MessageBox.Show("完成發送 Email 提醒通知，將開啟 Log 記錄。");
(new frmEMailingLog(this.guid)).ShowDialog();
}, System.Threading.CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext());
}
private void SendEMail(MailLogBase MailLogBase)
{
dynamic course = (this.cboCourse.Items[this.cboCourse.SelectedIndex] as ComboItem).Tag;
List<string> StudentIDs = new List<string>();
Dictionary<string, Dictionary<int, string>> dicSections = new Dictionary<string, Dictionary<int, string>>();
Dictionary<string, string> dicAttendNos = new Dictionary<string, string>();
Dictionary<int, object> CheckedColumns = new Dictionary<int, object>();
foreach(DataGridViewColumn column in this.dg.Columns)
{
if (column.HeaderCell.GetType().Name != "DatagridViewCheckBoxHeaderCell")
continue;
if ((column.HeaderCell as DatagridViewCheckBoxHeaderCell).Checked)
CheckedColumns.Add(column.Index, column.Tag);
}
if (CheckedColumns.Count == 0)
throw new Exception("請先勾選日期。");
foreach (DataGridViewRow row in this.dg.Rows)
{
if (row.IsNewRow)
continue;
bool is_cancel = false;
if (bool.TryParse(row.Cells[3].Value + "", out is_cancel) && is_cancel)
continue;
string StudentID = row.Tag + "";
int attend_no = 0;
bool dirty = false;
foreach (int j in CheckedColumns.Keys)
{
if (string.IsNullOrEmpty(row.Cells[j].Value + ""))
continue;
dirty = true;
if (!dicSections.ContainsKey(StudentID))
dicSections.Add(StudentID, new Dictionary<int, string>());
object[] sections = this.dg.Columns[j].Tag as object[];
DateTime begin_time = DateTime.Parse(sections[0] + "");
DateTime end_time = DateTime.Parse(sections[1] + "");
string sDate = begin_time.Year.ToString().Length == 4 ? begin_time.ToString("yyyy/MM/dd") : (begin_time.Year + 1911) + "/" + begin_time.Month.ToString("00") + "/" + begin_time.Day.ToString("00");
string bTime = begin_time.Hour.ToString("00") + ":" + begin_time.Minute.ToString("00");
string eTime = end_time.Hour.ToString("00") + ":" + end_time.Minute.ToString("00");
string section_time = sDate + " " + bTime + "~" + eTime + ((row.Cells[j].Value + "").Contains("補") ? "(補)" : "");
dicSections[StudentID].Add(j, section_time);
if (!(row.Cells[j].Value + "").Contains("補"))
attend_no += 1;
}
if (dirty)
{
StudentIDs.Add(StudentID);
if (!dicAttendNos.ContainsKey(StudentID))
dicAttendNos.Add(StudentID, attend_no.ToString());
}
}
if (StudentIDs.Count == 0)
throw new Exception("勾選日期沒有學生缺(補)課。");
MandrillApi mandrill = new MandrillApi(this.MandrillAPIKey, false);
string pong = string.Empty;
try
{
pong = mandrill.Ping();
}
catch (Exception ex)
{
MessageBox.Show("未正確設定「MandrillAPIKey」, 錯誤訊息：" + ex.Message);
this.btnSend.Enabled = true;
this.btnSend2.Enabled = true;
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
return;
}
if (!pong.ToUpper().Contains("PONG!"))
{
MessageBox.Show("未正確設定「MandrillAPIKey」。");
this.btnSend.Enabled = true;
this.btnSend2.Enabled = true;
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
return;
}
Task<List<UDT.MailLog>> task = Task<List<UDT.MailLog>>.Factory.StartNew(() =>
{
List<UDT.MailLog> MandrillSendLogs = new List<UDT.MailLog>();
this.SendEmails(StudentIDs, course, dicSections, dicAttendNos, MailLogBase, mandrill, MandrillSendLogs);
return MandrillSendLogs;
});
task.ContinueWith((x) =>
{
this.btnSend.Enabled = true;
this.btnSend2.Enabled = true;
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
if (x.Exception != null)
MessageBox.Show(x.Exception.InnerException.Message);
else
{
x.Result.SaveAll();
this.UpdateLogShow(x.Result);
MessageBox.Show("已寄出缺課通知。");
}
}, TaskScheduler.FromCurrentSynchronizationContext());
}
