# Car Tracking Controller Documentation

**File**: `/controllers/carTrackingController.php`  
**Purpose**: Manages car shipment tracking through import process stages  
**Last Updated**: December 20, 2024  
**Total Functions**: 6  
**Lines of Code**: ~198

---

## 📋 Overview

The Car Tracking Controller manages the complete lifecycle tracking of imported cars from purchase through delivery. It handles:
- Multi-stage car shipment tracking (7 stages)
- Chassis number-based car identification
- Status-based filtering and reporting
- User assignment tracking for each stage
- Comments and audit trail for each milestone
- Visual status reporting with stage descriptions

### Primary Functions
- [x] Create new car tracking records
- [x] Update tracking milestones
- [x] Filter cars by current status
- [x] Display comprehensive tracking reports
- [x] Edit existing tracking records
- [x] Generate printable tracking reports
- [x] Validate chassis number uniqueness
- [x] Track user assignments per stage

### Car Import Stages (7 Stages)
1. **Buy** - Purchase from dealer showroom
2. **Warehouse** - Storage in warehouse
3. **E2** - Ocean shipping
4. **Port** - Arrival at Egyptian port
5. **Arrival** - Delivery to Viking warehouse
6. **Showroom** - Transfer to showroom
7. **Deliver** - Final delivery to customer

