Client Reports New Controller Documentation

File: /controllers/clientReportsnewController.php

Purpose: Generates enhanced customer account reports with detailed transaction views and special output formats

Last Updated: December 20, 2024

Total Functions: 4

Lines of Code: ~1,051

---

📋 Overview

The Client Reports New Controller is an enhanced version of the standard client reporting system that provides specialized customer account analysis with multiple view formats. It handles:

• • Individual customer debt reports with enhanced formatting
• • Customer transaction history with special "bure" (net) view processing
• • Detailed customer transaction reports with product-level breakdown
• • YouTube tutorial integration for user guidance
• • Enhanced data presentation for different output formats
• • Advanced transaction linking and calculation methods

Primary Functions

• ✅ Generate enhanced customer debt reports (clientShow2)
• ✅ Specialized "bure" view customer reports (clientShowbure)
• ✅ Detailed customer reports with product breakdown (clientShowDetail)
• ✅ YouTube tutorial integration
• ✅ Advanced transaction processing and linking
• ✅ Enhanced calculation methods with better accuracy
• ✅ Multi-format report output support
• ✅ Complex debt balance calculations with net view processing

Related Controllers

• • clientReportsController.php - Standard customer reports
• • sellbillController.php - Sales operations
• • clientController.php - Customer management
• • returnsellbillController.php - Sales returns
• • buyBillController.php - Purchase operations
• • bills.php - Service billing

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action (clientShow2) - Enhanced Customer Report

Location: Line 141

Purpose: Generate comprehensive customer debt report with enhanced processing and formatting

Function Signature:

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

Process Flow:

1. Load customer data for dropdown population

2. Validate input parameters (clientid and date range)

3. Load customer details for report header message

4. Build header message with customer name and current debt

5. Call clientShow2() function for data processing

6. Display via clientShow22.html template

Features:

• • Enhanced message formatting with customer info
• • Client debt display in header
• • Date range display in header
• • Integration with customer selection dropdown

---

2. clientShow2() - Core Enhanced Report Logic

Location: Line 240

Purpose: Build detailed customer debt history with enhanced transaction linking and calculations

Function Signature:

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

Process Flow:

1. Build dynamic SQL query string with WHERE clause construction

2. Apply client ID filter if specified

3. Apply date range filter if provided

4. Clean up query string (remove trailing AND/WHERE)

5. Query clientdebtchange table via extended DAO

6. Process each transaction record:

- Identify source table and load related data

- Calculate totals, discounts, and payments

- Build navigation links to source documents

- Handle special cases for different transaction types

7. Calculate running totals and assign to template

Transaction Types Handled:

• • sellbillController.php - Sales bills with full detail loading
• • returnsellbillController.php - Sales returns with negative amounts
• • sellbillandruternController.php - Combined operations
• • kempialaController.php - Promissory notes
• • depositcheckController.php - Check deposits
• • clientPayedDeptController.php - Customer payments
• • clientController.php - Client account adjustments
• • billreceiptController.php - Bill receipts
• • clientDeficitController.php - Client deficit adjustments
• • bills.php - Service bills
• • billsreturn.php - Service bill returns
• • clientPayedDeptReportController.php - Payment reports

Enhanced Calculations:

// Total operation cost calculation
$data->totalOperationCost = $sellBillData->sellbilltotalbill + $data->clientdebtchangebefore;

// Discount calculation
$data->discount = ($sellBillData->sellbilltotalbill - $sellBillData->sellbillaftertotalbill);

// Payment amount assignment
$data->payedamount = $sellBillData->sellbilltotalpayed;

---

3. clientShowbure() - Enhanced Net View Report

Location: Line 545

Purpose: Generate customer report with advanced "bure" (net) view processing and balance recalculation

Function Signature:

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

Process Flow:

1. Execute same query logic as clientShow2()

2. Process all transaction types with enhanced logic

3. Apply special "bure" view processing:

- Identify paired transactions using finalstring matching

- Hide offsetting entries for cleaner view

- Recalculate running balances from scratch

- Clean up process names (remove modification markers)

4. Recalculate debt progression:

- Set initial balance to 0

- Process each visible transaction in sequence

- Apply correct addition/subtraction based on transaction type

- Update before/after amounts for accurate tracking

Advanced Bure Processing:

// Hide paired transactions
if ($type == 1) {
    $otherfinal = substr_replace($finalstring, '0', -1);
    foreach ($shownData as $xx) {
        if ($xx->finalstring == $otherfinal) {
            $xx->clientdebtchangeid = -1; // Hide from display
            $datax->clientdebtchangeid = -1;
        }
    }
}

