Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        /// <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);
        }