Files
Tory 0ae6a2c1d4 Issue#6·B2: 三契约页 1:1 实装(v3-2-tailwind.html 逐行直抄,真实数据)—— ①#p-dash 运营总览(895-1005):状态横幅(五子系统 badge+summary 指标串)+stat 双行 6+4 卡+chart.js 发送趋势(teal/sky 双线渐变填充,近7/30 切换)+业务分布动态 donut(SVG dasharray)+通道实时监控表(monitor 快照 5s 轮询,负载条/失败率/P50)+系统健康(overview 10s 轮询,up/degraded/down 三态 badge)+最近动态(诚实空态)+快捷操作 4+通道配置快照;缺数据项显式 —(今日收入/TPM);②#p-acct 帐号管理(1006-1034):单卡一体筛选区(input!w-56+双 select+搜索/重置+已选N)+8 列原生 table(彩色首字圆徽轮转/角色 badge/余额右对齐/停用灰)+分页条(共N条+页码窗口),/accounts 真实分页;③#p-chan 通道编辑(1035-1059):双卡表单(基本参数 6 字段必填 4*+吞吐风控 5 字段+启用 switch)+操作条(探测期提示/取消/保存),协议键↔protocol+version 映射,编辑回填 host:port 合并;④api 层补 P2 三端点封装(statsApi.biz/realtime+systemApi.overview+channelApi.monitor+三类型);⑤后端根修:stats_handler.go 两处 AS key 撞 MySQL 8.0 保留字致 /stats/biz 500(反引号转义,P2 遗留 bug B2 首次消费暴露);⑥验收:vue-tsc 0/build ✓(dash 懒加载 chunk gzip 65KB)/Chrome 冒烟 12/12(横幅真实判定/健康 5 徽章/acct 真实账号 3 行/chan 双卡)/stat-card computed style 双会话对拍一致(radius16/pad20/flex/gap16/shadow 同 17890);网关重建重启(11-16 旧二进制→含 P2 端点)
2026-11-18 10:58:18 +08:00

461 lines
16 KiB
Go

// stats_handler.go Day68:运营总览(/home)真实数据——四指标卡 summary + 近 N 日
// trend 两端点。契约=web/src/api/stats.ts(FE-Design-Audit B1,此前仅 msw mock
// 承载);响应形状与 mock ok() 逐字段一致({code,msg,data})。
//
// 性能(Day64/67 教训前置):趋势/历史不直查明细(340 万行×7 天=700 万条目扫描,
// HDD 5s+/次×60s 自动刷新不可持续)——日汇总表 stats_daily 由后台 60s ticker 增量
// UPSERT(当天行滚动更新+跨天补昨日终值,幂等),API 只读汇总表毫秒级;聚合 SQL
// 三条纯覆盖索引 COUNT(总量 idx_detail_created/成败 idx_status_time(status,
// created_at))零随机回表。
//
// 口径:success_rate=终态成功率 success/(success+failed)(在途0/提交1/未知4/拦截5
// 不计分母,运营语义);today_sent=全部提交含在途。balance_alert_count 同
// service/account/warning.go ScanOnce 口径(启用规则×启用账号×可用额度
// balance+credit_balance<阈值)。pending_audit_count=message_task.audit_status=0。
//
// 角色范围:admin/ops/service=全平台(stats_daily);group/customer=仅自己账号
// (明细直查——自有量级小;大客户场景预留账号维度汇总,见 entity.StatsDaily 注)。
package v1
import (
"math"
"strconv"
"time"
"github.com/gin-gonic/gin"
"go.uber.org/zap"
"gorm.io/gorm"
"gorm.io/gorm/clause"
"go-sms/internal/middleware"
"go-sms/internal/model/entity"
"go-sms/internal/pkg/response"
)
const (
// statsRefreshEvery 汇总刷新周期(与前端 Dashboard 60s 自动刷新同频)。
statsRefreshEvery = time.Minute
// statsTrendMaxDays 趋势最大天数(防 days 超大;mock 契约默认 7)。
statsTrendMaxDays = 30
// statsRefresherBackfillDays 启动回补天数(趋势窗余量)。
statsRefresherBackfillDays = 30
)
type statsHandler struct{ db *gorm.DB }
// registerStatsRoutes 注册 stats 域端点(查询类,JWT 保护即可,不挂 Casbin)。
func registerStatsRoutes(protected *gin.RouterGroup, db *gorm.DB) {
h := &statsHandler{db: db}
protected.GET("/stats/summary", h.summary)
protected.GET("/stats/trend", h.trend)
// Issue#6·P2:Dashboard 业务分布与实时速率(契约 v3.2.1)。
protected.GET("/stats/biz", h.biz)
protected.GET("/stats/realtime", h.realtime)
}
// statsSummaryDTO 四指标卡(mock 契约逐字段)。
type statsSummaryDTO struct {
TodaySent int64 `json:"today_sent"`
YesterdaySent int64 `json:"yesterday_sent"`
SuccessRate float64 `json:"success_rate"` // 0~1(终态口径)
SuccessRateDelta float64 `json:"success_rate_delta"` // 今-昨(pp 差值)
BalanceAlertCount int64 `json:"balance_alert_count"` // 余额低于预警线账号数
PendingAuditCount int64 `json:"pending_audit_count"` // 待人工审核条数
}
// trendDTO 趋势点(mock 契约:date='MM-DD')。
type trendDTO struct {
Date string `json:"date"`
Sent int64 `json:"sent"`
Success int64 `json:"success"`
}
// statsClaims 从 gin 上下文取登录 Claims(protected 组内恒在;nil 仅测试场景)。
func statsClaims(c *gin.Context) *middleware.Claims {
v, _ := c.Get(middleware.ContextKeyClaims)
claims, _ := v.(*middleware.Claims)
return claims
}
// statsIsPlatform 平台口径角色判定(admin/ops/service);其余(group/customer)
// 走自己账号范围。
func statsIsPlatform(c *gin.Context) bool {
claims := statsClaims(c)
if claims == nil {
return true
}
for _, r := range claims.Roles {
if r == "admin" || r == "ops" || r == "service" {
return true
}
}
return false
}
// statsRate 终态成功率(分母 0 返回 0)。
func statsRate(succ, fail int64) float64 {
if d := succ + fail; d > 0 {
return float64(succ) / float64(d)
}
return 0
}
// platformDay 读汇总表单日三元组(无行=0,如启动回补前的首分钟)。
func (h *statsHandler) platformDay(day time.Time) (sub, succ, fail int64) {
var row entity.StatsDaily
if err := h.db.Where("day = ?", day.Format("2006-01-02")).Take(&row).Error; err != nil {
return 0, 0, 0
}
return row.Submitted, row.Success, row.Failed
}
// accountDay 客户口径单日直查(自有量级小,一条聚合可接受;SUM 布尔两库通用)。
func (h *statsHandler) accountDay(accountID int64, day time.Time) (sub, succ, fail int64) {
type r struct {
Total int64
Succ int64
Fail int64
}
var row r
if err := h.db.Table("message_detail").
Select("COUNT(*) AS total, COALESCE(SUM(status = 2), 0) AS succ, COALESCE(SUM(status = 3), 0) AS fail").
Where("account_id = ? AND created_at >= ? AND created_at < ?",
accountID, day, day.AddDate(0, 0, 1)).
Take(&row).Error; err != nil {
return 0, 0, 0
}
return row.Total, row.Succ, row.Fail
}
// balanceAlertCount 余额预警计数(平台=全量规则;客户=自己 0/1)。Raw 绕 gorm
// 软删过滤需手工 deleted_at IS NULL(与 warning.go Take 自动过滤口径一致)。
func (h *statsHandler) balanceAlertCount(c *gin.Context, platform bool) int64 {
if platform {
var n int64
h.db.Raw(`SELECT COUNT(*) FROM system_alert_rule r
JOIN account_user a ON a.id = r.target_id AND a.status = 1 AND a.deleted_at IS NULL
WHERE r.status = 1 AND r.target_type = 'account' AND r.metric = 'balance'
AND (a.balance + a.credit_balance) < r.threshold`).Scan(&n)
return n
}
claims := statsClaims(c)
if claims == nil {
return 0
}
var rule entity.SystemAlertRule
if err := h.db.Where(
"status = 1 AND target_type = ? AND metric = ? AND target_id = ?",
"account", "balance", claims.AccountID,
).Take(&rule).Error; err != nil {
return 0 // 未配置阈值不计
}
var acct entity.AccountUser
if err := h.db.Select("balance", "credit_balance").Take(&acct, claims.AccountID).Error; err != nil {
return 0
}
if float64(acct.Balance+acct.CreditBalance) < rule.Threshold {
return 1
}
return 0
}
// pendingAuditCount 待人工审核计数(audit_status=0;idx_audit 覆盖快扫)。
func (h *statsHandler) pendingAuditCount(c *gin.Context, platform bool) int64 {
q := h.db.Table("message_task").Where("audit_status = 0")
if !platform {
if claims := statsClaims(c); claims != nil {
q = q.Where("account_id = ?", claims.AccountID)
}
}
var n int64
q.Count(&n)
return n
}
// summary GET /stats/summary——四指标卡。
func (h *statsHandler) summary(c *gin.Context) {
now := time.Now()
// 自然日窗口(00:00~24:00):与 statsAggregateDay 聚合口径一致,且避免
// 「行 created_at 与请求时刻同毫秒」的窗口边界竞争。
today := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
yesterday := today.AddDate(0, 0, -1)
platform := statsIsPlatform(c)
var tSub, tSucc, tFail, ySub, ySucc, yFail int64
if platform {
tSub, tSucc, tFail = h.platformDay(today)
ySub, ySucc, yFail = h.platformDay(yesterday)
} else {
claims := statsClaims(c)
tSub, tSucc, tFail = h.accountDay(claims.AccountID, today)
ySub, ySucc, yFail = h.accountDay(claims.AccountID, yesterday)
}
response.Success(c, statsSummaryDTO{
TodaySent: tSub,
YesterdaySent: ySub,
SuccessRate: statsRate(tSucc, tFail),
SuccessRateDelta: statsRate(tSucc, tFail) - statsRate(ySucc, yFail),
BalanceAlertCount: h.balanceAlertCount(c, platform),
PendingAuditCount: h.pendingAuditCount(c, platform),
})
}
// trend GET /stats/trend?days=7——近 N 日趋势(缺日补零;date=MM-DD 同 mock)。
func (h *statsHandler) trend(c *gin.Context) {
days := intQuery(c, "days", 7)
if days < 1 {
days = 7
}
if days > statsTrendMaxDays {
days = statsTrendMaxDays
}
now := time.Now()
// 自然日起点(与 summary/聚合同口径,避免毫秒级窗口边界竞争)。
start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).
AddDate(0, 0, -(days - 1))
byDay := map[string][2]int64{}
if statsIsPlatform(c) {
var rows []entity.StatsDaily
if err := h.db.Where("day >= ?", start.Format("2006-01-02")).
Order("day").Find(&rows).Error; err != nil {
bizResp(c, err)
return
}
for _, r := range rows {
byDay[r.Day] = [2]int64{r.Submitted, r.Success}
}
} else {
claims := statsClaims(c)
type row struct {
Day string
Submitted int64
Success int64
}
var rows []row
if err := h.db.Table("message_detail").
Select("date(created_at) AS day, COUNT(*) AS submitted, COALESCE(SUM(status = 2), 0) AS success").
Where("account_id = ? AND created_at >= ?", claims.AccountID, start).
Group("day").Order("day").Scan(&rows).Error; err != nil {
bizResp(c, err)
return
}
for _, r := range rows {
byDay[r.Day] = [2]int64{r.Submitted, r.Success}
}
}
list := make([]trendDTO, 0, days)
for i := 0; i < days; i++ {
day := start.AddDate(0, 0, i)
v := byDay[day.Format("2006-01-02")]
list = append(list, trendDTO{Date: day.Format("01-02"), Sent: v[0], Success: v[1]})
}
response.Success(c, list)
}
// statsAggregateDay 单日聚合(幂等 UPSERT):三条纯覆盖索引 COUNT——总量走
// idx_detail_created,成功/失败各走 idx_status_time(status,created_at)——
// 零随机回表(Day67 教训:合一条 SUM(status=2) 会让覆盖索引失效退化百万级回表)。
func statsAggregateDay(db *gorm.DB, day time.Time) error {
lo := time.Date(day.Year(), day.Month(), day.Day(), 0, 0, 0, 0, day.Location())
hi := lo.AddDate(0, 0, 1)
var submitted, success, failed int64
if err := db.Table("message_detail").
Where("created_at >= ? AND created_at < ?", lo, hi).
Count(&submitted).Error; err != nil {
return err
}
if err := db.Table("message_detail").
Where("status = ? AND created_at >= ? AND created_at < ?", 2, lo, hi).
Count(&success).Error; err != nil {
return err
}
if err := db.Table("message_detail").
Where("status = ? AND created_at >= ? AND created_at < ?", 3, lo, hi).
Count(&failed).Error; err != nil {
return err
}
row := entity.StatsDaily{
Day: lo.Format("2006-01-02"), Submitted: submitted, Success: success, Failed: failed,
}
return db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "day"}},
DoUpdates: clause.AssignmentColumns([]string{"submitted", "success", "failed", "updated_at"}),
}).Create(&row).Error
}
// StartStatsRefresher 后台汇总刷新(仅 gateway 装配调用;测试/迁移工具不启):
// 启动即后台回补 30 天(不阻塞启动,HDD 首轮 1~2 分钟),此后 60s ticker 聚合
// 今天+昨天(跨天窗口补昨日终值)。幂等 UPSERT——多实例重复刷新无害。
func StartStatsRefresher(db *gorm.DB, log *zap.Logger) {
go func() {
for i := statsRefresherBackfillDays - 1; i >= 0; i-- {
day := time.Now().AddDate(0, 0, -i)
if err := statsAggregateDay(db, day); err != nil && log != nil {
log.Warn("stats_backfill_day_failed", zap.String("day", day.Format("2006-01-02")), zap.Error(err))
}
}
if log != nil {
log.Info("stats_backfill_done")
}
ticker := time.NewTicker(statsRefreshEvery)
defer ticker.Stop()
for range ticker.C {
now := time.Now()
for _, d := range []time.Time{now, now.AddDate(0, 0, -1)} {
if err := statsAggregateDay(db, d); err != nil && log != nil {
log.Warn("stats_refresh_failed", zap.Error(err))
}
}
}
}()
}
// ---- Issue#6·P2:业务类型分布(/stats/biz)与实时速率(/stats/realtime) ----
// 契约=docs/design/issue6/v3-2-tailwind.html(v3.2.1)业务分布 donut 与 KPI
// 实时速率卡;两维分布均聚合 message_task(表量级远小于 detail,HDD 可控),
// 实时速率走 message_detail 的 idx_detail_created 覆盖索引 60s 窗口 COUNT。
type bizCountDTO struct {
Key string `json:"key"`
Count int64 `json:"count"`
}
// statsBizDTO 业务分布(business=sms/video 业务线;send_type=发送方式)。
// 注:biz_type(sms/template/sign/group)属审核域 audit_record 非 task 维度,
// 契约 donut 的第二维以 send_type 呈现(群发/个性/定时/文件)更贴运营语义。
type statsBizDTO struct {
Days int `json:"days"`
ByBusiness []bizCountDTO `json:"by_business"` // message_task.business(空=sms 兼容 Day48 旧载荷)
BySendType []bizCountDTO `json:"by_send_type"` // message_task.send_type:batch/personal/timer/file/other
}
// biz GET /stats/biz?days=7——近 N 自然日两维分布(平台全量/客户仅自己)。
func (h *statsHandler) biz(c *gin.Context) {
days, _ := strconv.Atoi(c.DefaultQuery("days", "7"))
if days < 1 || days > statsTrendMaxDays {
days = 7
}
now := time.Now()
start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).
AddDate(0, 0, -(days - 1))
type row struct {
Key string
Count int64
}
var rows []row
if err := h.db.Model(&entity.MessageTask{}).
Where("created_at >= ?", start).Scopes(statsTaskScope(c)).
Select("COALESCE(NULLIF(business,''),'sms') AS `key`, COUNT(*) AS count").
Group("`key`").Order("count DESC").Scan(&rows).Error; err != nil {
bizResp(c, err)
return
}
byBiz := make([]bizCountDTO, 0, len(rows))
for _, r := range rows {
byBiz = append(byBiz, bizCountDTO(r))
}
rows = nil
if err := h.db.Model(&entity.MessageTask{}).
Where("created_at >= ?", start).Scopes(statsTaskScope(c)).
Select("send_type AS `key`, COUNT(*) AS count").
Group("`key`").Order("count DESC").Scan(&rows).Error; err != nil {
bizResp(c, err)
return
}
sendNames := map[string]string{"1": "batch", "2": "personal", "3": "timer", "4": "file"}
bySend := make([]bizCountDTO, 0, len(rows))
for _, r := range rows {
k, ok := sendNames[r.Key]
if !ok {
k = "other"
}
bySend = append(bySend, bizCountDTO{Key: k, Count: r.Count})
}
response.Success(c, statsBizDTO{Days: days, ByBusiness: byBiz, BySendType: bySend})
}
// statsTaskScope 平台/客户口径收敛(admin/ops/service 全量;其余仅自己)。
func statsTaskScope(c *gin.Context) func(*gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
if !statsIsPlatform(c) {
return db.Where("account_id = ?", statsClaims(c).AccountID)
}
return db
}
}
type rtAccountDTO struct {
AccountID int64 `json:"account_id"`
Name string `json:"name"`
SubmitPs float64 `json:"submit_ps"`
}
// statsRealtimeDTO 实时速率(60s 窗口均值;平台=全局+Top5 账号,客户=仅自己)。
type statsRealtimeDTO struct {
Ts int64 `json:"ts"`
WindowS int `json:"window_s"`
GlobalSubmitPs float64 `json:"global_submit_ps"`
TopAccounts []rtAccountDTO `json:"top_accounts"`
}
// realtime GET /stats/realtime——60s 窗口提交速率(idx_detail_created 覆盖
// COUNT 零回表;账号名小表 IN 补齐,缺失=空串不阻塞)。
func (h *statsHandler) realtime(c *gin.Context) {
const win = 60
now := time.Now()
since := now.Add(-win * time.Second)
var global int64
type topRow struct {
AccountID int64
Count int64
}
var tops []topRow
if statsIsPlatform(c) {
if err := h.db.Model(&entity.MessageDetail{}).Where("created_at >= ?", since).
Count(&global).Error; err != nil {
bizResp(c, err)
return
}
if err := h.db.Model(&entity.MessageDetail{}).Where("created_at >= ?", since).
Select("account_id, COUNT(*) AS count").
Group("account_id").Order("count DESC").Limit(5).Scan(&tops).Error; err != nil {
bizResp(c, err)
return
}
} else {
claims := statsClaims(c)
if err := h.db.Model(&entity.MessageDetail{}).
Where("created_at >= ? AND account_id = ?", since, claims.AccountID).
Count(&global).Error; err != nil {
bizResp(c, err)
return
}
tops = []topRow{{AccountID: claims.AccountID, Count: global}}
}
names := make(map[int64]string, len(tops))
if len(tops) > 0 {
ids := make([]int64, 0, len(tops))
for _, t := range tops {
ids = append(ids, t.AccountID)
}
var accs []entity.AccountUser
_ = h.db.Select("id", "username").Where("id IN ?", ids).Find(&accs).Error
for _, a := range accs {
names[a.ID] = a.Username
}
}
list := make([]rtAccountDTO, 0, len(tops))
for _, t := range tops {
list = append(list, rtAccountDTO{
AccountID: t.AccountID, Name: names[t.AccountID],
SubmitPs: statsRound2(float64(t.Count) / float64(win)),
})
}
response.Success(c, statsRealtimeDTO{
Ts: now.Unix(), WindowS: win,
GlobalSubmitPs: statsRound2(float64(global) / float64(win)),
TopAccounts: list,
})
}
func statsRound2(f float64) float64 { return math.Round(f*100) / 100 }