Documentation for the automated customer data synchronization between KDL and SAT systems

Customer Data Synchronization

The customer data synchronization ensures that all customer records in KDL are regularly transferred to SAT, maintaining current customer information for accurate sales, reporting, and operational workflows.

Overview

This scheduled integration point runs every five minutes, automatically synchronizing customer data from KDL's ArCustomer table to SAT. The synchronization includes customer details, contact information, pricing configurations, and geographical data.

Technical Details

Code Location

Scheduled Command: sat-pull:customers
Registration: app/Console/Kernel.php
Controller: SATIntegrationController@pullCustomers
Primary Table: ArCustomer
Related Tables: SalBranch, InvPrice, TblSoStdCom

Execution Schedule

// Laravel Scheduler - every 5 minutes
$schedule->command('sat-pull:customers')->everyFiveMinutes();

Data Selection Criteria

Only customers meeting the following criteria are synchronized:

SAT_SYNC is null or not equal to 'Y'
Area field is not blank or null
Active customer records

Data Mapping

The following table shows how KDL customer data is transformed for SAT:

KDL Field	Source Table	SAT Field	Transformation Notes
`Name`	ArCustomer	`name`	Direct mapping
`Area`	ArCustomer	`region`	Direct mapping
`City`	ArCustomer	`location`	Fallback to `region` if missing
`SalBranch.Description`	SalBranch	`branch`	Joined from SalBranch table
`Telephone`	ArCustomer	`phone_number`	Direct mapping
`Customer`	ArCustomer	`customer_code`	Direct mapping
`SalesWarehouse`	ArCustomer	`customer_warehouse`	Direct mapping
`Email`	ArCustomer	`email`	Direct mapping
`CompanyTaxNumber`	ArCustomer	`kra_pin`	Direct mapping
`Contact`	ArCustomer	`contact_person`	Direct mapping
`InvPrice.PriceCode`	InvPrice	`price_code`	Joined from InvPrice table
`SATPriceList`	ArCustomer	`pricelist_code`	Direct mapping
`CreditLimit`	ArCustomer	`credit_limit`	Direct mapping
`InvCommentCode`	ArCustomer	`region`	Enriched via TblSoStdCom.Comment

Virtual Fields

The following fields are set to standardized values:

latitude: null
longitude: null
postal_address: null
address: null

Category Logic

The category field is determined by branch logic:

if ($customer->branch === 'DIRECT SALES') {
    $category = 'No Location';
} else {
    $category = $customer->customer_code;
}

Sample Data Structure

{
  "name": "ABC Trading Ltd",
  "region": "Nairobi",
  "location": "Westlands",
  "branch": "Main Branch",
  "phone_number": "+254700000000",
  "customer_code": "CUST001",
  "customer_warehouse": "WH001",
  "email": "contact@abctrading.com",
  "kra_pin": "P051234567M",
  "contact_person": "John Doe",
  "price_code": "RETAIL",
  "pricelist_code": "PL001",
  "credit_limit": 100000.00,
  "category": "CUST001",
  "latitude": null,
  "longitude": null,
  "postal_address": null,
  "address": null
}

Database Schema

Primary Tables

-- ArCustomer: Main customer table
SELECT 
    Name,
    Area,
    City,
    Telephone,
    Customer,
    SalesWarehouse,
    Email,
    CompanyTaxNumber,
    Contact,
    SATPriceList,
    CreditLimit,
    InvCommentCode,
    SAT_SYNC,
    BuyingGroup1,
    BuyingGroup2,
    BuyingGroup3,
    BuyingGroup4,
    BuyingGroup5
FROM ArCustomer;

-- SalBranch: Branch information
SELECT Branch, Description FROM SalBranch;

-- InvPrice: Price list information  
SELECT Customer, PriceCode FROM InvPrice;

Configuration

Environment Variables

SAT_BASE_URL=https://kdl.solutechlabs.com/api
SAT_PASSWORD_AUTH=your_api_token
SAT_CUSTOMERS_API=/api/customers

Laravel Configuration

// config/sat.php
return [
    'customers' => env('SAT_CUSTOMERS_API'),
    'login' => env('SAT_LOGIN_URL'),
];

Monitoring & Logging

LogSatRequest Tracking

All customer sync operations are logged in the LogSatRequest table:

-- View recent customer sync attempts
SELECT 
    created_at,
    success,
    error,
    doc_num
FROM LogSatRequest 
WHERE type = 'customers' 
ORDER BY created_at DESC 
LIMIT 10;

Troubleshooting

Common Issues & Solutions

Issue: Customers Not Syncing

Symptoms: SAT_SYNC remains null or 'N' Possible Causes:

Scheduler not running
Network connectivity issues
Invalid customer data

Solution:

Check scheduler status: php artisan schedule:list
Verify network connectivity to SAT
Review customer data for completeness

Debugging Commands

# Enable detailed logging
export LOG_LEVEL=debug

# Monitor Laravel logs in real-time
tail -f storage/logs/laravel.log | grep "sat integration"

# Check scheduler history
php artisan schedule:list

Customer Data Synchronization

On this page