Employee Workstation Activity Tracking System

A system for logging user activity (login events, active/inactive periods) on Windows workstations and reporting these events to a central Flask server. The server provides a web dashboard for viewing daily, weekly, and monthly aggregated working time summaries.

Installation Instructions

Client-Side Setup (Windows Workstations)

Prerequisites:

• Windows 10 or Windows 11
• PowerShell 3.0 or higher
• Administrative privileges (for setting up the scheduled task)

Steps:

1. Copy Script: Transfer the report.ps1 script to a suitable location on the client workstation (e.g., C:\Scripts\UserActivityTracker\).
   • The report.ps1 script and schedule_task.ps1 can be found in the client_tools/ directory of the project.
2. Configure:
   • Create a config.env file in the same directory as report.ps1 with the following content:

     API_ENDPOINT="http://your-server-address:5050/api/report"
     IDLE_THRESHOLD_MINUTES="10"
     POLL_INTERVAL_SECONDS="60"
     REPORT_INTERVAL_MINUTES="1"
     

     Replace http://your-server-address:5050/api/report with the actual URL of your server's reporting endpoint.
   • Alternatively, these values can be set as system-wide environment variables on the workstation.
3. Schedule Task:
   • Copy the schedule_task.ps1 script (from the client_tools/ directory) to the workstation.
   • Open PowerShell as an Administrator.
   • Navigate to the directory where you saved schedule_task.ps1.
   • Run the script, providing the path to report.ps1:

     .\schedule_task.ps1 -ScriptPath "C:\Scripts\UserActivityTracker\report.ps1"
     

   This will create a scheduled task that runs report.ps1 when any user logs on.

Server-Side Setup

Prerequisites:

• Python 3.9 or higher
• pip (Python package installer)
• A database system: SQLite (for development/testing) or PostgreSQL (recommended for production).
• Git (for cloning the repository)

Steps:

1. Clone Repository:

   git clone <repository_url>  # Replace <repository_url> with the actual URL
   cd employee-workstation-activity-tracking # Or your project directory name
   

2. Create Virtual Environment:

   python -m venv venv
   

3. Activate Virtual Environment:
   • On Windows:

     .\venv\Scripts\activate
     

   • On macOS/Linux:

     source venv/bin/activate
     

4. Install Dependencies:

   pip install -r requirements.txt
   

5. Configure Application:
   • Create a config.env file in the project root directory (e.g., alongside run.py).
   • Add the following necessary configurations:

     # Flask Secret Key (change this to a random string for production)
     SECRET_KEY="your_very_secret_flask_key"
     
     # Database URI
     # For SQLite (creates a file in the 'instance' folder, good for development):
     DATABASE_URI="sqlite:///../instance/work_events.db"
     # For PostgreSQL (replace with your actual connection details):
     # DATABASE_URI="postgresql://username:password@host:port/database_name"
     
     # Optional: Define HOST, PORT, DEBUG for Flask development server
     # HOST="0.0.0.0"
     # PORT="5050"
     # DEBUG="True"
     

     Important:
     • Replace your_very_secret_flask_key with a strong, random string for production.
     • Choose and configure either SQLite or PostgreSQL for DATABASE_URI.
     • The instance folder will be created automatically by the application if it doesn't exist.
6. Initialize Database:
   • Ensure your config.env is correctly set up with the DATABASE_URI.
   • The application is designed to create the database and all necessary tables (including work_events and user_real_work_summary) if they don't exist when it first runs. For explicit control, use the Flask CLI command:

     flask init-db
     

     This command should be run from the project root directory with the virtual environment activated. It ensures all tables defined in app/models.py are created in the database.
   • Alternatively, if you need to set up or inspect the database schema manually (e.g., for PostgreSQL), SQL script files are provided in the database_utils/ directory:
     • database_utils/create_db.sql (original schema, may need review if used directly against current models)
     • database_utils/001_create_user_real_work_summary.sql (for the real work hours summary table) Example for PostgreSQL:

     psql -U your_pg_user -d your_pg_database -f database_utils/your_script_file.sql
     

     It's generally recommended to use flask init-db for consistency with the application's models.

