package main import ( "context" "fmt" "git.hilo.cn/hilo-common/mylogrus" "git.hilo.cn/hilo-common/script/mysql" "github.com/go-redis/redis/v8" "github.com/spf13/cast" "github.com/tealeg/xlsx" ) var RedisClient *redis.Client func init() { RedisClient = redis.NewClient(&redis.Options{ Addr: "172.28.16.47:6379", // redis cluster Password: "", DB: 0, // use default DB PoolSize: 20, MinIdleConns: 20, }) pong, err := RedisClient.Ping(context.Background()).Result() if err != nil { panic(err) } else { mylogrus.MyLog.Info("redis db0 connection success - ", pong) } } type GroupRoomVisit struct { GroupId string GroupCode string Country string Visit string Consume string Area string } func getGroupCode(groupId string) string { var code string if err := mysql.ProdReadOnlyDB.Table("group_info").Where("im_group_id = ?", groupId).Select("code").Scan(&code).Error; err != nil { panic(err) } return code } func getGroupCountry(groupId string) string { var code string if err := mysql.ProdReadOnlyDB.Table("group_info").Where("im_group_id = ?", groupId).Select("country").Scan(&code).Error; err != nil { panic(err) } return code } func getArea(country string) string { code := "非阿语区" var area int if err := mysql.ProdReadOnlyDB.Table("res_country").Where("name = ?", country).Select("area").Scan(&area).Error; err != nil { panic(err) } if area == 1 { code = "阿语区" } return code } func getConsume(groupId string) string { var result int64 if err := mysql.ProdReadOnlyDB.Table("room_day_consume").Where("group_id = ?", groupId).Select("SUM(diamond)").Scan(&result).Error; err != nil { //panic(err) } return cast.ToString(result) } func main() { var data []GroupRoomVisit zRes, err := RedisClient.ZRevRangeByScoreWithScores(context.Background(), "room_visit_count_zset", &redis.ZRangeBy{ Min: fmt.Sprintf("%d", 500), Max: "+inf", }).Result() if err != nil { panic(err) } for _, v := range zRes { d := GroupRoomVisit{ GroupId: cast.ToString(v.Member), GroupCode: "", Country: "", Visit: cast.ToString(v.Score), Consume: "", } code := getGroupCode(d.GroupId) country := getGroupCountry(d.GroupId) area := getArea(country) consume := getConsume(d.GroupId) d.GroupCode = code d.Country = country d.Area = area d.Consume = consume data = append(data, d) } excelFileName := fmt.Sprintf("./房间500热度.xlsx") xlFile := xlsx.NewFile() sheet, err := xlFile.AddSheet("room") 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 = "房间code", "国家", "访客热度", "房间奖杯", "区域" for _, d := range data { 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.GroupCode, d.Country, d.Visit, d.Consume, d.Area } _ = xlFile.Save(excelFileName) }