svip_user_charge.go

package main

import (
	"encoding/json"
	"fmt"
	"git.hilo.cn/hilo-common/script/mysql"
	"github.com/tealeg/xlsx"
	"io/ioutil"
	"net/http"
	"time"
)

type AutoGenerated34 struct {
	Code    int    `json:"code"`
	Message string `json:"message"`
	Data    struct {
		Total int                   `json:"total"`
		Data  []AutoGenerated34Data `json:"data"`
	} `json:"data"`
}

type AutoGenerated34Data struct {
	UserID            uint64 `json:"userId"`
	Date              string `json:"date"`
	Country           string `json:"country"`
	UserCode          string `json:"userCode"`
	UserName          string `json:"userName"`
	ChargeMoney       string `json:"chargeMoney"`
	HaveFirst         int    `json:"haveFirst"`
	RegisterAt        string `json:"registerAt"`
	SvipLevel         int    `json:"svipLevel"`
	MaxDollar         int    `json:"maxDollar"`         // 历史最高单笔
	MaxDollarDuration int    `json:"maxDollarDuration"` // 时段最高单笔
	Phone             string `json:"phone"`
	Charm             int    `json:"charm"`
	LastRequestTime   string `json:"lastRequestTime"`
}

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

func main() {
	//url := "https://apiv2.faceline.live/v1/stats/charge/user?lang=zh-cn&diamondType=2&beginDate=2023-10-01&endDate=2023-10-31&pageIndex=1&pageSize=10000&country=All&code=&area=1&timezone=1"
	//url := "https://apiv2.faceline.live/v1/stats/charge/user?lang=zh-cn&diamondType=1&beginDate=2022-06-01&endDate=2023-12-13&pageIndex=1&pageSize=4805&country=All&code=&area=1&timezone=0"
	var data []AutoGenerated34Data
	pageIndex := 1
	pageSize := 1000
	for {
		url := fmt.Sprintf("https://apiv2.faceline.live/v1/stats/charge/user?lang=zh-cn&diamondType=1&beginDate=2020-01-01&endDate=2024-07-05&pageIndex=%d&pageSize=%d&country=All&code=&area=2&timezone=0",
			pageIndex, pageSize)
		method := "GET"

		client := &http.Client{}
		req, err := http.NewRequest(method, url, nil)

		if err != nil {
			fmt.Println(err)
			return
		}
		req.Header.Add("nonce", "hilo")
		req.Header.Add("token", "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VySWQiOjI1MSwiRXh0ZXJuYWxJZCI6IiIsImV4cCI6MTcyMjczNzEzMH0.LZDmjeRkUiYIQA7hVnbb6_xFQtyGf9yev-y6NQqVhRY")

		res, err := client.Do(req)
		if err != nil {
			fmt.Println(err)
			return
		}

		body, err := ioutil.ReadAll(res.Body)
		if err != nil {
			panic(err)
		}
		println(string(body))
		var response = new(AutoGenerated34)
		err = json.Unmarshal(body, &response)
		if err != nil {
			panic(err)
		}
		if len(response.Data.Data) <= 0 {
			break
		}
		data = append(data, response.Data.Data...)
		pageIndex++
	}
	var userIds []uint64
	for _, v := range data {
		userIds = append(userIds, v.UserID)
	}
	phones := GetPhones(userIds)
	charms := GetUserCharmLevel(userIds)
	requsets := GetRequestLast(userIds)
	for i, v := range data {
		data[i].Phone = phones[v.UserID]
		data[i].Charm = charms[v.UserID]
		data[i].LastRequestTime = requsets[v.UserID]
	}
	excelFileName := fmt.Sprintf("./非阿语区黄钻充值202001-至今.xlsx")
	xlFile := xlsx.NewFile()
	sheet, _ := xlFile.AddSheet("charge")
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), 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, c10.Value, c11.Value, c12.Value, c13.Value = "日期", "国家", "用户ID", "用户昵称", "充值金额", "注册时间", "是否举办首场活动", "svip等级", "时段最高单笔", "历史最高单笔", "手机号", "魅力等级", "最后登陆时间"
	for _, d := range data {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), 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 = ats34(d.Date), ats34(d.Country), ats34(d.UserCode), ats34(d.UserName), ats34(d.ChargeMoney), ats34(d.RegisterAt)
		c7.Value, c8.Value, c9.Value, c10.Value, c11.Value, c12.Value, c13.Value = ats34(d.HaveFirst), ats34(d.SvipLevel), ats34(d.MaxDollarDuration), ats34(d.MaxDollar), d.Phone, ats34(d.Charm), d.LastRequestTime
	}
	_ = xlFile.Save(excelFileName)
}

type UserBindInfo struct {
	UserId   uint64 `gorm:"column:user_id"`
	Phone    string `gorm:"column:phone"`     //  手机号码（包含区号）
	AreaCode string `gorm:"column:area_code"` //  国家区号
}

func (UserBindInfo) TableName() string {
	return "user_bind_info"
}

func GetPhones(userIds []uint64) map[uint64]string {
	var rows []UserBindInfo
	if err := mysql.ProdReadOnlyDB.Model(UserBindInfo{}).Where("user_id in ?", userIds).Find(&rows).Error; err != nil {
		panic(err)
	}
	var res = make(map[uint64]string)
	for _, v := range rows {
		res[v.UserId] = v.AreaCode + v.Phone
	}
	return res
}

// MatchCharmUserScore  用户魅力分数
type MatchCharmUserScore struct {
	UserId uint64 `gorm:"column:user_id"` //  user_id
	Score  int64  `gorm:"column:score"`   //  分数
	Grade  int    `gorm:"column:grade"`   //  等级
}

func (MatchCharmUserScore) TableName() string {
	return "match_charm_user_score"
}

func GetUserCharmLevel(userIds []uint64) map[uint64]int {
	var rows []MatchCharmUserScore
	if err := mysql.ProdReadOnlyDB.Model(MatchCharmUserScore{}).Where("user_id in ?", userIds).Find(&rows).Error; err != nil {
		panic(err)
	}
	res := make(map[uint64]int)
	for _, v := range rows {
		res[v.UserId] = v.Grade
	}
	return res
}

// UserRequestLast  用户请求最后时间
type UserRequestLast struct {
	ID       int64     `gorm:"column:id"`        //  id
	UserId   uint64    `gorm:"column:user_id"`   //  用户id
	TimeLast time.Time `gorm:"column:time_last"` //  最后的时间
}

func (UserRequestLast) TableName() string {
	return "user_request_last"
}

func GetRequestLast(userIds []uint64) map[uint64]string {
	var rows []UserRequestLast
	if err := mysql.ProdReadOnlyDB.Model(UserRequestLast{}).Where("user_id in ?", userIds).Find(&rows).Error; err != nil {
		panic(err)
	}
	res := make(map[uint64]string)
	for _, v := range rows {
		res[v.UserId] = v.TimeLast.Format("2006-01-02 15:04:05")
	}
	return res
}