# Sales Report Controller Documentation

**File**: `/controllers/salesreport.php`  
**Purpose**: Comprehensive sales analysis and reporting system with advanced filtering and profit calculations  
**Last Updated**: December 21, 2024  
**Total Functions**: 8  
**Lines of Code**: ~2898

---

## 📋 Overview

The Sales Report Controller is the primary sales analysis engine of the ERP system, providing comprehensive reporting capabilities across all transaction types. It offers:
- Advanced multi-criteria sales filtering and analysis
- Comprehensive product profit and cost analysis with multiple pricing methods
- Multi-transaction type consolidation (sales, returns, service bills, combined bills)
- Product category hierarchy filtering with recursive subcategory inclusion
- Store-based analysis with multi-store support
- Client type filtering and analysis
- User and branch-based sales analysis
- Tax bill filtering for approved sales
- Consumption analysis for inventory management
- Advanced cost calculation methods with discount and tax handling

### Primary Functions
- [x] Multi-criteria sales analysis with advanced filtering
- [x] Profit calculation with multiple cost basis options
- [x] Product category hierarchy analysis with recursive filtering
- [x] Multi-transaction type consolidation and reporting
- [x] Store and branch-based sales analysis
- [x] Client type and individual client filtering
- [x] User performance analysis
- [x] Tax bill and approved sales filtering
- [x] Consumption analysis for inventory planning
- [x] Advanced cost methods (last buy, mean buy, with discounts/tax)

### Related Controllers
- [sellingReportByArea.php](sellingReportByArea.md) - Geographic sales analysis
- [sellreportpricetype.php](sellreportpricetype.md) - Price type analysis
- [salesNumbersReport.php](salesNumbersReport.md) - Movement analysis
- [reportfunctions.php](reportfunctions.md) - Shared utility functions

---

## 🗄️ Database Tables

### Core Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilldate, sellbilltotalbill, sellbillaftertotalbill, sellbilldiscount, userid, taxBillNumber, conditions |
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid, buyprice, storeid |
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbillclientid, returnsellbilldate, returnsellbilltotalbill, conditions |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbilldetailproductid, returnsellbilldetailquantity, productunitid |
| **sellbillandrutern** | Combined bills | sellbillid, sellbillclientid, sellbilldate, sellbilltotalbill, conditions |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbilldetailproductid, sellbilldetailquantity, selltype, productunitid |

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

### Product and Pricing Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master | productid, productname, productcatid, productbuyprice, buyprice, lastbuyprice, meanbuyprice, lastbuyprice_withDiscount, meanbuyprice_withDiscount, lastbuyprice_withTax, meanbuyprice_withTax |
| **productcat** | Product categories | productcatid, productcatname, productcatparent, opticservices |
| **productunit** | Unit conversions | productunitid, productid, unitid, productnumber |

### Client and User Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer master | clientid, clientname, typeclientid |
| **typeclient** | Client types | typeclientid, typename |
| **user** | System users | userid, username, userlevel, usergroupid, branchid |
| **usergroup** | User groups | usergroupid, groupname |
| **branch** | Branch locations | branchid, branchname |

### Store and Geographic Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **store** | Store locations | storeid, storename |
| **government** | Governments/provinces | governmetid, governmentname |
| **youtubelink** | Tutorial videos | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Comprehensive Sales Report
**Location**: Line 199 (`empty($do)` or `$do == "approvedsales"`)  
**Purpose**: Generate advanced sales analysis with multiple filtering options

