app.py

import streamlit as st
import pandas as pd
import numpy as np
import gspread
import io
import googleapiclient.discovery
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.http import MediaIoBaseDownload
import random
import requests
import altair as alt
from PIL import Image
from io import BytesIO


logo_url = "https://www.gradina-slavu.ro/"
logo_path = "https://static.wixstatic.com/media/268e9b_fb1da1f5fc304d15beee1d2e581d5d0c~mv2.png/v1/fill/w_134,h_62,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/nume.png"

# Display the logo with a black background and centered alignment
st.markdown(
   f"""
   <div style='background-color: rgba(128,128,128,0.4); text-align: center;'>
       <a href='{logo_url}' target='_blank'>
          <img src='{logo_path}' width='150'>
       </a>
    </div>
    """,
   unsafe_allow_html=True,
)
query_params = st.experimental_get_query_params()

# Get the value of a specific parameter

# Display the input value

st.title('RETETE - Selecteaza produse si retete din sidebar ori un meniu de mai jos')
st.write(' ')

sa= gspread.service_account(filename='creds.json')
# Open the Google Sheets file
sh = sa.open_by_url("https://docs.google.com/spreadsheets/d/1GZjbDLYTgRhKHtvgT868ursHYzXXmHF23w86-3KNZN0/edit#gid=489241774")

worksheet=sh.get_worksheet(0)
worksheet1=sh.get_worksheet(1)
worksheet2=sh.get_worksheet(2)
worksheet3=sh.get_worksheet(3)
rows = worksheet.get_all_values()
rows1 = worksheet1.get_all_values()
rows2 = worksheet2.get_all_values()
rows3 = worksheet3.get_all_values()
df=pd.DataFrame(rows)
df1=pd.DataFrame(rows1)
df2=pd.DataFrame(rows2)
df3=pd.DataFrame(rows3)
df2=df2.iloc[1:]
st.sidebar.markdown("---")
st.sidebar.write("In prima sectiune poti alege unul din produsele noastre! Vei primi sugestii de retete pentru el (gramajul este fixat la 100 de grame),recomandam folosirea celei de-a doua sectiuni pentru a-ti contrui meniul!")
if "input_param" in query_params:
    input_value = query_params["input_param"][0]
    input_value = [input_value]
    input_values = [""] + df3.iloc[:, 0].tolist()
    st.selectbox("Selecteaza Meniu", input_values)
elif "input_param" not in query_params:
    # Get the values from the first column in df3
    input_values = [""] + df3.iloc[:, 0].tolist()
    input_value = st.selectbox("Selecteaza Meniu", input_values)
    #st.write('Nu uita sa deselectezi optiunile din sidebar, in cazul in care meniul nu se afiseaza!', text_color="green")
    st.markdown('<span style="color:green">Nu uita sa deselectezi optiunile din sidebar, in cazul in care meniul nu se afiseaza!</span>', unsafe_allow_html=True)
    input_value = [input_value] if input_value else []
else:
    input_value = []
options=['branza vegetala','dulceata de ardei iute','stevie','rosii cherry','rosii tocate','suc de rosii','rosii cherry uscate in ulei','pesto de busuioc','carne vegetala','pudra de dovleac','zacusca de vinete','zacusca de peste','zacusca de fasole','magiun de prune','hrean in otet','legume uscate']
options.sort()
valori_optime=['2000','280','25','50','70','20','1600','17000','50','5000','75','350','10','120','1.4','1.6','18','2','400','6','6','550','500','1000','15','350','1000','3500','2400','15','2','5','200','3500','300','2000']
selected_option = st.sidebar.radio("Selecteaza un produs", options)

st.sidebar.write("Fiecare cadru de date poate fi derulat la stânga și la dreapta pentru mai multe informații!")

st.experimental_set_query_params()


def select_random_value(df, selected_option):
    # Select a random item from the list
    selected_item = selected_option 
    
    # Find the corresponding values in the dataframe
    matching_rows = df[df[3] == selected_item]
    corresponding_values = matching_rows[0].tolist()
    
    # Select a random value from the corresponding values
    if corresponding_values:
        selected_value = random.choice(corresponding_values)
        return selected_value
    else:
        return None

reteta_selectata=select_random_value(df1,selected_option)

d2 = df2.set_index(0).to_dict('index')
d2 = {k: {k2: float(v2) for k2, v2 in v.items()} for k, v in d2.items()}

discarded_keys=['Calorii','Zaharuri','Grasimi saturate','Omega-6','Vit A','Vit C','Vit D','Vit E','Vit K','Pantothenic acid-B5','Choline-B4','Betaine','Sodiu','Cupru','Mangan','Seleniu','Fluor','Cholesterol','Phytosterols']

def select_optimized_keys(d, value, optimals):
    optimals = [float(x) for x in optimals]

    matching_key = [k for k, v in d.items() if k == value][0]
    other_keys = [k for k in d.keys() if k != matching_key]
    
    second_key = random.choice(other_keys)
    other_keys.remove(second_key)

    third_key = random.choice(other_keys)
    
    return (second_key, third_key, matching_key)

key_options = list(df1[0].unique())
key_options = sorted(key_options)
if '' in key_options:
    key_options.remove('')
    
st.sidebar.markdown("---")
st.sidebar.write("In a doua sectiune poti alege pana la trei retete, gramajele pentru fiecare reteta si bauturile! Vei afla nutrientii consumati in decurs de o masa sau o zi!")
st.sidebar.write("Construieste-ti meniul!")
selected_keys_option = st.sidebar.multiselect("Selecteaza pana la trei retete dorite", key_options)

if selected_keys_option:
    selected_keys = list(selected_keys_option)

elif input_value and not pd.isnull(input_value):
    if input_value in df3.iloc[:, 0].values:
        selected_row = df3[df3.iloc[:, 0] == input_value[0]]
        # Store the selected row in a different DataFrame
        selected_df = selected_row.copy()
        column_values = df3.iloc[:, 0].tolist()
        index = column_values.index(input_value[0])
        selected_keys = input_value+[df3.iloc[index, 2]]
        if df3.iloc[index, 4]:
            selected_keys.append(df3.iloc[index, 4])
    else:
       selected_keys = []
else:
    selected_keys = []
        
# If the user hasn't selected their own keys, use the default function to select random keys
if not selected_keys:
    selected_keys = select_optimized_keys(d2, reteta_selectata, valori_optime)
    selected_keys = list(selected_keys)
    selected_keys.reverse()

def search_values(lst, dct):
    result = {}
    for item in lst:
        for key, value in dct.items():
            if item == key:
                result[item] = value
    return result

# create a dictionary to hold the new dataframes
dfs = {}

# loop through the categories and create a new dataframe for each one
for reteta in selected_keys:
    dfs[reteta] = df1[df1[0] == reteta]

dfs2={}
for reteta in selected_keys:
    dfs2[reteta] = df1[df1[0] == reteta].iloc[:,6:]

def make_clickable(url):
    return f'<a href="{url}" target="_blank">{url}</a>'

  
   
    

final_result=search_values(selected_keys,d2)

Finaldf=pd.DataFrame(final_result)
Finaldf=Finaldf.round(2)
new_index=['Calorii','Carbohidrati','Fibre dietetice','Zaharuri','Grasimi','Grasimi saturate','Omega-3','Omega-6','Proteina','Vit A','Vit C','Vit D','Vit E','Vit K','Thiamin-B1','Riboflavin-B2','Niacin-B3','Vitamina-B6','Folate-B9','Vitamina-B12','Pantothenic acid-B5','Choline-B4','Betaine','Calciu','Fier','Magneziu','Fosfor','Potasiu','Sodiu','Zinc','Cupru','Mangan','Seleniu','Fluor','Cholesterol','Phytosterols']
Finaldf = Finaldf.set_index(pd.Index(new_index))

def add_dict_values(d):
    result = {}
    keys = set().union(*(d[key].keys() for key in d))
    for key in keys:
        result[key] = sum(d[k].get(key, 0) for k in d)
    return result

valori_meniu_d = add_dict_values(final_result)
valori_meniu = list(valori_meniu_d.values())

# Define the function to subtract two lists
def subtract_lists(list1, list2):
    result = []
    for i, j in zip(list1, list2):
        result.append(max(0, float(i) - float(j)))
    return result

# Define the default value of the slider
factors = {
    "Option 1": 1,
    "Option 2": 2,
    "Option 3": 3
}


factor_dict = {}
for col in Finaldf.columns:
    default_factor=100
    slider_label = f"Alege greutatea meniului pentru {col}, in grame"
    slider_key = f"{slider_label}-{default_factor}"
    factor1=st.sidebar.slider(label=slider_label, key=slider_key, min_value=0, max_value=750,step=50, value=default_factor)
    multiplied_column = Finaldf[col] * factor1/100
    Finaldf[col] = multiplied_column
    factor_dict[col] = factor1
    
    

if not selected_keys_option: # default factor is being used or dict values
    if input_value and input_value[0]:
        selected_dict = {}
        for col_idx in range(0, len(selected_df.columns), 2):
            key_col = selected_df.columns[col_idx]
            value_col = selected_df.columns[col_idx + 1]
            selected_dict[selected_df[key_col].values[0]] = selected_df[value_col].values[0]
            
        for col in Finaldf.columns:
            #col
            if col in selected_dict:
                factor1 = float(selected_dict[col])
                #factor1
                multiplied_column = Finaldf[col] * factor1/100
                Finaldf[col] = multiplied_column
                factor_dict[col] = factor1
        row_sums = Finaldf.sum(axis=1)
        selected_df = pd.DataFrame.from_dict(selected_dict, orient='index', columns=['Value'])
        # Add " g" to the values
        selected_df['Value'] = selected_df['Value'].astype(str) + ' g'
        st.write('Tabel Gramaje Retete')
        selected_df
        Finaldf['Valori meniu'] = row_sums
    else:
        default_factor=350
        factor1 = default_factor
        row_sums = Finaldf.sum(axis=1)
        factor_dict = {col: factor1 for col in Finaldf.columns}
        Finaldf['Valori meniu'] = row_sums
else:
    
    Finaldf['Valori meniu'] = Finaldf[selected_keys_option].sum(axis=1)


#selectare bautura
bautura_list = sorted(df[df.iloc[:, 1] == 'bautura'].iloc[:, 0].tolist())
selected_bautura_list = st.sidebar.multiselect("Selecteaza bauturi", bautura_list)

bauturi={}
if selected_bautura_list:
    for bautura in selected_bautura_list:
        filtered_df = df[(df[0] == bautura) & (df[1] == 'bautura')].reset_index(drop=True)
        selected_df = filtered_df.iloc[:, 2:].astype(float)
        selected_df = selected_df.apply(lambda x: x * 2.5)  # multiply by 2.5
        selected_df = selected_df.T.rename(columns={0: bautura})
        Finaldf[bautura] = selected_df[bautura].values
        bauturi[bautura] = selected_df[bautura].fillna(0).values

bauturi_df = pd.DataFrame.from_dict(bauturi, orient='index').T
bauturi_sum = bauturi_df.apply(pd.to_numeric).sum(axis=1)



Finaldf['Valori meniu portii de 100g']=valori_meniu
valori_meniu_factori=Finaldf['Valori meniu'].tolist()
if bauturi_sum.any():
    Valori_ramase=subtract_lists(valori_optime,valori_meniu_factori)
    Valori_ramase=Valori_ramase-bauturi_sum.values
    Valori_ramase = np.maximum(Valori_ramase, 0)
    bauturi_sum.index = new_index
    Finaldf['Valori bauturi'] = bauturi_sum
    Finaldf['Valori meniu si bauturi'] = ((Finaldf['Valori meniu'])+bauturi_sum).round(2)
else:
    Valori_ramase=subtract_lists(valori_optime,valori_meniu_factori)
    Valori_ramase = np.maximum(Valori_ramase, 0)
    Finaldf['Valori meniu si bauturi'] = (Finaldf['Valori meniu']).round(2)
