459 lines
21 KiB
Go
459 lines
21 KiB
Go
package storage
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"strconv"
|
|
"strings"
|
|
"time"
|
|
)
|
|
|
|
func (r *MySQLRepository) replaceGameMetricDay(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
if _, err := tx.ExecContext(ctx, `
|
|
UPDATE country_dashboard_period_metric
|
|
SET game_total_flow = 0, game_user = 0, game_payout = 0, refreshed_at = NOW()
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ?`,
|
|
dayKey, statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM country_dashboard_period_user_metric
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ?
|
|
AND metric_type = ?`, dayKey, statTimezone, sysOrigin, "GAME_USER"); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM country_dashboard_game_period_user_metric
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ?`,
|
|
dayKey, statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM country_dashboard_game_period_metric
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ?`,
|
|
dayKey, statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
if err := r.insertWalletGamePeriodAmounts(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd); err != nil {
|
|
return err
|
|
}
|
|
if err := r.insertLuckyBoxGamePeriodAmounts(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd); err != nil {
|
|
return err
|
|
}
|
|
if err := r.refreshOverviewGameAmounts(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd); err != nil {
|
|
return err
|
|
}
|
|
if err := r.insertWalletGamePeriodUsers(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd); err != nil {
|
|
return err
|
|
}
|
|
if err := r.insertLuckyBoxGamePeriodUsers(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd); err != nil {
|
|
return err
|
|
}
|
|
if err := r.refreshOverviewGameUsers(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd); err != nil {
|
|
return err
|
|
}
|
|
return r.refreshGameDimensionUsers(ctx, tx, statTimezone, sysOrigin, dayStart, dayEnd)
|
|
}
|
|
|
|
func (r *MySQLRepository) insertWalletGamePeriodAmounts(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
sourceSQL, sourceArgs := r.walletGameSourceSQL(sysOrigin, dayStart, dayEnd)
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
args := append([]interface{}{
|
|
dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
r.cfg.Dashboard.UnknownCountryCode, r.cfg.Dashboard.UnknownCountryCode,
|
|
}, sourceArgs...)
|
|
query := `
|
|
INSERT INTO country_dashboard_game_period_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, game_name,
|
|
consume_amount, payout_amount, profit_amount, order_count, refreshed_at
|
|
)
|
|
SELECT 'DAY', ?, ?, ?, ?, ?,
|
|
t.sys_origin,
|
|
IFNULL(NULLIF(u.country_code, ''), ?) AS country_code,
|
|
IFNULL(NULLIF(u.country_name, ''), IFNULL(NULLIF(u.country_code, ''), ?)) AS country_name,
|
|
` + walletGameProviderExpr() + ` AS game_provider,
|
|
` + walletGameIDExpr() + ` AS game_id,
|
|
MAX(` + walletGameNameExpr() + `) AS game_name,
|
|
IFNULL(SUM(CASE WHEN t.type = 1 THEN t.penny_amount ELSE 0 END) / 100.00, 0) AS consume_amount,
|
|
IFNULL(SUM(CASE WHEN t.type = 0 THEN t.penny_amount ELSE 0 END) / 100.00, 0) AS payout_amount,
|
|
IFNULL((SUM(CASE WHEN t.type = 1 THEN t.penny_amount ELSE 0 END)
|
|
- SUM(CASE WHEN t.type = 0 THEN t.penny_amount ELSE 0 END)) / 100.00, 0) AS profit_amount,
|
|
COUNT(1) AS order_count,
|
|
NOW()
|
|
FROM (` + sourceSQL + `) t
|
|
INNER JOIN ` + quoteIdent(r.cfg.MySQL.Schema) + `.user_base_info u ON u.id = t.user_id
|
|
` + r.walletGameJoinCatalogs() + `
|
|
WHERE ` + walletGameEventWhere() + `
|
|
AND (u.is_del = 0 OR u.is_del IS NULL)
|
|
AND u.origin_sys = t.sys_origin
|
|
GROUP BY t.sys_origin, country_code, country_name, game_provider, game_id
|
|
ON DUPLICATE KEY UPDATE
|
|
country_name = VALUES(country_name),
|
|
game_name = VALUES(game_name),
|
|
consume_amount = consume_amount + VALUES(consume_amount),
|
|
payout_amount = payout_amount + VALUES(payout_amount),
|
|
profit_amount = profit_amount + VALUES(profit_amount),
|
|
order_count = order_count + VALUES(order_count),
|
|
refreshed_at = NOW()`
|
|
_, err := tx.ExecContext(ctx, query, args...)
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) insertLuckyBoxGamePeriodAmounts(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO country_dashboard_game_period_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, game_name,
|
|
consume_amount, payout_amount, profit_amount, order_count, refreshed_at
|
|
)
|
|
SELECT 'DAY', ?, ?, ?, ?, ?,
|
|
t.sys_origin,
|
|
IFNULL(NULLIF(u.country_code, ''), ?) AS country_code,
|
|
IFNULL(NULLIF(u.country_name, ''), IFNULL(NULLIF(u.country_code, ''), ?)) AS country_name,
|
|
'INTERNAL', 'LUCKY_BOX', 'Lucky Box',
|
|
IFNULL(SUM(t.pay_amount), 0),
|
|
IFNULL(SUM(t.gift_amount), 0),
|
|
IFNULL(SUM(t.pay_amount), 0) - IFNULL(SUM(t.gift_amount), 0),
|
|
COUNT(1),
|
|
NOW()
|
|
FROM `+quoteIdent(r.cfg.MySQL.Schema)+`.game_lucky_box_count t
|
|
INNER JOIN `+quoteIdent(r.cfg.MySQL.Schema)+`.user_base_info u ON u.id = t.user_id
|
|
WHERE t.create_time >= ? AND t.create_time < ?
|
|
AND t.sys_origin = ?
|
|
AND (u.is_del = 0 OR u.is_del IS NULL)
|
|
AND u.origin_sys = t.sys_origin
|
|
GROUP BY t.sys_origin, country_code, country_name
|
|
ON DUPLICATE KEY UPDATE
|
|
country_name = VALUES(country_name),
|
|
game_name = VALUES(game_name),
|
|
consume_amount = consume_amount + VALUES(consume_amount),
|
|
payout_amount = payout_amount + VALUES(payout_amount),
|
|
profit_amount = profit_amount + VALUES(profit_amount),
|
|
order_count = order_count + VALUES(order_count),
|
|
refreshed_at = NOW()`, dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
r.cfg.Dashboard.UnknownCountryCode, r.cfg.Dashboard.UnknownCountryCode,
|
|
mysqlTime(dayStart), mysqlTime(dayEnd), sysOrigin)
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) refreshOverviewGameAmounts(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO country_dashboard_period_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_total_flow, game_payout, refreshed_at
|
|
)
|
|
SELECT 'DAY', ?, ?, ?, ?, ?,
|
|
sys_origin, country_code, MAX(country_name),
|
|
IFNULL(SUM(consume_amount), 0), IFNULL(SUM(payout_amount), 0), NOW()
|
|
FROM country_dashboard_game_period_metric
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ?
|
|
GROUP BY sys_origin, country_code
|
|
ON DUPLICATE KEY UPDATE
|
|
country_name = VALUES(country_name),
|
|
game_total_flow = VALUES(game_total_flow),
|
|
game_payout = VALUES(game_payout),
|
|
refreshed_at = NOW()`, dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
dayKey, statTimezone, sysOrigin)
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) insertWalletGamePeriodUsers(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
sourceSQL, sourceArgs := r.walletGameSourceSQL(sysOrigin, dayStart, dayEnd)
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
args := append([]interface{}{
|
|
dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
r.cfg.Dashboard.UnknownCountryCode, r.cfg.Dashboard.UnknownCountryCode,
|
|
}, sourceArgs...)
|
|
query := `
|
|
INSERT IGNORE INTO country_dashboard_game_period_user_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, user_id
|
|
)
|
|
SELECT DISTINCT 'DAY', ?, ?, ?, ?, ?,
|
|
t.sys_origin,
|
|
IFNULL(NULLIF(u.country_code, ''), ?) AS country_code,
|
|
IFNULL(NULLIF(u.country_name, ''), IFNULL(NULLIF(u.country_code, ''), ?)) AS country_name,
|
|
` + walletGameProviderExpr() + ` AS game_provider,
|
|
` + walletGameIDExpr() + ` AS game_id,
|
|
t.user_id
|
|
FROM (` + sourceSQL + `) t
|
|
INNER JOIN ` + quoteIdent(r.cfg.MySQL.Schema) + `.user_base_info u ON u.id = t.user_id
|
|
` + r.walletGameJoinCatalogs() + `
|
|
WHERE ` + walletGameEventWhere() + `
|
|
AND (u.is_del = 0 OR u.is_del IS NULL)
|
|
AND u.origin_sys = t.sys_origin`
|
|
_, err := tx.ExecContext(ctx, query, args...)
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) insertLuckyBoxGamePeriodUsers(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT IGNORE INTO country_dashboard_game_period_user_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, user_id
|
|
)
|
|
SELECT DISTINCT 'DAY', ?, ?, ?, ?, ?,
|
|
t.sys_origin,
|
|
IFNULL(NULLIF(u.country_code, ''), ?) AS country_code,
|
|
IFNULL(NULLIF(u.country_name, ''), IFNULL(NULLIF(u.country_code, ''), ?)) AS country_name,
|
|
'INTERNAL', 'LUCKY_BOX', t.user_id
|
|
FROM `+quoteIdent(r.cfg.MySQL.Schema)+`.game_lucky_box_count t
|
|
INNER JOIN `+quoteIdent(r.cfg.MySQL.Schema)+`.user_base_info u ON u.id = t.user_id
|
|
WHERE t.create_time >= ? AND t.create_time < ?
|
|
AND t.sys_origin = ?
|
|
AND (u.is_del = 0 OR u.is_del IS NULL)
|
|
AND u.origin_sys = t.sys_origin`, dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
r.cfg.Dashboard.UnknownCountryCode, r.cfg.Dashboard.UnknownCountryCode,
|
|
mysqlTime(dayStart), mysqlTime(dayEnd), sysOrigin)
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) refreshOverviewGameUsers(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
if _, err := tx.ExecContext(ctx, `
|
|
INSERT IGNORE INTO country_dashboard_period_user_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, user_id, metric_type
|
|
)
|
|
SELECT DISTINCT period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, user_id, ?
|
|
FROM country_dashboard_game_period_user_metric
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ?`,
|
|
"GAME_USER", dayKey, statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO country_dashboard_period_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_user, refreshed_at
|
|
)
|
|
SELECT 'DAY', ?, ?, ?, ?, ?, sys_origin, country_code, MAX(country_name), COUNT(1), NOW()
|
|
FROM country_dashboard_period_user_metric
|
|
WHERE period_type = 'DAY' AND period_key = ? AND stat_timezone = ? AND sys_origin = ? AND metric_type = ?
|
|
GROUP BY sys_origin, country_code
|
|
ON DUPLICATE KEY UPDATE
|
|
country_name = VALUES(country_name),
|
|
game_user = VALUES(game_user),
|
|
refreshed_at = NOW()`, dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
dayKey, statTimezone, sysOrigin, "GAME_USER")
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) refreshGameDimensionUsers(ctx context.Context, tx *sql.Tx,
|
|
statTimezone string, sysOrigin string, dayStart, dayEnd time.Time) error {
|
|
dayKey := dayStart.Format("2006-01-02")
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO country_dashboard_game_period_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, game_name, user_count, refreshed_at
|
|
)
|
|
SELECT 'DAY', ?, ?, ?, ?, ?,
|
|
u.sys_origin, u.country_code, MAX(u.country_name), u.game_provider, u.game_id,
|
|
COALESCE(MAX(m.game_name), u.game_id), COUNT(1), NOW()
|
|
FROM country_dashboard_game_period_user_metric u
|
|
LEFT JOIN country_dashboard_game_period_metric m
|
|
ON m.period_type = u.period_type AND m.period_key = u.period_key AND m.stat_timezone = u.stat_timezone
|
|
AND m.sys_origin = u.sys_origin AND m.country_code = u.country_code
|
|
AND m.game_provider = u.game_provider AND m.game_id = u.game_id
|
|
WHERE u.period_type = 'DAY' AND u.period_key = ? AND u.stat_timezone = ? AND u.sys_origin = ?
|
|
GROUP BY u.sys_origin, u.country_code, u.game_provider, u.game_id
|
|
ON DUPLICATE KEY UPDATE
|
|
country_name = VALUES(country_name),
|
|
game_name = VALUES(game_name),
|
|
user_count = VALUES(user_count),
|
|
refreshed_at = NOW()`, dayKey, statTimezone, dayKey, dayStart, dayEnd,
|
|
dayKey, statTimezone, sysOrigin)
|
|
return err
|
|
}
|
|
|
|
func (r *MySQLRepository) rebuildAllGamePeriodFromDays(ctx context.Context, tx *sql.Tx, statTimezone string) error {
|
|
for _, sysOrigin := range r.cfg.Dashboard.SysOrigins {
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM country_dashboard_game_period_user_metric
|
|
WHERE period_type = 'ALL' AND period_key = 'ALL' AND stat_timezone = ? AND sys_origin = ?`,
|
|
statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
INSERT IGNORE INTO country_dashboard_game_period_user_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, user_id
|
|
)
|
|
SELECT DISTINCT 'ALL', 'ALL', ?, '全部', NULL, NULL,
|
|
sys_origin, country_code, country_name, game_provider, game_id, user_id
|
|
FROM country_dashboard_game_period_user_metric
|
|
WHERE period_type = 'DAY' AND stat_timezone = ? AND sys_origin = ?`,
|
|
statTimezone, statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM country_dashboard_game_period_metric
|
|
WHERE period_type = 'ALL' AND period_key = 'ALL' AND stat_timezone = ? AND sys_origin = ?`,
|
|
statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
INSERT INTO country_dashboard_game_period_metric (
|
|
period_type, period_key, stat_timezone, period_name, period_start_date, period_end_date,
|
|
sys_origin, country_code, country_name, game_provider, game_id, game_name,
|
|
consume_amount, payout_amount, profit_amount, user_count, order_count, refreshed_at
|
|
)
|
|
SELECT 'ALL', 'ALL', ?, '全部', NULL, NULL,
|
|
d.sys_origin, d.country_code, MAX(d.country_name), d.game_provider, d.game_id, MAX(d.game_name),
|
|
IFNULL(SUM(d.consume_amount), 0),
|
|
IFNULL(SUM(d.payout_amount), 0),
|
|
IFNULL(SUM(d.profit_amount), 0),
|
|
IFNULL(MAX(u.user_count), 0),
|
|
IFNULL(SUM(d.order_count), 0),
|
|
NOW()
|
|
FROM country_dashboard_game_period_metric d
|
|
LEFT JOIN (
|
|
SELECT country_code, game_provider, game_id, COUNT(1) AS user_count
|
|
FROM country_dashboard_game_period_user_metric
|
|
WHERE period_type = 'ALL' AND period_key = 'ALL' AND stat_timezone = ? AND sys_origin = ?
|
|
GROUP BY country_code, game_provider, game_id
|
|
) u ON u.country_code = d.country_code AND u.game_provider = d.game_provider AND u.game_id = d.game_id
|
|
WHERE d.period_type = 'DAY' AND d.stat_timezone = ? AND d.sys_origin = ?
|
|
GROUP BY d.sys_origin, d.country_code, d.game_provider, d.game_id`,
|
|
statTimezone, statTimezone, sysOrigin, statTimezone, sysOrigin); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *MySQLRepository) walletGameSourceSQL(sysOrigin string, start, end time.Time) (string, []interface{}) {
|
|
startMS := start.UnixMilli()
|
|
endMS := end.UnixMilli()
|
|
parts := make([]string, 0, 12)
|
|
args := make([]interface{}, 0, 36)
|
|
for i := 1; i <= 12; i++ {
|
|
parts = append(parts, `SELECT user_id, sys_origin, type, penny_amount, event_type, event_id
|
|
FROM `+quoteIdent(r.cfg.MySQL.WalletSchema)+`.`+quoteIdent("wallet_gold_asset_record_"+strconv.Itoa(i))+`
|
|
WHERE create_time >= ? AND create_time < ? AND sys_origin = ?`)
|
|
args = append(args, startMS, endMS, sysOrigin)
|
|
}
|
|
return strings.Join(parts, "\nUNION ALL\n"), args
|
|
}
|
|
|
|
func (r *MySQLRepository) walletGameJoinCatalogs() string {
|
|
schema := quoteIdent(r.cfg.MySQL.Schema)
|
|
idExpr := walletGameIDExpr()
|
|
return `
|
|
LEFT JOIN ` + schema + `.baishun_order_idempotency bo
|
|
ON bo.sys_origin = t.sys_origin COLLATE utf8mb4_0900_ai_ci
|
|
AND bo.wallet_event_id = t.event_id COLLATE utf8mb4_0900_ai_ci
|
|
LEFT JOIN ` + schema + `.game_open_callback_log go
|
|
ON go.sys_origin = t.sys_origin COLLATE utf8mb4_0900_ai_ci
|
|
AND t.event_id COLLATE utf8mb4_0900_ai_ci = CONCAT('GAME_OPEN:', go.order_id)
|
|
LEFT JOIN ` + schema + `.baishun_game_catalog bc
|
|
ON (bo.vendor_game_id IS NOT NULL OR t.event_type LIKE 'BAISHUN_GAME_%')
|
|
AND bc.sys_origin = t.sys_origin COLLATE utf8mb4_0900_ai_ci
|
|
AND bc.profile = 'PROD'
|
|
AND bc.vendor_game_id = CAST(` + idExpr + ` AS UNSIGNED)
|
|
LEFT JOIN ` + schema + `.lingxian_game_catalog lc
|
|
ON (go.game_id IS NOT NULL OR t.event_type LIKE 'GAME_OPEN_%'
|
|
OR t.event_type LIKE 'YOMI_GAME_%' OR t.event_type LIKE 'HKYS_GAME_%' OR t.event_type LIKE 'HOT_GAME_%')
|
|
AND lc.sys_origin = t.sys_origin COLLATE utf8mb4_0900_ai_ci
|
|
AND lc.profile = 'PROD'
|
|
AND lc.vendor_game_id = ` + idExpr + ` COLLATE utf8mb4_0900_ai_ci
|
|
LEFT JOIN ` + schema + `.sys_game_list_config sg
|
|
ON sg.sys_origin = t.sys_origin COLLATE utf8mb4_0900_ai_ci
|
|
AND sg.game_id = ` + idExpr + ` COLLATE utf8mb4_0900_ai_ci`
|
|
}
|
|
|
|
func walletGameEventWhere() string {
|
|
return `(
|
|
t.event_type IN (
|
|
'GAME_SLOT_MACHINE', 'GAME_BARBECUE', 'TURNTABLE_GAME', 'LUDO_GAME',
|
|
'GAME_BURST_CRYSTAL', 'EGG', 'FRUIT_GAME', 'DOUBLE_LAYER_FRUIT',
|
|
'DOUBLE_LAYER_BARBECUE', 'GAME_FRUIT_BET', 'GAME_FRUIT_AWARD',
|
|
'GAME_BACK', 'GAME_WINNING', 'LUDO_VICTORY', 'LUDO_REFUND',
|
|
'GAME_LUDO_REFUND', 'USD_GAME_WIN', 'USD_GAME_REFUND',
|
|
'USD_GAME_REFUND_DRAW', 'TEEN_PATTI', 'BET_TEEN_PATTI',
|
|
'LOTTERY_NUMBER_BET', 'LOTTERY_REWARD'
|
|
)
|
|
OR t.event_type LIKE 'BAISHUN_GAME%'
|
|
OR t.event_type LIKE 'GAME_OPEN_%'
|
|
OR t.event_type LIKE 'YOMI_GAME%'
|
|
OR t.event_type LIKE 'HKYS_GAME%'
|
|
OR t.event_type LIKE 'HOT_GAME%'
|
|
)
|
|
AND t.event_type NOT IN ('LUCKY_GIFT', 'LUCKY_GIFT_GOLD_REWARD', 'LUCKY_BOX_GAME', 'NEW_LUCKY_BOX_GAME', 'GAME_RAKE')
|
|
AND t.event_type NOT LIKE 'GIVE_GIFT%'
|
|
AND t.event_type NOT LIKE 'ACCEPT_GIFT%'`
|
|
}
|
|
|
|
func walletGameProviderExpr() string {
|
|
return `CASE
|
|
WHEN bo.vendor_game_id IS NOT NULL THEN 'BAISHUN'
|
|
WHEN go.game_id IS NOT NULL THEN 'GAME_OPEN'
|
|
WHEN t.event_type LIKE 'BAISHUN_GAME%' THEN 'BAISHUN'
|
|
WHEN t.event_type LIKE 'GAME_OPEN_%' THEN 'GAME_OPEN'
|
|
WHEN t.event_type LIKE 'YOMI_GAME%' THEN 'YOMI'
|
|
WHEN t.event_type LIKE 'HKYS_GAME%' THEN 'HKYS'
|
|
WHEN t.event_type LIKE 'HOT_GAME%' THEN 'HOT'
|
|
ELSE 'INTERNAL'
|
|
END`
|
|
}
|
|
|
|
func walletGameIDExpr() string {
|
|
return `CASE
|
|
WHEN bo.vendor_game_id IS NOT NULL THEN CAST(bo.vendor_game_id AS CHAR)
|
|
WHEN go.game_id IS NOT NULL THEN go.game_id
|
|
WHEN t.event_type LIKE 'BAISHUN_GAME_%' THEN COALESCE(NULLIF(SUBSTRING(t.event_type, 14), ''), t.event_type)
|
|
WHEN t.event_type LIKE 'GAME_OPEN_%' THEN COALESCE(NULLIF(SUBSTRING(t.event_type, 11), ''), t.event_type)
|
|
WHEN t.event_type LIKE 'YOMI_GAME_%' THEN COALESCE(NULLIF(SUBSTRING(t.event_type, 11), ''), t.event_type)
|
|
WHEN t.event_type LIKE 'HKYS_GAME_%' THEN COALESCE(NULLIF(SUBSTRING(t.event_type, 11), ''), t.event_type)
|
|
WHEN t.event_type LIKE 'HOT_GAME_%' THEN COALESCE(NULLIF(SUBSTRING(t.event_type, 10), ''), t.event_type)
|
|
WHEN t.event_type IN ('FRUIT_GAME', 'DOUBLE_LAYER_FRUIT', 'GAME_FRUIT_BET', 'GAME_FRUIT_AWARD') THEN 'FRUIT_GAME'
|
|
WHEN t.event_type IN ('LUDO_GAME', 'LUDO_VICTORY', 'LUDO_REFUND', 'GAME_LUDO_REFUND') THEN 'LUDO_GAME'
|
|
WHEN t.event_type IN ('GAME_BARBECUE', 'DOUBLE_LAYER_BARBECUE') THEN 'BARBECUE_GAME'
|
|
WHEN t.event_type IN ('GAME_SLOT_MACHINE') THEN 'SLOT_MACHINE'
|
|
WHEN t.event_type IN ('TURNTABLE_GAME') THEN 'TURNTABLE_GAME'
|
|
WHEN t.event_type IN ('GAME_BURST_CRYSTAL') THEN 'BURST_CRYSTAL'
|
|
WHEN t.event_type IN ('TEEN_PATTI', 'BET_TEEN_PATTI') THEN 'TEEN_PATTI'
|
|
WHEN t.event_type IN ('LOTTERY_NUMBER_BET', 'LOTTERY_REWARD') THEN 'LOTTERY_NUMBER'
|
|
ELSE t.event_type
|
|
END`
|
|
}
|
|
|
|
func walletGameNameFallbackExpr() string {
|
|
return `CASE
|
|
WHEN t.event_type IN ('FRUIT_GAME', 'DOUBLE_LAYER_FRUIT', 'GAME_FRUIT_BET', 'GAME_FRUIT_AWARD') THEN 'Fruit Game'
|
|
WHEN t.event_type IN ('LUDO_GAME', 'LUDO_VICTORY', 'LUDO_REFUND', 'GAME_LUDO_REFUND') THEN 'Ludo Game'
|
|
WHEN t.event_type IN ('GAME_BARBECUE', 'DOUBLE_LAYER_BARBECUE') THEN 'Barbecue Game'
|
|
WHEN t.event_type = 'GAME_SLOT_MACHINE' THEN 'Slot Machine'
|
|
WHEN t.event_type = 'TURNTABLE_GAME' THEN 'Turntable Game'
|
|
WHEN t.event_type = 'GAME_BURST_CRYSTAL' THEN 'Burst Crystal'
|
|
WHEN t.event_type IN ('TEEN_PATTI', 'BET_TEEN_PATTI') THEN 'Teen Patti'
|
|
WHEN t.event_type IN ('LOTTERY_NUMBER_BET', 'LOTTERY_REWARD') THEN 'Lottery Number'
|
|
ELSE t.event_type
|
|
END`
|
|
}
|
|
|
|
func walletGameNameExpr() string {
|
|
return `COALESCE(
|
|
NULLIF(CONVERT(bc.name USING utf8mb4) COLLATE utf8mb4_unicode_ci, CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci),
|
|
NULLIF(CONVERT(lc.name USING utf8mb4) COLLATE utf8mb4_unicode_ci, CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci),
|
|
NULLIF(CONVERT(sg.name USING utf8mb4) COLLATE utf8mb4_unicode_ci, CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci),
|
|
CONVERT(` + walletGameNameFallbackExpr() + ` USING utf8mb4) COLLATE utf8mb4_unicode_ci
|
|
)`
|
|
}
|
|
|
|
func mysqlTime(value time.Time) string {
|
|
return value.Format("2006-01-02 15:04:05")
|
|
}
|