Client Buys Controller Documentation

File: /controllers/clientbuysController.php

Purpose: Customer purchasing analysis and reporting system for tracking customer buy transactions, payments, and debt changes

Last Updated: December 20, 2024

Total Functions: 2 main actions

Lines of Code: ~473

---

📋 Overview

The Client Buys Controller provides comprehensive analysis of customer purchasing activity, focusing on buy bills (purchases made by customers), return buy bills, and associated payment tracking. It offers detailed reporting capabilities for customer purchase patterns and payment behavior analysis.

Primary Functions

✅ Customer purchase transaction analysis (buy bills and return buy bills)
✅ Payment tracking for customer purchases with dual view modes
✅ Debt change analysis with chronological ordering
✅ Date range filtering with configurable time offset support
✅ Customer-specific purchase history and payment patterns
✅ Running balance calculations for customer debt changes
✅ Integration with program settings for time zone adjustments

Related Controllers

• buyBillController.php - Purchase bill creation and management
• returnBuyBillController.php - Return purchase bill management
• clientReportsController.php - Comprehensive client reporting
• clientPayedDeptController.php - Customer debt payment processing

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
buybill	Purchase bills master	buybillid, client, buybilldate, buybillaftertotalbill, conditions
returnbuybill	Return purchase bills	returnbuybillid, client, returnbuybilldate, returnbuybillaftertotalbill, conditions
clientdebtchange	Customer debt transactions	clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangetype, clientdebtchangedate, tablename, dailyentryid
savedaily	Daily cash register operations	savedailyid, savedailymodelid, tablename, savedailydate

### Reference Tables

Table Name	Purpose	Key Columns
client	Customer master data	clientid, clientname
programsettings	System configuration	programsettingsid, reportsPlusHours
youtubelink	Tutorial/help links	youtubelinkid, title, url

---

🔑 Key Functions

1. show / Default Action - Customer Purchase Analysis

Location: Lines 233-450

Purpose: Comprehensive analysis of customer purchasing activity with configurable reporting modes

Function Signature:

// Parameters: client, showWhat, from, to
$clientId = (int) $_REQUEST['client'];
$showWhat = (int) $_REQUEST['showWhat']; // 0=payments only, 1=all moves
$from = $_REQUEST['from'];
$to = $_REQUEST['to'];

Reporting Modes:

• showWhat = 0: Payments only (filtered payment transactions)
• showWhat = 1: All debt movements (comprehensive transaction history)

Time Offset Support:

if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
}

---

2. Payment-Only Analysis (showWhat = 0)

Location: Lines 390-397

Purpose: Focus on payment transactions related to customer purchases

Advanced Payment Query:

SELECT clientdebtchangeid, paySerialNo, clientdebtchangedate, clientdebtchangebefore,
       clientdebtchangeamount, clientdebtchangeafter, clientdebtchangetype, del,
       clientdebtchange.dailyentryid, clientdebtchange.tablename, clientdebtchangemodelid, paytype
FROM clientdebtchange
JOIN savedaily ON (
    (savedaily.savedailymodelid = clientdebtchange.clientdebtchangeid 
     AND savedaily.tablename IN('premiumController.php','clientPayedDeptController.php'))
    OR
    (savedaily.savedailymodelid = clientdebtchange.clientdebtchangemodelid 
     AND savedaily.tablename = 'clientPayedDeptSellBillsController.php')
) WHERE [filters]

Key Features:

• Links payment records with daily cash register operations
• Filters for specific payment controller types
• Maintains audit trail through daily entry connections

---

3. All Movements Analysis (showWhat = 1)

Location: Lines 398-436

Purpose: Complete debt change history with running balance calculations

Query Structure:

SELECT clientdebtchange.*, 
       CONCAT(tablename, clientdebtchangemodelid, clientdebtchangeamount, clientdebtchangetype) AS finalstring
FROM clientdebtchange
WHERE [filters]
ORDER BY clientdebtchangedate DESC

Running Balance Calculation:

$clientDebtChange = $clientDebtChangeData;
$clientDebtChangeData = array();

