# 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
- [x] Generate detailed discount reports by product/category
- [x] Full product hierarchy path resolution
- [x] Government and area-based filtering
- [x] Multi-pricing strategy discount analysis
- [x] Card payment fee integration
- [x] Product categorization with full paths
- [x] Geographic customer segmentation
- [x] Service vs product filtering

### Related Controllers
- [salesreportcatstore.php](salesreportcatstore.md) - Category sales reports
- [sellbillController.php](sellbillController.md) - Sales operations
- [clientController.php](#) - Customer management
- [productController.php](#) - Product management

---

## 🗄️ Database Tables

### Primary Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilldate, sellbillstoreid |
| **sellbilldetail** | Sales line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice |
| **returnsellbill** | Return bills master | returnsellbillid, returnsellbillclientid, returnsellbillaftertotalbill, returnsellbilldate |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity |
| **sellbillandrutern** | Combined bills | sellbillid, sellbillclientid, sellbilldate, selltype |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, selltype |

### Product Hierarchy Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master data | productId, productName, productCatId, overAllAveragePrice, isService |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |
| **productunit** | Product units | productunitid, productid, productnumber |

### Geographic Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **government** | Government/province data | governmentid, governmentname |
| **goverarea** | Government areas | govareaid, governmentid, name |
| **client** | Customer information | clientid, clientname, clientareaid |

### Optical System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Optical bills | billid, clientid, productstotalprice, finalnetbillvalue, card, paymentnetworkid |
| **billsproducts** | Optical products | billproductid, billid, productid, producttotalprice, productno |
| **billsreturnproducts** | Optical returns | billreturnproductid, productid, producttotalprice |

---

## 🔑 Key Functions

### 1. **getData()** - Main Report Generation Function
**Location**: Line 400  
**Purpose**: Core function that processes sales data with detailed discount analysis

**Function Signature**:
```php
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**:
```php
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**:
```php
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**:
```php
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
```
┌─────────────────────────────────────────────────────────────┐
│         START: Select Geographic & Product Filters         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Geographic Filters                             │
│     - Government selection (province level)                │
│     - Area selection within government                     │
│     - Build client ID lists for geographic regions         │
│     - Apply client filters to all query types              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Product/Category Filters                       │
│     - Handle individual product selection                  │
│     - Expand category hierarchies if category selected     │
│     - Build product ID lists for filtering                 │
│     - Handle optic mode product resolution                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Query Optical System Bills                             │
│     - Load bills with card payment information             │
│     - Calculate Mada system fees                           │
│     - Process percentage vs fixed discounts                │
│     - Track card network fees                              │
│     - Group by product for analysis                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Query Regular Sales Bills                              │
│     - Load sellbilldetail with product information         │
│     - Calculate bill-level discount distributions          │
│     - Handle line-item discount values                     │
│     - Process product unit conversions                     │
│     - Track discount amounts per product                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Query Return Bills                                      │
│     - Process returnsellbilldetail records                 │
│     - Handle return-specific discount processing           │
│     - Calculate returned quantities and values             │
│     - Subtract discounts for returns                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Query Combined Bills                                    │
│     - Process sellandruternbilldetail records              │
│     - Check selltype for sale vs return                    │
│     - Apply appropriate discount calculations              │
│     - Handle mixed transaction types                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Build Product Hierarchy Paths                          │
│     FOR EACH product in results:                           │
│       │                                                     │
│       ├─→ Call get_parents_of_products()                   │
│       │                                                     │
│       ├─→ Build complete category path                     │
│       │   "Electronics / Computers / Laptops"             │
│       │                                                     │
│       └─→ Update product display name                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  8. Generate Final Report                                   │
│     - Calculate net quantities and values                  │
│     - Sum total discount amounts                           │
│     - Assign data with hierarchy paths                     │
│     - Display via saleswithdetaileddiscountview           │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

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

### Required Parameters
- `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
```php
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
```php
// 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
```php
// 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
```php
// 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
```php
// 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
```php
// 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
```php
// 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**:
```sql
-- 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**:
```php
// 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**:
```php
// 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [salesreportcatstore.md](salesreportcatstore.md) - Category sales reports
- [sellbillController.md](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