/// <summary>
/// 按键值获取统计数字列表
/// </summary>
/// <param name="keyName"></param>
/// <param name="keyValue"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <returns></returns>
public List <PVKeyCountView> GetKeyCountList(string keyName, string keyValue, DateTime begin, DateTime end)
{
IDatabase db = Assistant.GetDatabases()["We7.CMS.Common"];
SqlStatement sql = new SqlStatement();
string sqlCommandTxt =
@"SELECT DISTINCT [{1}],COUNT([{1}]) AS [pvcount] FROM [PageVisitorHistory] {0}
GROUP BY [{1}] ORDER BY COUNT([{1}]) DESC ";
string strWhere = " where 1=1 ";
if (begin <= end)
{
if (begin != DateTime.MinValue)
{
strWhere += "and [VisitDate] > {0}BEGIN";
DataParameter dp = new DataParameter();
dp.ParameterName = db.DbDriver.Prefix + "BEGIN";
dp.DbType = DbType.DateTime;
dp.SourceColumn = "VisitDate";
dp.Value = begin;
sql.Parameters.Add(dp);
}
if (end != DateTime.MaxValue)
{
strWhere += " and [VisitDate] < {0}END";
DataParameter dp2 = new DataParameter();
dp2.ParameterName = db.DbDriver.Prefix + "END";
dp2.Value = end.AddDays(1);
dp2.DbType = DbType.DateTime;
dp2.SourceColumn = "VisitDate";
sql.Parameters.Add(dp2);
}
}
if (keyValue.Trim() != "")
{
strWhere += " and [{1}] like {0}KEY";
DataParameter dp3 = new DataParameter();
dp3.ParameterName = db.DbDriver.Prefix + "KEY";
dp3.Value = "%" + keyValue.Trim() + "%";
dp3.DbType = DbType.String;
sql.Parameters.Add(dp3);
}
strWhere = string.Format(strWhere, db.DbDriver.Prefix, keyName);
sqlCommandTxt = string.Format(sqlCommandTxt, strWhere, keyName);
sql.SqlClause = sqlCommandTxt;
db.DbDriver.FormatSQL(sql);
List <PVKeyCountView> list = new List <PVKeyCountView>();
using (IConnection conn = db.CreateConnection())
{
DataTable dt = conn.Query(sql);
int total = 0;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
PVKeyCountView kv = new PVKeyCountView();
kv.KeyValue = dr[keyName].ToString();
kv.Count = Int32.Parse(dr["pvcount"].ToString());
total += kv.Count;
list.Add(kv);
}
foreach (PVKeyCountView v in list)
{
v.Percent = (double)v.Count / (double)total * 100;
}
}
}
return(list);
}
/// <summary>
/// 获取月访问统计
/// </summary>
/// <param name="channelID"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <param name="orderKey"></param>
/// <param name="sort"></param>
/// <returns></returns>
public List <PVKeyCountView> GetStatisticsMonthsList
(string channelID, DateTime begin, DateTime end, string orderKey, bool sort)
{
IDatabase db = Assistant.GetDatabases()["We7.CMS.Common"];
SqlStatement sql = new SqlStatement();
string sqlCommandTxt = @"SELECT DISTINCT MONTH([VisitDate]) AS MyMonth, COUNT( MONTH([VisitDate]) ) AS [pvcount] FROM [StatisticsHistory] {0} GROUP BY MONTH([VisitDate]) ORDER BY MONTH([VisitDate])";
//根据条件组织追加相应的sql语句
string strWhere = " where 1=1 ";
if (begin > end)
{
begin = new DateTime(DateTime.Now.Year, 01, 01);
end = new DateTime(DateTime.Now.Year, 01, 31);
}
if (begin != DateTime.MinValue)
{
strWhere += "and [VisitDate] >= {0}BEGIN";
DataParameter dp = new DataParameter();
dp.ParameterName = db.DbDriver.Prefix + "BEGIN";
dp.DbType = DbType.DateTime;
dp.SourceColumn = "VisitDate";
dp.Value = begin;
sql.Parameters.Add(dp);
}
if (end != DateTime.MaxValue)
{
strWhere += " and [VisitDate] < {0}END";
DataParameter dp2 = new DataParameter();
dp2.ParameterName = db.DbDriver.Prefix + "END";
dp2.Value = end.AddDays(1);
dp2.DbType = DbType.DateTime;
dp2.SourceColumn = "VisitDate";
sql.Parameters.Add(dp2);
}
if (channelID.Trim() != "")
{
HttpContext Context = HttpContext.Current;
string chFullUrl = ChannelHelper.GetFullUrl(channelID);
if (chFullUrl != "")
{
strWhere += " and Url like {0}Url";
DataParameter dp3 = new DataParameter();
dp3.ParameterName = db.DbDriver.Prefix + "Url";
dp3.Value = chFullUrl + '%';
dp3.DbType = DbType.String;
dp3.Size = 255;
sql.Parameters.Add(dp3);
}
}
strWhere = string.Format(strWhere, db.DbDriver.Prefix);
sqlCommandTxt = string.Format(sqlCommandTxt, strWhere);
sql.SqlClause = sqlCommandTxt;
sql = db.DbDriver.FormatSQL(sql);
List <PVKeyCountView> list = new List <PVKeyCountView>();
using (IConnection conn = db.CreateConnection())
{
DataTable dt = conn.Query(sql);
int total = 0;
foreach (DataRow dr in dt.Rows)
{
PVKeyCountView kv = new PVKeyCountView();
kv.KeyValue = dr["MyMonth"].ToString();
kv.Count = Int32.Parse(dr["pvcount"].ToString());
total += kv.Count;
list.Add(kv);
}
//判断月份是不是十二个月份
if (list.Count != 12)
{
int num = 1;
for (int i = 1; i <= 12; i++)
{
PVKeyCountView pv = list.Find(delegate(PVKeyCountView view)
{
if (view.KeyValue != "0" + i.ToString() && view.KeyValue.IndexOf("0") == 0)
{
num = 0;
}
return(view.KeyValue == i.ToString() || view.KeyValue == "0" + i.ToString());
});
//如果没有 i 这个月份，则进行追加
if (pv == null)
{
PVKeyCountView kv = new PVKeyCountView();
if (num == 0 && i.ToString().Length < 2)
{
kv.KeyValue = "0" + i.ToString();
}
else
{
kv.KeyValue = i.ToString();
}
kv.Count = 0;
total += kv.Count;
list.Add(kv);
}
}
}
//合算比例
foreach (PVKeyCountView v in list)
{
if (v.Count != 0)
{
v.Percent = (double)v.Count / (double)total * 100;
}
}
}
list.Sort(new DinoComparer(orderKey, sort));
return(list);
}
