350 lines
11 KiB
Python
350 lines
11 KiB
Python
from flask import Blueprint, flash, g, redirect, render_template, request, url_for
|
||
|
||
from services.contractors import (
|
||
activate_contractor,
|
||
add_contractor,
|
||
add_contractors_bulk,
|
||
delete_contractor,
|
||
disable_contractor,
|
||
get_all_contractors,
|
||
)
|
||
from services.documents import (
|
||
get_distinct_states_1c,
|
||
get_distinct_states_sbis,
|
||
update_comment_1c,
|
||
update_comment_sbis,
|
||
)
|
||
from services.excel_import import import_excel
|
||
from services.sbis_import import import_sbis_excel
|
||
|
||
bp = Blueprint("main", __name__)
|
||
|
||
|
||
def _filter_params():
|
||
return {
|
||
"contractor": request.values.get("contractor", ""),
|
||
"search": request.values.get("search", ""),
|
||
"status": request.values.get("status", ""),
|
||
}
|
||
|
||
|
||
def _redirect_docs(endpoint):
|
||
params = {k: v for k, v in _filter_params().items() if v}
|
||
return redirect(url_for(endpoint, **params))
|
||
|
||
|
||
def _query_1c(contractor=None, search=None, status=None):
|
||
sql = """
|
||
SELECT
|
||
d.id,
|
||
c.name AS contractor,
|
||
d.doc_type,
|
||
d.doc_date,
|
||
d.doc_number,
|
||
d.amount,
|
||
d.state,
|
||
d.comment,
|
||
d.updated_at
|
||
FROM documents_1c d
|
||
LEFT JOIN contractors c ON d.contractor_id = c.id
|
||
WHERE 1=1
|
||
"""
|
||
params = []
|
||
|
||
if contractor:
|
||
sql += " AND c.name = ?"
|
||
params.append(contractor)
|
||
|
||
if search:
|
||
sql += " AND d.doc_number LIKE ?"
|
||
params.append(f"%{search}%")
|
||
|
||
if status:
|
||
sql += " AND d.state = ?"
|
||
params.append(status)
|
||
|
||
sql += " ORDER BY d.updated_at DESC"
|
||
|
||
return g.db.execute(sql, params).fetchall()
|
||
|
||
|
||
def _query_sbis(contractor=None, search=None, status=None):
|
||
sql = """
|
||
SELECT
|
||
id, doc_date, doc_number, contractor,
|
||
doc_type, amount, state, responsible,
|
||
comment, updated_at
|
||
FROM documents_sbis
|
||
WHERE 1=1
|
||
"""
|
||
params = []
|
||
|
||
if contractor:
|
||
sql += " AND contractor = ?"
|
||
params.append(contractor)
|
||
|
||
if search:
|
||
sql += " AND doc_number LIKE ?"
|
||
params.append(f"%{search}%")
|
||
|
||
if status:
|
||
sql += " AND state = ?"
|
||
params.append(status)
|
||
|
||
# Сортируем как в Excel: сначала по дате документа DESC, затем по ID DESC
|
||
# (чем позже добавлен документ, тем выше он в списке)
|
||
sql += " ORDER BY doc_date DESC, id DESC"
|
||
|
||
return g.db.execute(sql, params).fetchall()
|
||
|
||
def _contractor_names_from_docs(table):
|
||
if table == "1c":
|
||
sql = """
|
||
SELECT DISTINCT c.name
|
||
FROM documents_1c d
|
||
JOIN contractors c ON d.contractor_id = c.id
|
||
WHERE c.name IS NOT NULL
|
||
ORDER BY c.name
|
||
"""
|
||
else:
|
||
sql = """
|
||
SELECT DISTINCT contractor
|
||
FROM documents_sbis
|
||
WHERE contractor IS NOT NULL AND TRIM(contractor) != ''
|
||
ORDER BY contractor
|
||
"""
|
||
return [r[0] for r in g.db.execute(sql).fetchall()]
|
||
|
||
|
||
@bp.route("/documents/1c", methods=["GET", "POST"])
|
||
def documents_1c():
|
||
filters = _filter_params()
|
||
|
||
if request.method == "POST":
|
||
action = request.form.get("action", "upload")
|
||
|
||
if action == "comment":
|
||
doc_id = request.form.get("doc_id")
|
||
comment = request.form.get("comment", "")
|
||
if doc_id and update_comment_1c(g.db, int(doc_id), comment):
|
||
flash("Комментарий сохранён.", "success")
|
||
else:
|
||
flash("Не удалось сохранить комментарий.", "error")
|
||
return _redirect_docs("main.documents_1c")
|
||
|
||
file = request.files.get("file")
|
||
if not file or not file.filename:
|
||
flash("Выберите Excel-файл для загрузки.", "error")
|
||
else:
|
||
try:
|
||
imported, updated, skipped = import_excel(file, g.db)
|
||
flash(
|
||
f"Импорт 1С: добавлено {imported}, обновлено {updated}, "
|
||
f"пропущено (не в списке контрагентов) {skipped}.",
|
||
"success",
|
||
)
|
||
except Exception as exc:
|
||
flash(f"Ошибка импорта: {exc}", "error")
|
||
return redirect(url_for("main.documents_1c"))
|
||
|
||
rows = _query_1c(
|
||
contractor=filters["contractor"] or None,
|
||
search=filters["search"] or None,
|
||
status=filters["status"] or None,
|
||
)
|
||
|
||
return render_template(
|
||
"documents_1c.html",
|
||
rows=rows,
|
||
contractors=_contractor_names_from_docs("1c"),
|
||
statuses=get_distinct_states_1c(g.db),
|
||
selected_contractor=filters["contractor"],
|
||
search=filters["search"],
|
||
selected_status=filters["status"],
|
||
)
|
||
|
||
|
||
@bp.route("/documents/sbis", methods=["GET", "POST"])
|
||
def documents_sbis():
|
||
filters = _filter_params()
|
||
|
||
if request.method == "POST":
|
||
action = request.form.get("action", "upload")
|
||
|
||
if action == "comment":
|
||
doc_id = request.form.get("doc_id")
|
||
comment = request.form.get("comment", "")
|
||
if doc_id and update_comment_sbis(g.db, int(doc_id), comment):
|
||
flash("Комментарий сохранён.", "success")
|
||
else:
|
||
flash("Не удалось сохранить комментарий.", "error")
|
||
return _redirect_docs("main.documents_sbis")
|
||
|
||
file = request.files.get("file")
|
||
if not file or not file.filename:
|
||
flash("Выберите Excel-файл для загрузки.", "error")
|
||
else:
|
||
try:
|
||
imported, updated, skipped = import_sbis_excel(file, g.db)
|
||
flash(
|
||
f"Импорт СБИС: добавлено {imported}, обновлено {updated}, "
|
||
f"пропущено (не в списке контрагентов) {skipped}.",
|
||
"success",
|
||
)
|
||
except Exception as exc:
|
||
flash(f"Ошибка импорта: {exc}", "error")
|
||
return redirect(url_for("main.documents_sbis"))
|
||
|
||
# Параметры пагинации
|
||
page = request.args.get('page', 1, type=int)
|
||
per_page = 150 # Количество записей на странице
|
||
offset = (page - 1) * per_page
|
||
|
||
# Получаем общее количество записей с учётом фильтров
|
||
count_sql = """
|
||
SELECT COUNT(*) as total
|
||
FROM documents_sbis
|
||
WHERE 1=1
|
||
"""
|
||
count_params = []
|
||
|
||
contractor = filters["contractor"] or None
|
||
search = filters["search"] or None
|
||
status = filters["status"] or None
|
||
|
||
if contractor:
|
||
count_sql += " AND contractor = ?"
|
||
count_params.append(contractor)
|
||
|
||
if search:
|
||
count_sql += " AND doc_number LIKE ?"
|
||
count_params.append(f"%{search}%")
|
||
|
||
if status:
|
||
count_sql += " AND state = ?"
|
||
count_params.append(status)
|
||
|
||
total = g.db.execute(count_sql, count_params).fetchone()['total']
|
||
total_pages = (total + per_page - 1) // per_page if total > 0 else 1
|
||
|
||
# Основной запрос с пагинацией
|
||
# В методе documents_sbis замените SQL запрос на:
|
||
sql = """
|
||
SELECT
|
||
id, doc_date, doc_number, contractor,
|
||
doc_type, amount, state, responsible,
|
||
comment, updated_at
|
||
FROM documents_sbis
|
||
WHERE 1=1
|
||
"""
|
||
params = []
|
||
|
||
if contractor:
|
||
sql += " AND contractor = ?"
|
||
params.append(contractor)
|
||
|
||
if search:
|
||
sql += " AND doc_number LIKE ?"
|
||
params.append(f"%{search}%")
|
||
|
||
if status:
|
||
sql += " AND state = ?"
|
||
params.append(status)
|
||
|
||
# Сортировка по дате документа и ID
|
||
sql += " ORDER BY doc_date DESC, id DESC LIMIT ? OFFSET ?"
|
||
params.extend([per_page, offset])
|
||
rows = g.db.execute(sql, params).fetchall()
|
||
|
||
return render_template(
|
||
"documents_sbis.html",
|
||
rows=rows,
|
||
contractors=_contractor_names_from_docs("sbis"),
|
||
statuses=get_distinct_states_sbis(g.db),
|
||
selected_contractor=filters["contractor"],
|
||
search=filters["search"],
|
||
selected_status=filters["status"],
|
||
page=page,
|
||
total_pages=total_pages,
|
||
total=total
|
||
)
|
||
def _query_sbis_with_pagination(contractor=None, search=None, status=None, limit=50, offset=0):
|
||
sql = """
|
||
SELECT
|
||
id, doc_date, doc_number, contractor,
|
||
doc_type, amount, state, responsible,
|
||
comment, updated_at
|
||
FROM documents_sbis
|
||
WHERE 1=1
|
||
ORDER BY doc_date DESC
|
||
"""
|
||
params = []
|
||
|
||
if contractor:
|
||
sql += " AND contractor = ?"
|
||
params.append(contractor)
|
||
|
||
if search:
|
||
sql += " AND doc_number LIKE ?"
|
||
params.append(f"%{search}%")
|
||
|
||
|
||
if status:
|
||
sql += " AND state = ?"
|
||
params.append(status)
|
||
|
||
sql += " ORDER BY updated_at DESC LIMIT ? OFFSET ?"
|
||
params.extend([limit, offset])
|
||
|
||
return g.db.execute(sql, params).fetchall()
|
||
|
||
@bp.route("/contractors", methods=["GET", "POST"])
|
||
def contractors():
|
||
if request.method == "POST":
|
||
action = request.form.get("action")
|
||
|
||
if action == "add":
|
||
name = request.form.get("name", "").strip()
|
||
if not name:
|
||
flash("Введите название контрагента.", "error")
|
||
elif add_contractor(g.db, name):
|
||
flash(f"Контрагент «{name}» добавлен.", "success")
|
||
else:
|
||
flash(f"Контрагент «{name}» уже существует.", "error")
|
||
|
||
elif action == "bulk_add":
|
||
text = request.form.get("names", "")
|
||
if not text.strip():
|
||
flash("Введите хотя бы одно название.", "error")
|
||
else:
|
||
added, exists = add_contractors_bulk(g.db, text)
|
||
flash(
|
||
f"Добавлено: {added}. Уже были в списке: {exists}.",
|
||
"success",
|
||
)
|
||
|
||
elif action == "activate":
|
||
contractor_id = request.form.get("contractor_id")
|
||
if contractor_id:
|
||
activate_contractor(g.db, int(contractor_id))
|
||
flash("Контрагент активирован.", "success")
|
||
|
||
elif action == "deactivate":
|
||
contractor_id = request.form.get("contractor_id")
|
||
if contractor_id:
|
||
disable_contractor(g.db, int(contractor_id))
|
||
flash("Контрагент деактивирован.", "success")
|
||
|
||
elif action == "delete":
|
||
contractor_id = request.form.get("contractor_id")
|
||
if contractor_id:
|
||
delete_contractor(g.db, int(contractor_id))
|
||
flash("Контрагент удалён.", "success")
|
||
|
||
return redirect(url_for("main.contractors"))
|
||
|
||
return render_template(
|
||
"contractors.html",
|
||
contractors=get_all_contractors(g.db),
|
||
)
|