221 lines
8.2 KiB
Python
221 lines
8.2 KiB
Python
import sqlite3
|
||
import time
|
||
from contextlib import contextmanager
|
||
from datetime import datetime
|
||
|
||
DATABASE = 'partners.db'
|
||
|
||
|
||
def get_db_connection():
|
||
"""Создает подключение к базе данных с правильными настройками"""
|
||
conn = sqlite3.connect(DATABASE, timeout=10)
|
||
conn.row_factory = sqlite3.Row
|
||
conn.execute("PRAGMA foreign_keys = ON")
|
||
conn.execute("PRAGMA journal_mode = WAL")
|
||
return conn
|
||
|
||
|
||
@contextmanager
|
||
def get_db():
|
||
"""Контекстный менеджер для безопасной работы с БД"""
|
||
conn = None
|
||
try:
|
||
conn = get_db_connection()
|
||
yield conn
|
||
if conn:
|
||
conn.commit()
|
||
except Exception as e:
|
||
if conn:
|
||
conn.rollback()
|
||
raise e
|
||
finally:
|
||
if conn:
|
||
conn.close()
|
||
|
||
|
||
def create_tables():
|
||
"""Создает все таблицы в базе данных"""
|
||
with get_db() as conn:
|
||
cursor = conn.cursor()
|
||
|
||
cursor.executescript('''
|
||
DROP TABLE IF EXISTS order_items;
|
||
DROP TABLE IF EXISTS orders;
|
||
DROP TABLE IF EXISTS products;
|
||
DROP TABLE IF EXISTS partners;
|
||
DROP TABLE IF EXISTS product_types;
|
||
|
||
CREATE TABLE product_types (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
type_name VARCHAR(100) NOT NULL UNIQUE,
|
||
description TEXT
|
||
);
|
||
|
||
CREATE TABLE partners (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name VARCHAR(255) NOT NULL,
|
||
director VARCHAR(255),
|
||
email VARCHAR(255) NOT NULL,
|
||
phone VARCHAR(50),
|
||
legal_address TEXT,
|
||
inn VARCHAR(20) UNIQUE
|
||
);
|
||
|
||
CREATE TABLE products (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
product_type_id INTEGER NOT NULL,
|
||
product_name VARCHAR(255) NOT NULL,
|
||
article VARCHAR(100) UNIQUE NOT NULL,
|
||
min_partner_price DECIMAL(15,2) NOT NULL CHECK (min_partner_price > 0),
|
||
FOREIGN KEY (product_type_id) REFERENCES product_types(id) ON DELETE RESTRICT
|
||
);
|
||
|
||
CREATE TABLE orders (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
partner_id INTEGER NOT NULL,
|
||
order_date DATE NOT NULL,
|
||
notes TEXT,
|
||
discount_percent INTEGER DEFAULT 0,
|
||
FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE RESTRICT
|
||
);
|
||
|
||
CREATE TABLE order_items (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id INTEGER NOT NULL,
|
||
product_id INTEGER NOT NULL,
|
||
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
||
price_at_order DECIMAL(15,2) NOT NULL CHECK (price_at_order > 0),
|
||
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
|
||
);
|
||
''')
|
||
|
||
print("✅ Таблицы успешно созданы")
|
||
|
||
|
||
def calculate_discount(total_amount):
|
||
"""Рассчитывает скидку на основе общей суммы закупок за год"""
|
||
if total_amount >= 300000:
|
||
return 50
|
||
elif total_amount >= 200000:
|
||
return 30
|
||
elif total_amount >= 150000:
|
||
return 15
|
||
else:
|
||
return 0
|
||
|
||
|
||
def get_discount_description(discount_percent):
|
||
"""Возвращает описание скидки"""
|
||
if discount_percent == 50:
|
||
return '50% (300 000+ руб.)'
|
||
elif discount_percent == 30:
|
||
return '30% (200 000 - 299 999 руб.)'
|
||
elif discount_percent == 15:
|
||
return '15% (150 000 - 199 999 руб.)'
|
||
else:
|
||
return '0% (до 150 000 руб.)'
|
||
|
||
|
||
def calculate_order_total(order_id):
|
||
"""Рассчитывает общую стоимость заказа с учетом скидки и добавочной стоимости 10%"""
|
||
try:
|
||
with get_db() as conn:
|
||
order_info = conn.execute('''
|
||
SELECT o.*, p.id as partner_id, p.name as partner_name
|
||
FROM orders o
|
||
JOIN partners p ON o.partner_id = p.id
|
||
WHERE o.id = ?
|
||
''', (order_id,)).fetchone()
|
||
|
||
if not order_info:
|
||
return None
|
||
|
||
items = conn.execute('''
|
||
SELECT oi.*, p.product_name
|
||
FROM order_items oi
|
||
JOIN products p ON oi.product_id = p.id
|
||
WHERE oi.order_id = ?
|
||
''', (order_id,)).fetchall()
|
||
|
||
subtotal_without_markup = 0
|
||
items_list = []
|
||
|
||
for item in items:
|
||
# Базовая стоимость = количество × цена
|
||
base_item_subtotal = item['quantity'] * item['price_at_order']
|
||
# Добавляем 10% добавочной стоимости
|
||
item_subtotal = base_item_subtotal * 1.1
|
||
subtotal_without_markup += base_item_subtotal
|
||
items_list.append({
|
||
'id': item['id'],
|
||
'product_name': item['product_name'],
|
||
'quantity': item['quantity'],
|
||
'price': item['price_at_order'],
|
||
'base_subtotal': round(base_item_subtotal, 2),
|
||
'subtotal_with_markup': round(item_subtotal, 2)
|
||
})
|
||
|
||
# Общая стоимость с добавочной стоимостью (без скидки)
|
||
subtotal_with_markup = subtotal_without_markup * 1.1
|
||
|
||
discount_percent = order_info['discount_percent'] if order_info['discount_percent'] else 0
|
||
discount_desc = get_discount_description(discount_percent)
|
||
|
||
discount_amount = subtotal_with_markup * discount_percent / 100
|
||
total = subtotal_with_markup - discount_amount
|
||
|
||
return {
|
||
'order_id': order_id,
|
||
'partner_id': order_info['partner_id'],
|
||
'partner_name': order_info['partner_name'],
|
||
'order_date': order_info['order_date'],
|
||
'notes': order_info['notes'] if order_info['notes'] else '',
|
||
'items': items_list,
|
||
'items_count': len(items_list),
|
||
'subtotal_without_markup': round(subtotal_without_markup, 2),
|
||
'subtotal_with_markup': round(subtotal_with_markup, 2),
|
||
'discount_percent': discount_percent,
|
||
'discount_desc': discount_desc,
|
||
'discount_amount': round(discount_amount, 2),
|
||
'total': round(total, 2)
|
||
}
|
||
except Exception as e:
|
||
print(f"Ошибка при расчете стоимости заказа {order_id}: {e}")
|
||
return None
|
||
|
||
|
||
def calculate_discount_for_new_order(partner_id, order_date, current_order_amount_without_markup,
|
||
exclude_order_id=None):
|
||
"""Рассчитывает скидку для нового заказа на основе предыдущих заказов в этом году"""
|
||
try:
|
||
with get_db() as conn:
|
||
year = order_date[:4] if order_date else str(datetime.now().year)
|
||
|
||
query = '''
|
||
SELECT id
|
||
FROM orders
|
||
WHERE partner_id = ? AND strftime('%Y', order_date) = ?
|
||
'''
|
||
params = [partner_id, year]
|
||
|
||
if exclude_order_id:
|
||
query += ' AND id != ?'
|
||
params.append(exclude_order_id)
|
||
|
||
previous_orders = conn.execute(query, params).fetchall()
|
||
|
||
total_previous_without_markup = 0
|
||
for prev_order in previous_orders:
|
||
order_total = conn.execute('''
|
||
SELECT COALESCE(SUM(quantity * price_at_order), 0) as total
|
||
FROM order_items WHERE order_id = ?
|
||
''', (prev_order['id'],)).fetchone()['total']
|
||
total_previous_without_markup += order_total
|
||
|
||
total_with_current_without_markup = total_previous_without_markup + current_order_amount_without_markup
|
||
|
||
return calculate_discount(total_with_current_without_markup)
|
||
except Exception as e:
|
||
print(f"Ошибка при расчете скидки: {e}")
|
||
return 0 |