Quick Profit Reports Controller Documentation

File: /controllers/quickprofitreports.php

Purpose: Generates comprehensive quick profit analysis reports for sales performance tracking

Last Updated: December 20, 2024

Total Functions: 2+

Lines of Code: ~406

---

📋 Overview

The Quick Profit Reports Controller is a specialized reporting module that provides detailed profit analysis capabilities across multiple dimensions. It handles:

• • General profit analysis (overall system)
• • Daily profit breakdowns with date range filtering
• • Store-specific profit tracking
• • Product-level profit analysis
• • Client-specific profit reporting
• • Category-based profit summaries with hierarchical support
• • Multi-format cost calculation methods (first, last, mean, with/without discounts)
• • Date range filtering and comparison
• • Profit margin calculations with various pricing methods

Primary Functions

• ✅ Generate system-wide general profit reports
• ✅ Track daily profit by date ranges
• ✅ Store-specific profit analysis
• ✅ Product-level profit tracking
• ✅ Client profit performance reports
• ✅ Category profit analysis with sub-category rollup
• ✅ Multiple buy price calculation methods
• ✅ Sales vs returns profit analysis
• ✅ Date range filtering and comparison
• ✅ Hierarchical category reporting

Related Controllers

• • sellbillController.php - Sales operations that generate profit data
• • buyBillController.php - Purchase operations for cost tracking
• • calcquickprofit.php - Profit calculation engine
• • productController.php - Product management
• • clientController.php - Customer management
• • storeController.php - Store management

---

🗄️ Database Tables

Primary Profit Tables (Direct Operations)

🔑 Key Functions

1. Default Action - General Profit Overview

Location: Line 109

Purpose: Display system-wide profit summary and overview dashboard

Function Signature:

// Triggered when: empty $do
// No parameters required

Process Flow:

1. Include authentication check

2. Load general profit data from quickprofitgeneral table

3. Load YouTube tutorial links

4. Set empty dates flag for template

5. Display via general.html template

Features:

• • System-wide profit overview
• • Multiple cost calculation methods
• • Tutorial links integration
• • Clean dashboard view

---

2. day - Daily Profit Report

Location: Line 122

Purpose: Generate daily profit breakdown with date range filtering

Function Signature:

// Triggered when: do=day
$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

Process Flow:

1. Parse and validate date parameters

2. Set default buy price type to "first" if not specified

3. Build dynamic query string for date filtering

4. Query quickprofitday table with date constraints

5. Handle empty date scenario (show general profits)

6. Display via day.html template

Query Building:

if (isset($from) && !empty($from)) {
    $queryString .= 'and quickprofitday.theDate >= "' . $from . '" ';
}
if (isset($to) && !empty($to)) {
    $queryString .= 'and quickprofitday.theDate <= "' . $to . '" ';
}

Features:

• • Date range filtering
• • Buy price type selection (first/last/mean)
• • Fallback to general profits when no dates
• • Daily profit trend analysis

---

3. storeday - Store-Specific Daily Profits

Location: Line 159

Purpose: Generate daily profit reports filtered by store and date range

Function Signature:

// Triggered when: do=storeday
$storeId = filter_input(INPUT_POST, "storeId");
$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

Process Flow:

1. Load all store master data for dropdown

2. Parse store and date filter parameters

3. Build query with store and date constraints

4. Query quickprofitstore table

5. Display via storeDay.html template

Query Building:

if (isset($storeId) && !empty($storeId)) {
    $queryString .= 'and quickprofitstore.storeId = "' . $storeId . '" ';
}

Features:

• • Store-specific filtering
• • Store dropdown population
• • Date range analysis per store
• • Store performance comparison capability

---

4. productday - Product-Level Profit Analysis

Location: Line 197

Purpose: Track profit performance for specific products over time

Function Signature:

// Triggered when: do=productday
$productId = filter_input(INPUT_POST, "productId");
$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

Process Flow:

1. Load program settings for display configuration

2. Parse product and date parameters

3. Build query for product-specific filtering

4. Query quickprofitproduct table

5. Display via productDay.html template

Features:

• • Individual product profit tracking
• • Product performance over time
• • Multiple cost calculation methods
• • Product profitability analysis

---

5. catday - Category Profit Report

Location: Line 234

Purpose: Analyze profit performance by product category

Function Signature:

// Triggered when: do=catday
$lastCatId = filter_input(INPUT_POST, "lastCatId");
$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

Process Flow:

1. Load program settings

2. Parse category and date parameters

3. Build query with category filtering via JOIN to productcat

4. Query quickprofitproduct with category constraints

5. Display via catDay.html template

Query Building:

if (isset($lastCatId) && !empty($lastCatId)) {
    $queryString .= 'and productcat.productCatId = "' . $lastCatId . '" ';
}

Features:

• • Category-level profit analysis
• • Category performance comparison
• • Product category filtering
• • Category profitability insights

---

6. allcatday - Hierarchical Category Profit Summary

Location: Line 272

Purpose: Comprehensive category analysis with sub-category rollup

Function Signature:

// Triggered when: do=allcatday
$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

Process Flow:

1. Load program settings and category hierarchy via getCategoryChilds()

2. Parse hierarchical category selection (supports multiple levels)

3. Handle category level fallback logic

4. Build category ID string including all subcategories via getAllSubCat()

5. Query with category IN clause for hierarchical inclusion

6. Use specialized queryAllEXGroupByCatAndDate() method

7. Display via allCatDay.html template

Category Hierarchy Processing:

$catsIDS = '' . $productCatId;
if ($productCatId != '') {
    getAllSubCat($productCatId, 1); // mode = 1 get all sub cats
}

Advanced Query:

if (isset($catsIDS) && !empty($catsIDS)) {
    $queryString .= 'and productcat.productCatId in (' . $catsIDS . ') ';
}

Features:

• • Multi-level category hierarchy support
• • Recursive subcategory inclusion
• • Category tree navigation
• • Aggregated subcategory profits
• • Level-based category selection

---

7. clientday - Client Profit Analysis

Location: Line 323

Purpose: Track profit performance per customer over time

Function Signature:

// Triggered when: do=clientday
$clientId = filter_input(INPUT_POST, "clientId");
$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

Process Flow:

1. Parse client and date filter parameters

2. Build query string for client-specific filtering

3. Query quickprofitclient table

4. Display via clientDay.html template

Features:

• • Customer-specific profit tracking
• • Client profitability analysis
• • Customer performance over time
• • Client profit contribution analysis

---

8. getAllSubCat() - Recursive Category Function

Location: Line 367

Purpose: Recursively build category ID list including all subcategories

Function Signature:

function getAllSubCat($catid, $mode)

Process Flow:

1. Query subcategories using queryByParentExt()

2. For each subcategory:

- Add to global $catsIDS string (mode 1)

- Or collect last-level categories (mode 2)

- Recursively call self for deeper levels

3. Build comma-separated ID string for SQL IN clauses

Modes:

• • Mode 1: Collect all subcategory IDs for comprehensive reporting
• • Mode 2: Collect only leaf-level categories (unused in this controller)

Recursive Logic:

foreach ($result as $data) {
    if ($mode == 1) {
        $catsIDS .= "," . $data->productCatId;
        getAllSubCat($data->productCatId, $mode);
    }
}

---

🔄 Workflows

Workflow 1: Daily Profit Report Generation

---

Workflow 2: Hierarchical Category Profit Analysis

---

🌐 URL Routes & Actions

General Report (do= empty):

• • No parameters required

Daily Report (do=day):

• • from - Start date (YYYY-MM-DD) [optional]
• • to - End date (YYYY-MM-DD) [optional]
• • buyPriceType - Cost calculation method: "first", "last", "mean" [optional, defaults to "first"]

Store Daily (do=storeday):

• • storeId - Store ID [optional, for filtering specific store]
• • from - Start date [optional]
• • to - End date [optional]
• • buyPriceType - Cost calculation method [optional]

Product Daily (do=productday):

• • productId - Product ID [optional, for specific product]
• • from - Start date [optional]
• • to - End date [optional]
• • buyPriceType - Cost calculation method [optional]

Category Daily (do=catday):

• • lastCatId - Category ID [optional, for specific category]
• • from - Start date [optional]
• • to - End date [optional]
• • buyPriceType - Cost calculation method [optional]

All Categories (do=allcatday):

• • level - Category hierarchy level number
• • productCatId{level} - Category ID for specified level
• • from - Start date [optional]
• • to - End date [optional]
• • buyPriceType - Cost calculation method [optional]

Client Daily (do=clientday):

• • clientId - Customer ID [optional, for specific client]
• • from - Start date [optional]
• • to - End date [optional]
• • buyPriceType - Cost calculation method [optional]

---

🧮 Calculation Methods

Buy Price Types

The system supports multiple cost calculation methods controlled by $buyPriceType:

"first" - First Purchase Price:

• • Uses original purchase cost from sellCostBuyPrice fields
• • Most conservative profit calculation
• • Based on FIFO (First-In-First-Out) costing

"last" - Last Purchase Price:

• • Uses most recent purchase cost from sellCostLastBuyPrice fields
• • Reflects current market conditions
• • Based on LIFO (Last-In-First-Out) costing

"mean" - Average Purchase Price:

• • Uses weighted average cost from sellCostMeanBuyPrice fields
• • Smooths price fluctuations
• • Based on weighted average costing

"withDiscount" - Discount-Adjusted Prices:

• • Available for both last and mean methods
• • Fields: sellCostLastBuyPricewithDiscount, sellCostMeanBuyPricewithDiscount
• • Accounts for volume discounts and supplier rebates

Profit Calculations

// Basic profit calculation
$profit = $sellVal - $sellCost;
$profitMargin = ($profit / $sellVal) * 100;

// Net profit (including returns)
$netProfit = ($sellVal - $returnSellVal) - ($sellCost - $returnSellCost);

Return Impact Analysis

// Returns reduce both revenue and cost
$netSellVal = $sellVal - $returnSellVal;
$netSellCost = $sellCost - $returnSellCost;
$netProfit = $netSellVal - $netSellCost;

---

🔒 Security & Permissions

Authentication

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

• • All actions require user authentication
• • Session validation performed before data access
• • Redirects to login if not authenticated

Input Sanitization

$from = filter_input(INPUT_POST, "from");
$to = filter_input(INPUT_POST, "to");
$buyPriceType = filter_input(INPUT_POST, "buyPriceType");

• • All user inputs filtered through filter_input()
• • SQL injection prevention via DAO layer
• • Parameter validation before database queries

Data Access Control

• • No explicit user-based filtering in this controller
• • Assumes profit data is accessible to authenticated users
• • Store/client filtering through form selection only
• • No row-level security implementation

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

   -- Daily reports
   CREATE INDEX idx_quickprofitday_date ON quickprofitday(theDate);
   
   -- Store reports  
   CREATE INDEX idx_quickprofitstore_store_date ON quickprofitstore(storeId, theDate);
   
   -- Product reports
   CREATE INDEX idx_quickprofitproduct_product_date ON quickprofitproduct(productId, theDate);
   
   -- Client reports
   CREATE INDEX idx_quickprofitclient_client_date ON quickprofitclient(clientId, theDate);
   
   -- Category hierarchy
   CREATE INDEX idx_productcat_parent ON productcat(productCatParent);
   ```

2. **Query Optimization**:
   - Date range queries benefit from proper date formatting
   - Category hierarchy queries can be expensive with deep nesting
   - Consider materialized views for complex aggregations

3. **Memory Management**:
   - Large date ranges may return significant data
   - Category recursion limited by available memory
   - Template variable cleanup important for category reports

### Known Performance Issues

-- Recursive category queries can be slow with deep hierarchies

-- Consider limiting recursion depth or using iterative approach

-- Date range queries without indexes

SELECT * FROM quickprofitday

WHERE theDate BETWEEN '2024-01-01' AND '2024-12-31';

-- Solution: Add proper date index and limit ranges

CREATE INDEX idx_quickprofitday_thedate ON quickprofitday(theDate);

---

## 🐛 Common Issues & Troubleshooting

### 1. **Empty Profit Data**
**Issue**: Reports show no data despite having sales  
**Cause**: Profit calculation process (`calcquickprofit.php`) not running

**Debug**:

-- Check if profit tables are populated

SELECT COUNT(*) FROM quickprofitday WHERE theDate >= '2024-01-01';

SELECT COUNT(*) FROM quickprofitproduct WHERE theDate >= '2024-01-01';

**Fix**: Run profit calculation process to populate tables

### 2. **Category Hierarchy Issues**
**Issue**: Subcategories not included in reports  
**Cause**: `getAllSubCat()` recursion failing or category relationships broken

**Debug**:

-- Check category parent relationships

SELECT productCatId, productCatName, productCatParent

FROM productcat WHERE productCatParent IS NOT NULL;

-- Verify category hierarchy integrity

SELECT COUNT(*) as orphaned_categories

FROM productcat c1

LEFT JOIN productcat c2 ON c1.productCatParent = c2.productCatId

WHERE c1.productCatParent IS NOT NULL AND c2.productCatId IS NULL;

### 3. **Date Range Problems**
**Issue**: Date filters not working correctly  
**Cause**: Date format mismatch or timezone issues

**Debug**:

// Check date format in queries

echo "Query String: " . $queryString . "
";

// Verify date format matches database

echo "From: " . $from . " To: " . $to . "
";

**Fix**:

// Ensure proper date format

if (!empty($from)) $from .= " 00:00:00";

if (!empty($to)) $to .= " 23:59:59";

### 4. **Buy Price Type Confusion**
**Issue**: Profit calculations inconsistent between reports  
**Cause**: Different buy price types selected or default not set

**Fix**:

// Always set default buy price type

if (!isset($buyPriceType) || empty($buyPriceType)) {

$buyPriceType = "first";

}

---

## 🧪 Testing Scenarios

### Test Case 1: Daily Profit Accuracy

1. Create test sales with known costs

2. Run profit calculation process

3. Generate daily report for test date

4. Verify profit = revenue - cost matches manually calculated values

5. Test with different buy price types

### Test Case 2: Category Hierarchy Rollup

1. Create category structure: Parent > Child > Grandchild

2. Add products to grandchild category

3. Generate sales for these products

4. Run allcatday report selecting parent category

5. Verify totals include all descendant categories

### Test Case 3: Date Range Filtering

1. Create sales across multiple months

2. Test date range spanning partial months

3. Verify exact date boundary handling

4. Test edge cases (single day, year boundaries)

### Debug Mode Enable

// Add at top of controller for debugging

error_reporting(E_ALL);

ini_set('display_errors', 1);

// Debug query building

echo "Query String: " . $queryString . "
";

echo "Category IDs: " . $catsIDS . "
";

// Debug data results

echo "

";

print_r($quickProfitDay);

echo "

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • buyBillController.md - Purchase operations
• • calcquickprofit.php - Profit calculation engine
• • reportfunctions.php - Shared reporting functions
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur