charge_month.go

package main

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

type ChargeMonthData struct {
	Area   string  // 区域
	Code   string  // 用户ID
	Month  string  // 月份
	Dollar float64 // 当月充值金额$
}

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

func main() {
	type ChargeMaxUid struct {
		Code   string
		Month  string
		Dollar int64
	}
	var chargeUids []ChargeMaxUid
	/*
		SELECT
			u. CODE,
			t2.d,
			t2.dollar
		FROM
			(
				SELECT
					user_id,
					d,
					SUM(dollar) dollar
				FROM
					(
						SELECT
							user_id,
							DATE_FORMAT(created_time, "%Y-%m") d,
							SUM(price) AS dollar
						FROM
							`pay_order`
						WHERE
							`status` = '2'
						AND `type` = '0'
						AND created_time >= "2022-05-01"
						GROUP BY
							user_id,
							d
						UNION ALL
							SELECT
								receiver_id AS user_id,
								DATE_FORMAT(created_time, "%Y-%m") d,
								SUM(dollar) AS dollar
							FROM
								dealer_transfer_detail
							WHERE
								created_time >= "2022-05-01"
							GROUP BY
								receiver_id,
								d
					) t
				GROUP BY
					user_id,
					d
			) t2,
			`user` u
		WHERE
			u.id = t2.user_id
	*/
	if err := mysql.ProdReadOnlyDB.Raw("SELECT\n\tcode,\n\tt2.d month,\n\tt2.dollar\nFROM\n\t(\n\t\tSELECT\n\t\t\tuser_id,\n\t\t\td,\n\t\t\tSUM(dollar) dollar\n\t\tFROM\n\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\t\tuser_id,\n\t\t\t\t\tDATE_FORMAT(created_time, \"%Y-%m\") d,\n\t\t\t\t\tSUM(price) AS dollar\n\t\t\t\tFROM\n\t\t\t\t\t`pay_order`\n\t\t\t\tWHERE\n\t\t\t\t\t`status` = '2'\n\t\t\t\tAND `type` = '0'\n\t\t\t\tAND created_time >= \"2022-05-01\"\n\t\t\t\tGROUP BY\n\t\t\t\t\tuser_id,\n\t\t\t\t\td\n\t\t\t\tUNION ALL\n\t\t\t\t\tSELECT\n\t\t\t\t\t\treceiver_id AS user_id,\n\t\t\t\t\t\tDATE_FORMAT(created_time, \"%Y-%m\") d,\n\t\t\t\t\t\tSUM(dollar) AS dollar\n\t\t\t\t\tFROM\n\t\t\t\t\t\tdealer_transfer_detail\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tcreated_time >= \"2022-05-01\"\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\treceiver_id,\n\t\t\t\t\t\td\n\t\t\t) t\n\t\tGROUP BY\n\t\t\tuser_id,\n\t\t\td\n\t) t2,\n\t`user` u\nWHERE\n\tu.id = t2.user_id").Find(&chargeUids).Error; err != nil {
		panic(err)
	}
	var data []ChargeMonthData
	maxData := make(map[string]ChargeMonthData) // code->maxMonthData
	for _, uc := range chargeUids {
		data = append(data, ChargeMonthData{
			Dollar: float64(uc.Dollar) / 100,
			Code:   uc.Code,
			Area:   GetAreaByCode27(uc.Code),
			Month:  uc.Month,
		})
		//break
	}
	for _, d := range data {
		if d.Dollar > maxData[d.Code].Dollar {
			maxData[d.Code] = ChargeMonthData{
				Area:   d.Area,
				Code:   d.Code,
				Month:  d.Month,
				Dollar: d.Dollar,
			}
		}
	}

	excelFileName := fmt.Sprintf("./202205当月最高充值.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("slot")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c1.Value, c2.Value, c3.Value, c4.Value = "区域", "用户ID", "充值月份", "单月充值金额$"
	for _, d := range maxData {
		row := sheet.AddRow()
		c1, c2, c3, c4 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c1.Value, c2.Value, c3.Value, c4.Value = ats27(d.Area), ats27(d.Code), ats27(d.Month), ats27(d.Dollar)
	}
	_ = xlFile.Save(excelFileName)
}

func GetAreaByCode27(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 "非阿语区"
}