Sales with Detailed Discount Controller Documentation

File: /controllers/saleswithdetaileddiscount.php

Purpose: Generates detailed sales reports with comprehensive discount analysis and product hierarchy tracking

Last Updated: December 21, 2024

Total Functions: 5

Lines of Code: ~868

---

📋 Overview

The Sales with Detailed Discount Controller provides specialized reporting focused on discount analysis with complete product categorization. It handles:

• • Detailed discount tracking and analysis by product/category
• • Product hierarchy resolution with full category paths
• • Multi-bill type processing with discount distribution
• • Geographic filtering by government and area
• • Complex product categorization with parent-child relationships
• • Card payment discount calculations (Mada system integration)
• • Product path generation showing complete category hierarchy

Primary Functions

• ✅ Generate detailed discount reports by product/category
• ✅ Full product hierarchy path resolution
• ✅ Government and area-based filtering
• ✅ Multi-pricing strategy discount analysis
• ✅ Card payment fee integration
• ✅ Product categorization with full paths
• ✅ Geographic customer segmentation
• ✅ Service vs product filtering

Related Controllers

• • salesreportcatstore.php - Category sales reports
• • sellbillController.php - Sales operations
• • clientController.php - Customer management
• • productController.php - Product management

---

🗄️ Database Tables

Primary Sales Tables

🔑 Key Functions

1. getData() - Main Report Generation Function

Location: Line 400

Purpose: Core function that processes sales data with detailed discount analysis

Function Signature:

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, 
                $searchtype, $productCatId, $theStore)

Process Flow:

1. Initialize productData class with discount tracking

2. Handle default date filtering for current day

3. Query optical bills, regular sales, returns, and combined bills

4. Calculate detailed discounts including card fees

5. Process product hierarchy for display names

6. Generate aggregated totals with discount breakdowns

Key Variables:

• • $allDataArr - Array of productData objects with discount details
• • $totalsObj - Summary totals including total discount amounts
• • $searchtype - Report focus (0=product, 1=category)

---

2. get_parents_of_products() - Product Hierarchy Resolution

Location: Line 819

Purpose: Builds complete product hierarchy path for display

Function Signature:

function get_parents_of_products($product_id)

Process Flow:

1. Load product data and get category ID

2. Call recursive get_parents() function

3. Build full category path string

4. Combine with product name for complete path

5. Return formatted hierarchy string

Example Output:

Electronics / Computers / Laptops / Gaming Laptops / Product Name

---

3. get_parents() - Recursive Category Path Builder

Location: Line 838

Purpose: Recursively builds category hierarchy paths

Function Signature:

function get_parents($productCatId, $parent_name)

Process Flow:

1. Load current category data

2. Add category name to path string

3. Check if category has parent

4. Recursively call for parent categories

5. Build complete hierarchy path

Return Format:

"Parent Category / Child Category / "

---

4. getAllSubCat() - Category Expansion

Location: Line 779

Purpose: Expands category hierarchies for filtering

Function Signature:

function getAllSubCat($catid, $mode)

Parameters:

• • $catid - Parent category to expand
• • $mode - Expansion mode (1=all children, 2=leaf only)

---

5. Default Action - Main Controller Logic

Location: Line 161

Purpose: Handles routing, parameter processing, and geographic filtering

Process Flow:

1. Load geographic data (governments, areas)

2. Process complex parameter filtering

3. Handle government and area-based customer filtering

4. Build query strings for all bill types

5. Apply store, date, seller, and geographic filters

6. Call getData() with constructed parameters

---

🔄 Workflows

Workflow 1: Geographic Sales Analysis with Discount Tracking

---

🌐 URL Routes & Actions

• • storeId - Store filter (-1 for all stores)
• • branchId - Branch filter (-1 for all branches)
• • datefrom - Start date (YYYY-MM-DD)
• • dateto - End date (YYYY-MM-DD)
• • searchtype - Report focus (0=product level, 1=category level)

Optional Parameters

• • governmentId - Government/province filter (-1 for all)
• • areaid - Area filter within government (-1 for all)
• • sellerid - Seller filter (-1 for all sellers)
• • userid - User filter (-1 for all users)
• • productCatId{level} - Hierarchical category selection
• • productId - Specific product filter
• • proIsOptic - Optic system mode indicator

---

🧮 Calculation Methods

Product Data Structure with Discount Tracking

class productData {
    public $id;               // Product ID
    public $productName;      // Product display name
    public $soldNo = 0;       // Total quantity sold
    public $soldVal = 0;      // Total sale value
    public $returnNo = 0;     // Total quantity returned
    public $returnVal = 0;    // Total return value
    public $netNo = 0;        // Net quantity (sold - returned)
    public $netVal = 0;       // Net value (sales - returns)
    public $realCost = 0;     // Real cost calculation
    public $netProfit = 0;    // Net profit (net value - cost)
    public $currentQuantity = 0; // Current stock quantity
    public $discount = 0;     // *** Total discount amount ***
}

Optical System Discount Calculation

// Card payment processing
if ($theBill->card == 1) {
    if ($theBill->paymentnetworkid == 4) { // Mada system
        $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
        if ($madaData->totalCarry < 5000)
            $dicount = (7 * $madaData->totalCarry) / 1000;  // Variable fee
        else
            $dicount = 40;  // Fixed fee for large amounts
    } else {
        // Other card networks
        $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
    }
}

// Additional discounts (examination fees, etc.)
$dicount += ($theBill->productstotalprice - $theBill->finalnetbillvalue);

// Distribute discount proportionally
$theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
$myproduct->discount += $theDiscount;

Regular Sales Discount Processing

// Get bill-level discount
$dicount = $value->parcode - $value->note; // Total before vs after discount

if ($dicount != 0) {
    // Get total pieces for proportional distribution
    $billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
    $billNoOfProduct = $billpecies->note;
    
    // Calculate proportional discount
    $theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
    $theDiscount -= $value->discountvalue; // Subtract line-item discount
    $theDiscount = round($theDiscount, 2);
}

$myproduct->discount += $theDiscount;
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;

Geographic Client Filtering

// Government level filtering
if (isset($governmentId) && $governmentId != '-1') {
    $clientids = '';
    if ($governmentId == 0) {
        $government_Data = $governAreaDAO->queryAll(); // All areas
    } else {
        $government_Data = $governAreaDAO->queryByGovernmentid($governmentId);
    }
    
    // Build client list for geographic region
    foreach ($government_Data as $value) {
        $clientareaid = $value->clientareaid;
        $clientData = $clientExt->queryAllbyarea($clientareaid);
        foreach ($clientData as $client) {
            $clientids .= ',' . $client->clientid;
        }
    }
    $clientids = ltrim($clientids, ',');
    
    // Apply to all query types
    $queryString1 .= 'and sellbill.sellbillclientid in (' . $clientids . ') ';
    $queryString1R .= 'and returnsellbill.returnsellbillclientid in (' . $clientids . ') ';
    $queryString1SR .= 'and sellbillandrutern.sellbillclientid in (' . $clientids . ') ';
}

---

🔒 Security & Permissions

Store and Branch Access Control

// Store access control
if ($user->userstoreid == 0) {
    $theStore = $storeId;  // Admin can access any store
} else {
    $theStore = $user->userstoreid;  // Restricted to user's store
}

// Branch access control
if ($user->branchId == 0) {
    // Can access specified branch
    $queryString .= 'and bills.branchid = ' . $branchId . ' ';
} else {
    // Restricted to user's branch
    $queryString .= 'and bills.branchid = ' . $user->branchId . ' ';
}

Input Validation

