charge_history_sum.go

package main

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

type ChargeHistoryData struct {
	UserId         uint64
	Code           string
	Country        string
	Dollar         float64 // 累充金额$
	SvipLevel      int
	MoneyMaxDollar float64 // 最高当月充值金额$
	Month          string
}

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

func main() {
	var charges []ChargeHistoryData
	if err := mysql.ProdReadOnlyDB.Raw("select id as user_id,code,SUM(dollar) as dollar,country FROM " +
		"(select u.id,u.`code`,SUM(price) / 100 as dollar,u.country FROM pay_order p, `user` u  where  u.id = p.user_id AND p.`status` = 2 AND `type` = 0 group by user_id UNION ALL select u.id,u.code,SUM(dollar) / 100 as dollar,u.country FROM dealer_transfer_detail d, `user` u where u.id = d.receiver_id group by receiver_id UNION ALL " +
		"select u.id,u.code,SUM(dollar) / 100 as dollar,u.country FROM dealer_transfer_detail_pink d, `user` u where u.id = d.receiver_id group by receiver_id) t " +
		"where country in (SELECT name FROM res_country where area = 1) group by code order by dollar DESC").Find(&charges).Error; err != nil {
		panic(err)
	}
	for i, uc := range charges {
		md, month := GetUserMonthMaxCharge2(uc.UserId)
		charges[i].Month = month
		charges[i].MoneyMaxDollar = md
		charges[i].SvipLevel = GetUserSvip(uc.UserId)
		//break
	}

	excelFileName := fmt.Sprintf("./阿语历史充值.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("charge")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4, c5, c6 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c1.Value, c2.Value, c3.Value, c4.Value, c5.Value, c6.Value = "用户ID", "累充金额$", "SVIP等级", "最高当月充值金额$", "充值月份", "国家"
	for _, d := range charges {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5, c6 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c1.Value, c2.Value, c3.Value, c4.Value, c5.Value, c6.Value = d.Code, ats40(d.Dollar), ats40(d.SvipLevel), ats40(d.MoneyMaxDollar), d.Month, d.Country
	}
	_ = xlFile.Save(excelFileName)
}

func GetUserMonthMaxCharge2(uid uint64) (float64, string) {
	type MD struct {
		UserId uint64
		D      string
		Dollar float64
	}
	var md []MD
	if err := mysql.ProdReadOnlyDB.Raw("select user_id,d,SUM(dollar) / 100  dollar \nFROM (\nSELECT\n\tuser_id,\n\tDATE_FORMAT(created_time,\"%Y-%m\") as d,\n\tSUM(price) as dollar\nFROM\n\t`pay_order`\nWHERE\n\t`status` = '2' AND `type` = 0 AND user_id = ?\nGROUP BY d UNION ALL \nSELECT\n\treceiver_id user_id,\n\tDATE_FORMAT(created_time,\"%Y-%m\") as d,\n\tSUM(dollar) as dollar\nFROM\n\t`dealer_transfer_detail`\nWHERE receiver_id = ?\nGROUP BY d UNION ALL \nSELECT\n\treceiver_id user_id,\n\tDATE_FORMAT(created_time,\"%Y-%m\") as d,\n\tSUM(dollar) as dollar\nFROM\n\t`dealer_transfer_detail_pink`\nWHERE receiver_id = ?\nGROUP BY d\n) t GROUP BY d order by dollar desc ", uid, uid, uid).Find(&md).Error; err != nil {
		panic(err)
	}
	if len(md) > 0 {
		return md[0].Dollar, md[0].D
	}
	return 0, ""
}

func GetUserSvip(uid uint64) int {
	type UserSvip struct {
		UserId uint64
		Level  int
	}
	var res UserSvip
	if err := mysql.ProdReadOnlyDB.Table("user_svip").Where("user_id = ?", uid).First(&res).Error; err != nil {
		if err != gorm.ErrRecordNotFound {
			panic(err)
		}
	}
	return res.Level
}