fn-registry agent
309 lines
12 KiB
Python
309 lines
12 KiB
Python
#!/usr/bin/env python3
|
||
"""Build Metabase dashboard 'Presupuestos Call Center → Facturación'.
|
||
|
||
5 dashboard filters: fecha (date/range), centro, agente CC, compañía, producto.
|
||
3 KPIs × 3 cards each = 9 scalar cards:
|
||
A — Presupuestos Call center (quote CC → invoice mismo order_id)
|
||
B — Presupuestos CallC regenerados (Q0 CC → Q1 en centro físico → invoice)
|
||
C — Total facturado (invoice en centros, excluye 159/162)
|
||
|
||
Métricas por KPI:
|
||
- Total facturado (€)
|
||
- # Facturas
|
||
- Ticket medio (€) = Total / #
|
||
"""
|
||
import json
|
||
import os
|
||
import subprocess
|
||
import sys
|
||
|
||
import httpx
|
||
|
||
API_KEY = subprocess.check_output(["pass", "show", "metabase/aurgi-api-key"], text=True).strip().splitlines()[0]
|
||
BASE = "https://reports.autingo.es"
|
||
DB_ID = 6
|
||
COLLECTION_ID = 559 # "Datos de call center"
|
||
|
||
# Field IDs (precomputados via /api/table/<id>/query_metadata)
|
||
F_INVOICE_CREATED_AT = 16235
|
||
F_CENTER_ID = 17327
|
||
F_TPVUSER_ID = 17965
|
||
F_COMPANY_ID = 17157
|
||
F_PRODUCT_ID = 16698
|
||
|
||
client = httpx.Client(base_url=BASE, headers={"x-api-key": API_KEY}, timeout=120)
|
||
|
||
|
||
# ---------- Template tags ----------
|
||
def field_filter_tag(name: str, field_id: int, widget: str, display_name: str):
|
||
return {
|
||
"id": name + "-tag",
|
||
"name": name,
|
||
"display-name": display_name,
|
||
"type": "dimension",
|
||
"dimension": ["field", field_id, None],
|
||
"widget-type": widget,
|
||
"default": None,
|
||
}
|
||
|
||
# Tag set para A y B (5 filtros)
|
||
TAGS_AB = {
|
||
"date": field_filter_tag("date", F_INVOICE_CREATED_AT, "date/range", "Fecha"),
|
||
"centro": field_filter_tag("centro", F_CENTER_ID, "id", "Centro"),
|
||
"agente": field_filter_tag("agente", F_TPVUSER_ID, "id", "Agente CC"),
|
||
"compania": field_filter_tag("compania", F_COMPANY_ID, "id", "Compañía"),
|
||
"producto": field_filter_tag("producto", F_PRODUCT_ID, "id", "Producto"),
|
||
}
|
||
|
||
# Tag set para C (4 filtros — sin agente)
|
||
TAGS_C = {k: v for k, v in TAGS_AB.items() if k != "agente"}
|
||
|
||
|
||
# ---------- SQL skeletons ----------
|
||
SQL_A = """
|
||
WITH
|
||
cc_users AS (
|
||
SELECT DISTINCT tpvuser_id AS user_id
|
||
FROM `psql_dcpublic.tpv_authorization_tpvuser_centers`
|
||
WHERE dccenter_id IN (159, 162)
|
||
),
|
||
filtered AS (
|
||
SELECT DISTINCT
|
||
`psql_dcpublic.tpv_orders_invoice`.id AS invoice_id,
|
||
`psql_dcpublic.tpv_orders_order`.total_cost AS total_cost
|
||
FROM `psql_dcpublic.tpv_orders_quote`
|
||
JOIN cc_users ON `psql_dcpublic.tpv_orders_quote`.created_by_id = cc_users.user_id
|
||
JOIN `psql_dcpublic.tpv_orders_order` ON `psql_dcpublic.tpv_orders_quote`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
JOIN `psql_dcpublic.tpv_orders_invoice` ON `psql_dcpublic.tpv_orders_invoice`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_terminals` ON `psql_dcpublic.tpv_orders_order`.terminal_id = `psql_dcpublic.tpv_terminals`.id
|
||
LEFT JOIN `psql_dcpublic.centers` ON `psql_dcpublic.tpv_terminals`.center_id = `psql_dcpublic.centers`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_authorization_tpvuser` ON `psql_dcpublic.tpv_orders_quote`.created_by_id = `psql_dcpublic.tpv_authorization_tpvuser`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_customers` ON `psql_dcpublic.tpv_orders_order`.customer_id = `psql_dcpublic.tpv_customers`.id
|
||
LEFT JOIN `psql_dcpublic.companies` ON `psql_dcpublic.tpv_customers`.company_id = `psql_dcpublic.companies`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_orders_orderitem` ON `psql_dcpublic.tpv_orders_orderitem`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
WHERE `psql_dcpublic.tpv_orders_quote`.deleted_at IS NULL
|
||
AND COALESCE(`psql_dcpublic.centers`.id, 0) NOT IN (159, 162)
|
||
[[AND {{date}}]]
|
||
[[AND {{centro}}]]
|
||
[[AND {{agente}}]]
|
||
[[AND {{compania}}]]
|
||
[[AND {{producto}}]]
|
||
)
|
||
SELECT __AGG__ AS valor FROM filtered
|
||
"""
|
||
|
||
SQL_B = """
|
||
WITH
|
||
cc_users AS (
|
||
SELECT DISTINCT tpvuser_id AS user_id
|
||
FROM `psql_dcpublic.tpv_authorization_tpvuser_centers`
|
||
WHERE dccenter_id IN (159, 162)
|
||
),
|
||
q0 AS (
|
||
SELECT q.id AS q0_id, q.order_id AS q0_order, q.created_at AS q0_ts,
|
||
q.created_by_id AS cc_agent_id,
|
||
o.customer_id, o.vehicle_id
|
||
FROM `psql_dcpublic.tpv_orders_quote` q
|
||
JOIN cc_users cc ON q.created_by_id = cc.user_id
|
||
JOIN `psql_dcpublic.tpv_orders_order` o ON q.order_id = o.id
|
||
WHERE q.deleted_at IS NULL
|
||
AND o.customer_id IS NOT NULL AND o.vehicle_id IS NOT NULL
|
||
),
|
||
qN AS (
|
||
SELECT q.id AS qn_id, q.order_id AS qn_order, q.created_at AS qn_ts,
|
||
o.customer_id, o.vehicle_id, t.center_id
|
||
FROM `psql_dcpublic.tpv_orders_quote` q
|
||
JOIN `psql_dcpublic.tpv_orders_order` o ON q.order_id = o.id
|
||
LEFT JOIN `psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id
|
||
WHERE q.deleted_at IS NULL
|
||
AND t.center_id IS NOT NULL AND t.center_id NOT IN (159, 162)
|
||
),
|
||
pairs AS (
|
||
SELECT DISTINCT q0.cc_agent_id, qN.qn_order
|
||
FROM q0
|
||
JOIN qN
|
||
ON q0.customer_id = qN.customer_id
|
||
AND q0.vehicle_id = qN.vehicle_id
|
||
AND qN.qn_ts > q0.q0_ts
|
||
AND qN.qn_ts <= TIMESTAMP_ADD(q0.q0_ts, INTERVAL 60 DAY)
|
||
AND qN.qn_order != q0.q0_order
|
||
),
|
||
filtered AS (
|
||
SELECT DISTINCT
|
||
`psql_dcpublic.tpv_orders_invoice`.id AS invoice_id,
|
||
`psql_dcpublic.tpv_orders_order`.total_cost AS total_cost
|
||
FROM pairs
|
||
JOIN `psql_dcpublic.tpv_orders_order` ON pairs.qn_order = `psql_dcpublic.tpv_orders_order`.id
|
||
JOIN `psql_dcpublic.tpv_orders_invoice` ON `psql_dcpublic.tpv_orders_invoice`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_terminals` ON `psql_dcpublic.tpv_orders_order`.terminal_id = `psql_dcpublic.tpv_terminals`.id
|
||
LEFT JOIN `psql_dcpublic.centers` ON `psql_dcpublic.tpv_terminals`.center_id = `psql_dcpublic.centers`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_authorization_tpvuser` ON pairs.cc_agent_id = `psql_dcpublic.tpv_authorization_tpvuser`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_customers` ON `psql_dcpublic.tpv_orders_order`.customer_id = `psql_dcpublic.tpv_customers`.id
|
||
LEFT JOIN `psql_dcpublic.companies` ON `psql_dcpublic.tpv_customers`.company_id = `psql_dcpublic.companies`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_orders_orderitem` ON `psql_dcpublic.tpv_orders_orderitem`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
WHERE 1=1
|
||
[[AND {{date}}]]
|
||
[[AND {{centro}}]]
|
||
[[AND {{agente}}]]
|
||
[[AND {{compania}}]]
|
||
[[AND {{producto}}]]
|
||
)
|
||
SELECT __AGG__ AS valor FROM filtered
|
||
"""
|
||
|
||
SQL_C = """
|
||
WITH filtered AS (
|
||
SELECT DISTINCT
|
||
`psql_dcpublic.tpv_orders_invoice`.id AS invoice_id,
|
||
`psql_dcpublic.tpv_orders_order`.total_cost AS total_cost
|
||
FROM `psql_dcpublic.tpv_orders_invoice`
|
||
JOIN `psql_dcpublic.tpv_orders_order` ON `psql_dcpublic.tpv_orders_invoice`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_terminals` ON `psql_dcpublic.tpv_orders_order`.terminal_id = `psql_dcpublic.tpv_terminals`.id
|
||
LEFT JOIN `psql_dcpublic.centers` ON `psql_dcpublic.tpv_terminals`.center_id = `psql_dcpublic.centers`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_customers` ON `psql_dcpublic.tpv_orders_order`.customer_id = `psql_dcpublic.tpv_customers`.id
|
||
LEFT JOIN `psql_dcpublic.companies` ON `psql_dcpublic.tpv_customers`.company_id = `psql_dcpublic.companies`.id
|
||
LEFT JOIN `psql_dcpublic.tpv_orders_orderitem` ON `psql_dcpublic.tpv_orders_orderitem`.order_id = `psql_dcpublic.tpv_orders_order`.id
|
||
WHERE COALESCE(`psql_dcpublic.centers`.id, 0) NOT IN (159, 162)
|
||
[[AND {{date}}]]
|
||
[[AND {{centro}}]]
|
||
[[AND {{compania}}]]
|
||
[[AND {{producto}}]]
|
||
)
|
||
SELECT __AGG__ AS valor FROM filtered
|
||
"""
|
||
|
||
AGG = {
|
||
"total": "ROUND(SUM(total_cost), 2)",
|
||
"count": "COUNT(*)",
|
||
"ticket": "ROUND(SAFE_DIVIDE(SUM(total_cost), NULLIF(COUNT(*), 0)), 2)",
|
||
}
|
||
|
||
|
||
def make_card(name: str, sql_skel: str, agg_key: str, tags: dict, display: str, currency: bool):
|
||
sql = sql_skel.replace("__AGG__", AGG[agg_key])
|
||
viz = {}
|
||
if currency:
|
||
viz = {
|
||
"column_settings": {
|
||
'["name","valor"]': {
|
||
"number_style": "currency",
|
||
"currency": "EUR",
|
||
"currency_style": "symbol",
|
||
"currency_in_header": False,
|
||
"decimals": 0,
|
||
}
|
||
}
|
||
}
|
||
body = {
|
||
"name": name,
|
||
"type": "question",
|
||
"display": display,
|
||
"visualization_settings": viz,
|
||
"dataset_query": {
|
||
"type": "native",
|
||
"database": DB_ID,
|
||
"native": {"query": sql, "template-tags": tags},
|
||
},
|
||
"collection_id": COLLECTION_ID,
|
||
"description": name,
|
||
"result_metadata": None,
|
||
}
|
||
r = client.post("/api/card", json=body)
|
||
if r.status_code >= 400:
|
||
print(r.status_code, r.text[:500])
|
||
r.raise_for_status()
|
||
return r.json()
|
||
|
||
|
||
# ---------- Build 9 cards ----------
|
||
specs = [
|
||
# (id, name, skeleton, agg, tags, display, currency)
|
||
("a_total", "A · Presupuestos Call center — Total facturado", SQL_A, "total", TAGS_AB, "scalar", True),
|
||
("a_count", "A · Presupuestos Call center — # Facturas", SQL_A, "count", TAGS_AB, "scalar", False),
|
||
("a_ticket", "A · Presupuestos Call center — Ticket medio", SQL_A, "ticket", TAGS_AB, "scalar", True),
|
||
|
||
("b_total", "B · Presupuestos CallC regenerados — Total facturado", SQL_B, "total", TAGS_AB, "scalar", True),
|
||
("b_count", "B · Presupuestos CallC regenerados — # Facturas", SQL_B, "count", TAGS_AB, "scalar", False),
|
||
("b_ticket", "B · Presupuestos CallC regenerados — Ticket medio", SQL_B, "ticket", TAGS_AB, "scalar", True),
|
||
|
||
("c_total", "C · Total facturado — Total facturado", SQL_C, "total", TAGS_C, "scalar", True),
|
||
("c_count", "C · Total facturado — # Facturas", SQL_C, "count", TAGS_C, "scalar", False),
|
||
("c_ticket", "C · Total facturado — Ticket medio", SQL_C, "ticket", TAGS_C, "scalar", True),
|
||
]
|
||
|
||
cards = {}
|
||
for sid, name, skel, agg, tags, display, currency in specs:
|
||
print(f"creating {sid} ... {name}")
|
||
c = make_card(name, skel, agg, tags, display, currency)
|
||
cards[sid] = c
|
||
print(f" -> id={c['id']}")
|
||
|
||
|
||
# ---------- Build dashboard ----------
|
||
print("\ncreating dashboard")
|
||
dash = client.post("/api/dashboard", json={
|
||
"name": "Call Center — Presupuestos · KPIs (90d)",
|
||
"collection_id": COLLECTION_ID,
|
||
"description": "A=Quotes CC facturados · B=CallC regenerados · C=Total facturado · Filtros: fecha/centro/agente/compañía/producto",
|
||
}).json()
|
||
DID = dash["id"]
|
||
print(f" dashboard id={DID}")
|
||
|
||
# Dashboard-level parameters
|
||
DPARAMS = [
|
||
{"id": "p_date", "name": "Fecha", "slug": "fecha", "type": "date/range", "sectionId": "date"},
|
||
{"id": "p_centro", "name": "Centro", "slug": "centro", "type": "id", "sectionId": "id"},
|
||
{"id": "p_agente", "name": "Agente CC", "slug": "agente", "type": "id", "sectionId": "id"},
|
||
{"id": "p_compania", "name": "Compañía", "slug": "compania", "type": "id", "sectionId": "id"},
|
||
{"id": "p_producto", "name": "Producto", "slug": "producto", "type": "id", "sectionId": "id"},
|
||
]
|
||
|
||
# Parameter mappings: each dashboard param → each card's template-tag target
|
||
def mapping_for_card(card_id: int, tag_keys: list[str]):
|
||
return [
|
||
{
|
||
"parameter_id": f"p_{k}",
|
||
"card_id": card_id,
|
||
"target": ["dimension", ["template-tag", k]],
|
||
}
|
||
for k in tag_keys
|
||
]
|
||
|
||
# Grid: 24 wide. Columns 0/8/16 = 8 wide. Rows 0/4/8 by KPI band.
|
||
DASHCARDS = []
|
||
layout = [
|
||
# (sid, row, col, h)
|
||
("a_total", 0, 0, 4), ("a_count", 0, 8, 4), ("a_ticket", 0, 16, 4),
|
||
("b_total", 4, 0, 4), ("b_count", 4, 8, 4), ("b_ticket", 4, 16, 4),
|
||
("c_total", 8, 0, 4), ("c_count", 8, 8, 4), ("c_ticket", 8, 16, 4),
|
||
]
|
||
|
||
neg = -1
|
||
for sid, row, col, h in layout:
|
||
card = cards[sid]
|
||
tag_keys = list((TAGS_AB if sid[0] in "ab" else TAGS_C).keys())
|
||
DASHCARDS.append({
|
||
"id": neg,
|
||
"card_id": card["id"],
|
||
"row": row,
|
||
"col": col,
|
||
"size_x": 8,
|
||
"size_y": h,
|
||
"parameter_mappings": mapping_for_card(card["id"], tag_keys),
|
||
"visualization_settings": {},
|
||
})
|
||
neg -= 1
|
||
|
||
# Update dashboard with params + dashcards
|
||
r = client.put(f"/api/dashboard/{DID}", json={
|
||
"parameters": DPARAMS,
|
||
"dashcards": DASHCARDS,
|
||
})
|
||
if r.status_code >= 400:
|
||
print(r.status_code, r.text[:800])
|
||
r.raise_for_status()
|
||
|
||
print(f"\nDONE")
|
||
print(f"dashboard URL: {BASE}/dashboard/{DID}")
|
||
client.close()
|