Project Material Summary Report Controller Documentation

File: /controllers/projectreportControllerdetail2.php

Purpose: Generate consolidated material usage reports by product for projects

Last Updated: 2024-12-20

---

📋 Overview

Primary Functions

✅ Product-wise material consumption summary
✅ Net material usage calculation (issued - returned)
✅ Material cost analysis by product
✅ Consolidated material reporting

Related Controllers

• projectreportControllerdetail.php - Detailed financial reports
• projectoperationController.php - Material operations

---

🗄️ Database Tables

Primary Tables

Table Name	Purpose	Key Columns
projectexchmaterial	Material issues	id, projectid, totalbuyprice
projectexchmaterialdetail	Issue line items	projectexchid, productid, pronumber, probuyprice
projectexchmaterialreturn	Material returns	id, projectid, totalbuyprice
projectexchmaterialdetailreturn	Return line items	projectexchid, productid, pronumber, probuyprice
product	Product master	productid, productName

---

🔧 Key Functions

1. Material Consolidation Engine

Purpose: Calculate net material usage by product across all project transactions

Business Logic:

// Get all unique products used in project
$allproductids = R::getAll("SELECT productid FROM ( 
    SELECT productid FROM projectexchmaterial left join projectexchmaterialdetail on 
    projectexchmaterial.id = projectexchmaterialdetail.projectexchid WHERE projectexchmaterial.projectid = $projectId
    UNION ALL
    SELECT productid FROM projectexchmaterialreturn left join projectexchmaterialdetailreturn on 
    projectexchmaterialreturn.id = projectexchmaterialdetailreturn.projectexchid WHERE projectexchmaterialreturn.projectid = $projectId 
) s GROUP BY productid");

$alldata = array();
$alltotal = 0;
$alltotaln = 0;

foreach($allproductids as $product) {
    // Calculate total issued
    $projectexchmaterialnum = R::getrow("SELECT sum(pronumber) as pronumbers, sum(probuyprice) as probuyprices FROM projectexchmaterial left join projectexchmaterialdetail on 
    projectexchmaterial.id = projectexchmaterialdetail.projectexchid WHERE projectexchmaterial.projectid = $projectId and productid = " . $product['productid']);
    
    // Get product details
    $productdata = R::getrow("SELECT productName FROM product WHERE productid = " . $product['productid']);
    
    // Calculate total returned
    $projectexchmaterialnnum = R::getrow("SELECT sum(pronumber) as pronumbers, sum(probuyprice) as probuyprices FROM projectexchmaterialreturn left join projectexchmaterialdetailreturn on 
    projectexchmaterialreturn.id = projectexchmaterialdetailreturn.projectexchid WHERE projectexchmaterialreturn.projectid = $projectId and productid = " . $product['productid']);
    
    // Calculate totals
    $alltotal += intval($projectexchmaterialnum['pronumbers']) * intval($projectexchmaterialnum['probuyprices']);
    $alltotaln += intval($projectexchmaterialnnum['pronumbers']) * intval($projectexchmaterialnnum['probuyprices']);
    
    $inside_item = array(
        "productid" => $product['productid'],
        "productName" => $productdata['productName'],
        "pronumber" => intval($projectexchmaterialnum['pronumbers']),
        "probuyprice" => intval($projectexchmaterialnum['probuyprices']),
        "pronumbern" => intval($projectexchmaterialnnum['pronumbers']),
        "probuypricen" => intval($projectexchmaterialnnum['probuyprices'])
    );
    
    array_push($alldata, $inside_item);
}

2. Summary Calculations

Net Usage Calculation:

$smarty->assign("alltotal", $alltotal);        // Total issued value
$smarty->assign("alltotaln", $alltotaln);      // Total returned value  
$smarty->assign("alltotals", $alltotal - $alltotaln); // Net consumption value

---

📊 Report Structure

Product Summary Format

Product ID | Product Name | Issued Qty | Issued Value | Returned Qty | Returned Value | Net Qty | Net Value
-----------|--------------|------------|--------------|--------------|----------------|---------|----------
001        | Steel Bars   | 100        | 5,000        | 10           | 500            | 90      | 4,500
002        | Cement       | 50         | 2,500        | 5            | 250            | 45      | 2,250

Summary Totals

• Total Issued Value: Sum of all material issues
• Total Returned Value: Sum of all material returns
• Net Consumption Value: Total Issued - Total Returned

---

🎯 URL Routes & Actions

Action (`?do=`)	Method	Description	View Template
(empty)	GET/POST	Generate material summary report	reportdetail2.html

---

📈 Business Intelligence Features

Material Utilization Analysis

• Product-wise consumption tracking
• Return rate analysis by product
• Cost efficiency by material type
• Project material optimization insights

Cost Control Benefits

• Identify high-consumption materials
• Track material waste through returns
• Monitor cost per unit consumption
• Support procurement planning

---

⚠️ Known Issues & Fixes

Issue 1: Integer Casting for Calculations (Lines 38-39)

Problem: Using intval() may lose decimal precision

Cause: Cost calculations need decimal precision

Fix: Use floatval() or (float) casting

File: Lines 38-39, 44-46

Issue 2: UNION ALL Query Performance

Problem: Complex UNION query may be slow for large datasets

Cause: Full table scans on material tables

Fix: Add indexes on projectid and productid columns

File: Lines 22-28

---

📝 Notes

Important Features

• Handles both material issues and returns
• Consolidates data by product across all transactions
• Provides net consumption analysis
• Supports cost tracking and budgeting

Use Cases

• Project cost analysis
• Material budgeting
• Procurement planning
• Waste reduction initiatives
• Project profitability analysis

Performance Considerations

• Query complexity increases with project size
• Consider caching for frequently accessed reports
• Index optimization recommended for large datasets

---

📚 ERP Docs

Projectreportdetail2 Documentation