aa8fdaa5aa
- Created a database migration to add `team_id` to `collection_logs`. - Created a database migration to backfill `team_id` for past scans using `trip_id` and scanner's active team. - Updated `ScanService` to lookup the scanner's active team and store the `team_id` directly on the `CollectionLog`. - Modified the `CollectionTeam` model's `collectionLogs()` relation to be a direct `HasMany` using the new `team_id` instead of routing through `Trip`. - Updated `AdminTeamReportController` to query metrics directly from `collection_logs.team_id`, ensuring all past and future scans (including those done without an active trip) correctly increment team stats and daily charts.
372 lines
17 KiB
PHP
372 lines
17 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers\Api\V1\Admin;
|
|
|
|
use App\Http\Controllers\Api\V1\ApiController;
|
|
use App\Models\CollectionLog;
|
|
use App\Models\CollectionTeam;
|
|
use App\Models\Tenant;
|
|
use App\Models\Trip;
|
|
use App\Models\TripTimelineEvent;
|
|
use App\Tenancy\Tenancy;
|
|
use Illuminate\Http\JsonResponse;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Carbon;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class AdminTeamReportController extends ApiController
|
|
{
|
|
/**
|
|
* GET /api/v1/admin/reports/teams/leaderboard
|
|
*
|
|
* Returns a ranked list of all collection teams with aggregate KPIs
|
|
* for the given date range. Super-admins can pass tenant_id to scope
|
|
* to a specific LGU, or omit it to see all LGUs combined.
|
|
*/
|
|
public function leaderboard(Request $request): JsonResponse
|
|
{
|
|
$data = $request->validate([
|
|
'from' => ['nullable', 'date'],
|
|
'to' => ['nullable', 'date'],
|
|
'tenant_id' => ['nullable', 'integer', 'exists:tenants,id'],
|
|
'per_page' => ['nullable', 'integer', 'min:1', 'max:200'],
|
|
]);
|
|
|
|
$from = Carbon::parse($data['from'] ?? now()->subDays(30))->startOfDay();
|
|
$to = Carbon::parse($data['to'] ?? now())->endOfDay();
|
|
$perPage = (int) ($data['per_page'] ?? 50);
|
|
|
|
$tenant = $this->resolveTargetTenant($request);
|
|
|
|
// For the leaderboard we run a single DB query per team to avoid N+1.
|
|
// We use subquery aggregates to compute everything in one pass.
|
|
$rows = Tenancy::withoutScope(function () use ($from, $to, $tenant, $perPage) {
|
|
$query = CollectionTeam::withoutTrashed()
|
|
->with(['driver', 'scanner', 'truck'])
|
|
->when($tenant, fn ($q) => $q->where('collection_teams.tenant_id', $tenant->id));
|
|
|
|
// Eager-load trip IDs, then compute aggregates in PHP to avoid
|
|
// complex cross-table subqueries that differ by DB engine.
|
|
return $query->orderBy('name')->paginate($perPage);
|
|
});
|
|
|
|
$teamIds = $rows->pluck('id');
|
|
$fromStr = $from->toDateTimeString();
|
|
$toStr = $to->toDateTimeString();
|
|
|
|
// Batch-load all aggregate data using a single query per metric.
|
|
$tripStats = Tenancy::withoutScope(function () use ($teamIds, $from, $to) {
|
|
return DB::table('trips')
|
|
->select(
|
|
'team_id',
|
|
DB::raw('COUNT(*) as total_trips'),
|
|
DB::raw("SUM(CASE WHEN status IN ('completed','handed_off') THEN 1 ELSE 0 END) as completed_trips"),
|
|
DB::raw("SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_trips"),
|
|
DB::raw('SUM(COALESCE(total_load_kg, 0)) as total_weight_kg'),
|
|
DB::raw('MAX(scheduled_date) as last_trip_date')
|
|
)
|
|
->whereIn('team_id', $teamIds)
|
|
->whereBetween('scheduled_date', [$from->toDateString(), $to->toDateString()])
|
|
->whereNull('deleted_at')
|
|
->groupBy('team_id')
|
|
->get()
|
|
->keyBy('team_id');
|
|
});
|
|
|
|
$scanStats = Tenancy::withoutScope(function () use ($teamIds, $fromStr, $toStr) {
|
|
return DB::table('collection_logs')
|
|
->select('team_id', DB::raw('COUNT(id) as total_scans'))
|
|
->whereIn('team_id', $teamIds)
|
|
->whereBetween('scanned_at', [$fromStr, $toStr])
|
|
->groupBy('team_id')
|
|
->get()
|
|
->keyBy('team_id');
|
|
});
|
|
|
|
$eventStats = Tenancy::withoutScope(function () use ($teamIds, $fromStr, $toStr) {
|
|
return DB::table('trip_timeline_events')
|
|
->join('trips', 'trips.id', '=', 'trip_timeline_events.trip_id')
|
|
->select('trips.team_id', DB::raw('COUNT(trip_timeline_events.id) as event_count'))
|
|
->whereIn('trips.team_id', $teamIds)
|
|
->whereBetween('trip_timeline_events.event_at', [$fromStr, $toStr])
|
|
->whereNull('trips.deleted_at')
|
|
->groupBy('trips.team_id')
|
|
->get()
|
|
->keyBy('team_id');
|
|
});
|
|
|
|
// Map teams to leaderboard rows.
|
|
$leaderboard = $rows->map(function (CollectionTeam $team) use ($tripStats, $scanStats, $eventStats) {
|
|
$ts = $tripStats->get($team->id);
|
|
$ss = $scanStats->get($team->id);
|
|
$es = $eventStats->get($team->id);
|
|
|
|
$totalTrips = (int) ($ts->total_trips ?? 0);
|
|
$completedTrips = (int) ($ts->completed_trips ?? 0);
|
|
$totalWeight = (int) ($ts->total_weight_kg ?? 0);
|
|
$totalScans = (int) ($ss->total_scans ?? 0);
|
|
|
|
return [
|
|
'team_uuid' => $team->uuid,
|
|
'team_name' => $team->name,
|
|
'driver_name' => $team->driver?->full_name,
|
|
'scanner_name' => $team->scanner?->full_name,
|
|
'truck_plate' => $team->truck?->plate_number,
|
|
'status' => $team->status,
|
|
'total_trips' => $totalTrips,
|
|
'completed_trips' => $completedTrips,
|
|
'cancelled_trips' => (int) ($ts->cancelled_trips ?? 0),
|
|
'completion_rate_percent'=> $totalTrips > 0
|
|
? round(($completedTrips / $totalTrips) * 100, 1)
|
|
: 0,
|
|
'total_scans' => $totalScans,
|
|
'total_weight_kg' => $totalWeight,
|
|
'avg_load_per_trip_kg' => $totalTrips > 0
|
|
? (int) round($totalWeight / $totalTrips)
|
|
: 0,
|
|
'event_count' => (int) ($es->event_count ?? 0),
|
|
'last_trip_date' => $ts?->last_trip_date,
|
|
];
|
|
})->sortByDesc('total_scans')->values();
|
|
|
|
return $this->ok($leaderboard, null, [
|
|
'page' => $rows->currentPage(),
|
|
'per_page' => $rows->perPage(),
|
|
'total' => $rows->total(),
|
|
'last_page' => $rows->lastPage(),
|
|
'from' => $from->toDateString(),
|
|
'to' => $to->toDateString(),
|
|
]);
|
|
}
|
|
|
|
/**
|
|
* GET /api/v1/admin/reports/teams/{team:uuid}/profile
|
|
*
|
|
* Returns the full analytics profile for a single team:
|
|
* KPIs, daily scan trend, weekly scan trend, trip history, and full event log.
|
|
*/
|
|
public function teamProfile(Request $request, CollectionTeam $team): JsonResponse
|
|
{
|
|
$data = $request->validate([
|
|
'from' => ['nullable', 'date'],
|
|
'to' => ['nullable', 'date'],
|
|
'trip_page' => ['nullable', 'integer', 'min:1'],
|
|
'tenant_id' => ['nullable', 'integer', 'exists:tenants,id'],
|
|
]);
|
|
|
|
$from = Carbon::parse($data['from'] ?? now()->subDays(30))->startOfDay();
|
|
$to = Carbon::parse($data['to'] ?? now())->endOfDay();
|
|
$tripPage = (int) ($data['trip_page'] ?? 1);
|
|
$fromDate = $from->toDateString();
|
|
$toDate = $to->toDateString();
|
|
$fromDT = $from->toDateTimeString();
|
|
$toDT = $to->toDateTimeString();
|
|
|
|
// Re-load team fully (route model binding already resolved it, but load relations)
|
|
$team->load(['driver', 'scanner', 'truck', 'area']);
|
|
|
|
// Get all trip IDs for this team in range.
|
|
$tripIds = Tenancy::withoutScope(function () use ($team, $fromDate, $toDate) {
|
|
return Trip::withoutGlobalScopes()
|
|
->where('team_id', $team->id)
|
|
->whereBetween('scheduled_date', [$fromDate, $toDate])
|
|
->pluck('id');
|
|
});
|
|
|
|
// --- KPIs ---
|
|
$kpis = $this->buildKpis($team->id, $tripIds, $fromDate, $toDate, $fromDT, $toDT);
|
|
|
|
// --- Daily scans (group by DATE) ---
|
|
$dailyScans = Tenancy::withoutScope(function () use ($team, $fromDT, $toDT) {
|
|
return DB::table('collection_logs')
|
|
->selectRaw('DATE(scanned_at) as date, COUNT(*) as scans')
|
|
->where('team_id', $team->id)
|
|
->whereBetween('scanned_at', [$fromDT, $toDT])
|
|
->groupByRaw('DATE(scanned_at)')
|
|
->orderBy('date')
|
|
->get()
|
|
->map(fn ($r) => ['date' => $r->date, 'scans' => (int) $r->scans]);
|
|
});
|
|
|
|
// --- Weekly scans (group by YEARWEEK) ---
|
|
$weeklyScans = Tenancy::withoutScope(function () use ($team, $fromDT, $toDT) {
|
|
return DB::table('collection_logs')
|
|
->selectRaw("DATE(DATE_SUB(scanned_at, INTERVAL WEEKDAY(scanned_at) DAY)) as week_start, COUNT(*) as scans")
|
|
->where('team_id', $team->id)
|
|
->whereBetween('scanned_at', [$fromDT, $toDT])
|
|
->groupByRaw("DATE(DATE_SUB(scanned_at, INTERVAL WEEKDAY(scanned_at) DAY))")
|
|
->orderBy('week_start')
|
|
->get()
|
|
->map(fn ($r) => ['week_start' => $r->week_start, 'scans' => (int) $r->scans]);
|
|
});
|
|
|
|
// --- Trip history (paginated, 10/page) ---
|
|
$tripsData = Tenancy::withoutScope(function () use ($team, $fromDate, $toDate, $tripPage, $tripIds) {
|
|
$perPage = 10;
|
|
$trips = Trip::withoutGlobalScopes()
|
|
->withCount('collectionLogs')
|
|
->where('team_id', $team->id)
|
|
->whereBetween('scheduled_date', [$fromDate, $toDate])
|
|
->orderByDesc('scheduled_date')
|
|
->paginate($perPage, ['*'], 'trip_page', $tripPage);
|
|
|
|
return [
|
|
'data' => $trips->map(fn (Trip $t) => [
|
|
'trip_number' => $t->trip_number,
|
|
'scheduled_date' => $t->scheduled_date?->toDateString(),
|
|
'status' => $t->status,
|
|
'scans_count' => $t->collection_logs_count,
|
|
'total_load_kg' => (int) ($t->total_load_kg ?? 0),
|
|
'duration_minutes' => ($t->actual_start_time && $t->actual_end_time)
|
|
? (int) $t->actual_start_time->diffInMinutes($t->actual_end_time)
|
|
: null,
|
|
'actual_start_time' => $t->actual_start_time?->toIso8601String(),
|
|
'actual_end_time' => $t->actual_end_time?->toIso8601String(),
|
|
]),
|
|
'meta' => [
|
|
'page' => $trips->currentPage(),
|
|
'per_page' => $trips->perPage(),
|
|
'total' => $trips->total(),
|
|
'last_page' => $trips->lastPage(),
|
|
],
|
|
];
|
|
});
|
|
|
|
// --- Event log (all event types, last 200) ---
|
|
$events = Tenancy::withoutScope(function () use ($tripIds, $fromDT, $toDT) {
|
|
return DB::table('trip_timeline_events')
|
|
->join('trips', 'trips.id', '=', 'trip_timeline_events.trip_id')
|
|
->select(
|
|
'trip_timeline_events.event_type',
|
|
'trip_timeline_events.event_at',
|
|
'trip_timeline_events.notes',
|
|
'trip_timeline_events.metadata',
|
|
'trips.trip_number'
|
|
)
|
|
->whereIn('trip_timeline_events.trip_id', $tripIds)
|
|
->whereBetween('trip_timeline_events.event_at', [$fromDT, $toDT])
|
|
->orderByDesc('trip_timeline_events.event_at')
|
|
->limit(200)
|
|
->get()
|
|
->map(fn ($e) => [
|
|
'event_type' => $e->event_type,
|
|
'event_at' => $e->event_at,
|
|
'trip_number' => $e->trip_number,
|
|
'notes' => $e->notes,
|
|
'metadata' => $e->metadata ? json_decode($e->metadata, true) : null,
|
|
]);
|
|
});
|
|
|
|
return $this->ok([
|
|
'team' => [
|
|
'uuid' => $team->uuid,
|
|
'name' => $team->name,
|
|
'status' => $team->status,
|
|
'area' => $team->area?->name,
|
|
'driver' => $team->driver ? [
|
|
'name' => $team->driver->full_name,
|
|
'email' => $team->driver->email,
|
|
] : null,
|
|
'scanner' => $team->scanner ? [
|
|
'name' => $team->scanner->full_name,
|
|
'email' => $team->scanner->email,
|
|
] : null,
|
|
'truck' => $team->truck ? [
|
|
'plate' => $team->truck->plate_number,
|
|
'model' => $team->truck->model,
|
|
'capacity_kg' => $team->truck->capacity_kg,
|
|
] : null,
|
|
],
|
|
'kpis' => $kpis,
|
|
'daily_scans' => $dailyScans,
|
|
'weekly_scans' => $weeklyScans,
|
|
'trips' => $tripsData,
|
|
'events' => $events,
|
|
]);
|
|
}
|
|
|
|
/**
|
|
* Compute all KPI metrics for a team within the given date range.
|
|
*/
|
|
private function buildKpis(int $teamId, $tripIds, string $fromDate, string $toDate, string $fromDT, string $toDT): array
|
|
{
|
|
$tripStats = Tenancy::withoutScope(function () use ($teamId, $fromDate, $toDate) {
|
|
return DB::table('trips')
|
|
->select(
|
|
DB::raw('COUNT(*) as total_trips'),
|
|
DB::raw("SUM(CASE WHEN status IN ('completed','handed_off') THEN 1 ELSE 0 END) as completed_trips"),
|
|
DB::raw("SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_trips"),
|
|
DB::raw('SUM(COALESCE(total_load_kg,0)) as total_weight_kg'),
|
|
// On-time: actual_start_time <= scheduled_start_time + 30 minutes
|
|
DB::raw("SUM(CASE
|
|
WHEN actual_start_time IS NOT NULL AND scheduled_start_time IS NOT NULL
|
|
AND actual_start_time <= DATE_ADD(
|
|
CONCAT(scheduled_date, ' ', scheduled_start_time),
|
|
INTERVAL 30 MINUTE
|
|
)
|
|
THEN 1 ELSE 0 END) as on_time_trips")
|
|
)
|
|
->where('team_id', $teamId)
|
|
->whereBetween('scheduled_date', [$fromDate, $toDate])
|
|
->whereNull('deleted_at')
|
|
->first();
|
|
});
|
|
|
|
$scanStats = Tenancy::withoutScope(function () use ($teamId, $fromDT, $toDT) {
|
|
return DB::table('collection_logs')
|
|
->selectRaw('COUNT(*) as total_scans, SUM(COALESCE(weight_kg,0)) as total_scan_weight')
|
|
->where('team_id', $teamId)
|
|
->whereBetween('scanned_at', [$fromDT, $toDT])
|
|
->first();
|
|
});
|
|
|
|
$eventStats = Tenancy::withoutScope(function () use ($tripIds, $fromDT, $toDT) {
|
|
return DB::table('trip_timeline_events')
|
|
->selectRaw("
|
|
COUNT(*) as total_events,
|
|
SUM(CASE WHEN event_type IN ('incident_reported','breakdown') THEN 1 ELSE 0 END) as incident_count,
|
|
SUM(CASE WHEN event_type = 'stop_skipped' THEN 1 ELSE 0 END) as stops_skipped_count,
|
|
SUM(CASE WHEN event_type = 'detour_to_dumpsite' THEN 1 ELSE 0 END) as detours_count,
|
|
SUM(CASE WHEN event_type = 'breakdown' THEN 1 ELSE 0 END) as breakdowns_count,
|
|
SUM(CASE WHEN event_type = 'truck_full_warning' THEN 1 ELSE 0 END) as truck_full_count
|
|
")
|
|
->whereIn('trip_id', $tripIds)
|
|
->whereBetween('event_at', [$fromDT, $toDT])
|
|
->first();
|
|
});
|
|
|
|
$totalTrips = (int) ($tripStats->total_trips ?? 0);
|
|
$completedTrips = (int) ($tripStats->completed_trips ?? 0);
|
|
$onTimeTrips = (int) ($tripStats->on_time_trips ?? 0);
|
|
$totalWeight = (int) ($tripStats->total_weight_kg ?? 0);
|
|
$totalScans = (int) ($scanStats->total_scans ?? 0);
|
|
|
|
return [
|
|
'total_trips' => $totalTrips,
|
|
'completed_trips' => $completedTrips,
|
|
'cancelled_trips' => (int) ($tripStats->cancelled_trips ?? 0),
|
|
'completion_rate_percent' => $totalTrips > 0 ? round(($completedTrips / $totalTrips) * 100, 1) : 0,
|
|
'on_time_trips' => $onTimeTrips,
|
|
'on_time_rate_percent' => $completedTrips > 0 ? round(($onTimeTrips / $completedTrips) * 100, 1) : 0,
|
|
'total_scans' => $totalScans,
|
|
'total_weight_kg' => $totalWeight,
|
|
'avg_load_per_trip_kg' => $totalTrips > 0 ? (int) round($totalWeight / $totalTrips) : 0,
|
|
'incident_count' => (int) ($eventStats->incident_count ?? 0),
|
|
'stops_skipped_count' => (int) ($eventStats->stops_skipped_count ?? 0),
|
|
'detours_count' => (int) ($eventStats->detours_count ?? 0),
|
|
'breakdowns_count' => (int) ($eventStats->breakdowns_count ?? 0),
|
|
'truck_full_count' => (int) ($eventStats->truck_full_count ?? 0),
|
|
'total_events' => (int) ($eventStats->total_events ?? 0),
|
|
];
|
|
}
|
|
|
|
private function resolveTargetTenant(Request $request): ?Tenant
|
|
{
|
|
if (optional($request->user())->isSuperAdmin() && $request->filled('tenant_id')) {
|
|
return Tenant::find($request->input('tenant_id'));
|
|
}
|
|
|
|
return Tenancy::current();
|
|
}
|
|
}
|