https://pastein.ru/t/BXT
Загрузка данных
from flask import Flask, render_template, request, send_file
import psycopg2
import traceback
import os
import tempfile
import xlrd
import xlwt
from xlutils.copy import copy as xl_copy
from openpyxl import load_workbook
import pymorphy3 # вместо pymorphy2
app = Flask(__name__)
# -------------------- Настройки БД --------------------
DB_HOST = "172.16.1.28"
DB_PORT = 5432
DB_NAME = "test"
DB_USER = "dev_intern"
DB_PASSWORD = "Cneltynbr33"
TEMPLATE_BLANK6 = "BLANK6.XLS"
TEMPLATE_BLANK5 = "BLANK5_1.xlsx"
TEMPLATE_BLANK4 = "BLANK4_3.xlsx"
# Инициализация pymorphy3
morph = pymorphy3.MorphAnalyzer()
def get_db_connection():
return psycopg2.connect(
host=DB_HOST, port=DB_PORT, dbname=DB_NAME,
user=DB_USER, password=DB_PASSWORD
)
# -------------------- Склонение ФИО --------------------
def inflect_fio(fam, imja, otch, case='nomn'):
if case == 'nomn':
return fam, imja, otch
def inflect_word(word):
if not word:
return word
parsed = morph.parse(word)[0]
inflected = parsed.inflect({'gent'})
return inflected.word if inflected else word
return inflect_word(fam), inflect_word(imja), inflect_word(otch)
# -------------------- Главная --------------------
@app.route("/")
def index():
search_tabnum = request.args.get("tabnum", "").strip()
search_fam = request.args.get("fam", "").strip()
try:
conn = get_db_connection()
cur = conn.cursor()
query = "SELECT Tabnum, fam, imja, otch, nam_dol FROM rabotnik WHERE 1=1"
params = []
if search_tabnum:
query += " AND Tabnum = %s"
params.append(search_tabnum)
if search_fam:
query += " AND fam ILIKE %s"
params.append(f"%{search_fam}%")
query += " ORDER BY fam, imja"
cur.execute(query, params)
rows = cur.fetchall()
columns = [desc[0] for desc in cur.description]
cur.close()
conn.close()
return render_template("index.html", rows=rows, columns=columns,
search_tabnum=search_tabnum, search_fam=search_fam)
except Exception as e:
return f"<h2>Ошибка загрузки работников</h2><pre>{traceback.format_exc()}</pre>"
# -------------------- Страница детей --------------------
@app.route("/employee/<tabnum>/children")
def select_children(tabnum):
try:
conn = get_db_connection()
cur = conn.cursor()
cur.execute("SELECT fam, imja, otch, Tabnum, nam_dol FROM rabotnik WHERE Tabnum = %s", (tabnum,))
worker = cur.fetchone()
if not worker:
return f"<h2>Работник с таб. № {tabnum} не найден</h2><a href='/'>Назад</a>"
cur.execute("SELECT id, fam_rod, imja_rod, otch_rod, drog_rod, step_rod, pol_rod FROM deti WHERE tabnum = %s ORDER BY id", (tabnum,))
children = cur.fetchall()
cur.close()
conn.close()
return render_template("employee.html", worker=worker, children=children)
except Exception as e:
return f"<h2>Ошибка загрузки детей</h2><pre>{traceback.format_exc()}</pre>"
# -------------------- Единый экспорт --------------------
@app.route("/employee/<tabnum>/export")
def export_data(tabnum):
form_type = request.args.get("form")
child_id = request.args.get("child_id")
case = request.args.get("case", "nomn")
try:
conn = get_db_connection()
cur = conn.cursor()
cur.execute("SELECT fam, imja, otch FROM rabotnik WHERE Tabnum = %s", (tabnum,))
worker = cur.fetchone()
if not worker:
cur.close()
conn.close()
return "Работник не найден"
if form_type == "blank5":
if not child_id:
cur.close()
conn.close()
return "Для BLANK5_1 выберите ребёнка"
cur.execute("SELECT fam_rod, imja_rod, otch_rod FROM deti WHERE id = %s AND tabnum = %s", (child_id, tabnum))
child = cur.fetchone()
if not child:
cur.close()
conn.close()
return "Ребёнок не найден"
else:
child = None
cur.close()
conn.close()
fam, imja, otch = worker
fam_inf, imja_inf, otch_inf = inflect_fio(fam, imja, otch, case)
if child:
fam_rod, imja_rod, otch_rod = child
# -------------------- BLANKS6 --------------------
if form_type == "blank6":
if not os.path.exists(TEMPLATE_BLANK6):
return f"Шаблон {TEMPLATE_BLANK6} не найден"
rb = xlrd.open_workbook(TEMPLATE_BLANK6, formatting_info=True)
wb = xl_copy(rb)
ws = wb.get_sheet(0)
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'Times New Roman'
font.height = 200
style.font = font
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style.borders = borders
rows_fio = [14, 15, 16]
start_col = 3
step = 3
for i, ch in enumerate(fam_inf.upper()):
col = start_col + i * step
if col < 256:
ws.write(rows_fio[0], col, ch, style)
for i, ch in enumerate(imja_inf.upper()):
col = start_col + i * step
if col < 256:
ws.write(rows_fio[1], col, ch, style)
for i, ch in enumerate(otch_inf.upper()):
col = start_col + i * step
if col < 256:
ws.write(rows_fio[2], col, ch, style)
fd, tmp = tempfile.mkstemp(suffix=".xls")
os.close(fd)
wb.save(tmp)
return send_file(tmp, as_attachment=True,
download_name=f"BLANKS6_{tabnum}.xls",
mimetype="application/vnd.ms-excel")
# -------------------- BLANK5_1 --------------------
elif form_type == "blank5":
if not os.path.exists(TEMPLATE_BLANK5):
return f"Шаблон {TEMPLATE_BLANK5} не найден"
wb = load_workbook(TEMPLATE_BLANK5)
ws = wb.active
step = 5
rows_worker = [16, 17, 18]
start_col_worker = 6
for i, ch in enumerate(fam_inf.upper()):
ws.cell(row=rows_worker[0], column=start_col_worker + i * step, value=ch)
for i, ch in enumerate(imja_inf.upper()):
ws.cell(row=rows_worker[1], column=start_col_worker + i * step, value=ch)
for i, ch in enumerate(otch_inf.upper()):
ws.cell(row=rows_worker[2], column=start_col_worker + i * step, value=ch)
rows_child = [33, 36, 38]
start_col_child = 1
for i, ch in enumerate(fam_rod.upper()):
ws.cell(row=rows_child[0], column=start_col_child + i * step, value=ch)
for i, ch in enumerate(imja_rod.upper()):
ws.cell(row=rows_child[1], column=start_col_child + i * step, value=ch)
for i, ch in enumerate(otch_rod.upper()):
ws.cell(row=rows_child[2], column=start_col_child + i * step, value=ch)
fd, tmp = tempfile.mkstemp(suffix=".xlsx")
os.close(fd)
wb.save(tmp)
return send_file(tmp, as_attachment=True,
download_name=f"BLANK5_1_{tabnum}.xlsx",
mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
# -------------------- BLANK4_3 --------------------
elif form_type == "blank4_3":
if not os.path.exists(TEMPLATE_BLANK4):
return f"Шаблон {TEMPLATE_BLANK4} не найден"
wb = load_workbook(TEMPLATE_BLANK4)
ws = wb.active
step = 5
rows_block1 = [15, 16, 17]
start_col_block1 = 6
for i, ch in enumerate(fam_inf.upper()):
ws.cell(row=rows_block1[0], column=start_col_block1 + i * step, value=ch)
for i, ch in enumerate(imja_inf.upper()):
ws.cell(row=rows_block1[1], column=start_col_block1 + i * step, value=ch)
for i, ch in enumerate(otch_inf.upper()):
ws.cell(row=rows_block1[2], column=start_col_block1 + i * step, value=ch)
rows_block2 = [22, 24, 26]
start_col_block2 = 1
for i, ch in enumerate(fam_inf.upper()):
ws.cell(row=rows_block2[0], column=start_col_block2 + i * step, value=ch)
for i, ch in enumerate(imja_inf.upper()):
ws.cell(row=rows_block2[1], column=start_col_block2 + i * step, value=ch)
for i, ch in enumerate(otch_inf.upper()):
ws.cell(row=rows_block2[2], column=start_col_block2 + i * step, value=ch)
fd, tmp = tempfile.mkstemp(suffix=".xlsx")
os.close(fd)
wb.save(tmp)
return send_file(tmp, as_attachment=True,
download_name=f"BLANK4_3_{tabnum}.xlsx",
mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
else:
return "Неверный тип формы"
except Exception as e:
return f"<h2>Ошибка экспорта</h2><pre>{traceback.format_exc()}</pre>"
# -------------------- Запуск --------------------
if __name__ == "__main__":
app.run(debug=True, host="0.0.0.0", port=5000)