user_charge.go

package main

import (
	"fmt"
	"git.hilo.cn/hilo-common/script/model"
	"git.hilo.cn/hilo-common/script/mysql"
)

type UserChargeData struct {
	UserId          uint64 // 用户ID
	Code            string // 用户ID
	Country         string // 国家
	ChargeMoneySum  int64  // 美分
	CostDiamondSum  int64  // 消费钻石数
	GiftDiamondSum  int64  // 礼物消费
	FruitDiamondSum int64  // 水果机消费
	BoxDiamondSum   int64  // 幸运盒子消费
	SlotsDiamondSum int64  // slots消费
}

var bt = "2023-03-02 00:00:00"
var et = "2023-03-03 00:00:00"

func main() {
	//uids := []uint64{889621, 904971, 1072931, 1083981, 1108951, 1147471, 1187511, 1365041, 1496521, 1540091, 1888121, 2037771, 2236221, 2396461, 2603721, 2613321, 2648851, 2778081, 3142631, 3219391, 3401561, 3533911, 3602511, 3791481, 3840571, 3845671, 3865481, 3873381, 3892321, 4009941}
	//var uids []uint64
	//if err := mysql.ProdReadOnlyDB.Raw("select id from user where origin_code <> `code`").Find(&uids).Error; err != nil {
	//	panic(err)
	//}
	uids := []uint64{3691801, 4361771, 4261141, 206061, 1450801, 4458001, 2627481, 201141, 3211931, 4232281, 313121, 2165761, 1115171, 3116641, 1416901, 3762791, 565631, 4471551, 4512281, 2668561, 3343361, 2167481, 1545481, 4306781, 2416031, 1225981, 1318961, 4303311, 3478821, 1201981, 2797481, 4301001, 1911701, 3438741, 4279071, 4513781, 2563341, 4443311, 3318141, 4464801, 4419451, 51741, 4478341, 4477521, 1550611, 1191181, 3212151, 4499871, 1238031, 1560831, 3006311, 4184611, 4070001, 3264331, 4285581, 4093971, 891231, 3119041, 11691, 1048161, 1313241, 2104211, 3974541, 2496921, 4435491, 4119371, 946801, 1100011, 4456151, 1157811, 4166041, 3113351, 1571731, 4462431, 1929581, 1977221, 2996001, 1073901, 2249401, 4217601, 3219631, 1719111, 3130071, 4483291, 118401, 962841, 1740481, 4364131, 2123201, 2415141, 2274831, 3574511, 974201, 3573581, 4519531, 1597231, 1567641, 1543581, 1368261, 24241, 390031, 1066961, 3933841, 4053771, 3866641, 2464891, 1027751, 2104851, 2054111, 885431, 2648851, 2088911, 4053821, 3603581, 1788151, 2382981, 907001, 4377321, 3801231, 4349001, 3601421, 4449381, 4132941, 4398991, 1185241, 4356371, 1939641, 2290651, 1876831, 4508611, 4201461, 464051, 3226601, 1010931, 3963501, 3114091, 644741, 2151781, 3409921, 2915281, 1321461, 3297011, 1332781, 4451111, 1013641, 3142631, 2119981, 2158431, 1769061, 3897281, 3333141, 2116841, 3506911, 3731641, 1862511, 3821461, 2953251, 4368081, 4156231, 1126511, 2424171, 2658841, 1365041, 1410661, 1263881, 1147471, 4121831, 1618351, 2695141, 1776781, 1987961, 4131151, 2416141, 3795521, 177031, 1867161, 2930101, 2648661, 1114691, 2264901, 548601, 4167721, 1496141, 2575021, 21591, 2307421, 4023301, 1922541, 3702761, 2087771, 3717501, 1013131, 2980541, 2729461, 4521701, 2502911, 4416531, 3602251, 4346661, 4298761, 2187871, 1092391, 2831841, 1522461, 240341, 4058151, 3551951, 3635581, 3530481, 2755011, 1110791, 1129691, 1427491, 3165421, 923871, 2649261, 1945101, 1797921, 1014201, 129141, 1796141, 980911, 1670441, 910451, 2342381, 1673561, 2113811, 1259521}
	//uids := []uint64{3691801, 4361771, 4261141}
	var users []model.User
	if err := mysql.ProdReadOnlyDB.Model(model.User{}).Where("id in ?", uids).Find(&users).Error; err != nil {
		panic(err)
	}
	uM := make(map[uint64]model.User)
	for k, u := range users {
		uM[u.Id] = users[k]
	}
	var data = make(map[uint64]*UserChargeData)
	for _, uid := range uids {
		m1, _, _ := GetUsersChargeMoneySum([]uint64{uid})
		m2, _, _ := GetUserDealerTransferMoneySum([]uint64{uid})
		data[uid] = &UserChargeData{UserId: uid, Code: uM[uid].Code, Country: uM[uid].Country, ChargeMoneySum: m1 + m2}
	}
	var err error
	for d, u := range data {
		data[d].CostDiamondSum, err = GetDiamondSumOperateTypeAll(u.UserId)
		if err != nil {
			panic(err)
		}
		data[d].GiftDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 1)
		if err != nil {
			panic(err)
		}
		data[d].FruitDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 39)
		if err != nil {
			panic(err)
		}
		data[d].BoxDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 29)
		if err != nil {
			panic(err)
		}
		data[d].SlotsDiamondSum, err = GetDiamondSumOperateType2(u.UserId, 69)
	}
	fmt.Printf("code,country,充值美分,消费钻石数,礼物消费,水果机消费,幸运盒子消费,slot消费\n")
	for _, d := range data {
		fmt.Printf("%v,%v,%v,%v,%v,%v,%v,%v\n", d.Code, d.Country, d.ChargeMoneySum, d.CostDiamondSum, d.GiftDiamondSum, d.FruitDiamondSum, d.BoxDiamondSum, d.SlotsDiamondSum)
	}
}

