# Supplier Reports New Controller Documentation

**File**: `/controllers/supplierReportsnewController.php`  
**Purpose**: Simplified supplier reporting module for debt tracking and transaction history  
**Last Updated**: December 20, 2024  
**Total Functions**: 7  
**Lines of Code**: ~1,062

---

## 📋 Overview

The Supplier Reports New Controller is a streamlined reporting module that provides basic supplier account analysis and debt tracking capabilities. It handles:
- Individual supplier debt reports
- Supplier transaction history
- Purchase bill tracking (buy, return, combined)
- Date range filtering and analysis
- Detailed product breakdowns in purchases
- Multi-format report output (HTML)
- Net view processing for cleaner reports
- Transaction linking to source documents

### Primary Functions
- [x] Generate supplier debt reports
- [x] Track supplier payment history  
- [x] Purchase transaction analysis
- [x] Date range filtering
- [x] Debt balance calculations
- [x] Transaction linking to source documents
- [x] Purchase bill detail analysis
- [x] Net view calculations

### Related Controllers
- [buyBillController.php](buyBillController.md) - Purchase operations
- [supplierController.php](#) - Supplier management
- [returnBuyBillController.php](#) - Purchase returns
- [supplierPayedDeptController.php](#) - Payment processing
- [checkwithdrawalController.php](#) - Check withdrawals
- [kempialaController.php](#) - Promissory notes

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **supplierdebtchange** | Supplier debt transaction log | supplierdebtchangeid, supplierid, supplierdebtchangeamount, supplierdebtchangetype, supplierdebtchangedate, tablename |
| **supplier** | Supplier master data | supplierid, suppliername, suppliercurrentDebt, userid |

### Purchase Tables (Referenced)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **buybill** | Purchase bills | buybillid, buybillsupplierid, buybilltotalbill, buybillaftertotalbill, buybilltotalpayed |
| **buybilldetail** | Purchase bill line items | buybilldetailid, buybillid, buybilldetailproductid, buybilldetailquantity, buybilldetailtotalprice |
| **returnbuybill** | Purchase return bills | returnbuybillid, returnbuybillsupplierid, returnbuybilltotalbill, returnbuybillaftertotalbill |
| **returnbuybilldetail** | Return bill details | returnbuybilldetailid, returnbuybillid, returnbuybilldetailproductid, returnbuybilldetailquantity |
| **buyandruternbill** | Combined buy & return | buybillid, buybillsupplierid, buybillprice, returnbuybillprice, buybilldate |
| **buyandruternbilldetail** | Combined bill details | buyandruternbilldetailid, buybillid, buybilldetailproductid, buybilldetailquantity, buytype |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **youtubelink** | Tutorial links | youtubelinkid, title, url |
| **product** | Product information | productid, productName |
| **productcat** | Product categories | productCatId, productCatName |
| **unit** | Measurement units | unitid, unitName |

---

## 🔑 Key Functions

### 1. **Default Action** - Basic Supplier Report Form
**Location**: Line 129  
**Purpose**: Display supplier report form with basic filtering options

**Function Signature**:
```php
// Triggered when: do is empty or do=supplierShow2
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];  
$supplierId = $_POST['supplierId'];
```

**Process Flow**:
1. Load supplier dropdown data
2. Validate input parameters
3. Call `supplierShow2()` if supplier selected
4. Load YouTube tutorial links
5. Display via `supplierShow2.html` template

---

### 2. **supplierShow2()** - Core Supplier Report Logic
**Location**: Line 217  
**Purpose**: Build supplier debt history with transaction linking

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

**Process Flow**:
1. Build dynamic SQL query string based on filters
2. Query `supplierdebtchange` table with supplier/date filters
3. Process each transaction record:
   - Link to source documents (bills, payments, checks)
   - Calculate operational costs and payments
   - Load related data (bill totals, discounts)
4. Handle "bure" (net) view calculation
5. Assign data to Smarty template

**Transaction Types Handled**:
- `buyBillController.php` - Purchase bills
- `returnBuyBillController.php` - Purchase returns
- `buyAndReturnBillController.php` - Combined bills  
- `kempialaController.php` - Promissory notes
- `checkwithdrawalController.php` - Check withdrawals
- `supplierPayedDeptController.php` - Supplier payments
- `supplierController.php` - Supplier account adjustments
- `supplierDeficitController.php` - Supplier deficit adjustments
- `datedCheckedController.php` - Post-dated checks
- `dailyentry.php` - Journal entries

---

### 3. **supplierShowDetail()** - Detailed Supplier Report
**Location**: Line 866  
**Purpose**: Enhanced supplier report with product-level details

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

**Process Flow**:
1. Similar to `supplierShow2()` but with enhanced detail
2. Load product details for each bill type:
   - `$productsArr` - Regular purchase bill details
   - `$productsReArr` - Return bill details
   - `$productsBuyAndReArr` - Combined bill details
3. Query bill detail tables for product information
4. Assign enhanced data arrays to template

**Enhanced Features**:
- Product-level breakdown for all bill types
- Detailed line item analysis
- Enhanced template with product tables

---

### 4. **showBuyBillsBySupplierAndDate()** - Purchase Bills Query
**Location**: Line 663  
**Purpose**: Retrieve and process purchase bills for a supplier/date range

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

**Process Flow**:
1. Query `buybill` table with supplier/date filters
2. Query `buyandruternbill` table for combined bills (buy portion)
3. For each bill:
   - Count product quantities via detail tables
   - Apply discount calculations
   - Calculate final bill amounts
4. Merge datasets and return combined array

**Discount Processing**:
```php
// Fixed amount discount
if ($buybilldiscountrype == 0) {
    $myfialtotal = $mytotal - $buybilldiscount;
} 
// Percentage discount  
else {
    $myfialtotal = $mytotal - (($mytotal * $buybilldiscount) / 100);
}
```

---

### 5. **showReturnBuyBillsBySupplierAndDate()** - Returns Query
**Location**: Line 734  
**Purpose**: Retrieve and process return bills for analysis

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

**Process Flow**:
1. Query `returnbuybill` table
2. Query `buyandruternbill` table for return portions
3. Handle negative amounts in returns (convert to positive)
4. Count returned quantities via detail tables
5. Return array with both datasets

**Return Value**:
```php
$returnbuybillData = array_merge($returnbuybillData, $buybillandruternData);
// Combined return bills from both sources
```

---

### 6. **getSupplierData()** - Supplier Dropdown Data
**Location**: Line 207  
**Purpose**: Load supplier list for report selection dropdowns

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

**Returns**: Array of active supplier objects for dropdown population

---

### 7. **Net View Processing** - Transaction Cleanup
**Location**: Lines 404-473  
**Purpose**: Clean up offsetting entries for simpler reporting view

**Process Flow**:
```php
if ($bure == "1") {
    foreach ($shownData as $datax) {
        if ($datax->supplierdebtchangeid > 0) {
            $finalstring = $datax->finalstring;
            $type = $datax->supplierdebtchangetype;
            
            if ($type == 1) { // Payment entry
                $otherfinal = substr_replace($finalstring, '0', -1);
                // Hide offsetting entries
                foreach ($shownData as $xx) {
                    if ($xx->finalstring == $otherfinal) {
                        $xx->supplierdebtchangeid = -1;
                        $datax->supplierdebtchangeid = -1;
                    }
                }
            }
        }
    }
}
```

---

## 🔄 Workflows

### Workflow 1: Basic Supplier Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Supplier & Date Range           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Validate Input Parameters                               │
│     - Check supplierid                                      │
│     - Parse date range                                      │
│     - Load supplier data                                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build SQL Query String                                  │
│     - Add supplier filter                                   │
│     - Add date range filter                                 │
│     - Add deletion filter (del = 0)                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Query supplierdebtchange Table                          │
│     - Get all transactions for supplier/date               │
│     - Order by specified criteria                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Transaction                                │
│     FOR EACH debt change record:                            │
│       │                                                     │
│       ├─→ Identify source table (buybill, payment, etc.)   │
│       │                                                     │
│       ├─→ Load source document details                     │
│       │   ├─ Bill totals and discounts                     │
│       │   ├─ Payment amounts                               │
│       │   └─ Operational costs                             │
│       │                                                     │
│       └─→ Generate link to source document                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Apply Net View Processing (optional)                    │
│     - Remove offsetting entries                             │
│     - Recalculate running balances                          │
│     - Clean up display                                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Report Output                                  │
│     - Assign data to Smarty template                        │
│     - Include totals and summaries                          │
│     - Display via appropriate template                      │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=supplierShow2` | Default action | Basic supplier report form |
| `do=supplierShowDetail` | `supplierShowDetail()` | Detailed supplier report with products |

### Required Parameters by Action

**Basic Supplier Report** (`do=supplierShow2`):
- `supplierId` - Supplier ID  
- `from` - Start date (YYYY-MM-DD)
- `to` - End date (YYYY-MM-DD)

**Detailed Supplier Report** (`do=supplierShowDetail`):
- `supplierId` - Supplier ID
- `from` - Start date (YYYY-MM-DD) 
- `to` - End date (YYYY-MM-DD)

---

## 🧮 Calculation Methods

### Operational Cost Calculation
```php
// For purchase bills
$data->totalOperationCost = $buybilldata->buybilltotalbill + $data->supplierdebtchangebefore;

// For returns
$data->totalOperationCost = 0 + $data->supplierdebtchangebefore;

// For payments
$data->totalOperationCost = $data->supplierdebtchangebefore;
```

### Discount Processing
```php
// Calculate bill discount
$data->discount = ($buybilldata->buybilltotalbill - $buybilldata->buybillaftertotalbill);

// Fixed vs percentage discount in combined bills
if ($buybilldiscountrype == 0) {
    $myfialtotal = $mytotal - $buybilldiscount; // Fixed
} else {
    $myfialtotal = $mytotal - (($mytotal * $buybilldiscount) / 100); // Percentage
}
```

### Running Balance Calculation
```php
foreach ($shownData as $mov) {
    if ($mov->supplierdebtchangetype == "0") { // Debt increase
        $mov->supplierdebtchangeafter = $startafter + $startvalue;
        $startafter = $startafter + $startvalue;
    } else { // Debt decrease (payment)
        $mov->supplierdebtchangeafter = $startafter - $startvalue;
        $startafter = $startafter - $startvalue;
    }
}
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Supplier Report
```
1. Select supplier with recent transactions
2. Set date range covering known purchases
3. Verify transaction count matches database
4. Check operational cost calculations
5. Confirm links to source documents work
```

### Test Case 2: Detailed Product Breakdown
```
1. Select supplier with detailed purchase history
2. Run detailed report
3. Verify product arrays are populated
4. Check product quantities and prices
5. Validate template displays product tables
```

### Test Case 3: Net View Processing
```
1. Select supplier with offsetting transactions
2. Enable net view processing
3. Verify offsetting entries are hidden
4. Check running balance accuracy
5. Confirm cleaner display
```

---

## 🔒 Security & Permissions

### Input Sanitization
- All `$_REQUEST` parameters are filtered
- Numeric IDs cast to integer where needed
- Date strings validated before SQL inclusion
- SQL injection prevented by DAO layer

### Access Control
- Requires authentication via `../public/authentication.php`
- No specific permission level checks implemented
- Relies on session-based access control

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `supplierdebtchange(supplierid, supplierdebtchangedate)`
   - `buybill(buybillsupplierid, buybilldate)`
   - `returnbuybill(returnbuybillsupplierid, returnbuybilldate)`

2. **Query Optimization**:
   - Date filtering with proper format: `YYYY-MM-DD HH:MM:SS`
   - Efficient WHERE clauses
   - Batch loading of related data

3. **Memory Management**:
   - Product arrays can grow large for active suppliers
   - Consider pagination for very long date ranges

---

## 🐛 Common Issues & Troubleshooting

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

### 2. **Incorrect Operational Costs**
**Issue**: Cost calculations don't match expected values
**Cause**: Bill loading issues or discount calculation errors

### 3. **Empty Product Arrays**
**Issue**: Detailed reports don't show products
**Cause**: Bill detail queries failing or empty detail tables

### 4. **Net View Not Working**
**Issue**: Offsetting entries still visible
**Cause**: `finalstring` matching logic issues or incorrect transaction types

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [buyBillController.md](buyBillController.md) - Purchase operations
- [supplierController.php](#) - Supplier management
- [Database Schema Documentation](#) - Table relationships

---

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