import streamlit as st import pandas as pd from docxtpl import DocxTemplate, InlineImage from docx.shared import Mm, Inches import datetime from datetime import timedelta, date import io # from utils import * ########## Title for the Web App ########## st.title("Report Generator") ########## Create Input field ########## # feedback = st.text_input('Type your text here', 'Customer suggested that the customer service needs to be improved and the response time needs to be improved.') # if st.button('Click for predictions!'): # with st.spinner('Generating predictions...'): # topics_prob, sentiment_prob, touchpoint_prob = get_single_prediction(feedback) # bar_topic = px.bar(topics_prob, x='probability', y='topic') # bar_touchpoint = px.bar(touchpoint_prob, x='probability', y='touchpoint') # pie = px.pie(sentiment_prob, # values='probability', # names='sentiment', # color_discrete_map={'positive':'rgb(0, 204, 0)', # 'negative':'rgb(215, 11, 11)' # }, # color='sentiment' # ) # st.plotly_chart(bar_topic, use_container_width=True) # st.plotly_chart(bar_touchpoint, use_container_width=True) # st.plotly_chart(pie, use_container_width=True) # st.write("\n") # st.subheader('Or... Upload a csv file if you have a file instead.') # st.write("\n") # st.download_button( # label="Download sample file here", # data=sample_file, # file_name='sample_data.csv', # mime='text/csv', # ) uploaded_files = st.file_uploader("Upload multiple files", accept_multiple_files=True) if len(uploaded_files) > 0: # with st.spinner('Generating report...'): for uploaded_file in uploaded_files: if uploaded_file.name == 'Flip_accum.xlsx': flip_accum1 = pd.read_excel(uploaded_file, skiprows=8, nrows=11, usecols="A:D") flip_accum2 = pd.read_excel(uploaded_file, skiprows=24, nrows=5, usecols="A:N") #st.write('flip_accum1: ' + str(flip_accum1.shape)) elif uploaded_file.name == 'Fold_accum.xlsx': fold_accum1 = pd.read_excel(uploaded_file, skiprows=8, nrows=11, usecols="A:D") fold_accum2 = pd.read_excel(uploaded_file, skiprows=24, nrows=5, usecols="A:N") #st.write('fold_accum1: ' + str(fold_accum1.shape)) elif uploaded_file.name == 'Flip_today.xlsx': flip_today1 = pd.read_excel(uploaded_file, skiprows=8, nrows=11, usecols="A:D") flip_today2 = pd.read_excel(uploaded_file, skiprows=24, nrows=5, usecols="A:M") #st.write('flip_today1: ' + str(flip_today1.shape)) elif uploaded_file.name == 'Fold_today.xlsx': fold_today1 = pd.read_excel(uploaded_file, skiprows=8, nrows=11, usecols="A:D") fold_today2 = pd.read_excel(uploaded_file, skiprows=24, nrows=5, usecols="A:M") #st.write('fold_today1: ' + str(fold_today1.shape)) elif uploaded_file.name == 'FlipFold4_accum.xlsx': flipfold_accum = pd.read_excel(uploaded_file, skiprows=8, nrows=11, usecols="A:D") flipfold_accum2 = pd.read_excel(uploaded_file, skiprows=24, nrows=5, usecols="A:N") #st.write('flipfold_accum2: ' + str(flipfold_accum2.shape)) elif uploaded_file.name == 'FlipFold4_analysis.xlsx': flipfold = pd.read_excel(uploaded_file, skiprows=9) #st.write('flipfold: ' + str(flipfold.shape)) elif uploaded_file.name == 'flipfold4_report_template.docx': doc = DocxTemplate(uploaded_file) if datetime.datetime.now().day == 1: day_suffix = "st" elif datetime.datetime.now().day == 2: day_suffix = "nd" elif datetime.datetime.now().day == 3: day_suffix = "rd" else: day_suffix = "th" if round(((flipfold_accum2.iloc[2, 4] - flipfold_accum2.iloc[2, 13])/flipfold_accum2.iloc[2, 13]) * 100) < 0: increase_decrease = "Decrease" else: increase_decrease = "Increase" flipfold = flipfold[['Symptom\nGroup 1', 'Subsidiary', 'Marketing Name']] flipfold.columns = ['symptom', 'subsidiary', 'Marketing Name'] display = ['Display', 'Touch', 'OCTA/Backglass Broken', 'Sensor'] quick_discharge = ['Quick Discharge', 'Charging', 'Discharging'] appearance = ['Appearance', 'Case', 'Button'] others = ['In Process', 'WIFI', 'Connection', 'S pen', 'Fault Operation', 'Bluetooth'] flipfold['symptom'] = flipfold['symptom'].apply(lambda x: 'Display' if x in display else 'Quick Discharge' if x in quick_discharge else 'Appearance' if x in appearance else 'Others' if x in others else 'Sound/Call Audio' if x == 'Sound/Call audio' else x ) template = pd.DataFrame({ 'symptom': ['Total', 'Heat', 'Display', 'Camera', 'Quick Discharge', 'Power', 'Rebooting', 'App/SW', 'Sound/Call Audio', 'Appearance', 'Others'], 'SEAO Total': [0]*11, 'SAVINA': [0]*11, 'SEAU': [0]*11, 'SEIN': [0]*11, 'SENZ': [0]*11, 'SEPCO': [0]*11, 'SESP': [0]*11, 'SME': [0]*11, 'TSE': [0]*11 }).set_index('symptom') flip4 = flipfold[flipfold['Marketing Name'] == 'Galaxy Z Flip4'] flip4_groupby = pd.DataFrame(flip4.groupby(['symptom', 'subsidiary'])['subsidiary'].count()) flip4_groupby.columns=['count'] flip4_groupby.reset_index(inplace=True) flip4_groupby = flip4_groupby.pivot(index='symptom', columns='subsidiary', values='count').fillna(0) fold4 = flipfold[flipfold['Marketing Name'] == 'Galaxy Z Fold4'] fold4_groupby = pd.DataFrame(fold4.groupby(['symptom', 'subsidiary'])['subsidiary'].count()) fold4_groupby.columns=['count'] fold4_groupby.reset_index(inplace=True) fold4_groupby = fold4_groupby.pivot(index='symptom', columns='subsidiary', values='count').fillna(0) template_flip4 = template.copy() template_fold4 = template.copy() for col in template.columns: for row in template.index: try: template_flip4.loc[row, col] = flip4_groupby.loc[row, col] except: continue for col in template.columns: for row in template.index: try: template_fold4.loc[row, col] = fold4_groupby.loc[row, col] except: continue # Account for SEPCO data entry error template_flip4.loc['Display', 'SEPCO'] = template_flip4.loc['Display', 'SEPCO'] - 4 template_flip4.loc['App/SW', 'SEPCO'] = template_flip4.loc['App/SW', 'SEPCO'] - 2 template_flip4.loc['Others', 'SEPCO'] = template_flip4.loc['Others', 'SEPCO'] + 6 # Account for SEVT into SAVINA count template_fold4.loc['Display', 'SAVINA'] = template_fold4.loc['Display', 'SAVINA'] + 5 template_fold4.loc['Others', 'SAVINA'] = template_fold4.loc['Others', 'SAVINA'] + 2 template_fold4.loc['Rebooting', 'SAVINA'] = template_fold4.loc['Rebooting', 'SAVINA'] + 1 template_fold4.loc['Appearance', 'SAVINA'] = template_fold4.loc['Appearance', 'SAVINA'] + 1 template_flip4.loc['Appearance', 'SAVINA'] = template_flip4.loc['Appearance', 'SAVINA'] + 1 template_flip4.loc['Others', 'SAVINA'] = template_flip4.loc['Others', 'SAVINA'] + 2 template_flip4['SEAO Total'] = template_flip4[['SAVINA', 'SEAU', 'SEIN', 'SENZ', 'SEPCO', 'SESP', 'SME', 'TSE']].sum(axis=1) template_flip4.loc['Total'] = template_flip4[['SEAO Total', 'SAVINA', 'SEAU', 'SEIN', 'SENZ', 'SEPCO', 'SESP', 'SME', 'TSE']].sum(axis=0) template_flip4 = template_flip4.astype(int) template_fold4['SEAO Total'] = template_fold4[['SEAO Total', 'SAVINA', 'SEAU', 'SEIN', 'SENZ', 'SEPCO', 'SESP', 'SME', 'TSE']].sum(axis=1) template_fold4.loc['Total'] = template_fold4[['SEAO Total', 'SAVINA', 'SEAU', 'SEIN', 'SENZ', 'SEPCO', 'SESP', 'SME', 'TSE']].sum(axis=0) template_fold4 = template_fold4.astype(int) flip4_dict = {'a' + str(i): template_flip4.values.flatten()[i-1] for i in range(1,100)} fold4_dict = {'b' + str(i): template_fold4.values.flatten()[i-1] for i in range(1,100)} context = { #"topleft": topleft, #"topright": topright, #"bottomleft": bottomleft, #"bottomright": bottomright, "date0" : int((datetime.date.today() - date(2022, 9, 2))/ timedelta(days=1) + 1), "date1" : datetime.datetime.now().strftime("%#d.%#m.%Y"), "date2" : (datetime.datetime.now() - timedelta(days=1)).strftime("%#d/%#m"), "date3": datetime.datetime.now().strftime("%b.%#d"), "day_suffix": day_suffix, "v2": "{:>6}".format(f'{flip_today1.iloc[5, 2] + fold_today1.iloc[5, 2]:,}'), "v3": f'{int(flip_accum1.iloc[3, 2]):,}', "v4": f'{int(flip_accum1.iloc[5, 2]):,}', "v5": f'{int(flip_accum1.iloc[7, 2]):,}', "v6": f'{int(flip_accum2.iloc[1, 12]):,}', "v7": f'{int(flip_accum2.iloc[1, 5]):,}', "v8": f'{int(flip_accum2.iloc[1, 6]):,}', "v9": f'{int(fold_accum1.iloc[3, 2]):,}', "v10": f'{int(fold_accum1.iloc[5, 2]):,}', "v11": f'{int(fold_accum1.iloc[7, 2]):,}', "v12": f'{int(fold_accum2.iloc[1, 12]):,}', "v13": f'{int(fold_accum2.iloc[1, 5]):,}', "v14": f'{int(fold_accum2.iloc[1, 6]):,}', # "v21": f'{int(flip_today1.iloc[3, 2]):,}', # changed on 5 Sep 2022 # "v22": f'{int(flip_today1.iloc[5, 2]):,}', # "v23": f'{int(flip_today1.iloc[7, 2]):,}', "v21": f'{int(flip_today2.iloc[1, 12]):,}', "v22": f'{int(flip_today2.iloc[1, 5]):,}', "v23": f'{int(flip_today2.iloc[1, 6]):,}', "v24": f'{int(fold_today2.iloc[1, 12]):,}', "v25": f'{int(fold_today2.iloc[1, 5]):,}', "v26": f'{int(fold_today2.iloc[1, 6]):,}', # Table 1 Subtotals "v16": f'{int(flip_accum1.iloc[7, 2] + fold_accum1.iloc[7, 2]):,}', "v17": f'{int(flip_accum1.iloc[3, 2] + fold_accum1.iloc[3, 2]):,}', "v18": f'{int(flip_accum2.iloc[1, 12] + fold_accum2.iloc[1, 12]):,}', "v19": f'{int(flip_accum2.iloc[1, 5] + fold_accum2.iloc[1, 5]):,}', "v20": f'{int(flip_accum2.iloc[1, 6] + fold_accum2.iloc[1, 6]):,}', "v27": f'{int(flip_today2.iloc[1, 12]) + int(fold_today2.iloc[1, 12]):,}', "v28": f'{int(flip_today2.iloc[1, 5]) + int(fold_today2.iloc[1, 5]):,}', "v29": f'{int(flip_today2.iloc[1, 6]) + int(fold_today2.iloc[1, 6]):,}', "v30": f'{int(flip_today2.iloc[1, 4]):,}', "v31": f'{int(fold_today2.iloc[1, 4]):,}', "v15": f'{int(flip_today2.iloc[1, 4]) + int(fold_today2.iloc[1, 4]):,}', "v1": f'{int(flip_accum1.iloc[5, 2] + fold_accum1.iloc[5, 2]):,}', "v32": f'{int(flipfold_accum2.iloc[2, 4]):,}', "v33": f'{int(flip_accum2.iloc[2, 4]):,}', "v34": f'{int(fold_accum2.iloc[2, 4]):,}', "v35": f'{int(flipfold_accum2.iloc[2, 13]):,}', "v36": f'{int(fold_accum2.iloc[2, 13]):,}', "v37": f'{int(flip_accum2.iloc[2, 13]):,}', "v38": abs(round(((flipfold_accum2.iloc[2, 4] - flipfold_accum2.iloc[2, 13])/flipfold_accum2.iloc[2, 13]) * 100)), "increase_decrease": increase_decrease, "c12": int(template_flip4.loc[['Heat', 'Quick Discharge', 'Power', 'Rebooting'], 'SEAO Total'].sum()), "c19": int(template_fold4.loc[['Heat', 'Quick Discharge', 'Power', 'Rebooting'], 'SEAO Total'].sum()), "c14": int(template_flip4.loc[['Camera', 'Others'], 'SEAO Total'].sum()), "c21": int(template_fold4.loc[['Camera', 'Others'], 'SEAO Total'].sum()), "c1": int(flip4_dict["a1"] + fold4_dict["b1"]), "c2": int(flip4_dict["a19"] + fold4_dict["b19"]), "c3": int(flip4_dict["a64"] + fold4_dict["b64"]), "c4": int(flip4_dict["a82"] + fold4_dict["b82"]), "c5": int(template_flip4.loc[['Heat', 'Quick Discharge', 'Power', 'Rebooting'], 'SEAO Total'].sum() + template_fold4.loc[['Heat', 'Quick Discharge', 'Power', 'Rebooting'], 'SEAO Total'].sum()), "c6": int(flip4_dict["a73"] + fold4_dict["b73"]), "c7": int(template_flip4.loc[['Camera', 'Others'], 'SEAO Total'].sum() + template_fold4.loc[['Camera', 'Others'], 'SEAO Total'].sum()), "d1": round(100*(flip4_dict["a19"] + fold4_dict["b19"])/(flip4_dict["a1"] + fold4_dict["b1"])), "d2": round(100*(flip4_dict["a64"] + fold4_dict["b64"])/(flip4_dict["a1"] + fold4_dict["b1"])), "d3": round(100*(template_flip4.loc[['Heat', 'Quick Discharge', 'Power', 'Rebooting'], 'SEAO Total'].sum() + template_fold4.loc[['Heat', 'Quick Discharge', 'Power', 'Rebooting'], 'SEAO Total'].sum())/(flip4_dict["a1"] + fold4_dict["b1"])) } context2 = {**context, **flip4_dict, **fold4_dict} doc.render(context2) # Create in-memory buffer file_stream = io.BytesIO() # Save the .docx to the buffer doc.save(file_stream) # Reset the buffer's file-pointer to the beginning of the file file_stream.seek(0) #doc.save("SEAO Fold 4_Flip 4 Quality Monitoring (" + datetime.datetime.now().strftime("%#d %b") + ").docx") st.download_button( label="Download report here", data=file_stream, file_name="SEAO Fold 4_Flip 4 Quality Monitoring (" + datetime.datetime.now().strftime("%#d %b") + ").docx", mime='application/vnd.openxmlformats-officedocument.wordprocessingml.document' )