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