# 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
- [x] Product-wise material consumption summary
- [x] Net material usage calculation (issued - returned)
- [x] Material cost analysis by product
- [x] Consolidated material reporting

### Related Controllers
- [projectreportControllerdetail.php](projectreportControllerdetail.md) - Detailed financial reports
- [projectoperationController.php](projectoperationController.md) - 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**:
```php
// 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**:
```php
$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

---

## 📚 Related Documentation
- [Material Operations](projectoperationController.md)
- [Material Returns](projectoperationControllerreturn.md)
- [Detailed Financial Reports](projectreportControllerdetail.md)
- [Project Master](projectController.md)