public EdmSendQuery EdmSendLoad(EdmSendQuery query) { try { return _edmSendDao.EdmSendLoad(query); } catch (Exception ex) { throw new Exception("EdmSendMgr-->EdmSendLoad-->" + ex.Message); } }
public DataTable EdmSendExportCSV(EdmSendQuery query) { try { return _edmSendDao.EdmSendExportCSV(query); } catch (Exception ex) { throw new Exception("EdmSendMgr-->EdmSendExportCSV-->" + ex.Message); } }
public int GetMaxOpen(EdmSendQuery query) { try { return _edmSendDao.GetMaxOpen(query); } catch (Exception ex) { throw new Exception("EdmSendMgr-->GetMaxOpen-->" + ex.Message); } }
public List<EdmSendQuery> GetStatisticsEdmSend(EdmSendQuery query, out int totalCount) { try { return _edmSendDao.GetStatisticsEdmSend(query, out totalCount); } catch (Exception ex) { throw new Exception("EdmSendMgr-->GetStatisticsEdmSend-->" + ex.Message); } }
public DataTable GetSendRecordList(EdmSendQuery query,out int totalCount) { totalCount = 0; try { return _edmSendDao.GetSendRecordList(query,out totalCount); } catch (Exception ex) { throw new Exception("EdmSendMgr-->GetSendRecordList-->" + ex.Message); } }
public DataTable GetSendRecordList(EdmSendQuery query,out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); totalCount = 0; try { sql.AppendFormat(@"SELECT ee.send_status,ec.content_id,ec.content_title,ee.open_total,e.email_address,e.email_name,FROM_UNIXTIME(ee.send_datetime) as sendtime,FROM_UNIXTIME(ee.open_first) as firsttime ,FROM_UNIXTIME(ee.open_last) as lasttime "); sqlWhere.AppendFormat(@" FROM edm_send ee LEFT JOIN edm_content ec ON ee.content_id = ec.content_id LEFT JOIN edm_email e on ee.email_id=e.email_id WHERE ee.email_id = {0} ", query.email_id); if (!string.IsNullOrEmpty(query.content_title)) //email_name { sqlWhere.AppendFormat(" and ec.content_title like '%{0}%'", query.content_title); } if (query.date == 1) //寄信時間 { if (!string.IsNullOrEmpty(query.start_time)) //email_name { sqlWhere.AppendFormat(" and ee.send_datetime >='{0}'", CommonFunction.GetPHPTime(query.start_time)); } if (!string.IsNullOrEmpty(query.end_time)) //email_name { sqlWhere.AppendFormat(" and ee.send_datetime <='{0}'", CommonFunction.GetPHPTime(query.end_time)); } } if (query.date == 2) //首次開信時間 { if (!string.IsNullOrEmpty(query.start_time)) //email_name { sqlWhere.AppendFormat(" and ee.open_first >='{0}'", CommonFunction.GetPHPTime(query.start_time)); } if (!string.IsNullOrEmpty(query.end_time)) //email_name { sqlWhere.AppendFormat(" and ee.open_first <='{0}'", CommonFunction.GetPHPTime(query.end_time)); } } if (query.date == 3) //最近開信時間 { if (!string.IsNullOrEmpty(query.start_time)) //email_name { sqlWhere.AppendFormat(" and ee.open_last >='{0}'", CommonFunction.GetPHPTime(query.start_time)); } if (!string.IsNullOrEmpty(query.end_time)) //email_name { sqlWhere.AppendFormat(" and ee.open_last <='{0}'", CommonFunction.GetPHPTime(query.end_time)); } } sqlWhere.AppendFormat("ORDER BY ec.content_id DESC"); sqlCount.Append(@"SELECT count(ec.content_id) totalCount "); if (query.IsPage) { DataTable dt = _access.getDataTable(sqlCount.ToString()+sqlWhere.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]); } sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit); } return _access.getDataTable(sql.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("EdmSendDao-->GetSendRecordList-->" + sql.ToString() + sqlWhere.ToString() + ex.Message, ex); } }
public List<EdmSendQuery> GetStatisticsEdmSend(EdmSendQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); totalCount = 1;//IFNULL(SUM(traveluser_totalppl),9)es.content_id = ' 1207 ' try { sql.Append(@"SELECT es.email_id,es.send_status,es.send_datetime,es.open_first,es.open_last,es.open_total,ee.email_name "); sqlWhere.Append(" FROM edm_email ee,edm_send es WHERE es.email_id = ee.email_id "); if (!string.IsNullOrEmpty(query.email_name)) //email_name { sqlWhere.AppendFormat(" and ee.email_name like '%{0}%'", query.email_name); } if (query.date == 1) //寄信時間 { if (!string.IsNullOrEmpty(query.start_time)) //email_name { sqlWhere.AppendFormat(" and send_datetime >='{0}'", CommonFunction.GetPHPTime(query.start_time)); } if (!string.IsNullOrEmpty(query.end_time)) //email_name { sqlWhere.AppendFormat(" and send_datetime <='{0}'", CommonFunction.GetPHPTime(query.end_time)); } } if (query.date == 2) //首次開信時間 { if (!string.IsNullOrEmpty(query.start_time)) //email_name { sqlWhere.AppendFormat(" and open_first >='{0}'", CommonFunction.GetPHPTime(query.start_time)); } if (!string.IsNullOrEmpty(query.end_time)) //email_name { sqlWhere.AppendFormat(" and open_first <='{0}'", CommonFunction.GetPHPTime(query.end_time)); } } if (query.date == 3) //最近開信時間 { if (!string.IsNullOrEmpty(query.start_time)) //email_name { sqlWhere.AppendFormat(" and open_last >='{0}'", CommonFunction.GetPHPTime(query.start_time)); } if (!string.IsNullOrEmpty(query.end_time)) //email_name { sqlWhere.AppendFormat(" and open_last <='{0}'", CommonFunction.GetPHPTime(query.end_time)); } } sqlWhere.AppendFormat(" and es.content_id = '{0}' ORDER BY open_total DESC, es.email_id ASC ", query.content_id); sqlCount.Append("select count(es.email_id) totalCount "); // if (query.IsPage) { DataTable dt = _access.getDataTable(sqlCount.ToString()+sqlWhere.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]); } sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit); } return _access.getDataTableForObj<EdmSendQuery>(sql.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("EdmSendDao-->GetStatisticsEdmSend-->" + sql.ToString() + ex.Message); } }
public EdmSendQuery EdmSendLoad(EdmSendQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"SELECT group_id, content_status, content_email_id, content_start, content_end, content_range, content_single_count, content_click, content_person, content_send_success, content_send_failed, content_from_name, content_from_email, content_reply_email, content_priority, content_title, content_body, content_createdate, content_updatedate FROM edm_content WHERE content_id = '{0}' ", query.content_id); return _access.getSinggleObj<EdmSendQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("EdmSendDao-->EdmSendLoad-->" + sql.ToString() + ex.Message, ex); } }
public int GetMaxOpen(EdmSendQuery query) { int result = 0; StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"SELECT max(open_total) open_total FROM edm_send es WHERE content_id = '{0}' ", query.content_id); DataTable _dt = _access.getDataTable(sql.ToString()); if(_dt.Rows.Count>0) { result = Convert.ToInt32(_dt.Rows[0]["open_total"].ToString()); } return result; } catch (Exception ex) { throw new Exception("EdmSendDao-->GetMaxOpen-->" + sql.ToString() + ex.Message,ex); } }
public DataTable EdmSendExportCSV(EdmSendQuery query) { StringBuilder sql = new StringBuilder(); try { sql.Append(@"SELECT es.email_id,es.send_status,es.send_datetime,es.open_first,es.open_last,es.open_total,ee.email_name,ee.email_address"); sql.Append(" FROM edm_email ee,edm_send es WHERE es.email_id = ee.email_id "); sql.AppendFormat(" and es.content_id = '{0}' ORDER BY open_total DESC, es.email_id ASC ", query.content_id); return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("EdmSendDao->EdmSendExportCSV->" + sql.ToString() + ex.Message, ex); } }
public HttpResponseBase GetSendRecordList() { string json = string.Empty; _edmSendMgr = new EdmSendMgr(mySqlConnectionString); EdmSendQuery query = new EdmSendQuery(); int totalCount = 0; try { if (!string.IsNullOrEmpty(Request.Params["eid"])) { query.email_id = Convert.ToUInt32(Request.Params["eid"]); } query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); if (!string.IsNullOrEmpty(Request.Params["content_title"])) { query.content_title = Request.Params["content_title"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["date"])) { query.date = Convert.ToInt32(Request.Params["date"]); } if (!string.IsNullOrEmpty(Request.Params["start_time"])) { query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00"); } if (!string.IsNullOrEmpty(Request.Params["end_time"])) { query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59"); } DataTable store = _edmSendMgr.GetSendRecordList(query, out totalCount); 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 EdmSendLoad() { string json = string.Empty; EdmSendQuery store = new EdmSendQuery(); EdmSendQuery query = new EdmSendQuery(); try { if (!string.IsNullOrEmpty(Request.Params["cid"])) { query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString()); } _edmSendMgr = new EdmSendMgr(mySqlConnectionString); store = _edmSendMgr.EdmSendLoad(query); int nMax_Image_Width = 250; //最大圖片寬度 if (store != null) { store.content_send = store.content_send_success + store.content_send_failed; if (store.content_send > 0) { store.content_openRate = Math.Round((double)store.content_person / store.content_send * 100, 2); store.content_imagewidth_send = nMax_Image_Width; store.content_imagewidth_success = (int)Math.Round((double)store.content_send_success / store.content_send * nMax_Image_Width, 0); store.content_imagewidth_failed = (int)Math.Round((double)store.content_send_failed / store.content_send * nMax_Image_Width, 0); } if (store.content_person > 0) { store.content_averageClick = Math.Round((double)store.content_click / store.content_person, 1); } store.content_start_s = CommonFunction.GetNetTime(store.content_start).ToString("yyyy-MM-dd HH:mm:ss"); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,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,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase EdmSendExportCSV() { string json = string.Empty; try { EdmSendQuery query = new EdmSendQuery(); if (!string.IsNullOrEmpty(Request.Params["cid"])) { query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString()); } _edmSendMgr = new EdmSendMgr(mySqlConnectionString); DataTable _dt = _edmSendMgr.EdmSendExportCSV(query); string fileName = "edm_status_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "." + "csv"; string newFileName = Server.MapPath(excelPath_export + fileName); //"電子信箱" string[] colName = { "發信狀態", "郵件編號", "姓名", "開信次數", "寄信時間", "首次開信時間", "最近開信時間" }; DataTable _newdt = new DataTable(); foreach (string item in colName) { _newdt.Columns.Add(item, typeof(string)); } for (int i = 0; i < _dt.Rows.Count; i++) { DataRow dr = _newdt.NewRow();//SELECT es.email_id,es.send_status,es.send_datetime,es.open_first,es.open_last,es.open_total,ee.email_name dr[0] = _dt.Rows[i]["send_status"].ToString() == "1" ? "Success" : "Fail"; dr[1] = _dt.Rows[i]["email_id"]; dr[2] = _dt.Rows[i]["email_name"]; //dr[3] = _dt.Rows[i]["email_address"]; //不導出email_address dr[3] = _dt.Rows[i]["open_total"]; if (Convert.ToUInt32(_dt.Rows[i]["send_datetime"]) != 0) { dr[4] = CommonFunction.GetNetTime(Convert.ToUInt32(_dt.Rows[i]["send_datetime"])).ToString("yyyy-MM-dd HH:mm:ss"); } if (Convert.ToUInt32(_dt.Rows[i]["open_first"]) != 0) { dr[5] = CommonFunction.GetNetTime(Convert.ToUInt32(_dt.Rows[i]["open_first"])).ToString("yyyy-MM-dd HH:mm:ss"); } if (Convert.ToUInt32(_dt.Rows[i]["open_last"]) != 0) { dr[6] = CommonFunction.GetNetTime(Convert.ToUInt32(_dt.Rows[i]["open_last"])).ToString("yyyy-MM-dd HH:mm:ss"); } if (Request["st"].ToString() == "1" && Convert.ToUInt32(_dt.Rows[i]["open_first"]) != 0) //開信名單下載 { _newdt.Rows.Add(dr); } if (Request["st"].ToString() == "0" && Convert.ToUInt32(_dt.Rows[i]["open_first"]) == 0)//未開信名單下載 { _newdt.Rows.Add(dr); } } CsvHelper.ExportDataTableToCsv(_newdt, newFileName, colName, true); json = "{success:true,fileName:\'" + fileName + "\'}"; } 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 GetStatisticsEdmSend() { string json = string.Empty; List<EdmSendQuery> store = new List<EdmSendQuery>(); EdmSendQuery query = new EdmSendQuery(); try { query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); if (!string.IsNullOrEmpty(Request.Params["cid"])) { query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString()); } if (!string.IsNullOrEmpty(Request.Params["email_name"])) { query.email_name = Request.Params["email_name"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["date"])) { query.date = Convert.ToInt32(Request.Params["date"]); } if (!string.IsNullOrEmpty(Request.Params["start_time"])) { query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00"); } if (!string.IsNullOrEmpty(Request.Params["end_time"])) { query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59"); } _edmSendMgr = new EdmSendMgr(mySqlConnectionString); int totalCount = 0; store = _edmSendMgr.GetStatisticsEdmSend(query, out totalCount); foreach (var items in store) { items.email_name = items.email_name.ToString().Substring(0, 1) + "**"; } //計算圖表width int max_open = _edmSendMgr.GetMaxOpen(query); double nTemp_Image_Rate = 1; int nMax_Image_Width = 250; if (max_open > 0) { nTemp_Image_Rate = (max_open > nMax_Image_Width) ? Math.Round((double)nMax_Image_Width / max_open, 2) : 1; } foreach (var item in store) { item.s_send_status = item.send_status == 1 ? "成功" : "失敗"; if (item.send_datetime != 0) { item.s_send_datetime = CommonFunction.GetNetTime(item.send_datetime).ToString("yyyy-MM-dd HH:mm:ss"); } if (item.open_first != 0) { item.s_open_first = CommonFunction.GetNetTime(item.open_first).ToString("yyyy-MM-dd HH:mm:ss"); } if (item.open_last != 0) { item.s_open_last = CommonFunction.GetNetTime(item.open_last).ToString("yyyy-MM-dd HH:mm:ss"); } item.image_width = (uint)Math.Round(item.open_total * nTemp_Image_Rate, 0); } 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; }