Zuncle
0e202fabd8
将玩家盈亏趋势中的商品产出从当前资产快照估值， 调整为按用户、订单、抽奖日志链路聚合的商品成本口径。 这样可使商品产出与消费看板中的活动产出统计保持一致， 避免同一用户在两个面板中看到不同的商品产出口径。 同时保留积分、道具卡、优惠券三项当前分项展示， 避免接口结构调整后页面字段缺失或被误显示为 0。
661 lines
22 KiB
Go
Executable File
661 lines
22 KiB
Go
Executable File
package admin
|
|
|
|
import (
|
|
"fmt"
|
|
"net/http"
|
|
"strconv"
|
|
"time"
|
|
|
|
"bindbox-game/internal/code"
|
|
"bindbox-game/internal/pkg/core"
|
|
"bindbox-game/internal/pkg/validation"
|
|
)
|
|
|
|
type userProfitLossRequest struct {
|
|
RangeType string `form:"rangeType"`
|
|
Granularity string `form:"granularity"`
|
|
}
|
|
|
|
type userProfitLossPoint struct {
|
|
Date string `json:"date"`
|
|
Cost int64 `json:"cost"` // 累计投入（已支付-已退款）
|
|
Value int64 `json:"value"` // 累计产出（订单链路商品成本）
|
|
Profit int64 `json:"profit"` // 净盈亏
|
|
Ratio float64 `json:"ratio"` // 盈亏比
|
|
Breakdown struct {
|
|
Products int64 `json:"products"`
|
|
Points int64 `json:"points"`
|
|
Cards int64 `json:"cards"`
|
|
Coupons int64 `json:"coupons"`
|
|
} `json:"breakdown"`
|
|
}
|
|
|
|
type userProfitLossResponse struct {
|
|
Granularity string `json:"granularity"`
|
|
List []userProfitLossPoint `json:"list"`
|
|
Summary struct {
|
|
TotalCost int64 `json:"total_cost"`
|
|
TotalValue int64 `json:"total_value"`
|
|
TotalProfit int64 `json:"total_profit"`
|
|
AvgRatio float64 `json:"avg_ratio"`
|
|
} `json:"summary"`
|
|
CurrentAssets struct {
|
|
Points int64 `json:"points"`
|
|
Products int64 `json:"products"`
|
|
Cards int64 `json:"cards"`
|
|
Coupons int64 `json:"coupons"`
|
|
Total int64 `json:"total"`
|
|
} `json:"currentAssets"`
|
|
}
|
|
|
|
func (h *handler) GetUserProfitLossTrend() 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(userProfitLossRequest)
|
|
if err := ctx.ShouldBindForm(req); err != nil {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
|
|
return
|
|
}
|
|
|
|
start, end := parseRange(req.RangeType, "", "")
|
|
if req.RangeType == "all" {
|
|
u, _ := h.readDB.Users.WithContext(ctx.RequestContext()).Where(h.readDB.Users.ID.Eq(userID)).First()
|
|
if u != nil {
|
|
start = u.CreatedAt
|
|
} else {
|
|
start = time.Date(2025, 1, 1, 0, 0, 0, 0, time.Local)
|
|
}
|
|
}
|
|
gran := normalizeGranularity(req.Granularity)
|
|
buckets := buildBuckets(start, end, gran)
|
|
|
|
if len(buckets) > 1500 {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, "范围过大"))
|
|
return
|
|
}
|
|
|
|
// --- 1. 获取当前资产快照（用于积分/道具卡/优惠券分项展示）---
|
|
var curAssets struct {
|
|
Points int64
|
|
Products int64
|
|
Cards int64
|
|
Coupons int64
|
|
}
|
|
_ = h.repo.GetDbR().Raw("SELECT COALESCE(SUM(points), 0) FROM user_points WHERE user_id = ? AND (valid_end IS NULL OR valid_end > NOW())", userID).Scan(&curAssets.Points).Error
|
|
_ = h.repo.GetDbR().Raw("SELECT COALESCE(SUM(sc.price), 0) FROM user_item_cards uic LEFT JOIN system_item_cards sc ON sc.id = uic.card_id WHERE uic.user_id = ? AND uic.status = 1", userID).Scan(&curAssets.Cards).Error
|
|
_ = h.repo.GetDbR().Raw("SELECT COALESCE(SUM(balance_amount), 0) FROM user_coupons WHERE user_id = ? AND status = 1", userID).Scan(&curAssets.Coupons).Error
|
|
|
|
// --- 2. 获取订单链路商品产出（与 spending 保持一致，仅统计普通活动）---
|
|
type prizeRow struct {
|
|
CreatedAt time.Time
|
|
PrizeValue int64
|
|
}
|
|
var basePrizeValue int64 = 0
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT CAST(COALESCE(SUM(COALESCE(products.cost_price, 0) * (
|
|
COALESCE(NULLIF(activity_reward_settings.drop_quantity, 0), 1) +
|
|
CASE WHEN user_item_cards.used_draw_log_id = activity_draw_logs.id AND system_item_cards.effect_type = 1 AND system_item_cards.reward_multiplier_x1000 >= 2000 THEN 1 ELSE 0 END
|
|
)), 0) AS SIGNED) as prize_value
|
|
FROM activity_draw_logs
|
|
JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id
|
|
LEFT JOIN activity_reward_settings ON activity_reward_settings.id = activity_draw_logs.reward_id
|
|
LEFT JOIN products ON products.id = activity_reward_settings.product_id
|
|
LEFT JOIN orders ON orders.id = activity_draw_logs.order_id
|
|
LEFT JOIN user_item_cards ON user_item_cards.id = orders.item_card_id
|
|
LEFT JOIN system_item_cards ON system_item_cards.id = user_item_cards.card_id
|
|
WHERE orders.user_id = ? AND orders.status = 2 AND orders.created_at < ?
|
|
`, userID, start).Scan(&basePrizeValue).Error
|
|
|
|
var prizeRows []prizeRow
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT orders.created_at,
|
|
CAST(COALESCE(products.cost_price, 0) * (
|
|
COALESCE(NULLIF(activity_reward_settings.drop_quantity, 0), 1) +
|
|
CASE WHEN user_item_cards.used_draw_log_id = activity_draw_logs.id AND system_item_cards.effect_type = 1 AND system_item_cards.reward_multiplier_x1000 >= 2000 THEN 1 ELSE 0 END
|
|
) AS SIGNED) as prize_value
|
|
FROM activity_draw_logs
|
|
JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id
|
|
LEFT JOIN activity_reward_settings ON activity_reward_settings.id = activity_draw_logs.reward_id
|
|
LEFT JOIN products ON products.id = activity_reward_settings.product_id
|
|
LEFT JOIN orders ON orders.id = activity_draw_logs.order_id
|
|
LEFT JOIN user_item_cards ON user_item_cards.id = orders.item_card_id
|
|
LEFT JOIN system_item_cards ON system_item_cards.id = user_item_cards.card_id
|
|
WHERE orders.user_id = ? AND orders.status = 2 AND orders.created_at BETWEEN ? AND ?
|
|
`, userID, start, end).Scan(&prizeRows).Error
|
|
|
|
// --- 2. 获取订单数据（仅 status=2 已支付） ---
|
|
// 注意：为了计算累计趋势，我们需要获取 start 之前的所有已支付订单总额作为基数
|
|
var baseCost int64 = 0
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT COALESCE(SUM(CASE
|
|
WHEN o.source_type = 4 OR o.order_no LIKE 'GP%' OR (o.actual_amount = 0 AND o.remark LIKE '%use_game_pass%')
|
|
THEN COALESCE(od.draw_count * a.price_draw, 0)
|
|
ELSE o.actual_amount
|
|
END), 0)
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT l.order_id, COUNT(*) as draw_count, MAX(ai.activity_id) as activity_id
|
|
FROM activity_draw_logs l
|
|
JOIN activity_issues ai ON ai.id = l.issue_id
|
|
GROUP BY l.order_id
|
|
) od ON od.order_id = o.id
|
|
LEFT JOIN activities a ON a.id = od.activity_id
|
|
WHERE o.user_id = ? AND o.status = 2 AND o.source_type IN (2,3,4) AND o.created_at < ?
|
|
`, userID, start).Scan(&baseCost).Error
|
|
|
|
// 扣除历史退款 (如果有的话，此处简化处理，主要关注当前范围内的波动)
|
|
var baseRefund int64 = 0
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT COALESCE(SUM(pr.amount_refund), 0)
|
|
FROM payment_refunds pr
|
|
JOIN orders o ON o.order_no = pr.order_no COLLATE utf8mb4_unicode_ci
|
|
WHERE o.user_id = ? AND pr.status = 'SUCCESS' AND pr.created_at < ?
|
|
`, userID, start).Scan(&baseRefund).Error
|
|
baseCost -= baseRefund
|
|
if baseCost < 0 {
|
|
baseCost = 0
|
|
}
|
|
|
|
type orderSpendRow struct {
|
|
CreatedAt time.Time
|
|
Spending int64
|
|
}
|
|
var orderRows []orderSpendRow
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT o.created_at,
|
|
CASE
|
|
WHEN o.source_type = 4 OR o.order_no LIKE 'GP%' OR (o.actual_amount = 0 AND o.remark LIKE '%use_game_pass%')
|
|
THEN COALESCE(od.draw_count * a.price_draw, 0)
|
|
ELSE o.actual_amount
|
|
END as spending
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT l.order_id, COUNT(*) as draw_count, MAX(ai.activity_id) as activity_id
|
|
FROM activity_draw_logs l
|
|
JOIN activity_issues ai ON ai.id = l.issue_id
|
|
GROUP BY l.order_id
|
|
) od ON od.order_id = o.id
|
|
LEFT JOIN activities a ON a.id = od.activity_id
|
|
WHERE o.user_id = ? AND o.status = 2 AND o.source_type IN (2,3,4) AND o.created_at BETWEEN ? AND ?
|
|
`, userID, start, end).Scan(&orderRows).Error
|
|
|
|
// 获取当前范围内的退款
|
|
type refundInfo struct {
|
|
Amount int64
|
|
CreatedAt time.Time
|
|
}
|
|
var refunds []refundInfo
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT pr.amount_refund as amount, pr.created_at
|
|
FROM payment_refunds pr
|
|
JOIN orders o ON o.order_no = pr.order_no COLLATE utf8mb4_unicode_ci
|
|
WHERE o.user_id = ? AND pr.status = 'SUCCESS' AND pr.created_at BETWEEN ? AND ?
|
|
`, userID, start, end).Scan(&refunds).Error
|
|
|
|
// --- 3. 按时间分桶计算 ---
|
|
list := make([]userProfitLossPoint, len(buckets))
|
|
|
|
inBucket := func(t time.Time, b bucket) bool {
|
|
return (t.After(b.Start) || t.Equal(b.Start)) && t.Before(b.End)
|
|
}
|
|
|
|
cumulativeCost := baseCost
|
|
cumulativeValue := basePrizeValue
|
|
|
|
for i, b := range buckets {
|
|
p := &list[i]
|
|
p.Date = b.Label
|
|
|
|
// 计算该时间段内的净投入变化
|
|
var periodDelta int64 = 0
|
|
for _, o := range orderRows {
|
|
if inBucket(o.CreatedAt, b) {
|
|
periodDelta += o.Spending
|
|
}
|
|
}
|
|
for _, r := range refunds {
|
|
if inBucket(r.CreatedAt, b) {
|
|
periodDelta -= r.Amount
|
|
}
|
|
}
|
|
|
|
cumulativeCost += periodDelta
|
|
if cumulativeCost < 0 {
|
|
cumulativeCost = 0
|
|
}
|
|
p.Cost = cumulativeCost
|
|
|
|
var periodValueDelta int64 = 0
|
|
for _, prize := range prizeRows {
|
|
if inBucket(prize.CreatedAt, b) {
|
|
periodValueDelta += prize.PrizeValue
|
|
}
|
|
}
|
|
cumulativeValue += periodValueDelta
|
|
if cumulativeValue < 0 {
|
|
cumulativeValue = 0
|
|
}
|
|
p.Value = cumulativeValue
|
|
p.Breakdown.Products = cumulativeValue
|
|
p.Breakdown.Points = curAssets.Points
|
|
p.Breakdown.Cards = curAssets.Cards
|
|
p.Breakdown.Coupons = curAssets.Coupons
|
|
|
|
p.Profit = p.Cost - p.Value
|
|
if p.Value > 0 {
|
|
p.Ratio = float64(p.Cost) / float64(p.Value)
|
|
} else if p.Cost > 0 {
|
|
p.Ratio = 99.9
|
|
}
|
|
}
|
|
|
|
// 汇总数据
|
|
var totalCost int64 = 0
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT COALESCE(SUM(CASE
|
|
WHEN o.source_type = 4 OR o.order_no LIKE 'GP%' OR (o.actual_amount = 0 AND o.remark LIKE '%use_game_pass%')
|
|
THEN COALESCE(od.draw_count * a.price_draw, 0)
|
|
ELSE o.actual_amount
|
|
END), 0)
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT l.order_id, COUNT(*) as draw_count, MAX(ai.activity_id) as activity_id
|
|
FROM activity_draw_logs l
|
|
JOIN activity_issues ai ON ai.id = l.issue_id
|
|
GROUP BY l.order_id
|
|
) od ON od.order_id = o.id
|
|
LEFT JOIN activities a ON a.id = od.activity_id
|
|
WHERE o.user_id = ? AND o.status = 2 AND o.source_type IN (2,3,4)
|
|
`, userID).Scan(&totalCost).Error
|
|
|
|
var totalRefund int64 = 0
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT COALESCE(SUM(pr.amount_refund), 0)
|
|
FROM payment_refunds pr
|
|
JOIN orders o ON o.order_no = pr.order_no COLLATE utf8mb4_unicode_ci
|
|
WHERE o.user_id = ? AND pr.status = 'SUCCESS'
|
|
`, userID).Scan(&totalRefund).Error
|
|
|
|
finalNetCost := totalCost - totalRefund
|
|
if finalNetCost < 0 {
|
|
finalNetCost = 0
|
|
}
|
|
|
|
var totalValue int64 = 0
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT CAST(COALESCE(SUM(COALESCE(products.cost_price, 0) * (
|
|
COALESCE(NULLIF(activity_reward_settings.drop_quantity, 0), 1) +
|
|
CASE WHEN user_item_cards.used_draw_log_id = activity_draw_logs.id AND system_item_cards.effect_type = 1 AND system_item_cards.reward_multiplier_x1000 >= 2000 THEN 1 ELSE 0 END
|
|
)), 0) AS SIGNED) as prize_value
|
|
FROM activity_draw_logs
|
|
JOIN activity_issues ON activity_issues.id = activity_draw_logs.issue_id
|
|
LEFT JOIN activity_reward_settings ON activity_reward_settings.id = activity_draw_logs.reward_id
|
|
LEFT JOIN products ON products.id = activity_reward_settings.product_id
|
|
LEFT JOIN orders ON orders.id = activity_draw_logs.order_id
|
|
LEFT JOIN user_item_cards ON user_item_cards.id = orders.item_card_id
|
|
LEFT JOIN system_item_cards ON system_item_cards.id = user_item_cards.card_id
|
|
WHERE orders.user_id = ? AND orders.status = 2
|
|
`, userID).Scan(&totalValue).Error
|
|
|
|
resp := userProfitLossResponse{
|
|
Granularity: gran,
|
|
List: list,
|
|
}
|
|
resp.Summary.TotalCost = finalNetCost
|
|
resp.Summary.TotalValue = totalValue
|
|
resp.Summary.TotalProfit = finalNetCost - totalValue
|
|
if totalValue > 0 {
|
|
resp.Summary.AvgRatio = float64(finalNetCost) / float64(totalValue)
|
|
} else if finalNetCost > 0 {
|
|
resp.Summary.AvgRatio = 99.9
|
|
}
|
|
resp.CurrentAssets.Points = curAssets.Points
|
|
resp.CurrentAssets.Products = totalValue
|
|
resp.CurrentAssets.Cards = curAssets.Cards
|
|
resp.CurrentAssets.Coupons = curAssets.Coupons
|
|
resp.CurrentAssets.Total = totalValue + curAssets.Points + curAssets.Cards + curAssets.Coupons
|
|
|
|
ctx.Payload(resp)
|
|
}
|
|
}
|
|
|
|
// 盈亏明细请求
|
|
type profitLossDetailsRequest struct {
|
|
Page int `form:"page"`
|
|
PageSize int `form:"page_size"`
|
|
RangeType string `form:"rangeType"`
|
|
}
|
|
|
|
// 盈亏明细项
|
|
type profitLossDetailItem struct {
|
|
OrderID int64 `json:"order_id"`
|
|
OrderNo string `json:"order_no"`
|
|
CreatedAt string `json:"created_at"`
|
|
SourceType int32 `json:"source_type"` // 来源类型 1商城 2抽奖 3系统
|
|
ActivityName string `json:"activity_name"` // 活动名称
|
|
ActualAmount int64 `json:"actual_amount"` // 实际支付金额(分)
|
|
RefundAmount int64 `json:"refund_amount"` // 退款金额(分)
|
|
NetCost int64 `json:"net_cost"` // 净投入(分)
|
|
PrizeValue int64 `json:"prize_value"` // 获得奖品价值(分)
|
|
PrizeName string `json:"prize_name"` // 奖品名称
|
|
PointsEarned int64 `json:"points_earned"` // 获得积分
|
|
PointsValue int64 `json:"points_value"` // 积分价值(分)
|
|
CouponUsedValue int64 `json:"coupon_used_value"` // 使用优惠券价值(分)
|
|
CouponUsedName string `json:"coupon_used_name"` // 使用的优惠券名称
|
|
ItemCardUsed string `json:"item_card_used"` // 使用的道具卡名称
|
|
ItemCardValue int64 `json:"item_card_value"` // 道具卡价值(分)
|
|
NetProfit int64 `json:"net_profit"` // 净盈亏
|
|
}
|
|
|
|
// 盈亏明细响应
|
|
type profitLossDetailsResponse struct {
|
|
Page int `json:"page"`
|
|
PageSize int `json:"page_size"`
|
|
Total int64 `json:"total"`
|
|
List []profitLossDetailItem `json:"list"`
|
|
Summary struct {
|
|
TotalCost int64 `json:"total_cost"`
|
|
TotalValue int64 `json:"total_value"`
|
|
TotalProfit int64 `json:"total_profit"`
|
|
} `json:"summary"`
|
|
}
|
|
|
|
// GetUserProfitLossDetails 获取用户盈亏明细
|
|
// @Summary 获取用户盈亏明细
|
|
// @Description 获取用户每笔订单的详细盈亏信息
|
|
// @Tags 管理端.用户
|
|
// @Accept json
|
|
// @Produce json
|
|
// @Param user_id path integer true "用户ID"
|
|
// @Param page query int false "页码" default(1)
|
|
// @Param page_size query int false "每页数量" default(20)
|
|
// @Param rangeType query string false "时间范围" default("all")
|
|
// @Success 200 {object} profitLossDetailsResponse
|
|
// @Failure 400 {object} code.Failure
|
|
// @Router /api/admin/users/{user_id}/stats/profit_loss_details [get]
|
|
// @Security LoginVerifyToken
|
|
func (h *handler) GetUserProfitLossDetails() 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(profitLossDetailsRequest)
|
|
if err := ctx.ShouldBindForm(req); err != nil {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, code.ParamBindError, validation.Error(err)))
|
|
return
|
|
}
|
|
|
|
if req.Page <= 0 {
|
|
req.Page = 1
|
|
}
|
|
if req.PageSize <= 0 {
|
|
req.PageSize = 20
|
|
}
|
|
if req.PageSize > 100 {
|
|
req.PageSize = 100
|
|
}
|
|
|
|
// 解析时间范围
|
|
start, end := parseRange(req.RangeType, "", "")
|
|
if req.RangeType == "all" {
|
|
u, _ := h.readDB.Users.WithContext(ctx.RequestContext()).Where(h.readDB.Users.ID.Eq(userID)).First()
|
|
if u != nil {
|
|
start = u.CreatedAt
|
|
} else {
|
|
start = time.Date(2025, 1, 1, 0, 0, 0, 0, time.Local)
|
|
}
|
|
}
|
|
|
|
// 查询订单总数
|
|
orderQ := h.readDB.Orders.WithContext(ctx.RequestContext()).ReadDB().
|
|
Where(h.readDB.Orders.UserID.Eq(userID)).
|
|
Where(h.readDB.Orders.Status.Eq(2)).
|
|
Where(h.readDB.Orders.CreatedAt.Gte(start)).
|
|
Where(h.readDB.Orders.CreatedAt.Lte(end))
|
|
|
|
total, err := orderQ.Count()
|
|
if err != nil {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, 20108, err.Error()))
|
|
return
|
|
}
|
|
|
|
// 分页查询订单
|
|
orders, err := orderQ.Order(h.readDB.Orders.CreatedAt.Desc()).
|
|
Offset((req.Page - 1) * req.PageSize).Limit(req.PageSize).Find()
|
|
if err != nil {
|
|
ctx.AbortWithError(core.Error(http.StatusBadRequest, 20108, err.Error()))
|
|
return
|
|
}
|
|
|
|
// 收集订单ID
|
|
orderIDs := make([]int64, len(orders))
|
|
orderNos := make([]string, len(orders))
|
|
for i, o := range orders {
|
|
orderIDs[i] = o.ID
|
|
orderNos[i] = o.OrderNo
|
|
}
|
|
|
|
// 批量查询退款信息
|
|
refundMap := make(map[string]int64)
|
|
if len(orderNos) > 0 {
|
|
type refundRow struct {
|
|
OrderNo string
|
|
Amount int64
|
|
}
|
|
var refunds []refundRow
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT order_no, COALESCE(SUM(amount_refund), 0) as amount
|
|
FROM payment_refunds
|
|
WHERE order_no IN ? AND status = 'SUCCESS'
|
|
GROUP BY order_no
|
|
`, orderNos).Scan(&refunds).Error
|
|
for _, r := range refunds {
|
|
refundMap[r.OrderNo] = r.Amount
|
|
}
|
|
}
|
|
|
|
// 批量查询库存价值（获得的奖品）
|
|
prizeValueMap := make(map[int64]int64)
|
|
prizeNameMap := make(map[int64]string)
|
|
if len(orderIDs) > 0 {
|
|
type prizeRow struct {
|
|
OrderID int64
|
|
Value int64
|
|
Name string
|
|
}
|
|
var prizes []prizeRow
|
|
if err := h.repo.GetDbR().Raw(`
|
|
SELECT ui.order_id,
|
|
CAST(COALESCE(SUM(COALESCE(NULLIF(ui.value_cents, 0), activity_reward_settings.price_snapshot_cents, p.price, 0) * GREATEST(COALESCE(system_item_cards.reward_multiplier_x1000, 1000), 1000) / 1000), 0) AS SIGNED) as value,
|
|
GROUP_CONCAT(p.name SEPARATOR ', ') as name
|
|
FROM user_inventory ui
|
|
LEFT JOIN products p ON p.id = ui.product_id
|
|
LEFT JOIN activity_reward_settings ON activity_reward_settings.id = ui.reward_id
|
|
LEFT JOIN orders o ON o.id = ui.order_id
|
|
LEFT JOIN user_item_cards uic ON uic.id = o.item_card_id
|
|
LEFT JOIN system_item_cards ON system_item_cards.id = uic.card_id
|
|
WHERE ui.order_id IN ?
|
|
GROUP BY ui.order_id
|
|
`, orderIDs).Scan(&prizes).Error; err != nil {
|
|
h.logger.Error(fmt.Sprintf("GetUserProfitLoss detail prize query error: %v", err))
|
|
}
|
|
for _, p := range prizes {
|
|
prizeValueMap[p.OrderID] = p.Value
|
|
prizeNameMap[p.OrderID] = p.Name
|
|
}
|
|
}
|
|
|
|
// 批量查询使用的优惠券
|
|
couponValueMap := make(map[int64]int64)
|
|
couponNameMap := make(map[int64]string)
|
|
if len(orderIDs) > 0 {
|
|
type couponRow struct {
|
|
OrderID int64
|
|
Value int64
|
|
Name string
|
|
}
|
|
var coupons []couponRow
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT ucu.order_id, COALESCE(SUM(ABS(ucu.change_amount)), 0) as value,
|
|
GROUP_CONCAT(DISTINCT sc.name SEPARATOR ', ') as name
|
|
FROM user_coupon_usage ucu
|
|
LEFT JOIN user_coupons uc ON uc.id = ucu.user_coupon_id
|
|
LEFT JOIN system_coupons sc ON sc.id = uc.coupon_id
|
|
WHERE ucu.order_id IN ?
|
|
GROUP BY ucu.order_id
|
|
`, orderIDs).Scan(&coupons).Error
|
|
for _, c := range coupons {
|
|
couponValueMap[c.OrderID] = c.Value
|
|
couponNameMap[c.OrderID] = c.Name
|
|
}
|
|
}
|
|
|
|
// 批量查询活动信息
|
|
activityNameMap := make(map[int64]string)
|
|
if len(orderIDs) > 0 {
|
|
type actRow struct {
|
|
OrderID int64
|
|
ActivityName string
|
|
}
|
|
var acts []actRow
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT o.id as order_id, a.name as activity_name
|
|
FROM orders o
|
|
LEFT JOIN activities a ON a.id = o.activity_id
|
|
WHERE o.id IN ? AND o.activity_id > 0
|
|
`, orderIDs).Scan(&acts).Error
|
|
for _, a := range acts {
|
|
activityNameMap[a.OrderID] = a.ActivityName
|
|
}
|
|
}
|
|
|
|
// 批量计算订单统一支出（普通单=支付+优惠券；次卡单=次卡价值）
|
|
orderSpendingMap := make(map[int64]int64)
|
|
if len(orderIDs) > 0 {
|
|
type spendRow struct {
|
|
OrderID int64
|
|
Spending int64
|
|
}
|
|
var spends []spendRow
|
|
_ = h.repo.GetDbR().Raw(`
|
|
SELECT o.id as order_id,
|
|
CASE
|
|
WHEN o.source_type = 4 OR o.order_no LIKE 'GP%' OR (o.actual_amount = 0 AND o.remark LIKE '%use_game_pass%')
|
|
THEN COALESCE(od.draw_count * a.price_draw, 0)
|
|
ELSE o.actual_amount
|
|
END as spending
|
|
FROM orders o
|
|
LEFT JOIN (
|
|
SELECT l.order_id, COUNT(*) as draw_count, MAX(ai.activity_id) as activity_id
|
|
FROM activity_draw_logs l
|
|
JOIN activity_issues ai ON ai.id = l.issue_id
|
|
GROUP BY l.order_id
|
|
) od ON od.order_id = o.id
|
|
LEFT JOIN activities a ON a.id = od.activity_id
|
|
WHERE o.id IN ?
|
|
`, orderIDs).Scan(&spends).Error
|
|
for _, s := range spends {
|
|
orderSpendingMap[s.OrderID] = s.Spending
|
|
}
|
|
}
|
|
|
|
// 组装明细数据
|
|
list := make([]profitLossDetailItem, len(orders))
|
|
var totalCost, totalValue int64
|
|
|
|
for i, o := range orders {
|
|
refund := refundMap[o.OrderNo]
|
|
prizeValue := prizeValueMap[o.ID]
|
|
couponValue := couponValueMap[o.ID]
|
|
spending := orderSpendingMap[o.ID]
|
|
if spending == 0 {
|
|
spending = o.ActualAmount
|
|
}
|
|
netCost := spending - refund
|
|
if netCost < 0 {
|
|
netCost = 0
|
|
}
|
|
netProfit := prizeValue - netCost
|
|
|
|
list[i] = profitLossDetailItem{
|
|
OrderID: o.ID,
|
|
OrderNo: o.OrderNo,
|
|
CreatedAt: o.CreatedAt.Format("2006-01-02 15:04:05"),
|
|
SourceType: o.SourceType,
|
|
ActivityName: activityNameMap[o.ID],
|
|
ActualAmount: o.ActualAmount,
|
|
RefundAmount: refund,
|
|
NetCost: netCost,
|
|
PrizeValue: prizeValue,
|
|
PrizeName: prizeNameMap[o.ID],
|
|
PointsEarned: 0, // 简化处理
|
|
PointsValue: 0,
|
|
CouponUsedValue: couponValue,
|
|
CouponUsedName: couponNameMap[o.ID],
|
|
ItemCardUsed: "", // 从订单备注中解析
|
|
ItemCardValue: 0,
|
|
NetProfit: netProfit,
|
|
}
|
|
|
|
// 解析道具卡信息（从订单备注）
|
|
if o.Remark != "" {
|
|
list[i].ItemCardUsed = parseItemCardFromRemark(o.Remark)
|
|
}
|
|
|
|
totalCost += netCost
|
|
totalValue += prizeValue
|
|
}
|
|
|
|
resp := profitLossDetailsResponse{
|
|
Page: req.Page,
|
|
PageSize: req.PageSize,
|
|
Total: total,
|
|
List: list,
|
|
}
|
|
resp.Summary.TotalCost = totalCost
|
|
resp.Summary.TotalValue = totalValue
|
|
resp.Summary.TotalProfit = totalValue - totalCost
|
|
|
|
ctx.Payload(resp)
|
|
}
|
|
}
|
|
|
|
// 从订单备注中解析道具卡信息
|
|
func parseItemCardFromRemark(remark string) string {
|
|
// 格式: itemcard:xxx|...
|
|
if len(remark) == 0 {
|
|
return ""
|
|
}
|
|
idx := 0
|
|
for i := 0; i < len(remark); i++ {
|
|
if remark[i:] == "itemcard:" || (i+9 <= len(remark) && remark[i:i+9] == "itemcard:") {
|
|
idx = i
|
|
break
|
|
}
|
|
}
|
|
if idx == 0 && len(remark) < 9 {
|
|
return ""
|
|
}
|
|
if idx+9 >= len(remark) {
|
|
return ""
|
|
}
|
|
seg := remark[idx+9:]
|
|
// 找到 | 分隔符
|
|
end := len(seg)
|
|
for i := 0; i < len(seg); i++ {
|
|
if seg[i] == '|' {
|
|
end = i
|
|
break
|
|
}
|
|
}
|
|
return seg[:end]
|
|
}
|