Balance Report Controller Documentation

File: /controllers/balancereportController.php

Purpose: Generates comprehensive balance sheet reports and financial position analysis

Last Updated: December 19, 2024

Total Functions: 22

Lines of Code: 818

---

📋 Overview

The Balance Report Controller provides comprehensive financial reporting and balance sheet analysis for the ERP system. It handles:

• Balance sheet generation with assets and liabilities
• Financial position reporting by date ranges
• Profit and loss calculations
• Asset valuation based on different inventory methods
• Client and supplier debt analysis
• Cash flow tracking (saves, bank accounts)
• Capital and withdrawal tracking
• Check value calculations for both clients and suppliers
• Comprehensive financial analysis with multiple evaluation methods

Primary Functions

✅ Generate balance sheet reports
✅ Calculate profit and loss statements
✅ Track asset valuations
✅ Monitor client/supplier debts
✅ Analyze cash flow positions
✅ Track capital and withdrawals
✅ Calculate check values
✅ Support multiple inventory valuation methods
✅ Date range reporting
✅ Financial position analysis

Related Controllers

• sellbillController.php - Sales data for revenue calculations
• buyBillController.php - Purchase data for cost calculations
• FaidaController.php - Profit reports
• FinancialPositionController.php - Financial position
• storereportController.php - Inventory valuation
• clientController.php - Customer debt analysis
• supplierController.php - Supplier debt analysis
• expenseController.php - Expense tracking

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
`sellbill`	Sales revenue	sellbillid, sellbillaftertotalbill, sellbilldate, conditions
`returnsellbill`	Sales returns	returnsellbillid, returnsellbillaftertotalbill, returnsellbilldate
`sellbillandrutern`	Combined sales/returns	id, sellaftertotalbill, returnaftertotalbill, sellbilldate
`expenses`	Business expenses	expensesid, expensesamount, expensesdate

### Asset & Financial Tables

Table Name	Purpose	Key Columns
`assets`	Fixed assets	assetsid, assetvalue, assetsdate, assetscatid
`storedetail`	Inventory valuation	storedetailid, productid, productquantity, storeid
`product`	Product pricing	productId, productBuyPrice, lastbuyprice, meanbuyprice
`save`	Cash/petty cash	saveid, saveamount, savedate
`bankaccount`	Bank balances	bankaccountid, bankaccountbalance

### Debt & Capital Tables

Table Name	Purpose	Key Columns
`client`	Customer debts	clientid, clientname, clientdebt
`supplier`	Supplier debts	supplierid, suppliername, supplierdebt
`capital`	Company capital	capitalid, capitalamount, capitaldate
`partner`	Partner withdrawals	partnerid, partnermoney, conditions
`kempiala`	Bills of exchange	kempialaId, kempialaValue, kempialaisselling

### Detail & Check Tables

Table Name	Purpose	Key Columns
`sellbilldetail`	Sales line items	sellbilldetailid, sellbilldetailquantity, buyprice
`returnsellbilldetail`	Return line items	returnsellbilldetailid, returnsellbilldetailquantity
`sellandruternbilldetail`	Combined bill details	id, sellbilldetailquantity, sellbilldetailtype
`datedchecked`	Check tracking	id, checkValue, checktype, checkdate

---

🔧 Key Functions

Core Balance Sheet Functions

Main Balance Sheet Display (Lines 242-368)

Purpose: Generates comprehensive balance sheet with assets, liabilities, and equity

Parameters:

• $_REQUEST['from'] - Start date for reporting period
• $_REQUEST['to'] - End date for reporting period

Process Flow:

┌─ Validate Date Range ─┐

Check from/to dates

Sales - Returns

COGS from purchases

Inventory + Fixed

Client/Supplier

Balance sheet format

└─ Display Results ─────┘

Key Calculations:

$totalSellCost = $totalSellPrice - $totalReturnSellPrice;
$totalBuyCost = $totalQuantityBuyPrice - $totalQuantityReturnBuyPrice;
$profitFinal = ($totalSellCost - $totalBuyCost) - $totalExpenses;
$forYou = $assetValue + $productPrice + $saveValue + $bankAccountbalance + $notSellingKempialaValue + $clientDebt + $totalclientcheck;

Revenue Calculation Functions

`getTotalSellbillByDate($startDate, $endDate)` (Lines 378-388)

Purpose: Calculates total sales revenue for date range

Parameters: Start and end dates

Returns: Total sales amount excluding taxes and discounts

`getTotalAditionalSellbillByDate($startDate, $endDate)` (Lines 391-401)

Purpose: Gets additional sales from combined sell/return bills

Parameters: Start and end dates

Returns: Additional sales revenue

`getTotalReturnSellbillByDate($startDate, $endDate)` (Lines 404-414)

Purpose: Calculates total sales returns for date range

Parameters: Start and end dates

Returns: Total return amount

`getTotalAditionalReturnSellbillByDate($startDate, $endDate)` (Lines 417-427)

Purpose: Gets additional returns from combined bills

Parameters: Start and end dates

Returns: Additional return amount

Cost of Goods Sold Functions

`getTotalQuantityBuyPriceByDate($startDate, $endDate)` (Lines 430-466)

