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

Order Management Integration (SAT ↔ KDL)

Overview

The order management integration enables bi-directional sales order processing between SAT and KDL systems with user-region scoping, staging, validation, and synchronization features.

Technical Architecture

Code Location

Order Ingestion: SATIntegrationController@postOrder - Receives orders from SAT
Order Management: TempSalesHeaderController - Handles staging, dashboard, and sync operations
Data Model: TempSalesHeader - Eloquent model with business logic
User Access Control: UserRegion model with region-based filtering
UI Dashboard: orders.blade.php - Order review and processing interface

Database Tables

Staging: temp_sales_headers - Temporary order storage with full audit trail
User Access: user_regions - User-region mapping for access control
Main Processing: SalesHeader, SalesImport - Final synchronized order tables
Product Integration: InvMaster, InvWhControl - Product and warehouse validation
Customer Integration: ArCustomer, SalBranch - Customer validation and enrichment

Data Flow Architecture

1. Order Ingestion (SAT → KDL)

API Endpoint Structure

// POST /api/sat/post-order
{
    "customer_code": "CUST001",
    "customer_name": "ABC Trading Ltd",
    "region": "Nairobi",
    "warehouse_code": "WH001", 
    "transaction_date": "2024-01-15",
    "vehicle_code": "KCA123A",
    "order_details": [
        {
            "line_id": 1,
            "item_code": "PROD001",
            "item_text": "Premium Widget",
            "item_quantity": 10,
            "item_price": 150.00,
            "base_price": 150.00,
            "uom_code": "PCS"
        }
    ]
}

Validation Implementation

// SATIntegrationController@postOrder validation rules
$this->validate($request, [
    'customer_code' => 'required',
    'customer_name' => 'required',
    'region' => 'required',
    'warehouse_code' => 'required',
    'transaction_date' => 'required',
    'vehicle_code' => 'required',
    'order_details' => 'required',
]);

Data Storage Structure

// Stored in temp_sales_headers table
TempSalesHeader::insert([
    'vehicle_code' => $request->vehicle_code,
    'customer_name' => $request->customer_name,
    'customer_code' => $request->customer_code,
    'region' => $request->region,
    'transaction_date' => $request->transaction_date,
    'warehouse_code' => $request->warehouse_code,
    'order_details' => json_encode($request->order_details), // JSON field
]);

2. User-Region Access Control

Region Filtering Logic

// From TempSalesHeader@dashboardData
if (Auth::id() != null) {
    $user_regions = UserRegion::where('user_id', Auth::id())
        ->join('TblSoStdCom', 'TblSoStdCom.CommentCode', '=', 'user_regions.region_id')
        ->select('Comment as region')
        ->where('status', 'active')
        ->get()
        ->pluck('region')
        ->toArray();
} else {
    $user_regions = null;
}

// Applied in query
->when(isset($user_regions) && is_array($user_regions), function ($query) use ($user_regions) {
    $query->whereIn('region', $user_regions);
})

Dashboard Data Enrichment

// From TempSalesHeader@dashboardData - Data enrichment process
$data->getCollection()->map(function ($query) {
    // Warehouse description lookup
    $query->warehouse_code = DB::connection('syspro')
        ->table('InvWhControl')
        ->where('Warehouse', $query->warehouse_code)
        ->select('Description')
        ->first()->Description ?? $query->warehouse_code;

    // Customer name enrichment
    $customer_name = DB::connection('syspro')
        ->table('ArCustomer')
        ->where('Customer', $query->customer_code)
        ->select('Name')
        ->first();

    $query->customer_name = isset($customer_name->Name)
        ? $query->customer_code . ' - ' . $customer_name->Name
        : $query->customer_code . ' - ' . $query->customer_name;

    // Order status from SorMaster
    $query->order_status = SorMaster::find('KDSAT/'. Carbon::now()->format('y') .'/' . $query->sales_order)->OrderStatus ?? null;
    
    // Source document from SorHeader
    $query->source_doc = SorHeader::find($query->sales_order)->SourceDoc ?? null;

    // Generate proper sales order code
    $query->sales_order = empty($query->sales_order) ? null : $this->generateCode($query, $query->sales_order);

    // Format and enrich order details
    $query->order_details = $this->formatOrderItems($query->order_details);

    return $query;
});

3. Order Synchronization Process

Sync Operation Implementation

// TempSalesHeaderController@sync - Main synchronization logic
public function sync(Request $request): CollectionResource
{
    $id = gettype($request->id) == 'array' ? $request->id : [$request->id];

    TempSalesHeader::whereIn('id', $id)
        ->get()
        ->map(function ($query) use ($request) {
            // Check if already synced
            if ($query->sales_order != null) {
                $query->update(['SAT_SYNC' => 'Y']);
                abort(200, 'Order already synced');
            }

            // Decode and deduplicate order details
            $order_details = collect(json_decode($query->order_details))
                ->unique(function ($item) {
                    return $item->line_id . $item->item_code . $item->item_quantity . $item->base_price . $item->uom_code;
                })
                ->values()
                ->all();

            if (count($order_details) > 0) {
                $order_id = $this->updateSalesHeader($query, $order_details);

                // Generate XML for Sales transactions
                if ($query->transaction_type == 'Sale') {
                    $this->generateXML($query, $order_id);
                }
            }
        });

    return new CollectionResource([]);
}

Sales Header Creation

// TempSalesHeaderController@updateSalesHeader - Creates main order record
$order_id = DB::table('SalesHeader')->insertGetId([
    'Vehicle' => $query->vehicle_code == 'N/A' ? null : $query->vehicle_code,
    'Comment' => empty($request_log['lpo_number']) ? $query->customer_code : $request_log['lpo_number'],
    'Reference' => empty($request_log['lpo_number']) ? $query->customer_code : $request_log['lpo_number'],
    'CustomerName' => $customer->Name,
    'Customer' => $query->customer_code,
    'Region' => DB::connection('syspro')
        ->table('TblSoStdCom')
        ->select('Comment')
        ->where('CommentCode', $customer->InvCommentCode)
        ->first()->Comment ?? $query->region,
    'CreatedOn' => Carbon::now()->format('Y-m-d H:i:s'),
    'DispatchFrom' => $warehouse_code ?? null,
    'Area' => $area ?? null,
    'OrderDate' => Carbon::parse($query->transaction_date)->format('Y-m-d H:i:s.u'),
    'Currency' => 'Ksh',
    'Route' => $warehouse_code ?? null,
    'RouteName' => $warehouse,
    'Branch' => $customer->Branch ?? null,
    'OrderTime' => Carbon::now()->format('H:i:s.u'),
    'BranchName' => DB::table('SalBranch')->where('Branch', $customer->Branch)->first()->Description ?? null,
    'SourceDocRef' => 0,
    'SourceDoc' => $query->transaction_type,
    'HostName' => 'KDL-TEST',
    'Telephone' => $customer->Telephone,
    'YellowCopyRoute' => '',
    'BuyingGroup1' => $customer->BuyingGroup1,
    'BuyingGroup2' => $customer->BuyingGroup2,
    'BuyingGroup3' => $customer->BuyingGroup3,
    'BuyingGroup4' => $customer->BuyingGroup4,
    'BuyingGroup5' => $customer->BuyingGroup5,
    'LogisticsFlag' => 'N',
    'CreditLimit' => $customer->CreditLimit,
    'ExportFlag' => 'N',
    'OrderTotal' => collect($order_details)->map(function ($query) {
        return $query->item_price * $query->item_quantity;
    })->sum(),
    'UpdatedBy' => config('sat.user_id'),
    'CreatedBy' => config('sat.user_id'),
    'Mode' => 'A',
    "SAT_TRANSACTION_ID" => $query->transaction_id,
    "SAT_RECORD_NUMBER" => $query->record_number ?? null
]);

Line Item Processing

