public List<RedirectClickQuery> QueryAllById(RedirectClickQuery query) { StringBuilder sqlclomn = new StringBuilder(); StringBuilder sqltable = new StringBuilder(); StringBuilder sqlcondition = new StringBuilder(); StringBuilder sqlgroup = new StringBuilder(); try { sqlclomn.Append(@" select count(click_total)sum_click,SUBSTRING(click_id,1,8) as click_id,click_year,click_month,click_day,redirect_id ");//,count(click_total)click_total, sqltable.Append(" from redirect_click "); sqlcondition.Append(" where 1=1 "); if (query.redirect_id_groupID!=0) { sqlcondition.AppendFormat(" and redirect_id in(SELECT redirect_id from redirect where group_id='{0}') ", query.redirect_id_groupID); } sqlgroup.Append(" GROUP BY redirect_id,click_year,click_month,click_day ORDER BY click_id ASC "); string sql = sqlclomn.ToString() + sqltable.ToString() + sqlcondition.ToString() + sqlgroup.ToString(); return _access.getDataTableForObj<RedirectClickQuery>(sql); } catch (Exception ex) { throw new Exception("RedirectClickDao.QueryAllById-->" + ex.Message + sqlclomn.ToString() + sqltable.ToString() + sqlcondition.ToString() + sqlgroup.ToString(), ex); } }
public List<RedirectClickQuery> GetRedirectClick(RedirectClickQuery query) { try { return _redirectClickDao.GetRedirectClick(query); } catch (Exception ex) { throw new Exception("RedirectClickMgr-->GetRedirectClick-->" + ex.Message, ex); } }
public List<RedirectClickQuery> QueryAllById(RedirectClickQuery query) { try { return _redirectClickDao.QueryAllById(query); } catch (Exception ex) { throw new Exception("RedirectClickMgr-->QueryAllById-->" + ex.Message, ex); } }
/// <summary> /// 報表統計 /// </summary> /// <param name="query"></param> /// <returns></returns> public List<RedirectClickQuery> GetRedirectClick(RedirectClickQuery query) { string month = query.click_month < 10 ? "0" + query.click_month.ToString() : query.click_month.ToString(); string start = query.click_year.ToString() + month + "01" + "00"; int days = DateTime.DaysInMonth(int.Parse(query.click_year.ToString()), int.Parse(query.click_month.ToString())); string end = query.click_year.ToString() + month + days + "23"; string redirect_id = string.Empty; StringBuilder sql = new StringBuilder(); try { if (query.redirect_id_groupID != 0) { //根據group_id查出所有的redirect_id sql.AppendFormat(@"SELECT GROUP_CONCAT(redirect_id) FROM redirect WHERE group_id='{0}';", query.redirect_id_groupID); DataTable dt = _access.getDataTable(sql.ToString()); if(dt.Rows.Count>0) { //判斷此群組下是否有連結 if (!string.IsNullOrEmpty(dt.Rows[0][0].ToString())) { redirect_id = dt.Rows[0][0].ToString(); } else { redirect_id = "0"; } } } else { redirect_id = query.redirect_id.ToString(); } sql.Clear(); sql.AppendFormat(@"SELECT click_id,click_day,click_hour,click_week,click_total "); sql.AppendFormat(@" FROM redirect_click"); sql.AppendFormat(@" WHERE 1=1 AND redirect_id in ({0}) ", redirect_id); sql.AppendFormat(@" AND click_id>={0} AND click_id<={1} ", start, end); sql.AppendFormat(@" ORDER BY click_id ASC;"); return _access.getDataTableForObj<RedirectClickQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("RedirectClickDao.GetRedirectClick-->" + ex.Message + sql.ToString(), ex); } }
public HttpResponseBase GetRedirectClickCount() { _rcMgr = new RedirectClickMgr(mySqlConnectionString); RedirectClickQuery query = new RedirectClickQuery(); if (!string.IsNullOrEmpty(Request.Params["selectDate"])) { string date = Request.Params["selectDate"]; query.click_year = uint.Parse(date.Substring(0, 4)); query.click_month = uint.Parse(date.Substring(5, 2)); } if (!string.IsNullOrEmpty(Request.Params["redirect_id"])) { query.redirect_id = uint.Parse(Request.Params["redirect_id"]); } if (!string.IsNullOrEmpty(Request.Params["group_id"])) { query.redirect_id_groupID = uint.Parse(Request.Params["group_id"]); } DataColumn coll; DataColumn col; DataTable days = new DataTable(); coll = new DataColumn("datetype", typeof(string)); col = new DataColumn("totalcount", typeof(string)); days.Columns.Add(coll); days.Columns.Add(col); DataTable weeks = new DataTable(); coll = new DataColumn("datetype", typeof(string)); col = new DataColumn("totalcount", typeof(string)); weeks.Columns.Add(coll); weeks.Columns.Add(col); DataTable hours = new DataTable(); coll = new DataColumn("datetype", typeof(string)); col = new DataColumn("totalcount", typeof(string)); hours.Columns.Add(coll); hours.Columns.Add(col); //獲取指定時間的月的總天數 for (int i = 1; i <= DateTime.DaysInMonth(int.Parse(query.click_year.ToString()), int.Parse(query.click_month.ToString())); i++) { DataRow dr = days.NewRow(); DateTime tday = new DateTime(int.Parse(query.click_year.ToString()), int.Parse(query.click_month.ToString()), i); if (tday.DayOfWeek == DayOfWeek.Saturday || tday.DayOfWeek == DayOfWeek.Sunday) { dr[0] = tday.ToString("yyyy年MM月dd日*"); } else { dr[0] = tday.ToString("yyyy年MM月dd日"); } dr[1] = 0; days.Rows.Add(dr); } for (int i = 0; i < 7; i++) { DataRow dr = weeks.NewRow(); dr[0] = i; dr[1] = 0; weeks.Rows.Add(dr); } for (int i = 0; i < 24; i++) { DataRow dr = hours.NewRow(); dr[0] = i; dr[1] = 0; hours.Rows.Add(dr); } List<RedirectClickQuery> store = new List<RedirectClickQuery>(); store = _rcMgr.GetRedirectClick(query); string json = string.Empty; try { foreach (var item in store) { int dayindex = int.Parse(item.click_day.ToString()) - 1; days.Rows[dayindex][1] = uint.Parse(days.Rows[dayindex][1].ToString()) + item.click_total; int weekindex = int.Parse(item.click_week.ToString()); weeks.Rows[weekindex][1] = uint.Parse(weeks.Rows[weekindex][1].ToString()) + item.click_total; int hourindex = int.Parse(item.click_hour.ToString()); hours.Rows[hourindex][1] = uint.Parse(hours.Rows[hourindex][1].ToString()) + item.click_total; } json = "{success:true,daydata:" + JsonConvert.SerializeObject(days, Formatting.Indented) + ",weekdata:" + JsonConvert.SerializeObject(weeks, Formatting.Indented) + ",hourdata:" + JsonConvert.SerializeObject(hours, Formatting.Indented) + "}";//返回json數據 } 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:true,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase GetDailyGrid() { string json = string.Empty; try { // string selectdate = Request.Params["selectDate"].ToString(); string selectdate = "2015/2"; DateTime getMonthDaily = Convert.ToDateTime(Convert.ToDateTime(selectdate).ToString("yyyy-MM-dd 23:59:59")); int year = getMonthDaily.Year; int month = getMonthDaily.Month; RedirectClickQuery RcQuery = new RedirectClickQuery(); RcQuery.startdate = Convert.ToInt32(year.ToString("0000") + month.ToString("00") + getMonthDaily.Day.ToString("00") + "00"); RcQuery.enddate = Convert.ToInt32(year.ToString("0000") + getMonthDaily.AddMonths(1).Month.ToString("00") + getMonthDaily.Day.ToString("00") + "00"); int dayNum = (getMonthDaily.AddMonths(1) - getMonthDaily).Days; uint group_id = Convert.ToUInt32(Request.Params["group_id"].ToString()); _rdGroupMgr = new RedirectGroupMgr(mySqlConnectionString); List<Redirect> rgli = _rdGroupMgr.QueryRedirectAll(group_id); if (rgli.Count != 0) { foreach (var item in rgli) { if (string.IsNullOrEmpty(RcQuery.redirectstr)) { RcQuery.redirectstr += item.redirect_id; } else { RcQuery.redirectstr += "," + item.redirect_id; } } } List<RedirectClick> reCli = _rdGroupMgr.QueryRedirectClictAll(RcQuery); uint[] redayArray = new uint[dayNum]; uint[] reweekArray = new UInt32[7]; uint[] rehourArray = new UInt32[24]; StringBuilder dayJson = new StringBuilder(); //dayJson.Append("{\"day\":["); StringBuilder weekJson = new StringBuilder(); weekJson.Append("{\"week\":["); StringBuilder hourJson = new StringBuilder(); hourJson.Append("{\"hour\":["); foreach (var item in reCli) { redayArray[item.click_day - 1] += item.click_total; reweekArray[item.click_week] += item.click_total; rehourArray[item.click_hour] += item.click_total; } DateTime Wday = new DateTime(); for (int i = 1; i < redayArray.Length; i++) { int wd = 0; Wday = Convert.ToDateTime(year.ToString("0000") + "/" + month.ToString("00") + "/" + i.ToString("00")); if (Wday.DayOfWeek == DayOfWeek.Sunday || Wday.DayOfWeek == DayOfWeek.Saturday) { wd = 1; } dayJson.Append("{"); dayJson.Append(string.Format("\"datetime\":\"{0}\",\"click_total\":\"{1}\",\"holiday\":\"{2}\"", Wday.ToShortDateString(), redayArray[i - 1], wd)); dayJson.Append("}"); } //dayJson.Append("]}"); for (int i = 0; i < reweekArray.Length; i++) { weekJson.Append("{"); weekJson.Append(string.Format("\"datetime\":\"{0}\",\"click_total\":\"{1}\"", i, reweekArray[i])); weekJson.Append("}"); } weekJson.Append("]}"); //weekJson.ToString().Replace("}{", "},{"); for (int i = 0; i < rehourArray.Length; i++) { hourJson.Append("{"); hourJson.Append(string.Format("\"datetime\":\"{0}\",\"click_total\":\"{1}\"", i, rehourArray[i])); hourJson.Append("}"); } hourJson.Append("]}"); //hourJson.ToString().Replace("}{", "},{"); //json = "{success:true,data:[" + (dayJson.ToString() + weekJson.ToString() + hourJson.ToString()).Replace("}{", "},{") + "]}"; json = "{success:true,data:[" + (dayJson.ToString()).Replace("}{", "},{") + "]}"; } 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:true,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public List<RedirectClick> QueryRedirectClictAll(RedirectClickQuery rcModel) { StringBuilder sql = new StringBuilder(""); try { sql.Append(" SELECT redirect_id,click_id,click_year,click_month,click_week,click_day,click_hour,click_total FROM redirect_click "); sql.Append("where 1=1 "); if (rcModel.startdate != 0) { sql.AppendFormat(" AND click_id >={0}", rcModel.startdate); } if (rcModel.enddate != 0) { sql.AppendFormat(" AND click_id <{0}", rcModel.enddate); } if (!string.IsNullOrEmpty(rcModel.redirectstr)) { sql.AppendFormat(" AND redirect_id in ({0})", rcModel.redirectstr); } sql.Append(" ORDER BY click_id ASC ;"); return _access.getDataTableForObj<RedirectClick>(sql.ToString()); } catch (Exception ex) { throw new Exception("RedirectGroupDao-->QueryRedirectClictAll-->" + ex.Message + sql.ToString(), ex); } }
public List<RedirectClick> QueryRedirectClictAll(RedirectClickQuery rcModel) { try { return _RdGroupdao.QueryRedirectClictAll(rcModel); } catch (Exception ex) { throw new Exception("RedirectGroupMgr.QueryRedirectClictAll-->" + ex.Message, ex); } }