Discount Report Controller Documentation

File: /controllers/disountreport.php

Purpose: Comprehensive discount and profit analysis across all billing systems with multi-currency support and detailed profitability calculations

Last Updated: December 20, 2024

Total Functions: 3

Lines of Code: ~705

---

📋 Overview

The Discount Report Controller provides sophisticated analysis of discounts, profits, and sales performance across multiple billing systems including optical sales, regular sales bills, returns, and combined transactions. It features complex profit calculations with configurable cost evaluation methods, network discount tracking, and comprehensive business analytics.

Primary Functions

• ✅ Multi-system discount analysis (optical, sales, returns)
• ✅ Advanced profit calculations with multiple cost methods
• ✅ Network payment discount tracking (Mada, credit cards)
• ✅ Branch-based filtering and reporting
• ✅ Date range analysis with flexible filtering
• ✅ Quantity tracking and piece counting
• ✅ Unified reporting across different billing systems

Related Controllers

• • sellbillController.php - Sales bills
• • returnSellBillController.php - Sales returns
• • sellbillandruternController.php - Combined bills
• • bills.php - Optical bills
• • billsreturn.php - Optical returns

---

🗄️ Database Tables

Sales System Tables

🔑 Key Functions

1. Default Action - Comprehensive Discount Report

Location: Line 178

Purpose: Generate unified discount and profit analysis across all systems

Function Signature:

// Triggered when: empty($do)

Process Flow:

1. Load user permissions and branch access

2. Parse search parameters (dates, branch, discount ranges)

3. Build dynamic query strings for all billing systems

4. Call getData() to execute analysis

5. Load tutorial links

6. Display comprehensive results

Parameter Processing:

$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$branchId = filter_input(INPUT_POST, 'branchId');
$discountfrom = filter_input(INPUT_POST, 'discountfrom') ?: 0;
$discountto = filter_input(INPUT_POST, 'discountto') ?: 0;

Multi-System Query Building:

$queryString = " where 1 ";   // Bills (optical)
$queryStringR = " where 1 ";  // Bills return (optical)
$queryString1 = " where 1 ";  // Sellbill (regular sales)
$queryString1R = " where 1 "; // Return sellbill
$queryString1SR = " where 1 ";// Sell and return combined

// Apply date filters to all systems
if (isset($datefrom) && !empty($datefrom)) {
    $queryString .= 'and date(bills.billdate) >= "' . $datefrom . '" ';
    $queryString1 .= 'and sellbill.sellbilldate >= "' . $datefrom . '" ';
    $queryString1R .= 'and returnsellbill.returnsellbilldate >= "' . $datefrom . '" ';
    // ... continue for all systems
}

---

2. getData() - Core Analysis Engine

Location: Line 262

Purpose: Execute complex multi-system analysis with profit calculations

Function Signature:

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $searchtype, $datefrom, $dateto, $discountfrom, $discountto)

Process Flow:

1. Load system configuration for profit evaluation method

2. Query all billing systems with filters

3. Process each system's data with specific business rules

4. Calculate profits using configurable cost methods

5. Handle network discounts and payment processing fees

6. Aggregate totals across all systems

7. Optionally unify by date for summary view

Bill Data Class Structure:

class billData {
    public $billId;
    public $billserial = "";
    public $billno;
    public $totalbill = 0;
    public $noOfpieces = 0;
    public $datetime;
    public $billdiscount = 0;
    public $networkDiscount = 0;
    public $billProfit = 0;
    public $clientName;
    public $sellerName;
    public $userName;
    public $billURL;
    public $billtype;
}

---

3. System-Specific Processing Methods

Optical Bills Processing

foreach ($billsData as $value) {
    $myBill = new billData();
    $myBill->billtype = "مبيعات بصريات";
    $myBill->totalbill = $value->finalnetbillvalue;
    $myBill->billdiscount = $value->discountvalue;
    
    // Handle network discounts (Mada, credit cards)
    if ($value->card == 1) {
        if ($value->paymentnetworkid == 4) {
            // Mada payment processing
            $madaData = $billsEX->queryTotalNetworkReportMadaSimple($value->billdate);
            if ($madaData->totalCarry < 5000)
                $myBill->networkDiscount = (7 * $madaData->totalCarry) / 1000;
            else
                $myBill->networkDiscount = 40;
        } else {
            // Other payment networks
            $myBill->networkDiscount = ($value->cardvalue * $value->netdiscountpercent) / 100;
        }
    }
    
    // Calculate profit
    $productsCost = 0;
    $billDetail = $billsProductsEX->queryAllGeneral(" and billid=" . $myBill->billId . " ");
    foreach ($billDetail as $mybillDetail) {
        $myBill->noOfpieces += $mybillDetail->productno;
        $productsCost += ($mybillDetail->deleted * $mybillDetail->productno);
    }
    $myBill->billProfit = ($value->netbillvalue - $value->discountvalue) - $productsCost;
}

Regular Sales Processing

