app.py

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'
     )