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