app.py

# Import package and module
import gradio as gr
import pandas as pd
from transformers import pipeline
from g4f.client import Client


# /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


# Initialize GPT-4 Client
client = Client()


# Function for answering questions using GPT-4
def process_notes(notes):
    questions = [
        "Sebutkan Kendala Customer?   (Contoh jawaban yang benar -> sinyal inet tidak stabil, all app, 1-2 bar E, in/outdoor sama, no ybs saja)", # detail_complain
        "Siapa Nama Customer?   (Contoh jawaban yang benar -> Damelia)", # customer_name
        "Berapa Nomor MSISDN Yang Bermasalah:   (Contoh jawaban yang benar -> 6281298765432)", # msisdn
        "Kapan Tanggal/Jam Kejadian?    (Contoh jawaban yang benar -> 20/12/2024 11:24 - 15:24)", # tanggal_kejadian
        "Dimana Lokasi Pelanggan (alamat)?    (Contoh jawaban yang benar -> Jl. Bambu Kuning No.33, Bojonggede, Kecamatan Bojonggede, Kabupaten Bogor, Jawa Barat 16922)", # location
        # "Apakah mode jaringan (4G/3G/2G)?", # mode_jaringan
        # "Apakah Tier pelanggan (Silver/Gold/Platinum/Diamond)?", # tipe_pelanggan
    ]

    answers = []
    for q in questions:
      prompt = f"[Language: Bahasa Indonesia] Jawab singkat dan langsung: {q} {notes}"
      response = client.chat.completions.create(model="gpt-4o-mini", messages=[{"role": "user", "content": prompt}])
      answers.append(response.choices[0].message.content.strip())
    return answers



# Function for generating report
def generate_report(no_ticket, detail_complain, customer_name, msisdn, tanggal_kejadian, remedy_submit_time, severity,
                    location, long_lat, mode_jaringan, kategori_keluhan, tipe_pelanggan, device, profile_check,
                    imsi_bcp_choice, site_name, availability, alarm_impact, accessibility_issue, high_prb_dl, high_prb_ul,
                    high_max_user, active_user_max, high_ul_interference, transport_issue_pl, transport_issue_tnl,
                    status, av_status, orbit_byu, rsrp_rsrq, hc_site, suspect_issue, action_plan):

    # Report format
    report = f"""
    No. Ticket: {no_ticket}
    Detail Complaint: {detail_complain}
    Nama: {customer_name}
    MSISDN: {msisdn}
    Tanggal Kejadian: {tanggal_kejadian}
    Remedy Submit Time: {remedy_submit_time}
    Severity: {severity}
    ------------------
    Location: {location}
    Long Lat: {long_lat} (perkiraan lokasi pelanggan)
    ------------------
    Mode Jaringan: {mode_jaringan}
    Kategori Keluhan: {kategori_keluhan}
    Tipe Pelanggan: {tipe_pelanggan}
    SIM Capability: USIM
    Device: {device}
    ------------------
    1. Profile Check:
    {profile_check}
    ------------------
    2. Healty Check:
    #4G: {site_name}
    - Availability: {availability}
    - Alarm impact service: {alarm_impact}
    - Accessibility issue: {accessibility_issue}
    - High PRB DL (>90%): {high_prb_dl}
    - High PRB UL (>90%): {high_prb_ul}
    - High Max user (>150): {high_max_user}
    - Active User Max (>20): {active_user_max}
    - High Ul Interference (>-100): {high_ul_interference}
    - Transport issue based on PL (>0.1%): {transport_issue_pl}
    - Transport issue based on TNL (>500): {transport_issue_tnl}
    ------------------
    3. Parameter Check:
    - {site_name}, Status: {status}, Av. Status: {av_status}
    - All Parameter Standard and Proper
    ------------------
    4. Finding:
    - Pelanggan {orbit_byu}HVC {tipe_pelanggan} berkendala {detail_complain}. Sejak {tanggal_kejadian}.
    - The Nearest site is {site_name}
    {imsi_bcp(imsi_bcp_choice, site_name)}
    - {rsrp_rsrq}
    - Healthy Check site {site_name} {hc_site}
    ------------------
    5. Suspect Issue :
    - {suspect_issue}
    ------------------
    6. Action Plan:
    - {action_plan}
    ------------------
    Terima kasih
    """
    # Remove extra spaces in the output
    return "\n".join([line.strip() for line in report.splitlines() if line.strip()])



