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, Date, ForeignKey
from sqlalchemy.orm import Session, relationship, DeclarativeBase
from sqlalchemy.ext.declarative import declarative_base
import os

# Set page config
st.set_page_config(
    page_title="Field Data Collection App",
    page_icon="📊",
    layout="wide"
)

# Database setup
class Base(DeclarativeBase):
    pass

class Project(Base):
    __tablename__ = "projects"
    
    id = Column(Integer, primary_key=True)
    unit_name = Column(String, nullable=False)
    address = Column(String, nullable=False)
    survey_date = Column(Date, nullable=False)
    parameters = Column(String, nullable=False)  # Store as comma-separated string
    
    measurements = relationship("Measurement", back_ref="project", cascade="all, delete-orphan")

class Measurement(Base):
    __tablename__ = "measurements"
    
    id = Column(Integer, primary_key=True)
    project_id = Column(Integer, ForeignKey("projects.id"))
    location = Column(String, nullable=False)
    
    # Dynamic columns for measurements will be added based on the parameters
    parameter1_value = Column(Float, nullable=True)
    parameter2_value = Column(Float, nullable=True)
    parameter3_value = Column(Float, nullable=True)
    parameter4_value = Column(Float, nullable=True)
    parameter5_value = Column(Float, nullable=True)

# Create database engine
engine = create_engine("sqlite:///field_data.sqlite")
Base.metadata.create_all(bind=engine)

# Initialize session state variables if they don't exist
if "page" not in st.session_state:
    st.session_state.page = 1

if "project_data" not in st.session_state:
    st.session_state.project_data = {
        "unit_name": "",
        "address": "",
        "survey_date": datetime.now().date(),
        "parameters": []
    }

if "measurements" not in st.session_state:
    st.session_state.measurements = pd.DataFrame(columns=["STT", "Vị trí quan trắc"])

if "edited_data" not in st.session_state:
    st.session_state.edited_data = None

# Function to move to page 2
def go_to_page_2():
    # Validate inputs
    if not st.session_state.project_data["unit_name"]:
        st.error("Vui lòng nhập tên đơn vị!")
        return
    
    if not st.session_state.project_data["address"]:
        st.error("Vui lòng nhập địa chỉ!")
        return
    
    if not st.session_state.project_data["parameters"]:
        st.error("Vui lòng chọn ít nhất một chỉ tiêu quan trắc!")
        return
    
    # Initialize the measurements dataframe with the selected parameters as columns
    st.session_state.measurements = pd.DataFrame(columns=["STT", "Vị trí quan trắc"] + st.session_state.project_data["parameters"])
    
    # Move to page 2
    st.session_state.page = 2
    st.rerun()

# Function to go back to page 1
def back_to_page_1():
    st.session_state.page = 1
    st.rerun()

# Function to save data
def save_data():
    if st.session_state.edited_data is None or len(st.session_state.edited_data) == 0:
        st.error("Không có dữ liệu để lưu!")
        return
    
    try:
        # Create a new project
        with Session(engine) as session:
            # Create project
            new_project = Project(
                unit_name=st.session_state.project_data["unit_name"],
                address=st.session_state.project_data["address"],
                survey_date=st.session_state.project_data["survey_date"],
                parameters=",".join(st.session_state.project_data["parameters"])
            )
            session.add(new_project)
            session.flush()  # Flush to get the project ID
            
            # Add measurements
            for _, row in st.session_state.edited_data.iterrows():
                if pd.isna(row["Vị trí quan trắc"]) or row["Vị trí quan trắc"] == "":
                    continue  # Skip rows without location
                
                measurement = Measurement(
                    project_id=new_project.id,
                    location=row["Vị trí quan trắc"]
                )
                
                # Add parameter values (up to 5)
                for i, param in enumerate(st.session_state.project_data["parameters"], 1):
                    if i <= 5:  # We support up to 5 parameters in the database schema
                        if param in row and not pd.isna(row[param]):
                            setattr(measurement, f"parameter{i}_value", float(row[param]))
                
                session.add(measurement)
            
            session.commit()
        
        st.success("Dữ liệu đã được lưu thành công!")
        
        # Reset to start a new project
        st.session_state.project_data = {
            "unit_name": "",
            "address": "",
            "survey_date": datetime.now().date(),
            "parameters": []
        }
        st.session_state.measurements = pd.DataFrame(columns=["STT", "Vị trí quan trắc"])
        st.session_state.edited_data = None
        st.session_state.page = 1
        st.rerun()
    
    except Exception as e:
        st.error(f"Lỗi khi lưu dữ liệu: {str(e)}")

# Page navigation
if st.session_state.page == 1:
    # Page 1: Basic Information
    st.title("Nhập thông tin cơ bản")
    
    with st.form(key="info_form"):
        col1, col2 = st.columns(2)
        
        with col1:
            unit_name = st.text_input(
                "Tên đơn vị", 
                value=st.session_state.project_data["unit_name"],
                key="unit_name"
            )
            
            address = st.text_input(
                "Địa chỉ", 
                value=st.session_state.project_data["address"],
                key="address"
            )
        
        with col2:
            survey_date = st.date_input(
                "Ngày thực hiện",
                value=st.session_state.project_data["survey_date"],
                key="survey_date"
            )
            
            available_parameters = [
                "Chỉ tiêu 1", "Chỉ tiêu 2", "Chỉ tiêu 3", 
                "Chỉ tiêu 4", "Chỉ tiêu 5", "Chỉ tiêu 6"
            ]
            
            parameters = st.multiselect(
                "Chọn chỉ tiêu quan trắc",
                options=available_parameters,
                default=st.session_state.project_data["parameters"],
                key="parameters"
            )
        
        # Update session state
        st.session_state.project_data["unit_name"] = unit_name
        st.session_state.project_data["address"] = address
        st.session_state.project_data["survey_date"] = survey_date
        st.session_state.project_data["parameters"] = parameters
        
        # Submit button
        submit_button = st.form_submit_button("Tiếp theo")
        if submit_button:
            go_to_page_2()

elif st.session_state.page == 2:
    # Page 2: Data Collection Table
    st.title("Nhập dữ liệu quan trắc")
    
    # Display project info
    st.sidebar.header("Thông tin dự án")
    st.sidebar.write(f"**Đơn vị:** {st.session_state.project_data['unit_name']}")
    st.sidebar.write(f"**Địa chỉ:** {st.session_state.project_data['address']}")
    st.sidebar.write(f"**Ngày thực hiện:** {st.session_state.project_data['survey_date'].strftime('%d/%m/%Y')}")
    st.sidebar.write(f"**Các chỉ tiêu quan trắc:** {', '.join(st.session_state.project_data['parameters'])}")
    
    # Back button
    if st.sidebar.button("Quay lại"):
        back_to_page_1()
    
    # Table for data entry
    st.subheader("Bảng nhập dữ liệu")
    
    # Initialize empty dataframe if needed
    if len(st.session_state.measurements) == 0:
        # Create at least one empty row to start with
        empty_row = {"STT": 1, "Vị trí quan trắc": ""}
        for param in st.session_state.project_data["parameters"]:
            empty_row[param] = None
        st.session_state.measurements = pd.DataFrame([empty_row])
    
    # Add STT if not already present
    if "STT" not in st.session_state.measurements.columns:
        st.session_state.measurements.insert(0, "STT", range(1, len(st.session_state.measurements) + 1))
    
    # Create an editable grid
    edited_df = st.data_editor(
        st.session_state.measurements,
        num_rows="dynamic",
        key="data_editor",
        column_config={
            "STT": st.column_config.NumberColumn(
                "STT",
                help="Số thứ tự",
                width="small",
                disabled=True
            ),
            "Vị trí quan trắc": st.column_config.TextColumn(
                "Vị trí quan trắc",
                help="Nhập vị trí lấy mẫu"
            )
        },
        hide_index=True
    )
    
    # Store the edited dataframe
    st.session_state.edited_data = edited_df
    
    # Automatically update STT after edit
    if st.session_state.edited_data is not None:
        st.session_state.edited_data["STT"] = range(1, len(st.session_state.edited_data) + 1)
    
    # Save button
    if st.button("Lưu dữ liệu", type="primary"):
        save_data()