# Reports Dashboard - Complete Documentation

## Overview
The Reports Dashboard provides a comprehensive interface for generating, managing, and scheduling various reports related to attendance, leave, and employee data.

---

## Features Implemented

✅ **Dynamic Statistics** - Real-time dashboard statistics
✅ **Recent Reports History** - View recently generated reports
✅ **Attendance Reports** - Summary, detailed, and daily reports
✅ **Leave Reports** - Leave balance and usage reports
✅ **Scheduled Reports** - Automated report generation (requires table setup)
✅ **Export Options** - PDF, Excel, CSV formats
✅ **API Endpoint** - `/api/reports/dashboard-stats` for real-time updates
✅ **Auto-Refresh** - Statistics refresh every 5 minutes
✅ **User-Friendly UI** - Modern card-based interface

---

## Dashboard Statistics

The Reports Dashboard displays 4 key metrics:

### 1. Today's Attendance
- **Calculation:** (Present Employees / Total Active Employees) × 100
- **Color Coding:**
  - Green: ≥ 80% (Good attendance)
  - Yellow: 60-79% (Moderate attendance)
  - Red: < 60% (Low attendance)
- **Updates:** Real-time when page loads

### 2. Pending Leaves
- **Count:** Number of leave requests with status = "pending"
- **Color Coding:**
  - Yellow: > 10 pending requests (Needs attention)
  - Muted: ≤ 10 pending requests (Normal)
- **Action:** Click to navigate to leave management

### 3. Reports This Month
- **Count:** Number of reports generated in current month
- **Source:** Audit logs with action = "generate" and module = "reports"
- **Period:** Current calendar month

### 4. Scheduled Reports
- **Count:** Number of active scheduled reports
- **Status:** Only counts reports with is_active = 1
- **Management:** Click to manage scheduled reports

---

## Recent Reports Section

Shows the 5 most recently generated reports with:

### Information Displayed
- **Report Type:** Extracted from audit log
- **Format:** PDF, Excel, or CSV
- **Generated By:** User who generated the report
- **Timestamp:** Date and time of generation

### Actions Available
- **Refresh Button:** Reload recent reports
- **Re-generate:** Quickly generate the same report again
- **Empty State:** Shows helpful message when no reports exist

---

## Report Types Available

### 1. Attendance Reports
**Location:** Attendance Reports Card

**Report Types:**
- **Summary:** Overview of attendance statistics
- **Detailed:** Comprehensive attendance data with all fields
- **Daily:** Day-by-day attendance breakdown

**Filters Available:**
- Date range (From - To)
- Department
- Location
- Specific employee
- Employment status

**Formats:**
- PDF (with charts and formatted layout)
- Excel (raw data, can be analyzed)
- CSV (universal data format)

### 2. Leave Reports
**Location:** Leave Reports Card

**Report Types:**
- **Leave Balance:** Current leave balances for all employees
- **Leave Usage:** Historical leave usage data
- **Leave Summary:** Overview by department/location

**Filters Available:**
- Date range
- Department
- Location
- Leave type
- Leave status

**Formats:**
- PDF
- Excel
- CSV

### 3. Scheduled Reports
**Location:** Scheduled Reports Card
**Permission Required:** `schedule-reports`

**Features:**
- Create automated report schedules
- Set frequency (daily, weekly, monthly)
- Configure recipients
- Choose format
- Enable/disable schedules
- View execution history

### 4. Custom Reports
**Status:** Coming Soon
**Location:** Custom Reports Card

**Planned Features:**
- Build custom queries
- Select specific columns
- Advanced filtering
- Save report templates
- Share with team members

---

## Installation

### Step 1: Create Database Table

**Option 1: Run SQL Script (PHP 7.4)**
```bash
mysql -u username -p database < REPORTS_DASHBOARD_SETUP.sql
```

**Option 2: Run Migration (PHP 8.0+)**
```bash
php spark migrate
```

**Option 3: Manual SQL**
Execute the SQL from [REPORTS_DASHBOARD_SETUP.sql](REPORTS_DASHBOARD_SETUP.sql) in phpMyAdmin.

### Step 2: Verify Installation

1. Log in as administrator
2. Go to **Reports > Dashboard**
3. Verify statistics are showing real numbers
4. Check that "Scheduled Reports" shows count = 0

---

## API Endpoint

### Get Dashboard Statistics

