package main import ( "fmt" "strings" "gorm.io/driver/mysql" "gorm.io/gorm" "bindbox-game/internal/pkg/util/remark" ) 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. 查所有订单 remark + source_type type RemarkRow struct { ID int64 Remark string SourceType int32 } var rows []RemarkRow db.Table("orders"). Joins("JOIN users ON users.id = orders.user_id"). Select("orders.id, orders.remark, orders.source_type"). Where(orderFilter, channelID). Scan(&rows) fmt.Printf("渠道 %d 总有效订单: %d\n\n", channelID, len(rows)) // 2. 三路分类统计 var case1, case2, case3, unmatched int actIDSet := make(map[int64]struct{}) issueIDSet := make(map[int64]struct{}) pkgIDSet := make(map[int64]struct{}) type parsed struct { orderID int64 caseType int activityID int64 issueID int64 pkgID int64 count int64 } var items []parsed for _, r := range rows { rmk := remark.Parse(r.Remark) p := parsed{orderID: r.ID, count: rmk.Count} if rmk.ActivityID > 0 { p.caseType = 1 p.activityID = rmk.ActivityID actIDSet[rmk.ActivityID] = struct{}{} case1++ } else if rmk.IssueID > 0 { p.caseType = 2 p.issueID = rmk.IssueID issueIDSet[rmk.IssueID] = struct{}{} case2++ } else if rmk.PkgID > 0 { p.caseType = 3 p.pkgID = rmk.PkgID pkgIDSet[rmk.PkgID] = struct{}{} case3++ } else { unmatched++ } items = append(items, p) } fmt.Println("=== 三路分类统计 ===") fmt.Printf(" Case1 (抽奖/直购, ActivityID>0): %d 笔\n", case1) fmt.Printf(" Case2 (对对碰, IssueID>0): %d 笔\n", case2) fmt.Printf(" Case3 (一番赏, PkgID>0): %d 笔\n", case3) fmt.Printf(" 未匹配: %d 笔\n", unmatched) fmt.Println() // 3. 查 activity_issues (Case2) issueActivityMap := make(map[int64]int64) if len(issueIDSet) > 0 { issueIDs := make([]int64, 0, len(issueIDSet)) for id := range issueIDSet { issueIDs = append(issueIDs, id) } type IssueRow struct { ID int64 ActivityID int64 } var issueRows []IssueRow db.Table("activity_issues"). Select("id, activity_id"). Where("id IN ?", issueIDs). Scan(&issueRows) for _, ir := range issueRows { issueActivityMap[ir.ID] = ir.ActivityID actIDSet[ir.ActivityID] = struct{}{} } fmt.Printf("activity_issues 查到: %d / %d\n", len(issueRows), len(issueIDs)) } // 4. 查 activities.price_draw (Case1+2) priceMap := make(map[int64]int64) if len(actIDSet) > 0 { actIDs := make([]int64, 0, len(actIDSet)) for id := range actIDSet { actIDs = append(actIDs, id) } type ActRow struct { ID int64 PriceDraw int64 } var actRows []ActRow db.Unscoped().Table("activities"). Select("id, price_draw"). Where("id IN ?", actIDs). Scan(&actRows) for _, a := range actRows { priceMap[a.ID] = a.PriceDraw } fmt.Printf("activities 查到: %d / %d\n", len(actRows), len(actIDs)) } // 5. 查 game_pass_packages.price (Case3) pkgPriceMap := make(map[int64]int64) if len(pkgIDSet) > 0 { pkgIDs := make([]int64, 0, len(pkgIDSet)) for id := range pkgIDSet { pkgIDs = append(pkgIDs, id) } type PkgRow struct { ID int64 Price int64 } var pkgRows []PkgRow db.Unscoped().Table("game_pass_packages"). Select("id, price"). Where("id IN ?", pkgIDs). Scan(&pkgRows) for _, p := range pkgRows { pkgPriceMap[p.ID] = p.Price } fmt.Printf("game_pass_packages 查到: %d / %d\n", len(pkgRows), len(pkgIDs)) } fmt.Println() // 6. 计算金额 var totalCase1, totalCase2, totalCase3 int64 var matchedCase1, matchedCase2, matchedCase3 int var unmatchedCase1, unmatchedCase2, unmatchedCase3 int for _, item := range items { switch item.caseType { case 1: if price, ok := priceMap[item.activityID]; ok { totalCase1 += price * item.count matchedCase1++ } else { unmatchedCase1++ } case 2: if actID, ok := issueActivityMap[item.issueID]; ok { if price, ok := priceMap[actID]; ok { totalCase2 += price * item.count matchedCase2++ } else { unmatchedCase2++ } } else { unmatchedCase2++ } case 3: if price, ok := pkgPriceMap[item.pkgID]; ok { totalCase3 += price * item.count matchedCase3++ } else { unmatchedCase3++ } } } total := totalCase1 + totalCase2 + totalCase3 fmt.Println("=== 金额统计 (price_draw/price × count) ===") fmt.Printf(" Case1 抽奖/直购: %d 分 = %.2f 元 (匹配 %d, 未匹配 %d)\n", totalCase1, float64(totalCase1)/100, matchedCase1, unmatchedCase1) fmt.Printf(" Case2 对对碰: %d 分 = %.2f 元 (匹配 %d, 未匹配 %d)\n", totalCase2, float64(totalCase2)/100, matchedCase2, unmatchedCase2) fmt.Printf(" Case3 一番赏: %d 分 = %.2f 元 (匹配 %d, 未匹配 %d)\n", totalCase3, float64(totalCase3)/100, matchedCase3, unmatchedCase3) fmt.Println(strings.Repeat("-", 60)) fmt.Printf(" 合计: %d 分 = %.2f 元\n", total, float64(total)/100) fmt.Printf(" 覆盖订单: %d / %d (%.1f%%)\n", matchedCase1+matchedCase2+matchedCase3, len(rows), float64(matchedCase1+matchedCase2+matchedCase3)/float64(len(rows))*100) fmt.Println() // 7. 对比 actual_amount type AmountResult struct { TotalCents int64 } var ar AmountResult db.Table("orders"). Joins("JOIN users ON users.id = orders.user_id"). Select("COALESCE(SUM(orders.actual_amount), 0) as total_cents"). Where(orderFilter, channelID). Scan(&ar) fmt.Println("=== 对比 ===") fmt.Printf(" SUM(actual_amount): %d 分 = %.2f 元\n", ar.TotalCents, float64(ar.TotalCents)/100) fmt.Printf(" price_draw/price × count: %d 分 = %.2f 元\n", total, float64(total)/100) diff := total - ar.TotalCents fmt.Printf(" 差额: %d 分 = %.2f 元\n", diff, float64(diff)/100) if diff > 0 { fmt.Printf(" 说明: 用户享受了 %.2f 元优惠\n", float64(diff)/100) } // 8. 打印未匹配订单示例 if unmatched > 0 { fmt.Printf("\n=== 未匹配 remark 示例 (共 %d 笔) ===\n", unmatched) shown := 0 for _, item := range items { if item.caseType == 0 && shown < 5 { for _, r := range rows { if r.ID == item.orderID { rmk := r.Remark if len(rmk) > 80 { rmk = rmk[:80] + "..." } fmt.Printf(" ID=%-6d type=%d remark=[%s]\n", r.ID, r.SourceType, rmk) shown++ break } } } } } }