### Related Controllers
- [carReviewerController.php](carReviewerController.md) - Reviewer management
- [Import contract system](#) - Source chassis numbers

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **cartracking** | Main tracking records | id, chasis, userid, sysdate, buydate, warehousedate, e2date, portdate, arrivaldate, showroomdate, deliverdate |

### Stage-Specific Columns
| Stage | Date Column | User Column | Comment Column | Description |
|-------|-------------|-------------|----------------|-------------|
| Buy | buydate | buyuserid | buycomment | Dealer showroom purchase |
| Warehouse | warehousedate | warehouseuserid | warehousecomment | Initial warehouse storage |
| E2 | e2date | e2userid | e2comment | Ocean shipping |
| Port | portdate | portuserid | portcomment | Port of Egypt arrival |
| Arrival | arrivaldate | arrivaluserid | arrivalcomment | Viking warehouse delivery |
| Showroom | showroomdate | showroomuserid | showroomcomment | Showroom transfer |
| Deliver | deliverdate | deliveruserid | delivercomment | Customer delivery |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **importcontract** | Car import contracts | id, chasisno, brand, carcolor, carmodel, del |
| **user** | System users/employees | userid, employeename |

---

## 🔑 Key Functions

### 1. **Default Action** - Search Form
**Location**: Lines 8-16  
**Purpose**: Display initial search form with chassis selection

**Process Flow**:
1. Load distinct chassis numbers from import contracts
2. Display search form template
3. Allow user to select chassis and status filter

**SQL Operations**:
```sql
SELECT DISTINCT chasisno FROM importcontract WHERE chasisno != '' AND del < 2
```

---

### 2. **show** - Display Tracking Results
**Location**: Lines 17-79  
**Purpose**: Search and display car tracking records with status filtering

**Function Signature**:
```php
// Triggered when: do=show
elseif ($do == "show") {
```

**Process Flow**:
1. **Input Processing**:
   - Get chassis filter and status filter from POST
   - Build dynamic WHERE clause

2. **Status Filtering Logic**:
   - Each status filter shows cars ONLY in that specific stage
   - Uses complex date conditions to ensure single-stage filtering

3. **Data Enhancement**:
   - Load all matching tracking records
   - For each record, determine current status based on latest completed stage
   - Load user names for "last updated by" information
   - Set display status and last update date

4. **Status Determination Logic**:
```php
// Determine current status based on latest completed stage
if ($single->deliverdate != "0000-00-00 00:00:00") {
    $single->status = "Delivered to the customer";
} elseif ($single->showroomdate != "0000-00-00 00:00:00") {
    $single->status = "vroom showroom";
} // ... and so on through all stages
```

**Status Filters**:
- `buy` - Cars purchased but not yet warehoused
- `warehouse` - Cars warehoused but not yet shipped
- `e2` - Cars on ocean shipping
- `port` - Cars arrived at port
- `arrival` - Cars at Viking warehouse
- `showroom` - Cars in showroom
- `deliver` - Cars delivered to customers

---

### 3. **edit** - Edit Tracking Record
**Location**: Lines 80-90  
**Purpose**: Load existing tracking record for editing

**Function Signature**:
```php
// Triggered when: do=edit
elseif ($do == "edit") {
```

**Process Flow**:
1. Get tracking record ID from URL
2. Load complete tracking record
3. Load chassis dropdown options
4. Display edit form with current data

---

### 4. **editprint** - Print View
**Location**: Lines 91-111  
**Purpose**: Generate printable tracking report with full user details

**Process Flow**:
1. Load tracking record by ID
2. Load user names for ALL stage assignments:
   - `buyuser`, `warehouseuser`, `e2user`, `portuser`
   - `arrivaluser`, `showroomuser`, `deliveruser`
3. Display print-friendly template

**User Loading Pattern**:
```php
$trackingData['buyuser'] = R::findOne('user', 'userid = ?', [$trackingData['buyuserid']])->employeename;
$trackingData['warehouseuser'] = R::findOne('user', 'userid = ?', [$trackingData['warehouseuserid']])->employeename;
// ... etc for all stages
```

---

### 5. **savedata** - Save Tracking Updates
**Location**: Lines 112-185  
**Purpose**: Process form submission and update tracking record

**Function Signature**:
```php
// Triggered when: do=savedata
else if ($do == 'savedata') {
```

**Process Flow**:
1. **Input Validation**:
   - Filter all stage dates and comments from POST
   - Determine if creating new record or updating existing

2. **User Assignment Logic**:
   - For each stage, if date is being set for first time OR changed
   - Assign current user as the stage user
   - This creates audit trail of who completed each stage

3. **Stage Update Pattern**:
```php
if($buydate != "0000-00-00 00:00:00") {
    if($trackingData->buydate != $buydate)
        $trackingData->buyuserid = $_SESSION['userid'];
}
$trackingData->buydate = $buydate;
$trackingData->buycomment = $buycomment;
```

4. **Database Operation**:
   - Store complete tracking record
   - Redirect to show page on success
   - Redirect to main page on error

**Key Features**:
- **Automatic User Assignment**: User who updates a stage is recorded
- **Change Detection**: Only assigns user when date actually changes
- **Complete Audit Trail**: Tracks who completed each stage and when

---

### 6. **checkExist** - Chassis Validation
**Location**: Lines 186-198  
**Purpose**: AJAX endpoint to validate chassis number uniqueness

**Function Signature**:
```php
// Triggered when: do=checkExist
elseif ($do == "checkExist") {
```

**Process Flow**:
1. Get chassis number and optional existing record ID
2. Search for existing records with same chassis
3. Exclude current record ID from search (for updates)
4. Return existing record ID or -1 if unique

**Response**:
- Returns existing tracking record ID if duplicate found
- Returns -1 if chassis number is available
- Used for front-end validation

---

## 🔄 Workflows

### Workflow 1: New Car Tracking Setup
```
┌─────────────────────────────────────────────────────────────┐
│                START: New Car Arrival                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Select Chassis Number                                   │
│     - Load available chassis from import contracts         │
│     - Choose specific car to track                         │
│     - Validate chassis uniqueness                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Create Initial Tracking Record                          │
│     - Set chassis number                                    │
│     - Record system date and creating user                 │
│     - Initialize all stage dates to "0000-00-00 00:00:00" │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Update Stages as Car Progresses                        │
│     FOR EACH completed stage:                               │
│       │                                                     │
│       ├─→ Set stage date to completion time                │
│       ├─→ Assign current user as stage user               │
│       ├─→ Add optional comments                           │
│       └─→ Save record                                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Track Until Final Delivery                             │
│     - Update stages in chronological order                 │
│     - Maintain audit trail of users and dates             │
│     - Generate reports and status updates                  │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Status Filtering and Reporting
```
┌─────────────────────────────────────────────────────────────┐
│              START: Generate Status Report                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Select Filter Criteria                                  │
│     - Choose specific chassis (optional)                    │
│     - Select status filter                                  │
│     - Submit search form                                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Dynamic SQL Query                                 │
│     - Add chassis filter if specified                       │
│     - Add complex status-based date filters                │
│     - Ensure cars appear in only one status category       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Each Tracking Record                            │
│     FOR EACH car in results:                                │
│       │                                                     │
│       ├─→ Determine current status (latest completed stage)│
│       ├─→ Load user who last updated the record           │
│       ├─→ Set display status message                      │
│       ├─→ Set last update date                            │
│       └─→ Format for display                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate Report Output                                  │
│     - Display results in table format                      │
│     - Show current status and last update info            │
│     - Provide edit links for each record                   │
│     - Include chassis filter form for refinement          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| (no parameters) | Default action | Display chassis search form |
| `do=show` | Show tracking | Search and display tracking records |
| `do=edit` | Edit tracking | Load tracking record for editing |
| `do=editprint` | Print view | Generate printable tracking report |
| `do=savedata` | Save updates | Process form submission |
| `do=checkExist` | AJAX validation | Check chassis number uniqueness |

### Required Parameters by Action

**Search and Display** (`do=show`):
- `chasis` - Chassis number filter (optional)
- `status` - Stage filter (buy/warehouse/e2/port/arrival/showroom/deliver)

**Edit Tracking** (`do=edit`):
- `id` - Tracking record ID (GET parameter)

**Print View** (`do=editprint`):
- `id` - Tracking record ID (GET parameter)

**Save Data** (`do=savedata`):
- `id` - Record ID (empty for new records)
- `chasis` - Chassis number
- Stage data: `buydate`, `buycomment`, `warehousedate`, `warehousecomment`, etc.

**Check Existence** (`do=checkExist`):
- `chasis` - Chassis number to validate
- `id` - Current record ID (optional, for updates)

---

## 🧮 Status Logic & Calculations

### Status Determination Algorithm
```php
// Determine current status based on latest completed stage
// Checks stages in reverse chronological order
if ($single->deliverdate != "0000-00-00 00:00:00") {
    $single->status = "Delivered to the customer";
    $single->lastdate = $single->deliverdate;
} elseif ($single->showroomdate != "0000-00-00 00:00:00") {
    $single->status = "vroom showroom";
    $single->lastdate = $single->showroomdate;
} elseif ($single->arrivaldate != "0000-00-00 00:00:00") {
    $single->status = "Viking warehouse";
    $single->lastdate = $single->arrivaldate;
} elseif ($single->portdate != "0000-00-00 00:00:00") {
    $single->status = "Port of Egypt";
    $single->lastdate = $single->portdate;
} elseif ($single->e2date != "0000-00-00 00:00:00") {
    $single->status = "Ocean flight";
    $single->lastdate = $single->e2date;
} elseif ($single->warehousedate != "0000-00-00 00:00:00") {
    $single->status = "warehouse";
    $single->lastdate = $single->warehousedate;
} elseif ($single->buydate != "0000-00-00 00:00:00") {
    $single->status = "Dealer showroom";
    $single->lastdate = $single->buydate;
} else {
    $single->status = "لم يتم التتبع بعد";  // "Not tracked yet"
    $single->lastdate = "";
}
```

### Status Filter Logic
Each status filter uses complex SQL to ensure cars appear in only one category:

**Buy Status Filter**:
```sql
buydate != '0000-00-00 00:00:00' AND 
warehousedate = '0000-00-00 00:00:00' AND 
e2date = '0000-00-00 00:00:00' AND 
-- ... all subsequent stages must be unset
```

**Warehouse Status Filter**:
```sql
warehousedate != '0000-00-00 00:00:00' AND 
e2date = '0000-00-00 00:00:00' AND 
-- ... all subsequent stages must be unset
```

### User Assignment Logic
```php
// Only assign user when stage date is actually set or changed
if($buydate != "0000-00-00 00:00:00") {
    if($trackingData->buydate != $buydate)
        $trackingData->buyuserid = $_SESSION['userid'];
}
```

---

## 🔒 Security & Permissions

### Input Validation
```php
// All input filtered through filter_input
$chasis = filter_input(INPUT_POST, 'chasis');
$buydate = filter_input(INPUT_POST, 'buydate');
$id = filter_input(INPUT_GET, 'id');
```

### Data Integrity
- Chassis number uniqueness validation
- Stage date validation (prevents invalid dates)
- User ID validation through session
- Exception handling for database operations

### Access Control
- Session-based user tracking (`$_SESSION['userid']`)
- User assignment for audit trail
- No direct permission checks (relies on application-level security)

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `cartracking(chasis)` - For chassis-based searches
   - `cartracking(buydate, warehousedate, e2date, ...)` - For status filtering
   - `importcontract(chasisno, del)` - For chassis dropdown

2. **Query Optimization**:
   - Status filters use multiple date column checks
   - Could benefit from computed status column
   - User lookups could be optimized with JOINs

3. **N+1 Query Issues**:
   - Each tracking record loads user separately
   - Consider batching user lookups
   - Print view loads 8 users per record individually

### Performance Improvements
```sql
-- Current approach (N+1 queries)
SELECT * FROM cartracking WHERE ...;
-- Then for each record:
SELECT employeename FROM user WHERE userid = ?;

-- Optimized approach (single query)
SELECT ct.*, u.employeename as lastuser 
FROM cartracking ct 
LEFT JOIN user u ON u.userid = ct.userid 
WHERE ...;
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Cars Appearing in Multiple Status Categories**
**Issue**: Car shows up in multiple status filters  
**Cause**: Incorrect date values or logic errors in status filtering

**Debug**:
```sql
SELECT chasis, buydate, warehousedate, e2date, portdate, arrivaldate, showroomdate, deliverdate 
FROM cartracking WHERE chasis = '[CHASSIS_NUMBER]';
```

**Fix**: Ensure only sequential stage dates are set

### 2. **Missing User Names**
**Issue**: User names appear blank or null  
**Cause**: User ID references to non-existent users

**Debug**:
```sql
SELECT ct.chasis, ct.userid, u.employeename 
FROM cartracking ct 
LEFT JOIN user u ON u.userid = ct.userid 
WHERE u.employeename IS NULL;
```

### 3. **Status Not Updating Correctly**
**Issue**: Current status doesn't reflect latest stage  
**Cause**: Date comparison logic or invalid date formats

**Fix**: Check date format consistency and status determination logic

### 4. **Chassis Dropdown Empty**
**Issue**: No chassis numbers available for selection  
**Cause**: Import contract data missing or filtered out

**Debug**:
```sql
SELECT chasisno, del FROM importcontract 
WHERE chasisno != '' ORDER BY chasisno;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Complete Car Journey
```
1. Create new tracking record with chassis number
2. Update buy stage with date and comment
3. Progress through each stage in order
4. Verify status changes correctly at each step
5. Check user assignments are recorded
6. Verify final delivery status
```

### Test Case 2: Status Filtering
```
1. Create cars in different stages
2. Test each status filter
3. Verify cars appear in correct categories only
4. Check no cars appear in multiple filters
```

### Test Case 3: Chassis Validation
```
1. Attempt to create duplicate chassis tracking
2. Verify AJAX validation works
3. Test edit mode allows same chassis
4. Check error handling
```

### Test Case 4: Print Report
```
1. Create tracking record with multiple stage users
2. Generate print view
3. Verify all user names appear correctly
4. Check formatting and completeness
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [carReviewerController.md](carReviewerController.md) - Reviewer management
- [Import Contract System](#) - Source data for chassis numbers

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur