# Employee Holidays Controller Documentation

**File**: `/controllers/employeeHolidays.php`  
**Purpose**: Manages company-wide holiday definitions and employee holiday calendar administration  
**Last Updated**: December 20, 2024  
**Total Functions**: 6+  
**Lines of Code**: ~189

---

## 📋 Overview

The Employee Holidays Controller manages the company holiday calendar system that defines official holidays affecting employee attendance and payroll calculations. It provides:
- Holiday definition and management
- Auto-save functionality for inline editing
- AJAX-powered holiday listing with search and pagination
- Holiday search and selection for other modules
- Multi-user holiday administration
- Integration with attendance systems
- Soft deletion with recovery capabilities
- Real-time holiday validation

### Primary Functions
- [x] Create and manage company holidays
- [x] Auto-save functionality for seamless editing
- [x] AJAX-based holiday listing with DataTables integration
- [x] Holiday search with autocomplete
- [x] Soft deletion and recovery
- [x] Multi-user audit trail
- [x] Date-based filtering and reporting
- [x] Integration with employee attendance systems

### Related Controllers
- [employeeAttendance.php](employeeAttendance.md) - Attendance tracking integration
- [employeeAttendanceSystems.php](employeeAttendanceSystems.md) - System configuration
- [EmployeeAttendanceExcelController.php](EmployeeAttendanceExcelController.md) - Bulk attendance processing
- [employeeController.php](employeeController.md) - Employee management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **employeeholidays** | Holiday definitions and calendar | id, holiday, holiday_date, userid, today, del |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | System users for audit trail | userid, username |

---

## 🔑 Key Functions

### 1. **Default Action** - Holiday Management Interface
**Location**: Line 26  
**Purpose**: Display holiday creation/management interface with auto-initialization

**Process Flow**:
1. **Auto-Initialize**: Check for existing empty holiday record
2. **Create if Missing**: Create new empty holiday record if none exists
3. **Load Existing**: Load existing empty record for editing
4. **Display Interface**: Show holiday management form

**Auto-Initialization Logic**:
```php
$employeeholidaysnull = R::findOne('employeeholidays',"ISNULL(NULLIF(holiday, ''))");
if(!$employeeholidaysnull->id){
    $employeeholidays = R::dispense('employeeholidays');
    $employeeholidays->holiday = '';         
    $employeeholidays->holiday_date = date("Y-m-d");  
    $employeeholidays->userid = $_SESSION['userid'];
    $employeeholidays->today = $today;
    $employeeholidays->del = 0;
    R::store($employeeholidays);
} else {
    // Update existing empty record with current date/user
    $employeeholidaysnull->holiday_date = date("Y-m-d");  
    $employeeholidaysnull->userid = $_SESSION['userid'];
    $employeeholidaysnull->today = $today;
    R::store($employeeholidaysnull);
}
```

**Features**:
- **Auto-Creation**: Automatically creates working record for new holidays
- **Date Pre-fill**: Sets default date to current date
- **User Tracking**: Records current user for audit
- **Seamless UX**: No separate "create new" button needed

---

### 2. **addappend()** - Add Additional Holiday Entry
**Location**: Line 50  
**Purpose**: Create additional blank holiday entry for bulk holiday creation

**Process Flow**:
1. Create new empty holiday record
2. Set current date as default
3. Track creating user
4. Display in append mode for inline editing

**Implementation**:
```php
$employeeholidays = R::dispense('employeeholidays');
$employeeholidays->holiday = null;         
$employeeholidays->holiday_date = date("Y-m-d");  
$employeeholidays->userid = $_SESSION['userid'];
$employeeholidays->today = $today;
$employeeholidays->del = 0;
R::store($employeeholidays);
```

---

### 3. **autosave()** - Real-time Auto-save
**Location**: Line 62  
**Purpose**: Save holiday data automatically as user types (AJAX)

**Function Signature**:
```php
// Triggered when: do=autosave
$id = filter_input(INPUT_POST, 'id');
$name = filter_input(INPUT_POST, 'name');  // Field name
$val = filter_input(INPUT_POST, 'val');   // Field value
```

**Process Flow**:
1. Receive AJAX request with field data
2. Update specific field in database
3. No response needed (silent save)

**Implementation**:
```php
R::exec("update employeeholidays set $name = '" . $val . "' where id = $id");
```

**Features**:
- **Real-time Saving**: No manual save button required
- **Field-level Updates**: Only updates changed fields
- **Silent Operation**: No UI interruption
- **Immediate Persistence**: Changes saved instantly

---

### 4. **showajax()** - Holiday Listing with DataTables
**Location**: Line 93  
**Purpose**: Provide AJAX data feed for holiday listing with search, pagination, and filtering

**Function Signature**:
```php
function showajax() {
    global $allcutsup;  // DataTables configuration
}
```

**Process Flow**:
1. **Parameter Extraction**: Get DataTables parameters (search, pagination, ordering)
2. **Query Building**: Build dynamic SQL with filters
3. **Data Processing**: Process records for display
4. **JSON Response**: Return DataTables-compatible JSON

**Search Implementation**:
```php
if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
    $searchQuery .= "and ( employeeholidays.id LIKE '%" . $_POST["search"]["value"] . "%'
                OR employeeholidays.holiday LIKE '%" . $_POST["search"]["value"] . "%'
                OR employeeholidays.holiday_date LIKE '%" . $_POST["search"]["value"] . "%'
                OR employeeholidays.userid LIKE '%" . $_POST["search"]["value"] . "%'
                OR employeeholidays.today LIKE '%" . $_POST["search"]["value"] . "%'
    )";
}
```

**Filtering Options**:
- `fromdate` / `todate` - Date range filtering
- `data1` (id) - Specific holiday ID
- `data2` (userid) - User-based filtering
- Global search across all fields

**Response Format**:
```php
$output = array(
    "recordsTotal" => $counts,
    "recordsFiltered" => count($rResult),
    "data" => array()
);
```

---

### 5. **holidays()** - Holiday Search API
**Location**: Line 175  
**Purpose**: Provide autocomplete search for holiday selection in other modules

**Function Signature**:
```php
function holidays() {
    $name = $_POST['searchTerm'];  // Search query
}
```

**Process Flow**:
1. Receive search term from AJAX request
2. Query holidays with LIKE match
3. Format results for Select2 dropdown
4. Return JSON response

**Implementation**:
```php
$productsData = R::getAll("SELECT employeeholidays.id, holiday as name
FROM employeeholidays WHERE holiday LIKE '%" . $name . "%' limit 50");

foreach ($productsData as $pro) {
    $row_array['id'] = $pro['id'];
    $row_array['text'] = $pro['name'];
    array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
```

**Features**:
- **Fast Search**: LIKE query with limit for performance
- **Select2 Compatible**: Returns id/text format
- **Autocomplete**: Enables typeahead functionality
- **Integration Ready**: Used by other controllers for holiday selection

---

### 6. **edit()** - Holiday Edit Interface
**Location**: Line 75  
**Purpose**: Load specific holiday for editing

**Process Flow**:
1. Get holiday ID from URL parameter
2. Load holiday record using RedBean
3. Assign to template for editing
4. Display edit form

---

### 7. **del()** - Soft Delete Holiday
**Location**: Line 83  
**Purpose**: Mark holiday as deleted without removing from database

**Process Flow**:
1. Get holiday ID from POST data
2. Load holiday record
3. Set del flag to 1
4. Save record
5. Return success indicator

**Implementation**:
```php
$id = filter_input(INPUT_POST, 'id');
$employeeholidays = R::load('employeeholidays',$id);
$employeeholidays->del = 1;
R::store($employeeholidays);
echo "1";
```

---

## 🔄 Workflows

### Workflow 1: Holiday Creation Process
```
┌─────────────────────────────────────────────────────────────┐
│                START: Access Holiday Management            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Check for Working Record                               │
│     - Query for existing empty holiday record              │
│     - If none exists, create new empty record              │
│     - If exists, update with current user/date             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Display Holiday Form                                   │
│     - Show holiday name field                              │
│     - Display date picker pre-filled with current date     │
│     - Enable auto-save functionality                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Real-time Auto-save                                    │
│     - User types holiday name                              │
│     - AJAX auto-save triggers on field change              │
│     - Save individual fields without page refresh          │
│     - User selects/changes date                            │
│     - Auto-save date change immediately                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Complete Holiday Creation                              │
│     - Holiday automatically saved as user works            │
│     - No explicit save button needed                       │
│     - Record marked as complete when both fields filled    │
│     - New empty record auto-created for next holiday       │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Holiday Management and Search
```
┌─────────────────────────────────────────────────────────────┐
│              START: Holiday List Management                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Display Holiday DataTable                              │
│     - Load AJAX-powered holiday listing                    │
│     - Show search, pagination, and sorting                 │
│     - Display edit and delete actions                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Search and Filter Operations                           │
│     - Global search across all fields                      │
│     - Date range filtering                                  │
│     - User-specific filtering                              │
│     - Real-time AJAX updates                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Holiday Actions                                        │
│     EDIT Action:                                           │
│       ├─→ Load holiday for editing                        │
│       ├─→ Display edit form                               │
│       └─→ Use auto-save for updates                       │
│     DELETE Action:                                         │
│       ├─→ Soft delete holiday (set del = 1)               │
│       ├─→ Remove from active listings                     │
│       └─→ Preserve for audit trail                        │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display holiday management interface |
| `do=addappend` | Additional entry | Create new empty holiday for bulk entry |
| `do=autosave` | `autosave()` | AJAX auto-save field updates |
| `do=show` | List view | Display holiday management list |
| `do=showajax` | `showajax()` | AJAX data feed for DataTables |
| `do=holidays` | `holidays()` | Holiday search API for autocomplete |
| `do=edit` | Edit form | Display holiday edit form |
| `do=del` | `del()` | Soft delete holiday |

### Required Parameters by Action

**Auto-save** (`do=autosave`):
- `id` - Holiday record ID
- `name` - Field name to update
- `val` - New field value

**Holiday Search** (`do=holidays`):
- `searchTerm` - Search query string (POST)

**DataTables AJAX** (`do=showajax`):
- Standard DataTables parameters:
  - `search[value]` - Global search term
  - `order[0][column]` - Sort column index
  - `order[0][dir]` - Sort direction (asc/desc)
  - `start` - Pagination start
  - `length` - Page size
- Custom filters:
  - `fromdate` - Start date filter
  - `todate` - End date filter
  - `data1` - Holiday ID filter
  - `data2` - User ID filter

**Edit** (`do=edit`):
- `id` - Holiday ID to edit

**Delete** (`do=del`):
- `id` - Holiday ID to delete (POST)

---

## 🧮 Calculation Methods

### Date Formatting and Validation
```php
$today = date("Y-m-d H:i:s");  // Full timestamp for audit
$holiday_date = date("Y-m-d");  // Date only for holiday
```

### Auto-save Field Updates
```php
// Dynamic field update
R::exec("update employeeholidays set $name = '" . $val . "' where id = $id");
```

### Search Query Building
```php
$searchQuery = " ";
if ($id != '') {
    $searchQuery .= " and employeeholidays.id = " . $id . " ";
}
if ($userid != '') {
    $searchQuery .= " and employeeholidays.userid = " . $userid . " ";
}
if ($fromdate != '' && $todate != '') {
    $searchQuery .='and employeeholidays.today >= "' . $fromdate . ' 00-00-00" 
                     and employeeholidays.today <= "' . $todate . ' 23-59-55" ';
}
```

---

## 🔒 Security & Permissions

### Input Validation
- **Filter Input**: Uses `filter_input()` for all user input
- **SQL Injection**: RedBean ORM provides parameterized queries
- **XSS Prevention**: Template system handles output escaping

### Authentication
- **Session Required**: User must be logged in for all operations
- **User Tracking**: All records include userid for audit

### Data Integrity
- **Soft Delete**: Preserves data for audit trail
- **Timestamp Audit**: Records creation and modification times
- **User Attribution**: Tracks which user created/modified records

---

## 📊 Performance Considerations

### Database Optimization
1. **Efficient Queries**: 
   - Simple LIKE queries for search
   - Indexed fields for common searches
   - Limited result sets (LIMIT 50 for autocomplete)

2. **RedBean ORM**:
   - Automatic query optimization
   - Built-in caching for repeated queries
   - Lazy loading for better performance

### AJAX Optimization
1. **Auto-save Throttling**: Consider implementing debouncing for frequent updates
2. **Search Limits**: Autocomplete limited to 50 results
3. **DataTables Pagination**: Server-side processing for large datasets

### Known Performance Issues
- **No Debouncing**: Auto-save triggers on every keystroke
- **Unbounded Search**: Global search may be slow on large datasets
- **No Caching**: Holiday search results not cached

---

## 🐛 Common Issues & Troubleshooting

### 1. **Auto-save Not Working**
**Issue**: Changes not saved automatically  
**Causes**:
- JavaScript not loaded
- AJAX request failing
- Field name/ID mismatch

**Debug**:
```javascript
// Check browser console for AJAX errors
console.log('Auto-save request:', {id: id, name: name, val: val});
```

### 2. **DataTables Not Loading**
**Issue**: Holiday list shows empty or error  
**Causes**:
- AJAX endpoint not accessible
- JSON format errors
- Database query failure

**Debug**:
```php
// Add to showajax() function
error_log("DataTables query: " . $searchQuery);
error_log("Result count: " . count($rResult));
```

### 3. **Holiday Search Empty Results**
**Issue**: Autocomplete returns no holidays  
**Causes**:
- All holidays marked as deleted (del = 1)
- Search term too specific
- Database connection issues

**Fix**:
```sql
-- Check for active holidays
SELECT COUNT(*) FROM employeeholidays WHERE del = 0 AND holiday != '';

-- Check for partial matches
SELECT * FROM employeeholidays WHERE holiday LIKE '%search%' AND del = 0;
```

### 4. **Empty Records Created**
**Issue**: Multiple empty holiday records in database  
**Cause**: Auto-initialization creating duplicates

**Prevention**:
```php
// Improved check for existing empty record
$existingEmpty = R::findOne('employeeholidays', 
    'holiday IS NULL OR holiday = "" AND del = 0');
```

---

## 🧪 Testing Scenarios

### Test Case 1: Holiday Creation Workflow
```
1. Access holiday management (empty do parameter)
2. Verify auto-creation of empty record
3. Type holiday name and verify auto-save
4. Select date and verify auto-save
5. Check database for completed holiday record
6. Verify new empty record auto-created
```

### Test Case 2: Auto-save Functionality
```
1. Create holiday with partial information
2. Test auto-save on holiday name field
3. Test auto-save on date field
4. Verify immediate database updates
5. Test with special characters and dates
6. Check for race conditions with rapid typing
```

### Test Case 3: Holiday Search and Selection
```
1. Create multiple test holidays
2. Test autocomplete search with partial names
3. Verify Select2 dropdown functionality
4. Test with special characters and Unicode
5. Verify search result limits (50 max)
6. Test empty search handling
```

### Test Case 4: DataTables Integration
```
1. Create holidays with various dates and users
2. Test global search across all fields
3. Test date range filtering
4. Test user-specific filtering
5. Verify pagination with large datasets
6. Test sorting by different columns
```

### Test Case 5: Soft Delete and Recovery
```
1. Create test holidays
2. Soft delete holidays via del action
3. Verify holidays removed from listings
4. Check holidays still exist in database
5. Test recovery by updating del flag
6. Verify audit trail preservation
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [employeeAttendance.md](employeeAttendance.md) - Attendance tracking
- [employeeAttendanceSystems.md](employeeAttendanceSystems.md) - System configuration
- [EmployeeAttendanceExcelController.md](EmployeeAttendanceExcelController.md) - Bulk processing

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur