user_wealth_high.go

package main

import (
	"fmt"
	"git.hilo.cn/hilo-common/script/mysql"
	"github.com/spf13/cast"
	"github.com/tealeg/xlsx"
)

type UserWealthHigh struct {
	UserId    uint64
	Grade     int
	Code      string
	Country   string
	Area      string
	Charge    int64
	SvipLevel int64
}

type ResArea struct {
	Name string
	Area int
}

type UserSvip22 struct {
	UserId uint64
	Level  int64
}

func main() {
	var users []UserWealthHigh
	if err := mysql.ProdReadOnlyDB.Table("match_wealth_user_score").Joins("INNER JOIN user ON user.id = match_wealth_user_score.user_id").Where("grade >= 20").
		Select("user_id,grade,country,code").Find(&users).Error; err != nil {
		panic(err)
	}
	var resAreas []ResArea
	if err := mysql.ProdReadOnlyDB.Table("res_country").Find(&resAreas).Error; err != nil {
		panic(err)
	}
	areaMap := make(map[string]string)
	for _, v := range resAreas {
		if v.Area == 1 {
			areaMap[v.Name] = "阿语区"
		} else {
			areaMap[v.Name] = "非阿语"
		}
	}
	var userIds []uint64
	for _, v := range users {
		userIds = append(userIds, v.UserId)
	}
	var svips []UserSvip22
	if err := mysql.ProdReadOnlyDB.Table("user_svip").Where("user_id in ?", userIds).Find(&svips).Error; err != nil {
		panic(err)
	}
	sm := make(map[uint64]int64)
	for _, v := range svips {
		sm[v.UserId] = v.Level
	}
	for i, u := range users {
		users[i].Area = areaMap[u.Country]
		users[i].SvipLevel = sm[u.UserId]
		users[i].Charge = GetUserChargeMoneySum(u.UserId)
		if i >= 2 {
			//break
		}
	}
	excelFileName := fmt.Sprintf("./财富等级用户.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("wealth")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c1.Value, c2.Value, c3.Value, c4.Value, c5.Value, c6.Value = "id", "财富等级", "区域", "国家", "累计充值金额(美分)", "svip等级"
	for _, d := range users {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c1.Value, c2.Value, c3.Value, c4.Value, c5.Value, c6.Value =
			cast.ToString(d.Code), cast.ToString(d.Grade), cast.ToString(d.Area), cast.ToString(d.Country), cast.ToString(d.Charge), cast.ToString(d.SvipLevel)
	}
	_ = xlFile.Save(excelFileName)
}

// 获取用户累计充值
func GetUserChargeMoneySum(userId uint64) int64 {
	type R struct {
		Money int64
	}
	var money R
	var total int64
	if err := mysql.ProdReadOnlyDB.Table("pay_order AS p").
		Where("p.status = 2 AND p.`type` = 0"). // type=0 就是用户给自己充值,status=2成功
		Where("p.user_id = ?", userId).
		Select("SUM(p.price) as money").Scan(&money).Error; err != nil {
	}
	total += money.Money
	var money2 R
	if err := mysql.ProdReadOnlyDB.Table("dealer_transfer_detail AS t").
		Where("t.receiver_id = ?", userId).
		Select("SUM(t.dollar) as money").
		Scan(&money2).Error; err != nil {
	}
	total += money2.Money
	var money3 R
	if err := mysql.ProdReadOnlyDB.Table("dealer_transfer_detail_pink AS t").
		Where("t.receiver_id = ?", userId).
		Select("SUM(t.dollar) as money").
		Scan(&money3).Error; err != nil {
	}
	total += money3.Money
	return total
}