Report Client And Supplier Controller Documentation

File: /controllers/reportClientAndSupplier.php

Purpose: Unified reporting for both customer and supplier debt transactions and history

Last Updated: December 21, 2024

Total Functions: 4

Lines of Code: ~741

---

📋 Overview

The Report Client And Supplier Controller provides comprehensive reporting capabilities for both customer and supplier accounts in a unified interface. It handles:

• • Individual client debt transaction reports
• • Individual supplier debt transaction reports
• • Transaction history with source document linking
• • Running balance calculations for both clients and suppliers
• • Date range filtering and sorting options
• • Payment tracking and debt change analysis
• • Combined client/supplier selection in single interface

Primary Functions

• ✅ Generate client debt transaction reports
• ✅ Generate supplier debt transaction reports
• ✅ Track transaction history with document links
• ✅ Calculate running balances and debt changes
• ✅ Date range filtering and sorting
• ✅ Payment and debt analysis
• ✅ Source document linking and navigation

Related Controllers

• • clientReportsController.php - Dedicated client reports
• • supplierReportsController.php - Dedicated supplier reports
• • sellbillController.php - Sales transactions
• • buyBillController.php - Purchase transactions
• • clientPayedDeptController.php - Customer payments
• • supplierPayedDeptController.php - Supplier payments

---

🗄️ Database Tables

Client Transaction Tables

🔑 Key Functions

1. Default Action - Combined Client/Supplier Report

Location: Line 249 (empty($do) or $do == "show")

Purpose: Main report interface handling both client and supplier debt reports

Function Signature:

// URL parameters: clientid, supplierid, from, to, order
$clientid = $_REQUEST['clientid'];
$supplierid = $_REQUEST['supplierid']; 
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$order = (int)$_REQUEST['order'];

Process Flow:

1. Include authentication check

2. Load client and supplier dropdown data

3. Process client report if client ID provided:

- Load client information

- Call clientShow() function

- Build message with client name

4. Process supplier report if supplier ID provided:

- Load supplier information

- Call supplierShow() function

- Build message with supplier name

5. Load YouTube tutorial links

6. Display combined report via template

Template Variables:

• • $clientData - Client dropdown options
• • $supplierData - Supplier dropdown options
• • $shownData - Client transaction data (if client selected)
• • $shownData2 - Supplier transaction data (if supplier selected)
• • $message - Report header with selected entity and date range
• • $youtubes - Tutorial video links

---

2. clientShow() - Client Transaction Report

Location: Line 312

Purpose: Generate detailed client debt transaction report with document linking

Function Signature:

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

Process Flow:

1. Build dynamic query string with filters:

- Client ID filter

- Date range filter (if provided)

- User permission restrictions

2. Execute query with sorting options:

- $order = 1: Sort by date descending

- Default: Sort by ID descending

3. Process each transaction record:

- Calculate running total based on transaction type

- Generate links to source documents

- Load related data (bill amounts, bank info, etc.)

4. Handle "bure" (net) view processing if requested

5. Assign data to Smarty template

Transaction Type Handling:

foreach ($shownData as $data) {
    if ($data->clientdebtchangetype == 0) {
        $total = $total + $data->clientdebtchangeamount; // Debt increase
    } else {
        $total = $total - $data->clientdebtchangeamount; // Payment/decrease
    }
    
    // Generate document links based on tablename
    if ($data->tablename == "sellbillController.php") {
        $data->link = "sellbillController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;
        // Load bill totals...
    }
    // ... additional transaction types
}

Document Link Generation:

• • sellbillController.php - Sales bills
• • returnsellbillController.php - Sales returns
• • depositcheckController.php - Check deposits with bank details
• • clientPayedDeptController.php - Customer payments
• • billreceiptController.php - Bill receipts
• • premiumController.php - Premium transactions

---

3. supplierShow() - Supplier Transaction Report

Location: Line 538

Purpose: Generate detailed supplier debt transaction report with document linking

Function Signature:

function supplierShow($supplierid, $startDate, $endDate, $order)

Process Flow:

1. Build query string similar to client report

2. Query supplier debt change table with filters

3. Process each transaction:

- Load related document data

- Generate appropriate links

- Calculate totals and amounts

4. Handle "bure" view processing for net display

5. Assign to $shownData2 template variable

Supplier Transaction Types:

• • buyBillController.php - Purchase bills
• • returnBuyBillController.php - Purchase returns
• • buyAndReturnBillController.php - Combined purchase bills
• • checkwithdrawalController.php - Check withdrawals with bank details
• • supplierPayedDeptController.php - Supplier payments
• • kempialaController.php - Promissory notes

Unique Supplier Features:

// Load bank information for check withdrawals
if ($data->tablename == "checkwithdrawalController.php") {
    $checkdata = $checkWithdrawalExtDAO->queryByIdExt($id);
    $data->bankname = $checkdata->bankname;
    $data->accountname = $checkdata->accountname;
}

---

4. getClientData() - Client Dropdown Data

Location: Line 298

Purpose: Load client data for report selection

Function Signature:

function getClientData()

Returns: Array of client objects with extended information using queryAllsup()

---

5. getSupplierData() - Supplier Dropdown Data

Location: Line 305

Purpose: Load supplier data for report selection

Function Signature:

function getSupplierData()

Returns: Array of all supplier objects using queryAll()

---

🔄 Workflows

Workflow 1: Combined Client/Supplier Report Generation

---

🌐 URL Routes & Actions

Combined Report:

