# 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
- [x] Comprehensive project data retrieval with financial analysis
- [x] Client payment history and debt change tracking
- [x] Detailed expense categorization and supervision calculations
- [x] Multi-source income aggregation and reporting
- [x] Project stage management with file handling
- [x] Timeline and milestone tracking
- [x] Material exchange and return processing
- [x] Advanced financial formulas and profit/loss calculations

### Related Controllers
- [api_web.php](api_web.md) - General web client API
- [projectController.php](#) - Internal project management
- [expensesController.php](#) - Expense tracking
- [clientController.php](#) - Customer management

---

## 🗄️ Database Tables

### Core Project Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **project** | Project master data | id, clientid, costcenterid, supervision_type, supervision_amount |
| **client** | Customer information | clientid, clientname, clientdebt |
| **costcenter** | Cost center assignments | id, name |

### Financial Tracking Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **clientdebtchange** | Payment/debt history | clientid, clientdebtchangeamount, clientdebtchangedate, paySerialNo |
| **expenseexchange** | Direct project expenses | projectid, expensetype, thevalue, finalsupervision |
| **income** | Project income records | costcenterid, incomeName, incomeValue, conditions |
| **expensestype** | Expense categories | expensestypeid, expensestypename |

### Material & Exchange Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **projectexchmaterial** | Material exchange headers | id, projectid |
| **projectexchmaterialdetail** | Exchange line items | projectexchid, expensestypeid, totalbuyprice, finalsupervision |
| **projectexchmaterialreturn** | Material return headers | id, projectid |
| **projectexchmaterialdetailreturn** | Return line items | projectexchid, expensestypeid, totalbuyprice, finalsupervision |

### Project Management Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **projectstages** | Stage definitions | id, name |
| **projectstagechosse** | Selected project stages | projectid, projectstageid, projectstageimages, projectstagefiles, oldnamefiles |
| **projecttimetableitems** | Timeline items | projectid, clause, amount, duration, startdate, enddate |

---

## 🔑 Key Functions

### 1. **projectReport** - Comprehensive Project Analysis
**Location**: Line 177  
**Purpose**: Generate complete project report with financial analysis and file management

**Function Signature**:
```php
// GET parameter: id (project ID)
// Returns comprehensive JSON report
```

**Core Financial Logic**:

**A. Expense Aggregation**:
```php
// 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**:
```php
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**:
```php
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**:
```php
$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**:
```php
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**:
```php
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**:
```php
// 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
```
┌─────────────────────────────────────────────────────────────┐
│         START: Generate Complete Project Report            │
│   GET: projectApi.php?do=projectReport&id={projectid}      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Project Master Data                               │
│     - Query project table for basic information            │
│     - Build file URLs with host prefix                     │
│     - Initialize financial calculation variables           │
│     - Set base image/file source path                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Project Stages (Conditional)                   │
│     IF project.projectstagesdata = 1:                      │
│       A. Load Stage Assignments:                           │
│         - Query projectstagechosse for project             │
│         - Get stage definitions from projectstages         │
│                                                             │
│       B. Process Stage Media:                               │
│         - Split comma-separated image filenames            │
│         - Build full URLs for each image                   │
│         - Split comma-separated document filenames         │
│         - Combine with original filenames                  │
│         - Build download URLs for documents                │
│                                                             │
│       C. Build Stage Array:                                │
│         - Include stage name, images array, files array    │
│     ELSE:                                                   │
│       - Set empty stages array                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Timeline Items (Conditional)                   │
│     IF project.projecttimetableitems = 1:                  │
│       - Query projecttimetableitems for project            │
│       - Include clause, amount, duration, dates            │
│       - Build timeline array                               │
│     ELSE:                                                   │
│       - Set empty timeline array                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Calculate Expenses (Conditional)                       │
│     IF project.expenses = 1:                               │
│       A. Material Exchange Expenses:                       │
│         - Query projectexchmaterialdetail grouped by type  │
│         - Sum totalbuyprice and finalsupervision           │
│         - Build expense type list                          │
│                                                             │
│       B. Material Returns (Subtractions):                  │
│         - Query projectexchmaterialdetailreturn            │
│         - Group by expense type                            │
│         - Subtract from corresponding expense totals       │
│                                                             │
│       C. Direct Expenses:                                  │
│         - Query expenseexchange by project and type        │
│         - Sum thevalue and finalsupervision                │
│         - Add to expense type totals                       │
│                                                             │
│       D. Expense Type Aggregation:                         │
│         FOR EACH unique expense type:                      │
│           - Load expense type master data                  │
│           - Sum: material + direct - returns               │
│           - Apply supervision calculations                 │
│           - Add to final expense array                     │
│                                                             │
│       E. Supervision Handling:                             │
│         IF supervision_type = 1:                           │
│           - Use fixed supervision_amount                   │
│         ELSE:                                              │
│           - Calculate based on expense percentages        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Income (Conditional)                         │
│     IF project.income = 1:                                 │
│       A. Client Payment History:                           │
│         - Query clientdebtchange for project client        │
│         - Filter: paySerialNo > 0 (confirmed payments)     │
│         - Filter: del = 0 (not deleted)                    │
│         - Sum clientdebtchangeamount                       │
│         - Build payment detail array                       │
│                                                             │
│       B. Other Income Sources:                              │
│         - Query income by project cost center              │
│         - Filter: conditions = 0 (active income)           │
│         - Sum incomeValue                                   │
│         - Build income detail array                        │
│                                                             │
│       C. Total Income Calculation:                         │
│         - alltotalsincome = client payments + other income │
│     ELSE:                                                   │
│       - Set income arrays to empty                         │
│       - Set income totals to 0                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Calculate Final Project Financials                     │
│     A. Total Project Costs:                                │
│       - alltotals = sum of all direct expenses             │
│       - alltotalvalues = supervision costs                 │
│                                                             │
│     B. Project Profit/Loss:                                │
│       - endtotals = (costs + supervision) - total income   │
│       - Positive = Loss (costs exceed income)              │
│       - Negative = Profit (income exceeds costs)           │
│                                                             │
│     C. Update Project Object:                              │
│       - Add all calculated financial totals                │
│       - Add profit/loss calculation                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Build & Return Comprehensive Response                  │
│     JSON Response Structure:                                │
│     {                                                       │
│       "project": {                                         │
│         // Basic project data + all financial totals      │
│         "alltotals": total_expenses,                       │
│         "alltotalvalues": supervision_costs,               │
│         "alltotalsincome": total_income,                   │
│         "endtotals": profit_loss_amount                    │
│       },                                                    │
│       "projectstages": [                                   │
│         {                                                   │
│           "projectstage": "stage_name",                    │
│           "images": ["url1", "url2"],                     │
│           "files": [{"name": "orig", "file": "url"}]      │
│         }                                                   │
│       ],                                                    │
│       "projecttimetableitems": [timeline_items],           │
│       "expenses": [expense_type_summaries],                │
│       "clientdebtchange": [payment_history],               │
│       "incomes": [income_records]                          │
│     }                                                       │
└─────────────────────────────────────────────────────────────┘
```

---

## 🧮 Financial Calculation Methods

### Expense Aggregation Formula
```php
// 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
```php
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
```php
$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
```json
{
  "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
```php
// 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**:
   ```sql
   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**:
```php
// 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**:
```php
// 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**:
```sql
-- 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [api_web.md](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