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

• ✅ Generate comprehensive P&L reports
• ✅ Track revenue from sales operations
• ✅ Monitor expenses across categories
• ✅ Calculate store movement costs
• ✅ Track cash transfers between safes
• ✅ Employee salary calculations
• ✅ Inventory adjustment reporting
• ✅ Net profit margin analysis

Related Controllers

• • sellbillController.php - Sales revenue tracking
• • buyBillController.php - Purchase cost analysis
• • expensesController.php - Expense management
• • salaryreportController.php - Payroll reporting

---

🗄️ Database Tables

Primary Revenue Tables

🔑 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:

// 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:

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:

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:

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:

// 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:

$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:

$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:

$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:

$netprofit = ($allSellBilldetails - $returnsellbillidetails) 
           - $allSellBilldetailsBuyPrice 
           - $restaurantrawdestructionValue 
           - $expensesValue 
           - $allsalarysfinal 
           + $storereport;

---

🔄 Workflows

Workflow 1: Branch Profitability Analysis

---

🌐 URL Routes & Actions

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

// 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

$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

// 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

$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

$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

include_once("../public/authentication.php");

Input Sanitization

$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:

-- 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:

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 - PHP 8.2 migration guide
• • 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