// 获取用户范围内充值金额
// param sinceTime: 过去n时间内
// 返回美分
func GetUsersChargeMoneySum(uids []uint64) (int64, int64, error) {
	if len(uids) <= 0 {
		return 0, 0, nil
	}
	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",
			uids, []int{int(4), int(42), int(55), int(68)}).
		Where("d.created_time >= ? AND d.created_time < ?", bt, et).
		Order("d.user_id").
		Select("SUM(p.price) as money,SUM(d.num) as diamond").Scan(&money).Error; err != nil {
		return 0, 0, err
	}
	return money.Money, money.Diamond, nil
}

// 获取代理范围内总充值金额
// param sinceTime: 过去n时间内
// 返回美分
func GetUserDealerTransferMoneySum(uids []uint64) (int64, int64, error) {
	if len(uids) <= 0 {
		return 0, 0, nil
	}
	type R struct {
		Price   int64
		Diamond int64
	}
	var money R
	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",
			uids, 26).Select("SUM(t.dollar) as price,SUM(d.num) as diamond").
		Where("d.created_time >= ? AND d.created_time < ?", bt, et).
		Order("d.user_id").
		Scan(&money).Error; err != nil {
		return 0, 0, err
	}
	return money.Price, money.Diamond, nil
}

func GetDiamondSumOperateTypeAll(uid uint64) (int64, error) {
	var num int64
	if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail").
		Where("user_id = ? AND add_reduce = ?", uid, 2).
		Where("created_time >= ? AND created_time < ?", bt, et).
		Group("user_id").
		Select("SUM(num) as num").
		Scan(&num).Error; err != nil {
		return num, err
	}
	return num, nil
}

// operType: 1:送礼 29:幸运盒子购买 39:水果机投注 69:slot消费
func GetDiamondSumOperateType2(uid uint64, operType int) (int64, error) {
	var num int64
	if err := mysql.ProdReadOnlyDB.Table("diamond_account_detail").
		Where("user_id = ? AND operate_type = ?", uid, operType).
		Where("created_time >= ? AND created_time < ?", bt, et).
		Group("user_id").
		Select("SUM(num) as num").
		Scan(&num).Error; err != nil {
		return num, err
	}
	return num, nil
}