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)

Table Name	Purpose	Key Columns
clientdebtchange	Customer debt transaction log	clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangetype, clientdebtchangedate, tablename
client	Customer master data	clientid, clientname, clientdebt, clientarea, userid
clientarea	Customer area/region groupings	id, name, description

### Sales Tables (Referenced)

Table Name	Purpose	Key Columns
sellbill	Sales bills	sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, conditions
sellbilldetail	Sales bill line items	sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice
returnsellbill	Sales return bills	returnsellbillid, returnsellbillclientid, returnsellbilltotalbill, returnsellbillaftertotalbill
returnsellbilldetail	Return bill details	returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity
sellbillandrutern	Combined sell & return	sellbillid, sellbillclientid, sellbillprice, returnsellbillprice, sellbilldate
sellandruternbilldetail	Combined bill details	sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, selltype

### Financial Tables

Table Name	Purpose	Key Columns
checkdeposit	Check deposits	checkdepositid, clientid, bankname, accountname, checkamount
save	Cash registers/safes	saveid, savename, savevalue
bills	Service bills	billid, clientid, productstotalprice, finalnetbillvalue
datedchecked	Post-dated checks	datedcheckedid, endorsePersonId, done

### Reference Tables

Table Name	Purpose	Key Columns
supplier	Supplier information	supplierid, suppliername
user	System users	userid, username, viewclients, viewbills, usergroupid
youtubelink	Tutorial links	youtubelinkid, title, url
programsettings	System configuration	programsettingsid, settingkey, settingvalue

---

🔑 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

┌─────────────────────────────────────────────────────────────┐

START: Select Customer & Date Range

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Validate Input Parameters

- Check clientid

- Parse date range

- Check user permissions

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Build SQL Query String

- Add client filter

- Add date range filter

- Add permission restrictions

- Handle match-only filter

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Query clientdebtchange Table

- Get all transactions for client/date

- Order by specified criteria

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Process Each Transaction

FOR EACH debt change record:

│

→ Identify source table (sellbill, payment, etc.)

│

→ Load source document details

│ ├─ Bill totals

│ ├─ Payment amounts

│ │ └─ Related party info │

│

→ Generate link to source document

│

│ └─→ Calculate running balance │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

5Apply Net View Processing (if requested)

- Remove offsetting entries

- Recalculate running balances

- Clean up display names

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

6Generate Report Output

- Assign data to Smarty template

- Include totals and summaries

- Display via clientShow.html

└─────────────────────────────────────────────────────────────┘

---

Workflow 2: Area-Based Customer Summary

┌─────────────────────────────────────────────────────────────┐

START: Select Customer Area

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Load All Customer Areas

- Query clientarea table

- Apply area filter if specified

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Process Each Area

FOR EACH customer area:

│

→ Query customers in this area

│

→ Calculate area debt total

│ │ └─ Sum clientdebt for all customers │

│

→ Assign to template variables

│ ├─ clientShowData{N}

│ │ └─ sum{N} │

│

│ └─→ Add to grand total │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Generate Area Summary Report

- Display all areas with totals

- Show grand total across areas

- Present via clientShowByArea.html

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty) or `do=show`	Default action	Individual customer report
`do=clientShowByArea`	Area grouping	Customer reports grouped by area
`do=clientShowforsellonly`	`clientShowforsellonly()`	Sales transactions only
`do=clientShow2`	`clientShow2()`	Alternative customer view format
`do=all`	`showAllOperations()`	Comprehensive operations report

### Required Parameters by Action

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

📚 ERP Docs

ClientReports Documentation

Client Reports Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

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

2. clientShow() - Core Customer Report Logic

3. clientShowByArea() - Area-Based Customer Summary

4. clientShowforsellonly() - Sales Transaction Report

5. showAllOperations() - Comprehensive Operations Report

6. showSellBillsByClientAndDate() - Sales Bills Query

7. showReturnSellBillsByClientAndDate() - Returns Query

8. getClientData() - Customer Dropdown Data

🔄 Workflows

Workflow 1: Individual Customer Report Generation

Workflow 2: Area-Based Customer Summary

🌐 URL Routes & Actions

🧮 Calculation Methods

Debt Balance Calculation

Discount Processing

Tax Calculation

Net View Processing

🔒 Security & Permissions

User Permission Levels

Input Sanitization

📊 Performance Considerations

Database Optimization Tips

Known Performance Issues

🐛 Common Issues & Troubleshooting

1. Missing Transaction Links

2. Incorrect Running Totals

3. Area Totals Don't Match

4. Date Range Issues

🧪 Testing Scenarios

Test Case 1: Basic Customer Report

Test Case 2: Area Summary Accuracy

Test Case 3: Permission Enforcement

Debug Mode Enable

📚 Related Documentation