# 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
- [x] Set customer account closing dates
- [x] Track account closure history
- [x] Calculate customer balances at closure date
- [x] Ajax-powered data table for closure records
- [x] Client search with Select2 integration
- [x] Balance calculation with complex transaction logic

### Related Controllers
- [clientController.php](#) - Customer management
- [sellbillController.php](sellbillController.md) - Sales transactions
- [returnsellbillController.php](#) - Sales returns
- [clientPayedDeptController.php](#) - Customer payments

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **closingaccountclient** | Account closure records | id, client_id, date_close, today, userid |
| **client** | Customer master data | clientid, clientname, datecustomeraccount |

### Transaction Tables (Referenced for Balance Calculations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **clientdebtchange** | Customer debt changes | clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangedate, del |
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilltotalpayed, sellbilldate, conditions |
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbillclientid, returnsellbilltotalpayed, returnsellbilldate |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | System users | userid, employeename |

---

## 🔑 Key Functions

### 1. **Default Action** - Add Closure Form
**Location**: Line 7  
**Purpose**: Display form for setting customer account closure date

**Function Signature**:
```php
// 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**:
```php
$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**:
```php
// 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**:
```php
$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**:
```php
$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**:
```php
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**:
```php
$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**:
```php
$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**:
```php
$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
```
┌─────────────────────────────────────────────────────────────┐
│              START: User Closes Customer Account           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Select Customer                                         │
│     - Use Select2 search to find customer                   │
│     - Enter customer name or partial match                  │
│     - System shows matching customers                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Set Closure Date                                        │
│     - Select desired account closure date                   │
│     - Date determines cutoff for balance calculations       │
│     - Can be past, present, or future date                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Calculate Balance (Optional)                            │
│     - System can show balance up to closure date            │
│     - Includes all transaction types:                       │
│       ├─ Debt changes (payments, adjustments)              │
│       ├─ Sales bill payments                               │
│       └─ Return bill payments                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Save Closure Record                                     │
│     - Insert record into closingaccountclient table         │
│     - Update client.datecustomeraccount field              │
│     - Log user and timestamp information                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Confirmation                                            │
│     - Display success message                               │
│     - Account marked as closed on specified date            │
│     - Closure visible in history reports                    │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Balance Calculation Logic
```
┌─────────────────────────────────────────────────────────────┐
│            START: Calculate Customer Balance               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Get Customer Closure Date                               │
│     - Retrieve datecustomeraccount from client record       │
│     - Use as cutoff date for calculations                   │
│     - If no date set, use all-time data                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Debt Changes                                    │
│     - Query clientdebtchange table                          │
│     - Filter by customer ID and date range                  │
│     - Add active changes (del = 0)                         │
│     - Subtract deleted/reversed changes (del = 1)           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Sales Bill Payments                             │
│     - Query sellbill table                                  │
│     - Filter by customer ID and date range                  │
│     - Add payments from active bills (conditions = 0)       │
│     - Subtract payments from cancelled bills (conditions = 1)│
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Return Bill Payments                            │
│     - Query returnsellbill table                           │
│     - Filter by customer ID and date range                  │
│     - Add payments from cancelled returns (conditions = 1)  │
│     - Subtract payments from active returns (conditions = 0)│
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Return Final Balance                                    │
│     - Sum all positive and negative adjustments             │
│     - Return net balance to closure date                    │
│     - Display result to user                               │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Show closure form |
| `do=save` | `save` | Process account closure |
| `do=show` | Show interface | Display closure history |
| `do=showajax` | `showajax()` | Ajax data table for closures |
| `do=getdata` | `getdata` | Calculate customer balance |
| `do=select2client` | Client search | Search clients for dropdown |
| `do=sucess` | Success page | Display success message |

### Required Parameters by Action

**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
```php
$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
```php
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
```php
$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**:
```sql
-- 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**:
```sql
-- 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**:
```sql
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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientController.php](#) - Customer management
- [sellbillController.md](sellbillController.md) - Sales operations
- [Database Schema Documentation](#) - Table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur