/// <summary>
/// 電子報列表
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<EdmContentNew> GetECNList(EdmContentNew query, out int totalCount)
{
try
{
return _edmContentNewDao.GetECNList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("EdmContentNewMgr-->GetECNList-->" + ex.Message, ex);
}
}
public List<EdmContentNew> GetECNList(EdmContentNew query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
query.Replace4MySQL();
totalCount = 0;
try
{
sqlCount.AppendFormat("select count(edn.content_id) as countTotal ");
sql.AppendFormat("select edn.content_id,edn.group_id,`subject`,esl.count,esl.date,edn.sender_id,ms.sender_email,ms.sender_name,edn.importance,edn.template_id,edn.template_data,'' as 'template_data_send', et.edit_url,et.content_url , edn.pm, para.parameterName 'edm_pm',et.static_template,mu.user_username 'user_username_create',mu2.user_username 'user_username_update' ");
sqlFrom.AppendFormat("from edm_content_new edn LEFT JOIN (SELECT content_id,COUNT(content_id) as count,MAX(schedule_date) as date from edm_send_log WHERE test_send=0 GROUP BY content_id) esl ON edn.content_id=esl.content_id LEFT JOIN mail_sender ms on edn.sender_id=ms.sender_id LEFT JOIN edm_template et on et.template_id=edn.template_id ");
sqlFrom.Append(" left join (select parameterCode,parameterName from t_parametersrc where parameterType='edm_pm_name' and used=1) para on edn.pm=para.parameterCode ");
sqlFrom.Append(" left join manage_user mu on mu.user_id=edn.content_create_userid ");
sqlFrom.Append(" left join manage_user mu2 on mu2.user_id=edn.content_update_userid ");
sqlWhere.AppendFormat(" where 1=1 ");
sqlWhere.AppendFormat(" and edn.content_createdate between '{0}' and '{1}' ", CommonFunction.DateTimeToString(DateTime.Now.AddDays(-5)), CommonFunction.DateTimeToString(DateTime.Now));
if (query.group_id != 0)
{
sqlWhere.AppendFormat(" and edn.group_id='{0}' ", query.group_id);
}
if (query.content_id != 0)
{
sqlWhere.AppendFormat(" and edn.content_id='{0}' ", query.content_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(" order by edn.content_id desc limit {0},{1}; ", query.Start, query.Limit);
return _access.getDataTableForObj<EdmContentNew>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->GetECNList-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
/*
編輯的預覽按鈕和列表頁的預覽都訪問這個方法
* 1.
*
*/
public HttpResponseBase GetPreviewHtml()
{
string html = string.Empty;
string replaceStr = string.Empty;
string contentStr = string.Empty;
string templateStr = string.Empty;
string recommendStr = string.Empty;
string previewStr = string.Empty;
string editStr = string.Empty;
try
{
_edmContentNewMgr = new EdmContentNewMgr(mySqlConnectionString);
EdmContentNew query = new EdmContentNew();
//將傳過來的html加上根據當前登入者的id獲得的推薦html替換掉content_url中的替換符
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
query.content_id = Convert.ToInt32(Request.Params["content_id"]);
string content_url = _edmContentNewMgr.GetContentUrlByContentId(query.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();
#endregion
}
else
{
contentStr = "";
}
}
//列表頁的預覽按鈕不進這個方法
if (!string.IsNullOrEmpty(Request.Params["template_id"]))
{
query.template_id = Convert.ToInt32(Request.Params["template_id"]);
edmtemplatemgr = new EdmTemplateMgr(mySqlConnectionString);
if (edmtemplatemgr.GetStaticTemplate(query.template_id))
{
recommendStr = string.Empty;
}
else
{
recommendStr = _edmContentNewMgr.GetRecommendHtml(Convert.ToUInt32((Session["caller"] as Caller).user_id));
}
}
//獲取template_data（編輯器中的內容或者是表中的template_data）
if (!string.IsNullOrEmpty(Request.Params["template_data"]))
{
templateStr = Request.Params["template_data"];
}
else
{
templateStr = _edmContentNewMgr.GetHtml(query);
}
//編輯的預覽按鈕不進這個方法
//根據user_id獲得精準推薦
if (!string.IsNullOrEmpty(Request.Params["static_template"]))
{
if (Request.Params["static_template"] == "0")// 動態範本，精準推薦
{
recommendStr = _edmContentNewMgr.GetRecommendHtml(Convert.ToUInt32((Session["caller"] as Caller).user_id));
}
else
{
recommendStr = string.Empty;
}
}
//替換符
DataTable _dt = _edmContentNewMgr.GetPraraData(1);
if (_dt != null && _dt.Rows.Count > 0)
{
replaceStr = _dt.Rows[0][0].ToString();
}
else
{
replaceStr = " ";
}
//分隔符
DataTable _dtEdit = _edmContentNewMgr.GetPraraData(3);
if (_dtEdit != null && _dtEdit.Rows.Count > 0)
{
editStr = _dtEdit.Rows[0][0].ToString();
}
else
{
editStr = " ";
}
templateStr = templateStr.Replace(editStr,"") + recommendStr;
if (Request.Params["checked"] == "true")
{
if (!string.IsNullOrEmpty(contentStr))
{
previewStr = contentStr.Replace(replaceStr, templateStr) + subscribe_url;
}
else
{
previewStr = templateStr + subscribe_url;
}
}
else
{
if (!string.IsNullOrEmpty(contentStr))
{
previewStr = contentStr.Replace(replaceStr, templateStr);
}
else
{
previewStr = templateStr;
}
}
}
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);
previewStr = "";
}
this.Response.Clear();
this.Response.Write(previewStr);
this.Response.End();
return this.Response;
}
public HttpResponseBase SaveEdmContentNew()
{
string json = string.Empty;
EdmContentNew query = new EdmContentNew();
try
{
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
query.content_id = Convert.ToInt32(Request.Params["content_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["sender_id"]))
{
query.sender_id = Convert.ToInt32(Request.Params["sender_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["importance"]))
{
query.importance = Convert.ToInt32(Request.Params["importance"]);
}
if (!string.IsNullOrEmpty(Request.Params["subject"]))
{
query.subject = Request.Params["subject"];
}
if (!string.IsNullOrEmpty(Request.Params["template_id"]))
{
query.template_id = Convert.ToInt32(Request.Params["template_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["template_data"]))
{
query.template_data = Request.Params["template_data"];
}
if (!string.IsNullOrEmpty(Request.Params["pm"]))
{
query.pm = Convert.ToInt32(Request.Params["pm"]);
}
if (!string.IsNullOrEmpty(Request.Params["check"]))
{
//點擊了訂閱電子電子報,在此埋入一個code，
if (Request.Params["check"] == "true")
{
query.template_data = query.template_data + subscribe;
}
else
{
query.template_data = query.template_data.Replace(subscribe_url, "");
}
}
if (!string.IsNullOrEmpty(Request.Params["active_dis"]))
{
int n=0;
if (int.TryParse(Request.Params["active_dis"].ToString(), out n))
{
query.active = Convert.ToInt32(Request.Params["active_dis"]);
}
else
{
query.active = 0;
}
if (query.active != 0)
{
query.template_id = _edmContentNewMgr.AdvanceTemplate();
}
}
query.content_create_userid = (Session["caller"] as Caller).user_id;
query.content_update_userid = (Session["caller"] as Caller).user_id;
json = _edmContentNewMgr.SaveEdmContentNew(query);
}
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}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetECNList()
{
string json = string.Empty;
try
{
EdmContentNew query = new EdmContentNew();
List<EdmContentNew> store = new List<EdmContentNew>();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToInt32(Request.Params["group_id"]);
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
_edmContentNewMgr = new EdmContentNewMgr(mySqlConnectionString);
store = _edmContentNewMgr.GetECNList(query, out totalCount);
foreach (var item in store)
{
item.template_data = Server.HtmlDecode(Server.HtmlDecode(item.template_data));
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, 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 HttpResponseBase Load()
{
string json = string.Empty;
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
if (!string.IsNullOrEmpty(Request.Params["log_id"]))
{
int log_id = Convert.ToInt32(Request.Params["log_id"]);
string openAveragePrecent = "0";
string openAverageCount = "0";
int content_id = Convert.ToInt32(Request.Params["content_id"]);
List<EdmContentNew> store = new List<BLL.gigade.Model.EdmContentNew>();
EdmContentNew query = new BLL.gigade.Model.EdmContentNew();
EdmContentNew newQuery = new BLL.gigade.Model.EdmContentNew();
_edmContentNewMgr = new EdmContentNewMgr(mySqlConnectionString);
query.content_id = content_id;
int count = 0;
int successCount = 0;
int failCount = 0;
int totalPersonCount = 0;
int totalCount = 0;
//電子報主旨和發送時間
newQuery = _edmContentNewMgr.GetECNList(query, out count).FirstOrDefault();
string subject = newQuery.subject;
string date=string.Empty;
DataTable _dt=_edmContentNewMgr.GetScheduleDate(content_id, log_id);
if (_dt != null && _dt.Rows.Count > 0)
{
date = Convert.ToDateTime(_dt.Rows[0][0].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
//發信成功人數
if (DateTime.Now > Convert.ToDateTime(date))
{
successCount = _edmContentNewMgr.GetSendMailSCount(content_id, log_id);
//發信失敗人數
failCount = _edmContentNewMgr.GetSendMailFCount(content_id, log_id);
//總開信人數
totalPersonCount = _edmContentNewMgr.GetSendMailCount(content_id, log_id);
//開總信次數
totalCount = _edmContentNewMgr.GetSendCount(content_id, log_id);
//開信率
if (successCount == 0)
{
openAveragePrecent = "0";
}
else
{
openAveragePrecent = Math.Round((double)totalPersonCount / successCount * 100, 2) + "%";
}
//平均開信次數
if (totalPersonCount == 0)
{
openAverageCount = "0";
}
else
{
openAverageCount = Math.Round((double)totalCount / totalPersonCount, 2).ToString();
}
}
}
json = "{success:true,successCount:'" + successCount + "',failCount:'" + failCount + "',totalPersonCount:'" + totalPersonCount + "',totalCount:'" + totalCount + "',openAveragePrecent:'" + openAveragePrecent + "',openAverageCount:'" + openAverageCount + "',subject:'" + subject + "',date:'" + date + "'}";
}
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetHtml()
{
string htmlStr = string.Empty;
try
{
EdmContentNew query = new EdmContentNew();
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
query.content_id = Convert.ToInt32(Request.Params["content_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["template_id"]))
{
query.template_id = Convert.ToInt32(Request.Params["template_id"]);
}
_edmContentNewMgr = new EdmContentNewMgr(mySqlConnectionString);
htmlStr=_edmContentNewMgr.GetHtml(query);
htmlStr = Server.HtmlDecode(Server.HtmlDecode(htmlStr));
}
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);
}
this.Response.Clear();
this.Response.Write(htmlStr);
this.Response.End();
return this.Response;
}
public DataTable GetHtml(EdmContentNew query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select template_data from edm_content_new where content_id='{0}' and template_id='{1}';", query.content_id, query.template_id);
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->GetHtml-->" + sql.ToString() + ex.Message, ex);
}
}
public int UpdateEdmContentNew(EdmContentNew query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat("update edm_content_new set group_id='{0}',subject='{1}',template_id='{2}',template_data='{3}',importance='{4}',pm='{5}', ", query.group_id, query.subject, query.template_id, query.template_data, query.importance,query.pm);
sql.AppendFormat(" sender_id='{0}',content_updatedate='{1}',content_update_userid='{2}' where content_id='{3}';", query.sender_id, CommonFunction.DateTimeToString(DateTime.Now), query.content_update_userid, query.content_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->InsertEdmContentNew-->" + ex.Message + ";sql:" + sql.ToString(), ex);
}
}
public int InsertEdmContentNew(EdmContentNew query)
{
query.Replace4MySQL(); StringBuilder sql = new StringBuilder();
try
{
sql.Append("insert into edm_content_new(group_id,`subject`,template_id,template_data, ");
sql.Append("importance,sender_id,content_createdate,content_updatedate,content_create_userid,content_update_userid,pm) ");
sql.AppendFormat("values('{0}','{1}','{2}','{3}',", query.group_id, query.subject, query.template_id, query.template_data);
sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}');", query.importance, query.sender_id, CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(DateTime.Now), query.content_create_userid, query.content_update_userid,query.pm);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->InsertEdmContentNew-->" + ex.Message + ";sql:" + sql.ToString(), ex);
}
}
/// <summary>
/// 電子報新增/編輯
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string SaveEdmContentNew(EdmContentNew query)
{
int result = 0;
string json = string.Empty;
try
{
if (query.content_id == 0)//新增
{
result = _edmContentNewDao.InsertEdmContentNew(query);
}
else
{
result = _edmContentNewDao.UpdateEdmContentNew(query);
}
if (result > 0)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
return json;
}
catch (Exception ex)
{
throw new Exception("EdmContentNewMgr-->SaveEdmContentNew-->" + ex.Message, ex);
}
}
public string GetHtml(EdmContentNew query)
{
string htmlStr = string.Empty;
try
{
DataTable _dt = _edmContentNewDao.GetHtml(query);
if (_dt != null && _dt.Rows.Count > 0)
{
htmlStr = _dt.Rows[0][0].ToString();
}
return htmlStr;
}
catch (Exception ex)
{
throw new Exception("EdmContentNewMgr-->GetHtml-->" + ex.Message, ex);
}
}
