Project API Controller Documentation

File: /controllers/projectApi.php

Purpose: Comprehensive project management API with detailed financial reporting and data visualization

Last Updated: December 20, 2024

Total Functions: 6+

Lines of Code: ~410

---

📋 Overview

The Project API Controller provides detailed project management capabilities with comprehensive financial analysis. This API appears to be specifically designed for external integrations and mobile applications that need access to:

• • Detailed project data with complete financial calculations
• • Client debt change tracking and payment history
• • Expense breakdown by category with supervision costs
• • Income tracking from multiple sources
• • Project stage management with file attachments
• • Timeline and scheduling data

Primary Functions

• ✅ Comprehensive project data retrieval with financial analysis
• ✅ Client payment history and debt change tracking
• ✅ Detailed expense categorization and supervision calculations
• ✅ Multi-source income aggregation and reporting
• ✅ Project stage management with file handling
• ✅ Timeline and milestone tracking
• ✅ Material exchange and return processing
• ✅ Advanced financial formulas and profit/loss calculations

Related Controllers

• • api_web.php - General web client API
• • projectController.php - Internal project management
• • expensesController.php - Expense tracking
• • clientController.php - Customer management

---

🗄️ Database Tables

Core Project Tables

🔑 Key Functions

1. projectReport - Comprehensive Project Analysis

Location: Line 177

Purpose: Generate complete project report with financial analysis and file management

Function Signature:

// GET parameter: id (project ID)
// Returns comprehensive JSON report

Core Financial Logic:

A. Expense Aggregation:

// Material exchange expenses
$projectexchmaterialdetails = R::getAll("
    select *, sum(totalbuyprice) as totals, sum(finalsupervision) as finalsupervisions 
    from projectexchmaterialdetail JOIN projectexchmaterial
    ON projectexchmaterialdetail.projectexchid = projectexchmaterial.id 
    WHERE projectexchmaterial.projectid = ?  
    group by projectexchmaterialdetail.expensestypeid", [$projectid]);

// Direct expenses
$expenseexchange = R::getROW("
    select sum(thevalue) as totals, sum(finalsupervision) as finalsupervisions 
    from expenseexchange 
    where del = 0 and projectid = ? and expensetype = ?", [$projectid, $expensetype]);

// Material returns (subtract from totals)
$projectexchmaterialdetailreturn = R::getROW("
    select *, sum(totalbuyprice) as totals, sum(finalsupervision) as finalsupervisions 
    from projectexchmaterialdetailreturn JOIN projectexchmaterialreturn
    ON projectexchmaterialdetailreturn.projectexchid = projectexchmaterialreturn.id 
    WHERE projectexchmaterial.projectid = ? and projectexchmaterialdetailreturn.expensestypeid = ?", 
    [$projectid, $expensetype]);

B. Supervision Calculation:

if ($project['supervision_type'] == 1) {
    // Fixed supervision amount
    $finalsupervisions = $project['supervision_amount'];
} else {
    // Calculated supervision based on percentages
    $finalsupervisions = $expenseexchange['finalsupervisions'] + 
                         $projectexchmaterialdetail['finalsupervisions'] - 
                         $projectexchmaterialdetailreturn['finalsupervisions'];
}

C. Income Calculation:

if ($project['income'] == 1) {
    // Client payments (actual payments only)
    $project['allclientdebtchange'] = R::getCell("
        SELECT sum(clientdebtchangeamount) 
        FROM clientdebtchange 
        WHERE clientid = ? and paySerialNo > 0 and del = 0", [$project['clientid']]);
    
    // Other income sources
    $project['allincomeValue'] = R::getCell("
        SELECT sum(incomeValue) 
        FROM income 
        WHERE costcenterid = ? and conditions = 0", [$project['costcenterid']]);
    
    $project['alltotalsincome'] = $project['allclientdebtchange'] + $project['allincomeValue'];
}

D. Final Profit/Loss Calculation:

$project['endtotals'] = ($project['alltotals'] + $project['alltotalvalues']) - $project['alltotalsincome'];

// Positive endtotals = Loss (costs > income)
// Negative endtotals = Profit (income > costs)

---

2. Project Stage Management

Purpose: Handle project stage data with file attachments

Stage Processing Logic:

if ($project['projectstagesdata'] == 1) {
    $projectstages = R::getAll("
        select * from projectstagechosse  
        where projectstagechosse.projectid = ?", [$projectid]);
    
    foreach ($projectstages as $key => $projectstage) {
        // Load stage definition
        $getprojectstage = R::getRow('
            select * from projectstages where id = ?', [$projectstage['projectstageid']]);
        $projectstages[$key]['projectstage'] = $getprojectstage['name'];
        
        // Process stage images
        $projectstageimages = explode(',', $projectstage['projectstageimages']);
        $images = [];
        for ($i = 0; $i < count($projectstageimages); $i++) { 
           $images[] = $src . $projectstageimages[$i];
        }
        $projectstages[$key]['images'] = $images;
        
        // Process stage files with original names
        $projectstagefiles = explode(',', $projectstage['projectstagefiles']);
        $oldnamefiles = explode(',', $projectstage['oldnamefiles']);
        $files = [];
        for ($i = 0; $i < count($projectstagefiles); $i++) { 
           $files[] = [
               'name' => $oldnamefiles[$i],
               'file' => $src . $projectstagefiles[$i]
           ];
        }
        $projectstages[$key]['files'] = $files;
    }
}

---

3. Timeline Management

Purpose: Handle project timeline and milestone data

Timeline Processing:

if ($project['projecttimetableitems'] == 1) {
    $projecttimetableitems = R::getAll("
        select clause, amount, duration, startdate, enddate 
        from projecttimetableitems 
        where projecttimetableitems.projectid = ?", [$projectid]);
} else {
    $projecttimetableitems = [];
}

---

4. Complex Expense Type Aggregation

Purpose: Aggregate expenses by type across multiple sources

Aggregation Logic:

// Build expense type list to avoid duplicates
$expensestypeids = '0';
$arrayexpensestypeids = [];
$allexpensestype = [];

foreach ($projectexchmaterialdetails as $detail) {
    $expensestypeids .= ',' . $detail['expensestypeid'];
    
    // Get expense type information
    $expensestypedata = R::getRow('
        SELECT expensestype.* FROM expensestype WHERE expensestypeid = ?', 
        [$detail['expensestypeid']]);
    
    // Calculate totals from all sources
    $totals = $expenseexchange['totals'] + 
              $detail['totals'] - 
              $projectexchmaterialdetailreturn['totals'];
    
    $expensestype = [
        'expensestypeid' => $expensestypedata['expensestypeid'],
        'expensestypename' => $expensestypedata['expensestypename'],
        'totals' => $totals,
        'finalsupervisions' => $finalsupervisions,
    ];
    
    $allexpensestype[] = $expensestype;
}

---

🔄 Workflows

Workflow 1: Comprehensive Project Report Generation

---

🧮 Financial Calculation Methods

Expense Aggregation Formula

// For each expense type:
$finalTotal = $materialExchangeTotal + $directExpenseTotal - $materialReturnTotal;

// Where:
$materialExchangeTotal = SUM(projectexchmaterialdetail.totalbuyprice) 
                        WHERE expensestypeid = X;

$directExpenseTotal = SUM(expenseexchange.thevalue) 
                     WHERE projectid = Y AND expensetype = X;

$materialReturnTotal = SUM(projectexchmaterialdetailreturn.totalbuyprice) 
                      WHERE expensestypeid = X;

Supervision Cost Calculation

if ($project['supervision_type'] == 1) {
    // Fixed supervision - use specified amount
    $supervisionCost = $project['supervision_amount'];
} else {
    // Percentage-based supervision - calculate from expenses
    $supervisionCost = $calculatedSupervisionFromExpensePercentages;
}

Profit/Loss Determination

$totalCosts = $allDirectExpenses + $supervisionCosts;
$totalIncome = $clientPayments + $otherIncome;
$netResult = $totalCosts - $totalIncome;

if ($netResult > 0) {
    // Project is showing a loss
    $status = 'Loss';
    $amount = $netResult;
} else {
    // Project is showing a profit  
    $status = 'Profit';
    $amount = abs($netResult);
}

---

📊 Data Structures & Response Format

Project Report Response Structure

{
  "project": {
    "id": 123,
    "clientid": 456,
    "costcenterid": 789,
    "name": "Project Name",
    "supervision_type": 1,
    "supervision_amount": 5000.00,
    "alltotals": 15000.00,
    "alltotalvalues": 5000.00,
    "alltotalsincome": 18000.00,
    "endtotals": 2000.00,
    "image": "http://host/upload/project/image.jpg",
    "filepdf": "http://host/upload/project/document.pdf"
  },
  "projectstages": [
    {
      "projectstage": "Foundation",
      "images": [
        "http://host/upload/project/stage1_photo1.jpg",
        "http://host/upload/project/stage1_photo2.jpg"
      ],
      "files": [
        {
          "name": "Foundation Plan.pdf",
          "file": "http://host/upload/project/foundation_plan_internal.pdf"
        }
      ]
    }
  ],
  "projecttimetableitems": [
    {
      "clause": "Foundation Work",
      "amount": 5000.00,
      "duration": 14,
      "startdate": "2024-01-01",
      "enddate": "2024-01-15"
    }
  ],
  "expenses": [
    {
      "expensestypeid": 1,
      "expensestypename": "Materials",
      "totals": 8000.00,
      "finalsupervisions": 1200.00
    },
    {
      "expensestypeid": 2,
      "expensestypename": "Labor",
      "totals": 7000.00,
      "finalsupervisions": 1050.00
    }
  ],
  "clientdebtchange": [
    {
      "clientdebtchangedate": "2024-01-10",
      "clientdebtchangeamount": 10000.00
    },
    {
      "clientdebtchangedate": "2024-02-10", 
      "clientdebtchangeamount": 8000.00
    }
  ],
  "incomes": [
    {
      "incomeName": "Progress Payment 1",
      "incomeValue": 10000.00
    }
  ]
}

---

🔒 Security & Data Protection

File URL Security

// File URLs are built with host prefix
$src = Host_URL . '/upload/project/';
$images[] = $src . $projectstageimages[$i];

// Should implement access control for file downloads
// Current implementation exposes direct file paths

Data Access Control

• • No explicit user permission checks in this API
• • Relies on external authentication
• • Should implement project-user access validation

Recommendations

1. Add user authentication token validation

2. Implement project access permission checks

3. Validate project ID to prevent unauthorized access

4. Add rate limiting for API calls

5. Sanitize file paths to prevent directory traversal

---

📊 Performance Considerations

Database Query Optimization

1. Heavy Aggregation Queries:

- Multiple SUM operations across large tables

- GROUP BY operations on expense types

- Complex JOINs across material exchange tables

2. Recommended Indexes:

   CREATE INDEX idx_project_expenses ON expenseexchange(projectid, expensetype);
   CREATE INDEX idx_material_detail ON projectexchmaterialdetail(projectexchid, expensestypeid);
   CREATE INDEX idx_client_payments ON clientdebtchange(clientid, paySerialNo, del);
   CREATE INDEX idx_project_stages ON projectstagechosse(projectid);
   ```

3. **Query Performance Issues**:
   ```php
   // This pattern repeated multiple times - should be optimized
   foreach ($projectexchmaterialdetails as $detail) {
       $expenseexchange = R::getROW("select sum(thevalue)... WHERE expensetype = ?");
       // N+1 query problem - should use single query with GROUP BY
   }
   ```

### Memory Management
- Large projects with many expense types can cause high memory usage
- Consider pagination for very large result sets
- Implement caching for frequently accessed project data

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Financial Calculations**
**Issue**: Totals don't match expected values  
**Cause**: Missing material returns or supervision calculation errors

**Debug**:

// Check individual components

error_log("Material Expenses: " . $materialTotal);

error_log("Direct Expenses: " . $directTotal);

error_log("Material Returns: " . $returnTotal);

error_log("Supervision Type: " . $project['supervision_type']);

### 2. **Missing File URLs**
**Issue**: Stage images or files not displaying  
**Cause**: Incorrect file path construction or missing files

**Debug**:

// Verify file path construction

error_log("Source path: " . $src);

error_log("Image filename: " . $projectstageimages[$i]);

error_log("Full URL: " . $src . $projectstageimages[$i]);

// Check if file exists

if (!file_exists($src . $projectstageimages[$i])) {

error_log("File not found: " . $projectstageimages[$i]);

}

### 3. **Empty Expense Categories**
**Issue**: Some expense types show zero amounts  
**Cause**: Missing relationships or deleted records

**Debug**:

-- Check for orphaned records

SELECT * FROM projectexchmaterialdetail

WHERE expensestypeid NOT IN (SELECT expensestypeid FROM expensestype);

-- Check for soft-deleted records

SELECT * FROM expenseexchange WHERE del = 1 AND projectid = ?;

---

## 🧪 Testing Scenarios

### Test Case 1: Complete Financial Report

1. Create project with known expenses and income

2. Request project report via API

3. Verify all expense categories appear

4. Check financial calculations manually

5. Verify profit/loss calculation accuracy

6. Test with different supervision types

### Test Case 2: Stage File Handling

1. Create project with multiple stages

2. Upload images and documents to stages

3. Request project report

4. Verify all file URLs are correctly formed

5. Test file accessibility via generated URLs

6. Verify original filenames preserved

### Test Case 3: Edge Cases

1. Test project with no expenses

2. Test project with no income

3. Test project with only material returns

4. Test project with missing cost center

5. Verify appropriate defaults and error handling

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • api_web.md - General web API functions
• • Database Schema Documentation - Complete table relationships
• • Financial Calculation Guide - Detailed calculation methods

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When financial calculation logic changes