Purpose: Calculates total cost of goods sold for sales

Parameters: Start and end dates

Process Flow:

┌─ Get Sold Items ────┐

From sellbilldetail

Get product number

Handle % and fixed

Quantity × Cost

└─ Return COGS ─────┘

Discount Calculation Logic:

if ($buyDiscount > 0 && $buyDiscountType == 0) {
    $productBuyPrice = $buyPrice - $buyDiscount; // Fixed discount
} elseif ($buyDiscount > 0 && $buyDiscountType == 1) {
    $productBuyPrice = $buyPrice - (($buyDiscount / 100) * $buyPrice); // Percentage
}

`getTotalQuantityReturnBuyPriceByDate($startDate, $endDate)` (Lines 513-555)

Purpose: Calculates cost of returned goods

Parameters: Start and end dates

Returns: Total cost of returned merchandise

Asset Valuation Functions

`getTotalAssetByDate($startDate, $endDate)` (Lines 622-638)

Purpose: Calculates total fixed asset value by date range

Parameters: Start and end dates

Process: Groups assets by category and sums values

Returns: Total fixed asset value

`getTotalProductPrice()` (Lines 640-702)

Purpose: Calculates inventory value using configured valuation method

Inventory Valuation Methods:

switch ($Programsettingdata->Inventoryevaluation) {
    case "first": $pro_price = (float) $storedetail->productBuyPrice; break;
    case "last": $pro_price = (float) $storedetail->lastbuyprice; break;
    case "mean": $pro_price = (float) $storedetail->meanbuyprice; break;
    case "last_discount": $pro_price = (float) $storedetail->lastbuyprice_withDiscount; break;
    case "mean_discount": $pro_price = (float) $storedetail->meanbuyprice_withDiscount; break;
    case "generalPrice": $pro_price = (float) $storedetail->overAllAveragePrice; break;
    case "tax": $pro_price = (float) $storedetail->lastbuyprice_withTax; break;
    case "mean_tax": $pro_price = (float) $storedetail->meanbuyprice_withTax; break;
}

Size/Color Handling:

if($storedetail->hasSizeAndColor == 1) {
    $scquantity = R::getCell('select sum(quantity) from sizecolorstoredetail where productid = '.$storedetail->productId);
    $productQuantity = $scquantity;
} else {
    $productQuantity = $storedetail->productquantity;
}

Cash & Bank Functions

`getTotalSaveValue()` (Lines 704-712)

Purpose: Gets total petty cash/save value

Returns: Current total in all save accounts

`getBankAccountbalance()` (Lines 714-723)

Purpose: Calculates total bank account balances

Returns: Sum of all bank account balances

Debt Analysis Functions

`getClientDebt()` (Lines 799-807)

Purpose: Calculates total customer debt

Returns: Total amount owed by all clients

`getSupplierDebt()` (Lines 767-775)

Purpose: Calculates total supplier debt

Returns: Total amount owed to all suppliers

`clientcheckvalue($startDate, $endDate)` (Lines 736-749)

Purpose: Calculates value of checks from clients

Parameters: Date range for check calculation

Returns: Total value of client checks

`suppliercheckvalue($startDate, $endDate)` (Lines 752-765)

Purpose: Calculates value of checks to suppliers

Parameters: Date range for check calculation

Returns: Total value of supplier checks

Capital & Investment Functions

`getCapitalamount()` (Lines 777-785)

Purpose: Gets total company capital investment

Returns: Total capital amount

`getPartnerwithdrawalvalue($startDate, $endDate)` (Lines 787-797)

Purpose: Calculates partner withdrawal amounts

Parameters: Date range (currently not used in query)

Returns: Total partner money withdrawals

Bill of Exchange Functions

`getNotSellingKempialaValue()` (Lines 725-733)

Purpose: Gets value of bills of exchange not yet sold

Returns: Total value of unsold Kempiala (bills of exchange)

`getSellingKempialaValue()` (Lines 809-818)

Purpose: Gets value of bills of exchange that have been sold

Returns: Total value of sold Kempiala

Expense Tracking

`getTotalExpensesByDate($startDate, $endDate)` (Lines 611-620)

Purpose: Calculates total expenses for date range

Parameters: Start and end dates

Returns: Total expense amount

---

🔄 Business Logic Flow

Balance Sheet Generation Process

User Request → Date Validation → Data Collection
     ↓
Revenue Calculation → Cost Calculation → Expense Calculation
     ↓
Asset Valuation → Debt Analysis → Capital Tracking
     ↓
Balance Sheet Assembly → Profit/Loss Calculation → Display

Financial Position Analysis

├─ Inventory

│ ├─ Cash (Saves) │ └─ Supplier Checks

│ ├─ Bank Accounts └─ Equity

├─ Client Debt

├─ Client Checks

│ └─ Unsold Kempiala │ └─ Partner Withdrawals

└─ Fixed Assets └─ Sold Kempiala

Inventory Valuation Logic

Get Product → Check Size/Color → Apply Valuation Method → Calculate Total
    ↓
FIFO/LIFO → Last Price → Mean Price → With/Without Discount → With/Without Tax

---

⚠️ Common Issues

Data Accuracy Issues

1. Date range dependency: All calculations depend on accurate date filtering

2. Inventory valuation: Different methods can show significant value differences

3. Currency precision: Financial calculations need proper rounding

Performance Considerations

1. Large date ranges: Extensive periods may cause slow report generation

2. Complex queries: Multiple table joins for cost calculations

3. Real-time calculations: No caching of frequently accessed totals

Business Logic Warnings

1. Incomplete transactions: Pending/draft bills may not be included

2. Multi-currency: No apparent support for foreign currency conversion

3. Tax calculations: Complex tax scenarios may need manual verification

---

🔗 Dependencies

Required Files

• ../public/impOpreation.php - Core operations
• ../public/include_dao.php - All DAO includes
• ../public/config.php - Database configuration
• ../public/authentication.php - User authentication
• ../library/breadcrumb.php - Navigation support

Database Dependencies

• Complete transaction history for accurate calculations
• Proper foreign key relationships between tables
• Updated product pricing information
• Current debt balances for clients and suppliers

External Dependencies

• Smarty templating engine for report display
• RedBean ORM for some direct queries
• Session management for user context
• YouTube integration for help videos

Configuration Dependencies

• Program settings for inventory valuation method
• Proper decimal place configuration
• Date format standardization
• Multi-language support setup

---

Financial Formula Notes:

• Gross Profit = Sales Revenue - Sales Returns - Cost of Goods Sold
• Net Profit = Gross Profit - Operating Expenses
• Total Assets = Current Assets + Fixed Assets
• Balance Check = Total Assets = Total Liabilities + Total Equity
• Working Capital = Current Assets - Current Liabilities

📚 ERP Documentation Viewer

Balance Report Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔧 Key Functions

Core Balance Sheet Functions

Main Balance Sheet Display (Lines 242-368)

Revenue Calculation Functions

getTotalSellbillByDate($startDate, $endDate) (Lines 378-388)

getTotalAditionalSellbillByDate($startDate, $endDate) (Lines 391-401)

getTotalReturnSellbillByDate($startDate, $endDate) (Lines 404-414)

getTotalAditionalReturnSellbillByDate($startDate, $endDate) (Lines 417-427)

Cost of Goods Sold Functions

getTotalQuantityBuyPriceByDate($startDate, $endDate) (Lines 430-466)

getTotalQuantityReturnBuyPriceByDate($startDate, $endDate) (Lines 513-555)

Asset Valuation Functions

getTotalAssetByDate($startDate, $endDate) (Lines 622-638)

getTotalProductPrice() (Lines 640-702)

Cash & Bank Functions

getTotalSaveValue() (Lines 704-712)

getBankAccountbalance() (Lines 714-723)

Debt Analysis Functions

getClientDebt() (Lines 799-807)

getSupplierDebt() (Lines 767-775)

clientcheckvalue($startDate, $endDate) (Lines 736-749)

suppliercheckvalue($startDate, $endDate) (Lines 752-765)

Capital & Investment Functions

getCapitalamount() (Lines 777-785)

getPartnerwithdrawalvalue($startDate, $endDate) (Lines 787-797)

Bill of Exchange Functions

getNotSellingKempialaValue() (Lines 725-733)

getSellingKempialaValue() (Lines 809-818)

Expense Tracking

getTotalExpensesByDate($startDate, $endDate) (Lines 611-620)

🔄 Business Logic Flow

Balance Sheet Generation Process

Financial Position Analysis

Inventory Valuation Logic

⚠️ Common Issues

Data Accuracy Issues

Performance Considerations

Business Logic Warnings

🔗 Dependencies

Required Files

Database Dependencies

External Dependencies

Configuration Dependencies

`getTotalSellbillByDate($startDate, $endDate)` (Lines 378-388)

`getTotalAditionalSellbillByDate($startDate, $endDate)` (Lines 391-401)

`getTotalReturnSellbillByDate($startDate, $endDate)` (Lines 404-414)

`getTotalAditionalReturnSellbillByDate($startDate, $endDate)` (Lines 417-427)

`getTotalQuantityBuyPriceByDate($startDate, $endDate)` (Lines 430-466)

`getTotalQuantityReturnBuyPriceByDate($startDate, $endDate)` (Lines 513-555)

`getTotalAssetByDate($startDate, $endDate)` (Lines 622-638)

`getTotalProductPrice()` (Lines 640-702)

`getTotalSaveValue()` (Lines 704-712)

`getBankAccountbalance()` (Lines 714-723)

`getClientDebt()` (Lines 799-807)

`getSupplierDebt()` (Lines 767-775)

`clientcheckvalue($startDate, $endDate)` (Lines 736-749)

`suppliercheckvalue($startDate, $endDate)` (Lines 752-765)

`getCapitalamount()` (Lines 777-785)

`getPartnerwithdrawalvalue($startDate, $endDate)` (Lines 787-797)

`getNotSellingKempialaValue()` (Lines 725-733)

`getSellingKempialaValue()` (Lines 809-818)

`getTotalExpensesByDate($startDate, $endDate)` (Lines 611-620)