124 lines
5.4 KiB
Python
124 lines
5.4 KiB
Python
|
"""
|
|||
|
|
Модуль Сводка - агрегированная гистограмма активности
|
|||
|
|
"""
|
|||
|
|
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)}")
|
|||
|
|
|