slot_user_charge.go

package main

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

type SlotChargeData struct {
	Period string
	UserId uint64
	Code   string
	Stake  uint64
	Award  uint64
	Charge int64
}

var bt22 = "2023-03-28 00:00:00"
var et22 = "2023-03-29 00:00:00"

func ct(c string, arr []string) bool {
	for _, v := range arr {
		if c == v {
			return true
		}
	}
	return false
}

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

func main() {
	var countries []string
	if err := mysql.ProdReadOnlyDB.Table("res_country").Where("area = 1").Select("name").Find(&countries).Error; err != nil {
		panic(err)
	}
	var rows []SlotChargeData
	if err := mysql.ProdReadOnlyDB.Table("act_slot_day_rank_area").Where("`period` = '2023-03-28' AND `area` = 1").Select("period,user_id,stake,award").Find(&rows).Error; err != nil {
		panic(err)
	}
	var data []SlotChargeData
	for _, v := range rows {
		u, err := GetUserById(v.UserId)
		if err != nil {
			panic(err)
		}
		if !ct(u.Country, countries) {
			continue
		}
		charge, _, err := GetChargeMoneyDiamond22(v.UserId)
		if err != nil {
			panic(err)
		}
		data = append(data, SlotChargeData{
			Period: v.Period,
			UserId: v.UserId,
			Code:   u.Code,
			Stake:  v.Stake,
			Award:  v.Award,
			Charge: charge,
		})
	}
	excelFileName := fmt.Sprintf("./slot%s.xlsx", "20230328")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("slot")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4, c5 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = "period", "code", "投注金额", "中奖金额", "当日充值金额(美分)"
	for _, d := range data {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = d.Period, d.Code, ats22(d.Stake), ats22(d.Award), ats22(d.Charge)
	}
	_ = xlFile.Save(excelFileName)
}

func GetUserById(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 GetChargeMoneyDiamond22(uid uint64) (int64, int64, error) {
	type R struct {
		Money   int64
		Diamond int64
	}
	var money R
	if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail AS d").
		Joins("INNER JOIN pay_order AS p ON d.origin_id = p.id").
		Where("d.user_id in (?) AND operate_type in (?) AND add_reduce = 1",
			uid, []int{int(4), int(42), int(55), int(68)}).
		Where("d.created_time >= ? AND d.created_time < ?", bt22, et22).
		Order("d.user_id").
		Select("SUM(p.price) as money,SUM(d.num) as diamond").Scan(&money).Error; err != nil {
		return 0, 0, err
	}
	type R2 struct {
		Price   int64
		Diamond int64
	}
	var money2 R2
	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("d.user_id in (?) AND operate_type = ? AND add_reduce = 1",
			uid, 26).Select("SUM(t.dollar) as price,SUM(d.num) as diamond").
		Where("d.created_time >= ? AND d.created_time < ?", bt22, et22).
		Order("d.user_id").
		Scan(&money2).Error; err != nil {
		return 0, 0, err
	}
	return money.Money + money2.Price, money.Diamond + money2.Diamond, nil
}