package main import ( "fmt" "git.hilo.cn/hilo-common/script/model" "git.hilo.cn/hilo-common/script/mysql" ) type UserChargeData struct { UserId uint64 // 用户ID Code string // 用户ID Country string // 国家 ChargeMoneySum int64 // 美分 CostDiamondSum int64 // 消费钻石数 GiftDiamondSum int64 // 礼物消费 FruitDiamondSum int64 // 水果机消费 BoxDiamondSum int64 // 幸运盒子消费 SlotsDiamondSum int64 // slots消费 } var bt = "2023-03-02 00:00:00" var et = "2023-03-03 00:00:00" func main() { //uids := []uint64{889621, 904971, 1072931, 1083981, 1108951, 1147471, 1187511, 1365041, 1496521, 1540091, 1888121, 2037771, 2236221, 2396461, 2603721, 2613321, 2648851, 2778081, 3142631, 3219391, 3401561, 3533911, 3602511, 3791481, 3840571, 3845671, 3865481, 3873381, 3892321, 4009941} //var uids []uint64 //if err := mysql.ProdReadOnlyDB.Raw("select id from user where origin_code <> `code`").Find(&uids).Error; err != nil { // panic(err) //} uids := []uint64{3691801, 4361771, 4261141, 206061, 1450801, 4458001, 2627481, 201141, 3211931, 4232281, 313121, 2165761, 1115171, 3116641, 1416901, 3762791, 565631, 4471551, 4512281, 2668561, 3343361, 2167481, 1545481, 4306781, 2416031, 1225981, 1318961, 4303311, 3478821, 1201981, 2797481, 4301001, 1911701, 3438741, 4279071, 4513781, 2563341, 4443311, 3318141, 4464801, 4419451, 51741, 4478341, 4477521, 1550611, 1191181, 3212151, 4499871, 1238031, 1560831, 3006311, 4184611, 4070001, 3264331, 4285581, 4093971, 891231, 3119041, 11691, 1048161, 1313241, 2104211, 3974541, 2496921, 4435491, 4119371, 946801, 1100011, 4456151, 1157811, 4166041, 3113351, 1571731, 4462431, 1929581, 1977221, 2996001, 1073901, 2249401, 4217601, 3219631, 1719111, 3130071, 4483291, 118401, 962841, 1740481, 4364131, 2123201, 2415141, 2274831, 3574511, 974201, 3573581, 4519531, 1597231, 1567641, 1543581, 1368261, 24241, 390031, 1066961, 3933841, 4053771, 3866641, 2464891, 1027751, 2104851, 2054111, 885431, 2648851, 2088911, 4053821, 3603581, 1788151, 2382981, 907001, 4377321, 3801231, 4349001, 3601421, 4449381, 4132941, 4398991, 1185241, 4356371, 1939641, 2290651, 1876831, 4508611, 4201461, 464051, 3226601, 1010931, 3963501, 3114091, 644741, 2151781, 3409921, 2915281, 1321461, 3297011, 1332781, 4451111, 1013641, 3142631, 2119981, 2158431, 1769061, 3897281, 3333141, 2116841, 3506911, 3731641, 1862511, 3821461, 2953251, 4368081, 4156231, 1126511, 2424171, 2658841, 1365041, 1410661, 1263881, 1147471, 4121831, 1618351, 2695141, 1776781, 1987961, 4131151, 2416141, 3795521, 177031, 1867161, 2930101, 2648661, 1114691, 2264901, 548601, 4167721, 1496141, 2575021, 21591, 2307421, 4023301, 1922541, 3702761, 2087771, 3717501, 1013131, 2980541, 2729461, 4521701, 2502911, 4416531, 3602251, 4346661, 4298761, 2187871, 1092391, 2831841, 1522461, 240341, 4058151, 3551951, 3635581, 3530481, 2755011, 1110791, 1129691, 1427491, 3165421, 923871, 2649261, 1945101, 1797921, 1014201, 129141, 1796141, 980911, 1670441, 910451, 2342381, 1673561, 2113811, 1259521} //uids := []uint64{3691801, 4361771, 4261141} var users []model.User if err := mysql.ProdReadOnlyDB.Model(model.User{}).Where("id in ?", uids).Find(&users).Error; err != nil { panic(err) } uM := make(map[uint64]model.User) for k, u := range users { uM[u.Id] = users[k] } var data = make(map[uint64]*UserChargeData) for _, uid := range uids { m1, _, _ := GetUsersChargeMoneySum([]uint64{uid}) m2, _, _ := GetUserDealerTransferMoneySum([]uint64{uid}) data[uid] = &UserChargeData{UserId: uid, Code: uM[uid].Code, Country: uM[uid].Country, ChargeMoneySum: m1 + m2} } var err error for d, u := range data { data[d].CostDiamondSum, err = GetDiamondSumOperateTypeAll(u.UserId) if err != nil { panic(err) } data[d].GiftDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 1) if err != nil { panic(err) } data[d].FruitDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 39) if err != nil { panic(err) } data[d].BoxDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 29) if err != nil { panic(err) } data[d].SlotsDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 69) } fmt.Printf("code,country,充值美分,消费钻石数,礼物消费,水果机消费,幸运盒子消费,slot消费\n") for _, d := range data { fmt.Printf("%v,%v,%v,%v,%v,%v,%v,%v\n", d.Code, d.Country, d.ChargeMoneySum, d.CostDiamondSum, d.GiftDiamondSum, d.FruitDiamondSum, d.BoxDiamondSum, d.SlotsDiamondSum) } } // 获取用户范围内充值金额 // param sinceTime: 过去n时间内 // 返回美分 func GetUsersChargeMoneySum(uids []uint64) (int64, int64, error) { if len(uids) <= 0 { return 0, 0, nil } type R struct { Money int64 Diamond int64 } var money R if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail AS d"). Joins("INNER JOIN pay_order AS p ON d.origin_id = p.id"). Where("d.user_id in (?) AND operate_type in (?) AND add_reduce = 1", uids, []int{int(4), int(42), int(55), int(68)}). Where("d.created_time >= ? AND d.created_time < ?", bt, et). Order("d.user_id"). Select("SUM(p.price) as money,SUM(d.num) as diamond").Scan(&money).Error; err != nil { return 0, 0, err } return money.Money, money.Diamond, nil } // 获取代理范围内总充值金额 // param sinceTime: 过去n时间内 // 返回美分 func GetUserDealerTransferMoneySum(uids []uint64) (int64, int64, error) { if len(uids) <= 0 { return 0, 0, nil } type R struct { Price int64 Diamond int64 } var money R if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail AS d"). Joins("INNER JOIN dealer_transfer_detail AS t ON d.origin_id = t.id"). Where("d.user_id in (?) AND operate_type = ? AND add_reduce = 1", uids, 26).Select("SUM(t.dollar) as price,SUM(d.num) as diamond"). Where("d.created_time >= ? AND d.created_time < ?", bt, et). Order("d.user_id"). Scan(&money).Error; err != nil { return 0, 0, err } return money.Price, money.Diamond, nil } func GetDiamondSumOperateTypeAll(uid uint64) (int64, error) { var num int64 if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail"). Where("user_id = ? AND add_reduce = ?", uid, 2). Where("created_time >= ? AND created_time < ?", bt, et). Group("user_id"). Select("SUM(num) as num"). Scan(&num).Error; err != nil { return num, err } return num, nil } // operType: 1:送礼 29:幸运盒子购买 39:水果机投注 69:slot消费 func GetDiamondSumOperateType2(uid uint64, operType int) (int64, error) { var num int64 if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail"). Where("user_id = ? AND operate_type = ?", uid, operType). Where("created_time >= ? AND created_time < ?", bt, et). Group("user_id"). Select("SUM(num) as num"). Scan(&num).Error; err != nil { return num, err } return num, nil }