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

Table Name	Purpose	Key Columns
buybill	Purchase bills	buybillid, buybillsupplierid, buybilldate, buybillfinalbill
buybilldetail	Purchase bill items	buybilldetailid, buybillid, buybilldetailproductid, buybilldetailquantity
returnbuybill	Purchase returns	returnbuybillid, returnbuybillsupplierid, returnbuybilldate
returnbuybilldetail	Return bill items	returnbuybilldetailid, returnbuybillid, returnbuybilldetailproductid
buyandruternbill	Combined bills	buybillid, buybillsupplierid, buybilldate
buyandruternbilldetail	Combined bill items	buybilldetailid, buybillid, buybilldetailproductid

### Service Bill Tables

Table Name	Purpose	Key Columns
billsbuy	Service purchase bills	billsbuyid, clientid, billdate, finalnetbillvalue
billsproductsbuy	Service bill items	billsproductsbuyid, billsbuyid, productid, quantity

### Supporting Tables

Table Name	Purpose	Key Columns
supplier	Supplier master data	supplierid, suppliername, suppliercurrentDebt
supplierdebtchange	Supplier debt history	supplierdebtchangeid, supplierid, supplierdebtchangeamount
product	Product catalog	productid, productname, productCatId
productcat	Product categories	productCatId, productCatName, parentExt
productunit	Product units	productunitid, productid, unitid
storedetail	Inventory levels	storedetailid, productid, storeid, productquantity

---

🔑 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

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

START: Purchase Report Request

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

▼

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

1Initialize Report Parameters

- Load user permissions and settings

- Load suppliers and clients for filters

- Load YouTube tutorial links

- Initialize query builders

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

▼

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

2Process Search Filters

- Supplier selection and validation

- Date range with timezone adjustments

- Client filtering (if applicable)

- Sort order specification

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

▼

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

3Build Dynamic Query Strings

FOR EACH bill type:

│

→ Service bills (billsbuy)

│ │ └─ Apply supplier and date filters │

│

→ Purchase bills (buybill)

│ │ └─ Apply supplier and date filters │

│

→ Return bills (returnbuybill)

│ │ └─ Apply supplier and date filters │

│

│ └─→ Combined bills (buyandruternbill) │

│ └─ Apply supplier and date filters │

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

▼

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

4Execute Data Collection

- Query service bills and details

- Query purchase bills and details

- Query return bills and details

- Query combined bills and details

- Aggregate data by product and supplier

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

▼

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

5Process and Enhance Data

- Calculate quantities and totals

- Apply currency conversions

- Generate financial summaries

- Sort by specified criteria

- Add supplier debt information

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

▼

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

6Generate Report Output

- Assign data to template variables

- Include search parameters for display

- Add summary totals and statistics

- Display via reporting template

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default action	Main purchase report with full filtering

### Required/Optional Parameters

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

php

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

php

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

php

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

php

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

📚 ERP Docs

Buyreport Documentation

Buy Report Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Purchase Tables

🔑 Key Functions

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

2. getDataNew() - Enhanced Data Aggregation

3. getData() - Standard Data Aggregation

4. sortByTotal() - Data Sorting Algorithm

5. getAllSubCat() - Category Hierarchy Navigation

6. getSupplierDept() - Supplier Debt Calculation

7. getCLientPayedDeptData() - Payment History Analysis

8. getDataNewWithClient() - Client-Integrated Reporting

🔄 Workflows

Workflow 1: Comprehensive Purchase Report Generation

🌐 URL Routes & Actions

🧮 Calculation Methods

Date Range Processing with Timezone

Multi-Bill Type Aggregation

Supplier Debt Calculation

🔒 Security & Permissions

User Authentication

User Group Permissions

Input Validation

📊 Performance Considerations

Database Optimization

📚 Related Documentation