search

# Payroll & Analytics System

## Overview

The Payroll & Analytics system allows HR/Admin to query employee clock-in/clock-out records and export payroll-ready reports. Results display employee name, date, timestamps, and hours worked in formats compatible with Xero, Sage, and QuickBooks.

## Access

**Endpoint**: `/<companyslug>/search`

**Example**: `/acmecorp/search`

**Access from**: Admin panel → "Payroll & Analytics" link (after HR authentication)

## Search Options

### By Name
- **First Name Only**: Search all employees with matching first name
- **Last Name Only**: Search all employees with matching last name
- **Full Name**: Search by both first and last name
- **Case Insensitive**: Searches ignore capitalization

### By Date Range
- **From Date to Present**: Enter start date only, leave end date empty
- **Date Range**: Enter both start and end dates
- **Specific Date**: Enter same date for both start and end
- **All Records**: Leave all fields empty

### Combined Search
Mix name and date filters:
- "John" + date range = All Johns within date range
- Last name "Doe" + start date = All Does from that date forward

## Search Results

Results display in a table with:
- **Employee Name**: First and last name
- **Date**: Login date (DD-MM-YYYY format for display)
- **Clock In**: Login time (HH:MM:SS)
- **Clock Out**: Logout time or "Still logged in"
- **Hours Worked**: Calculated duration (e.g., "8 hours 30 minutes")

Results are sorted by login time (newest first).

## Universal Payroll Export

After searching, export results in payroll-ready format:

### Export Features
- **Pre-validated**: Checks for overlaps and missing clock-outs
- **Compatible with**: Xero, Sage, QuickBooks, and other payroll systems
- **Dual Format**: Both Excel (.xlsx) and CSV (.csv) sent via email
- **Instant Delivery**: Reports sent immediately to specified email

### Export Format

Reports are formatted for direct import into payroll systems:

| Column | Export Format | Example |
|--------|---------------|---------|
| First Name | Text | John |
| Last Name | Text | Smith |
| User ID | Text | JS1234 |
| Date | YYYY-MM-DD | 2026-01-17 |
| Clock In | HH:MM | 09:00 |
| Clock Out | HH:MM | 17:30 |
| Hours Worked | Decimal | 8.50 |

**Note**: Hours are automatically converted from "8 hours 30 minutes" to decimal format (8.50) for payroll compatibility.

## Database Schema

### Employees Table: `<company>_employees`
```sql
user_id VARCHAR(10) PRIMARY KEY
first_name VARCHAR(100) NOT NULL
last_name VARCHAR(100) NOT NULL
image_data BYTEA NOT NULL
face_encoding BYTEA NOT NULL
```

### Logs Table: `<company>_employees_logs`
```sql
id SERIAL PRIMARY KEY
user_id VARCHAR(10)
first_name VARCHAR(100)
last_name VARCHAR(100)
login_time TIMESTAMP
logout_time TIMESTAMP
hours_worked VARCHAR(50)
```

## Workflow

1. **HR logs into admin panel** (`/<company>/admin`)
2. **Clicks "Search Employee Logs"** link
3. **Views inactive employees** (if tracking enabled):
   - Yellow warning box at top
   - Shows employees who haven't clocked in for X days
   - Displays: Name, User ID, Last Activity, Days Inactive
4. **Views late arrivals today** (if tracking enabled):
   - Orange warning box below inactive section
   - Shows employees who clocked in late today
   - Displays: Name, User ID, Clock-In Time, Reason
5. **Enters search criteria** (below warning sections):
   - First name: "John"
   - Start date: "2025-12-01"
   - End date: "2025-12-07"
6. **Clicks "Search"**
7. **Views results** showing all Johns who logged in during that week
8. **Optional: Email Report**
   - Clicks "Send Report via Email"
   - Chooses default HR email or enters custom email
   - Report sent with Excel and CSV attachments

## Inactive Employee Tracking

### Overview

If enabled in settings, the search page displays a warning box showing employees who haven't clocked in for a configurable number of days.

### Configuration

**Location**: `/<company>/settings` → Inactive Employee Tracking

**Settings:**
- **Tracking**: Enabled / Disabled (default: disabled)
- **Threshold**: 3, 5, 7, 14, or 30 days (default: 7)

### Display

**When Enabled:**
- Yellow warning box at top of search page
- Shows count of inactive employees
- Table with: Employee Name, User ID, Last Activity, Days Inactive
- Sorted by most inactive first

**When Disabled:**
- No inactive employee section shown
- Regular search functionality only

### Use Cases

- Monitor attendance patterns
- Follow up with absent employees
- Identify long-term absences
- Verify new hires are onboarding

See `INACTIVE_TRACKING.md` for detailed documentation.

## Late Arrival Tracking

### Overview

If enabled in settings, the search page displays an orange warning box showing employees who clocked in late today.

### Configuration

**Location**: `/<company>/settings` → Late Arrival Tracking

**Settings:**
- **Tracking**: Enabled (default) / Disabled
- **Scheduled Start Time**: Configurable per company (default: 9:00 AM)
- **Late Threshold (Grace Period)**: 0, 5, 10, 15, 20, 30 minutes (default: 0)

### How It Works

**Late Detection Formula:**
```
is_late = clock_in_time > (scheduled_start_time + late_threshold_minutes)
```

**Examples:**
- Scheduled: 9:00 AM, Threshold: 0 min → Late if after 9:00 AM
- Scheduled: 9:00 AM, Threshold: 10 min → Late if after 9:10 AM
- Scheduled: 9:00 AM, Threshold: 30 min → Late if after 9:30 AM

### Display

**When Enabled:**
- Orange warning box at top of search page
- Shows count of late arrivals today
- Table with: Employee Name, User ID, Clock-In Time, Reason
- Sorted by most recent first

**When Disabled:**
- No late arrivals section shown
- Employees not prompted for late reason

### Use Cases

- Monitor punctuality patterns
- Review reasons for late arrivals
- Identify systemic issues (e.g., traffic, transit)
- Track individual attendance
- Document late arrivals for performance reviews
- **Flexible grace periods** for different workplace cultures

See `LATE_CLOCKIN_TRACKING.md`, `LATE_CLOCKIN_PHASE2.md`, and `LATE_CLOCKIN_PHASE3.md` for detailed documentation.

## Email Report Feature

### How It Works

After viewing search results, HR can instantly email the report:

1. **Review Results**: Search results displayed in table
2. **Click "Send Report via Email"**: Button appears below results
3. **Choose Email**:
   - **Use default HR email**: Pre-configured in settings
   - **Use custom email**: Enter any email address
4. **Send**: Report generated and emailed immediately

### Report Contents

- Same data as search results
- **Excel file**: Formatted spreadsheet (.xlsx)
- **CSV file**: Plain text format (.csv)
- Includes: Employee name, user ID, date, clock in/out times, hours worked

### Email Configuration

Requires SMTP settings in `.env` file:
```env
SMTP_SERVER=mail.privateemail.com
SMTP_PORT=465
SMTP_USER=your_email@domain.com
SMTP_PASSWORD=your_password
FROM_EMAIL=your_email@domain.com
```

See `AUTOMATED_REPORTS.md` for detailed SMTP setup instructions.

## Technical Implementation

**Backend**: Go handler at `dynamicSearchHandler()`
- Accepts form data (first_name, last_name, start_date, end_date)
- Calls Python script with parameters
- Parses results and renders template

**Python**: `search_logs()` function in `face_verify_db.py`
- Builds dynamic SQL query based on provided parameters
- Queries `<company>_employees_logs` table
- Returns formatted results (pipe-delimited)

**Frontend**: `search_logs.html` template
- Search form with name and date inputs
- Results table with employee records
- Back link to admin panel

## Command Line Usage

```bash
python face_verify_db.py search_logs <company_slug> <first_name> <last_name> <start_date> <end_date>
```

**Examples**:
```bash
# Search by first name only
python face_verify_db.py search_logs acmecorp John "" "" ""

# Search by date range
python face_verify_db.py search_logs acmecorp "" "" 2025-12-01 2025-12-07

# Search by name and date
python face_verify_db.py search_logs acmecorp John Doe 2025-12-01 ""

# Get all logs
python face_verify_db.py search_logs acmecorp "" "" "" ""
```

## Migration

Existing databases require migration to add name columns:

```bash
python migrate_add_names.py
```

This adds `first_name` and `last_name` to both employees and logs tables for all companies.

**Note**: Existing employees must be re-uploaded or manually updated with names in the database.