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

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

Related Controllers

• salesreport.php - General sales reporting
• reportprovinces.php - Geographic customer analysis
• sellreportpricetype.php - Price type analysis
• reportfunctions.php - 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:

// 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:

   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 - PHP 8.2 migration guide
• salesreport.md - General sales reporting
• reportprovinces.md - Geographic customer analysis
• reportfunctions.md - Shared utility functions

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

SellingReportByArea Documentation

Selling Report By Area Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Geographic Tables

🔑 Key Functions

1. Default Action - Area-Based Sales Report

📚 Related Documentation