Buy Bill Reports Controller Documentation

File: /controllers/buyBillreportController.php

Purpose: Generates comprehensive reports for purchase bills, supplier transactions, and inventory serial tracking

Last Updated: December 20, 2024

Total Functions: 10

Lines of Code: ~629

---

📋 Overview

The Buy Bill Reports Controller is a specialized reporting module that provides detailed purchase bill analysis and inventory tracking capabilities. It handles:

• • Purchase bill reports by supplier, date range, and bill details
• • Product serial number tracking (sold and unsold serials)
• • Supplier purchase history and transaction analysis
• • Inventory movement reports for serialized products
• • Purchase bill totals, taxes, and discount calculations
• • Multi-format report output (HTML templates)
• • Date range filtering with time zone support
• • Cross-referenced serial tracking between purchases and sales

Primary Functions

• ✅ Generate supplier purchase reports
• ✅ Track product serial numbers (in stock vs sold)
• ✅ Purchase bill analysis with totals
• ✅ Date range filtering and analysis
• ✅ Serial number search and tracking
• ✅ Purchase quantity calculations
• ✅ Discount and tax processing
• ✅ Supplier transaction history
• ✅ Store-based inventory tracking
• ✅ Combined buy and return bill analysis

Related Controllers

• • buyBillController.php - Purchase operations
• • returnBuyBillController.php - Purchase returns
• • supplierController.php - Supplier management
• • productController.php - Product management
• • storereportController.php - Store reports
• • sellbillController.php - Sales operations

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Purchase Bill Reports

Location: Line 340

Purpose: Generate comprehensive purchase bill reports with filtering and calculations

Function Signature:

// Triggered when: do=show or empty $do
$supplierId = $_REQUEST['supplierId'];
$serial = $_REQUEST['serial'];
$buybillid = $_REQUEST['buybillid'];
$from = $_REQUEST['from']; 
$to = $_REQUEST['to'];

Process Flow:

1. Build dynamic query string with supplier, serial, bill ID, and date filters

2. Handle time zone adjustments via reportsPlusHours setting

3. Query regular purchase bills (buybill table)

4. Query combined buy/return bills (buyandruternbill table)

5. Calculate totals for each bill:

- Total bill amounts

- Product quantities

- Discount processing (fixed vs percentage)

- Tax calculations

6. Merge datasets and assign to template

7. Display via buyBillreportview/show.html

Key Calculations:

// Discount processing
if ($buybilldiscountrype == 0) { // Fixed amount
    $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
} else { // Percentage
    $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;
    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
}

// Tax calculation
$taxvalue = $buybillaftertotalbill - ($buybilltotalbill - $discountvalue);

Template Variables:

• • $buyBillData - Combined purchase bill data
• • $totalBills - Sum of all bill amounts
• • $totalqty - Total product quantities
• • $totaldiscount - Total discounts applied
• • $totaltax - Total tax amounts

---

2. notSoldSerials Action - Serial Number Tracking

Location: Line 277

Purpose: Track product serial numbers between inventory and sales

Process Flow:

1. Receive search parameters:

- storeid - Store/warehouse filter

- product - Product ID (supports size/color variants)

- client - Customer filter for sold serials

- productserial - Specific serial number search

2. Build product query with size/color support:

   if (strpos($product, "hasSizeColor") !== false) {
       $productIdComplex = explode('-', str_replace("hasSizeColor", "", $product));
       $product = $productIdComplex[0];
       $sizeId = $productIdComplex[1];
       $colorId = $productIdComplex[2];
       $productQuery = " and productserial.productid = $product and productserial.sizeid =$sizeId and productserial.colorid=$colorId ";
   }
   ```
3. Query inventory serials via `ProductserialMySqlExtDAO`
4. Query sold serials via `SoldserialproductMySqlExtDAO`
5. Filter for sales only: `sellbilltype in(0,1)`
6. Display via `buyBillreportview/notSoldSerials.html`

**Search Modes**:
- **By Serial Number**: Find specific serial across inventory and sales
- **By Store**: List all serials in a warehouse/store
- **By Customer**: Show serials sold to specific customer
- **By Product**: All serials for a product (with size/color variants)

**Template Variables**:
- `$inStoresSerial` - Available inventory serials
- `$soldSerials` - Sold/transferred serials
- `$allStores` - Store dropdown data

---

### 3. **getBuyBillNames()** - Bill Template Loader
**Location**: Line 540  
**Purpose**: Load purchase bill templates and configurations

**Function Signature**:

function getBuyBillNames()

**Process Flow**:
1. Query `billname` table for type = 1 (purchase bills)
2. Return available bill templates for dropdowns

**Returns**: Array of bill name objects for template selection

---

### 4. **loadBillProperties()** - Bill Configuration Loader
**Location**: Line 549  
**Purpose**: Load specific bill template settings and properties

**Function Signature**:

function loadBillProperties($billnameid)

**Process Flow**:
1. Query `billsettings` table by bill name ID
2. Return configuration settings for the selected bill template

**Usage**: Used by bill creation forms to load template-specific settings

---

### 5. **getProducts()** - Product Catalog with Categories
**Location**: Line 558  
**Purpose**: Load complete product list with category hierarchy

**Function Signature**:

function getProducts()

**Process Flow**:
1. Query all products via `ProductMySqlExtDAO->queryAllExt()`
2. For each product, build category path:
   - Call `fetch_recursive()` to traverse category tree
   - Build hierarchical category string
   - Assign to template variables `$names{N}`
3. Return enriched product array

**Category Processing**:
- Builds full category paths (e.g., "Electronics/Computers/Laptops/")
- Handles multi-level category hierarchies
- Provides dropdown-friendly category names

---

### 6. **fetch_recursive()** - Category Tree Traversal
**Location**: Line 577  
**Purpose**: Recursively build category hierarchy paths

**Function Signature**:

function fetch_recursive($parentid, $categories)

**Process Flow**:
1. Load category data by parent ID
2. Append current category name to path
3. Check for parent category (`productCatParent != 0`)
4. Recursively call for parent categories
5. Return complete category path string

**Usage**: Called by `getProducts()` to build category breadcrumbs

---

### 7. **getSuppliers()** - Supplier Dropdown Data
**Location**: Line 596  
**Purpose**: Load active suppliers for report filtering

**Function Signature**:

function getSuppliers()

**Process Flow**:
1. Query suppliers with condition = 0 (active/undeleted)
2. Return array for dropdown population

**Returns**: Array of supplier objects for filter dropdowns

---

### 8. **getProductCatParents()** - Category Hierarchy
**Location**: Line 602  
**Purpose**: Load product category structure for filtering

**Function Signature**:

function getProductCatParents()

**Process Flow**:
1. Query all product categories via `queryAllCategories()`
2. Return hierarchical category structure

**Returns**: Category tree for product filtering dropdowns

---

### 9. **checkRoundNumbers()** - Number Formatting Settings
**Location**: Line 610  
**Purpose**: Check system setting for number rounding preferences

**Function Signature**:

function checkRoundNumbers()

**Process Flow**:
1. Query program settings for rounding configuration
2. Return rounding status for display formatting

**Returns**: Boolean/setting for number display formatting

---

### 10. **showBuyBills()** - Purchase Bill Serials
**Location**: Line 623  
**Purpose**: Load purchase bill serial numbers for dropdown filtering

**Function Signature**:

function showBuyBills()

**Process Flow**:
1. Query all non-deleted purchase bills
2. Extract serial numbers for filter dropdown

**Returns**: Array of bill serials for report filtering

---

## 🔄 Workflows

### Workflow 1: Purchase Bill Report Generation

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

│ START: Select Filters (Supplier/Date/Serial) │

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

│

▼

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

│ 1. Build Query Parameters │

│ - Parse supplier filter │

│ - Parse date range with timezone │

│ - Parse bill serial/ID filters │

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

│

▼

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

│ 2. Query Purchase Bills │

│ - Query buybill table (regular purchases) │

│ - Query buyandruternbill table (combined bills) │

│ - Apply WHERE clause filters │

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

│

▼

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

│ 3. Process Each Bill Record │

│ FOR EACH purchase bill: │

│ │ │

│ ├─→ Load bill detail items │

│ │ ├─ Product quantities │

│ │ ├─ Unit prices │

│ │ └─ Line totals │

│ │ │

│ ├─→ Calculate discount amount │

│ │ ├─ Fixed amount vs percentage │

│ │ └─ Add detail-level discounts │

│ │ │

│ ├─→ Calculate tax amount │

│ │ └─ (After-discount total - pre-discount) = tax │

│ │ │

│ └─→ Sum quantities and amounts │

│ ├─ Total bill amount │

│ ├─ Total product quantities │

│ └─ Running discount/tax totals │

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

│

▼

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

│ 4. Generate Report Output │

│ - Merge regular and combined bill data │

│ - Assign totals to template variables │

│ - Display via buyBillreportview/show.html │

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

---

### Workflow 2: Serial Number Tracking Report

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

│ START: Search by Serial/Store/Product/Client │

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

│

▼

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

│ 1. Parse Search Parameters │

│ - Detect product size/color variants │

│ - Build product query filters │

│ - Set search mode (serial/store/product/client) │

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

│

▼

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

│ 2. Query Inventory Serials │

│ IF searching by: │

│ ├─ Serial Number → Find exact match │

│ ├─ Store ID → All serials in store │

│ ├─ Product → All serials for product+variants │

│ └─ Client → No inventory query (sales only) │

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

│

▼

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

│ 3. Query Sold Serials │

│ IF searching by: │

│ ├─ Serial Number → Find if sold │

│ ├─ Store ID → No sold query (inventory only) │

│ ├─ Product → All sold serials for product │

│ └─ Client → All serials sold to client │

│ Apply filter: sellbilltype in(0,1) [sales only] │

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

│

▼

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

│ 4. Cross-Reference and Display │

│ - Match serial numbers between inventory and sales │

│ - Identify available vs sold status │

│ - Show customer who bought (if sold) │

│ - Display via notSoldSerials.html template │

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

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | `show()` | Purchase bill reports with filtering |
| `do=notSoldSerials` | Serial tracking | Product serial number tracking report |

### Required Parameters by Action

**Purchase Bill Reports** (`do=show`):
- `supplier` - Supplier ID (-1 for all suppliers)
- `serial` - Bill serial number (-1 for all serials)
- `buybillid` - Specific bill ID (-1 for all bills)
- `from` - Start date (YYYY-MM-DD)  
- `to` - End date (YYYY-MM-DD)

**Serial Tracking** (`do=notSoldSerials`):
- `storeid` - Store/warehouse ID (optional)
- `product` - Product ID or "hasSizeColor" variant (optional)
- `client` - Customer ID for sold serials (optional)
- `productserial` - Specific serial number (optional)

---

## 🧮 Calculation Methods

### Purchase Bill Totals

foreach ($buyBillData1 as $myBillData) {

$buybillid = $myBillData->buybillid;

$totalBills = $totalBills + $myBillData->buybillaftertotalbill;

// Sum product quantities

foreach ($buybilldetailes as $mybuybilldetailes) {

$productnumber = $mybuybilldetailes->productnumber;

$buybilldetailquantity = $mybuybilldetailes->buybilldetailquantity;

$totalqty = $totalqty + ($productnumber * $buybilldetailquantity);

}

}

### Discount Processing

// Fixed amount discount

if ($buybilldiscountrype == 0) {

$totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;

$myBillData->buybilldiscount = ($buybilldiscount + $detaildiscount);

}

// Percentage discount

else {

$discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;

$totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;

$myBillData->buybilldiscount = ($discountvalue + $detaildiscount);

}

### Tax Calculation

// Tax = After-total - (Pre-total - Discount)

$taxvalue = $buybillaftertotalbill - ($buybilltotalbill - $discountvalue);

$taxvalue = round($taxvalue, 2);

$taxvalue = ($taxvalue == -0) ? 0 : $taxvalue; // Handle negative zero

### Size/Color Product Parsing

if (strpos($product, "hasSizeColor") !== false) {

$productIdComplex = explode('-', str_replace("hasSizeColor", "", $product));

$product = $productIdComplex[0]; // Base product ID

$sizeId = $productIdComplex[1]; // Size variant ID

$colorId = $productIdComplex[2]; // Color variant ID

}

---

## 🔒 Security & Permissions

### Access Control

// Authentication required for all actions

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

### Input Sanitization

// Filter and cast input parameters

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

$product = (int) filter_input(INPUT_POST, 'product');

$client = (int) filter_input(INPUT_POST, 'client');

$productserial = filter_input(INPUT_POST, 'productserial');

### SQL Injection Prevention
- All database queries use DAO layer with parameterized queries
- String inputs filtered through `filter_input()` functions
- Integer casts applied to numeric parameters

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `buybill(buybillsupplierid, buybilldate)`
   - `buybilldetail(buybillid)`
   - `productserial(serialnumber, productid, storeid)`
   - `soldserialproduct(clientid, sellbilltype)`

2. **Query Optimization**:
   - Date filtering with proper timestamp format
   - Use of specific WHERE clauses in serial searches
   - Efficient JOIN operations in DAO extended methods

3. **Memory Management**:
   - Large date ranges may return many purchase bills
   - Serial number searches can be expensive without proper indexing
   - Category tree traversal is recursive (watch stack depth)

### Known Performance Issues

-- Serial searches without indexes can be slow

SELECT * FROM productserial ps

JOIN soldserialproduct ssp ON ps.productserialid = ssp.productserialid

WHERE ps.productid = ? AND ssp.clientid = ?;

-- Solution: Add composite indexes

CREATE INDEX idx_productserial_product_store ON productserial(productid, storeid);

CREATE INDEX idx_soldserial_client_type ON soldserialproduct(clientid, sellbilltype);

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Serial Numbers in Results**
**Issue**: Serial tracking shows empty results despite known inventory  
**Cause**: Serial numbers not properly linked between `productserial` and `soldserialproduct`

**Debug**:

SELECT COUNT(*) FROM productserial WHERE serialnumber = '[SERIAL]';

SELECT COUNT(*) FROM soldserialproduct ssp

JOIN productserial ps ON ps.productserialid = ssp.productserialid

WHERE ps.serialnumber = '[SERIAL]';

### 2. **Incorrect Purchase Totals**
**Issue**: Report totals don't match individual bill sums  
**Cause**: Mixed discount types or missing detail records

**Debug**:

SELECT bb.buybillid, bb.buybilltotalbill, bb.buybillaftertotalbill,

bb.buybilldiscount, bb.buybilldiscountrype,

SUM(bbd.buybilldetailtotalprice) as detail_total

FROM buybill bb

LEFT JOIN buybilldetail bbd ON bb.buybillid = bbd.buybillid

WHERE bb.buybillid = [ID]

GROUP BY bb.buybillid;

### 3. **Size/Color Product Parsing Errors**
**Issue**: Size/color variants not found in searches  
**Cause**: Incorrect "hasSizeColor" format or missing variant data

**Fix**:

// Verify format: "123hasSizeColor-456-789"

// Where: 123=productid, 456=sizeid, 789=colorid

if (strpos($product, "hasSizeColor") !== false && strpos($product, "-") !== false) {

// Process variant

} else {

// Handle as regular product

}

### 4. **Time Zone Issues in Date Filtering**
**Issue**: Reports miss transactions due to time zone differences  
**Cause**: `reportsPlusHours` setting not applied correctly

**Fix**:

// Ensure proper time zone adjustment

$Programsetting = $ProgramsettingDAO->load(1);

if (isset($Programsetting->reportsPlusHours)) {

$reportsPlusHours = $Programsetting->reportsPlusHours + 24; // +24 for end of day

$to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($to)));

}

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Purchase Report

1. Select supplier with recent purchases

2. Set date range covering known transactions

3. Verify bill count matches database

4. Check total calculations (amounts, quantities, discounts, tax)

5. Confirm all bill types included (regular + combined)

### Test Case 2: Serial Number Tracking

1. Create test products with serial numbers

2. Record purchase with specific serials

3. Create sale bill using some serials

4. Search by serial number and verify status

5. Search by store and verify inventory

6. Search by customer and verify sold items

### Test Case 3: Size/Color Variant Handling

1. Create product with size and color variants

2. Add serials for different size/color combinations

3. Search using "hasSizeColor" format

4. Verify correct variant filtering

5. Test edge cases (missing variants, invalid format)

### Test Case 4: Discount and Tax Calculations

1. Create bills with fixed amount discounts

2. Create bills with percentage discounts

3. Add detail-level discounts

4. Verify total calculations match manual computation

5. Test edge cases (zero tax, 100% discount)

### Debug Mode Enable

// Add at top of controller for debugging

error_reporting(E_ALL);

ini_set('display_errors', 1);

// Debug query building in show()

echo "Query String: " . $queryString . "
";

// Debug serial search logic

echo "Product Query: " . $productQuery . "
";

echo "In Store Serials: " . count($inStoresSerial) . "
";

echo "Sold Serials: " . count($soldSerials) . "
";

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • buyBillController.md - Purchase operations
• • supplierController.php - Supplier management
• • productController.md - Product management
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur