/// <summary> /// 按广告分析 /// </summary> /// <param name="flow"></param> /// <returns></returns> public DataTable GetAllAdDetail(FlowInfo flow) { StringBuilder sb = new StringBuilder(); if (flow.AdId.HasValue) { sb.AppendFormat(" and AdId = {0} ", flow.AdId); } if (flow.FlowUserId.HasValue) { sb.AppendFormat(" and FlowUserId = {0} ", flow.FlowUserId); } if (flow.AdUserID.HasValue) { sb.AppendFormat(" and AdUserId = {0} ", flow.AdUserID); } string cmd = string.Format(@"select time ,AdId ,count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount ,count(distinct(clientid)) /count(*) as useravg from [AdBrowseHistory] where time={0} {1} group by time,AdId", flow.Time.ToString("yyyyMMdd"), sb); ChartPara cp = new ChartPara(); cp.CommandText = cmd; DataTable table = acc.GetTable(cp); return(table); }
/// <summary> /// 生成当天的最大小时数据 /// </summary> /// <param name="userid"></param> /// <param name="time"></param> /// <returns></returns> public DataTable GetBrowseHour(FlowInfo flow) { StringBuilder sb = new StringBuilder(); if (flow.AdId.HasValue) { sb.AppendFormat(" and AdId = {0} ", flow.AdId); } if (flow.FlowUserId.HasValue) { sb.AppendFormat(" and FlowUserId = {0} ", flow.FlowUserId); } if (flow.AdUserID.HasValue) { sb.AppendFormat(" and AdUserId = {0} ", flow.AdUserID); } ChartPara cp = new ChartPara(); cp.CommandText = string.Format(@"select * from ( select count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount ,count(distinct(clientid)) /count(*) as useravg ,left(convert(varchar(10), CreateDate, 108), 2) as time from [AdBrowseHistory] where time = {0} {1} group by left(convert(varchar(10), CreateDate, 108), 2) ) a order by a.time asc", flow.Time.ToString("yyyyMMdd"), sb); //数据 DataTable table = acc.GetTable(cp); return(table); }
public DataTable GetTable(string cmd) { ChartPara cp = new ChartPara(); cp.CommandText = cmd; return(acc.GetTable(cp)); }
/// <summary> /// 获取某月的历史数据 /// </summary> /// <param name="userid"></param> /// <returns></returns> public EChartInfoJson GetAdBrowseDay(int userid) { ChartPara cp = new ChartPara(); cp.CommandText = string.Format(@"select * from ( select AdId, count(distinct(ClientIp)) as clickcount, time as time from[dbo].[AdBrowseHistory] where UserId={0} group by AdId, time ) a order by a.time asc ", userid); //时间数据 var hours = CommonBLL.GetDayTable(); //数据 DataTable table = acc.GetTable(cp); //图形 Entity.EChartInfoJson chart = new Entity.EChartInfoJson(); chart.legend = new List <string>(); chart.xAxis = hours; chart.series = new List <Entity.serie>(); var adlist = AdPageInfoBLL.Instance.GetModels(new AdPageInfoPara() { UserId = userid }); chart.xAxis = hours; foreach (var item in adlist) { chart.legend.Add(item.Title); Entity.serie data = new Entity.serie(); data.name = item.Title; data.data = new List <string>(); data.stack = "浏览量"; data.type = "line"; foreach (var hour in hours) { var row = table.Select(string.Format("AdId={0} and time='{1}'", item.Id, hour)); if (row.Length == 0) { data.data.Add("0"); } else { data.data.Add(row[0]["clickcount"].ToString()); } } chart.series.Add(data); } return(chart); }
public override int InsertInto(ChartPara para) { CodeCommand command = new CodeCommand(); command.CommandText = para.CommandText; var rowcount = DbProxyFactory.Instance.Proxy.ExecuteNonQuery(command); return(rowcount); }
public override DataTable GetTable(ChartPara para) { CodeCommand command = new CodeCommand(); command.CommandText = para.CommandText; var table = DbProxyFactory.Instance.Proxy.ExecuteTable(command); return(table); }
/// <summary> /// 通用汇总,groupby必须填写 /// </summary> /// <param name="flow"></param> /// <returns></returns> public DataTable GetAnalysis(QueryGroupInfo flow) { StringBuilder sb = new StringBuilder(); if (flow.AdId.HasValue) { sb.AppendFormat(" and AdId = {0} ", flow.AdId); } if (flow.FlowUserId.HasValue) { sb.AppendFormat(" and FlowUserId = {0} ", flow.FlowUserId); } if (flow.AdUserID.HasValue) { sb.AppendFormat(" and AdUserId = {0} ", flow.AdUserID); } if (flow.Time.HasValue) { sb.AppendFormat(" and time = {0} ", flow.Time.Value.ToString("yyyyMMdd")); } if (flow.TimeStart.HasValue) { sb.AppendFormat(" and time >= {0} ", flow.TimeStart.Value.ToString("yyyyMMdd")); } if (flow.TimeEnd.HasValue) { sb.AppendFormat(" and time <= {0} ", flow.TimeEnd.Value.ToString("yyyyMMdd")); } string orderby = ""; if (!string.IsNullOrEmpty(flow.OrderBy)) { orderby = " order by " + flow.OrderBy; } string cmd = string.Format(@" select {1} ,count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount ,count(distinct(clientid)) /count(*) as useravg from [LogBrowseHistory] where 1=1 {0} group by {1} {2}", sb, flow.GroupBy, orderby); ChartPara cp = new ChartPara(); cp.CommandText = cmd; DataTable table = acc.GetTable(cp); return(table); }
/// <summary> /// 分页面统计详情 /// </summary> /// <param name="userid"></param> /// <param name="time"></param> /// <returns></returns> public DataTable GetPagesAnalysis(int aduserid, DateTime time) { string cmd = string.Format(@"select adurl,count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount ,count(distinct(clientid)) /count(*) as useravg from AdBrowseHistory where AdUserId={0} and time={1} group by adurl", aduserid, time.ToString("yyyyMMdd")); ChartPara cp = new ChartPara(); cp.CommandText = cmd; DataTable table = acc.GetTable(cp); return(table); }
public DataTable GetBrowseHour(int aduserid, DateTime time) { ChartPara cp = new ChartPara(); cp.CommandText = string.Format(@"select * from ( select count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount ,count(distinct(clientid)) /count(*) as useravg ,left(convert(varchar(10), CreateDate, 108), 2) as time from [AdBrowseHistory] where AdUserId={0} and time = {1} group by left(convert(varchar(10), CreateDate, 108), 2) ) a order by a.time asc", aduserid, time.ToString("yyyyMMdd")); //数据 DataTable table = acc.GetTable(cp); return(table); }
/// <summary> /// 汇总相关数据 /// </summary> /// <param name="time"></param> public void SummaryHistoryUserLogBrowse(DateTime time) { string timeid = time.ToString("yyyyMMdd"); string cmd = @"select time,adid,aduserid,count(*) pvcount,count(distinct(clientid)) uvcount,count(distinct(clientip)) ipcount from LogBrowseHistory where Time=$TIME$ group by time,adid,aduserid order by adid"; //替换汇总时间 cmd = cmd.Replace("$TIME$", timeid); ChartPara cp = new ChartPara(); cp.CommandText = cmd; DataTable table = m_acc.GetTable(cp); //获取所有的广告信息,获取相关的价格信息 var list = AdPageInfoBLL.Instance.GetModels(new AdPageInfoPara()); for (int i = 0; i < table.Rows.Count; i++) { HistoryUserLogBrowseVO his = new HistoryUserLogBrowseVO(); his.AdId = int.Parse(table.Rows[i]["adid"].ToString()); his.CreateUserId = 0; his.CreateDate = DateTime.Now; his.IpCount = int.Parse(table.Rows[i]["ipcount"].ToString()); his.UvCount = int.Parse(table.Rows[i]["uvcount"].ToString()); his.PvCount = int.Parse(table.Rows[i]["pvcount"].ToString()); his.UserId = int.Parse(table.Rows[i]["aduserid"].ToString()); his.Time = int.Parse(timeid); var adinfo = list.SingleOrDefault(p => p.Id == his.AdId); if (adinfo != null) { his.Price = adinfo.Money; his.Money = his.Price * his.IpCount; } HistoryUserLogBrowseBLL.Instance.Add(his); } }
/// <summary> /// 数据迁移功能 /// </summary> /// <param name="time">时间</param> public void TransferDay(DateTime time) { if (time.ToString("yyyyMMdd") == DateTime.Now.ToString("yyyyMMdd")) { //如果是当前时间,不允许转入数据 return; } string cmd = string.Format("insert into LogBrowseHistory select [Url],[ClientIp],[BrowseType],[CreateDate],[AdId],[AdUserId],[FlowUserId],[AdUrl],[Money],[IsMoney],[Time],[ClientId],[IsMobile],[ReferrerUrl],[BrowseName],[BrowseVersion],[OsName],[Country],[Area],[Region],[City],[County],[Isp],[IpSource] from LogBrowse where Time={0}", time.ToString("yyyyMMdd")); ChartPara cp = new ChartPara(); cp.CommandText = cmd; var rowscount = m_acc.InsertInto(cp); LogBrowseBLL.Instance.Delete(new LogBrowsePara() { Time = int.Parse(time.ToString("yyyyMMdd")) }); //迁移完成之后,更新汇总信息 SummaryHistoryUserLogBrowse(time); }
/// <summary> /// 通用汇总,groupby必须填写 /// </summary> /// <param name="flow"></param> /// <returns></returns> public DataTable GetAnalysis(QueryGroupInfo flow) { StringBuilder sb = new StringBuilder(); if (flow.AdId.HasValue) { sb.AppendFormat(" and AdId = {0} ", flow.AdId); } if (flow.FlowUserId.HasValue) { sb.AppendFormat(" and FlowUserId = {0} ", flow.FlowUserId); } if (flow.AdUserID.HasValue) { sb.AppendFormat(" and AdUserId = {0} ", flow.AdUserID); } if (flow.Time.HasValue) { sb.AppendFormat(" and time = {0} ", flow.Time.Value.ToString("yyyyMMdd")); } string cmd = string.Format(@" select {1} ,count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount ,count(distinct(clientid)) /count(*) as useravg from [AdBrowse] where 1=1 {0} group by {1}", sb, flow.GroupBy); ChartPara cp = new ChartPara(); cp.CommandText = cmd; DataTable table = acc.GetTable(cp); return(table); }
/// <summary> /// 分小时粒度汇总 /// </summary> /// <param name="aduserid"></param> /// <param name="time"></param> /// <returns></returns> public DataTable GetBrowseHour(QcodeQueryInfo flow) { StringBuilder sb = new StringBuilder(); if (flow.AdId.HasValue) { sb.AppendFormat(" and AdId = {0} ", flow.AdId); } if (flow.AdUserId.HasValue) { sb.AppendFormat(" and AdUserId = {0} ", flow.AdUserId); } if (!string.IsNullOrEmpty(flow.Url)) { sb.AppendFormat(" and Url = '{0}' ", flow.Url); } if (flow.Time.HasValue) { sb.AppendFormat(" and time = {0} ", flow.Time.Value); } ChartPara cp = new ChartPara(); cp.CommandText = string.Format(@"select * from ( select left(convert(varchar(10), CreateDate, 108), 2) as time ,count(*) as pvcount ,count(distinct(clientid)) as uvcount ,count(distinct(clientip)) as ipcount from [LogAdQcode] where 1=1 {0} group by left(convert(varchar(10), CreateDate, 108), 2) ) a order by a.time asc", sb); //数据 DataTable table = chartcacc.GetTable(cp); return(table); }
/// <summary> /// 获取某月的历史数据 /// </summary> /// <param name="userid"></param> /// <returns></returns> public EChartInfoJson GetHistoryMonthDays(FlowInfo flow) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" [Time] = {0} ", flow.Time.ToString("yyyyMM")); if (flow.FlowUserId.HasValue) { sb.AppendFormat(" and FlowUserId={0} ", flow.FlowUserId); } if (flow.AdId.HasValue) { sb.AppendFormat(" and AdId={0} ", flow.AdId); } ChartPara cp = new ChartPara(); cp.CommandText = string.Format(@"select * from ( select AdId, count(distinct(ClientIp)) as clickcount, time as time from [AdBrowseHistory] where {0} group by AdId, time ) a order by a.time asc ", sb); //时间数据 var hours = CommonBLL.GetDayTable(int.Parse(flow.Time.ToString("yyyyMM"))); //数据 DataTable table = acc.GetTable(cp); //图形 Entity.EChartInfoJson chart = new Entity.EChartInfoJson(); chart.legend = new List <string>(); chart.xAxis = hours; chart.series = new List <Entity.serie>(); var adlist = AdUserPageBLL.Instance.GetModels(new AdUserPagePara() { FlowUserId = flow.FlowUserId.Value }); chart.xAxis = hours; foreach (var item in adlist) { string title = AdPageInfoBLL.Instance.GetTitleById(item.AdPageId); chart.legend.Add(title); Entity.serie data = new Entity.serie(); data.name = title; data.data = new List <string>(); data.stack = "浏览量"; data.type = "line"; foreach (var hour in hours) { var row = table.Select(string.Format("AdId={0} and time='{1}'", item.AdPageId, hour)); if (row.Length == 0) { data.data.Add("0"); } else { data.data.Add(row[0]["clickcount"].ToString()); } } chart.series.Add(data); } return(chart); }
/// <summary> /// 执行SQL /// </summary> /// <param name="para"></param> /// <returns></returns> public abstract int InsertInto(ChartPara para);
/// <summary> /// 获取图形 /// </summary> /// <param name="para"></param> /// <returns></returns> public abstract DataTable GetTable(ChartPara para);