""" Театр — Приложение для управления базой данных PyQt6 + PostgreSQL (psycopg2) Python 3.10+ """ import sys import csv import hashlib import os from datetime import datetime from typing import Optional import psycopg2 import psycopg2.extras from PyQt6.QtWidgets import ( QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QTabWidget, QTableWidget, QTableWidgetItem, QPushButton, QLabel, QLineEdit, QComboBox, QDateTimeEdit, QDoubleSpinBox, QSpinBox, QDialog, QFormLayout, QDialogButtonBox, QMessageBox, QFileDialog, QGroupBox, QTextEdit, QSplitter, QHeaderView, QFrame, QStatusBar, QInputDialog, QCheckBox, QListWidget ) from PyQt6.QtCore import Qt, QDateTime, QThread, pyqtSignal, QTimer from PyQt6.QtGui import QColor, QFont, QIcon, QPalette # ── Настройки подключения ────────────────────────────────────────────────── DB_CONFIG = { "host": os.environ.get("DB_HOST", "edu-pg.itiscaf.ru"), "port": int(os.environ.get("DB_PORT", 5432)), "dbname": os.environ.get("DB_NAME", "dbwork"), "user": os.environ.get("DB_USER", "sokolov_ta"), "password": os.environ.get("DB_PASS", "%4aH&Dz6a5#"), "connect_timeout": 5, "options": "-c search_path=sokolov_ta,public" } APP_ROLES = { "admin": "Администратор", "cashier": "Кассир", "buyer": "Покупатель", } BLUE = "#2d6eaa" BLUE_DARK = "#1a4a7a" LIGHT_BG = "#f4f6f9" ACCENT = "#e8f4fd" STYLE = f""" QMainWindow, QDialog, QWidget {{ background: {LIGHT_BG}; color: #222; }} QTabWidget::pane {{ border: 1px solid #c8d6e5; background: white; border-radius: 4px; }} QTabBar::tab {{ background: #dce8f5; color: #333; padding: 8px 18px; border-radius: 4px 4px 0 0; margin-right: 2px; }} QTabBar::tab:selected {{ background: {BLUE}; color: white; }} QPushButton {{ background: {BLUE}; color: white; border: none; padding: 7px 18px; border-radius: 5px; font-weight: bold; }} QPushButton:hover {{ background: {BLUE_DARK}; }} QPushButton:pressed {{ background: #0f3358; }} QPushButton:disabled {{ background: #aabdd4; color: #ddd; }} QPushButton#danger {{ background: #c0392b; color: white; }} QPushButton#danger:hover {{ background: #a93226; }} QPushButton#success {{ background: #27ae60; color: white; }} QPushButton#success:hover {{ background: #1e8449; }} QPushButton#secondary {{ background: #6c8eaf; color: white; }} QPushButton#secondary:hover {{ background: #526a87; }} QLineEdit, QComboBox, QDateTimeEdit, QDoubleSpinBox, QSpinBox {{ border: 1px solid #b0c4d8; border-radius: 4px; padding: 5px 8px; background: white; color: #222; }} QLineEdit:focus, QComboBox:focus {{ border-color: {BLUE}; }} QComboBox QAbstractItemView {{ background: white; color: #222; selection-background-color: {ACCENT}; selection-color: #1a4a7a; }} QTableWidget {{ border: 1px solid #c8d6e5; gridline-color: #e0e8f0; background: white; color: #222; }} QTableWidget::item {{ color: #222; }} QTableWidget::item:selected {{ background: {ACCENT}; color: #1a4a7a; }} QHeaderView::section {{ background: {BLUE}; color: white; padding: 6px; border: none; font-weight: bold; }} QGroupBox {{ border: 1px solid #c8d6e5; border-radius: 6px; margin-top: 10px; padding-top: 8px; color: #222; }} QGroupBox::title {{ subcontrol-origin: margin; left: 10px; color: {BLUE}; font-weight: bold; }} QLabel {{ color: #222; }} QLabel#header {{ font-size: 18px; font-weight: bold; color: {BLUE_DARK}; }} QLabel#hint {{ color: #666; font-size: 11px; }} QStatusBar {{ background: {BLUE_DARK}; color: white; padding: 2px 8px; }} QDialogButtonBox QPushButton {{ min-width: 80px; }} QListWidget {{ background: white; color: #222; border: 1px solid #b0c4d8; border-radius: 4px; }} QListWidget::item:selected {{ background: {ACCENT}; color: #1a4a7a; }} """ # ══════════════════════════════════════════════════════════════════════════════ # DATABASE LAYER # ══════════════════════════════════════════════════════════════════════════════ class Database: def __init__(self): self.conn: Optional[psycopg2.extensions.connection] = None def connect(self, config: dict) -> bool: try: self.conn = psycopg2.connect(**config) self.conn.autocommit = False return True except Exception as e: print(f"DB connect error: {e}") return False def disconnect(self): if self.conn: self.conn.close() self.conn = None def execute(self, sql: str, params=None) -> list: """Выполнить запрос и вернуть строки.""" with self.conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur: cur.execute(sql, params) self.conn.commit() try: return cur.fetchall() except psycopg2.ProgrammingError: return [] def execute_many(self, sql: str, data: list): with self.conn.cursor() as cur: psycopg2.extras.execute_batch(cur, sql, data) self.conn.commit() def verify_password(self, email: str, password: str): rows = self.execute( """ SELECT user_id, last_name, name, mid_name, role, is_active FROM users WHERE email = %s AND password_hash = %s """, (email, password) ) if rows and rows[0]["is_active"]: return dict(rows[0]) return None # ── TICKETS ── def get_tickets(self, search: str = "", status: str = "", show_id: int = 0) -> list: sql = """ SELECT t.ticket_id, s.title AS show_title, t.event_datetime, h.name AS hall, se.row_number, se.seat_number, se.category, t.price, t.status, a.manager_name FROM tickets t JOIN shows s ON t.show_id = s.show_id JOIN seats se ON t.seat_id = se.seat_id JOIN halls h ON se.hall_id = h.hall_id JOIN accounting a ON t.accounting_id = a.accounting_id WHERE 1=1 """ params = [] if search: sql += " AND (s.title ILIKE %s OR se.category ILIKE %s)" params += [f"%{search}%", f"%{search}%"] if status: sql += " AND t.status = %s" params.append(status) if show_id: sql += " AND t.show_id = %s" params.append(show_id) sql += " ORDER BY t.event_datetime DESC, t.ticket_id" return self.execute(sql, params or None) def add_ticket(self, data: dict) -> bool: try: self.execute( """INSERT INTO tickets (accounting_id, show_id, seat_id, event_datetime, price, status) VALUES (%(accounting_id)s, %(show_id)s, %(seat_id)s, %(event_datetime)s, %(price)s, %(status)s)""", data ) return True except Exception as e: self.conn.rollback() raise e def update_ticket(self, ticket_id: int, data: dict) -> bool: try: self.execute( """UPDATE tickets SET show_id=%(show_id)s, seat_id=%(seat_id)s, event_datetime=%(event_datetime)s, price=%(price)s, status=%(status)s WHERE ticket_id=%(ticket_id)s""", {**data, "ticket_id": ticket_id} ) return True except Exception as e: self.conn.rollback() raise e def delete_ticket(self, ticket_id: int): # Нельзя удалить проданный билет rows = self.execute("SELECT status FROM tickets WHERE ticket_id=%s", (ticket_id,)) if rows and rows[0]["status"] == "продан": raise ValueError("Нельзя удалить проданный билет!") self.execute("DELETE FROM tickets WHERE ticket_id=%s", (ticket_id,)) # ── SALES ── def get_sales(self, search: str = "") -> list: sql = """ SELECT sa.sale_id, u.last_name || ' ' || u.name || ' ' || u.mid_name AS buyer, u.email, sh.title AS show_title, t.event_datetime, t.price, sa.amount_paid, sa.payment_method, sa.sale_datetime FROM sales sa JOIN users u ON sa.user_id = u.user_id JOIN tickets t ON sa.ticket_id = t.ticket_id JOIN shows sh ON t.show_id = sh.show_id """ params = [] if search: sql += """WHERE last_name ILIKE %s OR name ILIKE %s OR mid_name ILIKE %s OR email ILIKE %s""" params = [ f"%{search}%", f"%{search}%", f"%{search}%", f"%{search}%"] sql += " ORDER BY sa.sale_datetime DESC" return self.execute(sql, params or None) def sell_ticket(self, ticket_id: int, user_id: int, method: str) -> bool: rows = self.execute("SELECT price, status FROM tickets WHERE ticket_id=%s", (ticket_id,)) if not rows: raise ValueError("Билет не найден") t = rows[0] if t["status"] != "доступен": raise ValueError(f"Билет недоступен (статус: {t['status']})") try: self.execute( "UPDATE tickets SET status='продан' WHERE ticket_id=%s", (ticket_id,) ) self.execute( """INSERT INTO sales (ticket_id, user_id, payment_method, amount_paid) VALUES (%s, %s, %s, %s)""", (ticket_id, user_id, method, t["price"]) ) return True except Exception as e: self.conn.rollback() raise e # ── USERS ── def get_users(self, search: str = "") -> list: sql = """SELECT user_id, last_name, name, mid_name, birthdate, phone, email, role, is_active, created_at FROM users""" params = [] if search: sql += " WHERE last_name || ' ' || name || ' ' || mid_name ILIKE %s OR email ILIKE %s" params = [f"%{search}%", f"%{search}%"] sql += " ORDER BY last_name, name, mid_name" return self.execute(sql, params or None) def add_user(self, data: dict) -> bool: try: self.execute( """INSERT INTO users (last_name, name, mid_name, birthdate, phone, email, password_hash, role) VALUES (%(last_name)s, %(name)s, %(mid_name)s, %(birthdate)s, %(phone)s, %(email)s, %(password)s, %(role)s)""", data ) return True except Exception as e: self.conn.rollback() raise e def toggle_user_active(self, user_id: int, active: bool): self.execute("UPDATE users SET is_active=%s WHERE user_id=%s", (active, user_id)) # ── SHOWS ── def get_shows(self) -> list: return self.execute("SELECT * FROM shows ORDER BY title") # ── LOOKUPS ── def get_accounting_list(self) -> list: return self.execute("SELECT accounting_id, manager_name FROM accounting ORDER BY manager_name") def get_seats_by_hall(self, hall_id: int) -> list: return self.execute( "SELECT seat_id, row_number, seat_number, category FROM seats WHERE hall_id=%s ORDER BY row_number, seat_number", (hall_id,) ) def get_halls(self) -> list: return self.execute("SELECT * FROM halls ORDER BY name") # ── REPORTS ── def revenue_by_show(self) -> list: return self.execute(""" SELECT sh.title, COUNT(*) AS sold, SUM(sa.amount_paid) AS revenue FROM sales sa JOIN tickets t ON sa.ticket_id = t.ticket_id JOIN shows sh ON t.show_id = sh.show_id GROUP BY sh.title ORDER BY revenue DESC """) def get_audit_log(self, limit=50) -> list: return self.execute( "SELECT * FROM audit_log ORDER BY changed_at DESC LIMIT %s", (limit,) ) # ══════════════════════════════════════════════════════════════════════════════ # DIALOGS # ══════════════════════════════════════════════════════════════════════════════ class LoginDialog(QDialog): def __init__(self): super().__init__() self.setWindowTitle("Вход в систему — Театр") self.setFixedWidth(380) self.user_data = None layout = QVBoxLayout(self) layout.setSpacing(14) title = QLabel("🎭 Театр — Вход в систему") title.setObjectName("header") title.setAlignment(Qt.AlignmentFlag.AlignCenter) layout.addWidget(title) form = QFormLayout() self.email_edit = QLineEdit() self.email_edit.setPlaceholderText("example@mail.ru") self.pass_edit = QLineEdit() self.pass_edit.setEchoMode(QLineEdit.EchoMode.Password) form.addRow("Email:", self.email_edit) form.addRow("Пароль:", self.pass_edit) layout.addLayout(form) hint = QLabel("Демо: admin@teatr.ru / AdminPass!1") hint.setAlignment(Qt.AlignmentFlag.AlignCenter) hint.setStyleSheet("color: gray; font-size: 11px;") layout.addWidget(hint) btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel) btns.accepted.connect(self.try_login) btns.rejected.connect(self.reject) layout.addWidget(btns) self.db = Database() def set_db_config(self, config: dict): self.db.connect(config) def try_login(self): email = self.email_edit.text().strip() password = self.pass_edit.text() if not email or not password: QMessageBox.warning(self, "Ошибка", "Заполните все поля.") return user = self.db.verify_password(email, password) if user: self.user_data = user self.accept() else: QMessageBox.critical(self, "Ошибка", "Неверный email или пароль.") class TicketDialog(QDialog): """Диалог добавления / редактирования билета.""" def __init__(self, db: Database, ticket=None, parent=None): super().__init__(parent) self.db = db self.ticket = ticket self.setWindowTitle("Редактировать билет" if ticket else "Добавить билет") self.setMinimumWidth(420) layout = QFormLayout(self) # Спектакль self.show_cb = QComboBox() shows = db.get_shows() for s in shows: self.show_cb.addItem(s["title"], s["show_id"]) layout.addRow("Спектакль:", self.show_cb) # Касса self.acc_cb = QComboBox() accs = db.get_accounting_list() for a in accs: self.acc_cb.addItem(a["manager_name"], a["accounting_id"]) layout.addRow("Кассир:", self.acc_cb) # Зал → место self.hall_cb = QComboBox() halls = db.get_halls() for h in halls: self.hall_cb.addItem(h["name"], h["hall_id"]) self.hall_cb.currentIndexChanged.connect(self._load_seats) layout.addRow("Зал:", self.hall_cb) self.seat_cb = QComboBox() layout.addRow("Место:", self.seat_cb) self._load_seats() # Дата и время self.dt_edit = QDateTimeEdit(QDateTime.currentDateTime()) self.dt_edit.setDisplayFormat("dd.MM.yyyy HH:mm") layout.addRow("Дата/время:", self.dt_edit) # Цена self.price_spin = QDoubleSpinBox() self.price_spin.setRange(0, 99999) self.price_spin.setSuffix(" ₽") layout.addRow("Цена:", self.price_spin) # Статус self.status_cb = QComboBox() self.status_cb.addItems(["доступен", "бронь", "продан", "возврат"]) layout.addRow("Статус:", self.status_cb) btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Save | QDialogButtonBox.StandardButton.Cancel) btns.accepted.connect(self.accept) btns.rejected.connect(self.reject) layout.addRow(btns) if ticket: self._fill(ticket) def _load_seats(self): hall_id = self.hall_cb.currentData() self.seat_cb.clear() if hall_id: for s in self.db.get_seats_by_hall(hall_id): label = f"Ряд {s['row_number']}, место {s['seat_number']} ({s['category']})" self.seat_cb.addItem(label, s["seat_id"]) def _fill(self, t): idx = self.show_cb.findData(t["show_id"]) if "show_id" in t else -1 if idx >= 0: self.show_cb.setCurrentIndex(idx) self.price_spin.setValue(float(t.get("price", 0))) status_idx = self.status_cb.findText(t.get("status", "доступен")) if status_idx >= 0: self.status_cb.setCurrentIndex(status_idx) def get_data(self) -> dict: return { "accounting_id": self.acc_cb.currentData(), "show_id": self.show_cb.currentData(), "seat_id": self.seat_cb.currentData(), "event_datetime": self.dt_edit.dateTime().toString("yyyy-MM-dd HH:mm"), "price": self.price_spin.value(), "status": self.status_cb.currentText(), } class SellDialog(QDialog): """Диалог продажи билета.""" def __init__(self, db: Database, ticket_id: int, parent=None): super().__init__(parent) self.db = db self.ticket_id = ticket_id self.setWindowTitle("Продажа билета") self.setFixedWidth(380) layout = QFormLayout(self) self.user_cb = QComboBox() users = db.get_users() fio = f"{u['last_name']} {u['name']} {u['mid_name'] or ''}".strip() for u in users: self.user_cb.addItem(f"{u['fio']} ({u['email']})", u["user_id"]) layout.addRow("Покупатель:", self.user_cb) self.method_cb = QComboBox() self.method_cb.addItems(["карта", "наличные", "онлайн"]) layout.addRow("Способ оплаты:", self.method_cb) btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel) btns.accepted.connect(self.accept) btns.rejected.connect(self.reject) layout.addRow(btns) class UserDialog(QDialog): def __init__(self, parent=None): super().__init__(parent) self.setWindowTitle("Добавить пользователя") self.setFixedWidth(400) layout = QFormLayout(self) self.last_name_edit = QLineEdit() self.name_edit = QLineEdit() self.mid_name_edit = QLineEdit() self.email_edit = QLineEdit() self.phone_edit = QLineEdit() self.birth_edit = QLineEdit() self.birth_edit.setPlaceholderText("ГГГГ-ММ-ДД") self.pass_edit = QLineEdit() self.pass_edit.setEchoMode(QLineEdit.EchoMode.Password) self.role_cb = QComboBox() self.role_cb.addItems(["buyer", "cashier", "admin"]) layout.addRow("Фамилия:", self.last_name_edit) layout.addRow("Имя:", self.name_edit) layout.addRow("Отчество:", self.mid_name_edit) layout.addRow("Email:", self.email_edit) layout.addRow("Телефон:", self.phone_edit) layout.addRow("Дата рождения:", self.birth_edit) layout.addRow("Пароль:", self.pass_edit) layout.addRow("Роль:", self.role_cb) btns = QDialogButtonBox(QDialogButtonBox.StandardButton.Save | QDialogButtonBox.StandardButton.Cancel) btns.accepted.connect(self.accept) btns.rejected.connect(self.reject) layout.addRow(btns) def get_data(self) -> dict: return { "last_name": self.last_name_edit.text().strip(), "name": self.name_edit.text().strip(), "mid_name": self.mid_name_edit.text().strip(), "email": self.email_edit.text().strip(), "phone": self.phone_edit.text().strip(), "birthdate": self.birth_edit.text().strip() or None, "password": self.pass_edit.text(), "role": self.role_cb.currentText(), } def make_table(headers: list) -> QTableWidget: t = QTableWidget(0, len(headers)) t.setHorizontalHeaderLabels(headers) t.setEditTriggers(QTableWidget.EditTrigger.NoEditTriggers) t.setSelectionBehavior(QTableWidget.SelectionBehavior.SelectRows) t.horizontalHeader().setStretchLastSection(True) t.verticalHeader().setVisible(False) t.setAlternatingRowColors(False) return t def fill_table(table: QTableWidget, rows: list, keys: list): table.setRowCount(0) for row in rows: r = table.rowCount() table.insertRow(r) for c, k in enumerate(keys): val = row.get(k, "") item = QTableWidgetItem(str(val) if val is not None else "") item.setData(Qt.ItemDataRole.UserRole, row) table.setItem(r, c, item) def export_to_csv(table: QTableWidget, parent=None): path, _ = QFileDialog.getSaveFileName(parent, "Сохранить CSV", "", "CSV (*.csv)") if not path: return with open(path, "w", newline="", encoding="utf-8-sig") as f: writer = csv.writer(f, delimiter=";") headers = [table.horizontalHeaderItem(i).text() for i in range(table.columnCount())] writer.writerow(headers) for row in range(table.rowCount()): writer.writerow([ table.item(row, col).text() if table.item(row, col) else "" for col in range(table.columnCount()) ]) QMessageBox.information(parent, "Экспорт", f"Файл сохранён:\n{path}") # ── Вкладка: Билеты ────────────────────────────────────────────────────────── class TicketsTab(QWidget): def __init__(self, db: Database, user: dict): super().__init__() self.db = db self.user = user self._build_ui() self.refresh() def _build_ui(self): layout = QVBoxLayout(self) # Панель поиска search_grp = QGroupBox("Поиск и фильтрация") sg = QHBoxLayout(search_grp) self.search_edit = QLineEdit() self.search_edit.setPlaceholderText("Название спектакля, категория...") self.status_filter = QComboBox() self.status_filter.addItems(["", "доступен", "продан", "бронь", "возврат"]) sg.addWidget(QLabel("Поиск:")) sg.addWidget(self.search_edit) sg.addWidget(QLabel("Статус:")) sg.addWidget(self.status_filter) btn_search = QPushButton("🔍 Найти") btn_search.clicked.connect(self.refresh) sg.addWidget(btn_search) layout.addWidget(search_grp) # Таблица self.table = make_table([ "ID", "Спектакль", "Дата/время", "Зал", "Ряд", "Место", "Категория", "Цена ₽", "Статус" ]) layout.addWidget(self.table) # Кнопки btn_row = QHBoxLayout() btn_add = QPushButton("➕ Добавить") btn_edit = QPushButton("✏️ Изменить") btn_del = QPushButton("🗑️ Удалить") btn_sell = QPushButton("💳 Продать") btn_exp = QPushButton("📤 Экспорт CSV") btn_ref = QPushButton("🔄 Обновить") btn_del.setObjectName("danger") btn_sell.setObjectName("success") btn_add.clicked.connect(self.add_ticket) btn_edit.clicked.connect(self.edit_ticket) btn_del.clicked.connect(self.delete_ticket) btn_sell.clicked.connect(self.sell_ticket) btn_exp.clicked.connect(lambda: export_to_csv(self.table, self)) btn_ref.clicked.connect(self.refresh) for b in [btn_add, btn_edit, btn_del, btn_sell, btn_exp, btn_ref]: btn_row.addWidget(b) # Права доступа — скрываем кнопки управления для покупателя if self.user["role"] not in ("admin", "cashier"): for b in (btn_add, btn_edit, btn_del, btn_sell): b.setVisible(False) layout.addLayout(btn_row) def refresh(self): rows = self.db.get_tickets( self.search_edit.text(), self.status_filter.currentText() ) keys = ["ticket_id","show_title","event_datetime","hall", "row_number","seat_number","category","price","status"] fill_table(self.table, rows, keys) def _selected_id(self) -> Optional[int]: row = self.table.currentRow() if row < 0: QMessageBox.warning(self, "Выбор", "Выберите строку.") return None item = self.table.item(row, 0) return int(item.text()) def add_ticket(self): dlg = TicketDialog(self.db, parent=self) if dlg.exec(): try: self.db.add_ticket(dlg.get_data()) self.refresh() except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) def edit_ticket(self): tid = self._selected_id() if tid is None: return row = self.table.currentRow() ticket = {"show_id": None, "price": float(self.table.item(row, 7).text()), "status": self.table.item(row, 8).text()} dlg = TicketDialog(self.db, ticket, parent=self) if dlg.exec(): try: self.db.update_ticket(tid, dlg.get_data()) self.refresh() except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) def delete_ticket(self): tid = self._selected_id() if tid is None: return if QMessageBox.question(self, "Удаление", f"Удалить билет #{tid}?") == QMessageBox.StandardButton.Yes: try: self.db.delete_ticket(tid) self.refresh() except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) def sell_ticket(self): tid = self._selected_id() if tid is None: return dlg = SellDialog(self.db, tid, parent=self) if dlg.exec(): try: self.db.sell_ticket( tid, dlg.user_cb.currentData(), dlg.method_cb.currentText() ) QMessageBox.information(self, "Успех", "Билет продан!") self.refresh() except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) # ── Вкладка: Продажи ───────────────────────────────────────────────────────── class SalesTab(QWidget): def __init__(self, db: Database, user: dict): super().__init__() self.db = db self.user = user self._build_ui() self.refresh() def _build_ui(self): layout = QVBoxLayout(self) sh = QHBoxLayout() self.search_edit = QLineEdit() self.search_edit.setPlaceholderText("Имя покупателя, спектакль, email...") btn_s = QPushButton("🔍 Найти") btn_s.clicked.connect(self.refresh) btn_e = QPushButton("📤 Экспорт CSV") btn_e.clicked.connect(lambda: export_to_csv(self.table, self)) sh.addWidget(QLabel("Поиск:")) sh.addWidget(self.search_edit) sh.addWidget(btn_s) sh.addWidget(btn_e) layout.addLayout(sh) self.table = make_table([ "ID продажи", "Покупатель", "Email", "Спектакль", "Дата показа", "Цена ₽", "Оплачено ₽", "Способ", "Дата продажи" ]) layout.addWidget(self.table) # Итог self.total_lbl = QLabel() self.total_lbl.setStyleSheet(f"color: {BLUE_DARK}; font-weight: bold; padding: 4px;") layout.addWidget(self.total_lbl) def refresh(self): rows = self.db.get_sales(self.search_edit.text()) keys = ["sale_id","buyer","email","show_title", "event_datetime","price","amount_paid","payment_method","sale_datetime"] fill_table(self.table, rows, keys) total = sum(float(r["amount_paid"] or 0) for r in rows) self.total_lbl.setText(f"Итого: {total:,.2f} ₽ | Записей: {len(rows)}") # ── Вкладка: Пользователи ──────────────────────────────────────────────────── class UsersTab(QWidget): def __init__(self, db: Database, user: dict): super().__init__() self.db = db self.user = user self._build_ui() self.refresh() def _build_ui(self): layout = QVBoxLayout(self) sh = QHBoxLayout() self.search_edit = QLineEdit() self.search_edit.setPlaceholderText("ФИО, email...") btn_s = QPushButton("🔍 Найти") btn_s.clicked.connect(self.refresh) sh.addWidget(QLabel("Поиск:")) sh.addWidget(self.search_edit) sh.addWidget(btn_s) layout.addLayout(sh) self.table = make_table([ "ID", "Фaмилия", "Имя", "Отчество", "Дата рожд.", "Телефон", "Email", "Роль", "Активен", "Дата регистр." ]) layout.addWidget(self.table) btn_row = QHBoxLayout() btn_add = QPushButton("➕ Добавить") btn_toggle = QPushButton("🔒 Активировать/Блок.") btn_exp = QPushButton("📤 Экспорт CSV") btn_ref = QPushButton("🔄 Обновить") btn_add.clicked.connect(self.add_user) btn_toggle.clicked.connect(self.toggle_user) btn_exp.clicked.connect(lambda: export_to_csv(self.table, self)) btn_ref.clicked.connect(self.refresh) for b in [btn_add, btn_toggle, btn_exp, btn_ref]: btn_row.addWidget(b) if self.user["role"] != "admin": btn_add.setVisible(False) btn_toggle.setVisible(False) layout.addLayout(btn_row) def refresh(self): rows = self.db.get_users(self.search_edit.text()) keys = [ "user_id", "last_name", "name", "mid_name", "birthdate", "phone", "email", "role", "is_active", "created_at" ] fill_table(self.table, rows, keys) def add_user(self): dlg = UserDialog(self) if dlg.exec(): try: self.db.add_user(dlg.get_data()) self.refresh() except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) def toggle_user(self): row = self.table.currentRow() if row < 0: QMessageBox.warning(self, "Выбор", "Выберите пользователя.") return uid = int(self.table.item(row, 0).text()) active = self.table.item(row, 6).text() == "True" self.db.toggle_user_active(uid, not active) self.refresh() # ── Вкладка: Отчёты ────────────────────────────────────────────────────────── class ReportsTab(QWidget): def __init__(self, db: Database): super().__init__() self.db = db self._build_ui() def _build_ui(self): layout = QVBoxLayout(self) btn_rev = QPushButton("📊 Выручка по спектаклям") btn_rev.clicked.connect(self.show_revenue) btn_aud = QPushButton("📋 Журнал аудита") btn_aud.clicked.connect(self.show_audit) row = QHBoxLayout() row.addWidget(btn_rev) row.addWidget(btn_aud) row.addStretch() layout.addLayout(row) self.report_table = make_table(["Спектакль", "Продано билетов", "Выручка ₽"]) layout.addWidget(self.report_table) self.summary_lbl = QLabel() self.summary_lbl.setStyleSheet(f"color: {BLUE_DARK}; font-weight: bold;") layout.addWidget(self.summary_lbl) btn_exp = QPushButton("📤 Экспорт CSV") btn_exp.clicked.connect(lambda: export_to_csv(self.report_table, self)) layout.addWidget(btn_exp) def show_revenue(self): rows = self.db.revenue_by_show() self.report_table.setColumnCount(3) self.report_table.setHorizontalHeaderLabels(["Спектакль", "Продано", "Выручка ₽"]) fill_table(self.report_table, rows, ["title", "sold", "revenue"]) total = sum(float(r["revenue"] or 0) for r in rows) self.summary_lbl.setText(f"Общая выручка: {total:,.2f} ₽") def show_audit(self): rows = self.db.get_audit_log() self.report_table.setColumnCount(5) self.report_table.setHorizontalHeaderLabels( ["ID", "Таблица", "Операция", "Пользователь", "Дата/время"] ) fill_table(self.report_table, rows, ["log_id","table_name","operation","changed_by","changed_at"]) self.summary_lbl.setText(f"Записей в журнале: {len(rows)}") # ══════════════════════════════════════════════════════════════════════════════ # MAIN WINDOW # ══════════════════════════════════════════════════════════════════════════════ class MainWindow(QMainWindow): def __init__(self, db: Database, user: dict): super().__init__() self.db = db self.user = user fio = f"{user['last_name']} {user['name']}" if user['mid_name']: fio += f" {user['mid_name']}" self.setWindowTitle(f"Театр — {fio} ({APP_ROLES.get(user['role'], user['role'])})") self.resize(1100, 680) # Статус-бар sb = QStatusBar() self.setStatusBar(sb) sb.showMessage(f"Подключено | Пользователь: {fio} | Роль: {APP_ROLES.get(user['role'])}") # Вкладки tabs = QTabWidget() role = user["role"] # Вкладка «Билеты» — доступна всем tabs.addTab(TicketsTab(db, user), "🎫 Билеты") # Вкладка «Продажи» — только admin и cashier if role in ("admin", "cashier"): tabs.addTab(SalesTab(db, user), "💳 Продажи") # Вкладка «Пользователи» — только admin if role == "admin": tabs.addTab(UsersTab(db, user), "👤 Пользователи") # Вкладка «Отчёты» — только admin и cashier if role in ("admin", "cashier"): tabs.addTab(ReportsTab(db), "📊 Отчёты") self.setCentralWidget(tabs) # ══════════════════════════════════════════════════════════════════════════════ # ENTRY POINT # ══════════════════════════════════════════════════════════════════════════════ import json # ── Хранилище профилей подключения ────────────────────────────────────────── PROFILES_FILE = os.path.join(os.path.expanduser("~"), ".theatre_db_profiles.json") def load_profiles() -> list: """Загрузить сохранённые профили подключения.""" try: if os.path.exists(PROFILES_FILE): with open(PROFILES_FILE, "r", encoding="utf-8") as f: return json.load(f) except Exception: pass return [] def save_profiles(profiles: list): """Сохранить профили подключения.""" try: with open(PROFILES_FILE, "w", encoding="utf-8") as f: json.dump(profiles, f, ensure_ascii=False, indent=2) except Exception as e: print(f"Ошибка сохранения профилей: {e}") class DBConnectDialog(QDialog): """ Диалог выбора/создания профиля подключения к БД. Показывает список сохранённых пар (логин, пароль). Позволяет выбрать, удалить или добавить новый профиль. """ def __init__(self): super().__init__() self.setWindowTitle("Подключение к базе данных") self.setFixedWidth(440) self.selected_config: dict | None = None self._profiles = load_profiles() self._build_ui() def _build_ui(self): from PyQt6.QtWidgets import QListWidget, QListWidgetItem layout = QVBoxLayout(self) layout.setSpacing(12) title = QLabel("🎭 Театр — Подключение к БД") title.setObjectName("header") title.setAlignment(Qt.AlignmentFlag.AlignCenter) layout.addWidget(title) # Группа сохранённых профилей saved_grp = QGroupBox("Сохранённые подключения") sg = QVBoxLayout(saved_grp) self.list_widget = QListWidget() self.list_widget.setMinimumHeight(120) self._populate_list() self.list_widget.itemDoubleClicked.connect(self._accept_selected) sg.addWidget(self.list_widget) btn_row = QHBoxLayout() self.btn_connect = QPushButton("✅ Подключиться") self.btn_connect.setObjectName("success") self.btn_connect.clicked.connect(self._accept_selected) btn_delete = QPushButton("🗑️ Удалить") btn_delete.setObjectName("danger") btn_delete.clicked.connect(self._delete_selected) btn_row.addWidget(self.btn_connect) btn_row.addWidget(btn_delete) sg.addLayout(btn_row) layout.addWidget(saved_grp) # Разделитель line = QFrame() line.setFrameShape(QFrame.Shape.HLine) line.setFrameShadow(QFrame.Shadow.Sunken) layout.addWidget(line) # Группа нового подключения new_grp = QGroupBox("Новое подключение") ng = QFormLayout(new_grp) self.user_edit = QLineEdit() self.user_edit.setPlaceholderText("Пользователь PostgreSQL") self.pass_edit = QLineEdit() self.pass_edit.setEchoMode(QLineEdit.EchoMode.Password) self.pass_edit.setPlaceholderText("Пароль") ng.addRow("Логин:", self.user_edit) ng.addRow("Пароль:", self.pass_edit) btn_new = QPushButton("🔌 Подключиться с новым аккаунтом") btn_new.setObjectName("secondary") btn_new.clicked.connect(self._connect_new) ng.addRow(btn_new) layout.addWidget(new_grp) # Кнопка отмены btn_cancel = QPushButton("Отмена") btn_cancel.setObjectName("secondary") btn_cancel.clicked.connect(self.reject) layout.addWidget(btn_cancel) self._update_connect_btn_state() self.list_widget.currentRowChanged.connect(lambda _: self._update_connect_btn_state()) def _populate_list(self): self.list_widget.clear() for p in self._profiles: self.list_widget.addItem(f"👤 {p['user']}") if self._profiles: self.list_widget.setCurrentRow(0) def _update_connect_btn_state(self): self.btn_connect.setEnabled(self.list_widget.currentRow() >= 0) def _build_config(self, user: str, password: str) -> dict: base = DB_CONFIG.copy() base["user"] = user base["password"] = password return base def _accept_selected(self): idx = self.list_widget.currentRow() if idx < 0: QMessageBox.warning(self, "Выбор", "Выберите профиль из списка.") return p = self._profiles[idx] config = self._build_config(p["user"], p["password"]) db_test = Database() if not db_test.connect(config): QMessageBox.critical(self, "Ошибка подключения", f"Не удалось подключиться с логином «{p['user']}».\n" "Возможно, данные устарели — удалите профиль и добавьте заново.") return db_test.disconnect() self.selected_config = config self.accept() def _connect_new(self): user = self.user_edit.text().strip() password = self.pass_edit.text() if not user: QMessageBox.warning(self, "Ввод", "Введите логин.") return # Проверяем дубликат по логину existing_users = [p["user"] for p in self._profiles] if user in existing_users: QMessageBox.information(self, "Уже есть", f"Профиль с логином «{user}» уже сохранён.\nВыберите его из списка.") # Выбрать соответствующий элемент в списке idx = existing_users.index(user) self.list_widget.setCurrentRow(idx) return config = self._build_config(user, password) db_test = Database() if not db_test.connect(config): QMessageBox.critical(self, "Ошибка подключения", "Не удалось подключиться.\nПроверьте логин и пароль.") return db_test.disconnect() # Сохраняем новый профиль self._profiles.append({"user": user, "password": password}) save_profiles(self._profiles) self._populate_list() self.list_widget.setCurrentRow(len(self._profiles) - 1) self.selected_config = config self.accept() def _delete_selected(self): idx = self.list_widget.currentRow() if idx < 0: return user = self._profiles[idx]["user"] reply = QMessageBox.question( self, "Удаление профиля", f"Удалить профиль «{user}»?", QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No ) if reply == QMessageBox.StandardButton.Yes: self._profiles.pop(idx) save_profiles(self._profiles) self._populate_list() self._update_connect_btn_state() def main(): app = QApplication(sys.argv) app.setStyleSheet(STYLE) # Диалог выбора подключения к БД (только логин + пароль) conn_dlg = DBConnectDialog() if conn_dlg.exec() != QDialog.DialogCode.Accepted: sys.exit(0) config = conn_dlg.selected_config db = Database() if not db.connect(config): QMessageBox.critical(None, "Ошибка", "Не удалось подключиться к базе данных.") sys.exit(1) # Логин в приложение login = LoginDialog() login.set_db_config(config) if login.exec() != QDialog.DialogCode.Accepted: sys.exit(0) window = MainWindow(db, login.user_data) window.show() sys.exit(app.exec()) if __name__ == "__main__": main()