Spaces:
Paused
Paused
File size: 2,968 Bytes
04477e7 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
os.chdir(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from app import create_app
from models import EmployeeKpiValue, KpiDefinition, User, EmployeeMapping, KpiValue
from extensions import db
from sqlalchemy import text
app = create_app()
with app.app_context():
# Check table structure
result = db.session.execute(text('PRAGMA table_info(employee_kpi_values)'))
print("=== employee_kpi_values columns ===")
for row in result:
print(f" {row}")
print()
# Check the other kpi tables too
for tname in ['kpi_definition', 'kpi_value']:
try:
result = db.session.execute(text(f'PRAGMA table_info({tname})'))
print(f"=== {tname} columns ===")
for row in result:
print(f" {row}")
except:
pass
print()
# Check current week data overview - use correct column names
result = db.session.execute(text("""
SELECT ekv.category, ekv.metric_name, COUNT(*) as cnt,
ROUND(AVG(CAST(ekv.fact_value AS REAL)),4) as avg_fact,
ROUND(AVG(CAST(ekv.plan_value AS REAL)),4) as avg_plan
FROM employee_kpi_values ekv
WHERE ekv.week_start = '2026-07-06'
GROUP BY ekv.category, ekv.metric_name
ORDER BY ekv.category, ekv.metric_name
"""))
for row in result:
print(f" {row.category:15s} | {row.metric_name:45s} | cnt={row.cnt} | fact={row.avg_fact} | plan={row.avg_plan}")
print()
print("=== Users with data this week ===")
result = db.session.execute(text("""
SELECT DISTINCT u.username, u.full_name, u.id
FROM employee_kpi_values ekv
JOIN employee_mapping em ON em.user_id = ekv.user_id
JOIN user u ON u.id = em.user_id
WHERE ekv.week_start = '2026-07-06'
ORDER BY u.username
"""))
for row in result:
print(f" id={row.id} username={row.username} name={row.full_name}")
print()
print("=== All KPI definitions ===")
metrics = KpiDefinition.query.order_by(KpiDefinition.category, KpiDefinition.name).all()
for m in metrics:
print(f" id={m.id:3d} | {m.category:15s} | {m.name:45s} | unit={m.unit or ''} | db_field={m.db_field or ''} | formula={m.formula or ''}")
print("\n=== KpiValue (kpi_value table) ===")
kvs = KpiValue.query.order_by(KpiValue.id).all()
for kv in kvs[:20]:
print(f" id={kv.id} name={kv.name} category={kv.category} unit={kv.unit} order={kv.display_order}")
if len(kvs) > 20:
print(f" ... and {len(kvs) - 20} more")
print("\n=== User notes ===")
print(f"EmployeeKpiValue count: {EmployeeKpiValue.query.count()}")
print(f"KpiDefinition count: {KpiDefinition.query.count()}")
print(f"KpiValue count: {KpiValue.query.count()}")
print(f"User count: {User.query.count()}")
|