27 — Imports¶

Overview¶

PyroRadius supports bulk customer creation via Excel file upload. This allows ISP operators to onboard large numbers of customers (from another system, a spreadsheet, or a field team's list) without entering records one by one. The import system uses PhpSpreadsheet for Excel parsing, validates every row, creates customer records in a batch, and returns a detailed per-row results report.

Supported Formats¶

Format	Extension	Notes
Excel 2007+	`.xlsx`	Preferred format
Excel 97-2003	`.xls`	Supported via PhpSpreadsheet

CSV is not directly supported by the import UI. Users should convert CSV to XLSX before importing.

Maximum file size: 10MB. Maximum rows per import: 1000 (configurable via IMPORT_MAX_ROWS env variable, default 1000).

Required and Optional Columns¶

The import template uses the first row as a header row. Column names are matched case-insensitively and whitespace is trimmed. Below is the full column reference:

Required Columns¶

Column Header	Description	Validation
`full_name`	Customer's full name	Required, string, max 255
`mobile`	Mobile number (any format, normalized to E.164 on import)	Required, must be a valid Pakistani number or E.164
`pppoe_username`	PPPoE username for RADIUS authentication	Required, unique across all customers (case-insensitive), max 100, alphanumeric + hyphens/underscores/dots
`pppoe_password`	PPPoE password (stored in plain text — FreeRADIUS requirement)	Required, min 6 chars
`package_id` OR `package_name`	Package to assign	One of the two is required. `package_id` takes precedence. `package_name` is matched case-insensitively.

Optional Columns¶

Column Header	Description	Default if Omitted
`area`	Area/zone name (matched to existing Area records)	None (no area assigned)
`dealer`	Dealer username or dealer code (matched to dealer user)	Assigned to the importing user's dealer context
`address`	Physical address	null
`cnic`	National ID number	null
`email`	Customer email	null
`expiry_date`	Expiry date in `YYYY-MM-DD` or `DD/MM/YYYY` format	Calculated from package billing cycle from today
`status`	`active` or `inactive`	`active`
`nas`	NAS hostname or IP (matched to existing NAS records)	Dealer's default NAS
`notes`	Internal notes about the customer	null
`install_date`	Installation date	Today

ImportController Flow¶

File: app/Http/Controllers/ImportController.php

Step 1 — Upload¶

The Import/Index.jsx page shows a file picker and template download link. On submit, the form POSTs the file to:

POST /import/customers
Content-Type: multipart/form-data

Step 2 — Parse¶

ImportController::store():

Validates the uploaded file (required, max 10MB, mimes: xlsx, xls).
Uses PhpSpreadsheet\IOFactory::load() to open the file.
Reads the active worksheet.
Extracts header row (row 1) and normalizes column names (trim, lowercase).
Iterates over data rows (row 2 onward), stopping at the first completely empty row or at IMPORT_MAX_ROWS.

Step 3 — Validate¶

For each row, a validation array is built and passed through Laravel's Validator::make(). Validation rules:

[
    'full_name'       => 'required|string|max:255',
    'mobile'          => 'required|string',
    'pppoe_username'  => 'required|string|max:100|regex:/^[a-zA-Z0-9._\-]+$/',
    'pppoe_password'  => 'required|string|min:6',
    'package_id'      => 'nullable|exists:packages,id',
    'package_name'    => 'nullable|string',
    'expiry_date'     => 'nullable|date',
    'status'          => 'nullable|in:active,inactive',
    'email'           => 'nullable|email',
    'cnic'            => 'nullable|string|max:15',
]

Additional cross-row checks performed in PHP (not via Validator):

Duplicate pppoe_username in DB: Customer::whereRaw('LOWER(pppoe_username) = ?', [strtolower($row['pppoe_username'])])->exists() — if true, row is skipped with error "pppoe_username already exists".
Duplicate pppoe_username within the same import file: A set of already-seen usernames is maintained in memory during the parse loop. Duplicate within the same file → skip with error "duplicate in file".
Package not found: If package_name is provided but doesn't match any package for the dealer → skip with error.
Area not found: If area is provided but doesn't match → skip with error (area is optional so a mismatch is treated as skip, not fatal).
NAS not found: If nas is provided but doesn't match → skip with error.

Step 4 — Create¶

Rows that pass all validation are created using Customer::create() within a database transaction. For each successful row:

customer_code is auto-generated via the sequence (NEXTVAL('customer_code_seq')) in the customer model boot hook.
created_by is set to Auth::id().
dealer_id is set from the importing user's dealer context (or matched from the dealer column if provided and the user has permission to import for other dealers).
RADIUS sync is dispatched: SyncCustomerRadiusJob::dispatch($customer->id, 'upsert')->onQueue('radius').

All creates are wrapped in a single transaction. If the transaction fails at the DB level (not validation), the entire batch is rolled back and an error is returned.

Step 5 — Results¶

After processing, ImportController::store() returns an Inertia redirect to Import/Results.jsx (or inlines results in the same page) with:

[
    'total'   => 150,
    'success' => 142,
    'skipped' => 6,
    'errors'  => 2,
    'rows'    => [
        ['row' => 2, 'status' => 'success', 'pppoe_username' => 'john.doe', 'message' => 'Created'],
        ['row' => 5, 'status' => 'skipped', 'pppoe_username' => 'jane.doe', 'message' => 'pppoe_username already exists'],
        ['row' => 8, 'status' => 'error',   'pppoe_username' => '',         'message' => 'full_name is required'],
        // ...
    ]
]

What Happens on Duplicate pppoe_username¶

Duplicate detection works at two levels:

Within the file: The first occurrence is created. Any subsequent row in the same file with the same pppoe_username (case-insensitive) is marked skipped with the message "Duplicate pppoe_username in file (row X was first)".
Already in database: The row is marked skipped with the message "pppoe_username already exists in system". The existing customer record is NOT modified.

Skipped rows do not cause the import to fail. The remaining valid rows continue to be processed.

Auto-Assigned Fields¶

Fields that are not taken from the import file but are set automatically:

Field	Value
`customer_code`	Auto-generated sequential code (e.g., `PYR-00142`)
`created_by`	`Auth::id()` — the logged-in user performing the import
`dealer_id`	Importing user's dealer context (or from `dealer` column if super_admin)
`created_at`	Current timestamp
`status`	`active` unless overridden in file
`radius_synced_at`	null (set after SyncCustomerRadiusJob completes)

Import Results Page¶

File: resources/js/Pages/Import/Index.jsx (or Import/Results.jsx)

After import, the results page shows:

Summary cards: Total rows processed, created successfully, skipped, errors.
Row detail table: One row per imported row showing: row number, pppoe_username, status badge (green success / yellow skip / red error), and message.
The table is filterable by status (show all / show errors only / show successes only).
A Download Report button exports the results as CSV.
A Import Another File button resets the form.

Permissions Required¶

Action	Required Permission
Access import page	`customers.import` or `super_admin`
Import customers for own dealer	`customers.import`
Import customers for other dealers	`super_admin` only

Dealers without customers.import permission will see a 403 if they attempt to access the import routes. The sidebar import link is hidden for users without this permission via the can() helper on the frontend.

Template Download¶

A sample XLSX template is provided for download from the Import page. It contains:

Row 1: Column headers in the exact format expected.
Rows 2–5: Example data rows with comments/notes in a sidebar column.
Column widths formatted for readability.
Data validation (dropdown) on the status column.

The template is stored as a static file at public/templates/customer_import_template.xlsx or generated dynamically by ImportController::template().

Common Import Errors and Fixes¶

Error Message	Cause	Fix
`pppoe_username already exists in system`	Username taken by existing customer	Change the username in the file
`Duplicate pppoe_username in file`	Same username appears twice in the file	Remove the duplicate row
`Package not found: "Gold 10Mb"`	Package name doesn't match any package for the dealer	Use the correct package name or use `package_id` column instead
`full_name is required`	Cell is empty	Fill in the customer's name
`pppoe_password must be at least 6 characters`	Password too short	Use a longer password
`Invalid date format in expiry_date`	Date is not in `YYYY-MM-DD` or `DD/MM/YYYY` format	Reformat the date column in Excel
`NAS not found: "nas01"`	NAS hostname doesn't match any record	Check the NAS name spelling or leave blank to use default
`File exceeds 10MB limit`	File too large	Split into multiple import files or remove unused columns/rows
`Too many rows (max 1000)`	File has more than 1000 data rows	Split the file and import in batches
`Unsupported file format`	File is CSV or ODS	Convert to XLSX using Excel or LibreOffice

PhpSpreadsheet Integration¶

Library: phpoffice/phpspreadsheet (installed via Composer).

The ImportController uses the auto-detect loader:

use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load($request->file('file')->getPathname());
$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, true);

The toArray() call returns the sheet as a PHP array with: - null as the null value for empty cells - true for formatted values (dates display as dates, not serial numbers) - true for calculated values (evaluates formulas) - true for column letters as array keys (A, B, C...)

The controller then maps letter keys to header names using row 1 as the lookup.

Memory limit is temporarily raised to 256MB during parsing (ini_set('memory_limit', '256M')) and restored after. Large files with many columns (>50) can be slow to parse — users should be advised to keep only necessary columns in their import file.