fruit_machine_charge.go

package main

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

type FruitDayChargeData struct {
	Date                 string   // 日期
	FruitUserNum         int      // 水果机参与人数
	Recycle              float64  // 总回收 M
	DayChargeCnt         int      // 当日充值用户数
	DayChargeUserIds     []uint64 // 当日充值用户id
	DayChargeMoney       int64    // 当日充值金额
	ChargeRecycle        float64  // 充值用户回收 M
	Top100DayChargeCnt   int      // 投注榜TOP100的充值用户数
	Top100DayChargeMoney int64    // 投注榜TOP100的当日充值金额
	Top100ChargeRecycle  float64  // 投注榜TOP100的回收
}

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

type UserAndMoney struct {
	UserId uint64
	Money  int64 // 美分
}

// 1月的所有充值用户明细
func main() {
	startTime := time.Date(2023, time.January, 1, 0, 0, 0, 0, time.Local)
	endTime := time.Date(2023, time.March, 1, 0, 0, 0, 0, time.Local)
	var data []FruitDayChargeData
	for startTime.Before(endTime) {
		var userIds []uint64
		var top100UserIds []uint64
		date := startTime.Format("2006-01-02")
		if err := mysql.ProdReadOnlyDB.Table("fruit_machine_stake").Where("`date` = ?", date).Group("user_id").Select("user_id").Scan(&userIds).Error; err != nil {
			panic(err)
		}
		if err := mysql.ProdReadOnlyDB.Table("fruit_machine_stake").Where("`date` = ?", date).Group("user_id").
			Order("SUM(stake) DESC").Limit(100).
			Select("user_id").Scan(&top100UserIds).Error; err != nil {
			panic(err)
		}
		top100UserIdMap := make(map[uint64]struct{})
		for _, userId := range top100UserIds {
			top100UserIdMap[userId] = struct{}{}
		}
		um1, err := GetDateChargeUserAndMoney(date, userIds)
		if err != nil {
			panic(err)
		}
		um2, err := GetUserDealerTransferUserAndMoney(date, userIds)
		if err != nil {
			panic(err)
		}
		userChargeNum := make(map[uint64]struct{})
		top100UserChargeNum := make(map[uint64]struct{})
		userChargeMoney := int64(0)
		top100UserChargeMoney := int64(0)
		for _, um := range um1 {
			userChargeNum[um.UserId] = struct{}{}
			userChargeMoney += um.Money
			if _, ok := top100UserIdMap[um.UserId]; ok {
				top100UserChargeNum[um.UserId] = struct{}{}
				top100UserChargeMoney += um.Money
			}
		}
		for _, um := range um2 {
			userChargeNum[um.UserId] = struct{}{}
			userChargeMoney += um.Money
			if _, ok := top100UserIdMap[um.UserId]; ok {
				top100UserChargeNum[um.UserId] = struct{}{}
				top100UserChargeMoney += um.Money
			}
		}
		var chargeUserIds []uint64
		for userId := range userChargeNum {
			chargeUserIds = append(chargeUserIds, userId)
		}
		var top100ChargeUserIds []uint64
		for userId := range top100UserChargeNum {
			top100ChargeUserIds = append(top100ChargeUserIds, userId)
		}
		userNum, err := GetFruitUserNum(date)
		if err != nil {
			panic(err)
		}
		fruitRecycle, err := GetFruitRecycle(date)
		if err != nil {
			panic(err)
		}
		fruitChargeRecycle, err := GetFruitChargeRecycle(date, chargeUserIds)
		if err != nil {
			panic(err)
		}
		top100FruitChargeRecycle, err := GetFruitChargeRecycle(date, top100ChargeUserIds)
		if err != nil {
			panic(err)
		}
		data = append(data, FruitDayChargeData{
			Date:                 date,
			FruitUserNum:         userNum,
			Recycle:              float64(fruitRecycle) * 0.05 / 1000000, // 1M
			DayChargeCnt:         len(userChargeNum),
			DayChargeUserIds:     userIds,
			DayChargeMoney:       userChargeMoney,
			ChargeRecycle:        float64(fruitChargeRecycle) * 0.05 / 1000000, // 1M
			Top100DayChargeCnt:   len(top100UserChargeNum),
			Top100DayChargeMoney: top100UserChargeMoney,
			Top100ChargeRecycle:  float64(top100FruitChargeRecycle) * 0.05 / 1000000, // 1M
		})
		startTime = startTime.AddDate(0, 0, 1)
	}

	excelFileName := fmt.Sprintf("./水果机充值数据%s.xlsx", time.Now().Format("20060102"))
	xlFile := xlsx.NewFile()
	sheet, _ := xlFile.AddSheet("charge")
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6, c7, c8, c9 := row.AddCell(), 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, c9.Value = "日期", "参与人数", "总回收 M", "当日充值用户数", "当日充值金额(美分)", "充值用户回收 M",
		"投注榜TOP100的充值用户数", "投注榜TOP100的当日充值金额(美分)", "投注榜TOP100的回收"
	for _, d := range data {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6, c7, c8, c9 := row.AddCell(), 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 = d.Date, ats2(d.FruitUserNum), ats2(d.Recycle), ats2(d.DayChargeCnt), ats2(d.DayChargeMoney), ats2(d.ChargeRecycle)
		c7.Value, c8.Value, c9.Value = ats2(d.Top100DayChargeCnt), ats2(d.Top100DayChargeMoney), ats2(d.Top100ChargeRecycle)
	}
	_ = xlFile.Save(excelFileName)
}

