Intelligent product matching system and hierarchical pricing for EvaDocs LPO processing

Product Matching & Pricing System

The product matching system is the most sophisticated part of the LPO processing engine, using a hierarchical approach to match extracted product information with the product catalog.

Product Matching Logic

Matching Hierarchy Overview

The system uses a strict 5-tier hierarchical approach. Each step is attempted in order, and once a match is found, the search stops:

flowchart TD
    A[OCR Extracted Data] --> B{Direct Product ID?}
    B -->|Yes| C[Step 1: Use Product ID]
    B -->|No| D{Product Code Available?}
    D -->|Yes| E[Step 2: Code Matching]
    D -->|No| F{Product Description?}
    F -->|Yes| G[Step 3: Description Matching]
    F -->|No| H[Step 5: AI Fallback]
    E -->|No Match| I{Customer Known?}
    G -->|No Match| I
    I -->|Yes| J[Step 4: Customer Mapping]
    I -->|No| H
    J -->|No Match| H
    C --> K[Product Matched]
    E --> K
    G --> K
    J --> K
    H --> L[Suggestions Generated]

Step 1: Direct Product ID Match (Highest Priority)

When Used: If OCR extracted data contains product_id

if (isset($record['product_id']) && !empty($record['product_id'])) {
    $product = Product::find($record['product_id']);
    if ($product) {
        return $product;
    }
}

Characteristics:

Accuracy: 100% when available
Frequency: Rare in typical LPO documents
Use Case: Advanced OCR systems or structured data formats

Step 2: Product Code Matching (Primary Strategy)

When Used: OCR extracts item_code or bar_code fields

$codes = array_filter([
    $record['item_code'] ?? null,
    $record['bar_code'] ?? null,
    $record['product_code'] ?? null
]);

if (!empty($codes)) {
    $product = Product::whereIn('product_code', $codes)
                    ->orWhereIn('product_valcode', $codes)
                    ->first();
    if ($product) {
        return $product;
    }
}

Search Fields:

product_code: Primary product identifier
product_valcode: Alternative/validation code
Handles multiple code variations simultaneously

Best Practices:

Maintains clean product code standards
Regular audit of duplicate or similar codes
Standardized barcode format across products

Step 3: Product Description Matching (Text-based Fallback)

When Used: Codes fail but product names are available

if (isset($record['item_name']) && !empty($record['item_name'])) {
    $cleanName = $this->cleanProductName($record['item_name']);
    
    $product = Product::where('product_desc', 'LIKE', "%{$cleanName}%")
                     ->orderBy('product_desc')
                     ->first();
    if ($product) {
        return $product;
    }
}

private function cleanProductName(string $name): string
{
    // Remove HTML entities, extra whitespace, special characters
    $cleaned = html_entity_decode($name);
    $cleaned = preg_replace('/\s+/', ' ', $cleaned);
    $cleaned = trim($cleaned);
    return $cleaned;
}

Text Cleaning Process:

HTML Decoding: Remove HTML entities and tags
Whitespace Normalization: Collapse multiple spaces
Special Character Handling: Remove or standardize punctuation
Case Normalization: Consistent case handling

Step 4: Customer-Specific Code Mapping (Relationship-based)

When Used: Previous steps fail AND customer/outlet is identified

if ($outlet && $outlet->customer_set_id) {
    $mapping = EvaDocsProductCode::where('customer_account_id', $outlet->customer_set_id)
                                ->where('lpo_product_code', $record['item_code'])
                                ->first();
    
    if ($mapping) {
        return Product::find($mapping->product_id);
    }
}

Use Cases:

Internal Codes: Customers use their own product numbering
Legacy Systems: Historical code mappings for long-term customers
Industry Standards: Sector-specific product identification

Configuration Example:

// Create customer-specific mapping
EvaDocsProductCode::create([
    'customer_account_id' => 123,
    'product_id' => 456,
    'lpo_product_code' => 'CUST_BEER_001'
]);

Step 5: Full-Text MeiliSearch / MySql Fuzzy Matching

When Used: All above methods fail

if (isset($record['item_name'])) {
    $searchResults = $this->meiliSearchClient->index('products')
                                           ->search($record['item_name'], [
                                               'limit' => 5,
                                               'attributesToHighlight' => ['*']
                                           ]);
    
    if ($searchResults['estimatedTotalHits'] > 0) {
        $suggestions = $this->createProductSuggestions($searchResults);
        $productSetId = $this->storeProductSuggestions($suggestions);
        
        // Update LPO item with suggestions
        $lpoItem->update(['product_set_id' => $productSetId]);
        
        return null; // No automatic match, requires user review
    }
}

private function createProductSuggestions($searchResults): array
{
    $suggestions = [];
    foreach ($searchResults['hits'] as $hit) {
        $suggestions[] = [
            'product_id' => $hit['id'],
            'confidence_score' => $hit['_score'] ?? 0,
            'highlight' => $hit['_formatted'] ?? []
        ];
    }
    return $suggestions;
}

AI Features:

Typo Tolerance: Handles OCR reading errors
Fuzzy Matching: Similar but not exact descriptions
Confidence Scoring: Ranked suggestions with reliability scores
Contextual Search: Considers product categories and attributes

Price Mapping System

The pricing system follows a strict hierarchy to ensure accurate and business-appropriate pricing.

Price Hierarchy (Highest to Lowest Priority)

flowchart TD
    A[Price Determination] --> B{Manual Override?}
    B -->|Yes| C[Use Manual Price]
    B -->|No| D{prefer_lpo_prices = true?}
    D -->|Yes| E{LPO Price Available?}
    E -->|Yes| F[Use LPO Price]
    E -->|No| G[Customer Price List]
    D -->|No| G
    G --> H{Customer Price Exists?}
    H -->|Yes| I[Use Customer Price]
    H -->|No| J[Use Default Product Price]

1. Manual Override Price (Highest Priority)

if ($request->has('unit_price') && $request->price_updated_manually) {
    $lpoItem->update([
        'unit_price' => $request->unit_price,
        'total_price' => $request->unit_price * $lpoItem->quantity,
        'price_updated_manually' => true
    ]);
}

Characteristics:

User Control: Complete pricing flexibility
Audit Trail: Tracked in history for compliance
Override Protection: Cannot be automatically changed once set

2. LPO Extracted Price (OCR Price)

if ($lpo->prefer_lpo_prices && isset($extractedData['unit_price'])) {
    $unitPrice = (float) $extractedData['unit_price'];
    $totalPrice = $unitPrice * $quantity;
    
    $lpoItem->update([
        'unit_price' => $unitPrice,
        'total_price' => $totalPrice,
        'lpo_unit_price' => $unitPrice, // Store original
        'lpo_total_price' => $totalPrice
    ]);
}

Business Logic:

Configuration Dependent: Controlled by prefer_lpo_prices setting
Price Validation: Ensures extracted prices are reasonable
Original Preservation: Stores both system and LPO prices

3. Customer-Specific Price List

if ($outlet && $outlet->customer_set_id && $product) {
    $customerPrice = CustomerPrice::where('customer_set_id', $outlet->customer_set_id)
                                 ->where('product_id', $product->id)
                                 ->where('effective_date', '<=', now())
                                 ->orderBy('effective_date', 'desc')
                                 ->first();
    
    if ($customerPrice) {
        return $customerPrice->unit_price;
    }
}

Features:

Time-based Pricing: Effective date ranges for price changes
Volume Discounts: Quantity-based pricing tiers
Special Agreements: Contract-specific pricing

Product Matching & Pricing System

On this page