import os import csv import tkinter as tk from tkinter import ttk, messagebox import psycopg2 DB_CONFIG = { "dbname": "dbwork", "user": "tokarev_pi", "password": "#8&3u&52#&D", "host": "edu-pg.itiscaf.ru", "port": "5432" } class BookWarehouseApp(tk.Tk): def __init__(self): super().__init__() self.title("Информационная система «Склад книг»") self.geometry("1150x700") # Настройка визуального стиля (UI/UX) self.setup_styles() self.current_user = None self.current_role = None self.show_login_window() def setup_styles(self): """Настраивает цветовую схему, шрифты и стили компонентов интерфейса.""" self.style = ttk.Style() self.style.theme_use('clam') bg_main = "#F4F6F9" fg_main = "#333333" primary_color = "#1A73E8" btn_active = "#1557B0" self.style.configure('.', background=bg_main, foreground=fg_main, font=('Segoe UI', 10)) self.style.configure('TNotebook', background=bg_main, borderwidth=0) self.style.configure('TNotebook.Tab', font=('Segoe UI', 10, 'bold'), padding=[15, 6], background='#E0E4EC') self.style.map('TNotebook.Tab', background=[('selected', primary_color)], foreground=[('selected', 'white')]) self.style.configure('TButton', font=('Segoe UI', 10, 'bold'), background=primary_color, foreground='white', borderwidth=0, padding=6) self.style.map('TButton', background=[('active', btn_active), ('disabled', '#D2D6DE')], foreground=[('disabled', '#959DA5')]) self.style.configure('Treeview', font=('Segoe UI', 10), rowheight=28, background='white', fieldbackground='white', borderwidth=1) self.style.configure('Treeview.Heading', font=('Segoe UI', 10, 'bold'), background='#E6E9ED', foreground=fg_main, padding=5) self.style.map('Treeview', background=[('selected', primary_color)], foreground=[('selected', 'white')]) self.style.configure('TLabel', background=bg_main, font=('Segoe UI', 10)) self.style.configure('TLabelframe', background=bg_main, borderwidth=1, relief="solid") self.style.configure('TLabelframe.Label', font=('Segoe UI', 10, 'bold'), foreground=primary_color, background=bg_main) def connect_db(self): """Устанавливает соединение с БД PostgreSQL.""" conn = psycopg2.connect(**DB_CONFIG) conn.set_client_encoding('UTF8') return conn def safe_decode(self, value): """Безопасно преобразует любые типы данных из БД в строку.""" if value is None: return "" if isinstance(value, bytes): return value.decode('utf-8', errors='ignore') return str(value) def show_login_window(self): """Отрисовывает центрированное окно авторизации и регистрации.""" self.configure(background="#F4F6F9") self.login_frame = ttk.Labelframe(self, text=" Авторизация в системе ", padding=25) self.login_frame.place(relx=0.5, rely=0.5, anchor="center") ttk.Label(self.login_frame, text="Добро пожаловать!", font=("Segoe UI", 16, "bold"), foreground="#1A73E8").grid(row=0, column=0, columnspan=2, pady=15) ttk.Label(self.login_frame, text="Логин:").grid(row=1, column=0, sticky="e", padx=5, pady=8) self.ent_login = ttk.Entry(self.login_frame, font=('Segoe UI', 11), width=25) self.ent_login.grid(row=1, column=1, padx=5, pady=8) ttk.Label(self.login_frame, text="Пароль:").grid(row=2, column=0, sticky="e", padx=5, pady=8) self.ent_password = ttk.Entry(self.login_frame, show="*", font=('Segoe UI', 11), width=25) self.ent_password.grid(row=2, column=1, padx=5, pady=8) btn_frame = ttk.Frame(self.login_frame) btn_frame.grid(row=3, column=0, columnspan=2, pady=20) ttk.Button(btn_frame, text="Войти", command=self.handle_login).pack(side="left", padx=5, ipadx=10) ttk.Button(btn_frame, text="Регистрация", command=self.show_register_dialog).pack(side="left", padx=5, ipadx=10) def show_register_dialog(self): """Окно создания нового аккаунта (Регистрация).""" dialog = tk.Toplevel(self) dialog.title("Регистрация нового пользователя") dialog.geometry("400x360") dialog.grab_set() dialog.configure(background="#F4F6F9") ttk.Label(dialog, text="Регистрация аккаунта", font=("Segoe UI", 14, "bold"), foreground="#1A73E8").pack(pady=15) ttk.Label(dialog, text="Придумайте логин:").pack(pady=2, anchor="w", padx=40) ent_reg_login = ttk.Entry(dialog, font=('Segoe UI', 11), width=30) ent_reg_login.pack(padx=40, pady=4, fill="x") ttk.Label(dialog, text="Придумайте пароль:").pack(pady=2, anchor="w", padx=40) ent_reg_password = ttk.Entry(dialog, show="*", font=('Segoe UI', 11), width=30) ent_reg_password.pack(padx=40, pady=4, fill="x") ttk.Label(dialog, text="Повторите пароль:").pack(pady=2, anchor="w", padx=40) ent_reg_confirm = ttk.Entry(dialog, show="*", font=('Segoe UI', 11), width=30) ent_reg_confirm.pack(padx=40, pady=4, fill="x") def execute_registration(): login = ent_reg_login.get().strip() password = ent_reg_password.get().strip() confirm = ent_reg_confirm.get().strip() if not login or not password or not confirm: messagebox.showerror("Ошибка", "Заполните все доступные поля!", parent=dialog) return if password != confirm: messagebox.showerror("Ошибка", "Введенные пароли не совпадают!", parent=dialog) return try: conn = self.connect_db() with conn: with conn.cursor() as cur: # Точное соответствие колонкам: login, password_hash, role_name cur.execute(""" INSERT INTO Users (login, password_hash, role_name, employee_id) VALUES (%s, %s, 'читатель', NULL); """, (login, password)) conn.close() dialog.destroy() messagebox.showinfo("Успех", "Регистрация завершена! Роль по умолчанию: читатель. Теперь вы можете войти.") except psycopg2.errors.UniqueViolation: messagebox.showerror("Ошибка", "Пользователь с таким логином уже существует!", parent=dialog) except Exception as e: messagebox.showerror("Ошибка СУБД", f"Не удалось выполнить регистрацию:\n{e}", parent=dialog) ttk.Button(dialog, text="✨ Зарегистрироваться", command=execute_registration).pack(pady=22, ipadx=10) def handle_login(self): """Проверяет данные через хранимую функцию getrole.""" login = self.ent_login.get().strip() password = self.ent_password.get().strip() if not login or not password: messagebox.showerror("Ошибка", "Заполните все поля!") return try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('SELECT getRole(%s::varchar, %s::varchar);', (login, password)) row = cur.fetchone() conn.close() if row and row[0] is not None: role_raw = self.safe_decode(row[0]).strip().upper() self.current_user = login # Если роль содержит ADMIN или АДМИН — даем полные права, иначе — обычные self.current_role = "ADMIN" if any(x in role_raw for x in ["АДМИН", "ADMIN"]) else "EMPLOYEE" self.login_frame.destroy() self.show_main_window() else: messagebox.showerror("Ошибка", "Неверный логин или пароль") except Exception as e: messagebox.showerror("Ошибка подключения", f"Ошибка базы данных:\n{e}") def show_main_window(self): """Инициализирует главное рабочее пространство системы с вкладками.""" status_frame = ttk.Frame(self, padding=10, relief="groove") status_frame.pack(side="top", fill="x") ttk.Label(status_frame, text=f"👤 Пользователь: {self.current_user} | 💼 Уровень прав: {self.current_role}", font=("Segoe UI", 10, "bold"), foreground="#555555").pack(side="left") ttk.Button(status_frame, text="🚪 Выйти из аккаунта", command=self.logout).pack(side="right") self.notebook = ttk.Notebook(self, padding=10) self.notebook.pack(expand=True, fill="both") self.create_books_tab() self.create_warehouse_tab() self.create_reservations_tab() self.create_personnel_tab() def logout(self): """Уничтожает текущий интерфейс и возвращает пользователя на форму логина.""" for widget in self.winfo_children(): widget.destroy() self.show_login_window() # --- ВСПОМОГАТЕЛЬНЫЕ МЕТОДЫ СВЯЗЕЙ СУБД --- def get_authors_list(self): return self._fetch_lookup('SELECT author_id, author_name FROM Authors ORDER BY author_name;') def get_categories_list(self): return self._fetch_lookup('SELECT category_id, category_name FROM Categories ORDER BY category_name;') def get_books_list(self): return self._fetch_lookup('SELECT book_id, title FROM Books ORDER BY title;') def get_personnel_list(self): return self._fetch_lookup("SELECT employee_id, surname || ' ' || name FROM Personnel ORDER BY surname;") def get_positions_list(self): """Загружает список доступных должностей для Personnel constraint.""" return self._fetch_lookup("SELECT position_id, position_name FROM Positions ORDER BY position_name;") def _fetch_lookup(self, sql): res = [] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute(sql) res = [(row[0], self.safe_decode(row[1])) for row in cur.fetchall()] conn.close() except Exception as e: print(f"Ошибка lookup: {e}") return res # ========================================== # ВКЛАДКА 1: КНИГИ (ПРОСМОТР + CRUD) # ========================================== def create_books_tab(self): tab = ttk.Frame(self.notebook, padding=10) self.notebook.add(tab, text="📚 Книги") search_frame = ttk.LabelFrame(tab, text=" Поиск книги по названию ", padding=10) search_frame.pack(fill="x", padx=5, pady=5) ttk.Label(search_frame, text="Название:").pack(side="left", padx=5) self.book_search_var = tk.StringVar() self.book_search_var.trace_add("write", lambda *args: self.load_books_data()) ttk.Entry(search_frame, textvariable=self.book_search_var, font=('Segoe UI', 10)).pack(side="left", fill="x", expand=True, padx=5, pady=5) ttk.Button(search_frame, text="📥 Экспорт в CSV", command=lambda: self.export_to_csv(self.books_tree, "books_export.csv")).pack(side="right", padx=5) columns = ("id", "title", "author", "category", "price") self.books_tree = ttk.Treeview(tab, columns=columns, show="headings") self.books_tree.heading("id", text="ID") self.books_tree.heading("title", text="Название книги") self.books_tree.heading("author", text="Автор") self.books_tree.heading("category", text="Категория") self.books_tree.heading("price", text="Цена (руб.)") self.books_tree.column("id", width=60, anchor="center", stretch=tk.NO) self.books_tree.column("title", width=300, anchor="w", stretch=tk.YES) self.books_tree.column("author", width=200, anchor="w", stretch=tk.YES) self.books_tree.column("category", width=150, anchor="w", stretch=tk.YES) self.books_tree.column("price", width=100, anchor="e", stretch=tk.NO) self.books_tree.pack(expand=True, fill="both", padx=5, pady=5) btn_frame = ttk.Frame(tab, padding=5) btn_frame.pack(fill="x", padx=5, pady=5) ttk.Button(btn_frame, text="➕ Добавить книгу", command=self.add_book_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="✏️ Редактировать", command=self.edit_book_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="❌ Удалить книгу", command=self.delete_book).pack(side="left", padx=5) self.load_books_data() def load_books_data(self): for i in self.books_tree.get_children(): self.books_tree.delete(i) search_query = self.book_search_var.get() try: conn = self.connect_db() with conn: with conn.cursor() as cur: query = """ SELECT b.book_id, b.title, a.author_name, c.category_name, b.price FROM Books b JOIN Authors a ON b.author_id = a.author_id JOIN Categories c ON b.category_id = c.category_id WHERE b.title ILIKE %s ORDER BY b.book_id DESC; """ cur.execute(query, (f"%{search_query}%",)) for row in cur.fetchall(): self.books_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row]) conn.close() except Exception as e: print(f"Ошибка загрузки книг: {e}") def add_book_dialog(self): authors_list = self.get_authors_list() categories_list = self.get_categories_list() if not authors_list or not categories_list: messagebox.showerror("Ошибка", "Списки авторов или категорий пусты в БД!") return dialog = tk.Toplevel(self) dialog.title("Добавление книги") dialog.geometry("400x320") dialog.grab_set() ttk.Label(dialog, text="Название книги:").pack(pady=2, anchor="w", padx=20) ent_title = ttk.Entry(dialog, width=40) ent_title.pack(padx=20, pady=2, fill="x") ttk.Label(dialog, text="Автор:").pack(pady=2, anchor="w", padx=20) cb_author = ttk.Combobox(dialog, values=[a[1] for a in authors_list], state="readonly") cb_author.pack(padx=20, pady=2, fill="x") cb_author.current(0) ttk.Label(dialog, text="Категория:").pack(pady=2, anchor="w", padx=20) cb_category = ttk.Combobox(dialog, values=[c[1] for c in categories_list], state="readonly") cb_category.pack(padx=20, pady=2, fill="x") cb_category.current(0) ttk.Label(dialog, text="Цена:").pack(pady=2, anchor="w", padx=20) ent_price = ttk.Entry(dialog, width=40) ent_price.pack(padx=20, pady=2, fill="x") def save(): title = ent_title.get().strip() price_str = ent_price.get().strip() if not title or not price_str: messagebox.showerror("Ошибка", "Заполните все поля!", parent=dialog) return try: price = float(price_str) except ValueError: messagebox.showerror("Ошибка", "Цена должна быть числом!", parent=dialog) return author_id = authors_list[cb_author.current()][0] category_id = categories_list[cb_category.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('INSERT INTO Books (title, price, author_id, category_id) VALUES (%s, %s, %s, %s)', (title, price, author_id, category_id)) conn.close() self.load_books_data() dialog.destroy() messagebox.showinfo("Успех", "Книга успешно добавлена!") except Exception as e: messagebox.showerror("Ошибка", f"Не удалось добавить книгу: {e}", parent=dialog) ttk.Button(dialog, text="💾 Сохранить", command=save).pack(pady=20) def edit_book_dialog(self): selected = self.books_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите книгу для редактирования!") return book_values = self.books_tree.item(selected[0])['values'] book_id, current_title, current_author_name, current_category_name, current_price = book_values authors_list = self.get_authors_list() categories_list = self.get_categories_list() dialog = tk.Toplevel(self) dialog.title(f"Редактирование книги (ID: {book_id})") dialog.geometry("400x320") dialog.grab_set() ttk.Label(dialog, text="Название книги:").pack(pady=2, anchor="w", padx=20) ent_title = ttk.Entry(dialog, width=40) ent_title.pack(padx=20, pady=2, fill="x") ent_title.insert(0, current_title) ttk.Label(dialog, text="Автор:").pack(pady=2, anchor="w", padx=20) author_names = [a[1] for a in authors_list] cb_author = ttk.Combobox(dialog, values=author_names, state="readonly") cb_author.pack(padx=20, pady=2, fill="x") if current_author_name in author_names: cb_author.current(author_names.index(current_author_name)) ttk.Label(dialog, text="Категория:").pack(pady=2, anchor="w", padx=20) category_names = [c[1] for c in categories_list] cb_category = ttk.Combobox(dialog, values=category_names, state="readonly") cb_category.pack(padx=20, pady=2, fill="x") if current_category_name in category_names: cb_category.current(category_names.index(current_category_name)) ttk.Label(dialog, text="Цена:").pack(pady=2, anchor="w", padx=20) ent_price = ttk.Entry(dialog, width=40) ent_price.pack(padx=20, pady=2, fill="x") ent_price.insert(0, current_price) def update_save(): title = ent_title.get().strip() price_str = ent_price.get().strip() if not title or not price_str: messagebox.showerror("Ошибка", "Заполните все поля!", parent=dialog) return try: price = float(price_str) except ValueError: messagebox.showerror("Ошибка", "Цена должна быть числом!", parent=dialog) return author_id = authors_list[cb_author.current()][0] category_id = categories_list[cb_category.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute(''' UPDATE Books SET title = %s, price = %s, author_id = %s, category_id = %s WHERE book_id = %s; ''', (title, price, author_id, category_id, book_id)) conn.close() self.load_books_data() dialog.destroy() messagebox.showinfo("Успех", "Данные книги успешно обновлены!") except Exception as e: messagebox.showerror("Ошибка", f"Не удалось обновить данные: {e}", parent=dialog) ttk.Button(dialog, text="🔄 Обновить", command=update_save).pack(pady=20) def delete_book(self): selected = self.books_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите книгу для удаления!") return book_id = self.books_tree.item(selected[0])['values'][0] book_title = self.books_tree.item(selected[0])['values'][1] if messagebox.askyesno("Подтверждение", f"Вы уверены, что хотите удалить '{book_title}'?\nЭто каскадно очистит связанные остатки склада!"): try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('DELETE FROM Warehouse WHERE book_id = %s;', (book_id,)) cur.execute('DELETE FROM Reservation_Books WHERE book_id = %s;', (book_id,)) cur.execute('DELETE FROM Books WHERE book_id = %s;', (book_id,)) conn.close() self.load_books_data() self.load_warehouse_data() messagebox.showinfo("Успех", f"Книга '{book_title}' успешно удалена!") except Exception as e: messagebox.showerror("Ошибка удаления", f"Критический сбой СУБД:\n{e}") # ========================================== # ВКЛАДКА 2: СКЛАД (ПРОСМОТР + CRUD) # ========================================== def create_warehouse_tab(self): tab = ttk.Frame(self.notebook, padding=10) self.notebook.add(tab, text="🏢 Склад") action_frame = ttk.Frame(tab, padding=5) action_frame.pack(fill="x", side="top", pady=5) ttk.Button(action_frame, text="📥 Экспорт остатков (CSV)", command=lambda: self.export_to_csv(self.wh_tree, "warehouse_export.csv")).pack(side="right", padx=5) columns = ("book_id", "title", "author", "quantity", "price", "total_cost") self.wh_tree = ttk.Treeview(tab, columns=columns, show="headings") self.wh_tree.heading("book_id", text="ID Книги") self.wh_tree.heading("title", text="Название книги") self.wh_tree.heading("author", text="Автор") self.wh_tree.heading("quantity", text="Количество на складе") self.wh_tree.heading("price", text="Цена за ед.") self.wh_tree.heading("total_cost", text="Общая стоимость") self.wh_tree.column("book_id", width=80, anchor="center") self.wh_tree.column("title", width=250, anchor="w") self.wh_tree.column("author", width=180, anchor="w") self.wh_tree.column("quantity", width=130, anchor="center") self.wh_tree.column("price", width=100, anchor="e") self.wh_tree.column("total_cost", width=120, anchor="e") self.wh_tree.pack(expand=True, fill="both", padx=5, pady=5) btn_frame = ttk.Frame(tab, padding=5) btn_frame.pack(fill="x", padx=5, pady=5) ttk.Button(btn_frame, text="➕ Принять на склад", command=self.add_warehouse_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="✏️ Изменить количество", command=self.edit_warehouse_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="❌ Списать со склада", command=self.delete_warehouse).pack(side="left", padx=5) self.load_warehouse_data() def load_warehouse_data(self): """Загрузка данных из системного представления BookStockInfo.""" for i in self.wh_tree.get_children(): self.wh_tree.delete(i) try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('SELECT * FROM BookStockInfo;') for row in cur.fetchall(): self.wh_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row]) conn.close() except Exception as e: print(f"Ошибка загрузки представления склада: {e}") def add_warehouse_dialog(self): books_list = self.get_books_list() if not books_list: messagebox.showerror("Ошибка", "Список книг пуст!") return dialog = tk.Toplevel(self) dialog.title("Приемка на склад") dialog.geometry("400x220") dialog.grab_set() ttk.Label(dialog, text="Выберите книгу:").pack(pady=5, anchor="w", padx=20) cb_book = ttk.Combobox(dialog, values=[b[1] for b in books_list], state="readonly") cb_book.pack(padx=20, pady=2, fill="x") cb_book.current(0) ttk.Label(dialog, text="Количество:").pack(pady=5, anchor="w", padx=20) ent_qty = ttk.Entry(dialog, width=40) ent_qty.pack(padx=20, pady=2, fill="x") def save(): qty_str = ent_qty.get().strip() if not qty_str: messagebox.showerror("Ошибка", "Укажите количество!", parent=dialog) return try: qty = int(qty_str) if qty < 0: raise ValueError except ValueError: messagebox.showerror("Ошибка", "Должно быть целым положительным числом!", parent=dialog) return book_id = books_list[cb_book.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('SELECT quantity FROM Warehouse WHERE book_id = %s;', (book_id,)) row = cur.fetchone() if row: cur.execute('UPDATE Warehouse SET quantity = quantity + %s WHERE book_id = %s;', (qty, book_id)) else: cur.execute('INSERT INTO Warehouse (book_id, quantity) VALUES (%s, %s);', (book_id, qty)) conn.close() self.load_warehouse_data() dialog.destroy() messagebox.showinfo("Успех", "Товар оприходован!") except Exception as e: messagebox.showerror("Ошибка", f"Не удалось выполнить операцию: {e}", parent=dialog) ttk.Button(dialog, text="💾 Оприходовать", command=save).pack(pady=20) def edit_warehouse_dialog(self): selected = self.wh_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите строку для корректировки остатка!") return values = self.wh_tree.item(selected[0])['values'] book_id, book_title, _, current_qty, _, _ = values dialog = tk.Toplevel(self) dialog.title("Корректировка остатков") dialog.geometry("400x180") dialog.grab_set() ttk.Label(dialog, text=f"Книга: {book_title}", font=("Segoe UI", 10, "bold"), wraplength=350).pack(pady=10, anchor="w", padx=20) ttk.Label(dialog, text="Новое количество на складе:").pack(pady=2, anchor="w", padx=20) ent_qty = ttk.Entry(dialog, width=40) ent_qty.pack(padx=20, pady=2, fill="x") ent_qty.insert(0, str(current_qty)) def update(): qty_str = ent_qty.get().strip() if not qty_str: messagebox.showerror("Ошибка", "Заполните поле!", parent=dialog) return try: qty = int(qty_str) if qty < 0: raise ValueError except ValueError: messagebox.showerror("Ошибка", "Введите целое положительное число!", parent=dialog) return try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('UPDATE Warehouse SET quantity = %s WHERE book_id = %s;', (qty, book_id)) conn.close() self.load_warehouse_data() dialog.destroy() messagebox.showinfo("Успех", "Количество обновлено!") except Exception as e: messagebox.showerror("Ошибка", f"Не удалось обновить: {e}", parent=dialog) ttk.Button(dialog, text="🔄 Обновить остаток", command=update).pack(pady=15) def delete_warehouse(self): selected = self.wh_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите позицию для списания!") return values = self.wh_tree.item(selected[0])['values'] book_id, book_title = values[0], values[1] if messagebox.askyesno("Подтверждение", f"Удалить '{book_title}' со склада? Строка остатков будет стерта."): try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('DELETE FROM Warehouse WHERE book_id = %s;', (book_id,)) conn.close() self.load_warehouse_data() messagebox.showinfo("Успех", "Позиция списана.") except Exception as e: messagebox.showerror("Ошибка списания", f"Не удалось удалить: {e}") # ========================================== # ВКЛАДКА 3: БРОНИРОВАНИЕ (ЖУРНАЛ + CRUD) # ========================================== def create_reservations_tab(self): tab = ttk.Frame(self.notebook, padding=10) self.notebook.add(tab, text="📋 Бронирование") columns = ("res_id", "date", "employee") self.res_tree = ttk.Treeview(tab, columns=columns, show="headings") self.res_tree.heading("res_id", text="ID Брони") self.res_tree.heading("date", text="Дата оформления") self.res_tree.heading("employee", text="Ответственный сотрудник") self.res_tree.column("res_id", width=100, anchor="center") self.res_tree.column("date", width=250, anchor="center") self.res_tree.column("employee", width=400, anchor="w") self.res_tree.pack(expand=True, fill="both", padx=5, pady=5) btn_frame = ttk.Frame(tab, padding=5) btn_frame.pack(fill="x", padx=5, pady=5) ttk.Button(btn_frame, text="➕ Создать новую бронь", command=self.add_reservation_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="✏️ Изменить сотрудника", command=self.edit_reservation_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="❌ Аннулировать бронь", command=self.delete_reservation).pack(side="left", padx=5) self.load_reservations_data() def load_reservations_data(self): for i in self.res_tree.get_children(): self.res_tree.delete(i) try: conn = self.connect_db() with conn: with conn.cursor() as cur: query = """ SELECT r.reservation_id, r.reservation_date, p.surname || ' ' || p.name || ' ' || COALESCE(p.patronymic, '') FROM Reservations r JOIN Personnel p ON r.employee_id = p.employee_id ORDER BY r.reservation_id DESC; """ cur.execute(query) for row in cur.fetchall(): self.res_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row]) conn.close() except Exception as e: print(f"Ошибка загрузки броней: {e}") def add_reservation_dialog(self): pers_list = self.get_personnel_list() if not pers_list: messagebox.showerror("Ошибка", "Штат персонала пуст!") return dialog = tk.Toplevel(self) dialog.title("Новое бронирование") dialog.geometry("400x180") dialog.grab_set() ttk.Label(dialog, text="Выберите ответственного сотрудника:").pack(pady=10, anchor="w", padx=20) cb_pers = ttk.Combobox(dialog, values=[p[1] for p in pers_list], state="readonly") cb_pers.pack(padx=20, pady=5, fill="x") cb_pers.current(0) def make(): emp_id = pers_list[cb_pers.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('INSERT INTO Reservations (employee_id) VALUES (%s) RETURNING reservation_id;', (emp_id,)) new_id = cur.fetchone()[0] conn.close() self.load_reservations_data() dialog.destroy() messagebox.showinfo("Успех", f"Успешно оформлена бронь №{new_id}!") except Exception as e: messagebox.showerror("Ошибка", f"Не удалось создать бронь: {e}", parent=dialog) ttk.Button(dialog, text="💾 Зарегистрировать бронь", command=make).pack(pady=20) def edit_reservation_dialog(self): selected = self.res_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите бронь для изменения!") return res_id, _, current_emp = self.res_tree.item(selected[0])['values'] pers_list = self.get_personnel_list() dialog = tk.Toplevel(self) dialog.title(f"Редактирование брони №{res_id}") dialog.geometry("400x180") dialog.grab_set() ttk.Label(dialog, text="Изменить сотрудника:").pack(pady=10, anchor="w", padx=20) names = [p[1] for p in pers_list] cb_pers = ttk.Combobox(dialog, values=names, state="readonly") cb_pers.pack(padx=20, pady=5, fill="x") for i, n in enumerate(names): if current_emp.startswith(n) or n.startswith(current_emp): cb_pers.current(i) break if not cb_pers.get() and names: cb_pers.current(0) def update(): emp_id = pers_list[cb_pers.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('UPDATE Reservations SET employee_id = %s WHERE reservation_id = %s;', (emp_id, res_id)) conn.close() self.load_reservations_data() dialog.destroy() messagebox.showinfo("Успех", "Ответственный успешно изменен!") except Exception as e: messagebox.showerror("Ошибка", f"Не удалось изменить: {e}", parent=dialog) ttk.Button(dialog, text="🔄 Обновить бронь", command=update).pack(pady=20) def delete_reservation(self): selected = self.res_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите бронь для аннулирования!") return res_id = self.res_tree.item(selected[0])['values'][0] if messagebox.askyesno("Подтверждение", f"Вы уверены, что хотите полностью удалить бронь №{res_id}?"): try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('DELETE FROM Reservation_Books WHERE reservation_id = %s;', (res_id,)) cur.execute('DELETE FROM Reservations WHERE reservation_id = %s;', (res_id,)) conn.close() self.load_reservations_data() messagebox.showinfo("Успех", f"Бронь №{res_id} удалена.") except Exception as e: messagebox.showerror("Ошибка удаления", f"Не удалось удалить:\n{e}") # ========================================== # ВКЛАДКА 4: УПРАВЛЕНИЕ ПЕРСОНАЛОМ (CRUD) # ========================================== def create_personnel_tab(self): tab = ttk.Frame(self.notebook, padding=10) self.notebook.add(tab, text="👥 Управление Персоналом") columns = ("id", "fio", "position", "birth", "passport", "phone", "salary") self.pers_tree = ttk.Treeview(tab, columns=columns, show="headings") self.pers_tree.heading("id", text="ID") self.pers_tree.heading("fio", text="ФИО Сотрудника") self.pers_tree.heading("position", text="Должность") self.pers_tree.heading("birth", text="Дата рожд.") self.pers_tree.heading("passport", text="Паспортные данные") self.pers_tree.heading("phone", text="Телефон") self.pers_tree.heading("salary", text="Оклад (руб.)") self.pers_tree.column("id", width=50, anchor="center", stretch=tk.NO) self.pers_tree.column("fio", width=220, stretch=tk.YES) self.pers_tree.column("position", width=140, stretch=tk.YES) self.pers_tree.column("birth", width=100, anchor="center", stretch=tk.NO) self.pers_tree.column("passport", width=140, anchor="center", stretch=tk.NO) self.pers_tree.column("phone", width=130, anchor="center", stretch=tk.NO) self.pers_tree.column("salary", width=100, anchor="e", stretch=tk.NO) self.pers_tree.pack(expand=True, fill="both", padx=5, pady=5) btn_frame = ttk.Frame(tab, padding=5) btn_frame.pack(fill="x", padx=5, pady=5) ttk.Button(btn_frame, text="➕ Нанять сотрудника", command=self.add_personnel_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="✏️ Редактировать анкету", command=self.edit_personnel_dialog).pack(side="left", padx=5) ttk.Button(btn_frame, text="❌ Уволить сотрудника", command=self.delete_personnel).pack(side="left", padx=5) self.load_personnel_data() def load_personnel_data(self): for i in self.pers_tree.get_children(): self.pers_tree.delete(i) try: conn = self.connect_db() with conn: with conn.cursor() as cur: # Добавлен JOIN таблицы Positions для корректного отображения query = """ SELECT p.employee_id, p.surname || ' ' || p.name || ' ' || COALESCE(p.patronymic, ''), pos.position_name, p.birth_date, p.passport_series || ' ' || p.passport_number, p.phone, p.salary FROM Personnel p JOIN Positions pos ON p.position_id = pos.position_id ORDER BY p.employee_id DESC; """ cur.execute(query) for row in cur.fetchall(): self.pers_tree.insert("", "end", values=[self.safe_decode(cell) for cell in row]) conn.close() except Exception as e: print(f"Ошибка загрузки персонала: {e}") def add_personnel_dialog(self): positions_list = self.get_positions_list() if not positions_list: messagebox.showerror("Ошибка", "Таблица должностей Positions пуста в БД!") return dialog = tk.Toplevel(self) dialog.title("Регистрация сотрудника") dialog.geometry("460x520") dialog.grab_set() ttk.Label(dialog, text="Фамилия:").pack(anchor="w", padx=20, pady=2) ent_surname = ttk.Entry(dialog) ent_surname.pack(fill="x", padx=20, pady=2) ttk.Label(dialog, text="Имя:").pack(anchor="w", padx=20, pady=2) ent_name = ttk.Entry(dialog) ent_name.pack(fill="x", padx=20, pady=2) ttk.Label(dialog, text="Отчество:").pack(anchor="w", padx=20, pady=2) ent_patr = ttk.Entry(dialog) ent_patr.pack(fill="x", padx=20, pady=2) ttk.Label(dialog, text="Выбрать должность:").pack(anchor="w", padx=20, pady=2) cb_pos = ttk.Combobox(dialog, values=[p[1] for p in positions_list], state="readonly") cb_pos.pack(fill="x", padx=20, pady=2) cb_pos.current(0) ttk.Label(dialog, text="Дата рождения (ГГГГ-ММ-ДД):").pack(anchor="w", padx=20, pady=2) ent_birth = ttk.Entry(dialog) ent_birth.pack(fill="x", padx=20, pady=2) ent_birth.insert(0, "1995-01-01") ttk.Label(dialog, text="Серия паспорта (4 цифры):").pack(anchor="w", padx=20, pady=2) ent_pass_ser = ttk.Entry(dialog) ent_pass_ser.pack(fill="x", padx=20, pady=2) ttk.Label(dialog, text="Номер паспорта (6 цифр):").pack(anchor="w", padx=20, pady=2) ent_pass_num = ttk.Entry(dialog) ent_pass_num.pack(fill="x", padx=20, pady=2) ttk.Label(dialog, text="Телефон:").pack(anchor="w", padx=20, pady=2) ent_phone = ttk.Entry(dialog) ent_phone.pack(fill="x", padx=20, pady=2) ttk.Label(dialog, text="Оклад:").pack(anchor="w", padx=20, pady=2) ent_salary = ttk.Entry(dialog) ent_salary.pack(fill="x", padx=20, pady=2) def save(): s = ent_surname.get().strip() n = ent_name.get().strip() p = ent_patr.get().strip() b = ent_birth.get().strip() ps = ent_pass_ser.get().strip() pn = ent_pass_num.get().strip() ph = ent_phone.get().strip() sal_str = ent_salary.get().strip() if not s or not n or not b or not ps or not pn or not sal_str: messagebox.showerror("Ошибка", "Заполните обязательные поля!", parent=dialog) return try: salary = float(sal_str) except ValueError: messagebox.showerror("Ошибка", "Оклад должен быть числом!", parent=dialog) return # Получаем корректный position_id из СУБД для внешнего ключа position_id = positions_list[cb_pos.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute(""" INSERT INTO Personnel (surname, name, patronymic, birth_date, passport_series, passport_number, phone, salary, position_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s); """, (s, n, p if p else None, b, ps, pn, ph if ph else None, salary, position_id)) conn.close() self.load_personnel_data() dialog.destroy() messagebox.showinfo("Успех", "Сотрудник принят в штат!") except Exception as e: messagebox.showerror("Ошибка СУБД", f"Не удалось добавить: {e}", parent=dialog) ttk.Button(dialog, text="💾 Сохранить анкету", command=save).pack(pady=15) def edit_personnel_dialog(self): selected = self.pers_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите сотрудника для изменения!") return values = self.pers_tree.item(selected[0])['values'] emp_id, full_fio, current_pos_name, birth, passport, phone, salary = values fio_parts = full_fio.split() s_val = fio_parts[0] if len(fio_parts) > 0 else "" n_val = fio_parts[1] if len(fio_parts) > 1 else "" p_val = fio_parts[2] if len(fio_parts) > 2 else "" pass_parts = str(passport).split() ps_val = pass_parts[0] if len(pass_parts) > 0 else "" pn_val = pass_parts[1] if len(pass_parts) > 1 else "" positions_list = self.get_positions_list() dialog = tk.Toplevel(self) dialog.title(f"Анкета сотрудника (ID: {emp_id})") dialog.geometry("460x520") dialog.grab_set() ttk.Label(dialog, text="Фамилия:").pack(anchor="w", padx=20, pady=2) ent_surname = ttk.Entry(dialog) ent_surname.pack(fill="x", padx=20, pady=2) ent_surname.insert(0, s_val) ttk.Label(dialog, text="Имя:").pack(anchor="w", padx=20, pady=2) ent_name = ttk.Entry(dialog) ent_name.pack(fill="x", padx=20, pady=2) ent_name.insert(0, n_val) ttk.Label(dialog, text="Отчество:").pack(anchor="w", padx=20, pady=2) ent_patr = ttk.Entry(dialog) ent_patr.pack(fill="x", padx=20, pady=2) ent_patr.insert(0, p_val) ttk.Label(dialog, text="Изменить должность:").pack(anchor="w", padx=20, pady=2) pos_names = [p[1] for p in positions_list] cb_pos = ttk.Combobox(dialog, values=pos_names, state="readonly") cb_pos.pack(fill="x", padx=20, pady=2) if current_pos_name in pos_names: cb_pos.current(pos_names.index(current_pos_name)) ttk.Label(dialog, text="Дата рождения (ГГГГ-ММ-ДД):").pack(anchor="w", padx=20, pady=2) ent_birth = ttk.Entry(dialog) ent_birth.pack(fill="x", padx=20, pady=2) ent_birth.insert(0, birth) ttk.Label(dialog, text="Серия паспорта:").pack(anchor="w", padx=20, pady=2) ent_pass_ser = ttk.Entry(dialog) ent_pass_ser.pack(fill="x", padx=20, pady=2) ent_pass_ser.insert(0, ps_val) ttk.Label(dialog, text="Номер паспорта:").pack(anchor="w", padx=20, pady=2) ent_pass_num = ttk.Entry(dialog) ent_pass_num.pack(fill="x", padx=20, pady=2) ent_pass_num.insert(0, pn_val) ttk.Label(dialog, text="Телефон:").pack(anchor="w", padx=20, pady=2) ent_phone = ttk.Entry(dialog) ent_phone.pack(fill="x", padx=20, pady=2) ent_phone.insert(0, phone) ttk.Label(dialog, text="Оклад:").pack(anchor="w", padx=20, pady=2) ent_salary = ttk.Entry(dialog) ent_salary.pack(fill="x", padx=20, pady=2) ent_salary.insert(0, str(salary)) def update(): s = ent_surname.get().strip() n = ent_name.get().strip() p = ent_patr.get().strip() b = ent_birth.get().strip() ps = ent_pass_ser.get().strip() pn = ent_pass_num.get().strip() ph = ent_phone.get().strip() sal_str = ent_salary.get().strip() if not s or not n or not b or not ps or not pn or not sal_str: messagebox.showerror("Ошибка", "Заполните обязательные поля!", parent=dialog) return try: sal = float(sal_str) except ValueError: messagebox.showerror("Ошибка", "Оклад должен быть числом!", parent=dialog) return position_id = positions_list[cb_pos.current()][0] try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute(""" UPDATE Personnel SET surname=%s, name=%s, patronymic=%s, birth_date=%s, passport_series=%s, passport_number=%s, phone=%s, salary=%s, position_id=%s WHERE employee_id=%s; """, (s, n, p if p else None, b, ps, pn, ph if ph else None, sal, position_id, emp_id)) conn.close() self.load_personnel_data() self.load_reservations_data() dialog.destroy() messagebox.showinfo("Успех", "Данные обновлены!") except Exception as e: messagebox.showerror("Ошибка СУБД", f"Не удалось обновить: {e}", parent=dialog) ttk.Button(dialog, text="🔄 Обновить анкету", command=update).pack(pady=15) def delete_personnel(self): selected = self.pers_tree.selection() if not selected: messagebox.showwarning("Внимание", "Выберите сотрудника для увольнения!") return emp_id, fio = self.pers_tree.item(selected[0])['values'][0], self.pers_tree.item(selected[0])['values'][1] if messagebox.askyesno("Подтверждение", f"Удалить сотрудника '{fio}' из системы?\nЭто очистит связанные записи!"): try: conn = self.connect_db() with conn: with conn.cursor() as cur: cur.execute('DELETE FROM Reservations WHERE employee_id = %s;', (emp_id,)) cur.execute('DELETE FROM Personnel WHERE employee_id = %s;', (emp_id,)) conn.close() self.load_personnel_data() self.load_reservations_data() messagebox.showinfo("Успех", f"Сотрудник '{fio}' удален.") except Exception as e: messagebox.showerror("Ошибка СУБД", f"Не удалось удалить:\n{e}") # ========================================== # УНИВЕРСАЛЬНЫЙ МОДУЛЬ ЭКСПОРТА # ========================================== def export_to_csv(self, tree, filename): headers = [tree.heading(col)["text"] for col in tree["columns"]] rows = [tree.item(item)["values"] for item in tree.get_children()] try: with open(filename, mode="w", newline="", encoding="utf-8-sig") as f: writer = csv.writer(f, delimiter=";") writer.writerow(headers) writer.writerows(rows) messagebox.showinfo("Успех", f"Данные экспортированы в:\n{os.path.abspath(filename)}") except Exception as e: messagebox.showerror("Ошибка экспорта", f"Не удалось сохранить: {e}") if __name__ == "__main__": app = BookWarehouseApp() app.mainloop()