21 — Reports¶

Table of Contents¶

Overview
Available Report Types
ReportController
DefaultersController
CustomerCollectionsController
Date Filtering
Export to Excel
Reports/Index.jsx Page
Access Control
Report Query Reference

Overview¶

PyroRadius provides a suite of reporting tools that give admins and dealers visibility into billing performance, customer health, and collection efficiency. Reports are rendered as paginated tables in the UI and can be exported to Excel via PhpSpreadsheet.

All reports respect the role boundary — dealers only see data for their own customers; super admins can view across all dealers.

Reports are read-only and do not modify any data. Exporting a report does not change any record statuses.

Available Report Types¶

1. Defaulters Report (Overdue Customers)¶

What it shows: All customers whose expiry_date has passed and who have no paid invoice covering the current period. In other words, customers who are overdue for payment.

Primary use: Collections follow-up. The list is sorted by oldest expiry first so the most overdue customers appear at the top.

Key columns: Customer Name, Username, Phone, Package, Expiry Date, Days Overdue, Outstanding Amount, Dealer, Last Payment Date.

Managed by: DefaultersController

2. Collections Report¶

What it shows: Payments collected within a date range, grouped or filterable by dealer, engineer, or payment method.

Primary use: Daily/weekly/monthly reconciliation. Finance teams use this to tally cash collected by engineers against invoices.

Key columns: Payment Date, Customer Name, Invoice Number, Amount, Payment Method, Received By (engineer), Dealer.

Managed by: CustomerCollectionsController

3. Package-wise Breakdown¶

What it shows: For each package, shows the number of active customers and total recurring revenue contribution.

Primary use: Understanding which packages are most popular and their revenue weight.

Key columns: Package Name, Speed, Price, Active Customer Count, Monthly Revenue, % of Total Revenue.

4. Dealer-wise Summary¶

What it shows: Per-dealer aggregate: total customers, active, expired, suspended, total revenue this month, outstanding dues.

Primary use: Admin-level performance overview. Not available to dealer role.

Key columns: Dealer Name, Total Customers, Active, Expired, Suspended, Monthly Revenue, Outstanding.

5. Aging Report¶

What it shows: Outstanding invoices grouped by age buckets — 0–30 days, 31–60 days, 61–90 days, 90+ days overdue.

Primary use: Identifying chronically non-paying customers vs. temporarily overdue ones.

Key columns: Age Bucket, Customer Count, Total Outstanding Amount.

6. Billing Archive Report¶

What it shows: Historical invoice records filterable by date range, dealer, and customer. Includes all invoice statuses (paid, unpaid, cancelled).

Primary use: Dispute resolution and historical billing audit.

Key columns: Invoice Number, Customer, Package, Amount, Status, Issue Date, Due Date, Paid Date, Paid By.

ReportController¶

Class: App\Http\Controllers\ReportController
Route prefix: /admin/reports

index()¶

Renders the main reports landing page (Reports/Index.jsx) with no data loaded initially. Data is fetched when the user selects a report type and applies filters. This avoids loading all report data on every page visit.

public function index(): Response
{
    return Inertia::render('Reports/Index', [
        'dealers'  => Dealer::select('id', 'name')->orderBy('name')->get(),
        'packages' => Package::select('id', 'name')->orderBy('name')->get(),
    ]);
}

packageWise(Request $request): JsonResponse¶

Returns package-wise breakdown data. Accepts optional dealer_id filter.

$data = Package::query()
    ->withCount(['customers as active_count' => fn($q) => $q->where('status', 'active')])
    ->withSum(['customers as monthly_revenue' => fn($q) => $q->where('status', 'active')], 'price')
    ->when($request->dealer_id, fn($q, $id) => $q->whereHas('customers', fn($q2) => $q2->where('dealer_id', $id)))
    ->orderByDesc('active_count')
    ->get();

dealerWise(Request $request): JsonResponse¶

Super admin only. Aggregates per-dealer stats using a subquery strategy to avoid N+1:

$dealers = Dealer::query()
    ->withCount('customers as total_customers')
    ->withCount(['customers as active_count'    => fn($q) => $q->where('status', 'active')])
    ->withCount(['customers as expired_count'   => fn($q) => $q->where('status', 'expired')])
    ->withCount(['customers as suspended_count' => fn($q) => $q->where('status', 'suspended')])
    ->with(['monthlyRevenue', 'outstandingDues']) // eager-loaded computed relations
    ->orderBy('name')
    ->get();

aging(Request $request): JsonResponse¶

Groups unpaid invoices by how many days past their due date they are:

SELECT
    CASE
        WHEN CURRENT_DATE - due_date <= 30  THEN '0-30 days'
        WHEN CURRENT_DATE - due_date <= 60  THEN '31-60 days'
        WHEN CURRENT_DATE - due_date <= 90  THEN '61-90 days'
        ELSE '90+ days'
    END AS age_bucket,
    COUNT(*) AS invoice_count,
    SUM(amount) AS total_outstanding