// Recalculate running balances
if ($mov->clientdebtchangetype == "1") {
    $mov->clientdebtchangeafter = $startbefore - $startvalue; // Payment decreases debt
    $startbefore = $startbefore - $startvalue;
} else {
    $mov->clientdebtchangeafter = $startbefore + $startvalue; // Sale increases debt
    $startbefore = $startbefore + $startvalue;
}

Special Features:

• • Process name cleanup: str_replace("(معدله)", "", $mov->processname)
• • Enhanced payment handling for combined bills
• • Negative payment amounts for returns: $data->payedamount = $returnSellBillData->returnsellbilltotalpayed * -1

---

4. clientShowDetail() - Detailed Report with Product Breakdown

Location: Line 848

Purpose: Generate comprehensive customer report with product-level transaction details

Function Signature:

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

Process Flow:

1. Build query with simplified WHERE clause (WHERE 1 + conditions)

2. Query customer debt changes with date/client filters

3. Process each transaction and load detailed product information:

- For sales bills: Load sellbilldetail data

- For return bills: Load returnsellbilldetail data

- For combined bills: Load sellandruternbilldetail data

4. Store product details in indexed arrays to avoid duplicate queries

5. Assign all data arrays to template for detailed display

Product Detail Loading:

// Avoid duplicate queries with array indexing
if(!array_key_exists($data->clientdebtchangemodelid, $productsArr)){
    $productsArr[$data->clientdebtchangemodelid] = $mySellbilldetailEx->queryWithSellBillId($data->clientdebtchangemodelid);
}

if(!array_key_exists($data->clientdebtchangemodelid, $productsReArr)){
    $productsReArr[$data->clientdebtchangemodelid] = $myReturnsellbilldetailEx->queryWithReturnsellbillId($data->clientdebtchangemodelid);
}

if(!array_key_exists($data->clientdebtchangemodelid, $productsBuyAndReArr)){
    $productsBuyAndReArr[$data->clientdebtchangemodelid] = $mySellandruternbilldetailEx->queryWithSellBillIdwithoutSellType($data->clientdebtchangemodelid);
}

Template Variables:

• • $shownData - Main transaction data
• • $productsArr - Sales bill product details
• • $productsReArr - Return bill product details
• • $productsBuyAndReArr - Combined operation product details
• • $totalsum - Grand total of all operations

---

5. getClientData() - Customer Data Loader

Location: Line 232

Purpose: Load customer list for report selection dropdowns

Function Signature:

function getClientData()

Returns: Array of customer objects via $clientExt->queryAllsup()

---

🔄 Workflows

Workflow 1: Enhanced Customer Report Generation

---

Workflow 2: Detailed Product-Level Report

---

🌐 URL Routes & Actions

Enhanced Customer Report (do=clientShow2):

• • clientid - Customer ID
• • from - Start date (YYYY-MM-DD)
• • to - End date (YYYY-MM-DD)

Bure View Report (do=clientShowbure):

• • clientid - Customer ID
• • from - Start date (YYYY-MM-DD)
• • to - End date (YYYY-MM-DD)

Detailed Report (do=clientShowDetail):

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

---

🧮 Calculation Methods

Enhanced Total Operation Cost

// For sales bills
$data->totalOperationCost = $sellBillData->sellbilltotalbill + $data->clientdebtchangebefore;

// For service bills  
$data->totalOperationCost = $billsData->productstotalprice + $data->clientdebtchangebefore;

// For payments/adjustments
$data->totalOperationCost = $data->clientdebtchangebefore;

Advanced Discount Calculation

// Sales bill discounts
$data->discount = ($sellBillData->sellbilltotalbill - $sellBillData->sellbillaftertotalbill);

// Combined bill discounts  
$data->discount = ($sellAndReturnBillData->sellbilltotalbill - $sellAndReturnBillData->sellbillaftertotalbill);

// Service bill discounts
$data->discount = ($billsData->productstotalprice - $billsData->finalnetbillvalue);

Enhanced Payment Processing

// Regular sales payments
$data->payedamount = $sellBillData->sellbilltotalpayed;

// Return payments (negative)
$data->payedamount = $returnSellBillData->returnsellbilltotalpayed * -1;

// Service bill payments (multiple sources)
$data->payedamount = $billsData->companycarry + $billsData->cardvalue + $billsData->cashvalue;

