Buy Report Controller Documentation

File: /controllers/buyreport.php

Purpose: Comprehensive purchase transaction reporting with supplier analysis and financial tracking

Last Updated: December 20, 2024

Total Functions: 8

Lines of Code: ~1,000+

---

📋 Overview

The Buy Report Controller is a comprehensive reporting module that provides detailed analysis of purchase transactions, supplier relationships, and financial tracking. It handles:

• • Multi-bill type purchase reporting (standard, returns, combined)
• • Supplier-specific transaction analysis
• • Date range filtering with timezone support
• • Category-based product grouping
• • Financial debt tracking and supplier payments
• • Comprehensive data aggregation and sorting
• • YouTube tutorial integration
• • Advanced search and filtering capabilities

Primary Functions

• ✅ Multi-bill type purchase reporting
• ✅ Supplier transaction analysis
• ✅ Date range filtering with timezone support
• ✅ Product category grouping and analysis
• ✅ Supplier debt tracking and payment history
• ✅ Financial totals and aggregation
• ✅ Data sorting and organization
• ✅ Export and display capabilities

Related Controllers

• • buyBillController.php - Purchase bill operations
• • returnBuyBillController.php - Purchase returns
• • buyandruternBillController.php - Combined bills
• • supplierController.php - Supplier management

---

🗄️ Database Tables

Primary Purchase Tables

🔑 Key Functions

1. Default Action (empty $do) - Main Report Display

Location: Line 188

Purpose: Generate comprehensive purchase report with filtering capabilities

Search Parameters:

$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$supplierid = filter_input(INPUT_POST, 'supplierid');
$clientid = filter_input(INPUT_POST, 'clientid');
$order = filter_input(INPUT_POST, 'order');

Query Building Logic:

$queryString = " where 1 ";   // Service bills
$queryString1 = " where 1 ";  // Purchase bills  
$queryString1R = " where 1 "; // Return bills
$queryString1SR = " where 1 "; // Combined bills

// Supplier filtering
if (isset($supplierid) && !empty($supplierid) && $supplierid != -1) {
    $queryString .= 'and billsbuy.clientid = ' . $supplierid . ' ';
    $queryString1 .= 'and buybill.buybillsupplierid = ' . $supplierid . ' ';
    $queryString1R .= 'and returnbuybill.returnbuybillsupplierid = ' . $supplierid . ' ';
    $queryString1SR .= 'and buyandruternbill.buybillsupplierid = ' . $supplierid . ' ';
}

Date Processing with Timezone Support:

if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $datefrom = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($datefrom)));
} else {
    $datefrom = $datefrom . " 00:00:00";
}

---

2. getDataNew() - Enhanced Data Aggregation

Purpose: Aggregate data from multiple bill types with advanced processing

Function Signature:

function getDataNew($queryString, $queryString1, $queryString1R, $queryString1SR, $order)

Multi-Source Data Collection:

1. Service bills (billsbuy)

2. Standard purchase bills (buybill)

3. Purchase return bills (returnbuybill)

4. Combined bills (buyandruternbill)

Data Processing Flow:

• • Aggregate quantities and totals by product
• • Handle multiple bill types for same products
• • Apply sorting and organization logic
• • Calculate financial summaries

---

3. getData() - Standard Data Aggregation

Purpose: Basic data aggregation without enhanced features

Differences from getDataNew():

• • Simplified processing logic
• • Basic aggregation only
• • Legacy support functionality

---

4. sortByTotal() - Data Sorting Algorithm

Purpose: Sort aggregated data by various criteria

Function Signature:

function sortByTotal($type, $allDataArrTemp, $productTotalArrTemp)

Sorting Options:

• • By total amount
• • By quantity
• • By supplier
• • By date range
• • Custom sorting logic

---

5. getAllSubCat() - Category Hierarchy Navigation

Purpose: Recursively traverse product category hierarchy

Function Signature:

function getAllSubCat($catid, $mode)

Mode Options:

• • 1 - Get all sub-categories
• • 2 - Get last level categories only

Category Processing:

$result = $productCatExt->queryByParentExt($catid);
if (count($result) > 0) {
    foreach ($result as $data) {
        if ($mode == 1) {
            $catsIDS .= "," . $data->productCatId;
            getAllSubCat($data->productCatId, $mode);
        } elseif ($mode == 2) {
            $childData = $productCatExt->queryByParentExt($data->productCatId);
            if (count($childData) == 0) {
                array_push($lastLevelCatIDS, $data->productCatId);
            }
        }
    }
}

---

6. getSupplierDept() - Supplier Debt Calculation

Purpose: Calculate current supplier debt balance

Function Signature:

function getSupplierDept($supplierId)

Debt Calculation Logic:

• • Load current supplier debt
• • Account for currency differences
• • Include pending transactions
• • Return formatted debt amount

---

7. getCLientPayedDeptData() - Payment History Analysis

Purpose: Analyze supplier payment history within date range

Function Signature:

function getCLientPayedDeptData($supplierid, $datefrom, $dateto)

Payment Analysis:

• • Payment amounts by date
• • Payment method tracking
• • Currency conversion handling
• • Payment trend analysis

---

8. getDataNewWithClient() - Client-Integrated Reporting

Purpose: Enhanced reporting with client relationship data

Function Signature:

function getDataNewWithClient($queryString, $queryString1, $queryString1R, $queryString1SR, $order, $queryStringClient, $queryStringClient1R, $queryStringClient1SR)

Client Integration Features:

• • Cross-reference supplier and client data
• • B2B transaction analysis
• • Reseller relationship tracking
• • Commission and markup analysis

---

🔄 Workflows

Workflow 1: Comprehensive Purchase Report Generation

---

🌐 URL Routes & Actions

Date Filtering:

• • datefrom - Start date for report (YYYY-MM-DD)
• • dateto - End date for report (YYYY-MM-DD)
• • Timezone adjustment via programsettings.reportsPlusHours

Entity Filtering:

• • supplierid - Filter by specific supplier (-1 for all)
• • clientid - Client filter for cross-reference
• • order - Sort order (asc/desc/no)

Category Filtering (via global variables):

• • $catsIDS - Comma-separated category ID list
• • $lastLevelCatIDS - Array of leaf category IDs

---

🧮 Calculation Methods

Date Range Processing with Timezone

// Timezone adjustment calculation
if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24; // +24 for end of day
    $adjustedDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($inputDate)));
}

Multi-Bill Type Aggregation

// Data aggregation across bill types
$totalQuantity = 0;
$totalAmount = 0;

// Service bills
foreach ($serviceBillData as $bill) {
    $totalQuantity += $bill->quantity;
    $totalAmount += $bill->finalnetbillvalue;
}

// Purchase bills  
foreach ($purchaseBillData as $bill) {
    $totalQuantity += $bill->buybilldetailquantity;
    $totalAmount += $bill->buybillfinalbill;
}

// Return bills (subtract)
foreach ($returnBillData as $bill) {
    $totalQuantity -= $bill->returnbuybilldetailquantity;
    $totalAmount -= $bill->returnbuybillfinalbill;
}

Supplier Debt Calculation

function getSupplierDept($supplierId) {
    global $supplierDAO;
    
    if ($supplierId > 0) {
        $supplierData = $supplierDAO->load($supplierId);
        return $supplierData->suppliercurrentDebt;
    }
    return 0;
}

---

🔒 Security & Permissions

User Authentication

include_once("../public/authentication.php");

User Group Permissions

$userGroupId = $_SESSION['usergroupid'];
$loadUserGroupData = $myUserGroupRecord->load($userGroupId);
$smarty->assign("loadUserGroupData", $loadUserGroupData);

Input Validation

// Secure input filtering
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$supplierid = filter_input(INPUT_POST, 'supplierid');

// Order validation
if ($order != "asc" && $order != "desc") {
    $order = "no";
}

---

📊 Performance Considerations

Database Optimization

1. Required Indexes:

   CREATE INDEX idx_buybill_supplier_date ON buybill(buybillsupplierid, buybilldate);
   CREATE INDEX idx_returnbuybill_supplier_date ON returnbuybill(returnbuybillsupplierid, returnbuybilldate);
   CREATE INDEX idx_billsbuy_client_date ON billsbuy(clientid, billdate);
   ```

2. **Query Optimization**:
   - Use appropriate date range queries
   - Limit result sets with proper WHERE clauses
   - Efficient JOIN operations for detail data

3. **Memory Management**:
   - Large date ranges may consume significant memory
   - Consider pagination for very large datasets
   - Optimize data aggregation algorithms

### Performance Monitoring

// Add performance tracking

$startTime = microtime(true);

// Report generation code...

$endTime = microtime(true);

$executionTime = ($endTime - $startTime);

error_log("Report generation time: " . $executionTime . " seconds");

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Date Range Results**
**Issue**: Reports show wrong data for date ranges  
**Cause**: Timezone adjustment errors or date format issues

**Debug**:

echo "Original date: " . $datefrom . "
";

echo "Adjusted date: " . $adjustedDate . "
";

echo "Timezone hours: " . $Programsetting->reportsPlusHours . "
";

### 2. **Supplier Debt Calculation Errors**
**Issue**: Debt amounts don't match expected values  
**Cause**: Currency conversion or debt change tracking issues

**Debug**:

function debugSupplierDebt($supplierId) {

global $supplierDAO, $supplierDeptChangeExt;

$supplier = $supplierDAO->load($supplierId);

$debtChanges = $supplierDeptChangeExt->queryBySupplierId($supplierId);

echo "Current debt: " . $supplier->suppliercurrentDebt . "
";

echo "Debt changes count: " . count($debtChanges) . "
";

foreach ($debtChanges as $change) {

echo "Change: " . $change->supplierdebtchangeamount . " Type: " . $change->supplierdebtchangetype . "
";

}

}

### 3. **Data Aggregation Inconsistencies**
**Issue**: Totals don't match when filtering by different criteria  
**Cause**: Logic errors in data collection or aggregation

**Debug**:

// Log aggregation steps

foreach ($billTypes as $type => $data) {

echo "$type bills count: " . count($data) . "
";

$typeTotal = array_sum(array_column($data, 'amount'));

echo "$type total: $typeTotal
";

}

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Purchase Report

1. Select date range (last 30 days)

2. Select specific supplier

3. Generate report

4. Verify all bill types included

5. Check financial totals accuracy

### Test Case 2: Multi-Supplier Analysis

1. Run report for all suppliers

2. Verify supplier-specific data separation

3. Check debt calculations per supplier

4. Validate payment history accuracy

### Test Case 3: Date Range with Timezone

1. Set specific timezone in program settings

2. Run report with date range

3. Verify timezone adjustments applied

4. Check edge cases (date boundaries)

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • buyBillController.md - Purchase operations
• • supplierController.md - Supplier management
• • Reporting Framework Documentation - Complete reporting system

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When purchase reporting requirements change