charge_max.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"
	"time"
)

type ChargeMaxData struct {
	Dollar         float64 // 最高单笔充值金额$
	Area           string  // 区域
	Code           string  // 用户ID
	MoneyMaxDollar float64 // 最高当月充值金额$
	LastLoginTime  string  // 最后登录时间
	LastChargeTime string  // 最近一次充值时间
	GroupPowerCode string  // 家族长ID
	CpCode         string  // CP ID
}

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

func main() {
	type ChargeMaxUid struct {
		UserId uint64
		Dollar int64
	}
	var chargeUids []ChargeMaxUid
	if err := mysql.ProdReadOnlyDB.Raw("select user_id,MAX(dollar) as dollar " +
		"FROM (SELECT user_id,MAX(price) as dollar FROM `pay_order` WHERE `status` = '2' AND `type` = '0' AND created_time >= \"2022-07-01\" GROUP BY user_id " +
		"union ALL SELECT receiver_id as user_id,MAX(dollar) as dollar FROM dealer_transfer_detail WHERE created_time >= \"2022-07-01\" GROUP BY receiver_id) t GROUP BY user_id").Find(&chargeUids).Error; err != nil {
		panic(err)
	}
	var data []ChargeMaxData
	for _, uc := range chargeUids {
		if uc.UserId <= 0 {
			continue
		}
		u, err := GetUserById25(uc.UserId)
		if err != nil {
			panic(err)
		}
		data = append(data, ChargeMaxData{
			Dollar:         float64(uc.Dollar) / 100,
			Code:           u.Code,
			Area:           GetAreaByCode(u.Code),
			MoneyMaxDollar: GetUserMonthMaxCharge(uc.UserId),
			LastLoginTime:  GetLastLoginTime(uc.UserId),
			LastChargeTime: GetLastChargeTime(uc.UserId),
			GroupPowerCode: GetGroupPowerCode(uc.UserId),
			CpCode:         GetCpCode(uc.UserId),
		})
		//break
	}

	excelFileName := fmt.Sprintf("./202207充值.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("slot")
	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", "CP 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 = ats25(d.Dollar), ats25(d.Area), ats25(d.Code), ats25(d.MoneyMaxDollar), ats25(d.LastLoginTime), ats25(d.LastChargeTime), ats25(d.GroupPowerCode), ats25(d.CpCode)
	}
	_ = xlFile.Save(excelFileName)
}

func GetUserById25(id uint64) (model.User, error) {
	var u model.User
	if err := mysql.ProdReadOnlyDB.Model(model.User{}).Where("id = ?", id).First(&u).Error; err != nil {
		//panic(err)
	}
	return u, nil
}

func GetUserMonthMaxCharge(uid uint64) float64 {
	var dollar int64
	if err := mysql.ProdReadOnlyDB.Raw("select MAX(dollar) FROM\n(\nselect d,SUM(dollar) as dollar FROM (\nSELECT\n\tuser_id,\n\tDATE_FORMAT(created_time,\"%Y-%m\") as d,\n\tSUM(price) as dollar\nFROM\n\t`pay_order`\nWHERE\n\t`status` = '2'\nAND `type` = '0'\nAND created_time >= \"2022-07-01\"\nAND user_id = ?\nGROUP BY user_id,d \nUNION ALL \nSELECT\n\treceiver_id user_id,\n\tDATE_FORMAT(created_time, \"%Y-%m\") AS d,\n\tSUM(dollar) AS dollar\nFROM\n\t`dealer_transfer_detail`\nWHERE\n\tcreated_time >= \"2022-07-01\"\nAND receiver_id = ?\nGROUP BY\n\treceiver_id,\n\td\n) t GROUP BY d\n) t2", uid, uid).Scan(&dollar).Error; err != nil {
		panic(err)
	}
	return float64(dollar) / 100
}

func GetGroupPowerCode(uid uint64) string {
	var groupPowerId uint64
	if err := mysql.ProdReadOnlyDB.Table("group_power_user").Where("user_id = ?", uid).Select("group_power_id").Scan(&groupPowerId).Error; err != nil {
		panic(err)
	}
	if groupPowerId <= 0 {
		return "0"
	}
	var ownerId uint64
	if err := mysql.ProdReadOnlyDB.Table("group_power_user").Where("group_power_id = ? AND role = 2", groupPowerId).Select("user_id").Scan(&ownerId).Error; err != nil {
		panic(err)
	}
	user, err := GetUserById25(ownerId)
	if err != nil {
		panic(err)
	}
	return user.Code
}

func GetCpCode(uid uint64) string {
	type Cp struct {
		Id      uint64
		UserId1 uint64
		UserId2 uint64
	}
	var cp Cp
	if err := mysql.ProdReadOnlyDB.Table("cp").Where("user_id1 = ? OR user_id2 = ?", uid, uid).Order("score DESC").Limit(1).First(&cp).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return "0"
		}
		panic(err)
	}
	if cp.Id <= 0 {
		return "0"
	}
	cpUid := cp.UserId2
	if cpUid == uid {
		cpUid = cp.UserId1
	}
	user, err := GetUserById25(cpUid)
	if err != nil {
		panic(err)
	}
	return user.Code
}

func GetLastLoginTime(uid uint64) string {
	var t time.Time
	if err := mysql.ProdReadOnlyDB.Table("user_request_last").Where("user_id = ?", uid).Select("time_last").Scan(&t).Error; err != nil {
		panic(err)
	}
	return t.Format("2006-01-02")
}

func GetLastChargeTime(uid uint64) string {
	var t time.Time
	if err := mysql.ProdReadOnlyDB.Raw("SELECT\n\tmax(created_time)\nFROM\n\t(\n\t\tSELECT\n\t\t\tMAX(created_time) created_time\n\t\tFROM\n\t\t\t`pay_order`\n\t\tWHERE\n\t\t\t`status` = '2'\n\t\tAND `type` = '0'\n\t\tAND created_time >= \"2022-07-01\"\n\t\tAND user_id = ?\n\t\tUNION ALL\n\t\t\tSELECT\n\t\t\t\tMAX(created_time) created_time\n\t\t\tFROM\n\t\t\t\t`dealer_transfer_detail`\n\t\t\tWHERE\n\t\t\t\tcreated_time >= \"2022-07-01\"\n\t\t\tAND receiver_id = ?\n\t) t", uid, uid).Scan(&t).Error; err != nil {
		panic(err)
	}
	return t.Format("2006-01-02")
}

func GetAreaByCode(code string) string {
	sql := "SELECT area FROM res_country c,user u WHERE u.country = c.name AND u.code = ?"
	var area int
	if err := mysql.ProdReadOnlyDB.Raw(sql, code).Scan(&area).Error; err != nil {
		panic(err)
	}
	if area == 1 {
		return "阿语区"
	}
	return "非阿语区"
}