public HttpResponseBase EdmContentNewReportList()
{
string json = string.Empty;
try
{
EdmTrace query = new EdmTrace();
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
query.content_id = Convert.ToInt32(Request.Params["content_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["log_id"]))
{
query.log_id = Convert.ToInt32(Request.Params["log_id"]);
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
_edmContentNewMgr = new EdmContentNewMgr(mySqlConnectionString);
DataTable _dt = _edmContentNewMgr.EdmContentNewReportList(query);
_dt.Columns.Add("avgPerson", typeof(string));
_dt.Columns.Add("avgCount", typeof(string));
int totalOpenPerson = 0;
int totalOpenCount = 0;
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (_dt.Rows[i]["openPerson"] != "")
{
totalOpenPerson += Convert.ToInt32(_dt.Rows[i]["openPerson"]);
}
if (_dt.Rows[i]["openCount"] != "")
{
totalOpenCount += Convert.ToInt32(_dt.Rows[i]["openCount"]);
}
}
for (int i = 0; i < _dt.Rows.Count; i++)
{
//Math.Round((double)item.total_person / sum_total_person * 100, 2);
if (_dt.Rows[i]["openPerson"] != "")
{
_dt.Rows[i]["avgPerson"] = Math.Round((double)Convert.ToInt32(_dt.Rows[i]["openPerson"]) / totalOpenPerson * 100, 2) + "%";
}
if (_dt.Rows[i]["openCount"] != "")
{
_dt.Rows[i]["avgCount"] = Math.Round((double)Convert.ToInt32(_dt.Rows[i]["openCount"]) / totalOpenCount * 100, 2) + "%";
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
///電子報統計報表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable EdmTrace(EdmTrace query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
int totalCount = 0;
try
{
sqlCount.Append(" select count(et.log_id) as totalCount ");
sql.Append("select * from(SELECT DATE_FORMAT(etl.trace_day,'%Y-%m-%d') as 'trace_day',count(etl.log_id) as 'openPerson',SUM(etl.trace_count) as 'openCount' ");
sqlFrom.Append("from edm_trace et LEFT JOIN edm_trace_log etl ON et.content_id=etl.content_id AND et.email_id=etl.email_id AND et.log_id=etl.log_id ");
sqlWhere.AppendFormat(" where et.content_id='{0}' and et.first_traceback>0 and et.log_id='{1}' GROUP BY DATE_FORMAT(etl.trace_day,'%Y-%m-%d') ", query.content_id,query.log_id);
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sqlWhere.AppendFormat(" ) e where e.openPerson>0 limit {0},{1}; ", query.Start, query.Limit);
return _access.getDataTable(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->EdmTrace-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
public HttpResponseBase EdmSendListCount()
{
string json = string.Empty;
try
{
EdmTrace query = new EdmTrace();
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
query.content_id = Convert.ToInt32(Request.Params["content_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["log_id"]))
{
query.log_id = Convert.ToInt32(Request.Params["log_id"]);
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
_edmContentNewMgr = new EdmContentNewMgr(mySqlConnectionString);
DataTable _dt = _edmContentNewMgr.FXMD(query);
foreach (DataRow item in _dt.Rows)
{
if (item["name"] != "")
{
item["name"] = item["name"].ToString().Substring(0, 1) + "**";
}
if (item["email"] != "")
{
item["email"] = item["email"].ToString().Split('@')[0] + "@***";
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public string InsertEdmTrace(EdmTrace query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" insert into edm_trace (log_id,content_id,email_id,first_traceback,last_traceback,count,success) values('{0}','{1}','{2}',NOW(),NOW(),'{3}','{4}');", query.log_id, query.content_id, query.email_id, query.count, query.success);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->InsertEdmTrace-->" + sql.ToString() + ex.Message, ex);
}
}
//發信名單統計
public DataTable FXMD(EdmTrace query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
int totalCount = 0;
try
{
sqlCount.Append("select count(et.content_id) as totalCount ");
sql.AppendFormat(" SELECT et.success,ete.`name`,ete.email,et.count,et.send_date,et.first_traceback,et.last_traceback ", query.content_id);
sqlFrom.Append(" from edm_trace et LEFT JOIN edm_trace_email ete ON et.email_id=ete.email_id ");
sqlWhere.AppendFormat(" WHERE et.content_id='{0}' and et.log_id='{1}' ", query.content_id,query.log_id);
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sqlWhere.AppendFormat(" limit {0},{1} ; ", query.Start, query.Limit);
return _access.getDataTable(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->FXMD-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
public DataTable EdmContentNewReportList(EdmTrace query)
{
try
{
return _edmContentNewDao.EdmTrace(query);
}
catch (Exception ex)
{
throw new Exception("EdmContentNewMgr-->EdmContentNewReportList-->" + ex.Message, ex);
}
}
public DataTable FXMD(EdmTrace query)
{
try
{
return _edmContentNewDao.FXMD(query);
}
catch (Exception ex)
{
throw new Exception("EdmContentNewMgr-->FXMD-->" + 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);
}
}