You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
450 lines
14 KiB
450 lines
14 KiB
package statistics
|
|
|
|
import (
|
|
"fmt"
|
|
"net/http"
|
|
"strconv"
|
|
"time"
|
|
|
|
"task-track-backend/model"
|
|
"task-track-backend/pkg/database"
|
|
|
|
"github.com/gin-gonic/gin"
|
|
)
|
|
|
|
// UserStatisticsResponse 用户统计响应
|
|
type UserStatisticsResponse struct {
|
|
// 基本统计
|
|
TotalUsers int64 `json:"total_users"`
|
|
ActiveUsers int64 `json:"active_users"` // 最近30天有活动的用户
|
|
|
|
// 用户任务统计
|
|
UserTaskStats []UserTaskStat `json:"user_task_stats"`
|
|
|
|
// 用户效率统计
|
|
UserEfficiencyStats []UserEfficiencyStat `json:"user_efficiency_stats"`
|
|
|
|
// 用户活跃度统计
|
|
UserActivityStats []UserActivityStat `json:"user_activity_stats"`
|
|
|
|
// 用户完成率排行
|
|
UserCompletionRank []UserCompletionRank `json:"user_completion_rank"`
|
|
|
|
// 用户任务分配统计
|
|
UserAssignmentStats []UserAssignmentStat `json:"user_assignment_stats"`
|
|
|
|
// 总体用户效率指标
|
|
OverallEfficiency struct {
|
|
AverageCompletionRate float64 `json:"average_completion_rate"`
|
|
AverageTasksPerUser float64 `json:"average_tasks_per_user"`
|
|
AverageCompletionTime float64 `json:"average_completion_time"`
|
|
} `json:"overall_efficiency"`
|
|
}
|
|
|
|
// UserTaskStat 用户任务统计
|
|
type UserTaskStat struct {
|
|
UserID uint `json:"user_id"`
|
|
Username string `json:"username"`
|
|
RealName string `json:"real_name"`
|
|
CreatedTasks int64 `json:"created_tasks"`
|
|
AssignedTasks int64 `json:"assigned_tasks"`
|
|
CompletedTasks int64 `json:"completed_tasks"`
|
|
PendingTasks int64 `json:"pending_tasks"`
|
|
InProgressTasks int64 `json:"in_progress_tasks"`
|
|
}
|
|
|
|
// UserEfficiencyStat 用户效率统计
|
|
type UserEfficiencyStat struct {
|
|
UserID uint `json:"user_id"`
|
|
Username string `json:"username"`
|
|
RealName string `json:"real_name"`
|
|
CompletionRate float64 `json:"completion_rate"`
|
|
AverageCompletionTime float64 `json:"average_completion_time"`
|
|
TasksCompletedThisWeek int64 `json:"tasks_completed_this_week"`
|
|
TasksCompletedThisMonth int64 `json:"tasks_completed_this_month"`
|
|
}
|
|
|
|
// UserActivityStat 用户活跃度统计
|
|
type UserActivityStat struct {
|
|
UserID uint `json:"user_id"`
|
|
Username string `json:"username"`
|
|
RealName string `json:"real_name"`
|
|
LastActivity time.Time `json:"last_activity"`
|
|
CommentsCount int64 `json:"comments_count"`
|
|
TasksCreatedThisWeek int64 `json:"tasks_created_this_week"`
|
|
TasksCreatedThisMonth int64 `json:"tasks_created_this_month"`
|
|
}
|
|
|
|
// UserCompletionRank 用户完成率排行
|
|
type UserCompletionRank struct {
|
|
UserID uint `json:"user_id"`
|
|
Username string `json:"username"`
|
|
RealName string `json:"real_name"`
|
|
CompletionRate float64 `json:"completion_rate"`
|
|
CompletedTasks int64 `json:"completed_tasks"`
|
|
TotalTasks int64 `json:"total_tasks"`
|
|
Rank int `json:"rank"`
|
|
}
|
|
|
|
// UserAssignmentStat 用户任务分配统计
|
|
type UserAssignmentStat struct {
|
|
UserID uint `json:"user_id"`
|
|
Username string `json:"username"`
|
|
RealName string `json:"real_name"`
|
|
UrgentTasks int64 `json:"urgent_tasks"`
|
|
HighPriorityTasks int64 `json:"high_priority_tasks"`
|
|
MediumPriorityTasks int64 `json:"medium_priority_tasks"`
|
|
LowPriorityTasks int64 `json:"low_priority_tasks"`
|
|
OverdueTasks int64 `json:"overdue_tasks"`
|
|
}
|
|
|
|
func (h *StatisticsHandler) GetUserStatistics(c *gin.Context) {
|
|
// 获取组织ID参数（可选）
|
|
organizationIDStr := c.Query("organization_id")
|
|
var organizationID uint
|
|
if organizationIDStr != "" {
|
|
if id, err := strconv.ParseUint(organizationIDStr, 10, 32); err == nil {
|
|
organizationID = uint(id)
|
|
}
|
|
}
|
|
|
|
// 获取用户ID参数（可选，如果提供则只查询该用户）
|
|
userIDStr := c.Query("user_id")
|
|
var userID uint
|
|
if userIDStr != "" {
|
|
if id, err := strconv.ParseUint(userIDStr, 10, 32); err == nil {
|
|
userID = uint(id)
|
|
}
|
|
}
|
|
|
|
db := database.GetDB()
|
|
var response UserStatisticsResponse
|
|
|
|
// 基础用户查询构建器
|
|
baseUserQuery := db.Model(&model.User{})
|
|
if organizationID > 0 {
|
|
baseUserQuery = baseUserQuery.Joins("JOIN user_organizations ON users.id = user_organizations.user_id").
|
|
Where("user_organizations.organization_id = ?", organizationID)
|
|
}
|
|
if userID > 0 {
|
|
baseUserQuery = baseUserQuery.Where("users.id = ?", userID)
|
|
}
|
|
|
|
// 1. 基本统计
|
|
if err := baseUserQuery.Count(&response.TotalUsers).Error; err != nil {
|
|
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户总数失败"})
|
|
return
|
|
}
|
|
|
|
// 活跃用户数（最近30天有任务活动）
|
|
thirtyDaysAgo := time.Now().AddDate(0, 0, -30)
|
|
activeUserQuery := baseUserQuery.Joins("JOIN tasks ON users.id = tasks.creator_id OR users.id = tasks.assignee_id").
|
|
Where("tasks.created_at >= ? OR tasks.updated_at >= ?", thirtyDaysAgo, thirtyDaysAgo).
|
|
Distinct("users.id")
|
|
if err := activeUserQuery.Count(&response.ActiveUsers).Error; err != nil {
|
|
response.ActiveUsers = 0
|
|
}
|
|
|
|
// 2. 用户任务统计
|
|
var userTaskStats []UserTaskStat
|
|
|
|
// 构建用户任务统计查询
|
|
userTaskQuery := `
|
|
SELECT
|
|
u.id as user_id,
|
|
u.username,
|
|
u.real_name,
|
|
COALESCE(created_tasks.count, 0) as created_tasks,
|
|
COALESCE(assigned_tasks.count, 0) as assigned_tasks,
|
|
COALESCE(completed_tasks.count, 0) as completed_tasks,
|
|
COALESCE(pending_tasks.count, 0) as pending_tasks,
|
|
COALESCE(in_progress_tasks.count, 0) as in_progress_tasks
|
|
FROM users u
|
|
LEFT JOIN (
|
|
SELECT creator_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE 1=1 %s
|
|
GROUP BY creator_id
|
|
) created_tasks ON u.id = created_tasks.creator_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL %s
|
|
GROUP BY assignee_id
|
|
) assigned_tasks ON u.id = assigned_tasks.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'completed' %s
|
|
GROUP BY assignee_id
|
|
) completed_tasks ON u.id = completed_tasks.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'pending' %s
|
|
GROUP BY assignee_id
|
|
) pending_tasks ON u.id = pending_tasks.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'in_progress' %s
|
|
GROUP BY assignee_id
|
|
) in_progress_tasks ON u.id = in_progress_tasks.assignee_id
|
|
WHERE 1=1 %s
|
|
ORDER BY (COALESCE(created_tasks.count, 0) + COALESCE(assigned_tasks.count, 0)) DESC
|
|
LIMIT 20
|
|
`
|
|
|
|
// 构建WHERE条件
|
|
orgCondition := ""
|
|
userCondition := ""
|
|
if organizationID > 0 {
|
|
orgCondition = "AND organization_id = " + strconv.FormatUint(uint64(organizationID), 10)
|
|
}
|
|
if userID > 0 {
|
|
userCondition = "AND u.id = " + strconv.FormatUint(uint64(userID), 10)
|
|
}
|
|
|
|
finalQuery := fmt.Sprintf(userTaskQuery, orgCondition, orgCondition, orgCondition, orgCondition, orgCondition, userCondition)
|
|
|
|
if err := db.Raw(finalQuery).Scan(&userTaskStats).Error; err != nil {
|
|
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户任务统计失败"})
|
|
return
|
|
}
|
|
|
|
response.UserTaskStats = userTaskStats
|
|
|
|
// 3. 用户效率统计
|
|
var userEfficiencyStats []UserEfficiencyStat
|
|
|
|
now := time.Now()
|
|
weekStart := now.AddDate(0, 0, -int(now.Weekday())+1)
|
|
monthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location())
|
|
|
|
efficiencyQuery := `
|
|
SELECT
|
|
u.id as user_id,
|
|
u.username,
|
|
u.real_name,
|
|
CASE
|
|
WHEN total_assigned.count > 0 THEN
|
|
(completed_assigned.count * 100.0 / total_assigned.count)
|
|
ELSE 0
|
|
END as completion_rate,
|
|
COALESCE(avg_completion.avg_hours, 0) as average_completion_time,
|
|
COALESCE(week_completed.count, 0) as tasks_completed_this_week,
|
|
COALESCE(month_completed.count, 0) as tasks_completed_this_month
|
|
FROM users u
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL %s
|
|
GROUP BY assignee_id
|
|
) total_assigned ON u.id = total_assigned.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'completed' %s
|
|
GROUP BY assignee_id
|
|
) completed_assigned ON u.id = completed_assigned.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, AVG(TIMESTAMPDIFF(HOUR, created_at, updated_at)) as avg_hours
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'completed' %s
|
|
GROUP BY assignee_id
|
|
) avg_completion ON u.id = avg_completion.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'completed' AND updated_at >= '%s' %s
|
|
GROUP BY assignee_id
|
|
) week_completed ON u.id = week_completed.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND status = 'completed' AND updated_at >= '%s' %s
|
|
GROUP BY assignee_id
|
|
) month_completed ON u.id = month_completed.assignee_id
|
|
WHERE 1=1 %s
|
|
HAVING total_assigned.count > 0
|
|
ORDER BY completion_rate DESC
|
|
LIMIT 20
|
|
`
|
|
|
|
finalEfficiencyQuery := fmt.Sprintf(efficiencyQuery,
|
|
orgCondition, orgCondition, orgCondition,
|
|
weekStart.Format("2006-01-02"), orgCondition,
|
|
monthStart.Format("2006-01-02"), orgCondition,
|
|
userCondition)
|
|
|
|
if err := db.Raw(finalEfficiencyQuery).Scan(&userEfficiencyStats).Error; err != nil {
|
|
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户效率统计失败"})
|
|
return
|
|
}
|
|
|
|
response.UserEfficiencyStats = userEfficiencyStats
|
|
|
|
// 4. 用户活跃度统计
|
|
var userActivityStats []UserActivityStat
|
|
|
|
activityQuery := `
|
|
SELECT
|
|
u.id as user_id,
|
|
u.username,
|
|
u.real_name,
|
|
COALESCE(MAX(GREATEST(t.created_at, t.updated_at)), u.created_at) as last_activity,
|
|
COALESCE(comments.count, 0) as comments_count,
|
|
COALESCE(week_created.count, 0) as tasks_created_this_week,
|
|
COALESCE(month_created.count, 0) as tasks_created_this_month
|
|
FROM users u
|
|
LEFT JOIN tasks t ON u.id = t.creator_id OR u.id = t.assignee_id
|
|
LEFT JOIN (
|
|
SELECT user_id, COUNT(*) as count
|
|
FROM task_comments
|
|
GROUP BY user_id
|
|
) comments ON u.id = comments.user_id
|
|
LEFT JOIN (
|
|
SELECT creator_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE created_at >= '%s' %s
|
|
GROUP BY creator_id
|
|
) week_created ON u.id = week_created.creator_id
|
|
LEFT JOIN (
|
|
SELECT creator_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE created_at >= '%s' %s
|
|
GROUP BY creator_id
|
|
) month_created ON u.id = month_created.creator_id
|
|
WHERE 1=1 %s
|
|
GROUP BY u.id, u.username, u.real_name, u.created_at, comments.count, week_created.count, month_created.count
|
|
ORDER BY last_activity DESC
|
|
LIMIT 20
|
|
`
|
|
|
|
finalActivityQuery := fmt.Sprintf(activityQuery,
|
|
weekStart.Format("2006-01-02"), orgCondition,
|
|
monthStart.Format("2006-01-02"), orgCondition,
|
|
userCondition)
|
|
|
|
if err := db.Raw(finalActivityQuery).Scan(&userActivityStats).Error; err != nil {
|
|
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户活跃度统计失败"})
|
|
return
|
|
}
|
|
|
|
response.UserActivityStats = userActivityStats
|
|
|
|
// 5. 用户完成率排行
|
|
var userCompletionRank []UserCompletionRank
|
|
|
|
for i, stat := range userEfficiencyStats {
|
|
rank := UserCompletionRank{
|
|
UserID: stat.UserID,
|
|
Username: stat.Username,
|
|
RealName: stat.RealName,
|
|
CompletionRate: stat.CompletionRate,
|
|
Rank: i + 1,
|
|
}
|
|
|
|
// 获取用户的总任务数和完成任务数
|
|
for _, taskStat := range userTaskStats {
|
|
if taskStat.UserID == stat.UserID {
|
|
rank.TotalTasks = taskStat.AssignedTasks
|
|
rank.CompletedTasks = taskStat.CompletedTasks
|
|
break
|
|
}
|
|
}
|
|
|
|
userCompletionRank = append(userCompletionRank, rank)
|
|
}
|
|
|
|
response.UserCompletionRank = userCompletionRank
|
|
|
|
// 6. 用户任务分配统计
|
|
var userAssignmentStats []UserAssignmentStat
|
|
|
|
assignmentQuery := `
|
|
SELECT
|
|
u.id as user_id,
|
|
u.username,
|
|
u.real_name,
|
|
COALESCE(urgent.count, 0) as urgent_tasks,
|
|
COALESCE(high_priority.count, 0) as high_priority_tasks,
|
|
COALESCE(medium_priority.count, 0) as medium_priority_tasks,
|
|
COALESCE(low_priority.count, 0) as low_priority_tasks,
|
|
COALESCE(overdue.count, 0) as overdue_tasks
|
|
FROM users u
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND priority = 'urgent' %s
|
|
GROUP BY assignee_id
|
|
) urgent ON u.id = urgent.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND priority = 'high' %s
|
|
GROUP BY assignee_id
|
|
) high_priority ON u.id = high_priority.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND priority = 'medium' %s
|
|
GROUP BY assignee_id
|
|
) medium_priority ON u.id = medium_priority.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND priority = 'low' %s
|
|
GROUP BY assignee_id
|
|
) low_priority ON u.id = low_priority.assignee_id
|
|
LEFT JOIN (
|
|
SELECT assignee_id, COUNT(*) as count
|
|
FROM tasks
|
|
WHERE assignee_id IS NOT NULL AND end_time < NOW() AND status != 'completed' %s
|
|
GROUP BY assignee_id
|
|
) overdue ON u.id = overdue.assignee_id
|
|
WHERE 1=1 %s
|
|
ORDER BY (COALESCE(urgent.count, 0) + COALESCE(high_priority.count, 0)) DESC
|
|
LIMIT 20
|
|
`
|
|
|
|
finalAssignmentQuery := fmt.Sprintf(assignmentQuery,
|
|
orgCondition, orgCondition, orgCondition, orgCondition, orgCondition, userCondition)
|
|
|
|
if err := db.Raw(finalAssignmentQuery).Scan(&userAssignmentStats).Error; err != nil {
|
|
c.JSON(http.StatusInternalServerError, gin.H{"error": "获取用户任务分配统计失败"})
|
|
return
|
|
}
|
|
|
|
response.UserAssignmentStats = userAssignmentStats
|
|
|
|
// 7. 总体效率指标
|
|
if len(userEfficiencyStats) > 0 {
|
|
var totalCompletionRate float64
|
|
var totalTasks int64
|
|
var totalCompletionTime float64
|
|
var validTimeCount int64
|
|
|
|
for _, stat := range userEfficiencyStats {
|
|
totalCompletionRate += stat.CompletionRate
|
|
if stat.AverageCompletionTime > 0 {
|
|
totalCompletionTime += stat.AverageCompletionTime
|
|
validTimeCount++
|
|
}
|
|
}
|
|
|
|
for _, stat := range userTaskStats {
|
|
totalTasks += stat.AssignedTasks
|
|
}
|
|
|
|
response.OverallEfficiency.AverageCompletionRate = totalCompletionRate / float64(len(userEfficiencyStats))
|
|
if response.TotalUsers > 0 {
|
|
response.OverallEfficiency.AverageTasksPerUser = float64(totalTasks) / float64(response.TotalUsers)
|
|
}
|
|
if validTimeCount > 0 {
|
|
response.OverallEfficiency.AverageCompletionTime = totalCompletionTime / float64(validTimeCount)
|
|
}
|
|
}
|
|
|
|
c.JSON(http.StatusOK, response)
|
|
}
|
|
|