history_charge_with_agent.go 4.47 KB
Newer Older
hujiebin's avatar
1  
hujiebin committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
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
19
	Last30Dollar   int64
hujiebin's avatar
1  
hujiebin committed
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
}

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)
	}
51
	last30Charge := getLast30Charge()
hujiebin's avatar
1  
hujiebin committed
52 53 54 55 56
	for i, d := range data {
		data[i].LastDealerCode = getLastDealerCode(d.UserId)
		data[i].Area = am[d.Country]
		if len(data[i].LastDealerCode) > 0 {
		}
57
		data[i].Last30Dollar = last30Charge[d.UserId]
hujiebin's avatar
1  
hujiebin committed
58 59 60 61 62 63 64 65 66
		println(i)
	}
	excelFileName := fmt.Sprintf("./历史累充带代理.xlsx")
	xlFile := xlsx.NewFile()
	sheet, err := xlFile.AddSheet("wealth")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
67 68
	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 = "靓号", "充值美分", "国家", "区域", "最近代理", "最近30天充值"
hujiebin's avatar
1  
hujiebin committed
69 70
	for _, d := range data {
		row := sheet.AddRow()
71 72
		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, cast.ToString(d.Dollar), d.Country, d.Area, d.LastDealerCode, cast.ToString(d.Last30Dollar)
hujiebin's avatar
1  
hujiebin committed
73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
	}
	_ = 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
}
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127

func getLast30Charge() map[uint64]int64 {
	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 AND p.created_time > NOW() - INTERVAL 30 DAY 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 AND d.created_time > NOW() - INTERVAL 30 DAY  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 AND d.created_time > NOW() - INTERVAL 30 DAY group by receiver_id
) t 
group by code order by dollar DESC;
`
	var data []ChargeWithAgent
	if err := mysql.ProdReadOnlyDB.Raw(sql).Scan(&data).Error; err != nil {
		panic(err)
	}
	var res = make(map[uint64]int64)
	for _, v := range data {
		res[v.UserId] = v.Dollar
	}
	return res
}