# 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
- [x] Price type analysis across all sales channels
- [x] Unit-based product reporting with conversions
- [x] Multi-store sales consolidation
- [x] Product category hierarchy filtering
- [x] Client-specific sales analysis
- [x] Date range filtering with system timezone support
- [x] Sorting capabilities by sales totals
- [x] Service bills and regular sales integration

### Related Controllers
- [salesreport.php](salesreport.md) - General sales reporting
- [sellingReportByArea.php](sellingReportByArea.md) - Geographic sales analysis
- [reportfunctions.php](reportfunctions.md) - 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**:
```php
// 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:
   ```php
   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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [salesreport.md](salesreport.md) - General sales reporting
- [sellingReportByArea.md](sellingReportByArea.md) - Geographic sales analysis
- [reportfunctions.md](reportfunctions.md) - Shared utility functions

---

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