group_room_visit500.go

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)
}