# Branch Profitability Report Controller Documentation

**File**: `/controllers/branchProfitabilityReport.php`  
**Purpose**: Generates comprehensive branch profitability reports with revenue, expenses, and net profit analysis  
**Last Updated**: December 20, 2024  
**Total Functions**: 4  
**Lines of Code**: ~307

---

## 📋 Overview

The Branch Profitability Report Controller is a comprehensive financial reporting module that provides detailed profit and loss analysis for business branches. It handles:
- Multi-dimensional filtering (branch, store, safe/cash register)
- Revenue analysis from sales and returns
- Expense tracking including salaries, raw materials, and operational costs
- Store movement and inventory adjustments
- Cash transfer tracking between safes
- Net profitability calculations
- Date range analysis
- Multi-location business intelligence

### Primary Functions
- [x] Generate comprehensive P&L reports
- [x] Track revenue from sales operations
- [x] Monitor expenses across categories
- [x] Calculate store movement costs
- [x] Track cash transfers between safes
- [x] Employee salary calculations
- [x] Inventory adjustment reporting
- [x] Net profit margin analysis

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales revenue tracking
- [buyBillController.php](buyBillController.md) - Purchase cost analysis
- [expensesController.php](#) - Expense management
- [salaryreportController.php](#) - Payroll reporting

---

## 🗄️ Database Tables

### Primary Revenue Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbilldetail** | Sales line items | sellbilldetailid, sellbillid, sellbilldetailquantity, sellbilldetailprice, storeid |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailquantity, returnsellbilldetailprice, storeid |
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbilldate, conditions |
| **returnsellbill** | Return bills master | returnsellbillid, returnsellbillclientid, returnsellbilldate, conditions |

### Expense & Cost Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **expenses** | Operating expenses | expensesid, expensesValue, expensesdate, saveid, conditions |
| **salaryreport** | Employee salaries | salaryreportid, employeeid, salaryreportnet, salaryreportdate, conditions |
| **restaurantrawdestruction** | Raw material waste | id, quantity, lastbuyprice, sysdate, storeid, conditions |
| **storereport** | Inventory adjustments | storereportid, productid, productquantity, storereporttype, storereportdate, storeid |

### Movement & Transfer Tables  
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storemovement** | Store-to-store transfers | id, productid, transferproductamount, storeidfrom, storeidto, transferproductdate, conditions |
| **transfermoney** | Cash safe transfers | transfermoneyid, transfermoneyvalue, saveidfrom, saveidto, transfermoneydate, conditions |
| **storedetail** | Current inventory | storedetailid, productid, productquantity, storeid, conditions |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **branch** | Business branches | branchId, branchName |
| **store** | Storage locations | storeId, storeName, conditions |
| **save** | Cash registers/safes | saveid, savename, conditions |
| **employee** | Staff information | employeeId, branchid, conditions |
| **product** | Product master data | productId, productBuyPrice, productSellAllPrice, lastbuyprice |

---

## 🔑 Key Functions

### 1. **Default Action** - Report Interface
**Location**: Line 9  
**Purpose**: Display the main profitability report interface

**Process Flow**:
1. Display header template
2. Load report interface (`branchProfitabilityReportView/show.html`)
3. Display footer template

---

### 2. **select2branchs()** - Branch Search
**Location**: Line 13  
**Purpose**: AJAX endpoint for branch selection autocomplete

**Function Signature**:
```php
// Triggered when: do=select2branchs
```

**Process Flow**:
1. Get search term from POST data
2. Query branches with LIKE match on branch name
3. Format results for Select2 dropdown
4. Return JSON response

**SQL Query**:
```sql
SELECT branchId as id, branchName as name
FROM branch
WHERE branchName LIKE '%{searchTerm}%' 
LIMIT 50
```

---

### 3. **select2stores()** - Store Search  
**Location**: Line 27  
**Purpose**: AJAX endpoint for store/warehouse selection

**Process Flow**:
1. Get search term from POST data
2. Query active stores with name matching
3. Format results for Select2 dropdown
4. Return JSON response

**SQL Query**:
```sql
SELECT storeId as id, storeName as name
FROM store
WHERE conditions = 0 AND storeName LIKE '%{searchTerm}%' 
LIMIT 50
```

---

### 4. **select2saves()** - Cash Safe Search
**Location**: Line 40  
**Purpose**: AJAX endpoint for cash register/safe selection

**Process Flow**:
1. Get search term from POST data  
2. Query active cash safes with name matching
3. Format results for Select2 dropdown
4. Return JSON response

**SQL Query**:
```sql
SELECT saveid as id, savename as name
FROM save
WHERE conditions = 0 AND savename LIKE '%{searchTerm}%'
LIMIT 50
```

---

### 5. **show()** - Main Profitability Report
**Location**: Line 54  
**Purpose**: Generate comprehensive branch profitability analysis

**Function Signature**:
```php
// Triggered when: do=show
```

**Process Flow**:
1. **Parse Input Parameters**:
   - Date range (start_date, end_date)
   - Branch filter (branch_id)
   - Store filter (store_id) 
   - Safe filter (save_id)

2. **Build Dynamic Query Conditions**:
   - Add date range filters to all queries
   - Add location filters based on selections
   - Generate descriptive message

3. **Calculate Revenue Components**:
   - Sales revenue from sellbilldetail
   - Return deductions from returnsellbilldetail
   - Net sales revenue

4. **Calculate Cost Components**:
   - Cost of goods sold (at buy prices)
   - Raw material destruction costs
   - Operating expenses
   - Employee salaries

5. **Calculate Movement Adjustments**:
   - Store-to-store transfer costs
   - Cash transfer adjustments
   - Inventory adjustment values

6. **Generate Net Profit**:
   - Formula: (Sales - Returns) - COGS - Destruction - Expenses - Salaries + Adjustments

**Key Calculations**:

**Revenue Calculation**:
```php
$allSellBilldetails = R::getCell('
    SELECT SUM(sellbilldetailquantity * sellbilldetailprice) 
    FROM sellbilldetail 
    JOIN sellbill ON sellbill.sellbillid = sellbilldetail.sellbillid 
    WHERE conditions = 0 ' . $sellbillqs);

$returnsellbillidetails = R::getCell('
    SELECT SUM(returnsellbilldetailquantity * returnsellbilldetailprice) 
    FROM returnsellbilldetail
    JOIN returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid 
    WHERE returnsellbill.conditions = 0 ' . $retsellbillqs);
```

**Cost of Goods Sold**:
```php
$allSellBilldetailsBuyPrice = R::getCell('
    SELECT SUM(sellbilldetailquantity * product.lastbuyprice) 
    FROM sellbilldetail 
    JOIN sellbill ON sellbill.sellbillid = sellbilldetail.sellbillid
    JOIN product ON product.productId = sellbilldetail.sellbilldetailproductid
    WHERE sellbill.conditions = 0 ' . $sellbillqs);
```

**Store Movement Cost Impact**:
```php
$storemovementfromBuyPrice = R::getCell('
    SELECT SUM(storemovement.transferproductamount * product.productBuyPrice) 
    FROM storemovement
    JOIN product ON product.productId = storemovement.productid  
    WHERE storemovement.conditions = 0 AND storemovement.storeidfrom = "' . $store_id . '"' . $storemovementq);

$storemovementtoBuyPrice = R::getCell('
    SELECT SUM(storemovement.transferproductamount * product.productBuyPrice) 
    FROM storemovement
    JOIN product ON product.productId = storemovement.productid  
    WHERE storemovement.conditions = 0 AND storemovement.storeidto = "' . $store_id . '"' . $storemovementq);

$storemovementfrom = $storemovementtoBuyPrice - $storemovementfromBuyPrice;
```

**Net Profit Formula**:
```php
$netprofit = ($allSellBilldetails - $returnsellbillidetails) 
           - $allSellBilldetailsBuyPrice 
           - $restaurantrawdestructionValue 
           - $expensesValue 
           - $allsalarysfinal 
           + $storereport;
```

---

## 🔄 Workflows

### Workflow 1: Branch Profitability Analysis
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Analysis Parameters              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Input Filters                                     │
│     - Date range (start_date, end_date)                     │
│     - Branch selection (branch_id)                          │
│     - Store/warehouse (store_id)                            │
│     - Cash safe (save_id)                                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Dynamic Query Conditions                         │
│     - Add date filters to all table queries                │
│     - Add branch employee filter                            │ 
│     - Add store location filters                            │
│     - Add safe/cash register filters                        │
│     - Generate descriptive report message                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Calculate Revenue Components                            │
│     ├─→ Sales Revenue                                       │
│     │   └─ SUM(quantity × price) from sellbilldetail       │
│     │                                                       │
│     ├─→ Return Deductions                                   │
│     │   └─ SUM(quantity × price) from returnsellbilldetail │
│     │                                                       │
│     └─→ Net Sales = Sales - Returns                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Calculate Cost Components                               │
│     ├─→ Cost of Goods Sold                                  │
│     │   └─ SUM(sold_qty × product.lastbuyprice)            │
│     │                                                       │
│     ├─→ Raw Material Destruction                            │
│     │   └─ SUM(quantity × lastbuyprice) from destruction   │
│     │                                                       │
│     ├─→ Operating Expenses                                  │
│     │   └─ SUM(expensesValue) from expenses table          │
│     │                                                       │
│     └─→ Employee Salaries                                   │
│         └─ SUM(salaryreportnet) from salaryreport          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Movement Adjustments                          │
│     ├─→ Store Movement Impact                               │
│     │   ├─ Products moved FROM store (cost reduction)      │
│     │   └─ Products moved TO store (cost addition)         │
│     │                                                       │
│     ├─→ Cash Transfer Adjustments                           │
│     │   ├─ Money transferred FROM safe                     │
│     │   └─ Money transferred TO safe                       │
│     │                                                       │
│     └─→ Inventory Adjustments                               │
│         ├─ Positive adjustments (storereporttype = 0)      │
│         └─ Negative adjustments (storereporttype = 1)      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Final Profitability Report                    │
│     - Calculate Net Profit                                  │
│     - Formula: Revenue - Costs + Adjustments               │
│     - Assign all values to template                         │
│     - Display comprehensive report                          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Main report interface |
| `do=select2branchs` | `select2branchs()` | Branch autocomplete |
| `do=select2stores` | `select2stores()` | Store autocomplete |
| `do=select2saves` | `select2saves()` | Safe autocomplete |
| `do=show` | `show()` | Generate profitability report |

### Report Parameters (POST)
**Profitability Report** (`do=show`):
- `start_date` - Report start date (YYYY-MM-DD)
- `end_date` - Report end date (YYYY-MM-DD)  
- `branch_id` - Branch filter (optional)
- `store_id` - Store/warehouse filter (optional)
- `save_id` - Cash safe filter (optional)

---

## 🧮 Calculation Methods

### Revenue Calculation
```php
// Gross Sales Revenue
$allSellBilldetails = R::getCell('
    SELECT SUM(sellbilldetailquantity * sellbilldetailprice) 
    FROM sellbilldetail 
    JOIN sellbill ON sellbill.sellbillid = sellbilldetail.sellbillid 
    WHERE conditions = 0 ' . $sellbillqs);

// Return Deductions  
$returnsellbillidetails = R::getCell('
    SELECT SUM(returnsellbilldetailquantity * returnsellbilldetailprice) 
    FROM returnsellbilldetail
    JOIN returnsellbill ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid 
    WHERE returnsellbill.conditions = 0 ' . $retsellbillqs);

// Net Sales = Gross Sales - Returns
$netSales = $allSellBilldetails - $returnsellbillidetails;
```

### Cost of Goods Sold Calculation
```php
$allSellBilldetailsBuyPrice = R::getCell('
    SELECT SUM(sellbilldetailquantity * product.lastbuyprice) 
    FROM sellbilldetail 
    JOIN sellbill ON sellbill.sellbillid = sellbilldetail.sellbillid
    JOIN product ON product.productId = sellbilldetail.sellbilldetailproductid
    WHERE sellbill.conditions = 0 ' . $sellbillqs);
```

### Store Movement Cost Impact
```php
// Cost of products moved OUT of store (reduces inventory value)
$storemovementfromBuyPrice = R::getCell('
    SELECT SUM(storemovement.transferproductamount * product.productBuyPrice) 
    FROM storemovement
    JOIN product ON product.productId = storemovement.productid  
    WHERE storemovement.conditions = 0 AND storemovement.storeidfrom = "' . $store_id . '"');

// Cost of products moved INTO store (increases inventory value)  
$storemovementtoBuyPrice = R::getCell('
    SELECT SUM(storemovement.transferproductamount * product.productBuyPrice) 
    FROM storemovement
    JOIN product ON product.productId = storemovement.productid  
    WHERE storemovement.conditions = 0 AND storemovement.storeidto = "' . $store_id . '"');

// Net movement impact (positive = net inflow)
$storemovementfrom = $storemovementtoBuyPrice - $storemovementfromBuyPrice;
```

### Cash Transfer Impact
```php
$transfermoney = R::getCell('
    SELECT SUM(transfermoneyvalue) FROM transfermoney
    WHERE conditions = 0 ' . $transfermoneyq . ' ' . $transfermoneyto) 
    - R::getCell('SELECT SUM(transfermoneyvalue) FROM transfermoney
    WHERE conditions = 0 ' . $transfermoneyq . ' ' . $transfermoneyfrom);
```

### Final Net Profit
```php
$netprofit = ($allSellBilldetails - $returnsellbillidetails)  // Net Sales
           - $allSellBilldetailsBuyPrice                      // - Cost of Goods Sold
           - $restaurantrawdestructionValue                   // - Raw Material Waste
           - $expensesValue                                   // - Operating Expenses  
           - $allsalarysfinal                                 // - Employee Salaries
           + $storereport;                                    // + Inventory Adjustments
```

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `sellbilldetail(storeid, sellbillid)`
   - `sellbill(sellbilldate, conditions)`
   - `returnsellbilldetail(storeid, returnsellbillid)`
   - `storemovement(storeidfrom, storeidto, transferproductdate)`
   - `expenses(saveid, expensesdate, conditions)`
   - `salaryreport(employeeid, salaryreportdate, conditions)`

2. **Query Optimization**:
   - Multiple SUM aggregations across large tables
   - Date filtering on all major queries
   - JOINs across product pricing tables

### Known Performance Issues
- **Multiple Heavy Aggregations**: Each cost component requires separate SUM query
- **Large Date Ranges**: Can result in full table scans without proper indexing
- **Complex Store Movement Calculations**: Requires multiple queries per store

---

## 🔒 Security & Permissions

### Access Control
```php
include_once("../public/authentication.php");
```

### Input Sanitization  
```php
$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');
$branch_id = filter_input(INPUT_POST, 'branch_id');
$store_id = filter_input(INPUT_POST, 'store_id');
$save_id = filter_input(INPUT_POST, 'save_id');
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Profit Calculations**
**Issue**: Net profit doesn't match expected values  
**Cause**: Missing cost components or wrong price calculations

**Debug**:
```sql
-- Check individual components
SELECT SUM(sellbilldetailquantity * sellbilldetailprice) as sales FROM sellbilldetail;
SELECT SUM(sellbilldetailquantity * lastbuyprice) as cogs FROM sellbilldetail JOIN product ON...;
```

### 2. **Store Movement Miscalculations**
**Issue**: Store transfer costs are incorrect  
**Cause**: Wrong direction of transfer calculation

**Check**:
```sql
SELECT storeidfrom, storeidto, transferproductamount, productBuyPrice 
FROM storemovement JOIN product ON... 
WHERE storeidfrom = {store_id} OR storeidto = {store_id};
```

### 3. **Date Range Issues**
**Issue**: Report shows no data for valid date ranges  
**Cause**: Date format or timezone problems

**Fix**: Ensure consistent date format across all queries

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide  
- [sellbillController.md](sellbillController.md) - Sales operations
- [expensesController.php](#) - Expense management
- [salaryreportController.php](#) - Payroll reporting

---

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