**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');
$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');
$chosenProductPrice = filter_input(INPUT_POST, 'chosenProductPrice');
$typeIdOfClient = filter_input(INPUT_POST, 'typeIdOfClient');
$userid = filter_input(INPUT_POST, 'userid');
$branchid = filter_input(INPUT_POST, 'branchid');
$servicesAreIncluded = filter_input(INPUT_POST, 'servicesAreIncluded');
```

**Advanced Filtering Options**:
1. **Date Range**: Start and end date filtering
2. **Price Type**: Filter by specific price types
3. **Client Filtering**: Individual client or client type filtering
4. **Product Filtering**: Category, individual product, or optic product filtering
5. **Store Filtering**: Multi-store or single store analysis
6. **User Analysis**: Filter by sales user or user branch
7. **Cost Basis Selection**: Multiple cost calculation methods
8. **Service Bill Inclusion**: Optional service bill integration

**Process Flow**:
1. Load authentication and system configuration
2. Process and validate all filter parameters
3. Handle special optic product filtering:
   ```php
   if ($isOptic == 2 && $searchtype == 1) {
       $productCatId = $productId; // For optic products, use product as category
   }
   ```
4. Build complex query strings for all transaction types
5. Apply date, store, client, product, and user filters
6. Call advanced data processing function (`getDataNew()`)
7. Display results with comprehensive filtering applied

**Special Features**:
- **Approved Sales Mode**: When `$do == "approvedsales"`, filters for tax bills only
- **Recursive Category Filtering**: Includes all subcategories automatically
- **Multi-Store Support**: Handles user store permissions and access control
- **Service Bill Integration**: Optionally includes service transactions

---

### 2. **getDataNew()** - Advanced Data Processing Engine
**Location**: Line 824  
**Purpose**: Core processing engine with optimized queries and advanced calculations

**Function Signature**:
```php
function getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDSOfProducts, $typeIdOfClient, $servicesAreIncluded)
```

**Advanced Features**:
1. **Optimized Data Loading**: Batch queries with custom indexing
2. **Client Type Filtering**: Advanced client filtering by type
3. **Multiple Cost Basis Support**: 8 different cost calculation methods
4. **Advanced Product Filtering**: Category and individual product support
5. **Performance Optimizations**: Custom array indexing and batch processing

**Cost Calculation Methods**:
```php
switch ($chosenProductPrice) {
    case 0: $priceName = ',buyprice';                    // Bill cost price
    case 1: $priceName = ',meanbuyprice';                // Mean buy price
    case 2: $priceName = ',lastbuyprice';                // Last buy price
    case 3: $priceName = ',lastbuyprice_withDiscount';   // Last price with discount
    case 4: $priceName = ',meanbuyprice_withDiscount';   // Mean price with discount
    case 5: /* General price calculation */
    case 6: $priceName = ',lastbuyprice_withTax';        // Last price with tax
    case 7: $priceName = ',meanbuyprice_withTax';        // Mean price with tax
    case 8: $priceName = ',lastbuyprice_withDiscountAndTax'; // Last price with discount and tax
}
```

**Data Processing Workflow**:
1. **Load Sales Bills**: Query with filters and batch load client names
2. **Load Bill Details**: Batch load with product unit conversions
3. **Load Product Data**: Apply chosen cost calculation method
4. **Process Returns**: Handle return bills and details
5. **Process Combined Bills**: Handle sell/return combination bills
6. **Calculate Profits**: Apply chosen cost method and calculate margins
7. **Generate Totals**: Calculate summary totals and format output

---

### 3. **Consumption Analysis** - Inventory Planning
**Location**: Line 625 (`$do == "consumption"`)  
**Purpose**: Advanced consumption analysis for inventory planning

**Features**:
- **Product Category Analysis**: Deep consumption patterns by category
- **Date Range Consumption**: Analyze consumption over specific periods
- **Store-based Analysis**: Consumption patterns by store location
- **Unit Conversion Support**: Accurate consumption in base units
- **Trend Analysis**: Historical consumption patterns

**Process Flow**:
1. Apply same filtering as main sales report
2. Focus on quantity consumption rather than monetary values
3. Calculate consumption rates and patterns
4. Generate inventory planning recommendations

---

### 4. **getData()** - Legacy Processing Engine
**Location**: Line 1976  
**Purpose**: Original data processing engine (maintained for compatibility)

**Function Signature**:
```php
function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore)
```

**Key Differences from getDataNew()**:
- Simpler parameter structure
- No client type filtering
- No service bill integration option
- Original array processing logic
- Maintained for backward compatibility

---

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

**Function Signature**:
```php
function getAllSubCat($catid, $mode)
```

**Modes**:
- `$mode = 1`: Get all subcategories recursively
- `$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);
        } elseif ($mode == 2) {
            $childData = $productCatExt->queryByParentExt($data->productCatId);
            if (count($childData) == 0) {
                array_push($lastLevelCatIDS, $data->productCatId);
            } else {
                getAllSubCat($data->productCatId, $mode);
            }
        }
    }
}
```

---

### 6. **getMainParentId()** - Root Category Finder
**Location**: Line 2814  
**Purpose**: Find the root parent category for any given category

**Function Signature**:
```php
function getMainParentId($catid)
```

**Process**: Recursively traverse category hierarchy to find top-level parent

---

### 7. **CURL_IT2()** - External Integration
**Location**: Line 2862  
**Purpose**: External system communication (likely for integrations)

**Function Signature**:
```php
function CURL_IT2($data_arr, $url)
```

**Features**:
- POST data transmission
- External API communication
- Integration support for third-party systems

---

## 🔄 Workflows

### Workflow 1: Advanced Sales Analysis
```
┌─────────────────────────────────────────────────────────────┐
│              START: Configure Advanced Filters              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Multiple Filter Parameters                     │
│     ├─ Date range (from/to with timezone support)          │
│     ├─ Client filtering (individual or by type)            │
│     ├─ Product filtering (category/individual/optic)       │
│     ├─ Store filtering (single/multi-store)                │
│     ├─ User performance filtering                          │
│     ├─ Branch-based analysis                               │
│     ├─ Price type filtering                                │
│     ├─ Cost calculation method selection                   │
│     └─ Service bill inclusion option                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Handle Special Filter Logic                             │
│     IF optic product mode:                                  │
│       └─→ Treat individual product as category             │
│                                                             │
│     IF category selected:                                   │
│       ├─→ Get all subcategories recursively                │
│       └─→ Build comprehensive product ID list              │
│                                                             │
│     IF approved sales mode:                                 │
│       └─→ Filter for tax bill numbers only                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Complex Query Strings                            │
│     FOR EACH transaction type:                              │
│     ├─→ 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:                           │
│     ├─ Date range filters                                  │
│     ├─ Store access control                                │
│     ├─ Client/client type filters                          │
│     ├─ Product/category filters                            │
│     ├─ User/branch filters                                 │
│     └─ Price type filters                                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Execute Advanced Data Processing                       │
│     Call getDataNew() with all parameters:                 │
│       │                                                     │
│       ├─→ Batch Load Sales Bills                          │
│       │   ├─ Apply client type filtering                   │
│       │   └─ Load client names in batch                    │
│       │                                                     │
│       ├─→ Batch Load Bill Details                         │
│       │   ├─ Apply product filtering                       │
│       │   ├─ Include unit conversion data                  │
│       │   └─ Include multiple cost prices                  │
│       │                                                     │
│       ├─→ Load Product Master Data                        │
│       │   ├─ Apply chosen cost calculation method          │
│       │   ├─ Include hierarchy information                 │
│       │   └─ Load current inventory levels                 │
│       │                                                     │
│       ├─→ Process Return Transactions                     │
│       │   ├─ Load return bills and details                 │
│       │   └─ Apply same filtering logic                    │
│       │                                                     │
│       └─→ Process Service Bills (if included)             │
│           ├─ Load service transactions                     │
│           └─ Apply discount calculations                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Advanced Metrics                             │
│     FOR EACH product in results:                           │
│       │                                                     │
│       ├─→ Apply Chosen Cost Calculation Method            │
│       │   ├─ Bill cost price (from transaction)            │
│       │   ├─ Last buy price                                │
│       │   ├─ Mean buy price                                │
│       │   ├─ Prices with discounts applied                 │
│       │   ├─ Prices with tax applied                       │
│       │   └─ Prices with both discount and tax             │
│       │                                                     │
│       ├─→ Calculate Unit Conversions                      │
│       │   ├─ Convert all quantities to base units          │
│       │   └─ Handle multiple unit types per product        │
│       │                                                     │
│       ├─→ Calculate Net Values                            │
│       │   ├─ Net quantity (sold - returned)                │
│       │   ├─ Net value (sales - returns)                   │
│       │   └─ Apply discount allocations                    │
│       │                                                     │
│       ├─→ Calculate Profit Metrics                        │
│       │   ├─ Cost basis using chosen method                │
│       │   ├─ Gross profit (net value - cost)               │
│       │   ├─ Profit margin percentage                      │
│       │   └─ Cost-to-sale ratio                            │
│       │                                                     │
│       └─→ Generate Product Hierarchy Path                 │
│           ├─ Build complete category path                  │
│           └─ Include optic service categorization          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Comprehensive Report Output                   │
│     ├─ Summary totals across all products                  │
│     ├─ Profit analysis and margins                         │
│     ├─ Product performance ranking                         │
│     ├─ Category performance analysis                       │
│     ├─ User/branch performance (if filtered)               │
│     ├─ Store performance comparison                        │
│     ├─ Client type analysis (if applicable)                │
│     └─ Trend analysis over date range                      │
│                                                             │
│     Display via: salesreport/show.html template            │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| No `do` parameter | Default action | Comprehensive sales analysis |
| `do=approvedsales` | Default action | Approved sales with tax bill filtering |
| `do=consumption` | Consumption analysis | Inventory consumption analysis |

### Advanced Filter Parameters

**Date and Basic Filters**:
- `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)

**Product and Category Filters**:
- `level` - Category level for filtering (1-5, optional)
- `productCatId{level}` - Category ID at specified level (optional)
- `productId` - Specific product ID (optional)
- `searchtype` - Search type mode (optional)
- `proIsOptic` - Optic product flag (0/1/2, optional)

**Client and Store Filters**:
- `clientid` - Individual client ID (-1 for all, optional)
- `typeIdOfClient` - Client type ID (-1 for all, optional)
- `storeId` - Store ID (-1 for all, optional)

**User and Branch Filters**:
- `userid` - User ID for performance analysis (optional)
- `branchid` - Branch ID for branch analysis (optional)

**Advanced Options**:
- `chosenProductPrice` - Cost calculation method (0-8, optional)
- `servicesAreIncluded` - Include service bills (1/0, optional)

**Example URLs**:
```
salesreport.php?datefrom=2024-01-01&dateto=2024-01-31&chosenProductPrice=2&order=desc
salesreport.php?do=approvedsales&datefrom=2024-01-01&dateto=2024-01-31
salesreport.php?do=consumption&productCatId1=5&storeId=1
```

---

## 🧮 Calculation Methods

### Cost Basis Selection (chosenProductPrice)
```php
// 8 different cost calculation methods available
switch ($chosenProductPrice) {
    case 0: // Use transaction cost (buyprice from bill)
        $costPrice = $sellbilldetail->buyprice;
        break;
    case 1: // Mean buy price
        $costPrice = $product->meanbuyprice;
        break;
    case 2: // Last buy price
        $costPrice = $product->lastbuyprice;
        break;
    case 3: // Last buy price with discount
        $costPrice = $product->lastbuyprice_withDiscount;
        break;
    case 4: // Mean buy price with discount
        $costPrice = $product->meanbuyprice_withDiscount;
        break;
    case 5: // General price calculation (complex formula)
        $costPrice = calculateGeneralPrice($product);
        break;
    case 6: // Last buy price with tax
        $costPrice = $product->lastbuyprice_withTax;
        break;
    case 7: // Mean buy price with tax
        $costPrice = $product->meanbuyprice_withTax;
        break;
    case 8: // Last buy price with discount and tax
        $costPrice = $product->lastbuyprice_withDiscountAndTax;
        break;
}
```

### Advanced Profit Calculation
```php
// Calculate profit using chosen cost method
$realCost = $netQuantity * $costPrice;
$netProfit = $netValue - $realCost;
$profitMargin = ($netValue > 0) ? ($netProfit / $netValue) * 100 : 0;

// Cost-to-sale ratio
$costRatio = ($netValue > 0) ? ($realCost / $netValue) * 100 : 0;
```

### Unit Conversion with Multiple Types
```php
// Convert transaction quantity to base unit
$quantity = $sellbilldetail->sellbilldetailquantity;
$unitFactor = $productunit->productnumber;
$baseQuantity = $quantity * $unitFactor;

// Aggregate across different units for same product
if (isset($productTotals[$productId])) {
    $productTotals[$productId]['quantity'] += $baseQuantity;
    $productTotals[$productId]['value'] += $totalPrice;
} else {
    $productTotals[$productId] = [
        'quantity' => $baseQuantity,
        'value' => $totalPrice,
        'cost' => $baseQuantity * $costPrice
    ];
}
```

---

## 🔒 Security & Permissions

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

### Store Access Control
- Multi-store access controlled by user permissions
- Store filtering respects user access rights
- Session-based store permission enforcement

### User Performance Access
- User performance reports may be restricted
- Branch-based filtering controlled by user permissions
- Administrative access required for cross-user analysis

### Input Validation and Security
```php
// Comprehensive input filtering
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$pricetype = filter_input(INPUT_POST, 'pricetype', FILTER_VALIDATE_INT);
$clientid = filter_input(INPUT_POST, 'clientid', FILTER_VALIDATE_INT);
$storeId = filter_input(INPUT_POST, 'storeId', FILTER_VALIDATE_INT);
```

### SQL Injection Prevention
- Uses RedBean ORM with prepared statements
- Parameter binding for all database queries
- Input sanitization through filter functions

---

## 📊 Performance Considerations

### Database Performance
1. **Complex Multi-Table Joins**: Reports involve 10+ tables
2. **Large Data Sets**: Can process thousands of transactions
3. **Recursive Category Queries**: Category hierarchy traversal
4. **Multiple Cost Price Calculations**: Additional queries for pricing

### Critical Indexes Required
```sql
-- Essential indexes for performance
CREATE INDEX idx_sellbill_date_conditions ON sellbill(sellbilldate, conditions);
CREATE INDEX idx_sellbill_client_date ON sellbill(sellbillclientid, sellbilldate);
CREATE INDEX idx_sellbilldetail_product ON sellbilldetail(sellbilldetailproductid);
CREATE INDEX idx_sellbilldetail_store ON sellbilldetail(storeid);
CREATE INDEX idx_product_category ON product(productcatid);
CREATE INDEX idx_productcat_parent ON productcat(productcatparent);
CREATE INDEX idx_client_type ON client(typeclientid);
```

### Memory Optimization
- Batch loading with custom array indexing
- Reduced database round trips
- Efficient data structure management
- Progressive result building

### Query Optimization Features
- Custom array indexing for efficient lookups
- Batch client name loading
- Optimized JOIN structures
- Reduced redundant queries

---

## 🐛 Common Issues & Troubleshooting

### 1. **Performance Issues with Large Data Sets**
**Issue**: Reports timeout or take very long to load  
**Cause**: Missing indexes or inefficient category filtering

**Debug Steps**:
```sql
-- Check for missing indexes
EXPLAIN SELECT * FROM sellbilldetail WHERE sellbilldetailproductid = 1;
EXPLAIN SELECT * FROM sellbill WHERE sellbilldate >= '2024-01-01';

-- Check category hierarchy depth
SELECT MAX(level) FROM (
  WITH RECURSIVE cat_hierarchy AS (
    SELECT productcatid, productcatparent, 0 as level
    FROM productcat WHERE productcatparent = 0
    UNION ALL
    SELECT p.productcatid, p.productcatparent, ch.level + 1
    FROM productcat p
    JOIN cat_hierarchy ch ON p.productcatparent = ch.productcatid
  )
  SELECT level FROM cat_hierarchy
) levels;
```

### 2. **Incorrect Profit Calculations**
**Issue**: Profit margins don't match expected values  
**Cause**: Wrong cost calculation method selected

**Debug**:
```php
// Add debugging to cost calculation
error_log("Product: {$productId}, Chosen Method: {$chosenProductPrice}, Cost: {$costPrice}");
error_log("Net Value: {$netValue}, Real Cost: {$realCost}, Profit: {$netProfit}");
```

### 3. **Category Filtering Not Working**
**Issue**: Subcategory products not appearing in reports  
**Cause**: Recursive category logic errors

**Fix**: Check category hierarchy integrity:
```sql
-- Find orphaned categories
SELECT pc1.productcatid, pc1.productcatname, pc1.productcatparent
FROM productcat pc1
LEFT JOIN productcat pc2 ON pc1.productcatparent = pc2.productcatid
WHERE pc1.productcatparent > 0 AND pc2.productcatid IS NULL;

-- Find circular references
WITH RECURSIVE cat_check AS (
  SELECT productcatid, productcatparent, CAST(productcatid AS CHAR(1000)) as path
  FROM productcat
  UNION ALL
  SELECT p.productcatid, p.productcatparent, CONCAT(cc.path, ',', p.productcatid)
  FROM productcat p
  JOIN cat_check cc ON p.productcatparent = cc.productcatid
  WHERE FIND_IN_SET(p.productcatid, cc.path) = 0
)
SELECT * FROM cat_check WHERE FIND_IN_SET(productcatid, path) > 1;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Multi-Criteria Filtering
```
1. Apply date range filter
2. Add client type filter
3. Add product category filter
4. Add store filter
5. Verify all filters work together correctly
6. Check that results match manual query
```

### Test Case 2: Cost Calculation Methods
```
1. Create product with known buy prices
2. Test each cost calculation method (0-8)
3. Verify profit calculations are accurate
4. Compare with manual calculations
5. Test with products having multiple unit types
```

### Test Case 3: Category Hierarchy Filtering
```
1. Set up nested categories (3+ levels deep)
2. Create products in various subcategories
3. Filter by parent category
4. Verify all child category products included
5. Test recursive logic with complex hierarchies
```

### Test Case 4: Performance with Large Data Sets
```
1. Generate large test dataset (10K+ transactions)
2. Apply complex filters
3. Measure query execution time
4. Verify memory usage stays reasonable
5. Test with maximum filter complexity
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellingReportByArea.md](sellingReportByArea.md) - Geographic sales analysis
- [sellreportpricetype.md](sellreportpricetype.md) - Price type analysis
- [salesNumbersReport.md](salesNumbersReport.md) - Movement analysis
- [reportfunctions.md](reportfunctions.md) - Shared utility functions

---

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