/// <summary>
/// 按工作室分析
/// </summary>
/// <param name="flow"></param>
/// <returns></returns>
public DataTable GetAdFlowDetail(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
,FlowUserId
,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,FlowUserId", flow.Time.ToString("yyyyMMdd"), sb);
ChartPara cp = new ChartPara();
cp.CommandText = cmd;
DataTable table = acc.GetTable(cp);
return(table);
}
public DataTable GetTable(string cmd)
{
ChartPara cp = new ChartPara();
cp.CommandText = cmd;
return(acc.GetTable(cp));
}
/// <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="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>
/// 通用汇总，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);
}