857 lines
34 KiB
Go
857 lines
34 KiB
Go
package dashboard
|
||
|
||
import (
|
||
"context"
|
||
"database/sql"
|
||
"fmt"
|
||
"math"
|
||
"sort"
|
||
"strconv"
|
||
"strings"
|
||
"time"
|
||
|
||
"hyapp-admin-server/internal/config"
|
||
)
|
||
|
||
const externalPeriodMetricSelect = `
|
||
COALESCE(SUM(t.country_new_user), 0),
|
||
COALESCE(SUM(t.daily_active_user), 0),
|
||
COALESCE(SUM(t.daily_recharge_user), 0),
|
||
CAST(COALESCE(SUM(t.new_user_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.official_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.mifapay_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.google_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.dealer_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.user_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.new_dealer_user_recharge), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.salary_exchange), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.salary_transfer), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.gift_consume), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.lucky_gift_total_flow), 0) AS CHAR),
|
||
COALESCE(SUM(t.lucky_gift_user), 0),
|
||
CAST(COALESCE(SUM(t.lucky_gift_payout), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.lucky_gift_anchor_share), 0) AS CHAR),
|
||
CAST(COALESCE(SUM(t.game_total_flow), 0) AS CHAR),
|
||
COALESCE(SUM(t.game_user), 0),
|
||
CAST(COALESCE(SUM(t.game_payout), 0) AS CHAR),
|
||
COALESCE(SUM(t.d1_retention_user), 0),
|
||
COALESCE(SUM(t.d1_retention_base_user), 0),
|
||
COALESCE(SUM(t.d7_retention_user), 0),
|
||
COALESCE(SUM(t.d7_retention_base_user), 0),
|
||
COALESCE(SUM(t.d30_retention_user), 0),
|
||
COALESCE(SUM(t.d30_retention_base_user), 0),
|
||
MAX(t.refreshed_at)`
|
||
|
||
type MySQLExternalDashboardSource struct {
|
||
db *sql.DB
|
||
appCode string
|
||
appName string
|
||
sysOrigin string
|
||
statTimezone string
|
||
requestTimeout time.Duration
|
||
regionResolvers []ExternalDashboardRegionResolver
|
||
}
|
||
|
||
type externalDashboardMetric struct {
|
||
NewUsers int64
|
||
ActiveUsers int64
|
||
PaidUsers int64
|
||
RechargeUsers int64
|
||
NewUserRechargeUSDMinor int64
|
||
OfficialRechargeUSDMinor int64
|
||
MifaPayRechargeUSDMinor int64
|
||
GoogleRechargeUSDMinor int64
|
||
CoinSellerRechargeUSDMinor int64
|
||
UserRechargeUSDMinor int64
|
||
NewDealerRechargeUSDMinor int64
|
||
SalaryExchangeUSDMinor int64
|
||
SalaryTransferUSDMinor int64
|
||
SalaryUSDMinor *int64
|
||
GiftCoinSpent int64
|
||
LuckyGiftTurnover int64
|
||
LuckyGiftPayers int64
|
||
LuckyGiftPayout int64
|
||
LuckyGiftAnchorShare int64
|
||
GameTurnover int64
|
||
GamePlayers int64
|
||
GamePayout int64
|
||
D1RetentionUsers int64
|
||
D1RetentionBaseUsers int64
|
||
D7RetentionUsers int64
|
||
D7RetentionBaseUsers int64
|
||
D30RetentionUsers int64
|
||
D30RetentionBaseUsers int64
|
||
RechargeUSDMinor int64
|
||
ARPUUSDMinor int64
|
||
ARPPUUSDMinor int64
|
||
PayerRate float64
|
||
PaidConversionRate float64
|
||
RechargeConversionRate float64
|
||
GameProfit int64
|
||
GameProfitRate float64
|
||
LuckyGiftProfit int64
|
||
LuckyGiftPayoutRate float64
|
||
LuckyGiftProfitRate float64
|
||
D1RetentionRate float64
|
||
D7RetentionRate float64
|
||
D30RetentionRate float64
|
||
CoinTotal *int64
|
||
// 以下指针字段只有 likei Mongo 源能给出事实；cdc MySQL 源保持 nil，前端展示 "--"。
|
||
CoinSellerTransferCoin *int64
|
||
ConsumedCoin *int64
|
||
OutputCoin *int64
|
||
RefreshedAt sql.NullTime
|
||
}
|
||
|
||
type externalMetricScanner interface {
|
||
Scan(dest ...any) error
|
||
}
|
||
|
||
// NewMySQLExternalDashboardSource 把外接 App 的大屏查询绑定到 dashboard-cdc-worker 产出的只读聚合库；
|
||
// 返回 nil 代表配置未启用，调用方可以统一把 nil 过滤掉。
|
||
func NewMySQLExternalDashboardSource(db *sql.DB, sourceConfig config.DashboardExternalSourceConfig, regionResolvers ...ExternalDashboardRegionResolver) ExternalDashboardSource {
|
||
if db == nil || !sourceConfig.Enabled {
|
||
return nil
|
||
}
|
||
appCode := strings.ToLower(strings.TrimSpace(sourceConfig.AppCode))
|
||
sysOrigin := strings.ToUpper(strings.TrimSpace(sourceConfig.SysOrigin))
|
||
statTimezone := strings.TrimSpace(sourceConfig.StatTimezone)
|
||
if statTimezone == "" {
|
||
statTimezone = "Asia/Riyadh"
|
||
}
|
||
timeout := sourceConfig.RequestTimeout
|
||
if timeout <= 0 {
|
||
timeout = 5 * time.Second
|
||
}
|
||
if appCode == "" || sysOrigin == "" {
|
||
return nil
|
||
}
|
||
return &MySQLExternalDashboardSource{
|
||
db: db,
|
||
appCode: appCode,
|
||
appName: strings.TrimSpace(sourceConfig.AppName),
|
||
sysOrigin: sysOrigin,
|
||
statTimezone: statTimezone,
|
||
requestTimeout: timeout,
|
||
regionResolvers: compactExternalDashboardRegionResolvers(regionResolvers),
|
||
}
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) AppCode() string {
|
||
if s == nil {
|
||
return ""
|
||
}
|
||
return s.appCode
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) StatisticsOverview(ctx context.Context, query StatisticsQuery) (map[string]any, error) {
|
||
if s == nil || s.db == nil {
|
||
return nil, fmt.Errorf("external dashboard source is not configured")
|
||
}
|
||
countryFilter, err := s.countryFilter(ctx, query)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
requestTimezone := strings.TrimSpace(query.StatTZ)
|
||
if requestTimezone == "" {
|
||
requestTimezone = s.statTimezone
|
||
}
|
||
requestLocation, err := time.LoadLocation(requestTimezone)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("load external dashboard request timezone %s: %w", requestTimezone, err)
|
||
}
|
||
storageTimezone := s.statTimezone
|
||
if _, err := time.LoadLocation(storageTimezone); err != nil {
|
||
return nil, fmt.Errorf("load external dashboard storage timezone %s: %w", storageTimezone, err)
|
||
}
|
||
// 前端的 stat_tz 表示筛选日期的解释方式；dashboard-cdc-worker 表里的 stat_timezone 是外部项目固定存储口径，两者不能混用。
|
||
startDate, endDate, startMS, endMS := externalDashboardDateRange(query, requestLocation)
|
||
|
||
queryCtx, cancel := context.WithTimeout(ctx, s.requestTimeout)
|
||
defer cancel()
|
||
|
||
total, err := s.loadMetricSummary(queryCtx, storageTimezone, startDate, endDate, countryFilter)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
days := countCalendarDays(startDate, endDate)
|
||
previousStart := startDate.AddDate(0, 0, -days)
|
||
previous, err := s.loadMetricSummary(queryCtx, storageTimezone, previousStart, startDate, countryFilter)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
dailySeries, err := s.loadDailySeries(queryCtx, storageTimezone, startDate, endDate, countryFilter)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
countryBreakdown, err := s.loadCountryBreakdown(queryCtx, storageTimezone, startDate, endDate, countryFilter)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
dailyCountryBreakdown, err := s.loadDailyCountryBreakdown(queryCtx, storageTimezone, startDate, endDate, countryFilter)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
response := total.toMap()
|
||
response["app_code"] = s.appCode
|
||
response["app_name"] = firstExternalDashboardValue(s.appName, s.appCode)
|
||
response["sys_origin"] = s.sysOrigin
|
||
response["stat_tz"] = storageTimezone
|
||
response["request_stat_tz"] = requestTimezone
|
||
response["start_ms"] = startMS
|
||
response["end_ms"] = endMS
|
||
response["daily_series"] = dailySeries
|
||
response["country_breakdown"] = countryBreakdown
|
||
response["daily_country_breakdown"] = dailyCountryBreakdown
|
||
response["retention"] = total.retentionMap()
|
||
response["report_metric_sources"] = externalDashboardMetricSources()
|
||
response["updated_at_ms"] = total.updatedAtMS()
|
||
applyExternalDashboardDeltas(response, total, previous)
|
||
return response, nil
|
||
}
|
||
|
||
type externalDashboardCountryFilter struct {
|
||
Applied bool
|
||
RegionID int64
|
||
Codes []string
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) countryFilter(ctx context.Context, query StatisticsQuery) (externalDashboardCountryFilter, error) {
|
||
if query.CountryID > 0 {
|
||
return externalDashboardCountryFilter{}, fmt.Errorf("external dashboard source %s does not support numeric country_id filter", s.appCode)
|
||
}
|
||
if query.RegionID <= 0 {
|
||
return externalDashboardCountryFilter{}, nil
|
||
}
|
||
for _, resolver := range s.regionResolvers {
|
||
codes, handled, err := resolver.CountryCodesForRegion(ctx, s.appCode, query.RegionID)
|
||
if err != nil {
|
||
return externalDashboardCountryFilter{}, err
|
||
}
|
||
if !handled {
|
||
continue
|
||
}
|
||
return externalDashboardCountryFilter{
|
||
Applied: true,
|
||
RegionID: query.RegionID,
|
||
Codes: normalizeExternalCountryCodes(codes),
|
||
}, nil
|
||
}
|
||
// dashboard-cdc-worker 没有 hyapp 的数字 region_id，只能通过 legacy 区域目录映射到 country_code 后过滤；缺映射时直接失败，避免返回未筛选全量数据。
|
||
return externalDashboardCountryFilter{}, fmt.Errorf("external dashboard source %s cannot resolve region_id %d", s.appCode, query.RegionID)
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) loadMetricSummary(ctx context.Context, statTimezone string, startDate time.Time, endDate time.Time, countryFilter externalDashboardCountryFilter) (externalDashboardMetric, error) {
|
||
whereSQL, args := s.periodMetricWhere(statTimezone, startDate, endDate, countryFilter)
|
||
row := s.db.QueryRowContext(ctx, `
|
||
SELECT `+externalPeriodMetricSelect+`
|
||
FROM country_dashboard_period_metric t
|
||
WHERE `+whereSQL, args...)
|
||
metric, err := scanExternalDashboardMetric(row)
|
||
if err != nil {
|
||
return externalDashboardMetric{}, fmt.Errorf("query external dashboard summary for %s: %w", s.appCode, err)
|
||
}
|
||
return metric, nil
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) loadDailySeries(ctx context.Context, statTimezone string, startDate time.Time, endDate time.Time, countryFilter externalDashboardCountryFilter) ([]map[string]any, error) {
|
||
whereSQL, args := s.periodMetricWhere(statTimezone, startDate, endDate, countryFilter)
|
||
rows, err := s.db.QueryContext(ctx, `
|
||
SELECT t.period_key, `+externalPeriodMetricSelect+`
|
||
FROM country_dashboard_period_metric t
|
||
WHERE `+whereSQL+`
|
||
GROUP BY t.period_key
|
||
ORDER BY t.period_key`, args...)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("query external dashboard daily series for %s: %w", s.appCode, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
series := []map[string]any{}
|
||
for rows.Next() {
|
||
var statDay string
|
||
metric, err := scanExternalDashboardMetricWithPrefix(rows, &statDay)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("scan external dashboard daily series for %s: %w", s.appCode, err)
|
||
}
|
||
row := metric.toMap()
|
||
row["label"] = statDay
|
||
row["stat_day"] = statDay
|
||
series = append(series, row)
|
||
}
|
||
if err := rows.Err(); err != nil {
|
||
return nil, fmt.Errorf("iterate external dashboard daily series for %s: %w", s.appCode, err)
|
||
}
|
||
return series, nil
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) loadCountryBreakdown(ctx context.Context, statTimezone string, startDate time.Time, endDate time.Time, countryFilter externalDashboardCountryFilter) ([]map[string]any, error) {
|
||
whereSQL, args := s.periodMetricWhere(statTimezone, startDate, endDate, countryFilter)
|
||
rows, err := s.db.QueryContext(ctx, `
|
||
SELECT t.country_code, MAX(t.country_name), `+externalPeriodMetricSelect+`
|
||
FROM country_dashboard_period_metric t
|
||
WHERE `+whereSQL+`
|
||
GROUP BY t.country_code
|
||
ORDER BY SUM(t.official_recharge + t.mifapay_recharge + t.dealer_recharge) DESC, t.country_code`, args...)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("query external dashboard country breakdown for %s: %w", s.appCode, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
countries := []map[string]any{}
|
||
for rows.Next() {
|
||
var countryCode string
|
||
var countryName sql.NullString
|
||
metric, err := scanExternalDashboardMetricWithPrefix(rows, &countryCode, &countryName)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("scan external dashboard country breakdown for %s: %w", s.appCode, err)
|
||
}
|
||
row := metric.toMap()
|
||
row["country_id"] = 0
|
||
row["country_code"] = countryCode
|
||
row["country_name"] = firstExternalDashboardValue(countryName.String, countryCode)
|
||
row["country"] = firstExternalDashboardValue(countryName.String, countryCode)
|
||
if countryFilter.RegionID > 0 {
|
||
row["region_id"] = countryFilter.RegionID
|
||
}
|
||
countries = append(countries, row)
|
||
}
|
||
if err := rows.Err(); err != nil {
|
||
return nil, fmt.Errorf("iterate external dashboard country breakdown for %s: %w", s.appCode, err)
|
||
}
|
||
return countries, nil
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) loadDailyCountryBreakdown(ctx context.Context, statTimezone string, startDate time.Time, endDate time.Time, countryFilter externalDashboardCountryFilter) ([]map[string]any, error) {
|
||
whereSQL, args := s.periodMetricWhere(statTimezone, startDate, endDate, countryFilter)
|
||
rows, err := s.db.QueryContext(ctx, `
|
||
SELECT t.period_key, t.country_code, MAX(t.country_name), `+externalPeriodMetricSelect+`
|
||
FROM country_dashboard_period_metric t
|
||
WHERE `+whereSQL+`
|
||
GROUP BY t.period_key, t.country_code
|
||
ORDER BY t.period_key, t.country_code`, args...)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("query external dashboard daily country breakdown for %s: %w", s.appCode, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
out := []map[string]any{}
|
||
for rows.Next() {
|
||
var statDay string
|
||
var countryCode string
|
||
var countryName sql.NullString
|
||
metric, err := scanExternalDashboardMetricWithPrefix(rows, &statDay, &countryCode, &countryName)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("scan external dashboard daily country breakdown for %s: %w", s.appCode, err)
|
||
}
|
||
row := metric.toMap()
|
||
row["label"] = statDay
|
||
row["stat_day"] = statDay
|
||
row["country_id"] = 0
|
||
row["country_code"] = countryCode
|
||
row["country_name"] = firstExternalDashboardValue(countryName.String, countryCode)
|
||
row["country"] = firstExternalDashboardValue(countryName.String, countryCode)
|
||
if countryFilter.RegionID > 0 {
|
||
row["region_id"] = countryFilter.RegionID
|
||
}
|
||
out = append(out, row)
|
||
}
|
||
if err := rows.Err(); err != nil {
|
||
return nil, fmt.Errorf("iterate external dashboard daily country breakdown for %s: %w", s.appCode, err)
|
||
}
|
||
return out, nil
|
||
}
|
||
|
||
func (s *MySQLExternalDashboardSource) periodMetricWhere(statTimezone string, startDate time.Time, endDate time.Time, countryFilter externalDashboardCountryFilter) (string, []any) {
|
||
whereSQL := `t.period_type = 'DAY'
|
||
AND t.stat_timezone = ?
|
||
AND t.sys_origin = ?
|
||
AND t.period_start_date >= ?
|
||
AND t.period_start_date < ?`
|
||
args := []any{statTimezone, s.sysOrigin, dashboardSQLDate(startDate), dashboardSQLDate(endDate)}
|
||
if !countryFilter.Applied {
|
||
return whereSQL, args
|
||
}
|
||
if len(countryFilter.Codes) == 0 {
|
||
return whereSQL + `
|
||
AND 1 = 0`, args
|
||
}
|
||
placeholders := make([]string, 0, len(countryFilter.Codes))
|
||
for _, code := range countryFilter.Codes {
|
||
placeholders = append(placeholders, "?")
|
||
args = append(args, code)
|
||
}
|
||
return whereSQL + `
|
||
AND t.country_code IN (` + strings.Join(placeholders, ",") + `)`, args
|
||
}
|
||
|
||
func scanExternalDashboardMetric(scanner externalMetricScanner) (externalDashboardMetric, error) {
|
||
metric := externalDashboardMetric{}
|
||
if err := scanner.Scan(externalDashboardScanDest(&metric)...); err != nil {
|
||
return externalDashboardMetric{}, err
|
||
}
|
||
metric.finalize()
|
||
return metric, nil
|
||
}
|
||
|
||
func scanExternalDashboardMetricWithPrefix(scanner externalMetricScanner, prefix ...any) (externalDashboardMetric, error) {
|
||
metric := externalDashboardMetric{}
|
||
dest := append(prefix, externalDashboardScanDest(&metric)...)
|
||
if err := scanner.Scan(dest...); err != nil {
|
||
return externalDashboardMetric{}, err
|
||
}
|
||
metric.finalize()
|
||
return metric, nil
|
||
}
|
||
|
||
func externalDashboardScanDest(metric *externalDashboardMetric) []any {
|
||
var newUserRecharge string
|
||
var officialRecharge string
|
||
var mifaPayRecharge string
|
||
var googleRecharge string
|
||
var dealerRecharge string
|
||
var userRecharge string
|
||
var newDealerRecharge string
|
||
var salaryExchange string
|
||
var salaryTransfer string
|
||
var giftConsume string
|
||
var luckyGiftTurnover string
|
||
var luckyGiftPayout string
|
||
var luckyGiftAnchorShare string
|
||
var gameTurnover string
|
||
var gamePayout string
|
||
|
||
return []any{
|
||
&metric.NewUsers,
|
||
&metric.ActiveUsers,
|
||
&metric.PaidUsers,
|
||
decimalMinorScanner(func(value int64) { metric.NewUserRechargeUSDMinor = value }, &newUserRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.OfficialRechargeUSDMinor = value }, &officialRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.MifaPayRechargeUSDMinor = value }, &mifaPayRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.GoogleRechargeUSDMinor = value }, &googleRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.CoinSellerRechargeUSDMinor = value }, &dealerRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.UserRechargeUSDMinor = value }, &userRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.NewDealerRechargeUSDMinor = value }, &newDealerRecharge),
|
||
decimalMinorScanner(func(value int64) { metric.SalaryExchangeUSDMinor = value }, &salaryExchange),
|
||
decimalMinorScanner(func(value int64) { metric.SalaryTransferUSDMinor = value }, &salaryTransfer),
|
||
decimalWholeScanner(func(value int64) { metric.GiftCoinSpent = value }, &giftConsume),
|
||
decimalWholeScanner(func(value int64) { metric.LuckyGiftTurnover = value }, &luckyGiftTurnover),
|
||
&metric.LuckyGiftPayers,
|
||
decimalWholeScanner(func(value int64) { metric.LuckyGiftPayout = value }, &luckyGiftPayout),
|
||
decimalWholeScanner(func(value int64) { metric.LuckyGiftAnchorShare = value }, &luckyGiftAnchorShare),
|
||
decimalWholeScanner(func(value int64) { metric.GameTurnover = value }, &gameTurnover),
|
||
&metric.GamePlayers,
|
||
decimalWholeScanner(func(value int64) { metric.GamePayout = value }, &gamePayout),
|
||
&metric.D1RetentionUsers,
|
||
&metric.D1RetentionBaseUsers,
|
||
&metric.D7RetentionUsers,
|
||
&metric.D7RetentionBaseUsers,
|
||
&metric.D30RetentionUsers,
|
||
&metric.D30RetentionBaseUsers,
|
||
&metric.RefreshedAt,
|
||
}
|
||
}
|
||
|
||
func (m *externalDashboardMetric) finalize() {
|
||
// worker 同时沉淀了官方、MifaPay、Google、用户充值和币商充值；这里取能覆盖拆分口径的最大无重复总额，避免 Google/MifaPay 被重复相加。
|
||
m.UserRechargeUSDMinor = maxInt64(m.UserRechargeUSDMinor, m.GoogleRechargeUSDMinor+m.MifaPayRechargeUSDMinor)
|
||
m.RechargeUSDMinor = maxInt64(
|
||
m.OfficialRechargeUSDMinor+m.MifaPayRechargeUSDMinor+m.CoinSellerRechargeUSDMinor,
|
||
maxInt64(m.UserRechargeUSDMinor+m.CoinSellerRechargeUSDMinor, m.GoogleRechargeUSDMinor+m.MifaPayRechargeUSDMinor+m.CoinSellerRechargeUSDMinor),
|
||
)
|
||
// dashboard-cdc-worker 的 daily_recharge_user 是充值去重用户；为兼容 Databi 旧字段名，同时输出 paid_users/recharge_users 两个同口径字段。
|
||
m.RechargeUsers = m.PaidUsers
|
||
m.ARPUUSDMinor = divideInt64(m.RechargeUSDMinor, m.ActiveUsers)
|
||
m.ARPPUUSDMinor = divideInt64(m.RechargeUSDMinor, m.PaidUsers)
|
||
m.PayerRate = ratioFloat64(m.PaidUsers, m.ActiveUsers)
|
||
m.PaidConversionRate = m.PayerRate
|
||
m.RechargeConversionRate = ratioFloat64(m.RechargeUsers, m.ActiveUsers)
|
||
m.GameProfit = m.GameTurnover - m.GamePayout
|
||
m.GameProfitRate = ratioFloat64(m.GameProfit, m.GameTurnover)
|
||
m.LuckyGiftProfit = m.LuckyGiftTurnover - m.LuckyGiftPayout
|
||
m.LuckyGiftPayoutRate = ratioFloat64(m.LuckyGiftPayout, m.LuckyGiftTurnover)
|
||
m.LuckyGiftProfitRate = ratioFloat64(m.LuckyGiftProfit, m.LuckyGiftTurnover)
|
||
m.D1RetentionRate = ratioFloat64(m.D1RetentionUsers, m.D1RetentionBaseUsers)
|
||
m.D7RetentionRate = ratioFloat64(m.D7RetentionUsers, m.D7RetentionBaseUsers)
|
||
m.D30RetentionRate = ratioFloat64(m.D30RetentionUsers, m.D30RetentionBaseUsers)
|
||
}
|
||
|
||
func (m externalDashboardMetric) toMap() map[string]any {
|
||
return map[string]any{
|
||
"active_users": m.ActiveUsers,
|
||
"arpu_usd_minor": m.ARPUUSDMinor,
|
||
"arppu_usd_minor": m.ARPPUUSDMinor,
|
||
"coin_seller_recharge_usd_minor": m.CoinSellerRechargeUSDMinor,
|
||
"coin_seller_stock_coin": nil,
|
||
"coin_seller_transfer_coin": nullableInt64(m.CoinSellerTransferCoin),
|
||
"coin_total": nullableInt64(m.CoinTotal),
|
||
"consumed_coin": nullableInt64(m.ConsumedCoin),
|
||
"consume_output_ratio": consumeOutputRatioValue(m.ConsumedCoin, m.OutputCoin),
|
||
"daily_active_user": m.ActiveUsers,
|
||
"d1_retention_base_users": retentionMetricValue(m.D1RetentionBaseUsers, m.D1RetentionBaseUsers),
|
||
"d1_retention_rate": retentionRateValue(m.D1RetentionUsers, m.D1RetentionBaseUsers),
|
||
"d1_retention_users": retentionMetricValue(m.D1RetentionUsers, m.D1RetentionBaseUsers),
|
||
"d7_retention_base_users": retentionMetricValue(m.D7RetentionBaseUsers, m.D7RetentionBaseUsers),
|
||
"d7_retention_rate": retentionRateValue(m.D7RetentionUsers, m.D7RetentionBaseUsers),
|
||
"d7_retention_users": retentionMetricValue(m.D7RetentionUsers, m.D7RetentionBaseUsers),
|
||
"d30_retention_base_users": retentionMetricValue(m.D30RetentionBaseUsers, m.D30RetentionBaseUsers),
|
||
"d30_retention_rate": retentionRateValue(m.D30RetentionUsers, m.D30RetentionBaseUsers),
|
||
"d30_retention_users": retentionMetricValue(m.D30RetentionUsers, m.D30RetentionBaseUsers),
|
||
"game_payout": m.GamePayout,
|
||
"game_players": m.GamePlayers,
|
||
"game_profit": m.GameProfit,
|
||
"game_profit_rate": m.GameProfitRate,
|
||
"game_turnover": m.GameTurnover,
|
||
"gift_coin_spent": m.GiftCoinSpent,
|
||
"consume_output_delta": consumeOutputDeltaValue(m.ConsumedCoin, m.OutputCoin),
|
||
"google_recharge_usd_minor": m.GoogleRechargeUSDMinor,
|
||
"lucky_gift_anchor_share": m.LuckyGiftAnchorShare,
|
||
"lucky_gift_payers": m.LuckyGiftPayers,
|
||
"lucky_gift_payout": m.LuckyGiftPayout,
|
||
"lucky_gift_payout_rate": m.LuckyGiftPayoutRate,
|
||
"lucky_gift_profit": m.LuckyGiftProfit,
|
||
"lucky_gift_profit_rate": m.LuckyGiftProfitRate,
|
||
"lucky_gift_turnover": m.LuckyGiftTurnover,
|
||
"mifapay_recharge_usd_minor": m.MifaPayRechargeUSDMinor,
|
||
"mic_online_ms": nil,
|
||
"avg_mic_online_ms": nil,
|
||
"manual_grant_coin": nil,
|
||
"new_dealer_recharge_usd_minor": m.NewDealerRechargeUSDMinor,
|
||
"new_user_recharge_usd_minor": m.NewUserRechargeUSDMinor,
|
||
"new_users": m.NewUsers,
|
||
"output_coin": nullableInt64(m.OutputCoin),
|
||
"paid_users": m.PaidUsers,
|
||
"paid_conversion_rate": m.PaidConversionRate,
|
||
"payer_rate": m.PayerRate,
|
||
"platform_grant_coin": nil,
|
||
"recharge_usd_minor": m.RechargeUSDMinor,
|
||
"recharge_users": m.RechargeUsers,
|
||
"recharge_conversion_rate": m.RechargeConversionRate,
|
||
"registered_users": m.NewUsers,
|
||
"registrations": m.NewUsers,
|
||
"room_lucky_gift_turnover": m.LuckyGiftTurnover,
|
||
"salary_exchange_usd_minor": m.SalaryExchangeUSDMinor,
|
||
"salary_transfer_coin": nil,
|
||
"salary_transfer_usd_minor": m.SalaryTransferUSDMinor,
|
||
"salary_usd_minor": nullableInt64(m.SalaryUSDMinor),
|
||
"super_lucky_gift_payout": nil,
|
||
"super_lucky_gift_profit": nil,
|
||
"super_lucky_gift_profit_rate": nil,
|
||
"super_lucky_gift_turnover": nil,
|
||
"total_recharge_usd_minor": m.RechargeUSDMinor,
|
||
"user_recharge_usd_minor": m.UserRechargeUSDMinor,
|
||
}
|
||
}
|
||
|
||
func (m externalDashboardMetric) retentionMap() map[string]any {
|
||
return map[string]any{
|
||
"day1_base_users": retentionMetricValue(m.D1RetentionBaseUsers, m.D1RetentionBaseUsers),
|
||
"day1_rate": retentionRateValue(m.D1RetentionUsers, m.D1RetentionBaseUsers),
|
||
"day1_users": retentionMetricValue(m.D1RetentionUsers, m.D1RetentionBaseUsers),
|
||
"day7_base_users": retentionMetricValue(m.D7RetentionBaseUsers, m.D7RetentionBaseUsers),
|
||
"day7_rate": retentionRateValue(m.D7RetentionUsers, m.D7RetentionBaseUsers),
|
||
"day7_users": retentionMetricValue(m.D7RetentionUsers, m.D7RetentionBaseUsers),
|
||
"day30_base_users": retentionMetricValue(m.D30RetentionBaseUsers, m.D30RetentionBaseUsers),
|
||
"day30_rate": retentionRateValue(m.D30RetentionUsers, m.D30RetentionBaseUsers),
|
||
"day30_users": retentionMetricValue(m.D30RetentionUsers, m.D30RetentionBaseUsers),
|
||
"registered_users": m.NewUsers,
|
||
}
|
||
}
|
||
|
||
func (m externalDashboardMetric) updatedAtMS() int64 {
|
||
if m.RefreshedAt.Valid {
|
||
return m.RefreshedAt.Time.UTC().UnixMilli()
|
||
}
|
||
return time.Now().UTC().UnixMilli()
|
||
}
|
||
|
||
func applyExternalDashboardDeltas(out map[string]any, current externalDashboardMetric, previous externalDashboardMetric) {
|
||
out["active_users_delta_rate"] = deltaRate(current.ActiveUsers, previous.ActiveUsers)
|
||
out["arpu_delta_rate"] = deltaRate(current.ARPUUSDMinor, previous.ARPUUSDMinor)
|
||
out["arppu_delta_rate"] = deltaRate(current.ARPPUUSDMinor, previous.ARPPUUSDMinor)
|
||
out["game_payout_delta_rate"] = deltaRate(current.GamePayout, previous.GamePayout)
|
||
out["game_profit_delta_rate"] = deltaRate(current.GameProfit, previous.GameProfit)
|
||
out["game_turnover_delta_rate"] = deltaRate(current.GameTurnover, previous.GameTurnover)
|
||
out["gift_coin_spent_delta_rate"] = deltaRate(current.GiftCoinSpent, previous.GiftCoinSpent)
|
||
out["lucky_gift_payout_delta_rate"] = deltaRate(current.LuckyGiftPayout, previous.LuckyGiftPayout)
|
||
out["lucky_gift_profit_delta_rate"] = deltaRate(current.LuckyGiftProfit, previous.LuckyGiftProfit)
|
||
out["lucky_gift_turnover_delta_rate"] = deltaRate(current.LuckyGiftTurnover, previous.LuckyGiftTurnover)
|
||
out["new_users_delta_rate"] = deltaRate(current.NewUsers, previous.NewUsers)
|
||
out["paid_users_delta_rate"] = deltaRate(current.PaidUsers, previous.PaidUsers)
|
||
out["payer_rate_delta_pp"] = percentagePointDelta(current.PayerRate, previous.PayerRate)
|
||
out["paid_conversion_rate_delta_pp"] = percentagePointDelta(current.PaidConversionRate, previous.PaidConversionRate)
|
||
out["recharge_delta_rate"] = deltaRate(current.RechargeUSDMinor, previous.RechargeUSDMinor)
|
||
out["recharge_conversion_rate_delta_pp"] = percentagePointDelta(current.RechargeConversionRate, previous.RechargeConversionRate)
|
||
out["user_recharge_delta_rate"] = deltaRate(current.UserRechargeUSDMinor, previous.UserRechargeUSDMinor)
|
||
}
|
||
|
||
func externalDashboardMetricSources() []map[string]any {
|
||
return []map[string]any{
|
||
{"field": "new_users", "available": true, "source": "country_dashboard_period_metric.country_new_user"},
|
||
{"field": "active_users", "available": true, "source": "country_dashboard_period_metric.daily_active_user"},
|
||
{"field": "paid_users", "available": true, "source": "country_dashboard_period_metric.daily_recharge_user"},
|
||
{"field": "recharge_users", "available": true, "source": "country_dashboard_period_metric.daily_recharge_user"},
|
||
{"field": "recharge_usd_minor", "available": true, "source": "country_dashboard_period_metric official/mifapay/google/dealer/user recharge"},
|
||
{"field": "new_user_recharge_usd_minor", "available": true, "source": "country_dashboard_period_metric.new_user_recharge"},
|
||
{"field": "coin_seller_recharge_usd_minor", "available": true, "source": "country_dashboard_period_metric.dealer_recharge"},
|
||
{"field": "coin_seller_stock_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有币商库存进货金币字段"},
|
||
{"field": "coin_seller_transfer_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有币商出货金币字段"},
|
||
{"field": "google_recharge_usd_minor", "available": true, "source": "country_dashboard_period_metric.google_recharge"},
|
||
{"field": "mifapay_recharge_usd_minor", "available": true, "source": "country_dashboard_period_metric.mifapay_recharge"},
|
||
{"field": "game_turnover", "available": true, "source": "country_dashboard_period_metric.game_total_flow/game_payout"},
|
||
{"field": "lucky_gift_turnover", "available": true, "source": "country_dashboard_period_metric.lucky_gift_total_flow/lucky_gift_payout"},
|
||
{"field": "super_lucky_gift_turnover", "available": false, "missing_reason": "dashboard-cdc-worker 没有超级幸运礼物独立奖池字段"},
|
||
{"field": "gift_coin_spent", "available": true, "source": "country_dashboard_period_metric.gift_consume"},
|
||
{"field": "coin_total", "available": false, "missing_reason": "dashboard-cdc-worker 没有金币余额快照"},
|
||
{"field": "consumed_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有消耗金币总口径，不能把局部流水硬拼成总数"},
|
||
{"field": "output_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有产出金币总口径"},
|
||
{"field": "consume_output_ratio", "available": false, "missing_reason": "缺少消耗金币和产出金币总口径"},
|
||
{"field": "platform_grant_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有平台发放金币字段"},
|
||
{"field": "manual_grant_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有人工发放金币字段"},
|
||
{"field": "salary_usd_minor", "available": false, "missing_reason": "dashboard-cdc-worker 只有 salary_exchange/salary_transfer 流水，没有存量工资余额快照"},
|
||
{"field": "salary_transfer_coin", "available": false, "missing_reason": "dashboard-cdc-worker 没有工资兑换金币字段"},
|
||
{"field": "avg_mic_online_ms", "available": false, "missing_reason": "dashboard-cdc-worker 没有麦上时长字段"},
|
||
{"field": "mic_online_ms", "available": false, "missing_reason": "dashboard-cdc-worker 没有麦上时长字段"},
|
||
{"field": "arpu_usd_minor", "available": true, "source": "recharge_usd_minor / daily_active_user"},
|
||
{"field": "arppu_usd_minor", "available": true, "source": "recharge_usd_minor / daily_recharge_user"},
|
||
{"field": "paid_conversion_rate", "available": true, "source": "daily_recharge_user / daily_active_user"},
|
||
{"field": "recharge_conversion_rate", "available": true, "source": "daily_recharge_user / daily_active_user"},
|
||
{"field": "retention.day1_rate", "available": true, "source": "country_dashboard_period_metric.d1_retention_user/base_user"},
|
||
{"field": "retention.day7_rate", "available": true, "source": "country_dashboard_period_metric.d7_retention_user/base_user"},
|
||
{"field": "retention.day30_rate", "available": true, "source": "country_dashboard_period_metric.d30_retention_user/base_user"},
|
||
}
|
||
}
|
||
|
||
func externalDashboardDateRange(query StatisticsQuery, location *time.Location) (time.Time, time.Time, int64, int64) {
|
||
now := time.Now().In(location)
|
||
startMS := query.StartMS
|
||
if startMS <= 0 {
|
||
startMS = localMidnight(now, location).UnixMilli()
|
||
}
|
||
endMS := query.EndMS
|
||
if endMS <= startMS {
|
||
endMS = time.UnixMilli(startMS).In(location).AddDate(0, 0, 1).UnixMilli()
|
||
}
|
||
startDate := localMidnight(time.UnixMilli(startMS).In(location), location)
|
||
endMoment := time.UnixMilli(endMS - 1).In(location)
|
||
endDate := localMidnight(endMoment, location).AddDate(0, 0, 1)
|
||
return startDate, endDate, startDate.UnixMilli(), endDate.UnixMilli()
|
||
}
|
||
|
||
func localMidnight(t time.Time, location *time.Location) time.Time {
|
||
local := t.In(location)
|
||
return time.Date(local.Year(), local.Month(), local.Day(), 0, 0, 0, 0, location)
|
||
}
|
||
|
||
func dashboardSQLDate(t time.Time) string {
|
||
return t.Format("2006-01-02")
|
||
}
|
||
|
||
func countCalendarDays(start time.Time, end time.Time) int {
|
||
days := 0
|
||
for day := start; day.Before(end); day = day.AddDate(0, 0, 1) {
|
||
days++
|
||
}
|
||
if days <= 0 {
|
||
return 1
|
||
}
|
||
return days
|
||
}
|
||
|
||
type externalDecimalScanner struct {
|
||
raw *string
|
||
assign func(int64)
|
||
whole bool
|
||
}
|
||
|
||
func decimalMinorScanner(assign func(int64), raw *string) sql.Scanner {
|
||
return externalDecimalScanner{raw: raw, assign: assign}
|
||
}
|
||
|
||
func decimalWholeScanner(assign func(int64), raw *string) sql.Scanner {
|
||
return externalDecimalScanner{raw: raw, assign: assign, whole: true}
|
||
}
|
||
|
||
func (s externalDecimalScanner) Scan(value any) error {
|
||
var raw string
|
||
switch typed := value.(type) {
|
||
case nil:
|
||
raw = "0"
|
||
case []byte:
|
||
raw = string(typed)
|
||
case string:
|
||
raw = typed
|
||
default:
|
||
raw = fmt.Sprint(typed)
|
||
}
|
||
raw = strings.TrimSpace(raw)
|
||
if raw == "" || raw == "<nil>" {
|
||
raw = "0"
|
||
}
|
||
if s.raw != nil {
|
||
*s.raw = raw
|
||
}
|
||
minor, err := decimalTextToMinor(raw)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if s.whole {
|
||
minor = roundMinorToWhole(minor)
|
||
}
|
||
s.assign(minor)
|
||
return nil
|
||
}
|
||
|
||
func decimalTextToMinor(raw string) (int64, error) {
|
||
text := strings.TrimSpace(raw)
|
||
if text == "" {
|
||
return 0, nil
|
||
}
|
||
sign := int64(1)
|
||
if strings.HasPrefix(text, "-") {
|
||
sign = -1
|
||
text = strings.TrimPrefix(text, "-")
|
||
} else {
|
||
text = strings.TrimPrefix(text, "+")
|
||
}
|
||
wholeText, fractionText, _ := strings.Cut(text, ".")
|
||
if wholeText == "" {
|
||
wholeText = "0"
|
||
}
|
||
for len(fractionText) < 2 {
|
||
fractionText += "0"
|
||
}
|
||
if len(fractionText) > 2 {
|
||
fractionText = fractionText[:2]
|
||
}
|
||
whole, err := strconv.ParseInt(wholeText, 10, 64)
|
||
if err != nil {
|
||
return 0, fmt.Errorf("parse decimal whole %q: %w", raw, err)
|
||
}
|
||
fraction, err := strconv.ParseInt(fractionText, 10, 64)
|
||
if err != nil {
|
||
return 0, fmt.Errorf("parse decimal fraction %q: %w", raw, err)
|
||
}
|
||
return sign * (whole*100 + fraction), nil
|
||
}
|
||
|
||
func roundMinorToWhole(minor int64) int64 {
|
||
if minor < 0 {
|
||
return -roundMinorToWhole(-minor)
|
||
}
|
||
return (minor + 50) / 100
|
||
}
|
||
|
||
func divideInt64(numerator int64, denominator int64) int64 {
|
||
if denominator == 0 {
|
||
return 0
|
||
}
|
||
return int64(math.Round(float64(numerator) / float64(denominator)))
|
||
}
|
||
|
||
func ratioFloat64(numerator int64, denominator int64) float64 {
|
||
if denominator == 0 {
|
||
return 0
|
||
}
|
||
return float64(numerator) / float64(denominator)
|
||
}
|
||
|
||
func retentionMetricValue(value int64, base int64) any {
|
||
if base <= 0 {
|
||
return nil
|
||
}
|
||
return value
|
||
}
|
||
|
||
func retentionRateValue(users int64, base int64) any {
|
||
if base <= 0 {
|
||
return nil
|
||
}
|
||
return ratioFloat64(users, base)
|
||
}
|
||
|
||
func deltaRate(current int64, previous int64) float64 {
|
||
if previous == 0 {
|
||
if current == 0 {
|
||
return 0
|
||
}
|
||
return 1
|
||
}
|
||
return float64(current-previous) / float64(previous)
|
||
}
|
||
|
||
func percentagePointDelta(current float64, previous float64) float64 {
|
||
return (current - previous) * 100
|
||
}
|
||
|
||
func maxInt64(left int64, right int64) int64 {
|
||
if left > right {
|
||
return left
|
||
}
|
||
return right
|
||
}
|
||
|
||
func int64Ptr(value int64) *int64 {
|
||
out := value
|
||
return &out
|
||
}
|
||
|
||
func nullableInt64(value *int64) any {
|
||
if value == nil {
|
||
return nil
|
||
}
|
||
return *value
|
||
}
|
||
|
||
func consumeOutputRatioValue(consumed *int64, output *int64) any {
|
||
if consumed == nil || output == nil {
|
||
return nil
|
||
}
|
||
return ratioFloat64(*consumed, *output)
|
||
}
|
||
|
||
func consumeOutputDeltaValue(consumed *int64, output *int64) any {
|
||
if consumed == nil || output == nil {
|
||
return nil
|
||
}
|
||
return *consumed - *output
|
||
}
|
||
|
||
func firstExternalDashboardValue(values ...string) string {
|
||
for _, value := range values {
|
||
if trimmed := strings.TrimSpace(value); trimmed != "" {
|
||
return trimmed
|
||
}
|
||
}
|
||
return ""
|
||
}
|
||
|
||
func compactExternalDashboardRegionResolvers(resolvers []ExternalDashboardRegionResolver) []ExternalDashboardRegionResolver {
|
||
out := make([]ExternalDashboardRegionResolver, 0, len(resolvers))
|
||
for _, resolver := range resolvers {
|
||
if resolver != nil {
|
||
out = append(out, resolver)
|
||
}
|
||
}
|
||
return out
|
||
}
|
||
|
||
func normalizeExternalCountryCodes(codes []string) []string {
|
||
out := make([]string, 0, len(codes))
|
||
seen := map[string]struct{}{}
|
||
for _, rawCode := range codes {
|
||
code := strings.ToUpper(strings.TrimSpace(rawCode))
|
||
if code == "" {
|
||
continue
|
||
}
|
||
if _, ok := seen[code]; ok {
|
||
continue
|
||
}
|
||
seen[code] = struct{}{}
|
||
out = append(out, code)
|
||
}
|
||
sort.Strings(out)
|
||
return out
|
||
}
|