// Numeric parameters properly cast
$chosenProductPrice = (int) filter_input(INPUT_POST, 'chosenProductPrice');
$isOptic = filter_input(INPUT_POST, 'proIsOptic');
if (!isset($isOptic) || empty($isOptic)) {
    $isOptic = 0;
}

---

📊 Performance Considerations

Database Optimization

1. Critical Indexes:

- sellbilldetail(sellbillid, sellbilldetailproductid)

- client(clientareaid) for geographic filtering

- goverarea(governmentid) for area lookups

- productcat(productCatParent) for hierarchy traversal

2. Query Optimization:

- Geographic filtering builds client lists efficiently

- Product hierarchy resolution cached per product

- Proper JOIN relationships in bill queries

3. Performance Warnings:

- Product hierarchy resolution called for every product

- Geographic client list building can be expensive

- Recursive category parent traversal needs depth limits

Optimization Opportunities

// Current: Individual hierarchy resolution
foreach ($allDataArr as $val) {
    $product_id = $val->id;
    $full_product_name = get_parents_of_products($product_id);
    $val->productName = $full_product_name;
}

// Better: Batch hierarchy resolution with caching
$hierarchyCache = [];
foreach ($allDataArr as $val) {
    if (!isset($hierarchyCache[$val->id])) {
        $hierarchyCache[$val->id] = get_parents_of_products($val->id);
    }
    $val->productName = $hierarchyCache[$val->id];
}

---

🐛 Common Issues & Troubleshooting

1. Geographic Filtering Not Working

Issue: Customer data missing when government/area filter applied

Cause: Client area assignments missing or incorrect

Debug:

-- Check client area assignments
SELECT c.clientid, c.clientname, c.clientareaid, ga.name as area_name
FROM client c 
LEFT JOIN goverarea ga ON c.clientareaid = ga.govareaid
WHERE c.clientareaid IS NULL OR ga.govareaid IS NULL;

-- Verify government structure
SELECT g.governmentid, g.name, COUNT(ga.govareaid) as area_count
FROM government g
LEFT JOIN goverarea ga ON g.governmentid = ga.governmentid  
GROUP BY g.governmentid;

2. Product Hierarchy Paths Missing

Issue: Product names don't show full category paths

Cause: Category hierarchy broken or recursive function issues

Debug:

// Test hierarchy function directly
$test_product_id = 123;
$hierarchy = get_parents_of_products($test_product_id);
echo "Product Hierarchy: " . $hierarchy;

// Check for circular references in categories
$product = $ProductDAO->load($test_product_id);
echo "Product Cat ID: " . $product->productCatId;

3. Discount Calculations Incorrect

Issue: Discount amounts don't match expectations

Cause: Bill-level vs line-item discount conflicts

Fix:

// Debug discount calculations
echo "Bill Total: " . $value->parcode . "<br>";
echo "After Discount: " . $value->note . "<br>";
echo "Line Discount: " . $value->discountvalue . "<br>";
echo "Calculated Discount: " . $theDiscount . "<br>";

---

🧪 Testing Scenarios

Test Case 1: Geographic Filtering Accuracy

1. Create customers in different government areas
2. Generate sales for each customer group
3. Apply government filter to report
4. Verify only customers from selected region appear
5. Check area-level filtering works correctly

Test Case 2: Product Hierarchy Display

1. Create multi-level category hierarchy
2. Add products at various hierarchy levels
3. Generate sales for these products
4. Run report and verify complete paths display
5. Check hierarchy format: "Parent / Child / Product"

Test Case 3: Discount Distribution Accuracy

1. Create bills with various discount types:
   - Fixed amount discounts
   - Percentage discounts  
   - Line-item discounts
   - Card payment fees
2. Run detailed discount report
3. Verify discount amounts are distributed correctly
4. Check totals match bill-level calculations

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • salesreportcatstore.md - Category sales reports
• • sellbillController.md - Sales operations
• • clientController.md - Customer management
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur