/// <summary>
/// 查询
/// </summary>
/// <param name="begin">开始时间</param>
/// <param name="end">结束时间</param>
/// <param name="page">页码</param>
/// <param name="rows">页量</param>
/// <param name="dicWhere">条件：键=值</param>
/// <returns></returns>
public static LoggingResultVM Query(DateTime begin, DateTime end, int page = 1, int rows = 30, Dictionary <string, string> dicWhere = null)
{
var vm = new LoggingResultVM();
var sql = GetSqlForQuery(begin, end, out SQLiteHelper db, out int lost, out List <string> listPreSql);
if (sql != null)
{
var whereSql = DicWhereJoin(dicWhere);
if (!string.IsNullOrWhiteSpace(whereSql))
{
whereSql = " where " + whereSql;
}
var totalSql = $"select count(1) as Total from ({sql}) as a {whereSql}";
vm.Total = Convert.ToInt32(db.ExecuteScalar(totalSql, listPreSql));
sql = $"select * from ({sql}) as a {whereSql} order by LogCreateTime desc limit " + rows + " offset " + (page - 1) * rows;
vm.Data = db.Query(sql, listPreSql).Tables[0];
vm.Lost = lost;
}
return(vm);
}
/// <summary>
/// 统计属性排行
/// </summary>
/// <param name="type">类型（0：今天，-1：昨天，-7：最近7天，-30：最近30天）</param>
/// <param name="field">字段列</param>
/// <param name="dicWhere">条件</param>
/// <returns></returns>
public static LoggingResultVM StatsTop(int type, string field, Dictionary <string, string> dicWhere = null)
{
var vm = new LoggingResultVM();
var now = DateTime.Now;
var end = now;
DateTime begin;
switch (type)
{
case 0:
case -1:
{
begin = now.AddDays(type).Date;
end = DateTime.Parse(now.AddDays(type).ToString("yyyy-MM-dd 23:59:59.999"));
}
break;
default:
{
type++;
begin = now.AddDays(type).Date;
}
break;
}
var sql = GetSqlForQuery(begin, end, out SQLiteHelper db, out int lost, out List <string> listPreSql);
if (sql != null)
{
var listName = new LoggingModel().GetType().GetProperties().ToList().Select(x => x.Name).ToList();
if (listName.Contains(field))
{
var whereSql = DicWhereJoin(dicWhere);
if (!string.IsNullOrWhiteSpace(whereSql))
{
whereSql += " and ";
}
sql = $"select {field} as field,count({field}) as total from ({sql}) where {whereSql} LogCreateTime>={begin.Date.Ticks} and LogCreateTime<={end.Ticks} group by {field} order by total desc";
var dt = db.Query(sql, listPreSql).Tables[0];
while (dt.Rows.Count > 50)
{
dt.Rows.RemoveAt(dt.Rows.Count - 1);
}
vm.Lost = lost;
vm.Data = dt;
}
}
return(vm);
}
/// <summary>
/// 统计PV/UV
/// </summary>
/// <param name="type">类型（0：今天，-1：昨天，-7：最近7天，-30：最近30天）</param>
/// <param name="dicWhere">条件</param>
/// <returns></returns>
public static LoggingResultVM StatsPVUV(int type, Dictionary <string, string> dicWhere = null)
{
var vm = new LoggingResultVM();
var now = DateTime.Now;
var begin = now;
var end = now;
switch (type)
{
//今天
//昨天
case 0:
case -1:
{
begin = now.AddDays(type).Date;
end = DateTime.Parse(now.AddDays(type).ToString("yyyy-MM-dd 23:59:59.999"));
}
break;
//最近
default:
{
type++;
begin = now.AddDays(type).Date;
}
break;
}
var sql = GetSqlForQuery(begin, end, out SQLiteHelper db, out int lost, out List <string> listPreSql);
vm.Lost = lost;
if (sql != null)
{
var whereSql = DicWhereJoin(dicWhere);
if (!string.IsNullOrWhiteSpace(whereSql))
{
whereSql += " and ";
}
sql = $"select LogCreateTime,LogIp from ({sql}) where {whereSql} LogCreateTime>=" + begin.Date.Ticks + " and LogCreateTime<=" + end.Ticks;
var query = db.Query(sql, listPreSql).Tables[0].Select();
switch (type)
{
case 0:
case -1:
{
vm.Data = query.GroupBy(x => new DateTime(Convert.ToInt64(x["LogCreateTime"])).ToString("yyyy-MM-dd HH"))
.OrderByDescending(x => x.Key).Select(x => new
{
time = x.Key.Split(' ')[1] + ":00",
pv = x.Count(),
ip = x.Select(p => p["LogIp"].ToString()).Distinct().Count()
}).ToList();
}
break;
default:
{
vm.Data = query.GroupBy(x => new DateTime(Convert.ToInt64(x["LogCreateTime"])).ToString("yyyy-MM-dd"))
.OrderByDescending(x => x.Key).Select(x => new
{
time = x.Key,
pv = x.Count(),
ip = x.Select(p => p["LogIp"].ToString()).Distinct().Count()
}).ToList();
}
break;
}
}
return(vm);
}