# Function for determining IMSI or BCP
def imsi_bcp(imsi_bcp_choice, site_name):
    # Simplified conditional return
    return (
        f"- IMSI trace was Covered By {site_name}" if imsi_bcp_choice == "IMSI" else
        f"- IMSI trace no record\n- BCP trace was Covered By {site_name}" if imsi_bcp_choice == "BCP (IMSI no record)" else
        f"- IMSI trace was Covered By {site_name}\n- BCP trace was Covered By {site_name}" if imsi_bcp_choice == "IMSI & BCP" else ""
    )



# Interface for tab 1
with gr.Blocks() as tab1:
    # Input for notes
    notes = gr.Textbox(label="Notes", max_lines = 5)
    # Submit button for notes
    with gr.Row():
        reset1 = gr.ClearButton(components=[notes], value="Reset")
        submit1 = gr.Button("Submit Notes", variant="primary")

    # Input for generate report
    with gr.Row():
        with gr.Column():
            no_ticket = gr.Textbox(label="No. Ticket", placeholder="INCxxx", lines=1)
            detail_complain = gr.Textbox(label="Detail Complaint", placeholder="Internet Lambat")
            customer_name = gr.Textbox(label="Nama", placeholder="Damelia", lines=1)
            msisdn = gr.Textbox(label="MSISDN", placeholder="62xxx", lines=1)
            tanggal_kejadian = gr.Textbox(label="Tanggal Kejadian", placeholder="1 Januari 2001", lines=1)
            remedy_submit_time = gr.Textbox(label="Remedy Submit Time", placeholder="01/01/2001 01:00:00 AM", lines=1)
            severity = gr.Radio(["Low", "Medium", "High"], label="Severity", value="Low")
            orbit_byu = gr.Radio(["", "ORBIT ", "BYU "], label="ORBIT / BYU", value="")

        with gr.Column():
            location = gr.Textbox(label="Location", placeholder="Jalan xxx No.1 RT/RW")
            long_lat = gr.Textbox(label="Long Lat", placeholder="-6.123456, 106.123456", lines=1)
            mode_jaringan = gr.Radio(["4G", "3G", "2G"], label="Mode Jaringan", value="4G")
            kategori_keluhan = gr.Radio(["Data", "Coverage", "Voice"], label="Kategori Keluhan", value="Data")
            tipe_pelanggan = gr.Radio(["Silver", "Gold", "Platinum", "Diamond"], label="Tipe Pelanggan", value="Gold")
            device = gr.Textbox(label="Device", placeholder="iPhone 15 Pro Max", value="-")
            profile_check = gr.Textbox(label="Profile Check", value="-", max_lines=5)

    with gr.Row():
        with gr.Column():
            imsi_bcp_choice = gr.Radio(["IMSI", "BCP (IMSI no record)", "IMSI & BCP"], label="Trace dari IMSI / BCP", value="IMSI")
            site_name = gr.Textbox(label="Site Name", placeholder="X_JXX000MXX_Nama-DXX_MX00")
            status = gr.Radio(["Enable", "Disable", "Unknown"], label="Status", value="Enable")
            av_status = gr.Radio(["Normal", "Invalid", "Unknown"], label="Av. Status", value="Normal")
            rsrp_rsrq = gr.Textbox(label="RSRP & RSRQ", value="RSRP -106 dBm & RSRQ -12 dB")

        with gr.Column():
            availability = gr.Radio(["SAFE", "NOT SAFE"], label="Availability", value="SAFE")
            alarm_impact = gr.Radio(["SAFE", "NOT SAFE"], label="Alarm Impact", value="SAFE")
            accessibility_issue = gr.Radio(["SAFE", "NOT SAFE"], label="Accessibility Issue", value="SAFE")
            high_prb_dl = gr.Radio(["SAFE", "NOT SAFE"], label="High PRB DL (>90%)", value="SAFE")
            high_prb_ul = gr.Radio(["SAFE", "NOT SAFE"], label="High PRB UL (>90%)", value="SAFE")
            high_max_user = gr.Radio(["SAFE", "NOT SAFE"], label="High Max User (>150)", value="SAFE")

        with gr.Column():
            active_user_max = gr.Radio(["SAFE", "NOT SAFE"], label="Active User Max (>20)", value="SAFE")
            high_ul_interference = gr.Radio(["SAFE", "NOT SAFE"], label="High UL Interference (>-100)", value="SAFE")
            transport_issue_pl = gr.Radio(["SAFE", "NOT SAFE"], label="Transport Issue PL (>0.1%)", value="SAFE")
            transport_issue_tnl = gr.Radio(["SAFE", "NOT SAFE"], label="Transport Issue TNL (>500)", value="SAFE")
            hc_site = gr.Textbox(label="HC Site", value="Safe")

    with gr.Row():
        suspect_issue = gr.Dropdown(["Low coverage", "Quality Problem", "No Dominant Coverage", "High PRB", "High Max User", "High Active User", "Availability Problem", "Comcase", "Module Stolen", "Site Dismantle", "Blacksite", "Force Majeure", "Trans Alarm", "Trans Packet Loss", "Trans TNL", "High UL Interference", "High VSWR", "Alarm Problem", "Performance Problem", "Profile Problem", "Simcard Problem", "UE Problem", "IT Problem", "Core Problem", "Others"], label="Suspect Issue", value="Low coverage")
        action_plan = gr.Dropdown(["Optimasi", "Troubleshoot", "User Education", "Quality (Network Normal)", "Recovery", "Percepatan Relokasi", "Reengineering", "Escalated to HQ"], label="Action Plan", value="Optimasi")

    # Inputs list of generate report
    fields_generate_report = [no_ticket, detail_complain, customer_name, msisdn, tanggal_kejadian, remedy_submit_time, severity,
                              location, long_lat, mode_jaringan, kategori_keluhan, tipe_pelanggan, device, profile_check,
                              imsi_bcp_choice, site_name, availability, alarm_impact, accessibility_issue, high_prb_dl, high_prb_ul,
                              high_max_user, active_user_max, high_ul_interference, transport_issue_pl, transport_issue_tnl,
                              status, av_status, orbit_byu, rsrp_rsrq, hc_site, suspect_issue, action_plan]

    # Submit button for generate report
    with gr.Row():
        reset2 = gr.ClearButton(components=[*fields_generate_report], value="Reset")
        submit2 = gr.Button("Generate Report", variant="primary")

    # For showing the output
    output = gr.Textbox(label="Report", interactive=True, lines=0)

    # Function for PPT
    download_button = gr.File(label="Download Template PPT", value="template.pptx")

    # Process button for notes using GPT-4
    submit1.click(process_notes, inputs=notes, outputs=[detail_complain, customer_name, msisdn, tanggal_kejadian, location])
    # submit1.click(process_notes, inputs=notes, outputs=[detail_complain, customer_name, msisdn, tanggal_kejadian, location, mode_jaringan, tipe_pelanggan])
    # Process button for generating report
    submit2.click(generate_report, inputs=fields_generate_report, outputs=output)


# /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


# Function for creating HTML imsi table
def imsi_df_style(df, table_id="imsi-table"):
    # Convert DataFrame to HTML table with custom classes
    html = df.to_html(classes='table table-bordered',
                      border=0,
                      index=False,
                      table_id=table_id)

    # Custom CSS for the table
    custom_css = """
    <style>
    body {
        font-family: 'Calibri', sans-serif;
        font-size: 5px;
    }
    #table-container {
        max-height: 600px;
        overflow-y: auto;
        position: relative;
    }
    #imsi-table {
        width: 100%;
        border-collapse: collapse;
    }
    #imsi-table th {
        position: sticky;
        top: 0;
        background-color: #FFFF00;
        color: black;
        padding: 2px;
        font-weight: normal;
        border: 1px solid #d6d6d6;
        z-index: 1;
    }
    #imsi-table td {
        background-color: white;
        color: black;
        padding: 2px;
        border: 1px solid #d6d6d6;
    }
    </style>
    """

    # Wrap the table in a container
    wrapped_html = f'<div id="table-container">{html}</div>'
    return custom_css + wrapped_html


# Function for tab 2: IMSI Trace
def imsi_trace(file):
    # Load IMSI data
    imsi = pd.read_csv(file.name).sort_values('Start Time', ascending=False)

    # Group and calculate statistics
    count_df = imsi.groupby('Start Cell Name').size().reset_index(name='Count of Start Cell Name').sort_values(by='Count of Start Cell Name', ascending=False)
    avg_df = imsi.groupby('Start Cell Name')[['Start RxLev/RSCP/RSRP (dBm)', 'Start RxQual/ECN0/RSRQ (dB)']].mean().reset_index()
    result_df = pd.merge(count_df, avg_df, on='Start Cell Name', how='inner')

    # Select columns for filtered data
    filtered_imsi = imsi[['Start Time', 'MSISDN', 'Technology', 'Call Type', 'Start Cell Name', 'Start Latitude', 'Start Longtitude', 'Start RxLev/RSCP/RSRP (dBm)', 'Start RxQual/ECN0/RSRQ (dB)']]

    # Write to Excel with colored headers
    writer = pd.ExcelWriter('hasil_imsi.xlsx', engine='xlsxwriter')
    filtered_imsi.to_excel(writer, sheet_name='Data IMSI', index=False)
    workbook = writer.book
    worksheet = writer.sheets['Data IMSI']

    # Define header format
    header_format = workbook.add_format({'valign': 'center', 'fg_color': '#FFFF00', 'font_color': "black", 'border': 1})

    # Apply header format using list comprehension
    [worksheet.write(0, col_num, value, header_format) for col_num, value in enumerate(filtered_imsi.columns)]

    writer.close()

    # Convert to HTML table with custom CSS
    imsi_output = imsi_df_style(filtered_imsi)

    # styler = IMSIReportStyler(filtered_imsi)
    # imsi_output = styler.style_report()

    return result_df, imsi_output, "hasil_imsi.xlsx"



# Interface for tab 2
with gr.Blocks() as tab2:
    with gr.Row():
        with gr.Column(scale=1/4):
            file_input = gr.File(label="Upload CSV")
            with gr.Row():
                submit_btn = gr.Button("Submit", variant="primary")
                reset_btn = gr.ClearButton([file_input], value="Reset")
            dw_filtered_imsi = gr.File(label="Download Filtered IMSI")
        with gr.Column(scale=3/4):
            pivoted_imsi = gr.Dataframe(label="Pivoted Data")
    filtered_imsi = gr.HTML(label="Filtered Data")

    submit_btn.click(fn=imsi_trace, inputs=file_input, outputs=[pivoted_imsi, filtered_imsi, dw_filtered_imsi])


# /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


# Function for creating HTML imsi table
def bcp_df_style(df, table_id="bcp-table"):
    # Convert DataFrame to HTML table with custom classes
    html = df.to_html(classes='table table-bordered',
                      border=0,
                      index=False,
                      table_id=table_id)

    # Custom CSS for the table
    custom_css = """
    <style>
    body {
        font-family: 'Calibri', sans-serif;
        font-size: 5px;
    }
    #table-container {
        max-height: 600px;
        overflow-y: auto;
        position: relative;
    }
    #bcp-table {
        width: 100%;
        border-collapse: collapse;
    }
    #bcp-table th {
        position: sticky;
        top: 0;
        background-color: #2f75b5;
        color: white;
        padding: 2px;
        font-weight: bold;
        border: 1px solid #d6d6d6;
        z-index: 1;
        text-align: center;
    }
    #bcp-table th:nth-last-child(2),
    #bcp-table th:nth-last-child(1) {
        background-color: #ffd966;
    }
    #bcp-table td {
        background-color: white;
        color: black;
        padding: 2px;
        border: 1px solid #d6d6d6;
        white-space: nowrap;
        overflow: hidden;
    }
    #bcp-table td:nth-child(n+3):nth-child(-n+12) {
      overflow: hidden;
      text-overflow: ellipsis;
      max-width: 120px;
    }
    </style>
    """

    # Wrap the table in a container
    wrapped_html = f'<div id="table-container">{html}</div>'
    return custom_css + wrapped_html



# Function for tab 3: BCP Trace
def bcp_trace(file):
    # Load site list and BCP data
    bcp_site_list = pd.read_excel("Site List.xlsx", sheet_name="Site List")
    # bcp = pd.read_csv(file.name, sep=",").iloc[:, :-1].sort_values('Timestamp ', ascending=False)
    bcp = pd.read_csv(file.name, sep=",").sort_values('Timestamp ', ascending=False)
    bcp['RAT '] = bcp['RAT '].replace('5G-NSA', '4G')

    # Calculate LAC-CI
    bcp['LAC-CI'] = bcp.apply(lambda row: int(row['CGI '][11:17] + row['CGI '][-2:]) if row['RAT '] == '4G' else int(row['CGI '][-9:]), axis=1)

    # Map site names
    dict_site_name = bcp_site_list.set_index('LACCI')['cellname'].to_dict()
    bcp['SITE NAME'] = bcp['LAC-CI'].map(dict_site_name)

    # Select and sort columns
    bcp = bcp[['Timestamp ', 'Source ', 'CGI ', 'RAT ', 'Village ', 'LAC ', 'CI/ECI ', 'City ', 'Province ', 'Region ', 'Geolocation ', 'Country ', 'LAC-CI', 'SITE NAME']]

    # Write to Excel with colored headers
    writer = pd.ExcelWriter('hasil_bcp.xlsx', engine='xlsxwriter')
    bcp.to_excel(writer, sheet_name='Data BCP', index=False)
    workbook = writer.book
    worksheet = writer.sheets['Data BCP']

    # Define header colors
    header_format_1 = workbook.add_format({'bold': True, 'valign': 'center', 'fg_color': '#2f75b5', 'font_color': "#FFFFFF", 'border': 1})
    header_format_2 = workbook.add_format({'bold': True, 'valign': 'center', 'fg_color': '#ffd966', 'font_color': "#FFFFFF", 'border': 1})

    # Apply header colors using list comprehension
    [worksheet.write(0, col_num, value, header_format_1 if value in ['Timestamp ', 'Source ', 'CGI ', 'RAT ', 'Village ', 'LAC ', 'CI/ECI ', 'City ', 'Province ', 'Region ', 'Geolocation ', 'Country '] else header_format_2) for col_num, value in enumerate(bcp.columns)]

    writer.close()

    # Group and count site names
    count_df = bcp.groupby('SITE NAME').size().reset_index(name='Count of SITE NAME').sort_values(by='Count of SITE NAME', ascending=False)

    # Convert to HTML table with custom CSS
    bcp_output = bcp_df_style(bcp)

    return count_df, bcp_output, "hasil_bcp.xlsx"



# Interface for tab 3
with gr.Blocks() as tab3:
    with gr.Row():
        with gr.Column(scale=1/4):
            file_input = gr.File(label="Upload CSV")
            with gr.Row():
                submit_btn = gr.Button("Submit", variant="primary")
                reset_btn = gr.ClearButton([file_input], value="Reset")
            dw_filtered_bcp = gr.File(label="Download Filtered BCP")
        with gr.Column(scale=3/4):
            pivoted_bcp = gr.Dataframe(label="Pivoted Data")
    filtered_bcp = gr.HTML(label="Filtered Data")

    submit_btn.click(fn=bcp_trace, inputs=file_input, outputs=[pivoted_bcp, filtered_bcp, dw_filtered_bcp])


# /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


# Fuction for VIP and Non-Remedy


# /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


# Combine all tabs
demo = gr.TabbedInterface(
          [tab1, tab2, tab3],
          ["Generate Report", "IMSI Trace", "BCP Trace"],
          # theme=gr.themes.Glass(),
        )
demo.launch()