user_register_stat.go

package main

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

type UserRegisterData struct {
	RegisterTime string // 注册时间
	Code         string // 逻辑ID
	Country      string // 国籍
	Imei         string // 设备码
	SameImei     int64  // 同设备码用户数
	IP           string // IP
	SameIp       int64  // 同IP用户数
}

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

func getImei(userId uint64) string {
	var imei string
	if err := mysql.ProdReadOnlyDB.Table("user_imei").Where("user_id = ?", userId).Select("imei").Scan(&imei).Error; err != nil {
	}
	return imei
}

func getSameImei(imei string) int64 {
	var cnt int64
	if err := mysql.ProdReadOnlyDB.Table("user_imei").Where("imei = ?", imei).Count(&cnt).Error; err != nil {
	}
	return cnt
}

func getIp(userId uint64) string {
	var ip string
	if err := mysql.ProdReadOnlyDB.Table("user_ip").Where("user_id = ?", userId).Select("ip").Scan(&ip).Error; err != nil {
	}
	return ip
}

func getSameIp(ip string) int64 {
	var cnt int64
	if err := mysql.ProdReadOnlyDB.Table("user_ip").Where("ip = ?", ip).Count(&cnt).Error; err != nil {
	}
	return cnt
}

func main() {
	var users []model.User
	if err := mysql.ProdReadOnlyDB.Table("user").Where("created_time >= ? AND created_time < ?", "2023-06-18", "2023-07-19").Find(&users).Error; err != nil {
		//if err := mysql.ProdReadOnlyDB.Table("user").Where("created_time >= ? AND created_time < ?", "2023-07-18", "2023-07-19").Limit(10).Find(&users).Error; err != nil {
		panic(err)
	}
	var data []UserRegisterData
	for i, u := range users {
		imei := getImei(u.Id)
		imeiCnt := int64(0)
		if len(imei) > 0 {
			imeiCnt = getSameImei(imei)
		}
		ip := getIp(u.Id)
		ipCnt := int64(0)
		if len(ip) > 0 {
			ipCnt = getSameIp(ip)
		}
		data = append(data, UserRegisterData{
			RegisterTime: u.CreatedTime.Format("2006-01-02"),
			Code:         u.Code,
			Country:      u.Country,
			Imei:         imei,
			SameImei:     imeiCnt,
			IP:           ip,
			SameIp:       ipCnt,
		})
		println(i)
	}
	excelFileName := fmt.Sprintf("./hilo新增用户信息-6.18-7.18.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("slot")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6, c7 := 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 = "注册时间", "逻辑ID", "国籍", "设备码", "同设备码用户数", "IP", "同IP用户数"
	for _, d := range data {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6, c7 := 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 = ats30(d.RegisterTime), ats30(d.Code), ats30(d.Country), ats30(d.Imei), ats30(d.SameImei), ats30(d.IP), ats30(d.SameIp)
	}
	_ = xlFile.Save(excelFileName)
}