cp_pairs_stat.go

package main

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

// cp等级积分
var CpLevelPoints = map[uint64]uint64{
	0: 0,
	1: 200000,
	2: 800000,
	3: 1000000,
	4: 3000000,
	5: 10000000,
}

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

func getAreaByCode2(code string) string {
	sql := "SELECT area FROM res_country c,user u WHERE u.country = c.name AND u.code = ?"
	var area int
	if err := mysql.ProdReadOnlyDB.Raw(sql, code).Scan(&area).Error; err != nil {
		panic(err)
	}
	if area == 1 {
		return "阿语区"
	}
	return "非阿语区"
}

func getChargeMoneyDiamond(uid uint64, sinceTime time.Time) int64 {
	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 >= ?", sinceTime).
		Order("d.user_id").
		Select("SUM(p.price) as money,SUM(d.num) as diamond").Scan(&money).Error; err != nil {
		return 0
	}
	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 >= ?", sinceTime).
		Order("d.user_id").
		Scan(&money2).Error; err != nil {
		return 0
	}
	return money.Money + money2.Price
}

type CpInfo struct {
	Id          uint64
	UserId1     uint64
	UserId2     uint64
	Code1       string
	Code2       string
	UserId1Area string
	UserId2Area string
	Level       uint64
	Points      uint64
	CreatedTime time.Time
	Charge      float64
}

type CpLevel struct {
	CpId        uint64
	Level       uint64
	Points      uint64
	CreatedTime time.Time
}

func main() {
	var cpInfos []*CpInfo
	if err := mysql.ProdReadOnlyDB.Table("cp_relation").Select("id,user_id1,user_id2").Find(&cpInfos).Error; err != nil {
		panic(err)
	}
	var cpIds []uint64
	for _, v := range cpInfos {
		cpIds = append(cpIds, v.Id)
	}
	var cpLevels []CpLevel
	if err := mysql.ProdReadOnlyDB.Table("cp_level").Select("cp_id,level,points,created_time").Where("cp_id in ?", cpIds).Find(&cpLevels).Error; err != nil {
		panic(err)
	}
	var userIds []uint64
	var users []model.User
	for i, v := range cpInfos {
		userIds = append(userIds, v.UserId1)
		userIds = append(userIds, v.UserId2)
		for _, v2 := range cpLevels {
			if v.Id == v2.CpId {
				cpInfos[i].Level = v2.Level
				cpInfos[i].Points = v2.Points
				cpInfos[i].CreatedTime = v2.CreatedTime
				break
			}
		}
	}
	if err := mysql.ProdReadOnlyDB.Table("user").Where("id in ?", userIds).Find(&users).Error; err != nil {
		panic(err)
	}
	uM := make(map[uint64]model.User)
	for i, v := range users {
		uM[v.Id] = users[i]
	}
	for i, v := range cpInfos {
		cpInfos[i].Code1 = uM[v.UserId1].Code
		cpInfos[i].Code2 = uM[v.UserId2].Code
		cpInfos[i].UserId1Area = getAreaByCode2(uM[v.UserId1].Code)
		cpInfos[i].UserId2Area = getAreaByCode2(uM[v.UserId2].Code)
		cpInfos[i].Points = v.Points + CpLevelPoints[v.Level]
		cpInfos[i].Charge = float64(getChargeMoneyDiamond(v.UserId1, v.CreatedTime)+getChargeMoneyDiamond(v.UserId2, v.CreatedTime)) / 100
	}
	excelFileName := fmt.Sprintf("./cp数据.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("cp")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6, c7, c8, c9 := 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 = "cp ID", "userId1", "userId2", "user1区域", "user2区域", "等级", "积分", "绑定后充值金额 $", "绑定时间"
	for _, d := range cpInfos {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6, c7, c8, c9 := 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 =
			ats26(d.Id), ats26(d.Code1), ats26(d.Code2), ats26(d.UserId1Area), ats26(d.UserId2Area), ats26(d.Level), ats26(d.Points), ats26(d.Charge), ats26(d.CreatedTime.Format("2006-01-02 15:04:05"))
	}
	_ = xlFile.Save(excelFileName)

	//var data []CpPairData

}