main
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import pymysql
import os
from PIL import Image, ImageTk
# --- КОНФИГУРАЦИЯ ---
DB_CONFIG = {
'host': 'localhost',
'user': 'root', # ЗАМЕНИТЕ на вашего пользователя MySQL
'password': '12345',# ЗАМЕНИТЕ на ваш пароль MySQL
'database': 'shoe_store_db',
'cursorclass': pymysql.cursors.DictCursor,
'charset': 'utf8mb4'
}
DEFAULT_IMAGE_PATH = "picture.png"
IMAGES_FOLDER = "images"
class Database:
def __init__(self):
self.conn = None
self.connect()
def connect(self):
try:
self.conn = pymysql.connect(**DB_CONFIG)
except pymysql.MySQLError as e:
messagebox.showerror("Ошибка БД", f"Не удалось подключиться к базе данных:\n{e}")
exit(1)
def get_products(self, search="", supplier_id=None, sort_by="quantity", sort_order="ASC"):
try:
with self.conn.cursor() as cursor:
query = """
SELECT p.product_id, p.product_name, c.category_name, p.description,
m.manufacturer_name, s.supplier_name, s.supplier_id, p.price, p.unit,
p.quantity, p.discount, p.image_path
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE 1=1
"""
params = []
if search:
query += " AND (p.product_name LIKE %s OR p.description LIKE %s OR s.supplier_name LIKE %s)"
term = f"%{search}%"
params.extend([term, term, term])
if supplier_id:
query += " AND p.supplier_id = %s"
params.append(supplier_id)
query += f" ORDER BY p.{sort_by} {sort_order}"
cursor.execute(query, params)
return cursor.fetchall()
except pymysql.MySQLError as e:
messagebox.showerror("Ошибка", f"Ошибка получения данных: {e}")
return []
def get_suppliers(self):
with self.conn.cursor() as cursor:
cursor.execute("SELECT supplier_id, supplier_name FROM suppliers")
return cursor.fetchall()
def get_categories(self):
with self.conn.cursor() as cursor:
cursor.execute("SELECT category_id, category_name FROM categories")
return cursor.fetchall()
def get_manufacturers(self):
with self.conn.cursor() as cursor:
cursor.execute("SELECT manufacturer_id, manufacturer_name FROM manufacturers")
return cursor.fetchall()
def check_product_in_orders(self, product_id):
with self.conn.cursor() as cursor:
cursor.execute("SELECT COUNT(*) as cnt FROM order_items WHERE product_id = %s", (product_id,))
return cursor.fetchone()['cnt'] > 0
def delete_product(self, product_id, image_path):
if self.check_product_in_orders(product_id):
return False, "Невозможно удалить товар, так как он присутствует в заказах!"
try:
with self.conn.cursor() as cursor:
cursor.execute("DELETE FROM products WHERE product_id = %s", (product_id,))
self.conn.commit()
# Удаляем старое фото, если это не заглушка
if image_path and image_path != DEFAULT_IMAGE_PATH and os.path.exists(image_path):
os.remove(image_path)
return True, "Товар успешно удален."
except pymysql.MySQLError as e:
return False, f"Ошибка базы данных: {e}"
def save_product(self, data, product_id=None):
try:
with self.conn.cursor() as cursor:
if product_id:
query = """
UPDATE products SET product_name=%s, category_id=%s, description=%s,
manufacturer_id=%s, supplier_id=%s, price=%s, unit=%s, quantity=%s,
discount=%s, image_path=%s WHERE product_id=%s
"""
cursor.execute(query, (data['name'], data['cat'], data['desc'], data['man'],
data['sup'], data['price'], data['unit'], data['qty'],
data['disc'], data['img'], product_id))
else:
query = """
INSERT INTO products (product_name, category_id, description, manufacturer_id,
supplier_id, price, unit, quantity, discount, image_path)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
cursor.execute(query, (data['name'], data['cat'], data['desc'], data['man'],
data['sup'], data['price'], data['unit'], data['qty'],
data['disc'], data['img']))
self.conn.commit()
return True, "Данные успешно сохранены!"
except pymysql.MySQLError as e:
return False, f"Ошибка сохранения: {e}"
db = Database()
class App(tk.Tk):
def __init__(self):
super().__init__()
self.title("Система управления складом обуви")
self.geometry("1100x650")
self.current_user = None
self.edit_window_open = False
self.sort_order = "ASC" # Для сортировки по количеству
# Настройка стилей Treeview
style = ttk.Style()
style.configure("Treeview", rowheight=30, font=('Arial', 10))
style.configure("Treeview.Heading", font=('Arial', 10, 'bold'))
# Теги для подсветки (Модуль 2)
style.map('Treeview', background=[('selected', '#2E8B57')])
# Создаем папку для картинок, если нет
if not os.path.exists(IMAGES_FOLDER):
os.makedirs(IMAGES_FOLDER)
self.show_login_screen()
def clear_screen(self):
for widget in self.winfo_children():
widget.destroy()
def show_login_screen(self):
self.clear_screen()
self.title("Вход в систему")
frame = tk.Frame(self, padx=50, pady=50)
frame.pack(expand=True)
tk.Label(frame, text="Логин:", font=('Arial', 12)).grid(row=0, column=0, pady=10, sticky='e')
self.login_entry = tk.Entry(frame, font=('Arial', 12))
self.login_entry.grid(row=0, column=1, pady=10)
tk.Label(frame, text="Пароль:", font=('Arial', 12)).grid(row=1, column=0, pady=10, sticky='e')
self.pass_entry = tk.Entry(frame, show="*", font=('Arial', 12))
self.pass_entry.grid(row=1, column=1, pady=10)
tk.Button(frame, text="Войти", command=self.login, width=15, bg="#4CAF50", fg="white", font=('Arial', 10, 'bold')).grid(row=2, column=0, columnspan=2, pady=20)
tk.Button(frame, text="Войти как Гость", command=lambda: self.start_app({'full_name': 'Гость', 'role_name': 'guest'}), width=15).grid(row=3, column=0, columnspan=2)
def login(self):
login = self.login_entry.get()
password = self.pass_entry.get()
try:
with db.conn.cursor() as cursor:
cursor.execute("""
SELECT u.full_name, r.role_name
FROM users u JOIN roles r ON u.role_id = r.role_id
WHERE u.login = %s AND u.password_hash = %s
""", (login, password))
user = cursor.fetchone()
if user:
self.start_app(user)
else:
messagebox.showerror("Ошибка авторизации", "Неверный логин или пароль", icon='error')
except Exception as e:
messagebox.showerror("Ошибка", str(e))
def start_app(self, user):
self.current_user = user
self.clear_screen()
self.title(f"Главная панель - Роль: {user['role_name']}")
# Верхняя панель
top_frame = tk.Frame(self, bg="#333", height=50)
top_frame.pack(fill=tk.X)
top_frame.pack_propagate(False)
tk.Button(top_frame, text="← Выход", command=self.show_login_screen, bg="#f44336", fg="white", relief=tk.FLAT, font=('Arial', 10, 'bold')).pack(side=tk.LEFT, padx=10, pady=10)
tk.Label(top_frame, text=f"👤 {user['full_name']}", bg="#333", fg="white", font=('Arial', 12, 'bold')).pack(side=tk.RIGHT, padx=20, pady=10)
self.setup_products_screen()
def setup_products_screen(self):
main_frame = tk.Frame(self)
main_frame.pack(fill=tk.BOTH, expand=True, padx=15, pady=15)
# Панель управления (только для manager и admin)
ctrl_frame = tk.Frame(main_frame)
ctrl_frame.pack(fill=tk.X, pady=(0, 10))
is_admin = self.current_user['role_name'] == 'admin'
is_manager = self.current_user['role_name'] == 'manager'
if is_admin or is_manager:
tk.Label(ctrl_frame, text="🔍 Поиск:").pack(side=tk.LEFT)
self.search_var = tk.StringVar()
self.search_var.trace_add("write", self.on_search_filter_change)
tk.Entry(ctrl_frame, textvariable=self.search_var, width=25).pack(side=tk.LEFT, padx=5)
tk.Label(ctrl_frame, text="Поставщик:").pack(side=tk.LEFT, padx=(20, 0))
self.supplier_var = tk.StringVar(value="Все поставщики")
self.supplier_var.trace_add("write", self.on_search_filter_change)
suppliers = db.get_suppliers()
sup_values = ["Все поставщики"] + [s['supplier_name'] for s in suppliers]
# Сохраняем маппинг имя -> id для фильтрации
self.supplier_map = {s['supplier_name']: s['supplier_id'] for s in suppliers}
self.supplier_map["Все поставщики"] = None
ttk.Combobox(ctrl_frame, textvariable=self.supplier_var, values=sup_values, state="readonly", width=20).pack(side=tk.LEFT, padx=5)
tk.Label(ctrl_frame, text="Сортировка по ост.:", fg="gray").pack(side=tk.LEFT, padx=(20, 0))
tk.Button(ctrl_frame, text="▲", width=2, command=lambda: self.toggle_sort("ASC")).pack(side=tk.LEFT)
tk.Button(ctrl_frame, text="▼", width=2, command=lambda: self.toggle_sort("DESC")).pack(side=tk.LEFT)
if is_admin:
tk.Button(ctrl_frame, text="+ Добавить товар", command=self.open_edit_form, bg="#2196F3", fg="white").pack(side=tk.RIGHT)
# Таблица
columns = ("id", "name", "category", "price", "quantity", "discount")
self.tree = ttk.Treeview(main_frame, columns=columns, show="headings")
self.tree.heading("id", text="ID")
self.tree.heading("name", text="Наименование")
self.tree.heading("category", text="Категория")
self.tree.heading("price", text="Цена")
self.tree.heading("quantity", text="Остаток")
self.tree.heading("discount", text="Скидка %")
self.tree.column("id", width=40, anchor='center')
self.tree.column("name", width=250)
self.tree.column("category", width=100)
self.tree.column("price", width=150, anchor='e')
self.tree.column("quantity", width=80, anchor='center')
self.tree.column("discount", width=80, anchor='center')
self.tree.pack(fill=tk.BOTH, expand=True)
# Настройка тегов для подсветки (Модуль 2)
self.tree.tag_configure('high_discount', background='#2E8B57') # Зеленый при >15%
self.tree.tag_configure('no_stock', background='lightblue') # Голубой при 0
self.tree.bind("<Double-1>", lambda e: self.open_edit_form() if is_admin else None)
if is_admin:
btn_frame = tk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=5)
tk.Button(btn_frame, text="🗑 Удалить выбранный товар", command=self.delete_selected, bg="#ffcdd2").pack(side=tk.RIGHT)
tk.Button(btn_frame, text="✏ Редактировать", command=self.open_edit_form).pack(side=tk.RIGHT, padx=5)
self.refresh_table()
def toggle_sort(self, order):
self.sort_order = order
self.refresh_table()
def on_search_filter_change(self, *args):
self.refresh_table()
def refresh_table(self):
for item in self.tree.get_children():
self.tree.delete(item)
search = self.search_var.get() if hasattr(self, 'search_var') else ""
sup_name = self.supplier_var.get() if hasattr(self, 'supplier_var') else "Все поставщики"
sup_id = self.supplier_map.get(sup_name) if hasattr(self, 'supplier_map') else None
products = db.get_products(search=search, supplier_id=sup_id, sort_by="quantity", sort_order=self.sort_order)
for row in products:
p_id = row['product_id']
price = float(row['price'])
discount = float(row['discount'])
qty = int(row['quantity'])
# Форматирование цены (Модуль 2: перечеркнутая цена эмулируется текстом)
if discount > 0:
final_price = price * (1 - discount / 100)
price_display = f"{price:.0f} ₽ → {final_price:.0f} ₽"
else:
price_display = f"{price:.0f} ₽"
# Определение тегов подсветки
tags = ()
if qty == 0:
tags = ('no_stock',)
elif discount > 15:
tags = ('high_discount',)
self.tree.insert("", tk.END, iid=p_id, values=(
p_id, row['product_name'], row['category_name'],
price_display, qty, f"{discount}%"
), tags=tags)
def open_edit_form(self):
if self.edit_window_open:
messagebox.showwarning("Предупреждение", "Окно редактирования уже открыто! Закройте его, чтобы открыть новое.", icon='warning')
return
selected = self.tree.selection()
product_id = selected[0] if selected else None
self.edit_window_open = True
EditForm(self, product_id)
def delete_selected(self):
selected = self.tree.selection()
if not selected:
messagebox.showinfo("Информация", "Выберите товар для удаления.", icon='info')
return
product_id = selected[0]
# Получаем путь к картинке для удаления файла
with db.conn.cursor() as cursor:
cursor.execute("SELECT image_path FROM products WHERE product_id = %s", (product_id,))
img_path = cursor.fetchone()['image_path']
success, msg = db.delete_product(product_id, img_path)
if success:
messagebox.showinfo("Успех", msg, icon='info')
self.refresh_table()
else:
messagebox.showerror("Ошибка удаления", msg, icon='error')
def on_edit_window_close(self):
self.edit_window_open = False
self.refresh_table()
class EditForm(tk.Toplevel):
def __init__(self, parent, product_id):
super().__init__(parent)
self.parent = parent
self.product_id = product_id
self.title("Редактирование товара" if product_id else "Добавление товара")
self.geometry("500x600")
self.resizable(False, False)
self.protocol("WM_DELETE_WINDOW", self.on_close)
# Переменная для пути к картинке
self.current_image_path = DEFAULT_IMAGE_PATH
self.photo_image = None
self.build_form()
if product_id:
self.load_data()
def build_form(self):
main_frame = tk.Frame(self, padx=20, pady=15)
main_frame.pack(fill=tk.BOTH, expand=True)
# Картинка
self.img_label = tk.Label(main_frame, text="Нет изображения", bg="gray", width=30, height=10)
self.img_label.grid(row=0, column=0, columnspan=2, pady=5)
tk.Button(main_frame, text="Загрузить/Изменить фото", command=self.load_image).grid(row=1, column=0, columnspan=2, pady=5)
fields = [
("Наименование:", "name", 2),
("Категория:", "cat", 3, "combo"),
("Производитель:", "man", 4, "combo"),
("Поставщик:", "sup", 5, "combo"),
("Цена (руб):", "price", 6),
("Ед. изм.:", "unit", 7),
("Количество:", "qty", 8),
("Скидка (%):", "disc", 9),
]
self.vars = {}
for f in fields:
tk.Label(main_frame, text=f[0]).grid(row=f[2], column=0, sticky='e', pady=3)
if len(f) > 3 and f[3] == "combo":
var = tk.StringVar()
cb = ttk.Combobox(main_frame, textvariable=var, state="readonly", width=25)
cb.grid(row=f[2], column=1, pady=3, sticky='w')
# Заполнение комбобоксов
if f[1] == "cat":
cb['values'] = [""] + [c['category_name'] for c in db.get_categories()]
self.cat_map = {c['category_name']: c['category_id'] for c in db.get_categories()}
elif f[1] == "man":
cb['values'] = [""] + [m['manufacturer_name'] for m in db.get_manufacturers()]
self.man_map = {m['manufacturer_name']: m['manufacturer_id'] for m in db.get_manufacturers()}
elif f[1] == "sup":
cb['values'] = [""] + [s['supplier_name'] for s in db.get_suppliers()]
self.sup_map = {s['supplier_name']: s['supplier_id'] for s in db.get_suppliers()}
self.vars[f[1]] = var
else:
var = tk.StringVar()
tk.Entry(main_frame, textvariable=var, width=28).grid(row=f[2], column=1, pady=3, sticky='w')
self.vars[f[1]] = var
tk.Label(main_frame, text="Описание:").grid(row=10, column=0, sticky='ne', pady=3)
self.desc_text = tk.Text(main_frame, width=30, height=4)
self.desc_text.grid(row=10, column=1, pady=3, sticky='w')
if self.product_id:
self.vars['id'] = tk.StringVar(value=str(self.product_id))
tk.Label(main_frame, text=f"ID: {self.product_id} (только для чтения)", fg="gray").grid(row=11, column=0, columnspan=2, pady=5)
tk.Button(main_frame, text="💾 Сохранить", command=self.save, bg="#4CAF50", fg="white", font=('Arial', 10, 'bold'), width=15).grid(row=12, column=0, columnspan=2, pady=15)
def load_image(self):
filepath = filedialog.askopenfilename(filetypes=[("Image files", "*.png *.jpg *.jpeg")])
if filepath:
# Модуль 3: Ограничение размера фото 300x200
img = Image.open(filepath)
img = img.resize((300, 200), Image.Resampling.LANCZOS)
# Сохраняем в папку images
filename = os.path.basename(filepath)
save_path = os.path.join(IMAGES_FOLDER, filename)
img.save(save_path)
self.current_image_path = save_path
self.photo_image = ImageTk.PhotoImage(img)
self.img_label.config(image=self.photo_image, text="")
def load_data(self):
with db.conn.cursor() as cursor:
cursor.execute("SELECT * FROM products WHERE product_id = %s", (self.product_id,))
row = cursor.fetchone()
if row:
self.vars['name'].set(row['product_name'])
self.vars['cat'].set(row['category_name'] or "")
self.vars['man'].set(row['manufacturer_name'] or "")
self.vars['sup'].set(row['supplier_name'] or "")
self.vars['price'].set(str(row['price']))
self.vars['unit'].set(row['unit'])
self.vars['qty'].set(str(row['quantity']))
self.vars['disc'].set(str(row['discount']))
self.desc_text.insert(tk.END, row['description'] or "")
self.current_image_path = row['image_path']
if os.path.exists(self.current_image_path):
try:
img = Image.open(self.current_image_path)
img = img.resize((300, 200), Image.Resampling.LANCZOS)
self.photo_image = ImageTk.PhotoImage(img)
self.img_label.config(image=self.photo_image, text="")
except Exception:
pass
def save(self):
# Модуль 3: Валидация (неотрицательные значения, числа)
try:
price = float(self.vars['price'].get())
qty = int(self.vars['qty'].get())
disc = float(self.vars['disc'].get() or 0)
if price < 0 or qty < 0 or disc < 0 or disc > 100:
raise ValueError("Отрицательные значения или некорректная скидка")
except ValueError:
messagebox.showerror("Ошибка ввода", "Цена и количество должны быть числами >= 0. Скидка от 0 до 100.", icon='error')
return
if not self.vars['name'].get().strip():
messagebox.showerror("Ошибка ввода", "Наименование товара не может быть пустым.", icon='error')
return
data = {
'name': self.vars['name'].get(),
'cat': self.cat_map.get(self.vars['cat'].get()),
'man': self.man_map.get(self.vars['man'].get()),
'sup': self.sup_map.get(self.vars['sup'].get()),
'price': price,
'unit': self.vars['unit'].get() or 'шт',
'qty': qty,
'disc': disc,
'img': self.current_image_path,
'desc': self.desc_text.get("1.0", tk.END).strip()
}
success, msg = db.save_product(data, self.product_id)
if success:
messagebox.showinfo("Успех", msg, icon='info')
self.on_close()
else:
messagebox.showerror("Ошибка сохранения", msg, icon='error')
def on_close(self):
self.destroy()
self.parent.on_edit_window_close()
if __name__ == "__main__":
# Проверка наличия заглушки
if not os.path.exists(DEFAULT_IMAGE_PATH):
# Создаем пустой файл, чтобы приложение не падало при первой попытке
with open(DEFAULT_IMAGE_PATH, 'w') as f:
pass
app = App()
app.mainloop()