Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import sqlite3 | |
| from datetime import datetime | |
| # ------------------------- DATABASE ------------------------- # | |
| conn = sqlite3.connect('library.db', check_same_thread=False) | |
| c = conn.cursor() | |
| def init_db(): | |
| c.execute('''CREATE TABLE IF NOT EXISTS books ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| title TEXT, | |
| author TEXT, | |
| genre TEXT, | |
| isbn TEXT UNIQUE, | |
| status TEXT DEFAULT 'available' | |
| )''') | |
| c.execute('''CREATE TABLE IF NOT EXISTS members ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| name TEXT, | |
| email TEXT UNIQUE, | |
| phone TEXT | |
| )''') | |
| c.execute('''CREATE TABLE IF NOT EXISTS transactions ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| book_id INTEGER, | |
| member_id INTEGER, | |
| action TEXT, | |
| date TEXT, | |
| FOREIGN KEY(book_id) REFERENCES books(id), | |
| FOREIGN KEY(member_id) REFERENCES members(id) | |
| )''') | |
| c.execute('''CREATE TABLE IF NOT EXISTS reservations ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| book_id INTEGER, | |
| member_id INTEGER, | |
| reserved_at TEXT, | |
| FOREIGN KEY(book_id) REFERENCES books(id), | |
| FOREIGN KEY(member_id) REFERENCES members(id) | |
| )''') | |
| c.execute('''CREATE TABLE IF NOT EXISTS users ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| username TEXT UNIQUE, | |
| password TEXT, | |
| role TEXT DEFAULT 'member' | |
| )''') | |
| conn.commit() | |
| # ------------------------- FUNCTIONS ------------------------- # | |
| def add_book(title, author, genre, isbn): | |
| c.execute("INSERT INTO books (title, author, genre, isbn) VALUES (?, ?, ?, ?)", (title, author, genre, isbn)) | |
| conn.commit() | |
| def get_books(): | |
| c.execute("SELECT * FROM books") | |
| return c.fetchall() | |
| def issue_book(book_id, member_id): | |
| c.execute("UPDATE books SET status='issued' WHERE id=?", (book_id,)) | |
| c.execute("INSERT INTO transactions (book_id, member_id, action, date) VALUES (?, ?, 'issued', ?)", (book_id, member_id, datetime.now())) | |
| conn.commit() | |
| def return_book(book_id, member_id): | |
| c.execute("UPDATE books SET status='available' WHERE id=?", (book_id,)) | |
| c.execute("INSERT INTO transactions (book_id, member_id, action, date) VALUES (?, ?, 'returned', ?)", (book_id, member_id, datetime.now())) | |
| conn.commit() | |
| def reserve_book(book_id, member_id): | |
| c.execute("INSERT INTO reservations (book_id, member_id, reserved_at) VALUES (?, ?, ?)", (book_id, member_id, datetime.now())) | |
| conn.commit() | |
| def add_member(name, email, phone): | |
| c.execute("INSERT INTO members (name, email, phone) VALUES (?, ?, ?)", (name, email, phone)) | |
| conn.commit() | |
| def get_members(): | |
| c.execute("SELECT * FROM members") | |
| return c.fetchall() | |
| def add_user(username, password, role='member'): | |
| c.execute("INSERT INTO users (username, password, role) VALUES (?, ?, ?)", (username, password, role)) | |
| conn.commit() | |
| def authenticate_user(username, password): | |
| c.execute("SELECT * FROM users WHERE username=? AND password=?", (username, password)) | |
| return c.fetchone() | |
| def get_transactions(): | |
| c.execute("""SELECT t.id, b.title, m.name, t.action, t.date FROM transactions t | |
| JOIN books b ON t.book_id = b.id | |
| JOIN members m ON t.member_id = m.id ORDER BY t.date DESC""") | |
| return c.fetchall() | |
| def get_reservations(): | |
| c.execute("""SELECT r.id, b.title, m.name, r.reserved_at FROM reservations r | |
| JOIN books b ON r.book_id = b.id | |
| JOIN members m ON r.member_id = m.id""") | |
| return c.fetchall() | |
| def get_most_borrowed_books(): | |
| c.execute("SELECT b.title, COUNT(*) as borrow_count FROM transactions t JOIN books b ON t.book_id = b.id WHERE t.action='issued' GROUP BY b.title ORDER BY borrow_count DESC LIMIT 5") | |
| return c.fetchall() | |
| # ------------------------- STREAMLIT UI ------------------------- # | |
| st.set_page_config(page_title="Library Management System", layout="wide") | |
| if 'logged_in' not in st.session_state: | |
| st.session_state.logged_in = False | |
| st.session_state.role = '' | |
| init_db() | |
| if not st.session_state.logged_in: | |
| st.title("🔐 Login") | |
| with st.form("login"): | |
| username = st.text_input("Username") | |
| password = st.text_input("Password", type="password") | |
| submitted = st.form_submit_button("Login") | |
| if submitted: | |
| user = authenticate_user(username, password) | |
| if user: | |
| st.session_state.logged_in = True | |
| st.session_state.username = username | |
| st.session_state.role = user[3] # role | |
| st.experimental_rerun() | |
| else: | |
| st.error("Invalid credentials") | |
| else: | |
| st.title("📚 Library Management System") | |
| menu = ["Dashboard", "Add Book", "Issue/Return Book", "Reserve Book", "Audit Logs"] | |
| if st.session_state.role == 'admin': | |
| menu.append("Users") | |
| choice = st.sidebar.selectbox("Menu", menu) | |
| if choice == "Dashboard": | |
| st.subheader("Library Overview") | |
| books = get_books() | |
| members = get_members() | |
| st.metric("Total Books", len(books)) | |
| st.metric("Total Members", len(members)) | |
| st.metric("Issued Books", sum(1 for b in books if b[5] == 'issued')) | |
| st.metric("Available Books", sum(1 for b in books if b[5] == 'available')) | |
| st.subheader("📊 Most Borrowed Books") | |
| popular = get_most_borrowed_books() | |
| for title, count in popular: | |
| st.write(f"🔹 {title}: {count} times") | |
| elif choice == "Add Book": | |
| st.subheader("Add a New Book") | |
| with st.form("add_book"): | |
| title = st.text_input("Title") | |
| author = st.text_input("Author") | |
| genre = st.text_input("Genre") | |
| isbn = st.text_input("ISBN") | |
| submitted = st.form_submit_button("Add Book") | |
| if submitted: | |
| add_book(title, author, genre, isbn) | |
| st.success("✅ Book added successfully") | |
| elif choice == "Issue/Return Book": | |
| st.subheader("Manage Book Transactions") | |
| books = get_books() | |
| members = get_members() | |
| book_dict = {f"{b[1]} ({b[4]})": b[0] for b in books} | |
| member_dict = {f"{m[1]} ({m[2]})": m[0] for m in members} | |
| tab1, tab2 = st.tabs(["📦 Issue", "📥 Return"]) | |
| with tab1: | |
| selected_book = st.selectbox("Select Book to Issue", list(book_dict.keys())) | |
| selected_member = st.selectbox("Select Member", list(member_dict.keys())) | |
| if st.button("Issue Book"): | |
| issue_book(book_dict[selected_book], member_dict[selected_member]) | |
| st.success("📦 Book Issued") | |
| with tab2: | |
| selected_book = st.selectbox("Select Book to Return", list(book_dict.keys()), key='return') | |
| selected_member = st.selectbox("Select Member", list(member_dict.keys()), key='return_mem') | |
| if st.button("Return Book"): | |
| return_book(book_dict[selected_book], member_dict[selected_member]) | |
| st.success("📥 Book Returned") | |
| elif choice == "Reserve Book": | |
| st.subheader("Reserve a Book") | |
| books = get_books() | |
| members = get_members() | |
| available_books = [b for b in books if b[5] == 'available'] | |
| book_dict = {f"{b[1]} ({b[4]})": b[0] for b in available_books} | |
| member_dict = {f"{m[1]} ({m[2]})": m[0] for m in members} | |
| if book_dict and member_dict: | |
| selected_book = st.selectbox("Select Book", list(book_dict.keys())) | |
| selected_member = st.selectbox("Select Member", list(member_dict.keys())) | |
| if st.button("Reserve Book"): | |
| reserve_book(book_dict[selected_book], member_dict[selected_member]) | |
| st.success("🔒 Book Reserved") | |
| else: | |
| st.warning("No available books or members to reserve") | |
| elif choice == "Audit Logs": | |
| st.subheader("Transaction History") | |
| logs = get_transactions() | |
| st.dataframe(logs, use_container_width=True) | |
| st.subheader("Reservation Logs") | |
| res = get_reservations() | |
| st.dataframe(res, use_container_width=True) | |
| elif choice == "Users" and st.session_state.role == 'admin': | |
| st.subheader("User Management") | |
| with st.form("add_user"): | |
| username = st.text_input("Username") | |
| password = st.text_input("Password", type="password") | |
| role = st.selectbox("Role", ["admin", "librarian", "member"]) | |
| if st.form_submit_button("Add User"): | |
| add_user(username, password, role) | |
| st.success("👤 User Added") | |
| st.sidebar.write("---") | |
| if st.sidebar.button("Logout"): | |
| st.session_state.logged_in = False | |
| st.experimental_rerun() | |