group_power_india.go

package main

import (
	"fmt"
	"github.com/hilo-common/mysql"
	"github.com/spf13/cast"
	"github.com/tealeg/xlsx"
	"time"
)

type GroupPower struct {
	GroupPowerId    uint64
	Owner           uint64
	OwnerCode       string
	Country         string
	UserIds         []uint64 // 所有uid
	UserIds9        []uint64 // 9月1号注册后的uid
	UserIds10       []uint64 // 10月1号注册后的uid
	UserIds11       []uint64 // 11月1号注册后的uid
	UserChargeNum   int      // 所有进行了充值的势力成员数量
	UserChargeSum   float64  // 所有势力成员累积充值
	UserChargeNum9  int      // 9月1号后注册所有进行了充值的势力成员数量
	UserChargeSum9  float64  // 9月1号后注册所有势力成员累积充值
	UserChargeNum10 int      // 10月1号后注册所有进行了充值的势力成员数量
	UserChargeSum10 float64  // 10月1号后注册所有势力成员累积充值
	UserChargeNum11 int      // 11月1号后注册所有进行了充值的势力成员数量
	UserChargeSum11 float64  // 11月1号后注册所有势力成员累积充值
}

type User struct {
	Id          uint64
	Code        string
	Country     string
	CreatedTime time.Time
}

type GroupPowerUser struct {
	GroupPowerId uint64
	UserId       uint64
}

