make_report.py - main - DATKAI/label_system

make_report.py
452 строки20 KB
DATKAI
Initial commit: система этикеток v3 (резка плёнки)
29 июн 2026, 21:08
29 июн 2026, 21:08b147562
#!/usr/bin/env python3
"""Beautiful XLSX report generator for Label System v3."""
import sys, json, base64, re
from io import BytesIO
from datetime import datetime
from openpyxl import Workbook
from openpyxl.styles import (Font, PatternFill, Alignment, Border, Side,
                               GradientFill)
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference

# ── Load data ────────────────────────────────────────────────
with open(sys.argv[1], encoding='utf-8') as f:
    data = json.load(f)

rpt_type   = data.get('rptType', 'summary')
rpt_from   = data.get('rptFrom', '')
rpt_to     = data.get('rptTo', '')
rpt_op     = data.get('rptOperator', '')
rpt_dec    = data.get('rptDecor', '')
log        = data.get('log', [])
tasks      = data.get('tasks', [])
users      = data.get('users', [])
decors     = data.get('decors', [])

# ── Filter log ───────────────────────────────────────────────
def get_meters(r):
    m = re.search(r'×\s*([\d.]+)м', r.get('details', {}).get('size', ''))
    return float(m.group(1)) if m else 0

from_ts = datetime.fromisoformat(rpt_from + 'T00:00:00').timestamp() * 1000 if rpt_from else 0
to_ts   = datetime.fromisoformat(rpt_to + 'T23:59:59').timestamp() * 1000 if rpt_to else 9e15

prints = []
for r in log:
    if r.get('type') != 'print': continue
    d = r.get('details', {})
    dec = d.get('decor', '')
    if not dec or dec.startswith('['): continue
    ts = r.get('ts', '')
    try:
        ts_ms = datetime.fromisoformat(ts.replace('Z','+00:00')).timestamp() * 1000
    except:
        continue
    if ts_ms < from_ts or ts_ms > to_ts: continue
    if rpt_op and d.get('login', d.get('operator', '')) != rpt_op: continue
    if rpt_dec and dec != rpt_dec: continue
    prints.append(r)

task_list = []
for t in tasks:
    d = t.get('date', '')
    if not d: continue
    if rpt_from and d < rpt_from: continue
    if rpt_to and d > rpt_to: continue
    if rpt_op and t.get('assignTo', '') and t.get('assignTo') != rpt_op: continue
    if rpt_dec and t.get('decor', '') != rpt_dec: continue
    task_list.append(t)

total_m    = sum(get_meters(r) for r in prints)
total_lbl  = len(prints)
done_tasks = sum(1 for t in task_list if t.get('status') in ('archived', 'done', 'partial'))

# ── Styles ───────────────────────────────────────────────────
GREEN      = '2ECC71'
DARK_GREEN = '27AE60'
WHITE      = 'FFFFFF'
LIGHT_BG   = 'F0FFF4'
HEADER_BG  = '1A7A4A'
ALT_BG     = 'E8F8EE'
GRAY_BG    = 'F5F5F5'
BORDER_CLR = 'B2DFDB'

thin = Side(style='thin', color=BORDER_CLR)
med  = Side(style='medium', color=DARK_GREEN)
brd  = Border(left=thin, right=thin, top=thin, bottom=thin)
brd_med = Border(left=med, right=med, top=med, bottom=med)

def hdr_font(sz=11): return Font(bold=True, color=WHITE, size=sz, name='Arial')
def ttl_font(sz=13): return Font(bold=True, color=DARK_GREEN, size=sz, name='Arial')
def reg_font(sz=10): return Font(size=sz, name='Arial')
def num_font(sz=10): return Font(size=sz, name='Arial', bold=True, color='1A7A4A')
def hdr_fill(): return PatternFill('solid', fgColor=HEADER_BG)
def alt_fill(): return PatternFill('solid', fgColor=ALT_BG)
def kpi_fill(): return PatternFill('solid', fgColor=LIGHT_BG)
def ctr(): return Alignment(horizontal='center', vertical='center', wrap_text=True)
def lft(): return Alignment(horizontal='left',   vertical='center', wrap_text=False)
def rgt(): return Alignment(horizontal='right',  vertical='center')

def set_cell(ws, row, col, val, font=None, fill=None, align=None, border=None, fmt=None):
    c = ws.cell(row=row, column=col, value=val)
    if font:   c.font = font
    if fill:   c.fill = fill
    if align:  c.alignment = align
    if border: c.border = border
    if fmt:    c.number_format = fmt
    return c

# ── Workbook ─────────────────────────────────────────────────
wb = Workbook()

# ────────────────────────────────────────────────────────────
# SHEET 1 — Сводка
# ────────────────────────────────────────────────────────────
ws1 = wb.active
ws1.title = 'Сводка'
ws1.sheet_view.showGridLines = False
ws1.column_dimensions['A'].width = 3
ws1.column_dimensions['B'].width = 32
ws1.column_dimensions['C'].width = 20
ws1.column_dimensions['D'].width = 20
ws1.column_dimensions['E'].width = 20
ws1.column_dimensions['F'].width = 20
ws1.row_dimensions[1].height = 8

# Title banner
ws1.merge_cells('B2:F4')
c = ws1['B2']
c.value = 'ОТЧЁТ — УЧАСТОК РЕЗКИ ПЛЁНКИ'
c.font = Font(bold=True, size=18, color=WHITE, name='Arial')
c.fill = PatternFill('solid', fgColor=HEADER_BG)
c.alignment = ctr()

# Period info
ws1.merge_cells('B5:F5')
period_str = f'Период: {rpt_from} — {rpt_to}'
if rpt_op:
    op_name = next((u.get('name', rpt_op) for u in users if u.get('login') == rpt_op), rpt_op)
    period_str += f'   |   Оператор: {op_name}'
if rpt_dec:
    period_str += f'   |   Декор: {rpt_dec}'
c5 = ws1['B5']
c5.value = period_str
c5.font = Font(size=10, italic=True, color='555555', name='Arial')
c5.alignment = ctr()
c5.fill = PatternFill('solid', fgColor='E8F5E9')

ws1.row_dimensions[6].height = 10

# KPI cards row 7-9
kpis = [
    ('МЕТРОВ ПОРЕЗАНО', f'{total_m:,.0f} м'.replace(',', ' ')),
    ('ЭТИКЕТОК',        str(total_lbl)),
    ('ЗАДАНИЙ',         str(len(task_list))),
    ('ВЫПОЛНЕНО',       f'{round(done_tasks/len(task_list)*100) if task_list else 0}%'),
]
for i, (lbl, val) in enumerate(kpis):
    col = i + 2  # B=2, C=3, D=4, E=5 ... but we have 4 cols B-E skip F
    # Adjust to spread across B,C,D,E,F — use pairs
    pass

# KPIs in 4 cells B7:C9, D7:E9, ... using merged cells
kpi_ranges = ['B7:C9', 'D7:E9', 'B11:C13', 'D11:E13']
for (lbl, val), rng in zip(kpis, kpi_ranges):
    ws1.merge_cells(rng)
    c = ws1[rng.split(':')[0]]
    c.value = f'{val}\n{lbl}'
    c.font = Font(bold=True, size=14, color=DARK_GREEN, name='Arial')
    c.fill = PatternFill('solid', fgColor=LIGHT_BG)
    c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    c.border = Border(left=med, right=med, top=med, bottom=med)
    ws1.row_dimensions[int(rng.split(':')[0][1:])].height = 30

ws1.row_dimensions[10].height = 8

# ── Operators table ──────────────────────────────────────────
row = 15
ws1.merge_cells(f'B{row}:F{row}')
set_cell(ws1, row, 2, '🏆  ТОП ОПЕРАТОРОВ', ttl_font(12), align=lft())
ws1.row_dimensions[row].height = 22
row += 1

hdrs = ['#', 'Оператор', 'Метров', 'Этикеток', 'Ср. м/эт.']
widths_op = [4, 28, 14, 12, 12]
for j, (h_txt, wd) in enumerate(zip(hdrs, widths_op)):
    col = j + 2
    set_cell(ws1, row, col, h_txt, hdr_font(), hdr_fill(), ctr(), brd)
    ws1.column_dimensions[get_column_letter(col)].width = wd
ws1.row_dimensions[row].height = 20
row += 1

op_map = {}
for r in prints:
    d = r.get('details', {})
    op = d.get('operator', d.get('login', '—'))
    if op not in op_map:
        op_map[op] = {'name': op, 'count': 0, 'meters': 0}
    op_map[op]['count'] += 1
    op_map[op]['meters'] += get_meters(r)

top_ops = sorted(op_map.values(), key=lambda x: -x['meters'])[:15]
for i, op in enumerate(top_ops):
    fill = alt_fill() if i % 2 == 0 else None
    avg = round(op['meters'] / op['count']) if op['count'] else 0
    row_data = [i+1, op['name'], round(op['meters']), op['count'], avg]
    for j, val in enumerate(row_data):
        fnt = num_font() if j == 2 else reg_font()
        set_cell(ws1, row, j+2, val, fnt, fill, rgt() if j >= 2 else lft(), brd,
                 '#,##0' if j == 2 else None)
    ws1.row_dimensions[row].height = 16
    row += 1

row += 1

# ── Decors table ─────────────────────────────────────────────
ws1.merge_cells(f'B{row}:F{row}')
set_cell(ws1, row, 2, '🎨  ТОП ДЕКОРОВ', ttl_font(12), align=lft())
ws1.row_dimensions[row].height = 22
row += 1

hdrs_d = ['#', 'Декор', 'Метров', 'Этикеток', 'Операторов']
for j, h_txt in enumerate(hdrs_d):
    set_cell(ws1, row, j+2, h_txt, hdr_font(), hdr_fill(), ctr(), brd)
ws1.row_dimensions[row].height = 20
row += 1

dec_map = {}
for r in prints:
    d = r.get('details', {})
    dec = d.get('decor', '—')
    op  = d.get('operator', d.get('login', '—'))
    if dec not in dec_map:
        dec_map[dec] = {'name': dec, 'count': 0, 'meters': 0, 'ops': set()}
    dec_map[dec]['count'] += 1
    dec_map[dec]['meters'] += get_meters(r)
    dec_map[dec]['ops'].add(op)

top_decs = sorted(dec_map.values(), key=lambda x: -x['meters'])[:15]
for i, dec in enumerate(top_decs):
    fill = alt_fill() if i % 2 == 0 else None
    row_data = [i+1, dec['name'], round(dec['meters']), dec['count'], len(dec['ops'])]
    for j, val in enumerate(row_data):
        fnt = num_font() if j == 2 else reg_font()
        set_cell(ws1, row, j+2, val, fnt, fill,
                 rgt() if j >= 2 else lft(), brd,
                 '#,##0' if j == 2 else None)
    ws1.row_dimensions[row].height = 16
    row += 1

# ────────────────────────────────────────────────────────────
# SHEET 2 — По операторам
# ────────────────────────────────────────────────────────────
ws2 = wb.create_sheet('По операторам')
ws2.sheet_view.showGridLines = False

ws2.column_dimensions['A'].width = 3
col_defs = [('B', 5), ('C', 30), ('D', 16), ('E', 14), ('F', 14), ('G', 30), ('H', 14)]
for col_l, w in col_defs:
    ws2.column_dimensions[col_l].width = w

row = 2
ws2.merge_cells(f'B{row}:H{row}')
set_cell(ws2, row, 2, 'СТАТИСТИКА ПО ОПЕРАТОРАМ', hdr_font(14), hdr_fill(), ctr())
ws2.row_dimensions[row].height = 28
row += 2

hdrs2 = ['#', 'Оператор', 'Метров', 'Этикеток', 'Ср.м/эт.', 'Топ декор', 'Заданий']
for j, h_txt in enumerate(hdrs2):
    set_cell(ws2, row, j+2, h_txt, hdr_font(), hdr_fill(), ctr(), brd)
ws2.row_dimensions[row].height = 20
row += 1

for i, op in enumerate(top_ops):
    fill = alt_fill() if i % 2 == 0 else None
    avg = round(op['meters'] / op['count']) if op['count'] else 0
    # Find top decor for this operator
    op_prints = [r for r in prints if r.get('details',{}).get('operator', r.get('details',{}).get('login','')) == op['name']]
    dec_count = {}
    for r in op_prints:
        d = r.get('details', {}).get('decor', '')
        dec_count[d] = dec_count.get(d, 0) + get_meters(r)
    top_d = max(dec_count, key=dec_count.get) if dec_count else '—'
    # Tasks
    op_tasks = [t for t in task_list if t.get('assignTo') == op.get('login', '') or t.get('doneBy') == op['name']]
    done_t = sum(1 for t in op_tasks if t.get('status') in ('archived', 'done'))
    tasks_str = f'{done_t}/{len(op_tasks)}'

    row_data = [i+1, op['name'], round(op['meters']), op['count'], avg, top_d, tasks_str]
    for j, val in enumerate(row_data):
        fnt = num_font() if j == 2 else reg_font()
        set_cell(ws2, row, j+2, val, fnt, fill,
                 rgt() if j in (2,3,4,6) else lft(), brd,
                 '#,##0' if j == 2 else None)
    ws2.row_dimensions[row].height = 16
    row += 1

# ────────────────────────────────────────────────────────────
# SHEET 3 — Задания
# ────────────────────────────────────────────────────────────
ws3 = wb.create_sheet('Задания')
ws3.sheet_view.showGridLines = False

col_defs3 = [('A',3),('B',14),('C',32),('D',12),('E',12),('F',12),('G',8),('H',18),('I',14),('J',30)]
for col_l, w in col_defs3:
    ws3.column_dimensions[col_l].width = w

row = 2
ws3.merge_cells(f'B{row}:J{row}')
set_cell(ws3, row, 2, 'ОТЧЁТ ПО ЗАДАНИЯМ', hdr_font(14), hdr_fill(), ctr())
ws3.row_dimensions[row].height = 28
row += 2

hdrs3 = ['Дата', 'Декор', 'Ширина мм', 'План м', 'Факт м', '%', 'Оператор', 'Статус', 'Примечание']
for j, h_txt in enumerate(hdrs3):
    set_cell(ws3, row, j+2, h_txt, hdr_font(), hdr_fill(), ctr(), brd)
ws3.row_dimensions[row].height = 20
row += 1

status_ru = {'pending':'Ожидает','done':'Выполнено','partial':'Частично',
             'awaiting_confirm':'На проверке','archived':'Архив'}
sorted_tasks = sorted(task_list, key=lambda t: t.get('date',''), reverse=True)

for i, t in enumerate(sorted_tasks):
    fill = alt_fill() if i % 2 == 0 else None
    plan = t.get('length', 0)
    fact = t.get('doneLength', 0) or 0
    pct  = round(fact/plan*100) if plan else 0
    status = status_ru.get(t.get('status',''), t.get('status',''))
    row_data = [t.get('date',''), t.get('decor',''), t.get('width',''),
                plan, fact, f'{pct}%',
                t.get('assignTo', t.get('doneBy','')), status, t.get('note','')]
    for j, val in enumerate(row_data):
        fnt = num_font() if j in (3,4) else reg_font()
        set_cell(ws3, row, j+2, val, fnt, fill,
                 rgt() if j in (2,3,4,5) else lft(), brd,
                 '#,##0' if j in (3,4) else None)
    ws3.row_dimensions[row].height = 16
    row += 1

# Totals row
fill_tot = PatternFill('solid', fgColor='D5F5E3')
set_cell(ws3, row, 2, 'ИТОГО', Font(bold=True, size=10, name='Arial'), fill_tot, lft(), brd)
set_cell(ws3, row, 5, sum(t.get('length',0) for t in sorted_tasks), num_font(), fill_tot, rgt(), brd, '#,##0')
set_cell(ws3, row, 6, sum(t.get('doneLength',0) or 0 for t in sorted_tasks), num_font(), fill_tot, rgt(), brd, '#,##0')
ws3.row_dimensions[row].height = 18

# ────────────────────────────────────────────────────────────
# SHEET 4 — По дням
# ────────────────────────────────────────────────────────────
ws4 = wb.create_sheet('По дням')
ws4.sheet_view.showGridLines = False
ws4.column_dimensions['A'].width = 3
for col_l, w in [('B',16),('C',18),('D',14),('E',14),('F',14)]:
    ws4.column_dimensions[col_l].width = w

row = 2
ws4.merge_cells(f'B{row}:F{row}')
set_cell(ws4, row, 2, 'АКТИВНОСТЬ ПО ДНЯМ', hdr_font(14), hdr_fill(), ctr())
ws4.row_dimensions[row].height = 28
row += 2

hdrs4 = ['Дата', 'Метров', 'Этикеток', 'Операторов', 'Декоров']
for j, h_txt in enumerate(hdrs4):
    set_cell(ws4, row, j+2, h_txt, hdr_font(), hdr_fill(), ctr(), brd)
ws4.row_dimensions[row].height = 20
row += 1

