# AI-Powered Kimai to MIS Report Converter 

Convert informal Kimai2 time tracking entries into professional Management Information System (MIS) reports using Google Gemini AI.

##  Overview

This FastAPI application automatically transforms casual employee work logs from Kimai2 into polished, executive-ready documentation. It uses Google Gemini 2.5 Flash Lite to convert descriptions like "fixed bug in checkout" into professional sentences like "Resolved critical bug affecting checkout flow functionality."

##  Key Features

- ** AI-Powered Transformation**: Uses Google Gemini 2.5 Flash Lite with advanced prompt engineering
- ** Smart Deduplication**: Merges similar tasks while preserving unique details
- ** Professional Language**: Converts informal notes to action verb-based sentences
- ** Technical Preservation**: Maintains code, SQL, HTML tags, and URLs exactly as written
- ** Scalable Processing**: Handles 350+ descriptions per employee in a single API call
- ** Secure API**: API key authentication with comprehensive input validation
- ** Batch Processing**: Supports monthly, quarterly, and annual reports

##  Architecture

```
┌─────────────┐      ┌──────────────┐      ┌─────────────┐      ┌──────────────┐
│   Client    │─────▶│  FastAPI     │─────▶│   MySQL     │      │   Google     │
│ Application │      │   Endpoints  │      │   Kimai2    │      │   Gemini AI  │
└─────────────┘      └──────────────┘      └─────────────┘      └──────────────┘
                             │                                            │
                             └────────────────────────────────────────────┘
                                    AI Processing & Summarization
```

## Quick Start

### Prerequisites

- Python 3.8+
- MySQL database with Kimai2 schema
- Google Gemini API key
- FastAPI and dependencies

### Installation

1. **Clone the repository**
```bash
git clone https://github.com/yourusername/kimai-mis-converter.git
cd kimai-mis-converter
```

2. **Install dependencies**
```bash
pip install fastapi uvicorn mysql-connector-python pydantic python-jose passlib google-generativeai
```

3. **Configure environment variables**

Create a `core/config.py` file:
```python
class Settings:
    # Database Configuration
    DB_HOST = "localhost"
    DB_USER = "your_db_user"
    DB_PASSWORD = "your_db_password"
    DB_NAME = "kimai2_database"
    
    # API Keys
    SECRET_API_KEY = "your_secret_api_key_here"
    GEMINI_API_KEY = "your_google_gemini_api_key"
```

4. **Run the application**
```bash
uvicorn main:app --reload --host 0.0.0.0 --port 8000
```

The API will be available at `http://localhost:8000`

##  API Endpoints

### 1. Get Time Logs

**Endpoint:** `POST /getTimeLogs`

Retrieves and organizes raw time log data from Kimai2 database.

**Request Headers:**
```
X-API-Key: your_secret_api_key_here
Content-Type: application/json
```

**Request Body:**
```json
{
  "Email": "employee@company.com",
  "Month": 1,
  "Year": 2025
}
```

**Response:**
```json
[
  {
    "project": "E-Commerce Platform",
    "activity": [
      {
        "name": "Development",
        "descriptions": [
          "fixed bug in checkout",
          "added payment gateway",
          "updated database schema"
        ]
      },
      {
        "name": "Testing",
        "descriptions": [
          "unit testing for login",
          "regression testing"
        ]
      }
    ]
  }
]
```

**Validation Rules:**
- `Email`: Must be valid email format
- `Month`: Integer between 1-12
- `Year`: Integer between 2000-2100

---

### 2. Get Summarized Data

**Endpoint:** `POST /getSummarizedData`

Transforms raw task descriptions into professional MIS report format using AI.

**Request Headers:**
```
X-API-Key: your_secret_api_key_here
Content-Type: application/json
```

**Request Body:**
```json
{
  "request": "[{\"project\":\"E-Commerce Platform\",\"activity\":[{\"name\":\"Development\",\"descriptions\":[\"fixed bug\",\"added gateway\"]}]}]"
}
```

**Response:**
```json
{
  "response": {
    "description": "[{\"project\":\"E-Commerce Platform\",\"activity\":[{\"name\":\"Development\",\"descriptions\":[\"Resolved critical bug affecting system functionality\",\"Integrated new payment gateway to support multiple transaction methods\"]}]}]"
  }
}
```

---

##  Authentication

All endpoints require API key authentication via the `X-API-Key` header.

**Example cURL request:**
```bash
curl -X POST "http://localhost:8000/getTimeLogs" \
  -H "X-API-Key: your_secret_api_key_here" \
  -H "Content-Type: application/json" \
  -d '{
    "Email": "employee@company.com",
    "Month": 1,
    "Year": 2025
  }'
```

**Error Responses:**
- `401 Unauthorized`: Invalid or missing API key
- `404 Not Found`: No time logs found for specified criteria
- `422 Unprocessable Entity`: Invalid request parameters

---

##  AI Processing

### Transformation Examples

| Input (Kimai) | Output (MIS Report) |
|---------------|---------------------|
| "fixed bug in checkout" | "Resolved critical bug affecting checkout flow functionality" |
| "mtg w/ client" | "Attended meeting with client regarding project requirements" |
| "updated db schema" | "Modified database schema to accommodate new feature requirements" |
| "css fixes on homepage" | "Resolved CSS styling issues affecting homepage layout" |

### Prompt Engineering

The system uses a structured prompt with the following rules:

1. **Professional Language**: Convert to action verb-based sentences
2. **Deduplication**: Merge similar tasks intelligently
3. **Technical Preservation**: Maintain code, SQL, HTML, URLs exactly as written
4. **Title Standardization**: Replace "mam"/"madam" with "Ma'am"
5. **Format Consistency**: Preserve JSON structure without extra formatting
6. **Grammar Correction**: Fix errors while maintaining meaning

---

##  Usage Examples

### Example 1: Monthly Employee Report

**Step 1: Get Time Logs**
```python
import requests

url = "http://localhost:8000/getTimeLogs"
headers = {
    "X-API-Key": "your_secret_api_key_here",
    "Content-Type": "application/json"
}
payload = {
    "Email": "john.doe@company.com",
    "Month": 12,
    "Year": 2024
}

response = requests.post(url, json=payload, headers=headers)
time_logs = response.json()
print(time_logs)
```

**Step 2: Summarize Data**
```python
import json

url = "http://localhost:8000/getSummarizedData"
payload = {
    "request": json.dumps(time_logs)
}

response = requests.post(url, json=payload, headers=headers)
summary = response.json()
print(summary['response']['description'])
```

### Example 2: Batch Processing Multiple Employees

```python
import requests
import json
from concurrent.futures import ThreadPoolExecutor

def process_employee(email):
    # Get time logs
    logs_response = requests.post(
        "http://localhost:8000/getTimeLogs",
        json={"Email": email, "Month": 1, "Year": 2025},
        headers={"X-API-Key": "your_key"}
    )
    
    # Summarize
    summary_response = requests.post(
        "http://localhost:8000/getSummarizedData",
        json={"request": json.dumps(logs_response.json())},
        headers={"X-API-Key": "your_key"}
    )
    
    return {
        "email": email,
        "summary": summary_response.json()
    }

# Process 10 employees in parallel
employees = ["emp1@company.com", "emp2@company.com", ...]
with ThreadPoolExecutor(max_workers=5) as executor:
    results = list(executor.map(process_employee, employees))
```

---

##  Database Schema

### Required Kimai2 Tables

The application queries the following Kimai2 tables:

**kimai2_timesheet**
- `id`, `user`, `project_id`, `activity_id`, `description`, `start_time`

**kimai2_users**
- `id`, `username`, `email`

**kimai2_projects**
- `id`, `name`

**kimai2_activities**
- `id`, `name`

### SQL Query Logic

The system:
-  Includes entries with non-null descriptions
-  Excludes projects named "Break"
-  Excludes activities named "NO WORK"
-  Filters by month and year
-  Filters by user email
-  Groups by user, activity, project, and description

---

##  Configuration

### Settings Class

```python
class Settings:
    # Database
    DB_HOST: str = "localhost"
    DB_USER: str = "kimai_user"
    DB_PASSWORD: str = "secure_password"
    DB_NAME: str = "kimai2"
    
    # Security
    SECRET_API_KEY: str = "your-secret-key-minimum-32-characters"
    
    # AI
    GEMINI_API_KEY: str = "your-google-gemini-api-key"
```

### Environment Variables (Alternative)

```bash
export DB_HOST="localhost"
export DB_USER="kimai_user"
export DB_PASSWORD="secure_password"
export DB_NAME="kimai2"
export SECRET_API_KEY="your-secret-key"
export GEMINI_API_KEY="your-gemini-key"
```

---

## 🔧 Advanced Features

### Custom Filtering

Modify the `fetch_data()` function to add custom filters:

```python
# Exclude specific projects
AND p.name NOT IN ('Break', 'Internal', 'Training')

# Include only billable activities
AND a.billable = 1

# Filter by date range
AND t.start_time BETWEEN '2024-01-01' AND '2024-12-31'
```

### Rate Limiting (Free Tier)

If using Gemini free tier (15 RPM limit):

```python
import time

def rate_limited_summarize(data_list):
    results = []
    for i, data in enumerate(data_list):
        if i > 0 and i % 15 == 0:
            time.sleep(60)  # Wait 1 minute after every 15 requests
        results.append(summarize_data(data))
    return results
```

---

##  Troubleshooting

### Common Issues

**1. Database Connection Error**
```
Error: Can't connect to MySQL server
```
**Solution:** Verify database credentials in `core/config.py` and ensure MySQL is running.

**2. API Key Authentication Failed**
```
401 Unauthorized: Invalid or missing API Key
```
**Solution:** Check that `X-API-Key` header matches `SECRET_API_KEY` in config.

**3. Gemini API Rate Limit**
```
429 Too Many Requests
```
**Solution:** Implement rate limiting (see Advanced Features) or upgrade to paid tier.

**4. Empty Response from Summarization**
```
Response: {"response": {"description": ""}}
```
**Solution:** Check Gemini API key validity and internet connectivity.

---

## 📈 Performance

### Token Usage

- **Prompt**: ~1,500 tokens (fixed)
- **Monthly data** (350 descriptions): ~10,000 tokens
- **Total input**: ~11,500 tokens
- **Gemini capacity**: 1,000,000 tokens
- **Utilization**: ~1.15% 

### Processing Speed

| Scenario | Descriptions | Processing Time |
|----------|--------------|-----------------|
| Single employee (monthly) | 350 | 3-8 seconds |
| 10 employees (batch) | 3,500 | 30-60 seconds |
| 100 employees (parallel) | 35,000 | 5-10 minutes |

### Cost Estimation

**Gemini 2.5 Flash Lite (Free Tier):**
- Requests/day: 1,500
- Cost: $0/month
- Best for: Up to 1,500 employees/day

**Gemini 2.0 Flash (Paid):**
- Input: $0.10/1M tokens
- Output: $0.40/1M tokens
- Monthly cost (500 employees): ~$0.40
- Annual cost: ~$4.80

---

##  Development

### Project Structure

```
kimai-mis-converter/
├── main.py                 # Main application file
├── core/
│   └── config.py          # Configuration settings
├── requirements.txt        # Python dependencies
├── README.md              # This file
└── tests/
    └── test_api.py        # API tests
```

### Running Tests

```bash
pytest tests/ -v
```

### Code Style

```bash
# Format code
black main.py

# Lint
pylint main.py

# Type checking
mypy main.py
```

---

##  Contributing

Contributions are welcome! Please follow these steps:

1. Fork the repository
2. Create a feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

---

##  License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

---

##  Acknowledgments

- [FastAPI](https://fastapi.tiangolo.com/) - Modern web framework
- [Google Gemini](https://ai.google.dev/) - AI model
- [Kimai](https://www.kimai.org/) - Time tracking system
- [Pydantic](https://pydantic-docs.helpmanual.io/) - Data validation

---

##  Contact

For questions or support:
- **GitHub Issues**: [Create an issue](https://github.com/yourusername/kimai-mis-converter/issues)
- **Email**: abhi.aiml.moweb@gmail.com

---

##  Roadmap

- [ ] Web-based dashboard for report viewing
- [ ] Export to PDF/Excel formats
- [ ] Multi-language support
- [ ] Custom prompt templates
- [ ] Real-time WebSocket updates
- [ ] Integration with Slack/Teams
- [ ] Advanced analytics and insights

---

**⭐ Star this repository if you find it useful!**