/// <summary> /// 删除数据 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Delete(MailJob entity) { string sql = "DELETE FROM mail_job WHERE mailJobId=@mailJobId"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@mailJobId", entity.mailJobId); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
public const string mysqlConnection = DBConstant.mysqlConnection;//"User Id=root;Host=115.29.229.134;Database=chinaunion;password=c513324665;charset=utf8"; /// <summary> /// 添加数据 /// </summary> /// <returns></returns> public int Add(MailJob entity) { string sql = "INSERT INTO mail_job (mailJobId,feeMonth, subject, sendTime) VALUE (@mailJobId,@feeMonth,@subject, @sendTime)"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@mailJobId", entity.mailJobId); command.Parameters.AddWithValue("@feeMonth", entity.feeMonth); command.Parameters.AddWithValue("@subject", entity.subject); command.Parameters.AddWithValue("@sendTime", entity.sendTime); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 异步 开始事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void worker_DoWork(object sender, DoWorkEventArgs e) { //需要执行的代码 Cursor.Current = Cursors.WaitCursor; if (dgAgentFee.RowCount <= 0) { return; } try{ MailData mailData = new MailData(); StringBuilder sbAgent = new StringBuilder(); worker.ReportProgress(1, "准备数据...\r\n"); WechatAction wechatAction = new WechatAction(); for (int i = 0; i < dgAgentFee.RowCount; i++) { if (dgAgentFee[4, i].Value == null) { continue; } if (String.IsNullOrEmpty(dgAgentFee[4, i].Value.ToString())) { continue; } StringBuilder sb = new StringBuilder(); String url = String.Format(Settings.Default.Wechat_Message, dgAgentFee[0, i].Value.ToString(), feeMonth); wechatAction.sendTextMessageToWechat(dgAgentFee[0, i].Value.ToString(), feeMonth + url, Settings.Default.Wechat_Secret, Settings.Default.Wechat_Agent_AppId); sb.Append("agent_no#").Append(dgAgentFee[0, i].Value == null ? "" : dgAgentFee[0, i].Value.ToString()).Append(","); sb.Append("agent_name#").Append(dgAgentFee[1, i].Value == null ? "" : dgAgentFee[1, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(","); sb.Append("agent_type#").Append(dgAgentFee[2, i].Value == null ? "" : dgAgentFee[2, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(","); sb.Append("agent_type_comment#").Append(dgAgentFee[3, i].Value == null ? "" : dgAgentFee[3, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(","); sb.Append("email#").Append(dgAgentFee[4, i].Value == null ? "" : dgAgentFee[4, i].Value.ToString()); mailData.ContactJsonList.Add(sb.ToString()); } String client = Settings.Default.TripolisClient; String userName = Settings.Default.TripoisUserName; String password = Settings.Default.TripolisPassword; mailData.fromAddress = Settings.Default.MailFromAddress; mailData.replyAddress = Settings.Default.MailReplyAddress; mailData.sender = Settings.Default.MailSender; mailData.subject = this.subject; ChinaUnionAdapter mailAdapter = new ChinaUnionAdapter(client, userName, password, null); String databaseId = Settings.Default.TripolisDBId; String workspaceId = Settings.Default.TripolisWorkspaceId; String emailId = Settings.Default.TripolisDirectEmailId; worker.ReportProgress(1, "发送邮件...\r\n"); String message = mailAdapter.sendBatchMail(databaseId, workspaceId, emailId, mailData, this.dtFeeMonth.Value); if (message.Contains("OK:")) { String mailJobId = message.Substring(3); MailJob mailJob = new MailJob(); mailJob.feeMonth = feeMonth; mailJob.mailJobId = mailJobId; mailJob.subject = mailData.subject; mailJob.sendTime = this.dtFeeMonth.Value.ToString("yyyy-MM-dd hh:mm:ss"); MailJobDao mailJobDao = new MailJobDao(); mailJobDao.Delete(mailJob); mailJobDao.Add(mailJob); // WechatAction wechatAction = new WechatAction(); // wechatAction.sendMessageToWechat("@all",this.dtFeeMonth.Value.ToString("yyyy-MM") + Settings.Default.Wechat_Message); MessageBox.Show("邮件重新发送成功"); } else { MessageBox.Show(message); } } catch (Exception ex) { MessageBox.Show(ex.Message); } Cursor.Current = Cursors.Default; }
/// <summary> /// 根据主键查询 /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> //public Agent Get(int primaryKey) //{ // string sql = "SELECT userid,userNickName FROM cimuser where userid=@userid"; // using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) // { // mycn.Open(); // MySqlCommand command = new MySqlCommand(sql, mycn); // command.Parameters.AddWithValue("@userid", primaryKey); // MySqlDataReader reader = command.ExecuteReader(); // Agent userBase = null; // if (reader.Read()) // { // userBase = new Agent(); // userBase.UserId = Convert.ToInt32(reader["userid"]); // userBase.UserNickName = reader["userNickName"] == DBNull.Value ? null : reader["userNickName"].ToString(); // } // return userBase; // } //} /// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<MailJob> GetList(String feeMonth) { string sql = "SELECT mailJobId,feeMonth, subject, sendTime FROM mail_job "; if (!String.IsNullOrEmpty(feeMonth)) { sql = sql + " where feeMonth=@feeMonth"; } using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); if (!String.IsNullOrEmpty(feeMonth)) { command.Parameters.AddWithValue("@feeMonth", feeMonth); } MySqlDataReader reader = command.ExecuteReader(); IList<MailJob> list = new List<MailJob>(); MailJob mailJob = null; while (reader.Read()) { mailJob = new MailJob(); mailJob.mailJobId = reader["mailJobId"] == DBNull.Value ? null : reader["mailJobId"].ToString(); mailJob.feeMonth = reader["feeMonth"] == DBNull.Value ? null : reader["feeMonth"].ToString(); mailJob.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString(); mailJob.sendTime = reader["sendTime"] == DBNull.Value ? null : reader["sendTime"].ToString(); list.Add(mailJob); } mycn.Close(); return list; } }
/// <summary> /// 异步 开始事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void worker_DoWork(object sender, DoWorkEventArgs e) { //需要执行的代码 Cursor.Current = Cursors.WaitCursor; try { String client = Settings.Default.TripolisClient; String userName = Settings.Default.TripoisUserName; String password = Settings.Default.TripolisPassword; ChinaUnionAdapter mailAdapter = new ChinaUnionAdapter(client, userName, password, null); MailData mailData = new MailData(); mailData.fromAddress = Settings.Default.MailFromAddress; mailData.replyAddress = Settings.Default.MailReplyAddress; mailData.sender = Settings.Default.MailSender; mailData.subject = this.subject; String databaseId = Settings.Default.TripolisDBId; String workspaceId = Settings.Default.TripolisWorkspaceId; String emailId = Settings.Default.TripolisDirectEmailId; worker.ReportProgress(1, "准备数据...\r\n"); WechatAction wechatAction = new WechatAction(); for (int i = 0; i < dgAgentFee.RowCount; i++) { if (dgAgentFee[4, i].Value == null) { continue; } if (String.IsNullOrEmpty(dgAgentFee[4, i].Value.ToString())) { continue; } StringBuilder sb = new StringBuilder(); String url = String.Format(Settings.Default.Wechat_Message, dgAgentFee[0, i].Value.ToString(), this.dateTimePicker1.Value.ToString("yyyy-MM")); wechatAction.sendTextMessageToWechat(dgAgentFee[0, i].Value.ToString(), this.feeMonth + url, Settings.Default.Wechat_Secret, Settings.Default.Wechat_Agent_AppId); sb.Append("agent_no#").Append(dgAgentFee[0, i].Value == null ? "" : dgAgentFee[0, i].Value.ToString()).Append(","); sb.Append("agent_name#").Append(dgAgentFee[1, i].Value == null ? "" : dgAgentFee[1, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(","); sb.Append("agent_type#").Append(dgAgentFee[2, i].Value == null ? "" : dgAgentFee[2, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(","); sb.Append("agent_type_comment#").Append(dgAgentFee[3, i].Value == null ? "" : dgAgentFee[3, i].Value.ToString().Replace(",","、").Replace("#","%")).Append(","); sb.Append("email#").Append(dgAgentFee[4, i].Value == null ? "" : dgAgentFee[4, i].Value.ToString()).Append(","); sb.Append("contact_name#").Append(dgAgentFee[5, i].Value == null ? "" : dgAgentFee[5, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(","); sb.Append("agent_fee_seq#").Append(dgAgentFee[6, i].Value == null ? "" : dgAgentFee[6, i].Value.ToString()).Append(","); sb.Append("feemonth#").Append(this.feeMonth).Append(","); /// Dictionary<String, Dictionary<String, String>> CategoryMap = new Dictionary<string, Dictionary<String, String>>(); //按结账科目分类 int rowIndex = i; for (int j = 7; j < dgAgentFee.ColumnCount - 3; j++) { String headText = dgAgentFee.Columns[j].HeaderText; int locationIndex = headText.IndexOf("-"); int endIndex = headText.IndexOf("("); if (locationIndex == -1) { locationIndex = headText.IndexOf("-"); } if (endIndex == -1) { endIndex = headText.IndexOf("("); } String key = dgAgentFee.Columns[j].HeaderText.Substring(locationIndex + 1); if (endIndex != -1) { key = dgAgentFee.Columns[j].HeaderText.Substring(locationIndex + 1, endIndex - locationIndex - 1); } String value = dgAgentFee[j, rowIndex].Value == null ? "" : dgAgentFee[j, rowIndex].Value.ToString(); if (!CategoryMap.ContainsKey(key)) { Dictionary<String, String> valueMap = new Dictionary<string, string>(); valueMap.Add(headText, value); CategoryMap.Add(key, valueMap); } else { Dictionary<String, String> valueMap = CategoryMap[key]; valueMap.Add(headText, value); } } //int index = 1; int feeSeq = 1; int seq = 1; foreach (String itemKey in CategoryMap.Keys) { Dictionary<String, String> valueMap = CategoryMap[itemKey]; float subTotal = 0; foreach (String value in valueMap.Values) { if (!String.IsNullOrEmpty(value)) { subTotal = subTotal + float.Parse(value); } } sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append("").Append(","); sb.Append("fee_name").Append(feeSeq.ToString()).Append("#<b>").Append(itemKey.Replace(",", "、").Replace("#", "%")).Append("</b>,"); if (subTotal<=0) { sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append("").Append(","); } else { sb.Append("fee").Append(feeSeq.ToString()).Append("#<b>").Append(subTotal).Append("</b>,"); } feeSeq++; foreach (String subKey in valueMap.Keys) { sb.Append("fee_name").Append(feeSeq.ToString()).Append("#").Append(subKey.Replace(",", "、").Replace("#", "%")).Append(","); if (String.IsNullOrEmpty(valueMap[subKey]) ||(!String.IsNullOrEmpty(valueMap[subKey]) && valueMap[subKey].Equals("0"))) { sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append("").Append(","); sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append("").Append(","); } else { sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append(seq.ToString()).Append(","); sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append(valueMap[subKey]).Append(","); seq++; } feeSeq++; } } for (int j = feeSeq; j <= 101; j++) { sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append("").Append(","); sb.Append("fee_name").Append(feeSeq.ToString()).Append("#").Append("").Append(","); sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append("").Append(","); feeSeq++; } sb.Append("fee_total_seq").Append("#").Append(seq.ToString()).Append(","); sb.Append("fee_total#").Append(dgAgentFee[dgAgentFee.Columns.Count - 3, i].Value.ToString()).Append(","); seq++; sb.Append("invoice_fee_seq").Append("#").Append(seq.ToString()).Append(","); sb.Append("invoice_fee#").Append(dgAgentFee[dgAgentFee.Columns.Count - 2, i].Value.ToString()).Append(","); seq++; sb.Append("pre_invoice_fee_seq").Append("#").Append(seq.ToString()).Append(","); sb.Append("pre_invoice_fee#").Append(dgAgentFee[dgAgentFee.Columns.Count - 1, i].Value.ToString()); mailData.ContactJsonList.Add(sb.ToString()); Cursor.Current = Cursors.Default; } worker.ReportProgress(2, "发送邮件...\r\n"); String message = mailAdapter.sendBatchMail(databaseId, workspaceId, emailId, mailData, dateTimePicker1.Value); if (message.Contains("OK:")) { String mailJobId = message.Substring(3); MailJob mailJob = new MailJob(); mailJob.feeMonth = this.feeMonth; mailJob.mailJobId = mailJobId; mailJob.subject = mailData.subject; if (dateTimePicker1.Value != null) { mailJob.sendTime = dateTimePicker1.Value.ToString("yyyy-MM-dd hh:mm:ss"); } MailJobDao mailJobDao = new MailJobDao(); mailJobDao.Delete(mailJob); mailJobDao.Add(mailJob); MessageBox.Show("邮件发送成功"); } else { MessageBox.Show(message); } } catch (Exception ex) { MessageBox.Show(ex.Message); } //this.Close(); }