// TempSalesHeaderController@updateSalesHeader - Line item creation with complex logic
foreach ($order_details as $order_detail) {
    $order_ref = $order_id . $order_detail->item_code . $reference_line;
    
    // Product lookup and validation
    $product = DB::connection('syspro')
        ->table('InvMaster')
        ->where('StockCode', $order_detail->item_code)
        ->first();

    // Crate type determination
    $crate_id = Crate::whereNotNull('StockCodes')
        ->where('StockCodes', 'LIKE', "%$order_detail->item_code%")
        ->first()->CrateID ?? 0;
    
    $crate_type = ($crate_id == 21 || $crate_id == 22 || $crate_id == 23) ? 'Tray' : 'Uom';

    // Volume calculation with exclusions
    $skip_volume_conversion = [
        "BPM01", "BPM02", "BPM03", "BPM04", "JAMC01", "JAMC02", 
        "JAMC03", "JAMC04", "JAMCB01", "JAML02", "JAML03", "JAML04"
    ];

    $volume = !empty($product) && !empty(trim($product->AlternateKey2)) && !in_array($product->StockCode, $skip_volume_conversion)
        ? $product->AlternateKey2 * $order_detail->item_quantity
        : $order_detail->item_quantity;

    // Insert into SalesImport with comprehensive data
    DB::table('SalesImport')->insertGetId([
        'OrderRef' => $order_ref,
        'SalesOrder' => $order_id,
        'StockCode' => $order_detail->item_code,
        'OrderUOM' => $order_detail->uom_code,
        'FixedPrice' => $order_detail->uom_code == 'EA'
            ? $order_detail->item_price / $product->ConvFactAltUom
            : $order_detail->item_price,
        'Qty' => ($order_detail->uom_code == 'EA' || $order_detail->uom_code == 'LTR' || $order_detail->uom_code == 'KG') 
            ? $order_detail->item_quantity : 0,
        'Packets' => $order_detail->uom_code == 'PCS' ? $order_detail->item_quantity : 0,
        'Description' => $product->Description,
        'ReferenceLine' => count($order_details),
        'DrawOfficeNum' => $product->DrawOfficeNum,
        'Measure' => $product->AlternateKey2,
        'Factor' => $product->ConvFactAltUom,
        'AlternateUom' => $product->AlternateUom,
        'CreatedBy' => config('sat.user_id'),
        'Volume' => $volume == 0 ? "0.0000" : $volume,
        'PrdGroup' => $product->UserField1,
        'UomQty' => ($order_detail->uom_code == 'LTR' || $order_detail->uom_code == 'KG') && !empty(trim($product->AlternateKey2))
            ? $order_detail->item_quantity / $product->AlternateKey2
            : $order_detail->item_quantity,
        'ProductClass' => $product->ProductClass,
        'CrateID' => $crate_id,
        'CrateType' => $crate_type,
        'TotalQuantity' => $order_detail->uom_code == 'EA'
            ? $order_detail->item_quantity * $product->ConvFactAltUom
            : $order_detail->item_quantity,
        'UserField2' => $product->UserField2,
        'UserField3' => $product->UserField3,
        'UserField1' => $product->UserField1
    ]);

    $reference_line++;
}

4. Check Orders API Implementation

// TempSalesHeaderController@checkOrders - Order status verification
public function checkOrders(Request $request)
{
    $transaction_ids = $request->transaction_ids;
    $absent_orders = [];

    foreach ($transaction_ids as $transaction_id) {
        $order = TempSalesHeader::where('transaction_id', $transaction_id)->first();

        if (empty($order)) {
            // Order does not exist in KDL - add to absent list
            $absent_orders[] = $transaction_id;
        }
    }

    return response()->json([
        'absent_orders' => $absent_orders
    ], 200);
}

Data Structure Specifications

TempSalesHeader Model Fields

// From TempSalesHeader.php fillable fields
protected $fillable = [
    'customer_code',        // Customer identifier
    'customer_name',        // Customer display name
    'region',              // Geographic region for access control
    'warehouse_code',      // Source warehouse
    'transaction_date',    // Order transaction date
    'vehicle_code',        // Delivery vehicle code
    'order_details',       // JSON field with line items
    'SAT_SYNC',           // Sync status flag (Y/N)
    'transaction_id',     // Unique transaction identifier
    'transaction_type',   // Order type (Sale/Order)
    'user_code',          // User who created the order
    'notes',              // Additional notes/comments
    'request_log',        // JSON log of original request
    'sales_order',        // Generated sales order ID after sync
    'record_number'       // SAT record number
];

Order Details JSON Structure

[
    {
        "line_id": 1,
        "item_code": "PROD001",
        "item_text": "Premium Widget",
        "item_quantity": 10,
        "item_price": 150.00,
        "base_price": 150.00,
        "uom_code": "PCS"
    },
    {
        "line_id": 2,
        "item_code": "PROD002",
        "item_text": "Standard Widget",
        "item_quantity": 5,
        "item_price": 300.00,
        "base_price": 300.00,
        "uom_code": "CTN"
    }
]

Enhanced Order Details with Product Information

// From TempSalesHeader@formatOrderItems - Enriched with product descriptions
private function formatOrderItems(string $order_details)
{
    $order_details_array = json_decode($order_details);
    $order_details = [];

    // Get unique order items by item_code
    collect($order_details_array)->unique('item_code')->map(function ($query) use (&$order_details) {
        $order_details[] = $query;
    });

    // Enrich with product descriptions
    foreach ($order_details as $item) {
        $item->product_desc = DB::connection('syspro')
            ->table('InvMaster')
            ->where('StockCode', $item->item_code)
            ->first()->Description ?? null;
    }

    return json_encode($order_details);
}

Error Handling & Validation

Customer Validation

// From TempSalesHeaderController@updateSalesHeader
$customer = DB::connection('syspro')
    ->table('ArCustomer')
    ->where('Customer', $query->customer_code)
    ->first();

if ($customer->CustomerOnHold == 'Y' || $customer->CustomerOnHold == 'N' && trim($customer->SalesWarehouse) == null) {
    abort(500, 'Customer is on hold and cannot be processed');
}

Duplicate Order Prevention

// From TempSalesHeaderController@orderExists
public function orderExists(object $query): ?object
{
    return DB::table('SalesHeader')
        ->where('SAT_TRANSACTION_ID', $query->transaction_id)
        ->first();
}

// Used in sync process
if($order = $this->orderExists($query)){
    abort(422, "This record has already been synced (ID $order->SalesOrder). Please liaise with IT support for assistance.");
}

Transaction Safety

// From TempSalesHeaderController@updateSalesHeader
DB::beginTransaction();

try {
    // Create SalesHeader and SalesImport records
    $order_id = DB::table('SalesHeader')->insertGetId([...]);
    
    foreach ($order_details as $order_detail) {
        DB::table('SalesImport')->insertGetId([...]);
    }
    
    // Generate code and update temp order
    $code = $this->generateCode($query, $order_id);
    
    if ($query->transaction_type == 'Sale') {
        $this->storeTaxInfo($query, $code, false);
    }

    $query->update([
        'SAT_SYNC' => 'Y',
        'sales_order' => $order_id
    ]);
    
    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
    Log::info($e);
    abort(500, $e->getMessage());
}

Advanced Features

Record Number Generation

// From TempSalesHeaderController@generateCode
public function generateCode($query, $order_id)
{
    return isset($query->record_number)
        && $query->record_number != $query->transaction_id // Must follow series format
            ? $query->record_number
            : 'KDSAT/'. Carbon::now()->format('y') .'/' . $order_id;
}

Tax Information Storage

// From TempSalesHeaderController@storeTaxInfo
public function storeTaxInfo($query, $code, $invoice = false)
{
    try {
        $request_log = json_decode($query->request_log, true);

        if (!$request_log) {
            return;
        }

        if (!isset($query->record_number) || $query->record_number == null) {
            $code = $query->transaction_id;
        }

        DB::connection('syspro')
            ->table('CusSorMaster+')
            ->insertGetId([
                'SalesOrder' => $code,
                'InvoiceNumber' => $invoice ? $code : null,
                'Minvno' => intval(substr($request_log['tax_invoice_number'], 10)),
                'Qrurl' => $request_log['tax_invoice_number'] ?? null,
                'DevId' => $request_log['tax_printer'] ?? null
            ]);
    } catch (\Throwable $th) {
        Log::info($th);
    }
}

XML Generation for Sales

