Clients and Products Report Controller Documentation

File: /controllers/clientsAndProductsReportController.php

Purpose: Generates comprehensive reports for client-product transaction history and analysis

Last Updated: December 20, 2024

Total Functions: 4+

Lines of Code: ~450

---

📋 Overview

The Clients and Products Report Controller is a specialized reporting module that provides detailed analysis of customer transactions with specific products. It handles:

• • Individual client-product transaction reports
• • Combined sales, returns, and sell-return bills analysis
• • Product-specific customer purchasing history
• • Date range filtering for transaction analysis
• • Multi-table union queries for comprehensive data
• • Price history tracking by client and product
• • Unit conversion and quantity analysis
• • Discount and pricing analytics

Primary Functions

• ✅ Generate client-product transaction reports
• ✅ Track product sales history per customer
• ✅ Analyze price variations over time
• ✅ Combine multiple bill types (sales, returns, combined)
• ✅ Date range filtering and analysis
• ✅ Product unit conversion tracking
• ✅ Discount and price type analysis
• ✅ Stock quantity monitoring
• ✅ Multi-table data aggregation
• ✅ Transaction linking to source documents

Related Controllers

• • sellbillController.php - Sales operations
• • clientController.php - Customer management
• • returnsellbillController.php - Sales returns
• • productController.php - Product management
• • sellBillReportsController.php - Sales reporting

---

🗄️ Database Tables

Primary Sales Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Client-Product Transaction Report

Location: Line 139

Purpose: Generate comprehensive transaction report for specific client-product combinations

Function Signature:

// Triggered when: do=show or empty $do
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$productId = $_REQUEST['productId'];
$clientId = (int) $_REQUEST['clientId'];

Process Flow:

1. Validate user permissions and load settings

2. Parse date parameters with default to current date

3. Load client and product data for display names

4. Build complex WHERE clauses for multiple table types

5. Execute union query combining sales, returns, and combined bills

6. Apply date adjustments based on system settings

7. Display via clientsAndProductsReportview/showNew.html template

Features:

• • Date range filtering with time adjustments
• • Product and client filtering
• • Multi-table union queries
• • Price type and discount tracking
• • Stock quantity monitoring
• • Message generation for report headers

Union Query Structure:

(SELECT ... FROM sellbilldetail JOIN sellbill ...)
UNION ALL
(SELECT ... FROM sellandruternbilldetail JOIN sellbillandrutern ...)
UNION ALL  
(SELECT ... FROM returnsellbilldetail JOIN returnsellbill ...)

---

2. getClients() - Customer Data Loader

Location: Line 280

Purpose: Load active customers for dropdown selection

Function Signature:

function getClients()

Process Flow:

1. Query client table with condition filter (active clients only)

2. Return array of client objects for dropdown population

Returns: Array of customer objects with conditions = 0 (active)

---

3. loadPricesForClient() - Comprehensive Price History

Location: Line 286

Purpose: Load detailed price history for client-product combination (currently unused)

Function Signature:

function loadPricesForClient($productId, $clientId, $startDate, $endDate)

Process Flow:

1. Call getProductPriceFromSellBill() to get all transaction types

2. Merge data from multiple bill types

3. Return combined array of transactions

Note: This function contains commented-out unit-based iteration logic that was previously used for per-unit analysis.

---

4. getProductPriceFromSellBill() - Sales Transaction Data

Location: Line 342

Purpose: Retrieve product transactions from sales system tables

Function Signature:

function getProductPriceFromSellBill($ProductId, $ClientId, $startDate, $endDate)

Process Flow:

1. Build dynamic WHERE clauses for multiple table types:

- $whereSellBill - Regular sales bills

- $whereSellAndReturn - Combined bills

- $whereBillProduct - Service bills

- $whereReturnBill - Return bills

2. Apply product ID filter if specified

3. Apply client ID filter if specified

4. Apply date range filter if specified

5. Query each table type using extended DAO methods

6. Return array with four datasets

WHERE Clause Building:

if ($ProductId != '' && $ProductId != '-1') {
    $whereSellBill .= ' sellbilldetail.sellbilldetailproductid = ' . $ProductId;
    $whereSellAndReturn .= ' sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId;
    // ... for each table type
}

Returned Data Structure:

return array(
    $sellbilldetailData,        // [0] Regular sales
    $sellandruternbilldetailData, // [1] Combined bills  
    $BillProductData,           // [2] Service bills
    $ruternBillProductData      // [3] Service returns
);

---

5. getProductPriceFromBills() - Service Bills Analysis

Location: Line 438

Purpose: Retrieve product transactions from service bills system (alternative)

Function Signature:

function getProductPriceFromBills($ProductId, $ClientId, $startDate, $endDate)

Process Flow:

1. Query service bill products with filters

2. Query service bill returns with filters

3. Return array with both datasets

Use Case: Alternative billing system for service-based businesses (like optical shops)

---

🔄 Workflows

Workflow 1: Client-Product Transaction Report Generation

---

Workflow 2: Data Aggregation from Multiple Bill Types

---

🌐 URL Routes & Actions

Client-Product Report (do=show):

• • from - Start date (YYYY-MM-DD) - Optional, defaults to today
• • to - End date (YYYY-MM-DD) - Optional, defaults to today
• • productId - Product ID - Optional, empty or -1 for all products
• • clientId - Client ID - Optional, must be > 1 for specific client

Example URLs

# Specific client and product for date range
/clientsAndProductsReportController.php?do=show&clientId=5&productId=10&from=2024-12-01&to=2024-12-20

# All products for specific client
/clientsAndProductsReportController.php?do=show&clientId=5&from=2024-12-01&to=2024-12-20

# Specific product for all clients
/clientsAndProductsReportController.php?do=show&productId=10&from=2024-12-01&to=2024-12-20

# Today's transactions (default)
/clientsAndProductsReportController.php?do=show

---

🧮 Calculation Methods

Date Adjustment Logic

if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    // Add extra hours for business timezone
    $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)));
} else {
    // Standard day boundaries
    $endDate = $endDate . ' 23:59:59';
    $startDate = $startDate . " 00:00:00";
}

Bill Type Classification

// Bill type indicators in union query
0 = Regular sales bills (sellbilldetail)
1 = Combined bills - sales portion (sellandruternbilldetail)  
4 = Return bills (returnsellbilldetail)

Stock Quantity Handling

// Left join with storedetail for current stock
LEFT JOIN storedetail ON (
    product.productId = storedetail.productid 
    AND sellbilldetail.storeid = storedetail.storeid
)
// Shows productquantity for stock reference

Message Generation

$message = '';
if ($clientId > 1) {
    $message .= "العميل :" . $clientName; // Client: [name]
}
if ($productId != '' && $productId != '-1') {
    $message .= "<br> المنتج: " . $productName; // Product: [name]
}
if ($startDate != '' && $endDate != '') {
    $message .= "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate; // Date range
}

---

🔒 Security & Permissions

Authentication Check

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

Security Features:

• • Session-based authentication required
• • Input parameter sanitization
• • SQL injection prevention via DAO layer
• • Numeric ID casting: (int) $_REQUEST['clientId']

Input Validation

// Client ID validation
$clientId = (int) $_REQUEST['clientId']; // Cast to integer

// Product ID validation  
if ($productId != '' && $productId != '-1') {
    // Valid product specified
}

// Date validation through framework
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];

---

📊 Performance Considerations

Database Optimization

1. Indexes Required:

- sellbilldetail(sellbilldetailproductid, sellbillid)

- sellbill(sellbillclientid, sellbilldate)

- sellandruternbilldetail(sellbilldetailproductid, sellbillid)

- returnsellbilldetail(returnsellbilldetailproductid, returnsellbillid)

- client(clientid)

- product(productId)

2. Query Optimization:

- Union queries can be resource intensive

- Date filtering with proper indexes

- LEFT JOINs for optional data (storedetail)

- Efficient WHERE clause ordering

3. Memory Management:

- Large date ranges may return many records

- JSON encoding for template data

- Multiple table joins in single query

Union Query Performance

-- The main query structure
( SELECT ... FROM sellbilldetail ... ORDER BY sellbilldetailid DESC )
UNION ALL
( SELECT ... FROM sellandruternbilldetail ... ORDER BY sellbilldetailid DESC )  
UNION ALL
( SELECT ... FROM returnsellbilldetail ... ORDER BY returnsellbilldetailid DESC )

-- Potential optimization: Add LIMIT if needed
-- Consider pagination for very large result sets

---

🐛 Common Issues & Troubleshooting

1. Empty Results Despite Valid Data

Issue: Report shows no data when transactions exist

Cause: Date format mismatch or condition filtering

Debug:

-- Check data exists
SELECT COUNT(*) FROM sellbilldetail sd 
JOIN sellbill sb ON sd.sellbillid = sb.sellbillid
WHERE sb.sellbillclientid = [CLIENT_ID] 
AND sb.conditions = 0;

-- Check date format
SELECT sellbilldate FROM sellbill WHERE sellbillid = [BILL_ID];

2. Incorrect Stock Quantities

Issue: productquantity shows NULL or wrong values

Cause: Missing storedetail records or mismatched store IDs

Fix:

-- Verify storedetail records
SELECT * FROM storedetail WHERE productid = [PRODUCT_ID];

-- Check store assignments in bills
SELECT DISTINCT storeid FROM sellbilldetail WHERE sellbilldetailproductid = [PRODUCT_ID];

3. Missing Product/Client Names

Issue: Names don't appear in message header

Cause: Invalid IDs or inactive records

Debug:

-- Check client exists and is active
SELECT * FROM client WHERE clientid = [CLIENT_ID] AND conditions = 0;

-- Check product exists
SELECT * FROM product WHERE productId = [PRODUCT_ID];

4. Date Adjustment Issues

Issue: Transactions outside expected date range appear

Cause: reportsPlusHours setting affecting date boundaries

Fix:

// Check current setting
$setting = $ProgramsettingDAO->load(1);
echo "reportsPlusHours: " . $setting->reportsPlusHours;

// Disable adjustment temporarily
$Programsetting->reportsPlusHours = null;

---

🧪 Testing Scenarios

Test Case 1: Basic Client-Product Report

1. Select active client with recent transactions
2. Select product with sales in date range
3. Set date range covering known transactions
4. Verify all bill types appear (sales, returns, combined)
5. Check stock quantities are accurate

Test Case 2: Date Range Filtering

1. Create transactions on specific dates
2. Test various date ranges (same day, month, year)
3. Verify reportsPlusHours adjustment works
4. Test edge cases (midnight, end of month)

Test Case 3: Union Query Accuracy

1. Create one transaction of each type
2. Run report covering all transactions
3. Verify each appears with correct billtype indicator
4. Check data consistency across union branches

Test Case 4: Performance with Large Datasets

1. Test with clients having 1000+ transactions
2. Monitor query execution time
3. Test with broad date ranges
4. Verify memory usage with large result sets

---

📚 Related Documentation

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

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur