public List<EdmListQuery> GetStatisticsEdmList(EdmListQuery query, out int totalCount) { try { return _edmSendDao.GetStatisticsEdmList(query, out totalCount); } catch (Exception ex) { throw new Exception("EdmSendMgr-->GetStatisticsEdmList-->" + ex.Message); } }
public int GetMaxClick(EdmListQuery query,out int sum_total_click, out int sum_total_person) { try { return _edmSendDao.GetMaxClick(query,out sum_total_click, out sum_total_person); } catch (Exception ex) { throw new Exception("EdmSendMgr-->GetMaxClick-->" + ex.Message); } }
public List<EdmListQuery> GetStatisticsEdmList(EdmListQuery 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 statistics_id,total_click ,total_person "); sqlWhere.AppendFormat(" from edm_daily_statistics WHERE 1=1 and content_id = '{0}' ", query.content_id); sqlCount.Append("select count(statistics_id) totalCount "); // if (query.IsPage) { System.Data.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<EdmListQuery>(sql.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("EdmSendDao-->GetStatisticsEdmList-->" + sql.ToString() + ex.Message); } }
public int GetMaxClick(EdmListQuery query,out int sum_total_click, out int sum_total_person) { int result = 0; sum_total_click = 0; sum_total_person = 0; StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"SELECT MAX(total_click) total_click ,SUM(total_click) sum_total_click,SUM(total_person) sum_total_person FROM edm_daily_statistics WHERE content_id = '{0}' ", query.content_id); DataTable _dt = _access.getDataTable(sql.ToString()); if (_dt.Rows.Count > 0) { sum_total_click = Convert.ToInt32(_dt.Rows[0]["sum_total_click"].ToString()); sum_total_person = Convert.ToInt32(_dt.Rows[0]["sum_total_person"].ToString()); result = Convert.ToInt32(_dt.Rows[0]["total_click"].ToString()); } return result; } catch (Exception ex) { throw new Exception("EdmSendDao-->GetMaxOpen-->" + sql.ToString() + ex.Message, ex); } }
public HttpResponseBase GetStatisticsEdmList() { string json = string.Empty; List<EdmListQuery> store = new List<EdmListQuery>(); EdmListQuery query = new EdmListQuery(); 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()); } _edmSendMgr = new EdmSendMgr(mySqlConnectionString); int totalCount = 0; store = _edmSendMgr.GetStatisticsEdmList(query, out totalCount); //計算圖表width和sum_total_click、sum_total_click int sum_total_click = 0; int sum_total_person = 0; int max_open = _edmSendMgr.GetMaxClick(query, out sum_total_click, out sum_total_person); double nTemp_Image_Rate = 1.00; 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) { string temp_statistics_id = item.statistics_id.ToString(); string year = temp_statistics_id.Substring(0, 4); string month = temp_statistics_id.Substring(4, 2); string day = temp_statistics_id.Substring(6, 2); DateTime d1 = Convert.ToDateTime(year + "-" + month + "-" + day + " 00:00:00"); d1.DayOfWeek.ToString(); switch (d1.DayOfWeek) { case DayOfWeek.Sunday: item.week = "[日]"; break; case DayOfWeek.Monday: item.week = "[一]"; break; case DayOfWeek.Tuesday: item.week = "[二]"; break; case DayOfWeek.Wednesday: item.week = "[三]"; break; case DayOfWeek.Thursday: item.week = "[四]"; break; case DayOfWeek.Friday: item.week = "[五]"; break; case DayOfWeek.Saturday: item.week = "[六]"; break; default: break; } item.date = year + "-" + month + "-" + day; if (sum_total_click > 0) { item.clickRate = Math.Round((double)item.total_click / sum_total_click * 100, 2); } if (sum_total_person > 0) { item.personRate = Math.Round((double)item.total_person / sum_total_person * 100, 2); } item.image_width = (uint)Math.Round(item.total_click * 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; }