# Report Client And Supplier Controller Documentation

**File**: `/controllers/reportClientAndSupplier.php`  
**Purpose**: Unified reporting for both customer and supplier debt transactions and history  
**Last Updated**: December 21, 2024  
**Total Functions**: 4  
**Lines of Code**: ~741

---

## 📋 Overview

The Report Client And Supplier Controller provides comprehensive reporting capabilities for both customer and supplier accounts in a unified interface. It handles:
- Individual client debt transaction reports  
- Individual supplier debt transaction reports
- Transaction history with source document linking
- Running balance calculations for both clients and suppliers
- Date range filtering and sorting options
- Payment tracking and debt change analysis
- Combined client/supplier selection in single interface

### Primary Functions
- [x] Generate client debt transaction reports
- [x] Generate supplier debt transaction reports  
- [x] Track transaction history with document links
- [x] Calculate running balances and debt changes
- [x] Date range filtering and sorting
- [x] Payment and debt analysis
- [x] Source document linking and navigation

### Related Controllers
- [clientReportsController.php](clientReportsController.md) - Dedicated client reports
- [supplierReportsController.php](#) - Dedicated supplier reports
- [sellbillController.php](#) - Sales transactions
- [buyBillController.php](#) - Purchase transactions
- [clientPayedDeptController.php](#) - Customer payments
- [supplierPayedDeptController.php](#) - Supplier payments

---

## 🗄️ Database Tables

### Client Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer master data | clientid, clientname, clientdebt |
| **clientdebtchange** | Client debt transaction log | clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangetype, clientdebtchangedate, tablename, clientdebtchangemodelid |

### Supplier Transaction Tables  
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **supplier** | Supplier master data | supplierid, suppliername, supplierdebt |
| **supplierdebtchange** | Supplier debt transaction log | supplierdebtchangeid, supplierid, supplierdebtchangeamount, supplierdebtchangetype, supplierdebtchangedate, tablename, supplierdebtchangemodelid |

### Sales Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilltotalbill, returnsellbillaftertotalbill |
| **sellbillandrutern** | Combined sell & return | sellbillid, sellbillclientid, sellbillaftertotalbill |

### Purchase Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **buybill** | Purchase bills | buybillid, buybillsupplierid, buybillaftertotalbill |
| **returnbuybill** | Purchase return bills | returnbuybillid, returnbuybillsupplierid, returnbuybillaftertotalbill |
| **buyandruternbill** | Combined buy & return | buyandruternbillid, buybillsupplierid, buybillaftertotalbill |

### Supporting Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **checkdeposit** | Check deposits | checkdepositid, clientid, bankname, accountname |
| **checkwithdrawal** | Check withdrawals | checkwithdrawalid, supplierid, bankname, accountname |
| **bills** | Service bills | billid, clientid, finalnetbillvalue |
| **youtubelink** | Tutorial videos | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Combined Client/Supplier Report
**Location**: Line 249 (`empty($do)` or `$do == "show"`)  
**Purpose**: Main report interface handling both client and supplier debt reports

**Function Signature**:
```php
// URL parameters: clientid, supplierid, from, to, order
$clientid = $_REQUEST['clientid'];
$supplierid = $_REQUEST['supplierid']; 
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$order = (int)$_REQUEST['order'];
```

**Process Flow**:
1. Include authentication check
2. Load client and supplier dropdown data
3. Process client report if client ID provided:
   - Load client information
   - Call `clientShow()` function
   - Build message with client name
4. Process supplier report if supplier ID provided:
   - Load supplier information  
   - Call `supplierShow()` function
   - Build message with supplier name
5. Load YouTube tutorial links
6. Display combined report via template

**Template Variables**:
- `$clientData` - Client dropdown options
- `$supplierData` - Supplier dropdown options
- `$shownData` - Client transaction data (if client selected)
- `$shownData2` - Supplier transaction data (if supplier selected)
- `$message` - Report header with selected entity and date range
- `$youtubes` - Tutorial video links

---

### 2. **clientShow()** - Client Transaction Report
**Location**: Line 312  
**Purpose**: Generate detailed client debt transaction report with document linking

**Function Signature**:
```php
function clientShow($clientid, $startDate, $endDate, $order)
```

**Process Flow**:
1. Build dynamic query string with filters:
   - Client ID filter
   - Date range filter (if provided)
   - User permission restrictions
2. Execute query with sorting options:
   - `$order = 1`: Sort by date descending
   - Default: Sort by ID descending
3. Process each transaction record:
   - Calculate running total based on transaction type
   - Generate links to source documents
   - Load related data (bill amounts, bank info, etc.)
4. Handle "bure" (net) view processing if requested
5. Assign data to Smarty template

**Transaction Type Handling**:
```php
foreach ($shownData as $data) {
    if ($data->clientdebtchangetype == 0) {
        $total = $total + $data->clientdebtchangeamount; // Debt increase
    } else {
        $total = $total - $data->clientdebtchangeamount; // Payment/decrease
    }
    
    // Generate document links based on tablename
    if ($data->tablename == "sellbillController.php") {
        $data->link = "sellbillController.php?do=showDetail&sellbillid=" . $data->clientdebtchangemodelid;
        // Load bill totals...
    }
    // ... additional transaction types
}
```

**Document Link Generation**:
- `sellbillController.php` - Sales bills
- `returnsellbillController.php` - Sales returns
- `depositcheckController.php` - Check deposits with bank details
- `clientPayedDeptController.php` - Customer payments
- `billreceiptController.php` - Bill receipts
- `premiumController.php` - Premium transactions

---

### 3. **supplierShow()** - Supplier Transaction Report  
**Location**: Line 538  
**Purpose**: Generate detailed supplier debt transaction report with document linking

**Function Signature**:
```php
function supplierShow($supplierid, $startDate, $endDate, $order)
```

**Process Flow**:
1. Build query string similar to client report
2. Query supplier debt change table with filters
3. Process each transaction:
   - Load related document data
   - Generate appropriate links
   - Calculate totals and amounts
4. Handle "bure" view processing for net display
5. Assign to `$shownData2` template variable

**Supplier Transaction Types**:
- `buyBillController.php` - Purchase bills
- `returnBuyBillController.php` - Purchase returns
- `buyAndReturnBillController.php` - Combined purchase bills
- `checkwithdrawalController.php` - Check withdrawals with bank details
- `supplierPayedDeptController.php` - Supplier payments
- `kempialaController.php` - Promissory notes

**Unique Supplier Features**:
```php
// Load bank information for check withdrawals
if ($data->tablename == "checkwithdrawalController.php") {
    $checkdata = $checkWithdrawalExtDAO->queryByIdExt($id);
    $data->bankname = $checkdata->bankname;
    $data->accountname = $checkdata->accountname;
}
```

---

### 4. **getClientData()** - Client Dropdown Data
**Location**: Line 298  
**Purpose**: Load client data for report selection

**Function Signature**:
```php
function getClientData()
```

**Returns**: Array of client objects with extended information using `queryAllsup()`

---

### 5. **getSupplierData()** - Supplier Dropdown Data  
**Location**: Line 305  
**Purpose**: Load supplier data for report selection

**Function Signature**:
```php
function getSupplierData()
```

**Returns**: Array of all supplier objects using `queryAll()`

---

## 🔄 Workflows

### Workflow 1: Combined Client/Supplier Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Access Combined Report                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Authentication & Data Loading                           │
│     - Check user authentication                            │
│     - Load client dropdown data                            │
│     - Load supplier dropdown data                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Request Parameters                              │
│     - Check for client ID                                  │
│     - Check for supplier ID                                │
│     - Parse date range (from/to)                           │
│     - Get sort order preference                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Generate Client Report (if client selected)            │
│     IF clientid provided:                                  │
│       │                                                     │
│       ├─→ Load client information                          │
│       │                                                     │
│       ├─→ Call clientShow() function                       │
│       │   ├─ Build query with filters                      │
│       │   ├─ Load transaction history                      │
│       │   ├─ Generate document links                       │
│       │   ├─ Calculate running balances                    │
│       │   └─ Process "bure" view if needed                 │
│       │                                                     │
│       └─→ Add client info to message                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate Supplier Report (if supplier selected)        │
│     IF supplierid provided:                                │
│       │                                                     │
│       ├─→ Load supplier information                        │
│       │                                                     │
│       ├─→ Call supplierShow() function                     │
│       │   ├─ Build query with filters                      │
│       │   ├─ Load transaction history                      │
│       │   ├─ Generate document links                       │
│       │   ├─ Calculate running balances                    │
│       │   └─ Process "bure" view if needed                 │
│       │                                                     │
│       └─→ Add supplier info to message                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Finalize Report Display                                 │
│     - Complete message with date range                     │
│     - Load YouTube tutorial links                          │
│     - Assign all data to template                          │
│     - Display via reportClientAndSupplierView/show.html    │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| No `do` parameter or `do=show` | Default action | Main combined client/supplier report |

### Required Parameters

**Combined Report**:
- `clientid` - Client ID for client report (optional)
- `supplierid` - Supplier ID for supplier report (optional)  
- `from` - Start date filter (YYYY-MM-DD, optional)
- `to` - End date filter (YYYY-MM-DD, optional)
- `order` - Sort order (1=date desc, default=ID desc, optional)

**Example URLs**:
```
reportClientAndSupplier.php?clientid=123&from=2024-01-01&to=2024-01-31&order=1
reportClientAndSupplier.php?supplierid=456&from=2024-01-01&to=2024-01-31
reportClientAndSupplier.php?clientid=123&supplierid=456&from=2024-01-01&to=2024-01-31
```

---

## 🧮 Calculation Methods

### Running Balance Calculation (Client)
```php
foreach ($shownData as $data) {
    if ($data->clientdebtchangetype == 0) {
        $total = $total + $data->clientdebtchangeamount; // Debt increase
    } else {
        $total = $total - $data->clientdebtchangeamount; // Payment/decrease  
    }
}
```

### Query String Building
```php
$queryString = ' WHERE';

if (isset($clientid) && $clientid != '-1') {
    $queryString .= ' clientdebtchange.clientid =' . $clientid . ' AND';
}

if (!empty($startDate) && !empty($endDate)) {
    $queryString .= ' clientdebtchange.clientdebtchangedate >= "' . $startDate . '" AND clientdebtchange.clientdebtchangedate <= "' . $endDate . '" AND';
}

// Clean up trailing AND/WHERE
$arr = explode(' ', $queryString);
$lastWord = end($arr);
if ($lastWord == 'AND') {
    array_pop($arr);
    $queryString = implode(' ', $arr);
} else if ($lastWord == 'WHERE') {
    $queryString = ' ';
}
```

### Net View Processing (Bure View)
```php
if ($bure == "1") {
    foreach ($shownData as $datax) {
        $finalstring = $datax->finalstring;
        $type = $datax->clientdebtchangetype;
        
        if ($type == 1) { // Payment
            $otherfinal = substr_replace($finalstring, '0', -1);
            
            // Find and hide offsetting entries
            foreach ($shownData as $xx) {
                if ($xx->finalstring == $otherfinal) {
                    $xx->clientdebtchangeid = -1;
                    $datax->clientdebtchangeid = -1;
                }
            }
        }
    }
}
```

---

## 🔒 Security & Permissions

### Authentication
```php
include_once("../public/authentication.php");
```
- Requires user authentication before accessing reports
- No specific permission levels for clients vs suppliers

### Input Sanitization
- Parameters used directly in SQL queries
- Should implement proper sanitization and validation
- Integer casting used for order parameter: `(int)$_REQUEST['order']`

### Recommended Security Improvements
```php
// Sanitize IDs
$clientid = filter_input(INPUT_REQUEST, 'clientid', FILTER_VALIDATE_INT);
$supplierid = filter_input(INPUT_REQUEST, 'supplierid', FILTER_VALIDATE_INT);

// Validate dates
if (!empty($startDate) && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) {
    $startDate = '';
}
```

---

## 📊 Performance Considerations

### Database Optimization
1. **Indexes Required**:
   - `clientdebtchange(clientid, clientdebtchangedate)`
   - `supplierdebtchange(supplierid, supplierdebtchangedate)`
   - `sellbill(sellbillid)` for document linking
   - `buybill(buybillid)` for document linking

2. **Query Performance**:
   - Date range queries can be expensive without proper indexing
   - Document linking requires additional queries for each transaction
   - Running balance calculation done in PHP rather than SQL

### Performance Notes
- Multiple individual queries for loading related document data
- Could benefit from JOIN queries to reduce database round trips
- No pagination for large transaction histories

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Document Links**
**Issue**: Transaction shows "#" instead of proper link  
**Cause**: `clientdebtchangemodelid = -1` or unrecognized `tablename`

**Debug**:
```sql
-- Check for invalid model IDs
SELECT tablename, COUNT(*) 
FROM clientdebtchange 
WHERE clientdebtchangemodelid = -1 
GROUP BY tablename;

-- Check for unrecognized table names
SELECT DISTINCT tablename 
FROM clientdebtchange 
WHERE tablename NOT IN ('sellbillController.php', 'clientPayedDeptController.php', ...);
```

### 2. **Incorrect Running Balances**  
**Issue**: Balance calculations don't match expected values
**Cause**: Missing transactions or incorrect transaction types

**Debug**:
```sql
-- Verify transaction types
SELECT clientdebtchangetype, COUNT(*) 
FROM clientdebtchange 
WHERE clientid = [ID]
GROUP BY clientdebtchangetype;

-- Check for orphaned transactions
SELECT * FROM clientdebtchange 
WHERE clientdebtchangemodelid NOT IN (
    SELECT sellbillid FROM sellbill
) AND tablename = 'sellbillController.php';
```

### 3. **Date Range Issues**
**Issue**: No results for valid date ranges
**Cause**: Date format mismatch or timezone problems

**Fix**: Ensure consistent date formatting and add time components:
```php
if (!empty($startDate)) $startDate .= " 00:00:00";
if (!empty($endDate)) $endDate .= " 23:59:59";
```

---

## 🧪 Testing Scenarios

### Test Case 1: Client Report Generation
```
1. Select client with known transactions
2. Set date range covering transactions
3. Verify transaction list and running balance
4. Test document links functionality
5. Verify net view (bure) calculations
```

### Test Case 2: Supplier Report Generation  
```
1. Select supplier with purchase history
2. Apply date filters
3. Verify supplier-specific transaction types
4. Test check withdrawal bank information display
5. Verify payment tracking accuracy
```

### Test Case 3: Combined Client/Supplier Report
```
1. Select both client and supplier
2. Verify both reports display simultaneously  
3. Test with overlapping date ranges
4. Verify proper data separation
5. Test message generation with both entities
```

### Test Case 4: Sorting and Filtering
```
1. Test different sort orders (date vs ID)
2. Apply various date range combinations
3. Test edge cases (same start/end date)
4. Verify filter reset functionality
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientReportsController.md](clientReportsController.md) - Detailed client reporting
- [sellbillController.php](#) - Sales transaction creation
- [buyBillController.php](#) - Purchase transaction creation

---

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