SAT-KDL Integration

Trip Management Integration

Documentation for the bi-directional trip management integration between KDL and SAT systems

Trip Management Integration

The trip management integration provides bi-directional synchronization between KDL and SAT for delivery trips, supporting both manual trip creation from SAT requests and automatic trip synchronization from KDL to SAT.

Overview

This integration handles two primary workflows:

KDL → SAT: Automatic synchronization of delivery trips with associated invoices
SAT → KDL: Auto-assignment of delivery trips based on external requests

The system ensures seamless coordination between logistics operations, delivery tracking, and reconciliation processes.

Technical Details

Code Location

KDL → SAT Sync: SyncTripToSatService (app/Services/Sync/SyncTripToSatService.php)
SAT → KDL Auto-assign: AutoAssignDeliveryTripController
Supporting Job: SyncInvoicesToSatJob
Trip Creation Job: CreateDeliveryTripFromERP
Audit Logging: LogAutoAssignTrip model

Primary Tables

Trip Management: Trip, DeliveryTrip, Delivery
Supporting Data: SalesHeader, SalesImport, SalSalesperson, Fleet
User Management: User
Audit Trail: LogAutoAssignTrip, tbl_logs

Integration Workflows

1. Trip Sync (KDL → SAT)

Data Flow

graph TD
    A[Trigger Sync] --> B[Sync Remaining Invoices]
    B --> C[Load Unsynced Trips]
    C --> D[Filter Dispatched Trips]
    D --> E[Get Trip Invoices]
    E --> F[Prepare Trip Data]
    F --> G[Send to SAT]
    G --> H[Update SAT_SYNC Flag]
    H --> I[Log Result]

Implementation

class SyncTripToSatService
{
    public function syncTripsToSat()
    {
        // Step 1: Sync remaining invoices first
        dispatch_sync(new SyncInvoicesToSatJob());
        
        // Step 2: Get unsynced trips
        $trips = Trip::where('SAT_SYNC', 'N')
            ->whereDate('EntryDate', today())
            ->get();
        
        foreach ($trips as $trip) {
            // Step 3: Only sync if all invoices are dispatched
            if ($this->allInvoicesDispatched($trip)) {
                $this->syncTripToSat($trip);
            }
        }
    }
    
    private function syncTripToSat($trip)
    {
        $tripData = [
            'reference_id' => $trip->TripID,
            'vehicle' => $trip->Vehicle,
            'scheduled_time' => $trip->EntryDate,
            'user_code' => $this->getSalespersonCode($trip->Driver),
            'warehouse_code' => $trip->Route,
            'status' => $trip->Status,
            'transaction_id' => $this->getTripInvoices($trip)
        ];
        
        // Send to SAT
        $response = $this->guzzleActions->postTripToSat($tripData);
        
        if ($response['success']) {
            $trip->update(['SAT_SYNC' => 'Y']);
        }
        
        $this->logSyncAttempt($tripData, $response);
    }
}

2. Auto-assign Trips (SAT → KDL)

Data Flow

graph TD
    A[SAT Request] --> B[Validate Authorization]
    B --> C[Check Duplicate Reference]
    C --> D[Log Pending Status]
    D --> E[Resolve User]
    E --> F[Manage Vehicle]
    F --> G[Check Existing Trips]
    G --> H[Retrieve Orders]
    H --> I[Dispatch Trip Creation Job]
    I --> J[Update Success Status]
    J --> K[Return Response]

Request Structure

// SAT sends trip assignment request
POST /api/auto-assign-delivery-trip
{
    "reference_id": "EXT-TRIP-001",
    "user_code": "SALES001",
    "warehouse_code": "WH-MAIN",
    "vehicle": "KBA123C",
    "scheduled_time": "2024-01-15T09:00:00Z",
    "transaction_id": ["INV-2024-001", "INV-2024-002"]
}

Implementation

class AutoAssignDeliveryTripController
{
    public function autoAssignTrip(Request $request)
    {
        try {
            // Step 1: Check authorization
            if (!$this->isAuthorized()) {
                return response()->json(['error' => 'Unauthorized'], 403);
            }
            
            // Step 2: Validate and check duplicates
            $this->validateRequest($request);
            
            // Step 3: Log pending status
            LogAutoAssignTrip::create([
                'reference_id' => $request->reference_id,
                'status' => 'PENDING',
                'request_data' => $request->all()
            ]);
            
            // Step 4: Process trip assignment
            $result = $this->processAutoAssignment($request);
            
            // Step 5: Update status to success
            LogAutoAssignTrip::where('reference_id', $request->reference_id)
                ->update(['status' => 'SUCCESS']);
            
            return response()->json($result);
            
        } catch (\Exception $e) {
            $this->logError($request, $e);
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }
}

Data Mapping

Trip Sync (KDL → SAT)

KDL Field	KDL Table	SAT Field	Description
`TripID`	Trip	`reference_id`	Unique trip identifier
`Vehicle`	Trip	`vehicle`	Vehicle registration/code
`EntryDate`	Trip	`scheduled_time`	Scheduled delivery time
`Route`	Trip	`warehouse_code`	Warehouse/route code
`Status`	Trip	`status`	Trip status
`Driver`	Trip	`user_code`	Mapped to salesperson code
Invoice numbers	SalesHeader	`transaction_id`	Array of related invoices

Auto-assign (SAT → KDL)

SAT Field	KDL Processing	Description
`reference_id`	LogAutoAssignTrip.reference_id	Prevents duplicate processing
`user_code`	User.user_reference	Resolves to actual user
`warehouse_code`	Used for user resolution	Combined with user_code
`vehicle`	Fleet.registration_number	Creates/assigns vehicle
`scheduled_time`	DeliveryTrip.scheduled_time	Trip scheduling
`transaction_id`	Order.invoice_number	Links orders to trip

Sample Data Structures

Trip Sync to SAT

{
  "reference_id": "TRIP-2024-001",
  "vehicle": "KBA123C",
  "scheduled_time": "2024-01-15T09:00:00Z",
  "user_code": "SALES001",
  "warehouse_code": "WH-MAIN",
  "status": "SCHEDULED",
  "transaction_id": [
    "INV-2024-001",
    "INV-2024-002",
    "INV-2024-003"
  ]
}

Auto-assign Request from SAT

{
  "reference_id": "EXT-TRIP-001",
  "user_code": "SALES001", 
  "warehouse_code": "WH-MAIN",
  "vehicle": "KBA456D",
  "scheduled_time": "2024-01-15T14:00:00Z",
  "transaction_id": [
    "INV-2024-004",
    "INV-2024-005"
  ]
}

Auto-assign Response

{
  "success": true,
  "trip_id": "AUTO-TRIP-001",
  "message": "Delivery trip created successfully",
  "assigned_orders": 2,
  "vehicle_assigned": "KBA456D",
  "user_resolved": "John Doe (SALES001)"
}

User & Vehicle Resolution

User Resolution Logic

class UserResolutionService
{
    public function resolveUser($userCode, $warehouseCode)
    {
        // Primary: Match by user_reference
        $user = User::where('user_reference', $userCode)->first();
        
        if (!$user) {
            // Fallback: Default user for the system
            $user = User::where('user_reference', 'DEFAULT TRIP USER')->first();
            
            if (!$user) {
                throw new \Exception("User not found for code: {$userCode}");
            }
        }
        
        return $user;
    }
}

Vehicle Management

class VehicleManagementService
{
    public function assignVehicle($vehicleCode, $userId)
    {
        // Check if vehicle exists
        $vehicle = Fleet::where('registration_number', $vehicleCode)->first();
        
        if (!$vehicle) {
            // Create new vehicle
            $vehicle = Fleet::create([
                'registration_number' => $vehicleCode,
                'vehicle_type' => 'DELIVERY_TRUCK',
                'status' => 'ACTIVE'
            ]);
        }
        
        // Assign to user
        $vehicle->update(['assigned_user_id' => $userId]);
        
        return $vehicle;
    }
}

Trip Creation Process

Existing Trip Check

class TripCreationService
{
    public function checkExistingTrip($userId, $date)
    {
        return DeliveryTrip::where('user_id', $userId)
            ->whereDate('scheduled_time', $date)
            ->first();
    }
    
    public function createOrUpdateTrip($data)
    {
        $existingTrip = $this->checkExistingTrip($data['user_id'], $data['date']);
        
        if ($existingTrip) {
            // Add orders to existing trip
            $this->addOrdersToTrip($existingTrip, $data['orders']);
            return $existingTrip;
        } else {
            // Create new trip
            return $this->createNewTrip($data);
        }
    }
}

Order Association

public function associateOrders($tripId, $invoiceNumbers)
{
    foreach ($invoiceNumbers as $invoiceNumber) {
        $order = Order::where('invoice_number', $invoiceNumber)->first();
        
        if ($order) {
            Delivery::create([
                'delivery_trip_id' => $tripId,
                'order_id' => $order->id,
                'status' => 'ASSIGNED'
            ]);
        }
    }
}

Configuration & Authorization

Auto-assign Authorization

class AuthorizationService
{
    public function isAutoAssignAuthorized()
    {
        $setting = Setting::where('key', 'auto_assign_delivery_trips_from_external_system')
            ->first();
        
        return $setting && $setting->value === 'yes';
    }
}

Environment Configuration

# Trip Management Configuration
TRIP_AUTO_ASSIGN_ENABLED=true
TRIP_DEFAULT_USER_REFERENCE="DEFAULT TRIP USER"
TRIP_SYNC_BATCH_SIZE=25
USE_V2_LOGISTICS=false

# SAT Integration
SAT_TRIP_ENDPOINT=/api/delivery-trips
SAT_TRIP_TIMEOUT=120

Settings Configuration

// Database settings
INSERT INTO settings (key, value) VALUES 
('auto_assign_delivery_trips_from_external_system', 'yes'),
('use_v2_logistics', 'no'),
('default_trip_user_reference', 'DEFAULT TRIP USER');

Error Handling & Recovery

Common Error Scenarios

1. Authorization Errors (403)

{
    "error": "System not configured for auto-assignment",
    "code": 403,
    "details": "auto_assign_delivery_trips_from_external_system setting is disabled"
}

2. Duplicate Reference (422)

{
    "error": "Duplicate reference ID",
    "code": 422,
    "reference_id": "EXT-TRIP-001",
    "existing_status": "SUCCESS"
}

3. User Resolution Errors (422)

{
    "error": "User not found for code: INVALID_USER",
    "code": 422,
    "suggestion": "Check user_reference field or use default user"
}

4. General Processing Errors (500)

{
    "error": "Failed to create delivery trip",
    "code": 500,
    "details": "Database constraint violation"
}

Error Recovery

-- Reset failed auto-assignments
UPDATE LogAutoAssignTrip 
SET status = 'PENDING'
WHERE status = 'FAILED' 
AND created_at >= NOW() - INTERVAL 1 DAY;

-- Reset trip sync status
UPDATE Trip 
SET SAT_SYNC = 'N'
WHERE TripID IN (
    SELECT doc_num FROM LogSatRequest 
    WHERE type = 'sat.delivery_trips' AND success = false
);

Monitoring & Logging

Trip Sync Monitoring

-- Trip sync success rate
SELECT 
    DATE(created_at) as sync_date,
    COUNT(CASE WHEN success = true THEN 1 END) as successful,
    COUNT(CASE WHEN success = false THEN 1 END) as failed,
    COUNT(*) as total
FROM LogSatRequest 
WHERE type = 'sat.delivery_trips'
AND created_at >= NOW() - INTERVAL 7 DAY
GROUP BY DATE(created_at)
ORDER BY sync_date DESC;

-- Pending trips for sync
SELECT COUNT(*) as pending_trips
FROM Trip 
WHERE SAT_SYNC = 'N'
AND EntryDate >= CURDATE();

Auto-assign Monitoring

-- Auto-assignment statistics
SELECT 
    status,
    COUNT(*) as count,
    MIN(created_at) as earliest,
    MAX(created_at) as latest
FROM LogAutoAssignTrip 
WHERE created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY status;

-- Processing time analysis
SELECT 
    AVG(TIMESTAMPDIFF(SECOND, created_at, updated_at)) as avg_processing_time,
    MAX(TIMESTAMPDIFF(SECOND, created_at, updated_at)) as max_processing_time
FROM LogAutoAssignTrip 
WHERE status = 'SUCCESS'
AND created_at >= NOW() - INTERVAL 7 DAY;

-- User resolution patterns
SELECT 
    JSON_EXTRACT(request_data, '$.user_code') as user_code,
    COUNT(*) as request_count,
    COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) as success_count
FROM LogAutoAssignTrip 
WHERE created_at >= NOW() - INTERVAL 30 DAY
GROUP BY JSON_EXTRACT(request_data, '$.user_code')
ORDER BY request_count DESC;

Testing & Validation

Unit Testing

class TripManagementTest extends TestCase
{
    public function test_trip_sync_to_sat()
    {
        $trip = Trip::factory()->create(['SAT_SYNC' => 'N']);
        
        Http::fake([
            config('sat.trip_endpoint') => Http::response(['success' => true], 200)
        ]);
        
        $service = new SyncTripToSatService();
        $service->syncTripToSat($trip);
        
        $this->assertEquals('Y', $trip->fresh()->SAT_SYNC);
    }
    
    public function test_auto_assign_creates_trip()
    {
        $request = [
            'reference_id' => 'TEST-TRIP-001',
            'user_code' => 'SALES001',
            'vehicle' => 'TEST123',
            'transaction_id' => ['INV-001']
        ];
        
        $response = $this->postJson('/api/auto-assign-delivery-trip', $request);
        
        $response->assertSuccessful();
        $this->assertDatabaseHas('LogAutoAssignTrip', [
            'reference_id' => 'TEST-TRIP-001',
            'status' => 'SUCCESS'
        ]);
    }
}

Integration Testing

# Test trip sync to SAT
php artisan trip:sync-to-sat --trip-id=TRIP-2024-001

# Test auto-assignment
curl -X POST "https://kdl-api.example.com/api/auto-assign-delivery-trip" \
  -H "Authorization: Bearer TOKEN" \
  -d '{
    "reference_id": "TEST-001",
    "user_code": "SALES001",
    "vehicle": "TEST123"
  }'

Performance Optimization

Database Indexing

-- Trip sync optimization
CREATE INDEX idx_trip_sat_sync ON Trip(SAT_SYNC, EntryDate);
CREATE INDEX idx_trip_driver ON Trip(Driver);

-- Auto-assign optimization
CREATE INDEX idx_logautoassigntrip_reference ON LogAutoAssignTrip(reference_id);
CREATE INDEX idx_user_reference ON User(user_reference);
CREATE INDEX idx_fleet_registration ON Fleet(registration_number);

Batch Processing

// Process trips in batches
class BatchTripSync
{
    public function syncInBatches($batchSize = 10)
    {
        Trip::where('SAT_SYNC', 'N')
            ->chunk($batchSize, function ($trips) {
                foreach ($trips as $trip) {
                    $this->syncTripToSat($trip);
                }
            });
    }
}

Best Practices

Data Consistency

Always sync invoices before trip sync
Validate trip data before sending to SAT
Use database transactions for multi-table operations
Implement proper error handling and rollback mechanisms

Performance

Use appropriate batch sizes for processing
Implement database indexes for key lookup fields
Monitor memory usage during bulk operations
Consider async processing for large volumes

Security

Validate all incoming requests
Implement proper authorization checks
Log all trip operations for audit trails
Use secure authentication for API communications

Error Handling

Implement comprehensive error logging
Provide meaningful error messages
Use retry mechanisms for transient failures
Set up monitoring for critical failures

Order Management Integration

Documentation for the bi-directional order management integration with user-region scoping between SAT and KDL

Error Handling & Troubleshooting

Comprehensive guide for error handling, troubleshooting, and recovery procedures for the SAT-KDL integration