history_charge_with_agent.go

package main

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

type ChargeWithAgent struct {
	UserId         uint64
	Code           string
	Dollar         int64
	Country        string
	Area           string
	LastDealerCode string
}

func main() {
	type ResCountry struct {
		Name string
		Area int
	}
	var area []ResCountry
	if err := mysql.ProdReadOnlyDB.Table("res_country").Find(&area).Error; err != nil {
		panic(err)
	}
	am := make(map[string]string)
	for _, v := range area {
		am[v.Name] = "非阿语"
		if v.Area == 1 {
			am[v.Name] = "阿语区"
		}
	}
	sql := `
select id as user_id,code ,SUM(dollar) as dollar ,country FROM (
select u.id,u.code,SUM(price) 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) 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) 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;
`
	var data []ChargeWithAgent
	if err := mysql.ProdReadOnlyDB.Raw(sql).Scan(&data).Error; err != nil {
		panic(err)
	}
	for i, d := range data {
		data[i].LastDealerCode = getLastDealerCode(d.UserId)
		data[i].Area = am[d.Country]
		if len(data[i].LastDealerCode) > 0 {
		}
		println(i)
	}
	excelFileName := fmt.Sprintf("./历史累充带代理.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("wealth")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	c1, c2, c3, c4, c5 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
	c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = "靓号", "充值美分", "国家", "区域", "最近代理"
	for _, d := range data {
		row := sheet.AddRow()
		c1, c2, c3, c4, c5 := row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell(), row.AddCell()
		c1.Value, c2.Value, c3.Value, c4.Value, c5.Value = d.Code, cast.ToString(d.Dollar), d.Country, d.Area, d.LastDealerCode
	}
	_ = xlFile.Save(excelFileName)
}

func getLastDealerCode(userId uint64) string {
	type Result struct {
		Code       string
		CreateTime time.Time
	}
	sql := "SELECT u.code,dealer_id,receiver_id,dealer_transfer_detail.created_time FROM dealer_transfer_detail,user u where u.id = dealer_transfer_detail.dealer_id AND  receiver_id = ? order by dealer_transfer_detail.id DESC limit 1"
	var res Result
	if err := mysql.ProdReadOnlyDB.Raw(sql, userId).Scan(&res).Error; err != nil {
		if err != gorm.ErrRecordNotFound {
			panic(err)
		}
	}
	sql2 := "SELECT u.code,dealer_id,receiver_id,dealer_transfer_detail_pink.created_time FROM dealer_transfer_detail_pink,user u where u.id = dealer_transfer_detail_pink.dealer_id AND  receiver_id = ? order by dealer_transfer_detail_pink.id DESC limit 1"
	var res2 Result
	if err := mysql.ProdReadOnlyDB.Raw(sql2, userId).Scan(&res2).Error; err != nil {
		if err != gorm.ErrRecordNotFound {
			panic(err)
		}
	}
	if len(res.Code) > 0 && len(res2.Code) > 0 {
		if res.CreateTime.After(res2.CreateTime) {
			return res.Code
		} else {
			return res2.Code
		}
	}
	if len(res.Code) > 0 {
		return res.Code
	}
	return res2.Code
}