foreach ($clientDebtChange as $mov) {
    $mov['processname'] = str_replace("(معدله)", "", $mov['processname']);
    
    if ($ii == 0) {
        // First record - use actual values
        $startvalue = $mov['clientdebtchangeamount'];
        $startbefore = $mov['clientdebtchangebefore'];
        $startafter = $mov['clientdebtchangeafter'];
    } else {
        // Subsequent records - calculate running balance
        $startvalue = $mov['clientdebtchangeamount'];
        $mov['clientdebtchangeafter'] = $startbefore;
        
        if ($mov['clientdebtchangetype'] == "1") {
            // Payment (reduces debt)
            $mov['clientdebtchangebefore'] = $startbefore + $startvalue;
            $startbefore = $startbefore + $startvalue;
        } else {
            // Charge (increases debt)
            $mov['clientdebtchangebefore'] = $startbefore - $startvalue;
            $startbefore = $startbefore - $startvalue;
        }
    }
    $ii++;
    array_push($clientDebtChangeData, $mov);
}

---

4. Purchase Data Loading

Location: Lines 299-300

Purpose: Load customer purchase bills and return bills

Data Queries:

// Purchase bills
$buyBillData = R::getAll('SELECT buybillid, buybilldate, buybillaftertotalbill 
                         FROM buybill 
                         WHERE conditions=0 ' . $querySrtingBuy);

// Return purchase bills  
$retBuyBillData = R::getAll('SELECT returnbuybillid, returnbuybilldate, returnbuybillaftertotalbill 
                            FROM returnbuybill 
                            WHERE conditions=0 ' . $querySrtingRetBuy);

---

🔄 Workflows

Workflow 1: Customer Purchase Analysis

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

START: Select Customer & Analysis Type

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

▼

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

1Configure Analysis Parameters

- Select customer ID

- Choose reporting mode (payments vs all movements)

- Set date range with time offset support

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

▼

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

2Apply Program Settings

- Load reportsPlusHours for time zone adjustment

- Adjust date range with time offsets

- Build customer-specific message

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

▼

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

3Load Purchase Transaction Data

- Query buy bills with filters

- Query return buy bills with filters

- Apply conditions = 0 filter (active bills only)

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

▼

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

4Process Based on Analysis Mode

IF showWhat = 0 (Payments Only):

→ Query payment records with savedaily joins

→ Filter for specific payment controllers

│ └─→ Link payments to cash register operations │

ELSE IF showWhat = 1 (All Movements):

→ Query all debt change records

→ Calculate running balance chronologically

→ Process debt increase/decrease logic

│ └─→ Generate complete transaction timeline │

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

▼

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

5Calculate Running Balances (All Movements Mode)

FOR EACH debt change record:

→ Clean process names (remove edit markers)

├─→ IF first record: use actual values

→ ELSE: calculate based on previous record

→ Apply debt type logic (increase vs decrease)

│ └─→ Update running balance totals │

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

▼

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

6Display Customer Purchase Analysis

- Show purchase bills and return bills

- Display payment or movement data based on mode

- Include running balance calculations if applicable

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=show` or `do=` (empty)	Default action	Customer purchase analysis
`do=sucess`	success page	Success confirmation
`do=error`	error page	Error display

### Required Parameters

Customer Purchase Analysis:

• client - Customer ID (required for meaningful analysis)
• showWhat - Analysis mode (0=payments only, 1=all movements)
• from - Start date (YYYY-MM-DD, defaults to today if empty)
• to - End date (YYYY-MM-DD, defaults to today if empty)

---

🧮 Calculation Methods

Time Zone Adjustment for Date Filtering

// Apply program-specific time offset
if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24; // Add 24 for end-of-day
    $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
    $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
} else {
    // Standard time formatting
    $from = $from . " 00:00:00";
    $to = $to . ' 23:59:59';
}

Running Balance Calculation for Debt Changes

// Chronological processing
foreach ($clientDebtChange as $mov) {
    if ($ii == 0) {
        // Initialize with first record
        $startvalue = $mov['clientdebtchangeamount'];
        $startbefore = $mov['clientdebtchangebefore'];
        $startafter = $mov['clientdebtchangeafter'];
    } else {
        // Calculate running balance
        $mov['clientdebtchangeafter'] = $startbefore;
        
        if ($mov['clientdebtchangetype'] == "1") {
            // Payment type - increases balance before
            $mov['clientdebtchangebefore'] = $startbefore + $startvalue;
            $startbefore = $startbefore + $startvalue;
        } else {
            // Charge type - decreases balance before
            $mov['clientdebtchangebefore'] = $startbefore - $startvalue;
            $startbefore = $startbefore - $startvalue;
        }
    }
}

---

🔒 Security & Permissions

Authentication Requirements

• All actions require authentication via include_once("../public/authentication.php")
• User session validation before customer purchase data access

Data Access Control

• Customer data filtered by user permissions
• Purchase bill access controlled by user group settings
• Audit trail maintained through daily entry ID tracking

---

📊 Performance Considerations

Database Optimization

1. Conditional Filtering: Uses conditions = 0 to filter active bills only

2. Date Range Indexing: Requires proper indexing on date fields

3. Customer-Specific Queries: Efficient filtering by customer ID

Required Indexes:

• buybill(client, buybilldate, conditions) for purchase analysis
• returnbuybill(client, returnbuybilldate, conditions) for return analysis
• clientdebtchange(clientid, clientdebtchangedate) for debt tracking
• savedaily(savedailymodelid, tablename) for payment linking

Memory Management

// Process debt changes in single loop to minimize memory usage
$clientDebtChange = $clientDebtChangeData;
$clientDebtChangeData = array(); // Reset for processed data

---

🐛 Common Issues & Troubleshooting

1. Incorrect Running Balance Calculations

Issue: Running balances don't match expected values

Cause: Debt change type processing logic errors

Debug Steps:

// Verify debt change type logic
echo "Type: " . $mov['clientdebtchangetype'];
echo "Amount: " . $mov['clientdebtchangeamount'];
echo "Before: " . $mov['clientdebtchangebefore'];
echo "After: " . $mov['clientdebtchangeafter'];

2. Missing Purchase Data

Issue: Purchase bills not appearing in analysis

Cause: Date range filtering or conditions flag issues

Fix: Check date formatting and conditions:

-- Verify bill conditions
SELECT buybillid, conditions, buybilldate 
FROM buybill 
WHERE client = [CUSTOMER_ID];

-- Check date range formatting
-- Ensure dates include proper time components

3. Time Zone Offset Problems

Issue: Transactions appearing outside expected date range

Cause: Time offset calculation errors

Solution: Verify program settings:

SELECT reportsPlusHours FROM programsettings WHERE programsettingsid = 1;

---

🧪 Testing Scenarios

Test Case 1: Payment-Only Mode Accuracy

1. Create customer with mixed transaction types
2. Set showWhat = 0 (payments only)
3. Verify only payment records appear
4. Check savedaily integration for payment tracking

Test Case 2: Running Balance Calculations

1. Create customer with sequence of debt changes
2. Set showWhat = 1 (all movements)
3. Verify running balance calculations manually
4. Check chronological order maintenance

Test Case 3: Time Zone Offset Handling

1. Set reportsPlusHours in program settings
2. Create transactions at day boundaries
3. Run report with date range
4. Verify proper time offset application

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• buyBillController.md - Purchase bill management
• clientReportsController.md - Comprehensive client reporting

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Clientbuys Documentation

Client Buys Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show / Default Action - Customer Purchase Analysis

2. Payment-Only Analysis (showWhat = 0)

3. All Movements Analysis (showWhat = 1)

4. Purchase Data Loading

🔄 Workflows

Workflow 1: Customer Purchase Analysis

🌐 URL Routes & Actions

🧮 Calculation Methods

Time Zone Adjustment for Date Filtering

Running Balance Calculation for Debt Changes

🔒 Security & Permissions

Authentication Requirements

Data Access Control

📊 Performance Considerations

Database Optimization

Memory Management

🐛 Common Issues & Troubleshooting

1. Incorrect Running Balance Calculations

2. Missing Purchase Data

3. Time Zone Offset Problems

🧪 Testing Scenarios

Test Case 1: Payment-Only Mode Accuracy

Test Case 2: Running Balance Calculations

Test Case 3: Time Zone Offset Handling

📚 Related Documentation