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