Client Reports Controller Documentation

File: /controllers/clientReportsController.php

Purpose: Generates comprehensive reports for customer accounts, debt tracking, and transaction history

Last Updated: December 20, 2024

Total Functions: 12+

Lines of Code: ~1,458

---

📋 Overview

The Client Reports Controller is a specialized reporting module that provides detailed customer account analysis and debt tracking capabilities. It handles:

• • Individual customer debt reports
• • Customer transaction history
• • Area-based customer grouping reports
• • Sales-only customer reports
• • Combined operation reports (sales, returns, payments)
• • Customer debt change tracking
• • Multi-format report output (HTML, potentially Excel/PDF)
• • Date range filtering and analysis
• • Payment history tracking

Primary Functions

• ✅ Generate customer debt reports
• ✅ Track customer payment history
• ✅ Area-based customer reporting
• ✅ Sales transaction analysis
• ✅ Combined operation reports
• ✅ Date range filtering
• ✅ Debt balance calculations
• ✅ Transaction linking to source documents
• ✅ Multi-customer area summaries
• ✅ Payment vs invoice analysis

Related Controllers

• • sellbillController.php - Sales operations
• • clientController.php - Customer management
• • returnsellbillController.php - Sales returns
• • clientPayedDeptController.php - Payment processing
• • depositcheckController.php - Check deposits
• • billreceiptController.php - Bill receipts

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Individual Customer Report

Location: Line 208

Purpose: Generate comprehensive debt and transaction report for a specific customer

Function Signature:

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

Process Flow:

1. Validate user permissions (viewclients check)

2. Parse date parameters (supports "month" shortcut)

3. Load customer data for report header

4. Call clientShow() with parameters

5. Load YouTube tutorial links

6. Display via clientShow.html template

Features:

• • Date range filtering
• • Monthly quick filter (find=month)
• • User permission filtering
• • Order/sorting options
• • Match-only filtering for account reconciliation

---

2. clientShow() - Core Customer Report Logic

Location: Line 443

Purpose: Build detailed customer debt history with transaction linking

Function Signature:

function clientShow($clientid, $startDate, $endDate, $order)

Process Flow:

1. Build dynamic SQL query string based on filters

2. Apply user permission restrictions

3. Query clientdebtchange table with date/client filters

4. Process each transaction record:

- Link to source documents (bills, payments, checks)

- Calculate running totals

- Load related data (supplier names, bank info)

5. Handle "bure" (net) view calculation

6. Assign data to Smarty template

Transaction Types Handled:

• • sellbillController.php - Sales bills
• • returnsellbillController.php - Sales returns
• • sellbillandruternController.php - Combined bills
• • depositcheckController.php - Check deposits
• • clientPayedDeptController.php - Customer payments
• • kempialaController.php - Promissory notes
• • billreceiptController.php - Bill receipts
• • transferclientccountsController.php - Account transfers
• • dailyentry.php - Journal entries

Key Variables:

• • $total - Running debt balance
• • $queryString - Dynamic WHERE clause
• • $orderBy - Sort specification
• • $searchDate - Date field to filter on (sysDate vs userSelectedDate)
• • $matchOnly - Filter for reconciliation entries only

---

3. clientShowByArea() - Area-Based Customer Summary

Location: Line 243

Purpose: Group customers by area and show debt summaries by region

Process Flow:

1. Load all customer areas

2. Build query string with area filter

3. For each area:

- Query customers in that area

- Sum total debt per area

- Assign area data to template variables

4. Calculate grand total across all areas

5. Display via clientShowByArea.html

Template Variables:

• • $clientShowData{N} - Customer data for area N
• • $sum{N} - Debt total for area N
• • $totalsum - Grand total across all areas
• • $allclientarea - Area master data

---

4. clientShowforsellonly() - Sales Transaction Report

Location: Line 733

Purpose: Focus specifically on sales transactions with product detail

Function Signature:

function clientShowforsellonly($clientid, $startDate, $endDate)

Process Flow:

1. Query customer debt changes filtered for sales operations

2. For each sales bill type:

- Load bill master data

- Load detailed line items

- Calculate quantities and totals

- Handle discount and tax calculations

3. Aggregate totals:

- $allPayed - Total sales amount

- $allPcsPlus - Total quantity sold

- $allPcsMinus - Total quantity returned

- $allTax - Total tax amount

Sales Bill Types:

• • Regular sales (sellbillController.php)
• • Sales returns (returnsellbillController.php)
• • Combined bills (sellbillandruternController.php)

Discount Calculation:

if ($sellBillData->sellbilldiscounttype == 1)
    $discount = $sellBillData->sellbilldiscount; // Fixed amount
else
    $discount = $sellBillData->sellbilltotalbill * $sellBillData->sellbilldiscount / 100; // Percentage

---

5. showAllOperations() - Comprehensive Operations Report

Location: Line 1192

Purpose: Combined view of all customer operations (sales, returns, payments)

Function Signature:

function showAllOperations($clientid, $startDate, $endDate)

Process Flow:

1. Call showSellBillsByClientAndDate() for sales data

2. Call showReturnSellBillsByClientAndDate() for returns data

3. Query clientdebtchange for other operations

4. Cross-reference and eliminate double-counting

5. Calculate net payment totals

6. Assign all data arrays to template

Data Arrays:

• • $sellbillData - Sales bills with quantities
• • $returnsellbillData - Return bills
• • $sellBillAndReturnData - Combined bills
• • $clientShowData - Payment/debt transactions
• • $totalPrice - Net payment total

Double-counting Prevention:

// Subtract bill payments already counted in debt changes
foreach ($sellbillData as $data) {
    if ($data->sellbilltotalpayed != 0 && $data->conditions == 0) {
        $totalPrice -= $data->sellbilltotalpayed;
    }
}

---

6. showSellBillsByClientAndDate() - Sales Bills Query

Location: Line 1285

Purpose: Retrieve and process sales bills for a customer/date range

Function Signature:

function showSellBillsByClientAndDate($clientid, $startDate, $endDate)

Process Flow:

1. Query sellbill table with client/date filters

2. Query sellbillandrutern table for combined bills

3. For each bill:

- Count product quantities via detail tables

- Apply discount calculations

- Filter out cancelled bills (conditions != 0)

4. Merge datasets and return combined array

Discount Processing:

if ($sellbilldiscounttype == 1) {
    $sellbillprice = ($bill->sellbillprice) - ($bill->sellbilldiscount); // Fixed
} else {
    $sellbillprice = ($bill->sellbillprice) - ((($bill->sellbillprice) * ($bill->sellbilldiscount)) / 100); // Percentage
}

---

7. showReturnSellBillsByClientAndDate() - Returns Query

Location: Line 1370

Purpose: Retrieve and process return bills for analysis

Function Signature:

function showReturnSellBillsByClientAndDate($clientid, $startDate, $endDate)

Process Flow:

1. Query returnsellbill table

2. Query sellbillandrutern table for return portions

3. Count returned quantities via detail tables

4. Sort results by date

5. Return array with both datasets

Return Value:

$returnsellbill = array($returnsellbillData, $sellbillandruternData);
// [0] = Pure return bills
// [1] = Combined bill return portions

---

8. getClientData() - Customer Dropdown Data

Location: Line 427

Purpose: Load customer list for report selection dropdowns

Function Signature:

function getClientData()

Returns: Array of customer objects for dropdown population

---

🔄 Workflows

Workflow 1: Individual Customer Report Generation

---

Workflow 2: Area-Based Customer Summary

---

🌐 URL Routes & Actions

Individual Customer Report (do=show):

• • clientid - Customer ID
• • from - Start date (YYYY-MM-DD)
• • to - End date (YYYY-MM-DD)
• • find - Optional: "month" for current month
• • order - Sort order (1=date desc, 2=ID asc, 3=date asc)

Area Report (do=clientShowByArea):

• • clientareaid - Area ID (-1 for all areas)

Sales Only (do=clientShowforsellonly):

• • clientid - Customer ID
• • from - Start date
• • to - End date

All Operations (do=all):

• • clientid - Customer ID
• • from - Start date (optional)
• • to - End date (optional)

---

🧮 Calculation Methods

Debt Balance Calculation

foreach ($shownData as $data) {
    if ($data->clientdebtchangetype == 0) { // Debt increase
        $total = $total + $data->clientdebtchangeamount;
    } else { // Debt decrease (payment)
        $total = $total - $data->clientdebtchangeamount;
    }
}

Discount Processing

// Fixed amount discount
if ($sellBillData->sellbilldiscounttype == 1) {
    $discount = $sellBillData->sellbilldiscount;
}
// Percentage discount  
else {
    $discount = $sellBillData->sellbilltotalbill * $sellBillData->sellbilldiscount / 100;
}

Tax Calculation

$tax = ($sellBillData->sellbilltotalbill - $discount) * $sellBillData->tax / 100;

Net View Processing

// Remove offsetting entries for cleaner view
if ($type == 1) { // Payment entry
    $otherfinal = substr_replace($finalstring, '0', -1);
    foreach ($shownData as $xx) {
        if ($xx->finalstring == $otherfinal) {
            $xx->clientdebtchangeid = -1; // Hide from display
            $datax->clientdebtchangeid = -1;
        }
    }
}

---

🔒 Security & Permissions

User Permission Levels

// ViewClients Permission Check
$userData = $myUserRecord->load($_SESSION['userid']);
if ($userData->viewclients == 0) {
    $queryString .= ' client.userid =' . $_SESSION['userid'] . ' AND';
}

Permission Levels:

• • viewclients = 0 - User can only see their own customers
• • viewclients = 1 - User can see all customers
• • viewbills = 0 - User can only see their own bills
• • viewbills = 2 - User can see bills from their user group

Input Sanitization

• • All $_REQUEST parameters are filtered through the framework
• • Numeric IDs cast to integer: (int) $_REQUEST['order']
• • Date strings validated before SQL inclusion
• • SQL injection prevented by DAO layer parameterized queries

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- clientdebtchange(clientid, clientdebtchangedate)

- client(clientarea)

- sellbill(sellbillclientid, sellbilldate)

- returnsellbill(returnsellbillclientid, returnsellbilldate)

2. Query Optimization:

- Date filtering with proper format: YYYY-MM-DD HH:MM:SS

- Use of efficient WHERE clauses

- Avoid N+1 queries by batching related data loads

3. Memory Management:

- Large date ranges may return thousands of records

- Consider pagination for very active customers

- Template variable cleanup for area reports

Known Performance Issues

-- This query can be slow for customers with many transactions
SELECT * FROM clientdebtchange 
WHERE clientid = ? 
AND clientdebtchangedate BETWEEN ? AND ?
ORDER BY clientdebtchangedate DESC;

-- Solution: Add composite index
CREATE INDEX idx_client_date ON clientdebtchange(clientid, clientdebtchangedate);

---

🐛 Common Issues & Troubleshooting

1. Missing Transaction Links

Issue: Transaction shows "#" link instead of proper document link

Cause: clientdebtchangemodelid = -1 or unrecognized tablename

Debug:

SELECT tablename, COUNT(*) FROM clientdebtchange 
WHERE clientid = [ID] GROUP BY tablename;

2. Incorrect Running Totals

Issue: Debt balance calculations don't match

Cause: Mixed transaction types or missing entries

Debug:

SELECT 
    SUM(CASE WHEN clientdebtchangetype = 0 THEN clientdebtchangeamount ELSE 0 END) as increases,
    SUM(CASE WHEN clientdebtchangetype = 1 THEN clientdebtchangeamount ELSE 0 END) as decreases
FROM clientdebtchange WHERE clientid = [ID];

3. Area Totals Don't Match

Issue: Area summaries don't equal individual customer totals

Cause: Customer area assignments changed or missing

Fix:

-- Check for customers without areas
SELECT * FROM client WHERE clientarea IS NULL OR clientarea = 0;

-- Verify area assignments
SELECT ca.name, COUNT(c.clientid) as customer_count, SUM(c.clientdebt) as total_debt
FROM clientarea ca
LEFT JOIN client c ON c.clientarea = ca.id
GROUP BY ca.id;

4. Date Range Issues

Issue: Reports show no data for valid date ranges

Cause: Date format mismatch or timezone issues

Fix:

// Ensure proper date format
if (!empty($startDate)) $startDate .= " 00:00:00";
if (!empty($endDate)) $endDate .= " 23:59:59";

---

🧪 Testing Scenarios

Test Case 1: Basic Customer Report

1. Select customer with recent transactions
2. Set date range covering known transactions  
3. Verify transaction count matches database
4. Check running balance calculation
5. Confirm links to source documents work

Test Case 2: Area Summary Accuracy

1. Create test customers in different areas
2. Add known debt amounts to each customer
3. Run area report
4. Verify area totals match individual sums
5. Check grand total accuracy

Test Case 3: Permission Enforcement

1. Login as restricted user (viewclients = 0)
2. Attempt to view other user's customers
3. Verify only own customers appear
4. Test with different permission levels

Debug Mode Enable

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

// Debug query string building
echo "Query String: " . $queryString . "<br>";

// Debug data arrays
echo "<pre>";
print_r($shownData);
echo "</pre>";

---

📚 Related Documentation

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

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur