|
|
import streamlit as st |
|
|
import sqlite3 |
|
|
from datetime import datetime |
|
|
|
|
|
|
|
|
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() |
|
|
|
|
|
|
|
|
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() |
|
|
|
|
|
|
|
|
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] |
|
|
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() |
|
|
|
