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
	M1                int
	M2                int
	M3                int
	M4                int
	M5                int
	M6                int
	M7                int
	ModifyAnniversary int
}

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
		cpInfos[i].ModifyAnniversary, cpInfos[i].M1, cpInfos[i].M2, cpInfos[i].M3, cpInfos[i].M4, cpInfos[i].M5, cpInfos[i].M6, cpInfos[i].M7 = modifyAnniversary(v.Id)
	}
	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, c10 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c11, c12, c13, c14, c15, c16, c17 := 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 = "cp ID", "userId1", "userId2", "user1区域", "user2区域", "等级", "积分", "绑定后充值金额 $", "绑定时间", "是否修改纪念日"
	c11.Value, c12.Value, c13.Value, c14.Value, c15.Value, c16.Value, c17.Value = "我们在一起", "XXX的生日", "XXX的生日", "第一次说我爱你", "第一次亲吻", "结婚纪念日", "用户自行编辑"

	for _, d := range cpInfos {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c11, c12, c13, c14, c15, c16, c17 := 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 =
			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")), ats26(d.ModifyAnniversary)
		c11.Value, c12.Value, c13.Value, c14.Value, c15.Value, c16.Value, c17.Value = ats26(d.M1), ats26(d.M2), ats26(d.M3), ats26(d.M4), ats26(d.M5), ats26(d.M6), ats26(d.M7)
	}
	_ = xlFile.Save(excelFileName)

	//var data []CpPairData

}

type CpAnniversary struct {
	CpId        uint64
	Sort        int
	Type        int
	MsgId       int
	Timestamp   int
	CreatedTime time.Time `gorm:"->"`
	UpdatedTime time.Time `gorm:"->"`
}

func modifyAnniversary(cpId uint64) (t, m1, m2, m3, m4, m5, m6, m7 int) {
	var an []CpAnniversary
	if err := mysql.ProdReadOnlyDB.Model(CpAnniversary{}).Where("cp_id = ? AND timestamp > 0 ", cpId).Find(&an).Error; err != nil {
		panic(err)
	}
	for _, a := range an {
		if a.MsgId == 259 && a.UpdatedTime.Second() != 0 {
			m1 = 1
		}
		if a.MsgId == 260 && a.Type == 2 {
			m2 = 1
		}
		if a.MsgId == 260 && a.Type == 3 {
			m3 = 1
		}
		if a.MsgId == 261 {
			m4 = 1
		}
		if a.MsgId == 262 {
			m5 = 1
		}
		if a.MsgId == 263 {
			m6 = 1
		}
		if a.MsgId == 0 {
			m7++
		}
	}
	if m1 > 0 || m2 > 0 || m3 > 0 || m4 > 0 || m5 > 0 || m6 > 0 || m7 > 0 {
		t = 1
	}
	return
}