Employee Report Controller Documentation

File: /controllers/employeereport.php

Purpose: Comprehensive employee reporting system with individual profiles and payroll analytics

Last Updated: December 20, 2024

Total Functions: 5+

Lines of Code: ~216

---

📋 Overview

The Employee Report Controller provides detailed reporting capabilities for employee management, focusing on individual employee profiles and comprehensive payroll analytics. It serves as a central hub for employee information display and workforce analytics with advanced filtering and aggregation capabilities.

Key Capabilities

• Individual employee profile reporting with age calculation
• Comprehensive payroll analytics with grouping by employee groups
• Branch-based filtering and access control
• Date range filtering for payroll analysis
• Multi-type payroll transaction categorization (deductions, advances, absences, etc.)
• Integration with salary report history
• YouTube tutorial integration for user guidance

Primary Functions

✅ Generate individual employee profile reports
✅ Calculate and display employee ages from birth dates
✅ Provide comprehensive payroll analytics by employee group
✅ Track multiple payroll transaction types (deductions, advances, absences, permissions, leaves)
✅ Generate monthly and custom date range reports
✅ Integrate with salary history tracking
✅ Support branch-based access control
✅ Display transaction counters for frequency analysis

Related Controllers

• employeeController.php - Core employee management
• employeePersonalController.php - Payroll transactions
• salaryReportController.php - Salary processing
• employeegroups.php - Group management

---

🗄️ Database Tables

Primary Employee Tables

Table Name	Purpose	Key Columns
employee	Employee master records	employeeId, employeeName, employeeBOD, employeeSalary, employeegroupid, branchid, conditions
employeegroup	Employee group definitions	id, name, del

### Payroll Tables

Table Name	Purpose	Key Columns
employeepersonnel	Payroll transactions	employeepersonnelid, employeeid, employeepersonnelvalue, employeepersonneltype, employeepersonneldate, conditions
salaryreport	Salary processing history	salaryreportid, employeeid, salaryreportnet, salarymonthdate

### Organizational Tables

Table Name	Purpose	Key Columns
branch	Branch information	branchid, branchname, branchstatus

### Configuration Tables

Table Name	Purpose	Key Columns
youtubelink	Tutorial references	youtubelinkid, title, url

---

🔑 Key Functions

1. Default Action - Individual Employee Profiles

Location: Line 52-81

Purpose: Display individual employee information with age calculation and optional filtering

Function Signature:

// Default action (empty $do)
// Optional POST parameter: chooseEmp - Employee ID filter

Process Flow:

1. Load Employee Dropdown Data:

   $queryStringAll = '';
   if ($_SESSION['branchId'] > 0)
       $queryStringAll = ' AND  branchid = ' . $_SESSION['branchId'];
   
   $allemployee = $employeeEX->queryAllSimple($queryStringAll);
   $smarty->assign('allemployee', $allemployee);
   ```

2. **Apply Employee Filter** (if specified):
   ```php
   $empid = filter_input(INPUT_POST, "chooseEmp");
   $queryString = "";
   if (!empty($empid)) {
       $queryString = " and employeeId =" . $empid;
   }
   ```

3. **Calculate Employee Ages**:
   ```php
   foreach ($allData as $value) {
       if (!empty($value->employeeBOD) && $value->employeeBOD != "0000-00-00") {
           $value->employeeBOD = floor((time() - strtotime($value->employeeBOD)) / (60 * 60 * 24 * 365));
       } else {
           $value->employeeBOD = "";
       }
   }
   ```

4. **Display Report**:
   - Assign data to Smarty template
   - Load YouTube tutorial links
   - Display employeereportview/show.html

**Features**:
- Dynamic age calculation from birth dates
- Branch-based access control
- Optional employee filtering
- Integration with tutorial system

---

### 2. **totals** - Comprehensive Payroll Analytics
**Location**: Line 82-207  
**Purpose**: Generate detailed payroll analytics with grouping and aggregation

**Function Signature**:

php

// Action: do=totals

// POST Parameters:

$month = filter_input(INPUT_POST, 'month'); // YYYY-MM format

$empGroup = filter_input(INPUT_POST, 'empGroup'); // Employee group filter

$branchId = filter_input(INPUT_POST, 'branchId'); // Branch filter

$from = filter_input(INPUT_POST, 'from'); // Date range start

$to = filter_input(INPUT_POST, 'to'); // Date range end

**Process Flow**:
1. **Load Filter Options**:
   ```php
   $employeeGroup = R::getAll('SELECT * FROM employeegroup WHERE del =0');
   $smarty->assign('employeeGroup', $employeeGroup);
   
   $allbranchdata = R::getAll('SELECT * FROM branch');
   $smarty->assign("allbranchdata", $allbranchdata);
   ```

2. **Build Dynamic SQL Filter**:
   ```php
   $sql = '';
   if ($month) {
       $sql .= ' AND MONTH(employeepersonneldate) = ' . date('m', strtotime($month)) . 
               ' AND Year(employeepersonneldate) = ' . date('Y', strtotime($month));
   } else {
       $month = date('Y-m');
       $sql .= ' AND MONTH(employeepersonneldate) = MONTH(CURDATE()) AND Year(employeepersonneldate) = Year(CURDATE()) ';
   }
   
   if ($from && $to) {
       $month = '';
       $sql .= ' AND employeepersonneldate >= "' . $from . '" AND employeepersonneldate <= "' . $to . '"';
   }
   ```

3. **Apply Additional Filters**:
   ```php
   if ($branchId)
       $sql .= ' AND employee.branchid = ' . $branchId;
   else {
       if ($_SESSION['branchId'] > 0)
           $sql .= ' AND  employee.branchid = ' . $_SESSION['branchId'];
   }
   
   if ($empGroup)
       $sql .= ' AND employee.employeegroupid = ' . $empGroup;
   ```

4. **Execute Aggregation Query**:
   ```php
   $allEmployeePersonals = R::getAll('SELECT employee.employeeName,employeeSalary,employee.employeeId, 
                                      SUM(employeepersonnelvalue) as total,
                                      COUNT(employeepersonnelid) AS typeCounter, 
                                      employeepersonneltype
                                      FROM employeepersonnel 
                                      JOIN employee ON employee.employeeId = employeepersonnel.employeeid
                                      WHERE employee.conditions = 0 AND employeepersonnel.conditions = 0
                                      ' . $sql . '
                                      GROUP BY employeepersonneltype, employeepersonnel.employeeid ');
   ```

5. **Process and Categorize Results**:
   ```php
   foreach ($allEmployeePersonals as $emp) {
       $delay = $reward = $dis = $withdraw = $borrow = $absence = $repay = $permission = $leave = '0';
       $delayCounter = $rewardCounter = $disCounter = $withdrawCounter = $borrowCounter = 
                      $absenceCounter = $repayCounter = $permissionCounter = $leaveCounter = '0';
       
       // Categorize by transaction type
       if ($emp['employeepersonneltype'] == 9) {
           $absence = $emp['total']; // غياب
           $absenceCounter = $emp['typeCounter'];
       } elseif ($emp['employeepersonneltype'] == 5) {
           $delay = $emp['total']; // تأخير
           $delayCounter = $emp['typeCounter'];
       }
       // ... (additional type categorization)
   }
   ```

6. **Load Salary History**:
   ```php
   $lastSalary = R::getRow('select salaryreportnet,salarymonthdate from salaryreport 
                           where employeeid = ' . $emp['employeeId'] . ' 
                           order by salaryreportid desc');
   ```

**Payroll Transaction Types**:
- **Type 2**: Deductions (خصم)
- **Type 3**: Withdrawals (سحب) 
- **Type 4**: Advances/Loans (سلفة)
- **Type 5**: Late arrivals (تأخير)
- **Type 9**: Absences (غياب)
- **Type 11**: Permissions (استئذان)
- **Type 14**: Early departures (انصراف)

---

## 🧮 Calculation Methods

### Age Calculation from Birth Date

php

if (!empty($value->employeeBOD) && $value->employeeBOD != "0000-00-00") {

// Calculate age in years using timestamp difference

$value->employeeBOD = floor((time() - strtotime($value->employeeBOD)) / (60 60 24 * 365));

} else {

$value->employeeBOD = "";

}

### Payroll Aggregation Logic

php

// Group by employee and transaction type, then sum values

if (array_key_exists($emp['employeeId'], $empArr)) {

// Add to existing employee record

$empArr[$emp['employeeId']]['dis'] += $dis;

$empArr[$emp['employeeId']]['disCounter'] += $disCounter;

$empArr[$emp['employeeId']]['withdraw'] += $withdraw;

// ... (continue for all types)

} else {

// Create new employee record

$empArr[$emp['employeeId']] = [

'name' => $emp['employeeName'],

'dis' => $dis,

'disCounter' => $disCounter,

// ... (all payroll categories)

'salary' => $emp['employeeSalary'],

'lastSalary' => $lastSalary['salaryreportnet'],

'lastSalaryDate' => $lastSalary['salarymonthdate']

];

}

### Date Range Processing

php

// Month-based filtering

if ($month) {

$sql .= ' AND MONTH(employeepersonneldate) = ' . date('m', strtotime($month)) .

' AND Year(employeepersonneldate) = ' . date('Y', strtotime($month));

}

// Custom date range filtering

if ($from && $to) {

$sql .= ' AND employeepersonneldate >= "' . $from . '" AND employeepersonneldate <= "' . $to . '"';

}

---

## 🔄 Workflows

### Workflow 1: Individual Employee Report Generation

┌─────────────────────────────────────────────────────────────┐

│ START: Access Employee Report │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Load Employee Dropdown │

│ - Apply branch filtering if applicable │

│ - Load all active employees │

│ - Populate employee selection dropdown │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Apply Optional Employee Filter │

│ IF specific employee selected: │

│ │ - Add employee ID filter to query │

│ │ - Limit results to selected employee │

│ ELSE: │

│ │ - Show all employees (branch-filtered) │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Process Employee Data │

│ - Load employee records with filters applied │

│ - Calculate ages from birth dates │

│ - Convert birth dates to age in years │

│ - Handle missing/invalid birth dates │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 4. Generate Report Display │

│ - Assign processed data to template │

│ - Load YouTube tutorial links │

│ - Display individual employee profiles │

└─────────────────────────────────────────────────────────────┘

---

### Workflow 2: Payroll Analytics Generation

┌─────────────────────────────────────────────────────────────┐

│ START: Request Payroll Analytics │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Load Filter Configuration │

│ - Load all employee groups for dropdown │

│ - Load all branches for selection │

│ - Set default month to current month │

│ - Prepare filter interface │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Build Dynamic SQL Filter │

│ IF Month specified: │

│ │ - Filter by MONTH() and YEAR() │

│ IF Date range specified: │

│ │ - Use >= and <= date comparisons │

│ IF Employee group specified: │

│ │ - Filter by employeegroupid │

│ IF Branch specified: │

│ │ - Filter by branchid │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Execute Aggregation Query │

│ - JOIN employeepersonnel with employee │

│ - GROUP BY employeeid and transaction type │

│ - SUM transaction values by type │

│ - COUNT transaction frequencies │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 4. Process and Categorize Results │

│ FOR EACH Employee Transaction Group: │

│ │ │

│ ├─→ Categorize by transaction type │

│ │ ├─ Type 2: Deductions │

│ │ ├─ Type 3: Withdrawals │

│ │ ├─ Type 4: Advances │

│ │ ├─ Type 5: Late arrivals │

│ │ ├─ Type 9: Absences │

│ │ ├─ Type 11: Permissions │

│ │ └─ Type 14: Early departures │

│ │ │

│ ├─→ Aggregate amounts and counters by employee │

│ │ │

│ └─→ Load last salary information │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 5. Generate Analytics Report │

│ - Display aggregated payroll data by employee │

│ - Show transaction counts for frequency analysis │

│ - Include last salary information │

│ - Provide drill-down capabilities │

└─────────────────────────────────────────────────────────────┘

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| (empty) | Default action | Individual employee profile report |
| `do=totals` | Payroll analytics | Comprehensive payroll analytics dashboard |
| `do=sucess` | Success page | Display success message |
| `do=error` | Error page | Display error message |

### Required Parameters by Action

**Individual Employee Report** (default):
- `chooseEmp` - Employee ID (optional, filters to specific employee)

**Payroll Analytics** (`do=totals`):
- `month` - Month filter (YYYY-MM format, optional)
- `empGroup` - Employee group filter (optional)
- `branchId` - Branch filter (optional)
- `from` - Start date for custom range (optional)
- `to` - End date for custom range (optional)

### Parameter Priority Logic
1. If `from` and `to` are specified: Use date range, ignore month
2. If `month` is specified: Use month/year filter
3. If neither: Default to current month
4. Branch filter: Uses session branchId if user is branch-restricted
5. Group filter: Applied in addition to other filters

---

## 🔒 Security & Permissions

### Authentication Requirements

php

include_once("../public/authentication.php");

- All actions require valid user session
- User session tracked for audit purposes

### Branch-Level Access Control

php

if ($_SESSION['branchId'] > 0)

$queryStringAll = ' AND branchid = ' . $_SESSION['branchId'];

**Access Control Logic**:
- Users with `$_SESSION['branchId'] > 0`: See only their branch employees
- Super administrators (`branchId = 0`): See all employees across branches
- Branch filter parameter only works within user's permitted scope

### Input Sanitization

php

$empid = filter_input(INPUT_POST, "chooseEmp");

$month = filter_input(INPUT_POST, 'month');

$empGroup = filter_input(INPUT_POST, 'empGroup');

$branchId = filter_input(INPUT_POST, 'branchId');

**Security Features**:
- All input parameters filtered using `filter_input()`
- SQL injection prevented by RedBean ORM and prepared statements
- Numeric parameters validated implicitly through database operations
- Date parameters processed through PHP's date functions for validation

---

## 🐛 Common Issues & Troubleshooting

### 1. **Age Calculation Issues**
**Issue**: Incorrect age display or empty age fields  
**Causes**: 
- Invalid birth date formats (0000-00-00)
- Empty birth date fields
- Date format inconsistencies

**Debug Steps**:

sql

-- Check birth date data quality

SELECT employeeId, employeeName, employeeBOD

FROM employee

WHERE employeeBOD = '0000-00-00' OR employeeBOD IS NULL OR employeeBOD = '';

-- Check date format consistency

SELECT employeeId, employeeBOD,

STR_TO_DATE(employeeBOD, '%Y-%m-%d') as parsed_date

FROM employee

WHERE employeeBOD != '0000-00-00' AND employeeBOD IS NOT NULL;

### 2. **Payroll Analytics Aggregation Issues**
**Issue**: Incorrect totals or missing transaction types  
**Common Causes**:
- New transaction types not handled in categorization logic
- Date range filters excluding expected data
- Employee group assignments missing

**Debug Query**:

sql

-- Check transaction type distribution

SELECT employeepersonneltype, COUNT(*) as count, SUM(employeepersonnelvalue) as total

FROM employeepersonnel

WHERE conditions = 0

AND employeepersonneldate >= '2024-01-01'

AND employeepersonneldate <= '2024-12-31'

GROUP BY employeepersonneltype

ORDER BY employeepersonneltype;

### 3. **Branch Filtering Issues**
**Issue**: Users see employees from other branches  
**Causes**:
- Session branchId not set correctly
- Employee records missing branchid assignments
- Filter logic not applied consistently

**Verification**:

php

// Debug branch filtering

echo "User Branch ID: " . $_SESSION['branchId'] . "\n";

echo "Query String: " . $queryStringAll . "\n";

// Check employee branch assignments

$employees_without_branch = R::getAll(

'SELECT employeeId, employeeName FROM employee WHERE branchid IS NULL OR branchid = 0'

);

### 4. **Performance Issues with Large Datasets**
**Issue**: Slow loading on payroll analytics with many employees  
**Solutions**:
- Implement date range limitations
- Add pagination for large result sets
- Optimize aggregation queries with proper indexing

sql

-- Add performance indexes

CREATE INDEX idx_employeepersonnel_date_type ON employeepersonnel(employeepersonneldate, employeepersonneltype);

CREATE INDEX idx_employeepersonnel_employee ON employeepersonnel(employeeid, conditions);

CREATE INDEX idx_employee_group_branch ON employee(employeegroupid, branchid, conditions);

---

## 📊 Performance Considerations

### Database Optimization
1. **Essential Indexes**:
   ```sql
   -- Employee queries
   CREATE INDEX idx_employee_branch_conditions ON employee(branchid, conditions);
   CREATE INDEX idx_employee_group_conditions ON employee(employeegroupid, conditions);
   CREATE INDEX idx_employee_bod ON employee(employeeBOD);
   
   -- Payroll queries
   CREATE INDEX idx_employeepersonnel_composite ON employeepersonnel(employeeid, employeepersonneltype, employeepersonneldate, conditions);
   CREATE INDEX idx_employeepersonnel_date_range ON employeepersonnel(employeepersonneldate, conditions);
   
   -- Salary reports
   CREATE INDEX idx_salaryreport_employee_date ON salaryreport(employeeid, salarymonthdate);
   ```

2. **Query Optimization**:
   - Use appropriate date range filters to limit result sets
   - Implement GROUP BY efficiently with proper indexing
   - Consider materialized views for frequently accessed aggregations

### Memory Management
- Large aggregation queries can consume significant memory
- Implement result limiting for interactive displays
- Use streaming processing for export operations

### Caching Strategies
- Cache employee dropdown data for session duration
- Store frequently accessed group/branch combinations
- Implement query result caching for expensive aggregations

---

## 🧪 Testing Scenarios

### Test Case 1: Individual Employee Report

1. Access default report without employee filter

2. Verify all branch-appropriate employees display

3. Check age calculations for various birth dates

4. Test with specific employee selection

5. Validate branch filtering works correctly

6. Test with employees having no birth date

### Test Case 2: Payroll Analytics

1. Generate report for current month

2. Test with custom date ranges

3. Verify employee group filtering

4. Check branch filtering accuracy

5. Test with employees having various transaction types

6. Validate aggregation calculations

7. Check last salary data integration

### Test Case 3: Branch Access Control

1. Login as branch-restricted user

2. Verify only branch employees appear

3. Test payroll analytics respects branch limits

4. Login as super admin and verify all access

5. Check filter parameters work within permissions

### Test Case 4: Data Quality Handling

1. Test with employees having invalid birth dates

2. Check behavior with missing payroll data

3. Test with employees in multiple groups

4. Verify handling of deleted/inactive records

5. Test edge cases in date range filtering

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• employeeController.md - Core employee management
• employeePersonalController.md - Payroll transactions
• salaryReportController.md - Salary processing

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Employeereport Documentation

Employee Report Controller Documentation

📋 Overview

Key Capabilities

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Employee Tables

🔑 Key Functions

1. Default Action - Individual Employee Profiles

📚 Related Documentation