# Report Functions Controller Documentation

**File**: `/controllers/reportfunctions.php`  
**Purpose**: Shared utility functions for report generation across multiple report controllers  
**Last Updated**: December 21, 2024  
**Total Functions**: 15+  
**Lines of Code**: ~314

---

## 📋 Overview

The Report Functions Controller serves as a centralized library of utility functions used by various report controllers throughout the system. It provides:
- Category hierarchy traversal functions  
- Product category and product loading utilities
- Store and branch data loading functions
- Client data retrieval functions
- User and seller data loading
- Bill details and product management utilities
- Product path generation for hierarchical display
- Unit conversion and quantity calculations

### Primary Functions
- [x] Category hierarchy navigation
- [x] Product and product category loading
- [x] Store and branch management data loading
- [x] Client information retrieval 
- [x] User and seller data access
- [x] Bill details generation with product categorization
- [x] Product path generation for nested categories
- [x] Unit conversions and quantity calculations

### Related Controllers
- [salesreport.php](salesreport.md) - Sales reporting
- [reportQuotation.php](reportQuotation.md) - Quotation reports
- [reportClientAndSupplier.php](reportClientAndSupplier.md) - Client/supplier reports
- [sellingReportByArea.php](sellingReportByArea.md) - Area-based sales reports
- [sellreportpricetype.php](sellreportpricetype.md) - Price type reports

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **productcat** | Product category hierarchy | productCatId, productCatName, productCatParent |
| **product** | Product master data | productId, productName, productCatId, productBuyPrice |
| **store** | Store locations | storeId, storeName |
| **branch** | Branch locations | branchid, branchname |
| **client** | Customer data | clientid, clientname |
| **user** | System users | userid, username, userlevel |

### Reference Tables (Loaded for Dropdowns/Lookups)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Service bills | id, clientid, billdate, productstotalprice |
| **billsproducts** | Bill line items | billproductsid, billid, productid, productno |
| **productunit** | Product unit conversions | productunitid, productid, unitid, productnumber |
| **doctorkashf** | Medical prescriptions | kashfid |
| **insurancecompanies** | Insurance providers | insurancecompanyid, name |
| **paymentnetworks** | Payment networks | paymentnetworkid, name |

---

## 🔑 Key Functions

### 1. **getCategoryChilds()** - Category Hierarchy Loading
**Location**: Line 82  
**Purpose**: Load category hierarchy with optional service filtering

**Function Signature**:
```php
function getCategoryChilds($parentid = 0, $service = -1)
```

**Parameters**:
- `$parentid` - Parent category ID (0 for root categories)
- `$service` - Service filter (-1 for all services)

**Returns**: Array with parent object and children array

**Process Flow**:
1. Load parent category object
2. Apply service filter if specified
3. Get child categories using extended DAO
4. Return array with parent and children data

---

### 2. **getCatProducts()** - Products by Category
**Location**: Line 96  
**Purpose**: Load all products belonging to a specific category

**Function Signature**:
```php
function getCatProducts($catid)
```

**Returns**: Array with category object and products array

**Process Flow**:
1. Validate category ID > 0
2. Load category object
3. Get products for category using extended DAO
4. Return category and products data

---

### 3. **getSizeColor()** - Size/Color Product Variants
**Location**: Line 109  
**Purpose**: Load size/color variants for a product category

**Function Signature**:
```php
function getSizeColor($catid)
```

**Returns**: Array with category object and size/color variants

**Use Case**: For products with size/color variations (clothing, accessories, etc.)

---

### 4. **loadStore()** - Store Data Loading
**Location**: Line 124  
**Purpose**: Load all active store locations

**Function Signature**:
```php
function loadStore()
```

**Returns**: Array of store objects

**Query**: Uses `queryByConditions(0)` to get non-deleted stores

---

### 5. **loadBranch()** - Branch Data Loading
**Location**: Line 132  
**Purpose**: Load all branch locations

**Function Signature**:
```php
function loadBranch()
```

**Returns**: Array of branch objects

---

### 6. **getClientData()** - Client Data Loading
**Location**: Line 140  
**Purpose**: Load client data with supplier information

**Function Signature**:
```php
function getClientData()
```

**Returns**: Array of client objects with extended information

**Uses**: `queryAllsup()` method for enhanced client data

---

### 7. **loadseller()** - Seller User Loading
**Location**: Line 148  
**Purpose**: Load users with seller level (level 3)

**Function Signature**:
```php
function loadseller()
```

**Returns**: Array of seller user objects

**Filter**: Only users with `userlevel = 3`

---

### 8. **getBillDetails()** - Comprehensive Bill Display
**Location**: Line 173  
**Purpose**: Load and format complete bill information for display

**Function Signature**:
```php
function getBillDetails($billId)
```

**Process Flow**:
1. Load bill master data
2. Format dates (bill date, receive date)
3. Load branch information
4. Load client information  
5. Process insurance company data if applicable
6. Process payment network data if applicable
7. Load medical prescription (kashf) data
8. Load and process bill products with categorization
9. Assign all data to Smarty template variables

**Key Features**:
- Date formatting with day names
- Insurance company processing
- Payment network handling
- Product categorization with full paths
- Product code generation (FRM for frames, OPT for optics)

---

### 9. **getBillProducts()** - Bill Products with Categorization
**Location**: Line 238  
**Purpose**: Load bill products with full category paths and product codes

**Function Signature**:
```php
function getBillProducts($billId)
```

**Process Flow**:
1. Query products for specific bill ID
2. Load product and category information
3. Generate full category path for each product
4. Generate sequential product codes:
   - Frame products: FRM001, FRM002, etc.
   - Optic products: OPT001, OPT002, etc.
5. Format product codes with leading zeros

**Code Generation Logic**:
```php
if ($pro->service == "Frame") {
    $pro->productCode = 'FRM';
    $frameCount++;
} else {
    $pro->productCode = 'OPT'; 
    $opCount++;
}
```

---

### 10. **getProductPath_recursive()** - Recursive Path Building
**Location**: Line 289  
**Purpose**: Build complete category path for products using recursion

**Function Signature**:
```php
function getProductPath_recursive($parentid, $categories)
```

**Process Flow**:
1. Get category data and parent information
2. Append category name to path
3. Recursively call for parent categories
4. Return complete path string

**Output Format**: "Parent Category/Child Category/Product Category"

---

### 11. **loadProductUnitWithProductAndUnit()** - Unit Information Loading
**Location**: Line 304  
**Purpose**: Load product unit conversion information

**Function Signature**:
```php
function loadProductUnitWithProductAndUnit($productId, $unitId)
```

**Returns**: Product unit object with conversion factors

**Use Case**: For quantity calculations and unit conversions in reports

---

## 🔄 Workflows

### Workflow 1: Category Hierarchy Loading for Reports
```
┌─────────────────────────────────────────────────────────────┐
│                    START: Load Categories                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Call getCategoryChilds($parentid, $service)            │
│     - Check for service filter                             │
│     - Load parent category                                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Apply Service Filter (if specified)                    │
│     - Build service query string                           │
│     - Filter categories by opticServices                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Load Child Categories                                   │
│     - Query child categories                                │
│     - Return parent and children array                     │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Bill Details Generation with Product Paths
```
┌─────────────────────────────────────────────────────────────┐
│                 START: Generate Bill Details                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Bill Master Data                                   │
│     - Bill information                                      │
│     - Date formatting                                       │
│     - Branch and client data                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Additional Bill Information                     │
│     - Insurance company (if applicable)                    │
│     - Payment network (if applicable)                      │
│     - Medical prescription data                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Load and Process Bill Products                          │
│     FOR EACH product in bill:                               │
│       │                                                     │
│       ├─→ Load product information                         │
│       │                                                     │
│       ├─→ Load category information                        │
│       │                                                     │
│       ├─→ Generate category path recursively               │
│       │   └─ getProductPath_recursive()                    │
│       │                                                     │
│       ├─→ Generate product code                            │
│       │   ├─ Frame: FRM001, FRM002...                      │
│       │   └─ Optic: OPT001, OPT002...                      │
│       │                                                     │
│       └─→ Combine path and product name                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Assign to Template Variables                            │
│     - Complete bill information                             │
│     - Formatted product list with paths                    │
│     - Product count and codes                              │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

This controller provides utility functions only - it has no direct URL routes. Functions are called by other controllers:

### Function Usage by Controllers
| Function | Used By | Purpose |
|----------|---------|---------|
| `getCategoryChilds()` | salesreport.php, sellreportpricetype.php | Category filtering |
| `getCatProducts()` | Various report controllers | Product listing |
| `loadStore()` | Multiple controllers | Store selection |
| `getClientData()` | reportClientAndSupplier.php | Client reporting |
| `getBillDetails()` | Bill display controllers | Bill formatting |

---

## 🧮 Calculation Methods

### Category Path Generation
```php
function getProductPath_recursive($parentid, $categories) {
    $catData = $productCatExt->getCategoryAndParentByCatId($parentid);
    
    if (count($catData) > 0) {
        $categories .= $catData->productCatName . '/';
        $newParentId = $catData->productCatParent;
        
        return getProductPath_recursive($newParentId, $categories);
    }
    
    // Remove trailing slash
    $categories = substr($categories, 0, strlen($categories) - 1);
    return $categories;
}
```

### Product Code Generation
```php
// Frame products
if ($pro->service == "Frame") {
    $pro->productCode = 'FRM';
    $frameCount++;
} else {
    $pro->productCode = 'OPT';
    $opCount++;
}

// Add leading zeros
if (strlen($count) == 1) {
    $code = '00' . $count;
} elseif (strlen($count) == 2) {
    $code = '0' . $count;
} else {
    $code = $count;
}

$pro->productCode .= $code;
```

---

## 🔒 Security & Permissions

### Access Control
- No direct authentication in this controller
- Relies on calling controllers for permission checks
- Functions assume authorized access through parent controllers

### Data Sanitization
- Functions accept parameters from trusted sources (other controllers)
- Database interactions handled through DAO layer
- No direct user input processing in utility functions

---

## 📊 Performance Considerations

### Optimization Opportunities
1. **Category Path Caching**: Category paths could be cached to avoid recursive queries
2. **Product Loading**: Batch loading of products instead of individual queries
3. **Unit Conversion Caching**: Cache unit conversion factors

### Current Performance Notes
- Recursive category path generation can be expensive for deep hierarchies
- Product loading is done individually - could benefit from batch queries
- No caching implemented for frequently accessed data

---

## 🐛 Common Issues & Troubleshooting

### 1. **Infinite Recursion in Category Paths**
**Issue**: Category hierarchy has circular references  
**Cause**: Data corruption in productCatParent field

**Fix**: Check for circular references in category data
```sql
-- Find potential circular references
SELECT productCatId, productCatParent 
FROM productcat 
WHERE productCatId = productCatParent;
```

### 2. **Missing Product Codes**
**Issue**: Product codes not generating properly  
**Cause**: Service field not properly set

**Debug**: Check service field values and ensure proper categorization

### 3. **Empty Category Paths**  
**Issue**: Product shows without category path  
**Cause**: Missing category assignments

**Fix**: Verify product category assignments and category hierarchy

---

## 🧪 Testing Scenarios

### Test Case 1: Category Hierarchy Loading
```
1. Load categories with getCategoryChilds(0, -1)
2. Verify parent-child relationships
3. Test with specific service filters
4. Check for circular references
```

### Test Case 2: Product Code Generation
```
1. Load bill with mixed frame and optic products
2. Verify sequential numbering (FRM001, FRM002, OPT001)
3. Test with large quantities
4. Verify leading zero formatting
```

### Test Case 3: Recursive Path Building
```
1. Create nested category hierarchy (3+ levels)
2. Test getProductPath_recursive() 
3. Verify complete path generation
4. Test with missing parent categories
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [salesreport.md](salesreport.md) - Sales report controller
- [Database Schema Documentation](#) - Table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur