152 lines
4.7 KiB
Python
152 lines
4.7 KiB
Python
import pandas as pd
|
||
from datetime import datetime
|
||
|
||
from services.contractors import get_contractors
|
||
|
||
|
||
def _find_column(df, aliases):
|
||
for name in aliases:
|
||
if name in df.columns:
|
||
return name
|
||
return None
|
||
|
||
|
||
def import_sbis_excel(file, conn):
|
||
contractors = {
|
||
name: contractor_id
|
||
for contractor_id, name in get_contractors(conn)
|
||
}
|
||
|
||
cursor = conn.cursor()
|
||
df = pd.read_excel(file)
|
||
|
||
required = {
|
||
"contractor": _find_column(
|
||
df, ["Контрагент", "Наименование контрагента", "Организация"]
|
||
),
|
||
"doc_date": _find_column(df, ["Дата", "Дата документа"]),
|
||
"doc_number": _find_column(df, ["Номер", "Номер документа"]),
|
||
"doc_type": _find_column(df, ["Вид документа", "Тип документа", "Тип"]),
|
||
"amount": _find_column(df, ["Сумма"]),
|
||
"state": _find_column(df, ["Состояние", "Статус", "Состояние документа"]),
|
||
}
|
||
|
||
missing = [key for key, col in required.items() if col is None]
|
||
if missing:
|
||
raise ValueError(
|
||
f"В файле не найдены обязательные колонки: {', '.join(missing)}"
|
||
)
|
||
|
||
responsible_col = _find_column(
|
||
df, ["Ответственный", "Ответственный сотрудник"]
|
||
)
|
||
description_col = _find_column(df, ["Описание", "Комментарий", "Примечание"])
|
||
|
||
imported = 0
|
||
updated = 0
|
||
skipped = 0
|
||
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
||
|
||
# Получаем максимальный row_order для новых записей
|
||
cursor.execute("SELECT COALESCE(MAX(row_order), 0) FROM documents_sbis")
|
||
max_order = cursor.fetchone()[0] or 0
|
||
|
||
for idx, (_, row) in enumerate(df.iterrows()):
|
||
contr = str(row[required["contractor"]]).strip()
|
||
|
||
if contr not in contractors:
|
||
skipped += 1
|
||
continue
|
||
|
||
doc_type = row[required["doc_type"]]
|
||
doc_date = row[required["doc_date"]]
|
||
number = row[required["doc_number"]]
|
||
summ = row[required["amount"]]
|
||
state = row[required["state"]]
|
||
|
||
amount = float(summ) if pd.notna(summ) else 0
|
||
responsible = (
|
||
str(row[responsible_col]).strip()
|
||
if responsible_col and pd.notna(row[responsible_col])
|
||
else None
|
||
)
|
||
description = (
|
||
str(row[description_col]).strip()
|
||
if description_col and pd.notna(row[description_col])
|
||
else None
|
||
)
|
||
|
||
# Проверяем существование документа
|
||
cursor.execute(
|
||
"""
|
||
SELECT id
|
||
FROM documents_sbis
|
||
WHERE contractor = ?
|
||
AND doc_type = ?
|
||
AND doc_date = ?
|
||
AND doc_number = ?
|
||
AND amount = ?
|
||
""",
|
||
(
|
||
contr,
|
||
str(doc_type),
|
||
str(doc_date),
|
||
str(number),
|
||
amount,
|
||
),
|
||
)
|
||
|
||
document = cursor.fetchone()
|
||
|
||
if document:
|
||
cursor.execute(
|
||
"""
|
||
UPDATE documents_sbis
|
||
SET state = ?,
|
||
responsible = COALESCE(?, responsible),
|
||
description = COALESCE(?, description),
|
||
updated_at = ?
|
||
WHERE id = ?
|
||
""",
|
||
(str(state), responsible, description, now, document[0]),
|
||
)
|
||
updated += 1
|
||
else:
|
||
row_order = max_order + idx + 1
|
||
cursor.execute(
|
||
"""
|
||
INSERT INTO documents_sbis (
|
||
raw_text,
|
||
doc_date,
|
||
doc_number,
|
||
contractor,
|
||
doc_type,
|
||
amount,
|
||
description,
|
||
state,
|
||
responsible,
|
||
created_at,
|
||
updated_at,
|
||
row_order
|
||
)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
""",
|
||
(
|
||
None,
|
||
str(doc_date),
|
||
str(number),
|
||
contr,
|
||
str(doc_type),
|
||
amount,
|
||
description,
|
||
str(state),
|
||
responsible,
|
||
now,
|
||
now,
|
||
row_order,
|
||
),
|
||
)
|
||
imported += 1
|
||
|
||
conn.commit()
|
||
return imported, updated, skipped |