# Store Profit Controller Documentation

**File**: `/controllers/storeProfiteController.php`  
**Purpose**: Comprehensive store profitability analysis with multi-pricing method support and detailed cost calculations  
**Last Updated**: December 21, 2024  
**Total Functions**: 2  
**Lines of Code**: ~600

---

## 📋 Overview

The Store Profit Controller provides detailed profitability analysis across stores with comprehensive cost calculation methods. It handles:
- Multi-store profit comparison and analysis
- Flexible pricing method selection for cost calculations
- Sales, returns, and combined bill profit analysis
- Product unit conversion for accurate calculations
- Date range filtering with detailed metrics
- User permission-based store access control
- Real-time profit margin calculations
- Integration with multiple bill types and discount systems

### Primary Functions
- [x] Multi-store profit analysis and comparison
- [x] Flexible cost calculation methods (8 different pricing strategies)
- [x] Sales vs returns profit impact analysis
- [x] Product unit conversion handling
- [x] Date range filtering with detailed breakdowns
- [x] User permission-based store access
- [x] Real-time cost and profit calculations
- [x] Discount impact on profitability

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

---

## 🗄️ Database Tables

### Primary Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills master | sellbillid, sellbillstoreid, sellbillsysdate, sellbilltotalbill, sellbillaftertotalbill, deletedsellid |
| **sellbilldetail** | Sales line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid |
| **returnsellbill** | Return bills master | returnsellbillid, returnsellbillstoreid, returnsellbillsysdate, returnsellbilltotalbill, returnsellbillaftertotalbill |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity, productunitid |
| **sellbillandrutern** | Combined bills | sellbillid, sellbillstoreid, sellbillsysdate, sellbilltotalbill, sellbillaftertotalbill |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, productunitid |

### Product & Pricing Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **productunit** | Product units with pricing | productunitid, productid, productnumber, overAllAveragePrice, buyprice, lastbuyprice, meanbuyprice |
| **product** | Product master data | productId, productName, productBuyPrice |

### Store & User Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **store** | Store information | storeId, storeName |
| **user** | System users | userid, username, usergroupid |
| **usergroup** | User groups | usergroupid, groupname |

### System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System settings | programsettingsid, settingkey, settingvalue |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **all Action** - Main Profit Analysis
**Location**: Line 184  
**Purpose**: Comprehensive profit analysis across stores with flexible cost calculation

**Process Flow**:
1. Load program settings and user permissions
2. Process search parameters (store, date range, pricing method)
3. Build dynamic query strings with store access control
4. Query sales bills, return bills, and combined bills
5. Calculate profits using selected pricing method
6. Apply discount calculations and generate totals
7. Display results via storeProfitView template

**Key Variables**:
- `$storeId` - Store filter selection
- `$dateFrom` / `$dateTo` - Date range for analysis
- `$buyPriceType` - Pricing method selection
- `$totalOfTotal` - Net profit across all stores

---

### 2. **loadProductUnitWithProductAndUnit()** - Product Unit Data Loading
**Location**: Line 589  
**Purpose**: Loads detailed product unit information with pricing data

**Function Signature**:
```php
function loadProductUnitWithProductAndUnit($productId, $unitId)
```

**Returns**: Complete product unit object with all pricing fields and conversion factors

---

## 🔄 Workflows

### Workflow 1: Store Profitability Analysis
```
┌─────────────────────────────────────────────────────────────┐
│            START: Select Store & Analysis Period           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Analysis Parameters                             │
│     - Store selection (specific or all stores)             │
│     - Date range selection                                 │
│     - Cost calculation method selection                    │
│     - User permission validation                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Store Access Control                             │
│     - Check user store permissions                         │
│     - Apply searchinonestore restrictions                  │
│     - Build store ID lists for filtering                   │
│     - Default to today if no date specified                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Query Sales Bills                                       │
│     - Load sellbill records with store/date filters        │
│     - Apply user permission restrictions                   │
│     - Exclude deleted bills (deletedsellid = 0)            │
│     - Group by store for analysis                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Sales Bill                                │
│     FOR EACH sales bill:                                   │
│       │                                                     │
│       ├─→ Load all bill detail records                     │
│       │                                                     │
│       ├─→ For each line item:                              │
│       │   ├─ Load product unit information                 │
│       │   ├─ Calculate final quantities (qty × unit)       │
│       │   ├─ Select cost price based on method:            │
│       │   │   ├─ first: buyprice from detail               │
│       │   │   ├─ last: lastbuyprice                        │
│       │   │   ├─ mean: meanbuyprice                        │
│       │   │   ├─ last_discount: lastbuyprice_withDiscount  │
│       │   │   ├─ mean_discount: meanbuyprice_withDiscount  │
│       │   │   ├─ generalPrice: overAllAveragePrice         │
│       │   │   ├─ tax: lastbuyprice_withTax                 │
│       │   │   └─ mean_tax: meanbuyprice_withTax            │
│       │   │                                                 │
│       │   ├─ Calculate line cost (qty × unit × price)      │
│       │   └─ Sum line sale value                           │
│       │                                                     │
│       ├─→ Apply bill-level discount calculations           │
│       │   ├─ Handle fixed amount discounts                 │
│       │   └─ Handle percentage discounts                   │
│       │                                                     │
│       └─→ Calculate bill profit (sales - costs - discount) │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Query Return Bills                                      │
│     - Load returnsellbill records                          │
│     - Process return bill details                          │
│     - Calculate return costs and values                    │
│     - Apply return-specific discount logic                 │
│     - Subtract from profit calculations                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Query Combined Bills                                    │
│     - Load sellbillandrutern records                       │
│     - Process sellandruternbilldetail records              │
│     - Handle mixed sale/return transactions                │
│     - Apply appropriate cost calculations                  │
│     - Include in overall profit analysis                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Calculate Final Profit Metrics                         │
│     - Net profit per bill = (after total - costs)         │
│     - Aggregate profit by store                            │
│     - Calculate profit margins and percentages             │
│     - Generate grand total across all stores               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  8. Generate Profit Report                                 │
│     - Assign bill-level data to template                   │
│     - Include store-wise summaries                         │
│     - Display profit margins and totals                    │
│     - Show cost calculation method used                    │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=all` | Main analysis | Comprehensive store profit analysis |
| `do=sucess` | Success page | Display success message |
| `do=error` | Error page | Display error message |

