fn-registry agent
140 lines
5.6 KiB
Python
140 lines
5.6 KiB
Python
#!/usr/bin/env python3
|
|
"""B = (A) UNION (regenerados): vuelve la union al SQL de cards B.
|
|
|
|
Mantiene los field-filters por NAME (multi-select dropdown) ya aplicados en rewire_filters_by_name.
|
|
"""
|
|
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
|
|
|
|
F_QUOTE_CREATED_AT = 16588
|
|
F_CENTER_NAME = 17330
|
|
F_TPVUSER_NAME = 17958
|
|
F_COMPANY_NAME = 17158
|
|
F_PRODUCT_DESC = 16795
|
|
|
|
client = httpx.Client(base_url=BASE, headers={"x-api-key": API_KEY}, timeout=180)
|
|
|
|
|
|
def field_filter_tag(name, field_id, widget, display_name):
|
|
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_NAME, "string/=", "Centro"),
|
|
"agente": field_filter_tag("agente", F_TPVUSER_NAME, "string/=", "Agente CC"),
|
|
"compania": field_filter_tag("compania", F_COMPANY_NAME, "string/=", "Compañía"),
|
|
"producto": field_filter_tag("producto", F_PRODUCT_DESC, "string/contains", "Producto"),
|
|
}
|
|
|
|
|
|
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`),
|
|
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}}]]
|
|
),
|
|
b_orders AS (
|
|
-- A: orders directos del Q-CC
|
|
SELECT q0_order AS order_id FROM cc_anchored
|
|
UNION DISTINCT
|
|
-- Regenerados: Q1 dentro 60d desde Q0-CC, distinto order, centro NO-CC, mismo cliente+vehiculo
|
|
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
|
|
LEFT JOIN `psql_dcpublic.products` ON `psql_dcpublic.tpv_orders_orderitem`.product_id = `psql_dcpublic.products`.id
|
|
WHERE COALESCE(`psql_dcpublic.centers`.id, 0) NOT IN (159, 162)
|
|
[[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)",
|
|
}
|
|
|
|
CARDS = {
|
|
10251: ("total", "B · Presupuestos CallC + Regenerados — Total facturado"),
|
|
10252: ("count", "B · Presupuestos CallC + Regenerados — # Facturas"),
|
|
10253: ("ticket", "B · Presupuestos CallC + Regenerados — Ticket medio"),
|
|
}
|
|
|
|
|
|
for cid, (agg, name) in CARDS.items():
|
|
cur = client.get(f"/api/card/{cid}").json()
|
|
viz = cur.get("visualization_settings", {})
|
|
sql = SQL_B.replace("__AGG__", AGG[agg])
|
|
body = {
|
|
"name": name,
|
|
"description": name,
|
|
"dataset_query": {
|
|
"type": "native", "database": DB_ID,
|
|
"native": {"query": sql, "template-tags": TAGS_AB},
|
|
},
|
|
"visualization_settings": viz,
|
|
}
|
|
r = client.put(f"/api/card/{cid}", json=body)
|
|
r.raise_for_status()
|
|
print(f" card {cid} ({agg}) updated -> name='{name}'")
|
|
|
|
client.close()
|
|
print("done")
|