Code Editor for app.py

import streamlit as st
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime, Text
from sqlalchemy.orm import Session, DeclarativeBase
import os

# Set the page config
st.set_page_config(page_title="Work Order Management System", layout="wide")

# Define the database
class Base(DeclarativeBase):
    pass

class WorkOrder(Base):
    __tablename__ = "work_orders"
    
    id = Column(Integer, primary_key=True)
    customer_name = Column(String(100), nullable=False)
    customer_email = Column(String(100))
    customer_phone = Column(String(20))
    service_type = Column(String(50), nullable=False)
    description = Column(Text)
    location = Column(String(100))
    scheduled_date = Column(DateTime)
    estimated_hours = Column(Float)
    estimated_cost = Column(Float)
    status = Column(String(20), default="Pending")
    priority = Column(String(20))
    assigned_to = Column(String(100))
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

# Create the database engine
engine = create_engine("sqlite:///work_orders.sqlite")

# Create tables
Base.metadata.create_all(bind=engine)

# App title and description
st.title("Work Order Management System")
st.write("Create and manage work orders for your service business")

# Sidebar for navigation
st.sidebar.title("Navigation")
page = st.sidebar.radio("Go to", ["Create Work Order", "View Work Orders", "Update Work Order"])

# Initialize session state for notification
if "notification" not in st.session_state:
    st.session_state.notification = None

# Display notification if present
if st.session_state.notification:
    st.success(st.session_state.notification)
    st.session_state.notification = None

# Create Work Order page
if page == "Create Work Order":
    st.header("Create New Work Order")
    
    with st.form("work_order_form", border=True):
        col1, col2 = st.columns(2)
        
        with col1:
            customer_name = st.text_input("Customer Name*", help="Required field")
            customer_email = st.text_input("Customer Email")
            customer_phone = st.text_input("Customer Phone")
            service_type = st.selectbox(
                "Service Type*", 
                ["Repair", "Installation", "Maintenance", "Inspection", "Consultation", "Other"],
                help="Required field"
            )
            priority = st.selectbox(
                "Priority",
                ["Low", "Medium", "High", "Urgent"]
            )
        
        with col2:
            location = st.text_input("Service Location")
            scheduled_date = st.date_input("Scheduled Date")
            estimated_hours = st.number_input("Estimated Hours", min_value=0.0, step=0.5)
            estimated_cost = st.number_input("Estimated Cost ($)", min_value=0.0, step=10.0)
            assigned_to = st.text_input("Assigned To")
        
        description = st.text_area("Description of Work", height=150)
        
        submitted = st.form_submit_button("Submit Work Order")
        if submitted:
            if not customer_name or not service_type:
                st.error("Please fill in all required fields marked with *")
            else:
                # Create a new work order
                new_order = WorkOrder(
                    customer_name=customer_name,
                    customer_email=customer_email,
                    customer_phone=customer_phone,
                    service_type=service_type,
                    description=description,
                    location=location,
                    scheduled_date=datetime.combine(scheduled_date, datetime.min.time()),
                    estimated_hours=estimated_hours,
                    estimated_cost=estimated_cost,
                    status="Pending",
                    priority=priority,
                    assigned_to=assigned_to
                )
                
                # Save to database
                with Session(engine) as session:
                    session.add(new_order)
                    session.commit()
                
                # Set notification for next page load
                st.session_state.notification = "Work order created successfully!"
                st.rerun()

# View Work Orders page
elif page == "View Work Orders":
    st.header("Work Order List")
    
    # Status filter
    status_filter = st.selectbox(
        "Filter by Status",
        ["All", "Pending", "In Progress", "Completed", "Cancelled"]
    )
    
    # Retrieve work orders from the database
    with Session(engine) as session:
        if status_filter == "All":
            work_orders = session.query(WorkOrder).all()
        else:
            work_orders = session.query(WorkOrder).filter(WorkOrder.status == status_filter).all()
    
    if not work_orders:
        st.info("No work orders found.")
    else:
        # Convert to DataFrame for display
        data = []
        for order in work_orders:
            data.append({
                "ID": order.id,
                "Customer": order.customer_name,
                "Service Type": order.service_type,
                "Scheduled Date": order.scheduled_date.strftime('%Y-%m-%d') if order.scheduled_date else "",
                "Status": order.status,
                "Priority": order.priority,
                "Assigned To": order.assigned_to
            })
        
        df = pd.DataFrame(data)
        st.dataframe(df, use_container_width=True)
        
        # Select work order to view details
        if work_orders:
            order_ids = [order.id for order in work_orders]
            selected_id = st.selectbox("Select Work Order ID to view details", order_ids)
            
            # Find the selected work order
            selected_order = next((order for order in work_orders if order.id == selected_id), None)
            
            if selected_order:
                st.subheader(f"Work Order #{selected_order.id} Details")
                
                col1, col2 = st.columns(2)
                with col1:
                    st.write(f"**Customer:** {selected_order.customer_name}")
                    st.write(f"**Email:** {selected_order.customer_email}")
                    st.write(f"**Phone:** {selected_order.customer_phone}")
                    st.write(f"**Service Type:** {selected_order.service_type}")
                    st.write(f"**Status:** {selected_order.status}")
                
                with col2:
                    st.write(f"**Location:** {selected_order.location}")
                    st.write(f"**Date:** {selected_order.scheduled_date.strftime('%Y-%m-%d') if selected_order.scheduled_date else 'Not scheduled'}")
                    st.write(f"**Estimated Hours:** {selected_order.estimated_hours}")
                    st.write(f"**Estimated Cost:** ${selected_order.estimated_cost:.2f}")
                    st.write(f"**Assigned To:** {selected_order.assigned_to}")
                
                st.write("**Description:**")
                st.write(selected_order.description)
                
                st.write(f"**Created:** {selected_order.created_at.strftime('%Y-%m-%d %H:%M')}")
                st.write(f"**Last Updated:** {selected_order.updated_at.strftime('%Y-%m-%d %H:%M')}")

# Update Work Order page
elif page == "Update Work Order":
    st.header("Update Work Order")
    
    # Retrieve all work order IDs
    with Session(engine) as session:
        work_orders = session.query(WorkOrder).all()
    
    if not work_orders:
        st.info("No work orders found to update.")
    else:
        # Create a selection for work order ID
        order_ids = [order.id for order in work_orders]
        selected_id = st.selectbox("Select Work Order ID to update", order_ids)
        
        # Retrieve the selected work order
        with Session(engine) as session:
            order = session.query(WorkOrder).filter(WorkOrder.id == selected_id).first()
            
            if order:
                with st.form("update_work_order_form", border=True):
                    col1, col2 = st.columns(2)
                    
                    with col1:
                        customer_name = st.text_input("Customer Name*", value=order.customer_name)
                        customer_email = st.text_input("Customer Email", value=order.customer_email or "")
                        customer_phone = st.text_input("Customer Phone", value=order.customer_phone or "")
                        service_type = st.selectbox(
                            "Service Type*", 
                            ["Repair", "Installation", "Maintenance", "Inspection", "Consultation", "Other"],
                            index=["Repair", "Installation", "Maintenance", "Inspection", "Consultation", "Other"].index(order.service_type) if order.service_type in ["Repair", "Installation", "Maintenance", "Inspection", "Consultation", "Other"] else 0
                        )
                        status = st.selectbox(
                            "Status",
                            ["Pending", "In Progress", "Completed", "Cancelled"],
                            index=["Pending", "In Progress", "Completed", "Cancelled"].index(order.status) if order.status in ["Pending", "In Progress", "Completed", "Cancelled"] else 0
                        )
                    
                    with col2:
                        location = st.text_input("Service Location", value=order.location or "")
                        scheduled_date = st.date_input("Scheduled Date", value=order.scheduled_date if order.scheduled_date else datetime.now())
                        estimated_hours = st.number_input("Estimated Hours", min_value=0.0, step=0.5, value=order.estimated_hours or 0.0)
                        estimated_cost = st.number_input("Estimated Cost ($)", min_value=0.0, step=10.0, value=order.estimated_cost or 0.0)
                        priority = st.selectbox(
                            "Priority",
                            ["Low", "Medium", "High", "Urgent"],
                            index=["Low", "Medium", "High", "Urgent"].index(order.priority) if order.priority in ["Low", "Medium", "High", "Urgent"] else 0
                        )
                        assigned_to = st.text_input("Assigned To", value=order.assigned_to or "")
                    
                    description = st.text_area("Description of Work", value=order.description or "", height=150)
                    
                    submitted = st.form_submit_button("Update Work Order")
                    if submitted:
                        if not customer_name or not service_type:
                            st.error("Please fill in all required fields marked with *")
                        else:
                            # Update the work order
                            order.customer_name = customer_name
                            order.customer_email = customer_email
                            order.customer_phone = customer_phone
                            order.service_type = service_type
                            order.description = description
                            order.location = location
                            order.scheduled_date = datetime.combine(scheduled_date, datetime.min.time())
                            order.estimated_hours = estimated_hours
                            order.estimated_cost = estimated_cost
                            order.status = status
                            order.priority = priority
                            order.assigned_to = assigned_to
                            order.updated_at = datetime.now()
                            
                            # Save to database
                            session.commit()
                            
                            # Set notification for next page load
                            st.session_state.notification = f"Work order #{selected_id} updated successfully!"
                            st.rerun()