Closing Account Controller Documentation

File: /controllers/closingAccountController.php

Purpose: Manages customer account closing operations and tracks account closure dates with balance calculations

Last Updated: December 20, 2024

Total Functions: 3

Lines of Code: ~157

---

📋 Overview

The Closing Account Controller handles the process of closing customer accounts at specific dates, tracking closure history, and calculating customer balances up to closure dates. It provides functionality for setting account closure dates and viewing closure history with Ajax-based data tables.

Primary Functions

• ✅ Set customer account closing dates
• ✅ Track account closure history
• ✅ Calculate customer balances at closure date
• ✅ Ajax-powered data table for closure records
• ✅ Client search with Select2 integration
• ✅ Balance calculation with complex transaction logic

Related Controllers

• • clientController.php - Customer management
• • sellbillController.php - Sales transactions
• • returnsellbillController.php - Sales returns
• • clientPayedDeptController.php - Customer payments

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Add Closure Form

Location: Line 7

Purpose: Display form for setting customer account closure date

Function Signature:

// Triggered when: empty($do)

Process Flow:

1. Display header template

2. Show account closure form (add.html)

3. Display footer template

---

2. save - Save Account Closure

Location: Line 11

Purpose: Process account closure and update customer record

Function Signature:

$client_id = filter_input(INPUT_POST, 'client_id');
$date_close = filter_input(INPUT_POST, 'date_close');

Process Flow:

1. Validate input parameters

2. Create closure record in closingaccountclient table

3. Update customer record with closure date

4. Redirect to success page

Database Operations:

// Insert closure record using RedBean
$closing_account = R::dispense('closingaccountclient');
$closing_account->client_id = $client_id;
$closing_account->date_close = $date_close;
$closing_account->today = $today;
$closing_account->userid = $_SESSION['userid'];
R::store($closing_account);

// Update client record with closure date
R::exec("UPDATE `client` SET `datecustomeraccount`= '$date_close' WHERE clientid = $client_id");

---

3. getdata - Calculate Customer Balance

Location: Line 33

Purpose: Calculate complex customer balance including all transaction types up to closure date

Function Signature:

$client_id = filter_input(INPUT_POST, 'client_id');

Process Flow:

1. Get customer data and closure date

2. Build date filter queries for different transaction types

3. Calculate totals from multiple sources:

- Client debt changes (add/subtract based on type)

- Sales bill payments (add active, subtract cancelled)

- Return bill payments (subtract active, add cancelled)

4. Return final balance

Complex Balance Calculation:

$totals = 0;

// Add active debt changes, subtract deleted ones
$totals += R::getcell("SELECT sum(clientdebtchangeamount) FROM clientdebtchange WHERE clientdebtchange.del = 0 AND clientid = $client_id $debitQuery");
$totals -= R::getcell("SELECT sum(clientdebtchangeamount) FROM clientdebtchange WHERE clientdebtchange.del = 1 AND clientid = $client_id $debitQuery");

// Add active sales payments, subtract cancelled ones
$totals += R::getcell("SELECT sum(sellbilltotalpayed) FROM sellbill WHERE conditions = 0 AND sellbillclientid = $client_id $sellQuery");
$totals -= R::getcell("SELECT sum(sellbilltotalpayed) FROM sellbill WHERE conditions = 1 AND sellbillclientid = $client_id $sellQuery");

// Subtract active returns, add cancelled returns
$totals += R::getcell("SELECT sum(returnsellbilltotalpayed) FROM returnsellbill WHERE conditions = 1 AND returnsellbillclientid = $client_id $returnQuery");
$totals -= R::getcell("SELECT sum(returnsellbilltotalpayed) FROM returnsellbill WHERE conditions = 0 AND returnsellbillclientid = $client_id $returnQuery");

---

4. showajax - Ajax Data Table Display

Location: Line 80

Purpose: Provide paginated, searchable data table of closure records

Function Signature:

function showajax()

Process Flow:

1. Parse DataTables parameters (pagination, search, sorting)

2. Build dynamic search query with date and client filters

3. Execute query with JOINs to get related data

4. Format results for DataTables response

5. Return JSON response

DataTables Integration:

$columns = array('closingaccountclient.id', 'client_id', 'date_close', 'userid', 'today');

// Build search query with filters
if ($clientid != '') {
    $searchQuery .= " and closingaccountclient.client_id = " . $clientid . " ";
}

if ($fromdate != '' && $todate != '') {
    $searchQuery .='and closingaccountclient.today >= "' . $fromdate . ' 00-00-00" and closingaccountclient.today <= "' . $todate . ' 23-59-55" ';
}

// Execute query with JOINs
$rResult = R::getAll("SELECT *,employeename, clientname FROM `closingaccountclient`
        LEFT JOIN client ON closingaccountclient.client_id = client.clientid
        LEFT JOIN user ON user.userid = closingaccountclient.userid
        WHERE 1 $searchQuery ");

---

5. select2client - Client Search API

Location: Line 63

Purpose: Provide client search functionality for Select2 dropdown

Function Signature:

$name = $_POST['searchTerm'];

Process Flow:

1. Search clients by name pattern

2. Return JSON array with id and text fields

3. Limit results to 50 for performance

Search Implementation:

$productsData = R::getAll("SELECT clientid, clientname as texts
FROM client WHERE conditions = 0 and clientname LIKE '%" . $name . "%' limit 50");

// Format for Select2
foreach ($productsData as $pro) {
    $row_array = array();
    $row_array['id'] = $pro['clientid'];
    $row_array['text'] = $pro['texts'];
    array_push($return_arr, $row_array);
}
echo json_encode($return_arr);

---

🔄 Workflows

Workflow 1: Account Closure Process

---

Workflow 2: Balance Calculation Logic

---

🌐 URL Routes & Actions

Save Closure (do=save):

• • client_id - Customer ID
• • date_close - Closure date (YYYY-MM-DD)

Get Balance (do=getdata):

• • client_id - Customer ID

Show Ajax (do=showajax):

• • fromdate - Start date filter
• • todate - End date filter
• • data1 - Client ID filter

Client Search (do=select2client):

• • searchTerm - Search text

---

🧮 Calculation Methods

Balance Calculation Formula

$totals = 0;

// Debt Changes (positive = debt increase, negative = debt decrease)
$totals += Active_Debt_Changes - Deleted_Debt_Changes;

// Sales Payments (positive = money received)
$totals += Active_Sales_Payments - Cancelled_Sales_Payments;

// Return Payments (negative = money returned to customer)
$totals += Cancelled_Return_Payments - Active_Return_Payments;

return $totals;

Date Filter Construction

if($datecustomeraccount != '0000-00-00' && $datecustomeraccount != ''){
    $sellQuery .=' and sellbill.sellbilldate >= "' . $datecustomeraccount . ' 00-00-00" 
                  and sellbill.sellbilldate <= "' . $datecustomeraccount . ' 23-59-55" ';
    
    $returnQuery .=' and returnsellbill.returnsellbilldate >= "' . $datecustomeraccount . ' 00-00-00" 
                    and returnsellbill.returnsellbilldate <= "' . $datecustomeraccount . ' 23-59-55" ';
    
    $debitQuery .=' and clientdebtchange.clientdebtchangedate >= "' . $datecustomeraccount . '" 
                   and clientdebtchange.clientdebtchangedate <= "' . $datecustomeraccount . '" ';
}

---

🔒 Security & Permissions

Input Validation

$client_id = filter_input(INPUT_POST, 'client_id');
$date_close = filter_input(INPUT_POST, 'date_close');

SQL Injection Prevention

• • Uses RedBean ORM parameterized queries
• • Input filtering on all user data
• • Proper variable binding

Session Management

• • Tracks user performing closure: $_SESSION['userid']
• • Audit trail in closure records

---

🐛 Common Issues & Troubleshooting

1. Incorrect Balance Calculations

Issue: Balance doesn't match expected value

Cause: Complex transaction logic with multiple conditions

Debug:

-- Check each component separately
SELECT SUM(clientdebtchangeamount) FROM clientdebtchange 
WHERE del = 0 AND clientid = [ID];

SELECT SUM(sellbilltotalpayed) FROM sellbill 
WHERE conditions = 0 AND sellbillclientid = [ID];

SELECT SUM(returnsellbilltotalpayed) FROM returnsellbill 
WHERE conditions = 0 AND returnsellbillclientid = [ID];

2. Date Range Issues

Issue: Transactions outside date range included/excluded

Cause: Improper date format or timezone issues

Debug:

-- Check date formats and ranges
SELECT clientdebtchangedate, clientdebtchangeamount
FROM clientdebtchange  
WHERE clientid = [ID] 
ORDER BY clientdebtchangedate;

3. Missing Closure Records

Issue: Closure not saving or showing

Cause: Database insert failure or query issues

Debug:

SELECT * FROM closingaccountclient WHERE client_id = [ID];
SELECT datecustomeraccount FROM client WHERE clientid = [ID];

---

🧪 Testing Scenarios

Test Case 1: Normal Account Closure

1. Select existing customer
2. Set closure date (today)
3. Save closure
4. Verify closure record created
5. Verify client record updated
6. Check balance calculation accuracy

Test Case 2: Historical Closure

1. Select customer with transaction history
2. Set closure date in past
3. Calculate balance for that date
4. Verify only transactions up to date included
5. Check complex transaction scenarios

Test Case 3: Ajax Data Table

1. Open closure history
2. Test pagination
3. Test search functionality
4. Test date filtering
5. Verify JOIN queries return correct data

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- closingaccountclient(client_id, today)

- client(clientid, datecustomeraccount)

- clientdebtchange(clientid, clientdebtchangedate, del)

- sellbill(sellbillclientid, sellbilldate, conditions)

- returnsellbill(returnsellbillclientid, returnsellbilldate, conditions)

2. Query Optimization:

- Use efficient date range filters

- Minimize subqueries in balance calculations

- Proper JOIN usage in showajax

3. Ajax Performance:

- Limit search results (50 clients max)

- Efficient pagination

- Minimize data transferred

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • clientController.php - Customer management
• • sellbillController.md - Sales operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur