Employee Attendance Excel Controller Documentation

File: /controllers/EmployeeAttendanceExcelController.php

Purpose: Manages bulk employee attendance processing through Excel import with automated calculations and salary impact

Last Updated: December 20, 2024

Total Functions: 12+

Lines of Code: ~709

---

📋 Overview

The Employee Attendance Excel Controller is a comprehensive bulk attendance processing system that handles Excel file imports and automated attendance calculations. It provides:

• Multiple Excel format support for different attendance systems
• Automated late/absence detection and penalty calculation
• Overtime and extra hours calculation with bonus processing
• Integration with payroll system for salary adjustments
• Employee matching by code with validation
• Attendance system rule application
• Daily attendance history management
• CURL-based integration with employee personal records
• Date range processing for bulk attendance imports

Primary Functions

✅ Multi-format Excel attendance import (3 formats supported)
✅ Automated late penalty calculation based on attendance rules
✅ Overtime and extra hours bonus calculation
✅ Absent employee detection for date ranges
✅ Integration with employee attendance systems
✅ Salary impact processing via CURL to employee personal
✅ Daily attendance history tracking
✅ Employee validation and matching
✅ Transaction-safe bulk processing
✅ Date validation and range processing

Related Controllers

• employeeAttendance.php - Real-time attendance tracking
• employeeAttendanceSystems.php - Attendance system rules
• employeePersonalController.php - Employee financial records
• employeeController.php - Employee management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
employeeclosedayhistory	Daily attendance summaries	id, employeeid, day, attendanceTime, departureTime, latePeriod, lateHasPermission, lateDiscount, isAbsent, absentDiscount, status
employeeattendance	Individual attendance logs	employeeattendanceid, empid, theImage, sysdate, fingerid, rfid, accessType, syncToServer
employee	Employee master data	employeeId, empCode, employeeName, employeesubgroupid, employeegroupid, conditions, branchid, worthExtra, Hourlyrate, salary fields

### Attendance System Tables

Table Name	Purpose	Key Columns
employeeattendancesystem	Attendance rules and penalties	id, name, penalty fields, vacation settings
employeeattendancesystemweek	Weekly schedule definitions	id, employee_id, attendancedayen, attendancetime, departuretime, chosseday

### Reference Tables

Table Name	Purpose	Key Columns
youtubelink	Tutorial video links	youtubelinkid, title, url
user	System users	userid, username

---

🔑 Key Functions

1. addFromExcel() - Excel Import and Processing Engine

Location: Line 156

Purpose: Parse Excel attendance files and process attendance data with automated calculations

Function Signature:

function addFromExcel() {
    global $employeeEX, $employeeCloseDayHistory, $smarty;
}

Process Flow:

1. File Upload and Parsing:

- Upload Excel file to temporary directory

- Detect file type and create appropriate reader

- Parse worksheet data row by row

2. Multi-Format Support:

- Format 1: Employee dept/name/code + datetime

- Format 2: Employee code/name/dept + date + time range

- Format 3: Employee dept/name/code + datetime + check-in/out status

3. Employee Matching and Validation:

- Match employees by employee code

- Validate against active employee records

- Apply branch filtering if applicable

4. Attendance System Integration:

- Load weekly schedules for each employee

- Apply attendance system rules and penalties

- Calculate late minutes and overtime hours

5. Automated Calculations:

- Late penalty calculation based on minutes late

- Extra hours bonus calculation for overtime

- Absent employee detection for date ranges

Excel Format Processing:

if ($exceltype == 1) {
    $empDept = $rowData[$col++];
    $empName = $rowData[$col++];
    $empid = $rowData[$col++];
    $dateTime = $rowData[$col++];
    
    $dateArr = explode(' ', $dateTime, 2);
    $date = $dateArr[0];
    $time = date('H:i:s', strtotime($dateArr[1]));
}

Late Penalty Calculation:

$lateMinutes = ceil($diffAttend / 60);
if ($lateMinutes > 0 && $lateMinutes <= 30) {
    $row['delayDiscount'] = $employeeData['halfHourLateWithoutPermissionDisount'];
} else if ($lateMinutes > 30 && $lateMinutes <= 60) {
    $row['delayDiscount'] = $employeeData['hourLateWithoutPermissionDisount'];
} else if ($lateMinutes > 60 && $lateMinutes <= 90) {
    $row['delayDiscount'] = $employeeData['hourAndHalfLateWithoutPermissionDisount'];
} // ... continues for all time brackets

---

2. addEmployeesAttendance() - Bulk Attendance Processing

Location: Line 104

Purpose: Process bulk attendance updates from Excel import data with transaction safety

Process Flow:

1. Transaction Initialization: Start database transaction

2. Bulk Processing: Iterate through employee attendance data

3. Validation: Check date validity for each record

4. Attendance Update: Call closeday() function for each entry

5. Salary Impact: Apply penalties/bonuses to employee records

6. Extra Bonus Processing: Handle overtime bonuses separately

7. Transaction Commit: Commit all changes or rollback on error

Key Processing Loop:

foreach ($empitr as $itr) {
    $day = filter_input(INPUT_POST, 'date_' . $itr);
    $empid = (int) filter_input(INPUT_POST, 'empid_' . $itr);
    $attendanceTime = filter_input(INPUT_POST, 'attendanceTime' . $empid . '_' . $itr);
    $departureTime = filter_input(INPUT_POST, 'departureTime' . $empid . '_' . $itr);
    
    if (isRealDate(date_format(date_create($day), 'Y-m-d'))) {
        $employeeCloseDayHistory = closeday($empid, $id, $day, $attendanceTime, $departureTime, 
                                            $latePeriod, $lateHasPermission, $lateDiscount, 
                                            $isAbsent, $absentHasPermission, $absentDiscount);
        affectOnSalary($employeeCloseDayHistory, $oldEmployeeCloseDayHistory);
        
        // Handle extra bonus for overtime
        $extraBonus = (float) filter_input(INPUT_POST, 'extraBonus' . $empid . '_' . $itr);
        if($extraBonus > 0) {
            curlAddEmployeePersonal($empid, $oldEmployeeCloseDayHistory->day, $extraBonus, 13);
        }
    }
}

---

3. add() - Single Employee Attendance Creation

Location: Line 461

Purpose: Create or update attendance record for a single employee on a specific date

Function Signature:

function add($empid, $date, $attend_time = null, $depart_time = null)

Process Flow:

1. Date Processing: Format and validate date

2. Daily Initialization: Create daily records if first attendance

3. Attendance Logging: Create attendance records in database

4. History Update: Update daily attendance history

5. Status Management: Set absence status based on attendance data

Attendance Logic:

$isAbsent = 1;
$absenceDiscount = $employee->dayAbsenceWithoutPermissionDisount;
if ($attend_time || $depart_time) {
    $isAbsent = 0;
    $absenceDiscount = 0;
}

---

4. employeeAttendanceSystem() - System Configuration Builder

Location: Line 526

Purpose: Build attendance system configuration object for specific employee

Function Signature:

function employeeAttendanceSystem($emp, $attendanceTime, $departureTime)

Process Flow:

1. Create system configuration object

2. Set working hours (attendance/departure times)

3. Copy all penalty rates from employee record

4. Return JSON-encoded configuration

Configuration Building:

$sys = new stdClass();
$sys->attendanceTime = $attendanceTime;
$sys->departureTime = $departureTime;
$sys->halfHourLateWithPermissionDisount = $emp['halfHourLateWithPermissionDisount'];
// ... all penalty fields copied
$attendanceSystem = json_encode($sys);
return $attendanceSystem;

---

5. affectOnSalary() - Salary Impact Management

Location: Line 551

Purpose: Manage salary impact when attendance records change

Function Signature:

function affectOnSalary($newEmployeeCloseDayHistory, $oldEmployeeCloseDayHistory)

Process Flow:

1. Change Detection: Compare old vs new attendance data

2. Cleanup: Remove old salary impacts if changes detected

3. New Impact: Apply new penalties/bonuses to salary

4. Type-based Processing: Handle late penalties vs absence penalties separately

Impact Logic:

if ($newEmployeeCloseDayHistory->isAbsent == 0) { // Not absent
    // Apply late penalty (type 5)
    curlAddEmployeePersonal($newEmployeeCloseDayHistory->employeeid, 
                           $newEmployeeCloseDayHistory->day, 
                           $newEmployeeCloseDayHistory->lateDiscount, 5);
} elseif ($newEmployeeCloseDayHistory->isAbsent == 1) { // Absent
    // Apply absence penalty (type 9)  
    curlAddEmployeePersonal($newEmployeeCloseDayHistory->employeeid,
                           $newEmployeeCloseDayHistory->day,
                           $newEmployeeCloseDayHistory->absentDiscount, 9);
}

---

6. closeday() - Daily Attendance Closure

Location: Line 591

Purpose: Finalize daily attendance record with all calculations

Function Signature:

function closeday($empid, $closedDayHistoryId, $day, $attend_time, $depart_time, 
                  $latePeriod = 0, $lateHasPermission = 0, $lateDiscount = 0,
                  $isAbsent = 0, $absentHasPermission = 0, $absentDiscount = 0)

Process Flow:

1. Load existing daily history record

2. Update all attendance fields

3. Set status to 1 (affects applied)

4. Save record to database

5. Return updated history object

---

7. curlAddEmployeePersonal() - Salary System Integration

Location: Line 642

Purpose: Add salary adjustments via CURL to employee personal system

Function Signature:

function curlAddEmployeePersonal($empid, $day, $discountVal, $type)

Process Flow:

1. Validation: Skip if discount value is 0

2. Data Preparation: Build POST data array with session info

3. CURL Request: Send to employeePersonalController.php

4. Response Handling: Process response (logging available)

Integration Types:

• Type 5: Late penalties
• Type 9: Absence penalties
• Type 13: Extra hour bonuses

CURL Implementation:

$post = [
    'curlpost' => 1,
    'sessionlist' => json_encode($_SESSION),
    'fromCtrl' => 'EmployeeAttendanceExcelController',
    'empName' => $empid,
    'empValue' => $discountVal,
    'employeepersonneldate' => $dayAsDateTime,
    'type' => $type,
    'userid' => $_SESSION['userid'],
    'saveid' => $_SESSION["saveid"],
    'dbname' => $_SESSION["dbname"],
];

$url = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 
       'controllers/employeePersonalController.php?do=add';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
$response = curl_exec($ch);
curl_close($ch);

---

🔄 Workflows

Workflow 1: Excel Import Processing

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

START: Upload Excel File

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

▼

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

1File Upload and Validation

- Upload to temp directory

- Identify Excel file type

- Create appropriate PHPExcel reader

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

▼

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

2Parse Excel Data by Format

- Format 1: Dept/Name/Code + DateTime

- Format 2: Code/Name/Dept + Date + Time Range

- Format 3: Dept/Name/Code + DateTime + Status

- Extract employee codes and dates

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

▼

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

3Employee Validation and Matching

- Load active employees by employee codes

- Apply branch filtering

- Build employee data arrays

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

▼

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

4Attendance System Integration

- Load weekly schedules for each employee

- Match attendance times to required schedules

- Calculate working hours and overtime

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

▼

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

5Automated Calculations

FOR EACH attendance record:

│

→ Calculate Late Minutes

│ │ └─ Compare actual vs scheduled arrival │

│

→ Determine Penalty Amount

│ │ └─ Apply tiered penalty structure │

│

→ Calculate Overtime Hours

│ │ └─ Compare actual vs scheduled departure │

│

→ Calculate Extra Hour Bonus

│ │ └─ Apply hourly rate if worthExtra = 1 │

│

│ └─→ Create Daily History Record │

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

▼

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

6Absent Employee Detection

- Get date range from import

- Find employees not in attendance records

- Check if absent day is scheduled work day

- Create absence records with penalties

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

▼

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

7Display Processed Data

- Sort employees (invalid codes first)

- Show calculated penalties and bonuses

- Display for user review and confirmation

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

---

Workflow 2: Bulk Attendance Update

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

START: Process Attendance Updates

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

▼

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

1Initialize Transaction

- Start database transaction

- Prepare for rollback on any failure

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

▼

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

2Process Each Employee Record

FOR EACH employee in import data:

│

→ Validate Date Format

│ │ └─ Skip invalid dates │

│

→ Load Existing Daily History

│ │ └─ Get current attendance record │

│

→ Extract Form Data

│ ├─ Late period and permission flags

│ ├─ Late and absence discount amounts

│ │ └─ Extra bonus amounts │

│

│ └─→ Call closeday() Function │

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

▼

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

3Apply Salary Impacts

- Call affectOnSalary() for each record

- Remove old salary impacts if changed

- Add new penalties/bonuses to payroll

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

▼

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

4Process Extra Hour Bonuses

- Check for overtime bonus amounts > 0

- Add bonuses via CURL to employee personal

- Use type 13 for extra hour bonuses

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

▼

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

5Commit Transaction

- Commit all database changes

- Redirect to success page

- Or rollback and redirect to error on failure

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty) or `do=show`	Default action	Display Excel upload form
`do=addfromexcel`	`addFromExcel()`	Process uploaded Excel file
`do=addEmployeesAttendance`	Bulk processing	Process attendance updates with transaction
`do=success`	Success page	Display success message
`do=error`	Error page	Display error message

### Required Parameters by Action

Excel Upload (do=addfromexcel):

• attendancessheet - Excel file upload ($_FILES)
• exceltype - Format type (1, 2, or 3)

Bulk Update (do=addEmployeesAttendance):

• empIds[] - Array of employee iteration IDs
• For each employee/date combination:

- date_{itr} - Attendance date

- empid_{itr} - Employee ID

- attendanceTime{empid}_{itr} - Arrival time

- departureTime{empid}_{itr} - Departure time

- id{empid}_{itr} - Daily history record ID

- latePeriod{empid}_{itr} - Minutes late

- lateHasPermission{empid}_{itr} - Permission flag

- lateDiscount{empid}_{itr} - Late penalty amount

- absentHasPermission{empid}_{itr} - Absence permission flag

- absentDiscount{empid}_{itr} - Absence penalty amount

- absent{empid}_{itr} - Absence flag

- extraBonus{empid}_{itr} - Overtime bonus amount

---

🧮 Calculation Methods

Late Penalty Tiered Structure

if ($lateMinutes > 0 && $lateMinutes <= 30) {
    $row['delayDiscount'] = $employeeData['halfHourLateWithoutPermissionDisount'];
} else if ($lateMinutes > 30 && $lateMinutes <= 60) {
    $row['delayDiscount'] = $employeeData['hourLateWithoutPermissionDisount'];
} else if ($lateMinutes > 60 && $lateMinutes <= 90) {
    $row['delayDiscount'] = $employeeData['hourAndHalfLateWithoutPermissionDisount'];
} else if ($lateMinutes > 90 && $lateMinutes <= 120) {
    $row['delayDiscount'] = $employeeData['twoHoursLateWithoutPermissionDisount'];
} else if ($lateMinutes > 120) {
    $row['delayDiscount'] = $employeeData['moreThanTwoHoursLateWithoutPermissionDisount'];
}

Overtime Bonus Calculation

if ($employeeData['worthExtra'] == 1 && $diffDeparture > 0) {
    $extraHours = floor($diffDeparture / (60*60));
    $row['extraHoursTime'] = $extraHours;
    $row['extraBonus'] = $extraHours * $employeeData['Hourlyrate'];
}

Working Hours Calculation

if ($row['depart_time'] && $row['attend_time']) {
    $datetime1 = new DateTime($row['attend_time']);
    $datetime2 = new DateTime($row['depart_time']);
    $interval = $datetime1->diff($datetime2);
    $row['hoursWorked'] = $interval->format('%h:%i');
}

Date Range Generation

function getDatesBetween($startDate, $endDate, $format = 'Y-m-d') {
    $dates = [];
    $begin = new DateTime($startDate);
    $end = new DateTime($endDate);
    $interval = new DateInterval('P1D');
    $dateRange = new DatePeriod($begin, $interval, $end->modify('+1 day'));
    
    foreach ($dateRange as $date) {
        $dates[] = $date->format($format);
    }
    return $dates;
}

---

🔒 Security & Permissions

Authentication

• Required for All Actions: include_once("../public/authentication.php")
• Transaction Safety: Database transactions with rollback capability

File Upload Security

• Temporary Storage: Files stored in dedicated upload directory
• Cleanup: Uploaded files deleted after processing
• Type Validation: PHPExcel validates file format

Input Validation

• Type Casting: All numeric inputs cast appropriately
• Date Validation: isRealDate() function validates date formats
• SQL Injection: DAO layer provides parameterized queries

CURL Security

• Session Validation: Session data passed with CURL requests
• Internal URLs: CURL requests only to internal controllers
• Error Handling: CURL errors caught and handled gracefully

---

📊 Performance Considerations

Excel Processing Optimization

1. Memory Usage: Large Excel files may exceed memory limits

2. Processing Time: Complex calculations per row can be slow

3. File Cleanup: Temporary files deleted after processing

Database Performance

1. Bulk Operations: Transaction-wrapped for consistency

2. Index Requirements:

- employee(empCode, conditions, branchid)

- employeeclosedayhistory(employeeid, day, del)

- employeeattendancesystemweek(employee_id, attendancedayen)

Known Performance Issues

• CURL Overhead: Individual CURL requests for each salary adjustment
• No Batching: Employee personal updates not batched
• Large Date Ranges: Processing months of data can be slow

---

🐛 Common Issues & Troubleshooting

1. Excel Import Fails

Issue: Excel file not processed or data not extracted

Causes:

• Unsupported Excel format
• Missing PHPExcel library
• File permissions issues

Debug:

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
echo "Detected file type: " . $inputFileType;

if (!file_exists($inputFileName)) {
    echo "File not uploaded properly";
}

2. Employee Not Found by Code

Issue: Employees show as invalid or not matched

Causes:

• Employee code mismatch between Excel and database
• Employee marked as deleted (conditions != 0)
• Branch filtering excluding employees

Debug:

SELECT employeeId, empCode, employeeName, conditions, branchid 
FROM employee 
WHERE empCode IN ('code1', 'code2', ...);

3. Salary Impacts Not Applied

Issue: Penalties/bonuses not reflecting in payroll

Causes:

• CURL request failing
• employeePersonalController.php not accessible
• Session data corruption

Debug:

// Add to curlAddEmployeePersonal function
echo "CURL Response: " . $response;
echo "CURL Error: " . curl_error($ch);

4. Date Range Processing Errors

Issue: Some dates skipped or invalid

Causes:

• Date format inconsistencies in Excel
• Timezone issues
• Invalid date values

Fix:

function isRealDate($date) {
    if (false === strtotime($date)) {
        return false;
    }
    list($year, $month, $day) = explode('-', $date);
    return checkdate($month, $day, $year);
}

---

🧪 Testing Scenarios

Test Case 1: Multi-Format Excel Import

1. Create test Excel files for all 3 formats
2. Include various employee codes, dates, and times
3. Test with valid and invalid employee codes
4. Verify data extraction and employee matching
5. Check automated calculation accuracy

Test Case 2: Late Penalty Calculation

1. Create attendance records with various late minutes
2. Test all penalty tiers (15min, 30min, 1hr, etc.)
3. Verify permission vs non-permission penalties
4. Check salary impact integration
5. Test edge cases (exactly on time, very late)

Test Case 3: Overtime Bonus Processing

1. Set up employees with worthExtra = 1
2. Create attendance with overtime hours
3. Verify extra hours calculation
4. Check bonus amount = hours * hourly rate
5. Confirm bonus applied to employee personal

Test Case 4: Absent Employee Detection

1. Create date range with some employee absences
2. Verify absent employees identified correctly
3. Check work day vs non-work day detection
4. Test absence penalty application
5. Validate daily history record creation

Test Case 5: Transaction Safety

1. Create scenario that will cause error mid-processing
2. Verify database rollback occurs
3. Check no partial data saved
4. Test error handling and user notification
5. Verify system state consistency

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• employeeAttendance.md - Real-time attendance
• employeeAttendanceSystems.md - System configuration
• employeePersonalController.md - Payroll integration

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

EmployeeAttendanceExcel Documentation

Employee Attendance Excel Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. addFromExcel() - Excel Import and Processing Engine

2. addEmployeesAttendance() - Bulk Attendance Processing

3. add() - Single Employee Attendance Creation

4. employeeAttendanceSystem() - System Configuration Builder

5. affectOnSalary() - Salary Impact Management

6. closeday() - Daily Attendance Closure

7. curlAddEmployeePersonal() - Salary System Integration

🔄 Workflows

Workflow 1: Excel Import Processing

Workflow 2: Bulk Attendance Update

🌐 URL Routes & Actions

🧮 Calculation Methods

Late Penalty Tiered Structure

Overtime Bonus Calculation

Working Hours Calculation

Date Range Generation

🔒 Security & Permissions

Authentication

File Upload Security

Input Validation

CURL Security

📊 Performance Considerations

Excel Processing Optimization

Database Performance

Known Performance Issues

🐛 Common Issues & Troubleshooting

1. Excel Import Fails

2. Employee Not Found by Code

3. Salary Impacts Not Applied

4. Date Range Processing Errors

🧪 Testing Scenarios

Test Case 1: Multi-Format Excel Import

Test Case 2: Late Penalty Calculation

Test Case 3: Overtime Bonus Processing

Test Case 4: Absent Employee Detection

Test Case 5: Transaction Safety

📚 Related Documentation