124 lines
5.4 KiB
Python
Executable File
124 lines
5.4 KiB
Python
Executable File
"""
|
||
Модуль Сводка - агрегированная гистограмма активности
|
||
"""
|
||
from fastapi import APIRouter, Depends, Query, HTTPException
|
||
from sqlalchemy.orm import Session
|
||
from sqlalchemy import text
|
||
from datetime import datetime
|
||
from typing import List
|
||
from app.core.database import get_manictime_db, get_service_db
|
||
from app.core.security import get_current_user
|
||
from app.models.service_db import AppUser, AppConfiguration
|
||
from app.schemas.summary import SummaryHistogramResponse, SummaryDataset
|
||
|
||
router = APIRouter()
|
||
|
||
|
||
|
||
|
||
@router.get("/histogram", response_model=SummaryHistogramResponse)
|
||
async def get_summary_histogram(
|
||
start_date: str = Query(..., description="Начальная дата (YYYY-MM-DD)"),
|
||
end_date: str = Query(..., description="Конечная дата (YYYY-MM-DD)"),
|
||
current_user: AppUser = Depends(get_current_user),
|
||
manictime_db: Session = Depends(get_manictime_db)
|
||
):
|
||
"""
|
||
Получение данных для гистограммы активности по дням
|
||
"""
|
||
try:
|
||
# Валидация дат
|
||
start_dt = datetime.strptime(start_date, "%Y-%m-%d")
|
||
end_dt = datetime.strptime(end_date, "%Y-%m-%d")
|
||
|
||
if start_dt > end_dt:
|
||
raise HTTPException(status_code=400, detail="Начальная дата должна быть раньше конечной")
|
||
|
||
# SQL запрос для агрегации данных
|
||
query = text("""
|
||
WITH computer_usage AS (
|
||
SELECT
|
||
a."StartLocalTime",
|
||
a."EndLocalTime",
|
||
a."Name",
|
||
t."OwnerId",
|
||
DATE_TRUNC('day', a."StartLocalTime") AS "day"
|
||
FROM "Ar_Activity" a
|
||
JOIN "Ar_Timeline" t ON a."ReportId" = t."ReportId"
|
||
WHERE t."SchemaName" = 'ManicTime/Computer usage'
|
||
AND a."StartLocalTime" >= :start_date
|
||
AND a."EndLocalTime" <= :end_date
|
||
),
|
||
productive_time AS (
|
||
SELECT
|
||
a."StartLocalTime",
|
||
a."EndLocalTime",
|
||
t."OwnerId",
|
||
DATE_TRUNC('day', a."StartLocalTime") AS "day"
|
||
FROM "Ar_Activity" a
|
||
JOIN "Ar_Timeline" t ON a."ReportId" = t."ReportId"
|
||
JOIN "Ar_CommonGroup" cg ON a."CommonGroupId" = cg."CommonId"
|
||
JOIN "Ar_CategoryGroup" cag ON cg."CommonId" = cag."CommonGroupId"
|
||
JOIN "Ar_Category" c ON cag."CategoryId" = c."CategoryId"
|
||
WHERE c."Name" = 'Productive'
|
||
AND a."StartLocalTime" >= :start_date
|
||
AND a."EndLocalTime" <= :end_date
|
||
)
|
||
SELECT
|
||
cu."day",
|
||
SUM(CASE WHEN cu."Name" = 'Active'
|
||
THEN EXTRACT(EPOCH FROM (cu."EndLocalTime" - cu."StartLocalTime"))
|
||
ELSE 0 END) AS active_seconds,
|
||
SUM(CASE WHEN cu."Name" = 'Away'
|
||
THEN EXTRACT(EPOCH FROM (cu."EndLocalTime" - cu."StartLocalTime"))
|
||
ELSE 0 END) AS away_seconds,
|
||
SUM(CASE WHEN cu."Name" IN ('Session Locked', 'Power Off')
|
||
THEN EXTRACT(EPOCH FROM (cu."EndLocalTime" - cu."StartLocalTime"))
|
||
ELSE 0 END) AS afk_seconds,
|
||
COALESCE(SUM(EXTRACT(EPOCH FROM (pt."EndLocalTime" - pt."StartLocalTime"))), 0) AS productive_seconds
|
||
FROM computer_usage cu
|
||
LEFT JOIN productive_time pt ON cu."day" = pt."day"
|
||
AND cu."OwnerId" = pt."OwnerId"
|
||
AND pt."StartLocalTime" < cu."EndLocalTime"
|
||
AND pt."EndLocalTime" > cu."StartLocalTime"
|
||
GROUP BY cu."day"
|
||
ORDER BY cu."day"
|
||
""")
|
||
|
||
result = manictime_db.execute(
|
||
query,
|
||
{"start_date": start_date, "end_date": end_date}
|
||
)
|
||
|
||
rows = result.fetchall()
|
||
|
||
# Формирование ответа
|
||
labels = []
|
||
active_data = []
|
||
away_data = []
|
||
afk_data = []
|
||
productive_data = []
|
||
|
||
for row in rows:
|
||
day = row.day.strftime("%Y-%m-%d")
|
||
labels.append(day)
|
||
active_data.append(float(row.active_seconds or 0))
|
||
away_data.append(float(row.away_seconds or 0))
|
||
afk_data.append(float(row.afk_seconds or 0))
|
||
productive_data.append(float(row.productive_seconds or 0))
|
||
|
||
datasets = [
|
||
SummaryDataset(label="Активный", color="green", data=active_data),
|
||
SummaryDataset(label="Неактивный", color="red", data=away_data),
|
||
SummaryDataset(label="Не у ПК", color="yellow", data=afk_data),
|
||
SummaryDataset(label="Продуктивность", color="orange", data=productive_data),
|
||
]
|
||
|
||
return SummaryHistogramResponse(labels=labels, datasets=datasets)
|
||
|
||
except ValueError as e:
|
||
raise HTTPException(status_code=400, detail=f"Неверный формат даты: {str(e)}")
|
||
except Exception as e:
|
||
raise HTTPException(status_code=500, detail=f"Ошибка при получении данных: {str(e)}")
|
||
|