8d1eef2f7f
1. 排除商城直购(source_type=1)：GMV和成本过滤条件从IN(1,2,3,4)改为IN(2,3,4) 2. 排除次卡免费使用订单(actual_amount=0)：避免购买次卡和使用次卡双重计入GMV - source_type=4 一番赏使用次卡：1578单 44032元重复 - source_type=3 对对碰使用次卡：422单 7042元重复 - 合计去除51074元虚增GMV（29.1%） 3. 成本过滤条件同步修正：source_type IN(2,3,4)，total_amount>0 修正后：GMV从175600降至124527元，毛利率从37.4%回到真实的11.8%
220 lines
7.0 KiB
Go
220 lines
7.0 KiB
Go
package main
|
||
|
||
import (
|
||
"database/sql"
|
||
"fmt"
|
||
"os"
|
||
"strings"
|
||
"time"
|
||
|
||
_ "github.com/go-sql-driver/mysql"
|
||
)
|
||
|
||
func main() {
|
||
dsn := "root:bindbox2025kdy@tcp(150.158.78.154:3306)/dev_game?charset=utf8mb4&parseTime=True&loc=Asia%2FShanghai"
|
||
db, err := sql.Open("mysql", dsn)
|
||
if err != nil {
|
||
fmt.Println("连接失败:", err)
|
||
os.Exit(1)
|
||
}
|
||
defer db.Close()
|
||
fmt.Println("✅ 数据库连接成功\n")
|
||
|
||
// ============ 1. 全局汇总 ============
|
||
fmt.Println("【1】全局汇总")
|
||
var userCount, itemCount int64
|
||
var totalYuan float64
|
||
db.QueryRow(`
|
||
SELECT COUNT(DISTINCT t.to_user_id), COUNT(DISTINCT t.inventory_id), IFNULL(SUM(i.value_cents)/100.0, 0)
|
||
FROM user_inventory_transfers t
|
||
JOIN user_inventory i ON i.id = t.inventory_id
|
||
WHERE i.remark LIKE '%redeemed%'
|
||
`).Scan(&userCount, &itemCount, &totalYuan)
|
||
fmt.Printf(" 涉及用户: %d | 涉及资产: %d | 总薅取金额: %.2f 元\n\n", userCount, itemCount, totalYuan)
|
||
|
||
// ============ 2. 按用户汇总 ============
|
||
fmt.Println("【2】按用户汇总薅取金额")
|
||
fmt.Println(strings.Repeat("-", 95))
|
||
fmt.Printf(" %-8s %-16s %-15s %-10s %-12s %-12s %s\n",
|
||
"用户ID", "昵称", "手机号", "兑换资产数", "薅取金额(元)", "当前余额", "可扣回?")
|
||
fmt.Println(" " + strings.Repeat("-", 90))
|
||
|
||
rows2, _ := db.Query(`
|
||
SELECT
|
||
sub.user_id,
|
||
IFNULL(u.nickname, '') AS nickname,
|
||
IFNULL(u.mobile, '') AS mobile,
|
||
sub.redeem_count,
|
||
sub.total_yuan,
|
||
IFNULL(pts.balance, 0) AS balance
|
||
FROM (
|
||
SELECT t.to_user_id AS user_id,
|
||
COUNT(DISTINCT t.inventory_id) AS redeem_count,
|
||
SUM(i.value_cents) / 100.0 AS total_yuan,
|
||
SUM(i.value_cents) AS total_cents
|
||
FROM user_inventory_transfers t
|
||
JOIN user_inventory i ON i.id = t.inventory_id
|
||
WHERE i.remark LIKE '%redeemed%'
|
||
GROUP BY t.to_user_id
|
||
) sub
|
||
LEFT JOIN users u ON u.id = sub.user_id
|
||
LEFT JOIN (SELECT user_id, SUM(points) AS balance FROM user_points GROUP BY user_id) pts ON pts.user_id = sub.user_id
|
||
ORDER BY sub.total_yuan DESC
|
||
`)
|
||
if rows2 != nil {
|
||
defer rows2.Close()
|
||
for rows2.Next() {
|
||
var uid, redeemCnt, balance int64
|
||
var totalY float64
|
||
var nick, mobile string
|
||
rows2.Scan(&uid, &nick, &mobile, &redeemCnt, &totalY, &balance)
|
||
canDeduct := "✅ 可全额"
|
||
exploitCents := int64(totalY * 100)
|
||
if balance < exploitCents {
|
||
canDeduct = fmt.Sprintf("⚠️ 仅可扣%d", balance)
|
||
}
|
||
fmt.Printf(" %-8d %-16s %-15s %-10d %-12.2f %-12d %s\n",
|
||
uid, nick, mobile, redeemCnt, totalY, balance, canDeduct)
|
||
}
|
||
}
|
||
|
||
// ============ 3. 并发漏洞证据 ============
|
||
fmt.Println("\n【3】并发漏洞证据 — 同一资产被多次转赠")
|
||
fmt.Println(strings.Repeat("-", 100))
|
||
rows3, _ := db.Query(`
|
||
SELECT t.inventory_id, COUNT(*) AS cnt,
|
||
GROUP_CONCAT(CONCAT(t.from_user_id,'→',t.to_user_id) ORDER BY t.created_at SEPARATOR ' | ') AS path,
|
||
i.value_cents, IFNULL(p.name,'') AS pname
|
||
FROM user_inventory_transfers t
|
||
JOIN user_inventory i ON i.id = t.inventory_id
|
||
LEFT JOIN products p ON p.id = i.product_id
|
||
GROUP BY t.inventory_id, i.value_cents, p.name
|
||
HAVING COUNT(*) > 1
|
||
ORDER BY cnt DESC, i.value_cents DESC
|
||
`)
|
||
if rows3 != nil {
|
||
defer rows3.Close()
|
||
fmt.Printf(" %-10s %-6s %-10s %-28s %s\n", "资产ID", "次数", "价值(元)", "商品", "转赠路径")
|
||
fmt.Println(" " + strings.Repeat("-", 95))
|
||
for rows3.Next() {
|
||
var invID, cnt, vc int64
|
||
var path, pname string
|
||
rows3.Scan(&invID, &cnt, &path, &vc, &pname)
|
||
if len([]rune(pname)) > 14 {
|
||
pname = string([]rune(pname)[:14]) + ".."
|
||
}
|
||
fmt.Printf(" %-10d %-6d %-10.2f %-28s %s\n", invID, cnt, float64(vc)/100.0, pname, path)
|
||
}
|
||
}
|
||
|
||
// ============ 4. 转赠关系网络 Top15 ============
|
||
fmt.Println("\n【4】转赠关系网络 Top15")
|
||
fmt.Println(strings.Repeat("-", 110))
|
||
rows4, _ := db.Query(`
|
||
SELECT t.from_user_id, IFNULL(fu.nickname,'') AS fn,
|
||
t.to_user_id, IFNULL(tu.nickname,'') AS tn,
|
||
COUNT(*) AS xfer_cnt, COUNT(DISTINCT t.inventory_id) AS item_cnt,
|
||
SUM(i.value_cents)/100.0 AS total_yuan,
|
||
MIN(t.created_at) AS first_t, MAX(t.created_at) AS last_t
|
||
FROM user_inventory_transfers t
|
||
JOIN user_inventory i ON i.id = t.inventory_id
|
||
LEFT JOIN users fu ON fu.id = t.from_user_id
|
||
LEFT JOIN users tu ON tu.id = t.to_user_id
|
||
GROUP BY t.from_user_id, fu.nickname, t.to_user_id, tu.nickname
|
||
ORDER BY total_yuan DESC LIMIT 15
|
||
`)
|
||
if rows4 != nil {
|
||
defer rows4.Close()
|
||
fmt.Printf(" %-20s → %-20s %-6s %-6s %-12s %-12s %-12s\n",
|
||
"赠送方", "接收方", "转赠次", "资产数", "金额(元)", "首次", "末次")
|
||
fmt.Println(" " + strings.Repeat("-", 105))
|
||
for rows4.Next() {
|
||
var fuid, tuid, xcnt, icnt int64
|
||
var yuan float64
|
||
var fn, tn string
|
||
var ft, lt time.Time
|
||
rows4.Scan(&fuid, &fn, &tuid, &tn, &xcnt, &icnt, &yuan, &ft, <)
|
||
from := fmt.Sprintf("%d(%s)", fuid, truncStr(fn, 6))
|
||
to := fmt.Sprintf("%d(%s)", tuid, truncStr(tn, 6))
|
||
fmt.Printf(" %-20s → %-20s %-6d %-6d %-12.2f %-12s %-12s\n",
|
||
from, to, xcnt, icnt, yuan,
|
||
ft.Format("01-02 15:04"), lt.Format("01-02 15:04"))
|
||
}
|
||
}
|
||
|
||
// ============ 5. 典型利用链路样本（前10条） ============
|
||
fmt.Println("\n【5】典型利用链路样本（转赠→取消发货→兑换积分）")
|
||
fmt.Println(strings.Repeat("-", 130))
|
||
rows5, _ := db.Query(`
|
||
SELECT i.id, i.user_id, IFNULL(u.nickname,'') AS nick,
|
||
i.value_cents, i.status, i.remark
|
||
FROM user_inventory i
|
||
LEFT JOIN users u ON u.id = i.user_id
|
||
WHERE i.remark LIKE '%transferred_from_%'
|
||
AND i.remark LIKE '%shipping_cancelled%'
|
||
AND i.remark LIKE '%redeemed%'
|
||
ORDER BY i.value_cents DESC
|
||
LIMIT 10
|
||
`)
|
||
if rows5 != nil {
|
||
defer rows5.Close()
|
||
fmt.Printf(" %-8s %-8s %-14s %-10s %-6s %s\n", "资产ID", "用户ID", "昵称", "价值(元)", "状态", "操作链路")
|
||
fmt.Println(" " + strings.Repeat("-", 125))
|
||
for rows5.Next() {
|
||
var id, uid, vc int64
|
||
var status int32
|
||
var nick, remark string
|
||
rows5.Scan(&id, &uid, &nick, &vc, &status, &remark)
|
||
fmt.Printf(" %-8d %-8d %-14s %-10.2f %-6s %s\n",
|
||
id, uid, truncStr(nick, 12), float64(vc)/100.0,
|
||
statusText(status), parseActions(remark))
|
||
}
|
||
}
|
||
|
||
fmt.Println("\n✅ 核对完毕")
|
||
}
|
||
|
||
func truncStr(s string, maxRunes int) string {
|
||
runes := []rune(s)
|
||
if len(runes) > maxRunes {
|
||
return string(runes[:maxRunes]) + ".."
|
||
}
|
||
return s
|
||
}
|
||
|
||
func parseActions(remark string) string {
|
||
parts := strings.Split(remark, "|")
|
||
actions := make([]string, 0, len(parts))
|
||
for _, p := range parts {
|
||
p = strings.TrimSpace(p)
|
||
if p == "" {
|
||
continue
|
||
}
|
||
if strings.HasPrefix(p, "transferred_from_") {
|
||
actions = append(actions, "转赠")
|
||
} else if p == "shipping_requested" {
|
||
actions = append(actions, "发货")
|
||
} else if strings.HasPrefix(p, "shipping_cancelled") {
|
||
actions = append(actions, "取消发货")
|
||
} else if strings.Contains(p, "redeemed") {
|
||
actions = append(actions, "✖兑换积分")
|
||
} else {
|
||
actions = append(actions, p)
|
||
}
|
||
}
|
||
return strings.Join(actions, " → ")
|
||
}
|
||
|
||
func statusText(s int32) string {
|
||
switch s {
|
||
case 1:
|
||
return "持有"
|
||
case 2:
|
||
return "作废"
|
||
case 3:
|
||
return "已用"
|
||
default:
|
||
return fmt.Sprintf("%d", s)
|
||
}
|
||
}
|