84 lines
2.1 KiB
Plaintext
84 lines
2.1 KiB
Plaintext
import io
|
|
import base64
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font, Alignment
|
|
|
|
userid = await get_user()
|
|
userorgid = await get_userorgid()
|
|
|
|
start_date = params_kw.get('start_date', '')
|
|
end_date = params_kw.get('end_date', '')
|
|
|
|
if not start_date or not end_date:
|
|
return json.dumps({'status': 'error', 'data': {'message': '缺少日期参数'}}, ensure_ascii=False)
|
|
|
|
ns = {
|
|
'orgid': userorgid,
|
|
'start_date': start_date,
|
|
'end_date': end_date
|
|
}
|
|
|
|
async with get_sor_context(request._run_ns, 'accounting') as sor:
|
|
sql = """select d.acc_date, d.acc_timestamp, s.name as subject_name, d.acc_dir,
|
|
d.summary, d.amount, d.balance
|
|
from acc_detail d
|
|
join account a on d.accountid = a.id COLLATE utf8mb4_unicode_ci
|
|
join subject s on a.subjectid = s.id COLLATE utf8mb4_unicode_ci
|
|
where a.orgid = ${orgid}$
|
|
and d.acc_date >= ${start_date}$
|
|
and d.acc_date <= ${end_date}$
|
|
order by d.acc_date desc"""
|
|
recs = await sor.sqlExe(sql, ns)
|
|
|
|
# 生成 xlsx
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = '账单明细'
|
|
|
|
# 表头
|
|
headers = ['日期', '时间', '科目', '方向', '摘要', '金额', '余额']
|
|
ws.append(headers)
|
|
header_font = Font(bold=True)
|
|
for cell in ws[1]:
|
|
cell.font = header_font
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# 数据
|
|
for rec in recs:
|
|
direction = '借' if rec.acc_dir == '1' else '贷'
|
|
ws.append([
|
|
str(rec.acc_date),
|
|
str(rec.acc_timestamp),
|
|
rec.subject_name,
|
|
direction,
|
|
rec.summary,
|
|
float(rec.amount),
|
|
float(rec.balance)
|
|
])
|
|
|
|
# 调整列宽
|
|
ws.column_dimensions['A'].width = 12
|
|
ws.column_dimensions['B'].width = 20
|
|
ws.column_dimensions['C'].width = 15
|
|
ws.column_dimensions['D'].width = 8
|
|
ws.column_dimensions['E'].width = 40
|
|
ws.column_dimensions['F'].width = 15
|
|
ws.column_dimensions['G'].width = 15
|
|
|
|
# 保存到内存
|
|
output = io.BytesIO()
|
|
wb.save(output)
|
|
output.seek(0)
|
|
|
|
# Base64 编码
|
|
b64_data = base64.b64encode(output.read()).decode('utf-8')
|
|
filename = f'账单明细_{start_date}_{end_date}.xlsx'
|
|
|
|
return json.dumps({
|
|
'status': 'ok',
|
|
'data': {
|
|
'filename': filename,
|
|
'content': b64_data
|
|
}
|
|
}, ensure_ascii=False)
|