/// <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();
}
