Product Sales Report Controller Documentation

File: /controllers/productsellsreportController.php

Purpose: Generates comprehensive product sales reports with profit analysis, quantity tracking, and category-based filtering

Last Updated: December 20, 2024

Total Functions: 25+

Lines of Code: ~2,020

---

📋 Overview

The Product Sales Report Controller is a specialized reporting module that provides detailed product sales analysis and profit tracking capabilities. It handles:

• • Product-specific sales reports with profit calculations
• • Category-based product grouping and analysis
• • Date range filtering for sales data
• • Multiple bill types (regular sales, returns, combined bills, service bills)
• • Quantity tracking across different unit measurements
• • Profit evaluation using multiple pricing methods
• • Discount calculations and bill-level adjustments
• • Multi-level category hierarchy support
• • Sales vs returns comparison analysis

Primary Functions

• ✅ Generate product sales reports by category and date range
• ✅ Calculate profit using configurable pricing methods (first, last, mean, discount variants)
• ✅ Track quantities sold vs returned across bill types
• ✅ Handle discount calculations (fixed amount vs percentage)
• ✅ Support multi-level product categories
• ✅ Process regular sales, returns, and combined bills
• ✅ Include service bill analysis
• ✅ Generate category-based summary reports
• ✅ Calculate running totals and profit margins
• ✅ Support different unit measurements and conversions

Related Controllers

• • sellbillController.php - Sales operations
• • buyBillController.php - Purchase operations
• • returnSellBillController.php - Sales returns
• • productController.php - Product management
• • productCatController.php - Category management
• • billreceiptController.php - Service bills

---

🗄️ Database Tables

Primary Product Tables

🔑 Key Functions

1. show() / Default Action - Main Product Sales Report

Location: Line 154

Purpose: Generate comprehensive product sales report filtered by category and date range

Function Signature:

// Triggered when: do=show or empty $do
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$productCatId = $_REQUEST['productCatId'];

Process Flow:

1. Load product categories for dropdown selection

2. Parse and validate date parameters (defaults to current date)

3. Apply time zone adjustments from program settings

4. Route to appropriate report function based on parameters:

- loadProductByProductcatIdAndDate() - Specific category with date range

- loadProductByAllCategoriesAndDate() - All categories with date range

- loadProductByProductcatId() - Specific category without date filter

- loadProductByAllCategories() - All categories without date filter

5. Display via productsellsreportview/show.html template

Filter Combinations:

• • Category + Date Range
• • All Categories + Date Range
• • Category Only (all time)
• • All Categories (all time)
• • Automatic date defaulting when missing

---

2. loadProductByProductcatIdAndDate() - Category-Specific Date Report

Location: Line 1446

Purpose: Generate detailed sales report for products in specific category within date range

Function Signature:

function loadProductByProductcatIdAndDate($productcatId, $startDate, $endDate)

Process Flow:

1. Query products by category: queryByProductCatIdEX($productcatId)

2. For each product in category:

- Calculate sales quantities: showSellByProductAndDate()

- Calculate additional sales: showAditionalSellByProductAndDate()

- Calculate return quantities: showReturnSellByProductAndDate()

- Calculate net remaining quantity (sells - returns)

- Get profit/price data: getProductInBillByDateAndProductId()

- Process service bill data if applicable

3. Build category hierarchy path for display

4. Calculate running totals across all products

5. Assign individual product data to template variables

Key Calculations:

• • Net quantity = Total sells - Total returns
• • Profit using configurable evaluation method
• • Discount adjustments at bill and line levels
• • Service bill integration with separate totals

---

3. loadProductByAllCategoriesAndDate() - All Categories Date Report

Location: Line 1767

Purpose: Generate sales report for all product categories within date range

Function Signature:

function loadProductByAllCategoriesAndDate($startDate, $endDate)

Process Flow:

1. Query all products: queryByAllCategories()

2. Apply same detailed analysis as category-specific report

3. Process each product through full calculation pipeline

