Home_old_version.py

import sqlite3
import uuid
import json
import streamlit as st
from utilities import (
    load_local_css,
    set_header,
    load_authenticator,
    send_email,
)
import streamlit_authenticator as stauth
import yaml
from yaml import SafeLoader
import os
import datetime
import subprocess
import shutil
import pandas as pd
from st_aggrid import AgGrid
from st_aggrid import GridOptionsBuilder, GridUpdateMode
import pickle
from pathlib import Path

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

# def authenticator():
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
with open("config.yaml") as file:
    config = yaml.load(file, Loader=SafeLoader)
    st.session_state["config"] = config
authenticator = stauth.Authenticate(
    config["credentials"],
    config["cookie"]["name"],
    config["cookie"]["key"],
    config["cookie"]["expiry_days"],
    config["preauthorized"],
)
st.session_state["authenticator"] = authenticator
name, authentication_status, username = authenticator.login("Login", "main")
auth_status = st.session_state.get("authentication_status")

if auth_status == True:
    authenticator.logout("Logout", "main")
    is_state_initiaized = st.session_state.get("initialized", False)

    if not is_state_initiaized:

        if "session_name" not in st.session_state:
            st.session_state["session_name"] = None

        cols1 = st.columns([2, 1])

        with cols1[0]:
            st.markdown(f"**Welcome {name}**")
        with cols1[1]:
            st.markdown(
                f"**Current Session: {st.session_state['session_name']}**"
            )

        # relative_path = Path('DB_Sample','..' ,'DB', 'User.db')
        # absolute_path = Path.cwd() / relative_path
        # st.write(absolute_path)
        # database_file=Path(__file__).parent / relative_path

        database_file = r"C:\Users\ManojP\Documents\Mastercard\Build\DB_Sample\V6_persistant_data_home_page_connected_pages\DB\User.db"

    conn = sqlite3.connect(database_file)  # connection with sql db
    c = conn.cursor()
    c.execute("SELECT * from sessions")
    st.write(c.fetchall())
    # c.executemany("INSERT INTO users (username, email) VALUES (?, ?)",
    #         [("Geetha Krishna", "[email protected]"),
    #         ("Samkeet Sangai", "[email protected]"),
    #         ('Manoj P','[email protected]'),
    #         ('Srishti Verma','[email protected]'),
    #         ('Ismail mohammed',"[email protected]"),
    #         ('Sharon Sheng','[email protected]'),
    #         ('Ioannis Papadopoulos','[email protected]'),
    #         ('Herman Kwong',"[email protected]")
    #         ])

    # conn.commit()

    # c.execute("DELETE from sessions")
    # conn.commit()
    # st.write(c.fetchall())

    page_name = "Home Page"

    c.execute(
        "SELECT email, user_id, user_type FROM users WHERE username = ?",
        (name,),
    )
    user_data = c.fetchone()
    email, user_id, user_type = user_data

    # st.write(user_type)
    # with st.sidebar:
    #     # if user_type != 'technical':
    #     st.page_link("home.py", label="Home123")
    #     st.page_link('pages/1_Data_Import.py',label='Data Import')
    #     st.page_link('pages/2_Data_Validation.py',label="Data Validation")
    #     st.page_link('pages/3_Transformations.py',label='Transformations')
    #     st.page_link("pages/4_Model_Build.py")
    #     st.page_link('pages/5_Model_Tuning_with_panel.py',label='Model Tuning')

    #     st.page_link('pages/5_Saved_Model_Results.py',label="Saved Model Results")

    # st.write(pd.to_datetime(created_time))
    # c.execute("DELETE FROM sessions")
    # c.execute('select * from sessions')
    # conn.commit()
    # output = c.fetchall()

    # st.write(output)

    # if emails is not None:
    #     email = emails[0]

    folder_path = r"C:\Users\ManojP\Documents\Mastercard\Build\DB_Sample\V6_persistant_data_home_page_connected_pages\Users"
    user_folder_path = os.path.join(folder_path, email)

    # project_dct = {
    #         'data_import': {
    #                         "granularity_selection":0,
    #                         'cat_dct':{},
    #                         "merged_df":None,
    #                         'edited_df':None,
    #                         "numeric_columns":None,
    #                         "files_dict":None,
    #                         'formatted_panel1_values':None,
    #                         'formatted_panel2_values':None,
    #                         "missing_stats_df":None,
    #                         'edited_stats_df':None

    #                         },

    #         'data_validation': {"target_column":0,
    #                         'selected_panels':None,
    #                         "selected_feature":0,
    #                         "validated_variables":[],
    #                         "Non_media_variables":0

    #         },
    #         'transformations': {},
    #         'model_build': {},
    #         'model_tuning':{},
    #         'saved_model_results': {},
    #         'model_result_overview': {},
    #         'build_response_curves': {},
    #         'scenario_planner': {},
    #         'saved_scenarios': {},
    #         'optimized_result_analysis': {}
    #     }
    # st.session_state['project_dct']=project_dct

    # st.write(project_dct)

    def dump_session_details_db(allowed_users, session_name):
        created_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M")

        session_id = str(uuid.uuid4())

        if len(allowed_users) == 0:
            c.execute(
                "INSERT INTO sessions VALUES (?, ?, ?, ?, ?, ?, ?,?)",
                (
                    user_id,
                    name,
                    session_id,
                    session_name,
                    page_name,
                    created_time,
                    created_time,
                    None,
                ),
            )
            conn.commit()
        else:
            for allowed_user in allowed_users:
                c.execute(
                    "INSERT INTO sessions VALUES (?, ?, ?, ?, ?, ?, ?,?)",
                    (
                        user_id,
                        name,
                        session_id,
                        session_name,
                        page_name,
                        created_time,
                        created_time,
                        allowed_user,
                    ),
                )
            conn.commit()

        # st.success('Project created')

    if "session_path" not in st.session_state:
        st.session_state["session_path"] = None

        # creating dir for user

    if not os.path.exists(user_folder_path):
        os.makedirs(user_folder_path)

    c.execute("SELECT DISTINCT username FROM users ")
    allowed_users_db = [user[0] for user in c.fetchall() if user[0] != name]

    c.execute(
        "SELECT session_name from sessions WHERE allowed_users = ?", (name,)
    )
    available_sessions = c.fetchall()  # all sessions available for user

    c.execute(
        "SELECT Distinct Session_name, status, updated_time as last_updated FROM sessions WHERE owner=?",
        (name,),
    )

    session_summary = c.fetchall()

    session_summary_df = pd.DataFrame(
        session_summary,
        columns=["Project Name", "Last Page Edited", "Modified Date"],
    )
    session_summary_df["Modified Date"] = session_summary_df[
        "Modified Date"
    ].map(lambda x: pd.to_datetime(x))

    session_summary_df = session_summary_df.sort_values(
        by=["Modified Date"], ascending=False
    )

    st.header("Manage Projects")

    st.markdown(
        """

    * **Load Existing Project:** Select the project you want and click 'Load Project'.

    * **Delete Project:** If you wish to delete a project, select it and click 'Delete Project'.

    * **Modify User Access:** Make changes to user access permissions as needed.

    

    """
    )

    # session_col=st.columns([5,5])
    # with session_col[0]:
    gd = GridOptionsBuilder.from_dataframe(session_summary_df)
    gd.configure_pagination(
        enabled=True, paginationAutoPageSize=False, paginationPageSize=10
    )
    gd.configure_selection(use_checkbox=True)

    gridoptions = gd.build()

    if session_summary_df.shape[0] < 5:
        height = (session_summary_df.shape[0]) * 20 + 100

    else:
        height = None

    table = AgGrid(
        session_summary_df,
        gridOptions=gridoptions,
        update_mode=GridUpdateMode.SELECTION_CHANGED,
        height=height,
        fit_columns_on_grid_load=True,
    )

    if len(table.selected_rows) > 0:

        selected_rows = table.selected_rows

        project_name = selected_rows[0]["Project Name"]

        project_col = st.columns(2)

        with project_col[0]:

            project_path = os.path.join(user_folder_path, project_name)

            st.session_state["project_path"] = project_path  # load project dct

            project_dct_path = os.path.join(project_path, "project_dct.pkl")

            with open(project_dct_path, "rb") as f:
                st.session_state["project_dct"] = pickle.load(f)

                # st.write(st.session_state['project_dct'])

            with st.spinner("Redirecting to last Saved Page"):

                page_link = st.page_link(
                    "pages/1_Data_Import.py",
                    label=f"Load Project - **{project_name}**",
                )

        with project_col[1]:

            if st.button(
                f"Delete Project - **{selected_rows[0]['Project Name']}**"
            ):

                project_name_to_delete = selected_rows[0]["Project Name"]
                st.warning(
                    f"{project_name_to_delete} will be deleted permanentaly and all the information regarding the project will be lost"
                )

        with st.expander("Modify user access for selected project"):

            c.execute(
                "SELECT DISTINCT allowed_users FROM sessions WHERE session_name = ?",
                (project_name,),
            )

            present_users = c.fetchall()

            present_users = [
                user[0]
                for user in present_users
                if user[0] != name and user[0] is not None
            ]

            present_users = None if len(present_users) == 0 else present_users

            allowed_users = st.multiselect(
                "Modify other users access",
                allowed_users_db,
                default=present_users,
            )

            if st.button("Save Changes", use_container_width=True):
                pass

    c.execute("SELECT  Session_name FROM sessions WHERE owner=?", (name,))
    user_projects = [
        project[0] for project in c.fetchall()
    ]  # user owned sessions

    with st.expander("Create New Project"):
        st.markdown(
            "To create a new project, Enter Project name below, select user who you want to give access of this project and click **Create New Project**"
        )
        project_col1 = st.columns(2)
        with project_col1[0]:
            project_name = st.text_input("Enter Project Name")

        if project_name in user_projects:
            st.warning("Project already exists please enter new name")

        with project_col1[1]:

            allowed_users = st.multiselect(
                "Select Users who can access to this Project", allowed_users_db
            )
            allowed_users = list(allowed_users)

        Create = st.button("Create New Project", use_container_width=True)
        # st.button("Label", use_container_width=True)

        if Create:

            allowed_users.append(name)

            if project_name in user_projects:

                st.warning("Project already exists please enter new name")
                st.stop()

            project_path = os.path.join(user_folder_path, project_name)

            os.makedirs(project_path)

            dump_session_details_db(allowed_users, project_name)

            project_dct = {
                "data_import": {
                    "granularity_selection": 0,
                    "cat_dct": {},
                    "merged_df": None,
                    "edited_df": None,
                    "numeric_columns": None,
                    "files_dict": None,
                    "formatted_panel1_values": None,
                    "formatted_panel2_values": None,
                    "missing_stats_df": None,
                    "edited_stats_df": None,
                },
                "data_validation": {
                    "target_column": 0,
                    "selected_panels": None,
                    "selected_feature": 0,
                    "validated_variables": [],
                    "Non_media_variables": 0,
                },
                "transformations": {"Media": {}, "Exogenous": {}},
                "model_build": {
                    "sel_target_col": None,
                    "all_iters_check": False,
                    "iterations": 0,
                    "build_button": False,
                    "show_results_check": False,
                    "session_state_saved": {},
                },
                "model_tuning": {
                    "sel_target_col": None,
                    "sel_model": {},
                    "flag_expander": False,
                    "start_date_default": None,
                    "end_date_default": None,
                    "repeat_default": "No",
                    "flags": None,
                    "select_all_flags_check": {},
                    "selected_flags": {},
                    "trend_check": False,
                    "week_num_check": False,
                    "sine_cosine_check": False,
                    "session_state_saved": {},
                },
                "saved_model_results": {
                    "selected_options": None,
                    "model_grid_sel": [1],
                },
                "model_result_overview": {},
                "build_response_curves": {},
                "scenario_planner": {},
                "saved_scenarios": {},
                "optimized_result_analysis": {},
            }
            st.session_state["project_dct"] = project_dct

            st.session_state["project_path"] = project_path

            project_dct_path = os.path.join(project_path, "project_dct.pkl")
            st.session_state["session_path"] = project_path

            with open(project_dct_path, "wb") as f:
                pickle.dump(project_dct, f)

            st.success("Project Created")

    # st.header('Clone Project')

    with st.expander("**Clone saved projects**"):

        c.execute(
            "SELECT DISTINCT owner FROM sessions WHERE allowed_users=?",
            (name,),
        )  # owner
        owners = c.fetchall()

        owners = [owner[0] for owner in owners]

        if len(owners) == 0:

            st.warning("You dont have any shared project yet!")

            st.stop()

        cols = st.columns(2)

        with cols[0]:

            owner = st.selectbox("Select Owner", owners)

            c.execute("SELECT email FROM users WHERE username=?", (owner,))

            owner_email = c.fetchone()[0]

            owner_folder_path = os.path.join(folder_path, owner_email)

        with cols[1]:

            c.execute(
                "SELECT session_name FROM sessions WHERE  owner=? AND allowed_users = ?",
                (owner, name),
            )  # available sessions for user
            project_names = c.fetchall()

            project_name_owner = st.selectbox(
                "Select a saved Project available for you",
                [project_name[0] for project_name in project_names],
            )
            owner_project_path = os.path.join(owner_folder_path, project_name)

        with cols[0]:
            project_name_user = st.text_input(
                "Enter Project Name", value=project_name_owner
            )

            if project_name in user_projects:

                st.warning(
                    "This Project name already exists in your directory Please enter a different name"
                )

                # st.stop()

            project_path = os.path.join(user_folder_path, project_name_user)

            owner_project_path = os.path.join(
                owner_folder_path, project_name_owner
            )

        with cols[1]:

            allowed_users = st.multiselect(
                "Select Users who can access this session", allowed_users_db
            )
            allowed_users = list(allowed_users)

        if st.button("Load Project", use_container_width=True):

            if os.path.exists(project_path):

                st.warning(
                    "This Project name already exists in your directory Please enter a different name"
                )

                st.stop()

            shutil.copytree(owner_project_path, project_path)

            project_dct_path = os.path.join(project_path, "project_dct.pkl")

            with open(project_dct_path, "rb") as f:
                st.session_state["project_dct"] = pickle.load(f)
            st.session_state["session_path"] = project_path
            st.session_state["project_path"] = project_path

            # st.write(st.session_state['project_dct'])

            dump_session_details_db(allowed_users, project_name_user)
            st.success("Project Cloned")