library_management.py

import tkinter as tk
from tkinter import messagebox
from tkinter import ttk
import mysql.connector
from datetime import date

# MySQL connection
def connect_db():
    return mysql.connector.connect(
        host="localhost",
        user="root",  # Your MySQL username
        password="Anuj20040809",  # Your MySQL password
        database="lms"
    )

# Login validation
def validate_login(username, password):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username=%s AND password=%s", (username, password))
    user = cursor.fetchone()
    conn.close()
    return user

# Register new users
def register_user(username, password):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (username, password, role) VALUES (%s, %s, 'user')", (username, password))
    conn.commit()
    conn.close()

# Main library management system
def library_main_screen(role):
    main_screen = tk.Tk()
    main_screen.title("Library Management System")
    main_screen.geometry("800x600")

    # Book management
    def manage_books():
        book_frame = tk.Toplevel(main_screen)
        book_frame.title("Manage Books")
        book_frame.geometry("600x400")

        def display_books():
            for row in tree.get_children():
                tree.delete(row)
            conn = connect_db()
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM books")
            rows = cursor.fetchall()
            for row in rows:
                tree.insert("", tk.END, values=row)
            conn.close()

        def add_book():
            title = title_entry.get()
            author = author_entry.get()
            year = year_entry.get()
            conn = connect_db()
            cursor = conn.cursor()
            cursor.execute("INSERT INTO books (title, author, year) VALUES (%s, %s, %s)", (title, author, year))
            conn.commit()
            conn.close()
            display_books()

        columns = ('id', 'title', 'author', 'year', 'available')
        tree = ttk.Treeview(book_frame, columns=columns, show='headings')
        tree.heading('id', text='ID')
        tree.heading('title', text='Title')
        tree.heading('author', text='Author')
        tree.heading('year', text='Year')
        tree.heading('available', text='Available')
        tree.pack(fill=tk.BOTH, expand=True)

        title_label = tk.Label(book_frame, text="Title")
        title_label.pack()
        title_entry = tk.Entry(book_frame)
        title_entry.pack()

        author_label = tk.Label(book_frame, text="Author")
        author_label.pack()
        author_entry = tk.Entry(book_frame)
        author_entry.pack()

        year_label = tk.Label(book_frame, text="Year")
        year_label.pack()
        year_entry = tk.Entry(book_frame)
        year_entry.pack()

        add_button = tk.Button(book_frame, text="Add Book", command=add_book)
        add_button.pack()

        display_books()

    # Member management
    def manage_members():
        member_frame = tk.Toplevel(main_screen)
        member_frame.title("Manage Members")
        member_frame.geometry("600x400")

        def display_members():
            for row in tree.get_children():
                tree.delete(row)
            conn = connect_db()
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM members")
            rows = cursor.fetchall()
            for row in rows:
                tree.insert("", tk.END, values=row)
            conn.close()

        def add_member():
            name = name_entry.get()
            email = email_entry.get()
            phone = phone_entry.get()
            conn = connect_db()
            cursor = conn.cursor()
            cursor.execute("INSERT INTO members (name, email, phone) VALUES (%s, %s, %s)", (name, email, phone))
            conn.commit()
            conn.close()
            display_members()

        def remove_member():
            selected_item = tree.selection()
            if not selected_item:
                messagebox.showerror("Error", "Please select a member to remove.")
                return

            member_id = tree.item(selected_item, 'values')[0]
            
            confirmation = messagebox.askyesno("Confirm Deletion", "Are you sure you want to delete this member?")
            if confirmation:
                conn = connect_db()
                cursor = conn.cursor()
                cursor.execute("DELETE FROM members WHERE id = %s", (member_id,))
                conn.commit()
                conn.close()
                display_members()

        columns = ('id', 'name', 'email', 'phone')
        tree = ttk.Treeview(member_frame, columns=columns, show='headings')
        tree.heading('id', text='ID')
        tree.heading('name', text='Name')
        tree.heading('email', text='Email')
        tree.heading('phone', text='Phone')
        tree.pack(fill=tk.BOTH, expand=True)

        name_label = tk.Label(member_frame, text="Name")
        name_label.pack()
        name_entry = tk.Entry(member_frame)
        name_entry.pack()

        email_label = tk.Label(member_frame, text="Email")
        email_label.pack()
        email_entry = tk.Entry(member_frame)
        email_entry.pack()

        phone_label = tk.Label(member_frame, text="Phone")
        phone_label.pack()
        phone_entry = tk.Entry(member_frame)
        phone_entry.pack()

        add_button = tk.Button(member_frame, text="Add Member", command=add_member)
        add_button.pack()

        remove_button = tk.Button(member_frame, text="Remove Member", command=remove_member)
        remove_button.pack(pady=10)

        display_members()

    # Book borrowing and returning system
    def manage_transactions():
        transaction_frame = tk.Toplevel(main_screen)
        transaction_frame.title("Manage Transactions")
        transaction_frame.geometry("600x400")

        def borrow_book():
            member_id = member_id_entry.get()
            book_id = book_id_entry.get()
            conn = connect_db()
            cursor = conn.cursor()
            cursor.execute("INSERT INTO transactions (book_id, member_id, borrow_date) VALUES (%s, %s, %s)", (book_id, member_id, date.today()))
            cursor.execute("UPDATE books SET available = FALSE WHERE id = %s", (book_id,))
            conn.commit()
            conn.close()

        def return_book():
            transaction_id = transaction_id_entry.get()
            conn = connect_db()
            cursor = conn.cursor()
            cursor.execute("UPDATE transactions SET return_date = %s WHERE id = %s", (date.today(), transaction_id))
            cursor.execute("UPDATE books SET available = TRUE WHERE id = (SELECT book_id FROM transactions WHERE id = %s)", (transaction_id,))
            conn.commit()
            conn.close()

        # Borrow form
        member_id_label = tk.Label(transaction_frame, text="Member ID")
        member_id_label.pack()
        member_id_entry = tk.Entry(transaction_frame)
        member_id_entry.pack()

        book_id_label = tk.Label(transaction_frame, text="Book ID")
        book_id_label.pack()
        book_id_entry = tk.Entry(transaction_frame)
        book_id_entry.pack()

        borrow_button = tk.Button(transaction_frame, text="Borrow Book", command=borrow_book)
        borrow_button.pack()

        # Return form
        transaction_id_label = tk.Label(transaction_frame, text="Transaction ID")
        transaction_id_label.pack()
        transaction_id_entry = tk.Entry(transaction_frame)
        transaction_id_entry.pack()

        return_button = tk.Button(transaction_frame, text="Return Book", command=return_book)
        return_button.pack()

    # Display buttons based on role
    if role == 'admin':
        book_button = tk.Button(main_screen, text="Manage Books", command=manage_books)
        book_button.pack(pady=10)

        member_button = tk.Button(main_screen, text="Manage Members", command=manage_members)
        member_button.pack(pady=10)

    transaction_button = tk.Button(main_screen, text="Manage Transactions", command=manage_transactions)
    transaction_button.pack(pady=10)

    main_screen.mainloop()

# Login screen
def login_screen():
    root = tk.Tk()
    root.title("Library Management - Login")
    root.geometry("300x200")

    def attempt_login():
        user = entry_username.get()
        pwd = entry_password.get()

        user_info = validate_login(user, pwd)
        if user_info:
            messagebox.showinfo("Login Success", "Welcome!")
            root.destroy()  # Close login window
            library_main_screen(user_info[3])  # User role is in the 4th column (index 3)
        else:
            messagebox.showerror("Login Failed", "Invalid username or password.")

    # Login form
    label_username = tk.Label(root, text="Username")
    label_username.pack(pady=10)
    entry_username = tk.Entry(root)
    entry_username.pack()

    label_password = tk.Label(root, text="Password")
    label_password.pack(pady=10)
    entry_password = tk.Entry(root, show="*")
    entry_password.pack()

    login_button = tk.Button(root, text="Login", command=attempt_login)
    login_button.pack(pady=20)

    root.mainloop()

# Run the login screen
login_screen()