381 lines
20 KiB
Python
381 lines
20 KiB
Python
from flask import Flask, render_template, request, redirect, url_for, flash, Response, jsonify
|
||
import sqlite3, csv, io
|
||
from datetime import date
|
||
|
||
def validate_email(email):
|
||
if not email:
|
||
return True
|
||
return '@' in email and '.' in email.split('@')[-1]
|
||
|
||
def validate_phone(phone):
|
||
if not phone:
|
||
return True
|
||
digits = ''.join(filter(str.isdigit, phone))
|
||
return len(digits) == 11
|
||
|
||
app = Flask(__name__)
|
||
app.secret_key = 'stroy_key_2025'
|
||
DB_PATH = 'stroy.db'
|
||
|
||
# -------------------------------------------------------
|
||
# СОЗДАНИЕ 6 ТАБЛИЦ
|
||
# -------------------------------------------------------
|
||
|
||
def init_db():
|
||
conn = sqlite3.connect(DB_PATH)
|
||
cur = conn.cursor()
|
||
|
||
# 1. Типы материалов
|
||
cur.execute("""CREATE TABLE IF NOT EXISTS material_types (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
description TEXT
|
||
)""")
|
||
|
||
# 2. Материалы
|
||
cur.execute("""CREATE TABLE IF NOT EXISTS materials (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
unit TEXT NOT NULL DEFAULT 'шт',
|
||
current_price REAL NOT NULL DEFAULT 0,
|
||
material_type_id INTEGER NOT NULL,
|
||
FOREIGN KEY (material_type_id) REFERENCES material_types(id)
|
||
)""")
|
||
|
||
# 3. Контрагенты (и поставщики и партнёры в одной таблице)
|
||
# type=1 — партнёр (расходная), type=2 — поставщик (приходная)
|
||
cur.execute("""CREATE TABLE IF NOT EXISTS counterparties (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
contact_person TEXT,
|
||
phone TEXT,
|
||
email TEXT,
|
||
address TEXT,
|
||
type INTEGER NOT NULL DEFAULT 1
|
||
)""")
|
||
|
||
# 4. Заказы
|
||
cur.execute("""CREATE TABLE IF NOT EXISTS purchase_orders (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
number TEXT NOT NULL,
|
||
order_date TEXT NOT NULL,
|
||
delivery_date TEXT,
|
||
status TEXT NOT NULL DEFAULT 'Новый',
|
||
counterparty_id INTEGER NOT NULL,
|
||
FOREIGN KEY (counterparty_id) REFERENCES counterparties(id)
|
||
)""")
|
||
|
||
# 5. Позиции заказа
|
||
cur.execute("""CREATE TABLE IF NOT EXISTS order_items (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id INTEGER NOT NULL,
|
||
material_id INTEGER NOT NULL,
|
||
quantity REAL NOT NULL DEFAULT 1,
|
||
unit_price REAL NOT NULL DEFAULT 0,
|
||
FOREIGN KEY (order_id) REFERENCES purchase_orders(id),
|
||
FOREIGN KEY (material_id) REFERENCES materials(id)
|
||
)""")
|
||
|
||
conn.commit()
|
||
|
||
# Начальные данные
|
||
if cur.execute("SELECT COUNT(*) FROM material_types").fetchone()[0] == 0:
|
||
for t in [('Кирпич и блоки','Кирпич, блоки, камень'),('Цемент и смеси','Цемент, штукатурка, шпатлёвка'),
|
||
('Пиломатериалы','Доски, брус, фанера'),('Кровельные материалы','Профнастил, черепица'),
|
||
('Теплоизоляция','Минвата, пенопласт'),('Отделочные материалы','Плитка, гипсокартон')]:
|
||
cur.execute("INSERT INTO material_types (name, description) VALUES (?,?)", t)
|
||
|
||
for m in [('Кирпич керамический М150',1,'шт',18.5),('Газобетонный блок D500',1,'шт',185.0),
|
||
('Цемент ПЦ 500, 50 кг',2,'мешок',450.0),('Штукатурная смесь Ротбанд',2,'мешок',620.0),
|
||
('Доска обрезная 50x150x6000',3,'шт',850.0),('Фанера ФСФ 18 мм',3,'лист',1850.0),
|
||
('Профнастил НС35',4,'м²',580.0),('Черепица Braas',4,'шт',95.0),
|
||
('Минвата Knauf 100 мм',5,'м²',320.0),('Пенопласт ПСБ-С 35',5,'м²',185.0),
|
||
('Плитка керамическая 30x30',6,'м²',850.0),('Гипсокартон Knauf 12.5 мм',6,'лист',520.0)]:
|
||
cur.execute("INSERT INTO materials (name, material_type_id, unit, current_price) VALUES (?,?,?,?)", m)
|
||
|
||
# type=1 партнёры, type=2 поставщики
|
||
for c in [('ООО СтройГрупп','Иванов И.И.','+7(495)111-22-33','info@stroygroup.ru','Москва, ул. Ленина, 10',1),
|
||
('ИП Петров А.А.','Петров А.А.','+7(903)222-33-44','petrov@mail.ru','Московская обл., г. Химки',1),
|
||
('АО РемСтрой','Сидоров С.С.','+7(495)333-44-55','rem@stroy.ru','Москва, пр-т Мира, 55',1),
|
||
('СтройОптТорг','Козлов К.К.','+7(495)123-45-67','info@stroyopt.ru','Москва, ул. Строителей, 15',2),
|
||
('КирпичМастер','Морозов М.М.','+7(499)234-56-78','sales@kirpich.ru','Подольск, пр-т Ленина, 42',2),
|
||
('ЛесТорг Урал','Лесов Л.Л.','+7(343)345-67-89','les@ural.com','Екатеринбург, ул. Лесная, 8',2)]:
|
||
cur.execute("INSERT INTO counterparties (name,contact_person,phone,email,address,type) VALUES (?,?,?,?,?,?)", c)
|
||
|
||
# Заказы
|
||
cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES ('ЗК-001','2026-01-15','2026-01-20','Выполнен',4)")
|
||
cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES ('ЗК-002','2026-02-10','2026-02-15','Выполнен',1)")
|
||
cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES ('ЗК-003','2026-03-01',NULL,'Новый',5)")
|
||
|
||
# Позиции заказов
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (1,1,5000,18.5)")
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (1,3,100,450.0)")
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (2,5,50,850.0)")
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (2,9,200,320.0)")
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (3,7,100,580.0)")
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def get_db():
|
||
conn = sqlite3.connect(DB_PATH)
|
||
conn.row_factory = sqlite3.Row
|
||
return conn
|
||
|
||
# -------------------------------------------------------
|
||
# ГЛАВНАЯ — материалы
|
||
# -------------------------------------------------------
|
||
|
||
@app.route('/')
|
||
def index():
|
||
search = request.args.get('search','')
|
||
type_filter = request.args.get('type_id','')
|
||
conn = get_db(); cur = conn.cursor()
|
||
sql = "SELECT m.*, mt.name AS type_name FROM materials m JOIN material_types mt ON m.material_type_id=mt.id WHERE 1=1"
|
||
params = []
|
||
if search: sql += " AND m.name LIKE ?"; params.append('%'+search+'%')
|
||
if type_filter: sql += " AND m.material_type_id=?"; params.append(type_filter)
|
||
sql += " ORDER BY m.name"
|
||
cur.execute(sql, params)
|
||
materials = [dict(r) for r in cur.fetchall()]
|
||
cur.execute("SELECT * FROM material_types ORDER BY name"); types = cur.fetchall()
|
||
stat = {
|
||
'materials': cur.execute("SELECT COUNT(*) FROM materials").fetchone()[0],
|
||
'counterparties': cur.execute("SELECT COUNT(*) FROM counterparties").fetchone()[0],
|
||
'partners': cur.execute("SELECT COUNT(*) FROM counterparties WHERE type=1").fetchone()[0],
|
||
'suppliers': cur.execute("SELECT COUNT(*) FROM counterparties WHERE type=2").fetchone()[0],
|
||
'orders': cur.execute("SELECT COUNT(*) FROM purchase_orders").fetchone()[0],
|
||
}
|
||
conn.close()
|
||
return render_template('index.html', materials=materials, types=types, search=search, type_filter=type_filter, stat=stat)
|
||
|
||
@app.route('/materials/add', methods=['GET','POST'])
|
||
def material_add():
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT * FROM material_types ORDER BY name"); types = cur.fetchall(); conn.close()
|
||
if request.method == 'POST':
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("INSERT INTO materials (name,material_type_id,unit,current_price) VALUES (?,?,?,?)",
|
||
(request.form['name'],request.form['material_type_id'],request.form['unit'],request.form['current_price']))
|
||
conn.commit(); conn.close()
|
||
flash('Материал добавлен','success'); return redirect(url_for('index'))
|
||
return render_template('material_form.html', material=None, types=types, action='add')
|
||
|
||
@app.route('/materials/edit/<int:mid>', methods=['GET','POST'])
|
||
def material_edit(mid):
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT * FROM materials WHERE id=?",(mid,)); material = cur.fetchone()
|
||
cur.execute("SELECT * FROM material_types ORDER BY name"); types = cur.fetchall(); conn.close()
|
||
if not material: flash('Не найдено','error'); return redirect(url_for('index'))
|
||
if request.method == 'POST':
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("UPDATE materials SET name=?,material_type_id=?,unit=?,current_price=? WHERE id=?",
|
||
(request.form['name'],request.form['material_type_id'],request.form['unit'],request.form['current_price'],mid))
|
||
conn.commit(); conn.close()
|
||
flash('Материал обновлён','success'); return redirect(url_for('index'))
|
||
return render_template('material_form.html', material=material, types=types, action='edit')
|
||
|
||
# -------------------------------------------------------
|
||
# КОНТРАГЕНТЫ
|
||
# -------------------------------------------------------
|
||
|
||
@app.route('/counterparties')
|
||
def counterparties_list():
|
||
ctype = request.args.get('type','')
|
||
conn = get_db(); cur = conn.cursor()
|
||
sql = "SELECT c.*, COUNT(o.id) AS order_count FROM counterparties c LEFT JOIN purchase_orders o ON c.id=o.counterparty_id WHERE 1=1"
|
||
params = []
|
||
if ctype: sql += " AND c.type=?"; params.append(ctype)
|
||
sql += " GROUP BY c.id ORDER BY c.name"
|
||
cur.execute(sql, params); counterparties = cur.fetchall(); conn.close()
|
||
return render_template('counterparties.html', counterparties=counterparties, ctype=ctype)
|
||
|
||
@app.route('/counterparties/add', methods=['GET','POST'])
|
||
def counterparty_add():
|
||
is_ajax = request.args.get('ajax')=='1'
|
||
default_type = request.args.get('type','1')
|
||
if request.method == 'POST':
|
||
name = request.form.get('name','').strip()
|
||
phone = request.form.get('phone','').strip()
|
||
email = request.form.get('email','').strip()
|
||
|
||
# Проверки
|
||
if not name:
|
||
flash('Название обязательно', 'error')
|
||
elif phone and not validate_phone(phone):
|
||
flash('Неверный формат телефона. Должно быть 10 или 11 цифр', 'error')
|
||
elif email and not validate_email(email):
|
||
flash('Неверный формат email. Нужен символ @ и точка', 'error')
|
||
else:
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("INSERT INTO counterparties (name,contact_person,phone,email,address,type) VALUES (?,?,?,?,?,?)",
|
||
(name,request.form.get('contact_person',''),phone,email,request.form.get('address',''),request.form.get('type',1)))
|
||
new_id = cur.lastrowid; conn.commit(); conn.close()
|
||
if is_ajax: return jsonify({'id':new_id,'name':name})
|
||
flash('Контрагент добавлен','success')
|
||
return redirect(url_for('counterparties_list'))
|
||
|
||
return render_template('counterparty_form.html', counterparty=None, action='add', default_type=default_type)
|
||
|
||
@app.route('/counterparties/edit/<int:cid>', methods=['GET','POST'])
|
||
def counterparty_edit(cid):
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT * FROM counterparties WHERE id=?",(cid,)); counterparty = cur.fetchone()
|
||
conn.close()
|
||
if not counterparty:
|
||
flash('Не найдено','error')
|
||
return redirect(url_for('counterparties_list'))
|
||
|
||
if request.method == 'POST':
|
||
name = request.form.get('name','').strip()
|
||
phone = request.form.get('phone','').strip()
|
||
email = request.form.get('email','').strip()
|
||
|
||
# Проверки
|
||
if not name:
|
||
flash('Название обязательно', 'error')
|
||
elif phone and not validate_phone(phone):
|
||
flash('Неверный формат телефона. Должно быть ровно 11 цифр', 'error')
|
||
elif email and not validate_email(email):
|
||
flash('Неверный формат email. Нужен символ @ и точка', 'error')
|
||
else:
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("UPDATE counterparties SET name=?,contact_person=?,phone=?,email=?,address=?,type=? WHERE id=?",
|
||
(name,request.form.get('contact_person',''),phone,email,request.form.get('address',''),request.form.get('type',1),cid))
|
||
conn.commit(); conn.close()
|
||
flash('Контрагент обновлён','success')
|
||
return redirect(url_for('counterparties_list'))
|
||
|
||
return render_template('counterparty_form.html', counterparty=counterparty, action='edit', default_type=counterparty['type'])
|
||
|
||
# -------------------------------------------------------
|
||
# ЗАКАЗЫ
|
||
# -------------------------------------------------------
|
||
|
||
@app.route('/orders')
|
||
def orders_list():
|
||
sort = request.args.get('sort', 'date_desc')
|
||
search = request.args.get('search', '').strip()
|
||
|
||
conn = get_db()
|
||
cur = conn.cursor()
|
||
|
||
# Базовый SQL с поиском
|
||
sql = """
|
||
SELECT o.*, c.name AS counterparty_name, c.type AS counterparty_type,
|
||
COUNT(oi.id) AS items_count
|
||
FROM purchase_orders o
|
||
JOIN counterparties c ON o.counterparty_id = c.id
|
||
LEFT JOIN order_items oi ON o.id = oi.order_id
|
||
"""
|
||
params = []
|
||
|
||
if search:
|
||
sql += " WHERE o.number LIKE ? OR c.name LIKE ? OR o.status LIKE ?"
|
||
search_pattern = f'%{search}%'
|
||
params = [search_pattern, search_pattern, search_pattern]
|
||
|
||
sql += " GROUP BY o.id"
|
||
|
||
# Сортировка
|
||
if sort == 'date_asc':
|
||
sql += " ORDER BY o.order_date ASC"
|
||
elif sort == 'date_desc':
|
||
sql += " ORDER BY o.order_date DESC"
|
||
elif sort == 'price_asc':
|
||
sql += " ORDER BY (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) ASC"
|
||
elif sort == 'price_desc':
|
||
sql += " ORDER BY (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) DESC"
|
||
else:
|
||
sql += " ORDER BY o.order_date DESC"
|
||
|
||
cur.execute(sql, params)
|
||
orders_raw = cur.fetchall()
|
||
|
||
orders = []
|
||
for o in orders_raw:
|
||
# Считаем итого по позициям
|
||
cur.execute("SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = ?", (o['id'],))
|
||
total = cur.fetchone()[0] or 0
|
||
orders.append({**dict(o), 'total': round(total, 2)})
|
||
|
||
conn.close()
|
||
|
||
return render_template('orders_list.html', orders=orders, sort=sort, search=search)
|
||
|
||
|
||
@app.route('/orders/add', methods=['GET','POST'])
|
||
def order_add():
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT * FROM counterparties ORDER BY type, name"); counterparties = cur.fetchall()
|
||
cur.execute("SELECT m.*, mt.name AS type_name FROM materials m JOIN material_types mt ON m.material_type_id=mt.id ORDER BY m.name")
|
||
materials = cur.fetchall(); conn.close()
|
||
if request.method == 'POST':
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES (?,?,?,?,?)",
|
||
(request.form['number'],request.form['order_date'],request.form.get('delivery_date',''),
|
||
request.form.get('status','Новый'),request.form['counterparty_id']))
|
||
order_id = cur.lastrowid
|
||
# Сохраняем позиции
|
||
mat_ids = request.form.getlist('material_id[]')
|
||
quantities = request.form.getlist('quantity[]')
|
||
prices = request.form.getlist('unit_price[]')
|
||
for mid, qty, price in zip(mat_ids, quantities, prices):
|
||
if mid and qty and float(qty) > 0:
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (?,?,?,?)",
|
||
(order_id, mid, qty, price))
|
||
conn.commit(); conn.close()
|
||
flash('Заказ добавлен','success'); return redirect(url_for('orders_list'))
|
||
today = date.today().strftime('%Y-%m-%d')
|
||
return render_template('order_form.html', order=None, items=[], counterparties=counterparties, materials=materials, action='add', today=today)
|
||
|
||
@app.route('/orders/edit/<int:oid>', methods=['GET','POST'])
|
||
def order_edit(oid):
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT * FROM purchase_orders WHERE id=?",(oid,)); order = cur.fetchone()
|
||
cur.execute("SELECT oi.*, m.name AS material_name FROM order_items oi JOIN materials m ON oi.material_id=m.id WHERE oi.order_id=?",(oid,))
|
||
items = cur.fetchall()
|
||
cur.execute("SELECT * FROM counterparties ORDER BY type, name"); counterparties = cur.fetchall()
|
||
cur.execute("SELECT m.*, mt.name AS type_name FROM materials m JOIN material_types mt ON m.material_type_id=mt.id ORDER BY m.name")
|
||
materials = cur.fetchall(); conn.close()
|
||
if not order: flash('Не найдено','error'); return redirect(url_for('orders_list'))
|
||
if request.method == 'POST':
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("UPDATE purchase_orders SET number=?,order_date=?,delivery_date=?,status=?,counterparty_id=? WHERE id=?",
|
||
(request.form['number'],request.form['order_date'],request.form.get('delivery_date',''),
|
||
request.form.get('status','Новый'),request.form['counterparty_id'],oid))
|
||
cur.execute("DELETE FROM order_items WHERE order_id=?",(oid,))
|
||
mat_ids = request.form.getlist('material_id[]')
|
||
quantities = request.form.getlist('quantity[]')
|
||
prices = request.form.getlist('unit_price[]')
|
||
for mid, qty, price in zip(mat_ids, quantities, prices):
|
||
if mid and qty and float(qty) > 0:
|
||
cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (?,?,?,?)",
|
||
(oid, mid, qty, price))
|
||
conn.commit(); conn.close()
|
||
flash('Заказ обновлён','success'); return redirect(url_for('orders_list'))
|
||
return render_template('order_form.html', order=order, items=items, counterparties=counterparties, materials=materials, action='edit', today=order['order_date'])
|
||
|
||
@app.route('/api/material/<int:mid>')
|
||
def api_material(mid):
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT * FROM materials WHERE id=?",(mid,)); m = cur.fetchone(); conn.close()
|
||
if not m: return jsonify({}), 404
|
||
return jsonify({'price': m['current_price'], 'unit': m['unit'], 'name': m['name']})
|
||
|
||
@app.route('/export/csv')
|
||
def export_csv():
|
||
conn = get_db(); cur = conn.cursor()
|
||
cur.execute("SELECT m.name, mt.name AS type_name, m.unit, m.current_price FROM materials m JOIN material_types mt ON m.material_type_id=mt.id ORDER BY m.name")
|
||
rows = cur.fetchall(); conn.close()
|
||
output = io.StringIO(); writer = csv.writer(output)
|
||
writer.writerow(['Наименование','Тип','Ед.','Цена'])
|
||
for r in rows: writer.writerow([r['name'],r['type_name'],r['unit'],r['current_price']])
|
||
output.seek(0)
|
||
return Response('\ufeff'+output.read(), mimetype='text/csv; charset=utf-8-sig',
|
||
headers={'Content-Disposition':'attachment; filename=materials.csv'})
|
||
|
||
if __name__ == '__main__':
|
||
init_db()
|
||
app.run(debug=True, port=5000)
|