// 获取充值用户以及金额
// return 充值userIds,充值金额
func GetDateChargeUserAndMoney(date string, userIds []uint64) ([]UserAndMoney, error) {
	var money []UserAndMoney
	if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail AS d").
		Joins("INNER JOIN pay_order AS p ON d.origin_id = p.id").
		Where("DATE(d.created_time) >= ? AND DATE(d.created_time) <= ? AND operate_type in (?) AND add_reduce = 1",
			date, date, []int{int(4), int(42), int(55), int(68)}).
		Where("d.user_id in ?", userIds).
		Group("d.user_id").
		Select("d.user_id,SUM(p.price) as money").Scan(&money).Error; err != nil {
		return nil, err
	}
	return money, nil
}

// 获取代理充值用户以及金额
// return 充值userIds,充值金额
func GetUserDealerTransferUserAndMoney(date string, userIds []uint64) ([]UserAndMoney, error) {
	var money []UserAndMoney
	if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail AS d").
		Joins("INNER JOIN dealer_transfer_detail AS t ON d.origin_id = t.id").
		Where("DATE(d.created_time) >= ? AND DATE(d.created_time) <= ? AND operate_type = ? AND add_reduce = 1",
			date, date, 26).
		Where("d.user_id in ?", userIds).
		Select("d.user_id,SUM(t.dollar) as money").
		Group("d.user_id").
		Scan(&money).Error; err != nil {
		return nil, err
	}
	return money, nil
}

// 获取参与人数
func GetFruitUserNum(date string) (int, error) {
	var stake int
	if err := mysql.ProdReadOnlyDB.Table("fruit_machine_stake").Where("`date`= ?", date).Select("count(DISTINCT user_id) as total").Scan(&stake).Error; err != nil {
		return stake, err
	}
	return stake, nil
}

// 总回收
func GetFruitRecycle(date string) (int, error) {
	var total int
	if err := mysql.ProdReadOnlyDB.Table("fruit_machine").Where("`date`= ?", date).Select("SUM(stake) as total").Scan(&total).Error; err != nil {
		return total, err
	}
	return total, nil
}

// 充值用户回收
func GetFruitChargeRecycle(date string, userIds []uint64) (int, error) {
	var total int
	if err := mysql.ProdReadOnlyDB.Table("fruit_machine_stake").Where("`date`= ?", date).Where("user_id in ?", userIds).
		Select("SUM(stake) as total").Scan(&total).Error; err != nil {
		return total, err
	}
	return total, nil
}