foreach ($sellBillData as $value) {
    $myBill = new billData();
    $myBill->billtype = "فاتورة المبيعات";
    
    // Handle different discount types
    if ($value->sellbilldiscount > 0) {
        if ($value->sellbilldiscounttype == 1) {
            $myBill->billdiscount = $value->sellbilldiscount; // Fixed amount
        } elseif ($value->sellbilldiscounttype == 2) {
            $myBill->billdiscount = $value->sellbilltotalbill * $value->sellbilldiscount / 100; // Percentage
        }
    }
    
    // Calculate profit with configurable cost method
    $billDetail = $sellbilldetailEX->queryAllGeneral(" and sellbilldetail.sellbillid=" . $myBill->billId . " ");
    foreach ($billDetail as $mybillDetail) {
        // Get unit conversion
        $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
        $finalquantity = $quantity * $productnumber;
        
        // Apply profit evaluation method from settings
        switch ($Programsettingdata->Profitevaluation) {
            case "first":
                $buyprice = (float) $mybillDetail->buyprice;
                break;
            case "last":
                $buyprice = (float) $mybillDetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $mybillDetail->meanbuyprice;
                break;
            // ... other methods
        }
        
        $productsCost += $buyprice * $finalquantity;
    }
    
    $myBill->billProfit = $value->sellbillaftertotalbill - $productsCost;
}

---

4. unifyDate() - Date-Based Aggregation

Location: Line 681

Purpose: Aggregate data by date for summary reporting

Function Signature:

function unifyDate($allDataArr, $datefrom, $dateto)

Process Flow:

1. Create date-based grouping

2. Aggregate financial metrics by date

3. Combine bill URLs for drill-down

4. Return simplified date-based view

---

🔄 Workflows

Workflow 1: Comprehensive Discount Analysis

---

🌐 URL Routes & Actions

Discount Analysis:

• • datefrom - Start date (YYYY-MM-DD, optional)
• • dateto - End date (YYYY-MM-DD, optional)
• • branchId - Branch filter (optional, -1 for all)
• • discountfrom - Minimum discount amount filter (optional)
• • discountto - Maximum discount amount filter (optional)

---

🧮 Calculation Methods

Discount Type Processing

// Fixed amount discount
if ($sellbilldiscounttype == 1) {
    $discount = $sellbilldiscount;
}
// Percentage discount
elseif ($sellbilldiscounttype == 2) {
    $discount = $totalbill * $sellbilldiscount / 100;
}

Network Payment Fees

// Mada payment network fees
if ($paymentnetworkid == 4) {
    if ($totalCarry < 5000)
        $fee = (7 * $totalCarry) / 1000;
    else
        $fee = 40; // Fixed fee for larger amounts
}
// Other payment networks
else {
    $fee = ($cardvalue * $netdiscountpercent) / 100;
}

Configurable Profit Evaluation

switch ($Programsettingdata->Profitevaluation) {
    case "first":
        $buyprice = $product->productBuyPrice;
        break;
    case "last":
        $buyprice = $product->lastbuyprice;
        break;
    case "mean":
        $buyprice = $product->meanbuyprice;
        break;
    case "last_discount":
        $buyprice = $product->lastbuyprice_withDiscount;
        break;
    case "mean_discount":
        $buyprice = $product->meanbuyprice_withDiscount;
        break;
    case "generalPrice":
        $buyprice = $product->generalPrice;
        break;
    case "tax":
        $buyprice = $product->lastbuyprice_withTax;
        break;
    case "mean_tax":
        $buyprice = $product->meanbuyprice_withTax;
        break;
}

$profit = $sellprice - ($buyprice * $quantity);

Unit Conversion Handling

$quantity = $detail->quantity;
$productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber; // Convert to base units

---

🔒 Security & Permissions

User Permission Handling

$user = $userDAO->load($_SESSION['userid']);
if ($user->branchId == 0) {
    // User can see all branches
    $branchData = loadBranch();
} else {
    // User restricted to their branch
    $queryString .= 'and bills.branchid = ' . $user->branchId . ' ';
}

Input Validation

$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$branchId = filter_input(INPUT_POST, 'branchId');
$discountfrom = filter_input(INPUT_POST, 'discountfrom') ?: 0;
$discountto = filter_input(INPUT_POST, 'discountto') ?: 0;

SQL Injection Prevention

• • Uses parameterized query building
• • Proper input filtering
• • RedBean ORM protection

---

🐛 Common Issues & Troubleshooting

1. Profit Calculations Incorrect

Issue: Profit margins don't match expected values

Cause: Wrong profit evaluation method or missing cost data

Debug:

-- Check profit evaluation setting
SELECT Profitevaluation FROM programsettings WHERE programsettingsid = 1;

-- Verify product cost data
SELECT productId, productBuyPrice, lastbuyprice, meanbuyprice 
FROM product WHERE productId = [ID];

2. Missing Network Discount Calculations

Issue: Payment network fees not calculated

Cause: Missing payment network configuration

Debug:

-- Check payment network settings
SELECT * FROM bills WHERE card = 1 AND paymentnetworkid IS NOT NULL;

3. Date Range Issues

Issue: No data returned for valid date range

Cause: Date format mismatch or timezone issues

Debug:

echo "Date From: " . $datefrom . "<br>";
echo "Date To: " . $dateto . "<br>";
echo "Query: " . $queryString . "<br>";

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- bills(billdate, branchid) - For optical bills queries

- sellbill(sellbilldate, conditions) - For sales bills

- returnsellbill(returnsellbilldate, conditions) - For returns

- sellbillandrutern(sellbilldate) - For combined bills

2. Query Optimization:

- Use date range indexes effectively

- Minimize JOIN operations in detail queries

- Consider query result caching for large datasets

3. Memory Management:

- Process large result sets in batches

- Clear bill objects after processing

- Monitor memory usage with many bills

Large Dataset Handling

// For very large datasets, consider pagination
if ($resultsCount > 10000) {
    // Implement batch processing
    // or add pagination to results
}

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • bills.php - Optical billing system
• • programsettingsController.php - System configuration
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur