# Selling Report By Area Controller Documentation

**File**: `/controllers/sellingReportByArea.php`  
**Purpose**: Generate comprehensive sales reports grouped by geographic areas and product categories  
**Last Updated**: December 21, 2024  
**Total Functions**: 6  
**Lines of Code**: ~647

---

## 📋 Overview

The Selling Report By Area Controller provides detailed sales analysis organized by geographic regions and product hierarchies. It offers:
- Sales reporting by government/area/client geographic hierarchy
- Product-wise sales analysis with category filtering
- Multi-bill type consolidation (sales, returns, combined bills, service bills)
- Quantity and value calculations with unit conversions
- Discount and tax analysis per transaction
- Profit analysis with buy price comparisons
- Current inventory levels integration
- Hierarchical product path generation

### Primary Functions
- [x] Geographic sales analysis by areas and governments
- [x] Product category and individual product filtering
- [x] Multi-bill type reporting (sales, returns, service bills)
- [x] Quantity calculations with unit conversions
- [x] Discount and tax analysis
- [x] Profit margin calculations
- [x] Current stock level reporting
- [x] Hierarchical product categorization

### Related Controllers
- [salesreport.php](salesreport.md) - General sales reporting
- [reportprovinces.php](reportprovinces.md) - Geographic customer analysis
- [sellreportpricetype.php](sellreportpricetype.md) - Price type analysis
- [reportfunctions.php](reportfunctions.md) - Shared utility functions

---

## 🗄️ Database Tables

### Geographic Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **government** | Province/governorate master | governmetid, governmentname |
| **goverarea** | Areas within governments | governmentid, clientareaid |
| **clientarea** | Client area definitions | id, name, description |
| **client** | Customer data with area | clientid, clientname, clientareaid |

### Sales Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilldate, sellbilltotalbill, sellbilldiscount, tax |
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilldate, returnsellbilltotalbill |
| **returnsellbilldetail** | Return bill details | returnsellbilldetailid, returnsellbilldetailproductid, returnsellbilldetailquantity |
| **sellbillandrutern** | Combined bills | sellbillid, sellbillclientid, sellbilldate, sellbillprice |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbilldetailproductid, sellbilldetailquantity, selltype |

### Service Bills Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Service bills | id, clientid, billdate, productstotalprice, finalnetbillvalue |
| **billsproducts** | Service bill products | billproductsid, billid, productid, productno, producttotalprice |
| **billsreturn** | Service bill returns | billsreturnid, clientid, date, productstotalprice |
| **billsreturnproducts** | Return bill products | billsreturnproductsid, billproductid, productid, productno |

### Product Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master | productid, productname, productcatid, productbuyprice, isoptic |
| **productcat** | Product categories | productcatid, productcatname, productcatparent |
| **productunit** | Unit conversions | productunitid, productid, unitid, productnumber |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System settings | programsettingsid, reportsplushhours |
| **youtubelink** | Tutorial videos | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Area-Based Sales Report
**Location**: Line 200 (`empty($do)` or `$do == "show"`)  
**Purpose**: Generate comprehensive sales report organized by geographic areas

**Function Signature**:
```php
// POST parameters: clientareaid, from, to, governmentid
$clientareaid = $_POST["clientareaid"];
$startDate = $_POST["from"];
$endDate = $_POST["to"];  
$governmentId = $_POST['governmentid'];
```

**Process Flow**:
1. Load authentication and program settings
2. Load dropdown data (governments, areas, clients)
3. Build date range with time offset handling:
   ```php
   if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
       $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($startDate)));
   }
   ```
4. Build query strings for multiple bill types
5. Load data from all transaction types:
   - Service bills (`billsData`)
   - Service bill returns (`billsDataReturn`) 
   - Sales bills (`sellBillData`)
   - Sales returns (`sellBillDataReturn`)
   - Combined bills (`sellBillDataSellAndReturn`)
6. Process all transaction types into unified product analysis
7. Calculate totals, profits, and current inventory
8. Generate hierarchical product paths
9. Display comprehensive report

**Key Data Structures**:
```php
class productData {
    public $id;              // Product ID
    public $productName;     // Product name with full path
    public $soldNo = 0;      // Quantity sold
    public $soldVal = 0;     // Value sold
    public $returnNo = 0;    // Quantity returned
    public $returnVal = 0;   // Value returned
    public $netNo = 0;       // Net quantity (sold - returned)
    public $netVal = 0;      // Net value (sold - returned)
    public $discount = 0;    // Total discount applied
    public $realCost = 0;    // Cost price total
    public $netProfit = 0;   // Net profit (net value - real cost)
    public $buyPrice = 0;    // Unit buy price
    public $currentQuantity = 0; // Current stock level
}
```

---

### 2. **Service Bills Processing**
**Location**: Lines 288-342 (Bills), 343-395 (Bill Returns)  
**Purpose**: Process service bills with complex discount calculations

**Process Flow for Service Bills**:
1. Load service bill data with geographic filters
2. For each product in bills:
   - Load bill master data for discount calculation
   - Calculate discounts based on payment method:
     ```php
     if ($theBill->card == 1) {
         if ($theBill->paymentnetworkid == 4) { // مدى (Mada)
             $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
             if ($madaData->totalCarry < 5000)
                 $discount = (7 * $madaData->totalCarry) / 1000;
             else
                 $discount = 40;
         } else {
             $discount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
         }
     }
     ```
   - Calculate proportional discount per product
   - Update product totals and quantities

**Discount Allocation Logic**:
```php
$discount += ($theBill->productstotalprice - $theBill->finalnetbillvalue);
if ($billNoOfProducts > 0) {
    $theDiscount = ($value->productno * $discount) / $billNoOfProduct;
} else {
    $theDiscount = $discount;
}
```

---

### 3. **Sales Bills Processing**  
**Location**: Lines 396-443 (Sales), 445-490 (Returns), 492-547 (Combined)
**Purpose**: Process regular sales transactions with unit conversions

**Unit Conversion Logic**:
```php
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber; // Convert to base unit (pieces)
```

**Sales Bill Discount Processing**:
```php
// Bill-level discount calculation
$dicount = $value->parcode - $value->note; // From bill totals
if ($dicount != 0) {
    $billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
    $billNoOfProduct = $billpecies->note;
    $theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
    $theDiscount -= $value->discountvalue; // Subtract line-item discount
}
```

---

### 4. **Final Calculations and Analysis**
**Location**: Lines 549-588  
**Purpose**: Calculate final metrics including profit and inventory

**Process Flow**:
1. Calculate net quantities and values:
   ```php
   foreach ($allDataArr as $data) {
       $data->netNo = $data->soldNo - $data->returnNo;
       $data->netVal = $data->soldVal - $data->returnVal;
   }
   ```

2. Calculate cost and profit:
   ```php
   $buyDatail = $ProductDAO->load($data->id);
   $data->realCost = $data->netNo * $buyDatail->productBuyPrice;
   $data->netProfit = $data->netVal - $data->realCost;
   ```

3. Load current inventory levels:
   ```php
   $buyDatail = $ProductEX->getProductQuantity($data->id, $myQuery);
   $data->currentQuantity = $buyDatail->sumProductQuantity;
   ```

4. Generate hierarchical product names for optic products:
   ```php
   if ($buyDatail->isOptic == 2) {
       $catName = $ProductEX->loadProductCatNameOnly($data->id);
       $data->productName = $data->productName . "/" . $catName;
   }
   ```

---

### 5. **get_parents_of_products()** - Hierarchical Product Paths
**Location**: Line 594  
**Purpose**: Generate complete category path for products

**Function Signature**:
```php
function get_parents_of_products($product_id)
```

**Process Flow**:
1. Load product data to get category ID
2. Call recursive `get_parents()` to build path
3. Combine path with product name
4. Return full hierarchical name

**Example Output**: "Electronics / Mobile Phones / Smartphones / iPhone 13"

---

### 6. **get_parents()** - Recursive Category Path Builder
**Location**: Line 613  
**Purpose**: Recursively build category hierarchy paths

**Function Signature**:
```php
function get_parents($productCatId, $parent_name)
```

**Recursive Logic**:
```php
$product_cat_data = $productCatDAO->load($productCatId);
$parent_name = $product_cat_data->productCatName . ' / ' . $parent_name;

if ($productCatParent != 0) {
    $product_cat_data_parent = $productCatDAO->load($productCatParent);
    $parent_name = $product_cat_data_parent->productCatName . ' / ' . $parent_name;
    
    if ($productCatParent_parent != 0) {
        get_parents($productCatParent_parent, $parent_name);
    }
}
```

