# 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 = """ """ # Wrap the table in a container wrapped_html = f'
{html}
' 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 = """ """ # Wrap the table in a container wrapped_html = f'
{html}
' 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()