427 lines
19 KiB
Markdown
427 lines
19 KiB
Markdown
# 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:
|
|
```env
|
|
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`:
|
|
```powershell
|
|
.\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:**
|
|
```bash
|
|
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:**
|
|
```bash
|
|
python -m venv venv
|
|
```
|
|
3. **Activate Virtual Environment:**
|
|
* On Windows:
|
|
```cmd
|
|
.\venv\Scripts\activate
|
|
```
|
|
* On macOS/Linux:
|
|
```bash
|
|
source venv/bin/activate
|
|
```
|
|
4. **Install Dependencies:**
|
|
```bash
|
|
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:
|
|
```env
|
|
# 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:
|
|
```bash
|
|
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:
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
./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:**
|
|
```bash
|
|
./start_app.sh dev
|
|
# or simply
|
|
./start_app.sh
|
|
```
|
|
|
|
**Example for Production:**
|
|
```bash
|
|
./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:**
|
|
|
|
```json
|
|
{
|
|
"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:**
|
|
|
|
```bash
|
|
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`:**
|
|
```bash
|
|
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:**
|
|
```json
|
|
{
|
|
"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:
|
|
|
|
```text
|
|
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:
|
|
```bash
|
|
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:**
|
|
```bash
|
|
# 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.
|
|
```bash
|
|
# 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](https://opensource.org/licenses/MIT) |