# DA System Data Upload

---

## blacksoil-da-system — 2026-05-08

### Overview

Added a self-contained feature to upload a DA System Excel report and persist all rows into a dedicated database table. The upload is processed asynchronously via the existing `CampaignsJobProvider` job queue so large files don't block the web request. Once processing completes the file is deleted from S3.

A loan listing page is also provided so uploaded data can be browsed immediately after import.

---

### Excel Source Format

The Excel file contains **113 data columns** per row covering loan identifiers, borrower demographics, branch/lender details, repayment schedules, OD/NPA metrics, insurance, charges, and co-borrower details.

Key columns (by index):

| Index | Header | DB Column |
|-------|--------|-----------|
| 0 | COMPANY_ID | `company_id` |
| 2 | LOAN_NO | `loan_no` |
| 4 | MEMBER_NAME | `member_name` |
| 5 | DATE_OF_BIRTH | `date_of_birth` |
| 7 | MOBILE_NUMBER | `mobile_number` |
| 16 | DISBURSEMENT_DATE | `disbursement_date` |
| 22 | AADHAAR_ID | `aadhaar_id` |
| 36 | BANK_ACCOUNT | `bank_account` |
| 52 | OD_DAYS | `od_days` |
| 75 | OUTSTANDING_PRINCIPAL | `outstanding_principal` |

Full column mapping is defined in `app/Imports/DaSystemDataImport.php`.

---

### Data Cleaning — Import Helpers

Two private helpers normalise raw Excel cell values before insert:

#### `str($value)`
- Strips leading `'` (Excel text-prefix escape character)
- Trims surrounding whitespace
- Returns `null` for empty values

Applied to all non-date columns.

#### `date($value)`
- Handles three formats that appear in the source file:
  1. **PHP `DateTimeInterface`** — returned by PhpSpreadsheet for properly formatted date cells → formatted as `d-m-Y`
  2. **Excel serial number** (e.g. `45889`) — any numeric value `> 30000` is converted via `PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject()` → formatted as `d-m-Y`
  3. **Apostrophe-prefixed string** (e.g. `'01-JAN-89`) — apostrophe stripped, value stored as-is

Applied to: `date_of_birth`, `nominee_dob`, `disbursement_date`, `loan_created_on`, `fund_allocation_date`, `first_demand_date`, `last_maturity_date`, `last_coll_date`, `next_demand_date`, `loan_closure_date`, `insurance_maturity_date`, `npa_date`.

---

### Architecture

```
app/
  DaSystemData.php                            — Eloquent model (SoftDeletes)
  Imports/
    DaSystemDataImport.php                    — ToCollection + WithChunkReading (500 rows/chunk)
  Http/Controllers/Admin/
    DaSystemDataController.php                — index, import, startFileProcess, loans
  CampaignsJobProvider.php                    — added da_system_data_upload case

database/migrations/
  2026_05_08_000000_create_da_system_data_table.php

resources/views/admin/da-system/
  index.blade.php                             — upload form + job status table
  loans.blade.php                             — paginated loans listing with search

routes/
  admin.php                                   — three new admin routes
```

---

### Routes

| Method | URL | Controller Method | Purpose |
|--------|-----|-------------------|---------|
| `GET` | `/admin/da-system` | `index()` | Upload page + file status |
| `POST` | `/admin/da-system/import` | `import()` | Receive file, queue job |
| `GET` | `/admin/da-system/loans` | `loans()` | Browse imported records |

---

### Upload Flow

1. Admin uploads `.xls` / `.xlsx` (max 18 MB) via the form at `/admin/da-system`
2. File is saved to S3 at `/da_system/{timestamped_name}` and also copied to `storage/cron_da_system/` locally
3. A `CampaignsJobProvider` job is queued with `from = da_system_data_upload`
4. The cron runner picks up the job and calls `DaSystemDataController::startFileProcess()`
5. `DaSystemDataImport` reads the file from S3 in 500-row chunks, cleans each cell, and bulk-inserts into `da_system_data`
6. On success the file is deleted from S3 via `Storage::disk('s3')->delete($filePath)`

---

### Job Status Section

The index page shows the last 20 `campaigns_jobs` entries where `from = da_system_data_upload`:

| `is_available` | Label |
|----------------|-------|
| `0` | Pending (yellow badge) |
| `1` | Processed (green badge) |

File name is extracted from the JSON `payload.file_name` field.

---

### Loans Listing (`/admin/da-system/loans`)

- 25 rows per page, newest first
- Search filters (GET params, preserved across pagination):
  - `search_name` — partial match on `member_name`
  - `search_loan` — partial match on `loan_no`
- Columns shown: Loan No, Member Name, Mobile, Branch, District, State, Disbursement Date, Status, OD Days, OD Bucket, Outstanding Principal

---

### Impact — Files Changed / Added

| File | Type | Change |
|------|------|--------|
| `database/migrations/2026_05_08_000000_create_da_system_data_table.php` | Added | Creates `da_system_data` table with 113 data columns, `uploaded_file`, `created_by`, timestamps, soft deletes. Column widths sized per field type to stay within MySQL's 65,535-byte inline row limit. |
| `app/DaSystemData.php` | Added | Eloquent model with `SoftDeletes` and full `$fillable` list |
| `app/Imports/DaSystemDataImport.php` | Added | `ToCollection + WithChunkReading` import; `str()` and `date()` helpers for cell normalisation |
| `app/Http/Controllers/Admin/DaSystemDataController.php` | Added | `index()`, `import()`, `startFileProcess()`, `loans()` |
| `app/CampaignsJobProvider.php` | Modified | Added `da_system_data_upload` case and `use DaSystemDataController` import |
| `resources/views/admin/da-system/index.blade.php` | Added | Upload form, View Loans button, file processing status table |
| `resources/views/admin/da-system/loans.blade.php` | Added | Paginated loans listing with name/loan search |
| `routes/admin.php` | Modified | Three new routes under `/admin/da-system` |