999 lines
39 KiB
Python
999 lines
39 KiB
Python
from flask import Flask, render_template, send_from_directory, request, redirect, url_for, flash, session
|
||
import sqlite3
|
||
import os
|
||
from datetime import datetime
|
||
|
||
app = Flask(__name__)
|
||
app.secret_key = 'my_secret_key_12345'
|
||
|
||
# ========== ПОДКЛЮЧЕНИЕ К БАЗЕ ДАННЫХ ==========
|
||
def get_db_connection():
|
||
conn = sqlite3.connect('materials.db')
|
||
conn.row_factory = sqlite3.Row
|
||
return conn
|
||
|
||
# ========== ФУНКЦИИ РАСЧЕТА СКИДОК И СТОИМОСТИ ==========
|
||
|
||
def calculate_supplier_discount(supplier_id, year):
|
||
"""Расчет накопительной скидки поставщика за год (по сумме заказов)"""
|
||
conn = get_db_connection()
|
||
|
||
# Получаем все заказы поставщика за указанный год
|
||
orders = conn.execute('''
|
||
SELECT total_amount FROM Orders
|
||
WHERE client_id = ? AND strftime('%Y', order_date) = ?
|
||
''', (supplier_id, str(year))).fetchall()
|
||
|
||
conn.close()
|
||
|
||
total_year = sum(order['total_amount'] for order in orders)
|
||
|
||
if total_year >= 500000:
|
||
return 20
|
||
elif total_year >= 300000:
|
||
return 15
|
||
elif total_year >= 150000:
|
||
return 10
|
||
return 0
|
||
|
||
def calculate_order_total_with_transport(items):
|
||
"""Расчет общей суммы заказа с транспортными расходами (10%)"""
|
||
total = 0
|
||
for item in items:
|
||
total += item['quantity'] * item['price'] * 1.1
|
||
return round(total, 2)
|
||
|
||
# ========== ГЛАВНАЯ СТРАНИЦА (ПОСТАВЩИКИ) ==========
|
||
|
||
@app.route('/')
|
||
def index():
|
||
# Получаем параметры фильтрации из URL
|
||
search_query = request.args.get('search', '')
|
||
client_type = request.args.get('type', 'all')
|
||
min_rating = request.args.get('rating', '0')
|
||
sort_by = request.args.get('sort', 'name_asc')
|
||
|
||
conn = get_db_connection()
|
||
|
||
try:
|
||
# Получаем общее количество всех клиентов (без фильтров)
|
||
total_count = conn.execute('SELECT COUNT(*) as count FROM Clients').fetchone()['count']
|
||
|
||
# Базовый SQL запрос для клиентов
|
||
sql = '''
|
||
SELECT c.*, ct.type_name as contractor_type_name,
|
||
COALESCE(SUM(o.total_amount), 0) as total_amount,
|
||
COUNT(DISTINCT o.id) as orders_count
|
||
FROM Clients c
|
||
LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
|
||
LEFT JOIN Orders o ON c.id = o.client_id
|
||
WHERE 1=1
|
||
'''
|
||
params = []
|
||
|
||
# Поиск по названию или ИНН
|
||
if search_query:
|
||
sql += " AND (c.name LIKE ? OR c.inn LIKE ?)"
|
||
params.extend([f'%{search_query}%', f'%{search_query}%'])
|
||
|
||
# Фильтр по типу клиента
|
||
if client_type != 'all':
|
||
sql += " AND ct.type_name = ?"
|
||
params.append(client_type)
|
||
|
||
# Фильтр по минимальному рейтингу
|
||
if min_rating != '0':
|
||
sql += " AND c.rating >= ?"
|
||
params.append(float(min_rating))
|
||
|
||
# Группировка (обязательно после WHERE)
|
||
sql += " GROUP BY c.id"
|
||
|
||
# Сортировка
|
||
if sort_by == 'name_asc':
|
||
sql += " ORDER BY c.name ASC"
|
||
elif sort_by == 'name_desc':
|
||
sql += " ORDER BY c.name DESC"
|
||
elif sort_by == 'rating_desc':
|
||
sql += " ORDER BY c.rating DESC"
|
||
elif sort_by == 'rating_asc':
|
||
sql += " ORDER BY c.rating ASC"
|
||
elif sort_by == 'discount_desc':
|
||
sql += " ORDER BY total_amount DESC"
|
||
elif sort_by == 'discount_asc':
|
||
sql += " ORDER BY total_amount ASC"
|
||
else:
|
||
sql += " ORDER BY c.name ASC"
|
||
|
||
# Выполняем запрос
|
||
clients = conn.execute(sql, params).fetchall()
|
||
|
||
# Добавляем информацию о скидке на основе суммы
|
||
clients_with_discount = []
|
||
for client in clients:
|
||
total_amount = client['total_amount'] or 0
|
||
|
||
# Рассчитываем текущую скидку на основе истории заказов
|
||
current_discount = calculate_supplier_discount(client['id'], datetime.now().year)
|
||
|
||
client_dict = dict(client)
|
||
client_dict['total_amount'] = total_amount
|
||
client_dict['total_sales'] = total_amount
|
||
client_dict['orders_count'] = client['orders_count'] or 0
|
||
client_dict['discount_percent'] = current_discount
|
||
client_dict['discount'] = current_discount
|
||
client_dict['discount_text'] = f'{current_discount}%'
|
||
|
||
clients_with_discount.append(client_dict)
|
||
|
||
conn.close()
|
||
|
||
return render_template('index.html',
|
||
partners=clients_with_discount,
|
||
total_count=total_count)
|
||
|
||
except sqlite3.OperationalError as e:
|
||
conn.close()
|
||
return render_template('index.html',
|
||
partners=[],
|
||
total_count=0,
|
||
error_message=f"Ошибка базы данных: {str(e)}")
|
||
|
||
# ========== МАРШРУТЫ ДЛЯ КЛИЕНТОВ ==========
|
||
|
||
@app.route('/add_client', methods=['GET', 'POST'])
|
||
def add_client():
|
||
if request.method == 'POST':
|
||
try:
|
||
name = request.form['name']
|
||
director = request.form.get('director', '')
|
||
email = request.form['email']
|
||
phone = request.form.get('phone', '')
|
||
address = request.form.get('address', '')
|
||
inn = request.form.get('inn', '')
|
||
contractor_type_id = request.form['contractor_type_id']
|
||
rating = request.form.get('rating', 0.0)
|
||
|
||
conn = get_db_connection()
|
||
|
||
# Проверяем уникальность ИНН
|
||
if inn:
|
||
existing = conn.execute('SELECT id FROM Clients WHERE inn = ?', (inn,)).fetchone()
|
||
if existing:
|
||
flash('Клиент с таким ИНН уже существует', 'error')
|
||
conn.close()
|
||
return redirect(url_for('add_client'))
|
||
|
||
conn.execute('''
|
||
INSERT INTO Clients (name, director, email, phone, address, inn, contractor_type_id, rating)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (name, director, email, phone, address, inn, contractor_type_id, rating))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
flash('Клиент успешно добавлен!', 'success')
|
||
return redirect(url_for('index'))
|
||
|
||
except Exception as e:
|
||
flash(f'Ошибка: {str(e)}', 'error')
|
||
return redirect(url_for('add_client'))
|
||
|
||
# GET запрос
|
||
conn = get_db_connection()
|
||
contractor_types = conn.execute('SELECT * FROM ContractorType ORDER BY type_name').fetchall()
|
||
conn.close()
|
||
return render_template('add_client.html', contractor_types=contractor_types)
|
||
|
||
@app.route('/edit_client/<int:client_id>', methods=['GET', 'POST'])
|
||
def edit_client(client_id):
|
||
conn = get_db_connection()
|
||
|
||
if request.method == 'POST':
|
||
try:
|
||
name = request.form['name']
|
||
director = request.form.get('director', '')
|
||
email = request.form['email']
|
||
phone = request.form.get('phone', '')
|
||
address = request.form.get('address', '')
|
||
inn = request.form.get('inn', '')
|
||
contractor_type_id = request.form['contractor_type_id']
|
||
rating = request.form.get('rating', 0.0)
|
||
|
||
# Проверяем уникальность ИНН
|
||
if inn:
|
||
existing = conn.execute(
|
||
'SELECT id FROM Clients WHERE inn = ? AND id != ?',
|
||
(inn, client_id)
|
||
).fetchone()
|
||
if existing:
|
||
flash('Клиент с таким ИНН уже существует', 'error')
|
||
conn.close()
|
||
return redirect(url_for('edit_client', client_id=client_id))
|
||
|
||
conn.execute('''
|
||
UPDATE Clients
|
||
SET name = ?, director = ?, email = ?, phone = ?,
|
||
address = ?, inn = ?, contractor_type_id = ?, rating = ?
|
||
WHERE id = ?
|
||
''', (name, director, email, phone, address, inn, contractor_type_id, rating, client_id))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
flash('Данные обновлены!', 'success')
|
||
return redirect(url_for('index'))
|
||
|
||
except Exception as e:
|
||
flash(f'Ошибка: {str(e)}', 'error')
|
||
conn.close()
|
||
return redirect(url_for('edit_client', client_id=client_id))
|
||
|
||
# GET запрос
|
||
client = conn.execute('''
|
||
SELECT c.*, ct.type_name as contractor_type_name
|
||
FROM Clients c
|
||
LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
|
||
WHERE c.id = ?
|
||
''', (client_id,)).fetchone()
|
||
|
||
contractor_types = conn.execute('SELECT * FROM ContractorType ORDER BY type_name').fetchall()
|
||
conn.close()
|
||
|
||
if not client:
|
||
flash('Клиент не найден', 'error')
|
||
return redirect(url_for('index'))
|
||
|
||
return render_template('edit_client.html', client=client, contractor_types=contractor_types)
|
||
|
||
# ========== МАРШРУТЫ ДЛЯ ЗАКАЗОВ ==========
|
||
|
||
@app.route('/order_history/<int:client_id>')
|
||
def order_history(client_id):
|
||
conn = get_db_connection()
|
||
|
||
client = conn.execute('''
|
||
SELECT c.*, ct.type_name as contractor_type_name
|
||
FROM Clients c
|
||
LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
|
||
WHERE c.id = ?
|
||
''', (client_id,)).fetchone()
|
||
|
||
if not client:
|
||
conn.close()
|
||
flash('Клиент не найден', 'error')
|
||
return redirect(url_for('index'))
|
||
|
||
# Получаем все заказы
|
||
orders = conn.execute('''
|
||
SELECT o.*
|
||
FROM Orders o
|
||
WHERE o.client_id = ?
|
||
ORDER BY o.order_date DESC, o.id DESC
|
||
''', (client_id,)).fetchall()
|
||
|
||
orders_with_items = []
|
||
|
||
for order in orders:
|
||
order_dict = dict(order)
|
||
|
||
# Получаем позиции для заказа
|
||
items = conn.execute('''
|
||
SELECT oi.*, cat.product_name, cat.article,
|
||
cat2.category_name, m.material_name
|
||
FROM OrderItems oi
|
||
JOIN Catalog cat ON oi.product_id = cat.id
|
||
LEFT JOIN Categories cat2 ON cat.category_id = cat2.id
|
||
LEFT JOIN Materials m ON cat.material_id = m.id
|
||
WHERE oi.order_id = ?
|
||
''', (order['id'],)).fetchall()
|
||
|
||
# Рассчитываем базовую стоимость (сумма quantity * price_at_moment)
|
||
base_amount = 0
|
||
items_list = []
|
||
|
||
for item in items:
|
||
item_dict = dict(item)
|
||
# Рассчитываем стоимость позиции
|
||
item_total = item_dict['quantity'] * item_dict['price_at_moment']
|
||
item_dict['item_total'] = item_total
|
||
base_amount += item_total
|
||
items_list.append(item_dict)
|
||
|
||
# Транспортные расходы (10% от базовой стоимости)
|
||
shipping_cost = base_amount * 0.1
|
||
|
||
# Получаем скидку клиента
|
||
try:
|
||
if order_dict['order_date']:
|
||
order_year = datetime.strptime(order_dict['order_date'], '%Y-%m-%d').year
|
||
else:
|
||
order_year = datetime.now().year
|
||
except:
|
||
order_year = datetime.now().year
|
||
|
||
discount = calculate_supplier_discount(client_id, order_year)
|
||
|
||
# Рассчитываем итоговую стоимость с учётом скидки
|
||
total_with_shipping = base_amount + shipping_cost
|
||
total_amount_calculated = total_with_shipping * (1 - discount / 100) if discount > 0 else total_with_shipping
|
||
|
||
# Добавляем рассчитанные поля в словарь заказа
|
||
order_dict['base_amount'] = base_amount
|
||
order_dict['shipping_cost'] = shipping_cost
|
||
order_dict['discount'] = discount
|
||
order_dict['total_amount_calculated'] = total_amount_calculated
|
||
order_dict['display_total'] = order_dict.get('total_amount', total_amount_calculated)
|
||
order_dict['order_items'] = items_list
|
||
order_dict['items_count'] = len(items_list)
|
||
|
||
orders_with_items.append(order_dict)
|
||
|
||
# Считаем общую статистику
|
||
total_stats = conn.execute('''
|
||
SELECT
|
||
COUNT(DISTINCT o.id) as total_orders,
|
||
SUM(o.total_amount) as total_amount,
|
||
SUM(CASE WHEN o.status = 'Выполнен' THEN 1 ELSE 0 END) as completed_orders,
|
||
COALESCE(SUM(oi.quantity), 0) as total_quantity
|
||
FROM Orders o
|
||
LEFT JOIN OrderItems oi ON o.id = oi.order_id
|
||
WHERE o.client_id = ?
|
||
''', (client_id,)).fetchone()
|
||
|
||
conn.close()
|
||
|
||
return render_template('order_history.html',
|
||
client=client,
|
||
orders=orders_with_items,
|
||
total_stats=total_stats)
|
||
|
||
@app.route('/update_order_status/<int:order_id>', methods=['POST'])
|
||
def update_order_status(order_id):
|
||
"""Обновление статуса заказа"""
|
||
try:
|
||
new_status = request.form['status']
|
||
|
||
conn = get_db_connection()
|
||
conn.execute('UPDATE Orders SET status = ? WHERE id = ?', (new_status, order_id))
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
flash(f'Статус заказа обновлен на "{new_status}"', 'success')
|
||
except Exception as e:
|
||
flash(f'Ошибка при обновлении статуса: {str(e)}', 'error')
|
||
|
||
return redirect(request.referrer or url_for('orders_list'))
|
||
|
||
@app.route('/add_order/<int:client_id>', methods=['GET', 'POST'])
|
||
def add_order(client_id):
|
||
if request.method == 'POST':
|
||
try:
|
||
order_date = request.form['order_date']
|
||
status = request.form.get('status', 'Новый')
|
||
|
||
conn = get_db_connection()
|
||
|
||
# Проверяем наличие таблицы OrderItems
|
||
cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OrderItems'")
|
||
has_orderitems = cursor.fetchone() is not None
|
||
|
||
if has_orderitems:
|
||
# Новая структура
|
||
cursor = conn.execute('''
|
||
INSERT INTO Orders (client_id, order_date, status, total_amount)
|
||
VALUES (?, ?, ?, 0)
|
||
''', (client_id, order_date, status))
|
||
|
||
order_id = cursor.lastrowid
|
||
|
||
# Получаем позиции из формы
|
||
product_ids = request.form.getlist('product_id[]')
|
||
quantities = request.form.getlist('quantity[]')
|
||
|
||
total = 0
|
||
|
||
for i in range(len(product_ids)):
|
||
if product_ids[i] and quantities[i]:
|
||
product_id = int(product_ids[i])
|
||
quantity = int(quantities[i])
|
||
|
||
# Получаем цену товара
|
||
product = conn.execute('SELECT min_price FROM Catalog WHERE id = ?', (product_id,)).fetchone()
|
||
|
||
if product:
|
||
price = product['min_price']
|
||
|
||
# Добавляем позицию
|
||
conn.execute('''
|
||
INSERT INTO OrderItems (order_id, product_id, quantity, price_at_moment)
|
||
VALUES (?, ?, ?, ?)
|
||
''', (order_id, product_id, quantity, price))
|
||
|
||
# Считаем сумму с транспортными расходами
|
||
total += quantity * price * 1.1
|
||
|
||
# Рассчитываем скидку
|
||
year = datetime.strptime(order_date, '%Y-%m-%d').year
|
||
discount = calculate_supplier_discount(client_id, year)
|
||
|
||
# Применяем скидку
|
||
total_with_discount = total * (1 - discount/100)
|
||
|
||
# Обновляем сумму заказа
|
||
conn.execute('UPDATE Orders SET total_amount = ? WHERE id = ?', (total_with_discount, order_id))
|
||
|
||
discount_msg = f' Применена накопительная скидка {discount}% (экономия {total * discount/100:.2f} руб.)' if discount > 0 else ''
|
||
|
||
else:
|
||
# Старая структура (для совместимости)
|
||
product_id = request.form.get('product_id')
|
||
quantity = int(request.form.get('quantity', 1))
|
||
|
||
product = conn.execute('SELECT min_price FROM Catalog WHERE id = ?', (product_id,)).fetchone()
|
||
|
||
if not product:
|
||
flash('Товар не найден', 'error')
|
||
return redirect(url_for('add_order', client_id=client_id))
|
||
|
||
base_amount = product['min_price'] * quantity
|
||
total_amount = base_amount * 1.1 # с транспортом
|
||
|
||
cursor = conn.execute('''
|
||
INSERT INTO Orders (client_id, product_id, quantity, order_date, total_amount, status)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
''', (client_id, product_id, quantity, order_date, total_amount, status))
|
||
|
||
order_id = cursor.lastrowid
|
||
discount_msg = ''
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
flash(f'Заказ успешно добавлен!{discount_msg}', 'success')
|
||
return redirect(url_for('order_history', client_id=client_id))
|
||
|
||
except Exception as e:
|
||
flash(f'Ошибка при добавлении заказа: {str(e)}', 'error')
|
||
return redirect(url_for('add_order', client_id=client_id))
|
||
|
||
# GET запрос
|
||
conn = get_db_connection()
|
||
|
||
client = conn.execute('''
|
||
SELECT c.*, ct.type_name as contractor_type_name
|
||
FROM Clients c
|
||
LEFT JOIN ContractorType ct ON c.contractor_type_id = ct.id
|
||
WHERE c.id = ?
|
||
''', (client_id,)).fetchone()
|
||
|
||
products = conn.execute('''
|
||
SELECT cat.*, c.category_name, m.material_name
|
||
FROM Catalog cat
|
||
LEFT JOIN Categories c ON cat.category_id = c.id
|
||
LEFT JOIN Materials m ON cat.material_id = m.id
|
||
ORDER BY c.category_name, cat.product_name
|
||
''').fetchall()
|
||
|
||
conn.close()
|
||
|
||
if not client:
|
||
flash('Клиент не найден', 'error')
|
||
return redirect(url_for('index'))
|
||
|
||
today = datetime.now().strftime('%Y-%m-%d')
|
||
|
||
return render_template('add_order.html', client=client, products=products, today=today)
|
||
|
||
@app.route('/orders_list')
|
||
def orders_list():
|
||
"""Список всех заказов"""
|
||
conn = get_db_connection()
|
||
|
||
# Получаем всех клиентов для выпадающего списка
|
||
clients = conn.execute('''
|
||
SELECT id, name FROM Clients ORDER BY name
|
||
''').fetchall()
|
||
|
||
# Проверяем наличие таблицы OrderItems
|
||
cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OrderItems'")
|
||
has_orderitems = cursor.fetchone() is not None
|
||
|
||
if has_orderitems:
|
||
# Новая структура
|
||
orders = conn.execute('''
|
||
SELECT
|
||
o.id,
|
||
o.client_id,
|
||
o.order_date,
|
||
o.total_amount,
|
||
o.status,
|
||
c.name as client_name,
|
||
c.inn as client_inn,
|
||
COUNT(oi.id) as items_count,
|
||
SUM(oi.quantity) as total_quantity,
|
||
GROUP_CONCAT(cat.product_name, ', ') as products_list
|
||
FROM Orders o
|
||
LEFT JOIN Clients c ON o.client_id = c.id
|
||
LEFT JOIN OrderItems oi ON o.id = oi.order_id
|
||
LEFT JOIN Catalog cat ON oi.product_id = cat.id
|
||
GROUP BY o.id
|
||
ORDER BY o.order_date DESC, o.id DESC
|
||
LIMIT 100
|
||
''').fetchall()
|
||
|
||
# Считаем общую статистику
|
||
total_stats = conn.execute('''
|
||
SELECT
|
||
COUNT(*) as total_orders,
|
||
SUM(o.total_amount) as total_amount,
|
||
SUM(CASE WHEN o.status = 'Выполнен' THEN 1 ELSE 0 END) as completed_orders
|
||
FROM Orders o
|
||
''').fetchone()
|
||
|
||
else:
|
||
# Старая структура (с product_id и quantity в Orders)
|
||
orders = conn.execute('''
|
||
SELECT
|
||
o.id,
|
||
o.client_id,
|
||
o.order_date,
|
||
o.total_amount,
|
||
o.status,
|
||
o.quantity,
|
||
c.name as client_name,
|
||
c.inn as client_inn,
|
||
cat.product_name,
|
||
cat.article,
|
||
cat2.category_name
|
||
FROM Orders o
|
||
LEFT JOIN Clients c ON o.client_id = c.id
|
||
LEFT JOIN Catalog cat ON o.product_id = cat.id
|
||
LEFT JOIN Categories cat2 ON cat.category_id = cat2.id
|
||
ORDER BY o.order_date DESC, o.id DESC
|
||
LIMIT 100
|
||
''').fetchall()
|
||
|
||
# Считаем общую статистику
|
||
total_stats = conn.execute('''
|
||
SELECT
|
||
COUNT(*) as total_orders,
|
||
SUM(quantity) as total_quantity,
|
||
SUM(total_amount) as total_amount,
|
||
SUM(CASE WHEN status = 'Выполнен' THEN 1 ELSE 0 END) as completed_orders
|
||
FROM Orders
|
||
''').fetchone()
|
||
|
||
conn.close()
|
||
|
||
return render_template('orders_list.html', orders=orders, total_stats=total_stats, clients=clients, has_orderitems=has_orderitems)
|
||
|
||
# ========== МАРШРУТ ДЛЯ КАТАЛОГА ==========
|
||
|
||
@app.route('/catalog')
|
||
def catalog():
|
||
conn = get_db_connection()
|
||
|
||
# Получаем список всех категорий для фильтра
|
||
categories = conn.execute('SELECT * FROM Categories ORDER BY category_name').fetchall()
|
||
|
||
# Получаем список всех поставщиков для фильтра
|
||
suppliers = conn.execute('SELECT id, name FROM Clients ORDER BY name').fetchall()
|
||
|
||
# Получаем параметры фильтрации из URL
|
||
search_query = request.args.get('search', '')
|
||
category_id = request.args.get('category', 'all')
|
||
supplier_id = request.args.get('supplier', 'all')
|
||
sort_by = request.args.get('sort', 'name_asc')
|
||
|
||
# Базовый SQL запрос с добавлением поставщика
|
||
sql = '''
|
||
SELECT cat.*, c.category_name, m.material_name, m.color,
|
||
cl.name as supplier_name, cl.id as supplier_id
|
||
FROM Catalog cat
|
||
LEFT JOIN Categories c ON cat.category_id = c.id
|
||
LEFT JOIN Materials m ON cat.material_id = m.id
|
||
LEFT JOIN Clients cl ON m.supplier = cl.name
|
||
WHERE 1=1
|
||
'''
|
||
params = []
|
||
|
||
# Поиск по названию или артикулу
|
||
if search_query:
|
||
sql += " AND (cat.product_name LIKE ? OR cat.article LIKE ?)"
|
||
params.extend([f'%{search_query}%', f'%{search_query}%'])
|
||
|
||
# Фильтр по категории
|
||
if category_id != 'all':
|
||
sql += " AND cat.category_id = ?"
|
||
params.append(category_id)
|
||
|
||
# Фильтр по поставщику
|
||
if supplier_id != 'all':
|
||
sql += " AND cl.id = ?"
|
||
params.append(supplier_id)
|
||
|
||
# Сортировка
|
||
if sort_by == 'name_asc':
|
||
sql += " ORDER BY cat.product_name ASC"
|
||
elif sort_by == 'name_desc':
|
||
sql += " ORDER BY cat.product_name DESC"
|
||
elif sort_by == 'price_asc':
|
||
sql += " ORDER BY cat.min_price ASC"
|
||
elif sort_by == 'price_desc':
|
||
sql += " ORDER BY cat.min_price DESC"
|
||
elif sort_by == 'stock_desc':
|
||
sql += " ORDER BY cat.quantity_in_stock DESC"
|
||
else:
|
||
sql += " ORDER BY cat.product_name ASC"
|
||
|
||
# Выполняем запрос
|
||
products = conn.execute(sql, params).fetchall()
|
||
|
||
# Получаем количество товаров в корзине для бейджа
|
||
cart_count = 0
|
||
if 'cart' in session:
|
||
cart_count = len(session['cart'])
|
||
|
||
conn.close()
|
||
|
||
return render_template('catalog.html',
|
||
products=products,
|
||
categories=categories,
|
||
suppliers=suppliers,
|
||
cart_count=cart_count)
|
||
|
||
# ========== МАРШРУТЫ ДЛЯ УПРАВЛЕНИЯ ТОВАРАМИ ==========
|
||
|
||
@app.route('/add_product', methods=['GET', 'POST'])
|
||
def add_product():
|
||
"""Добавление нового товара"""
|
||
if request.method == 'POST':
|
||
try:
|
||
product_name = request.form['product_name']
|
||
article = request.form['article']
|
||
category_id = request.form['category_id']
|
||
material_id = request.form.get('material_id') or None
|
||
description = request.form.get('description', '')
|
||
min_price = float(request.form['min_price'])
|
||
quantity_in_stock = int(request.form.get('quantity_in_stock', 0))
|
||
|
||
conn = get_db_connection()
|
||
|
||
# Проверяем уникальность артикула
|
||
existing = conn.execute('SELECT id FROM Catalog WHERE article = ?', (article,)).fetchone()
|
||
if existing:
|
||
flash('Товар с таким артикулом уже существует', 'error')
|
||
conn.close()
|
||
return redirect(url_for('add_product'))
|
||
|
||
conn.execute('''
|
||
INSERT INTO Catalog (product_name, article, category_id, material_id, description, min_price, quantity_in_stock)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
''', (product_name, article, category_id, material_id, description, min_price, quantity_in_stock))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
flash('Товар успешно добавлен!', 'success')
|
||
return redirect(url_for('catalog'))
|
||
|
||
except Exception as e:
|
||
flash(f'Ошибка: {str(e)}', 'error')
|
||
return redirect(url_for('add_product'))
|
||
|
||
# GET запрос
|
||
conn = get_db_connection()
|
||
categories = conn.execute('SELECT * FROM Categories ORDER BY category_name').fetchall()
|
||
materials = conn.execute('SELECT * FROM Materials ORDER BY material_name').fetchall()
|
||
conn.close()
|
||
|
||
return render_template('add_product.html', categories=categories, materials=materials)
|
||
|
||
@app.route('/edit_product/<int:product_id>', methods=['GET', 'POST'])
|
||
def edit_product(product_id):
|
||
"""Редактирование товара"""
|
||
conn = get_db_connection()
|
||
|
||
if request.method == 'POST':
|
||
try:
|
||
product_name = request.form['product_name']
|
||
article = request.form['article']
|
||
category_id = request.form['category_id']
|
||
material_id = request.form.get('material_id') or None
|
||
description = request.form.get('description', '')
|
||
min_price = float(request.form['min_price'])
|
||
quantity_in_stock = int(request.form.get('quantity_in_stock', 0))
|
||
|
||
# Проверяем уникальность артикула (исключая текущий товар)
|
||
existing = conn.execute(
|
||
'SELECT id FROM Catalog WHERE article = ? AND id != ?',
|
||
(article, product_id)
|
||
).fetchone()
|
||
if existing:
|
||
flash('Товар с таким артикулом уже существует', 'error')
|
||
conn.close()
|
||
return redirect(url_for('edit_product', product_id=product_id))
|
||
|
||
conn.execute('''
|
||
UPDATE Catalog
|
||
SET product_name = ?, article = ?, category_id = ?, material_id = ?,
|
||
description = ?, min_price = ?, quantity_in_stock = ?
|
||
WHERE id = ?
|
||
''', (product_name, article, category_id, material_id, description, min_price, quantity_in_stock, product_id))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
flash('Товар успешно обновлен!', 'success')
|
||
return redirect(url_for('catalog'))
|
||
|
||
except Exception as e:
|
||
flash(f'Ошибка: {str(e)}', 'error')
|
||
return redirect(url_for('edit_product', product_id=product_id))
|
||
|
||
# GET запрос
|
||
product = conn.execute('''
|
||
SELECT cat.*, c.category_name, m.material_name
|
||
FROM Catalog cat
|
||
LEFT JOIN Categories c ON cat.category_id = c.id
|
||
LEFT JOIN Materials m ON cat.material_id = m.id
|
||
WHERE cat.id = ?
|
||
''', (product_id,)).fetchone()
|
||
|
||
categories = conn.execute('SELECT * FROM Categories ORDER BY category_name').fetchall()
|
||
materials = conn.execute('SELECT * FROM Materials ORDER BY material_name').fetchall()
|
||
conn.close()
|
||
|
||
if not product:
|
||
flash('Товар не найден', 'error')
|
||
return redirect(url_for('catalog'))
|
||
|
||
return render_template('edit_product.html', product=product, categories=categories, materials=materials)
|
||
|
||
@app.route('/delete_product/<int:product_id>', methods=['POST'])
|
||
def delete_product(product_id):
|
||
"""Удаление товара"""
|
||
try:
|
||
conn = get_db_connection()
|
||
|
||
# Проверяем наличие таблицы OrderItems
|
||
cursor = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OrderItems'")
|
||
has_orderitems = cursor.fetchone() is not None
|
||
|
||
if has_orderitems:
|
||
# Новая структура
|
||
items = conn.execute('SELECT COUNT(*) as count FROM OrderItems WHERE product_id = ?', (product_id,)).fetchone()
|
||
if items['count'] > 0:
|
||
flash('Нельзя удалить товар, который есть в заказах', 'error')
|
||
conn.close()
|
||
return redirect(url_for('catalog'))
|
||
else:
|
||
# Старая структура
|
||
orders = conn.execute('SELECT COUNT(*) as count FROM Orders WHERE product_id = ?', (product_id,)).fetchone()
|
||
if orders['count'] > 0:
|
||
flash('Нельзя удалить товар, который есть в заказах', 'error')
|
||
conn.close()
|
||
return redirect(url_for('catalog'))
|
||
|
||
conn.execute('DELETE FROM Catalog WHERE id = ?', (product_id,))
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
flash('Товар успешно удален', 'success')
|
||
except Exception as e:
|
||
flash(f'Ошибка при удалении: {str(e)}', 'error')
|
||
|
||
return redirect(url_for('catalog'))
|
||
|
||
# ========== МАРШРУТЫ ДЛЯ КОРЗИНЫ ==========
|
||
|
||
@app.route('/cart')
|
||
def view_cart():
|
||
"""Просмотр корзины"""
|
||
cart = session.get('cart', {})
|
||
cart_items = []
|
||
total_amount = 0
|
||
|
||
conn = get_db_connection()
|
||
|
||
for key, item_data in cart.items():
|
||
product_id = item_data['product_id']
|
||
quantity = item_data['quantity']
|
||
supplier_id = item_data.get('supplier_id')
|
||
|
||
product = conn.execute('''
|
||
SELECT cat.*, c.category_name, m.material_name, cat.quantity_in_stock
|
||
FROM Catalog cat
|
||
LEFT JOIN Categories c ON cat.category_id = c.id
|
||
LEFT JOIN Materials m ON cat.material_id = m.id
|
||
WHERE cat.id = ?
|
||
''', (product_id,)).fetchone()
|
||
|
||
if product:
|
||
# Получаем информацию о поставщике
|
||
supplier = None
|
||
if supplier_id:
|
||
supplier = conn.execute('SELECT name FROM Clients WHERE id = ?', (supplier_id,)).fetchone()
|
||
|
||
cart_items.append({
|
||
'key': key,
|
||
'product_id': product_id,
|
||
'supplier_id': supplier_id,
|
||
'supplier_name': supplier['name'] if supplier else 'Не указан',
|
||
'product_name': product['product_name'],
|
||
'article': product['article'],
|
||
'category_name': product['category_name'],
|
||
'material_name': product['material_name'],
|
||
'price': product['min_price'],
|
||
'quantity': quantity,
|
||
'in_stock': product['quantity_in_stock'] or 0
|
||
})
|
||
total_amount += product['min_price'] * quantity
|
||
|
||
conn.close()
|
||
|
||
return render_template('cart_new.html', cart_items=cart_items, total_amount=total_amount)
|
||
|
||
@app.route('/add_to_cart/<int:product_id>', methods=['POST'])
|
||
def add_to_cart(product_id):
|
||
"""Добавление товара в корзину с выбором поставщика"""
|
||
quantity = int(request.form.get('quantity', 1))
|
||
supplier_id = request.form.get('supplier_id')
|
||
|
||
if not supplier_id:
|
||
flash('Пожалуйста, выберите поставщика', 'error')
|
||
return redirect(url_for('catalog'))
|
||
|
||
# Получаем информацию о товаре для проверки наличия
|
||
conn = get_db_connection()
|
||
product = conn.execute('SELECT quantity_in_stock FROM Catalog WHERE id = ?', (product_id,)).fetchone()
|
||
conn.close()
|
||
|
||
if not product or product['quantity_in_stock'] < quantity:
|
||
flash('Недостаточно товара на складе', 'error')
|
||
return redirect(url_for('catalog'))
|
||
|
||
cart = session.get('cart', {})
|
||
|
||
# Создаем уникальный ключ для комбинации товар + поставщик
|
||
key = f"{product_id}_{supplier_id}"
|
||
|
||
if key in cart:
|
||
cart[key]['quantity'] += quantity
|
||
else:
|
||
cart[key] = {
|
||
'product_id': product_id,
|
||
'supplier_id': int(supplier_id),
|
||
'quantity': quantity
|
||
}
|
||
|
||
session['cart'] = cart
|
||
flash('Товар добавлен в корзину!', 'success')
|
||
|
||
return redirect(url_for('catalog'))
|
||
|
||
@app.route('/update_cart/<path:item_key>', methods=['POST'])
|
||
def update_cart(item_key):
|
||
"""Обновление количества товара в корзине"""
|
||
quantity = int(request.form.get('quantity', 1))
|
||
|
||
cart = session.get('cart', {})
|
||
|
||
if item_key in cart:
|
||
if quantity > 0:
|
||
cart[item_key]['quantity'] = quantity
|
||
else:
|
||
cart.pop(item_key, None)
|
||
|
||
session['cart'] = cart
|
||
|
||
return redirect(url_for('view_cart'))
|
||
|
||
@app.route('/remove_from_cart/<path:item_key>', methods=['POST'])
|
||
def remove_from_cart(item_key):
|
||
"""Удаление товара из корзины"""
|
||
cart = session.get('cart', {})
|
||
cart.pop(item_key, None)
|
||
session['cart'] = cart
|
||
|
||
return redirect(url_for('view_cart'))
|
||
|
||
@app.route('/checkout', methods=['POST'])
|
||
def checkout():
|
||
"""Оформление заказа из корзины"""
|
||
cart = session.get('cart', {})
|
||
|
||
if not cart:
|
||
flash('Корзина пуста', 'error')
|
||
return redirect(url_for('catalog'))
|
||
|
||
# Группируем товары по поставщикам для создания отдельных заказов
|
||
orders_by_supplier = {}
|
||
|
||
conn = get_db_connection()
|
||
|
||
for key, item_data in cart.items():
|
||
supplier_id = item_data['supplier_id']
|
||
product_id = item_data['product_id']
|
||
quantity = item_data['quantity']
|
||
|
||
if supplier_id not in orders_by_supplier:
|
||
orders_by_supplier[supplier_id] = []
|
||
|
||
# Получаем цену товара
|
||
product = conn.execute('SELECT min_price FROM Catalog WHERE id = ?', (product_id,)).fetchone()
|
||
if product:
|
||
orders_by_supplier[supplier_id].append({
|
||
'product_id': product_id,
|
||
'quantity': quantity,
|
||
'price': product['min_price']
|
||
})
|
||
|
||
# Создаем заказы для каждого поставщика
|
||
orders_created = 0
|
||
today = datetime.now().strftime('%Y-%m-%d')
|
||
|
||
for supplier_id, items in orders_by_supplier.items():
|
||
# Считаем общую сумму с транспортом
|
||
total = 0
|
||
for item in items:
|
||
total += item['quantity'] * item['price'] * 1.1
|
||
|
||
# Рассчитываем скидку
|
||
discount = calculate_supplier_discount(supplier_id, datetime.now().year)
|
||
total_with_discount = total * (1 - discount/100)
|
||
|
||
# Создаем заказ
|
||
cursor = conn.execute('''
|
||
INSERT INTO Orders (client_id, order_date, total_amount, status)
|
||
VALUES (?, ?, ?, ?)
|
||
''', (supplier_id, today, total_with_discount, 'Новый'))
|
||
|
||
order_id = cursor.lastrowid
|
||
|
||
# Добавляем позиции
|
||
for item in items:
|
||
conn.execute('''
|
||
INSERT INTO OrderItems (order_id, product_id, quantity, price_at_moment)
|
||
VALUES (?, ?, ?, ?)
|
||
''', (order_id, item['product_id'], item['quantity'], item['price']))
|
||
|
||
orders_created += 1
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
# Очищаем корзину
|
||
session.pop('cart', None)
|
||
|
||
flash(f'Заказ успешно оформлен! Создано {orders_created} заказов.', 'success')
|
||
return redirect(url_for('orders_list'))
|
||
|
||
# ========== МАРШРУТ ДЛЯ МАТЕРИАЛОВ ==========
|
||
|
||
@app.route('/materials')
|
||
def materials():
|
||
"""Список всех материалов"""
|
||
conn = get_db_connection()
|
||
|
||
materials = conn.execute('''
|
||
SELECT * FROM Materials
|
||
ORDER BY material_name
|
||
''').fetchall()
|
||
|
||
conn.close()
|
||
|
||
return render_template('materials.html', materials=materials)
|
||
|
||
# ========== СЛУЖЕБНЫЕ МАРШРУТЫ ==========
|
||
|
||
@app.route('/favicon.ico')
|
||
def favicon():
|
||
return send_from_directory(os.path.join(app.root_path, 'static'),
|
||
'favicon.ico', mimetype='image/vnd.microsoft.icon')
|
||
|
||
@app.route('/static/<path:filename>')
|
||
def static_files(filename):
|
||
return send_from_directory(app.root_path + '/static', filename)
|
||
|
||
if __name__ == '__main__':
|
||
app.run(debug=True) |