Function GetUserMonthlyUsageStatistics

model/usage_statistics.go:443–531

GetUserMonthlyUsageStatistics 获取特定用户的月度用量统计数据

(userId int, startDate, endDate string, tokenId int, modelName string, page, pageSize int)

// GetUserMonthlyUsageStatistics 获取特定用户的月度用量统计数据
func GetUserMonthlyUsageStatistics(userId int, startDate, endDate string, tokenId int, modelName string, page, pageSize int) ([]*UsageStatistics, int64, error) {
	var statistics []*UsageStatistics
	var total int64

	// 使用原生SQL查询实现按月分组统计
	db := DB.Table("usage_statistics").
		Joins("JOIN tokens ON usage_statistics.token_id = tokens.id").
		Where("tokens.user_id = ?", userId)

	// 构建查询条件
	conditions := " AND tokens.user_id = ?"
	params := []interface{}{userId}

	// 添加日期范围条件（按月查询）
	if startDate != "" {
		conditions += " AND usage_statistics.date >= ?"
		params = append(params, startDate+"-01")
	}
	if endDate != "" {
		// 获取 endDate 所在月份的最后一天
		if len(endDate) >= 7 {
			year := endDate[0:4]
			month := endDate[5:7]
			conditions += " AND usage_statistics.date <= ?"
			params = append(params, year+"-"+month+"-31")
		}
	}
	
	if tokenId > 0 {
		conditions += " AND usage_statistics.token_id = ?"
		params = append(params, tokenId)
	}
	if modelName != "" {
		conditions += " AND usage_statistics.model_name LIKE ?"
		params = append(params, "%"+modelName+"%")
	}

	// 构建完整的SQL查询
	sql := `
		SELECT 
			MAX(usage_statistics.id) as id,
			SUBSTR(usage_statistics.date, 1, 7) as date,
			usage_statistics.token_id,
			usage_statistics.token_name,
			usage_statistics.model_name,
			SUM(usage_statistics.total_requests) as total_requests,
			SUM(usage_statistics.successful_requests) as successful_requests,
			SUM(usage_statistics.failed_requests) as failed_requests,
			SUM(usage_statistics.total_tokens) as total_tokens,
			SUM(usage_statistics.prompt_tokens) as prompt_tokens,
			SUM(usage_statistics.completion_tokens) as completion_tokens,
			SUM(usage_statistics.total_quota) as total_quota,
			MAX(usage_statistics.created_time) as created_time,
			MAX(usage_statistics.updated_time) as updated_time
		FROM usage_statistics 
		JOIN tokens ON usage_statistics.token_id = tokens.id
		WHERE 1=1` + conditions + `
		GROUP BY SUBSTR(usage_statistics.date, 1, 7), usage_statistics.token_id, usage_statistics.token_name, usage_statistics.model_name