user_info.go

package main

import (
	"fmt"
	"git.hilo.cn/hilo-common/script/model"
	"git.hilo.cn/hilo-common/script/mysql"
	"github.com/tealeg/xlsx"
	"gorm.io/gorm"
)

type GroupPowerDataFix struct {
	GroupPowerId        uint64
	Exp                 int64
	Grade               int
	NotGrade            int
	GroupPowerOwnerCode string
	Country             string
	Area                string
	Charge              float64
}

var groupPowerIds = []uint64{24351, 19291, 12801, 14291, 19891, 23231, 23011, 25141, 12221, 19001, 24821, 23391, 9011, 13121, 22051, 25261, 4221, 10541, 24571, 3541, 18541, 15551, 15761, 17261, 21921, 4691, 24551, 19311, 2481, 23151, 6701, 11171, 8501, 14651, 25121, 8741, 1771, 23931, 21651, 23401, 14851, 24211, 2791, 19081, 4961, 14641, 5651, 26051, 4571, 25171, 4861, 25741, 20961, 9451, 20691, 25911, 2861, 14211, 24791, 19801, 19221, 8101, 3441, 921, 25661, 10041, 22831, 12601, 6811, 4501, 19031, 23961, 6721, 22491, 25431, 15261, 17721, 15941, 2411, 18611, 16611, 25071, 14361, 24701, 8281, 25371, 25081, 18691, 9421, 8131, 13421, 23421, 24801, 4241, 26581, 24921, 10421, 24961, 24391, 16591, 26001, 20241, 19161, 4801, 3351, 5801, 5581, 11181, 6211, 18991, 25411, 24341, 12671, 25811, 18891, 25181, 2251, 24601, 3001, 24991, 9391, 19911, 25481, 21481, 12101, 24141, 5871, 2441, 19371, 24191, 17371, 13151, 19261, 22961, 23911, 16231, 24851, 24811, 23211, 5511, 24251, 19551, 24201, 23751, 19831, 13541, 25471, 6131, 19461, 24901, 11401, 24831, 13881, 25041, 24061, 23351, 18821, 23981, 4751, 24611, 24001, 19571, 25051, 9741, 20191, 15521, 19901, 14101}
var Exps = []int64{13832068, 13403439, 13297842, 13294352, 13288374, 12674730, 11909217, 11511666, 11497642, 11462372, 11059096, 10661536, 10567574, 10480370, 10423501, 9946222, 9832519, 9532238, 9490327, 9194177, 9085226, 8891812, 8867650, 8715147, 8301794, 8203831, 8103674, 7246163, 7208835, 6715474, 6421177, 6212649, 6150580, 5934318, 5906318, 5837393, 5680884, 5560053, 5332538, 4019449, 3993994, 3826258, 2857571, 2621534, 2606725, 2499191, 2485685, 2444304, 2394223, 2362256, 2354537, 2340696, 2301961, 2277107, 2273430, 2262166, 2183237, 2181348, 2181102, 2173626, 2147152, 2118118, 2075115, 2060869, 1985529, 1948781, 1833511, 1832977, 1782934, 1780923, 1719446, 1667931, 1652100, 1619574, 1611277, 1590309, 1504380, 1497201, 1471447, 1465675, 1422977, 1417761, 1417492, 1409111, 1388859, 1352661, 1344402, 1338039, 1321998, 1253457, 1226655, 1212393, 1189305, 1114168, 1113801, 1096744, 1058926, 1058041, 1040023, 1028898, 1008527, 1007879, 1000988, 927809, 845653, 842572, 837581, 830823, 822889, 799687, 722092, 681294, 675063, 657535, 650551, 612469, 601754, 601384, 576578, 574690, 535670, 528766, 519261, 514781, 508661, 500272, 499544, 482028, 477736, 462447, 458211, 423348, 412066, 407211, 385878, 377427, 362139, 340853, 301622, 289717, 262355, 258988, 251724, 249314, 244429, 243399, 222771, 181332, 170380, 156802, 135650, 109984, 106263, 57110, 50076, 37711, 31048, 28970, 23765, 22617, 20407, 9401, 5600, 2430, 1606, 66, 50, 30}

func ats36(v interface{}) string {
	return fmt.Sprintf("%v", v)
}

func main() {
	var data []GroupPowerDataFix
	for i, groupPowerId := range groupPowerIds {
		ownerId, ownerCode, country, area := getGroupPowerOwner(groupPowerId)
		charge := float64(getUserChange(ownerId)) / 100
		areaStr := "阿语区"
		if area == 2 {
			areaStr = "非阿语区"
		}
		data = append(data, GroupPowerDataFix{
			GroupPowerId:        groupPowerId,
			Exp:                 Exps[i],
			Grade:               getShouldGrade(Exps[i]) + 1,
			NotGrade:            4,
			GroupPowerOwnerCode: ownerCode,
			Country:             country,
			Area:                areaStr,
			Charge:              charge,
		})
	}
	excelFileName := fmt.Sprintf("./家族bug.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("family")
	if err != nil {
		panic(err)
	}
	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, c6.Value, c7.Value, c8.Value = "家族id", "清零前经验", "应该等级", "错误等级", "家族长id", "国家", "区域", "累计充值"
	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, c6.Value, c7.Value, c8.Value =
			ats36(d.GroupPowerId), ats36(d.Exp), ats36(d.Grade), ats36(d.NotGrade), ats36(d.GroupPowerOwnerCode), ats36(d.Country), ats36(d.Area), ats36(d.Charge)
	}
	_ = xlFile.Save(excelFileName)
}

const (
	g1 = 2500000
	g2 = 15000000
	g3 = 60000000
)

func getShouldGrade(exp int64) int {
	var moreExp int64
	if exp > g3 {
		moreExp = exp - g3
	}
	if exp > g2 {
		moreExp = exp - g2
	}
	if exp > g1 {
		moreExp = exp - g1
	}
	if moreExp >= g3 {
		return 3
	}
	if moreExp >= g2 {
		return 2
	}
	if moreExp >= g1 {
		return 1
	}
	return 0
}

func getGroupPowerOwner(groupPowerId uint64) (uint64, string, string, int) {
	var userId uint64
	if err := mysql.ProdReadOnlyDB.Table("group_power_user").Select("user_id").Where("group_power_id = ?", groupPowerId).Where("role = 2").Scan(&userId).Error; err != nil {
		panic(err)
	}
	var user model.User
	if err := mysql.ProdReadOnlyDB.Table("user").Where("id = ?", userId).First(&user).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return 0, "没有家族长", "没有家族长", 0
		}
		panic(err)
	}
	var area int
	if err := mysql.ProdReadOnlyDB.Table("res_country").Select("area").Where("name = ?", user.Country).Scan(&area).Error; err != nil {
		panic(err)
	}
	return userId, user.Code, user.Country, area
}

func getUserChange(userId uint64) int64 {
	m1, err := getUserChargeMoneySumV2(userId)
	if err != nil {
		panic(err)
	}
	m2, _ := getUserDealerTransferMoneySumV2(userId)
	if err != nil {
		panic(err)
	}
	return m1 + m2
}

// 获取用户总充值金额
// 返回美分
func getUserChargeMoneySumV2(uid uint64) (int64, error) {
	type R struct {
		Money int64
	}
	var money R
	if err := mysql.ProdReadOnlyDB.Table("pay_order AS p").
		Where("p.status = 2 AND p.`type` = 0 AND platform >= 1 AND platform <= 6"). // type=0 就是用户给自己充值,status=2成功
		Where("p.user_id = ?", uid).Select("SUM(p.price) as money").Scan(&money).Error; err != nil {
		return 0, err
	}
	return money.Money, nil
}

// 获取代理总充值金额
// 返回美分
func getUserDealerTransferMoneySumV2(uid uint64) (int64, error) {
	type Money struct {
		Price int64
	}
	var money Money
	if err := mysql.ProdReadOnlyDB.Table("dealer_transfer_detail AS t").
		Where("t.receiver_id = ?", uid).Select("SUM(t.dollar) as price").
		Scan(&money).Error; err != nil {
		return 0, err
	}
	return money.Price, nil
}