Client Paid Debt Bills Controller Documentation

File: /controllers/clientPayedDeptBillsController.php

Purpose: Manages debt payment tracking for service bills (bills table) with detailed payment history and analysis

Last Updated: December 20, 2024

Total Functions: 3 main actions

Lines of Code: ~294

---

📋 Overview

The Client Paid Debt Bills Controller handles debt payment tracking specifically for service bills stored in the bills table. It provides comprehensive reporting on bill payment status, debt tracking, and payment history with detailed drill-down capabilities.

Primary Functions

✅ Service bill debt reporting with payment tracking
✅ Payment history analysis for individual bills
✅ Outstanding debt calculation and monitoring
✅ Client-specific bill payment summaries
✅ Date range filtering for payment analysis
✅ Detailed payment breakdown with dates and amounts
✅ Bill status tracking (paid vs unpaid portions)

Related Controllers

• billsController.php - Service bill creation and management
• clientController.php - Customer management
• clientPayedDeptController.php - General debt payments
• clientReportsController.php - Comprehensive client reports

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
bills	Service bills master	id, billno, clientid, finalnetbillvalue, waitvalue, cashvalue, cardvalue, companycarry, billdate
clientdebtchange	Payment tracking log	clientdebtchangeid, clientid, clientdebtchangeamount, billid, clientdebtchangedate, paytype

### Reference Tables

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

---

🔑 Key Functions

1. Default Action (empty $do) - Outstanding Bills Report

Location: Lines 78-159

Purpose: Display service bills with outstanding debt balances

Function Signature:

// Parameters: client, from, to, bill_no
$client = $_POST['client'];
$bill_no = $_POST['bill_no']; 
$start_date = $_REQUEST['from'];
$end_date = $_REQUEST['to'];

Process Flow:

1. Build WHERE clause based on filters

2. Query bills table for matching records

3. Calculate payment amounts from clientdebtchange

4. Filter bills with remaining debt (waitvalue > 0)

5. Display filtered results with payment totals

Key Features:

• Client-specific filtering
• Date range filtering on billdate
• Bill number exact match search
• Outstanding debt calculation: waitvalue - total_payments
• Payment total calculation: cashvalue + cardvalue + companycarry + payments

---

2. show - Paid Bills Summary

Location: Lines 160-226

Purpose: Show summary of bills that have received payments

Function Signature:

// Parameters: client, from, to, bill_no  
$client = $_POST['client'];
$bill_id = $_POST['bill_no'];
$start_date = $_REQUEST['from'];
$end_date = $_REQUEST['to'];

Process Flow:

1. Build WHERE clause for clientdebtchange table

2. Query bills that have payment records

3. Calculate totals for each bill

4. Generate summary with totals across all bills

Calculations:

$waitvalue = $Bill_detail->waitvalue - $dept_Bill_detail->clientdebtchangeamount;
$total_payed = $Bill_detail->cashvalue + $Bill_detail->cardvalue + 
               $Bill_detail->companycarry + $dept_Bill_detail->clientdebtchangeamount;

Template Variables:

• $total_bills - Sum of all bill final values
• $total_wait_bills - Sum of all remaining debt
• $total_payed_bills - Sum of all payments made

---

3. show_details - Individual Bill Payment History

Location: Lines 227-291

Purpose: Detailed payment breakdown for a specific bill

Function Signature:

$bill_no = $_GET['id']; // Bill number to analyze

Process Flow:

1. Load bill master data by bill number

2. Extract bill totals and client information

3. Query all payment records for this bill

4. Calculate running balance after each payment

5. Display chronological payment history

Running Balance Calculation:

if ($x == 1) {
    $wait_before = $waitvalue;
    $wait_aftre = $wait_before - ($cardvalue + $cashvalue + $companycarry + $clientdebtchangeamount);
} else {
    $wait_before = $wait_aftre;
    $wait_aftre = $wait_before - $clientdebtchangeamount;
}

Output Data:

• Sequential payment history with running totals
• Payment types and dates
• Remaining balance after each transaction
• Complete audit trail for bill settlement

---

🔄 Workflows

Workflow 1: Outstanding Bills Analysis

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

START: Filter Outstanding Bills

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

▼

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

1Apply Search Filters

- Client ID filter

- Date range filter

- Bill number filter

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

▼

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

2Query Bills Table

- Load bills matching criteria

- Extract bill totals and client info

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

▼

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

3Calculate Payment Amounts

FOR EACH bill:

│

→ Sum payments from clientdebtchange

│

→ Calculate remaining debt

│ │ └─ waitvalue - total_payments │

│

│ └─→ Include only bills with debt > 0 │

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

▼

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

4Display Outstanding Bills Report

- Show bills with remaining debt

- Include payment totals and balances

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

---

Workflow 2: Payment History Drill-Down

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

START: Select Bill for Details

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

▼

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

1Load Bill Master Data

- Get bill totals and amounts

- Load client information

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

▼

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

2Query Payment History

- Get all clientdebtchange records

- Order by payment date

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

▼

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

3Calculate Running Balances

FOR EACH payment:

│

→ Calculate balance before payment

│

→ Apply payment amount

│

│ └─→ Calculate balance after payment │

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

▼

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

4Display Payment Timeline

- Show chronological payment history

- Include running balance calculations

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default action	Outstanding bills report
`do=show`	show()	Paid bills summary
`do=show_details`	show_details()	Individual bill payment history

### Required Parameters by Action

Outstanding Bills (default):

• client - Customer ID (optional, -1 for all)
• from - Start date (YYYY-MM-DD, optional)
• to - End date (YYYY-MM-DD, optional)
• bill_no - Bill number for exact match (optional)

Paid Bills Summary (do=show):

• client - Customer ID (optional)
• bill_no - Bill number (optional)
• from - Start date for payment filter
• to - End date for payment filter

Payment Details (do=show_details):

• id - Bill number (required via GET)

---

🧮 Calculation Methods

Outstanding Debt Calculation

// For each bill, calculate remaining debt
$waitvalue = $cli_bill_val->waitvalue - $client_dept_change->clientdebtchangeamount;

// Only include bills with remaining debt
if ($waitvalue > 0) {
    // Include in outstanding bills report
}

Total Payment Calculation

$total_payed = $cashvalue + $cardvalue + $companycarry + $client_dept_change_amount;
// cashvalue: Cash payments on bill
// cardvalue: Card payments on bill  
// companycarry: Company credit applied
// client_dept_change_amount: Additional payments via debt system

Running Balance for Payment History

// First payment includes all bill payments
if ($x == 1) {
    $wait_before = $waitvalue;
    $wait_aftre = $wait_before - ($cardvalue + $cashvalue + $companycarry + $clientdebtchangeamount);
}
// Subsequent payments
else {
    $wait_before = $wait_aftre;
    $wait_aftre = $wait_before - $clientdebtchangeamount;
}

---

🔒 Security & Permissions

Authentication

• All actions require authentication via include_once("../public/authentication.php")
• User session validation before any database operations

Input Validation

• POST parameters validated for client selection
• Date parameters checked for proper format
• Bill numbers validated as integers

---

📊 Performance Considerations

Database Optimization

1. Indexes Required:

- bills(clientid, billdate) for client/date filtering

- bills(billno) for bill number lookups

- clientdebtchange(billid) for payment aggregation

2. Query Performance:

- Efficient WHERE clause construction

- Proper date range filtering with time stamps

- Aggregation of payments per bill using sumByBillId()

---

🐛 Common Issues & Troubleshooting

1. Incorrect Outstanding Amounts

Issue: Outstanding debt calculations don't match actual amounts

Cause: Missing payment records or incorrect payment linking

Debug:

-- Check payment sum for specific bill
SELECT SUM(clientdebtchangeamount) 
FROM clientdebtchange 
WHERE billid = [BILL_NUMBER];

-- Verify bill amounts
SELECT waitvalue, cashvalue, cardvalue, companycarry 
FROM bills 
WHERE billno = [BILL_NUMBER];

2. Missing Bills in Reports

Issue: Bills not appearing in outstanding or paid reports

Cause: Date range issues or filtering problems

Fix: Ensure proper date formatting and check filter logic:

// Proper date filtering
$where .= ' and date(billdate) >= "' . $start_date . '" and date(billdate) <= "' . $end_date . '"';

---

🧪 Testing Scenarios

Test Case 1: Outstanding Bills Accuracy

1. Create service bill with partial payment
2. Verify outstanding amount calculation
3. Make additional payment
4. Confirm updated outstanding balance
5. Check bill removal when fully paid

Test Case 2: Payment History Completeness

1. Select bill with multiple payments
2. Verify all payments appear in history
3. Check running balance calculations
4. Confirm payment dates and amounts

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• clientReportsController.md - Comprehensive client reports
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

ClientPayedDeptBills Documentation

Client Paid Debt Bills Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action (empty $do) - Outstanding Bills Report

2. show - Paid Bills Summary

3. show_details - Individual Bill Payment History

🔄 Workflows

Workflow 1: Outstanding Bills Analysis

Workflow 2: Payment History Drill-Down

🌐 URL Routes & Actions

🧮 Calculation Methods

Outstanding Debt Calculation

Total Payment Calculation

Running Balance for Payment History

🔒 Security & Permissions

Authentication

Input Validation

📊 Performance Considerations

Database Optimization

🐛 Common Issues & Troubleshooting

1. Incorrect Outstanding Amounts

2. Missing Bills in Reports

🧪 Testing Scenarios

Test Case 1: Outstanding Bills Accuracy

Test Case 2: Payment History Completeness

📚 Related Documentation