# Sales Report Category Store Controller Documentation

**File**: `/controllers/salesreportcatstore.php`  
**Purpose**: Generates sales reports grouped by product categories and stores with profit analysis  
**Last Updated**: December 21, 2024  
**Total Functions**: 4  
**Lines of Code**: ~1,281

---

## 📋 Overview

The Sales Report Category Store Controller is a specialized reporting module that provides comprehensive sales analysis by product categories and stores. It handles:
- Category-based sales reporting with hierarchical subcategory inclusion
- Store-based sales comparison reporting
- Profit calculations with multiple pricing methods
- Sales vs returns analysis with net calculations
- Detailed discount processing and VAT handling
- Multi-bill type processing (regular sales, returns, combined bills)
- Real-time cost calculation using various pricing strategies
- Session-based search parameter caching for new tab functionality

### Primary Functions
- [x] Generate category-based sales reports
- [x] Store-wise sales comparison
- [x] Net profit calculations with multiple cost methods
- [x] Hierarchical category tree processing
- [x] Discount and VAT calculations
- [x] Multi-pricing strategy support
- [x] Session caching for cross-tab functionality
- [x] Date range filtering with hour adjustments

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [buyBillController.php](buyBillController.md) - Purchase operations
- [returnsellbillController.php](#) - Sales returns
- [productController.php](#) - Product management
- [storeController.php](#) - Store management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilldate, sellbillstoreid, sellerid, userid |
| **sellbilldetail** | Sales line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid, storeid |
| **returnsellbill** | Return bills master | returnsellbillid, returnsellbillclientid, returnsellbilltotalbill, returnsellbilldate, returnsellbillstoreid |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity |
| **sellbillandrutern** | Combined sell & return | sellbillid, sellbillclientid, sellbilldate, sellbillstoreid, sellerid |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, selltype |

### Product Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master data | productId, productName, productCatId, overAllAveragePrice, productBuyPrice, isService |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |
| **productunit** | Product units | productunitid, productid, productnumber, buyprice, lastbuyprice, meanbuyprice |

### Configuration Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System settings | programsettingsid, vatValue, valueadded, reportsPlusHours |
| **store** | Store information | storeId, storeName |
| **client** | Customer data | clientid, clientname |
| **user** | System users | userid, employeename, userstoreid, usergroupid |

---

## 🔑 Key Functions

### 1. **getDataNew()** - Main Report Generation Function
**Location**: Line 370  
**Purpose**: Core function that processes all sales data and generates comprehensive reports

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

**Process Flow**:
1. Initialize productData class for result storage
2. Calculate VAT value for profit calculations
3. Process regular sales bills with R::getAll() queries
4. Process return bills and combined bills
5. Apply category/store filtering based on searchtype
6. Calculate real costs using chosen pricing method
7. Generate summary totals and category aggregations

**Key Variables**:
- `$vatValue` - VAT multiplier (1 + VAT percentage)
- `$chosenProductPrice` - Pricing method (0-7 different cost bases)
- `$searchtype` - Report grouping (0=category, 1=store)
- `$allDataArr` - Final result array of productData objects

---

### 2. **getAllSubCat()** - Category Hierarchy Processing
**Location**: Line 1229  
**Purpose**: Recursively processes product category hierarchies to include all subcategories

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

**Parameters**:
- `$catid` - Parent category ID
- `$mode` - Processing mode (1=all subcats, 2=leaf level only)

**Process Flow**:
1. Query subcategories of given parent
2. Recursively process each child category
3. Build comma-separated ID string for SQL IN clauses
4. Handle both full tree and leaf-only modes

---

### 3. **isCatChildToParent()** - Category Parent Checking
**Location**: Line 1267  
**Purpose**: Determines if a category is a child of a specified parent category

**Function Signature**:
```php
function isCatChildToParent($catId, $parent)
```

**Process Flow**:
1. Query parent ID of given category
2. Check if matches target parent
3. Recursively check grandparent relationships
4. Set global flag when relationship found

---

### 4. **Default Action** - Main Controller Logic
**Location**: Line 199  
**Purpose**: Handles URL routing, parameter processing, and report orchestration

**Process Flow**:
1. Load program settings and user permissions
2. Process POST parameters and session data
3. Handle "today's bills" quick filter
4. Build dynamic query strings for each bill type
5. Apply store, date, seller, and category filters
6. Call getDataNew() with constructed parameters
7. Assign results to Smarty template

---

## 🔄 Workflows

### Workflow 1: Category-Based Sales Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│             START: Select Category & Date Range            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Input Parameters                                │
│     - Category ID and search type                          │
│     - Date range (with hour adjustments)                   │
│     - Store and seller filters                             │
│     - Pricing method selection                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Expand Category Hierarchy                               │
│     - Call getAllSubCat() to get all child categories      │
│     - Build product list for category tree                 │
│     - Create SQL IN clause for product filtering           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Query All Bill Types                                    │
│     - Regular sales bills (sellbill + sellbilldetail)      │
│     - Return bills (returnsellbill + returnsellbilldetail) │
│     - Combined bills (sellbillandrutern + details)         │
│     - Apply filters: date, store, seller, category         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Transaction                                │
│     FOR EACH bill detail record:                            │
│       │                                                     │
│       ├─→ Load product and category information            │
│       │                                                     │
│       ├─→ Calculate quantities (handle product units)      │
│       │                                                     │
│       ├─→ Process discounts and VAT                        │
│       │                                                     │
│       ├─→ Calculate real cost using chosen method          │
│       │   ├─ Buy price from bill                           │
│       │   ├─ Last buy price                                │
│       │   ├─ Mean buy price                                │
│       │   ├─ Discounted prices                             │
│       │   └─ Tax-inclusive prices                          │
│       │                                                     │
│       └─→ Accumulate totals by category/store              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Generate Category Summations                            │
│     - Group results by product categories                   │
│     - Calculate net quantities (sold - returned)           │
│     - Calculate net values and profit margins              │
│     - Generate parent category rollups                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Output Report                                           │
│     - Assign data to Smarty template                       │
│     - Include summary totals                               │
│     - Display via salesreportcatstoreviews/show.html      │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Category/Store sales report based on searchtype |

### Required Parameters
- `storeId` - Store filter (-1 for all stores)
- `productCatId` - Category filter (-1 for all categories) 
- `searchtype` - Report grouping (0=category, 1=store)
- `datefrom` - Start date (YYYY-MM-DD)
- `dateto` - End date (YYYY-MM-DD)
- `sellerid` - Seller filter (-1 for all sellers)
- `chosenProductPrice` - Pricing method (0-7)

### Optional Parameters
- `getTodayBills` - Quick filter for today's sales (1=enabled)
- `search` - Search type indicator for date processing

---

## 🧮 Calculation Methods

### Profit Calculation by Pricing Method
```php
switch ($chosenProductPrice) {
    case 0: // Buy price from bill
        $myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
        break;
    case 1: // Mean buy price
        $myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
        break;
    case 2: // Last buy price  
        $myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
        break;
    case 3: // Last buy price with discount
        $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
        break;
    case 4: // Mean buy price with discount
        $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
        break;
    case 5: // General/average price
        $myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
        break;
    case 6: // Last buy price with tax
        $myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
        break;
    case 7: // Mean buy price with tax
        $myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
        break;
}
```

### Discount Processing
```php
// Get bill-level discount
$dicount = $value->sellbilldiscount;
if ($value->sellbilldiscounttype == 2) {
    // Percentage discount
    $dicount = ($value->sellbilldiscount / 100) * $value->parcode;
}

// Distribute discount proportionally to line item
if ($dicount != 0) {
    $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
    $theDiscount = round($theDiscount, 2);
}
```

### VAT Calculation
```php
// Calculate VAT multiplier for cost calculations
$vatValue = 1 + ($Programsetting->vatValue / 100);

// Apply to cost calculation
$realCost = $finalquantity * $buyPrice * $vatValue;
```

---

## 🔒 Security & Permissions

### User Store Access Control
```php
if ($user->userstoreid == 0) {
    // Admin user - can select any store
    $theStore = $storeId;
    $queryString1 .= 'and sellbill.sellbillstoreid = ' . $storeId . ' ';
} else if ($_SESSION['searchinonestore'] == 0) {
    // Multi-store user
    if ($_SESSION['storeids'] != 0) {
        $queryString1 .= 'and sellbill.sellbillstoreid in (' . $_SESSION['storeids'] . ') ';
    }
} else {
    // Single store user
    $theStore = $_SESSION['storeid'];
    $queryString1 .= 'and sellbill.sellbillstoreid = ' . $_SESSION['storeid'] . ' ';
}
```

### Authentication Requirements
- User must be logged in (authentication.php included)
- Store access controlled by user permissions
- Seller filtering respects user hierarchy

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Critical Indexes**:
   - `sellbill(sellbilldate, sellbillstoreid, sellerid)`
   - `sellbilldetail(sellbillid, sellbilldetailproductid)`
   - `productcat(productCatParent)` for hierarchy queries
   - `product(productCatId)` for category filtering

2. **Query Optimization**:
   - Uses R::getAll() for efficient data retrieval
   - Implements proper JOIN relationships
   - Date filtering with time adjustment logic
   - IN clauses for category hierarchy filtering

3. **Memory Considerations**:
   - Large date ranges may process thousands of records
   - Category hierarchy recursion depth should be monitored
   - Product arrays built in memory for fast lookups

### Known Performance Issues
```sql
-- Category hierarchy expansion can be expensive for deep trees
-- Solution: Consider materialized path or nested set model

-- Multiple bill type queries could be combined with UNION
-- Current separate queries allow for better debugging but impact performance
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Category Data**
**Issue**: Products don't appear in category reports  
**Cause**: Product category assignments missing or hierarchy broken

**Debug**:
```sql
-- Check products without categories
SELECT productId, productName FROM product WHERE productCatId IS NULL OR productCatId = 0;

-- Verify category hierarchy
SELECT productCatId, productCatName, productCatParent FROM productcat WHERE conditions = 0;
```

### 2. **Incorrect Profit Calculations**
**Issue**: Profit margins don't match expectations  
**Cause**: VAT settings or pricing method selection issues

**Debug**:
```php
// Check VAT configuration
$Programsetting = $ProgramsettingDAO->load(1);
echo "VAT Value: " . $Programsetting->vatValue . "%<br>";
echo "VAT Calculation: " . (1 + ($Programsetting->vatValue / 100));

// Verify pricing method data
echo "Chosen Price Method: " . $chosenProductPrice;
```

### 3. **Store Filtering Not Working**
**Issue**: All stores showing when specific store selected  
**Cause**: User permission conflicts or session data issues

**Fix**:
```php
// Debug user store permissions
echo "User Store ID: " . $user->userstoreid . "<br>";
echo "Search In One Store: " . $_SESSION['searchinonestore'] . "<br>";
echo "User Store IDs: " . $_SESSION['storeids'];
```

---

## 🧪 Testing Scenarios

### Test Case 1: Category Report Accuracy
```
1. Create test products in specific category
2. Generate test sales bills with known quantities/prices
3. Run category report for test period
4. Verify quantities and amounts match source bills
5. Check profit calculations against manual calculations
```

### Test Case 2: Store Comparison Report
```
1. Create sales in multiple stores
2. Use searchtype=1 for store-based grouping
3. Verify each store's totals are isolated correctly
4. Check that combined totals match overall sales
```

### Test Case 3: Pricing Method Validation
```
1. Set up product with different cost prices
2. Create sales bill using known pricing method
3. Run report with each pricing method (0-7)
4. Verify cost calculations use correct price source
5. Confirm profit margins calculated properly
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations
- [buyBillController.md](buyBillController.md) - Purchase operations
- [Database Schema Documentation](#) - Table relationships

---

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