pages/9_Saved_Scenarios.py

import streamlit as st
from numerize.numerize import numerize
import io
import pandas as pd
from utilities import (format_numbers,decimal_formater,
                       channel_name_formating,
                       load_local_css,set_header,
                       initialize_data,
                       load_authenticator)
from openpyxl import Workbook
from openpyxl.styles import Alignment,Font,PatternFill
import pickle
import streamlit_authenticator as stauth
import yaml
from yaml import SafeLoader
from classes import class_from_dict

st.set_page_config(layout='wide')
load_local_css('styles.css')
set_header()

# for k, v in st.session_state.items():
#     if k not in ['logout', 'login','config'] and not k.startswith('FormSubmitter'):
#         st.session_state[k] = v

def create_scenario_summary(scenario_dict):
    summary_rows = []
    for channel_dict in scenario_dict['channels']:
        name_mod = channel_name_formating(channel_dict['name'])
        summary_rows.append([name_mod,
                             channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate'),
                             channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate'),
                             channel_dict.get('actual_total_sales') ,
                             channel_dict.get('modified_total_sales'),
                             channel_dict.get('actual_total_sales') / (channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate')), 
                             channel_dict.get('modified_total_sales') / (channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate')),
                             channel_dict.get('actual_mroi'), 
                             channel_dict.get('modified_mroi'),
                             channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate') / channel_dict.get('actual_total_sales'),
                             channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate') / channel_dict.get('modified_total_sales')])
        
    summary_rows.append(['Total',
                         scenario_dict.get('actual_total_spends'), 
                         scenario_dict.get('modified_total_spends'),
                         scenario_dict.get('actual_total_sales'), 
                         scenario_dict.get('modified_total_sales'),
                         scenario_dict.get('actual_total_sales') / scenario_dict.get('actual_total_spends'),
                         scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
                         '-',
                         '-',
                         scenario_dict.get('actual_total_spends') / scenario_dict.get('actual_total_sales'),
                         scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')])
    
    columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["first", "second"])
    columns_index = columns_index.append(pd.MultiIndex.from_product([['Spends','NRPU','ROI','MROI','Spend per NRPU'],['Actual','Simulated']], names=["first", "second"]))
    return  pd.DataFrame(summary_rows, columns=columns_index)
    

   
def summary_df_to_worksheet(df, ws):
    heading_fill = PatternFill(fill_type='solid',start_color='FF11B6BD',end_color='FF11B6BD')
    for j,header in enumerate(df.columns.values):
        col = j + 1
        for i in range(1,3):
            ws.cell(row=i, column=j + 1, value=header[i - 1]).font = Font(bold=True, color='FF11B6BD')
            ws.cell(row=i,column=j+1).fill = heading_fill
        if  col > 1 and (col - 6)%5==0:    
            ws.merge_cells(start_row=1, end_row=1, start_column = col-3, end_column=col)
            ws.cell(row=1,column=col).alignment = Alignment(horizontal='center')
    for i,row in enumerate(df.itertuples()):
        for j,value in enumerate(row):
            if j == 0:
                continue
            elif (j-2)%4 == 0 or (j-3)%4 == 0:
                ws.cell(row=i+3, column = j, value=value).number_format = '$#,##0.0' 
            else:
                ws.cell(row=i+3, column = j, value=value)
   
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, PatternFill
import logging

def scenario_df_to_worksheet(df, ws):
    heading_fill = PatternFill(start_color='FF11B6BD', end_color='FF11B6BD', fill_type='solid')
    
    for j, header in enumerate(df.columns.values):
        cell = ws.cell(row=1, column=j + 1, value=header)
        cell.font = Font(bold=True, color='FF11B6BD')
        cell.fill = heading_fill

    for i, row in enumerate(df.itertuples()):
        for j, value in enumerate(row[1:], start=1):  # Start from index 1 to skip the index column
            try:
                cell = ws.cell(row=i + 2, column=j, value=value)
                if isinstance(value, (int, float)):
                    cell.number_format = '$#,##0.0'
                elif isinstance(value, str):
                    cell.value = value[:32767]
                else:
                    cell.value = str(value)
            except ValueError as e:
                logging.error(f"Error assigning value '{value}' to cell {get_column_letter(j)}{i+2}: {e}")
                cell.value = None  # Assign None to the cell where the error occurred

    return ws





   
def download_scenarios():
    """
    Makes a excel with all saved scenarios and saves it locally
    """
    ## create summary page
    if len(scenarios_to_download) == 0:
        return
    wb = Workbook()
    wb.iso_dates = True
    wb.remove(wb.active)
    st.session_state['xlsx_buffer'] = io.BytesIO()
    summary_df = None
    #print(scenarios_to_download)
    for scenario_name in scenarios_to_download:
        scenario_dict =  st.session_state['saved_scenarios'][scenario_name]
        _spends = []
        column_names = ['Date']
        _sales = None
        dates = None
        summary_rows = []
        for channel in scenario_dict['channels']:
            if dates is None:
                dates = channel.get('dates')
                _spends.append(dates)
            if _sales is None:
                _sales = channel.get('modified_sales')
            else:
                _sales += channel.get('modified_sales')
            _spends.append(channel.get('modified_spends') * channel.get('conversion_rate'))
            column_names.append(channel.get('name'))
            
            name_mod = channel_name_formating(channel['name'])
            summary_rows.append([name_mod,
                                channel.get('modified_total_spends') * channel.get('conversion_rate') ,
                                channel.get('modified_total_sales'),
                                channel.get('modified_total_sales') / channel.get('modified_total_spends') * channel.get('conversion_rate'),
                                channel.get('modified_mroi'),
                                channel.get('modified_total_sales') / channel.get('modified_total_spends') * channel.get('conversion_rate')])
        _spends.append(_sales)
        column_names.append('NRPU')
        scenario_df = pd.DataFrame(_spends).T
        scenario_df.columns = column_names
        ## write to sheet
        ws = wb.create_sheet(scenario_name)
        scenario_df_to_worksheet(scenario_df, ws)    
        summary_rows.append(['Total',
                        scenario_dict.get('modified_total_spends') ,
                        scenario_dict.get('modified_total_sales'),
                        scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
                        '-',
                        scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')])
        columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["first", "second"])
        columns_index = columns_index.append(pd.MultiIndex.from_product([[scenario_name],['Spends','NRPU','ROI','MROI','Spends per NRPU']], names=["first", "second"]))
        if summary_df is None:
            summary_df = pd.DataFrame(summary_rows, columns = columns_index)
            summary_df = summary_df.set_index(('','Channel'))
        else:
            _df = pd.DataFrame(summary_rows, columns = columns_index)
            _df = _df.set_index(('','Channel'))
            summary_df = summary_df.merge(_df, left_index=True, right_index=True)
    ws = wb.create_sheet('Summary',0)
    summary_df_to_worksheet(summary_df.reset_index(), ws)
    wb.save(st.session_state['xlsx_buffer'])
    st.session_state['disable_download_button'] = False

def disable_download_button():
    st.session_state['disable_download_button'] =True

def transform(x):
    if x.name == ("",'Channel'):
        return x
    elif x.name[0] == 'ROI' or x.name[0] == 'MROI':
        return x.apply(lambda y : y if isinstance(y,str) else decimal_formater(format_numbers(y,include_indicator=False,n_decimals=4),n_decimals=4))
    else:
        return x.apply(lambda y : y if isinstance(y,str) else format_numbers(y))

def delete_scenario():
    if selected_scenario in st.session_state['saved_scenarios']:
        del st.session_state['saved_scenarios'][selected_scenario]
        with open('../saved_scenarios.pkl', 'wb') as f:
            pickle.dump(st.session_state['saved_scenarios'],f)     
            
def load_scenario():
    if selected_scenario in st.session_state['saved_scenarios']:
        st.session_state['scenario'] = class_from_dict(selected_scenario_details)
        


authenticator = st.session_state.get('authenticator')
if authenticator is None:
    authenticator = load_authenticator()

name, authentication_status, username = authenticator.login('Login', 'main')
auth_status = st.session_state.get('authentication_status')

if auth_status == True:
    is_state_initiaized = st.session_state.get('initialized',False)
    if not is_state_initiaized:
        #print("Scenario page state reloaded")
        initialize_data()


    saved_scenarios = st.session_state['saved_scenarios']


    if len(saved_scenarios) ==0:
        st.header('No saved scenarios')
        
    else:
        
        with st.sidebar:
            selected_scenario = st.radio(
                'Pick a scenario to view details',
                list(saved_scenarios.keys())
            )
            st.markdown("""<hr>""", unsafe_allow_html=True)
            scenarios_to_download = st.multiselect('Select scenarios to download',
                        list(saved_scenarios.keys()))
            
            st.button('Prepare download',on_click=download_scenarios)
            st.download_button(
                    label="Download Scenarios",
                    data=st.session_state['xlsx_buffer'].getvalue(),
                    file_name="scenarios.xlsx",
                    mime="application/vnd.ms-excel",
                    disabled= st.session_state['disable_download_button'],
                    on_click= disable_download_button
                )
            
        column_1, column_2,column_3 = st.columns((6,1,1))
        with column_1:
            st.header(selected_scenario)
        with column_2:
            st.button('Delete scenarios', on_click=delete_scenario)
        with column_3:
            st.button('Load Scenario', on_click=load_scenario)
        
        selected_scenario_details = saved_scenarios[selected_scenario]
        
        pd.set_option('display.max_colwidth', 100)
            
        st.markdown(create_scenario_summary(selected_scenario_details).transform(transform).style.set_table_styles(
    [{
        'selector': 'th',
        'props': [('background-color', '#11B6BD')]
    },
        {
        'selector' : 'tr:nth-child(even)',
        'props' : [('background-color', '#11B6BD')]
        }
        ]).to_html(),unsafe_allow_html=True)
        
elif auth_status == False:
    st.error('Username/Password is incorrect')
    
if auth_status != True:
    try:
        username_forgot_pw, email_forgot_password, random_password = authenticator.forgot_password('Forgot password')
        if username_forgot_pw:
            st.success('New password sent securely')
            # Random password to be transferred to user securely
        elif username_forgot_pw == False:
            st.error('Username not found')
    except Exception as e:
        st.error(e)