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

✅ Generate supplier debt reports
✅ Track supplier payment history
✅ Purchase transaction analysis
✅ Date range filtering
✅ Debt balance calculations
✅ Transaction linking to source documents
✅ Purchase bill detail analysis
✅ Net view calculations

Related Controllers

• buyBillController.php - 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:

// 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:

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:

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:

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:

// 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:

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:

$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:

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:

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

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

▼

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

1Validate Input Parameters

- Check supplierid

- Parse date range

- Load supplier data

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

▼

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

2Build SQL Query String

- Add supplier filter

- Add date range filter

- Add deletion filter (del = 0)

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

▼

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

3Query supplierdebtchange Table

- Get all transactions for supplier/date

- Order by specified criteria

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

▼

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

4Process 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 │

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

▼

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

5Apply Net View Processing (optional)

- Remove offsetting entries

- Recalculate running balances

- Clean up display

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

▼

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

6Generate 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

// For purchase bills
$data->totalOperationCost = $buybilldata->buybilltotalbill + $data->supplierdebtchangebefore;

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

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

Discount Processing

// 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

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 - PHP 8.2 migration guide
• 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

📚 ERP Docs

SupplierReportsnew Documentation

Supplier Reports New Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Basic Supplier Report Form

2. supplierShow2() - Core Supplier Report Logic

3. supplierShowDetail() - Detailed Supplier Report

4. showBuyBillsBySupplierAndDate() - Purchase Bills Query

5. showReturnBuyBillsBySupplierAndDate() - Returns Query

6. getSupplierData() - Supplier Dropdown Data

7. Net View Processing - Transaction Cleanup

🔄 Workflows

Workflow 1: Basic Supplier Report Generation

🌐 URL Routes & Actions

🧮 Calculation Methods

Operational Cost Calculation

Discount Processing

Running Balance Calculation

🧪 Testing Scenarios

Test Case 1: Basic Supplier Report

Test Case 2: Detailed Product Breakdown

Test Case 3: Net View Processing

🔒 Security & Permissions

Input Sanitization

Access Control

📊 Performance Considerations

Database Optimization Tips

🐛 Common Issues & Troubleshooting

1. Missing Transaction Links

2. Incorrect Operational Costs

3. Empty Product Arrays

4. Net View Not Working

📚 Related Documentation