Report Quotation Controller Documentation

File: /controllers/reportQuotation.php

Purpose: Generate reports for sales quotations with filtering and client management

Last Updated: December 21, 2024

Total Functions: 3

Lines of Code: ~175

---

📋 Overview

The Report Quotation Controller generates comprehensive reports for sales quotations (offers) created in the system. It provides:

• Quotation listing with date range filtering
• Client-based quotation filtering
• Sales quotation data retrieval and display
• Integration with client management
• Program settings configuration
• Success/error page handling

Primary Functions

✅ Generate quotation reports with date/client filters
✅ Load and display sales bill offers
✅ Client dropdown management
✅ Date range filtering capabilities
✅ Program settings integration
✅ Template-based report display

Related Controllers

• sellbillController.php - Creates the quotations
• clientController.php - Client management
• reportfunctions.php - Shared utility functions

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
sellbilloffer	Sales quotation/offer master	sellbillid, sellbillclientid, sellbillsysdate
sellbilldetailoffer	Quotation line items	sellbilldetailofferid, sellbillid, productid
client	Customer information	clientid, clientname, deleted
programsettings	System configuration	programsettingsid, settingkey, settingvalue

### Reference Tables (For Lookups)

Table Name	Purpose	Key Columns
product	Product master data	productid, productname
youtubelink	Tutorial videos	youtubelinkid, title, url

---

🔑 Key Functions

1. Default Action - Quotation Report Display

Location: Line 121 (empty $do condition)

Purpose: Main quotation report with filtering capabilities

Function Signature:

// Triggered when: URL has no 'do' parameter
// POST parameters: clientid, from, to

Process Flow:

1. Include authentication check

2. Load program settings for system configuration

3. Process POST parameters for filtering:

- $clientid - Filter by specific client

- $from - Start date filter

- $to - End date filter

4. Build dynamic query string based on filters

5. Load quotation data using extended DAO

6. Load client list for dropdown

7. Display via reportquotationview/show.html template

Filter Logic:

$queryString = " ";
if (!empty($clientid)) {
    $queryString .= " and sellbilloffer.sellbillclientid = '" . $clientid . "' ";
}
if (!empty($from)) {
    $queryString .= " and date(sellbilloffer.sellbillsysdate) >= '" . $from . "' ";
}
if (!empty($to)) {
    $queryString .= " and date(sellbilloffer.sellbillsysdate) <= '" . $to . "' ";
}

Template Variables Assigned:

• $alldata - Quotation data with filters applied
• $clientsData - Client dropdown options
• $Programsettingdata - System configuration

---

2. getClients() - Client Data Loading

Location: Line 165

Purpose: Load non-deleted clients for dropdown selection

Function Signature:

function getClients()

Process Flow:

1. Query clients with deleted = 0 condition

2. Return array of active client objects

Returns: Array of client objects for dropdown population

Query: queryByCondition(0) - Gets non-deleted clients only

---

3. Success/Error Handlers

Location: Lines 153-158

Purpose: Handle success and error page displays

Success Handler (do=sucess):

• Displays succes.html template
• Shows confirmation of successful operations

Error Handler (do=error):

• Displays error.html template
• Shows error messages for failed operations

---

🔄 Workflows

Workflow 1: Quotation Report Generation

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

START: Access Quotation Report

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

▼

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

1Authentication Check

- Verify user session

- Check access permissions

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

▼

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

2Load System Configuration

- Load program settings

- Assign to template

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

▼

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

3Process Filter Parameters

- Check for client ID filter

- Check for date range (from/to)

- Build dynamic query string

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

▼

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

4Load Quotation Data

- Execute query with filters

- Get quotation master and detail data

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

▼

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

5Load Supporting Data

- Load client list for dropdown

- Load YouTube tutorial links

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

▼

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

6Generate Report Display

- Assign all data to template

- Render quotation report view

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
No `do` parameter	Default action	Main quotation report with filters
`do=sucess`	Success handler	Show success confirmation page
`do=error`	Error handler	Show error message page

### Required Parameters

Main Report (no do parameter):

• clientid - Client ID filter (optional)
• from - Start date filter (YYYY-MM-DD, optional)
• to - End date filter (YYYY-MM-DD, optional)

Example URLs:

reportQuotation.php - Show all quotations
reportQuotation.php?clientid=123&from=2024-01-01&to=2024-01-31
reportQuotation.php?do=sucess
reportQuotation.php?do=error

---

🧮 Calculation Methods

Query String Building

$queryString = " ";

// Client filter
if (!empty($clientid)) {
    $queryString .= " and sellbilloffer.sellbillclientid = '" . $clientid . "' ";
}

// Date range filters  
if (!empty($from)) {
    $queryString .= " and date(sellbilloffer.sellbillsysdate) >= '" . $from . "' ";
}

if (!empty($to)) {
    $queryString .= " and date(sellbilloffer.sellbillsysdate) <= '" . $to . "' ";
}

Date Format Handling

• Input dates expected in YYYY-MM-DD format
• Uses MySQL date() function to extract date portion from datetime fields
• Supports range queries with >= and <= operators

---

🔒 Security & Permissions

Authentication

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

• Requires user authentication before accessing reports
• No specific permission levels mentioned - relies on general authentication

Input Sanitization

• POST parameters used directly in SQL queries
• Should implement SQL injection protection
• Date format validation recommended

Recommended Security Improvements

// Sanitize client ID
$clientid = filter_input(INPUT_POST, 'clientid', FILTER_VALIDATE_INT);

// Validate date format
if (!empty($from) && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) {
    $from = '';
}

if (!empty($to) && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)) {
    $to = '';
}

---

📊 Performance Considerations

Database Optimization

1. Indexes Recommended:

- sellbilloffer(sellbillclientid, sellbillsysdate)

- sellbilloffer(sellbillsysdate) for date range queries

- client(deleted) for active client filtering

2. Query Optimization:

- Date range queries use date() function which prevents index usage

- Consider storing date separately or using range comparisons with time components

Current Performance Notes

• Extended DAO method getalldata() may load more data than necessary
• No pagination implemented for large result sets
• Client dropdown loads all clients regardless of quotation count

Performance Improvements

-- Better date range query (index-friendly)
WHERE sellbillsysdate >= 'YYYY-MM-DD 00:00:00' 
AND sellbillsysdate <= 'YYYY-MM-DD 23:59:59'

-- Instead of
WHERE date(sellbillsysdate) >= 'YYYY-MM-DD'

---

🐛 Common Issues & Troubleshooting

1. No Quotations Displayed

Issue: Report shows empty even with data in database

Cause: Date filter preventing results

Debug Steps:

-- Check quotation data exists
SELECT COUNT(*) FROM sellbilloffer;

-- Check date format in database
SELECT sellbillsysdate FROM sellbilloffer LIMIT 5;

-- Verify client filter
SELECT DISTINCT sellbillclientid FROM sellbilloffer;

2. Client Dropdown Empty

Issue: No clients appear in dropdown

Cause: All clients marked as deleted

Fix:

-- Check client status
SELECT COUNT(*) FROM client WHERE deleted = 0;

-- Restore clients if needed
UPDATE client SET deleted = 0 WHERE clientid IN (1,2,3...);

3. Date Filter Not Working

Issue: Date range filter returns no results

Cause: Date format mismatch or timezone issues

Debug:

// Add debug output
echo "From: " . $from . "<br>";
echo "To: " . $to . "<br>";
echo "Query: " . $queryString . "<br>";

---

🧪 Testing Scenarios

Test Case 1: Basic Report Display

1. Access reportQuotation.php with no parameters
2. Verify all quotations display
3. Check client dropdown is populated
4. Verify YouTube links load properly

Test Case 2: Client Filtering

1. Select specific client from dropdown
2. Submit form
3. Verify only quotations for selected client appear
4. Test with client having no quotations

Test Case 3: Date Range Filtering

1. Set date range with known quotations
2. Submit filter
3. Verify correct quotations in range appear
4. Test edge cases (same start/end date)
5. Test invalid date formats

Test Case 4: Combined Filters

1. Apply both client and date filters
2. Verify intersection of both filters works
3. Test with no matching results
4. Clear filters and verify reset works

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• reportfunctions.md - Shared utility functions
• sellbillController.php - Quotation creation
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

ReportQuotation Documentation

Report Quotation Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Quotation Report Display

2. getClients() - Client Data Loading

3. Success/Error Handlers

🔄 Workflows

Workflow 1: Quotation Report Generation

🌐 URL Routes & Actions

🧮 Calculation Methods

Query String Building

Date Format Handling

🔒 Security & Permissions

Authentication

Input Sanitization

Recommended Security Improvements

📊 Performance Considerations

Database Optimization

Current Performance Notes

Performance Improvements

🐛 Common Issues & Troubleshooting

1. No Quotations Displayed

2. Client Dropdown Empty

3. Date Filter Not Working

🧪 Testing Scenarios

Test Case 1: Basic Report Display

Test Case 2: Client Filtering

Test Case 3: Date Range Filtering

Test Case 4: Combined Filters

📚 Related Documentation