4. Generate comprehensive totals across entire product catalog

5. Group results by category for organized display

Performance Considerations:

• • Can process thousands of products
• • Memory-intensive for large catalogs
• • Optimized queries with date filtering
• • Results cached in template variables

---

4. getProductInBillByDateAndProductId() - Comprehensive Product Analysis

Location: Line 1191

Purpose: Calculate detailed profit, pricing, and quantity data for a specific product

Function Signature:

function getProductInBillByDateAndProductId($startDate, $endDate, $productId)

Process Flow:

1. Regular Sales: getTotalSellPriceByDateAndProductId()

2. Additional Sales: getTotalAditionalSellPriceByDateAndProductId()

3. Returns: getTotalReturnSellPriceByDateAndProductId()

4. Additional Returns: getTotalAditionalReturnSellPriceByDateAndProductId()

5. Buy Price Analysis: getTotalBuyPriceByDateAndProductId()

6. Additional Buy Data: getTotalAditionalBuyPriceByDateAndProductId()

7. Return Buy Data: getTotalReturnBuyPriceByDateAndProductId()

Returns Array:

return array(
    $finalProfit,           // [0] Net profit amount
    $finalSellPrice,        // [1] Total sell price after discounts
    $finalBuyPrice,         // [2] Total buy price
    $totalsell_withoutdiscount, // [3] Sell price before discounts
    $finalQuantity,         // [4] Net quantity (sells - returns)
    $finalSellQuantity,     // [5] Total sell quantity
    $finalReturnQuantity,   // [6] Total return quantity
    $finalBuyQuantity       // [7] Total buy quantity
);

---

5. getTotalSellPriceByDateAndProductId() - Regular Sales Analysis

Location: Line 557

Purpose: Calculate sales totals, profit, and quantities for regular sell bills

Function Signature:

function getTotalSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected)

Process Flow:

1. Query sell bill details: queryWithDateAndConditionsAndProductIdExt()

2. For each line item:

- Apply profit evaluation method from settings

- Calculate quantity in base units using unit conversions

- Process line-level discounts

- Calculate bill-level discount proportion

- Compute final sell price and profit

3. Aggregate totals across all bills

Profit Evaluation Methods:

switch ($Programsetting->Profitevaluation) {
    case "first": $buyprice = $sellbilldetail->buyprice; break;
    case "last": $buyprice = $sellbilldetail->lastbuyprice; break;
    case "mean": $buyprice = $sellbilldetail->meanbuyprice; break;
    case "last_discount": $buyprice = $sellbilldetail->lastbuyprice_withDiscount; break;
    case "mean_discount": $buyprice = $sellbilldetail->meanbuyprice_withDiscount; break;
    case "generalPrice": $buyprice = $sellbilldetail->generalPrice; break;
}

Discount Processing:

// Line-level discount
$discountValue = $sellbilldetail->discountvalue;

// Bill-level discount calculation
if ($sellbillDiscountType == 2) { // Percentage
    $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
}
// Proportional bill discount for this line
$billDiscountPortion = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;

// Final sell price after all discounts
$sellPriceForOneProduct = $totalPriceBeforeDiscount - ($discountValue + $billDiscountPortion);

---

6. getTotalAditionalSellPriceByDateAndProductId() - Combined Bill Sales

Location: Line 640

Purpose: Process sales from combined sell/return bills (selltype = 0)

Function Signature:

function getTotalAditionalSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $overAllAveragePrice)

Process Flow:

1. Query combined bill details with sell type 0

2. Apply same calculation logic as regular sales

3. Handle different discount structure for combined bills

4. Use average price override when configured

Key Differences from Regular Sales:

• • Different discount field structure
• • Average price fallback option
• • Combined bill-specific queries
• • Separate total tracking

---

7. showSellByProductAndDate() / showReturnSellByProductAndDate() - Quantity Helpers

Location: Lines 1416, 1431

Purpose: Get simple quantity totals for specific product/date combinations

Function Signatures:

function showSellByProductAndDate($productId, $startDate, $endDate)
function showReturnSellByProductAndDate($productId, $startDate, $endDate)

Returns: Simple quantity totals for quick calculations

---

8. loadProductCat() - Category Data Loader

Location: Line 1991

Purpose: Load product categories that contain products for dropdown population

Function Signature:

function loadProductCat()

Returns: Array of product categories with associated products

---

🔄 Workflows

Workflow 1: Product Category Sales Report Generation

---

Workflow 2: Comprehensive Product Analysis Pipeline

---

🌐 URL Routes & Actions

Product Sales Report (do=show):

• • productCatId - Category ID (-1=all, "all"=all, specific ID)
• • from - Start date (YYYY-MM-DD, optional)
• • to - End date (YYYY-MM-DD, optional)

Main Category View (do=showmain):

• • from - Start date (optional)
• • to - End date (optional)
• • level - Category level filter

Parameter Processing Logic

// Date defaulting
if (empty($startDate) && empty($endDate)) {
    $startDate = date('Y-m-d');
    $endDate = date('Y-m-d');
}

// Time adjustment from settings
if (isset($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($endDate)));
    $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($startDate)));
}

---

🧮 Calculation Methods

Profit Evaluation Methods

// Configurable via programsettings.profitevaluation
switch ($Programsetting->Profitevaluation) {
    case "first":         // First purchase price
    case "last":          // Last purchase price  
    case "mean":          // Average purchase price
    case "last_discount": // Last price with discount
    case "mean_discount": // Average price with discount
    case "generalPrice":  // General/override price
}

Discount Processing

// Line-level product discount
$lineDiscount = $sellbilldetail->discountvalue;

// Bill-level discount calculation
if ($sellbillDiscountType == 2) {
    $billDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill; // Percentage
} else {
    $billDiscount = $sellbillDiscount; // Fixed amount
}

// Proportional bill discount for this line item
$proportionalDiscount = ($lineTotal / $billTotal) * $billDiscount;

// Final price after all discounts
$finalPrice = $lineTotalBeforeDiscount - ($lineDiscount + $proportionalDiscount);

Unit Conversion

// Convert sold quantity to base units
$productnumber = $myProductunitEx->getProductNumber($productunitId);
$quantityInBaseUnits = $quantity * $productnumber;

Net Quantity Calculation

// Calculate remaining stock movement
$totalSellQuantity = $regularSells + $additionalSells;
$totalReturnQuantity = $regularReturns + $additionalReturns;
$remainingQuantity = $totalSellQuantity - $totalReturnQuantity;

Service Bills Integration

// Service bill discount calculation
$productTotal = $detail->productprice * $detail->productno;
$billDiscountAmount = $productTotal - $detail->netbillvalue;
$finalSellPrice = $productTotal - $billDiscountAmount;

// Service bill profit
$profit = $finalSellPrice - ($detail->productBuyPrice * $detail->productno);

---

🔒 Security & Permissions

User Authentication

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

// Category visibility control
$smarty->assign("hidecat", $_SESSION['hidecat']);

Input Sanitization

• • All $_REQUEST parameters processed through framework filters
• • Date strings validated before SQL usage
• • Numeric IDs cast appropriately: (int) $productCatId
• • SQL injection prevention via DAO layer parameterized queries

Session Management

• • User session validation required for access
• • Category hiding based on user permissions
• • Group-based access control integration

---

📊 Performance Considerations

Database Optimization

1. Required Indexes:

- sellbilldetail(sellbilldetailproductid, sellbilldate)

- product(productcatid)

- sellbill(sellbilldate, conditions)

- returnsellbilldetail(returnsellbilldetailproductid, returnsellbilldate)

- bills(billdate)

2. Query Performance:

- Date range filtering applied at database level

- Category filtering reduces result sets

- Conditions filtering excludes cancelled bills

- Unit conversion queries optimized

3. Memory Management:

- Large product catalogs can consume significant memory

- Template variable assignment for thousands of products

- Consider pagination for very large datasets

- Cleanup of intermediate calculation variables

Known Performance Bottlenecks

-- This can be slow for large date ranges with many products
SELECT * FROM sellbilldetail sbd
JOIN sellbill sb ON sbd.sellbillid = sb.sellbillid
WHERE sbd.sellbilldetailproductid = ? 
AND sb.sellbilldate BETWEEN ? AND ?
AND sb.conditions = 0;

-- Solution: Composite index
CREATE INDEX idx_product_date_conditions ON sellbilldetail(sellbilldetailproductid, sellbilldate);

---

🐛 Common Issues & Troubleshooting

1. Incorrect Profit Calculations

Issue: Profit amounts don't match expected values

Cause: Wrong profit evaluation method or missing buy price data

Debug:

-- Check profit evaluation setting
SELECT settingvalue FROM programsettings WHERE settingkey = 'Profitevaluation';

-- Verify buy price data exists
SELECT productid, buyprice, lastbuyprice, meanbuyprice 
FROM sellbilldetail WHERE sellbilldetailproductid = [ID];

2. Missing Product Data

Issue: Products not appearing in reports despite having sales

Cause: Category associations or date range issues

Fix:

-- Check product category assignment
SELECT p.productname, p.productcatid, pc.productcatname
FROM product p 
LEFT JOIN productcat pc ON p.productcatid = pc.productcatid
WHERE p.productid = [ID];

-- Verify sales exist in date range
SELECT COUNT(*) FROM sellbilldetail sbd
JOIN sellbill sb ON sbd.sellbillid = sb.sellbillid
WHERE sbd.sellbilldetailproductid = [ID]
AND sb.sellbilldate BETWEEN '[START]' AND '[END]';

3. Discount Calculation Issues

Issue: Discount amounts don't match manual calculations

Cause: Mixed discount types or bill-level discount distribution

Debug:

// Enable discount debugging
echo "Line Discount: " . $discountValue . "<br>";
echo "Bill Total: " . $sellbillTotalBill . "<br>";
echo "Bill Discount: " . $sellbillDiscount . " (Type: " . $sellbillDiscountType . ")<br>";
echo "Line Proportion: " . ($totalPrice / $sellbillTotalBill) . "<br>";
echo "Proportional Discount: " . (($totalPrice / $sellbillTotalBill) * $sellbillDiscount) . "<br>";

4. Unit Conversion Problems

Issue: Quantities don't match between reports and actual sales

Cause: Unit conversion factors not properly applied

Fix:

-- Verify unit conversion factors
SELECT pu.productunitname, pu.productnumber
FROM productunit pu
JOIN sellbilldetail sbd ON pu.productunitid = sbd.productunitid
WHERE sbd.sellbilldetailproductid = [ID];

---

🧪 Testing Scenarios

Test Case 1: Category Report Accuracy

1. Select category with known product sales
2. Set date range covering test transactions
3. Verify quantities match database queries
4. Check profit calculations against manual computation
5. Confirm discount applications are correct

Test Case 2: All Categories Performance

1. Run report for all categories with large date range
2. Monitor memory usage and execution time
3. Verify totals match sum of individual category reports
4. Test with different profit evaluation methods

Test Case 3: Service Bills Integration

1. Create test service bills with products
2. Run product report covering service bill dates
3. Verify service bill amounts included in totals
4. Check profit calculations include service data

Debug Mode Enable

// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug product analysis
echo "<pre>";
$debugData = getProductInBillByDateAndProductId($startDate, $endDate, $productId);
print_r($debugData);
echo "</pre>";

// Debug category queries
echo "Product Count: " . count($productData) . "<br>";
foreach($productData as $product) {
    echo "Product: " . $product->productname . " (ID: " . $product->productId . ")<br>";
}

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • productController.md - Product management
• • productCatController.md - Category management
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur