app.py

import streamlit as st
import psycopg2
import pandas as pd
import asyncio
from io import BytesIO

def create_conn():
    conn = psycopg2.connect(dbname="neondb", user="zhanabayevasset", password="txDhFR1yl8Pi", host='ep-cool-poetry-346809.us-east-2.aws.neon.tech')
    return conn

def get_data(table, start_date, end_date):
    conn = create_conn()
    cursor = conn.cursor()
    cursor.execute(f"""select * from {table}_params 
                        where created_at >= '{start_date}'
                        and created_at <= '{end_date}'
                        order by created_at desc""")
    data = cursor.fetchall()
    cols =[]
    for i in cursor.description:
        cols.append(i[0])
    cursor.close()
    conn.close()
    return pd.DataFrame(data, columns = cols)
def to_excel(df):
    output = BytesIO()
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    df.to_excel(writer, index=False, sheet_name='Sheet1')
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    format1 = workbook.add_format({'num_format': '0.00'}) 
    worksheet.set_column('A:A', None, format1)  
    writer.close()
    processed_data = output.getvalue()
    return processed_data

st.set_page_config(page_title='Seyco QC page')
st.title('Working line operations monitoring system')

val = st.selectbox('select product', [None,'PVC', 'PPR-C','Fitting Vodopr', 'Fitting Canal', 'Fitting Other', 'PERT', 'Window_door', 'bead','laminated_profile','laminated_other', 'windowsill'])
start_date = st.date_input('start_date')
end_date = st.date_input('end_date')

if val == 'PVC':
    df = get_data('pvc', start_date, end_date)
    st.write(f"weight: {df['weight'].sum()}")
    brands = df['brand'].unique().tolist()
    nom_diam = df['nominal_diameter'].unique().tolist()
    nom_length = df['nominal_length'].unique().tolist()
    brands.append(None)
    nom_diam.append(None)
    nom_length.append(None)
    brand = st.selectbox('select brand', brands)
    diam = st.selectbox('select nominal diameter', nom_diam)
    length = st.selectbox('select nominal length', nom_length)
    
    if (brand == None) | (length == None) | (diam == None):
        df = df.set_index('created_at')
        st.dataframe(df)
        df_xlsx = to_excel(df)
        st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'pvc.xlsx')
    else:
            df = df[df['brand'] == brand]
            df = df[df['nominal_diameter'] == diam]
            df = df[df['nominal_length'] == length]
            df = df.set_index('created_at')
            st.dataframe(df)
            df_xlsx = to_excel(df)
            st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'pvc.xlsx')
            st.line_chart(df['view'])
            st.line_chart(df['functionality'])
            st.line_chart(df['diameter'])
            st.line_chart(df[df['weight'].isnull()==False]['weight'])
            st.line_chart(df[df['width'].isnull() == False]['width'])
            st.line_chart(df[df['mark_control'].isnull() == False]['mark_control'])
            st.line_chart(df[df['strength'].isnull() == False]['strength'])
            st.line_chart(df[df['length'].isnull() == False]['length'])
    

if val == 'PPR-C':
    df = get_data('pprc', start_date, end_date)
    st.write(f"weight: {df['weight'].sum()}")
    brands = df['brand'].unique().tolist()
    nom_diam = df['nominal_diameter'].unique().tolist()
    brand = st.selectbox('select brand', brands)
    diam = st.selectbox('select nominal diameter', nom_diam)
    brands.append(None)
    nom_diam.append(None)
    if (brand == None) |  (diam == None):
        df = df.set_index('created_at')
        st.dataframe(df)
        df_xlsx = to_excel(df)
        st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'pprc.xlsx')
    else:
        
            df = df[df['brand'] == brand]
            df = df[df['nominal_diameter'] == diam]
            df = df.set_index('created_at')
            st.dataframe(df)
            df_xlsx = to_excel(df)
            st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'pprc.xlsx')
            st.line_chart(df['view'])
            st.line_chart(df['diameter'])
            st.line_chart(df['width'])
            st.line_chart(df[df['weight'].isnull()==False]['weight'])
            st.line_chart(df[df['mark_control'].isnull()==False]['mark_control'])

if val == 'Fitting Vodopr':
    df = get_data('fitting_vodop', start_date, end_date)
    st.write(f"weight: {df['weight'].sum()}")
    brands = df['brand'].unique().tolist()
    stanoks = df['stanok'].unique().tolist()
    fit_names = df['fitting_name'].unique().tolist()
    brands.append(None)
    stanoks.append(None)
    fit_names.append(None)

    brand = st.selectbox('select brand', brands)
    stanok = st.selectbox('select line', stanoks)
    fit_name = st.selectbox('select fitting name', fit_names)
    if (brand == None) |  (stanok == None) | (fit_name==None):
        df = df.set_index('created_at')
        st.dataframe(df)
        df_xlsx = to_excel(df)
        st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'fitting vodop.xlsx')
    else:
       
            df = df[df['brand'] == brand]
            df = df[df['stanok'] == stanok]
            df = df[df['fitting_name'] == fit_name]
            df = df.set_index('created_at')
            st.dataframe(df)
            df_xlsx = to_excel(df)
            st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'fitting vodop.xlsx')
            st.line_chart(df['view'])
            st.line_chart(df['functionality'])
            st.line_chart(df[df['weight'].isnull()==False]['weight'])

if val == 'Fitting Canal':
    df = get_data('fitting_canal', start_date, end_date)
    st.write(f"weight: {df['weight'].sum()}")
    brands = df['brand'].unique().tolist()
    stanoks = df['stanok'].unique().tolist()
    fit_names = df['fitting_name'].unique().tolist()
    nom_diams = df['nominal_diameter'].unique().tolist()
    type_materials = df['type_syr'].unique().tolist()
    prod_types = df['product_type'].unique().tolist()
    brand = st.selectbox('select brand', brands)
    stanok = st.selectbox('select line', stanoks)
    fit_name = st.selectbox('select fitting name', fit_names)
    nom_diam = st.selectbox('select nom diameter', nom_diams)
    type_mat = st.selectbox('select type material', type_materials)
    prod_type = st.selectbox('select product_type', prod_types)
    if (brand==None) | (stanok == None) | (fit_name == None) | (nom_diam == None) | (type_mat == None) | (prod_type == None):
        df = df.set_index('created_at')
        st.dataframe(df)
        df_xlsx = to_excel(df)
        st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'fitting canal.xlsx')
    # if (brand in brands) & (stanok in stanoks) & (fit_name in fit_names) & (nom_diam in nom_diams) & (prod_type in prod_types) & (type_mat in type_materials):
    else: 
     
            df = df[df['brand'] == brand]
            df = df[df['stanok'] == stanok]
            df = df[df['fitting_name'] == fit_name]
            df = df[df['type_syr'] == type_mat]
            df = df[df['product_type'] == prod_type]
            df = df.set_index('created_at')
            st.dataframe(df)
            df_xlsx = to_excel(df)
            st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'fitting canal.xlsx')
            st.line_chart(df['view'])
            st.line_chart(df[df['functionality'].isnull()==False]['functionality'])
            st.line_chart(df[df['weight'].isnull()==False]['weight'])

if val == 'Fitting Other':
    df = get_data('fitting_other', start_date, end_date)
    brands = df['brand'].unique().tolist()
    stanoks = df['stanok'].unique().tolist()
    fit_names = df['fitting_name'].unique().tolist()
    nom_sizes = df['nominal_size'].unique().tolist()
    colors = df['color'].unique().tolist()
    brand = st.selectbox('select brand', brands)
    stanok = st.selectbox('select line', stanoks)
    fit_name = st.selectbox('select fitting name', fit_names)
    nom_size = st.selectbox('select nom size', nom_sizes)
    color = st.selectbox('select color', colors)

    if (brand==None) | (stanok == None) | (fit_name == None) | (nom_size == None) | (color == None):
        df = df.set_index('created_at')
        st.dataframe(df)
        df_xlsx = to_excel(df)
        st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'fitting other.xlsx')
    else:
    # if (brand in brands) & (stanok in stanoks) & (fit_name in fit_names) & (nom_size in nom_sizes) & (color in colors):
                df = df[df['brand'] == brand]
                df = df[df['stanok'] == stanok]
                df = df[df['fitting_name'] == fit_name]
                df = df[df['nominal_size'] == nom_size]
                df = df.set_index('created_at')
                st.dataframe(df)
                df_xlsx = to_excel(df)
                st.download_button(label='📥 Download Current Result',
                                                data=df_xlsx ,
                                                file_name= 'fitting other.xlsx')
                st.line_chart(df['view'])
                st.line_chart(df['functionality'])
                st.line_chart(df['view'])
                # st.line_chart(df['color'])

        
if val == 'PERT':
    df = get_data('pert', start_date, end_date)
    st.write(f"weight: {df['weight'].sum()}")
    brands = df['brand'].unique().tolist()
    brand = st.selectbox('select brand', brands)
    if brand ==None:
        df = df.set_index('created_at')
        st.dataframe(df)
        df_xlsx = to_excel(df)
        st.download_button(label='📥 Download Current Result',
                                            data=df_xlsx ,
                                            file_name= 'pert.xlsx')
    # if (brand in brands):
    else:
        # if st.button('show data') == True:
            df = df[df['brand'] == brand]
            df = df.set_index('created_at')
            st.dataframe(df)
            df_xlsx = to_excel(df)
            st.download_button(label='📥 Download Current Result',
                                                data=df_xlsx ,
                                                file_name= 'pert.xlsx')
            st.line_chart(df['view'])
            st.line_chart(df['outer_diameter'])
            st.line_chart(df['width_st'])
            st.line_chart(df['weight_b'])
            st.line_chart(df[df['weight'].isnull()==False]['weight'])
            st.line_chart(df[df['mark_control'].isnull()==False]['mark_control'])
if val == 'Window_door':
         df = get_data('window_door', start_date, end_date)
         st.dataframe(df)
if val == 'bead':
        df = get_data('bead', start_date, end_date)
        st.dataframe(df)
if val == 'laminated_profile':
        df = get_data('laminated_profile', start_date, end_date)
        st.dataframe(df)
if val == 'laminated_other':
        df = get_data('laminated_other', start_date, end_date)
        st.dataframe(df)
if val == 'windowsill':
        df = get_data('windowsill', start_date, end_date)
        st.dataframe(df)