automated reports

# Automated Timesheet Reports (Payroll & Analytics)

## Overview

The system provides **Smart-Audit Payroll Exports** with two types of reporting:

1. **Scheduled Reports**: Automatically sent on a regular schedule (weekly, bi-weekly, 4-weekly, monthly)
2. **On-Demand Reports**: Instantly email search results from the Payroll & Analytics page

Both report types include employee names, user IDs, clock in/out times, and hours worked in Excel and CSV formats.

**✓ Compatible with:** Xero, Sage, QuickBooks, and other payroll systems

## Report Types

### 1. Scheduled Reports (Automated)

Configured in Settings, sent automatically based on schedule.

### 2. On-Demand Reports (Manual)

Generated instantly from Payroll & Analytics page (`/<company>/search`):
- Search for specific employees or date ranges
- Review results in browser
- Click "Email Payroll Export (Excel & CSV)"
- Choose default HR email or custom email
- Report sent immediately with pre-validated data

## Report Formats

Each report is sent as **two attachments** for maximum compatibility:
1. **Excel file (.xlsx)** - Formatted spreadsheet
2. **CSV file (.csv)** - Plain text comma-separated values

### Data Format (Payroll-Ready)

Reports include the following columns in payroll-compatible format:

| Column | Format | Example | Notes |
|--------|--------|---------|-------|
| First Name | Text | John | Employee first name |
| Last Name | Text | Smith | Employee last name |
| User ID | Text | JS1234 | Unique employee identifier |
| Date | YYYY-MM-DD | 2026-01-17 | ISO standard date format |
| Clock In | HH:MM | 09:00 | 24-hour time format |
| Clock Out | HH:MM | 17:30 | 24-hour time format |
| Hours Worked | Decimal | 8.50 | Numeric hours (e.g., 8.5 = 8 hours 30 minutes) |

**Key Features:**
- Hours are converted to decimal format for direct payroll import
- Dates use ISO standard (YYYY-MM-DD) for universal compatibility
- All exports are pre-validated for overlaps and missing clock-outs

## Configuration

### Admin Settings

1. **Navigate to** `/<company>/settings`
2. **Scroll to** "Automated Timesheet Reports" section
3. **Configure**:
   - **Report Email**: Email address to receive reports
   - **Report Frequency**: How often to send reports
   - **Day/Date**: When to send (depends on frequency)
4. **Click** "Save All Settings"

### Frequency Options

#### Weekly
- Reports sent every week on chosen day
- **Choose**: Monday, Tuesday, Wednesday, Thursday, or Friday
- **Covers**: Last 7 days of data

#### Bi-Weekly (Every 2 Weeks)
- Reports sent every 2 weeks on chosen day
- **Choose**: Monday, Tuesday, Wednesday, Thursday, or Friday
- **Covers**: Last 14 days of data

#### 4-Weekly (Every 4 Weeks)
- Reports sent every 4 weeks on chosen day
- **Choose**: Monday, Tuesday, Wednesday, Thursday, or Friday
- **Covers**: Last 28 days of data

#### Monthly (Specific Date)
- Reports sent on specific day of each month
- **Choose**: Day 1-30 of the month
- **Covers**: Last 30 days of data

## Report Contents

### Columns Included:
1. **First Name** - Employee first name
2. **Last Name** - Employee last name
3. **User ID** - Employee ID (e.g., JD1456)
4. **Date** - Work date (DD-MM-YYYY)
5. **Clock In** - Login time (HH:MM:SS)
6. **Clock Out** - Logout time (HH:MM:SS)
7. **Hours Worked** - Total hours and minutes (e.g., "8 hours 30 minutes")

### Data Sorting
- Records sorted by login time (newest first)
- All employees included in single report
- Only completed sessions included (with logout time)

## Email Configuration

### SMTP Setup

Add to your `.env` file:

```env
# SMTP Email Configuration
SMTP_SERVER=mail.privateemail.com
SMTP_PORT=465
SMTP_USER=your_email@domain.com
SMTP_PASSWORD=your_password
FROM_EMAIL=your_email@domain.com
```

### Port Information

**Port 465 (SSL/TLS - Currently Supported):**
- Implicit SSL/TLS encryption from connection start
- Uses `smtplib.SMTP_SSL()` in Python
- More secure and reliable for most providers
- **Recommended for production use**

**Port 587 (STARTTLS - Not Currently Supported):**
- Explicit STARTTLS encryption upgrade
- Uses `smtplib.SMTP()` + `starttls()` in Python
- Common for Gmail, Outlook, Yahoo
- Not implemented in current version

### Supported Email Providers

**PrivateEmail (Current Setup):**
```env
SMTP_SERVER=mail.privateemail.com
SMTP_PORT=465
```

**Other SSL/TLS Providers (Port 465):**
- Most business email hosting services
- Custom domain email providers
- Enterprise email solutions

**Note**: If you need port 587 support (Gmail, Outlook, Yahoo), the application would need to be updated to support STARTTLS connections.

## Scheduler Setup

### Manual Report Generation

Send report immediately for testing:

```bash
python report_scheduler.py send_report <company_slug>
```

**Example:**
```bash
python report_scheduler.py send_report acmecorp
```

### On-Demand Reports from Admin Search

Send custom reports based on search criteria:

```bash
python report_scheduler.py send_custom_report <company_slug> <email> <start_date> <end_date> <first_name> <last_name>
```

**Examples:**
```bash
# Send all logs for date range
python report_scheduler.py send_custom_report acmecorp hr@company.com 2025-12-01 2025-12-07 "" ""

# Send logs for specific employee
python report_scheduler.py send_custom_report acmecorp manager@company.com 2025-12-01 2025-12-07 John Doe

# Send logs for all Johns
python report_scheduler.py send_custom_report acmecorp hr@company.com "" "" John ""
```

**Note**: In the UI, this is done automatically when HR clicks "Send Report via Email" after searching.

### Automated Scheduling

#### Option 1: Cron Job (Linux/Mac)

Edit crontab:
```bash
crontab -e
```

Add daily check at 8 AM:
```cron
0 8 * * * cd /path/to/akotoverify && ./venv/bin/python report_scheduler.py check_reports
```

#### Option 2: Task Scheduler (Windows)

1. Open Task Scheduler
2. Create Basic Task
3. Set trigger: Daily at 8:00 AM
4. Action: Start a program
5. Program: `C:\path\to\venv\Scripts\python.exe`
6. Arguments: `report_scheduler.py check_reports`
7. Start in: `C:\path\to\akotoverify`

#### Option 3: Systemd Timer (Linux)

Create `/etc/systemd/system/akoto-reports.service`:
```ini
[Unit]
Description=Akoto Verify Report Checker

[Service]
Type=oneshot
WorkingDirectory=/path/to/akotoverify
ExecStart=/path/to/akotoverify/venv/bin/python report_scheduler.py check_reports
```

Create `/etc/systemd/system/akoto-reports.timer`:
```ini
[Unit]
Description=Run Akoto Reports Daily

[Timer]
OnCalendar=daily
OnCalendar=08:00
Persistent=true

[Install]
WantedBy=timers.target
```

Enable:
```bash
sudo systemctl enable akoto-reports.timer
sudo systemctl start akoto-reports.timer
```

## Example Workflow

### Setup
1. **HR configures settings**:
   - Email: `hr@acmecorp.com`
   - Frequency: Weekly
   - Day: Monday
2. **System saves configuration**

### Automated Execution
1. **Scheduler runs daily** (via cron/task scheduler)
2. **Checks if today matches** report schedule
3. **If Monday**: Generates report for last 7 days
4. **Sends email** with Excel + CSV attachments
5. **HR receives report** at 8 AM every Monday

## Email Sample

**Subject:** Timesheet Report - Acme Corp (01-12-2025 to 07-12-2025)

**Body:**
```
Timesheet Report for Acme Corp

Report Period: 01-12-2025 to 07-12-2025
Total Records: 45

Please find attached the timesheet report in Excel and CSV formats.

This is an automated report from Akoto Verify.
```

**Attachments:**
- `timesheet_Acme Corp_01-12-2025_to_07-12-2025.xlsx`
- `timesheet_Acme Corp_01-12-2025_to_07-12-2025.csv`

## Troubleshooting

### Reports Not Sending

**Check SMTP credentials:**
```bash
# Test SMTP connection
python -c "import smtplib; s=smtplib.SMTP('smtp.gmail.com', 587); s.starttls(); print('Connection OK')"
```

**Check company settings:**
```bash
python face_verify_db.py get_report_settings <company_slug>
```

**Check scheduler logs:**
```bash
python report_scheduler.py check_reports
```

### Gmail "Less Secure Apps" Error

- Use App Password (not regular password)
- Enable 2-Factor Authentication first
- Generate App Password in Google Account settings

### No Data in Report

- Verify employees have clocked in/out during report period
- Check date range matches frequency setting
- Ensure logout times are recorded (not still logged in)

## Database Schema

**Companies table additions:**
```sql
report_email VARCHAR(255)        -- Email address for reports
report_frequency VARCHAR(20)     -- weekly, biweekly, fourweekly, monthly
report_day VARCHAR(10)           -- monday, tuesday, etc.
report_date INT                  -- 1-28 for monthly reports
```

## Security Considerations

1. **SMTP credentials** stored in `.env` (not in database)
2. **Email addresses** validated before saving
3. **Reports only sent** to configured email (no user input)
4. **Attachments generated** server-side (no client access)
5. **Temp files cleaned up** after sending

## Future Enhancements

1. Multiple email recipients
2. Custom date ranges
3. Report templates/branding
4. PDF format option
5. Summary statistics in email
6. Failed delivery notifications
7. Report history/archive