373 lines
11 KiB
Go
373 lines
11 KiB
Go
package admindashboard
|
|
|
|
import (
|
|
"context"
|
|
"sort"
|
|
"time"
|
|
|
|
"hfb_sys/backend/internal/listingstatus"
|
|
"hfb_sys/backend/internal/model"
|
|
"hfb_sys/backend/internal/timeutil"
|
|
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
const (
|
|
listingDailyTrendDays = 7
|
|
listingChannelWebsite = "站内发布"
|
|
listingChannelExternalUnknown = "未填写"
|
|
)
|
|
|
|
type Repository struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewRepository(db *gorm.DB) *Repository {
|
|
return &Repository{db: db}
|
|
}
|
|
|
|
func (r *Repository) Summary(ctx context.Context) (*DashboardDTO, error) {
|
|
db := r.db.WithContext(ctx)
|
|
loc := timeutil.ShanghaiLocation()
|
|
now := timeutil.ShanghaiNow()
|
|
today := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, loc)
|
|
metrics := MetricsDTO{}
|
|
pending := PendingDTO{}
|
|
|
|
if err := db.Model(&model.User{}).Count(&metrics.TotalUsers).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.User{}).Where("realname_status = ?", "verified").Count(&metrics.VerifiedUsers).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalListing{}).Count(&metrics.TotalListings).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalListing{}).Where("status = ? AND review_status = ?", "published", "approved").Count(&metrics.PublishedListings).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalOrder{}).Count(&metrics.TotalOrders).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalOrder{}).Where("status = ?", "renting").Count(&metrics.RentingOrders).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalOrder{}).Where("created_at >= ?", today).Count(&metrics.TodayOrders).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.WalletLedger{}).
|
|
Select("COALESCE(SUM(amount_cent), 0)").
|
|
Where("created_at >= ?", today).
|
|
Scan(&metrics.TodayLedgerAmountCent).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalListing{}).Where("review_status = ?", "pending").Count(&pending.ListingReviews).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.Dispute{}).Where("status IN ?", []string{"open", "processing"}).Count(&pending.Disputes).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalOrder{}).Where("status = ? AND handoff_status IN ?", "pending_handoff", []string{"pending_owner", "pending_renter_confirm"}).Count(&pending.PendingHandoffs).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
if err := db.Model(&model.RentalOrder{}).Where("status IN ?", []string{"pending_checkout_confirm", "pending_checkout_accept"}).Count(&pending.PendingReturnConfirms).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
listingDaily, err := r.listingDailyOverview(ctx, now, listingDailyTrendDays)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
recentOrders, err := r.recentOrders(ctx)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
recentDisputes, err := r.recentDisputes(ctx)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &DashboardDTO{
|
|
Metrics: metrics,
|
|
Pending: pending,
|
|
ListingDaily: listingDaily,
|
|
RecentOrders: recentOrders,
|
|
RecentDisputes: recentDisputes,
|
|
GeneratedAt: now,
|
|
}, nil
|
|
}
|
|
|
|
func (r *Repository) listingDailyOverview(ctx context.Context, now time.Time, days int) (ListingDailyOverviewDTO, error) {
|
|
if days <= 0 {
|
|
days = listingDailyTrendDays
|
|
}
|
|
loc := timeutil.ShanghaiLocation()
|
|
now = now.In(loc)
|
|
// 右开区间 [start, end)
|
|
end := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, loc).AddDate(0, 0, 1)
|
|
start := end.AddDate(0, 0, -days)
|
|
|
|
type eventRow struct {
|
|
EventType string
|
|
Source string
|
|
SourceChannel string
|
|
CreatedAt time.Time
|
|
}
|
|
events := make([]eventRow, 0)
|
|
db := r.db.WithContext(ctx)
|
|
uploadSource := db.Table("listing_uploads").
|
|
Select("listing_id, MAX(id) AS upload_id, MAX(NULLIF(source_channel, '')) AS source_channel").
|
|
Where("listing_id IS NOT NULL").
|
|
Group("listing_id")
|
|
if err := db.Table("listing_status_events AS e").
|
|
Select(`e.event_type, e.source, e.created_at,
|
|
CASE
|
|
WHEN lu.upload_id IS NULL THEN ?
|
|
WHEN COALESCE(lu.source_channel, '') = '' THEN ?
|
|
ELSE lu.source_channel
|
|
END AS source_channel`, listingChannelWebsite, listingChannelExternalUnknown).
|
|
Joins("LEFT JOIN (?) AS lu ON lu.listing_id = e.listing_id", uploadSource).
|
|
Where("e.created_at >= ? AND e.created_at < ?", start, end).
|
|
Scan(&events).Error; err != nil {
|
|
return ListingDailyOverviewDTO{}, err
|
|
}
|
|
uploads := make([]listingUploaderRow, 0)
|
|
if err := db.Table("listing_uploads").
|
|
Select("COALESCE(matched_admin_id, 0) AS uploader_id, uploader_name, created_at").
|
|
Where("listing_id IS NOT NULL").
|
|
Where("created_at >= ? AND created_at < ?", start, end).
|
|
Scan(&uploads).Error; err != nil {
|
|
return ListingDailyOverviewDTO{}, err
|
|
}
|
|
|
|
byDay := make(map[string]*listingDailyBucket, days)
|
|
byChannel := make(map[listingChannelKey]*listingDailyBucket)
|
|
byUploader := make(map[listingUploaderKey]int64)
|
|
for _, ev := range events {
|
|
key := ev.CreatedAt.In(loc).Format("2006-01-02")
|
|
b := byDay[key]
|
|
if b == nil {
|
|
b = &listingDailyBucket{}
|
|
byDay[key] = b
|
|
}
|
|
if !addListingDailyEvent(b, ev.EventType, ev.Source) {
|
|
continue
|
|
}
|
|
channel := ev.SourceChannel
|
|
channelBucketKey := listingChannelKey{date: key, sourceChannel: channel}
|
|
cb := byChannel[channelBucketKey]
|
|
if cb == nil {
|
|
cb = &listingDailyBucket{}
|
|
byChannel[channelBucketKey] = cb
|
|
}
|
|
addListingDailyEvent(cb, ev.EventType, ev.Source)
|
|
}
|
|
for _, upload := range uploads {
|
|
key := upload.CreatedAt.In(loc).Format("2006-01-02")
|
|
uploaderKey := listingUploaderKey{date: key, uploaderID: upload.UploaderID, uploaderName: upload.UploaderName}
|
|
byUploader[uploaderKey]++
|
|
}
|
|
|
|
trend := make([]ListingDayStatsDTO, 0, days)
|
|
for i := days - 1; i >= 0; i-- {
|
|
day := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, loc).AddDate(0, 0, -i)
|
|
key := day.Format("2006-01-02")
|
|
item := ListingDayStatsDTO{Date: key}
|
|
if b := byDay[key]; b != nil {
|
|
item.PublishedCount = b.published
|
|
item.ActiveOfflineCount = b.activeOffline
|
|
item.TradeLeaveCount = b.tradeLeave
|
|
}
|
|
trend = append(trend, item)
|
|
}
|
|
|
|
todayStats := ListingDayStatsDTO{Date: now.Format("2006-01-02")}
|
|
if len(trend) > 0 {
|
|
todayStats = trend[len(trend)-1]
|
|
}
|
|
channelTrend := listingChannelTrend(byChannel)
|
|
uploaderTrend := listingUploaderTrend(byUploader)
|
|
todayChannels := make([]ListingChannelDayStatsDTO, 0)
|
|
for _, item := range channelTrend {
|
|
if item.Date == todayStats.Date {
|
|
todayChannels = append(todayChannels, item)
|
|
}
|
|
}
|
|
todayUploaders := make([]ListingUploaderDayStatsDTO, 0)
|
|
for _, item := range uploaderTrend {
|
|
if item.Date == todayStats.Date {
|
|
todayUploaders = append(todayUploaders, item)
|
|
}
|
|
}
|
|
return ListingDailyOverviewDTO{
|
|
Today: todayStats,
|
|
Trend: trend,
|
|
TodayChannels: todayChannels,
|
|
ChannelTrend: channelTrend,
|
|
TodayUploaders: todayUploaders,
|
|
UploaderTrend: uploaderTrend,
|
|
Days: days,
|
|
Timezone: "Asia/Shanghai",
|
|
}, nil
|
|
}
|
|
|
|
type listingUploaderRow struct {
|
|
UploaderID uint64
|
|
UploaderName string
|
|
CreatedAt time.Time
|
|
}
|
|
|
|
type listingChannelKey struct {
|
|
date string
|
|
sourceChannel string
|
|
}
|
|
|
|
type listingUploaderKey struct {
|
|
date string
|
|
uploaderID uint64
|
|
uploaderName string
|
|
}
|
|
|
|
type listingDailyBucket struct {
|
|
published int64
|
|
activeOffline int64
|
|
tradeLeave int64
|
|
}
|
|
|
|
func addListingDailyEvent(b *listingDailyBucket, eventType, source string) bool {
|
|
switch {
|
|
case eventType == listingstatus.EventPublished:
|
|
b.published++
|
|
return true
|
|
case isActiveOfflineEvent(eventType, source):
|
|
b.activeOffline++
|
|
return true
|
|
case isTradeLeaveEvent(eventType, source):
|
|
b.tradeLeave++
|
|
return true
|
|
default:
|
|
return false
|
|
}
|
|
}
|
|
|
|
func listingChannelTrend(rows map[listingChannelKey]*listingDailyBucket) []ListingChannelDayStatsDTO {
|
|
keys := make([]listingChannelKey, 0, len(rows))
|
|
for key := range rows {
|
|
keys = append(keys, key)
|
|
}
|
|
sort.Slice(keys, func(i, j int) bool {
|
|
if keys[i].date != keys[j].date {
|
|
return keys[i].date < keys[j].date
|
|
}
|
|
leftRank := listingChannelRank(keys[i].sourceChannel)
|
|
rightRank := listingChannelRank(keys[j].sourceChannel)
|
|
if leftRank != rightRank {
|
|
return leftRank < rightRank
|
|
}
|
|
return keys[i].sourceChannel < keys[j].sourceChannel
|
|
})
|
|
items := make([]ListingChannelDayStatsDTO, 0, len(keys))
|
|
for _, key := range keys {
|
|
b := rows[key]
|
|
items = append(items, ListingChannelDayStatsDTO{
|
|
Date: key.date,
|
|
SourceChannel: key.sourceChannel,
|
|
PublishedCount: b.published,
|
|
ActiveOfflineCount: b.activeOffline,
|
|
TradeLeaveCount: b.tradeLeave,
|
|
})
|
|
}
|
|
return items
|
|
}
|
|
|
|
func listingUploaderTrend(rows map[listingUploaderKey]int64) []ListingUploaderDayStatsDTO {
|
|
keys := make([]listingUploaderKey, 0, len(rows))
|
|
for key := range rows {
|
|
keys = append(keys, key)
|
|
}
|
|
sort.Slice(keys, func(i, j int) bool {
|
|
if keys[i].date != keys[j].date {
|
|
return keys[i].date < keys[j].date
|
|
}
|
|
if rows[keys[i]] != rows[keys[j]] {
|
|
return rows[keys[i]] > rows[keys[j]]
|
|
}
|
|
if keys[i].uploaderName != keys[j].uploaderName {
|
|
return keys[i].uploaderName < keys[j].uploaderName
|
|
}
|
|
return keys[i].uploaderID < keys[j].uploaderID
|
|
})
|
|
items := make([]ListingUploaderDayStatsDTO, 0, len(keys))
|
|
for _, key := range keys {
|
|
items = append(items, ListingUploaderDayStatsDTO{
|
|
Date: key.date,
|
|
UploaderID: key.uploaderID,
|
|
UploaderName: key.uploaderName,
|
|
UploadCount: rows[key],
|
|
})
|
|
}
|
|
return items
|
|
}
|
|
|
|
func listingChannelRank(channel string) int {
|
|
switch channel {
|
|
case "咸鱼":
|
|
return 10
|
|
case "淘宝":
|
|
return 20
|
|
case "京东":
|
|
return 30
|
|
case "QQ":
|
|
return 40
|
|
case "微信":
|
|
return 50
|
|
case listingChannelExternalUnknown:
|
|
return 90
|
|
case listingChannelWebsite:
|
|
return 100
|
|
default:
|
|
return 80
|
|
}
|
|
}
|
|
|
|
func isActiveOfflineEvent(eventType, source string) bool {
|
|
return eventType == listingstatus.EventOffline &&
|
|
(source == listingstatus.SourceSeller || source == listingstatus.SourceAdmin)
|
|
}
|
|
|
|
func isTradeLeaveEvent(eventType, source string) bool {
|
|
switch eventType {
|
|
case listingstatus.EventRented, listingstatus.EventCompleted, listingstatus.EventSealed:
|
|
return true
|
|
case listingstatus.EventOffline:
|
|
return source == listingstatus.SourceOrder ||
|
|
source == listingstatus.SourceDispute ||
|
|
source == listingstatus.SourcePickup ||
|
|
source == listingstatus.SourceSystem
|
|
default:
|
|
return false
|
|
}
|
|
}
|
|
|
|
func (r *Repository) recentOrders(ctx context.Context) ([]RecentOrderDTO, error) {
|
|
rows := make([]RecentOrderDTO, 0)
|
|
err := r.db.WithContext(ctx).Table("rental_orders AS o").
|
|
Select("o.id, o.order_no, a.title, o.renter_id, o.owner_id, o.status, o.rent_amount_cent, o.deposit_amount_cent, o.created_at").
|
|
Joins("JOIN game_accounts AS a ON a.id = o.account_id").
|
|
Order("o.id DESC").
|
|
Limit(8).
|
|
Scan(&rows).Error
|
|
return rows, err
|
|
}
|
|
|
|
func (r *Repository) recentDisputes(ctx context.Context) ([]RecentDisputeDTO, error) {
|
|
rows := make([]RecentDisputeDTO, 0)
|
|
err := r.db.WithContext(ctx).Table("disputes AS d").
|
|
Select("d.id, d.order_id, o.order_no, a.title, d.type, d.status, d.initiator_id, d.created_at").
|
|
Joins("JOIN rental_orders AS o ON o.id = d.order_id").
|
|
Joins("JOIN game_accounts AS a ON a.id = o.account_id").
|
|
Order("d.id DESC").
|
|
Limit(8).
|
|
Scan(&rows).Error
|
|
return rows, err
|
|
}
|