day_map = {}
for r in prints:
    day = (r.get('ts') or '')[:10]
    if not day: continue
    d = r.get('details', {})
    if day not in day_map:
        day_map[day] = {'count': 0, 'meters': 0, 'ops': set(), 'decs': set()}
    day_map[day]['count'] += 1
    day_map[day]['meters'] += get_meters(r)
    day_map[day]['ops'].add(d.get('operator', '—'))
    day_map[day]['decs'].add(d.get('decor', '—'))

data_start_row = row
for i, (day, v) in enumerate(sorted(day_map.items(), reverse=True)):
    fill = alt_fill() if i % 2 == 0 else None
    row_data = [day, round(v['meters']), v['count'], len(v['ops']), len(v['decs'])]
    for j, val in enumerate(row_data):
        fnt = num_font() if j == 1 else reg_font()
        set_cell(ws4, row, j+2, val, fnt, fill,
                 rgt() if j >= 1 else lft(), brd,
                 '#,##0' if j == 1 else None)
    ws4.row_dimensions[row].height = 16
    row += 1

# Add bar chart on ws4
if day_map and len(day_map) >= 2:
    chart = BarChart()
    chart.type = 'col'
    chart.title = 'Метров по дням'
    chart.style = 10
    chart.y_axis.title = 'Метров'
    chart.x_axis.title = 'Дата'
    chart.width = 20
    chart.height = 12
    data_ref  = Reference(ws4, min_col=3, min_row=data_start_row-1, max_row=data_start_row+len(day_map)-1)
    cats_ref  = Reference(ws4, min_col=2, min_row=data_start_row, max_row=data_start_row+len(day_map)-1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats_ref)
    chart.series[0].graphicalProperties.solidFill = GREEN
    ws4.add_chart(chart, f'H{data_start_row}')

# ────────────────────────────────────────────────────────────
# SHEET 5 — Все печати (raw)
# ────────────────────────────────────────────────────────────
ws5 = wb.create_sheet('Все печати')
ws5.sheet_view.showGridLines = False
for col_l, w in [('A',3),('B',14),('C',10),('D',30),('E',20),('F',16),('G',20),('H',16),('I',20)]:
    ws5.column_dimensions[col_l].width = w

row = 2
ws5.merge_cells(f'B{row}:I{row}')
set_cell(ws5, row, 2, 'ЖУРНАЛ ПЕЧАТИ', hdr_font(14), hdr_fill(), ctr())
ws5.row_dimensions[row].height = 28
row += 2

hdrs5 = ['Дата', 'Время', 'Декор', 'Размер', 'Партия', 'Оператор', 'Логин', 'Шаблон']
for j, h_txt in enumerate(hdrs5):
    set_cell(ws5, row, j+2, h_txt, hdr_font(), hdr_fill(), ctr(), brd)
ws5.row_dimensions[row].height = 20
row += 1

sorted_prints = sorted(prints, key=lambda r: r.get('ts',''), reverse=True)
for i, r in enumerate(sorted_prints):
    d = r.get('details', {})
    ts_str = r.get('ts', '')
    try:
        dt = datetime.fromisoformat(ts_str.replace('Z','+00:00'))
        date_str = dt.strftime('%d.%m.%Y')
        time_str = dt.strftime('%H:%M')
    except:
        date_str = ts_str[:10]
        time_str = ts_str[11:16]
    fill = alt_fill() if i % 2 == 0 else None
    row_data = [date_str, time_str, d.get('decor',''), d.get('size',''),
                d.get('batch',''), d.get('operator',''), d.get('login',''), d.get('template','')]
    for j, val in enumerate(row_data):
        set_cell(ws5, row, j+2, val, reg_font(), fill, lft(), brd)
    ws5.row_dimensions[row].height = 15
    row += 1

# ── Footer on all sheets ─────────────────────────────────────
gen_time = datetime.now().strftime('%d.%m.%Y %H:%M')
for ws in [ws1, ws2, ws3, ws4, ws5]:
    last_row = ws.max_row + 2
    ws.cell(row=last_row, column=2, value=f'Сформирован: {gen_time}  |  Участок резки плёнки v3').font = Font(italic=True, size=8, color='888888', name='Arial')

# ── Freeze panes ─────────────────────────────────────────────
for ws in [ws2, ws3, ws4, ws5]:
    ws.freeze_panes = 'C5'

# ── Output base64 ────────────────────────────────────────────
buf = BytesIO()
wb.save(buf)
buf.seek(0)
print(base64.b64encode(buf.read()).decode(), end='')