whereUserId(Auth::id());
}
public function user(): BelongsTo
{
return $this->belongsTo(User::class);
}
public function admin(): BelongsTo
{
return $this->belongsTo(User::class);
}
public function reply(): HasMany
{
return $this->hasMany(TicketReply::class);
}
public function close(): bool
{
$this->status = 2;
return $this->save();
}
public function getStatusLabelAttribute(): string
{
return match ($this->status) {
0 => ''.trans('common.status.pending').'',
1 => ''.trans('common.status.reply').'',
2 => ''.trans('common.status.closed').'',
default => ''.trans('common.status.unknown').'',
};
}
public static function getAvgFirstResponseData(int $days = 30): array
{
$startDate = Carbon::now()->subDays($days);
$subQuery = TicketReply::query()
->select('ticket_id')
->selectRaw('MIN(created_at) as first_reply_time')
->whereNotNull('admin_id')
->groupBy('ticket_id');
$result = self::query()
->joinSub($subQuery, 'reply', 'ticket.id', '=', 'reply.ticket_id')
->where('ticket.created_at', '>=', $startDate)
->selectRaw('
COUNT(*) as total_count,
COALESCE(AVG(TIMESTAMPDIFF(SECOND, ticket.created_at, reply.first_reply_time)), 0) as avg_seconds
')
->first();
return [
'count' => (int) ($result->total_count ?? 0),
'avg_time' => (float) ($result->avg_seconds ?? 0),
];
}
public static function getTicketStatusStats(): array
{
$stats = self::query()->selectRaw('
COUNT(*) as total,
SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as pending,
SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as replied,
SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as closed
')->first();
return [
'total' => (int) ($stats->total ?? 0),
'pending' => (int) ($stats->pending ?? 0),
'replied' => (int) ($stats->replied ?? 0),
'closed' => (int) ($stats->closed ?? 0),
];
}
public static function getTodayTicketCount(): int
{
return (int) self::query()
->whereDate('created_at', Carbon::today())
->count();
}
public static function getAvgCloseTime(int $days = 30): ?float
{
$startDate = Carbon::now()->subDays($days);
$subQuery = TicketReply::query()
->select('ticket_id')
->selectRaw('MAX(created_at) as last_reply_time')
->whereNotNull('admin_id')
->groupBy('ticket_id');
$result = self::query()
->joinSub($subQuery, 'reply', 'ticket.id', '=', 'reply.ticket_id')
->where('ticket.status', 2) // 已关闭的工单
->where('ticket.created_at', '>=', $startDate)
->selectRaw('
COALESCE(AVG(TIMESTAMPDIFF(SECOND, ticket.created_at, reply.last_reply_time)), 0) as avg_seconds
')
->first();
return (float) ($result->avg_seconds ?? 0);
}
}