Order Suppliers With Buy Controller Documentation

File: /controllers/orderSuppliersWithBuy.php

Purpose: Comprehensive supplier purchase analysis with transaction summaries, payment tracking, and procurement reporting

Last Updated: December 20, 2024

Total Functions: 5

Lines of Code: ~701

---

📋 Overview

The Order Suppliers With Buy Controller provides comprehensive analysis of supplier purchase activities across multiple bill types. It aggregates data from various purchase and billing systems to provide unified reporting on:

• Supplier purchase performance across multiple bill types
• Payment history and debt tracking for suppliers
• Purchase summaries with discounts and returns
• Transaction analysis with detailed breakdowns
• Store and branch-specific procurement reporting
• Supplier payment analysis and debt management

Primary Functions

✅ Aggregate purchase data across multiple bill types (buybill, returnbuybill, buyandruternbill, billsbuy)
✅ Calculate supplier payment totals and debt changes
✅ Track purchase discounts and returns by supplier
✅ Filter by date ranges, stores, and branches
✅ Provide detailed transaction breakdowns
✅ Support real-time supplier data analysis

Related Controllers

• buyBillController.php - Purchase bill processing
• supplierReportsController.php - Supplier reporting
• returnBuyBillController.php - Purchase returns
• supplierPayedDeptController.php - Supplier payment processing

---

🗄️ Database Tables

Primary Purchase Tables

Table Name	Purpose	Key Columns
buybill	Purchase bills	buybillid, buybillsupplierid, buybilltotalbill, buybillaftertotalbill, buybilltotalpayed, buybilldiscount, buybilldiscounttype, conditions
returnbuybill	Purchase return bills	returnbuybillid, returnbuybillsupplierid, returnbuybilltotalbill, returnbuybillaftertotalbill, returnbuybilltotalpayed, conditions
buyandruternbill	Combined purchase/returns	buybillid, buybillsupplierid, buybillprice, returnbuybillprice, buybilldate, conditions

### Detail Tables

Table Name	Purpose	Key Columns
buybilldetail	Purchase bill line items	buybilldetailid, buybillid, buybilldetailproductid, buybilldetailquantity
returnbuybilldetail	Return bill line items	returnbuybilldetailid, returnbuybillid, returnbuybilldetailproductid, returnbuybilldetailquantity
buyandruternbilldetail	Combined bill line items	buyandruternbilldetailid, buybillid, buybilldetailproductid, buybilldetailquantity

### Reference 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
user	System users	userid, employeename, username, userstoreid, branchId

---

🔑 Key Functions

1. Default Action - Purchase Analysis Dashboard

Location: Lines 151-225

Purpose: Main purchase analysis interface with filtering options

Function Signature:

// Triggered when: empty $do
// Includes: Date ranges, store filters, user permissions

Process Flow:

1. Load filtering options (stores, users, branches)

2. Process search parameters from POST

3. Build dynamic query strings for different bill types

4. Apply date filtering with program settings

5. Call getData() to aggregate results

6. Display via orderSuppliersWithBuys/show.html

Key Features:

• Date Range Processing: Handles program-specific hour adjustments
• Store/Branch Filtering: Filters by specific stores or branches
• User Permissions: Respects user store and branch assignments
• Category Filtering: Supports product category filtering

Query String Building:

$queryString = " where 1 ";        // bills
$queryStringR = " where 1 ";       // billsreturn
$queryString1 = " where 1 and buybill.conditions = 0 ";     // buybill
$queryString1R = " where 1 and returnbuybill.conditions = 0 "; // returnbuybill
$queryString1SR = " where 1 and buyandruternbill.conditions = 0 "; // buyandruternbill

// Apply date filters
if (isset($datefrom) && !empty($datefrom)) {
    $queryString1 .= ' and buybill.buybilldate >= "' . $datefrom . '" ';
    $queryString1R .= ' and returnbuybill.returnbuybilldate >= "' . $datefrom . '" ';
    $queryString1SR .= ' and buyandruternbill.buybilldate >= "' . $datefrom . '" ';
}

---

2. getData() - Core Purchase Data Aggregation

Location: Lines 234-418

Purpose: Aggregate purchase data across all bill types for suppliers

Function Signature:

function getData($datefrom, $dateto, $queryString, $queryStringR, $queryString1, $queryString1R, $queryString1SR)

Process Flow:

1. Define supplierData class for result aggregation

2. Query each bill type with respective filters

3. Aggregate data by supplier ID

4. Calculate totals and discounts

5. Get payment data for each supplier

6. Assign results to template

Supplier Data Structure:

class supplierData {
    public $supplierid;
    public $suppliername;
    public $totalbuy = 0;
    public $totalreturn = 0;
    public $totaldiscount = 0;
    public $supplierdebt = 0;
}

Purchase Bill Processing:

foreach ($buyBillData as $key => $value) {
    if (in_array($value->buybillsupplierid, $existId)) {
        $key = array_search($value->buybillsupplierid, $existId);
        $mysupplier = $buybillDataArr[$key];
    } else {
        $mysupplier = new supplierData();
        $mysupplier->supplierid = $value->buybillsupplierid;
        array_push($existId, $value->buybillsupplierid);
    }
    
    $mysupplier->suppliername = $value->suppliername;
    $mysupplier->totalbuy = $value->allGenTotal;
    $mysupplier->buybillaftertotalbill += $value->buybillaftertotalbill;
    $mysupplier->supplierdebt = $value->supplierdebt;
    $mysupplier->buydis += $value->allGenTotal - $value->buybillaftertotalbill;
}

---

3. getSupplierPayedDeptData() - Supplier Payment Analysis

Location: Lines 494-550

Purpose: Calculate comprehensive payment history for suppliers

Function Signature:

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

Process Flow:

1. Query supplier debt changes with date filtering

2. Process different payment transaction types

3. Handle bill payments vs separate payments

4. Calculate running payment totals

5. Return net payment amount

Payment Processing Logic:

$supDeptChanges = $supplierDeptChangeExt->queryBySupplierIdNotDeleted($supplierid, $qString);
if ($supDeptChanges) {
    foreach ($supDeptChanges as $change) {
        if (in_array($change->tablename, ["datedCheckedController.php", "checkwithdrawalController.php"])) {
            // Check payments
            if ($change->supplierdebtchangetype == 0)
                $payed -= $change->supplierdebtchangeamount; // Debt increase
            else
                $payed += $change->supplierdebtchangeamount; // Payment
        } elseif ($change->tablename == "buyBillController.php") {
            // Bill payments
            $buybillid = $buyBillDAO->load($change->supplierdebtchangemodelid);
            if ($buybillid->conditions == 0)
                $payed += $buybillid->buybilltotalpayed;
        }
    }
}

---

4. showBuyBillsBySupplierAndDate() - Purchase Bill Query

Location: Lines 552-625

Purpose: Retrieve and process purchase bills for specific supplier/date range

Function Signature:

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

Process Flow:

1. Query regular purchase bills

2. Query combined purchase/return bills

3. Count product quantities for each bill

4. Apply discount calculations

5. Filter out cancelled bills

6. Return merged dataset

Discount Processing:

if ($buybilldiscounttype == 1) {
    // Fixed amount discount
    $buybillprice = ($bill->buybillprice) - ($bill->buybilldiscount);
} else {
    // Percentage discount
    $buybillprice = ($bill->buybillprice) - ((($bill->buybillprice) * ($bill->buybilldiscount)) / 100);
}

---

5. showReturnBuyBillsByClientAndDate() - Purchase Returns Query

Location: Lines 628-700

Purpose: Retrieve and process purchase return bills for analysis

Function Signature:

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

Process Flow:

1. Query return bills from returnbuybill table

2. Query return portions from buyandruternbill table

3. Count returned quantities

4. Calculate return totals

5. Return combined datasets

Return Data Processing:

foreach ($buybillandruternData as $buy) {
    $buy->userid = -1;
    $returnbuybillprice = ($buy->returnbuybillprice);
    
    // Map return bill fields
    $buy->returnbuybillid = $buy->buybillid;
    $buy->returnbuybillserial = $buy->buybillserial;
    $buy->returnbuybillaftertotalbill = $buy->buybillaftertotalbill;
    $buy->returnbuybilldate = $buy->buybilldate;
    
    $totalQty2 = $buyAndReturnBillDetailExt->countProductInReturnBuyBillsOnly($buy->buybillid);
    $buy->buyQuantity = $totalQty2;
}

---

🔄 Workflows

Workflow 1: Comprehensive Purchase Analysis

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

START: Purchase Analysis Request

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

▼

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

1Process Filter Parameters

- Date range (with program hour adjustments)

- Store/branch filtering

- User permission checking

- Category filtering (if specified)

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

▼

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

2Build Dynamic Queries

- Purchase bill query (buybill)

- Return bill query (returnbuybill)

- Combined bill query (buyandruternbill)

- Service bill queries (billsbuy, billsreturn)

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

▼

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

3Execute Aggregation Queries

FOR EACH bill type:

→ Apply date and filter conditions

→ Join with supplier data

→ Aggregate totals by supplier

│ └─→ Calculate discounts and adjustments │

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

▼

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

4Merge Supplier Data

- Combine results from all bill types

- Resolve supplier information conflicts

- Calculate net purchases vs returns

- Sum discount amounts

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

▼

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

5Calculate Payment Data

FOR EACH supplier:

→ Query debt change transactions

→ Process bill payments vs separate payments

→ Calculate net payment totals

│ └─→ Handle payment type classifications │

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

▼

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

6Generate Report Output

- Format supplier purchase summaries

- Include payment analysis

- Show discount breakdowns

- Display via template system

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default action	Main purchase analysis dashboard

### Required Parameters

Main Report (do= empty):

• datefrom - Start date (optional, defaults to today)
• dateto - End date (optional, defaults to today)
• storeid - Store filter (optional)
• branchId - Branch filter (optional)

---

🧮 Calculation Methods

Purchase Total Calculation

// Purchase bill totals
$mysupplier->totalbuy = $value->allGenTotal;
$mysupplier->buybillaftertotalbill += $value->buybillaftertotalbill;

// Return bill totals
$mysupplier->totalreturn += $value->allGenTotalReturn;
$mysupplier->returnbuybillaftertotalbill += $value->returnbuybillaftertotalbill;

// Net calculations
$t_data->t_bure = $t_data->buybillaftertotalbill - $t_data->returnbuybillaftertotalbill;
$t_data->t_dis = $t_data->buydis - $t_data->returnbuydis + $t_data->buyandreturndis;

Discount Processing

// Fixed amount discount
if ($buybilldiscounttype == 1) {
    $discount = $bill->buybilldiscount;
}
// Percentage discount
else {
    $discount = $bill->buybillprice * $bill->buybilldiscount / 100;
}

Payment Calculation Logic

foreach ($supDeptChanges as $change) {
    if ($change->tablename == "buyBillController.php") {
        // Payment included in bill
        $buybillid = $buyBillDAO->load($change->supplierdebtchangemodelid);
        $payed += $buybillid->buybilltotalpayed;
    } else {
        // Separate payment transaction
        if ($change->supplierdebtchangetype == 0) {
            $payed -= $change->supplierdebtchangeamount; // Debt increase
        } else {
            $payed += $change->supplierdebtchangeamount; // Payment made
        }
    }
}

---

🔒 Security & Permissions

Authentication Requirements

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

User Permission Filtering

$user = $userDAO->load($_SESSION['userid']);
if ($user->userstoreid == 0) {
    // User can see all stores
    $storeData = loadStore();
}
if ($user->branchId == 0) {
    // User can see all branches  
    $branchData = loadBranch();
}

Input Sanitization

$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$storeid = (int) filter_input(INPUT_POST, 'storeid');

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- buybill(buybillsupplierid, buybilldate, conditions)

- returnbuybill(returnbuybillsupplierid, returnbuybilldate, conditions)

- buyandruternbill(buybillsupplierid, buybilldate, conditions)

- supplierdebtchange(supplierid, supplierdebtchangedate)

- supplier(supplierid)

2. Query Optimization:

- Use appropriate date range filtering

- Efficient JOINs for supplier data

- Batch process payment calculations

- Limit results with proper WHERE clauses

3. Memory Management:

- Process large supplier lists in chunks

- Avoid loading unnecessary detail data

- Clean up temporary arrays after processing

---

🐛 Common Issues & Troubleshooting

1. Incorrect Purchase Totals

Issue: Purchase totals don't match individual bill totals

Cause: Missing discount calculations or double-counting

Debug Query:

SELECT 
    SUM(buybilltotalbill) as gross_total,
    SUM(buybillaftertotalbill) as net_total,
    SUM(buybilldiscount) as total_discount
FROM buybill 
WHERE buybillsupplierid = [SUPPLIER_ID]
AND buybilldate BETWEEN '[START]' AND '[END]'
AND conditions = 0;

2. Payment Calculation Errors

Issue: Payment totals don't match actual transactions

Cause: Double-counting bill payments vs separate payments

Verification:

// Check payment processing
foreach ($buybillData as $data) {
    if ($data->buybilltotalpayed != 0 && $data->conditions == 0) {
        echo "Bill {$data->buybillid}: Payment {$data->buybilltotalpayed}";
    }
}

3. Date Range Issues

Issue: Reports show no data for valid date ranges

Cause: Hour adjustment conflicts or timezone issues

Debug:

echo "Processed dates: $datefrom - $dateto";
if (isset($Programsetting->reportsPlusHours)) {
    echo "Hour adjustment: " . $Programsetting->reportsPlusHours;
}

4. Supplier Name Resolution

Issue: Supplier names not showing correctly

Cause: Missing supplier records or JOIN issues

Fix Query:

-- Check for missing suppliers
SELECT DISTINCT buybillsupplierid 
FROM buybill 
WHERE buybillsupplierid NOT IN (SELECT supplierid FROM supplier);

---

🧪 Testing Scenarios

Test Case 1: Comprehensive Purchase Report

1. Create test data:
   - Multiple suppliers with different purchases
   - Purchase bills, returns, combined bills
   - Various discount types and amounts
2. Run report with date range
3. Verify totals match database sums
4. Check discount calculations
5. Validate payment totals

Test Case 2: Supplier Payment Analysis

1. Create complex payment scenarios:
   - Bill payments vs separate payments
   - Multiple payment types
   - Check deposits and withdrawals
2. Run getSupplierPayedDeptData for suppliers
3. Verify payment totals are accurate
4. Check debt change transaction processing

Test Case 3: Store Filtering

1. Create purchases in different stores
2. Apply store filters
3. Verify only matching transactions included
4. Test user permission restrictions
5. Check branch-level filtering

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• buyBillController.md - Purchase operations
• supplierReportsController.md - Supplier reporting
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

OrderSuppliersWithBuy Documentation

Order Suppliers With Buy Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Purchase Tables

🔑 Key Functions

1. Default Action - Purchase Analysis Dashboard

2. getData() - Core Purchase Data Aggregation

3. getSupplierPayedDeptData() - Supplier Payment Analysis

4. showBuyBillsBySupplierAndDate() - Purchase Bill Query

5. showReturnBuyBillsByClientAndDate() - Purchase Returns Query

🔄 Workflows

Workflow 1: Comprehensive Purchase Analysis

🌐 URL Routes & Actions

🧮 Calculation Methods

Purchase Total Calculation

Discount Processing

Payment Calculation Logic

🔒 Security & Permissions

Authentication Requirements

User Permission Filtering

Input Sanitization

📊 Performance Considerations

Database Optimization Tips

🐛 Common Issues & Troubleshooting

1. Incorrect Purchase Totals

2. Payment Calculation Errors

3. Date Range Issues

4. Supplier Name Resolution

🧪 Testing Scenarios

Test Case 1: Comprehensive Purchase Report

Test Case 2: Supplier Payment Analysis

Test Case 3: Store Filtering

📚 Related Documentation