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