Sell Report Price Type Controller Documentation

File: /controllers/sellreportpricetype.php

Purpose: Generate detailed sales reports with price type analysis and unit-based product breakdown

Last Updated: December 21, 2024

Total Functions: 7

Lines of Code: ~845

---

📋 Overview

The Sell Report Price Type Controller provides comprehensive sales analysis focused on price types and unit-based product reporting. It offers:

• Price type filtering and analysis across all transaction types
• Unit-based product quantity and value reporting
• Multi-transaction type consolidation (sales, returns, service bills)
• Product category hierarchy filtering
• Store-based reporting with multi-store support
• Client and date range filtering capabilities
• Detailed product unit analysis with conversion factors
• Sorting by total values (ascending/descending)

Primary Functions

✅ Price type analysis across all sales channels
✅ Unit-based product reporting with conversions
✅ Multi-store sales consolidation
✅ Product category hierarchy filtering
✅ Client-specific sales analysis
✅ Date range filtering with system timezone support
✅ Sorting capabilities by sales totals
✅ Service bills and regular sales integration

Related Controllers

• salesreport.php - General sales reporting
• sellingReportByArea.php - Geographic sales analysis
• reportfunctions.php - Shared utility functions

---

🗄️ Database Tables

Sales Transaction Tables

Table Name	Purpose	Key Columns
sellbill	Sales bills	sellbillid, sellbillclientid, sellbilldate, sellbillstoreid
sellbilldetail	Sales bill line items	sellbilldetailid, sellbilldetailproductid, sellbilldetailquantity, pricetype, unitid
returnsellbill	Sales return bills	returnsellbillid, returnsellbillclientid, returnsellbilldate, returnsellbillstoreid
returnsellbilldetail	Return bill details	returnsellbilldetailid, returnsellbilldetailproductid, returnsellbilldetailquantity, pricetype
sellbillandrutern	Combined bills	sellbillid, sellbillclientid, sellbilldate, sellbillstoreid
sellandruternbilldetail	Combined bill details	sellandruternbilldetailid, sellbilldetailproductid, sellbilldetailquantity, pricetype, selltype

### Service Bills Tables

Table Name	Purpose	Key Columns
bills	Service bills	id, clientid, billdate, pricetype
billsproducts	Service bill products	billproductsid, billid, productid, productno, producttotalprice
billsreturn	Service bill returns	billsreturnid, clientid, date, pricetype
billsreturnproducts	Return bill products	billsreturnproductsid, billproductid, productid, productno, producttotalprice

### Product and Unit Tables

Table Name	Purpose	Key Columns
product	Product master	productid, productname, productcatid, productbuyprice
productcat	Product categories	productcatid, productcatname, productcatparent
productunit	Unit definitions	productunitid, productid, unitid, productnumber
unit	Unit master	unitid, unitname, conditions

### System Tables

Table Name	Purpose	Key Columns
store	Store locations	storeid, storename
client	Customer master	clientid, clientname
usergroup	User groups	usergroupid, groupname
youtubelink	Tutorial videos	youtubelinkid, title, url

---

🔑 Key Functions

1. Default Action - Price Type Sales Report

Location: Line 162 (empty($do))

Purpose: Generate comprehensive sales report with price type analysis

Function Signature:

// POST parameters processed:
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto'); 
$pricetype = filter_input(INPUT_POST, 'pricetype');
$order = filter_input(INPUT_POST, 'order'); // asc/desc/no
$clientid = filter_input(INPUT_POST, 'clientid');
$storeId = filter_input(INPUT_POST, 'storeId');
$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
$productId = filter_input(INPUT_POST, 'productId');
$searchtype = filter_input(INPUT_POST, 'searchtype');
$isOptic = filter_input(INPUT_POST, 'proIsOptic');

Process Flow:

1. Load authentication and system configuration

2. Load dropdown data (stores, categories, clients, user groups)

