fn-registry agent
281 lines
13 KiB
Python
281 lines
13 KiB
Python
#!/usr/bin/env python3
|
||
"""Dashboard v2 — coherencia:
|
||
- Date filter en `tpv_orders_quote.created_at` (creacion de presupuesto) para A B C.
|
||
- A = quotes CC -> factura (mismo order_id).
|
||
- B = (A) ∪ (quotes regenerados en centro fisico desde Q0 CC dentro 60d) -> facturas.
|
||
- C = invoices de centros fisicos cuyo order tiene AL MENOS un quote en la ventana
|
||
(98.7% de invoices tienen quote, solo cae 3.7% del valor — aceptable).
|
||
"""
|
||
import subprocess
|
||
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
|
||
|
||
F_QUOTE_CREATED_AT = 16588
|
||
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=180)
|
||
|
||
|
||
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,
|
||
}
|
||
|
||
TAGS_AB = {
|
||
"date": field_filter_tag("date", F_QUOTE_CREATED_AT, "date/range", "Fecha presupuesto"),
|
||
"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"),
|
||
}
|
||
TAGS_C = {k: v for k, v in TAGS_AB.items() if k != "agente"}
|
||
|
||
|
||
# ---- SQL ----
|
||
# Convention: tabla cuya columna se filtra via field-filter NO lleva alias.
|
||
# Demas tablas pueden usar alias o full ref.
|
||
|
||
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
|
||
"""
|
||
|
||
# B: union de Q-CC directos + Q1 regenerados (desde Q0-CC dentro 60d).
|
||
# Q1 vive en una CTE clon de la tabla quote para no chocar con field-filter de Q-CC.
|
||
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)
|
||
),
|
||
quotes_q1 AS (
|
||
SELECT id, order_id, created_at, deleted_at
|
||
FROM `psql_dcpublic.tpv_orders_quote`
|
||
),
|
||
orders_q1 AS (
|
||
SELECT id, customer_id, vehicle_id, terminal_id
|
||
FROM `psql_dcpublic.tpv_orders_order`
|
||
),
|
||
terminals_q1 AS (
|
||
SELECT id, center_id FROM `psql_dcpublic.tpv_terminals`
|
||
),
|
||
-- Q0 (CC) anchor; field-filters (date/agente) aplican sobre la quote CC
|
||
cc_anchored AS (
|
||
SELECT
|
||
`psql_dcpublic.tpv_orders_quote`.id AS q0_id,
|
||
`psql_dcpublic.tpv_orders_quote`.order_id AS q0_order,
|
||
`psql_dcpublic.tpv_orders_quote`.created_at AS q0_ts,
|
||
`psql_dcpublic.tpv_orders_quote`.created_by_id AS cc_agent_id,
|
||
`psql_dcpublic.tpv_orders_order`.customer_id AS cust_id,
|
||
`psql_dcpublic.tpv_orders_order`.vehicle_id AS veh_id
|
||
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
|
||
LEFT JOIN `psql_dcpublic.tpv_authorization_tpvuser` ON `psql_dcpublic.tpv_orders_quote`.created_by_id = `psql_dcpublic.tpv_authorization_tpvuser`.id
|
||
WHERE `psql_dcpublic.tpv_orders_quote`.deleted_at IS NULL
|
||
[[AND {{date}}]]
|
||
[[AND {{agente}}]]
|
||
),
|
||
-- Orders incluidos: Q-CC directos + Q1 regenerados
|
||
b_orders AS (
|
||
SELECT q0_order AS order_id FROM cc_anchored
|
||
UNION DISTINCT
|
||
SELECT q1.order_id AS order_id
|
||
FROM cc_anchored a
|
||
JOIN quotes_q1 q1
|
||
ON q1.deleted_at IS NULL
|
||
AND q1.created_at > a.q0_ts
|
||
AND q1.created_at <= TIMESTAMP_ADD(a.q0_ts, INTERVAL 60 DAY)
|
||
AND q1.order_id != a.q0_order
|
||
JOIN orders_q1 o1 ON q1.order_id = o1.id
|
||
LEFT JOIN terminals_q1 t1 ON o1.terminal_id = t1.id
|
||
WHERE o1.customer_id = a.cust_id
|
||
AND o1.vehicle_id = a.veh_id
|
||
AND t1.center_id IS NOT NULL
|
||
AND t1.center_id NOT 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 b_orders
|
||
JOIN `psql_dcpublic.tpv_orders_order` ON b_orders.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_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 {{centro}}]]
|
||
[[AND {{compania}}]]
|
||
[[AND {{producto}}]]
|
||
)
|
||
SELECT __AGG__ AS valor FROM filtered
|
||
"""
|
||
|
||
# C: total facturado en centros fisicos cuyos orders tienen al menos un quote en la ventana
|
||
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_quote`
|
||
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_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 {{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, sql_skel, agg_key, tags, display, currency):
|
||
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()
|
||
|
||
|
||
specs = [
|
||
("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']}")
|
||
|
||
print("\ncreating dashboard v2")
|
||
dash = client.post("/api/dashboard", json={
|
||
"name": "Call Center — Presupuestos · KPIs (v2)",
|
||
"collection_id": COLLECTION_ID,
|
||
"description": "A=Quotes CC · B=CC + Regenerados · C=Total facturado (orders con quote). Filtros: fecha-presupuesto/centro/agente/compañía/producto",
|
||
}).json()
|
||
DID = dash["id"]
|
||
print(f" dashboard id={DID}")
|
||
|
||
DPARAMS = [
|
||
{"id": "p_date", "name": "Fecha presupuesto", "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"},
|
||
]
|
||
|
||
def mapping_for_card(card_id, tag_keys):
|
||
return [{"parameter_id": f"p_{k}", "card_id": card_id,
|
||
"target": ["dimension", ["template-tag", k]]} for k in tag_keys]
|
||
|
||
DASHCARDS = []
|
||
layout = [
|
||
("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
|
||
|
||
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()
|