user_charge.go

package main

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

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{548601, 886081, 923871, 1055451, 1263881, 1388201, 1670021, 1791671, 1844281, 1893271, 2087771, 2132651, 2133891, 2382981, 3058361, 3235801, 3270831, 3318141, 3533911, 3699011, 4121831}
	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][2]int64)
	for _, uid := range uids {
		m1, d1, _ := GetUsersChargeMoneySum([]uint64{uid})
		m2, d2, _ := GetUserDealerTransferMoneySum([]uint64{uid})
		data[uid] = [2]int64{m1 + m2, d1 + d2}
	}
	for uid, num := range data {
		fmt.Printf("%d,%d,%d\n", uid, num[0], num[1])
	}
}

// 获取用户范围内充值金额
// 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 <= ?", "2023-02-08 18:45:00", "2023-02-15 18:00:00").
		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 <= ?", "2023-02-08 18:45:00", "2023-02-15 18:00:00").
		Order("d.user_id").
		Scan(&money).Error; err != nil {
		return 0, 0, err
	}
	return money.Price, money.Diamond, nil
}