797 lines
30 KiB
Go
797 lines
30 KiB
Go
package cmd
|
||
|
||
import (
|
||
"bufio"
|
||
"context"
|
||
"fmt"
|
||
"io"
|
||
"math/rand"
|
||
"os"
|
||
"sort"
|
||
"strconv"
|
||
"strings"
|
||
"time"
|
||
|
||
"github.com/perfect-panel/server/internal/config"
|
||
logmodel "github.com/perfect-panel/server/internal/model/log"
|
||
ordermodel "github.com/perfect-panel/server/internal/model/order"
|
||
usermodel "github.com/perfect-panel/server/internal/model/user"
|
||
"github.com/perfect-panel/server/pkg/conf"
|
||
"github.com/perfect-panel/server/pkg/orm"
|
||
"github.com/redis/go-redis/v9"
|
||
"github.com/spf13/cobra"
|
||
"gorm.io/gorm"
|
||
)
|
||
|
||
func init() {
|
||
retroactiveReferralCmd.Flags().StringVar(&retroAgentIdStr, "agent-id", "", "目标代理用户 ID，或 * 表示所有 referral_percentage>0 的代理（必填）")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroPoolStart, "pool-start", "2025-05-04", "自然流量订单起始时间，格式 YYYY-MM-DD 或 'YYYY-MM-DD HH:MM:SS'（默认 2025-05-04）")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroPoolEnd, "pool-end", "", "自然流量订单截止时间，格式 YYYY-MM-DD 或 'YYYY-MM-DD HH:MM:SS'（默认今天）")
|
||
retroactiveReferralCmd.Flags().IntVar(&retroPercentage, "percentage", 120, "补偿百分比，例如 120 表示 120%（默认 120）")
|
||
retroactiveReferralCmd.Flags().IntVar(&retroForceCommissionPct, "force-commission-pct", 50, "强制指定发佣比例（0=使用数据库/配置，非0时覆盖代理设置，默认 50%）")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroOutput, "output", "retro_result.txt", "结果输出到指定 txt 文件（默认 retro_result.txt）")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroConfigPath, "config", "etc/ppanel.yaml", "配置文件路径")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroAgentCreatedAfter, "agent-created-after", "", "仅处理在此日期之后注册的代理，格式 YYYY-MM-DD（留空=不限）")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroLossStart, "loss-start", "2026-05-06", "数据丢失起始时间，丢失时长=现在-此时间（默认 2026-05-06）")
|
||
retroactiveReferralCmd.Flags().StringVar(&retroOrderStart, "order-start", "2026-05-01", "池内用户至少有一笔 updated_at >= 此时间的订单才入池（默认 2026-05-01）")
|
||
retroactiveReferralCmd.Flags().BoolVar(&retroDryRun, "dry-run", false, "仅预览，不执行写入")
|
||
rootCmd.AddCommand(retroactiveReferralCmd)
|
||
}
|
||
|
||
var (
|
||
retroAgentIdStr string
|
||
retroAgentCreatedAfter string
|
||
retroLossStart string
|
||
retroOrderStart string
|
||
retroPoolStart string
|
||
retroPoolEnd string
|
||
retroPercentage int
|
||
retroForceCommissionPct int
|
||
retroConfigPath string
|
||
retroDryRun bool
|
||
retroOutput string
|
||
)
|
||
|
||
var retroactiveReferralCmd = &cobra.Command{
|
||
Use: "retro-referral",
|
||
Short: "补单：按代理历史日均佣金补偿指定比例的用户",
|
||
Long: `统计代理从首次邀请到 pool-end 的日均佣金，
|
||
按指定百分比计算目标补偿金额，
|
||
从 pool-start 到 pool-end 的自然流量用户中随机抽取匹配的用户数量挂载到该代理。
|
||
--agent-id 支持单个 ID 或 *（处理所有 referral_percentage>0 的代理）。`,
|
||
RunE: func(cmd *cobra.Command, args []string) error {
|
||
if retroAgentIdStr == "" {
|
||
return fmt.Errorf("--agent-id 必填（单个 ID 或 *）")
|
||
}
|
||
return runRetroactiveReferral()
|
||
},
|
||
}
|
||
|
||
// commissionRule holds resolved commission settings for an agent.
|
||
type commissionRule struct {
|
||
Percentage uint8
|
||
OnlyFirstPurchase bool
|
||
}
|
||
|
||
// candidateUser holds a pool user plus their pre-calculated qualifying orders.
|
||
type candidateUser struct {
|
||
Id int64
|
||
CreatedAt time.Time
|
||
Identifier string
|
||
Orders []ordermodel.Order
|
||
CommissionTotal int64
|
||
}
|
||
|
||
// agentPlan holds one agent's computed allocation plan (preview phase output).
|
||
type agentPlan struct {
|
||
Agent *usermodel.User
|
||
Rule commissionRule
|
||
Selected []candidateUser
|
||
TargetAmt float64 // in cents
|
||
PreviewCommission int64
|
||
}
|
||
|
||
func runRetroactiveReferral() error {
|
||
// ── 0. 初始化输出（终端 + 可选文件）─────────────────────────
|
||
var w io.Writer = os.Stdout
|
||
if retroOutput != "" {
|
||
f, err := os.Create(retroOutput)
|
||
if err != nil {
|
||
return fmt.Errorf("创建输出文件失败: %w", err)
|
||
}
|
||
defer f.Close()
|
||
w = io.MultiWriter(os.Stdout, f)
|
||
fmt.Printf("结果将同步写入: %s\n\n", retroOutput)
|
||
}
|
||
|
||
// ── 1. 加载配置 ──────────────────────────────────────────────
|
||
var c config.Config
|
||
conf.MustLoad(retroConfigPath, &c)
|
||
|
||
// ── 2. 初始化 DB + Redis ──────────────────────────────────────
|
||
db, err := orm.ConnectMysql(orm.Mysql{Config: c.MySQL})
|
||
if err != nil {
|
||
return fmt.Errorf("连接数据库失败: %w", err)
|
||
}
|
||
rds := redis.NewClient(&redis.Options{
|
||
Addr: c.Redis.Host,
|
||
Password: c.Redis.Pass,
|
||
DB: c.Redis.DB,
|
||
})
|
||
ctx := context.Background()
|
||
if err = rds.Ping(ctx).Err(); err != nil {
|
||
return fmt.Errorf("连接 Redis 失败: %w", err)
|
||
}
|
||
um := usermodel.NewModel(db, rds)
|
||
|
||
// ── 3. 解析时间参数 ───────────────────────────────────────────
|
||
poolStart, err := parseFlexibleTime(retroPoolStart)
|
||
if err != nil {
|
||
return fmt.Errorf("--pool-start 格式错误: %w", err)
|
||
}
|
||
poolEnd := time.Now()
|
||
if retroPoolEnd != "" {
|
||
poolEnd, err = parseFlexibleTime(retroPoolEnd)
|
||
if err != nil {
|
||
return fmt.Errorf("--pool-end 格式错误: %w", err)
|
||
}
|
||
}
|
||
if !poolEnd.After(poolStart) {
|
||
return fmt.Errorf("--pool-end 必须晚于 --pool-start")
|
||
}
|
||
|
||
// ── 4. 确定代理列表 ───────────────────────────────────────────
|
||
var agents []*usermodel.User
|
||
if retroAgentIdStr == "*" {
|
||
agents, err = queryAllActiveAgents(ctx, db, retroAgentCreatedAfter)
|
||
if err != nil {
|
||
return fmt.Errorf("查询代理列表失败: %w", err)
|
||
}
|
||
if len(agents) == 0 {
|
||
return fmt.Errorf("没有找到任何 referral_percentage>0 的代理用户")
|
||
}
|
||
fmt.Fprintf(w, "模式：全量代理，共找到 %d 个代理（referral_percentage>0）\n\n", len(agents))
|
||
} else {
|
||
agentID, parseErr := strconv.ParseInt(retroAgentIdStr, 10, 64)
|
||
if parseErr != nil || agentID <= 0 {
|
||
return fmt.Errorf("--agent-id 必须是正整数或 *")
|
||
}
|
||
agent, findErr := um.FindOne(ctx, agentID)
|
||
if findErr != nil {
|
||
return fmt.Errorf("查询代理用户失败: %w", findErr)
|
||
}
|
||
if agent.DeletedAt.Valid {
|
||
return fmt.Errorf("代理用户 %d 已被删除", agentID)
|
||
}
|
||
agents = []*usermodel.User{agent}
|
||
}
|
||
|
||
if retroForceCommissionPct > 0 {
|
||
fmt.Fprintf(w, "⚠️ 强制覆盖所有代理佣金比例为 %d%%\n\n", retroForceCommissionPct)
|
||
}
|
||
|
||
// ── 5. 查询自然流量用户池（所有代理共用同一个池）────────────
|
||
// 先用 50% 规则（或强制值）预加载池，以便预览；执行时每个代理用自身规则
|
||
var orderStart time.Time
|
||
if retroOrderStart != "" {
|
||
orderStart, err = parseFlexibleTime(retroOrderStart)
|
||
if err != nil {
|
||
return fmt.Errorf("--order-start 格式错误: %w", err)
|
||
}
|
||
}
|
||
previewRule := commissionRule{Percentage: uint8(retroForceCommissionPct), OnlyFirstPurchase: false}
|
||
pool, err := queryNaturalTrafficPool(ctx, db, poolStart, poolEnd, orderStart, previewRule)
|
||
if err != nil {
|
||
return fmt.Errorf("查询自然流量用户池失败: %w", err)
|
||
}
|
||
orderStartDesc := ""
|
||
if !orderStart.IsZero() {
|
||
orderStartDesc = fmt.Sprintf("，订单 updated_at >= %s", orderStart.Format("2006-01-02"))
|
||
}
|
||
fmt.Fprintf(w, "自然流量用户池（%s ~ %s，referer_id=0，有已支付订单%s）：共 %d 人\n\n",
|
||
poolStart.Format("2006-01-02 15:04"), poolEnd.Format("2006-01-02 15:04"), orderStartDesc, len(pool))
|
||
if len(pool) == 0 {
|
||
return fmt.Errorf("自然流量用户池为空，无法补充")
|
||
}
|
||
|
||
// ── 6. 逐代理生成分配计划（预览阶段）────────────────────────
|
||
// 池按顺序分配：每个代理从剩余池中取用户，避免重复分配
|
||
remainingPool := make([]candidateUser, len(pool))
|
||
copy(remainingPool, pool)
|
||
|
||
plans := make([]agentPlan, 0, len(agents))
|
||
for _, agent := range agents {
|
||
plan, planErr := buildAgentPlan(w, ctx, db, c, agent, remainingPool, poolEnd)
|
||
if planErr != nil {
|
||
fmt.Fprintf(w, "⚠️ 代理 %d 跳过: %v\n\n", agent.Id, planErr)
|
||
continue
|
||
}
|
||
// 从剩余池中移除已分配给该代理的用户
|
||
assignedSet := make(map[int64]struct{}, len(plan.Selected))
|
||
for _, u := range plan.Selected {
|
||
assignedSet[u.Id] = struct{}{}
|
||
}
|
||
newRemaining := remainingPool[:0]
|
||
for _, u := range remainingPool {
|
||
if _, used := assignedSet[u.Id]; !used {
|
||
newRemaining = append(newRemaining, u)
|
||
}
|
||
}
|
||
remainingPool = newRemaining
|
||
plans = append(plans, plan)
|
||
}
|
||
|
||
if len(plans) == 0 {
|
||
return fmt.Errorf("所有代理均无法生成分配计划")
|
||
}
|
||
|
||
// ── 7. 汇总预览 ───────────────────────────────────────────────
|
||
var grandTotalUsers int
|
||
var grandTotalCommission int64
|
||
var grandTargetAmt float64
|
||
for _, p := range plans {
|
||
grandTotalUsers += len(p.Selected)
|
||
grandTotalCommission += p.PreviewCommission
|
||
grandTargetAmt += p.TargetAmt
|
||
}
|
||
fmt.Fprintln(w, strings.Repeat("═", 75))
|
||
fmt.Fprintf(w, "汇总：共 %d 个代理，补充 %d 个用户\n", len(plans), grandTotalUsers)
|
||
fmt.Fprintf(w, " 预计追溯佣金总额: $%.2f（目标补偿金额: $%.2f）\n",
|
||
float64(grandTotalCommission)/100, grandTargetAmt/100)
|
||
fmt.Fprintln(w, strings.Repeat("═", 75))
|
||
fmt.Fprintln(w)
|
||
|
||
if retroDryRun {
|
||
fmt.Fprintln(w, "[dry-run] 预览完成，未执行任何写入。")
|
||
return nil
|
||
}
|
||
|
||
// ── 8. 执行前汇总打印 ─────────────────────────────────────────
|
||
fmt.Fprintf(w, "\n┌─────────────────────────────────────────────┐\n")
|
||
fmt.Fprintf(w, "│ 即将写入数据库 │\n")
|
||
fmt.Fprintf(w, "│ 代理数量 : %-4d 个 │\n", len(plans))
|
||
fmt.Fprintf(w, "│ 补充用户 : %-4d 人 │\n", grandTotalUsers)
|
||
fmt.Fprintf(w, "│ 赠送金额 : $%-10.2f │\n", float64(grandTotalCommission)/100)
|
||
fmt.Fprintf(w, "└─────────────────────────────────────────────┘\n\n")
|
||
fmt.Printf("确认执行？(yes/no): ")
|
||
reader := bufio.NewReader(os.Stdin)
|
||
answer, _ := reader.ReadString('\n')
|
||
answer = strings.TrimSpace(strings.ToLower(answer))
|
||
if answer != "yes" && answer != "y" {
|
||
fmt.Fprintln(w, "已取消。")
|
||
return nil
|
||
}
|
||
|
||
// ── 9. 逐代理执行 ─────────────────────────────────────────────
|
||
var totalSuccess, totalFailed int
|
||
var totalCreditedOrder, totalCreditedAmt int64
|
||
|
||
for _, plan := range plans {
|
||
fmt.Fprintf(w, "\n── 执行代理 %d ──────────────────────────────────────────────\n", plan.Agent.Id)
|
||
var sc, fc int
|
||
var co, ca int64
|
||
for _, eu := range plan.Selected {
|
||
if eu.Id == plan.Agent.Id {
|
||
fmt.Fprintf(w, "[SKIP] 用户 %d 与代理相同，跳过\n", eu.Id)
|
||
fc++
|
||
continue
|
||
}
|
||
credited, amount, execErr := processOneUser(ctx, db, um, plan.Agent.Id, eu.Id, plan.Rule, orderStart)
|
||
if execErr != nil {
|
||
fmt.Fprintf(w, "[FAIL] 用户 %d: %v\n", eu.Id, execErr)
|
||
fc++
|
||
continue
|
||
}
|
||
fmt.Fprintf(w, "[OK] 用户 %d → 代理 %d，发佣 %d 单，金额 $%.2f\n",
|
||
eu.Id, plan.Agent.Id, credited, float64(amount)/100)
|
||
sc++
|
||
co += credited
|
||
ca += amount
|
||
}
|
||
// 直接删除代理的缓存 key，下次请求时从 DB 重新加载（避免 FindOne 读到旧缓存再写回）
|
||
if sc > 0 {
|
||
cacheKey := fmt.Sprintf("cache:user:id:%d", plan.Agent.Id)
|
||
_ = rds.Del(ctx, cacheKey).Err()
|
||
}
|
||
fmt.Fprintf(w, " 代理 %d 小计：成功 %d 人，失败 %d 人，佣金 $%.2f\n", plan.Agent.Id, sc, fc, float64(ca)/100)
|
||
totalSuccess += sc
|
||
totalFailed += fc
|
||
totalCreditedOrder += co
|
||
totalCreditedAmt += ca
|
||
}
|
||
|
||
// ── 10. 全局汇总 ──────────────────────────────────────────────
|
||
fmt.Fprintf(w, "\n══════════════════════════════════════════════\n")
|
||
fmt.Fprintf(w, " 成功挂载 : %d 人\n", totalSuccess)
|
||
fmt.Fprintf(w, " 失败/跳过 : %d 人\n", totalFailed)
|
||
fmt.Fprintf(w, " 追溯佣金 : %d 单，总额 $%.2f\n", totalCreditedOrder, float64(totalCreditedAmt)/100)
|
||
fmt.Fprintf(w, "══════════════════════════════════════════════\n")
|
||
return nil
|
||
}
|
||
|
||
// buildAgentPlan 计算一个代理的补单预览，同时打印预览内容，返回分配计划。
|
||
func buildAgentPlan(w io.Writer, ctx context.Context, db *gorm.DB, c config.Config,
|
||
agent *usermodel.User, pool []candidateUser, poolEnd time.Time) (agentPlan, error) {
|
||
|
||
rule := resolveCommissionRule(agent, c)
|
||
if retroForceCommissionPct > 0 {
|
||
rule.Percentage = uint8(retroForceCommissionPct)
|
||
}
|
||
// 补单场景：不区分新购/续费，所有已支付订单均参与佣金计算
|
||
rule.OnlyFirstPurchase = false
|
||
|
||
firstReferralTime, lastReferralTime, agentCreatedAt, totalReferred, totalOrders, totalCommission, err :=
|
||
queryAgentStats(ctx, db, agent.Id, poolEnd)
|
||
if err != nil {
|
||
return agentPlan{}, fmt.Errorf("查询历史数据失败: %w", err)
|
||
}
|
||
if totalReferred == 0 {
|
||
return agentPlan{}, fmt.Errorf("在 %s 之前没有任何邀请记录", poolEnd.Format("2006-01-02"))
|
||
}
|
||
|
||
lossStartTime, err := parseFlexibleTime(retroLossStart)
|
||
if err != nil {
|
||
return agentPlan{}, fmt.Errorf("--loss-start 格式错误: %w", err)
|
||
}
|
||
lossHours := time.Now().Sub(lossStartTime).Hours()
|
||
|
||
statsDays := lastReferralTime.Sub(firstReferralTime).Hours() / 24
|
||
if statsDays < 1 {
|
||
statsDays = 1
|
||
}
|
||
dailyAvgOrders := float64(totalOrders) / statsDays
|
||
avgOrdersPerUser := float64(totalOrders) / float64(totalReferred)
|
||
if avgOrdersPerUser < 1 {
|
||
avgOrdersPerUser = 1
|
||
}
|
||
dailyAvgCommission := float64(totalCommission) / statsDays
|
||
|
||
// 用用户池自身的平均佣金估算人数（避免历史费率与当前50%费率不匹配导致超发）
|
||
var poolAvgCommissionPerUser float64
|
||
if len(pool) > 0 {
|
||
var poolCommTotal int64
|
||
for _, u := range pool {
|
||
poolCommTotal += u.CommissionTotal
|
||
}
|
||
poolAvgCommissionPerUser = float64(poolCommTotal) / float64(len(pool))
|
||
}
|
||
if poolAvgCommissionPerUser < 1 {
|
||
poolAvgCommissionPerUser = 1
|
||
}
|
||
|
||
estimatedLostCommission := dailyAvgCommission * (lossHours / 24)
|
||
targetCommission := estimatedLostCommission * (float64(retroPercentage) / 100)
|
||
extraCount := int(targetCommission/poolAvgCommissionPerUser + 0.5)
|
||
if extraCount < 1 {
|
||
extraCount = 1
|
||
}
|
||
|
||
fmt.Fprintf(w, "\n═══════════════════════════════════════════════════════════\n")
|
||
fmt.Fprintf(w, " 代理 ID : %d（注册于 %s）\n", agent.Id, agentCreatedAt.Format("2006-01-02 15:04:05"))
|
||
fmt.Fprintf(w, " 统计起点 : %s（首次邀请时间）\n", firstReferralTime.Format("2006-01-02 15:04:05"))
|
||
fmt.Fprintf(w, " 统计截止 : %s（最后邀请时间）\n", lastReferralTime.Format("2006-01-02 15:04:05"))
|
||
fmt.Fprintf(w, " 统计天数 : %.2f 天\n", statsDays)
|
||
fmt.Fprintf(w, " 历史邀请总人数 : %d 人\n", totalReferred)
|
||
fmt.Fprintf(w, " 下线总订单数 : %d 单（所有下线，不限时间）\n", totalOrders)
|
||
fmt.Fprintf(w, " 日均订单 : %.4f 单/天\n", dailyAvgOrders)
|
||
fmt.Fprintf(w, " 每用户平均订单 : %.4f 单\n", avgOrdersPerUser)
|
||
fmt.Fprintf(w, " 历史佣金总额 : $%.2f\n", float64(totalCommission)/100)
|
||
fmt.Fprintf(w, " 日均佣金 : $%.4f\n", dailyAvgCommission/100)
|
||
fmt.Fprintf(w, " 池内用户均佣金 : $%.4f\n", poolAvgCommissionPerUser/100)
|
||
fmt.Fprintf(w, " 佣金规则 : %d%% 仅首单=%v\n", rule.Percentage, rule.OnlyFirstPurchase)
|
||
fmt.Fprintf(w, "───────────────────────────────────────────────────────────\n")
|
||
fmt.Fprintf(w, " 丢失时长 : %.2f 小时（%s → 现在）\n",
|
||
lossHours, lossStartTime.Format("2006-01-02 15:04:05"))
|
||
fmt.Fprintf(w, " 预估丢失佣金 : $%.4f（$%.4f × %.2f/24）\n",
|
||
estimatedLostCommission/100, dailyAvgCommission/100, lossHours)
|
||
fmt.Fprintf(w, " 目标补偿金额 : $%.4f（× %d%%）\n",
|
||
targetCommission/100, retroPercentage)
|
||
fmt.Fprintf(w, " 需补充人数 : %d 人（$%.4f ÷ $%.4f）\n",
|
||
extraCount, targetCommission/100, poolAvgCommissionPerUser/100)
|
||
fmt.Fprintf(w, "═══════════════════════════════════════════════════════════\n\n")
|
||
|
||
// 重新按当前代理规则计算池内用户佣金（pool 由调用方传入，已是当前规则计算好的）
|
||
if len(pool) == 0 {
|
||
return agentPlan{}, fmt.Errorf("剩余用户池为空")
|
||
}
|
||
if len(pool) < extraCount {
|
||
fmt.Fprintf(w, "⚠️ 剩余用户池只有 %d 人，少于需要的 %d 人，将全部分配\n\n", len(pool), extraCount)
|
||
extraCount = len(pool)
|
||
}
|
||
|
||
selected := randomSampleCandidates(pool, extraCount)
|
||
|
||
// 兜底追加：确保佣金合计 >= 目标
|
||
{
|
||
selectedSet := make(map[int64]struct{}, len(selected))
|
||
var selectedCommTotal int64
|
||
for _, u := range selected {
|
||
selectedSet[u.Id] = struct{}{}
|
||
selectedCommTotal += u.CommissionTotal
|
||
}
|
||
if selectedCommTotal < int64(targetCommission) {
|
||
remaining := make([]candidateUser, 0, len(pool)-len(selected))
|
||
for _, u := range pool {
|
||
if _, used := selectedSet[u.Id]; !used {
|
||
remaining = append(remaining, u)
|
||
}
|
||
}
|
||
// 按佣金从小到大排序，追加时精准补足，减少超发
|
||
sort.Slice(remaining, func(i, j int) bool {
|
||
return remaining[i].CommissionTotal < remaining[j].CommissionTotal
|
||
})
|
||
for _, u := range remaining {
|
||
if selectedCommTotal >= int64(targetCommission) {
|
||
break
|
||
}
|
||
selected = append(selected, u)
|
||
selectedCommTotal += u.CommissionTotal
|
||
}
|
||
if selectedCommTotal < int64(targetCommission) {
|
||
fmt.Fprintf(w, "⚠️ 用户池佣金不足，已抽取全部可用用户（实际 $%.2f < 目标 $%.2f）\n\n",
|
||
float64(selectedCommTotal)/100, targetCommission/100)
|
||
}
|
||
}
|
||
}
|
||
|
||
// 打印选中用户明细
|
||
var previewTotalCommission int64
|
||
fmt.Fprintf(w, "随机抽取 %d 个用户（含待追溯佣金订单）：\n", len(selected))
|
||
fmt.Fprintln(w, strings.Repeat("═", 75))
|
||
for i, u := range selected {
|
||
fmt.Fprintf(w, "[%d] 用户 %-10d 注册: %s %s\n",
|
||
i+1, u.Id, u.CreatedAt.Format("2006-01-02 15:04:05"), u.Identifier)
|
||
if len(u.Orders) == 0 {
|
||
fmt.Fprintln(w, " （无符合条件的订单）")
|
||
} else {
|
||
fmt.Fprintf(w, " %-38s %10s %8s %10s %s\n", "订单号", "金额", "手续费", "佣金", "类型")
|
||
fmt.Fprintf(w, " %s\n", strings.Repeat("-", 72))
|
||
for _, od := range u.Orders {
|
||
commAmt := calcCommissionAmount(od.Amount, od.FeeAmount, rule.Percentage)
|
||
orderType := "首购"
|
||
if od.Type == 2 {
|
||
orderType = "续费"
|
||
}
|
||
fmt.Fprintf(w, " %-38s $%8.2f $%6.2f $%8.2f %s\n",
|
||
od.OrderNo,
|
||
float64(od.Amount)/100,
|
||
float64(od.FeeAmount)/100,
|
||
float64(commAmt)/100,
|
||
orderType)
|
||
}
|
||
fmt.Fprintf(w, " 本用户追溯佣金合计: $%.2f\n", float64(u.CommissionTotal)/100)
|
||
}
|
||
previewTotalCommission += u.CommissionTotal
|
||
fmt.Fprintln(w)
|
||
}
|
||
fmt.Fprintln(w, strings.Repeat("═", 75))
|
||
fmt.Fprintf(w, "预计追溯佣金总额: $%.2f（目标补偿金额: $%.2f）\n\n",
|
||
float64(previewTotalCommission)/100, targetCommission/100)
|
||
|
||
return agentPlan{
|
||
Agent: agent,
|
||
Rule: rule,
|
||
Selected: selected,
|
||
TargetAmt: targetCommission,
|
||
PreviewCommission: previewTotalCommission,
|
||
}, nil
|
||
}
|
||
|
||
// queryAllActiveAgents returns all agents with referral_percentage > 0, optionally filtered by created_after.
|
||
func queryAllActiveAgents(ctx context.Context, db *gorm.DB, createdAfter string) ([]*usermodel.User, error) {
|
||
q := db.WithContext(ctx).Model(&usermodel.User{}).
|
||
Where("referral_percentage > 0 AND deleted_at IS NULL")
|
||
if createdAfter != "" {
|
||
t, err := parseFlexibleTime(createdAfter)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("--agent-created-after 格式错误: %w", err)
|
||
}
|
||
q = q.Where("created_at >= ?", t)
|
||
}
|
||
var agents []*usermodel.User
|
||
if err := q.Order("id ASC").Find(&agents).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
return agents, nil
|
||
}
|
||
|
||
// parseFlexibleTime parses "YYYY-MM-DD HH:MM:SS" or "YYYY-MM-DD".
|
||
func parseFlexibleTime(s string) (time.Time, error) {
|
||
s = strings.TrimSpace(s)
|
||
if t, err := time.ParseInLocation("2006-01-02 15:04:05", s, time.Local); err == nil {
|
||
return t, nil
|
||
}
|
||
return time.ParseInLocation("2006-01-02", s, time.Local)
|
||
}
|
||
|
||
// queryAgentStats returns (firstReferralTime, lastReferralTime, agentCreatedAt, totalReferred, totalOrders, totalCommission, error).
|
||
func queryAgentStats(ctx context.Context, db *gorm.DB, agentID int64, endTime time.Time) (time.Time, time.Time, time.Time, int64, int64, int64, error) {
|
||
var agent usermodel.User
|
||
if err := db.WithContext(ctx).Model(&usermodel.User{}).
|
||
Where("id = ?", agentID).
|
||
First(&agent).Error; err != nil {
|
||
return time.Time{}, time.Time{}, time.Time{}, 0, 0, 0, err
|
||
}
|
||
agentCreatedAt := agent.CreatedAt
|
||
|
||
var firstUser usermodel.User
|
||
if err := db.WithContext(ctx).Model(&usermodel.User{}).
|
||
Where("referer_id = ? AND created_at >= ? AND created_at <= ? AND deleted_at IS NULL",
|
||
agentID, agentCreatedAt, endTime).
|
||
Order("created_at ASC").
|
||
First(&firstUser).Error; err != nil {
|
||
if err == gorm.ErrRecordNotFound {
|
||
return time.Time{}, time.Time{}, agentCreatedAt, 0, 0, 0, nil
|
||
}
|
||
return time.Time{}, time.Time{}, agentCreatedAt, 0, 0, 0, err
|
||
}
|
||
|
||
var lastUser usermodel.User
|
||
if err := db.WithContext(ctx).Model(&usermodel.User{}).
|
||
Where("referer_id = ? AND created_at >= ? AND created_at <= ? AND deleted_at IS NULL",
|
||
agentID, agentCreatedAt, endTime).
|
||
Order("created_at DESC").
|
||
First(&lastUser).Error; err != nil {
|
||
return time.Time{}, time.Time{}, agentCreatedAt, 0, 0, 0, err
|
||
}
|
||
|
||
var totalReferred int64
|
||
if err := db.WithContext(ctx).Model(&usermodel.User{}).
|
||
Where("referer_id = ? AND created_at >= ? AND created_at <= ? AND deleted_at IS NULL",
|
||
agentID, agentCreatedAt, endTime).
|
||
Count(&totalReferred).Error; err != nil {
|
||
return time.Time{}, time.Time{}, agentCreatedAt, 0, 0, 0, err
|
||
}
|
||
|
||
var totalOrders int64
|
||
if err := db.WithContext(ctx).Model(&ordermodel.Order{}).
|
||
Joins("JOIN user u ON u.id = `order`.user_id").
|
||
Where("u.referer_id = ?", agentID).
|
||
Where("`order`.status IN (2, 5)").
|
||
Count(&totalOrders).Error; err != nil {
|
||
return time.Time{}, time.Time{}, agentCreatedAt, 0, 0, 0, err
|
||
}
|
||
|
||
type commResult struct{ Total int64 }
|
||
var result commResult
|
||
err := db.WithContext(ctx).Raw(`
|
||
SELECT COALESCE(SUM(
|
||
CAST(JSON_UNQUOTE(JSON_EXTRACT(content, '$.amount')) AS SIGNED)
|
||
), 0) AS total
|
||
FROM system_logs
|
||
WHERE type = 33
|
||
AND object_id = ?
|
||
AND created_at <= ?
|
||
AND JSON_UNQUOTE(JSON_EXTRACT(content, '$.type')) IN ('331', '332')
|
||
`, agentID, endTime).Scan(&result).Error
|
||
if err != nil {
|
||
return time.Time{}, time.Time{}, agentCreatedAt, 0, 0, 0, err
|
||
}
|
||
|
||
return firstUser.CreatedAt, lastUser.CreatedAt, agentCreatedAt, totalReferred, totalOrders, result.Total, nil
|
||
}
|
||
|
||
// queryNaturalTrafficPool returns pool candidates with pre-loaded qualifying orders.
|
||
// orderStart (可为零值)：若非零，则只收录至少有一笔 updated_at >= orderStart 订单的用户，
|
||
// 且只加载/统计 updated_at >= orderStart 的订单（确保分配后代理能在对应月份的销售报表中看到记录）。
|
||
func queryNaturalTrafficPool(ctx context.Context, db *gorm.DB, start, end time.Time, orderStart time.Time, rule commissionRule) ([]candidateUser, error) {
|
||
type userRow struct {
|
||
Id int64
|
||
CreatedAt time.Time
|
||
AuthIdentifier string
|
||
}
|
||
var userRows []userRow
|
||
q := db.WithContext(ctx).
|
||
Table("user u").
|
||
Select("u.id, u.created_at, COALESCE(am.auth_identifier, '') AS auth_identifier").
|
||
Joins("JOIN `order` o ON o.user_id = u.id AND o.status IN (2, 5)").
|
||
Joins("LEFT JOIN user_auth_methods am ON am.user_id = u.id AND am.auth_type = 'email'").
|
||
Where("u.created_at >= ? AND u.created_at <= ?", start, end).
|
||
Where("u.referer_id = 0").
|
||
Where("u.deleted_at IS NULL")
|
||
if !orderStart.IsZero() {
|
||
// 只入池那些在 orderStart 之后有过订单的用户（保证代理销售报表里能看到）
|
||
q = q.Where("o.updated_at >= ?", orderStart)
|
||
}
|
||
if err := q.Group("u.id, u.created_at, am.auth_identifier").
|
||
Order("u.id ASC").
|
||
Scan(&userRows).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
if len(userRows) == 0 {
|
||
return nil, nil
|
||
}
|
||
|
||
userIDs := make([]int64, len(userRows))
|
||
for i, r := range userRows {
|
||
userIDs[i] = r.Id
|
||
}
|
||
orderQuery := db.WithContext(ctx).Model(&ordermodel.Order{}).
|
||
Where("user_id IN ? AND status IN (2, 5)", userIDs)
|
||
if !orderStart.IsZero() {
|
||
// 只加载 orderStart 之后的订单：保证佣金统计和销售记录对齐
|
||
orderQuery = orderQuery.Where("updated_at >= ?", orderStart)
|
||
}
|
||
var allOrders []ordermodel.Order
|
||
if err := orderQuery.Order("user_id ASC, created_at ASC").Find(&allOrders).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
ordersByUser := make(map[int64][]ordermodel.Order, len(userRows))
|
||
for _, od := range allOrders {
|
||
ordersByUser[od.UserId] = append(ordersByUser[od.UserId], od)
|
||
}
|
||
|
||
candidates := make([]candidateUser, 0, len(userRows))
|
||
for _, r := range userRows {
|
||
orders := ordersByUser[r.Id]
|
||
var commTotal int64
|
||
for i := range orders {
|
||
if canCreditOrder(rule, &orders[i]) {
|
||
commTotal += calcCommissionAmount(orders[i].Amount, orders[i].FeeAmount, rule.Percentage)
|
||
}
|
||
}
|
||
candidates = append(candidates, candidateUser{
|
||
Id: r.Id,
|
||
CreatedAt: r.CreatedAt,
|
||
Identifier: r.AuthIdentifier,
|
||
Orders: orders,
|
||
CommissionTotal: commTotal,
|
||
})
|
||
}
|
||
return candidates, nil
|
||
}
|
||
|
||
// randomSampleCandidates picks n random elements from pool without replacement.
|
||
func randomSampleCandidates(pool []candidateUser, n int) []candidateUser {
|
||
if n >= len(pool) {
|
||
return append([]candidateUser{}, pool...)
|
||
}
|
||
rng := rand.New(rand.NewSource(time.Now().UnixNano()))
|
||
indices := rng.Perm(len(pool))[:n]
|
||
result := make([]candidateUser, n)
|
||
for i, idx := range indices {
|
||
result[i] = pool[idx]
|
||
}
|
||
return result
|
||
}
|
||
|
||
// processOneUser assigns agentId as referer and retroactively credits commission for qualifying orders.
|
||
func processOneUser(
|
||
ctx context.Context,
|
||
db *gorm.DB,
|
||
um usermodel.Model,
|
||
agentID, userID int64,
|
||
rule commissionRule,
|
||
orderStart time.Time,
|
||
) (int64, int64, error) {
|
||
var creditedOrders, creditedAmount int64
|
||
|
||
err := db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
|
||
var target usermodel.User
|
||
if e := tx.Model(&usermodel.User{}).
|
||
Where("id = ? AND referer_id = 0 AND deleted_at IS NULL", userID).
|
||
First(&target).Error; e != nil {
|
||
if e == gorm.ErrRecordNotFound {
|
||
return fmt.Errorf("用户不存在或已有代理或已删除")
|
||
}
|
||
return e
|
||
}
|
||
|
||
var orders []ordermodel.Order
|
||
oq := tx.Model(&ordermodel.Order{}).
|
||
Where("user_id = ? AND status IN (2, 5)", userID)
|
||
if !orderStart.IsZero() {
|
||
oq = oq.Where("updated_at >= ?", orderStart)
|
||
}
|
||
if e := oq.Order("created_at ASC, id ASC").Find(&orders).Error; e != nil {
|
||
return e
|
||
}
|
||
if len(orders) == 0 {
|
||
return fmt.Errorf("无合格订单")
|
||
}
|
||
|
||
if e := tx.Model(&usermodel.User{}).
|
||
Where("id = ? AND referer_id = 0 AND deleted_at IS NULL", userID).
|
||
Updates(map[string]interface{}{
|
||
"referer_id": agentID,
|
||
"updated_at": time.Now(),
|
||
}).Error; e != nil {
|
||
return e
|
||
}
|
||
|
||
for i := range orders {
|
||
od := &orders[i]
|
||
if !canCreditOrder(rule, od) {
|
||
continue
|
||
}
|
||
amount := calcCommissionAmount(od.Amount, od.FeeAmount, rule.Percentage)
|
||
if amount <= 0 {
|
||
continue
|
||
}
|
||
|
||
var existCount int64
|
||
if e := tx.Model(&logmodel.SystemLog{}).
|
||
Where("type = ? AND object_id = ? AND content LIKE ?",
|
||
logmodel.TypeCommission.Uint8(), agentID,
|
||
fmt.Sprintf("%%\"%s\"%%", od.OrderNo),
|
||
).Count(&existCount).Error; e != nil {
|
||
return e
|
||
}
|
||
if existCount > 0 {
|
||
continue
|
||
}
|
||
|
||
if e := tx.Model(&usermodel.User{}).
|
||
Where("id = ? AND deleted_at IS NULL", agentID).
|
||
UpdateColumn("commission", gorm.Expr("commission + ?", amount)).Error; e != nil {
|
||
return e
|
||
}
|
||
|
||
commType := logmodel.CommissionTypePurchase
|
||
if od.Type == 2 {
|
||
commType = logmodel.CommissionTypeRenewal
|
||
}
|
||
payload := &logmodel.Commission{
|
||
Type: commType,
|
||
Amount: amount,
|
||
OrderNo: od.OrderNo,
|
||
Timestamp: od.CreatedAt.UnixMilli(),
|
||
}
|
||
content, _ := payload.Marshal()
|
||
if e := tx.Create(&logmodel.SystemLog{
|
||
Type: logmodel.TypeCommission.Uint8(),
|
||
Date: od.CreatedAt.Format("2006-01-02"),
|
||
ObjectID: agentID,
|
||
Content: string(content),
|
||
CreatedAt: od.CreatedAt,
|
||
}).Error; e != nil {
|
||
return e
|
||
}
|
||
creditedOrders++
|
||
creditedAmount += amount
|
||
}
|
||
return nil
|
||
})
|
||
if err != nil {
|
||
return 0, 0, err
|
||
}
|
||
|
||
if updated, e := um.FindOne(ctx, userID); e == nil {
|
||
_ = um.UpdateUserCache(ctx, updated)
|
||
}
|
||
return creditedOrders, creditedAmount, nil
|
||
}
|
||
|
||
func resolveCommissionRule(agent *usermodel.User, c config.Config) commissionRule {
|
||
if agent.ReferralPercentage > 0 {
|
||
onlyFirst := true
|
||
if agent.OnlyFirstPurchase != nil {
|
||
onlyFirst = *agent.OnlyFirstPurchase
|
||
}
|
||
return commissionRule{Percentage: agent.ReferralPercentage, OnlyFirstPurchase: onlyFirst}
|
||
}
|
||
return commissionRule{
|
||
Percentage: uint8(c.Invite.ReferralPercentage),
|
||
OnlyFirstPurchase: c.Invite.OnlyFirstPurchase,
|
||
}
|
||
}
|
||
|
||
func canCreditOrder(rule commissionRule, od *ordermodel.Order) bool {
|
||
if rule.Percentage == 0 {
|
||
return false
|
||
}
|
||
if rule.OnlyFirstPurchase && !od.IsNew {
|
||
return false
|
||
}
|
||
return od.Status == 2 || od.Status == 5
|
||
}
|
||
|
||
func calcCommissionAmount(amount, feeAmount int64, percentage uint8) int64 {
|
||
base := amount - feeAmount
|
||
if base <= 0 || percentage == 0 {
|
||
return 0
|
||
}
|
||
return int64(float64(base) * float64(percentage) / 100)
|
||
}
|