func main() {
	groupPowerSql := "SELECT p.id as group_power_id,g.`owner` from group_power p,group_info g where p.group_uid = g.im_group_id"
	var groupPowers []GroupPower
	if err := mysql.Db.Raw(groupPowerSql).Find(&groupPowers).Error; err != nil {
		panic(err)
	}

	var userIds []uint64
	for _, v := range groupPowers {
		userIds = append(userIds, v.Owner)
	}
	var users []User
	if err := mysql.Db.Model(User{}).Where("id in ?", userIds).Find(&users).Error; err != nil {
		panic(err)
	}
	userMap := make(map[uint64]User)
	for i := range users {
		userMap[users[i].Id] = users[i]
	}
	var targetGroupPower []GroupPower
	var targetGroupPowerId []uint64
	for i, p := range groupPowers {
		if userMap[p.Owner].Country == "India" {
			groupPowers[i].Country = "India"
			groupPowers[i].Owner = userMap[p.Owner].Id
			groupPowers[i].OwnerCode = userMap[p.Owner].Code
			targetGroupPower = append(targetGroupPower, groupPowers[i])
			targetGroupPowerId = append(targetGroupPowerId, p.GroupPowerId)
		}
	}
	groupPowerUsersSql := "SELECT group_power_id,user_id FROM `group_power_user` where group_power_id in (?);"
	var groupPowerUsers []GroupPowerUser
	if err := mysql.Db.Raw(groupPowerUsersSql, targetGroupPowerId).Find(&groupPowerUsers).Error; err != nil {
		panic(err)
	}
	var groupPowerUserMap = make(map[uint64][]uint64) // group_power_id -> []userId
	for _, v := range groupPowerUsers {
		groupPowerUserMap[v.GroupPowerId] = append(groupPowerUserMap[v.GroupPowerId], v.UserId)
	}
	for i, v := range targetGroupPower {
		targetGroupPower[i].UserIds = groupPowerUserMap[v.GroupPowerId]
		for _, userId := range targetGroupPower[i].UserIds {
			if user, ok := userMap[userId]; ok {
				if user.CreatedTime.After(time.Date(2022, time.September, 1, 0, 0, 0, 0, time.Local)) {
					targetGroupPower[i].UserIds9 = append(targetGroupPower[i].UserIds9, userId)
				}
				if user.CreatedTime.After(time.Date(2022, time.October, 1, 0, 0, 0, 0, time.Local)) {
					targetGroupPower[i].UserIds10 = append(targetGroupPower[i].UserIds10, userId)
				}
				if user.CreatedTime.After(time.Date(2022, time.November, 1, 0, 0, 0, 0, time.Local)) {
					targetGroupPower[i].UserIds11 = append(targetGroupPower[i].UserIds11, userId)
				}
			}
		}
	}
	type NumSum struct {
		Count int
		Price int
	}
	// 直接充值
	for i, groupPower := range targetGroupPower {
		userChargeNumSql := "SELECT count(DISTINCT user_id) as count,sum(price) as price FROM `pay_order` where status = 2 AND platform in (1,2,3,4,5) AND user_id in ? ;"
		var userChargeNumSum NumSum
		if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds).First(&userChargeNumSum).Error; err != nil {
			panic(err)
		} else {
			targetGroupPower[i].UserChargeNum += userChargeNumSum.Count
			targetGroupPower[i].UserChargeSum += float64(userChargeNumSum.Price) / 100
		}
		if len(groupPower.UserIds9) > 0 {
			var userChargeNumSum9 NumSum
			if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds9).First(&userChargeNumSum9).Error; err != nil {
				panic(err)
			} else {
				targetGroupPower[i].UserChargeNum9 += userChargeNumSum9.Count
				targetGroupPower[i].UserChargeSum9 += float64(userChargeNumSum9.Price) / 100
			}
		}
		if len(groupPower.UserIds10) > 0 {
			var userChargeNumSum10 NumSum
			if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds10).First(&userChargeNumSum10).Error; err != nil {
				panic(err)
			} else {
				targetGroupPower[i].UserChargeNum10 += userChargeNumSum10.Count
				targetGroupPower[i].UserChargeSum10 += float64(userChargeNumSum10.Price) / 100
			}
		}
		if len(groupPower.UserIds11) > 0 {
			var userChargeNumSum11 NumSum
			if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds11).First(&userChargeNumSum11).Error; err != nil {
				panic(err)
			} else {
				targetGroupPower[i].UserChargeNum11 += userChargeNumSum11.Count
				targetGroupPower[i].UserChargeSum11 += float64(userChargeNumSum11.Price) / 100
			}
		}
	}
	// 代理充值
	for i, groupPower := range targetGroupPower {
		userChargeNumSql := "SELECT count(DISTINCT receiver_id) as count,sum(dollar) as price FROM `dealer_transfer_detail` where receiver_id in ? ;"
		var userChargeNumSum NumSum
		if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds).First(&userChargeNumSum).Error; err != nil {
			panic(err)
		} else {
			targetGroupPower[i].UserChargeNum += userChargeNumSum.Count
			targetGroupPower[i].UserChargeSum += float64(userChargeNumSum.Price) / 100
		}
		if len(groupPower.UserIds9) > 0 {
			var userChargeNumSum9 NumSum
			if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds9).First(&userChargeNumSum9).Error; err != nil {
				panic(err)
			} else {
				targetGroupPower[i].UserChargeNum9 += userChargeNumSum9.Count
				targetGroupPower[i].UserChargeSum9 += float64(userChargeNumSum9.Price) / 100
			}
		}
		if len(groupPower.UserIds10) > 0 {
			var userChargeNumSum10 NumSum
			if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds10).First(&userChargeNumSum10).Error; err != nil {
				panic(err)
			} else {
				targetGroupPower[i].UserChargeNum10 += userChargeNumSum10.Count
				targetGroupPower[i].UserChargeSum10 += float64(userChargeNumSum10.Price) / 100
			}
		}
		if len(groupPower.UserIds11) > 0 {
			var userChargeNumSum11 NumSum
			if err := mysql.Db.Raw(userChargeNumSql, groupPower.UserIds11).First(&userChargeNumSum11).Error; err != nil {
				panic(err)
			} else {
				targetGroupPower[i].UserChargeNum11 += userChargeNumSum11.Count
				targetGroupPower[i].UserChargeSum11 += float64(userChargeNumSum11.Price) / 100
			}
		}
	}
	println(targetGroupPower)
	excelFileName := fmt.Sprintf("./india_power%s.xlsx", time.Now().Format("20060102150405"))
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("india_charge_data")
	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()
	c1.Value, c2.Value, c3.Value, c4.Value, c5.Value, c6.Value, c7.Value, c8.Value, c9.Value, c10.Value =
		"印度国籍的势力主ID", "所有势力成员数量", "所有势力成员累积充值", "所有进行了充值的势力成员数量", "9月1日之后注册的势力成员累积充值",
		"9月1日之后注册的进行了充值的势力成员数量", "10月1日之后注册的势力成员累积充值", "10月1日之后注册的进行了充值的势力成员数量", "11月1日之后注册的势力成员累积充值", "11月1日之后注册的进行了充值的势力成员数量"
	for _, power := range targetGroupPower {
		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()
		c1.Value, c2.Value, c3.Value, c4.Value, c5.Value, c6.Value, c7.Value, c8.Value, c9.Value, c10.Value =
			toString(power.OwnerCode), toString(len(power.UserIds)), toString(power.UserChargeSum), toString(power.UserChargeNum),
			toString(power.UserChargeSum9), toString(power.UserChargeNum9), toString(power.UserChargeSum10), toString(power.UserChargeNum10),
			toString(power.UserChargeSum11), toString(power.UserChargeNum11)
	}
	_ = xlFile.Save(excelFileName)
}

func toString(n interface{}) string {
	return cast.ToString(n)
}