3. Process search parameters with validation:

   if ($order != "asc" && $order != "desc") {
       $order = "no";
   }
   ```
4. Handle optic product special filtering:
   ```php
   if ($isOptic == 2 && $searchtype == 1) {
       $productCatId = $productId; // For optic products, category = product
   }
   ```
5. Build query strings for all transaction types
6. Apply filters (date, price type, client, store, product/category)
7. Call `getData()` with all query strings
8. Display results via template

**Query String Building**:

php

$queryString = " where 1 "; // Bills

$queryStringR = " where 1 "; // Bills return

$queryString1 = " where 1 "; // Sellbill

$queryString1R = " where 1 "; // Sellbill return

$queryString1SR = " where 1 "; // Sellbill and return

// Date filters

if (isset($datefrom) && !empty($datefrom)) {

$queryString .= 'and date(bills.billdate) >= "' . $datefrom . '" ';

$queryString1 .= 'and sellbill.sellbilldate >= "' . $datefrom . '" ';

// ... additional date filters

}

// Price type filter

if (isset($pricetype) && $pricetype != -1) {

$queryString .= 'and bills.pricetype = ' . $pricetype . ' ';

$queryString1 .= 'and sellbilldetail.pricetype = ' . $pricetype . ' ';

// ... additional price type filters

}

---

### 2. **getData()** - Core Report Processing
**Location**: Line 311  
**Purpose**: Process all transaction types and generate unified product analysis

**Function Signature**:

php

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $order)

**Process Flow**:
1. Handle default date filtering (today if no filters):
   ```php
   if ($queryString == " where 1 ") {
       $today = date("Y-m-d");
       $queryString .= 'and  date(bills.billdate)  = "' . $today . '" ';
       // ... apply today filter to all query strings
   }
   ```

2. Load data from all transaction sources:
   ```php
   $billsData = $billsProductsEX->queryAllGeneral($queryString);
   $billsDataReturn = $billsReturnProductsEX->queryAllGeneralPriceType($queryStringR);
   $sellBillData = $sellbilldetailEX->queryAllGeneralPriceType($queryString1);
   $sellBillDataReturn = $returnSellBillDetailEX->queryAllGeneralPriceType($queryString1R);
   $sellBillDataSellAndReturn = $sellAndRuternBillDetailEX->queryAllGeneralPriceType($queryString1SR);
   ```

3. Create unified data structure using `productUnitData` class:
   ```php
   class productUnitData {
       public $productId;
       public $productName;
       public $unitId;
       public $unitName;
       public $amount = 0;      // Quantity
       public $price = 0;       // Total value
       public $pricetype;       // Price type
   }
   ```

4. Process each transaction type and aggregate by product + unit combination
5. Apply sorting if requested
6. Generate hierarchical product names
7. Assign results to template

---

### 3. **Service Bills Processing**
**Location**: Lines 355-429 (Bills), 431-492 (Bill Returns)  
**Purpose**: Process service bills with unit handling

**Key Logic**:

php

foreach ($billsData as $value) {

$productid = $value->productid;

$value->producttotalprice = round($value->producttotalprice, 2);

// Get unit information (all service bill products use "unit of one piece")

$unitData = $productUnitEX->getUnitDataOfUnityWithProductId($productid);

$unitid = 0;

$unitname = 'وحدة'; // Default unit name

if (count($unitData) > 0) {

$unitid = $unitData->unitid;

$unitname = $unitData->conditions;

}

// Check if product+unit combination exists

if (isset($allDataArr[$productid])) {

if (in_array($unitid, $AllDataIndexArr[$productid])) {

// Update existing product+unit

$key2 = array_search($unitid, $AllDataIndexArr[$productid]);

$myproduct = $allDataArr[$productid][$key2];

$myproduct->price += $value->producttotalprice;

$myproduct->amount += $value->productno;

} else {

// New unit for existing product

// ... create new unit entry

}

} else {

// New product and unit

// ... create new product entry

}

---

### 4. **Sales Bills Processing**
**Location**: Lines 494-554 (Sales), 556-611 (Returns), 613-688 (Combined)  
**Purpose**: Process regular sales with full unit conversion support

**Unit Handling**:

php

foreach ($sellBillData as $value) {

$productid = $value->sellbilldetailproductid;

$unitid = $value->unitid;

$unitname = $value->unitName;

$quantity = $value->sellbilldetailquantity;

// Process product+unit combination

if (isset($allDataArr[$productid])) {

if (in_array($unitid, $AllDataIndexArr[$productid])) {

// Existing product+unit combination

$key2 = array_search($unitid, $AllDataIndexArr[$productid]);

$myproduct = $allDataArr[$productid][$key2];

$myproduct->price += $value->sellbilldetailtotalprice;

$myproduct->amount += $quantity;

}

// ... handle new unit or new product cases

}

---

### 5. **sortByTotal()** - Results Sorting
**Location**: Line 736  
**Purpose**: Sort products by total sales value

**Function Signature**:

php

function sortByTotal($type, $allDataArrTemp, $productTotalArrTemp)

**Sorting Logic**:

php

foreach ($productTotalArrTemp as $key => $value) {

if ($type == "asc") {

$minTotalIndex = array_keys($productTotalArrTemp, min($productTotalArrTemp));

$minTotalIndex = $minTotalIndex[0];

unset($productTotalArrTemp[$minTotalIndex]);

$allDataArr[$minTotalIndex] = $allDataArrTemp[$minTotalIndex];

} elseif ($type == "desc") {

$maxTotalIndex = array_keys($productTotalArrTemp, max($productTotalArrTemp));

$maxTotalIndex = $maxTotalIndex[0];

unset($productTotalArrTemp[$maxTotalIndex]);

$allDataArr[$maxTotalIndex] = $allDataArrTemp[$maxTotalIndex];

}

---

### 6. **getAllSubCat()** - Category Hierarchy Navigation
**Location**: Line 756  
**Purpose**: Recursively get all subcategories for filtering

**Function Signature**:

php

function getAllSubCat($catid, $mode)

**Modes**:
- `$mode = 1`: Get all subcategories (recursive)
- `$mode = 2`: Get only last-level categories (no children)

**Recursive Logic**:

php

$result = $productCatExt->queryByParentExt($catid);

if (count($result) > 0) {

foreach ($result as $data) {

if ($mode == 1) {

$catsIDS .= ", " . $data->productCatId;

getAllSubCat($data->productCatId, $mode); // Recursive call

}

// ... handle mode 2 for last-level categories

}

---

### 7. **get_parents_of_products()** & **get_parents()** - Product Hierarchy
**Location**: Lines 796-844  
**Purpose**: Generate hierarchical product names with full category paths

Similar to other controllers, these functions build complete product paths like:
"Category / Subcategory / Product Name"

---

## 🔄 Workflows

### Workflow 1: Price Type Sales Analysis

┌─────────────────────────────────────────────────────────────┐

│ START: Select Filters and Price Type │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Process Search Parameters │

│ - Validate date range │

│ - Validate price type selection │

│ - Process store and client filters │

│ - Handle product/category selection │

│ - Validate sort order (asc/desc/no) │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Build Query Strings for All Transaction Types │

│ ├─→ Service Bills (bills + billsproducts) │

│ ├─→ Service Returns (billsreturn + billsreturnproducts)│

│ ├─→ Sales Bills (sellbill + sellbilldetail) │

│ ├─→ Sales Returns (returnsellbill + returnsellbilldetail)│

│ └─→ Combined Bills (sellbillandrutern + details) │

│ │

│ Apply filters to each query string: │

│ ├─ Date range filters │

│ ├─ Price type filters │

│ ├─ Client ID filters │

│ ├─ Store ID filters │

│ └─ Product/Category filters │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Load Transaction Data │

│ Execute all queries and load: │

│ ├─ $billsData - Service bill products │

│ ├─ $billsDataReturn - Service return products │

│ ├─ $sellBillData - Sales bill details │

│ ├─ $sellBillDataReturn - Sales return details │

│ └─ $sellBillDataSellAndReturn - Combined bill details │

│ │

│ Total results count: [sum of all data sets] │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 4. Process and Consolidate Data │

│ Initialize data structures: │

│ ├─ $allDataArr[productid][unitindex] = productUnitData │

│ ├─ $AllDataIndexArr[productid] = [unit indexes] │

│ └─ $unitTotal[unitname] = total quantity │

│ │

│ FOR EACH transaction type: │

│ FOR EACH product transaction: │

│ │ │

│ ├─→ Determine unit information │

│ │ ├─ Service bills: Get unit for "one piece" │

│ │ └─ Sales bills: Use transaction unit │

│ │ │

│ ├─→ Check if product+unit exists │

│ │ ├─ EXISTS: Update quantity and price │

│ │ └─ NEW: Create new productUnitData entry │

│ │ │

│ └─→ Update unit totals │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 5. Calculate Totals and Apply Sorting │

│ Calculate product totals: │

│ FOR EACH product: │

│ ├─ Sum all unit values for product │

│ ├─ Add to grand total │

│ └─ Store in $productTotalArr │

│ │

│ IF sorting requested: │

│ └─ Apply sortByTotal() with asc/desc order │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 6. Generate Hierarchical Product Names │

│ FOR EACH product: │

│ ├─ Call get_parents_of_products() │

│ ├─ Build complete category path │

│ └─ Update productName with full hierarchy │

│ │

│ Final format: "Category/Subcategory/Product Name" │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 7. Generate Report Output │

│ Template variables assigned: │

│ ├─ $allDataArr - Product+unit data │

│ ├─ $unitTotal - Total quantities by unit │

│ ├─ $totalOfTotals - Grand total value │

│ ├─ $productTotalArr - Totals by product │

│ └─ $resultsCount - Number of transactions processed │

│ │

│ Display via: sellreportpricetypeview/show.html │

└─────────────────────────────────────────────────────────────┘

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| No `do` parameter | Default action | Price type sales report |

### Required Parameters

**Main Report**:
- `datefrom` - Start date (YYYY-MM-DD, optional)
- `dateto` - End date (YYYY-MM-DD, optional)
- `pricetype` - Price type filter (-1 for all, optional)
- `order` - Sort order (asc/desc/no, optional)
- `clientid` - Client ID filter (-1 for all, optional)
- `storeId` - Store ID filter (-1 for all, optional)
- `level` - Category level for filtering (optional)
- `productCatId{level}` - Category ID at specified level (optional)
- `productId` - Specific product ID (optional)
- `searchtype` - Search type (optional)
- `proIsOptic` - Optic product flag (optional)

**Example URLs**:

sellreportpricetype.php

sellreportpricetype.php?datefrom=2024-01-01&dateto=2024-01-31&pricetype=1&order=desc

---

## 🧮 Calculation Methods

### Unit Total Calculation

php

if (!isset($unitTotal[$myproduct->unitName])) {

$unitTotal[$myproduct->unitName] = 0;

}

$unitTotal[$myproduct->unitName] += $quantity;

### Product Total Aggregation

php

$productTotalArr = array();

$totalOfTotals = 0;

foreach ($allDataArr as $data) {

$prototal = 0;

$proid = 0;

$i = 0;

foreach ($data as $product) {

$prototal += $product->price;

if ($i == 0) {

$proid = $product->productId;

}

$i++;

}

$productTotalArr[$proid] = $prototal;

$totalOfTotals += $prototal;

}

### Query String Building Pattern

php

// Start with base condition

$queryString = " where 1 ";

// Add date filters

if (isset($datefrom) && !empty($datefrom)) {

$queryString .= 'and date(bills.billdate) >= "' . $datefrom . '" ';

}

// Add price type filter

if (isset($pricetype) && $pricetype != -1) {

$queryString .= 'and bills.pricetype = ' . $pricetype . ' ';

}

// Add product/category filter

if (isset($productId) && !empty($productId) && $productId != -1) {

$queryString .= 'and billsproducts.productid in( ' . $productId . ' ) ';

}

---

## 🔒 Security & Permissions

### Authentication

php

include_once("../public/authentication.php");

### Input Validation

php

// Filter and validate inputs

$datefrom = filter_input(INPUT_POST, 'datefrom');

$dateto = filter_input(INPUT_POST, 'dateto');

$pricetype = filter_input(INPUT_POST, 'pricetype');

$order = filter_input(INPUT_POST, 'order');

// Validate order parameter

if ($order != "asc" && $order != "desc") {

$order = "no";

}

### SQL Injection Prevention
- Uses filter_input() for parameter sanitization
- Relies on DAO layer for prepared statements
- Direct concatenation in query strings should be improved

### Recommended Improvements

php

// Better input validation

$pricetype = filter_input(INPUT_POST, 'pricetype', FILTER_VALIDATE_INT);

if ($pricetype === false) $pricetype = -1;

$clientid = filter_input(INPUT_POST, 'clientid', FILTER_VALIDATE_INT);

if ($clientid === false) $clientid = -1;

// Validate dates

if (!empty($datefrom) && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $datefrom)) {

$datefrom = '';

}

---

## 📊 Performance Considerations

### Database Performance
1. **Multiple Query Types**: 5 separate queries for different transaction types
2. **Complex Filtering**: Multiple WHERE conditions across different table structures
3. **Unit Lookup Queries**: Individual unit queries for service bill products
4. **Category Hierarchy**: Recursive category traversal can be expensive

### Memory Usage
- Large datasets can consume significant memory with multi-dimensional arrays
- Product+unit combinations create complex data structures
- Sorting large datasets requires memory for temporary arrays

### Optimization Recommendations
1. **Query Consolidation**: Use UNION to combine similar queries
2. **Indexing**: Ensure proper indexes on date, pricetype, productid columns
3. **Pagination**: Add pagination for large result sets
4. **Caching**: Cache category hierarchy and unit data

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Unit Information**
**Issue**: Service bill products show no unit name  
**Cause**: Missing productunit data for "one piece" units

**Fix**:

sql

-- Create missing unit relationships

INSERT INTO productunit (productid, unitid, productnumber)

SELECT p.productid, 1, 1 -- Assuming unitid 1 is "piece"

FROM product p

LEFT JOIN productunit pu ON p.productid = pu.productid

WHERE pu.productunitid IS NULL;

### 2. **Incorrect Total Calculations**
**Issue**: Unit totals don't match individual product sums  
**Cause**: Data aggregation logic errors

**Debug**: Add logging to track data flow:

php

error_log("Product: {$productid}, Unit: {$unitid}, Amount: {$amount}, Price: {$price}");

### 3. **Price Type Filter Not Working**
**Issue**: Price type filter shows all data  
**Cause**: Different price type field names across table types

**Verification**:

sql

-- Check price type field names

DESCRIBE sellbilldetail; -- pricetype

DESCRIBE bills; -- pricetype

DESCRIBE returnsellbilldetail; -- pricetype

---

## 🧪 Testing Scenarios

### Test Case 1: Price Type Filtering

1. Create transactions with different price types (1, 2, 3)

2. Filter by specific price type

3. Verify only transactions with that price type appear

4. Test across all transaction types (sales, returns, service)

### Test Case 2: Unit Consolidation

1. Create sales with same product in different units

2. Verify data consolidates by product+unit combination

3. Check unit totals calculate correctly

4. Test with service bills (default to "piece" unit)

### Test Case 3: Sorting Functionality

1. Create products with varying sales totals

2. Test ascending sort - verify lowest values first

3. Test descending sort - verify highest values first

4. Test "no" sort - verify default order maintained

### Test Case 4: Category Hierarchy Filtering

1. Set up nested product categories (3+ levels)

2. Filter by parent category

3. Verify all child category products included

4. Test with specific product selection vs category selection

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• salesreport.md - General sales reporting
• sellingReportByArea.md - Geographic sales analysis
• reportfunctions.md - Shared utility functions

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Sellreportpricetype Documentation

Sell Report Price Type Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Sales Transaction Tables

🔑 Key Functions

1. Default Action - Price Type Sales Report

📚 Related Documentation