588 lines
24 KiB
Python
588 lines
24 KiB
Python
import openpyxl # Добавь это
|
||
openpyxl.__version__ = "3.1.5" # И это
|
||
import sqlite3
|
||
import pandas as pd
|
||
import os
|
||
import sys
|
||
from datetime import datetime
|
||
|
||
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
|
||
RESOURCE_DIR = os.path.join(BASE_DIR, "Resource")
|
||
# ========== ИЗМЕНЕНО: partners.db -> materials.db ==========
|
||
DB_PATH = os.path.join(BASE_DIR, "materials.db")
|
||
|
||
|
||
def create_tables(db_connection):
|
||
"""Создает все необходимые таблицы в базе данных"""
|
||
cursor = db_connection.cursor()
|
||
|
||
cursor.executescript('''
|
||
-- Типы контрагентов
|
||
CREATE TABLE IF NOT EXISTS ContractorType (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
type_name VARCHAR(100) NOT NULL UNIQUE
|
||
);
|
||
|
||
-- Контрагенты/Клиенты
|
||
CREATE TABLE IF NOT EXISTS Clients (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
contractor_type_id INTEGER REFERENCES ContractorType(id),
|
||
name VARCHAR(255) NOT NULL,
|
||
director VARCHAR(255),
|
||
email VARCHAR(255),
|
||
phone VARCHAR(50),
|
||
address TEXT,
|
||
inn VARCHAR(20) UNIQUE,
|
||
rating DECIMAL(3,2) DEFAULT 0.0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Категории продукции
|
||
CREATE TABLE IF NOT EXISTS Categories (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
category_name VARCHAR(100) NOT NULL UNIQUE,
|
||
coefficient DECIMAL(5,2) NOT NULL DEFAULT 1.0
|
||
);
|
||
|
||
-- Таблица материалов
|
||
CREATE TABLE IF NOT EXISTS Materials (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
material_name VARCHAR(100) NOT NULL UNIQUE,
|
||
description TEXT,
|
||
category VARCHAR(50),
|
||
color VARCHAR(50),
|
||
density DECIMAL(10,2),
|
||
thickness DECIMAL(10,2),
|
||
supplier VARCHAR(255),
|
||
country VARCHAR(100),
|
||
wear_class VARCHAR(20),
|
||
price_per_unit DECIMAL(15,2),
|
||
unit VARCHAR(20) DEFAULT 'шт',
|
||
in_stock INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Каталог продукции
|
||
CREATE TABLE IF NOT EXISTS Catalog (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
category_id INTEGER REFERENCES Categories(id),
|
||
material_id INTEGER REFERENCES Materials(id),
|
||
product_name VARCHAR(255) NOT NULL,
|
||
article VARCHAR(100) UNIQUE NOT NULL,
|
||
description TEXT,
|
||
min_price DECIMAL(15,2) NOT NULL,
|
||
quantity_in_stock INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Таблица корзин
|
||
CREATE TABLE IF NOT EXISTS Cart (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
client_id INTEGER REFERENCES Clients(id),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
status VARCHAR(50) DEFAULT 'активна'
|
||
);
|
||
|
||
-- Таблица товаров в корзине
|
||
CREATE TABLE IF NOT EXISTS CartItems (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
cart_id INTEGER REFERENCES Cart(id),
|
||
product_id INTEGER REFERENCES Catalog(id),
|
||
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
||
price_at_moment DECIMAL(15,2) NOT NULL,
|
||
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ========== НОВАЯ СТРУКТУРА ЗАКАЗОВ ==========
|
||
|
||
-- Заказы (без product_id и quantity)
|
||
CREATE TABLE IF NOT EXISTS Orders (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
client_id INTEGER NOT NULL REFERENCES Clients(id),
|
||
order_date DATE NOT NULL,
|
||
total_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
status VARCHAR(50) DEFAULT 'Новый',
|
||
discount_percent INTEGER DEFAULT 0,
|
||
notes TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Позиции заявок
|
||
CREATE TABLE IF NOT EXISTS OrderItems (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id INTEGER NOT NULL REFERENCES Orders(id) ON DELETE CASCADE,
|
||
product_id INTEGER NOT NULL REFERENCES Catalog(id),
|
||
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
||
price_at_moment DECIMAL(15,2) NOT NULL,
|
||
transport_cost DECIMAL(15,2) DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Индексы для оптимизации
|
||
CREATE INDEX IF NOT EXISTS idx_client_type ON Clients(contractor_type_id);
|
||
CREATE INDEX IF NOT EXISTS idx_client_inn ON Clients(inn);
|
||
CREATE INDEX IF NOT EXISTS idx_catalog_category ON Catalog(category_id);
|
||
CREATE INDEX IF NOT EXISTS idx_catalog_material ON Catalog(material_id);
|
||
CREATE INDEX IF NOT EXISTS idx_catalog_article ON Catalog(article);
|
||
CREATE INDEX IF NOT EXISTS idx_material_name ON Materials(material_name);
|
||
CREATE INDEX IF NOT EXISTS idx_cart_client ON Cart(client_id);
|
||
CREATE INDEX IF NOT EXISTS idx_cart_status ON Cart(status);
|
||
CREATE INDEX IF NOT EXISTS idx_cart_items_cart ON CartItems(cart_id);
|
||
CREATE INDEX IF NOT EXISTS idx_order_client ON Orders(client_id);
|
||
CREATE INDEX IF NOT EXISTS idx_order_date ON Orders(order_date);
|
||
CREATE INDEX IF NOT EXISTS idx_order_status ON Orders(status);
|
||
CREATE INDEX IF NOT EXISTS idx_orderitems_order ON OrderItems(order_id);
|
||
CREATE INDEX IF NOT EXISTS idx_orderitems_product ON OrderItems(product_id);
|
||
''')
|
||
|
||
db_connection.commit()
|
||
print("✅ Таблицы успешно созданы (новая структура с OrderItems)")
|
||
|
||
|
||
def calculate_discount(total_amount):
|
||
"""Рассчитывает скидку на основе общей суммы заказов"""
|
||
if total_amount >= 500000:
|
||
return 20
|
||
elif total_amount >= 300000:
|
||
return 15
|
||
elif total_amount >= 150000:
|
||
return 10
|
||
return 0
|
||
|
||
|
||
def get_clients(db_path=DB_PATH, sort_by='rating', ascending=False):
|
||
"""Получает данные о клиентах с возможностью сортировки"""
|
||
try:
|
||
conn = sqlite3.connect(db_path)
|
||
query = """
|
||
SELECT
|
||
ct.type_name AS "Тип клиента",
|
||
c.name AS "Наименование клиента",
|
||
c.director AS "Директор",
|
||
c.email AS "Электронная почта",
|
||
c.phone AS "Телефон",
|
||
c.address AS "Адрес",
|
||
c.inn AS "ИНН",
|
||
c.rating AS "Рейтинг",
|
||
c.created_at AS "Дата создания",
|
||
COALESCE(SUM(o.total_amount), 0) as total_amount,
|
||
COUNT(DISTINCT o.id) as orders_count
|
||
FROM Clients c
|
||
JOIN ContractorType ct ON c.contractor_type_id = ct.id
|
||
LEFT JOIN Orders o ON c.id = o.client_id
|
||
GROUP BY c.id
|
||
"""
|
||
|
||
if sort_by == 'discount':
|
||
query += " ORDER BY total_amount"
|
||
elif sort_by == 'rating':
|
||
query += " ORDER BY c.rating"
|
||
elif sort_by == 'name':
|
||
query += " ORDER BY c.name"
|
||
elif sort_by == 'type':
|
||
query += " ORDER BY ct.type_name, c.name"
|
||
else:
|
||
query += " ORDER BY c.rating"
|
||
|
||
query += " DESC" if not ascending else " ASC"
|
||
|
||
df = pd.read_sql_query(query, conn)
|
||
conn.close()
|
||
|
||
if not df.empty:
|
||
df['Скидка_процент'] = df['total_amount'].apply(calculate_discount)
|
||
df['Скидка_текст'] = df['Скидка_процент'].apply(lambda x: f'{x}%')
|
||
df['Общая_сумма'] = df['total_amount'].apply(lambda x: f"{x:,.2f} руб.")
|
||
|
||
return df
|
||
|
||
except Exception as e:
|
||
print(f"❌ Ошибка: {e}")
|
||
return None
|
||
|
||
|
||
def get_orders_history(db_path=DB_PATH, limit=50, sort_by='date_desc'):
|
||
"""Получает историю заказов"""
|
||
try:
|
||
conn = sqlite3.connect(db_path)
|
||
query = """
|
||
SELECT
|
||
o.order_date AS "Дата заказа",
|
||
c.name AS "Клиент",
|
||
COUNT(oi.id) AS "Кол-во позиций",
|
||
o.total_amount AS "Сумма заказа",
|
||
o.status AS "Статус",
|
||
o.discount_percent AS "Скидка",
|
||
GROUP_CONCAT(cat.product_name, ', ') AS "Товары"
|
||
FROM Orders o
|
||
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
|
||
"""
|
||
|
||
if sort_by == 'date_desc':
|
||
query += " ORDER BY o.order_date DESC, o.created_at DESC"
|
||
elif sort_by == 'date_asc':
|
||
query += " ORDER BY o.order_date ASC, o.created_at ASC"
|
||
elif sort_by == 'amount_desc':
|
||
query += " ORDER BY o.total_amount DESC"
|
||
else:
|
||
query += " ORDER BY o.order_date DESC, o.created_at DESC"
|
||
|
||
query += " LIMIT ?"
|
||
|
||
df = pd.read_sql_query(query, conn, params=(limit,))
|
||
conn.close()
|
||
return df
|
||
except Exception as e:
|
||
print(f"❌ Ошибка при получении истории заказов: {e}")
|
||
return None
|
||
|
||
|
||
def print_client_cards(clients_df):
|
||
"""Выводит карточки клиентов"""
|
||
if clients_df is None or clients_df.empty:
|
||
print("📭 Нет данных о клиентах")
|
||
return
|
||
|
||
for index, row in clients_df.iterrows():
|
||
print("╔" + "═" * 79 + "╗")
|
||
print(f"║ {'КАРТОЧКА КЛИЕНТА':^77} ║")
|
||
print("╠" + "═" * 79 + "╣")
|
||
|
||
print(f"║ Тип: {row['Тип клиента']:<70} ║")
|
||
print(f"║ Наименование: {row['Наименование клиента']:<63} ║")
|
||
print(f"║ Директор: {row['Директор']:<68} ║")
|
||
print(f"║ Рейтинг: {row['Рейтинг']:<69} ║")
|
||
print(f"║ Заказов: {row['orders_count']:<69} ║")
|
||
print(f"║ Сумма заказов: {row['total_amount']:,.2f} руб. ║")
|
||
print(f"║ Скидка: {row['Скидка_текст']:<70} ║")
|
||
print(f"║ {'':<77} ║")
|
||
print(f"║ Email: {row['Электронная почта']:<70} ║")
|
||
print(f"║ Телефон: {row['Телефон']:<69} ║")
|
||
print(f"║ ИНН: {row['ИНН']:<73} ║")
|
||
|
||
address = row['Адрес'] if row['Адрес'] else 'Не указан'
|
||
if len(address) > 70:
|
||
address = address[:67] + "..."
|
||
print(f"║ Адрес: {address:<70} ║")
|
||
print("╚" + "═" * 79 + "╝")
|
||
print()
|
||
|
||
|
||
def print_orders_cards(orders_df):
|
||
"""Выводит карточки заказов"""
|
||
if orders_df is None or orders_df.empty:
|
||
print("📭 Нет данных о заказах")
|
||
return
|
||
|
||
print("╔" + "═" * 120 + "╗")
|
||
print(f"║ {'ИСТОРИЯ ЗАКАЗОВ':^118} ║")
|
||
print("╠" + "═" * 120 + "╣")
|
||
|
||
for index, row in orders_df.iterrows():
|
||
date = str(row['Дата заказа'])[:10]
|
||
client = row['Клиент'][:20] if len(row['Клиент']) > 20 else row['Клиент']
|
||
items = row['Кол-во позиций']
|
||
amount = f"{row['Сумма заказа']:,.2f}"
|
||
status = row['Статус']
|
||
discount = f"{row['Скидка']}%"
|
||
products = row['Товары'][:50] if row['Товары'] else '-'
|
||
|
||
print(f"║ {date} | {client:<20} | Позиций: {items:2} | Скидка: {discount:3} | {amount:>15} руб. | {status} ║")
|
||
print(f"║ Товары: {products:<106} ║")
|
||
print("╠" + "═" * 120 + "╣")
|
||
|
||
total_amount = orders_df['Сумма заказа'].sum()
|
||
print(f"║ {'ИТОГО:':<20} {len(orders_df)} заказов, общая сумма: {total_amount:,.2f} руб. {'':>30} ║")
|
||
print("╚" + "═" * 120 + "╝")
|
||
|
||
|
||
def simple_choice(options):
|
||
"""Упрощенная функция выбора"""
|
||
print("\n" + "=" * 60)
|
||
print("ВЫБЕРИТЕ ОПЦИЮ:")
|
||
print("=" * 60)
|
||
for i, option in enumerate(options, 1):
|
||
print(f"{i}. {option}")
|
||
|
||
while True:
|
||
try:
|
||
choice = input(f"\nВведите номер (1-{len(options)}): ").strip()
|
||
if choice.isdigit():
|
||
choice_num = int(choice)
|
||
if 1 <= choice_num <= len(options):
|
||
return options[choice_num - 1]
|
||
print(f"❌ Пожалуйста, введите число от 1 до {len(options)}")
|
||
except (ValueError, EOFError):
|
||
print("❌ Неверный ввод. Попробуйте еще раз.")
|
||
except KeyboardInterrupt:
|
||
print("\n👋 Программа прервана пользователем.")
|
||
sys.exit(0)
|
||
|
||
|
||
def check_database():
|
||
"""Проверяет загруженные данные в базе"""
|
||
conn = sqlite3.connect(DB_PATH)
|
||
try:
|
||
cursor = conn.cursor()
|
||
print("\n🔍 Проверка загруженных данных:")
|
||
tables = ['ContractorType', 'Clients', 'Categories', 'Materials', 'Catalog', 'Cart', 'CartItems', 'Orders', 'OrderItems']
|
||
for table in tables:
|
||
cursor.execute(f"SELECT COUNT(*) FROM {table}")
|
||
count = cursor.fetchone()[0]
|
||
print(f" {table}: {count} записей")
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def load_data_from_files():
|
||
"""Загружает данные из Excel файлов в базу данных"""
|
||
conn = sqlite3.connect(DB_PATH)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
print("\n📁 Введите имена файлов:")
|
||
clients_file = input("Файл с клиентами (Enter = Clients_import.xlsx): ").strip() or "Clients_import.xlsx"
|
||
categories_file = input("Файл с категориями (Enter = Categories_import.xlsx): ").strip() or "Categories_import.xlsx"
|
||
materials_file = input("Файл с материалами (Enter = Materials_import.xlsx): ").strip() or "Materials_import.xlsx"
|
||
catalog_file = input("Файл с каталогом (Enter = Products_import.xlsx): ").strip() or "Products_import.xlsx"
|
||
orders_file = input("Файл с заказами (Enter = Orders_import.xlsx): ").strip() or "Orders_import.xlsx"
|
||
|
||
# Формируем пути
|
||
clients_file = os.path.join(RESOURCE_DIR, os.path.basename(clients_file))
|
||
categories_file = os.path.join(RESOURCE_DIR, os.path.basename(categories_file))
|
||
materials_file = os.path.join(RESOURCE_DIR, os.path.basename(materials_file))
|
||
catalog_file = os.path.join(RESOURCE_DIR, os.path.basename(catalog_file))
|
||
orders_file = os.path.join(RESOURCE_DIR, os.path.basename(orders_file))
|
||
|
||
# 1. Загрузка типов контрагентов
|
||
print("\n1. 📊 Загрузка типов контрагентов...")
|
||
clients_df = pd.read_excel(clients_file)
|
||
|
||
type_column = 'Тип партнера'
|
||
if type_column in clients_df.columns:
|
||
contractor_types = clients_df[type_column].unique()
|
||
for contractor_type in contractor_types:
|
||
cursor.execute("INSERT OR IGNORE INTO ContractorType (type_name) VALUES (?)", (contractor_type,))
|
||
print(f" Загружено {len(contractor_types)} типов")
|
||
|
||
# 2. Загрузка клиентов
|
||
print("2. 👥 Загрузка клиентов...")
|
||
loaded_clients = 0
|
||
for _, row in clients_df.iterrows():
|
||
type_value = row.get('Тип партнера', 'Клиент')
|
||
cursor.execute("SELECT id FROM ContractorType WHERE type_name = ?", (type_value,))
|
||
result = cursor.fetchone()
|
||
contractor_type_id = result[0] if result else 1
|
||
|
||
cursor.execute("""INSERT OR IGNORE INTO Clients
|
||
(contractor_type_id, name, director, email, phone, address, inn, rating)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
|
||
(contractor_type_id,
|
||
row.get('Наименование партнера', ''),
|
||
row.get('Директор', ''),
|
||
row.get('Электронная почта партнера', ''),
|
||
row.get('Телефон партнера', ''),
|
||
row.get('Юридический адрес партнера', ''),
|
||
str(row.get('ИНН', '')),
|
||
float(row.get('Рейтинг', 0)))
|
||
)
|
||
loaded_clients += 1
|
||
print(f" Загружено {loaded_clients} клиентов")
|
||
|
||
# 3. Загрузка категорий
|
||
print("3. 📦 Загрузка категорий...")
|
||
categories_df = pd.read_excel(categories_file)
|
||
loaded_categories = 0
|
||
for _, row in categories_df.iterrows():
|
||
cursor.execute("INSERT OR IGNORE INTO Categories (category_name, coefficient) VALUES (?, ?)",
|
||
(row.get('Тип продукции', ''),
|
||
float(row.get('Коэффициент типа продукции', 1.0)))
|
||
)
|
||
loaded_categories += 1
|
||
print(f" Загружено {loaded_categories} категорий")
|
||
|
||
# 4. Загрузка материалов
|
||
print("4. 📦 Загрузка материалов...")
|
||
materials_df = pd.read_excel(materials_file)
|
||
loaded_materials = 0
|
||
for _, row in materials_df.iterrows():
|
||
cursor.execute("""
|
||
INSERT OR IGNORE INTO Materials
|
||
(material_name, description, category, color, density, thickness,
|
||
supplier, country, wear_class, price_per_unit, unit, in_stock)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
""", (
|
||
row.get('Наименование материала', ''),
|
||
row.get('Описание', ''),
|
||
row.get('Категория', ''),
|
||
row.get('Цвет', ''),
|
||
row.get('Плотность', None),
|
||
row.get('Толщина', None),
|
||
row.get('Поставщик', ''),
|
||
row.get('Страна', ''),
|
||
row.get('Класс прочности', ''),
|
||
float(row.get('Цена за ед.', 0)),
|
||
row.get('Единица', 'шт'),
|
||
int(row.get('В наличии', 0))
|
||
))
|
||
loaded_materials += 1
|
||
print(f" ✅ Загружено {loaded_materials} материалов")
|
||
|
||
# 5. Загрузка каталога
|
||
print("5. 🏷️ Загрузка каталога...")
|
||
catalog_df = pd.read_excel(catalog_file)
|
||
loaded_products = 0
|
||
for _, row in catalog_df.iterrows():
|
||
cursor.execute("""
|
||
INSERT OR IGNORE INTO Catalog
|
||
(category_id, material_id, product_name, article, description, min_price, quantity_in_stock)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
""", (
|
||
int(row.get('category_id', 1)),
|
||
int(row.get('material_id')) if pd.notna(row.get('material_id')) else None,
|
||
row.get('product_name', ''),
|
||
str(row.get('article', '')),
|
||
row.get('description', ''),
|
||
float(row.get('min_price', 0)),
|
||
int(row.get('quantity_in_stock', 0))
|
||
))
|
||
loaded_products += 1
|
||
print(f" ✅ Загружено {loaded_products} товаров")
|
||
|
||
# 6. Загрузка заказов
|
||
print("6. 📝 Загрузка заказов...")
|
||
|
||
# Получаем соответствие товаров
|
||
products_dict = {}
|
||
cursor.execute("SELECT id, product_name, article, min_price FROM Catalog")
|
||
for row in cursor.fetchall():
|
||
products_dict[row[1]] = {'id': row[0], 'price': row[3]}
|
||
|
||
# Получаем соответствие поставщиков
|
||
suppliers_dict = {}
|
||
cursor.execute("SELECT id, name FROM Clients")
|
||
for row in cursor.fetchall():
|
||
suppliers_dict[row[1]] = row[0]
|
||
|
||
# Читаем заказы
|
||
orders_df = pd.read_excel(orders_file)
|
||
|
||
# Группируем заказы
|
||
order_groups = {}
|
||
for _, row in orders_df.iterrows():
|
||
supplier_name = row.get('Наименование партнера', '')
|
||
date = pd.to_datetime(row.get('Дата', datetime.now())).strftime('%Y-%m-%d')
|
||
key = f"{supplier_name}_{date}"
|
||
|
||
if key not in order_groups:
|
||
order_groups[key] = {
|
||
'supplier_id': suppliers_dict.get(supplier_name),
|
||
'date': date,
|
||
'items': []
|
||
}
|
||
|
||
product_name = row.get('Продукция', '')
|
||
quantity = int(row.get('Количество', 1))
|
||
|
||
if product_name in products_dict:
|
||
order_groups[key]['items'].append({
|
||
'product_id': products_dict[product_name]['id'],
|
||
'quantity': quantity,
|
||
'price': products_dict[product_name]['price']
|
||
})
|
||
|
||
# Создаем заказы
|
||
orders_count = 0
|
||
items_count = 0
|
||
|
||
for key, data in order_groups.items():
|
||
if not data['supplier_id'] or not data['items']:
|
||
continue
|
||
|
||
# Считаем сумму
|
||
total = 0
|
||
for item in data['items']:
|
||
total += item['quantity'] * item['price'] * 1.1
|
||
|
||
# Создаем заказ
|
||
cursor.execute('''
|
||
INSERT INTO Orders (client_id, order_date, total_amount, status)
|
||
VALUES (?, ?, ?, ?)
|
||
''', (data['supplier_id'], data['date'], total, 'Выполнен'))
|
||
|
||
order_id = cursor.lastrowid
|
||
orders_count += 1
|
||
|
||
# Создаем позиции
|
||
for item in data['items']:
|
||
transport = item['quantity'] * item['price'] * 0.1
|
||
cursor.execute('''
|
||
INSERT INTO OrderItems (order_id, product_id, quantity, price_at_moment, transport_cost)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
''', (order_id, item['product_id'], item['quantity'], item['price'], transport))
|
||
items_count += 1
|
||
|
||
conn.commit()
|
||
print(f" ✅ Загружено {orders_count} заказов и {items_count} позиций")
|
||
|
||
check_database()
|
||
|
||
except Exception as e:
|
||
print(f"❌ Ошибка при загрузке данных: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
conn.rollback()
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def display_menu():
|
||
"""Главное меню программы"""
|
||
menu_options = [
|
||
'КАРТОЧКИ КЛИЕНТОВ (сортировка по рейтингу)',
|
||
'КАРТОЧКИ КЛИЕНТОВ (сортировка по скидкам)',
|
||
'ИСТОРИЯ ЗАКАЗОВ',
|
||
'ЗАПОЛНЕНИЕ БД из файлов',
|
||
'ВЫХОД'
|
||
]
|
||
|
||
while True:
|
||
choice = simple_choice(menu_options)
|
||
|
||
if choice == 'КАРТОЧКИ КЛИЕНТОВ (сортировка по рейтингу)':
|
||
print("\n🎯 КАРТОЧКИ КЛИЕНТОВ (сортировка по рейтингу)")
|
||
clients = get_clients(sort_by='rating', ascending=False)
|
||
print_client_cards(clients)
|
||
|
||
elif choice == 'КАРТОЧКИ КЛИЕНТОВ (сортировка по скидкам)':
|
||
print("\n📊 КАРТОЧКИ КЛИЕНТОВ (сортировка по скидкам)")
|
||
clients = get_clients(sort_by='discount', ascending=False)
|
||
print_client_cards(clients)
|
||
|
||
elif choice == 'ИСТОРИЯ ЗАКАЗОВ':
|
||
print("\n📈 ИСТОРИЯ ЗАКАЗОВ")
|
||
orders = get_orders_history(limit=50)
|
||
print_orders_cards(orders)
|
||
|
||
elif choice == 'ЗАПОЛНЕНИЕ БД из файлов':
|
||
if os.path.exists(DB_PATH):
|
||
os.remove(DB_PATH)
|
||
print("🗑️ База данных materials.db удалена")
|
||
else:
|
||
print("ℹ️ База данных не найдена, создаем новую")
|
||
|
||
conn = sqlite3.connect(DB_PATH)
|
||
create_tables(conn)
|
||
conn.close()
|
||
load_data_from_files()
|
||
|
||
elif choice == 'ВЫХОД':
|
||
print("\n👋 До свидания!")
|
||
break
|
||
|
||
|
||
if __name__ == "__main__":
|
||
display_menu() |