Spaces:
Sleeping
Sleeping
| # 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() | |