// Combined bill payment handling
if ($sellAndReturnBillData->sellbillaftertotalbill < 0) {
    $data->payedamount = $sellAndReturnBillData->sellbilltotalpayed * -1;
} else {
    $data->payedamount = $sellAndReturnBillData->sellbilltotalpayed;
}

Net View Balance Recalculation

// Initialize for sequential calculation
$startvalue = 0;
$startbefore = 0;
$startafter = 0;

foreach ($shownData as $mov) {
    if ($bure == "1" && $mov->clientdebtchangeid > 0) {
        if ($ii == 0) {
            // First transaction sets the base
            $startvalue = $mov->clientdebtchangeamount;
            $startbefore = 0;
            $mov->clientdebtchangebefore = $startbefore;
            $startafter = $startvalue;
            $mov->clientdebtchangeafter = $startafter;
            $startbefore = $startvalue;
        } else {
            // Subsequent transactions build on previous
            $startvalue = $mov->clientdebtchangeamount;
            $mov->clientdebtchangebefore = $startbefore;
            
            if ($mov->clientdebtchangetype == "1") {
                // Payment decreases debt
                $mov->clientdebtchangeafter = $startbefore - $startvalue;
                $startbefore = $startbefore - $startvalue;
            } else {
                // Sale increases debt  
                $mov->clientdebtchangeafter = $startbefore + $startvalue;
                $startbefore = $startbefore + $startvalue;
            }
        }
    }
}

---

📊 Performance Considerations

Database Optimization

1. Query Efficiency:

- Uses extended DAO methods for complex joins

- Implements indexed array caching to prevent duplicate product queries

- Simplified WHERE clause construction for better performance

2. Memory Management:

- Product detail arrays are indexed by transaction ID

- Prevents N+1 query problems with array_key_exists() checks

- Efficient template variable assignment

Performance Improvements Over Standard Controller

// Efficient product loading with caching
if(!array_key_exists($data->clientdebtchangemodelid, $productsArr)){
    $productsArr[$data->clientdebtchangemodelid] = $mySellbilldetailEx->queryWithSellBillId($data->clientdebtchangemodelid);
}

// vs. loading every time (inefficient)
$products = $mySellbilldetailEx->queryWithSellBillId($data->clientdebtchangemodelid);

---

🐛 Common Issues & Troubleshooting

1. Bure View Balance Discrepancies

Issue: Net view calculations don't match expected balances

Cause: Paired transaction hiding or incorrect sequential processing

Debug:

// Enable debug output in bure processing
foreach ($shownData as $value) {
    print_r('<br>clientdebtchangeafter = '.$value->clientdebtchangeafter);
}

2. Missing Product Details

Issue: Detailed view doesn't show product breakdown

Cause: Extended DAO methods not returning data or array indexing issues

Debug:

-- Check if detail records exist
SELECT COUNT(*) FROM sellbilldetail WHERE sellbillid = [ID];
SELECT COUNT(*) FROM returnsellbilldetail WHERE returnsellbillid = [ID];

3. YouTube Links Not Loading

Issue: Tutorial videos don't appear in reports

Cause: YoutubeLink table empty or DAO query issues

Fix:

-- Verify tutorial data exists
SELECT * FROM youtubelink WHERE 1;

4. Template Variable Conflicts

Issue: Data not displaying properly in enhanced templates

Cause: Variable naming conflicts between different report types

Solution: Check template variable assignments match expected names:

• • clientShow22.html expects standard variables
• • clientShowbure.html expects burex flag
• • clientShowDetail.html expects product arrays

---

🧪 Testing Scenarios

Test Case 1: Enhanced Report Accuracy

1. Select customer with mixed transaction types
2. Compare totals with standard clientReportsController
3. Verify enhanced calculations match manual computation
4. Check all transaction links work properly

Test Case 2: Bure View Processing

1. Create paired transactions (sale + payment)
2. Run bure view report
3. Verify offsetting entries are hidden
4. Check balance recalculation is accurate
5. Confirm sequential balance progression

Test Case 3: Product Detail Loading

1. Select customer with product sales/returns
2. Run detailed report
3. Verify all product arrays populate
4. Check no duplicate queries occur
5. Confirm product data accuracy

Test Case 4: Performance Comparison

1. Test large customer with many transactions
2. Compare load times between standard and new controller
3. Monitor query count and memory usage
4. Verify enhanced features don't impact performance

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • clientReportsController.md - Standard customer reports
• • sellbillController.md - Sales operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur