345 lines
17 KiB
Python
345 lines
17 KiB
Python
|
"""Superadmin-only Project CT archive endpoints."""
|
|||
|
|
|
|||
|
|
from __future__ import annotations
|
|||
|
|
|
|||
|
|
import hashlib
|
|||
|
|
import io
|
|||
|
|
import json
|
|||
|
|
import os
|
|||
|
|
import tempfile
|
|||
|
|
from datetime import date
|
|||
|
|
from fastapi import APIRouter, Depends, File, HTTPException, Query, UploadFile, status
|
|||
|
|
from fastapi.responses import FileResponse, Response
|
|||
|
|
from starlette.background import BackgroundTask
|
|||
|
|
from pydantic import BaseModel
|
|||
|
|
|
|||
|
|
from app.admin.vtiger_archive import ARCHIVE_MODULES, run_archive_sync, termination_readiness
|
|||
|
|
from app.admin.hub_impact import build_impact_report, save_impact_report
|
|||
|
|
from app.admin.archive_bundle import create_archive_bundle, file_sha256, import_archive_bundle
|
|||
|
|
from app.core.auth_dependencies import get_current_user
|
|||
|
|
from app.core.database import execute_query, execute_query_single
|
|||
|
|
|
|||
|
|
router = APIRouter()
|
|||
|
|
|
|||
|
|
|
|||
|
|
class ImpactReportRequest(BaseModel):
|
|||
|
|
archive_version_id: int
|
|||
|
|
vtiger_from: date
|
|||
|
|
vtiger_to: date
|
|||
|
|
hub_from: date
|
|||
|
|
hub_to: date
|
|||
|
|
|
|||
|
|
|
|||
|
|
def require_hidden_superadmin(current_user: dict = Depends(get_current_user)) -> dict:
|
|||
|
|
# This feature must not disclose its existence to ordinary users.
|
|||
|
|
if not current_user.get("is_superadmin"):
|
|||
|
|
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Not found")
|
|||
|
|
return current_user
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/vtiger-archive/status")
|
|||
|
|
async def archive_status(current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
versions = execute_query(
|
|||
|
|
"""SELECT id,sync_kind,status,started_at,completed_at,source_cutoff,module_counts,
|
|||
|
|
critical_errors,control_report,control_approved_at,raw_export_sha256
|
|||
|
|
FROM vtiger_archive_versions ORDER BY id DESC LIMIT 25"""
|
|||
|
|
) or []
|
|||
|
|
checkpoints = execute_query(
|
|||
|
|
"SELECT * FROM vtiger_archive_checkpoints ORDER BY module"
|
|||
|
|
) or []
|
|||
|
|
transfers = execute_query(
|
|||
|
|
"""SELECT id,direction,bundle_sha256,through_version_id,source_instance,status,counts,
|
|||
|
|
error_message,started_at,completed_at
|
|||
|
|
FROM vtiger_archive_transfers ORDER BY id DESC LIMIT 25"""
|
|||
|
|
) or []
|
|||
|
|
file_archive = execute_query(
|
|||
|
|
"""SELECT source_module,COUNT(*)::integer AS files,SUM(size_bytes)::bigint AS size_bytes
|
|||
|
|
FROM vtiger_archive_files GROUP BY source_module ORDER BY source_module"""
|
|||
|
|
) or []
|
|||
|
|
return {"versions": versions, "checkpoints": checkpoints, "transfers": transfers,
|
|||
|
|
"file_archive": file_archive, "readiness": termination_readiness()}
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.post("/admin/vtiger-archive/sync/{sync_kind}")
|
|||
|
|
async def archive_sync(sync_kind: str, current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
return await run_archive_sync(sync_kind, int(current_user["id"]))
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.post("/admin/vtiger-archive/versions/{version_id}/approve-control")
|
|||
|
|
async def approve_control(version_id: int, current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
version = execute_query_single(
|
|||
|
|
"SELECT * FROM vtiger_archive_versions WHERE id=%s", (version_id,),
|
|||
|
|
)
|
|||
|
|
if not version:
|
|||
|
|
raise HTTPException(status_code=404, detail="Arkivversionen findes ikke")
|
|||
|
|
if version.get("status") != "completed" or version.get("critical_errors"):
|
|||
|
|
raise HTTPException(status_code=409, detail="En fejlet eller ukomplet kontrolrapport kan ikke godkendes")
|
|||
|
|
counts = version.get("module_counts") or {}
|
|||
|
|
missing = [module for module in ARCHIVE_MODULES if module not in counts]
|
|||
|
|
if missing:
|
|||
|
|
raise HTTPException(status_code=409, detail=f"Kontrolrapport mangler moduler: {', '.join(missing)}")
|
|||
|
|
execute_query(
|
|||
|
|
"""UPDATE vtiger_archive_versions SET control_approved_at=NOW(),control_approved_by=%s
|
|||
|
|
WHERE id=%s""", (current_user["id"], version_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
return {"approved": True, "version_id": version_id, "readiness": termination_readiness()}
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/vtiger-archive/versions/{version_id}/raw-export")
|
|||
|
|
async def raw_export(
|
|||
|
|
version_id: int,
|
|||
|
|
module: str | None = Query(None),
|
|||
|
|
current_user: dict = Depends(require_hidden_superadmin),
|
|||
|
|
):
|
|||
|
|
version = execute_query_single("SELECT id FROM vtiger_archive_versions WHERE id=%s", (version_id,))
|
|||
|
|
if not version:
|
|||
|
|
raise HTTPException(status_code=404, detail="Arkivversionen findes ikke")
|
|||
|
|
params: list[object] = [version_id]
|
|||
|
|
module_filter = ""
|
|||
|
|
if module:
|
|||
|
|
module_filter = "AND module=%s"
|
|||
|
|
params.append(module)
|
|||
|
|
rows = execute_query(
|
|||
|
|
f"""SELECT DISTINCT ON (module,vtiger_id) module,vtiger_id,revision_no,
|
|||
|
|
source_created_at,source_modified_at,is_deleted,payload,payload_sha256
|
|||
|
|
FROM vtiger_archive_records WHERE version_id <= %s {module_filter}
|
|||
|
|
ORDER BY module,vtiger_id,revision_no DESC""", tuple(params),
|
|||
|
|
) or []
|
|||
|
|
relations = execute_query(
|
|||
|
|
"""SELECT source_module,source_vtiger_id,field_name,target_vtiger_id,target_module
|
|||
|
|
FROM vtiger_archive_relations WHERE version_id <= %s
|
|||
|
|
ORDER BY source_module,source_vtiger_id,field_name,target_vtiger_id""", (version_id,),
|
|||
|
|
) or []
|
|||
|
|
version_meta = execute_query_single(
|
|||
|
|
"""SELECT id,sync_kind,status,started_at,completed_at,source_cutoff,module_counts,
|
|||
|
|
warnings,critical_errors,control_report,control_approved_at
|
|||
|
|
FROM vtiger_archive_versions WHERE id=%s""", (version_id,),
|
|||
|
|
) or {}
|
|||
|
|
export_lines = [{"record_type": "archive_version", "data": dict(version_meta)}]
|
|||
|
|
export_lines.extend({"record_type": "entity", "data": dict(row)} for row in rows)
|
|||
|
|
export_lines.extend({"record_type": "relation", "data": dict(row)} for row in relations)
|
|||
|
|
body = "\n".join(json.dumps(item, ensure_ascii=False, default=str, sort_keys=True) for item in export_lines) + "\n"
|
|||
|
|
digest = hashlib.sha256(body.encode("utf-8")).hexdigest()
|
|||
|
|
if not module:
|
|||
|
|
execute_query(
|
|||
|
|
"UPDATE vtiger_archive_versions SET raw_export_sha256=%s WHERE id=%s",
|
|||
|
|
(digest, version_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
filename = f"project-ct-vtiger-v{version_id}{'-' + module if module else ''}.jsonl"
|
|||
|
|
return Response(
|
|||
|
|
content=body,
|
|||
|
|
media_type="application/x-ndjson",
|
|||
|
|
headers={"Content-Disposition": f'attachment; filename="{filename}"', "X-Content-SHA256": digest},
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/vtiger-archive/readiness")
|
|||
|
|
async def readiness(current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
return termination_readiness()
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/vtiger-archive/bundle.zip")
|
|||
|
|
async def export_archive_bundle(
|
|||
|
|
through_version_id: int | None = Query(None),
|
|||
|
|
current_user: dict = Depends(require_hidden_superadmin),
|
|||
|
|
):
|
|||
|
|
if through_version_id is None:
|
|||
|
|
latest = execute_query_single(
|
|||
|
|
"SELECT id FROM vtiger_archive_versions WHERE status='completed' ORDER BY id DESC LIMIT 1"
|
|||
|
|
)
|
|||
|
|
if not latest:
|
|||
|
|
raise HTTPException(status_code=409, detail="Der findes ingen fuldført arkivversion")
|
|||
|
|
through_version_id = int(latest["id"])
|
|||
|
|
selected = execute_query_single(
|
|||
|
|
"SELECT id,status FROM vtiger_archive_versions WHERE id=%s", (through_version_id,),
|
|||
|
|
)
|
|||
|
|
if not selected:
|
|||
|
|
raise HTTPException(status_code=404, detail="Arkivversionen findes ikke")
|
|||
|
|
if selected.get("status") != "completed":
|
|||
|
|
raise HTTPException(status_code=409, detail="Kun en fuldført arkivversion kan eksporteres til produktion")
|
|||
|
|
transfer_id = execute_query_single(
|
|||
|
|
"""INSERT INTO vtiger_archive_transfers(direction,through_version_id,status,initiated_by)
|
|||
|
|
VALUES ('export',%s,'running',%s) RETURNING id""", (through_version_id, current_user["id"]),
|
|||
|
|
)["id"]
|
|||
|
|
try:
|
|||
|
|
path, manifest = create_archive_bundle(through_version_id, int(current_user["id"]))
|
|||
|
|
counts = {name: info["count"] for name, info in manifest["entries"].items()}
|
|||
|
|
execute_query(
|
|||
|
|
"""UPDATE vtiger_archive_transfers SET status='completed',completed_at=NOW(),
|
|||
|
|
bundle_sha256=%s,source_instance=%s,counts=%s::jsonb WHERE id=%s""",
|
|||
|
|
(manifest["bundle_sha256"], manifest["source_instance"], json.dumps(counts), transfer_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
return FileResponse(
|
|||
|
|
path, media_type="application/zip",
|
|||
|
|
filename=f"project-ct-vtiger-archive-v{through_version_id}.zip",
|
|||
|
|
background=BackgroundTask(lambda: os.path.exists(path) and os.unlink(path)),
|
|||
|
|
headers={"X-Archive-SHA256": manifest["bundle_sha256"], "X-vTiger-Contacted": "false"},
|
|||
|
|
)
|
|||
|
|
except Exception as exc:
|
|||
|
|
execute_query(
|
|||
|
|
"UPDATE vtiger_archive_transfers SET status='failed',completed_at=NOW(),error_message=%s WHERE id=%s",
|
|||
|
|
(str(exc), transfer_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
raise HTTPException(status_code=500, detail=f"Arkivpakken kunne ikke oprettes: {exc}") from exc
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.post("/admin/vtiger-archive/import-bundle")
|
|||
|
|
async def import_bundle(
|
|||
|
|
file: UploadFile = File(...),
|
|||
|
|
current_user: dict = Depends(require_hidden_superadmin),
|
|||
|
|
):
|
|||
|
|
if not str(file.filename or "").lower().endswith(".zip"):
|
|||
|
|
raise HTTPException(status_code=400, detail="Vælg en Project CT .zip-arkivpakke")
|
|||
|
|
fd, path = tempfile.mkstemp(prefix="project-ct-upload-", suffix=".zip")
|
|||
|
|
os.close(fd)
|
|||
|
|
transfer_id = execute_query_single(
|
|||
|
|
"""INSERT INTO vtiger_archive_transfers(direction,status,initiated_by)
|
|||
|
|
VALUES ('import','running',%s) RETURNING id""", (current_user["id"],),
|
|||
|
|
)["id"]
|
|||
|
|
try:
|
|||
|
|
size = 0
|
|||
|
|
with open(path, "wb") as target:
|
|||
|
|
while chunk := await file.read(1024 * 1024):
|
|||
|
|
size += len(chunk)
|
|||
|
|
if size > 20 * 1024 * 1024 * 1024:
|
|||
|
|
raise ValueError("Arkivpakken må højst fylde 20 GB")
|
|||
|
|
target.write(chunk)
|
|||
|
|
uploaded_sha256 = file_sha256(path)
|
|||
|
|
previous = execute_query_single(
|
|||
|
|
"""SELECT id,through_version_id,counts FROM vtiger_archive_transfers
|
|||
|
|
WHERE direction='import' AND status='completed' AND bundle_sha256=%s
|
|||
|
|
ORDER BY id DESC LIMIT 1""", (uploaded_sha256,),
|
|||
|
|
)
|
|||
|
|
if previous:
|
|||
|
|
execute_query(
|
|||
|
|
"""UPDATE vtiger_archive_transfers SET status='completed',completed_at=NOW(),bundle_sha256=%s,
|
|||
|
|
through_version_id=%s,counts=%s::jsonb WHERE id=%s""",
|
|||
|
|
(uploaded_sha256, previous.get("through_version_id"), json.dumps(previous.get("counts") or {}), transfer_id),
|
|||
|
|
fetch=False,
|
|||
|
|
)
|
|||
|
|
return {
|
|||
|
|
"bundle_sha256": uploaded_sha256,
|
|||
|
|
"counts": previous.get("counts") or {},
|
|||
|
|
"contacted_vtiger": False,
|
|||
|
|
"already_imported": True,
|
|||
|
|
"original_transfer_id": previous["id"],
|
|||
|
|
"transfer_id": transfer_id,
|
|||
|
|
}
|
|||
|
|
result = import_archive_bundle(path, int(current_user["id"]))
|
|||
|
|
execute_query(
|
|||
|
|
"""UPDATE vtiger_archive_transfers SET status='completed',completed_at=NOW(),bundle_sha256=%s,
|
|||
|
|
source_instance=%s,through_version_id=%s,counts=%s::jsonb WHERE id=%s""",
|
|||
|
|
(result["bundle_sha256"], result["manifest"].get("source_instance"),
|
|||
|
|
result["manifest"].get("through_version_id"), json.dumps(result["counts"]), transfer_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
return {**result, "transfer_id": transfer_id}
|
|||
|
|
except ValueError as exc:
|
|||
|
|
execute_query(
|
|||
|
|
"UPDATE vtiger_archive_transfers SET status='failed',completed_at=NOW(),error_message=%s WHERE id=%s",
|
|||
|
|
(str(exc), transfer_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
raise HTTPException(status_code=400, detail=str(exc)) from exc
|
|||
|
|
except Exception as exc:
|
|||
|
|
execute_query(
|
|||
|
|
"UPDATE vtiger_archive_transfers SET status='failed',completed_at=NOW(),error_message=%s WHERE id=%s",
|
|||
|
|
(str(exc), transfer_id), fetch=False,
|
|||
|
|
)
|
|||
|
|
raise HTTPException(status_code=500, detail=f"Arkivpakken kunne ikke importeres: {exc}") from exc
|
|||
|
|
finally:
|
|||
|
|
if os.path.exists(path):
|
|||
|
|
os.unlink(path)
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/hub-impact/options")
|
|||
|
|
async def impact_options(current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
versions = execute_query(
|
|||
|
|
"""SELECT id,sync_kind,completed_at,source_cutoff,module_counts,control_report,control_approved_at
|
|||
|
|
FROM vtiger_archive_versions WHERE status='completed' ORDER BY id DESC"""
|
|||
|
|
) or []
|
|||
|
|
reports = execute_query(
|
|||
|
|
"""SELECT id,archive_version_id,vtiger_from,vtiger_to,hub_from,hub_to,result_sha256,generated_at
|
|||
|
|
FROM hub_impact_reports ORDER BY id DESC LIMIT 30"""
|
|||
|
|
) or []
|
|||
|
|
return {"versions": versions, "reports": reports, "readiness": termination_readiness()}
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.post("/admin/hub-impact/reports")
|
|||
|
|
async def create_impact_report(payload: ImpactReportRequest, current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
try:
|
|||
|
|
result = build_impact_report(payload.archive_version_id, payload.vtiger_from, payload.vtiger_to,
|
|||
|
|
payload.hub_from, payload.hub_to)
|
|||
|
|
except ValueError as exc:
|
|||
|
|
raise HTTPException(status_code=400, detail=str(exc)) from exc
|
|||
|
|
report_id = save_impact_report(result, int(current_user["id"]))
|
|||
|
|
return {"id": report_id, "result": result}
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/hub-impact/reports/{report_id}")
|
|||
|
|
async def get_impact_report(report_id: int, current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
row = execute_query_single("SELECT * FROM hub_impact_reports WHERE id=%s", (report_id,))
|
|||
|
|
if not row:
|
|||
|
|
raise HTTPException(status_code=404, detail="Rapporten findes ikke")
|
|||
|
|
return row
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _impact_workbook(report: dict) -> bytes:
|
|||
|
|
from openpyxl import Workbook
|
|||
|
|
from openpyxl.styles import Font, PatternFill
|
|||
|
|
wb = Workbook()
|
|||
|
|
overview = wb.active
|
|||
|
|
overview.title = "Overblik"
|
|||
|
|
overview.append(["Project CT · Hub Impact", "vTiger", "Hub", "Forskel", "% ændring"])
|
|||
|
|
for cell in overview[1]:
|
|||
|
|
cell.font = Font(bold=True, color="FFFFFF")
|
|||
|
|
cell.fill = PatternFill("solid", fgColor="0F4C75")
|
|||
|
|
labels = {"hours": "Timer", "time_entries": "Tidsregistreringer", "cases": "Sager", "orders": "Ordrer"}
|
|||
|
|
for key, label in labels.items():
|
|||
|
|
overview.append([label, report["vtiger"]["totals"][key], report["hub"]["totals"][key], report["comparison"][key], (report.get("improvement") or {}).get("percent", {}).get(key)])
|
|||
|
|
overview.append([])
|
|||
|
|
overview.append(["Arkivversion", report["archive_version"]["id"]])
|
|||
|
|
overview.append(["vTiger-periode", f"{report['periods']['vtiger']['from']} – {report['periods']['vtiger']['to']}"])
|
|||
|
|
overview.append(["Hub-periode", f"{report['periods']['hub']['from']} – {report['periods']['hub']['to']}"])
|
|||
|
|
overview.freeze_panes = "A2"
|
|||
|
|
overview.column_dimensions["A"].width = 28
|
|||
|
|
for col in "BCDE": overview.column_dimensions[col].width = 18
|
|||
|
|
evidence = wb.create_sheet("Effekt")
|
|||
|
|
evidence.append(["Dokumenteret ændring"])
|
|||
|
|
for line in (report.get("improvement") or {}).get("evidence", []):
|
|||
|
|
evidence.append([line])
|
|||
|
|
evidence.column_dimensions["A"].width = 90
|
|||
|
|
|
|||
|
|
employees = wb.create_sheet("Pr medarbejder")
|
|||
|
|
employees.append(["Kilde", "Medarbejder", "E-mail", "Timer", "Registreringer", "Sager", "Ordrer", "Timer/arbejdsdag"])
|
|||
|
|
for source in ("vtiger", "hub"):
|
|||
|
|
for row in report[source]["employees"]:
|
|||
|
|
employees.append([source, row["name"], row.get("email"), row["hours"], row["time_entries"], row["cases"], row["orders"], row["productivity"]["hours_per_workday"]])
|
|||
|
|
for cell in employees[1]: cell.font = Font(bold=True)
|
|||
|
|
employees.freeze_panes = "A2"
|
|||
|
|
employees.auto_filter.ref = employees.dimensions
|
|||
|
|
|
|||
|
|
anomalies = wb.create_sheet("Afvigelser")
|
|||
|
|
anomalies.append(["Kilde", "Type", "ID", "Timer"])
|
|||
|
|
for source in ("vtiger", "hub"):
|
|||
|
|
for row in report[source]["anomalies"]:
|
|||
|
|
anomalies.append([row.get("source"), row.get("type"), row.get("id"), row.get("hours")])
|
|||
|
|
quality = wb.create_sheet("Datakvalitet")
|
|||
|
|
quality.append(["Kilde", "Manglende datoer", "Ikke matchede medarbejdere"])
|
|||
|
|
for source in ("vtiger", "hub"):
|
|||
|
|
data = report[source]["data_quality"]
|
|||
|
|
quality.append([source, data["missing_dates"], ", ".join(data["unmatched_employees"])])
|
|||
|
|
output = io.BytesIO()
|
|||
|
|
wb.save(output)
|
|||
|
|
return output.getvalue()
|
|||
|
|
|
|||
|
|
|
|||
|
|
@router.get("/admin/hub-impact/reports/{report_id}/export.xlsx")
|
|||
|
|
async def export_impact_report(report_id: int, current_user: dict = Depends(require_hidden_superadmin)):
|
|||
|
|
row = execute_query_single("SELECT result FROM hub_impact_reports WHERE id=%s", (report_id,))
|
|||
|
|
if not row:
|
|||
|
|
raise HTTPException(status_code=404, detail="Rapporten findes ikke")
|
|||
|
|
return Response(
|
|||
|
|
content=_impact_workbook(row["result"]),
|
|||
|
|
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
|||
|
|
headers={"Content-Disposition": f'attachment; filename="project-ct-impact-{report_id}.xlsx"'},
|
|||
|
|
)
|