**Endpoint:** `GET /api/reports/dashboard-stats`

**Response:**
```json
{
  "status": "success",
  "data": {
    "attendance_rate": 85.5,
    "pending_leaves": 12,
    "reports_this_month": 24,
    "scheduled_reports": 5,
    "last_updated": "2026-01-19 14:30:00"
  }
}
```

**Error Response:**
```json
{
  "status": "error",
  "message": "Failed to fetch dashboard statistics"
}
```

**Usage Example:**
```javascript
fetch('/api/reports/dashboard-stats')
  .then(response => response.json())
  .then(data => {
    console.log('Attendance Rate:', data.data.attendance_rate);
    console.log('Pending Leaves:', data.data.pending_leaves);
  });
```

---

## Files Modified/Created

### Created Files:

1. **[app/Database/Migrations/2026-01-19-120000_CreateScheduledReportsTable.php](app/Database/Migrations/2026-01-19-120000_CreateScheduledReportsTable.php)**
   - Migration for scheduled_reports table
   - Includes all necessary fields and indexes

2. **[REPORTS_DASHBOARD_SETUP.sql](REPORTS_DASHBOARD_SETUP.sql)**
   - Manual SQL installation script
   - Creates scheduled_reports table

3. **[REPORTS_DASHBOARD.md](REPORTS_DASHBOARD.md)**
   - This documentation file

### Modified Files:

1. **[app/Controllers/Reports/ReportController.php](app/Controllers/Reports/ReportController.php)**
   - Enhanced `index()` method with dynamic statistics
   - Added `getDashboardStats()` API method
   - Fetches real-time data from database

2. **[app/Views/reports/index.php](app/Views/reports/index.php)**
   - Updated statistics to display dynamic data
   - Enhanced recent reports section
   - Added refresh functionality
   - Added empty state handling
   - Added JavaScript for auto-refresh

3. **[app/Config/Routes.php](app/Config/Routes.php)**
   - Added API route: `GET /api/reports/dashboard-stats`

---

## Dashboard Workflow

### 1. Page Load
```
User visits /reports
    ↓
Controller fetches statistics from database
    ↓
Controller fetches recent reports from audit_logs
    ↓
Data passed to view
    ↓
Dashboard rendered with real data
```

### 2. Statistics Calculation

**Attendance Rate:**
```sql
Total Active Employees = COUNT(employees WHERE status = 'active')
Present Today = COUNT(attendance WHERE date = TODAY AND status != 'absent')
Attendance Rate = (Present Today / Total Active) × 100
```

**Pending Leaves:**
```sql
Pending Leaves = COUNT(leave_requests WHERE status = 'pending')
```

**Reports This Month:**
```sql
Reports This Month = COUNT(audit_logs
    WHERE action = 'generate'
    AND module = 'reports'
    AND MONTH(created_at) = CURRENT_MONTH)
```

**Scheduled Reports:**
```sql
Scheduled Reports = COUNT(scheduled_reports WHERE is_active = 1)
```

### 3. Recent Reports Logic

```sql
SELECT * FROM audit_logs
WHERE action = 'generate' AND module = 'reports'
ORDER BY created_at DESC
LIMIT 5
```

Reports are displayed with:
- Report name from `new_data` JSON field
- Format icon (PDF/Excel/CSV)
- Generator name from joined users table
- Formatted timestamp

---

## Customization Options

### 1. Change Statistics Refresh Interval

Edit `app/Views/reports/index.php`:

```javascript
// Default: 5 minutes (300000 ms)
setInterval(() => {
    console.log('Auto-refreshing dashboard statistics...');
}, 300000); // Change this value

// Examples:
// 1 minute:  60000
// 10 minutes: 600000
// 30 minutes: 1800000
```

### 2. Change Recent Reports Limit

Edit `app/Controllers/Reports/ReportController.php`:

```php
// Change from 5 to any number
->limit(5)  // Change to 10, 20, etc.
```

### 3. Add Custom Statistics

Add to `index()` method in `ReportController.php`:

```php
// Example: Count employees by department
$employeesByDept = $db->table('employees')
    ->select('department_id, COUNT(*) as count')
    ->groupBy('department_id')
    ->get()
    ->getResultArray();

$data['employeesByDept'] = $employeesByDept;
```

Then display in `reports/index.php`.

### 4. Customize Color Thresholds

Edit `app/Views/reports/index.php`:

```php
// Current: Green ≥80%, Yellow 60-79%, Red <60%
<?= $statistics['attendance_rate'] >= 80 ? 'text-success' :
    ($statistics['attendance_rate'] >= 60 ? 'text-warning' : 'text-danger') ?>

// Change to: Green ≥90%, Yellow 70-89%, Red <70%
<?= $statistics['attendance_rate'] >= 90 ? 'text-success' :
    ($statistics['attendance_rate'] >= 70 ? 'text-warning' : 'text-danger') ?>
```

---

## Troubleshooting

### Issue: Statistics Showing 0

**Possible Causes:**
- No employees in database
- No attendance records
- Incorrect database queries

**Solutions:**
1. Check if employees table has active employees
2. Verify attendance table has records for today
3. Check error logs: `writable/logs/log-YYYY-MM-DD.log`
4. Test database connection

### Issue: Recent Reports Not Showing

**Possible Causes:**
- No reports have been generated yet
- Audit logs not being created
- Database query error

**Solutions:**
1. Generate a test report from Attendance Reports
2. Check if audit_logs table exists
3. Verify audit logging is enabled
4. Check error logs

### Issue: Scheduled Reports Count is 0

**Possible Causes:**
- Table doesn't exist yet
- No scheduled reports created
- Migration not run

**Solutions:**
1. Run the SQL script: `REPORTS_DASHBOARD_SETUP.sql`
2. Or run migration: `php spark migrate`
3. Verify table exists: `SHOW TABLES LIKE 'scheduled_reports';`

### Issue: API Endpoint Returns Error

**Possible Causes:**
- Database connection issue
- Permission problem
- Route not configured

**Solutions:**
1. Check Routes.php has the API route
2. Test endpoint in Postman
3. Check error logs
4. Verify database connection

---

## Best Practices

### 1. Regular Monitoring
✅ Check dashboard daily for attendance trends
✅ Review pending leaves weekly
✅ Monitor report generation frequency
✅ Keep scheduled reports up to date

### 2. Performance Optimization
✅ Add database indexes on frequently queried columns
✅ Consider caching statistics for 5-10 minutes
✅ Archive old audit logs periodically
✅ Optimize report queries

### 3. Data Quality
✅ Ensure attendance is recorded daily
✅ Process leave requests promptly
✅ Keep employee records updated
✅ Validate report outputs regularly

### 4. User Experience
✅ Train users on report generation
✅ Create report templates for common needs
✅ Document report meanings and calculations
✅ Provide export format guidelines

---

## Future Enhancements

Potential improvements:

1. **Advanced Visualizations**
   - Charts and graphs on dashboard
   - Trend analysis over time
   - Comparative analytics

2. **Custom Reports Builder**
   - Drag-and-drop interface
   - Query builder
   - Save report templates
   - Share with team

3. **Report Scheduling**
   - Full scheduling interface
   - Email delivery
   - Recurring reports
   - Conditional triggers

4. **Export Enhancements**
   - PDF with company branding
   - Excel with formulas and charts
   - CSV with custom delimiters
   - Direct email delivery

5. **Real-time Updates**
   - WebSocket integration
   - Live dashboard updates
   - Push notifications
   - Real-time alerts

6. **Mobile Optimization**
   - Responsive dashboard
   - Mobile-friendly reports
   - Touch-optimized interface
   - Offline report viewing

---

## Support & Resources

### Documentation
- [Attendance Reports](reports/attendance)
- [Leave Reports](reports/leave)
- [Scheduled Reports](reports/scheduled)

### Logs
- Application Logs: `writable/logs/log-YYYY-MM-DD.log`
- Audit Logs: Settings > Audit Logs

### Database Tables
- `attendance` - Attendance records
- `leave_requests` - Leave requests
- `audit_logs` - Report generation history
- `scheduled_reports` - Automated reports

---

## Summary

The Reports Dashboard is now complete with:

✅ **Dynamic Statistics** - Real-time data from database
✅ **Recent Reports** - Last 5 reports with details
✅ **API Endpoint** - For external integrations
✅ **Auto-Refresh** - Every 5 minutes
✅ **Responsive Design** - Works on all devices
✅ **User-Friendly** - Intuitive interface
✅ **Extensible** - Easy to customize and extend

The dashboard provides instant insights into attendance, leave, and reporting activities, helping administrators make informed decisions quickly! 🎉
