350 lines
12 KiB
Go
Executable File
350 lines
12 KiB
Go
Executable File
package admin
|
|
|
|
import (
|
|
"fmt"
|
|
"net/http"
|
|
"strconv"
|
|
"strings"
|
|
"time"
|
|
|
|
"bindbox-game/internal/code"
|
|
"bindbox-game/internal/pkg/core"
|
|
"bindbox-game/internal/pkg/validation"
|
|
"bindbox-game/internal/repository/mysql/model"
|
|
financesvc "bindbox-game/internal/service/finance"
|
|
)
|
|
|
|
type userSpendingRequest struct {
|
|
RangeType string `form:"rangeType"`
|
|
StartDate string `form:"start"`
|
|
EndDate string `form:"end"`
|
|
}
|
|
|
|
// userActivitySpending 用户在具体活动实例上的消费统计
|
|
type userActivitySpending struct {
|
|
ActivityID int64 `json:"activity_id"`
|
|
ActivityName string `json:"activity_name"`
|
|
CategoryID int64 `json:"category_id"`
|
|
CategoryName string `json:"category_name"` // 一番赏/盲盒/对对碰/直播间
|
|
Spending int64 `json:"spending"` // 消费金额(分)
|
|
PrizeValue int64 `json:"prize_value"` // 产出价值(分)
|
|
Profit int64 `json:"profit"` // 收益(分)
|
|
OrderCount int64 `json:"order_count"` // 订单数
|
|
}
|
|
|
|
type userSpendingResponse struct {
|
|
UserID int64 `json:"user_id"`
|
|
Nickname string `json:"nickname"`
|
|
Avatar string `json:"avatar"`
|
|
TotalSpend int64 `json:"total_spend"`
|
|
TotalPrize int64 `json:"total_prize"`
|
|
TotalProfit int64 `json:"total_profit"`
|
|
TotalOrders int64 `json:"total_orders"`
|
|
Activities []userActivitySpending `json:"activities"`
|
|
}
|
|
|
|
var categoryNames = map[int64]string{
|
|
1: "一番赏",
|
|
2: "盲盒/无限",
|
|
3: "对对碰",
|
|
}
|
|
|
|
func (h *handler) GetUserSpendingDashboard() core.HandlerFunc {
|
|
return func(ctx core.Context) {
|
|
userID, err := strconv.ParseInt(ctx.Param("user_id"), 10, 64)
|
|
if err != nil {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, "未传递用户ID"))
|
|
return
|
|
}
|
|
req := new(userSpendingRequest)
|
|
if err := ctx.ShouldBindForm(req); err != nil {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
|
|
return
|
|
}
|
|
|
|
var start, end time.Time
|
|
hasRange := req.RangeType != "" && req.RangeType != "all"
|
|
if hasRange {
|
|
start, end = parseRange(req.RangeType, req.StartDate, req.EndDate)
|
|
}
|
|
|
|
db := h.repo.GetDbR().WithContext(ctx.RequestContext())
|
|
rsp := &userSpendingResponse{UserID: userID}
|
|
|
|
// 获取用户基本信息
|
|
user, _ := h.readDB.Users.WithContext(ctx.RequestContext()).ReadDB().Where(h.readDB.Users.ID.Eq(userID)).First()
|
|
if user != nil {
|
|
rsp.Nickname = user.Nickname
|
|
rsp.Avatar = user.Avatar
|
|
}
|
|
|
|
// 1. 按活动实例统计消费
|
|
type activityStat struct {
|
|
ActivityID int64
|
|
ActivityName string
|
|
CategoryID int64
|
|
Spending int64
|
|
OrderCount int64
|
|
}
|
|
var actStats []activityStat
|
|
|
|
query := db.Table(model.TableNameOrders).
|
|
Joins(`LEFT JOIN (
|
|
SELECT activity_draw_logs.order_id, activity_issues.activity_id, COUNT(*) as draw_count
|
|
FROM activity_draw_logs
|
|
JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id
|
|
GROUP BY activity_draw_logs.order_id, activity_issues.activity_id
|
|
) as order_activity_draws ON order_activity_draws.order_id = orders.id`).
|
|
Joins(`LEFT JOIN (
|
|
SELECT order_id, COUNT(*) as total_count
|
|
FROM activity_draw_logs
|
|
GROUP BY order_id
|
|
) as order_total_draws ON order_total_draws.order_id = orders.id`).
|
|
Joins("LEFT JOIN activities ON activities.id = order_activity_draws.activity_id").
|
|
Where("orders.user_id = ?", userID).
|
|
Where("orders.status = ?", 2)
|
|
|
|
if hasRange {
|
|
query = query.Where("orders.created_at >= ?", start).Where("orders.created_at <= ?", end)
|
|
}
|
|
|
|
if err := query.Select(`
|
|
COALESCE(activities.id, 0) as activity_id,
|
|
COALESCE(activities.name, '其他') as activity_name,
|
|
COALESCE(activities.activity_category_id, 0) as category_id,
|
|
CAST(ROUND(SUM(CASE
|
|
WHEN orders.source_type = 4 OR orders.order_no LIKE 'GP%' OR (orders.actual_amount = 0 AND orders.remark LIKE '%use_game_pass%')
|
|
THEN COALESCE(order_activity_draws.draw_count * activities.price_draw, 0)
|
|
ELSE COALESCE((orders.actual_amount + orders.discount_amount) * order_activity_draws.draw_count / NULLIF(order_total_draws.total_count, 0), 0)
|
|
END), 0) AS SIGNED) as spending,
|
|
COUNT(DISTINCT orders.id) as order_count
|
|
`).
|
|
Group("COALESCE(activities.id, 0)").
|
|
Order("spending DESC").
|
|
Scan(&actStats).Error; err != nil {
|
|
h.logger.Error(fmt.Sprintf("UserSpending SQL error: %v", err))
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, 21030, err.Error()))
|
|
return
|
|
}
|
|
|
|
// 2. 按活动实例统计产出价值
|
|
type prizeStat struct {
|
|
ActivityID int64
|
|
PrizeValue int64
|
|
}
|
|
var prizeStats []prizeStat
|
|
|
|
prizeQuery := db.Table(model.TableNameUserInventory).
|
|
Joins("LEFT JOIN orders ON orders.id = user_inventory.order_id").
|
|
Joins("LEFT JOIN activity_reward_settings ON activity_reward_settings.id = user_inventory.reward_id").
|
|
Joins("LEFT JOIN activity_issues AS cost_issues ON cost_issues.id = activity_reward_settings.issue_id").
|
|
Joins("LEFT JOIN products ON products.id = user_inventory.product_id").
|
|
Joins("LEFT JOIN user_item_cards ON user_item_cards.id = orders.item_card_id").
|
|
Joins("LEFT JOIN system_item_cards ON system_item_cards.id = user_item_cards.card_id").
|
|
Where("user_inventory.user_id = ?", userID).
|
|
Where("user_inventory.status IN ?", []int{1, 3}).
|
|
Where("COALESCE(user_inventory.remark, '') NOT LIKE ?", "%void%")
|
|
|
|
if hasRange {
|
|
prizeQuery = prizeQuery.Where("user_inventory.created_at >= ?", start).Where("user_inventory.created_at <= ?", end)
|
|
}
|
|
|
|
if err := prizeQuery.Select(`
|
|
COALESCE(NULLIF(user_inventory.activity_id, 0), cost_issues.activity_id, 0) as activity_id,
|
|
CAST(SUM(COALESCE(NULLIF(user_inventory.value_cents, 0), activity_reward_settings.price_snapshot_cents, products.price, 0) * GREATEST(COALESCE(system_item_cards.reward_multiplier_x1000, 1000), 1000) / 1000) AS SIGNED) as prize_value
|
|
`).
|
|
Group("COALESCE(NULLIF(user_inventory.activity_id, 0), cost_issues.activity_id, 0)").
|
|
Scan(&prizeStats).Error; err != nil {
|
|
h.logger.Error(fmt.Sprintf("GetUserSpendingDashboard prize stats error: %v", err))
|
|
}
|
|
|
|
prizeMap := make(map[int64]int64)
|
|
for _, p := range prizeStats {
|
|
prizeMap[p.ActivityID] = p.PrizeValue
|
|
}
|
|
|
|
// 3. 直播间消费统计
|
|
type livestreamStat struct {
|
|
ActivityID int64
|
|
ActivityName string
|
|
Spending int64
|
|
OrderCount int64
|
|
}
|
|
var lsStats []livestreamStat
|
|
|
|
lsQuery := db.Table("douyin_orders").
|
|
Joins("LEFT JOIN livestream_activities ON livestream_activities.id = douyin_orders.livestream_activity_id").
|
|
Select(`
|
|
COALESCE(douyin_orders.livestream_activity_id, 0) as activity_id,
|
|
COALESCE(livestream_activities.name, '直播间') as activity_name,
|
|
SUM(actual_pay_amount) as spending,
|
|
COUNT(*) as order_count
|
|
`).
|
|
Where("CAST(local_user_id AS SIGNED) = ?", userID).
|
|
Where("local_user_id != '' AND local_user_id != '0'")
|
|
|
|
if hasRange {
|
|
lsQuery = lsQuery.Where("douyin_orders.created_at >= ?", start).Where("douyin_orders.created_at <= ?", end)
|
|
}
|
|
|
|
lsQuery.Group("COALESCE(douyin_orders.livestream_activity_id, 0)").Scan(&lsStats)
|
|
|
|
// 直播间产出
|
|
type lsPrizeStat struct {
|
|
ActivityID int64
|
|
PrizeValue int64
|
|
}
|
|
var lsPrizeStats []lsPrizeStat
|
|
type lsLog struct {
|
|
ActivityID int64
|
|
ShopOrderID string
|
|
PrizeID int64
|
|
}
|
|
var lsLogs []lsLog
|
|
lsLogQuery := db.Table("livestream_draw_logs").
|
|
Select("livestream_activity_id as activity_id, shop_order_id, prize_id").
|
|
Where("local_user_id = ?", userID).
|
|
Where("is_refunded = 0").
|
|
Where("prize_id > 0")
|
|
if hasRange {
|
|
lsLogQuery = lsLogQuery.Where("created_at >= ?", start).Where("created_at <= ?", end)
|
|
}
|
|
_ = lsLogQuery.Scan(&lsLogs).Error
|
|
|
|
if len(lsLogs) > 0 {
|
|
prizeIDSet := make(map[int64]struct{})
|
|
for _, l := range lsLogs {
|
|
prizeIDSet[l.PrizeID] = struct{}{}
|
|
}
|
|
prizeIDs := make([]int64, 0, len(prizeIDSet))
|
|
for pid := range prizeIDSet {
|
|
prizeIDs = append(prizeIDs, pid)
|
|
}
|
|
prizeCostMap := make(map[int64]int64)
|
|
if len(prizeIDs) > 0 {
|
|
var prizes []struct {
|
|
ID int64
|
|
CostPrice int64
|
|
}
|
|
_ = h.repo.GetDbR().Table("livestream_prizes").Select("id, cost_price").Where("id IN ?", prizeIDs).Scan(&prizes).Error
|
|
for _, p := range prizes {
|
|
prizeCostMap[p.ID] = p.CostPrice
|
|
}
|
|
}
|
|
|
|
type invRow struct {
|
|
ValueCents int64
|
|
Remark string
|
|
}
|
|
var invRows []invRow
|
|
invQ := h.repo.GetDbR().Table("user_inventory").
|
|
Select("COALESCE(NULLIF(user_inventory.value_cents, 0), activity_reward_settings.price_snapshot_cents, products.price, 0) as value_cents, user_inventory.remark").
|
|
Joins("LEFT JOIN activity_reward_settings ON activity_reward_settings.id = user_inventory.reward_id").
|
|
Joins("LEFT JOIN products ON products.id = user_inventory.product_id").
|
|
Where("user_id = ?", userID).
|
|
Where("status IN (1,3)").
|
|
Where("COALESCE(user_inventory.remark, '') NOT LIKE ?", "%void%")
|
|
if hasRange {
|
|
invQ = invQ.Where("created_at >= ?", start.Add(-2*time.Hour)).Where("created_at <= ?", end.Add(24*time.Hour))
|
|
}
|
|
_ = invQ.Scan(&invRows).Error
|
|
|
|
lsByKey := make(map[string][]lsLog)
|
|
for _, l := range lsLogs {
|
|
key := fmt.Sprintf("%d|%s", l.ActivityID, l.ShopOrderID)
|
|
lsByKey[key] = append(lsByKey[key], l)
|
|
}
|
|
|
|
prizeByActivity := make(map[int64]int64)
|
|
for _, logs := range lsByKey {
|
|
if len(logs) == 0 {
|
|
continue
|
|
}
|
|
aid := logs[0].ActivityID
|
|
shopOrderID := logs[0].ShopOrderID
|
|
|
|
var snapshotSum int64
|
|
if shopOrderID != "" {
|
|
for _, inv := range invRows {
|
|
if strings.Contains(inv.Remark, shopOrderID) {
|
|
snapshotSum += inv.ValueCents
|
|
}
|
|
}
|
|
}
|
|
if snapshotSum > 0 {
|
|
prizeByActivity[aid] += snapshotSum
|
|
continue
|
|
}
|
|
for _, l := range logs {
|
|
prizeByActivity[aid] += prizeCostMap[l.PrizeID]
|
|
}
|
|
}
|
|
|
|
for aid, val := range prizeByActivity {
|
|
lsPrizeStats = append(lsPrizeStats, lsPrizeStat{
|
|
ActivityID: aid,
|
|
PrizeValue: val,
|
|
})
|
|
}
|
|
}
|
|
|
|
lsPrizeMap := make(map[int64]int64)
|
|
for _, p := range lsPrizeStats {
|
|
lsPrizeMap[p.ActivityID] = p.PrizeValue
|
|
}
|
|
|
|
// 4. 组装结果
|
|
activities := make([]userActivitySpending, 0)
|
|
var totalSpend, totalPrize, totalOrders int64
|
|
|
|
for _, s := range actStats {
|
|
prize := prizeMap[s.ActivityID]
|
|
catName := categoryNames[s.CategoryID]
|
|
if catName == "" {
|
|
catName = "其他"
|
|
}
|
|
item := userActivitySpending{
|
|
ActivityID: s.ActivityID,
|
|
ActivityName: s.ActivityName,
|
|
CategoryID: s.CategoryID,
|
|
CategoryName: catName,
|
|
Spending: s.Spending,
|
|
PrizeValue: prize,
|
|
Profit: func() int64 { p, _ := financesvc.ComputeProfit(s.Spending, prize); return p }(),
|
|
OrderCount: s.OrderCount,
|
|
}
|
|
activities = append(activities, item)
|
|
totalSpend += s.Spending
|
|
totalPrize += prize
|
|
totalOrders += s.OrderCount
|
|
}
|
|
|
|
// 追加直播间活动
|
|
for _, ls := range lsStats {
|
|
prize := lsPrizeMap[ls.ActivityID]
|
|
item := userActivitySpending{
|
|
ActivityID: ls.ActivityID + 100000, // 避免和普通活动 ID 冲突
|
|
ActivityName: ls.ActivityName,
|
|
CategoryID: 4,
|
|
CategoryName: "直播间",
|
|
Spending: ls.Spending,
|
|
PrizeValue: prize,
|
|
Profit: func() int64 { p, _ := financesvc.ComputeProfit(ls.Spending, prize); return p }(),
|
|
OrderCount: ls.OrderCount,
|
|
}
|
|
activities = append(activities, item)
|
|
totalSpend += ls.Spending
|
|
totalPrize += prize
|
|
totalOrders += ls.OrderCount
|
|
}
|
|
|
|
rsp.TotalSpend = totalSpend
|
|
rsp.TotalPrize = totalPrize
|
|
rsp.TotalProfit, _ = financesvc.ComputeProfit(totalSpend, totalPrize)
|
|
rsp.TotalOrders = totalOrders
|
|
rsp.Activities = activities
|
|
|
|
ctx.Payload(rsp)
|
|
}
|
|
}
|