---

## 🔄 Workflows

### Workflow 1: Area-Based Sales Analysis
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Geographic Filters               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Initialize and Load Settings                            │
│     - Load program settings for time zones                 │
│     - Apply authentication                                 │
│     - Load dropdown data (governments, areas, clients)     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Query Filters                                     │
│     - Apply government/area geographic filters             │
│     - Add date range with timezone adjustments             │
│     - Build separate query strings for each bill type      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Load Transaction Data (Multiple Sources)               │
│     ├─→ Service Bills (bills + billsproducts)              │
│     ├─→ Service Returns (billsreturn + billsreturnproducts)│
│     ├─→ Sales Bills (sellbill + sellbilldetail)            │
│     ├─→ Sales Returns (returnsellbill + returnsellbilldetail)│
│     └─→ Combined Bills (sellbillandrutern + details)       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Transaction Type                           │
│     FOR EACH transaction type:                              │
│       │                                                     │
│       ├─→ Service Bills:                                   │
│       │   ├─ Calculate complex payment discounts           │
│       │   ├─ Handle Mada payment special rates             │
│       │   └─ Allocate discounts proportionally             │
│       │                                                     │
│       ├─→ Sales Bills:                                     │
│       │   ├─ Convert units to base quantities              │
│       │   ├─ Calculate bill and line discounts             │
│       │   └─ Process sell vs return types                  │
│       │                                                     │
│       └─→ Aggregate into unified product structure         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Final Metrics                                 │
│     FOR EACH product:                                       │
│       │                                                     │
│       ├─→ Calculate net quantities (sold - returned)        │
│       │                                                     │
│       ├─→ Calculate net values (sales - returns)            │
│       │                                                     │
│       ├─→ Load current inventory levels                     │
│       │                                                     │
│       ├─→ Calculate costs and profit margins                │
│       │   └─ netProfit = netValue - (netQty * buyPrice)    │
│       │                                                     │
│       └─→ Generate hierarchical product paths              │
│           └─ "Category/Subcategory/Product Name"           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Report Output                                  │
│     - Calculate summary totals across all products         │
│     - Display geographic breakdown                         │
│     - Show profit analysis and inventory levels            │
│     - Present hierarchical product categorization          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| No `do` parameter or `do=show` | Default action | Main area-based sales report |

### Required Parameters

**Main Sales Report**:
- `governmentid` - Government/province ID for geographic filtering (optional)
- `clientareaid` - Client area ID within government (optional)
- `from` - Start date (YYYY-MM-DD, optional)
- `to` - End date (YYYY-MM-DD, optional)

**Example URLs**:
```
sellingReportByArea.php - All sales across all areas
sellingReportByArea.php?governmentid=1&from=2024-01-01&to=2024-01-31
sellingReportByArea.php?clientareaid=5&from=2024-01-01&to=2024-01-31
```

---

## 🧮 Calculation Methods

### Unit Conversion to Base Units
```php
function convertToBaseUnits($quantity, $productId, $unitId) {
    $productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);
    $productnumber = $productunitData->productnumber;
    return $quantity * $productnumber; // Convert to pieces
}
```

### Payment Network Discount Calculation
```php
// Special Mada payment network discount
if ($theBill->paymentnetworkid == 4) { // مدى
    $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
    if ($madaData->totalCarry < 5000)
        $discount = (7 * $madaData->totalCarry) / 1000;  // 0.7% for amounts < 5000
    else
        $discount = 40; // Fixed 40 for amounts >= 5000
}
```

### Proportional Discount Allocation
```php
// Allocate bill discount proportionally to products
if ($billNoOfProduct > 0) {
    $theDiscount = ($finalquantity * $billDiscount) / $billNoOfProduct;
    $theDiscount -= $lineItemDiscount; // Subtract already applied line discounts
}
```

### Profit Margin Calculation
```php
$data->netNo = $data->soldNo - $data->returnNo;
$data->netVal = $data->soldVal - $data->returnVal;
$data->realCost = $data->netNo * $data->buyPrice;
$data->netProfit = $data->netVal - $data->realCost;

// Profit margin percentage
$profitMargin = ($data->netVal > 0) ? ($data->netProfit / $data->netVal) * 100 : 0;
```

---

## 🔒 Security & Permissions

### Authentication
```php
include_once("../public/authentication.php");
```

### Geographic Access Control
- No specific geographic access restrictions implemented
- Users can access all areas and governments
- Could be enhanced with territory-based permissions

### Input Sanitization
- Date inputs should be validated for proper format
- Geographic IDs should be validated as integers
- Currently relies on DAO layer for SQL injection prevention

---

## 📊 Performance Considerations

### Database Performance Issues
1. **Multiple Query Types**: Queries 5 different transaction tables separately
2. **Complex Joins**: Geographic filtering involves multiple table joins
3. **Unit Conversion Queries**: Individual queries for each product unit conversion
4. **No Pagination**: Can load thousands of products for large areas

### Optimization Recommendations
```sql
-- Current: Multiple separate queries
-- Optimized: Single unified query with UNION

SELECT 'service' as source, p.productid, p.productname, bp.productno as quantity, 
       bp.producttotalprice as value, b.billdate as date
FROM bills b
JOIN billsproducts bp ON b.id = bp.billid  
JOIN product p ON bp.productid = p.productid
WHERE [filters]

UNION ALL

SELECT 'sales' as source, p.productid, p.productname, 
       (sd.sellbilldetailquantity * pu.productnumber) as quantity,
       sd.sellbilldetailtotalprice as value, s.sellbilldate as date  
FROM sellbill s
JOIN sellbilldetail sd ON s.sellbillid = sd.sellbillid
JOIN product p ON sd.sellbilldetailproductid = p.productid
JOIN productunit pu ON sd.productunitid = pu.productunitid
WHERE [filters]
```

### Memory Optimization
- Consider streaming results for large datasets
- Implement pagination for areas with many products
- Cache product hierarchy paths to avoid repeated calculations

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Unit Conversions**
**Issue**: Quantities don't match between reports  
**Cause**: Missing or incorrect productunit data

**Debug**:
```sql
-- Check unit conversion data
SELECT p.productname, pu.productnumber, u.unitname
FROM product p
LEFT JOIN productunit pu ON p.productid = pu.productid
LEFT JOIN unit u ON pu.unitid = u.unitid
WHERE p.productid = [PRODUCT_ID];

-- Find products without unit data
SELECT p.productid, p.productname
FROM product p
LEFT JOIN productunit pu ON p.productid = pu.productid  
WHERE pu.productunitid IS NULL;
```

### 2. **Missing Geographic Data**
**Issue**: Products show for wrong areas or don't appear  
**Cause**: Client area assignments incomplete

**Fix**:
```sql
-- Verify client-area relationships
SELECT c.clientname, ca.name as area_name, g.governmentname
FROM client c
LEFT JOIN clientarea ca ON c.clientareaid = ca.id
LEFT JOIN goverarea ga ON ca.id = ga.clientareaid  
LEFT JOIN government g ON ga.governmentid = g.governmetid
WHERE c.clientareaid IS NULL OR ca.id IS NULL;
```

### 3. **Discount Calculation Errors**
**Issue**: Profit margins don't match expected values  
**Cause**: Complex discount allocation logic errors

**Debug**: Add logging to discount calculation:
```php
error_log("Product: {$productId}, Bill Discount: {$billDiscount}, Line Discount: {$lineDiscount}, Final: {$theDiscount}");
```

---

## 🧪 Testing Scenarios

### Test Case 1: Multi-Bill Type Consolidation
```
1. Create sales bill, return bill, and service bill for same product
2. Run area report for date range covering all bills
3. Verify quantities and values aggregate correctly
4. Check net calculations (sold - returned)
```

### Test Case 2: Geographic Filtering
```
1. Set up clients in different areas/governments
2. Create sales for each area
3. Filter by specific government
4. Verify only sales from that government appear
5. Test area-level filtering within government
```

### Test Case 3: Unit Conversion Accuracy
```
1. Create products with different unit types (pieces, boxes, cartons)
2. Create sales with various units
3. Verify all quantities convert to base unit (pieces)
4. Compare with manual calculations
```

### Test Case 4: Profit Calculation Verification
```
1. Create sales with known buy prices and selling prices
2. Apply various discount types
3. Verify profit calculations match expected margins
4. Test with returns to ensure proper net profit calculation
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [salesreport.md](salesreport.md) - General sales reporting
- [reportprovinces.md](reportprovinces.md) - Geographic customer analysis
- [reportfunctions.md](reportfunctions.md) - Shared utility functions

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur