130 lines
4.9 KiB
Go
130 lines
4.9 KiB
Go
|
package handler
|
|||
|
|
|
|||
|
|
import (
|
|||
|
|
"fmt"
|
|||
|
|
"time"
|
|||
|
|
|
|||
|
|
"github.com/gin-gonic/gin"
|
|||
|
|
|
|||
|
|
"nl-game-api-gin/internal/database"
|
|||
|
|
"nl-game-api-gin/internal/model"
|
|||
|
|
"nl-game-api-gin/internal/room"
|
|||
|
|
"nl-game-api-gin/pkg/resp"
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
// dayStart 返回某时刻所在自然日的零点时间戳（本地时区）
|
|||
|
|
func dayStart(t time.Time) int64 {
|
|||
|
|
return time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, t.Location()).Unix()
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// AdminAnalytics 运营数据分析：日活趋势、游玩时长、平均时长、最火游戏、当前在线
|
|||
|
|
// 日活口径：当天有登录 / 游玩 / 签到 / 对战任一行为的去重用户数
|
|||
|
|
func AdminAnalytics(c *gin.Context) {
|
|||
|
|
now := time.Now()
|
|||
|
|
todayStart := dayStart(now)
|
|||
|
|
const trendDays = 14
|
|||
|
|
since := todayStart - int64(trendDays-1)*86400
|
|||
|
|
// ---- 日活趋势：四张行为表按天去重合并 ----
|
|||
|
|
type dayRow struct {
|
|||
|
|
D string `gorm:"column:d"`
|
|||
|
|
N int `gorm:"column:n"`
|
|||
|
|
}
|
|||
|
|
var rows []dayRow
|
|||
|
|
database.DB.Raw(`SELECT d, COUNT(DISTINCT uid) AS n FROM (
|
|||
|
|
SELECT user_id AS uid, FROM_UNIXTIME(created_at, '%Y-%m-%d') AS d FROM login_logs WHERE created_at >= ?
|
|||
|
|
UNION ALL SELECT user_id, FROM_UNIXTIME(created_at, '%Y-%m-%d') FROM game_records WHERE created_at >= ?
|
|||
|
|
UNION ALL SELECT user_id, FROM_UNIXTIME(created_at, '%Y-%m-%d') FROM sign_ins WHERE created_at >= ?
|
|||
|
|
UNION ALL SELECT user_id, FROM_UNIXTIME(created_at, '%Y-%m-%d') FROM battle_records WHERE created_at >= ?
|
|||
|
|
) t GROUP BY d ORDER BY d`, since, since, since, since).Scan(&rows)
|
|||
|
|
dauMap := map[string]int{}
|
|||
|
|
for _, r := range rows {
|
|||
|
|
dauMap[r.D] = r.N
|
|||
|
|
}
|
|||
|
|
// 补齐没有数据的日期（趋势图连续）
|
|||
|
|
trend := make([]gin.H, 0, trendDays)
|
|||
|
|
for i := 0; i < trendDays; i++ {
|
|||
|
|
day := time.Unix(todayStart-int64(trendDays-1-i)*86400, 0)
|
|||
|
|
key := day.Format("2006-01-02")
|
|||
|
|
trend = append(trend, gin.H{"date": day.Format("01-02"), "full": key, "count": dauMap[key]})
|
|||
|
|
}
|
|||
|
|
dauToday := dauMap[now.Format("2006-01-02")]
|
|||
|
|
dauYesterday := dauMap[time.Unix(todayStart-86400, 0).Format("2006-01-02")]
|
|||
|
|
// ---- 游玩时长与局数（单机 + 联机对战合并） ----
|
|||
|
|
type sumRow struct {
|
|||
|
|
Dur int64 `gorm:"column:dur"`
|
|||
|
|
Cnt int64 `gorm:"column:cnt"`
|
|||
|
|
}
|
|||
|
|
sumOf := func(table string, from int64) sumRow {
|
|||
|
|
var r sumRow
|
|||
|
|
database.DB.Raw(fmt.Sprintf(
|
|||
|
|
`SELECT COALESCE(SUM(duration),0) AS dur, COUNT(*) AS cnt FROM %s WHERE created_at >= ?`, table), from).Scan(&r)
|
|||
|
|
return r
|
|||
|
|
}
|
|||
|
|
gToday, gTotal := sumOf("game_records", todayStart), sumOf("game_records", 0)
|
|||
|
|
bToday, bTotal := sumOf("battle_records", todayStart), sumOf("battle_records", 0)
|
|||
|
|
secToday := gToday.Dur + bToday.Dur
|
|||
|
|
secTotal := gTotal.Dur + bTotal.Dur
|
|||
|
|
playsToday := gToday.Cnt + bToday.Cnt
|
|||
|
|
playsTotal := gTotal.Cnt + bTotal.Cnt
|
|||
|
|
avgSec := int64(0)
|
|||
|
|
if playsTotal > 0 {
|
|||
|
|
avgSec = secTotal / playsTotal
|
|||
|
|
}
|
|||
|
|
avgSecToday := int64(0)
|
|||
|
|
if playsToday > 0 {
|
|||
|
|
avgSecToday = secToday / playsToday
|
|||
|
|
}
|
|||
|
|
// ---- 最火游戏：近 7 天局数排序（单机+对战合并），附平均时长与历史总局数 ----
|
|||
|
|
weekAgo := now.Unix() - 7*86400
|
|||
|
|
type hotRow struct {
|
|||
|
|
GameID int `gorm:"column:game_id"`
|
|||
|
|
Plays int `gorm:"column:plays"`
|
|||
|
|
Dur int64 `gorm:"column:dur"`
|
|||
|
|
}
|
|||
|
|
var hot []hotRow
|
|||
|
|
database.DB.Raw(`SELECT game_id, COUNT(*) AS plays, COALESCE(SUM(duration),0) AS dur FROM (
|
|||
|
|
SELECT game_id, duration FROM game_records WHERE created_at >= ?
|
|||
|
|
UNION ALL SELECT game_id, duration FROM battle_records WHERE created_at >= ?
|
|||
|
|
) t GROUP BY game_id ORDER BY plays DESC LIMIT 8`, weekAgo, weekAgo).Scan(&hot)
|
|||
|
|
ids := make([]int, 0, len(hot))
|
|||
|
|
for _, h := range hot {
|
|||
|
|
ids = append(ids, h.GameID)
|
|||
|
|
}
|
|||
|
|
gameMap := map[int]model.Game{}
|
|||
|
|
if len(ids) > 0 {
|
|||
|
|
var games []model.Game
|
|||
|
|
database.DB.Where("id IN ?", ids).Find(&games)
|
|||
|
|
for _, g := range games {
|
|||
|
|
gameMap[g.ID] = g
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
hotGames := make([]gin.H, 0, len(hot))
|
|||
|
|
for _, h := range hot {
|
|||
|
|
g, ok := gameMap[h.GameID]
|
|||
|
|
if !ok {
|
|||
|
|
continue
|
|||
|
|
}
|
|||
|
|
avg := int64(0)
|
|||
|
|
if h.Plays > 0 {
|
|||
|
|
avg = h.Dur / int64(h.Plays)
|
|||
|
|
}
|
|||
|
|
hotGames = append(hotGames, gin.H{
|
|||
|
|
"game_id": g.ID, "code": g.Code, "name": g.Name, "icon": g.Icon, "category": g.Category,
|
|||
|
|
"plays_7d": h.Plays, "avg_seconds": avg, "play_count": g.PlayCount,
|
|||
|
|
})
|
|||
|
|
}
|
|||
|
|
resp.OK(c, gin.H{
|
|||
|
|
"dau_today": dauToday, // 今日日活
|
|||
|
|
"dau_yesterday": dauYesterday, // 昨日日活（对比）
|
|||
|
|
"dau_trend": trend, // 近 14 天日活趋势
|
|||
|
|
"online_now": room.OnlineCount(), // 当前在线人数（WebSocket 连接数）
|
|||
|
|
"play_seconds_today": secToday, // 今日游玩总时长（秒）
|
|||
|
|
"play_seconds_total": secTotal, // 累计游玩总时长（秒）
|
|||
|
|
"plays_today": playsToday, // 今日总局数
|
|||
|
|
"plays_total": playsTotal, // 累计总局数
|
|||
|
|
"avg_seconds": avgSec, // 平均单局时长（秒，历史）
|
|||
|
|
"avg_seconds_today": avgSecToday, // 平均单局时长（秒，今日）
|
|||
|
|
"hot_games": hotGames, // 近 7 天最火游戏 Top8
|
|||
|
|
})
|
|||
|
|
}
|