881 lines
44 KiB
Python
881 lines
44 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
"""Search and trace opportunities between Brand (Marca) and Branches (Sucursal) using verifier rules."""
|
|
|
|
import argparse
|
|
import os
|
|
import sys
|
|
import sqlite3
|
|
import json
|
|
import re
|
|
import unicodedata
|
|
|
|
ROOT_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
|
|
if ROOT_DIR not in sys.path:
|
|
sys.path.insert(0, ROOT_DIR)
|
|
|
|
import sync_engine # noqa: E402
|
|
|
|
from paths import DB_PATH
|
|
BRAND_LOCATION_ID = "GbKkBpCmKu2QmloKFHy3"
|
|
_schema_cache = {}
|
|
_tokens_map = None
|
|
|
|
def safe_print(*args, **kwargs):
|
|
sep = kwargs.get("sep", " ")
|
|
end = kwargs.get("end", "\n")
|
|
text = sep.join(str(arg) for arg in args)
|
|
encoding = sys.stdout.encoding or "utf-8"
|
|
try:
|
|
sys.stdout.write(text + end)
|
|
sys.stdout.flush()
|
|
except UnicodeEncodeError:
|
|
try:
|
|
safe_text = text.encode(encoding, errors="replace").decode(encoding)
|
|
sys.stdout.write(safe_text + end)
|
|
sys.stdout.flush()
|
|
except Exception:
|
|
try:
|
|
safe_text = text.encode("ascii", errors="replace").decode("ascii")
|
|
sys.stdout.write(safe_text + end)
|
|
sys.stdout.flush()
|
|
except Exception:
|
|
pass
|
|
|
|
def normalize_phone(phone):
|
|
if not phone:
|
|
return ""
|
|
return re.sub(r"\D+", "", str(phone))
|
|
|
|
def phone_last_digits(phone, n=10):
|
|
"""Retorna los ultimos n digitos del numero normalizado (util para ignorar codigo de pais)."""
|
|
norm = normalize_phone(phone)
|
|
return norm[-n:] if len(norm) >= n else norm
|
|
|
|
def normalize_text(text):
|
|
"""Normaliza texto removiendo acentos y espacios extra para comparaciones robustas."""
|
|
if not text:
|
|
return ""
|
|
nfkd = unicodedata.normalize("NFD", str(text))
|
|
without_accents = "".join(c for c in nfkd if unicodedata.category(c) != "Mn")
|
|
return " ".join(without_accents.lower().split())
|
|
|
|
def load_verifier_map():
|
|
path = os.path.join(ROOT_DIR, "Monte Providencia - Verificador de sucursales y correos - Sucursales.csv")
|
|
tienda_to_location = {}
|
|
location_to_tienda = {}
|
|
location_to_sc_name = {}
|
|
if os.path.exists(path):
|
|
import csv
|
|
try:
|
|
with open(path, mode="r", encoding="utf-8-sig") as f:
|
|
reader = csv.DictReader(f)
|
|
for row in reader:
|
|
tienda = (row.get("TIENDA") or "").strip().upper()
|
|
loc_id = (row.get("ID LOCATION BUCEFALO") or "").strip()
|
|
sc_name = (row.get("SC BUCEFALO") or "").strip()
|
|
if tienda and loc_id:
|
|
tienda_to_location[tienda] = loc_id
|
|
location_to_tienda[loc_id] = tienda
|
|
location_to_sc_name[loc_id] = sc_name
|
|
except Exception as e:
|
|
safe_print(f"Advertencia al leer verificador de sucursales: {e}")
|
|
return tienda_to_location, location_to_tienda, location_to_sc_name
|
|
|
|
def get_location_token(location_id):
|
|
global _tokens_map
|
|
if _tokens_map is None:
|
|
_tokens_map = sync_engine.get_tokens_map()
|
|
return _tokens_map.get(location_id)
|
|
|
|
|
|
def get_schema_field_id(location_id, object_key, field_name):
|
|
cache_key = (location_id, object_key)
|
|
if cache_key not in _schema_cache:
|
|
token = get_location_token(location_id)
|
|
if not token:
|
|
_schema_cache[cache_key] = {}
|
|
else:
|
|
_schema_cache[cache_key] = sync_engine.ghl_client.get_object_schema(token, location_id, object_key)
|
|
return _schema_cache[cache_key].get(field_name)
|
|
|
|
|
|
def extract_custom_field_value(cf_json, field_id):
|
|
if not field_id:
|
|
return None
|
|
if not cf_json:
|
|
return None
|
|
try:
|
|
cfs = json.loads(cf_json)
|
|
if isinstance(cfs, list):
|
|
for cf in cfs:
|
|
if cf.get("id") == field_id or cf.get("fieldId") == field_id:
|
|
return cf.get("value")
|
|
except Exception:
|
|
pass
|
|
return None
|
|
|
|
|
|
def extract_tienda_from_custom_fields(cf_json, tienda_to_location, tienda_field_id):
|
|
val = str(extract_custom_field_value(cf_json, tienda_field_id) or "").strip().upper()
|
|
if not val:
|
|
return None
|
|
if val in tienda_to_location:
|
|
return val
|
|
for tienda in tienda_to_location:
|
|
if tienda in val or val in tienda:
|
|
return tienda
|
|
return None
|
|
|
|
def calculate_opportunity_similarity(so, bo):
|
|
"""
|
|
Calcula un puntaje de similitud (0 a 100) entre la oportunidad de sucursal (so)
|
|
y la de marca (bo) para encontrar el par mas semejante.
|
|
"""
|
|
if so["id"] == bo["id"]:
|
|
return 100
|
|
|
|
score = 0
|
|
so_name = normalize_text(so["name"] or "")
|
|
bo_name = normalize_text(bo["name"] or "")
|
|
|
|
# 1. Match de Nombre Exacto (normalizado — ignora acentos y espacios)
|
|
if so_name == bo_name:
|
|
score += 60
|
|
# 2. Match de Nombre Parcial
|
|
elif so_name and bo_name:
|
|
if so_name in bo_name or bo_name in so_name:
|
|
score += 40
|
|
else:
|
|
# Overlap de palabras
|
|
so_words = set(so_name.split())
|
|
bo_words = set(bo_name.split())
|
|
overlap = so_words.intersection(bo_words)
|
|
if len(overlap) > 0:
|
|
score += min(35, len(overlap) * 10)
|
|
|
|
# 3. Match de Valor Monetario
|
|
so_val = so["monetary_value"] or 0.0
|
|
bo_val = bo["monetary_value"] or 0.0
|
|
if abs(so_val - bo_val) < 0.01:
|
|
score += 25
|
|
elif max(so_val, bo_val) > 0 and abs(so_val - bo_val) / max(1.0, so_val, bo_val) < 0.2:
|
|
score += 15
|
|
|
|
# 4. Match de Estado (Status)
|
|
if so["status"] == bo["status"]:
|
|
score += 15
|
|
|
|
return score
|
|
|
|
def search_brand_to_branch_opportunities(query_term):
|
|
if not os.path.exists(DB_PATH):
|
|
safe_print(f"Error: La base de datos local no existe en {DB_PATH}.")
|
|
safe_print("Por favor, ejecuta la sincronizacion global primero desde el dashboard.")
|
|
sys.exit(1)
|
|
|
|
tienda_to_location, location_to_tienda, location_to_sc_name = load_verifier_map()
|
|
brand_tienda_field_id = get_schema_field_id(BRAND_LOCATION_ID, "contact", "TIENDA")
|
|
if not brand_tienda_field_id:
|
|
safe_print("ALERTA: campo TIENDA no existe en el schema de contactos de Marca; no se inferira sucursal asignada.")
|
|
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
|
|
try:
|
|
safe_print("\n" + "=" * 90)
|
|
safe_print("BUSQUEDA Y AUDITORIA DE TRAZABILIDAD (MONTE PROVIDENCIA): MARCA -> SUCURSAL")
|
|
safe_print("=" * 90)
|
|
safe_print(f"Criterio de busqueda: '{query_term}'\n")
|
|
|
|
# 1. Encontrar todos los contactos que coincidan en la Marca Principal (Marca de Control)
|
|
q_like = f"%{query_term}%"
|
|
phone_digits = normalize_phone(query_term)
|
|
q_phone = f"%{phone_digits}%" if phone_digits else "____invalid____"
|
|
|
|
brand_contacts_sql = """
|
|
SELECT c.*
|
|
FROM contacts c
|
|
WHERE c.location_id = ?
|
|
AND (
|
|
c.first_name LIKE ?
|
|
OR c.last_name LIKE ?
|
|
OR c.email LIKE ?
|
|
OR c.phone LIKE ?
|
|
OR (? != '____invalid____' AND c.phone LIKE ?)
|
|
)
|
|
"""
|
|
brand_contacts = conn.execute(brand_contacts_sql, (BRAND_LOCATION_ID, q_like, q_like, q_like, q_like, q_phone, q_phone)).fetchall()
|
|
|
|
if not brand_contacts:
|
|
safe_print("No se encontraron contactos en la cuenta de MARCA PRINCIPAL que coincidan con la busqueda.")
|
|
safe_print("Buscando en sucursales...")
|
|
|
|
# Buscar en sucursales directamente si no se encontro en marca
|
|
all_contacts_sql = """
|
|
SELECT c.*, a.nombre as account_name
|
|
FROM contacts c
|
|
JOIN accounts a ON c.location_id = a.location_id
|
|
WHERE c.location_id != ?
|
|
AND (
|
|
c.first_name LIKE ?
|
|
OR c.last_name LIKE ?
|
|
OR c.email LIKE ?
|
|
OR c.phone LIKE ?
|
|
OR (? != '____invalid____' AND c.phone LIKE ?)
|
|
)
|
|
"""
|
|
branch_contacts = conn.execute(all_contacts_sql, (BRAND_LOCATION_ID, q_like, q_like, q_like, q_like, q_phone, q_phone)).fetchall()
|
|
|
|
if not branch_contacts:
|
|
safe_print("No se encontro el contacto en ninguna cuenta.")
|
|
return
|
|
|
|
# Si se encontro en sucursales, mostrar resumen simplificado
|
|
safe_print(f"Se encontraron {len(branch_contacts)} contactos en sucursales (pero no en la Marca Principal).\n")
|
|
for bc in branch_contacts:
|
|
safe_print(f"Cliente: {bc['first_name'] or ''} {bc['last_name'] or ''}".strip())
|
|
safe_print(f" Cuenta/Sucursal: {bc['account_name']}")
|
|
safe_print(f" Telefono: {bc['phone'] or 'N/A'}")
|
|
safe_print(f" Email: {bc['email'] or 'N/A'}")
|
|
safe_print("-" * 60)
|
|
return
|
|
|
|
safe_print(f"Se encontraron {len(brand_contacts)} clientes en Marca Principal. Analizando ruta de trazabilidad...\n")
|
|
|
|
for idx, bc in enumerate(brand_contacts, 1):
|
|
client_name = f"{bc['first_name'] or ''} {bc['last_name'] or ''}".strip() or "Sin nombre"
|
|
client_phone = bc['phone'] or ""
|
|
client_email = bc['email'] or ""
|
|
cf_json = bc['custom_fields_json']
|
|
|
|
safe_print(f"CLIENTE #{idx:02d}: {client_name}")
|
|
if client_phone: safe_print(f" Telefono: {client_phone}")
|
|
if client_email: safe_print(f" Email: {client_email}")
|
|
|
|
# Detectar tienda asignada en los campos personalizados
|
|
assigned_tienda = extract_tienda_from_custom_fields(cf_json, tienda_to_location, brand_tienda_field_id)
|
|
assigned_location_id = tienda_to_location.get(assigned_tienda) if assigned_tienda else None
|
|
assigned_sc_name = location_to_sc_name.get(assigned_location_id, assigned_tienda) if assigned_location_id else None
|
|
|
|
if assigned_tienda and assigned_location_id:
|
|
safe_print(f" --> SUCURSAL ASIGNADA EN MARCA: '{assigned_tienda}' ({assigned_sc_name})")
|
|
else:
|
|
safe_print(" --> SUCURSAL ASIGNADA EN MARCA: [ALERTA] No se ha definido tienda/sucursal en los campos personalizados de Marca.")
|
|
|
|
safe_print("-" * 90)
|
|
|
|
# Paso 1: Oportunidades del cliente en la Marca Principal
|
|
opps_brand_sql = """
|
|
SELECT o.*, p.name as pipeline_name
|
|
FROM opportunities o
|
|
LEFT JOIN pipelines p ON o.pipeline_id = p.id AND o.location_id = p.location_id
|
|
WHERE o.location_id = ? AND o.contact_id = ?
|
|
"""
|
|
brand_opps = conn.execute(opps_brand_sql, (BRAND_LOCATION_ID, bc["id"])).fetchall()
|
|
safe_print(f" Paso 1: Oportunidades en Marca Principal: {len(brand_opps)}")
|
|
for bo in brand_opps:
|
|
safe_print(f" * ID: {bo['id']}")
|
|
safe_print(f" Nombre: {bo['name'] or 'Sin nombre'}")
|
|
safe_print(f" Estado: {bo['status'].upper() if bo['status'] else 'OPEN'}")
|
|
safe_print(f" Valor: ${(bo['monetary_value'] or 0.0):,.2f}")
|
|
safe_print(f" Etapa: {bo['pipeline_name'] or 'Standar'} (Stage: {bo['pipeline_stage_id']})")
|
|
safe_print()
|
|
|
|
# Paso 2: Verificar existencia del contacto en la Sucursal Asignada
|
|
if assigned_location_id:
|
|
# Buscar contacto en la sucursal asignada (por email, teléfono o nombre)
|
|
norm_p = normalize_phone(client_phone)
|
|
phone_suf = phone_last_digits(client_phone)
|
|
branch_contact_sql = """
|
|
SELECT * FROM contacts
|
|
WHERE location_id = ?
|
|
AND (
|
|
(email IS NOT NULL AND email != '' AND LOWER(email) = ?)
|
|
OR (phone IS NOT NULL AND phone != '' AND (
|
|
REPLACE(REPLACE(REPLACE(REPLACE(phone,'+',''),'-',''),' ',''),'(','') = ?
|
|
OR phone LIKE '%' || ?
|
|
))
|
|
OR (LOWER(first_name || ' ' || last_name) = ?)
|
|
)
|
|
"""
|
|
branch_contact_row = conn.execute(branch_contact_sql, (assigned_location_id, client_email.lower(), norm_p, phone_suf, client_name.lower())).fetchone()
|
|
|
|
if branch_contact_row:
|
|
safe_print(f" Paso 2: Busqueda en Sucursal '{assigned_tienda}': [EXITO] Contacto encontrado.")
|
|
safe_print(f" * ID en Sucursal: {branch_contact_row['id']}")
|
|
|
|
# Paso 3: Verificar oportunidad en la sucursal asignada
|
|
branch_opps_sql = """
|
|
SELECT o.*, p.name as pipeline_name
|
|
FROM opportunities o
|
|
LEFT JOIN pipelines p ON o.pipeline_id = p.id AND o.location_id = p.location_id
|
|
WHERE o.location_id = ? AND o.contact_id = ?
|
|
"""
|
|
branch_opps = conn.execute(branch_opps_sql, (assigned_location_id, branch_contact_row["id"])).fetchall()
|
|
safe_print(f" Paso 3: Oportunidades en Sucursal '{assigned_tienda}': {len(branch_opps)}")
|
|
|
|
for so in branch_opps:
|
|
safe_print(f" * ID: {so['id']}")
|
|
safe_print(f" Nombre: {so['name'] or 'Sin nombre'}")
|
|
safe_print(f" Estado: {so['status'].upper() if so['status'] else 'OPEN'}")
|
|
safe_print(f" Valor: ${(so['monetary_value'] or 0.0):,.2f}")
|
|
safe_print(f" Etapa: {so['pipeline_name'] or 'Standar'} (Stage: {so['pipeline_stage_id']})")
|
|
|
|
# Paso 4: Trazabilidad / Sincronizacion de vuelta a Marca
|
|
synced_in_brand = False
|
|
for bo in brand_opps:
|
|
if bo["id"] == so["id"]:
|
|
synced_in_brand = True
|
|
break
|
|
if bo["name"] and so["name"] and normalize_text(bo["name"]) == normalize_text(so["name"]):
|
|
synced_in_brand = True
|
|
break
|
|
|
|
if synced_in_brand:
|
|
safe_print(f" --> STATUS TRAZABILIDAD: [OK] Sincronizacion Completa (Existe en Sucursal y Marca)")
|
|
else:
|
|
# Intentar buscar la más semejante
|
|
best_bo = None
|
|
best_score = 0
|
|
for bo in brand_opps:
|
|
score = calculate_opportunity_similarity(so, bo)
|
|
if score > best_score:
|
|
best_score = score
|
|
best_bo = bo
|
|
if best_bo and best_score >= 35:
|
|
safe_print(f" --> STATUS TRAZABILIDAD: [DISCREPANCIA] Detectada paridad parcial con '{best_bo['name']}' (Similitud: {best_score}%)")
|
|
safe_print(f" Campos discrepantes:")
|
|
if (so["name"] or "").strip().lower() != (best_bo["name"] or "").strip().lower():
|
|
safe_print(f" - Nombre: '{so['name']}' vs '{best_bo['name']}'")
|
|
if so["status"] != best_bo["status"]:
|
|
safe_print(f" - Estado: {so['status']} vs {best_bo['status']}")
|
|
if abs((so["monetary_value"] or 0) - (best_bo["monetary_value"] or 0)) >= 0.01:
|
|
safe_print(f" - Valor: ${(so['monetary_value'] or 0.0):,.2f} vs ${(best_bo['monetary_value'] or 0.0):,.2f}")
|
|
else:
|
|
safe_print(f" --> STATUS TRAZABILIDAD: [ALERTA] Oportunidad existe en sucursal pero NO se ha sincronizado de vuelta a la Marca Principal.")
|
|
|
|
if not branch_opps:
|
|
safe_print(f" * [ALERTA] El contacto existe en la sucursal '{assigned_tienda}' pero NO tiene oportunidad creada.")
|
|
else:
|
|
safe_print(f" Paso 2: Busqueda en Sucursal '{assigned_tienda}': [ALERTA] El contacto no existe en la sucursal asignada.")
|
|
safe_print(f" * Accion sugerida: Se requiere disparar sincronizacion del contacto de Marca a la sucursal '{assigned_tienda}'.")
|
|
else:
|
|
safe_print(" Paso 2: Busqueda en Sucursal: [ALERTA] No se puede auditar la sucursal porque el contacto no tiene asignada ninguna 'TIENDA' en Marca.")
|
|
|
|
safe_print("\n" + "=" * 90 + "\n")
|
|
|
|
finally:
|
|
conn.close()
|
|
|
|
def run_global_traceability_audit():
|
|
if not os.path.exists(DB_PATH):
|
|
safe_print(f"Error: La base de datos local no existe en {DB_PATH}.")
|
|
safe_print("Por favor, ejecuta la sincronizacion global primero desde el dashboard.")
|
|
sys.exit(1)
|
|
|
|
tienda_to_location, location_to_tienda, location_to_sc_name = load_verifier_map()
|
|
brand_tienda_field_id = get_schema_field_id(BRAND_LOCATION_ID, "contact", "TIENDA")
|
|
if not brand_tienda_field_id:
|
|
safe_print("ALERTA: campo TIENDA no existe en el schema de contactos de Marca; no se inferira sucursal asignada.")
|
|
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
|
|
try:
|
|
safe_print("\n" + "=" * 90)
|
|
safe_print("AUDITORIA GLOBAL DE TRAZABILIDAD DE OPORTUNIDADES (MONTE PROVIDENCIA): MARCA -> SUCURSAL")
|
|
safe_print("=" * 90)
|
|
safe_print("Iniciando escaneo masivo de la base de datos...\n")
|
|
|
|
# 1. Obtener todos los contactos de la Marca Principal
|
|
brand_contacts = conn.execute("SELECT * FROM contacts WHERE location_id = ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
|
|
# 2. Obtener todas las oportunidades de Marca y de Sucursales indexadas en memoria para busqueda ultra-rapida
|
|
brand_opps_rows = conn.execute("SELECT * FROM opportunities WHERE location_id = ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
brand_opps_by_contact = {}
|
|
for row in brand_opps_rows:
|
|
brand_opps_by_contact.setdefault(row["contact_id"], []).append(dict(row))
|
|
|
|
all_branch_opps_rows = conn.execute("SELECT o.*, a.nombre as branch_name FROM opportunities o JOIN accounts a ON o.location_id = a.location_id WHERE o.location_id != ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
|
|
# Obtener todos los contactos en sucursales
|
|
all_branch_contacts = conn.execute("SELECT c.* FROM contacts c WHERE c.location_id != ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
|
|
# Indexar contactos de sucursales para busqueda rapida instantanea
|
|
branch_contacts_by_phone = {}
|
|
branch_contacts_by_phone_suffix = {}
|
|
branch_contacts_by_email = {}
|
|
branch_contacts_by_name = {}
|
|
branch_contacts_by_norm_name = {}
|
|
for bc in all_branch_contacts:
|
|
loc = bc["location_id"]
|
|
phone = normalize_phone(bc["phone"])
|
|
suffix = phone_last_digits(bc["phone"])
|
|
email = (bc["email"] or "").strip().lower()
|
|
name = f"{bc['first_name'] or ''} {bc['last_name'] or ''}".strip().lower()
|
|
norm_name = normalize_text(f"{bc['first_name'] or ''} {bc['last_name'] or ''}")
|
|
|
|
if phone:
|
|
branch_contacts_by_phone[(loc, phone)] = dict(bc)
|
|
if suffix and suffix != phone:
|
|
branch_contacts_by_phone_suffix[(loc, suffix)] = dict(bc)
|
|
if email:
|
|
branch_contacts_by_email[(loc, email)] = dict(bc)
|
|
if name:
|
|
branch_contacts_by_name[(loc, name)] = dict(bc)
|
|
if norm_name and norm_name != name:
|
|
branch_contacts_by_norm_name[(loc, norm_name)] = dict(bc)
|
|
|
|
# Indexar oportunidades de sucursales por (location_id, contact_id)
|
|
branch_opps_by_contact_id = {}
|
|
for row in all_branch_opps_rows:
|
|
branch_opps_by_contact_id.setdefault((row["location_id"], row["contact_id"]), []).append(dict(row))
|
|
|
|
# Variables para métricas
|
|
total_audited_contacts = len(brand_contacts)
|
|
contacts_with_assigned_branch = 0
|
|
contacts_without_assigned_branch = 0
|
|
contacts_found_in_assigned_branch = 0
|
|
contacts_missing_in_assigned_branch = 0
|
|
|
|
total_brand_opportunities = len(brand_opps_rows)
|
|
total_branch_opportunities = len(all_branch_opps_rows)
|
|
|
|
opportunities_synced = 0
|
|
opps_with_discrepancies = 0
|
|
opps_only_in_brand = 0
|
|
opps_only_in_branch = 0
|
|
contacts_in_branch_without_opp = 0
|
|
|
|
discrepancies = [] # list of dicts with alerts
|
|
|
|
for bc in brand_contacts:
|
|
client_name = f"{bc['first_name'] or ''} {bc['last_name'] or ''}".strip() or "Sin nombre"
|
|
client_phone = bc['phone'] or ""
|
|
client_email = bc['email'] or ""
|
|
cf_json = bc['custom_fields_json']
|
|
brand_cid = bc["id"]
|
|
|
|
assigned_tienda = extract_tienda_from_custom_fields(cf_json, tienda_to_location, brand_tienda_field_id)
|
|
assigned_location_id = tienda_to_location.get(assigned_tienda) if assigned_tienda else None
|
|
|
|
if not assigned_location_id:
|
|
contacts_without_assigned_branch += 1
|
|
continue
|
|
|
|
contacts_with_assigned_branch += 1
|
|
|
|
# 1. Obtener oportunidades de este contacto en Marca
|
|
opps_in_brand = brand_opps_by_contact.get(brand_cid, [])
|
|
|
|
# 2. Buscar contacto en la sucursal asignada usando los Ãndices rápidos
|
|
norm_p = normalize_phone(client_phone)
|
|
p_suffix = phone_last_digits(client_phone)
|
|
norm_name = normalize_text(client_name)
|
|
branch_c = None
|
|
|
|
if norm_p and (assigned_location_id, norm_p) in branch_contacts_by_phone:
|
|
branch_c = branch_contacts_by_phone[(assigned_location_id, norm_p)]
|
|
elif p_suffix and (assigned_location_id, p_suffix) in branch_contacts_by_phone_suffix:
|
|
branch_c = branch_contacts_by_phone_suffix[(assigned_location_id, p_suffix)]
|
|
elif client_email and (assigned_location_id, client_email.lower()) in branch_contacts_by_email:
|
|
branch_c = branch_contacts_by_email[(assigned_location_id, client_email.lower())]
|
|
elif (assigned_location_id, client_name.lower()) in branch_contacts_by_name:
|
|
branch_c = branch_contacts_by_name[(assigned_location_id, client_name.lower())]
|
|
elif norm_name and (assigned_location_id, norm_name) in branch_contacts_by_norm_name:
|
|
branch_c = branch_contacts_by_norm_name[(assigned_location_id, norm_name)]
|
|
|
|
if not branch_c:
|
|
contacts_missing_in_assigned_branch += 1
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"contacto_marca_id": brand_cid,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"sucursal_asignada": assigned_tienda,
|
|
"tipo_alerta": "CONTACTO FALTANTE EN SUCURSAL",
|
|
"descripcion": f"El contacto existe en Marca pero no se encuentra en la sucursal asignada '{assigned_tienda}'."
|
|
})
|
|
continue
|
|
|
|
contacts_found_in_assigned_branch += 1
|
|
branch_cid = branch_c["id"]
|
|
|
|
# 3. Obtener oportunidades de este contacto en la sucursal asignada
|
|
opps_in_branch = branch_opps_by_contact_id.get((assigned_location_id, branch_cid), [])
|
|
|
|
if not opps_in_branch and (opps_in_brand or "sucursal" in str(bc.get("tags", "")).lower()):
|
|
contacts_in_branch_without_opp += 1
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"contacto_marca_id": brand_cid,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"sucursal_asignada": assigned_tienda,
|
|
"tipo_alerta": "CONTACTO SIN OPORTUNIDAD EN SUCURSAL",
|
|
"descripcion": f"El contacto existe en '{assigned_tienda}' pero no tiene ninguna oportunidad creada en esa sucursal."
|
|
})
|
|
|
|
# 4. Cruzar oportunidades para verificar sincronización
|
|
for bo in opps_in_brand:
|
|
matched = False
|
|
for so in opps_in_branch:
|
|
if bo["id"] == so["id"] or (bo["name"] and so["name"] and normalize_text(bo["name"]) == normalize_text(so["name"])):
|
|
matched = True
|
|
opportunities_synced += 1
|
|
break
|
|
if not matched:
|
|
# Buscar la más semejante
|
|
best_so = None
|
|
best_score = 0
|
|
for so in opps_in_branch:
|
|
score = calculate_opportunity_similarity(so, bo)
|
|
if score > best_score:
|
|
best_score = score
|
|
best_so = so
|
|
if not best_so or best_score < 35:
|
|
opps_only_in_brand += 1
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"contacto_marca_id": brand_cid,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"sucursal_asignada": assigned_tienda,
|
|
"tipo_alerta": "OPORTUNIDAD SOLO EN MARCA",
|
|
"descripcion": f"Oportunidad '{bo['name']}' (ID: {bo['id']}) existe en Marca pero no se encuentra en la sucursal '{assigned_tienda}'."
|
|
})
|
|
|
|
for so in opps_in_branch:
|
|
matched = False
|
|
for bo in opps_in_brand:
|
|
if bo["id"] == so["id"] or (bo["name"] and so["name"] and normalize_text(bo["name"]) == normalize_text(so["name"])):
|
|
matched = True
|
|
break
|
|
if not matched:
|
|
# Buscar la más semejante para ver si es una discrepancia de campos o falta total
|
|
best_bo = None
|
|
best_score = 0
|
|
for bo in opps_in_brand:
|
|
score = calculate_opportunity_similarity(so, bo)
|
|
if score > best_score:
|
|
best_score = score
|
|
best_bo = bo
|
|
if best_bo and best_score >= 35:
|
|
opps_with_discrepancies += 1
|
|
disc_fields = []
|
|
if (so["name"] or "").strip().lower() != (best_bo["name"] or "").strip().lower():
|
|
disc_fields.append("Nombre")
|
|
if so["status"] != best_bo["status"]:
|
|
disc_fields.append("Estado")
|
|
if abs((so["monetary_value"] or 0) - (best_bo["monetary_value"] or 0)) >= 0.01:
|
|
disc_fields.append("Valor")
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"contacto_marca_id": brand_cid,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"sucursal_asignada": assigned_tienda,
|
|
"tipo_alerta": "DISCREPANCIA EN CAMPOS",
|
|
"descripcion": f"Oportunidad parcial encontrada en Marca '{best_bo['name']}' pero difiere con la Sucursal en: {', '.join(disc_fields)} (Coincidencia: {best_score}%)."
|
|
})
|
|
else:
|
|
opps_only_in_branch += 1
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"contacto_marca_id": brand_cid,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"sucursal_asignada": assigned_tienda,
|
|
"tipo_alerta": "OPORTUNIDAD SOLO EN SUCURSAL",
|
|
"descripcion": f"Oportunidad '{so['name']}' (ID: {so['id']}) existe en la sucursal '{assigned_tienda}' pero falta sincronizar a la Marca."
|
|
})
|
|
|
|
# Imprimir reporte de discrepancias
|
|
safe_print("=== DETALLE DE DISCREPANCIAS Y ALERTAS DETECTADAS ===")
|
|
safe_print("-" * 110)
|
|
if not discrepancies:
|
|
safe_print("EXCELENTE: No se detecto ninguna discrepancia de trazabilidad en el sistema.")
|
|
else:
|
|
limit = 100
|
|
for idx, d in enumerate(discrepancies[:limit], 1):
|
|
safe_print(f"{idx:03d}. [{d['tipo_alerta']}] - Cliente: {d['cliente']}")
|
|
safe_print(f" Sucursal Asignada: {d['sucursal_asignada']}")
|
|
if d['telefono']: safe_print(f" Telefono: {d['telefono']}")
|
|
safe_print(f" Detalle: {d['descripcion']}")
|
|
safe_print("-" * 110)
|
|
|
|
if len(discrepancies) > limit:
|
|
safe_print(f"... [INFO] Se omitieron {len(discrepancies) - limit} alertas adicionales en consola para agilizar la visualizacion.")
|
|
safe_print("-" * 110)
|
|
|
|
# Imprimir resumen ejecutivo global
|
|
safe_print("\n=== RESUMEN EJECUTIVO DE TRAZABILIDAD GLOBAL ===")
|
|
safe_print(f" Total Clientes Auditados en Marca: {total_audited_contacts}")
|
|
safe_print(f" Clientes con Sucursal Asignada: {contacts_with_assigned_branch}")
|
|
safe_print(f" Clientes sin Sucursal Asignada: {contacts_without_assigned_branch}")
|
|
safe_print(f" Clientes Localizados en su Sucursal: {contacts_found_in_assigned_branch}")
|
|
safe_print(f" Clientes Faltantes en su Sucursal: {contacts_missing_in_assigned_branch}")
|
|
safe_print("-" * 50)
|
|
safe_print(f" Total Oportunidades en Marca Principal: {total_brand_opportunities}")
|
|
safe_print(f" Total Oportunidades en Sucursales: {total_branch_opportunities}")
|
|
safe_print(f" Oportunidades Sincronizadas Correctamente: {opportunities_synced}")
|
|
safe_print(f" Oportunidades con Discrepancias de Datos: {opps_with_discrepancies}")
|
|
safe_print(f" Oportunidades Huerfanas en Marca: {opps_only_in_brand}")
|
|
safe_print(f" Oportunidades No Sincronizadas en Sucursal: {opps_only_in_branch}")
|
|
safe_print(f" Clientes en Sucursal sin Oportunidad: {contacts_in_branch_without_opp}")
|
|
safe_print("=" * 90 + "\n")
|
|
|
|
except Exception as e:
|
|
safe_print(f"Error critico durante la auditoria global: {e}")
|
|
finally:
|
|
conn.close()
|
|
|
|
def run_reverse_traceability_audit():
|
|
"""
|
|
AuditorÃa Inversa: Escanea todas las oportunidades creadas en las sucursales
|
|
y verifica si se han sincronizado correctamente hacia la cuenta de Marca Principal.
|
|
Usa el motor de emparejamiento por similitud para reconciliar discrepancias parciales de datos.
|
|
"""
|
|
if not os.path.exists(DB_PATH):
|
|
safe_print(f"Error: La base de datos local no existe en {DB_PATH}.")
|
|
safe_print("Por favor, ejecuta la sincronizacion global primero desde el dashboard.")
|
|
sys.exit(1)
|
|
|
|
tienda_to_location, location_to_tienda, location_to_sc_name = load_verifier_map()
|
|
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
|
|
try:
|
|
safe_print("\n" + "=" * 90)
|
|
safe_print("AUDITORIA INVERSA DE TRAZABILIDAD DE OPORTUNIDADES (MONTE PROVIDENCIA): SUCURSAL -> MARCA")
|
|
safe_print("=" * 90)
|
|
safe_print("Iniciando escaneo masivo de oportunidades en sucursales...\n")
|
|
|
|
# 1. Obtener todos los contactos y oportunidades de la Marca Principal
|
|
brand_contacts = conn.execute("SELECT * FROM contacts WHERE location_id = ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
brand_opps_rows = conn.execute("SELECT * FROM opportunities WHERE location_id = ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
|
|
# Indexar contactos de la Marca Principal para búsqueda ultra-rápida
|
|
brand_contacts_by_phone = {}
|
|
brand_contacts_by_phone_suffix = {}
|
|
brand_contacts_by_email = {}
|
|
brand_contacts_by_name = {}
|
|
brand_contacts_by_norm_name = {}
|
|
for bc in brand_contacts:
|
|
phone = normalize_phone(bc["phone"])
|
|
suffix = phone_last_digits(bc["phone"])
|
|
email = (bc["email"] or "").strip().lower()
|
|
name = f"{bc['first_name'] or ''} {bc['last_name'] or ''}".strip().lower()
|
|
norm_name = normalize_text(f"{bc['first_name'] or ''} {bc['last_name'] or ''}")
|
|
|
|
if phone:
|
|
brand_contacts_by_phone[phone] = dict(bc)
|
|
if suffix and suffix != phone:
|
|
brand_contacts_by_phone_suffix[suffix] = dict(bc)
|
|
if email:
|
|
brand_contacts_by_email[email] = dict(bc)
|
|
if name:
|
|
brand_contacts_by_name[name] = dict(bc)
|
|
if norm_name and norm_name != name:
|
|
brand_contacts_by_norm_name[norm_name] = dict(bc)
|
|
|
|
# Indexar oportunidades de la Marca por contact_id
|
|
brand_opps_by_contact_id = {}
|
|
for bo in brand_opps_rows:
|
|
brand_opps_by_contact_id.setdefault(bo["contact_id"], []).append(dict(bo))
|
|
|
|
# 2. Obtener todos los contactos y oportunidades de las Sucursales
|
|
branch_contacts_rows = conn.execute("SELECT c.*, a.nombre as branch_name FROM contacts c JOIN accounts a ON c.location_id = a.location_id WHERE c.location_id != ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
branch_contacts_by_id = { (bc["location_id"], bc["id"]): dict(bc) for bc in branch_contacts_rows }
|
|
|
|
branch_opps_rows = conn.execute("SELECT o.*, a.nombre as branch_name FROM opportunities o JOIN accounts a ON o.location_id = a.location_id WHERE o.location_id != ?", (BRAND_LOCATION_ID,)).fetchall()
|
|
|
|
# Métricas de AuditorÃa Inversa
|
|
total_branch_opps_audited = len(branch_opps_rows)
|
|
opps_successfully_synced = 0
|
|
opps_with_discrepancies = 0
|
|
opps_missing_in_brand = 0
|
|
branch_contacts_missing_in_brand = 0
|
|
|
|
discrepancies = []
|
|
per_location_stats = {} # loc_id -> {nombre, total, sinc, discrepancias, faltantes, contactos_faltantes}
|
|
|
|
for so in branch_opps_rows:
|
|
loc_id = so["location_id"]
|
|
contact_id = so["contact_id"]
|
|
opp_name = so["name"] or "Sin nombre"
|
|
branch_name = so["branch_name"]
|
|
|
|
# Determinar nombre de sucursal antes del early-continue para contabilizar todos los casos
|
|
tienda = location_to_tienda.get(loc_id, branch_name)
|
|
if loc_id not in per_location_stats:
|
|
per_location_stats[loc_id] = {
|
|
"nombre": location_to_sc_name.get(loc_id) or tienda,
|
|
"total": 0, "sinc": 0, "discrepancias": 0, "faltantes": 0, "contactos_faltantes": 0
|
|
}
|
|
per_location_stats[loc_id]["total"] += 1
|
|
|
|
# Obtener el contacto de la sucursal
|
|
sc = branch_contacts_by_id.get((loc_id, contact_id))
|
|
if not sc:
|
|
continue
|
|
|
|
client_name = f"{sc['first_name'] or ''} {sc['last_name'] or ''}".strip() or "Sin nombre"
|
|
client_phone = sc["phone"] or ""
|
|
client_email = sc["email"] or ""
|
|
|
|
# Buscar el contacto correspondiente en la Marca Principal
|
|
norm_p = normalize_phone(client_phone)
|
|
p_suffix = phone_last_digits(client_phone)
|
|
norm_client_name = normalize_text(client_name)
|
|
bc = None
|
|
if norm_p and norm_p in brand_contacts_by_phone:
|
|
bc = brand_contacts_by_phone[norm_p]
|
|
elif p_suffix and p_suffix in brand_contacts_by_phone_suffix:
|
|
bc = brand_contacts_by_phone_suffix[p_suffix]
|
|
elif client_email and client_email.lower() in brand_contacts_by_email:
|
|
bc = brand_contacts_by_email[client_email.lower()]
|
|
elif client_name and client_name.lower() in brand_contacts_by_name:
|
|
bc = brand_contacts_by_name[client_name.lower()]
|
|
elif norm_client_name and norm_client_name in brand_contacts_by_norm_name:
|
|
bc = brand_contacts_by_norm_name[norm_client_name]
|
|
|
|
if not bc:
|
|
branch_contacts_missing_in_brand += 1
|
|
per_location_stats[loc_id]["contactos_faltantes"] += 1
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"sucursal": tienda,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"tipo_alerta": "CONTACTO SUCURSAL FALTANTE EN MARCA",
|
|
"descripcion": f"El cliente existe en '{tienda}' con oportunidad '{opp_name}' pero el contacto no se ha replicado en la Marca Principal."
|
|
})
|
|
continue
|
|
|
|
# Si el contacto existe en Marca, validar si existe la oportunidad en Marca
|
|
brand_opps = brand_opps_by_contact_id.get(bc["id"], [])
|
|
|
|
synced = False
|
|
for bo in brand_opps:
|
|
if bo["id"] == so["id"]:
|
|
synced = True
|
|
break
|
|
if bo["name"] and so["name"] and normalize_text(bo["name"]) == normalize_text(so["name"]):
|
|
synced = True
|
|
break
|
|
|
|
if synced:
|
|
opps_successfully_synced += 1
|
|
per_location_stats[loc_id]["sinc"] += 1
|
|
else:
|
|
# RECONCILIACIÓN POR SIMILITUD DE CAMPOS (100% de datos de contacto coincidentes, buscando par de oportunidad mas cercano)
|
|
best_bo = None
|
|
best_score = 0
|
|
for bo in brand_opps:
|
|
score = calculate_opportunity_similarity(so, bo)
|
|
if score > best_score:
|
|
best_score = score
|
|
best_bo = bo
|
|
|
|
if best_bo and best_score >= 35:
|
|
opps_with_discrepancies += 1
|
|
per_location_stats[loc_id]["discrepancias"] += 1
|
|
disc_fields = []
|
|
if normalize_text(so["name"] or "") != normalize_text(best_bo["name"] or ""):
|
|
disc_fields.append("Nombre")
|
|
if so["status"] != best_bo["status"]:
|
|
disc_fields.append("Estado")
|
|
if abs((so["monetary_value"] or 0) - (best_bo["monetary_value"] or 0)) >= 0.01:
|
|
disc_fields.append("Valor")
|
|
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"sucursal": tienda,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"tipo_alerta": "DISCREPANCIA EN CAMPOS DE SINC",
|
|
"descripcion": f"Oportunidad encontrada en Marca '{best_bo['name']}' pero difiere con la Sucursal '{tienda}' en: {', '.join(disc_fields)} (Coincidencia: {best_score}%)."
|
|
})
|
|
else:
|
|
opps_missing_in_brand += 1
|
|
per_location_stats[loc_id]["faltantes"] += 1
|
|
discrepancies.append({
|
|
"cliente": client_name,
|
|
"sucursal": tienda,
|
|
"telefono": client_phone,
|
|
"email": client_email,
|
|
"tipo_alerta": "OPORTUNIDAD SUCURSAL NO SINCRONIZADA",
|
|
"descripcion": f"Oportunidad '{opp_name}' (ID: {so['id']}) existe en la sucursal '{tienda}' pero falta sincronizar en la Marca Principal."
|
|
})
|
|
|
|
# Imprimir reporte de alertas inversas
|
|
safe_print("=== DETALLE DE DISCREPANCIAS Y ALERTAS DETECTADAS (SUCURSAL -> MARCA) ===")
|
|
safe_print("-" * 110)
|
|
if not discrepancies:
|
|
safe_print("EXCELENTE: Todas las oportunidades de sucursales estan correctamente sincronizadas en Marca.")
|
|
else:
|
|
limit = 100
|
|
for idx, d in enumerate(discrepancies[:limit], 1):
|
|
safe_print(f"{idx:03d}. [{d['tipo_alerta']}] - Cliente: {d['cliente']}")
|
|
safe_print(f" Sucursal de Origen: {d['sucursal']}")
|
|
if d['telefono']: safe_print(f" Telefono: {d['telefono']}")
|
|
safe_print(f" Detalle: {d['descripcion']}")
|
|
safe_print("-" * 110)
|
|
|
|
if len(discrepancies) > limit:
|
|
safe_print(f"... [INFO] Se omitieron {len(discrepancies) - limit} alertas adicionales en consola para agilizar la visualizacion.")
|
|
safe_print("-" * 110)
|
|
|
|
# Imprimir resumen de la AuditorÃa Inversa
|
|
safe_print("\n=== RESUMEN EJECUTIVO DE TRAZABILIDAD INVERSA (SALUD SUCURSALES) ===")
|
|
safe_print(f" Total Oportunidades Auditadas en Sucursales: {total_branch_opps_audited}")
|
|
safe_print(f" Oportunidades Sincronizadas Correctamente: {opps_successfully_synced}")
|
|
safe_print(f" Oportunidades con Discrepancias de Datos: {opps_with_discrepancies}")
|
|
safe_print(f" Oportunidades Faltantes de Sincronizar: {opps_missing_in_brand}")
|
|
safe_print(f" Clientes de Sucursal Faltantes en Marca: {branch_contacts_missing_in_brand}")
|
|
safe_print("-" * 50)
|
|
safe_print(" Regla de Sincronizacion: La Sucursal tiene prioridad (Unidireccional Sucursal -> Marca).")
|
|
|
|
# Tabla de resumen por sucursal (location)
|
|
if per_location_stats:
|
|
safe_print("\n--- DESGLOSE POR SUCURSAL (LOCATION) ---")
|
|
col_w = 38
|
|
safe_print(f" {'Sucursal (Location)':<{col_w}} {'Total':>6} {'Sinc.':>6} {'Discr.':>6} {'Falt.':>6} {'C.Falt':>6}")
|
|
safe_print(" " + "-" * (col_w + 36))
|
|
for loc_id, s in sorted(per_location_stats.items(), key=lambda x: x[1]["nombre"]):
|
|
nombre = s["nombre"] or loc_id
|
|
if len(nombre) > col_w:
|
|
nombre = nombre[:col_w - 1] + "…"
|
|
safe_print(
|
|
f" {nombre:<{col_w}} {s['total']:>6} {s['sinc']:>6} {s['discrepancias']:>6} {s['faltantes']:>6} {s['contactos_faltantes']:>6}"
|
|
)
|
|
safe_print(" " + "-" * (col_w + 36))
|
|
|
|
safe_print("=" * 90 + "\n")
|
|
|
|
except Exception as e:
|
|
safe_print(f"Error critico durante la auditoria inversa: {e}")
|
|
finally:
|
|
conn.close()
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(description="Busca y traza las oportunidades entre la cuenta de Marca y las Sucursales usando el Verificador.")
|
|
parser.add_argument("query", nargs="*", help="Termino de busqueda (Nombre, Email, Telefono, ID de Oportunidad o Cliente). Si se omite, realiza una auditoria.")
|
|
parser.add_argument("--reverse", action="store_true", help="Realiza la auditoria de trazabilidad a la inversa (Sucursal -> Marca).")
|
|
args = parser.parse_args()
|
|
|
|
if not args.query:
|
|
if args.reverse:
|
|
run_reverse_traceability_audit()
|
|
else:
|
|
run_global_traceability_audit()
|
|
else:
|
|
query_term = " ".join(args.query)
|
|
search_brand_to_branch_opportunities(query_term)
|
|
|
|
if __name__ == "__main__":
|
|
main()
|