bindbox-game/cmd/channel_stats_compare/main.go

package main

import (
	"fmt"
	"strings"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

func main() {
	dsn := "root:bindbox2025kdy@tcp(150.158.78.154:3306)/dev_game?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		fmt.Println("连接失败:", err)
		return
	}

	channelID := 3
	orderFilter := "users.channel_id = ? AND users.deleted_at IS NULL AND orders.status = 2 AND orders.actual_amount > 0 AND orders.source_type IN (1,2,3,4) AND (orders.ext_order_id = '' OR orders.ext_order_id IS NULL)"

	// 1. actual_amount 统计
	type AmountResult struct {
		OrderCount  int64
		TotalCents  int64
	}
	var ar AmountResult
	db.Table("orders").
		Joins("JOIN users ON users.id = orders.user_id").
		Select("COUNT(DISTINCT orders.id) as order_count, COALESCE(SUM(orders.actual_amount), 0) as total_cents").
		Where(orderFilter, channelID).
		Scan(&ar)

	fmt.Println("========================================")
	fmt.Printf("渠道 %d 数据对比\n", channelID)
	fmt.Println("========================================")
	fmt.Println()
	fmt.Println("【方式1】SUM(actual_amount) — 用户实际支付")
	fmt.Printf("  订单数: %d\n", ar.OrderCount)
	fmt.Printf("  金额:   %d 分 = %.2f 元\n", ar.TotalCents, float64(ar.TotalCents)/100)
	fmt.Println()

	// 2. 取所有订单的 remark，Go 中解析
	type RemarkRow struct {
		Remark string
	}
	var remarks []RemarkRow
	db.Table("orders").
		Joins("JOIN users ON users.id = orders.user_id").
		Select("orders.remark").
		Where(orderFilter, channelID).
		Scan(&remarks)

	// 解析 remark 收集 activityIDs
	type parsed struct {
		activityID int64
		count      int64
	}
	var items []parsed
	idSet := make(map[int64]struct{})
	noRemarkCount := 0

	for _, r := range remarks {
		aid, cnt := parseRemark(r.Remark)
		if aid > 0 {
			items = append(items, parsed{activityID: aid, count: cnt})
			idSet[aid] = struct{}{}
		} else {
			noRemarkCount++
		}
	}

	// 批量查 price_draw（含软删除）
	actIDs := make([]int64, 0, len(idSet))
	for id := range idSet {
		actIDs = append(actIDs, id)
	}

	type ActPrice struct {
		ID        int64
		PriceDraw int64
	}
	priceMap := make(map[int64]int64)
	if len(actIDs) > 0 {
		var acts []ActPrice
		db.Unscoped().Table("activities").
			Select("id, price_draw").
			Where("id IN ?", actIDs).
			Find(&acts)
		for _, a := range acts {
			priceMap[a.ID] = a.PriceDraw
		}
	}

	// 计算 price_draw × count
	var totalPriceDraw int64
	matchedCount := 0
	unmatchedCount := 0
	for _, item := range items {
		if price, ok := priceMap[item.activityID]; ok {
			totalPriceDraw += price * item.count
			matchedCount++
		} else {
			unmatchedCount++
		}
	}

	fmt.Println("【方式2】price_draw × count — 门票原价（当前实现）")
	fmt.Printf("  有效订单: %d (有 remark 且匹配活动)\n", matchedCount)
	fmt.Printf("  无 remark: %d\n", noRemarkCount)
	fmt.Printf("  活动不存在: %d\n", unmatchedCount)
	fmt.Printf("  金额:   %d 分 = %.2f 元\n", totalPriceDraw, float64(totalPriceDraw)/100)
	fmt.Println()

	// 3. 差额
	diff := totalPriceDraw - ar.TotalCents
	fmt.Println("【差异分析】")
	fmt.Printf("  price_draw×count - actual_amount = %d 分 = %.2f 元\n", diff, float64(diff)/100)
	if diff > 0 {
		fmt.Printf("  说明: 用户总共享受了 %.2f 元优惠（优惠券/积分/折扣）\n", float64(diff)/100)
	} else if diff < 0 {
		fmt.Printf("  说明: actual_amount 比 price_draw×count 多 %.2f 元（可能有额外费用）\n", float64(-diff)/100)
	} else {
		fmt.Println("  说明: 两者完全一致，无优惠抵扣")
	}
	fmt.Println()

	// 4. 抽样展示前10条差异订单
	type DetailRow struct {
		OrderID      int64
		ActualAmount int64
		Remark       string
	}
	var details []DetailRow
	db.Table("orders").
		Joins("JOIN users ON users.id = orders.user_id").
		Select("orders.id as order_id, orders.actual_amount, orders.remark").
		Where(orderFilter, channelID).
		Limit(200).
		Scan(&details)

	fmt.Println("【差异订单抽样（前10条有差异的）】")
	fmt.Printf("%-10s %-15s %-15s %-10s %s\n", "订单ID", "actual_amount", "price×count", "差额", "remark摘要")
	fmt.Println(strings.Repeat("-", 90))
	shown := 0
	for _, d := range details {
		aid, cnt := parseRemark(d.Remark)
		if aid <= 0 {
			continue
		}
		price, ok := priceMap[aid]
		if !ok {
			continue
		}
		priceTotal := price * cnt
		orderDiff := priceTotal - d.ActualAmount
		if orderDiff != 0 && shown < 10 {
			remarkShort := d.Remark
			if len(remarkShort) > 40 {
				remarkShort = remarkShort[:40] + "..."
			}
			fmt.Printf("%-10d %-15d %-15d %-10d %s\n", d.OrderID, d.ActualAmount, priceTotal, orderDiff, remarkShort)
			shown++
		}
	}
	if shown == 0 {
		fmt.Println("  (前200条订单中无差异)")
	}
}

func parseRemark(rm string) (activityID, count int64) {
	count = 1
	parts := strings.Split(rm, "|")
	for _, p := range parts {
		if strings.HasPrefix(p, "lottery:activity:") {
			activityID = parseInt64(p[17:])
		} else if strings.HasPrefix(p, "activity:") {
			activityID = parseInt64(p[9:])
		} else if strings.HasPrefix(p, "count:") {
			n := parseInt64(p[6:])
			if n > 0 {
				count = n
			}
		}
	}
	return
}

func parseInt64(s string) int64 {
	var n int64
	for _, c := range s {
		if c >= '0' && c <= '9' {
			n = n*10 + int64(c-'0')
		} else {
			break
		}
	}
	return n
}