• • clientid - Client ID for client report (optional)
• • supplierid - Supplier ID for supplier report (optional)
• • from - Start date filter (YYYY-MM-DD, optional)
• • to - End date filter (YYYY-MM-DD, optional)
• • order - Sort order (1=date desc, default=ID desc, optional)

Example URLs:

reportClientAndSupplier.php?clientid=123&from=2024-01-01&to=2024-01-31&order=1
reportClientAndSupplier.php?supplierid=456&from=2024-01-01&to=2024-01-31
reportClientAndSupplier.php?clientid=123&supplierid=456&from=2024-01-01&to=2024-01-31

---

🧮 Calculation Methods

Running Balance Calculation (Client)

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

Query String Building

$queryString = ' WHERE';

if (isset($clientid) && $clientid != '-1') {
    $queryString .= ' clientdebtchange.clientid =' . $clientid . ' AND';
}

if (!empty($startDate) && !empty($endDate)) {
    $queryString .= ' clientdebtchange.clientdebtchangedate >= "' . $startDate . '" AND clientdebtchange.clientdebtchangedate <= "' . $endDate . '" AND';
}

// Clean up trailing AND/WHERE
$arr = explode(' ', $queryString);
$lastWord = end($arr);
if ($lastWord == 'AND') {
    array_pop($arr);
    $queryString = implode(' ', $arr);
} else if ($lastWord == 'WHERE') {
    $queryString = ' ';
}

Net View Processing (Bure View)

if ($bure == "1") {
    foreach ($shownData as $datax) {
        $finalstring = $datax->finalstring;
        $type = $datax->clientdebtchangetype;
        
        if ($type == 1) { // Payment
            $otherfinal = substr_replace($finalstring, '0', -1);
            
            // Find and hide offsetting entries
            foreach ($shownData as $xx) {
                if ($xx->finalstring == $otherfinal) {
                    $xx->clientdebtchangeid = -1;
                    $datax->clientdebtchangeid = -1;
                }
            }
        }
    }
}

---

🔒 Security & Permissions

Authentication

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

• • Requires user authentication before accessing reports
• • No specific permission levels for clients vs suppliers

Input Sanitization

• • Parameters used directly in SQL queries
• • Should implement proper sanitization and validation
• • Integer casting used for order parameter: (int)$_REQUEST['order']

Recommended Security Improvements

// Sanitize IDs
$clientid = filter_input(INPUT_REQUEST, 'clientid', FILTER_VALIDATE_INT);
$supplierid = filter_input(INPUT_REQUEST, 'supplierid', FILTER_VALIDATE_INT);

// Validate dates
if (!empty($startDate) && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) {
    $startDate = '';
}

---

📊 Performance Considerations

Database Optimization

1. Indexes Required:

- clientdebtchange(clientid, clientdebtchangedate)

- supplierdebtchange(supplierid, supplierdebtchangedate)

- sellbill(sellbillid) for document linking

- buybill(buybillid) for document linking

2. Query Performance:

- Date range queries can be expensive without proper indexing

- Document linking requires additional queries for each transaction

- Running balance calculation done in PHP rather than SQL

Performance Notes

• • Multiple individual queries for loading related document data
• • Could benefit from JOIN queries to reduce database round trips
• • No pagination for large transaction histories

---

🐛 Common Issues & Troubleshooting

1. Missing Document Links

Issue: Transaction shows "#" instead of proper link

Cause: clientdebtchangemodelid = -1 or unrecognized tablename

Debug:

-- Check for invalid model IDs
SELECT tablename, COUNT(*) 
FROM clientdebtchange 
WHERE clientdebtchangemodelid = -1 
GROUP BY tablename;

-- Check for unrecognized table names
SELECT DISTINCT tablename 
FROM clientdebtchange 
WHERE tablename NOT IN ('sellbillController.php', 'clientPayedDeptController.php', ...);

2. Incorrect Running Balances

Issue: Balance calculations don't match expected values

Cause: Missing transactions or incorrect transaction types

Debug:

-- Verify transaction types
SELECT clientdebtchangetype, COUNT(*) 
FROM clientdebtchange 
WHERE clientid = [ID]
GROUP BY clientdebtchangetype;

-- Check for orphaned transactions
SELECT * FROM clientdebtchange 
WHERE clientdebtchangemodelid NOT IN (
    SELECT sellbillid FROM sellbill
) AND tablename = 'sellbillController.php';

3. Date Range Issues

Issue: No results for valid date ranges

Cause: Date format mismatch or timezone problems

Fix: Ensure consistent date formatting and add time components:

if (!empty($startDate)) $startDate .= " 00:00:00";
if (!empty($endDate)) $endDate .= " 23:59:59";

---

🧪 Testing Scenarios

Test Case 1: Client Report Generation

1. Select client with known transactions
2. Set date range covering transactions
3. Verify transaction list and running balance
4. Test document links functionality
5. Verify net view (bure) calculations

Test Case 2: Supplier Report Generation

1. Select supplier with purchase history
2. Apply date filters
3. Verify supplier-specific transaction types
4. Test check withdrawal bank information display
5. Verify payment tracking accuracy

Test Case 3: Combined Client/Supplier Report

1. Select both client and supplier
2. Verify both reports display simultaneously  
3. Test with overlapping date ranges
4. Verify proper data separation
5. Test message generation with both entities

Test Case 4: Sorting and Filtering

1. Test different sort orders (date vs ID)
2. Apply various date range combinations
3. Test edge cases (same start/end date)
4. Verify filter reset functionality

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • clientReportsController.md - Detailed client reporting
• • sellbillController.php - Sales transaction creation
• • buyBillController.php - Purchase transaction creation

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur