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