Usage Examples

Running the Server

Ensure your virtual environment is activated before running the server.

Use the start_app.sh script located in the project root:

./start_app.sh [mode]

• [mode] is optional.
  • If omitted or dev or development, the server starts in development mode (using python run.py).
  • If prod or production, the server starts in production mode (using Gunicorn).

Example for Development:

./start_app.sh dev
# or simply
./start_app.sh

Example for Production:

./start_app.sh prod

The application will typically start on http://0.0.0.0:5050/ or http://127.0.0.1:5050/ by default. Check the terminal output for the exact address. The script will attempt to activate the venv virtual environment.

Accessing the Dashboard

Once the server is running, open a web browser and navigate to the server's address (e.g., http://your-server-ip:5050/ or http://localhost:5050/ if running locally).

The dashboard provides views for daily, weekly, and monthly user activity summaries. It now displays "Real Work Hours," which are calculated based on continuous 40-minute blocks of 'working' activity reported by the client.

Client-Side Activity Reporting

The report.ps1 script, once configured and scheduled on a Windows workstation, runs automatically at user logon. It monitors user idle time based on the IDLE_THRESHOLD_MINUTES setting (default 10 minutes) and polls at intervals defined by POLL_INTERVAL_SECONDS (default 60 seconds).

When a user's state changes (e.g., from active to idle/stopped, or idle to active/working), the script sends an HTTP POST request to the server's /api/report endpoint.

Example API Payload Sent by Client:

{
  "user": "domain\\username",
  "state": "working",
  "ts": "2024-05-10T10:20:30Z" 
}

• user: The Windows username (often including domain).
• state: Either "working" or "stopped".
• ts: An optional ISO 8601 UTC timestamp. If not provided by the client, the server records the event with its current UTC time.

Manual API Interaction (for testing)

You can also send POST requests to the /api/report endpoint using tools like curl or Postman for testing purposes.

Example using curl for raw event reporting:

curl -X POST -H "Content-Type: application/json" \
-d '{"user":"testuser","state":"working","ts":"2024-01-01T12:00:00Z"}' \
http://localhost:5050/api/report

Successful requests will receive a JSON response like {"success": true} with an HTTP 201 status code.

New API Endpoint for Real Work Hours Data:

A new endpoint /api/reports/real_work_hours is available to fetch the calculated "Real Work Hours".

• Method: GET
• Query Parameters:
  • username (string, optional): Filter results by a specific username.
  • start_date (string, optional): Start date for the report period (YYYY-MM-DD).
  • end_date (string, optional): End date for the report period (YYYY-MM-DD). If start_date is provided and end_date is omitted, end_date defaults to start_date.
• Example curl:

  curl -X GET "http://localhost:5050/api/reports/real_work_hours?username=testuser&start_date=2024-01-01&end_date=2024-01-31"
  

• Example Response:

  {
      "success": true,
      "data": [
          {
              "id": 1,
              "username": "testuser",
              "work_date": "2024-01-15",
              "real_hours_counted": 3,
              "last_processed_event_id": 12345
          },
          // ... more records
      ]
  }
  

Project Structure

The project is organized as follows:

work-tracing/
├── app/                        # Main Flask application package
│   ├── __init__.py             # Application factory (create_app), blueprint registration
│   ├── api/                    # REST API Blueprints and modules
│   │   ├── __init__.py
│   │   ├── events.py           # Endpoints for event reporting
│   │   └── reports.py          # Endpoints for data retrieval
│   ├── models.py               # SQLAlchemy database models (includes WorkEvent, UserRealWorkSummary)
│   ├── utils/                  # Utility modules (query building, formatting)
│   │   ├── __init__.py
│   │   ├── formatting.py
│   │   └── queries.py
│   ├── views/                  # Web page view Blueprints
│   │   ├── __init__.py
│   │   └── dashboard.py
│   ├── errors.py               # Custom error handlers registration
│   ├── scheduler.py            # APScheduler setup and job definitions
│   ├── services/               # Service layer for business logic (e.g., work_hours_service.py)
│   └── cli.py                  # Custom Flask CLI commands (e.g., process-real-hours)
├── client_tools/               # Scripts and utilities for client-side setup
│   ├── report.ps1              # PowerShell script for client activity reporting
│   ├── schedule_task.ps1       # PowerShell script to schedule client agent
│   └── run_hidden.vbs          # VBScript to run PowerShell script hidden (optional)
├── database_utils/             # Database-related utility scripts
│   ├── create_db.sql           # Manual SQL schema creation script (reference for work_events)
│   └── 001_create_user_real_work_summary.sql # Manual SQL for user_real_work_summary table
├── instance/                   # Instance-specific data (SQLite DB, logs), .gitignored
├── static/                     # Static files (CSS, JavaScript)
│   ├── css/
│   │   └── dashboard.css
│   └── js/                     # JavaScript modules for the dashboard
│       ├── dashboard.js
│       └── ... (other .js files)
├── templates/                  # HTML templates
│   └── dashboard.html
├── venv/                       # Python virtual environment
├── .cursorignore
├── .flake8
├── .gitignore
├── config.env                  # Server-side environment configuration. IMPORTANT: .gitignore this if it contains secrets.
├── ecosystem.config.js         # PM2 configuration (if used for deployment)
├── README.md                   # This file
├── requirements.txt            # Python dependencies
├── run.py                      # Application entry point (used by start_app.sh for dev mode)
└── start_app.sh                # Primary script to start the application (dev/prod)

Time Synchronization

The application maintains consistent time handling across all components:

1. Client-side (PowerShell):

   • All timestamps are generated in UTC using (Get-Date).ToUniversalTime().ToString("o")
   • Timestamps are sent to the server in ISO 8601 format with 'Z' suffix indicating UTC

2. Backend (Flask):

   • All incoming timestamps are converted to UTC if they have timezone information
   • Backend code uses datetime.utcnow() for server-generated timestamps
   • All time-based calculations are performed in UTC

3. Database (PostgreSQL/SQLite):

   • For PostgreSQL, the database session timezone is set to 'Asia/Dubai' (UTC+4)
   • Timestamps are automatically converted from UTC to Asia/Dubai when stored
   • When retrieved, timestamps reflect the Asia/Dubai timezone

4. Frontend (JavaScript):

   • All displayed times use GMT+4 (Asia/Dubai) via the formatTimeToGMT4() function
   • Date selection for filtering is in the user's local timezone
   • Auto-refresh interval (60 seconds) aligns with client reporting frequency

This approach ensures that all timestamps are consistently handled throughout the application, preventing time drift or synchronization issues between components.

Dependencies and Requirements

Client-Side Requirements (Windows Workstations)

• Operating System: Windows 10 or Windows 11
• PowerShell: Version 3.0 or higher
• Permissions: Administrative privileges are required to set up the scheduled task for the client script (report.ps1).

Server-Side Requirements

• Python: Version 3.9 or higher.
• Database:
  • SQLite 3 for development or small-scale deployments.
  • PostgreSQL (version 12+ recommended) for production or larger deployments.
• Networking: Clients must be able to reach the server over HTTP on the configured port (default 5050) within the local area network (LAN).
• Python Packages: The specific Python dependencies are listed in requirements.txt. Key packages include:
  • Flask (web framework)
  • Flask-SQLAlchemy (SQLAlchemy integration for Flask)
  • SQLAlchemy (SQL toolkit and Object Relational Mapper)
  • Gunicorn (WSGI HTTP server for production on Unix-like systems)
  • psycopg2-binary (PostgreSQL adapter for Python, if using PostgreSQL)
  • python-dotenv (for managing environment variables from .env files)
  • requests (for making HTTP requests, used internally by the server)
  • APScheduler (for scheduling background tasks, like calculating real work hours)

Full requirements.txt:

Flask==2.3.3
Flask-SQLAlchemy==3.1.1
SQLAlchemy==2.0.20
gunicorn==21.2.0
python-dotenv==1.0.0
psycopg2-binary==2.9.7
pytest==7.4.0
black==23.7.0
flake8==6.1.0
isort==5.12.0
alembic==1.12.0
Werkzeug==2.3.7
Jinja2==3.1.2
itsdangerous==2.1.2
click==8.1.7
requests
APScheduler==3.10.4

Installation of these server-side dependencies is typically done by running pip install -r requirements.txt within an activated virtual environment.

Automated Tasks and Manual Operations

Real Work Hours Calculation

The system includes a new table, user_real_work_summary, which stores "Real Work Hours." A "Real Work Hour" is defined as a continuous block of 40 minutes where the user's status is reported as 'working' by the client-side script.

• Automated Processing:
  • An automated background task, managed by APScheduler within the Flask application, runs periodically (e.g., every 15 minutes).
  • This task (calculate_and_store_real_work_hours located in app/services/work_hours_service.py) processes new work_events from the work_events table.
  • It identifies 40-minute 'working' blocks and updates the real_hours_counted and last_processed_event_id in the user_real_work_summary table for each user and date.
• Manual Trigger / Backfilling:
  • A Flask CLI command is available for manual operations:

    flask process-real-hours
    

  • This command executes the same calculate_and_store_real_work_hours function.
  • It is useful for initial data backfilling if there's a large history of work_events before this feature was added, for testing the calculation logic, or for ad-hoc processing if the scheduler was temporarily inactive.

Contributing Guidelines

We welcome contributions to improve the Employee Workstation Activity Tracking System! To ensure a smooth process, please follow these guidelines.

Getting Started

1. Fork the repository on GitHub (or your Git hosting platform).
2. Clone your fork locally: git clone <your-fork-url>
3. Create a feature branch for your changes: git checkout -b feat/your-feature-name or fix/your-bug-fix.

Coding Standards

Consistency helps maintain code quality and readability.

• Python:
  • Adhere to PEP 8 style guidelines.
  • Use Black for code formatting. It's recommended to configure your editor to format on save or run it before committing.
  • Use isort for organizing imports.
  • Use Flake8 for linting to catch common errors and style issues. Configuration for Flake8 can be found in the .flake8 file in the project root (e.g., max-line-length = 88 to align with Black).
  • Before committing Python code, please run these tools from the project root directory:

    # Ensure your virtual environment (e.g., venv) is activated
    black .
    isort .
    flake8 .
    

    Address any critical issues reported by Flake8.
• PowerShell:
  • Use Verb-Noun function and cmdlet names (e.g., Get-UserIdleTime, Send-ActivityReport).
  • Maintain consistent two-space indentation.
• JavaScript:
  • Use modern ES6+ syntax.
  • Write modular code, leveraging ES6 modules.
  • Avoid polluting the global scope.

Commit Messages

Please follow the Conventional Commits specification for your commit messages. This helps in generating automated changelogs and makes the project history more understandable.

Examples:

• feat: add detailed user activity log endpoint
• fix: correct calculation for weekly report aggregation
• docs: update installation instructions for PostgreSQL
• style: apply Black formatting to all Python files
• refactor: improve exception handling in API modules
• test: add unit tests for date utility functions
• chore: update Gunicorn version in requirements.txt

Testing

• If you add new features, please include tests where applicable. This project uses pytest for Python testing.
• Ensure all existing tests pass before submitting a pull request.

  # To run tests (ensure pytest is installed and virtual env is active):
  pytest
  

Pull Requests

1. Once your changes are complete and tested, commit them with a clear Conventional Commit message.
2. Push your feature branch to your fork: git push origin feat/your-feature-name.
3. Open a Pull Request (PR) to the main branch of the original repository.
4. In your PR description, clearly explain the changes you've made, why they were made, and reference any related issues (e.g., "Closes #123").
5. Be prepared to discuss your changes and make adjustments if requested during the review process.

Thank you for contributing!

License

This project is licensed under the MIT License. A copy of the license should ideally be included as a LICENSE file in the project's root directory.

If a LICENSE file is not present, the general terms of the MIT License can be found at: https://opensource.org/licenses/MIT