// From TempSalesHeaderController@generateXML
public function generateXML($query, $order_id)
{
    $code = $this->generateCode($query, $order_id);
    
    $sale = DB::table('SalesHeader')
        ->where('SalesOrder', $order_id)
        ->first();

    // Update Reconciliation Header
    $trip = (new ReconciliationHeader())->getTripFromSATSale($query);

    // Save in reconciliation table
    (new Utils())->postReconInvoice(
        $trip->TripID ?? 0,    // trip id
        $order_id,             // order id
        $code,
        $sale->OrderDate,
        $sale->Customer,
        $sale->CustomerName,
        $sale->OrderTotal,
        0,
        0,
        'Y'
    );

    // Export to XML
    (new GenerateRetailXml())->retailInvoiceXml($sale, $code, $query->transaction_date);

    // Update sale flags
    DB::table('SalesHeader')
        ->where('SalesOrder', $order_id)
        ->update([
            'ExportID' => $code,
            'ExportFlag' => 'Y',
            'DispatchFlag' => 'Y'
        ]);

    if ($query->transaction_type == 'Sale') {
        $this->storeTaxInfo($query, $code, true);
    }
}

Dashboard Analytics

Unsynced Orders Tracking

// From TempSalesHeaderController@dashboard
// Get oldest unsynced records
$oldest_unsynced = $this->temp_sales_header->getOldestUnsyncedData((object) [
    'sync_status' => 'N'
]) ?? null;

// Count all unsynced records
$all_unsynced_records = $this->temp_sales_header->countAllRecords((object) [
    'sync_status' => 'N'
]);

return view('temp_tables.orders', [
    'data' => $data,
    'columns' => $columns,
    'past_data' => (object) [
        'oldest_unsynced' => $oldest_unsynced ?? Carbon::now()->format($this->default_date_format),
        'all_unsynced_records' => $all_unsynced_records > 0 ? $all_unsynced_records : 'no',
        'date_now' => Carbon::now()->format($this->default_date_format)
    ]
]);

Search and Filtering

// From TempSalesHeader@dashboardData
$data = $this->when(request()->has('search'), function ($query) {
    $query->where('id', 'like', '%' . request()->search . '%')
        ->orWhere('customer_name', 'like', '%' . request()->search . '%')
        ->orWhere('customer_code', 'like', '%' . request()->search . '%')
        ->orWhere('region', 'like', '%' . request()->search . '%')
        ->orWhere('sales_order', 'like', '%' . request()->search . '%')
        ->orWhere('transaction_id', 'like', '%' . request()->search . '%');
})
->when($request->has('status') && $request->status != 'all', function ($query) use ($request) {
    $query->where('SAT_SYNC', $request->status);
})
->when($request->has('transaction_type') && $request->transaction_type != 'all', function ($query) use ($request) {
    $query->where('transaction_type', $request->transaction_type);
})
->when(isset($user_regions) && is_array($user_regions), function ($query) use ($user_regions) {
    $query->whereIn('region', $user_regions);
})
->whereBetween('created_at', $dates)
->orderBy('created_at', 'desc')
->paginate($per_page ?? 25);

Configuration and Dependencies

Required Configuration

// config/sat.php
'user_id' => env('SAT_USER_ID', 'SAT_SYSTEM'), // User for system operations

Database Connections

Default: Main KDL database (temp_sales_headers, user_regions)
Syspro: Legacy system connection (ArCustomer, InvMaster, SalesHeader, SalesImport)

Service Dependencies

GetStartEndDates: Date range processing
GenerateRetailXml: XML export for retail invoices
ReconciliationHeader: Trip reconciliation
Utils: Reconciliation invoice posting
Crate: Product crate type determination

Monitoring and Logging

Audit Trail Fields

created_at: Order creation timestamp
updated_at: Last modification timestamp
request_log: JSON field storing original SAT request
SAT_SYNC: Synchronization status flag
sales_order: Generated order ID after sync

Error Logging

// All exceptions are logged
catch (\Exception $e) {
    Log::info($e);
    abort(500, $e->getMessage());
}

// Volume calculation logging
Log::info('Volume: ' . $volume);

API Endpoints Summary

Endpoint	Method	Purpose	Controller
`/api/sat/post-order`	POST	Receive orders from SAT	`SATIntegrationController@postOrder`
`/api/check-orders`	POST	Verify order sync status	`TempSalesHeaderController@checkOrders`
`/temp-sales-headers`	GET	Dashboard data with filtering	`TempSalesHeaderController@dashboard`
`/temp-sales-headers/sync`	POST	Sync orders to main tables	`TempSalesHeaderController@sync`

This documentation provides a complete technical reference for the Order Management Integration between SAT and KDL systems, based on the actual implementation code and business logic.

Order Management Integration

On this page