### Required Parameters for Analysis
- `store` - Store ID filter (-1 for all stores)
- `dateFrom` - Analysis start date (YYYY-MM-DD)
- `dateTo` - Analysis end date (YYYY-MM-DD)
- `buyPriceType` - Cost calculation method

### Cost Calculation Methods
- `first` - Buy price from bill detail record
- `last` - Last buy price from product unit
- `mean` - Mean/average buy price
- `last_discount` - Last buy price with discount applied
- `mean_discount` - Mean buy price with discount applied
- `generalPrice` - Overall average price from product
- `tax` - Last buy price with tax included
- `mean_tax` - Mean buy price with tax included

---

## 🧮 Calculation Methods

### Cost Calculation by Method
```php
switch ($buyPriceType) {
    case "first":
        $buyprice = (float) $mySellBillDetail->buyprice;
        break;
    case "last":
        $buyprice = (float) $mySellBillDetail->lastbuyprice;
        break;
    case "mean":
        $buyprice = (float) $mySellBillDetail->meanbuyprice;
        break;
    case "last_discount":
        $buyprice = (float) $mySellBillDetail->lastbuyprice_withDiscount;
        break;
    case "mean_discount":
        $buyprice = (float) $mySellBillDetail->meanbuyprice_withDiscount;
        break;
    case "generalPrice":
        $buyprice = (float) $overAllAveragePrice;
        break;
    case "tax":
        $buyprice = (float) $mySellBillDetail->lastbuyprice_withTax;
        break;
    case "mean_tax":
        $buyprice = (float) $mySellBillDetail->meanbuyprice_withTax;
        break;
}

// Calculate total cost for line item
$myBuyPrice = $buyprice * ($mySellBillDetail->sellbilldetailquantity * $myproductNumber);
```

### Discount Processing
```php
// Get bill discount settings
$sellbilldiscounttype = $mysellBill->sellbilldiscounttype;
$sellbilldiscount = $mysellBill->sellbilldiscount;

if ($sellbilldiscounttype == 1) {
    // Fixed amount discount
    $sellBillTotal = $mysellBill->sellbilldiscount;
} elseif ($sellbilldiscounttype == 2) {
    // Percentage discount
    $sellBillTotal = ($mysellBill->sellbilldiscount / 100) * $mysellBill->sellbilltotalbill;
}
```

### Final Profit Calculation
```php
// Calculate bill profit
$thisProfits += ($mySellBillDetail->sellbilldetailtotalprice) - $myBuyPrice;
$totalBuy += $myBuyPrice;
$totalSell += $mySellBillDetail->sellbilldetailtotalprice;

// Apply discount to final profit
$mysellBill->sellBillTotalx = $sellBillTotal;
$mysellBill->totalBuyx = $totalBuy;
$mysellBill->totalSellx = $totalSell;

// Net profit for bill
$totalOfTotal += ($mysellBill->sellbillaftertotalbill - $mysellBill->totalBuyx);
```

### Product Unit Conversion
```php
// Load product unit data
$productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);
$overAllAveragePrice = $productunitData->overAllAveragePrice;
$myproductNumber = $productunitData->productnumber;

// Convert to base units for costing
$finalQuantity = $sellbilldetailquantity * $myproductNumber;
$lineCost = $buyprice * $finalQuantity;
```

---

## 🔒 Security & Permissions

### Store Access Control
```php
// Multi-store vs single-store access
if ($_SESSION['searchinonestore'] == 0) {
    // Multi-store user
    if ($_SESSION['storeids'] != 0) {
        $queryString .= ' sellbill.sellbillstoreid in (' . $_SESSION['storeids'] . ') AND';
    }
} else {
    // Single-store user
    $queryString .= ' sellbill.sellbillstoreid = ' . $_SESSION['storeid'] . ' AND';
}

// Admin override for store selection
if (isset($storeId) && $storeId != -1) {
    $queryString .= ' sellbill.sellbillstoreid = ' . $storeId . ' AND';
}
```

### User Group Permissions
```php
// Load user group data for permissions
$myUserGroupRecord = new UsergroupMySqlDAO();
$loadData = $myUserGroupRecord->load($_SESSION['usergroupid']);
$smarty->assign("loadData", $loadData);
```

### Query Security
```php
// Exclude deleted records
$queryString .= ' and sellbill.deletedsellid = 0 ';

// Date filtering with proper format
if (isset($dateFrom) && !empty($dateFrom) && isset($dateTo) && !empty($dateTo)) {
    $queryString .= ' DATE( sellbill.sellbillsysdate )  >= "' . $dateFrom . '" AND DATE( sellbill.sellbillsysdate ) <= "' . $dateTo . '"';
}
```

---

## 📊 Performance Considerations

### Query Optimization
1. **Efficient Joins**:
   - Uses getSellBillsUsingQueryString() for optimized queries
   - Proper JOIN relationships between bills and details
   - Date indexing for performance

2. **Memory Management**:
   - Processes bills in sequence rather than loading all at once
   - Efficient aggregation of totals
   - Minimal template variable usage

3. **Database Indexes Required**:
   - `sellbill(sellbillsysdate, sellbillstoreid, deletedsellid)`
   - `sellbilldetail(sellbillid, sellbilldetailproductid)`
   - `productunit(productid, productunitid)`

### Performance Monitoring
```php
// Monitor query performance for large date ranges
$startTime = microtime(true);
$sellBill = $sellBillEx->getSellBillsUsingQueryString($queryString);
$queryTime = microtime(true) - $startTime;

// Alert if queries are slow
if ($queryTime > 2.0) {
    error_log("Slow profit analysis query: " . $queryTime . "s");
}
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Profit Calculations**
**Issue**: Profit margins don't match manual calculations  
**Cause**: Wrong pricing method selection or missing cost data

**Debug**:
```php
// Debug cost calculation
echo "Selected Method: " . $buyPriceType . "<br>";
echo "Unit Data: " . print_r($productunitData, true) . "<br>";
echo "Buy Price: " . $buyprice . "<br>";
echo "Quantity: " . $sellbilldetailquantity . "<br>";
echo "Product Number: " . $myproductNumber . "<br>";
echo "Final Cost: " . $myBuyPrice . "<br>";
```

### 2. **Store Access Issues**
**Issue**: User can't see expected stores or data  
**Cause**: Session permissions or store assignment problems

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

### 3. **Missing Bill Data**
**Issue**: Known bills not appearing in profit analysis  
**Cause**: Date range issues or deleted bill filtering

**Fix**:
```php
// Check bill status
SELECT sellbillid, sellbilldate, deletedsellid, conditions 
FROM sellbill 
WHERE sellbillid = [MISSING_BILL_ID];

// Verify date filtering
echo "Date From: " . $dateFrom . "<br>";
echo "Date To: " . $dateTo . "<br>";
echo "Query String: " . $queryString . "<br>";
```

---

## 🧪 Testing Scenarios

### Test Case 1: Multi-Store Profit Comparison
```
1. Create sales in different stores using same products
2. Set different cost prices for comparison
3. Run profit analysis for all stores
4. Verify store-wise profit calculations
5. Check grand total accuracy across stores
```

### Test Case 2: Pricing Method Accuracy
```
1. Set up products with different cost price types
2. Create sales using various pricing methods
3. Run profit analysis with each method (first, last, mean, etc.)
4. Verify cost calculations use correct price source
5. Compare profit margins across methods
```

### Test Case 3: Discount Impact Analysis
```
1. Create bills with various discount types:
   - Fixed amount discounts
   - Percentage discounts
   - No discounts
2. Run profit analysis
3. Verify discount impact on profit calculations
4. Check that discounts reduce profitability correctly
```

### Test Case 4: Combined Bill Type Analysis
```
1. Create regular sales, returns, and combined bills
2. Use same products across different bill types
3. Run comprehensive profit analysis
4. Verify returns reduce profits appropriately
5. Check combined bill profit calculations
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations
- [returnsellbillController.md](#) - Return bill management
- [storeController.md](#) - Store management
- [Database Schema Documentation](#) - Table relationships

---

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