girl_support.go

package main

import (
	"fmt"
	"git.hilo.cn/hilo-common/_const/enum/timezone_e"
	"git.hilo.cn/hilo-common/script/model"
	"git.hilo.cn/hilo-common/script/mysql"
	"github.com/tealeg/xlsx"
	"strconv"
	"time"
)

/**
 * 用户财富分数
 **/
type MatchWealthUserScore struct {
	UserId uint64
	Score  uint32
	Grade  int
}

//获取财富等级
func MGetWealthGrade(userIds []uint64) (map[uint64]MatchWealthUserScore, error) {
	var res = make(map[uint64]MatchWealthUserScore)
	var rows []MatchWealthUserScore
	if err := mysql.ProdReadOnlyDB.Model(&MatchWealthUserScore{}).Where("user_id in ?", userIds).Find(&rows).Error; err != nil {
		return res, err
	}
	for i, v := range rows {
		res[v.UserId] = rows[i]
	}
	return res, nil
}

// 用户上麦
type UserOnMic struct {
	Date      string
	UserId    uint64
	Seconds   uint32
	LastCalTs int64
	Tz        timezone_e.Timezone
}

// 批量获取用户上麦时长
// @return userId->seconds
func MGetUserOnMicSecondsRange(beginDate, endDate string, tz timezone_e.Timezone, userIds []uint64) (map[uint64]uint32, error) {
	var rows []UserOnMic
	res := make(map[uint64]uint32)
	if err := mysql.ProdReadOnlyDB.Model(UserOnMic{}).Where("`date` >= ? AND `date` <= ? AND tz = ? AND user_id in ?", beginDate, endDate, tz, userIds).Find(&rows).Error; err != nil {
		return res, err
	}
	for _, r := range rows {
		res[r.UserId] = r.Seconds
	}
	return res, nil
}

// 批量获取家族信息
func MGetGroupPowerInfoMapByUser(userIds []uint64) (map[uint64]string, error) {
	type GroupPowerWithUser struct {
		Name   string
		UserId uint64
	}
	var rows []GroupPowerWithUser
	res := make(map[uint64]string)
	if err := mysql.ProdReadOnlyDB.Table("group_power p").
		Joins("INNER JOIN group_power_user u ON p.id = u.group_power_id").
		Where("u.user_id in ?", userIds).
		Select("p.name,u.user_id").
		Find(&rows).Error; err != nil {
		return res, err
	}
	for i, v := range rows {
		res[v.UserId] = rows[i].Name
	}
	return res, nil
}

type UserGift struct {
	UserId  uint64
	Diamond uint32
	Send    uint32
}

// 批量获取收礼
func MGetUserGiftOperate(beginTime, endTime time.Time, userIds []uint64) (map[uint64]UserGift, error) {
	var rows []UserGift
	res := make(map[uint64]UserGift)
	if err := mysql.ProdReadOnlyDB.Table("gift_operate").Where("receive_user_id in ? AND created_time >= ? AND created_time < ?", userIds, beginTime, endTime).
		Select("receive_user_id as user_id,SUM(send_user_diamond) diamond,COUNT(distinct send_user_id) send").Group("receive_user_id").Find(&rows).Error; err != nil {
		return res, err
	}
	for i, v := range rows {
		res[v.UserId] = rows[i]
	}
	return res, nil
}

type GirlSupport struct {
	UserId      uint64
	CreatedTime time.Time
}

func main() {
	var girlSupport []GirlSupport
	var userIds []uint64
	// 4月13日到4月16日的报名用户的数据
	beginTime := time.Date(2023, time.April, 13, 0, 0, 0, 0, timezone_e.BeijingTimezoneLoc)
	endTime := time.Date(2023, time.April, 17, 0, 0, 0, 0, timezone_e.BeijingTimezoneLoc)
	if err := mysql.ProdReadOnlyDB.Table("girl_support_user").Where("created_time >= ? AND created_time < ?", beginTime, endTime).Find(&girlSupport).Error; err != nil {
		panic(err)
	}
	// 4月3日到4月9日的数据
	beginTime = time.Date(2023, time.April, 3, 0, 0, 0, 0, timezone_e.BeijingTimezoneLoc)
	endTime = time.Date(2023, time.April, 10, 0, 0, 0, 0, timezone_e.BeijingTimezoneLoc)
	gm := make(map[uint64]string)
	for _, v := range girlSupport {
		userIds = append(userIds, v.UserId)
		gm[v.UserId] = v.CreatedTime.Format("2006-01-02")
	}
	var users []model.User
	if err := mysql.ProdReadOnlyDB.Model(model.User{}).Where("id in ?", userIds).Find(&users).Error; err != nil {
		panic(err)
	}
	uM := make(map[uint64]model.User)
	for k, u := range users {
		uM[u.Id] = users[k]
	}
	grade, err := MGetWealthGrade(userIds)
	if err != nil {
		panic(err)
	}
	mic, err := MGetUserOnMicSecondsRange(beginTime.Format("2006-01-02"), endTime.AddDate(0, 0, -1).Format("2006-01-02"), timezone_e.TimezoneBeijing, userIds)
	if err != nil {
		panic(err)
	}
	gifts, err := MGetUserGiftOperate(beginTime, endTime, userIds)
	if err != nil {
		panic(err)
	}
	groupPower, err := MGetGroupPowerInfoMapByUser(userIds)
	if err != nil {
		panic(err)
	}
	type Data struct {
		ID         string
		Country    string
		Grade      string
		Name       string
		Mic        string
		SupportNum string
		Diamond    string
		Sign       string
	}
	var data []Data
	for _, v := range userIds {
		data = append(data, Data{
			ID:         uM[v].Code,
			Country:    uM[v].Country,
			Grade:      fmt.Sprintf("%d", grade[v].Grade),
			Name:       groupPower[v],
			Mic:        Decimal(float64(mic[v]) / 3600),
			SupportNum: fmt.Sprintf("%d", gifts[v].Send),
			Diamond:    fmt.Sprintf("%d", gifts[v].Diamond),
			Sign:       gm[v],
		})
	}
	excelFileName := fmt.Sprintf("./女孩扶持4月3-9日.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("charge")
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6, c7, c8 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = "用户ID", "国家", "财富等级", "家族名称", "上麦时长 h"
	c6.Value, c7.Value, c8.Value = "支持者人数", "收礼钻石数", "报名时间"
	for _, d := range data {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6, c7, c8 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = d.ID, d.Country, d.Grade, d.Name, d.Mic
		c6.Value, c7.Value, c8.Value = d.SupportNum, d.Diamond, d.Sign
	}
	_ = xlFile.Save(excelFileName)
}

// 保留两位小数
func Decimal(value float64) string {
	newValue, err := strconv.ParseFloat(fmt.Sprintf("%.2f", value), 64)
	if err != nil {
		return ""
	}
	return fmt.Sprintf("%v", newValue)
}