Metadata-Version: 2.4
Name: kpi-impact-sim
Version: 0.1.0
Summary: A business-ready engine for KPI impact simulation using Ridge Regression and interpretable feature contributions.
Author-email: "Victor Morales M." <victor@example.com>
License: MIT
Project-URL: Homepage, https://github.com/vmoralesm/kpi-impact-sim
Project-URL: Bug Tracker, https://github.com/vmoralesm/kpi-impact-sim/issues
Keywords: analytics,kpi,ridge,simulation,business
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE.txt
Requires-Dist: numpy
Requires-Dist: pandas
Requires-Dist: scikit-learn
Requires-Dist: scipy
Requires-Dist: matplotlib
Dynamic: license-file

import pyodbc
import pandas as pd
import datetime
import time
import win32com.client as win32
import warnings

warnings.filterwarnings("ignore", category=UserWarning, module="pandas.io.sql")

def consultar_y_enviar_alerta():
    try:
        ahora = datetime.datetime.now()
        print(f"\n⏳ {ahora} - Ejecutando consulta y generando alerta...")

        conn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};"
            "SERVER=172.17.248.31;"
            "DATABASE=WSP_BLINDAJE_PARTNER;"
            "Trusted_Connection=yes;"
        )


        sql = f"EXEC SP_TKSWSP @FechaInicio = '01-08-2025', @FechaFin = '01-01-2026'"

        df = pd.read_sql(sql, conn)
        df["APELLIDOS Y NOMBRES"] = df["APELLIDOS Y NOMBRES"].apply(lambda x: x.split("_", 1)[-1] if pd.notnull(x) else x)
        df['TMO Asesor'] = pd.to_datetime(df['TMO Asesor'], errors='coerce').dt.time
        df['TMO Asesor'] = pd.to_timedelta(df['TMO Asesor'].astype(str))
        df['DIA'] = df['DIA'].astype(int)

        df_filtrado = df[(df['Periodo'] == '202508') & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]

        max_dia = df_filtrado['DIA'].max()
        print(f"✔ MAX DIA_GEST encontrado:", max_dia)

        df_filtrado2 = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]

        max_tramo = df_filtrado2['HORA'].max()
        print(f"✔ MAX TRAMO encontrado:", max_tramo)

        df_filtradoH = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False)) & (df['ESTADO DE GESTIÓN'] == 'ACTIVO')]
        df_filtradoD1 = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia -1) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False)) & (df['ESTADO DE GESTIÓN'] == 'ACTIVO')]
        df_filtradoD7 = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia - 7) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False)) & (df['ESTADO DE GESTIÓN'] == 'ACTIVO')]

        df_filtradoHC = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]
        df_filtradoD1C = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia -1) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]
        df_filtradoD7C = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia - 7) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]

        df_on = df_filtradoH
        df_d7 = df_filtradoD7
        df_d1 = df_filtradoD1

        df_onC = df_filtradoHC
        df_d7C = df_filtradoD7C
        df_d1C = df_filtradoD1C

        def formatear_timedelta(td):
            if pd.isnull(td):
                return "00:00:00"
            total_seconds = int(td.total_seconds())
            horas = total_seconds // 3600
            minutos = (total_seconds % 3600) // 60
            segundos = total_seconds % 60
            return f"{horas:02}:{minutos:02}:{segundos:02}"

        def tramo_mayor_tmg(df):
            # Filtra solo los gestionados
            df_filtrado = df[df["Q Gestionado"] > 0]
            tmg_por_tramo = (
                df_filtrado.groupby("HORA")["TMO Asesor"]
                .mean()
                .sort_values(ascending=False)
            )
            if not tmg_por_tramo.empty:
                tramo = tmg_por_tramo.index[0]
                tmg = formatear_timedelta(tmg_por_tramo.iloc[0])
                return f"{tramo} ({tmg})"
            else:
                return "Sin datos"


        def asesores_tmg(df, top=True):
            orden = False if top else True
            tmg_por_asesor = (
                df[df["Q Gestionado"] > 0]
                .groupby("APELLIDOS Y NOMBRES")["TMO Asesor"]
                .mean()
                .sort_values(ascending=orden)
                .head(2)
            )
            return [
                f"{asesor} ({formatear_timedelta(tmg)})"
                for asesor, tmg in tmg_por_asesor.items()
            ]


        TMG_actual = df_on["TMO Asesor"].mean()
        tramo_TMG_critico = tramo_mayor_tmg(df_on)
        asesores_mayor_TMG = asesores_tmg(df_on, top=True)
        asesores_menor_TMG = asesores_tmg(df_on, top=False)

        def calcular_indicador(df, numerador, denominador, objetivo):
            total = df[denominador].sum()
            actual = (df[numerador].sum() / total * 100) if total else 0
            alerta = "Alta ✅" if actual >= objetivo else "Baja ⚠️"
            return actual, alerta

        contact_actual, contact_alerta = calcular_indicador(df_onC, "Q Atendido", "Q Tickets", 20)
        efectivo_actual, efectivo_alerta = calcular_indicador(df_onC, "Q Contacto Efectivo", "Q Gestionado", 40)
        retencion_actual, retencion_alerta = calcular_indicador(df_onC, "Q Retenido", "Q Gestionado", 20)

        def peor_tramo(df, col):
            tramo_porc = (
                df.groupby("HORA")[[col, "Q Gestionado"]]
                .sum()
                .query("`Q Gestionado` > 1")
                .assign(porc=lambda x: x[col] / x["Q Gestionado"] * 100)
                .sort_values("porc")
            )

            if not tramo_porc.empty:
                peor_tramo = tramo_porc.index[0]
                valor = tramo_porc.iloc[0]["porc"]
                return f"{peor_tramo} ({valor:.2f}%)"
            else:
                return "Sin datos"

        tramo_contact_critico = peor_tramo(df_onC, "Q Atendido")
        tramo_efectivo_critico = peor_tramo(df_on, "Q Contacto Efectivo")
        tramo_retencion_critico = peor_tramo(df_on, "Q Retenido")

        def peores_asesores(df, col):
            asesores = (
                df.groupby("APELLIDOS Y NOMBRES")[[col, "Q Gestionado"]]
                .sum()
                .query("`Q Gestionado` > 1")
                .assign(porc=lambda x: x[col] / x["Q Gestionado"] * 100)
                .sort_values("porc")
                .head(2)
            )

            return [
                f"{asesor} ({row['porc']:.2f}%)"
                for asesor, row in asesores.iterrows()
            ]

        peores_efectivo = peores_asesores(df_on, "Q Contacto Efectivo")
        peores_retencion = peores_asesores(df_on, "Q Retenido")

        def calcular_kpis(df):
            base = df["Q Tickets"].sum()
            gestionado = df["Q Gestionado"].sum()
            contacto = df["Q Atendido"].sum()
            contacto_efectivo = df["Q Contacto Efectivo"].sum()
            contacto_noefectivo = df["Q Contacto No Efectivo"].sum()
            no_conforme = df["Q No Conforme"].sum()
            conforme = df["Q Conforme"].sum()
            retenido = df["Q Retenido"].sum()
            no_retenido = df["Q No Retenido"].sum()
            df_gestionados = df[df["Q Gestionado"] > 0]
            tmg = df_gestionados["TMO Asesor"].mean()

            return {
                "Base": base,
                "Gestionado": gestionado,
                "Contacto Efectivo": contacto_efectivo,
                "Contacto no Efectivo": contacto_noefectivo,
                "No Conforme": no_conforme,
                "Conforme": conforme,
                "Retenido": retenido,
                "No Retenido": no_retenido,
                "% Contacto / Gestionado": (contacto / base * 100) if gestionado else 0,
                "% Contacto Efectivo / Gestionado": (contacto_efectivo / gestionado * 100) if gestionado else 0,
                "% Contacto No Efectivo / Gestionado": (contacto_noefectivo / gestionado * 100) if gestionado else 0,
                "% No Retenido / Gestionado": (no_retenido / gestionado * 100) if gestionado else 0,
                "% Efectividad / CE": (retenido / contacto_efectivo * 100) if contacto_efectivo else 0,
                "% Efectividad / Gestionado": (retenido / gestionado * 100) if gestionado else 0,
                "Tmg": tmg
            }

        def formatear_timedelta(td):
            if pd.isnull(td):
                return "00:00:00"
            total_seconds = int(td.total_seconds())
            horas = total_seconds // 3600
            minutos = (total_seconds % 3600) // 60
            segundos = total_seconds % 60
            return f"{horas:02}:{minutos:02}:{segundos:02}"

        kpis_on = calcular_kpis(df_onC)
        kpis_d7 = calcular_kpis(df_d7C)
        kpis_d1 = calcular_kpis(df_d1C)

        table_rows = ""
        for kpi, value_on in kpis_on.items():
            value_d7 = kpis_d7[kpi]
            value_d1 = kpis_d1[kpi]
            var_d7 = value_d7
            var_d1 = value_d1
            vs_d1 = value_on - value_d1
            vs_d7 = value_on - value_d7
            if kpi == "Tmg":
                table_rows += f"""
                <tr>
                    <td>{kpi}</td>
                    <td style='text-align:center;'>{formatear_timedelta(value_on)}</td>
                    <td style='text-align:center;'>{formatear_timedelta(value_d1)}</td>
                    <td style='text-align:center;'>{formatear_timedelta(value_d7)}</td>
                    <td style='text-align:center; color: #2E86C1;'>{formatear_timedelta(vs_d1)}</td>
                    <td style='text-align:center; color: #2E86C1;'>{formatear_timedelta(vs_d7)}</td>
                </tr>
                """
            elif "%" in kpi:
                table_rows += f"""
                <tr>
                    <td>{kpi}</td>
                    <td style='text-align:center;'>{value_on:.2f}%</td>
                    <td style='text-align:center;'>{var_d1:.2f}%</td>
                    <td style='text-align:center;'>{var_d7:.2f}%</td>
                    <td style='text-align:center; color: #2E86C1;'>{vs_d1:.2f}%</td>
                    <td style='text-align:center; color: #2E86C1;'>{vs_d7:.2f}%</td>
                </tr>
                """
            else:
                table_rows += f"""
                <tr>
                    <td>{kpi}</td>
                    <td style='text-align:center;'>{value_on:.0f}</td>
                    <td style='text-align:center;'>{var_d1:.0f}</td>
                    <td style='text-align:center;'>{var_d7:.0f}</td>
                    <td style='text-align:center; color: #2E86C1;'>{vs_d1:.0f}</td>
                    <td style='text-align:center; color: #2E86C1;'>{vs_d7:.0f}</td>
                </tr>
                """

        html_table = f"""
        <table style="border-collapse: collapse; width: 60%; font-size: 11px; font-family: Arial, sans-serif;">
        <tr style="background-color: #D6EAF8;">
        <th style="text-align: center;">KPI's</th>
        <th style="text-align: center;">ON<br>{max_dia}</th>
        <th style="text-align: center;">Var(+/-)<br>D-1</th>
        <th style="text-align: center;">Var(+/-)<br>D-7</th>
        <th style="text-align: center; color: #2E86C1;">Var(+/-)<br>Vs D-1</th>
        <th style="text-align: center; color: #2E86C1;">Var(+/-)<br>Vs D-7</th>
        </tr>
        {table_rows}
        </table>
        """


        html_body = f"""
        <h2 style="color:#2E86C1;">WSP CONTACTADOS BITEL</h2>


        <!-- Alerta Contactabilidad -->
        <div style="border:1px solid #dcdcdc; border-left: 6px solid #e67e22; background-color: #fcf3e6; padding:10px; margin-bottom:15px;">
            <h3 style="color:#e67e22;">Alerta | Contactabilidad: {contact_alerta}</h3>
            <p>🎯 <b>Objetivo:</b> 20%</p>
            <p>📈 <b>Actual:</b> {contact_actual:.2f}%</p>
        </div>

        <!-- Alerta TMG -->
        <div style="border:1px solid #dcdcdc; border-left: 6px solid #2E86C1; background-color: #f4faff; padding:10px; margin-bottom:15px;">
            <h3 style="color:#2E86C1;">Alerta | TMG</h3>
            <p>📈 <b>Actual:</b> {formatear_timedelta(TMG_actual)}</p>
            <p>🕑 <b>Tramo crítico:</b> {tramo_TMG_critico}</p>
            <p>👥 <b>Asesores con mayor TMG:</b> {', '.join(asesores_mayor_TMG)}</p>
            <p>👥 <b>Asesores con menor TMG:</b> {', '.join(asesores_menor_TMG)}</p>
        </div>

        <!-- Alerta Contactos Efectivos -->
        <div style="border:1px solid #dcdcdc; border-left: 6px solid #28B463; background-color: #eafaf1; padding:10px; margin-bottom:15px;">
            <h3 style="color:#28B463;">Alerta | Contactos Efectivos: {efectivo_alerta}</h3>
            <p>🎯 <b>Objetivo:</b> 40%</p>
            <p>📈 <b>Actual:</b> {efectivo_actual:.2f}%</p>
            <p>🕑 <b>Tramo crítico:</b> {tramo_efectivo_critico}</p>
            <p>👥 <b>Peores asesores:</b> {', '.join(peores_efectivo)}</p>
        </div>

        <!-- Alerta Retención -->
        <div style="border:1px solid #dcdcdc; border-left: 6px solid #C0392B; background-color: #fdecea; padding:10px; margin-bottom:15px;">
            <h3 style="color:#C0392B;">Alerta | Retención: {retencion_alerta}</h3>
            <p>🎯 <b>Objetivo:</b> 20%</p>
            <p>📈 <b>Actual:</b> {retencion_actual:.2f}%</p>
            <p>🕑 <b>Tramo crítico:</b> {tramo_retencion_critico}</p>
            <p>👥 <b>Peores asesores:</b> {', '.join(peores_retencion)}</p>
        </div>

        <hr>
        {html_table}
        """


        outlook = win32.Dispatch('outlook.application')
        mail = outlook.CreateItem(0)
        mail.To = "diego.vivanco@claro.com.pe"
        mail.CC = "jeancarlos.ramos@claro.com.pe; vmorales@partner.pe; ldoroteo@partner.pe; jtapia@partner.pe; ofernandez@partner.pe; eserrano@partner.pe; pcarrillo@partner.pe"
        mail.BCC = "jpalomino@partner.pe; ldelaguila@partner.pe; ycadillo@partner.pe; lpaz@partner.pe; nleonardo@partner.pe; wore@partner.pe; jguevara@partner.pe; cabad@partner.pe"
        mail.Subject = f"CONTROL KPIs- WSP CONTACTADOS BITEL - PARTNER (hasta Tramo {max_tramo})"
        mail.HTMLBody = html_body
        mail.Send()
        print("📧 Correo con alertas y tabla KPI’s enviado ✅")


    except Exception as e:
        print("❌ Error al ejecutar la alerta:", e)

        del df, df_filtrado, df_filtrado2, df_filtradoH, df_filtradoD1, df_filtradoD7, df_on, df_d7, df_d1
        gc.collect()

while True:
    ahora = datetime.datetime.now()
    hora_actual = ahora.hour

    if 11 <= hora_actual < 22:
        consultar_y_enviar_alerta()
        print("⏳ Esperando 1 hora para la próxima alerta...")
        time.sleep(60 * 60)
    else:
        print("⏸ Fuera del horario de envío (10:00-22:00). Esperando 10 minutos...")
        time.sleep(600)
