Code Editor for app.py

import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime
import sqlite3

# Page config
st.set_page_config(page_title="Chemical Injection Dashboard", layout="wide")

# Initialize database and create table if not exists
conn = sqlite3.connect('chemical_injection.db')
c = conn.cursor()

c.execute('''
    CREATE TABLE IF NOT EXISTS chemical_injections (
        CHEM_DATE DATE,
        INJECTION_POINT TEXT,
        CHEM_PRODUCT_NAME TEXT,
        TREATED_FLUID TEXT,
        TREATED_FLOWRATE FLOAT,
        TARGET_INJECTION_RATE FLOAT,
        TARGET_PPM FLOAT,
        IS_FIELD_TEST BOOLEAN,
        IS_BATCH BOOLEAN,
        BSW_TREATED_FLUID FLOAT,
        TEMP_TREATED_FLUID FLOAT,
        PRESSURE_TREATED_FLUID FLOAT,
        TDS_TREATED_FLUID FLOAT,
        PH_TREATED_FLUID FLOAT,
        SUBSTITUTE_PRODUCT TEXT
    )
''')
conn.commit()

# Title
st.title("Chemical Injection Dashboard")

# Sidebar filters
st.sidebar.header("Filters")

# Get unique values for filters
df = pd.read_sql("SELECT * FROM chemical_injections", conn)
if not df.empty:
    injection_points = ['All'] + list(df['INJECTION_POINT'].unique())
    products = ['All'] + list(df['CHEM_PRODUCT_NAME'].unique())
    
    selected_point = st.sidebar.selectbox("Injection Point", injection_points)
    selected_product = st.sidebar.selectbox("Chemical Product", products)
    date_range = st.sidebar.date_input(
        "Date Range",
        [df['CHEM_DATE'].min(), df['CHEM_DATE'].max()]
    )
    
    # Filter data based on selections
    query = "SELECT * FROM chemical_injections WHERE 1=1"
    if selected_point != 'All':
        query += f" AND INJECTION_POINT = '{selected_point}'"
    if selected_product != 'All':
        query += f" AND CHEM_PRODUCT_NAME = '{selected_product}'"
    query += f" AND CHEM_DATE BETWEEN '{date_range[0]}' AND '{date_range[1]}'"
    
    filtered_df = pd.read_sql(query, conn)
    
    # Create dashboard layout
    col1, col2 = st.columns(2)
    
    with col1:
        # Injection Rate vs Target Rate Over Time
        fig1 = px.line(filtered_df, x='CHEM_DATE', y=['TREATED_FLOWRATE', 'TARGET_INJECTION_RATE'],
                      title='Injection Rate vs Target Rate Over Time')
        st.plotly_chart(fig1)
        
        # PPM Distribution
        fig2 = px.histogram(filtered_df, x='TARGET_PPM',
                          title='PPM Distribution')
        st.plotly_chart(fig2)
    
    with col2:
        # Product Usage by Injection Point
        fig3 = px.bar(filtered_df.groupby('INJECTION_POINT')['TREATED_FLOWRATE'].sum().reset_index(),
                     x='INJECTION_POINT', y='TREATED_FLOWRATE',
                     title='Total Product Usage by Injection Point')
        st.plotly_chart(fig3)
        
        # Temperature vs Pressure Scatter Plot
        fig4 = px.scatter(filtered_df, x='TEMP_TREATED_FLUID', y='PRESSURE_TREATED_FLUID',
                         color='INJECTION_POINT', title='Temperature vs Pressure')
        st.plotly_chart(fig4)
    
    # Key Metrics
    st.header("Key Metrics")
    metric1, metric2, metric3, metric4 = st.columns(4)
    
    with metric1:
        st.metric("Average Flow Rate", 
                 f"{filtered_df['TREATED_FLOWRATE'].mean():.2f}")
    
    with metric2:
        st.metric("Average Target PPM",
                 f"{filtered_df['TARGET_PPM'].mean():.2f}")
    
    with metric3:
        st.metric("Field Tests",
                 filtered_df['IS_FIELD_TEST'].sum())
    
    with metric4:
        st.metric("Batch Treatments",
                 filtered_df['IS_BATCH'].sum())
    
    # Data Table
    st.header("Detailed Data")
    st.dataframe(filtered_df)

else:
    st.info("No data available in the database. Please add some data to view the dashboard.")

# Close database connection
conn.close()