public DataTable InsertEdmTraceEmail(EdmTraceEmail query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" insert into edm_trace_email (`email`,`name`)values('{0}','{1}');select @@identity;", query.email, query.name);
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->GetOuterCustomer-->" + sql.ToString() + ex.Message, ex);
}
}
public string MailAndRequest(EdmSendLog eslQuery, MailRequest MRquery)
{
eslQuery.Replace4MySQL();
MRquery.Replace4MySQL();
string json = string.Empty;
ArrayList arrList = new ArrayList();
try
{
if (eslQuery.test_send_end)//測試發送
{
_edmContentNewDao.InsertEdmSendLog(eslQuery);
arrList.Add(_edmContentNewDao.InsertEmailRequest(MRquery));
}
else//正式發送
{
/*
1.發送名單條件和額外發送列表和額外不發送列表進行查重
2.固定信箱名單和額外發送列表和額外不發送列表進行查重
3.發送名單條件和固定信箱名單進行查重
4.包含非訂閱的與額外發送列表和額外不發送列表進行查重進行查重
5.4的結果和3的結果去重
*/
string[] extra_send_temp = new string[] { };
string[] extra_send = new string[] { };
string[] extra_no_send_temp = new string[] { };
string[] extra_no_send = new string[] { };
#region 額外發送列表中數據去重
if (MRquery.extra_send != "")
{
extra_send_temp = MRquery.extra_send.Split('\n');
List<string> extraSendTList = new List<string>();
for (int i = 0; i < extra_send_temp.Length; i++)
{
if (!extraSendTList.Contains(extra_send_temp[i]))
{
extraSendTList.Add(extra_send_temp[i]);
}
}
extra_send= extraSendTList.ToArray();
}
#endregion
#region 額外不發送列表中數據去重
if (MRquery.extra_no_send != "")
{
extra_no_send_temp = MRquery.extra_no_send.Split('\n');
List<string> extraNoSendTList = new List<string>();
for (int i = 0; i < extra_no_send_temp.Length; i++)
{
if (!extraNoSendTList.Contains(extra_no_send_temp[i]))
{
extraNoSendTList.Add(extra_no_send_temp[i]);
}
}
extra_no_send = extraNoSendTList.ToArray();
}
#endregion
#region 第一步： 【發送名單條件】和額外發送列表和額外不發送列表進行查重
#region 發送名單條件
DataTable _newDt = new DataTable();
_newDt.Columns.Add("user_email", typeof(string));
_newDt.Columns.Add("user_name", typeof(string));
DataTable _dt = new DataTable();
//如果選得無則沒有任何email
if (eslQuery.elcm_id > 0)
{
_dt = _edmListConditionMgr.GetUserEmail(eslQuery.elcm_id);
}
else
{
_dt.Columns.Add("user_email", typeof(string));
_dt.Columns.Add("user_name", typeof(string));
_dt.Columns.Add("user_id", typeof(string));
}
#region 額外發送列表
#region 發送名單為空，額外發送不空
if ((_dt == null || _dt.Rows.Count == 0) && MRquery.extra_send != "")
{
for (int i = 0; i < extra_send.Length; i++)
{
if (extra_send[i] != "")
{
DataRow dr = _dt.NewRow();
dr["user_email"] = extra_send[i];
dr["user_name"] = "";
dr["user_id"] = "0";
_dt.Rows.Add(dr);
}
}
}
#endregion
#region 發送名單為空，額外發送列表為空
#endregion
#region 發送名單不空，額外發送為空
#endregion
#region 發送名單不空，額外發送不空
else if ((_dt != null && _dt.Rows.Count > 0) && MRquery.extra_send != "")
{
for (int i = 0; i < extra_send.Length; i++)
{
if (extra_send[i] != "")
{
int norepeat = 0;
#region 額外發送的時候看看是不是已經存在這個email了，存在則不加入
for (int j = 0; j < _dt.Rows.Count; j++)
{
if (_dt.Rows[j]["user_email"].ToString() != extra_send[i])
{
norepeat++;
}
}
if (norepeat == _dt.Rows.Count)//證明不重複
{
DataRow dr = _dt.NewRow();
dr["user_email"] = extra_send[i];
dr["user_name"] = "";
dr["user_id"] = "0";
_dt.Rows.Add(dr);
}
#endregion
}
}
}
#endregion
#endregion
#region 額外排除列表
#region 發送名單為空額外排除名單不空
#endregion
#region 發送名單為空，額外排除列表為空
#endregion
#region 發送名單不空，額外排除為空
#endregion
#region 發送名單不空，額外排除不空
if ((_dt != null && _dt.Rows.Count > 0) && MRquery.extra_no_send != "")
{
for (int i = 0; i < extra_no_send.Length; i++)
{
if (extra_no_send[i] != "")
{
for (int j = 0; j < _dt.Rows.Count; j++)
{
if (_dt.Rows[j]["user_email"].ToString() == extra_no_send[i])
{
_dt.Rows.Remove(_dt.Rows[j]);
_dt.AcceptChanges();
}
}
}
}
}
#endregion
#endregion
#endregion
#endregion
#region 第二步： 【固定信箱名單】額外發送列表和額外發送列表進行查重
#region 固定信箱名單
DataTable _emailDt = _emailGroup.GetEmailList(eslQuery.email_group_id);
#region 額外發送列表
#region 固定信箱名單為空，額外發送不為空
if ((_emailDt == null || _emailDt.Rows.Count == 0) && MRquery.extra_send != "")
{
_emailDt.Columns.Add("email_address", typeof(string));
_emailDt.Columns.Add("name", typeof(string));
for (int i = 0; i < extra_send.Length; i++)
{
if (extra_send[i] != "")
{
DataRow dr = _emailDt.NewRow();
dr["email_address"] = extra_send[i];
dr["name"] = "";
_emailDt.Rows.Add(dr);
}
}
}
#endregion
#region 固定信箱名單為空，額外發送為空
#endregion
#region 固定信箱名單不為空，額外發送為空
#endregion
#region 固定信箱名單不為空，額外發送不為空
else if ((_emailDt != null && _emailDt.Rows.Count > 0) && MRquery.extra_send != "")
{
for (int i = 0; i < extra_send.Length; i++)
{
if (extra_send[i] != "")
{
int norepeat = 0;
#region 額外發送的時候看看是不是已經存在這個email了，存在則不加入
for (int j = 0; j < _emailDt.Rows.Count; j++)
{
if (_emailDt.Rows[j]["email_address"].ToString() != extra_send[i])
{
norepeat++;
}
}
if (norepeat == _emailDt.Rows.Count)//證明不重複
{
DataRow dr = _emailDt.NewRow();
dr["email_address"] = extra_send[i];
dr["name"] = "";
_emailDt.Rows.Add(dr);
}
#endregion
}
}
}
#endregion
#endregion
#region 額外排除列表
#region 信箱名單為空，額外排除為空
#endregion
#region 信箱名單為空，額外排除不為空
#endregion
#region 信箱名單不為空，額外排除為空
#endregion
#region 信箱名單不為空，額外排除為空
if ((_emailDt != null && _emailDt.Rows.Count > 0) && MRquery.extra_no_send != "")
{
for (int i = 0; i < extra_no_send.Length; i++)
{
if (extra_no_send[i] != "")
{
for (int j = 0; j < _emailDt.Rows.Count; j++)
{
if (_emailDt.Rows[j]["email_address"].ToString() == extra_no_send[i])
{
_emailDt.Rows.Remove(_emailDt.Rows[j]);
_emailDt.AcceptChanges();
}
}
}
}
}
#endregion
#endregion
#endregion
#endregion
#region 第三步： 發送名單條件和固定信箱名單查重
#region 發送名單為空，固定信箱不為空
if ((_dt == null || _dt.Rows.Count == 0) && _emailDt != null)
{
for (int i = 0; i < _emailDt.Rows.Count; i++)
{
DataRow dr = _dt.NewRow();
dr["user_email"] = _emailDt.Rows[i]["email_address"];
dr["user_name"] = _emailDt.Rows[i]["name"];
dr["user_id"] = "0";
_dt.Rows.Add(dr);
}
}
#endregion
#region 發送名單為空，固定信箱為空
#endregion
#region 發送名單不為空，固定信箱為空
#endregion
#region 發送名單不為空，固定信箱不為空
else if ((_dt != null && _dt.Rows.Count > 0) && (_emailDt != null && _emailDt.Rows.Count > 0))
{
for (int i = 0; i < _emailDt.Rows.Count; i++)
{
int norepeat = 0;
string email_address = string.Empty;
for (int j = 0; j < _dt.Rows.Count; j++)
{
if (_dt.Rows[j]["user_email"].ToString() != _emailDt.Rows[i]["email_address"].ToString())
{
norepeat++;
email_address = _emailDt.Rows[i]["email_address"].ToString();
}
}
if (norepeat == _dt.Rows.Count)//證明不重複
{
DataRow dr = _dt.NewRow();
dr["user_name"] = "";
dr["user_email"] = email_address;
dr["user_id"] = "0";
_dt.Rows.Add(dr);
}
}
}
#endregion
#endregion
#region 【包含訂閱】與額外發送列表和額外排除列表
if (MRquery.is_outer)
{
#region 包含訂閱
DataTable _outDt = GetCheckedDataTable(MRquery.group_id);
#region 額外發送列表
if (MRquery.extra_send != "")
{
for (int i = 0; i < extra_send.Length; i++)
{
if (extra_send[i] != "")
{
#region 額外發送的時候看看是不是已經存在這個email了，存在則不加入
int norepeat = 0;
for (int j = 0; j < _outDt.Rows.Count; j++)
{
if (_outDt.Rows[j]["customer_email"].ToString() != extra_send[i])
{
norepeat++;
}
}
if (norepeat == _outDt.Rows.Count)//證明不重複
{
DataRow dr = _outDt.NewRow();
dr["customer_email"] = extra_send[i];
_outDt.Rows.Add(dr);
}
#endregion
}
}
}
#endregion
#region 額外排除列表
if (MRquery.extra_no_send != "")
{
for (int i = 0; i < extra_no_send.Length; i++)
{
if (extra_no_send[i] != "")
{
for (int j = 0; j < _outDt.Rows.Count; j++)
{
if (_outDt.Rows[j]["customer_email"].ToString() == extra_no_send[i])
{
_outDt.Rows.Remove(_outDt.Rows[j]);
_outDt.AcceptChanges();
}
}
}
}
}
#endregion
#region 去重
for (int i = 0; i < _outDt.Rows.Count; i++)
{
for (int j = 0; j < _dt.Rows.Count; j++)
{
if (_dt.Rows[j]["user_email"].ToString() == _outDt.Rows[i]["customer_email"].ToString())
{
_dt.Rows.RemoveAt(j);
_dt.AcceptChanges();
}
}
}
#endregion
_dt.Merge(_outDt);
#endregion
}
#endregion
#region 賦值，生成sql語句
string RecommendHtml = string.Empty;
if (_dt.Rows.Count > 0)
{
eslQuery.receiver_count = _dt.Rows.Count;
int log_id = Convert.ToInt32(_edmContentNewDao.InsertEdmSendLog(eslQuery).Rows[0][0]);
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (_dt.Columns.Contains("user_email"))
{
if (_dt.Rows[i]["user_email"].ToString() != "" && _dt.Rows[i]["user_email"].ToString() != null)
{
MRquery.receiver_address = _dt.Rows[i]["user_email"].ToString();
if (!string.IsNullOrEmpty(_dt.Rows[i]["user_name"].ToString()))
{
MRquery.receiver_name = _dt.Rows[i]["user_name"].ToString();
}
else
{
MRquery.receiver_name = "";
}
if (!string.IsNullOrEmpty(_dt.Rows[i]["user_id"].ToString()))
{
MRquery.user_id = Convert.ToInt32(_dt.Rows[i]["user_id"].ToString());
}
else
{
MRquery.user_id = 0;
}
}
else
{
MRquery.receiver_address = _dt.Rows[i]["customer_email"].ToString();
MRquery.receiver_name = "";
MRquery.user_id = 0;
}
}
else
{
if (_dt.Columns.Contains("customer_email"))
{
MRquery.receiver_address = _dt.Rows[i]["customer_email"].ToString();
MRquery.receiver_name = "";
MRquery.user_id = 0;
}
}
if (MRquery.static_template == 0)
{
RecommendHtml = GetRecommendHtml(Convert.ToUInt32(MRquery.user_id));//根據user_id做出精準推薦
}
else
{
RecommendHtml = string.Empty;
}
EdmTraceEmail ete = new EdmTraceEmail();
ete.email = MRquery.receiver_address;
ete.name = MRquery.receiver_name;
int email_id = Convert.ToInt32(_edmContentNewDao.InsertEdmTraceEmail(ete).Rows[0][0]);
EdmTrace et = new EdmTrace();
et.log_id = log_id;
et.content_id = eslQuery.content_id;
et.count = 0;
et.success = -1;
et.email_id = email_id;
arrList.Add(_edmContentNewDao.InsertEdmTrace(et));
MRquery.success_action = "update edm_trace set success=1,send_date=NOW() where log_id=" + log_id + " and content_id=" + eslQuery.content_id + " and email_id=" + email_id + ";";
MRquery.fail_action = "update edm_trace set success=0,send_date=NOW() where log_id=" + log_id + " and content_id=" + eslQuery.content_id + " and email_id=" + email_id + ";";
#region 用於統計開信人數次數的url
DataTable _dtUrl = _edmContentNewDao.GetPraraData(2);//用於統計開信人數次數的url
string url = string.Empty;
if (_dtUrl != null && _dtUrl.Rows.Count > 0)
{
url = "<img src='" + _dtUrl.Rows[0][0].ToString() + "?c=" + eslQuery.content_id + "&e=" + email_id + "&l=" + log_id + "'/>";
}
#endregion
#region 獲得電子報整體內容
#region 是範本還是活動頁面
string replaceStr = string.Empty;
string editStr = string.Empty;
string content_url = GetContentUrlByContentId(eslQuery.content_id);
if (!string.IsNullOrEmpty(content_url))
{
#region 獲取網頁內容
HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(content_url);
httpRequest.Timeout = 9000;
httpRequest.Method = "GET";
HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse();
StreamReader sr = new StreamReader(httpResponse.GetResponseStream(), System.Text.Encoding.GetEncoding("UTF-8"));
// contentStr = sr.ReadToEnd();
string contentStr = sr.ReadToEnd();
DataTable replaceStrDt = GetPraraData(1);
if (replaceStrDt != null && replaceStrDt.Rows.Count > 0)
{
replaceStr = replaceStrDt.Rows[0][0].ToString();
}
else
{
replaceStr = " ";
}
DataTable _dtEdit =GetPraraData(3);
if (_dtEdit != null && _dtEdit.Rows.Count > 0)
{
editStr = _dtEdit.Rows[0][0].ToString();
}
else
{
editStr = " ";
}
if (MRquery.body.IndexOf(subscribe) > 0)//找到了埋的那個code，證明是點擊了訂閱電子報
{
MRquery.bodyData = contentStr.Replace(replaceStr, MRquery.body.Replace(editStr, "").Replace(subscribe, "\n") + RecommendHtml) + subscribe_url + url;
}
else
{
MRquery.bodyData = contentStr.Replace(replaceStr, MRquery.body.Replace(editStr, "") + RecommendHtml + url);
}
#endregion
}
#endregion
#endregion
arrList.Add(_edmContentNewDao.InsertEmailRequest(MRquery));
MRquery.bodyData = string.Empty;
}
}
#endregion
}
if (_mySql.ExcuteSqlsThrowException(arrList))
{
json = "{success:'true'}";
}
else
{
json = "{success:'false'}";
}
return json;
}
catch (Exception ex)
{
throw new Exception("EdmContentNewMgr-->MailAndRequest-->" + ex.Message, ex);
}
}