186 lines
8.6 KiB
Python
186 lines
8.6 KiB
Python
import zipfile
|
||
from decimal import Decimal
|
||
from pathlib import Path
|
||
|
||
import openpyxl
|
||
import pytest
|
||
|
||
from synthgen import forms, forms_xlsx
|
||
from synthgen.periods import build_periods
|
||
|
||
TEMPLATES = Path(forms_xlsx.__file__).parent / "templates"
|
||
SAMPLE_DIR = Path("/Users/andrey.epifancev/projects/awp-ca/fixtures/statements-xlsx/mezheninovskaya/2025-Q3")
|
||
FORBIDDEN = ["7016006635", "85262693", "Межениновск", "МЕЖЕНИНОВСК", "Томск", "Шегарск", "Косарев", "Лушников", "Агрофирм", "АГРОФИРМ"]
|
||
ZERO_FORMAT = '[=0]"-";General'
|
||
|
||
|
||
@pytest.fixture(scope="module")
|
||
def sample_forms(forms_world, tmp_path_factory):
|
||
cfg, group, periods, books = forms_world
|
||
root = tmp_path_factory.mktemp("forms")
|
||
out = []
|
||
for m in group.members[:2]:
|
||
for p in (periods[0], periods[2], periods[3]):
|
||
for f in (forms.balance(books[m.code], m, p, periods, cfg.seed), forms.income(books[m.code], m, p, periods, cfg.seed)):
|
||
path = root / f"{m.code}_{p.key}_{forms_xlsx.file_name(f)}"
|
||
forms_xlsx.write(f, path)
|
||
out.append((f, path))
|
||
return out
|
||
|
||
|
||
def _raw_strings(path: Path) -> str:
|
||
with zipfile.ZipFile(path) as z:
|
||
return "\n".join(z.read(n).decode("utf-8", "ignore") for n in z.namelist())
|
||
|
||
|
||
def _find_table(ws):
|
||
"""Все таблицы форм по заголовку «Коды»: [(колонка кодов, [колонки значений], {код: строка})]."""
|
||
tables = []
|
||
for row in ws.iter_rows():
|
||
for c in row:
|
||
if c.value == "Коды":
|
||
cols = [x.column for x in ws[c.row + 1] if x.value is not None and x.column > c.column]
|
||
tables.append((c.column, cols, c.row))
|
||
return tables
|
||
|
||
|
||
def _read_lines(ws):
|
||
"""код -> [значения колонок] по всем таблицам листа."""
|
||
result = {}
|
||
cells = {}
|
||
for code_col, value_cols, head in _find_table(ws):
|
||
for r in range(head + 2, ws.max_row + 1):
|
||
v = ws.cell(r, code_col).value
|
||
if isinstance(v, str) and v.isdigit() and len(v) == 4:
|
||
result[v] = [ws.cell(r, c).value for c in value_cols]
|
||
cells[v] = [ws.cell(r, c) for c in value_cols]
|
||
return result, cells
|
||
|
||
|
||
def _sample_signers() -> list[str]:
|
||
names = ["Косарев Константин Евгеньевич", "Лушникова Светлана Михайловна"]
|
||
if SAMPLE_DIR.exists():
|
||
ws = openpyxl.load_workbook(SAMPLE_DIR / "1_Форма1_бух_09.xlsx").active
|
||
names += [ws["Q80"].value, ws["Q83"].value]
|
||
return [n for n in names if n]
|
||
|
||
|
||
@pytest.mark.parametrize("name,last_col", [("bb_1c.xlsx", 20), ("ofr_1c.xlsx", 18)])
|
||
def test_template_sanitized(name, last_col):
|
||
path = TEMPLATES / name
|
||
wb = openpyxl.load_workbook(path)
|
||
ws = wb.active
|
||
for row in ws.iter_rows():
|
||
for c in row:
|
||
assert not (isinstance(c.value, str) and c.value.startswith("=")), (c.coordinate, c.value)
|
||
if c.column > last_col:
|
||
assert c.value is None, c.coordinate
|
||
raw = _raw_strings(path)
|
||
for bad in FORBIDDEN + _sample_signers():
|
||
assert bad not in raw, bad
|
||
lines, _ = _read_lines(ws)
|
||
assert all(v is None for vals in lines.values() for v in vals), "в шаблоне нет значений строк"
|
||
assert set(forms.BALANCE_CODES if name == "bb_1c.xlsx" else forms.INCOME_CODES) == set(lines)
|
||
|
||
|
||
def test_written_file_no_sample_values(sample_forms):
|
||
signers = _sample_signers()
|
||
for form, path in sample_forms:
|
||
raw = _raw_strings(path)
|
||
for bad in FORBIDDEN + signers:
|
||
assert bad not in raw, (path.name, bad)
|
||
ws = openpyxl.load_workbook(path).active
|
||
last = 20 if form.kind == "BALANCE" else 18
|
||
assert all(c.value is None for row in ws.iter_rows(min_col=last + 1) for c in row)
|
||
assert not any(isinstance(c.value, str) and c.value.startswith("=") for row in ws.iter_rows() for c in row)
|
||
|
||
|
||
def test_file_name():
|
||
ps = build_periods("2025-Q1", 4)
|
||
|
||
class F: # только то, что нужно file_name
|
||
def __init__(self, kind, period):
|
||
self.kind, self.period = kind, period
|
||
|
||
assert forms_xlsx.file_name(F("BALANCE", ps[2])) == "ББ_9 месяцев 2025 г.xlsx"
|
||
assert forms_xlsx.file_name(F("INCOME", ps[3])) == "ОФР_2025 г.xlsx"
|
||
|
||
|
||
def test_codes_and_values(sample_forms):
|
||
for form, path in sample_forms:
|
||
ws = openpyxl.load_workbook(path).active
|
||
lines, cells = _read_lines(ws)
|
||
codes = forms.BALANCE_CODES if form.kind == "BALANCE" else forms.INCOME_CODES
|
||
assert set(lines) == set(codes), path.name
|
||
for code in codes:
|
||
for col, want in enumerate(form.values[code]):
|
||
got = lines[code][col]
|
||
assert isinstance(got, int) and got == want, (path.name, code, col, got, want)
|
||
assert cells[code][col].number_format == (ZERO_FORMAT if want == 0 else "#,##0"), (code, col)
|
||
assert any(v == 0 for f, _ in sample_forms for vals in f.values.values() for v in vals)
|
||
|
||
|
||
def test_requisites(sample_forms):
|
||
for form, path in sample_forms:
|
||
ws = openpyxl.load_workbook(path).active
|
||
m, p = form.member, form.period
|
||
title = ws["B2"].value
|
||
assert title == ("БУХГАЛТЕРСКИЙ БАЛАНС" if form.kind == "BALANCE" else "ОТЧЕТ О ФИНАНСОВЫХ РЕЗУЛЬТАТАХ")
|
||
assert ws["B3"].value == f"за {p.label_1c}"
|
||
assert ws["G7"].value == m.name
|
||
if form.kind == "BALANCE":
|
||
assert ws["R8"].value == m.inn and ws["R6"].value == p.end.day and ws["S6"].value == p.end.month
|
||
assert ws["T6"].value == p.end.year and ws["R7"].value and ws["R7"].value != "85262693"
|
||
head = [ws["M23"].value, ws["Q23"].value, ws["S23"].value]
|
||
head2 = [ws["M48"].value, ws["Q48"].value, ws["S48"].value]
|
||
else:
|
||
assert ws["P8"].value == m.inn and ws["P6"].value == p.end.day and ws["Q6"].value == p.end.month
|
||
assert ws["R6"].value == p.end.year
|
||
head = [ws["O14"].value, ws["Q14"].value]
|
||
head2 = [ws["O36"].value, ws["Q36"].value]
|
||
assert head == form.columns and head2 == form.columns
|
||
if form.kind == "BALANCE":
|
||
assert ws["G13"].value and "Томск" not in ws["G13"].value
|
||
assert ws["G10"].value and (ws["G10"].value.startswith("АО") if m.org.form == "АО" else ws["G10"].value.startswith("ООО"))
|
||
|
||
|
||
def test_signers_are_synthetic(sample_forms):
|
||
form, path = sample_forms[0]
|
||
ws = openpyxl.load_workbook(path).active
|
||
cell = ws["Q80"] if form.kind == "BALANCE" else ws["O49"]
|
||
cell2 = ws["Q83"] if form.kind == "BALANCE" else ws["O52"]
|
||
assert cell.value and cell2.value and cell.value not in _sample_signers() and cell.value != cell2.value
|
||
assert len(cell.value.split()) == 3
|
||
|
||
|
||
def test_style_kept(sample_forms):
|
||
for form, path in sample_forms:
|
||
name = "bb_1c.xlsx" if form.kind == "BALANCE" else "ofr_1c.xlsx"
|
||
tpl = openpyxl.load_workbook(TEMPLATES / name).active
|
||
ws = openpyxl.load_workbook(path).active
|
||
assert ws["B2"].font.name == "Times New Roman" and ws["B2"].font.b and ws["B2"].font.sz == 12
|
||
assert {str(r) for r in ws.merged_cells.ranges} == {str(r) for r in tpl.merged_cells.ranges} and ws.merged_cells.ranges
|
||
assert {k: v.width for k, v in ws.column_dimensions.items()} == {k: v.width for k, v in tpl.column_dimensions.items()}
|
||
assert {k: v.height for k, v in ws.row_dimensions.items()} == {k: v.height for k, v in tpl.row_dimensions.items()}
|
||
_, cells = _read_lines(ws)
|
||
_, tcells = _read_lines(tpl)
|
||
code = "1150" if form.kind == "BALANCE" else "2110"
|
||
assert cells[code][0].font.name == "Times New Roman" and cells[code][0].font.sz == tcells[code][0].font.sz
|
||
assert cells[code][0].border.left.style and cells[code][0].border.left.style == tcells[code][0].border.left.style
|
||
|
||
|
||
def test_deterministic_bytes(sample_forms, tmp_path):
|
||
form, path = sample_forms[0]
|
||
again = tmp_path / "again.xlsx"
|
||
forms_xlsx.write(form, again)
|
||
assert again.read_bytes() == path.read_bytes()
|
||
|
||
|
||
def test_negative_expenses_written_as_numbers(sample_forms):
|
||
for form, path in sample_forms:
|
||
if form.kind != "INCOME":
|
||
continue
|
||
lines, _ = _read_lines(openpyxl.load_workbook(path).active)
|
||
assert all(not isinstance(v, str) for vals in lines.values() for v in vals), "числа, не текст со скобками"
|
||
assert lines["2120"][0] <= 0 and Decimal(lines["2120"][0]) == form.values["2120"][0]
|