for i in range(len(selected_keys)):
    dfs1 = dfs[selected_keys[i]]
    dfs1 = pd.DataFrame(dfs1)
    images_displayed = False
    # update 'path' column with formatted URLs
    
    for index, row in dfs1.iterrows():
        image_path = row[6]
        if not image_path:
            continue
        credentials = service_account.Credentials.from_service_account_file('creds.json')

        # Build the Google Drive service
        drive_service = build('drive', 'v3', credentials=credentials)

        # Retrieve image content from Google Drive
        request = drive_service.files().get_media(fileId=image_path)
        fh = io.BytesIO()
        downloader = MediaIoBaseDownload(fh, request)
        done = False
        while done is False:
            status, done = downloader.next_chunk()
        try:
            image = Image.open(fh)
            image = image.resize((300,300))
            with st.container():
              col1, col2, col3 = st.columns([1, 2, 1])
              col2.image(image, caption=selected_keys[i], width=300, use_column_width=True)
              images_displayed = True
              if images_displayed:
                    dfs1 = dfs1.iloc[:,3:6]
                    dfs1 = dfs1.reset_index(drop=True)
                    indexcol = ['Ingrediente', 'Gramaj', 'Preparare']
                    dfs1.columns = indexcol
                    
                    new_df = dfs1.copy()
                    if not selected_keys_option: # default factor is being used or dict values
                        if input_value and input_value[0]:
                            selected_dict = {}
                            for col_idx1 in range(0, len(selected_df.index)):
                                key = selected_df.index[col_idx1]
                                value = selected_df.iloc[col_idx1,0]
                                selected_dict[key] = value.strip(' g')
                             
                            if selected_keys[i] in selected_dict:
                               
                                    factor2 = float(selected_dict[selected_keys[i]])/100
                                    #st.write("Factor2:", factor2) 
                                    new_df['Gramaj'] = (((new_df['Gramaj'].astype(float)*factor2)/ new_df['Gramaj'].astype(float).sum()) * 100).round(2)
                                    new_df['Gramaj/2Portii'] = (new_df['Gramaj'].astype(float) * 2).round(2)
                                    new_df['Gramaj/3Portii'] = (new_df['Gramaj'].astype(float) * 3).round(2)
                                    new_df['Gramaj/4Portii'] = (new_df['Gramaj'].astype(float) * 4).round(2)
                                    
                        else:
                            factor2 = 3.5
                            new_df['Gramaj'] = (((new_df['Gramaj'].astype(float)*factor2)/ new_df['Gramaj'].astype(float).sum()) * 100).round(2)
                            new_df['Gramaj/2Portii'] = (new_df['Gramaj'].astype(float) * 2).round(2)
                            new_df['Gramaj/3Portii'] = (new_df['Gramaj'].astype(float) * 3).round(2)
                            new_df['Gramaj/4Portii'] = (new_df['Gramaj'].astype(float) * 4).round(2)

                        
                    else:
                        if selected_keys[i] in factor_dict:
                                #selected_keys[i]
                                factor2 = factor_dict[selected_keys[i]]/100
                                
                                new_df['Gramaj'] = (((new_df['Gramaj'].astype(float)*factor2)/ new_df['Gramaj'].astype(float).sum()) * 100).round(2)
                                new_df['Gramaj/2Portii'] = (new_df['Gramaj'].astype(float) * 2).round(2)
                                new_df['Gramaj/3Portii'] = (new_df['Gramaj'].astype(float) * 3).round(2)
                                new_df['Gramaj/4Portii'] = (new_df['Gramaj'].astype(float) * 4).round(2)

                                 
                    
                    indexcol1 = ['Ingrediente', 'Gramaj', 'Gramaj/2Portii', 'Gramaj/3Portii', 'Gramaj/4Portii', 'Preparare']
                    new_df = new_df.reindex(columns=indexcol1)
                    st.subheader(selected_keys[i])
                    #st.write("Dublu-click pe o celulă pentru a vedea textul complet in Preparare.")
                    #st.write(dfs1,index=False)
                    pd.set_option('display.max_colwidth', None)
                    new_df_excluded = new_df.drop(columns=["Preparare"])

                    # Display the modified DataFrame
                    st.dataframe(new_df_excluded)
                    max_length = 800

                  # Truncate the text in the DataFrame columns
                    new_df_truncated = new_df.apply(lambda x: x.str[:max_length] + '...' if x.dtype == "object" else x)
                    new_df_truncated = new_df_truncated.dropna(axis=0, how='all')
                  # Display the DataFrame with tooltips
                    st.table(new_df_truncated[['Preparare']])
                  
                    
                    
                   
              else:
                    st.warning(f"No image found for {selected_keys[i]}")
        except:
            continue



Finaldf['Valori ramase dupa portiile selectate']=Valori_ramase.round(2)
Finaldf['Valori Optime']=valori_optime
st.write('Tabel cu valori ramase, valori totale per meniu si valori optime de consum')
st.write(Finaldf)

Valori_normate = [(float(valori_optime[i]) - Valori_ramase[i]) / float(valori_optime[i]) for i in range(len(valori_optime))]
Valori_ramase_normate=[ Valori_ramase[i] / float(valori_optime[i]) for i in range(len(valori_optime))]
chartdf = pd.DataFrame({
    'Procente ramase de consumat':pd.Categorical(new_index, categories=new_index),
    'max_values':Valori_normate,
    'Procente': Valori_ramase_normate,
})

# Create a Categorical datatype for the x-axis
x_scale = alt.Scale(domain=new_index, zero=False)
# Define the chart using Altair
chart = alt.Chart(chartdf).mark_bar().encode(
    x=alt.X('Procente ramase de consumat:O', scale=x_scale),
    y='Procente',
    color=alt.condition(
        alt.datum.results > alt.datum.max_values,
        alt.value('red'),  # Set the color to red if the result is greater than the max value
        alt.value('blue'),  # Set the color to blue if the result is less than or equal to the max value
    )
).properties(
    width=alt.Step(40),  # Set the width of each bar
)

# Display the chart in Streamlit
st.altair_chart(chart, use_container_width=True)

#sidebar for categories

# Define columns to display and exclude
all_columns = new_index
df.iloc[0, 2:] = new_index
# set the column names to the values in the first row
df = df.iloc[1:]

# concatenate the original column index sliced from the third column onward with the new column index
new_col_index = df.columns[:2].tolist() + new_index

# set the new column index
df.set_axis(new_col_index, axis=1, inplace=True)
df = df.set_index(0)
df.iloc[1:, 2:] = df.iloc[1:, 2:].astype(float)
st.write('Sorteaza coloanele dupa valorile ramase pentru a gasi ingrediente ce pot acoperi diferentele, valorile sunt pentru 100 de grame')
st.dataframe(df.iloc[:, 1:].astype(float), height=400)

df1.replace(r'^\s*$', np.nan, regex=True, inplace=True)  # replace empty white spaces with NaN
df1.dropna(how='all', inplace=True)  # drop empty rows
df1 = df1.reset_index(drop=True)  # reset the index to remove null values
df1.dropna(subset=[df1.columns[0]], inplace=True)  # drop any rows with null values in the first column
df1 = df1.set_index(df1.columns[0])  # set the index to the first column
df1 = df1.drop([1, 2, 5, 6], axis=1)  # drop some columns
df1.columns = ['Ingrediente', 'Gramaj']  # rename columns

st.write('Cauta reteta dupa ingredient, deschide bara laterala, adauga reteta, selecteaza gramajul si descopera valorile nutritive')
col1, col2, col3 = st.columns((1, 4, 1))

# Use the middle column for displaying the dataframe
with col2:
    st.dataframe(df1, height=400)
st.sidebar.markdown("---")
st.sidebar.write("Pentru a ajunge in magazin puteti da click pe banner-ul Gradina Slavu! Multumim!")
st.sidebar.markdown("---")

df=pd.DataFrame(rows)
lista=list(df[0])
#selectare ingredient