Daily Report Controller Documentation

File: /controllers/dailyReportController.php

Purpose: Generates comprehensive daily business summary reports with sales, purchases, debts, and inventory metrics

Last Updated: December 20, 2024

Total Functions: 1

Lines of Code: ~134

---

📋 Overview

The Daily Report Controller provides a comprehensive daily dashboard that summarizes key business metrics including sales revenue, purchase costs, customer payments, supplier payments, debt totals, inventory valuation, and expense breakdowns. It automatically calculates time-adjusted reports based on configured business hours.

Primary Functions

• ✅ Daily sales and payment summary
• ✅ Purchase and supplier payment tracking
• ✅ Customer and supplier debt totals
• ✅ Inventory valuation by configured method
• ✅ Daily expense breakdown by category
• ✅ Cash register totals
• ✅ Time-zone adjusted reporting periods
• ✅ Configurable report timing

Related Controllers

• • sellbillController.php - Sales transactions
• • buyBillController.php - Purchase transactions
• • clientPayedDeptController.php - Customer payments
• • supplierPayedDeptController.php - Supplier payments
• • expensesController.php - Expense management

---

🗄️ Database Tables

Sales Tables

🔑 Key Functions

1. show() / Default Action - Daily Report Generation

Location: Line 31

Purpose: Generate comprehensive daily business summary

Function Signature:

// Triggered when: do=show or empty $do
$today = filter_input(INPUT_POST, 'date') ?: date('Y-m-d');

Process Flow:

1. Load program settings for report configuration

2. Calculate time-adjusted reporting period

3. Query sales revenue and debt data

4. Query purchase and supplier payment data

5. Calculate customer and supplier debt totals

6. Evaluate inventory value by configured method

7. Summarize daily expenses by category

8. Calculate total cash register values

9. Display via show.html template

Time Adjustment Logic:

if (isset($Programsettingdata->reportsPlusHours) && !empty($Programsettingdata->reportsPlusHours)) {
    $reportsPlusHours = $Programsettingdata->reportsPlusHours + 24;
    $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($today)));
    $startToday = date('Y-m-d H:i:s', strtotime('+' . $Programsettingdata->reportsPlusHours . ' hour', strtotime($today)));
}

Dynamic Date Range Selection:

if (date('H') < $Programsettingdata->reportsPlusHours) {
    $startDate = $startYesterday; // Use previous day's data
    $endDate = $endYesterday;
} else {
    $startDate = $startToday; // Use current day's data  
    $endDate = $endToday;
}

---

🔄 Workflows

Workflow 1: Daily Report Generation

---

🌐 URL Routes & Actions

Daily Report:

• • date (POST, optional) - Specific date for report (defaults to today)

---

🧮 Calculation Methods

Sales Revenue Query

SELECT SUM(sellbilltotalpayed) FROM sellbill 
WHERE conditions = 0 
AND sellbilldate >= ? AND sellbilldate <= ?

Outstanding Invoices

SELECT SUM(sellbillfinalbill) FROM sellbill
WHERE conditions = 0
AND sellbilldate >= ? AND sellbilldate <= ?

Purchase Totals by Supplier

SELECT suppliername, SUM(buybillaftertotalbill) AS total 
FROM buybill 
JOIN supplier ON buybill.buybillsupplierid = supplier.supplierid
WHERE buybill.conditions = 0 
AND buybilldate >= ? AND buybilldate <= ?
GROUP BY buybill.buybillsupplierid

Customer Payments Received

SELECT clientname, SUM(clientdebtchangeamount) total 
FROM clientdebtchange 
JOIN client ON clientdebtchange.clientid = client.clientid
WHERE del = 0 
AND clientdebtchangedate >= ? AND clientdebtchangedate <= ?
AND tablename = 'clientPayedDeptController.php' 
GROUP BY clientdebtchange.clientid

Inventory Valuation Logic

switch ($Programsettingdata->Inventoryevaluation) {
    case "first":
        $pro_price = 'productBuyPrice';
        break;
    case "last":
        $pro_price = 'lastbuyprice';
        break;
    case "mean":
        $pro_price = 'meanbuyprice';
        break;
    case "last_discount":
        $pro_price = 'lastbuyprice_withDiscount';
        break;
    case "mean_discount":
        $pro_price = 'meanbuyprice_withDiscount';
        break;
    case "tax":
        $pro_price = 'lastbuyprice_withTax';
        break;
    case "mean_tax":
        $pro_price = 'meanbuyprice_withTax';
        break;
    default:
        $pro_price = 'overAllAveragePrice';
        break;
}

$storeEvaluation = R::getCell('
    SELECT ROUND(SUM(productquantity * ' . $pro_price . '),2) AS total 
    FROM storedetail 
    JOIN product ON product.productId = storedetail.productid 
    WHERE product.conditions = 0
');

---

🔒 Security & Permissions

Authentication Required

• • All actions require authentication via include_once("../public/authentication.php")
• • Session-based access control

Input Sanitization

$today = filter_input(INPUT_POST, 'date');
if (!$today) $today = date('Y-m-d');

SQL Injection Prevention

• • Uses parameterized queries through RedBean ORM
• • Date validation and formatting
• • Secure variable binding

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- sellbill(sellbilldate, conditions)

- buybill(buybilldate, conditions, buybillsupplierid)

- clientdebtchange(clientdebtchangedate, del, tablename)

- supplierdebtchange(supplierdebtchangedate, del, tablename)

- expenses(expensesdate, conditions)

2. Query Optimization:

- Single date range for all queries

- Efficient aggregation functions

- Minimal data retrieval

- Proper JOIN usage

3. Memory Management:

- Process large datasets in chunks

- Clear variables after use

- Optimize template variable assignment

Known Performance Issues

-- This inventory query can be slow with many products
SELECT ROUND(SUM(productquantity * productBuyPrice),2) AS total 
FROM storedetail 
JOIN product ON product.productId = storedetail.productid 
WHERE product.conditions = 0

-- Solution: Add composite index
CREATE INDEX idx_storedetail_product ON storedetail(productid);
CREATE INDEX idx_product_conditions ON product(conditions, productId);

---

🐛 Common Issues & Troubleshooting

1. Incorrect Time Periods

Issue: Report shows wrong day's data

Cause: reportsPlusHours configuration mismatch

Debug:

echo "Current Hour: " . date('H') . "<br>";
echo "Report Plus Hours: " . $Programsettingdata->reportsPlusHours . "<br>";
echo "Start Date: " . $startDate . "<br>";
echo "End Date: " . $endDate . "<br>";

2. Missing Sales Data

Issue: Sales totals show zero

Cause: Incorrect date range or cancelled bills

Debug:

SELECT COUNT(*) as total_bills, 
       COUNT(CASE WHEN conditions = 0 THEN 1 END) as active_bills
FROM sellbill 
WHERE sellbilldate = 'YYYY-MM-DD';

3. Inventory Valuation Errors

Issue: Negative or unrealistic inventory values

Cause: Missing price data or incorrect evaluation method

Debug:

SELECT COUNT(*) as products_with_no_price
FROM storedetail sd
JOIN product p ON sd.productid = p.productId  
WHERE p.productBuyPrice IS NULL OR p.productBuyPrice = 0;

4. Expense Category Missing

Issue: Expenses not categorized properly

Cause: Missing expensestype records

Fix:

SELECT e.expensesValue, e.expensestypeid, et.expensestypename
FROM expenses e
LEFT JOIN expensestype et ON e.expensestypeid = et.expensestypeid
WHERE et.expensestypeid IS NULL;

---

🧪 Testing Scenarios

Test Case 1: Normal Daily Report

1. Set specific test date
2. Create test sales, purchases, payments
3. Run daily report
4. Verify all totals match expected values
5. Check time period accuracy

Test Case 2: Cross-Midnight Operations

1. Set reportsPlusHours to offset time
2. Test before and after midnight  
3. Verify correct date period selection
4. Check data consistency

Test Case 3: Different Inventory Methods

1. Change Inventoryevaluation setting
2. Run report with each method
3. Verify price field usage
4. Compare valuation differences

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • buyBillController.md - Purchase operations
• • expensesController.php - Expense management
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur