package main import ( "fmt" "git.hilo.cn/hilo-common/script/mysql" "github.com/tealeg/xlsx" ) type FruitSlotRaceChargeHistoryData struct { UserId uint64 Code string Area string Country string Bet uint64 // 5月份 Dollar float64 // 累充金额$ } func ats42(a interface{}) string { return fmt.Sprintf("%v", a) } var start, end = "2024-05-27 05:00:00", "2024-06-03 05:00:00" // 左闭右开,沙特时间 func main() { var charges []FruitSlotRaceChargeHistoryData if err := mysql.ProdReadOnlyDB.Raw("select id as user_id,code,SUM(dollar) as dollar,country FROM " + "(select u.id,u.`code`,SUM(price) / 100 as dollar,u.country FROM pay_order p, `user` u where u.id = p.user_id AND p.`status` = 2 AND `type` = 0 AND p.created_time >= \"" + start + "\" AND p.created_time < \"" + end + "\" group by user_id" + " UNION ALL " + "select u.id,u.code,SUM(dollar) / 100 as dollar,u.country FROM dealer_transfer_detail d, `user` u where u.id = d.receiver_id AND d.created_time >= \"" + start + "\" AND d.created_time < \"" + end + "\" group by receiver_id " + " UNION ALL " + "select u.id,u.code,SUM(dollar) / 100 as dollar,u.country FROM dealer_transfer_detail_pink d, `user` u where u.id = d.receiver_id AND d.created_time >= \"" + start + "\" AND d.created_time < \"" + end + "\" group by receiver_id" + ") t group by code order by dollar DESC").Find(&charges).Error; err != nil { panic(err) } type ResCountry struct { Name string Area int } var countries []ResCountry if err := mysql.ProdReadOnlyDB.Model(ResCountry{}).Find(&countries).Error; err != nil { panic(err) } countryMap := make(map[string]string) for _, c := range countries { countryMap[c.Name] = "阿语" if c.Area == 2 { countryMap[c.Name] = "非阿语" } } var userIds []uint64 for _, v := range charges { userIds = append(userIds, v.UserId) } bets := GetBets(userIds) for i, uc := range charges { charges[i].Area = countryMap[uc.Country] charges[i].Bet = bets[uc.UserId] } excelFileName := fmt.Sprintf("./5月27日-6月02日水果机slot赛车数据.xlsx") xlFile := xlsx.NewFile() sheet, err := xlFile.AddSheet("data") if err != nil { panic(err) } row := sheet.AddRow() c1, c2, c3, c4, c5 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell() c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = "用户ID", "区域", "国家", "5月水果机+Slot+赛车的总下注钻石额", "上周充值金额" for _, d := range charges { row := sheet.AddRow() c1, c2, c3, c4, c5 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell() c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = d.Code, d.Area, d.Country, ats42(d.Bet), ats42(d.Dollar) } _ = xlFile.Save(excelFileName) } func GetBets(userIds []uint64) map[uint64]uint64 { type Bet struct { UserId uint64 Num uint64 } var bets []Bet res := make(map[uint64]uint64) sql := "select user_id,SUM(num) num FROM diamond_account_detail_202405 where operate_type in (69,39,117) AND created_time >= \"" + start + "\" AND created_time < \"" + end + "\" and user_id in ? group by user_id" if err := mysql.ProdReadOnlyDB.Raw(sql, userIds).Find(&bets).Error; err != nil { panic(err) } for _, v := range bets { res[v.UserId] = v.Num } return res }