import pymysql class Database: def __init__(self): self.connection = pymysql.connect( host="localhost", user="root", password="", database="fabrik_demo", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor ) def fetch(self, query, params=None): # with self.connection.cursor() as cursor: cursor = self.connection.cursor() cursor.execute(query, params) return cursor.fetchall() ----------- import sys from PyQt6.QtWidgets import * from PyQt6.QtGui import QColor from main_ui import Ui_Form from db3 import Database class MainWindow(QWidget): def __init__(self): super().__init__() self.ui = Ui_Form() self.ui.setupUi(self) self.db = Database() self.setWindowTitle('Заказы') self.setMinimumSize(800,300) self.setup_table() self.load_customers() self.load_orders() self.fill_sort_fields() # signals self.ui.pushButton.clicked.connect(self.filter_orders) self.ui.pushButton_3.clicked.connect(self.show_all) self.ui.pushButton_2.clicked.connect(self.search_orders) self.ui.radioButton.toggled.connect(self.sort_orders) self.ui.radioButton_2.toggled.connect(self.sort_orders) # ---------------- TABLE ---------------- def setup_table(self): self.ui.tableWidget.setColumnCount(5) self.ui.tableWidget.setHorizontalHeaderLabels([ "Заказчик", "Город", "Телефон", "Дата заказа", "Сумма заказа" ]) self.ui.tableWidget.setEditTriggers(self.ui.tableWidget.EditTrigger.NoEditTriggers) # ---------------- SORT LIST ---------------- def fill_sort_fields(self): self.ui.listWidget.clear() self.ui.listWidget.addItems([ "Заказчик", "Дата заказа", "Сумма заказа", "Город", "Телефон" ]) # ---------------- CUSTOMERS ---------------- def load_customers(self): try: self.ui.comboBox.clear() rows = self.db.fetch(""" SELECT id, name FROM customer WHERE salesman = 1 """) for row in rows: self.ui.comboBox.addItem(row["name"], row["id"]) except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) # ---------------- LOAD ORDERS ---------------- def load_orders(self, query=None): try: if query is None: query = """ SELECT c.name, c.address, c.phone, o.date, o.total_amount FROM orders o JOIN customer c ON c.id = o.customer """ rows = self.db.fetch(query) self.ui.tableWidget.setRowCount(len(rows)) total_sum = 0 for i, row in enumerate(rows): self.ui.tableWidget.setItem(i, 0, QTableWidgetItem(row["name"])) self.ui.tableWidget.setItem(i, 1, QTableWidgetItem(row["address"])) self.ui.tableWidget.setItem(i, 2, QTableWidgetItem(row["phone"])) self.ui.tableWidget.setItem(i, 3, QTableWidgetItem(str(row["date"]))) self.ui.tableWidget.setItem(i, 4, QTableWidgetItem(str(row["total_amount"]))) total_sum += float(row["total_amount"]) self.ui.label_4.setText(f"Всего заказов: {len(rows)}") self.ui.label_5.setText(f"Общая сумма: {total_sum:.2f}") except Exception as e: QMessageBox.critical(self, "Ошибка", str(e)) # ---------------- FILTER ---------------- def filter_orders(self): try: customer_id = self.ui.comboBox.currentData() query = f""" SELECT c.name, c.address, c.phone, o.date, o.total_amount FROM orders o JOIN customer c ON c.id = o.customer WHERE o.customer = {customer_id} """ self.load_orders(query) except Exception as e: QMessageBox.warning(self, "Ошибка", str(e)) # ---------------- SHOW ALL ---------------- def show_all(self): self.load_orders() # ---------------- SORT ---------------- def sort_orders(self): item = self.ui.listWidget.currentItem() if item is None: return fields = { "Заказчик": "c.name", "Дата заказа": "o.date", "Сумма заказа": "o.total_amount", "Город": "c.address", "Телефон": "c.phone" } field = fields.get(item.text()) if not field: return direction = "ASC" if self.ui.radioButton.isChecked() else "DESC" query = f""" SELECT c.name, c.address, c.phone, o.date, o.total_amount FROM orders o JOIN customer c ON c.id = o.customer ORDER BY {field} {direction} """ self.load_orders(query) # ---------------- SEARCH ---------------- def search_orders(self): text = self.ui.lineEdit.text().strip().lower() if not text: QMessageBox.information(self, "Поиск", "Введите строку поиска") return found = False for row in range(self.ui.tableWidget.rowCount()): for col in range(self.ui.tableWidget.columnCount()): item = self.ui.tableWidget.item(row, col) if not item: continue item.setBackground(QColor("white")) if text in item.text().lower(): item.setBackground(QColor("yellow")) found = True if not found: QMessageBox.information(self, "Поиск", "Совпадений не найдено") if __name__ == '__main__': app = QApplication(sys.argv) window = MainWindow() window.show() sys.exit(app.exec()) -------------- # Form implementation generated from reading ui file 'unL.ui' # # Created by: PyQt6 UI code generator 6.11.0 # # WARNING: Any manual changes made to this file will be lost when pyuic6 is # run again. Do not edit this file unless you know what you are doing. from PyQt6 import QtCore, QtGui, QtWidgets class Ui_Form(object): def setupUi(self, Form): Form.setObjectName("Form") Form.resize(759, 418) self.verticalLayout_6 = QtWidgets.QVBoxLayout(Form) self.verticalLayout_6.setObjectName("verticalLayout_6") self.verticalLayout_5 = QtWidgets.QVBoxLayout() self.verticalLayout_5.setObjectName("verticalLayout_5") self.horizontalLayout_2 = QtWidgets.QHBoxLayout() self.horizontalLayout_2.setObjectName("horizontalLayout_2") self.gridLayout = QtWidgets.QGridLayout() self.gridLayout.setObjectName("gridLayout") self.lineEdit = QtWidgets.QLineEdit(parent=Form) self.lineEdit.setObjectName("lineEdit") self.gridLayout.addWidget(self.lineEdit, 1, 1, 1, 1) self.label_2 = QtWidgets.QLabel(parent=Form) self.label_2.setObjectName("label_2") self.gridLayout.addWidget(self.label_2, 1, 0, 1, 1) self.comboBox = QtWidgets.QComboBox(parent=Form) self.comboBox.setObjectName("comboBox") self.gridLayout.addWidget(self.comboBox, 0, 1, 1, 1) self.pushButton_2 = QtWidgets.QPushButton(parent=Form) self.pushButton_2.setObjectName("pushButton_2") self.gridLayout.addWidget(self.pushButton_2, 1, 2, 1, 1) self.pushButton_3 = QtWidgets.QPushButton(parent=Form) self.pushButton_3.setObjectName("pushButton_3") self.gridLayout.addWidget(self.pushButton_3, 0, 3, 1, 1) self.pushButton = QtWidgets.QPushButton(parent=Form) self.pushButton.setObjectName("pushButton") self.gridLayout.addWidget(self.pushButton, 0, 2, 1, 1) self.label = QtWidgets.QLabel(parent=Form) self.label.setObjectName("label") self.gridLayout.addWidget(self.label, 0, 0, 1, 1) self.horizontalLayout_2.addLayout(self.gridLayout) self.verticalLayout = QtWidgets.QVBoxLayout() self.verticalLayout.setObjectName("verticalLayout") self.label_3 = QtWidgets.QLabel(parent=Form) self.label_3.setObjectName("label_3") self.verticalLayout.addWidget(self.label_3) self.horizontalLayout = QtWidgets.QHBoxLayout() self.horizontalLayout.setObjectName("horizontalLayout") self.listWidget = QtWidgets.QListWidget(parent=Form) self.listWidget.setObjectName("listWidget") self.horizontalLayout.addWidget(self.listWidget) self.verticalLayout_3 = QtWidgets.QVBoxLayout() self.verticalLayout_3.setObjectName("verticalLayout_3") self.radioButton = QtWidgets.QRadioButton(parent=Form) self.radioButton.setObjectName("radioButton") self.verticalLayout_3.addWidget(self.radioButton) self.radioButton_2 = QtWidgets.QRadioButton(parent=Form) self.radioButton_2.setObjectName("radioButton_2") self.verticalLayout_3.addWidget(self.radioButton_2) self.horizontalLayout.addLayout(self.verticalLayout_3) self.verticalLayout.addLayout(self.horizontalLayout) self.horizontalLayout_2.addLayout(self.verticalLayout) self.verticalLayout_5.addLayout(self.horizontalLayout_2) self.horizontalLayout_3 = QtWidgets.QHBoxLayout() self.horizontalLayout_3.setObjectName("horizontalLayout_3") self.tableWidget = QtWidgets.QTableWidget(parent=Form) self.tableWidget.setObjectName("tableWidget") self.tableWidget.setColumnCount(0) self.tableWidget.setRowCount(0) self.horizontalLayout_3.addWidget(self.tableWidget) self.label_6 = QtWidgets.QLabel(parent=Form) self.label_6.setText("") self.label_6.setObjectName("label_6") self.horizontalLayout_3.addWidget(self.label_6) self.verticalLayout_5.addLayout(self.horizontalLayout_3) self.verticalLayout_4 = QtWidgets.QVBoxLayout() self.verticalLayout_4.setObjectName("verticalLayout_4") self.label_4 = QtWidgets.QLabel(parent=Form) self.label_4.setObjectName("label_4") self.verticalLayout_4.addWidget(self.label_4) self.label_5 = QtWidgets.QLabel(parent=Form) self.label_5.setObjectName("label_5") self.verticalLayout_4.addWidget(self.label_5) self.verticalLayout_5.addLayout(self.verticalLayout_4) self.verticalLayout_6.addLayout(self.verticalLayout_5) self.retranslateUi(Form) QtCore.QMetaObject.connectSlotsByName(Form) def retranslateUi(self, Form): _translate = QtCore.QCoreApplication.translate Form.setWindowTitle(_translate("Form", "Form")) self.label_2.setText(_translate("Form", "Введите строку поиска ")) self.pushButton_2.setText(_translate("Form", "Найти")) self.pushButton_3.setText(_translate("Form", "Показать все")) self.pushButton.setText(_translate("Form", "Фильтровать")) self.label.setText(_translate("Form", "Выберите заказчика ")) self.label_3.setText(_translate("Form", "Выберите поле для сортировки")) self.radioButton.setText(_translate("Form", "По возрастанию")) self.radioButton_2.setText(_translate("Form", "По убыванию")) self.label_4.setText(_translate("Form", "Всего заказов: ")) self.label_5.setText(_translate("Form", "Общая сумма: ") ------------ CREATE DEFINER=`root`@`%` TRIGGER `product_material_AFTER_INSERT` AFTER INSERT ON `product_material` FOR EACH ROW BEGIN declare pr decimal(10, 2); declare t_pr decimal(10, 2); select m.price into pr from material m where m.id = new.material; set t_pr = new.quantity * pr; update product set price_cost = price_cost + t_pr where art = new.product; END