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

• ✅ Create new car tracking records
• ✅ Update tracking milestones
• ✅ Filter cars by current status
• ✅ Display comprehensive tracking reports
• ✅ Edit existing tracking records
• ✅ Generate printable tracking reports
• ✅ Validate chassis number uniqueness
• ✅ 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 - Reviewer management
• • Import contract system - Source chassis numbers

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 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:

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:

// 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:

// 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:

// 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:

$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:

// 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:

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:

// 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

---

Workflow 2: Status Filtering and Reporting

---

🌐 URL Routes & Actions

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

// 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:

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:

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

// 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

// 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

-- 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:

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:

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:

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 - PHP 8.2 migration guide
• • 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