FROM invoices
WHERE status = 'unpaid'
  AND due_date < CURRENT_DATE
  AND (dealer_id = :dealer_id OR :dealer_id IS NULL)
GROUP BY age_bucket
ORDER BY MIN(due_date);

billingArchive(Request $request): Response | JsonResponse¶

Returns paginated invoice history. Accepts from, to, dealer_id, customer_id, status filters.

$invoices = Invoice::query()
    ->with(['customer', 'customer.dealer', 'payments'])
    ->when($request->from,        fn($q, $v) => $q->whereDate('created_at', '>=', $v))
    ->when($request->to,          fn($q, $v) => $q->whereDate('created_at', '<=', $v))
    ->when($request->dealer_id,   fn($q, $v) => $q->whereRelation('customer', 'dealer_id', $v))
    ->when($request->customer_id, fn($q, $v) => $q->where('customer_id', $v))
    ->when($request->status,      fn($q, $v) => $q->where('status', $v))
    ->orderByDesc('created_at')
    ->paginate(50);

DefaultersController¶

Class: App\Http\Controllers\DefaultersController
Route: GET /admin/reports/defaulters

What "Defaulter" Means¶

A customer is a defaulter if: - customers.expiry_date < CURRENT_DATE (expiry has passed) - AND their account status is expired or suspended (not active) - OR status = active but expiry_date < CURRENT_DATE (technically expired but not yet suspended by cron)

index() Method¶

public function index(Request $request): Response
{
    $defaulters = Customer::query()
        ->where('expiry_date', '<', now()->toDateString())
        ->whereIn('status', ['expired', 'suspended'])
        ->with(['package', 'dealer', 'lastPayment'])
        ->when($request->dealer_id, fn($q, $v) => $q->where('dealer_id', $v))
        ->when($request->days_overdue, fn($q, $v) =>
            $q->where('expiry_date', '<=', now()->subDays($v)->toDateString())
        )
        ->orderBy('expiry_date', 'asc') // most overdue first
        ->paginate(50);

    return Inertia::render('Reports/Defaulters', [
        'defaulters'  => $defaulters,
        'dealers'     => Dealer::select('id', 'name')->get(),
        'totalAmount' => $this->calculateOutstanding($request),
    ]);
}

calculateOutstanding()¶

Sums the unpaid invoice amounts for all matching defaulters:

private function calculateOutstanding(Request $request): float
{
    return Invoice::query()
        ->whereIn('status', ['unpaid', 'overdue'])
        ->whereHas('customer', function ($q) use ($request) {
            $q->where('expiry_date', '<', now()->toDateString())
              ->when($request->dealer_id, fn($q2, $v) => $q2->where('dealer_id', $v));
        })
        ->sum('amount');
}

Export¶

DefaultersController::export(Request $request) calls the same query and pipes results to PhpSpreadsheet — see Export to Excel.

CustomerCollectionsController¶

Class: App\Http\Controllers\CustomerCollectionsController
Route prefix: /admin/reports/collections

index()¶

Returns payments within a date range, with optional grouping by dealer or engineer.

$payments = Payment::query()
    ->with(['customer', 'customer.dealer', 'recordedBy', 'invoice'])
    ->when($request->from,        fn($q, $v) => $q->whereDate('created_at', '>=', $v))
    ->when($request->to,          fn($q, $v) => $q->whereDate('created_at', '<=', $v))
    ->when($request->dealer_id,   fn($q, $v) => $q->whereRelation('customer', 'dealer_id', $v))
    ->when($request->engineer_id, fn($q, $v) => $q->where('recorded_by', $v))
    ->orderByDesc('created_at')
    ->paginate(50);

$summary = [
    'total_collected' => $payments->sum('amount'),
    'payment_count'   => $payments->total(),
];

byDealer()¶

Returns a grouped summary — one row per dealer — for a given date range. Useful for the monthly dealer reconciliation meeting.

$byDealer = Payment::query()
    ->join('customers', 'payments.customer_id', '=', 'customers.id')
    ->join('dealers', 'customers.dealer_id', '=', 'dealers.id')
    ->whereBetween('payments.created_at', [$request->from, $request->to])
    ->groupBy('dealers.id', 'dealers.name')
    ->select('dealers.name as dealer_name', DB::raw('SUM(payments.amount) as total'), DB::raw('COUNT(*) as count'))
    ->orderByDesc('total')
    ->get();

Date Filtering¶

All date-filterable reports accept from and to query parameters in YYYY-MM-DD format.

Default Ranges¶

If neither from nor to is provided, reports default to the current calendar month:

$from = $request->from ?? now()->startOfMonth()->toDateString();
$to   = $request->to   ?? now()->endOfMonth()->toDateString();

Frontend Date Picker¶

Reports/Index.jsx uses a date range picker component that produces two separate date inputs. When both are set, the URL is updated with ?from=YYYY-MM-DD&to=YYYY-MM-DD, allowing the filtered view to be bookmarked or shared.

Validation¶

$request->validate([
    'from' => 'nullable|date|before_or_equal:to',
    'to'   => 'nullable|date|after_or_equal:from',
]);

Export to Excel¶

All major reports support an Export to Excel action that triggers a download of an .xlsx file built with PhpSpreadsheet.

How Export Works¶

Each report controller has an export() method that:

Runs the same query as index() but without pagination (fetches all rows).
Instantiates PhpOffice\PhpSpreadsheet\Spreadsheet.
Writes header row (column labels).
Iterates rows and writes each cell.
Applies basic styling (bold headers, autofit columns, freeze first row).
Streams the file to the browser using Xlsx::createWriter() and ob_start().

Example Export Response¶

public function export(Request $request): StreamedResponse
{
    $data = $this->getDefaulters($request); // same query, no paginate()

    $spreadsheet = new Spreadsheet();
    $sheet       = $spreadsheet->getActiveSheet();

    $headers = ['Name', 'Username', 'Phone', 'Package', 'Expiry Date', 'Days Overdue', 'Outstanding'];
    foreach ($headers as $col => $label) {
        $sheet->setCellValueByColumnAndRow($col + 1, 1, $label);
    }

    foreach ($data as $row => $customer) {
        $sheet->setCellValueByColumnAndRow(1, $row + 2, $customer->name);
        $sheet->setCellValueByColumnAndRow(2, $row + 2, $customer->username);
        // ... etc
    }

    $writer   = new Xlsx($spreadsheet);
    $filename = 'defaulters_' . now()->format('Y-m-d') . '.xlsx';

    return response()->streamDownload(function () use ($writer) {
        $writer->save('php://output');
    }, $filename, [
        'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    ]);
}

Export Routes¶

Report	Route
Defaulters	`GET /admin/reports/defaulters/export`
Collections	`GET /admin/reports/collections/export`
Package-wise	`GET /admin/reports/packages/export`
Dealer-wise	`GET /admin/reports/dealers/export`
Aging	`GET /admin/reports/aging/export`
Billing Archive	`GET /admin/reports/billing-archive/export`

All export routes accept the same filter parameters as their corresponding index routes.

Reports/Index.jsx Page¶

Path: resources/js/Pages/Reports/Index.jsx

Layout¶

<ReportsIndex>
  ├── <PageHeader title="Reports" />
  ├── <ReportTypeSelector>
  │     ├── Defaulters
  │     ├── Collections
  │     ├── Package-wise
  │     ├── Dealer-wise (admin only)
  │     ├── Aging
  │     └── Billing Archive
  ├── <FilterPanel>
  │     ├── Date Range Picker (from / to)
  │     ├── Dealer Selector (admin) or hidden (dealer)
  │     ├── Status Filter (where applicable)
  │     └── Apply Filters Button
  ├── <ExportButton /> (triggers download)
  └── <ReportTable> (lazy-loaded when filters applied)
        └── <DataTable columns={...} data={rows} />

State Management¶

Report data is not pre-loaded. The component tracks: - selectedReport — which report type is active - filters — current filter values - data — fetched report rows (null until first query) - loading — loading spinner state

On "Apply Filters", a router.get() call with current filters is made. Inertia returns the page with a reportData prop populated.

Access Control¶

Report	Super Admin	Dealer Admin	Engineer
Defaulters	All dealers	Own dealer only	Own dealer only (read)
Collections	All dealers	Own dealer only	Payments they recorded
Package-wise	All	Own dealer only	No
Dealer-wise	Yes (all)	No	No
Aging	All dealers	Own dealer only	No
Billing Archive	All dealers	Own dealer only	No
Export (any)	Yes	Yes (own data)	No

Permission gate: reports.view for viewing, reports.export for Excel downloads.

Engineers are restricted to viewing collections limited to payments they personally recorded, enforced by filtering on recorded_by = auth()->id() when role is engineer.

Report Query Reference¶

Defaulters (core query)¶

SELECT c.*, p.name as package_name, d.name as dealer_name,
       CURRENT_DATE - c.expiry_date AS days_overdue
FROM customers c
JOIN packages p ON c.package_id = p.id
JOIN dealers d ON c.dealer_id = d.id
WHERE c.expiry_date < CURRENT_DATE
  AND c.status IN ('expired', 'suspended')
  AND (c.dealer_id = :dealer_id OR :dealer_id IS NULL)
ORDER BY c.expiry_date ASC;

Monthly Revenue¶

SELECT SUM(amount) AS monthly_revenue
FROM payments
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE)
  AND (dealer_id = :dealer_id OR :dealer_id IS NULL);

Outstanding Dues¶

SELECT SUM(amount) AS outstanding
FROM invoices
WHERE status IN ('unpaid', 'overdue')
  AND (
    customer_id IN (SELECT id FROM customers WHERE dealer_id = :dealer_id)
    OR :dealer_id IS NULL
  );

Last updated: 2026-07-13