# 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
- [x] Multi-system discount analysis (optical, sales, returns)
- [x] Advanced profit calculations with multiple cost methods
- [x] Network payment discount tracking (Mada, credit cards)
- [x] Branch-based filtering and reporting
- [x] Date range analysis with flexible filtering
- [x] Quantity tracking and piece counting
- [x] Unified reporting across different billing systems

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales bills
- [returnSellBillController.php](#) - Sales returns
- [sellbillandruternController.php](#) - Combined bills
- [bills.php](#) - Optical bills
- [billsreturn.php](#) - Optical returns

---

## 🗄️ Database Tables

### Sales System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Regular sales bills | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilldiscount, sellbilldiscounttype |
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice |
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbillclientid, returnsellbilltotalbill, returnsellbilldiscount |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity |

### Combined Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbillandrutern** | Combined sell/return bills | sellbillid, sellbillclientid, sellbillprice, returnsellbillprice, sellbilldate |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, selltype |

### Optical System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Optical service bills | id, billno, finalnetbillvalue, discountvalue, card, paymentnetworkid, cardvalue, netdiscountpercent |
| **billsproducts** | Optical bill items | billsproductsid, billid, productno, deleted (cost field) |
| **billsreturn** | Optical returns | id, returnedprice, discountvalue |
| **billsreturnproducts** | Optical return items | billsreturnproductsid, returnbillid, productno, deleted (cost field) |

### Product & Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master data | productId, productBuyPrice, lastbuyprice, meanbuyprice, generalPrice |
| **productunit** | Product unit conversions | productunitid, productid, unitid, productnumber |
| **client** | Customer data | clientid, clientname |
| **user** | System users | userid, username, employeename |
| **branches** | Branch locations | branchid, branchname |
| **programsettings** | System configuration | programsettingsid, Profitevaluation |

---

## 🔑 Key Functions

### 1. **Default Action** - Comprehensive Discount Report
**Location**: Line 178  
**Purpose**: Generate unified discount and profit analysis across all systems

**Function Signature**:
```php
// 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**:
```php
$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**:
```php
$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**:
```php
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**:
```php
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
```php
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
```php
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**:
```php
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
```
┌─────────────────────────────────────────────────────────────┐
│              START: Generate Discount Report               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Search Parameters                                 │
│     - Date range (from/to)                                  │
│     - Branch selection                                      │
│     - Discount range filters                               │
│     - Search type (unified/detailed)                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Multi-System Queries                             │
│     - Optical bills query                                  │
│     - Optical returns query                                │
│     - Regular sales query                                  │
│     - Sales returns query                                  │
│     - Combined bills query                                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Parallel Queries                               │
│     - Query all billing systems simultaneously             │
│     - Apply date, branch, and permission filters           │
│     - Count total results across systems                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each System's Data                              │
│     FOR EACH billing system:                               │
│       │                                                     │
│       ├─ Create billData objects                           │
│       ├─ Calculate system-specific discounts               │
│       ├─ Handle network payment fees                       │
│       ├─ Load detailed line items                          │
│       ├─ Apply profit evaluation method                    │
│       ├─ Calculate net profit margins                      │
│       └─ Generate drill-down URLs                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Aggregate and Summarize                                │
│     - Combine data from all systems                        │
│     - Calculate grand totals                               │
│     - Apply date unification if requested                  │
│     - Generate summary statistics                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Display Comprehensive Report                           │
│     - Show detailed bill-by-bill analysis                  │
│     - Display profit margins and discounts                 │
│     - Provide system-wise breakdowns                       │
│     - Enable drill-down to source documents                │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Generate discount report |

### Required Parameters

**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
```php
// Fixed amount discount
if ($sellbilldiscounttype == 1) {
    $discount = $sellbilldiscount;
}
// Percentage discount
elseif ($sellbilldiscounttype == 2) {
    $discount = $totalbill * $sellbilldiscount / 100;
}
```

### Network Payment Fees
```php
// 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
```php
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
```php
$quantity = $detail->quantity;
$productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber; // Convert to base units
```

---

## 🔒 Security & Permissions

### User Permission Handling
```php
$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
```php
$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**:
```sql
-- 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**:
```sql
-- 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**:
```php
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
```php
// For very large datasets, consider pagination
if ($resultsCount > 10000) {
    // Implement batch processing
    // or add pagination to results
}
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](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